Get list of tables installed
| public static getTablesInstalled ( boolean $forceReload = true ) : array | ||
| $forceReload | boolean | Invalidate cache |
| return | array | Tables installed |
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 getMigrationQueries(Updater $updater)
{
$sqlarray = array('ALTER TABLE `' . Common::prefixTable('log_visit') . '`
ADD `visit_goal_converted` VARCHAR( 1 ) NOT NULL AFTER `visit_total_time`' => 1060, 'ALTER TABLE `' . Common::prefixTable('log_visit') . '`
CHANGE `visit_goal_converted` `visit_goal_converted` TINYINT(1) NOT NULL' => 1060, 'CREATE TABLE `' . Common::prefixTable('goal') . "` (\n\t\t\t\t`idsite` int(11) NOT NULL,\n\t\t\t\t`idgoal` int(11) NOT NULL,\n\t\t\t\t`name` varchar(50) NOT NULL,\n\t\t\t\t`match_attribute` varchar(20) NOT NULL,\n\t\t\t\t`pattern` varchar(255) NOT NULL,\n\t\t\t\t`pattern_type` varchar(10) NOT NULL,\n\t\t\t\t`case_sensitive` tinyint(4) NOT NULL,\n\t\t\t\t`revenue` float NOT NULL,\n\t\t\t\t`deleted` tinyint(4) NOT NULL default '0',\n\t\t\t\tPRIMARY KEY (`idsite`,`idgoal`)\n\t\t\t)" => 1050, '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`)
)' => 1050);
$tables = DbHelper::getTablesInstalled();
foreach ($tables as $tableName) {
if (preg_match('/archive_/', $tableName) == 1) {
$sqlarray['CREATE INDEX index_all ON ' . $tableName . ' (`idsite`,`date1`,`date2`,`name`,`ts_archived`)'] = 1072;
}
}
return $sqlarray;
}
public function getMigrations(Updater $updater)
{
// Renaming old archived records now that the plugin is called Referrers
$migrations = array();
$tables = \Piwik\DbHelper::getTablesInstalled();
foreach ($tables as $tableName) {
if (strpos($tableName, 'archive_') !== false) {
$migrations[] = $this->migration->db->sql('UPDATE `' . $tableName . '` SET `name`=REPLACE(`name`, \'Referers_\', \'Referrers_\') WHERE `name` LIKE \'Referers_%\'');
}
}
$errorCodeTableNotFound = '1146';
// Rename custom segments containing Referers segments
$migrations[] = $this->migration->db->sql('UPDATE `' . Common::prefixTable('segment') . '` SET `definition`=REPLACE(`definition`, \'referer\', \'referrer\') WHERE `definition` LIKE \'%referer%\'', $errorCodeTableNotFound);
// Rename Referrers reports within scheduled reports
$query = 'UPDATE `' . Common::prefixTable('report') . '` SET `reports`=REPLACE(`reports`, \'Referer\', \'Referrer\') WHERE `reports` LIKE \'%Referer%\'';
$migrations[] = $this->migration->db->sql($query, $errorCodeTableNotFound);
// Rename Referrers widgets in custom dashboards
$query = 'UPDATE `' . Common::prefixTable('user_dashboard') . '` SET `layout`=REPLACE(`layout`, \'Referer\', \'Referrer\') WHERE `layout` LIKE \'%Referer%\'';
$migrations[] = $this->migration->db->sql($query, $errorCodeTableNotFound);
$query = 'UPDATE `' . Common::prefixTable('option') . '` SET `option_name` = \'version_ScheduledReports\' WHERE `option_name` = \'version_PDFReports\' ';
$migrations[] = $this->migration->db->sql($query, Updater\Migration\Db::ERROR_CODE_DUPLICATE_ENTRY);
// http://forum.piwik.org/read.php?2,106895
$migrations[] = $this->migration->plugin->activate('Referrers');
$migrations[] = $this->migration->plugin->activate('ScheduledReports');
return $migrations;
}
public function getMigrations(Updater $updater)
{
$migrations = array($this->migration->db->sql('ALTER TABLE `' . Common::prefixTable('user_dashboard') . '`
CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci ', Updater\Migration\Db::ERROR_CODE_TABLE_NOT_EXISTS), $this->migration->db->sql('ALTER TABLE `' . Common::prefixTable('user_language') . '`
CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci ', Updater\Migration\Db::ERROR_CODE_TABLE_NOT_EXISTS));
// alter table to set the utf8 collation
$tablesToAlter = DbHelper::getTablesInstalled(true);
foreach ($tablesToAlter as $table) {
$migrations[] = $this->migration->db->sql('ALTER TABLE `' . $table . '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci');
}
return $migrations;
}
public function setUp()
{
parent::setUp();
Option::set('version_core', self::VERSION_TO_UPDATE_FROM);
$this->oldScriptName = $_SERVER['SCRIPT_NAME'];
$_SERVER['SCRIPT_NAME'] = $_SERVER['SCRIPT_NAME'] . " console";
// update won't execute w/o this, see Common::isRunningConsoleCommand()
ArchiveTableCreator::clear();
DbHelper::getTablesInstalled($forceReload = true);
// force reload so internal cache in Mysql.php is refreshed
Updates_2_10_0_b5::$archiveBlobTables = null;
}
static function getSql()
{
$sqlarray = array('ALTER TABLE `' . Common::prefixTable('user_dashboard') . '`
CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci ' => '1146', 'ALTER TABLE `' . Common::prefixTable('user_language') . '`
CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci ' => '1146');
// alter table to set the utf8 collation
$tablesToAlter = DbHelper::getTablesInstalled(true);
foreach ($tablesToAlter as $table) {
$sqlarray['ALTER TABLE `' . $table . '`
CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci '] = false;
}
return $sqlarray;
}
static function getSql()
{
$sqlarray = array('DROP INDEX index_idsite_date ON ' . Common::prefixTable('log_visit') => '1091', 'CREATE INDEX index_idsite_date_config ON ' . Common::prefixTable('log_visit') . ' (idsite, visit_server_date, config_md5config(8))' => '1061');
$tables = DbHelper::getTablesInstalled();
foreach ($tables as $tableName) {
if (preg_match('/archive_/', $tableName) == 1) {
$sqlarray['DROP INDEX index_all ON ' . $tableName] = '1091';
}
if (preg_match('/archive_numeric_/', $tableName) == 1) {
$sqlarray['CREATE INDEX index_idsite_dates_period ON ' . $tableName . ' (idsite, date1, date2, period)'] = '1061';
}
}
return $sqlarray;
}
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 getSql()
{
// Renaming old archived records now that the plugin is called Referrers
$sql = array();
$tables = \Piwik\DbHelper::getTablesInstalled();
foreach ($tables as $tableName) {
if (strpos($tableName, 'archive_') !== false) {
$sql['UPDATE `' . $tableName . '` SET `name`=REPLACE(`name`, \'Referers_\', \'Referrers_\') WHERE `name` LIKE \'Referers_%\''] = false;
}
}
$errorCodeTableNotFound = '1146';
// Rename custom segments containing Referers segments
$sql['UPDATE `' . Common::prefixTable('segment') . '` SET `definition`=REPLACE(`definition`, \'referer\', \'referrer\') WHERE `definition` LIKE \'%referer%\''] = $errorCodeTableNotFound;
// Rename Referrers reports within scheduled reports
$sql['UPDATE `' . Common::prefixTable('report') . '` SET `reports`=REPLACE(`reports`, \'Referer\', \'Referrer\') WHERE `reports` LIKE \'%Referer%\''] = $errorCodeTableNotFound;
// Rename Referrers widgets in custom dashboards
$sql['UPDATE `' . Common::prefixTable('user_dashboard') . '` SET `layout`=REPLACE(`layout`, \'Referer\', \'Referrer\') WHERE `layout` LIKE \'%Referer%\''] = $errorCodeTableNotFound;
$sql['UPDATE `' . Common::prefixTable('option') . '` SET `option_name` = \'version_ScheduledReports\' WHERE `option_name` = \'version_PDFReports\' '] = '1062';
// http://forum.piwik.org/read.php?2,106895
return $sql;
}
/**
* Gets the result of a SHOW TABLE STATUS query for every Piwik table in the DB.
* Non-piwik tables are ignored.
*
* @param string $matchingRegex Regex used to filter out tables whose name doesn't
* match it.
* @return array The table information. See http://dev.mysql.com/doc/refman/5.5/en/show-table-status.html
* for specifics.
*/
public function getAllTablesStatus($matchingRegex = null)
{
if (is_null($this->tableStatuses)) {
$tablesPiwik = DbHelper::getTablesInstalled();
$this->tableStatuses = array();
foreach ($this->dataAccess->getAllTablesStatus() as $t) {
if (in_array($t['Name'], $tablesPiwik)) {
$this->tableStatuses[$t['Name']] = $t;
}
}
}
if (is_null($matchingRegex)) {
return $this->tableStatuses;
}
$result = array();
foreach ($this->tableStatuses as $status) {
if (preg_match($matchingRegex, $status['Name'])) {
$result[] = $status;
}
}
return $result;
}
/**
* Truncates all tables then inserts the data in $tables into each
* mapped table.
*
* @param array $tables Array mapping table names with arrays of row data.
*/
protected static function restoreDbTables($tables)
{
// truncate existing tables
DbHelper::truncateAllTables();
// insert data
$existingTables = DbHelper::getTablesInstalled();
foreach ($tables as $table => $rows) {
// create table if it's an archive table
if (strpos($table, 'archive_') !== false && !in_array($table, $existingTables)) {
$tableType = strpos($table, 'archive_numeric') !== false ? 'archive_numeric' : 'archive_blob';
$createSql = DbHelper::getTableCreateSql($tableType);
$createSql = str_replace(Common::prefixTable($tableType), $table, $createSql);
Db::query($createSql);
}
if (empty($rows)) {
continue;
}
$rowsSql = array();
$bind = array();
foreach ($rows as $row) {
$values = array();
foreach ($row as $value) {
if (is_null($value)) {
$values[] = 'NULL';
} else {
if (is_numeric($value)) {
$values[] = $value;
} else {
if (!ctype_print($value)) {
$values[] = "x'" . bin2hex(substr($value, 1)) . "'";
} else {
$values[] = "?";
$bind[] = $value;
}
}
}
}
$rowsSql[] = "(" . implode(',', $values) . ")";
}
$sql = "INSERT INTO `{$table}` VALUES " . implode(',', $rowsSql);
Db::query($sql, $bind);
}
}
/**
* Drops all tables
*/
public static function dropAllTables()
{
$tablesAlreadyInstalled = DbHelper::getTablesInstalled();
self::dropTables($tablesAlreadyInstalled);
}
/**
* Utility function that finds every archive table whose reports are considered
* old.
*
* @return array An array of two arrays. The first holds the numeric archive table
* names, and the second holds the blob archive table names.
*/
private function getArchiveTablesToPurge()
{
// get month for which reports as old or older than, should be deleted
// reports whose creation date <= this month will be deleted
// (NOTE: we ignore how far we are in the current month)
$toRemoveDate = Date::factory('today')->subMonth(1 + $this->deleteReportsOlderThan);
// find all archive tables that are older than N months
$oldNumericTables = array();
$oldBlobTables = array();
foreach (DbHelper::getTablesInstalled() as $table) {
$type = ArchiveTableCreator::getTypeFromTableName($table);
if ($type === false) {
continue;
}
$date = ArchiveTableCreator::getDateFromTableName($table);
list($year, $month) = explode('_', $date);
if (self::shouldReportBePurged($year, $month, $toRemoveDate)) {
if ($type == ArchiveTableCreator::NUMERIC_TABLE) {
$oldNumericTables[] = $table;
} else {
$oldBlobTables[] = $table;
}
}
}
return array($oldNumericTables, $oldBlobTables);
}
/**
* Installation Step 5: Table Creation
*/
function tablesCreation()
{
$this->checkPreviousStepIsValid(__FUNCTION__);
$view = new View('@Installation/tablesCreation', $this->getInstallationSteps(), __FUNCTION__);
$this->skipThisStep(__FUNCTION__);
$this->createDbFromSessionInformation();
if (Common::getRequestVar('deleteTables', 0, 'int') == 1) {
DbHelper::dropTables();
$view->existingTablesDeleted = true;
// when the user decides to drop the tables then we dont skip the next steps anymore
// workaround ZF-1743
$tmp = $this->session->skipThisStep;
$tmp['firstWebsiteSetup'] = false;
$tmp['trackingCode'] = false;
$this->session->skipThisStep = $tmp;
}
$tablesInstalled = DbHelper::getTablesInstalled();
$view->tablesInstalled = '';
if (count($tablesInstalled) > 0) {
// we have existing tables
$view->tablesInstalled = implode(', ', $tablesInstalled);
$view->someTablesInstalled = true;
// remove monthly archive tables
$archiveTables = ArchiveTableCreator::getTablesArchivesInstalled();
$baseTablesInstalled = count($tablesInstalled) - count($archiveTables);
$minimumCountPiwikTables = 17;
Access::getInstance();
Piwik::setUserIsSuperUser();
if ($baseTablesInstalled >= $minimumCountPiwikTables && count(APISitesManager::getInstance()->getAllSitesId()) > 0 && count(APIUsersManager::getInstance()->getUsers()) > 0) {
$view->showReuseExistingTables = true;
// when the user reuses the same tables we skip the website creation step
// workaround ZF-1743
$tmp = $this->session->skipThisStep;
$tmp['firstWebsiteSetup'] = true;
$tmp['trackingCode'] = true;
$this->session->skipThisStep = $tmp;
}
} else {
DbHelper::createTables();
DbHelper::createAnonymousUser();
Updater::recordComponentSuccessfullyUpdated('core', Version::VERSION);
$view->tablesCreated = true;
$view->showNextStep = true;
}
$this->session->currentStepDone = __FUNCTION__;
return $view->render();
}
/**
* inits the Piwik DB
* @return void
*/
public function installDatabase()
{
$this->initPiwikDatabase(TRUE);
$tablesInstalled = \Piwik\DbHelper::getTablesInstalled();
if (count($tablesInstalled) == 0) {
\Piwik\DbHelper::createTables();
\Piwik\DbHelper::createAnonymousUser();
$updater = new \Piwik\Updater();
//set Piwikversion
$updater->recordComponentSuccessfullyUpdated('core', \Piwik\Version::VERSION);
}
}
public static function refreshTableList($forceReload = false)
{
self::$tablesAlreadyInstalled = DbHelper::getTablesInstalled($forceReload);
}
/**
* Installation Step 4: Table Creation
*/
function tablesCreation()
{
$this->checkPiwikIsNotInstalled();
$view = new View('@Installation/tablesCreation', $this->getInstallationSteps(), __FUNCTION__);
if ($this->getParam('deleteTables')) {
Manager::getInstance()->clearPluginsInstalledConfig();
Db::dropAllTables();
$view->existingTablesDeleted = true;
}
$tablesInstalled = DbHelper::getTablesInstalled();
$view->tablesInstalled = '';
if (count($tablesInstalled) > 0) {
// we have existing tables
$view->tablesInstalled = implode(', ', $tablesInstalled);
$view->someTablesInstalled = true;
Access::getInstance();
Piwik::setUserHasSuperUserAccess();
if ($this->hasEnoughTablesToReuseDb($tablesInstalled) && count(APISitesManager::getInstance()->getAllSitesId()) > 0 && count(APIUsersManager::getInstance()->getUsers()) > 0) {
$view->showReuseExistingTables = true;
}
} else {
DbHelper::createTables();
DbHelper::createAnonymousUser();
$this->updateComponents();
Updater::recordComponentSuccessfullyUpdated('core', Version::VERSION);
$view->tablesCreated = true;
$view->showNextStep = true;
}
return $view->render();
}
function deletePiwikTables()
{
tx_piwikintegration_install::getInstaller()->getConfigObject()->initPiwikDatabase();
$tablesInstalled = \Piwik\DbHelper::getTablesInstalled();
$buffer = $GLOBALS['LANG']->getLL('action.deletePiwikTables.success');
foreach ($tablesInstalled as $table) {
$GLOBALS['TYPO3_DB']->admin_query('DROP TABLE `' . $table . '`');
$buffer .= $table . ', ';
}
return $buffer;
}
