The table prefix is determined by the [database] tables_prefix INI config
option.
| public static prefixTable ( string $table ) : string | ||
| $table | string | The table name to prefix, ie "log_visit" |
| 리턴 | string | The prefixed name, ie "piwik-production_log_visit". |
public function getMigrations(Updater $updater)
{
$migrations = array('# ATTENTION: This update script will execute some more SQL queries than that below as it is necessary to rebuilt some archives #' => false);
// update scheduled reports to use new plugin
$reportsToReplace = array('UserSettings_getBrowserVersion' => 'DevicesDetection_getBrowserVersions', 'UserSettings_getBrowser' => 'DevicesDetection_getBrowsers', 'UserSettings_getOSFamily' => 'DevicesDetection_getOsFamilies', 'UserSettings_getOS' => 'DevicesDetection_getOsVersions', 'UserSettings_getMobileVsDesktop' => 'DevicesDetection_getType', 'UserSettings_getBrowserType' => 'DevicesDetection_getBrowserEngines', 'UserSettings_getWideScreen' => 'UserSettings_getScreenType');
$reportTable = Common::prefixTable('report');
foreach ($reportsToReplace as $old => $new) {
$migrations[] = $this->migration->db->sql("UPDATE {$reportTable} SET reports = REPLACE(reports, '" . $old . "', '" . $new . "')");
}
// update dashboard to use new widgets
$oldWidgets = array(array('module' => 'UserSettings', 'action' => 'getBrowserVersion', 'params' => array()), array('module' => 'UserSettings', 'action' => 'getBrowser', 'params' => array()), array('module' => 'UserSettings', 'action' => 'getOSFamily', 'params' => array()), array('module' => 'UserSettings', 'action' => 'getOS', 'params' => array()), array('module' => 'UserSettings', 'action' => 'getMobileVsDesktop', 'params' => array()), array('module' => 'UserSettings', 'action' => 'getBrowserType', 'params' => array()), array('module' => 'UserSettings', 'action' => 'getWideScreen', 'params' => array()));
$newWidgets = array(array('module' => 'DevicesDetection', 'action' => 'getBrowserVersions', 'params' => array()), array('module' => 'DevicesDetection', 'action' => 'getBrowsers', 'params' => array()), array('module' => 'DevicesDetection', 'action' => 'getOsFamilies', 'params' => array()), array('module' => 'DevicesDetection', 'action' => 'getOsVersions', 'params' => array()), array('module' => 'DevicesDetection', 'action' => 'getType', 'params' => array()), array('module' => 'DevicesDetection', 'action' => 'getBrowserEngines', 'params' => array()), array('module' => 'UserSettings', 'action' => 'getScreenType', 'params' => array()));
$allDashboards = Db::get()->fetchAll(sprintf("SELECT * FROM %s", Common::prefixTable('user_dashboard')));
$dashboardTable = Common::prefixTable('user_dashboard');
$dashboardQuery = "UPDATE {$dashboardTable} SET layout = ? WHERE iddashboard = ?";
foreach ($allDashboards as $dashboard) {
$dashboardLayout = json_decode($dashboard['layout']);
$dashboardLayout = DashboardModel::replaceDashboardWidgets($dashboardLayout, $oldWidgets, $newWidgets);
$newLayout = json_encode($dashboardLayout);
if ($newLayout != $dashboard['layout']) {
$migrations[] = $this->migration->db->boundSql($dashboardQuery, array($newLayout, $dashboard['iddashboard']));
}
}
return $migrations;
}
static function getSql()
{
return array('ALTER TABLE `' . Common::prefixTable('log_visit') . '`
CHANGE custom_var_k1 custom_var_k1 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_v1 custom_var_v1 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_k2 custom_var_k2 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_v2 custom_var_v2 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_k3 custom_var_k3 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_v3 custom_var_v3 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_k4 custom_var_k4 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_v4 custom_var_v4 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_k5 custom_var_k5 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_v5 custom_var_v5 VARCHAR(100) DEFAULT NULL' => false, 'ALTER TABLE `' . Common::prefixTable('log_conversion') . '`
CHANGE custom_var_k1 custom_var_k1 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_v1 custom_var_v1 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_k2 custom_var_k2 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_v2 custom_var_v2 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_k3 custom_var_k3 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_v3 custom_var_v3 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_k4 custom_var_k4 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_v4 custom_var_v4 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_k5 custom_var_k5 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_v5 custom_var_v5 VARCHAR(100) DEFAULT NULL' => false, 'ALTER TABLE `' . Common::prefixTable('log_link_visit_action') . '`
CHANGE custom_var_k1 custom_var_k1 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_v1 custom_var_v1 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_k2 custom_var_k2 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_v2 custom_var_v2 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_k3 custom_var_k3 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_v3 custom_var_v3 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_k4 custom_var_k4 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_v4 custom_var_v4 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_k5 custom_var_k5 VARCHAR(100) DEFAULT NULL,
CHANGE custom_var_v5 custom_var_v5 VARCHAR(100) DEFAULT NULL' => false);
}
public function getMigrations(Updater $updater)
{
return array($this->migration->db->addColumn('goal', 'allow_multiple', 'TINYINT(4) NOT NULL', 'case_sensitive'), $this->migration->db->sql('ALTER TABLE `' . Common::prefixTable('log_conversion') . '`
ADD buster int unsigned NOT NULL AFTER revenue,
DROP PRIMARY KEY,
ADD PRIMARY KEY (idvisit, idgoal, buster)', Updater\Migration\Db::ERROR_CODE_DUPLICATE_COLUMN));
}
public function getAllByIdvisit($idvisit, $actionsLimit)
{
// Without "quoteIdentifier" postgresql will return column aliases
// in lower case
$sql = 'SELECT ' . " 'goal' AS type " . ' , g.name AS ' . $this->db->quoteIdentifier('goalName') . ' ' . ' , g.idgoal AS ' . $this->db->quoteIdentifier('goalId') . ' ' . ' , g.revenue AS revenue ' . ' , lc.idlink_va AS ' . $this->db->quoteIdentifier('goalPageId') . ' ' . ' , lc.server_time AS ' . $this->db->quoteIdentifier('serverTimePretty') . ' ' . ' , lc.url AS url ' . 'FROM ' . $this->table . ' AS lc ' . 'LEFT OUTER JOIN ' . Common::prefixTable('goal') . ' AS g ' . ' ON (g.idsite = lc.idsite AND g.idgoal = lc.idgoal) ' . ' AND g.deleted = 0 ' . 'WHERE lc.idvisit = ? AND lc.idgoal > 0 ' . 'ORDER BY ' . $this->db->quoteIdentifier('serverTimePretty') . ' ASC ' . "LIMIT {$actionsLimit} OFFSET 0";
return $this->db->fetchAll($sql, array($idvisit));
}
public static function update($idvisitor, $name = false, $email = false, $phone = false, $comments = false)
{
if ($name == false && $email == false && $phone == false && $comments == false) {
return false;
}
$buildQuery = "";
$argSet = array();
$argOnUpdate = array();
$argSet[] = @Common::hex2bin($idvisitor);
if ($name) {
$argSet[] = $name;
$argOnUpdate[] = $name;
$buildQuery .= " name = ?,";
}
if ($email) {
$argSet[] = $email;
$argOnUpdate[] = $email;
$buildQuery .= " email = ?,";
}
if ($phone) {
$argSet[] = $phone;
$argOnUpdate[] = $phone;
$buildQuery .= " phone = ?,";
}
if ($comments) {
$argSet[] = $comments;
$argOnUpdate[] = $comments;
$buildQuery .= " comments = ?,";
}
$buildQuery = trim(substr_replace($buildQuery, "", -1));
$arguments = array_merge($argSet, $argOnUpdate);
Db::query("INSERT INTO " . Common::prefixTable('chat_personnal_informations') . " SET idvisitor = ?, {$buildQuery} ON DUPLICATE KEY UPDATE {$buildQuery}", $arguments);
return true;
}
public static function setUpBeforeClass()
{
parent::setUpBeforeClass();
// note: not sure why I have to manually install plugin
\Piwik\Plugin\Manager::getInstance()->loadPlugin('CustomAlerts')->install();
$result = Fixture::updateDatabase();
if ($result === false) {
throw new \Exception("Failed to update pre-2.0 database (nothing to update).");
}
// truncate log tables so old data won't be re-archived
foreach (array('log_visit', 'log_link_visit_action', 'log_conversion', 'log_conversion_item') as $table) {
Db::query("TRUNCATE TABLE " . Common::prefixTable($table));
}
// add two visits from same visitor on dec. 29
$t = Fixture::getTracker(1, '2012-12-29 01:01:30', $defaultInit = true);
$t->setUrl('http://site.com/index.htm');
$t->setIp('136.5.3.2');
Fixture::checkResponse($t->doTrackPageView('incredible title!'));
$t->setForceVisitDateTime('2012-12-29 03:01:30');
$t->setUrl('http://site.com/other/index.htm');
$t->DEBUG_APPEND_URL = '&_idvc=2';
// make sure visit is marked as returning
Fixture::checkResponse($t->doTrackPageView('other incredible title!'));
// launch archiving
VisitFrequencyApi::getInstance()->get(1, 'year', '2012-12-29');
}
static function getSql()
{
$logVisit = Common::prefixTable('log_visit');
$logConversion = Common::prefixTable('log_conversion');
$addColumns = "DROP `location_continent`,\n\t\t\t\t\t ADD `location_region` CHAR(2) NULL AFTER `location_country`,\n\t\t\t\t\t ADD `location_city` VARCHAR(255) NULL AFTER `location_region`,\n\t\t\t\t\t ADD `location_latitude` FLOAT(10, 6) NULL AFTER `location_city`,\n\t\t\t ADD `location_longitude` FLOAT(10, 6) NULL AFTER `location_latitude`";
return array("ALTER TABLE `{$logVisit}` {$addColumns}" => 1091, "ALTER TABLE `{$logConversion}` {$addColumns}" => 1091);
}
/**
* @param $idVisit
* @param $idOrder
* @param $actionsLimit
* @return array
* @throws \Exception
*/
public function queryEcommerceItemsForOrder($idVisit, $idOrder, $actionsLimit)
{
$sql = "SELECT\n\t\t\t\t\t\t\tlog_action_sku.name as itemSKU,\n\t\t\t\t\t\t\tlog_action_name.name as itemName,\n\t\t\t\t\t\t\tlog_action_category.name as itemCategory,\n\t\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('price') . " as price,\n\t\t\t\t\t\t\tquantity as quantity\n\t\t\t\t\t\tFROM " . Common::prefixTable('log_conversion_item') . "\n\t\t\t\t\t\t\tINNER JOIN " . Common::prefixTable('log_action') . " AS log_action_sku\n\t\t\t\t\t\t\tON idaction_sku = log_action_sku.idaction\n\t\t\t\t\t\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_name\n\t\t\t\t\t\t\tON idaction_name = log_action_name.idaction\n\t\t\t\t\t\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_category\n\t\t\t\t\t\t\tON idaction_category = log_action_category.idaction\n\t\t\t\t\t\tWHERE idvisit = ?\n\t\t\t\t\t\t\tAND idorder = ?\n\t\t\t\t\t\t\tAND deleted = 0\n\t\t\t\t\t\tLIMIT 0, {$actionsLimit}\n\t\t\t\t";
$bind = array($idVisit, $idOrder);
$itemsDetails = Db::fetchAll($sql, $bind);
return $itemsDetails;
}
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;
}
/**
* Authenticates user
*
* @return AuthResult
*/
public function authenticate()
{
$rootLogin = Config::getInstance()->superuser['login'];
$rootPassword = Config::getInstance()->superuser['password'];
$rootToken = API::getInstance()->getTokenAuth($rootLogin, $rootPassword);
if (is_null($this->login)) {
if ($this->token_auth === $rootToken) {
return new AuthResult(AuthResult::SUCCESS_SUPERUSER_AUTH_CODE, $rootLogin, $this->token_auth);
}
$login = Db::fetchOne('SELECT login
FROM ' . Common::prefixTable('user') . '
WHERE token_auth = ?', array($this->token_auth));
if (!empty($login)) {
return new AuthResult(AuthResult::SUCCESS, $login, $this->token_auth);
}
} else {
if (!empty($this->login)) {
if ($this->login === $rootLogin && $this->getHashTokenAuth($rootLogin, $rootToken) === $this->token_auth || $rootToken === $this->token_auth) {
$this->setTokenAuth($rootToken);
return new AuthResult(AuthResult::SUCCESS_SUPERUSER_AUTH_CODE, $rootLogin, $this->token_auth);
}
$login = $this->login;
$userToken = Db::fetchOne('SELECT token_auth
FROM ' . Common::prefixTable('user') . '
WHERE login = ?', array($login));
if (!empty($userToken) && ($this->getHashTokenAuth($login, $userToken) === $this->token_auth || $userToken === $this->token_auth)) {
$this->setTokenAuth($userToken);
return new AuthResult(AuthResult::SUCCESS, $login, $userToken);
}
}
}
return new AuthResult(AuthResult::FAILURE, $this->login, $this->token_auth);
}
/**
* Generate the join sql based on the needed tables
* @throws Exception if tables can't be joined
* @return array
*/
public function generate()
{
/** @var LogTable[] $availableLogTables */
$availableLogTables = array();
$this->tables->sort(array($this, 'sortTablesForJoin'));
foreach ($this->tables as $i => $table) {
if (is_array($table)) {
// join condition provided
$alias = isset($table['tableAlias']) ? $table['tableAlias'] : $table['table'];
$this->joinString .= " LEFT JOIN " . Common::prefixTable($table['table']) . " AS " . $alias . " ON " . $table['joinOn'];
continue;
}
$tableSql = Common::prefixTable($table) . " AS {$table}";
$logTable = $this->tables->getLogTable($table);
if ($i == 0) {
// first table
$this->joinString .= $tableSql;
} else {
$join = $this->findJoinCriteriasForTables($logTable, $availableLogTables);
if ($join === null) {
$availableLogTables[$table] = $logTable;
continue;
}
// the join sql the default way
$this->joinString .= " LEFT JOIN {$tableSql} ON " . $join;
}
$availableLogTables[$table] = $logTable;
}
}
public function execute()
{
$isPiwikInstalling = !Config::getInstance()->existsLocalConfig();
if ($isPiwikInstalling) {
// Skip the diagnostic if Piwik is being installed
return array();
}
$label = $this->translator->translate('Installation_DatabaseAbilities');
$optionTable = Common::prefixTable('option');
$testOptionNames = array('test_system_check1', 'test_system_check2');
$loadDataInfile = false;
$errorMessage = null;
try {
$loadDataInfile = Db\BatchInsert::tableInsertBatch($optionTable, array('option_name', 'option_value'), array(array($testOptionNames[0], '1'), array($testOptionNames[1], '2')), $throwException = true);
} catch (\Exception $ex) {
$errorMessage = str_replace("\n", "<br/>", $ex->getMessage());
}
// delete the temporary rows that were created
Db::exec("DELETE FROM `{$optionTable}` WHERE option_name IN ('" . implode("','", $testOptionNames) . "')");
if ($loadDataInfile) {
return array(DiagnosticResult::singleResult($label, DiagnosticResult::STATUS_OK, 'LOAD DATA INFILE'));
}
$comment = sprintf('LOAD DATA INFILE<br/>%s<br/>%s', $this->translator->translate('Installation_LoadDataInfileUnavailableHelp', array('LOAD DATA INFILE', 'FILE')), $this->translator->translate('Installation_LoadDataInfileRecommended'));
if ($errorMessage) {
$comment .= sprintf('<br/><strong>%s:</strong> %s<br/>%s', $this->translator->translate('General_Error'), $errorMessage, 'Troubleshooting: <a target="_blank" href="?module=Proxy&action=redirect&url=http://piwik.org/faq/troubleshooting/%23faq_194">FAQ on piwik.org</a>');
}
return array(DiagnosticResult::singleResult($label, DiagnosticResult::STATUS_WARNING, $comment));
}
public function install()
{
$queries[] = 'CREATE TABLE `' . Common::prefixTable('segment') . '` (
`idsegment` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`definition` TEXT NOT NULL,
`login` VARCHAR(100) NOT NULL,
`enable_all_users` tinyint(4) NOT NULL default 0,
`enable_only_idsite` INTEGER(11) NULL,
`auto_archive` tinyint(4) NOT NULL default 0,
`ts_created` TIMESTAMP NULL,
`ts_last_edit` TIMESTAMP NULL,
`deleted` tinyint(4) NOT NULL default 0,
PRIMARY KEY (`idsegment`)
) DEFAULT CHARSET=utf8';
try {
foreach ($queries as $query) {
Db::exec($query);
}
} catch (Exception $e) {
if (!Db::get()->isErrNo($e, '1050')) {
throw $e;
}
}
}
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;
}
private function assertDuplicatesFixedInLogConversionItemTable()
{
$columns = array('idaction_sku', 'idaction_name', 'idaction_category', 'idaction_category2', 'idaction_category3', 'idaction_category4', 'idaction_category5');
$rows = Db::fetchAll("SELECT " . implode(',', $columns) . " FROM " . Common::prefixTable('log_conversion_item'));
$expectedRows = array(array('idaction_sku' => '1', 'idaction_name' => '1', 'idaction_category' => '1', 'idaction_category2' => '4', 'idaction_category3' => '5', 'idaction_category4' => '6', 'idaction_category5' => '5'), array('idaction_sku' => '1', 'idaction_name' => '1', 'idaction_category' => '5', 'idaction_category2' => '5', 'idaction_category3' => '8', 'idaction_category4' => '4', 'idaction_category5' => '6'));
$this->assertEquals($expectedRows, $rows);
}
public static function getUsersBySite($idsite)
{
$getRegularUsers = Db::fetchAll("SELECT login,\n (SELECT email FROM " . Common::prefixTable('user') . " WHERE login = acc.login) AS email\n FROM " . Common::prefixTable('access') . " AS acc WHERE idsite = ?", array($idsite));
$getSuperUsers = Db::fetchAll("SELECT login,email FROM " . Common::prefixTable('user') . " WHERE superuser_access = 1");
$getUsers = array_merge($getRegularUsers, $getSuperUsers);
return $getUsers;
}
/**
* Return SQL to be executed in this update.
*
* SQL queries should be defined here, instead of in `doUpdate()`, since this method is used
* in the `core:update` command when displaying the queries an update will run. If you execute
* queries directly in `doUpdate()`, they won't be displayed to the user.
*
* @param Updater $updater
* @return array ```
* array(
* 'ALTER .... ' => '1234', // if the query fails, it will be ignored if the error code is 1234
* 'ALTER .... ' => false, // if an error occurs, the update will stop and fail
* // and user will have to manually run the query
* )
* ```
*/
public function getMigrationQueries(Updater $updater)
{
$errorCodesToIgnore = array(1060);
$tableName = Common::prefixTable('log_visit');
$updateSql = "ALTER TABLE `" . $tableName . "` CHANGE `example` `example` BOOLEAN NOT NULL";
return array();
}
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;
}
private static function migrateConfigSuperUserToDb()
{
$config = Config::getInstance();
if (!$config->existsLocalConfig()) {
return;
}
try {
$superUser = $config->superuser;
} catch (\Exception $e) {
$superUser = null;
}
if (!empty($superUser['bridge']) || empty($superUser) || empty($superUser['login'])) {
// there is a super user which is not from the config but from the bridge, that means we already have
// a super user in the database
return;
}
$userApi = UsersManagerApi::getInstance();
try {
Db::get()->insert(Common::prefixTable('user'), array('login' => $superUser['login'], 'password' => $superUser['password'], 'alias' => $superUser['login'], 'email' => $superUser['email'], 'token_auth' => $userApi->getTokenAuth($superUser['login'], $superUser['password']), 'date_registered' => Date::now()->getDatetime(), 'superuser_access' => 1));
} catch (\Exception $e) {
echo "There was an issue, but we proceed: " . $e->getMessage();
}
if (array_key_exists('salt', $superUser)) {
$salt = $superUser['salt'];
} else {
$salt = Common::generateUniqId();
}
$config->General['salt'] = $salt;
$config->superuser = array();
$config->forceSave();
}
public function getNumWebsites()
{
if (!isset($this->websitesCache)) {
$this->websitesCache = (int) Db::get()->fetchOne('SELECT count(idsite) FROM ' . Common::prefixTable('site'));
}
return $this->websitesCache;
}
/**
* @param $matchType
* @param $actionType
* @return string
* @throws \Exception
*/
private static function getSelectQueryWhereNameContains($matchType, $actionType)
{
// now, we handle the cases =@ (contains) and !@ (does not contain)
// build the expression based on the match type
$sql = 'SELECT idaction FROM ' . Common::prefixTable('log_action') . ' WHERE %s AND type = ' . $actionType . ' )';
switch ($matchType) {
case SegmentExpression::MATCH_CONTAINS:
// use concat to make sure, no %s occurs because some plugins use %s in their sql
$where = '( name LIKE CONCAT(\'%\', ?, \'%\') ';
break;
case SegmentExpression::MATCH_DOES_NOT_CONTAIN:
$where = '( name NOT LIKE CONCAT(\'%\', ?, \'%\') ';
break;
case SegmentExpression::MATCH_STARTS_WITH:
// use concat to make sure, no %s occurs because some plugins use %s in their sql
$where = '( name LIKE CONCAT(?, \'%\') ';
break;
case SegmentExpression::MATCH_ENDS_WITH:
// use concat to make sure, no %s occurs because some plugins use %s in their sql
$where = '( name LIKE CONCAT(\'%\', ?) ';
break;
default:
throw new \Exception("This match type {$matchType} is not available for action-segments.");
break;
}
$sql = sprintf($sql, $where);
return $sql;
}
/**
* Analyzes numeric & blob tables for a single table date (ie, `'2015_01'`) and returns
* statistics including:
*
* - number of archives present
* - number of invalidated archives
* - number of temporary archives
* - number of error archives
* - number of segment archives
* - number of numeric rows
* - number of blob rows
*
* @param string $tableDate ie `'2015_01'`
* @return array
*/
public function getArchiveTableAnalysis($tableDate)
{
$numericQueryEmptyRow = array('count_archives' => '-', 'count_invalidated_archives' => '-', 'count_temporary_archives' => '-', 'count_error_archives' => '-', 'count_segment_archives' => '-', 'count_numeric_rows' => '-');
$tableDate = str_replace("`", "", $tableDate);
// for sanity
$numericTable = Common::prefixTable("archive_numeric_{$tableDate}");
$blobTable = Common::prefixTable("archive_blob_{$tableDate}");
// query numeric table
$sql = "SELECT CONCAT_WS('.', idsite, date1, date2, period) AS label,\n SUM(CASE WHEN name LIKE 'done%' THEN 1 ELSE 0 END) AS count_archives,\n SUM(CASE WHEN name LIKE 'done%' AND value = ? THEN 1 ELSE 0 END) AS count_invalidated_archives,\n SUM(CASE WHEN name LIKE 'done%' AND value = ? THEN 1 ELSE 0 END) AS count_temporary_archives,\n SUM(CASE WHEN name LIKE 'done%' AND value = ? THEN 1 ELSE 0 END) AS count_error_archives,\n SUM(CASE WHEN name LIKE 'done%' AND CHAR_LENGTH(name) > 32 THEN 1 ELSE 0 END) AS count_segment_archives,\n SUM(CASE WHEN name NOT LIKE 'done%' THEN 1 ELSE 0 END) AS count_numeric_rows,\n 0 AS count_blob_rows\n FROM `{$numericTable}`\n GROUP BY idsite, date1, date2, period";
$rows = Db::fetchAll($sql, array(ArchiveWriter::DONE_INVALIDATED, ArchiveWriter::DONE_OK_TEMPORARY, ArchiveWriter::DONE_ERROR));
// index result
$result = array();
foreach ($rows as $row) {
$result[$row['label']] = $row;
}
// query blob table & manually merge results (no FULL OUTER JOIN in mysql)
$sql = "SELECT CONCAT_WS('.', idsite, date1, date2, period) AS label,\n COUNT(*) AS count_blob_rows\n FROM `{$blobTable}`\n GROUP BY idsite, date1, date1, period";
foreach (Db::fetchAll($sql) as $blobStatsRow) {
$label = $blobStatsRow['label'];
if (isset($result[$label])) {
$result[$label] = array_merge($result[$label], $blobStatsRow);
} else {
$result[$label] = $blobStatsRow + $numericQueryEmptyRow;
}
}
return $result;
}
public function test_makeLogVisitsQueryString_whenSegment()
{
$model = new Model();
list($sql, $bind) = $model->makeLogVisitsQueryString($idSite = 1, $period = 'month', $date = '2010-01-01', $segment = 'customVariablePageName1==Test', $offset = 0, $limit = 100, $visitorId = 'abc', $minTimestamp = false, $filterSortOrder = false);
$expectedSql = ' SELECT sub.* FROM
(
SELECT log_inner.*
FROM (
SELECT log_visit.*
FROM ' . Common::prefixTable('log_visit') . ' AS log_visit
LEFT JOIN ' . Common::prefixTable('log_link_visit_action') . ' AS log_link_visit_action
ON log_link_visit_action.idvisit = log_visit.idvisit
WHERE ( log_visit.idsite in (?)
AND log_visit.idvisitor = ?
AND log_visit.visit_last_action_time >= ?
AND log_visit.visit_last_action_time <= ? )
AND ( log_link_visit_action.custom_var_k1 = ? )
ORDER BY idsite, visit_last_action_time DESC
LIMIT 100
) AS log_inner
ORDER BY idsite, visit_last_action_time DESC
LIMIT 100
) AS sub
GROUP BY sub.idvisit
ORDER BY sub.visit_last_action_time DESC
';
$expectedBind = array('1', Common::hex2bin('abc'), '2010-01-01 00:00:00', '2010-02-01 00:00:00', 'Test');
$this->assertEquals(SegmentTest::removeExtraWhiteSpaces($expectedSql), SegmentTest::removeExtraWhiteSpaces($sql));
$this->assertEquals(SegmentTest::removeExtraWhiteSpaces($expectedBind), SegmentTest::removeExtraWhiteSpaces($bind));
}
static function getSql()
{
return array('CREATE TABLE `' . Common::prefixTable('log_conversion_item') . '` (
idsite int(10) UNSIGNED NOT NULL,
idvisitor BINARY(8) NOT NULL,
server_time DATETIME NOT NULL,
idvisit INTEGER(10) UNSIGNED NOT NULL,
idorder varchar(100) NOT NULL,
idaction_sku INTEGER(10) UNSIGNED NOT NULL,
idaction_name INTEGER(10) UNSIGNED NOT NULL,
idaction_category INTEGER(10) UNSIGNED NOT NULL,
price FLOAT NOT NULL,
quantity INTEGER(10) UNSIGNED NOT NULL,
deleted TINYINT(1) UNSIGNED NOT NULL,
PRIMARY KEY(idvisit, idorder, idaction_sku),
INDEX index_idsite_servertime ( idsite, server_time )
) DEFAULT CHARSET=utf8 ' => false, 'ALTER IGNORE TABLE `' . Common::prefixTable('log_visit') . '`
ADD visitor_days_since_order SMALLINT(5) UNSIGNED NOT NULL AFTER visitor_days_since_last,
ADD visit_goal_buyer TINYINT(1) NOT NULL AFTER visit_goal_converted' => false, 'ALTER IGNORE TABLE `' . Common::prefixTable('log_conversion') . '`
ADD visitor_days_since_order SMALLINT(5) UNSIGNED NOT NULL AFTER visitor_days_since_first,
ADD idorder varchar(100) default NULL AFTER buster,
ADD items SMALLINT UNSIGNED DEFAULT NULL,
ADD revenue_subtotal float default NULL,
ADD revenue_tax float default NULL,
ADD revenue_shipping float default NULL,
ADD revenue_discount float default NULL,
ADD UNIQUE KEY unique_idsite_idorder (idsite, idorder),
MODIFY idgoal int(10) NOT NULL' => false);
}
public function getMigrationQueries(Updater $updater)
{
return array('UPDATE `' . Common::prefixTable('log_visit') . '`
SET location_ip=location_ip+CAST(POW(2,32) AS UNSIGNED) WHERE location_ip < 0' => false, 'ALTER TABLE `' . Common::prefixTable('log_visit') . '`
CHANGE `location_ip` `location_ip` BIGINT UNSIGNED NOT NULL' => 1054, 'UPDATE `' . Common::prefixTable('logger_api_call') . '`
SET caller_ip=caller_ip+CAST(POW(2,32) AS UNSIGNED) WHERE caller_ip < 0' => 1146, 'ALTER TABLE `' . Common::prefixTable('logger_api_call') . '`
CHANGE `caller_ip` `caller_ip` BIGINT UNSIGNED' => 1146);
}
static function getSql()
{
return array('ALTER TABLE `' . Common::prefixTable('access') . '`
CHANGE `login` `login` VARCHAR( 100 ) NOT NULL' => false, 'ALTER TABLE `' . Common::prefixTable('user') . '`
CHANGE `login` `login` VARCHAR( 100 ) NOT NULL' => false, 'ALTER TABLE `' . Common::prefixTable('user_dashboard') . '`
CHANGE `login` `login` VARCHAR( 100 ) NOT NULL' => '1146', 'ALTER TABLE `' . Common::prefixTable('user_language') . '`
CHANGE `login` `login` VARCHAR( 100 ) NOT NULL' => '1146');
}
public function test_getSitesAndDatesOfRowsUsingDuplicates_ReturnsTheServerTimeAndIdSite_OfRowsUsingSpecifiedActionIds()
{
$row = array('idsite' => 3, 'idvisitor' => pack("H*", DuplicateActions::DUMMY_IDVISITOR), 'server_time' => '2012-02-13 00:00:00', 'idvisit' => 5, 'idorder' => 6, 'price' => 15, 'quantity' => 21, 'deleted' => 1, 'idaction_sku' => 3, 'idaction_name' => 3, 'idaction_category' => 12, 'idaction_category2' => 3, 'idaction_category3' => 3, 'idaction_category4' => 3, 'idaction_category5' => 3);
Db::query("INSERT INTO " . Common::prefixTable('log_conversion_item') . " (" . implode(", ", array_keys($row)) . ") VALUES ('" . implode("', '", array_values($row)) . "')");
$expectedResult = array(array('idsite' => 1, 'server_time' => '2012-02-01'), array('idsite' => 3, 'server_time' => '2012-02-13'));
$actualResult = $this->duplicateActionRemover->getSitesAndDatesOfRowsUsingDuplicates('log_conversion_item', array(4, 6, 12));
$this->assertEquals($expectedResult, $actualResult);
}
public function getMigrationQueries(Updater $updater)
{
$logVisit = Common::prefixTable('log_visit');
$logConversion = Common::prefixTable('log_conversion');
$addColumns = "ADD `location_region` CHAR(2) NULL AFTER `location_country`,\n\t\t\t\t\t ADD `location_city` VARCHAR(255) NULL AFTER `location_region`,\n\t\t\t\t\t ADD `location_latitude` FLOAT(10, 6) NULL AFTER `location_city`,\n\t\t\t ADD `location_longitude` FLOAT(10, 6) NULL AFTER `location_latitude`";
$dropColumns = "DROP `location_continent`";
return array("ALTER TABLE `{$logVisit}` {$dropColumns}" => 1091, "ALTER TABLE `{$logConversion}` {$dropColumns}" => 1091, "ALTER TABLE `{$logVisit}` {$addColumns}" => 1060, "ALTER TABLE `{$logConversion}` {$addColumns}" => 1060);
}
static function getSql()
{
return array('ALTER TABLE `' . Common::prefixTable('goal') . '`
ADD `allow_multiple` tinyint(4) NOT NULL AFTER case_sensitive' => 1060, 'ALTER TABLE `' . Common::prefixTable('log_conversion') . '`
ADD buster int unsigned NOT NULL AFTER revenue,
DROP PRIMARY KEY,
ADD PRIMARY KEY (idvisit, idgoal, buster)' => 1060);
}
static function getSql()
{
return array('ALTER DATABASE `' . Config::getInstance()->database['dbname'] . '` DEFAULT CHARACTER SET utf8' => false, 'ALTER TABLE `' . Common::prefixTable('log_visit') . '`
DROP INDEX index_idsite_datetime_config,
DROP INDEX index_idsite_idvisit,
ADD INDEX index_idsite_config_datetime (idsite, config_id, visit_last_action_time),
ADD INDEX index_idsite_datetime (idsite, visit_last_action_time)' => false);
}
