phpOpenTracker/phpOpenTracker/DB mysql_merge.php,1.22.2.2,1.22.2.3
Sebastian Bergmann <[email protected]> Fri, 12 Mar 2004 08:34:24 +0000
| Newsgroups | gmane.comp.web.phpopentracker.cvs |
|---|---|
| Message-ID | <[email protected]> |
Update of /cvsroot/phpopencounter/phpOpenTracker/phpOpenTracker/DB
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv24066/phpOpenTracker/DB
Modified Files:
Tag: phpOpenTracker_1
mysql_merge.php
Log Message:
Add a new mode of operation to the MySQL Merge Tables handler that maintains separate accesslog/visitors tables per day.
Index: mysql_merge.php
===================================================================
RCS file: /cvsroot/phpopencounter/phpOpenTracker/phpOpenTracker/DB/Attic/mysql_merge.php,v
retrieving revision 1.22.2.2
retrieving revision 1.22.2.3
diff -C2 -d -r1.22.2.2 -r1.22.2.3
*** mysql_merge.php 24 Jan 2004 19:39:07 -0000 1.22.2.2
--- mysql_merge.php 12 Mar 2004 08:34:22 -0000 1.22.2.3
***************
*** 77,138 ****
/**
- * Creates a new merge table.
- *
- * @param string $name
- * @param string $type
- * @param mixed $tables
- * @param optional boolean $temporary
- * @access public
- */
- function createMergeTable($name, $type, $tables, $temporary = false) {
- if (is_array($tables)) {
- $tables = implode(',', $tables);
- }
-
- if ($temporary) {
- parent::query(
- sprintf(
- 'DROP TABLE IF EXISTS %s',
-
- $name
- )
- );
- }
-
- parent::query(
- sprintf(
- ($type == 'accesslog') ? $this->accesslogSchema : $this->visitorsSchema,
- $temporary ? 'TEMPORARY' : '',
- !$temporary ? 'IF NOT EXISTS' : '',
- $name,
- 'TYPE=MRG_MyISAM UNION=(' . $tables . ')'
- )
- );
- }
-
- /**
- * Fetches a row from the current result set.
- *
- * @param optional boolean $fetchAssoc
- * @access public
- * @return array
- */
- function fetchRow($fetchAssoc = true) {
- if (is_resource($this->result)) {
- if ($fetchAssoc) {
- $row = @mysql_fetch_assoc($this->result);
- } else {
- $row = @mysql_fetch_row($this->result);
- }
-
- if (is_array($row)) {
- return $row;
- }
- }
-
- return false;
- }
-
- /**
* Performs an SQL query.
*
--- 77,80 ----
***************
*** 155,162 ****
$insert = true;
! $query = $this->_replaceTableNames(
! $query,
! date('Y') . date('m')
! );
} else {
$query = $this->_rewriteSelectQuery($query);
--- 97,101 ----
$insert = true;
! $query = $this->_replaceTableNames($query);
} else {
$query = $this->_rewriteSelectQuery($query);
***************
*** 183,187 ****
!$tablesAlreadyCreated &&
mysql_errno($this->connection) == 1146) {
! $this->_newTables();
$this->query($query, $limit, $warnOnFailure, true);
$throwError = false;
--- 122,126 ----
!$tablesAlreadyCreated &&
mysql_errno($this->connection) == 1146) {
! $this->_createNewTables();
$this->query($query, $limit, $warnOnFailure, true);
$throwError = false;
***************
*** 198,297 ****
/**
! * Creates new pot_accesslog and pot_visitors tables
! * for a given month.
*
! * @param optional integer $month
! * @param optional integer $year
* @access private
*/
! function _newTables($month = '', $year = '') {
! $month = !empty($month) ? $month : date('n');
! $year = !empty($year) ? $year : date('Y');
!
! $this->_newTable(
! $this->config['accesslog_table'],
! 'accesslog',
! $month,
! $year
! );
! $this->_newTable(
! $this->config['visitors_table'],
! 'visitors',
! $month,
! $year
);
}
/**
! * Creates a new table and adds it to the
! * appropriate MRG_MyISAM table.
*
! * @param string $name
! * @param string $type
! * @param integer $month
! * @param integer $year
* @access private
*/
! function _newTable($name, $type, $month, $year) {
! $mergeTableExists = false;
! $tablePrefix = $name . '_';
! $tables = array();
! for ($i = $month; $i <= 12; $i++) {
parent::query(
sprintf(
! ($type == 'accesslog') ? $this->accesslogSchema : $this->visitorsSchema,
! '',
! 'IF NOT EXISTS',
! $tablePrefix . $year . sprintf('%02d', $i),
! $this->config['delay_key_write'] ? 'DELAY_KEY_WRITE=1' : ''
)
);
}
! parent::query('SHOW TABLES');
! while ($row = $this->fetchRow(false)) {
! if (strstr($row[0], $tablePrefix)) {
! $tables[] = $row[0];
}
! else if ($row[0] == $name) {
! $mergeTableExists = true;
}
}
! sort($tables);
! $tables = implode(',', $tables);
! if ($mergeTableExists) {
! parent::query(
! sprintf(
! 'ALTER TABLE %s UNION=(%s)',
! $name,
! $tables
! )
! );
! } else {
! $this->createMergeTable(
! $name,
! $type,
! $tables
! );
}
}
/**
* Replaces the names of the pot_accesslog and pot_visitors
! * merge tables with the ones for a given month.
*
! * @param string $query
! * @param string $suffix
* @return string
* @access private
*/
! function _replaceTableNames($query, $suffix) {
return str_replace(
array(
--- 137,419 ----
/**
! * Alters an existing merge table.
*
! * @param string $name
* @access private
*/
! function _alterMergeTable($name) {
! parent::query(
! sprintf(
! 'ALTER TABLE %s UNION=(%s)',
! $name,
! $this->_getMergeTables($name)
! )
);
}
/**
! * Creates a merge table.
*
! * @param string $name
! * @param string $type
! * @param array $tables
! * @param optional boolean $mayExist
! * @param optional boolean $temporary
* @access private
*/
! function _createMergeTable($name, $type, $tables, $mayExist = true, $temporary = false) {
! if ($mayExist && $this->_tableExists($name)) {
! $this->_alterMergeTable($name);
! return;
! }
! if ($temporary) {
parent::query(
sprintf(
! 'DROP TABLE IF EXISTS %s',
!
! $name
)
);
}
! parent::query(
! sprintf(
! ($type == 'accesslog') ? $this->accesslogSchema : $this->visitorsSchema,
! $temporary ? 'TEMPORARY' : '',
! !$temporary ? 'IF NOT EXISTS' : '',
! $name,
! 'TYPE=MRG_MyISAM UNION=(' . implode(',', $tables) . ')'
! )
! );
! }
! /**
! * Creates new pot_accesslog and pot_visitors tables
! * to accomodate INSERTs for the current month
! * (when in "day" mode) or the current year (when running
! * in "month" mode).
! *
! * @access private
! */
! function _createNewTables() {
! $accesslogTables = array();
! $visitorsTables = array();
!
! $currentDay = date('j');
! $currentMonth = date('n');
! $currentYear = date('Y');
!
! switch ($this->config['merge_tables_mode']) {
! case 'day': {
! $numDaysInCurrentMonth = date(
! 't',
! mktime(0, 0, 0, $currentMonth, 1, $currentYear)
! );
!
! for ($day = $currentDay; $day <= $numDaysInCurrentMonth; $day++) {
! $newAccesslogTable = sprintf(
! '%s_%d%02d%02d',
!
! $this->config['accesslog_table'],
! $currentYear,
! $currentMonth,
! $day
! );
!
! $accesslogTables[] = $newAccesslogTable;
!
! $this->_createNewTable(
! 'accesslog',
! $newAccesslogTable
! );
!
! $newVisitorsTable = sprintf(
! '%s_%d%02d%02d',
!
! $this->config['visitors_table'],
! $currentYear,
! $currentMonth,
! $day
! );
!
! $visitorsTables[] = $newVisitorsTable;
!
! $this->_createNewTable(
! 'visitors',
! $newVisitorsTable
! );
! }
!
! // Create merge tables for current month.
! $this->_createMergeTable(
! sprintf(
! '%s_%d%02d',
!
! $this->config['accesslog_table'],
! $currentYear,
! $currentMonth
! ),
! 'accesslog',
! $accesslogTables,
! false
! );
!
! $this->_createMergeTable(
! sprintf(
! '%s_%d%02d',
!
! $this->config['visitors_table'],
! $currentYear,
! $currentMonth
! ),
! 'visitors',
! $visitorsTables,
! false
! );
!
! // Create global merge tables.
! $this->_createMergeTable(
! $this->config['accesslog_table'],
! 'accesslog',
! $accesslogTables
! );
!
! $this->_createMergeTable(
! $this->config['visitors_table'],
! 'visitors',
! $visitorsTables
! );
}
+ break;
! case 'month': {
! for ($month = $currentMonth; $month <= 12; $month++) {
! $this->_createNewTable(
! 'accesslog',
! sprintf(
! '%s_%d%02d',
!
! $this->config['accesslog_table'],
! $currentYear,
! $month
! )
! );
!
! $this->_createNewTable(
! 'visitors',
! sprintf(
! '%s_%d%02d',
!
! $this->config['visitors_table'],
! $currentYear,
! $month
! )
! );
! }
}
+ break;
}
+ }
! /**
! * Helper method for _createNewTables().
! *
! * @param string $type
! * @param string $name
! * @access private
! * @see _createNewTables()
! */
! function _createNewTable($type, $name) {
! parent::query(
! sprintf(
! ($type == 'accesslog') ? $this->accesslogSchema : $this->visitorsSchema,
! '',
! 'IF NOT EXISTS',
! $name,
! $this->config['delay_key_write'] ? 'DELAY_KEY_WRITE=1' : ''
! )
! );
! }
! /**
! * Returns the first field of the next row.
! *
! * @return string
! * @access private
! */
! function _fetchFirstField() {
! if (is_resource($this->result)) {
! $row = @mysql_fetch_row($this->result);
! if (is_array($row)) {
! return $row[0];
! }
}
+
+ return false;
+ }
+
+ /**
+ * Returns a comma-separated list of existing merge tables.
+ *
+ * @param string $name
+ * @return string
+ * @access private
+ */
+ function _getMergeTables($name) {
+ $tables = array();
+
+ switch ($this->config['merge_tables_mode']) {
+ case 'day': {
+ $tableNameLength = strlen($name) + 9;
+ }
+ break;
+
+ case 'month': {
+ $tableNameLength = strlen($name) + 7;
+ }
+ break;
+ }
+
+ parent::query('SHOW TABLES');
+
+ while ($field = $this->_fetchFirstField()) {
+ if (strstr($field, $name) &&
+ strlen($field) == $tableNameLength) {
+ $tables[] = $field;
+ }
+ }
+
+ sort($tables);
+
+ return implode(',', $tables);
}
/**
* Replaces the names of the pot_accesslog and pot_visitors
! * merge tables with the ones for a given day or month.
*
! * @param string $query
! * @param optional string $suffix
* @return string
* @access private
*/
! function _replaceTableNames($query, $suffix = '') {
! if ($suffix == '') {
! switch ($this->config['merge_tables_mode']) {
! case 'day': {
! $suffix = date('Y') . date('m') . date('d');
! }
! break;
!
! case 'month': {
! $suffix = date('Y') . date('m');
! }
! break;
! }
! }
!
return str_replace(
array(
***************
*** 299,306 ****
--- 421,430 ----
$this->config['visitors_table']
),
+
array(
$this->config['accesslog_table'] . '_' . $suffix,
$this->config['visitors_table'] . '_' . $suffix
),
+
$query
);
***************
*** 315,395 ****
*/
function _rewriteSelectQuery($query) {
! $_query = explode(' ', $query);
! $between = array_search('BETWEEN', $_query);
if ($between != false) {
! $month = date('n', $_query[$between + 1]);
! $year = date('Y', $_query[$between + 1]);
! $endMonth = date('n', $_query[$between + 3]);
! $endYear = date('Y', $_query[$between + 3]);
! if ($year == $endYear &&
! $month == $endMonth) {
! $query = $this->_replaceTableNames(
$query,
! $year . sprintf('%02d', $month)
);
! } else {
! $accesslogTables = array();
! $visitorsTables = array();
!
! $done = false;
!
! while (!$done) {
! $accesslogTables[] = sprintf(
! '%s_%d%02d',
!
! $this->config['accesslog_table'],
! $year,
! $month
! );
!
! $visitorsTables[] = sprintf(
! '%s_%d%02d',
!
! $this->config['visitors_table'],
! $year,
! $month
! );
! if ($month == $endMonth &&
! $year == $endYear) {
! $done = true;
! }
! else if ($month < 12) {
! $month++;
! } else {
! $month = 1;
! $year++;
! }
! }
! if (sizeof($accesslogTables) <=
! $this->config['merge_tables_threshold']) {
! $this->createMergeTable(
! $this->config['accesslog_table'] . '_temporary',
! 'accesslog',
! $accesslogTables,
! true
! );
! $this->createMergeTable(
! $this->config['visitors_table'] . '_temporary',
! 'visitors',
! $visitorsTables,
! true
! );
! $query = $this->_replaceTableNames(
! $query,
! 'temporary'
! );
! }
}
}
! return $query;
}
}
--- 439,497 ----
*/
function _rewriteSelectQuery($query) {
! $parsedQuery = explode(' ', $query);
! $between = array_search('BETWEEN', $parsedQuery);
if ($between != false) {
! $startDay = date('j', $parsedQuery[$between + 1]);
! $startMonth = date('n', $parsedQuery[$between + 1]);
! $startYear = date('Y', $parsedQuery[$between + 1]);
! $endDay = date('j', $parsedQuery[$between + 3]);
! $endMonth = date('n', $parsedQuery[$between + 3]);
! $endYear = date('Y', $parsedQuery[$between + 3]);
! if ($this->config['merge_tables_mode'] == 'day' &&
! $startDay == $endDay &&
! $startMonth == $endMonth &&
! $startYear == $endYear) {
! return $this->_replaceTableNames(
$query,
! $startYear . $startMonth . $startDay;
);
! }
! else if ($startMonth == $endMonth &&
! $startYear == $endYear) {
! return $this->_replaceTableNames(
! $query,
! $startYear . $startMonth;
! );
! }
! else {
! // XXX: TO BE IMPLEMENTED
! }
! }
! return $query;
! }
! /**
! * Checks whether a table exists.
! *
! * @param string $name
! * @return boolean
! * @access private
! */
! function _tableExists($name) {
! parent::query('SHOW TABLES');
! while ($field = $this->_fetchFirstField()) {
! if ($field == $name) {
! return true;
}
}
! return false;
}
}
-------------------------------------------------------
This SF.Net email is sponsored by: IBM Linux Tutorials
Free Linux tutorial presented by Daniel Robbins, President and CEO of
GenToo technologies. Learn everything from fundamentals to system
administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click