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". |
| 리턴 | 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);
}
