The table prefix is determined by the [database] tables_prefix INI config
option.
public static unprefixTable ( string $table ) : string | ||
$table | string | The prefixed table name, eg "piwik-production_log_visit". |
return | string | The unprefixed table name, eg "log_visit". |
public function getMigrations(Updater $updater) { $migrations = array($this->migration->db->addColumn('log_visit', 'visit_goal_converted', 'TINYINT( 1 ) NOT NULL', 'visit_total_time'), $this->migration->db->sql('CREATE TABLE `' . Common::prefixTable('goal') . "` (\n `idsite` int(11) NOT NULL,\n `idgoal` int(11) NOT NULL,\n `name` varchar(50) NOT NULL,\n `match_attribute` varchar(20) NOT NULL,\n `pattern` varchar(255) NOT NULL,\n `pattern_type` varchar(10) NOT NULL,\n `case_sensitive` tinyint(4) NOT NULL,\n `revenue` float NOT NULL,\n `deleted` tinyint(4) NOT NULL default '0',\n PRIMARY KEY (`idsite`,`idgoal`)\n )", Updater\Migration\Db::ERROR_CODE_TABLE_EXISTS), $this->migration->db->sql('CREATE TABLE `' . Common::prefixTable('log_conversion') . '` ( `idvisit` int(10) unsigned NOT NULL, `idsite` int(10) unsigned NOT NULL, `visitor_idcookie` char(32) NOT NULL, `server_time` datetime NOT NULL, `visit_server_date` date NOT NULL, `idaction` int(11) NOT NULL, `idlink_va` int(11) NOT NULL, `referer_idvisit` int(10) unsigned default NULL, `referer_type` int(10) unsigned default NULL, `referer_name` varchar(70) default NULL, `referer_keyword` varchar(255) default NULL, `visitor_returning` tinyint(1) NOT NULL, `location_country` char(3) NOT NULL, `location_continent` char(3) NOT NULL, `url` text NOT NULL, `idgoal` int(10) unsigned NOT NULL, `revenue` float default NULL, PRIMARY KEY (`idvisit`,`idgoal`), KEY `index_idsite_date` (`idsite`,`visit_server_date`) )', Updater\Migration\Db::ERROR_CODE_TABLE_EXISTS)); $tables = DbHelper::getTablesInstalled(); foreach ($tables as $tableName) { if (preg_match('/archive_/', $tableName) == 1) { $columns = array('idsite', 'date1', 'date2', 'name', 'ts_archived'); $tableNameUnprefixed = Common::unprefixTable($tableName); $migrations[] = $this->migration->db->addIndex($tableNameUnprefixed, $columns, 'index_all'); } } return $migrations; }
public function getTableNameKey($tableName) { $result = Common::unprefixTable($tableName); if (strpos($tableName, "archive_numeric")) { $result = "archive_numeric"; } else { if (strpos($tableName, "archive_blob")) { $result = "archive_blob"; } } return $result; }
public function getMigrations(Updater $updater) { $migrations = array($this->migration->db->dropIndex('log_visit', 'index_idsite_date'), $this->migration->db->addIndex('log_visit', array('idsite', 'visit_server_date', 'config_md5config(8)'), 'index_idsite_date_config')); $tables = DbHelper::getTablesInstalled(); foreach ($tables as $tableName) { $unprefixedTable = Common::unprefixTable($tableName); if (preg_match('/archive_/', $tableName) == 1) { $migrations[] = $this->migration->db->dropIndex($unprefixedTable, 'index_all'); } if (preg_match('/archive_numeric_/', $tableName) == 1) { $columns = array('idsite', 'date1', 'date2', 'period'); $migrations[] = $this->migration->db->addIndex($unprefixedTable, $columns, 'index_idsite_dates_period'); } } return $migrations; }
public static function getDateFromTableName($tableName) { $tableName = Common::unprefixTable($tableName); $date = str_replace(array('archive_numeric_', 'archive_blob_'), '', $tableName); return $date; }
public function doUpdate(Updater $updater) { $returningMetrics = array('nb_visits_returning', 'nb_actions_returning', 'max_actions_returning', 'sum_visit_length_returning', 'bounce_count_returning', 'nb_visits_converted_returning', 'nb_uniq_visitors_returning'); $now = Date::factory('now')->getDatetime(); $archiveNumericTables = Db::get()->fetchCol("SHOW TABLES LIKE '%archive_numeric%'"); // for each numeric archive table, copy *_returning metrics to VisitsSummary metrics w/ the appropriate // returning visit segment foreach ($archiveNumericTables as $table) { // get archives w/ *._returning $sql = "SELECT idarchive, idsite, period, date1, date2 FROM {$table}\n WHERE name IN ('" . implode("','", $returningMetrics) . "')\n GROUP BY idarchive"; $idArchivesWithReturning = Db::fetchAll($sql); // get archives for visitssummary returning visitor segment $sql = "SELECT idarchive, idsite, period, date1, date2 FROM {$table}\n WHERE name = ? GROUP BY idarchive"; $visitSummaryReturningSegmentDone = Rules::getDoneFlagArchiveContainsOnePlugin(new Segment(VisitFrequencyApi::RETURNING_VISITOR_SEGMENT, $idSites = array()), 'VisitsSummary'); $idArchivesWithVisitReturningSegment = Db::fetchAll($sql, array($visitSummaryReturningSegmentDone)); // collect info for new visitssummary archives have to be created to match archives w/ *._returning // metrics $missingIdArchives = array(); $idArchiveMappings = array(); foreach ($idArchivesWithReturning as $row) { $withMetricsIdArchive = $row['idarchive']; foreach ($idArchivesWithVisitReturningSegment as $segmentRow) { if ($row['idsite'] == $segmentRow['idsite'] && $row['period'] == $segmentRow['period'] && $row['date1'] == $segmentRow['date1'] && $row['date2'] == $segmentRow['date2']) { $idArchiveMappings[$withMetricsIdArchive] = $segmentRow['idarchive']; } } if (!isset($idArchiveMappings[$withMetricsIdArchive])) { $missingIdArchives[$withMetricsIdArchive] = $row; } } // if there are missing idarchives, fill out new archive row values if (!empty($missingIdArchives)) { $newIdArchiveStart = Db::fetchOne("SELECT MAX(idarchive) FROM {$table}") + 1; foreach ($missingIdArchives as $withMetricsIdArchive => &$rowToInsert) { $idArchiveMappings[$withMetricsIdArchive] = $newIdArchiveStart; $rowToInsert['idarchive'] = $newIdArchiveStart; $rowToInsert['ts_archived'] = $now; $rowToInsert['name'] = $visitSummaryReturningSegmentDone; $rowToInsert['value'] = ArchiveWriter::DONE_OK; ++$newIdArchiveStart; } // add missing archives $params = array(); foreach ($missingIdArchives as $missingIdArchive) { $params[] = array_values($missingIdArchive); } $fields = array_keys(reset($missingIdArchives)); $tableUnprefixed = Common::unprefixTable($table); $migration = $this->migration->db->batchInsert($tableUnprefixed, $fields, $params, $throwException = false, $charset = 'latin1'); $updater->executeMigration(__FILE__, $migration); } // update idarchive & name columns in rows with *._returning metrics $updateSqlPrefix = "UPDATE {$table}\n SET idarchive = CASE idarchive "; $updateSqlSuffix = " END, name = CASE name "; foreach ($returningMetrics as $metric) { $newMetricName = substr($metric, 0, strlen($metric) - strlen(VisitFrequencyApi::COLUMN_SUFFIX)); $updateSqlSuffix .= "WHEN '{$metric}' THEN '" . $newMetricName . "' "; } $updateSqlSuffix .= " END WHERE idarchive IN (%s)\n AND name IN ('" . implode("','", $returningMetrics) . "')"; // update only 1000 rows at a time so we don't send too large an SQL query to MySQL foreach (array_chunk($missingIdArchives, 1000, $preserveKeys = true) as $chunk) { $idArchives = array(); $updateSql = $updateSqlPrefix; foreach ($chunk as $withMetricsIdArchive => $row) { $updateSql .= "WHEN {$withMetricsIdArchive} THEN {$row['idarchive']} "; $idArchives[] = $withMetricsIdArchive; } $updateSql .= sprintf($updateSqlSuffix, implode(',', $idArchives)); $updater->executeMigration(__FILE__, $this->migration->db->sql($updateSql)); } } }
public function setTable($table) { $table = Common::unprefixTable($table); $this->table = Common::prefixTable($table); }