Welcome to mirror list, hosted at ThFree Co, Russian Federation.

github.com/matomo-org/matomo.git - Unnamed repository; edit this file 'description' to name the repository.
summaryrefslogtreecommitdiff
path: root/core
diff options
context:
space:
mode:
authorThomas Steur <thomas.steur@googlemail.com>2014-10-04 13:28:23 +0400
committerThomas Steur <thomas.steur@googlemail.com>2014-10-04 13:28:23 +0400
commitf05a25d60acb670d4292c67a10d0b408643ed3df (patch)
tree19438be0d2c12a71e80c8fe752e77be1f792802d /core
parent1a7dbc5ff6ab987338fa644f1c4c7a07602c10f6 (diff)
moved some queries within core to model files
This is only a start. Especially the Tracker model contains quite a bunch of different models. Ideally, one day, the tracker is refactored and all the goal tracker stuff including the queries are in the goal plugins, all the ecommerce stuff is in an ecommerce plugin etc. Haven't moved some of the LogAggregator queries.
Diffstat (limited to 'core')
-rw-r--r--core/Config.php1
-rw-r--r--core/CronArchive.php13
-rw-r--r--core/DataAccess/ArchivePurger.php66
-rw-r--r--core/DataAccess/ArchiveSelector.php106
-rw-r--r--core/DataAccess/ArchiveTableCreator.php33
-rw-r--r--core/DataAccess/ArchiveWriter.php117
-rw-r--r--core/DataAccess/LogAggregator.php66
-rw-r--r--core/DataAccess/Model.php229
-rw-r--r--core/Db.php29
-rw-r--r--core/Db/Adapter.php2
-rw-r--r--core/Db/BatchInsert.php19
-rw-r--r--core/Db/Schema.php7
-rw-r--r--core/Db/Schema/Mysql.php437
-rw-r--r--core/Segment.php3
-rw-r--r--core/SegmentExpression.php17
-rw-r--r--core/Tracker/Action.php12
-rw-r--r--core/Tracker/GoalManager.php97
-rw-r--r--core/Tracker/Model.php383
-rw-r--r--core/Tracker/TableLogAction.php63
-rw-r--r--core/Tracker/Visit.php78
-rw-r--r--core/Tracker/Visitor.php141
21 files changed, 1171 insertions, 748 deletions
diff --git a/core/Config.php b/core/Config.php
index 572291b259..749c585647 100644
--- a/core/Config.php
+++ b/core/Config.php
@@ -459,6 +459,7 @@ class Config extends Singleton
$user['bridge'] = 1;
return $user;
}
+
} catch (Exception $e) {}
return array();
diff --git a/core/CronArchive.php b/core/CronArchive.php
index bbb4a7a6e4..47fbd7d3df 100644
--- a/core/CronArchive.php
+++ b/core/CronArchive.php
@@ -966,11 +966,14 @@ class CronArchive
private function initTokenAuth()
{
- $superUser = Db::get()->fetchRow("SELECT login, token_auth
- FROM " . Common::prefixTable("user") . "
- WHERE superuser_access = 1
- ORDER BY date_registered ASC");
- $this->token_auth = $superUser['token_auth'];
+ $token = '';
+
+ /**
+ * @ignore
+ */
+ Piwik::postEvent('CronArchive.getTokenAuth', array(&$token));
+
+ $this->token_auth = $token;
}
private function initPiwikHost($piwikUrl = false)
diff --git a/core/DataAccess/ArchivePurger.php b/core/DataAccess/ArchivePurger.php
index 2c1379115d..d795ee6fba 100644
--- a/core/DataAccess/ArchivePurger.php
+++ b/core/DataAccess/ArchivePurger.php
@@ -31,16 +31,7 @@ class ArchivePurger
* Select the archives that have already been invalidated and have been since re-processed.
* It purges records for each distinct { archive name (includes segment hash) , idsite, date, period } tuple.
*/
- $query = '
- SELECT t1.idarchive FROM `' . $archiveTable . '` t1
- INNER JOIN `' . $archiveTable . '` t2
- ON t1.name = t2.name AND t1.idsite=t2.idsite
- AND t1.date1=t2.date1 AND t1.date2=t2.date2 AND t1.period=t2.period
- WHERE t1.value = ' . ArchiveWriter::DONE_INVALIDATED . '
- AND t2.value IN(' . ArchiveWriter::DONE_OK . ', ' . ArchiveWriter::DONE_OK_TEMPORARY . ')
- AND t1.ts_archived < t2.ts_archived AND t1.name LIKE \'done%\'';
-
- $result = Db::fetchAll($query);
+ $result = self::getModel()->purgeInvalidatedArchiveTable($archiveTable);
if (count($result) > 0) {
$archiveIds = array_map(
@@ -59,35 +50,39 @@ class ArchivePurger
}
}
+ private static function getModel()
+ {
+ return new Model();
+ }
public static function purgeOutdatedArchives(Date $dateStart)
{
$purgeArchivesOlderThan = Rules::shouldPurgeOutdatedArchives($dateStart);
+
if (!$purgeArchivesOlderThan) {
return;
}
$idArchivesToDelete = self::getTemporaryArchiveIdsOlderThan($dateStart, $purgeArchivesOlderThan);
+
if (!empty($idArchivesToDelete)) {
self::deleteArchiveIds($dateStart, $idArchivesToDelete);
}
+
self::deleteArchivesWithPeriodRange($dateStart);
Log::debug("Purging temporary archives: done [ purged archives older than %s in %s ] [Deleted IDs: %s]",
- $purgeArchivesOlderThan,
- $dateStart->toString("Y-m"),
- implode(',', $idArchivesToDelete));
+ $purgeArchivesOlderThan,
+ $dateStart->toString("Y-m"),
+ implode(',', $idArchivesToDelete));
}
protected static function getTemporaryArchiveIdsOlderThan(Date $date, $purgeArchivesOlderThan)
{
- $query = "SELECT idarchive FROM " . ArchiveTableCreator::getNumericTable($date) . "
- WHERE name LIKE 'done%'
- AND (( value = " . ArchiveWriter::DONE_OK_TEMPORARY . "
- AND ts_archived < ?)
- OR value = " . ArchiveWriter::DONE_ERROR . ")";
+ $archiveTable = ArchiveTableCreator::getNumericTable($date);
+
+ $result = self::getModel()->getTemporaryArchivesOlderThan($archiveTable, $purgeArchivesOlderThan);
- $result = Db::fetchAll($query, array($purgeArchivesOlderThan));
$idArchivesToDelete = array();
if (!empty($result)) {
foreach ($result as $row) {
@@ -103,36 +98,25 @@ class ArchivePurger
*/
protected static function deleteArchivesWithPeriodRange(Date $date)
{
- $query = "DELETE FROM %s WHERE period = ? AND ts_archived < ?";
-
- $yesterday = Date::factory('yesterday')->getDateTime();
- $bind = array(Piwik::$idPeriods['range'], $yesterday);
$numericTable = ArchiveTableCreator::getNumericTable($date);
- Db::query(sprintf($query, $numericTable), $bind);
+ $blobTable = ArchiveTableCreator::getBlobTable($date);
+ $yesterday = Date::factory('yesterday')->getDateTime();
+
Log::debug("Purging Custom Range archives: done [ purged archives older than %s from %s / blob ]",
- $yesterday,
- $numericTable);
- try {
- Db::query(sprintf($query, ArchiveTableCreator::getBlobTable($date)), $bind);
- } catch (Exception $e) {
- // Individual blob tables could be missing
- }
+ $yesterday, $numericTable);
+
+ self::getModel()->deleteArchivesWithPeriodRange($numericTable, $blobTable, Piwik::$idPeriods['range'], $yesterday);
}
protected static function deleteArchiveIds(Date $date, $idArchivesToDelete)
{
- $batches = array_chunk($idArchivesToDelete, 1000);
- foreach ($batches as $idsToDelete) {
- $query = "DELETE FROM %s WHERE idarchive IN (" . implode(',', $idsToDelete) . ")";
+ $batches = array_chunk($idArchivesToDelete, 1000);
+ $numericTable = ArchiveTableCreator::getNumericTable($date);
+ $blobTable = ArchiveTableCreator::getBlobTable($date);
- Db::query(sprintf($query, ArchiveTableCreator::getNumericTable($date)));
- try {
- Db::query(sprintf($query, ArchiveTableCreator::getBlobTable($date)));
- } catch (Exception $e) {
- // Individual blob tables could be missing
- }
+ foreach ($batches as $idsToDelete) {
+ self::getModel()->deleteArchiveIds($numericTable, $blobTable, $idsToDelete);
}
-
}
}
diff --git a/core/DataAccess/ArchiveSelector.php b/core/DataAccess/ArchiveSelector.php
index 594889d6dd..a342257eca 100644
--- a/core/DataAccess/ArchiveSelector.php
+++ b/core/DataAccess/ArchiveSelector.php
@@ -38,40 +38,36 @@ class ArchiveSelector
const NB_VISITS_CONVERTED_RECORD_LOOKED_UP = "nb_visits_converted";
+ private static function getModel()
+ {
+ return new Model();
+ }
+
public static function getArchiveIdAndVisits(ArchiveProcessor\Parameters $params, $minDatetimeArchiveProcessedUTC)
{
- $dateStart = $params->getPeriod()->getDateStart();
- $bindSQL = array($params->getSite()->getId(),
- $dateStart->toString('Y-m-d'),
- $params->getPeriod()->getDateEnd()->toString('Y-m-d'),
- $params->getPeriod()->getId(),
- );
-
- $timeStampWhere = '';
+ $idSite = $params->getSite()->getId();
+ $period = $params->getPeriod()->getId();
+ $dateStart = $params->getPeriod()->getDateStart();
+ $dateStartIso = $dateStart->toString('Y-m-d');
+ $dateEndIso = $params->getPeriod()->getDateEnd()->toString('Y-m-d');
+
+ $numericTable = ArchiveTableCreator::getNumericTable($dateStart);
+
+ $minDatetimeIsoArchiveProcessedUTC = null;
if ($minDatetimeArchiveProcessedUTC) {
- $timeStampWhere = " AND ts_archived >= ? ";
- $bindSQL[] = Date::factory($minDatetimeArchiveProcessedUTC)->getDatetime();
+ $minDatetimeIsoArchiveProcessedUTC = Date::factory($minDatetimeArchiveProcessedUTC)->getDatetime();
}
$requestedPlugin = $params->getRequestedPlugin();
- $segment = $params->getSegment();
+ $segment = $params->getSegment();
$isSkipAggregationOfSubTables = $params->isSkipAggregationOfSubTables();
-
$plugins = array("VisitsSummary", $requestedPlugin);
- $sqlWhereArchiveName = self::getNameCondition($plugins, $segment, $isSkipAggregationOfSubTables);
-
- $sqlQuery = " SELECT idarchive, value, name, date1 as startDate
- FROM " . ArchiveTableCreator::getNumericTable($dateStart) . "``
- WHERE idsite = ?
- AND date1 = ?
- AND date2 = ?
- AND period = ?
- AND ( ($sqlWhereArchiveName)
- OR name = '" . self::NB_VISITS_RECORD_LOOKED_UP . "'
- OR name = '" . self::NB_VISITS_CONVERTED_RECORD_LOOKED_UP . "')
- $timeStampWhere
- ORDER BY idarchive DESC";
- $results = Db::fetchAll($sqlQuery, $bindSQL);
+
+ $doneFlags = self::getDoneFlags($plugins, $segment, $isSkipAggregationOfSubTables);
+ $possibleValues = self::getPossibleValues();
+
+ $results = self::getModel()->getArchiveIdAndVisits($numericTable, $idSite, $period, $dateStartIso, $dateEndIso, $minDatetimeIsoArchiveProcessedUTC, $doneFlags, $possibleValues);
+
if (empty($results)) {
return false;
}
@@ -81,9 +77,8 @@ class ArchiveSelector
list($visits, $visitsConverted) = self::getVisitsMetricsFromResults($idArchive, $idArchiveVisitsSummary, $results);
- if ($visits === false
- && $idArchive === false
- ) {
+ if (false === $visits && false === $idArchive) {
+
return false;
}
@@ -94,9 +89,11 @@ class ArchiveSelector
{
$visits = $visitsConverted = false;
$archiveWithVisitsMetricsWasFound = ($idArchiveVisitsSummary !== false);
+
if ($archiveWithVisitsMetricsWasFound) {
$visits = $visitsConverted = 0;
}
+
foreach ($results as $result) {
if (in_array($result['idarchive'], array($idArchive, $idArchiveVisitsSummary))) {
$value = (int)$result['value'];
@@ -112,6 +109,7 @@ class ArchiveSelector
}
}
}
+
return array($visits, $visitsConverted);
}
@@ -119,6 +117,7 @@ class ArchiveSelector
{
$idArchive = false;
$namesRequestedPlugin = Rules::getDoneFlags(array($requestedPlugin), $segment, $isSkipAggregationOfSubTables);
+
foreach ($results as $result) {
if ($idArchive === false
&& in_array($result['name'], $namesRequestedPlugin)
@@ -127,6 +126,7 @@ class ArchiveSelector
break;
}
}
+
return $idArchive;
}
@@ -198,8 +198,10 @@ class ArchiveSelector
$sql = sprintf($getArchiveIdsSql, $table, $dateCondition);
+ $archiveIds = Db::fetchAll($sql, $bind);
+
// get the archive IDs
- foreach (Db::fetchAll($sql, $bind) as $row) {
+ foreach ($archiveIds as $row) {
$archiveName = $row['name'];
//FIXMEA duplicate with Archive.php
@@ -249,18 +251,23 @@ class ArchiveSelector
// get data from every table we're querying
$rows = array();
foreach ($archiveIds as $period => $ids) {
+
if (empty($ids)) {
throw new Exception("Unexpected: id archive not found for period '$period' '");
}
+
// $period = "2009-01-04,2009-01-04",
$date = Date::factory(substr($period, 0, 10));
+
if ($archiveDataType == 'numeric') {
$table = ArchiveTableCreator::getNumericTable($date);
} else {
$table = ArchiveTableCreator::getBlobTable($date);
}
- $sql = sprintf($getValuesSql, $table, implode(',', $ids));
+
+ $sql = sprintf($getValuesSql, $table, implode(',', $ids));
$dataRows = Db::fetchAll($sql, $bind);
+
foreach ($dataRows as $row) {
$rows[] = $row;
}
@@ -282,10 +289,41 @@ class ArchiveSelector
{
// the flags used to tell how the archiving process for a specific archive was completed,
// if it was completed
+ $doneFlags = self::getDoneFlags($plugins, $segment, $isSkipAggregationOfSubTables);
+ $allDoneFlags = "'" . implode("','", $doneFlags) . "'";
+
+ $possibleValues = self::getPossibleValues();
+
+ // create the SQL to find archives that are DONE
+ return "((name IN ($allDoneFlags)) AND (value IN (" . implode(',', $possibleValues) . ")))";
+ }
+
+ /**
+ * Returns the SQL condition used to find successfully completed archives that
+ * this instance is querying for.
+ *
+ * @param array $plugins
+ * @param Segment $segment
+ * @param bool $isSkipAggregationOfSubTables
+ * @return string
+ */
+ private static function getDoneFlags(array $plugins, Segment $segment, $isSkipAggregationOfSubTables)
+ {
+ // the flags used to tell how the archiving process for a specific archive was completed,
+ // if it was completed
$doneFlags = Rules::getDoneFlags($plugins, $segment, $isSkipAggregationOfSubTables);
- $allDoneFlags = "'" . implode("','", $doneFlags) . "'";
+ return $doneFlags;
+ }
+ /**
+ * Returns the SQL condition used to find successfully completed archives that
+ * this instance is querying for.
+ *
+ * @return string
+ */
+ private static function getPossibleValues()
+ {
$possibleValues = array(ArchiveWriter::DONE_OK, ArchiveWriter::DONE_OK_TEMPORARY);
if (!Rules::isRequestAuthorizedToArchive()) {
@@ -293,9 +331,7 @@ class ArchiveSelector
$possibleValues[] = ArchiveWriter::DONE_INVALIDATED;
}
- // create the SQL to find archives that are DONE
- return "((name IN ($allDoneFlags)) AND " .
- " (value IN (" . implode(',', $possibleValues) . ")))";
+ return $possibleValues;
}
}
diff --git a/core/DataAccess/ArchiveTableCreator.php b/core/DataAccess/ArchiveTableCreator.php
index 67abd2b123..230c7da05a 100644
--- a/core/DataAccess/ArchiveTableCreator.php
+++ b/core/DataAccess/ArchiveTableCreator.php
@@ -9,7 +9,6 @@
namespace Piwik\DataAccess;
-use Exception;
use Piwik\Common;
use Piwik\Date;
use Piwik\Db;
@@ -18,8 +17,7 @@ use Piwik\DbHelper;
class ArchiveTableCreator
{
const NUMERIC_TABLE = "numeric";
-
- const BLOB_TABLE = "blob";
+ const BLOB_TABLE = "blob";
public static $tablesAlreadyInstalled = null;
@@ -38,7 +36,9 @@ class ArchiveTableCreator
$tableNamePrefix = "archive_" . $type;
$tableName = $tableNamePrefix . "_" . $date->toString('Y_m');
$tableName = Common::prefixTable($tableName);
+
self::createArchiveTablesIfAbsent($tableName, $tableNamePrefix);
+
return $tableName;
}
@@ -49,24 +49,16 @@ class ArchiveTableCreator
}
if (!in_array($tableName, self::$tablesAlreadyInstalled)) {
- $db = Db::get();
- $sql = DbHelper::getTableCreateSql($tableNamePrefix);
-
- // replace table name template by real name
- $tableNamePrefix = Common::prefixTable($tableNamePrefix);
- $sql = str_replace($tableNamePrefix, $tableName, $sql);
- try {
- $db->query($sql);
- } catch (Exception $e) {
- // accept mysql error 1050: table already exists, throw otherwise
- if (!$db->isErrNo($e, '1050')) {
- throw $e;
- }
- }
+ self::getModel()->createArchiveTable($tableName, $tableNamePrefix);
self::$tablesAlreadyInstalled[] = $tableName;
}
}
+ private static function getModel()
+ {
+ return new Model();
+ }
+
public static function clear()
{
self::$tablesAlreadyInstalled = null;
@@ -89,6 +81,7 @@ class ArchiveTableCreator
}
$archiveTables = array();
+
foreach (self::$tablesAlreadyInstalled as $table) {
if (strpos($table, 'archive_numeric_') !== false
|| strpos($table, 'archive_blob_') !== false
@@ -96,13 +89,15 @@ class ArchiveTableCreator
$archiveTables[] = $table;
}
}
+
return $archiveTables;
}
public static function getDateFromTableName($tableName)
{
$tableName = Common::unprefixTable($tableName);
- $date = str_replace(array('archive_numeric_', 'archive_blob_'), '', $tableName);
+ $date = str_replace(array('archive_numeric_', 'archive_blob_'), '', $tableName);
+
return $date;
}
@@ -111,9 +106,11 @@ class ArchiveTableCreator
if (strpos($tableName, 'archive_numeric_') !== false) {
return self::NUMERIC_TABLE;
}
+
if (strpos($tableName, 'archive_blob_') !== false) {
return self::BLOB_TABLE;
}
+
return false;
}
}
diff --git a/core/DataAccess/ArchiveWriter.php b/core/DataAccess/ArchiveWriter.php
index 70f2159b67..1ea6b0d08c 100644
--- a/core/DataAccess/ArchiveWriter.php
+++ b/core/DataAccess/ArchiveWriter.php
@@ -23,7 +23,6 @@ use Piwik\Period;
*/
class ArchiveWriter
{
- const PREFIX_SQL_LOCK = "locked_";
/**
* Flag stored at the end of the archiving
*
@@ -64,9 +63,10 @@ class ArchiveWriter
public function __construct(ArchiveProcessor\Parameters $params, $isArchiveTemporary)
{
$this->idArchive = false;
- $this->idSite = $params->getSite()->getId();
- $this->segment = $params->getSegment();
- $this->period = $params->getPeriod();
+ $this->idSite = $params->getSite()->getId();
+ $this->segment = $params->getSegment();
+ $this->period = $params->getPeriod();
+
$idSites = array($this->idSite);
$this->doneFlag = Rules::getDoneStringFlagFor($idSites, $this->segment, $this->period->getLabel(), $params->getRequestedPlugin(), $params->isSkipAggregationOfSubTables());
$this->isArchiveTemporary = $isArchiveTemporary;
@@ -92,7 +92,7 @@ class ArchiveWriter
$newName = $name . '_' . $id;
}
- $value = $this->compress($value);
+ $value = $this->compress($value);
$clean[] = array($newName, $value);
}
$this->insertBulkRecords($clean);
@@ -108,6 +108,7 @@ class ArchiveWriter
if ($this->idArchive === false) {
throw new Exception("Must call allocateNewArchiveId() first");
}
+
return $this->idArchive;
}
@@ -119,7 +120,11 @@ class ArchiveWriter
public function finalizeArchive()
{
- $this->deletePreviousArchiveStatus();
+ $numericTable = $this->getTableNumeric();
+ $idArchive = $this->getIdArchive();
+
+ $this->getModel()->deletePreviousArchiveStatus($numericTable, $idArchive, $this->doneFlag);
+
$this->logArchiveStatusAsFinal();
}
@@ -128,28 +133,8 @@ class ArchiveWriter
if (Db::get()->hasBlobDataType()) {
return gzcompress($data);
}
- return $data;
- }
-
- protected function getArchiveLockName()
- {
- $numericTable = $this->getTableNumeric();
- $dbLockName = "allocateNewArchiveId.$numericTable";
- return $dbLockName;
- }
-
- protected function acquireArchiveTableLock()
- {
- $dbLockName = $this->getArchiveLockName();
- if (Db::getDbLock($dbLockName, $maxRetries = 30) === false) {
- throw new Exception("allocateNewArchiveId: Cannot get named lock $dbLockName.");
- }
- }
- protected function releaseArchiveTableLock()
- {
- $dbLockName = $this->getArchiveLockName();
- Db::releaseDbLock($dbLockName);
+ return $data;
}
protected function allocateNewArchiveId()
@@ -171,56 +156,31 @@ class ArchiveWriter
{
$numericTable = $this->getTableNumeric();
$idSite = $this->idSite;
+ $date = date("Y-m-d H:i:s");
- $this->acquireArchiveTableLock();
+ $id = $this->getModel()->insertNewArchiveId($numericTable, $idSite, $date);
- $locked = self::PREFIX_SQL_LOCK . Common::generateUniqId();
- $date = date("Y-m-d H:i:s");
- $insertSql = "INSERT INTO $numericTable "
- . " SELECT IFNULL( MAX(idarchive), 0 ) + 1,
- '" . $locked . "',
- " . (int)$idSite . ",
- '" . $date . "',
- '" . $date . "',
- 0,
- '" . $date . "',
- 0 "
- . " FROM $numericTable as tb1";
- Db::get()->exec($insertSql);
-
- $this->releaseArchiveTableLock();
-
- $selectIdSql = "SELECT idarchive FROM $numericTable WHERE name = ? LIMIT 1";
- $id = Db::get()->fetchOne($selectIdSql, $locked);
return $id;
}
- protected function logArchiveStatusAsIncomplete()
+ private function getModel()
{
- $statusWhileProcessing = self::DONE_ERROR;
- $this->insertRecord($this->doneFlag, $statusWhileProcessing);
+ return new Model();
}
- protected function deletePreviousArchiveStatus()
+ protected function logArchiveStatusAsIncomplete()
{
- // without advisory lock here, the DELETE would acquire Exclusive Lock
- $this->acquireArchiveTableLock();
-
- Db::query("DELETE FROM " . $this->getTableNumeric() . "
- WHERE idarchive = ? AND (name = '" . $this->doneFlag
- . "' OR name LIKE '" . self::PREFIX_SQL_LOCK . "%')",
- array($this->getIdArchive())
- );
-
- $this->releaseArchiveTableLock();
+ $this->insertRecord($this->doneFlag, self::DONE_ERROR);
}
protected function logArchiveStatusAsFinal()
{
$status = self::DONE_OK;
+
if ($this->isArchiveTemporary) {
$status = self::DONE_OK_TEMPORARY;
}
+
$this->insertRecord($this->doneFlag, $status);
}
@@ -233,27 +193,37 @@ class ArchiveWriter
foreach ($records as $record) {
$this->insertRecord($record[0], $record[1]);
}
+
return true;
}
+
$bindSql = $this->getInsertRecordBind();
- $values = array();
+ $values = array();
$valueSeen = false;
foreach ($records as $record) {
// don't record zero
- if (empty($record[1])) continue;
+ if (empty($record[1])) {
+ continue;
+ }
- $bind = $bindSql;
- $bind[] = $record[0]; // name
- $bind[] = $record[1]; // value
+ $bind = $bindSql;
+ $bind[] = $record[0]; // name
+ $bind[] = $record[1]; // value
$values[] = $bind;
$valueSeen = $record[1];
}
- if (empty($values)) return true;
+
+ if (empty($values)) {
+ return true;
+ }
$tableName = $this->getTableNameToInsert($valueSeen);
- BatchInsert::tableInsertBatch($tableName, $this->getInsertFields(), $values);
+ $fields = $this->getInsertFields();
+
+ BatchInsert::tableInsertBatch($tableName, $fields, $values);
+
return true;
}
@@ -272,15 +242,11 @@ class ArchiveWriter
}
$tableName = $this->getTableNameToInsert($value);
+ $fields = $this->getInsertFields();
+ $record = $this->getInsertRecordBind();
+
+ $this->getModel()->insertRecord($tableName, $fields, $record, $name, $value);
- // duplicate idarchives are Ignored, see https://github.com/piwik/piwik/issues/987
- $query = "INSERT IGNORE INTO " . $tableName . "
- (" . implode(", ", $this->getInsertFields()) . ")
- VALUES (?,?,?,?,?,?,?,?)";
- $bindSql = $this->getInsertRecordBind();
- $bindSql[] = $name;
- $bindSql[] = $value;
- Db::query($query, $bindSql);
return true;
}
@@ -299,6 +265,7 @@ class ArchiveWriter
if (is_numeric($value)) {
return $this->getTableNumeric();
}
+
return ArchiveTableCreator::getBlobTable($this->dateStart);
}
diff --git a/core/DataAccess/LogAggregator.php b/core/DataAccess/LogAggregator.php
index 79db3148b7..83946b1f6f 100644
--- a/core/DataAccess/LogAggregator.php
+++ b/core/DataAccess/LogAggregator.php
@@ -292,52 +292,61 @@ class LogAggregator
$tableName = self::LOG_VISIT_TABLE;
$availableMetrics = $this->getVisitsMetricFields();
- $select = $this->getSelectStatement($dimensions, $tableName, $additionalSelects, $availableMetrics, $metrics);
- $from = array($tableName);
- $where = $this->getWhereStatement($tableName, self::VISIT_DATETIME_FIELD, $where);
+ $select = $this->getSelectStatement($dimensions, $tableName, $additionalSelects, $availableMetrics, $metrics);
+ $from = array($tableName);
+ $where = $this->getWhereStatement($tableName, self::VISIT_DATETIME_FIELD, $where);
$groupBy = $this->getGroupByStatement($dimensions, $tableName);
$orderBy = false;
if ($rankingQuery) {
$orderBy = '`' . Metrics::INDEX_NB_VISITS . '` DESC';
}
+
$query = $this->generateQuery($select, $from, $where, $groupBy, $orderBy);
if ($rankingQuery) {
unset($availableMetrics[Metrics::INDEX_MAX_ACTIONS]);
$sumColumns = array_keys($availableMetrics);
+
if ($metrics) {
$sumColumns = array_intersect($sumColumns, $metrics);
}
+
$rankingQuery->addColumn($sumColumns, 'sum');
if ($this->isMetricRequested(Metrics::INDEX_MAX_ACTIONS, $metrics)) {
$rankingQuery->addColumn(Metrics::INDEX_MAX_ACTIONS, 'max');
}
+
return $rankingQuery->execute($query['sql'], $query['bind']);
}
+
return $this->getDb()->query($query['sql'], $query['bind']);
}
protected function getSelectsMetrics($metricsAvailable, $metricsRequested = false)
{
$selects = array();
+
foreach ($metricsAvailable as $metricId => $statement) {
if ($this->isMetricRequested($metricId, $metricsRequested)) {
- $aliasAs = $this->getSelectAliasAs($metricId);
+ $aliasAs = $this->getSelectAliasAs($metricId);
$selects[] = $statement . $aliasAs;
}
}
+
return $selects;
}
protected function getSelectStatement($dimensions, $tableName, $additionalSelects, array $availableMetrics, $requestedMetrics = false)
{
$dimensionsToSelect = $this->getDimensionsToSelect($dimensions, $additionalSelects);
+
$selects = array_merge(
$this->getSelectDimensions($dimensionsToSelect, $tableName),
$this->getSelectsMetrics($availableMetrics, $requestedMetrics),
!empty($additionalSelects) ? $additionalSelects : array()
);
+
$select = implode(self::FIELDS_SEPARATOR, $selects);
return $select;
}
@@ -354,6 +363,7 @@ class LogAggregator
if (empty($additionalSelects)) {
return $dimensions;
}
+
$dimensionsToSelect = array();
foreach ($dimensions as $selectAs => $dimension) {
$asAlias = $this->getSelectAliasAs($dimension);
@@ -363,6 +373,7 @@ class LogAggregator
}
}
}
+
$dimensionsToSelect = array_unique($dimensionsToSelect);
return $dimensionsToSelect;
}
@@ -381,6 +392,7 @@ class LogAggregator
{
foreach ($dimensions as $selectAs => &$field) {
$selectAsString = $field;
+
if (!is_numeric($selectAs)) {
$selectAsString = $selectAs;
} else {
@@ -389,16 +401,18 @@ class LogAggregator
$selectAsString = $appendSelectAs = false;
}
}
+
$isKnownField = !in_array($field, array('referrer_data'));
- if ($selectAsString == $field
- && $isKnownField
- ) {
+
+ if ($selectAsString == $field && $isKnownField) {
$field = $this->prefixColumn($field, $tableName);
}
+
if ($appendSelectAs && $selectAsString) {
$field = $this->prefixColumn($field, $tableName) . $this->getSelectAliasAs($selectAsString);
}
}
+
return $dimensions;
}
@@ -421,7 +435,7 @@ class LogAggregator
protected function isFieldFunctionOrComplexExpression($field)
{
return strpos($field, "(") !== false
- || strpos($field, "CASE") !== false;
+ || strpos($field, "CASE") !== false;
}
protected function getSelectAliasAs($metricId)
@@ -432,7 +446,7 @@ class LogAggregator
protected function isMetricRequested($metricId, $metricsRequested)
{
return $metricsRequested === false
- || in_array($metricId, $metricsRequested);
+ || in_array($metricId, $metricsRequested);
}
protected function getWhereStatement($tableName, $datetimeField, $extraWhere = false)
@@ -440,17 +454,20 @@ class LogAggregator
$where = "$tableName.$datetimeField >= ?
AND $tableName.$datetimeField <= ?
AND $tableName.idsite IN (". Common::getSqlStringFieldsArray($this->sites) . ")";
+
if (!empty($extraWhere)) {
$extraWhere = sprintf($extraWhere, $tableName, $tableName);
- $where .= ' AND ' . $extraWhere;
+ $where .= ' AND ' . $extraWhere;
}
+
return $where;
}
protected function getGroupByStatement($dimensions, $tableName)
{
$dimensions = $this->getSelectDimensions($dimensions, $tableName, $appendSelectAs = false);
- $groupBy = implode(", ", $dimensions);
+ $groupBy = implode(", ", $dimensions);
+
return $groupBy;
}
@@ -464,6 +481,7 @@ class LogAggregator
{
$bind = array($this->dateStart->getDateStartUTC(), $this->dateEnd->getDateEndUTC());
$bind = array_merge($bind, $this->sites);
+
return $bind;
}
@@ -624,9 +642,9 @@ class LogAggregator
$tableName = self::LOG_ACTIONS_TABLE;
$availableMetrics = $this->getActionsMetricFields();
- $select = $this->getSelectStatement($dimensions, $tableName, $additionalSelects, $availableMetrics, $metrics);
- $from = array($tableName);
- $where = $this->getWhereStatement($tableName, self::ACTION_DATETIME_FIELD, $where);
+ $select = $this->getSelectStatement($dimensions, $tableName, $additionalSelects, $availableMetrics, $metrics);
+ $from = array($tableName);
+ $where = $this->getWhereStatement($tableName, self::ACTION_DATETIME_FIELD, $where);
$groupBy = $this->getGroupByStatement($dimensions, $tableName);
$orderBy = false;
@@ -638,12 +656,14 @@ class LogAggregator
foreach ($joinLogActionOnColumn as $i => $joinColumn) {
$tableAlias = 'log_action' . ($multiJoin ? $i + 1 : '');
+
if (strpos($joinColumn, ' ') === false) {
$joinOn = $tableAlias . '.idaction = ' . $tableName . '.' . $joinColumn;
} else {
// more complex join column like if (...)
$joinOn = $tableAlias . '.idaction = ' . $joinColumn;
}
+
$from[] = array(
'table' => 'log_action',
'tableAlias' => $tableAlias,
@@ -663,7 +683,9 @@ class LogAggregator
if ($metrics) {
$sumColumns = array_intersect($sumColumns, $metrics);
}
+
$rankingQuery->addColumn($sumColumns, 'sum');
+
return $rankingQuery->execute($query['sql'], $query['bind']);
}
@@ -738,8 +760,8 @@ class LogAggregator
public function queryConversionsByDimension($dimensions = array(), $where = false, $additionalSelects = array())
{
$dimensions = array_merge(array(self::IDGOAL_FIELD), $dimensions);
+ $tableName = self::LOG_CONVERSION_TABLE;
$availableMetrics = $this->getConversionsMetricFields();
- $tableName = self::LOG_CONVERSION_TABLE;
$select = $this->getSelectStatement($dimensions, $tableName, $additionalSelects, $availableMetrics);
@@ -748,6 +770,7 @@ class LogAggregator
$groupBy = $this->getGroupByStatement($dimensions, $tableName);
$orderBy = false;
$query = $this->generateQuery($select, $from, $where, $groupBy, $orderBy);
+
return $this->getDb()->query($query['sql'], $query['bind']);
}
@@ -824,14 +847,16 @@ class LogAggregator
{
$selects = array();
$extraCondition = '';
+
if ($restrictToReturningVisitors) {
// extra condition for the SQL SELECT that makes sure only returning visits are counted
// when creating the 'days since last visit' report
$extraCondition = 'and log_visit.visitor_returning = 1';
- $extraSelect = "sum(case when log_visit.visitor_returning = 0 then 1 else 0 end) "
- . " as `" . $selectColumnPrefix . 'General_NewVisits' . "`";
+ $extraSelect = "sum(case when log_visit.visitor_returning = 0 then 1 else 0 end) "
+ . " as `" . $selectColumnPrefix . 'General_NewVisits' . "`";
$selects[] = $extraSelect;
}
+
foreach ($ranges as $gap) {
if (count($gap) == 2) {
$lowerBound = $gap[0];
@@ -840,12 +865,11 @@ class LogAggregator
$selectAs = "$selectColumnPrefix$lowerBound-$upperBound";
$selects[] = "sum(case when $table.$column between $lowerBound and $upperBound $extraCondition" .
- " then 1 else 0 end) as `$selectAs`";
+ " then 1 else 0 end) as `$selectAs`";
} else {
$lowerBound = $gap[0];
- $selectAs = $selectColumnPrefix . ($lowerBound + 1) . urlencode('+');
-
+ $selectAs = $selectColumnPrefix . ($lowerBound + 1) . urlencode('+');
$selects[] = "sum(case when $table.$column > $lowerBound $extraCondition then 1 else 0 end) as `$selectAs`";
}
}
@@ -869,6 +893,7 @@ class LogAggregator
public static function makeArrayOneColumn($row, $columnName, $lookForThisPrefix = false)
{
$cleanRow = array();
+
foreach ($row as $label => $count) {
if (empty($lookForThisPrefix)
|| strpos($label, $lookForThisPrefix) === 0
@@ -877,6 +902,7 @@ class LogAggregator
$cleanRow[$cleanLabel] = array($columnName => $count);
}
}
+
return $cleanRow;
}
diff --git a/core/DataAccess/Model.php b/core/DataAccess/Model.php
new file mode 100644
index 0000000000..facaa5dd97
--- /dev/null
+++ b/core/DataAccess/Model.php
@@ -0,0 +1,229 @@
+<?php
+/**
+ * Piwik - free/libre analytics platform
+ *
+ * @link http://piwik.org
+ * @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
+ *
+ */
+namespace Piwik\DataAccess;
+
+use Exception;
+use Piwik\Common;
+use Piwik\Db;
+use Piwik\DbHelper;
+
+/**
+ * Cleans up outdated archives
+ *
+ * @package Piwik\DataAccess
+ */
+class Model
+{
+ const PREFIX_SQL_LOCK = "locked_";
+
+ public function purgeInvalidatedArchiveTable($archiveTable)
+ {
+ /**
+ * Select the archives that have already been invalidated and have been since re-processed.
+ * It purges records for each distinct { archive name (includes segment hash) , idsite, date, period } tuple.
+ */
+ $query = 'SELECT t1.idarchive FROM `' . $archiveTable . '` t1
+ INNER JOIN `' . $archiveTable . '` t2
+ ON t1.name = t2.name AND t1.idsite=t2.idsite
+ AND t1.date1=t2.date1 AND t1.date2=t2.date2 AND t1.period=t2.period
+ WHERE t1.value = ' . ArchiveWriter::DONE_INVALIDATED . '
+ AND t2.value IN(' . ArchiveWriter::DONE_OK . ', ' . ArchiveWriter::DONE_OK_TEMPORARY . ')
+ AND t1.ts_archived < t2.ts_archived AND t1.name LIKE \'done%\'';
+
+ $result = Db::fetchAll($query);
+
+ return $result;
+ }
+
+ public function getTemporaryArchivesOlderThan($archiveTable, $purgeArchivesOlderThan)
+ {
+ $query = "SELECT idarchive FROM " . $archiveTable . "
+ WHERE name LIKE 'done%'
+ AND (( value = " . ArchiveWriter::DONE_OK_TEMPORARY . "
+ AND ts_archived < ?)
+ OR value = " . ArchiveWriter::DONE_ERROR . ")";
+
+ return Db::fetchAll($query, array($purgeArchivesOlderThan));
+ }
+
+ /*
+ * Deleting "Custom Date Range" reports, since they can be re-processed and would take up un-necessary space
+ */
+ public function deleteArchivesWithPeriodRange($numericTable, $blobTable, $range, $date)
+ {
+ $query = "DELETE FROM %s WHERE period = ? AND ts_archived < ?";
+ $bind = array($range, $date);
+
+ Db::query(sprintf($query, $numericTable), $bind);
+
+ try {
+ Db::query(sprintf($query, $blobTable), $bind);
+ } catch (Exception $e) {
+ // Individual blob tables could be missing
+ }
+ }
+
+ public function deleteArchiveIds($numericTable, $blobTable, $idsToDelete)
+ {
+ $query = "DELETE FROM %s WHERE idarchive IN (" . implode(',', $idsToDelete) . ")";
+
+ Db::query(sprintf($query, $numericTable));
+
+ try {
+ Db::query(sprintf($query, $blobTable));
+ } catch (Exception $e) {
+ // Individual blob tables could be missing
+ }
+ }
+
+ public function getArchiveIdAndVisits($numericTable, $idSite, $period, $dateStartIso, $dateEndIso, $minDatetimeIsoArchiveProcessedUTC, $doneFlags, $possibleValues)
+ {
+ $bindSQL = array($idSite,
+ $dateStartIso,
+ $dateEndIso,
+ $period,
+ );
+
+ $timeStampWhere = '';
+ if ($minDatetimeIsoArchiveProcessedUTC) {
+ $timeStampWhere = " AND ts_archived >= ? ";
+ $bindSQL[] = $minDatetimeIsoArchiveProcessedUTC;
+ }
+
+ $sqlWhereArchiveName = self::getNameCondition($doneFlags, $possibleValues);
+
+ $sqlQuery = "SELECT idarchive, value, name, date1 as startDate FROM $numericTable
+ WHERE idsite = ?
+ AND date1 = ?
+ AND date2 = ?
+ AND period = ?
+ AND ( ($sqlWhereArchiveName)
+ OR name = '" . ArchiveSelector::NB_VISITS_RECORD_LOOKED_UP . "'
+ OR name = '" . ArchiveSelector::NB_VISITS_CONVERTED_RECORD_LOOKED_UP . "')
+ $timeStampWhere
+ ORDER BY idarchive DESC";
+ $results = Db::fetchAll($sqlQuery, $bindSQL);
+
+ return $results;
+ }
+
+ public function createArchiveTable($tableName, $tableNamePrefix)
+ {
+ $db = Db::get();
+ $sql = DbHelper::getTableCreateSql($tableNamePrefix);
+
+ // replace table name template by real name
+ $tableNamePrefix = Common::prefixTable($tableNamePrefix);
+ $sql = str_replace($tableNamePrefix, $tableName, $sql);
+
+ try {
+ $db->query($sql);
+ } catch (Exception $e) {
+ // accept mysql error 1050: table already exists, throw otherwise
+ if (!$db->isErrNo($e, '1050')) {
+ throw $e;
+ }
+ }
+ }
+
+ /**
+ * Locks the archive table to generate a new archive ID.
+ *
+ * We lock to make sure that
+ * if several archiving processes are running at the same time (for different websites and/or periods)
+ * then they will each use a unique archive ID.
+ *
+ * @return int
+ */
+ public function insertNewArchiveId($numericTable, $idSite, $date)
+ {
+ $this->acquireArchiveTableLock($numericTable);
+
+ $locked = self::PREFIX_SQL_LOCK . Common::generateUniqId();
+
+ $insertSql = "INSERT INTO $numericTable "
+ . " SELECT IFNULL( MAX(idarchive), 0 ) + 1,
+ '" . $locked . "',
+ " . (int)$idSite . ",
+ '" . $date . "',
+ '" . $date . "',
+ 0,
+ '" . $date . "',
+ 0 "
+ . " FROM $numericTable as tb1";
+ Db::get()->exec($insertSql);
+
+ $this->releaseArchiveTableLock($numericTable);
+
+ $selectIdSql = "SELECT idarchive FROM $numericTable WHERE name = ? LIMIT 1";
+ $id = Db::get()->fetchOne($selectIdSql, $locked);
+ return $id;
+ }
+
+ public function deletePreviousArchiveStatus($numericTable, $archiveId, $doneFlag)
+ {
+ // without advisory lock here, the DELETE would acquire Exclusive Lock
+ $this->acquireArchiveTableLock($numericTable);
+
+ Db::query("DELETE FROM $numericTable WHERE idarchive = ? AND (name = '" . $doneFlag
+ . "' OR name LIKE '" . self::PREFIX_SQL_LOCK . "%')",
+ array($archiveId)
+ );
+
+ $this->releaseArchiveTableLock($numericTable);
+ }
+
+ public function insertRecord($tableName, $fields, $record, $name, $value)
+ {
+ // duplicate idarchives are Ignored, see https://github.com/piwik/piwik/issues/987
+ $query = "INSERT IGNORE INTO " . $tableName . " (" . implode(", ", $fields) . ")
+ VALUES (?,?,?,?,?,?,?,?)";
+
+ $bindSql = $record;
+ $bindSql[] = $name;
+ $bindSql[] = $value;
+
+ Db::query($query, $bindSql);
+
+ return true;
+ }
+
+ /**
+ * Returns the SQL condition used to find successfully completed archives that
+ * this instance is querying for.
+ */
+ private static function getNameCondition($doneFlags, $possibleValues)
+ {
+ $allDoneFlags = "'" . implode("','", $doneFlags) . "'";
+
+ // create the SQL to find archives that are DONE
+ return "((name IN ($allDoneFlags)) AND (value IN (" . implode(',', $possibleValues) . ")))";
+ }
+
+ protected function acquireArchiveTableLock($numericTable)
+ {
+ $dbLockName = $this->getArchiveLockName($numericTable);
+
+ if (Db::getDbLock($dbLockName, $maxRetries = 30) === false) {
+ throw new Exception("allocateNewArchiveId: Cannot get named lock $dbLockName.");
+ }
+ }
+
+ protected function releaseArchiveTableLock($numericTable)
+ {
+ $dbLockName = $this->getArchiveLockName($numericTable);
+ Db::releaseDbLock($dbLockName);
+ }
+
+ protected function getArchiveLockName($numericTable)
+ {
+ return "allocateNewArchiveId.$numericTable";
+ }
+
+}
diff --git a/core/Db.php b/core/Db.php
index 4ad62684f6..52c454e333 100644
--- a/core/Db.php
+++ b/core/Db.php
@@ -141,6 +141,7 @@ class Db
}
$profiler->queryEnd($q);
+
return $return;
}
@@ -279,13 +280,13 @@ class Db
public static function deleteAllRows($table, $where, $orderBy, $maxRowsPerQuery = 100000, $parameters = array())
{
$orderByClause = $orderBy ? "ORDER BY $orderBy" : "";
- $sql = "DELETE FROM $table
- $where
- $orderByClause
+
+ $sql = "DELETE FROM $table $where $orderByClause
LIMIT " . (int)$maxRowsPerQuery;
// delete rows w/ a limit
$totalRowsDeleted = 0;
+
do {
$rowsDeleted = self::query($sql, $parameters)->rowCount();
@@ -308,6 +309,7 @@ class Db
public static function optimizeTables($tables)
{
$optimize = Config::getInstance()->General['enable_sql_optimize_queries'];
+
if (empty($optimize)) {
return;
}
@@ -315,13 +317,14 @@ class Db
if (empty($tables)) {
return false;
}
+
if (!is_array($tables)) {
$tables = array($tables);
}
// filter out all InnoDB tables
$myisamDbTables = array();
- foreach (Db::fetchAll("SHOW TABLE STATUS") as $row) {
+ foreach (self::getTableStatus() as $row) {
if (strtolower($row['Engine']) == 'myisam'
&& in_array($row['Name'], $tables)
) {
@@ -337,6 +340,11 @@ class Db
return self::query("OPTIMIZE TABLE " . implode(',', $myisamDbTables));
}
+ private static function getTableStatus()
+ {
+ return Db::fetchAll("SHOW TABLE STATUS");
+ }
+
/**
* Drops the supplied table or tables.
*
@@ -397,6 +405,7 @@ class Db
if (!is_array($tablesToRead)) {
$tablesToRead = array($tablesToRead);
}
+
if (!is_array($tablesToWrite)) {
$tablesToWrite = array($tablesToWrite);
}
@@ -405,6 +414,7 @@ class Db
foreach ($tablesToWrite as $table) {
$lockExprs[] = $table . " WRITE";
}
+
foreach ($tablesToRead as $table) {
$lockExprs[] = $table . " READ";
}
@@ -466,6 +476,7 @@ class Db
public static function segmentedFetchFirst($sql, $first, $last, $step, $params = array())
{
$result = false;
+
if ($step > 0) {
for ($i = $first; $result === false && $i <= $last; $i += $step) {
$result = self::fetchOne($sql, array_merge($params, array($i, $i + $step)));
@@ -475,6 +486,7 @@ class Db
$result = self::fetchOne($sql, array_merge($params, array($i, $i + $step)));
}
}
+
return $result;
}
@@ -502,6 +514,7 @@ class Db
public static function segmentedFetchOne($sql, $first, $last, $step, $params = array())
{
$result = array();
+
if ($step > 0) {
for ($i = $first; $i <= $last; $i += $step) {
$result[] = self::fetchOne($sql, array_merge($params, array($i, $i + $step)));
@@ -511,6 +524,7 @@ class Db
$result[] = self::fetchOne($sql, array_merge($params, array($i, $i + $step)));
}
}
+
return $result;
}
@@ -539,17 +553,19 @@ class Db
public static function segmentedFetchAll($sql, $first, $last, $step, $params = array())
{
$result = array();
+
if ($step > 0) {
for ($i = $first; $i <= $last; $i += $step) {
$currentParams = array_merge($params, array($i, $i + $step));
- $result = array_merge($result, self::fetchAll($sql, $currentParams));
+ $result = array_merge($result, self::fetchAll($sql, $currentParams));
}
} else {
for ($i = $first; $i >= $last; $i += $step) {
$currentParams = array_merge($params, array($i, $i + $step));
- $result = array_merge($result, self::fetchAll($sql, $currentParams));
+ $result = array_merge($result, self::fetchAll($sql, $currentParams));
}
}
+
return $result;
}
@@ -623,6 +639,7 @@ class Db
}
$maxRetries--;
}
+
return false;
}
diff --git a/core/Db/Adapter.php b/core/Db/Adapter.php
index 342a320d72..5ddd529caa 100644
--- a/core/Db/Adapter.php
+++ b/core/Db/Adapter.php
@@ -38,7 +38,7 @@ class Adapter
}
$className = self::getAdapterClassName($adapterName);
- $adapter = new $className($dbInfos);
+ $adapter = new $className($dbInfos);
if ($connect) {
$adapter->getConnection();
diff --git a/core/Db/BatchInsert.php b/core/Db/BatchInsert.php
index 1e818c2073..f6e7a223c7 100644
--- a/core/Db/BatchInsert.php
+++ b/core/Db/BatchInsert.php
@@ -33,13 +33,12 @@ class BatchInsert
public static function tableInsertBatchIterate($tableName, $fields, $values, $ignoreWhenDuplicate = true)
{
$fieldList = '(' . join(',', $fields) . ')';
- $ignore = $ignoreWhenDuplicate ? 'IGNORE' : '';
+ $ignore = $ignoreWhenDuplicate ? 'IGNORE' : '';
foreach ($values as $row) {
- $query = "INSERT $ignore
- INTO " . $tableName . "
- $fieldList
- VALUES (" . Common::getSqlStringFieldsArray($row) . ")";
+ $query = "INSERT $ignore INTO " . $tableName . "
+ $fieldList
+ VALUES (" . Common::getSqlStringFieldsArray($row) . ")";
Db::query($query, $row);
}
}
@@ -172,7 +171,8 @@ class BatchInsert
* @see http://bugs.php.net/bug.php?id=54158
*/
$openBaseDir = ini_get('open_basedir');
- $safeMode = ini_get('safe_mode');
+ $safeMode = ini_get('safe_mode');
+
if (empty($openBaseDir) && empty($safeMode)) {
// php 5.x - LOAD DATA LOCAL INFILE is disabled if open_basedir restrictions or safe_mode enabled
$keywords[] = 'LOCAL ';
@@ -199,9 +199,11 @@ class BatchInsert
$exceptions[] = "\n Try #" . (count($exceptions) + 1) . ': ' . $queryStart . ": " . $message;
}
}
+
if (count($exceptions)) {
throw new Exception(implode(",", $exceptions));
}
+
return false;
}
@@ -218,8 +220,8 @@ class BatchInsert
// Set up CSV delimiters, quotes, etc
$delim = $fileSpec['delim'];
$quote = $fileSpec['quote'];
- $eol = $fileSpec['eol'];
- $null = $fileSpec['null'];
+ $eol = $fileSpec['eol'];
+ $null = $fileSpec['null'];
$escapespecial_cb = $fileSpec['escapespecial_cb'];
$fp = @fopen($filePath, 'wb');
@@ -246,6 +248,7 @@ class BatchInsert
throw new Exception('Error writing to the tmp file ' . $filePath);
}
}
+
fclose($fp);
@chmod($filePath, 0777);
diff --git a/core/Db/Schema.php b/core/Db/Schema.php
index c7823cf70a..796e71dca5 100644
--- a/core/Db/Schema.php
+++ b/core/Db/Schema.php
@@ -116,11 +116,11 @@ class Schema extends Singleton
*/
private function loadSchema()
{
- $config = Config::getInstance();
- $dbInfos = $config->database;
+ $config = Config::getInstance();
+ $dbInfos = $config->database;
$schemaName = trim($dbInfos['schema']);
- $className = self::getSchemaClassName($schemaName);
+ $className = self::getSchemaClassName($schemaName);
$this->schema = new $className();
}
@@ -134,6 +134,7 @@ class Schema extends Singleton
if ($this->schema === null) {
$this->loadSchema();
}
+
return $this->schema;
}
diff --git a/core/Db/Schema/Mysql.php b/core/Db/Schema/Mysql.php
index 45ff75b685..eb8c1f71f1 100644
--- a/core/Db/Schema/Mysql.php
+++ b/core/Db/Schema/Mysql.php
@@ -20,6 +20,8 @@ use Piwik\DbHelper;
*/
class Mysql implements SchemaInterface
{
+ private $tablesInstalled = null;
+
/**
* Is this MySQL storage engine available?
*
@@ -58,216 +60,213 @@ class Mysql implements SchemaInterface
$prefixTables = $this->getTablePrefix();
$tables = array(
- 'user' => "CREATE TABLE {$prefixTables}user (
- login VARCHAR(100) NOT NULL,
- password CHAR(32) NOT NULL,
- alias VARCHAR(45) NOT NULL,
- email VARCHAR(100) NOT NULL,
- token_auth CHAR(32) NOT NULL,
- superuser_access TINYINT(2) unsigned NOT NULL DEFAULT '0',
- date_registered TIMESTAMP NULL,
- PRIMARY KEY(login),
- UNIQUE KEY uniq_keytoken(token_auth)
- ) ENGINE=$engine DEFAULT CHARSET=utf8
- ",
-
- 'access' => "CREATE TABLE {$prefixTables}access (
- login VARCHAR(100) NOT NULL,
- idsite INTEGER UNSIGNED NOT NULL,
- access VARCHAR(10) NULL,
- PRIMARY KEY(login, idsite)
- ) ENGINE=$engine DEFAULT CHARSET=utf8
- ",
-
- 'site' => "CREATE TABLE {$prefixTables}site (
- idsite INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- name VARCHAR(90) NOT NULL,
- main_url VARCHAR(255) NOT NULL,
- ts_created TIMESTAMP NULL,
- ecommerce TINYINT DEFAULT 0,
- sitesearch TINYINT DEFAULT 1,
- sitesearch_keyword_parameters TEXT NOT NULL,
- sitesearch_category_parameters TEXT NOT NULL,
- timezone VARCHAR( 50 ) NOT NULL,
- currency CHAR( 3 ) NOT NULL,
- excluded_ips TEXT NOT NULL,
- excluded_parameters TEXT NOT NULL,
- excluded_user_agents TEXT NOT NULL,
- `group` VARCHAR(250) NOT NULL,
- `type` VARCHAR(255) NOT NULL,
- keep_url_fragment TINYINT NOT NULL DEFAULT 0,
- PRIMARY KEY(idsite)
- ) ENGINE=$engine DEFAULT CHARSET=utf8
- ",
-
- 'site_url' => "CREATE TABLE {$prefixTables}site_url (
- idsite INTEGER(10) UNSIGNED NOT NULL,
- url VARCHAR(255) NOT NULL,
- PRIMARY KEY(idsite, url)
- ) ENGINE=$engine DEFAULT CHARSET=utf8
- ",
-
- 'goal' => " CREATE TABLE `{$prefixTables}goal` (
- `idsite` int(11) NOT NULL,
- `idgoal` int(11) NOT NULL,
- `name` varchar(50) NOT NULL,
- `match_attribute` varchar(20) NOT NULL,
- `pattern` varchar(255) NOT NULL,
- `pattern_type` varchar(10) NOT NULL,
- `case_sensitive` tinyint(4) NOT NULL,
- `allow_multiple` tinyint(4) NOT NULL,
- `revenue` float NOT NULL,
- `deleted` tinyint(4) NOT NULL default '0',
- PRIMARY KEY (`idsite`,`idgoal`)
- ) ENGINE=$engine DEFAULT CHARSET=utf8
- ",
-
- 'logger_message' => "CREATE TABLE {$prefixTables}logger_message (
- idlogger_message INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
+ 'user' => "CREATE TABLE {$prefixTables}user (
+ login VARCHAR(100) NOT NULL,
+ password CHAR(32) NOT NULL,
+ alias VARCHAR(45) NOT NULL,
+ email VARCHAR(100) NOT NULL,
+ token_auth CHAR(32) NOT NULL,
+ superuser_access TINYINT(2) unsigned NOT NULL DEFAULT '0',
+ date_registered TIMESTAMP NULL,
+ PRIMARY KEY(login),
+ UNIQUE KEY uniq_keytoken(token_auth)
+ ) ENGINE=$engine DEFAULT CHARSET=utf8
+ ",
+
+ 'access' => "CREATE TABLE {$prefixTables}access (
+ login VARCHAR(100) NOT NULL,
+ idsite INTEGER UNSIGNED NOT NULL,
+ access VARCHAR(10) NULL,
+ PRIMARY KEY(login, idsite)
+ ) ENGINE=$engine DEFAULT CHARSET=utf8
+ ",
+
+ 'site' => "CREATE TABLE {$prefixTables}site (
+ idsite INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
+ name VARCHAR(90) NOT NULL,
+ main_url VARCHAR(255) NOT NULL,
+ ts_created TIMESTAMP NULL,
+ ecommerce TINYINT DEFAULT 0,
+ sitesearch TINYINT DEFAULT 1,
+ sitesearch_keyword_parameters TEXT NOT NULL,
+ sitesearch_category_parameters TEXT NOT NULL,
+ timezone VARCHAR( 50 ) NOT NULL,
+ currency CHAR( 3 ) NOT NULL,
+ excluded_ips TEXT NOT NULL,
+ excluded_parameters TEXT NOT NULL,
+ excluded_user_agents TEXT NOT NULL,
+ `group` VARCHAR(250) NOT NULL,
+ `type` VARCHAR(255) NOT NULL,
+ keep_url_fragment TINYINT NOT NULL DEFAULT 0,
+ PRIMARY KEY(idsite)
+ ) ENGINE=$engine DEFAULT CHARSET=utf8
+ ",
+
+ 'site_url' => "CREATE TABLE {$prefixTables}site_url (
+ idsite INTEGER(10) UNSIGNED NOT NULL,
+ url VARCHAR(255) NOT NULL,
+ PRIMARY KEY(idsite, url)
+ ) ENGINE=$engine DEFAULT CHARSET=utf8
+ ",
+
+ 'goal' => "CREATE TABLE `{$prefixTables}goal` (
+ `idsite` int(11) NOT NULL,
+ `idgoal` int(11) NOT NULL,
+ `name` varchar(50) NOT NULL,
+ `match_attribute` varchar(20) NOT NULL,
+ `pattern` varchar(255) NOT NULL,
+ `pattern_type` varchar(10) NOT NULL,
+ `case_sensitive` tinyint(4) NOT NULL,
+ `allow_multiple` tinyint(4) NOT NULL,
+ `revenue` float NOT NULL,
+ `deleted` tinyint(4) NOT NULL default '0',
+ PRIMARY KEY (`idsite`,`idgoal`)
+ ) ENGINE=$engine DEFAULT CHARSET=utf8
+ ",
+
+ 'logger_message' => "CREATE TABLE {$prefixTables}logger_message (
+ idlogger_message INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
tag VARCHAR(50) NULL,
- timestamp TIMESTAMP NULL,
+ timestamp TIMESTAMP NULL,
level VARCHAR(16) NULL,
- message TEXT NULL,
- PRIMARY KEY(idlogger_message)
- ) ENGINE=$engine DEFAULT CHARSET=utf8
- ",
-
- 'log_action' => "CREATE TABLE {$prefixTables}log_action (
- idaction INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- name TEXT,
- hash INTEGER(10) UNSIGNED NOT NULL,
- type TINYINT UNSIGNED NULL,
- url_prefix TINYINT(2) NULL,
- PRIMARY KEY(idaction),
- INDEX index_type_hash (type, hash)
- ) ENGINE=$engine DEFAULT CHARSET=utf8
- ",
-
- 'log_visit' => "CREATE TABLE {$prefixTables}log_visit (
- idvisit INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- idsite INTEGER(10) UNSIGNED NOT NULL,
- idvisitor BINARY(8) NOT NULL,
- visit_last_action_time DATETIME NOT NULL,
- config_id BINARY(8) NOT NULL,
- location_ip VARBINARY(16) NOT NULL,
- PRIMARY KEY(idvisit),
- INDEX index_idsite_config_datetime (idsite, config_id, visit_last_action_time),
- INDEX index_idsite_datetime (idsite, visit_last_action_time),
- INDEX index_idsite_idvisitor (idsite, idvisitor)
- ) ENGINE=$engine DEFAULT CHARSET=utf8
- ",
+ message TEXT NULL,
+ PRIMARY KEY(idlogger_message)
+ ) ENGINE=$engine DEFAULT CHARSET=utf8
+ ",
+
+ 'log_action' => "CREATE TABLE {$prefixTables}log_action (
+ idaction INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
+ name TEXT,
+ hash INTEGER(10) UNSIGNED NOT NULL,
+ type TINYINT UNSIGNED NULL,
+ url_prefix TINYINT(2) NULL,
+ PRIMARY KEY(idaction),
+ INDEX index_type_hash (type, hash)
+ ) ENGINE=$engine DEFAULT CHARSET=utf8
+ ",
+
+ 'log_visit' => "CREATE TABLE {$prefixTables}log_visit (
+ idvisit INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
+ idsite INTEGER(10) UNSIGNED NOT NULL,
+ idvisitor BINARY(8) NOT NULL,
+ visit_last_action_time DATETIME NOT NULL,
+ config_id BINARY(8) NOT NULL,
+ location_ip VARBINARY(16) NOT NULL,
+ PRIMARY KEY(idvisit),
+ INDEX index_idsite_config_datetime (idsite, config_id, visit_last_action_time),
+ INDEX index_idsite_datetime (idsite, visit_last_action_time),
+ INDEX index_idsite_idvisitor (idsite, idvisitor)
+ ) ENGINE=$engine DEFAULT CHARSET=utf8
+ ",
'log_conversion_item' => "CREATE TABLE `{$prefixTables}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,
- idaction_category2 INTEGER(10) UNSIGNED NOT NULL,
- idaction_category3 INTEGER(10) UNSIGNED NOT NULL,
- idaction_category4 INTEGER(10) UNSIGNED NOT NULL,
- idaction_category5 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 )
- ) ENGINE=$engine DEFAULT CHARSET=utf8
- ",
-
- 'log_conversion' => "CREATE TABLE `{$prefixTables}log_conversion` (
- idvisit int(10) unsigned NOT NULL,
- idsite int(10) unsigned NOT NULL,
- idvisitor BINARY(8) NOT NULL,
- server_time datetime NOT NULL,
- idaction_url int(11) default NULL,
- idlink_va int(11) default NULL,
- idgoal int(10) NOT NULL,
- buster int unsigned NOT NULL,
- idorder varchar(100) default NULL,
- items SMALLINT UNSIGNED DEFAULT NULL,
- url text NOT NULL,
-
- PRIMARY KEY (idvisit, idgoal, buster),
- UNIQUE KEY unique_idsite_idorder (idsite, idorder),
- INDEX index_idsite_datetime ( idsite, server_time )
- ) ENGINE=$engine DEFAULT CHARSET=utf8
- ",
+ 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,
+ idaction_category2 INTEGER(10) UNSIGNED NOT NULL,
+ idaction_category3 INTEGER(10) UNSIGNED NOT NULL,
+ idaction_category4 INTEGER(10) UNSIGNED NOT NULL,
+ idaction_category5 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 )
+ ) ENGINE=$engine DEFAULT CHARSET=utf8
+ ",
+
+ 'log_conversion' => "CREATE TABLE `{$prefixTables}log_conversion` (
+ idvisit int(10) unsigned NOT NULL,
+ idsite int(10) unsigned NOT NULL,
+ idvisitor BINARY(8) NOT NULL,
+ server_time datetime NOT NULL,
+ idaction_url int(11) default NULL,
+ idlink_va int(11) default NULL,
+ idgoal int(10) NOT NULL,
+ buster int unsigned NOT NULL,
+ idorder varchar(100) default NULL,
+ items SMALLINT UNSIGNED DEFAULT NULL,
+ url text NOT NULL,
+ PRIMARY KEY (idvisit, idgoal, buster),
+ UNIQUE KEY unique_idsite_idorder (idsite, idorder),
+ INDEX index_idsite_datetime ( idsite, server_time )
+ ) ENGINE=$engine DEFAULT CHARSET=utf8
+ ",
'log_link_visit_action' => "CREATE TABLE {$prefixTables}log_link_visit_action (
- idlink_va INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
- idsite int(10) UNSIGNED NOT NULL,
- idvisitor BINARY(8) NOT NULL,
- idvisit INTEGER(10) UNSIGNED NOT NULL,
- idaction_url_ref INTEGER(10) UNSIGNED NULL DEFAULT 0,
- idaction_name_ref INTEGER(10) UNSIGNED NOT NULL,
-
- custom_float FLOAT NULL DEFAULT NULL,
- PRIMARY KEY(idlink_va),
- INDEX index_idvisit(idvisit)
- ) ENGINE=$engine DEFAULT CHARSET=utf8
- ",
-
- 'log_profiling' => "CREATE TABLE {$prefixTables}log_profiling (
- query TEXT NOT NULL,
- count INTEGER UNSIGNED NULL,
- sum_time_ms FLOAT NULL,
- UNIQUE KEY query(query(100))
- ) ENGINE=$engine DEFAULT CHARSET=utf8
- ",
-
- 'option' => "CREATE TABLE `{$prefixTables}option` (
- option_name VARCHAR( 255 ) NOT NULL,
- option_value LONGTEXT NOT NULL,
- autoload TINYINT NOT NULL DEFAULT '1',
- PRIMARY KEY ( option_name ),
- INDEX autoload( autoload )
- ) ENGINE=$engine DEFAULT CHARSET=utf8
- ",
-
- 'session' => "CREATE TABLE {$prefixTables}session (
- id VARCHAR( 255 ) NOT NULL,
- modified INTEGER,
- lifetime INTEGER,
- data TEXT,
- PRIMARY KEY ( id )
- ) ENGINE=$engine DEFAULT CHARSET=utf8
- ",
-
- 'archive_numeric' => "CREATE TABLE {$prefixTables}archive_numeric (
- idarchive INTEGER UNSIGNED NOT NULL,
- name VARCHAR(255) NOT NULL,
- idsite INTEGER UNSIGNED NULL,
- date1 DATE NULL,
- date2 DATE NULL,
- period TINYINT UNSIGNED NULL,
- ts_archived DATETIME NULL,
- value DOUBLE NULL,
- PRIMARY KEY(idarchive, name),
- INDEX index_idsite_dates_period(idsite, date1, date2, period, ts_archived),
- INDEX index_period_archived(period, ts_archived)
- ) ENGINE=$engine DEFAULT CHARSET=utf8
- ",
-
- 'archive_blob' => "CREATE TABLE {$prefixTables}archive_blob (
- idarchive INTEGER UNSIGNED NOT NULL,
- name VARCHAR(255) NOT NULL,
- idsite INTEGER UNSIGNED NULL,
- date1 DATE NULL,
- date2 DATE NULL,
- period TINYINT UNSIGNED NULL,
- ts_archived DATETIME NULL,
- value MEDIUMBLOB NULL,
- PRIMARY KEY(idarchive, name),
- INDEX index_period_archived(period, ts_archived)
- ) ENGINE=$engine DEFAULT CHARSET=utf8
- ",
+ idlink_va INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
+ idsite int(10) UNSIGNED NOT NULL,
+ idvisitor BINARY(8) NOT NULL,
+ idvisit INTEGER(10) UNSIGNED NOT NULL,
+ idaction_url_ref INTEGER(10) UNSIGNED NULL DEFAULT 0,
+ idaction_name_ref INTEGER(10) UNSIGNED NOT NULL,
+ custom_float FLOAT NULL DEFAULT NULL,
+ PRIMARY KEY(idlink_va),
+ INDEX index_idvisit(idvisit)
+ ) ENGINE=$engine DEFAULT CHARSET=utf8
+ ",
+
+ 'log_profiling' => "CREATE TABLE {$prefixTables}log_profiling (
+ query TEXT NOT NULL,
+ count INTEGER UNSIGNED NULL,
+ sum_time_ms FLOAT NULL,
+ UNIQUE KEY query(query(100))
+ ) ENGINE=$engine DEFAULT CHARSET=utf8
+ ",
+
+ 'option' => "CREATE TABLE `{$prefixTables}option` (
+ option_name VARCHAR( 255 ) NOT NULL,
+ option_value LONGTEXT NOT NULL,
+ autoload TINYINT NOT NULL DEFAULT '1',
+ PRIMARY KEY ( option_name ),
+ INDEX autoload( autoload )
+ ) ENGINE=$engine DEFAULT CHARSET=utf8
+ ",
+
+ 'session' => "CREATE TABLE {$prefixTables}session (
+ id VARCHAR( 255 ) NOT NULL,
+ modified INTEGER,
+ lifetime INTEGER,
+ data TEXT,
+ PRIMARY KEY ( id )
+ ) ENGINE=$engine DEFAULT CHARSET=utf8
+ ",
+
+ 'archive_numeric' => "CREATE TABLE {$prefixTables}archive_numeric (
+ idarchive INTEGER UNSIGNED NOT NULL,
+ name VARCHAR(255) NOT NULL,
+ idsite INTEGER UNSIGNED NULL,
+ date1 DATE NULL,
+ date2 DATE NULL,
+ period TINYINT UNSIGNED NULL,
+ ts_archived DATETIME NULL,
+ value DOUBLE NULL,
+ PRIMARY KEY(idarchive, name),
+ INDEX index_idsite_dates_period(idsite, date1, date2, period, ts_archived),
+ INDEX index_period_archived(period, ts_archived)
+ ) ENGINE=$engine DEFAULT CHARSET=utf8
+ ",
+
+ 'archive_blob' => "CREATE TABLE {$prefixTables}archive_blob (
+ idarchive INTEGER UNSIGNED NOT NULL,
+ name VARCHAR(255) NOT NULL,
+ idsite INTEGER UNSIGNED NULL,
+ date1 DATE NULL,
+ date2 DATE NULL,
+ period TINYINT UNSIGNED NULL,
+ ts_archived DATETIME NULL,
+ value MEDIUMBLOB NULL,
+ PRIMARY KEY(idarchive, name),
+ INDEX index_period_archived(period, ts_archived)
+ ) ENGINE=$engine DEFAULT CHARSET=utf8
+ ",
);
+
return $tables;
}
@@ -297,17 +296,17 @@ class Mysql implements SchemaInterface
*/
public function getTablesNames()
{
- $aTables = array_keys($this->getTablesCreateSql());
+ $aTables = array_keys($this->getTablesCreateSql());
$prefixTables = $this->getTablePrefix();
+
$return = array();
foreach ($aTables as $table) {
$return[] = $prefixTables . $table;
}
+
return $return;
}
- private $tablesInstalled = null;
-
/**
* Get list of installed columns in a table
*
@@ -317,7 +316,7 @@ class Mysql implements SchemaInterface
*/
public function getTableColumns($tableName)
{
- $db = Db::get();
+ $db = $this->getDb();
$allColumns = $db->fetchAll("SHOW COLUMNS FROM . $tableName");
@@ -340,7 +339,8 @@ class Mysql implements SchemaInterface
if (is_null($this->tablesInstalled)
|| $forceReload === true
) {
- $db = Db::get();
+
+ $db = $this->getDb();
$prefixTables = $this->getTablePrefixEscaped();
$allTables = $this->getAllExistingTables($prefixTables);
@@ -353,12 +353,13 @@ class Mysql implements SchemaInterface
// at this point we have the static list of core tables, but let's add the monthly archive tables
$allArchiveNumeric = $db->fetchCol("SHOW TABLES LIKE '" . $prefixTables . "archive_numeric%'");
- $allArchiveBlob = $db->fetchCol("SHOW TABLES LIKE '" . $prefixTables . "archive_blob%'");
+ $allArchiveBlob = $db->fetchCol("SHOW TABLES LIKE '" . $prefixTables . "archive_blob%'");
$allTablesReallyInstalled = array_merge($tablesInstalled, $allArchiveNumeric, $allArchiveBlob);
$this->tablesInstalled = $allTablesReallyInstalled;
}
+
return $this->tablesInstalled;
}
@@ -382,6 +383,7 @@ class Mysql implements SchemaInterface
if (is_null($dbName)) {
$dbName = $this->getDbName();
}
+
Db::exec("CREATE DATABASE IF NOT EXISTS " . $dbName . " DEFAULT CHARACTER SET utf8");
}
@@ -405,7 +407,7 @@ class Mysql implements SchemaInterface
} catch (Exception $e) {
// mysql code error 1050:table already exists
// see bug #153 https://github.com/piwik/piwik/issues/153
- if (!Db::get()->isErrNo($e, '1050')) {
+ if (!$this->getDb()->isErrNo($e, '1050')) {
throw $e;
}
}
@@ -425,7 +427,7 @@ class Mysql implements SchemaInterface
*/
public function createTables()
{
- $db = Db::get();
+ $db = $this->getDb();
$prefixTables = $this->getTablePrefix();
$tablesAlreadyInstalled = $this->getTablesInstalled();
@@ -448,9 +450,9 @@ class Mysql implements SchemaInterface
{
// The anonymous user is the user that is assigned by default
// note that the token_auth value is anonymous, which is assigned by default as well in the Login plugin
- $db = Db::get();
+ $db = $this->getDb();
$db->query("INSERT IGNORE INTO " . Common::prefixTable("user") . "
- VALUES ( 'anonymous', '', 'anonymous', 'anonymous@example.org', 'anonymous', 0, '" . Date::factory('now')->getDatetime() . "' );");
+ VALUES ( 'anonymous', '', 'anonymous', 'anonymous@example.org', 'anonymous', 0, '" . Date::factory('now')->getDatetime() . "' );");
}
/**
@@ -466,7 +468,7 @@ class Mysql implements SchemaInterface
private function getTablePrefix()
{
- $dbInfos = Db::getDatabaseConfig();
+ $dbInfos = Db::getDatabaseConfig();
$prefixTables = $dbInfos['tables_prefix'];
return $prefixTables;
@@ -475,10 +477,15 @@ class Mysql implements SchemaInterface
private function getTableEngine()
{
$dbInfos = Db::getDatabaseConfig();
- $engine = $dbInfos['type'];
+ $engine = $dbInfos['type'];
+
return $engine;
}
+ private function getDb(){
+ return Db::get();
+ }
+
private function getDbName()
{
$dbInfos = Db::getDatabaseConfig();
diff --git a/core/Segment.php b/core/Segment.php
index a0952f3bd7..420946794d 100644
--- a/core/Segment.php
+++ b/core/Segment.php
@@ -100,7 +100,7 @@ class Segment
// As a preventive measure, we restrict the filter size to a safe limit
$string = substr($string, 0, self::SEGMENT_TRUNCATE_LIMIT);
- $this->string = $string;
+ $this->string = $string;
$this->idSites = $idSites;
$segment = new SegmentExpression($string);
$this->segment = $segment;
@@ -118,6 +118,7 @@ class Segment
$expression[SegmentExpression::INDEX_OPERAND] = $cleanedExpression;
$cleanedExpressions[] = $expression;
}
+
$segment->setSubExpressionsAfterCleanup($cleanedExpressions);
}
diff --git a/core/SegmentExpression.php b/core/SegmentExpression.php
index 682aa201d0..cbd2a3eb9e 100644
--- a/core/SegmentExpression.php
+++ b/core/SegmentExpression.php
@@ -84,7 +84,7 @@ class SegmentExpression
}
$leftMember = $matches[1];
- $operation = $matches[2];
+ $operation = $matches[2];
$valueRightMember = urldecode($matches[3]);
// is null / is not null
@@ -138,6 +138,7 @@ class SegmentExpression
if ($operand[1] !== null) {
$this->valuesBind[] = $operand[1];
}
+
$operand = $operand[0];
$sqlSubExpressions[] = array(
self::INDEX_BOOL_OPERATOR => $operator,
@@ -161,9 +162,9 @@ class SegmentExpression
*/
protected function getSqlMatchFromDefinition($def, &$availableTables)
{
- $field = $def[0];
+ $field = $def[0];
$matchType = $def[1];
- $value = $def[2];
+ $value = $def[2];
$alsoMatchNULLValues = false;
switch ($matchType) {
@@ -188,22 +189,22 @@ class SegmentExpression
break;
case self::MATCH_CONTAINS:
$sqlMatch = 'LIKE';
- $value = '%' . $this->escapeLikeString($value) . '%';
+ $value = '%' . $this->escapeLikeString($value) . '%';
break;
case self::MATCH_DOES_NOT_CONTAIN:
$sqlMatch = 'NOT LIKE';
- $value = '%' . $this->escapeLikeString($value) . '%';
+ $value = '%' . $this->escapeLikeString($value) . '%';
$alsoMatchNULLValues = true;
break;
case self::MATCH_IS_NOT_NULL_NOR_EMPTY:
$sqlMatch = 'IS NOT NULL AND (' . $field . ' <> \'\' OR ' . $field . ' = 0)';
- $value = null;
+ $value = null;
break;
case self::MATCH_IS_NULL_OR_EMPTY:
$sqlMatch = 'IS NULL OR ' . $field . ' = \'\' ';
- $value = null;
+ $value = null;
break;
case self::MATCH_ACTIONS_CONTAINS:
@@ -212,7 +213,7 @@ class SegmentExpression
// it can be used internally to inject sub-expressions into the query.
// see Segment::getCleanedExpression()
$sqlMatch = 'IN (' . $value['SQL'] . ')';
- $value = $this->escapeLikeString($value['bind']);
+ $value = $this->escapeLikeString($value['bind']);
break;
default:
throw new Exception("Filter contains the match type '" . $matchType . "' which is not supported");
diff --git a/core/Tracker/Action.php b/core/Tracker/Action.php
index 5fba5a2862..6df6f64cfc 100644
--- a/core/Tracker/Action.php
+++ b/core/Tracker/Action.php
@@ -351,9 +351,8 @@ abstract class Action
$visitAction = array_merge($visitAction, $customVariables);
- $this->recordAction($visitAction);
+ $this->idLinkVisitAction = $this->getModel()->createAction($visitAction);
- $this->idLinkVisitAction = Tracker::getDatabase()->lastInsertId();
$visitAction['idlink_va'] = $this->idLinkVisitAction;
Common::printDebug("Inserted new action:");
@@ -384,14 +383,9 @@ abstract class Action
return true;
}
- private function recordAction($visitAction)
+ private function getModel()
{
- $fields = implode(", ", array_keys($visitAction));
- $bind = array_values($visitAction);
- $values = Common::getSqlStringFieldsArray($visitAction);
-
- $sql = "INSERT INTO " . Common::prefixTable('log_link_visit_action') . " ($fields) VALUES ($values)";
- Tracker::getDatabase()->query($sql, $bind);
+ return new Model();
}
/**
diff --git a/core/Tracker/GoalManager.php b/core/Tracker/GoalManager.php
index 7823b88cc2..69639fd1c6 100644
--- a/core/Tracker/GoalManager.php
+++ b/core/Tracker/GoalManager.php
@@ -330,12 +330,7 @@ class GoalManager
$conversion['items'] = $itemsCount;
if ($this->isThereExistingCartInVisit) {
- $updateWhere = array(
- 'idvisit' => $visitInformation['idvisit'],
- 'idgoal' => self::IDGOAL_CART,
- 'buster' => 0,
- );
- $recorded = $this->updateExistingConversion($conversion, $updateWhere);
+ $recorded = $this->getModel()->updateConversion($visitInformation['idvisit'], self::IDGOAL_CART, $conversion);
} else {
$recorded = $this->insertNewConversion($conversion, $visitInformation);
}
@@ -398,20 +393,8 @@ class GoalManager
$itemInCartBySku[$item[0]] = $item;
}
- // Select all items currently in the Cart if any
- $sql = "SELECT idaction_sku, idaction_name, idaction_category, idaction_category2, idaction_category3, idaction_category4, idaction_category5, price, quantity, deleted, idorder as idorder_original_value
- FROM " . Common::prefixTable('log_conversion_item') . "
- WHERE idvisit = ? AND (idorder = ? OR idorder = ?)";
-
- $bind = array($goal['idvisit'],
- isset($goal['idorder']) ? $goal['idorder'] : self::ITEM_IDORDER_ABANDONED_CART,
- self::ITEM_IDORDER_ABANDONED_CART
- );
-
- $itemsInDb = Tracker::getDatabase()->fetchAll($sql, $bind);
+ $itemsInDb = $this->getModel()->getAllItemsCurrentlyInTheCart($goal, self::ITEM_IDORDER_ABANDONED_CART);
- Common::printDebug("Items found in current cart, for conversion_item (visit,idorder)=" . var_export($bind, true));
- Common::printDebug($itemsInDb);
// Look at which items need to be deleted, which need to be added or updated, based on the SKU
$skuFoundInDb = $itemsToUpdate = array();
@@ -601,20 +584,16 @@ class GoalManager
foreach ($itemsToUpdate as $item) {
$newRow = $this->getItemRowEnriched($goal, $item);
Common::printDebug($newRow);
- $updateParts = $sqlBind = array();
- foreach ($newRow as $name => $value) {
- $updateParts[] = $name . " = ?";
- $sqlBind[] = $value;
- }
- $sql = 'UPDATE ' . Common::prefixTable('log_conversion_item') . " SET " . implode($updateParts, ', ') . "
- WHERE idvisit = ? AND idorder = ? AND idaction_sku = ?";
- $sqlBind[] = $newRow['idvisit'];
- $sqlBind[] = $item['idorder_original_value'];
- $sqlBind[] = $newRow['idaction_sku'];
- Tracker::getDatabase()->query($sql, $sqlBind);
+
+ $this->getModel()->updateEcommerceItem($item['idorder_original_value'], $newRow);
}
}
+ private function getModel()
+ {
+ return new Model();
+ }
+
/**
* Inserts in the cart in the DB the new items
* that were not previously in the cart
@@ -633,26 +612,13 @@ class GoalManager
Common::printDebug("Ecommerce items that are added to the cart/order");
Common::printDebug($itemsToInsert);
- $sql = "INSERT INTO " . Common::prefixTable('log_conversion_item') . "
- (idaction_sku, idaction_name, idaction_category, idaction_category2, idaction_category3, idaction_category4, idaction_category5, price, quantity, deleted,
- idorder, idsite, idvisitor, server_time, idvisit)
- VALUES ";
- $i = 0;
- $bind = array();
+ $items = array();
foreach ($itemsToInsert as $item) {
- if ($i > 0) {
- $sql .= ',';
- }
- $newRow = array_values($this->getItemRowEnriched($goal, $item));
- $sql .= " ( " . Common::getSqlStringFieldsArray($newRow) . " ) ";
- $i++;
- $bind = array_merge($bind, $newRow);
+ $items[] = $this->getItemRowEnriched($goal, $item);
}
- Tracker::getDatabase()->query($sql, $bind);
- Common::printDebug($sql);
- Common::printDebug($bind);
+ $this->getModel()->createEcommerceItems($items);
}
protected function getItemRowEnriched($goal, $item)
@@ -757,15 +723,9 @@ class GoalManager
$newGoalDebug['idvisitor'] = bin2hex($newGoalDebug['idvisitor']);
Common::printDebug($newGoalDebug);
- $fields = implode(", ", array_keys($conversion));
- $bindFields = Common::getSqlStringFieldsArray($conversion);
+ $wasInserted = $this->getModel()->createConversion($conversion);
- $sql = 'INSERT IGNORE INTO ' . Common::prefixTable('log_conversion') . " ($fields) VALUES ($bindFields) ";
- $bind = array_values($conversion);
- $result = Tracker::getDatabase()->query($sql, $bind);
-
- // If a record was inserted, we return true
- return Tracker::getDatabase()->rowCount($result) > 0;
+ return $wasInserted;
}
/**
@@ -788,35 +748,6 @@ class GoalManager
);
}
- protected function updateExistingConversion($newGoal, $updateWhere)
- {
- $updateParts = $sqlBind = $updateWhereParts = array();
-
- foreach ($newGoal as $name => $value) {
- $updateParts[] = $name . " = ?";
- $sqlBind[] = $value;
- }
-
- foreach ($updateWhere as $name => $value) {
- $updateWhereParts[] = $name . " = ?";
- $sqlBind[] = $value;
- }
-
- $table = Common::prefixTable('log_conversion');
- $parts = implode($updateParts, ', ');
- $sql = 'UPDATE ' . $table . " SET " . $parts . " WHERE " . implode($updateWhereParts, ' AND ');
-
- try {
- Tracker::getDatabase()->query($sql, $sqlBind);
- } catch(Exception $e){
- Common::printDebug("There was an error while updating the Conversion: " . $e->getMessage());
-
- return false;
- }
-
- return true;
- }
-
/**
* @param $goal
* @param $pattern_type
diff --git a/core/Tracker/Model.php b/core/Tracker/Model.php
new file mode 100644
index 0000000000..090cd8be59
--- /dev/null
+++ b/core/Tracker/Model.php
@@ -0,0 +1,383 @@
+<?php
+/**
+ * Piwik - free/libre analytics platform
+ *
+ * @link http://piwik.org
+ * @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
+ *
+ */
+namespace Piwik\Tracker;
+
+use Exception;
+use PDOStatement;
+use Piwik\Common;
+use Piwik\Tracker;
+use Piwik\Tracker\Db\DbException;
+
+class Model
+{
+
+ public function createAction($visitAction)
+ {
+ $fields = implode(", ", array_keys($visitAction));
+ $values = Common::getSqlStringFieldsArray($visitAction);
+ $table = Common::prefixTable('log_link_visit_action');
+
+ $sql = "INSERT INTO $table ($fields) VALUES ($values)";
+ $bind = array_values($visitAction);
+
+ $db = $this->getDb();
+ $db->query($sql, $bind);
+
+ $id = $db->lastInsertId();
+
+ return $id;
+ }
+
+ public function createConversion($conversion)
+ {
+ $fields = implode(", ", array_keys($conversion));
+ $bindFields = Common::getSqlStringFieldsArray($conversion);
+ $table = Common::prefixTable('log_conversion');
+
+ $sql = "INSERT IGNORE INTO $table ($fields) VALUES ($bindFields) ";
+ $bind = array_values($conversion);
+
+ $db = $this->getDb();
+ $result = $db->query($sql, $bind);
+
+ // If a record was inserted, we return true
+ return $db->rowCount($result) > 0;
+ }
+
+ public function updateConversion($idVisit, $idGoal, $newConversion)
+ {
+ $updateWhere = array(
+ 'idvisit' => $idVisit,
+ 'idgoal' => $idGoal,
+ 'buster' => 0,
+ );
+
+ $updateParts = $sqlBind = $updateWhereParts = array();
+
+ foreach ($newConversion as $name => $value) {
+ $updateParts[] = $name . " = ?";
+ $sqlBind[] = $value;
+ }
+
+ foreach ($updateWhere as $name => $value) {
+ $updateWhereParts[] = $name . " = ?";
+ $sqlBind[] = $value;
+ }
+
+ $parts = implode($updateParts, ', ');
+ $table = Common::prefixTable('log_conversion');
+
+ $sql = "UPDATE $table SET $parts WHERE " . implode($updateWhereParts, ' AND ');
+
+ try {
+ $this->getDb()->query($sql, $sqlBind);
+ } catch(Exception $e){
+ Common::printDebug("There was an error while updating the Conversion: " . $e->getMessage());
+
+ return false;
+ }
+
+ return true;
+ }
+
+
+ /**
+ * Loads the Ecommerce items from the request and records them in the DB
+ *
+ * @param array $goal
+ * @param int $defaultIdOrder
+ * @throws Exception
+ * @return array
+ */
+ public function getAllItemsCurrentlyInTheCart($goal, $defaultIdOrder)
+ {
+ $sql = "SELECT idaction_sku, idaction_name, idaction_category, idaction_category2, idaction_category3, idaction_category4, idaction_category5, price, quantity, deleted, idorder as idorder_original_value
+ FROM " . Common::prefixTable('log_conversion_item') . "
+ WHERE idvisit = ? AND (idorder = ? OR idorder = ?)";
+
+ $bind = array(
+ $goal['idvisit'],
+ isset($goal['idorder']) ? $goal['idorder'] : $defaultIdOrder,
+ $defaultIdOrder
+ );
+
+ $itemsInDb = $this->getDb()->fetchAll($sql, $bind);
+
+ Common::printDebug("Items found in current cart, for conversion_item (visit,idorder)=" . var_export($bind, true));
+ Common::printDebug($itemsInDb);
+
+ return $itemsInDb;
+ }
+
+ public function createEcommerceItems($ecommerceItems)
+ {
+ $sql = "INSERT INTO " . Common::prefixTable('log_conversion_item');
+ $i = 0;
+ $bind = array();
+
+ foreach ($ecommerceItems as $item) {
+
+ if ($i === 0) {
+ $fields = implode(', ', array_keys($item));
+ $sql .= ' (' . $fields . ') VALUES ';
+ } elseif ($i > 0) {
+ $sql .= ',';
+ }
+
+ $newRow = array_values($item);
+ $sql .= " ( " . Common::getSqlStringFieldsArray($newRow) . " ) ";
+ $bind = array_merge($bind, $newRow);
+ $i++;
+ }
+
+ $this->getDb()->query($sql, $bind);
+
+ Common::printDebug($sql);
+ Common::printDebug($bind);
+ }
+
+ public function createNewIdAction($name, $type, $urlPrefix)
+ {
+ $table = Common::prefixTable('log_action');
+ $sql = "INSERT INTO $table (name, hash, type, url_prefix) VALUES (?,CRC32(?),?,?)";
+
+ $db = $this->getDb();
+ $db->query($sql, array($name, $name, $type, $urlPrefix));
+
+ $actionId = $db->lastInsertId();
+
+ return $actionId;
+ }
+
+ private function getSqlSelectActionId()
+ {
+ $sql = "SELECT idaction, type, name FROM " . Common::prefixTable('log_action')
+ . " WHERE ( hash = CRC32(?) AND name = ? AND type = ? ) ";
+
+ return $sql;
+ }
+
+ public function getIdActionMatchingNameAndType($name, $type)
+ {
+ $sql = $this->getSqlSelectActionId();
+ $bind = array($name, $name, $type);
+
+ $idAction = $this->getDb()->fetchOne($sql, $bind);
+
+ return $idAction;
+ }
+
+ public function getIdsAction($actionsNameAndType)
+ {
+ $sql = $this->getSqlSelectActionId();
+ $bind = array();
+
+ $i = 0;
+ foreach ($actionsNameAndType as $actionNameType) {
+ $name = $actionNameType['name'];
+
+ if (empty($name)) {
+ continue;
+ }
+
+ if ($i > 0) {
+ $sql .= " OR ( hash = CRC32(?) AND name = ? AND type = ? ) ";
+ }
+
+ $bind[] = $name;
+ $bind[] = $name;
+ $bind[] = $actionNameType['type'];
+ $i++;
+ }
+
+ // Case URL & Title are empty
+ if (empty($bind)) {
+ return false;
+ }
+
+ $actionIds = $this->getDb()->fetchAll($sql, $bind);
+
+ return $actionIds;
+ }
+
+ public function updateEcommerceItem($originalIdOrder, $newItem)
+ {
+ $updateParts = $sqlBind = array();
+ foreach ($newItem as $name => $value) {
+ $updateParts[] = $name . " = ?";
+ $sqlBind[] = $value;
+ }
+
+ $parts = implode($updateParts, ', ');
+ $table = Common::prefixTable('log_conversion_item');
+
+ $sql = "UPDATE $table SET $parts WHERE idvisit = ? AND idorder = ? AND idaction_sku = ?";
+
+ $sqlBind[] = $newItem['idvisit'];
+ $sqlBind[] = $originalIdOrder;
+ $sqlBind[] = $newItem['idaction_sku'];
+
+ $this->getDb()->query($sql, $sqlBind);
+ }
+
+ public function createVisit($visit)
+ {
+ $fields = array_keys($visit);
+ $fields = implode(", ", $fields);
+ $values = Common::getSqlStringFieldsArray($visit);
+ $table = Common::prefixTable('log_visit');
+
+ $sql = "INSERT INTO $table ($fields) VALUES ($values)";
+ $bind = array_values($visit);
+
+ $db = $this->getDb();
+ $db->query($sql, $bind);
+
+ return $db->lastInsertId();
+ }
+
+ public function updateVisit($idSite, $idVisit, $valuesToUpdate)
+ {
+ list($updateParts, $sqlBind) = $this->visitFieldsToQuery($valuesToUpdate);
+
+ $parts = implode($updateParts, ', ');
+ $table = Common::prefixTable('log_visit');
+
+ $sqlQuery = "UPDATE $table SET $parts WHERE idsite = ? AND idvisit = ?";
+
+ $sqlBind[] = $idSite;
+ $sqlBind[] = $idVisit;
+
+ $db = $this->getDb();
+ $result = $db->query($sqlQuery, $sqlBind);
+ $wasInserted = $db->rowCount($result) != 0;
+
+ if (!$wasInserted) {
+ Common::printDebug("Visitor with this idvisit wasn't found in the DB.");
+ Common::printDebug("$sqlQuery --- ");
+ Common::printDebug($sqlBind);
+ }
+
+ return $wasInserted;
+ }
+
+ public function findVisitor($idSite, $configId, $idVisitor, $fieldsToRead, $numCustomVarsToRead, $shouldMatchOneFieldOnly, $isVisitorIdToLookup, $timeLookBack, $timeLookAhead)
+ {
+ $selectCustomVariables = '';
+
+ if ($numCustomVarsToRead) {
+ for ($index = 1; $index <= $numCustomVarsToRead; $index++) {
+ $selectCustomVariables .= ', custom_var_k' . $index . ', custom_var_v' . $index;
+ }
+ }
+
+ $selectFields = implode(', ', $fieldsToRead);
+
+ $select = "SELECT $selectFields $selectCustomVariables ";
+ $from = "FROM " . Common::prefixTable('log_visit');
+
+ // Two use cases:
+ // 1) there is no visitor ID so we try to match only on config_id (heuristics)
+ // Possible causes of no visitor ID: no browser cookie support, direct Tracking API request without visitor ID passed,
+ // importing server access logs with import_logs.py, etc.
+ // In this case we use config_id heuristics to try find the visitor in tahhhe past. There is a risk to assign
+ // this page view to the wrong visitor, but this is better than creating artificial visits.
+ // 2) there is a visitor ID and we trust it (config setting trust_visitors_cookies, OR it was set using &cid= in tracking API),
+ // and in these cases, we force to look up this visitor id
+ $whereCommon = "visit_last_action_time >= ? AND visit_last_action_time <= ? AND idsite = ?";
+ $bindSql = array(
+ $timeLookBack,
+ $timeLookAhead,
+ $idSite
+ );
+
+ if ($shouldMatchOneFieldOnly) {
+ if ($isVisitorIdToLookup) {
+ $whereCommon .= ' AND idvisitor = ?';
+ $bindSql[] = $idVisitor;
+ } else {
+ $whereCommon .= ' AND config_id = ?';
+ $bindSql[] = $configId;
+ }
+
+ $sql = "$select $from
+ WHERE " . $whereCommon . "
+ ORDER BY visit_last_action_time DESC
+ LIMIT 1";
+ } // We have a config_id AND a visitor_id. We match on either of these.
+ // Why do we also match on config_id?
+ // we do not trust the visitor ID only. Indeed, some browsers, or browser addons,
+ // cause the visitor id from the 1st party cookie to be different on each page view!
+ // It is not acceptable to create a new visit every time such browser does a page view,
+ // so we also backup by searching for matching config_id.
+ // We use a UNION here so that each sql query uses its own INDEX
+ else {
+ // will use INDEX index_idsite_config_datetime (idsite, config_id, visit_last_action_time)
+ $where = ' AND config_id = ? AND user_id IS NULL ';
+ $bindSql[] = $configId;
+ $sqlConfigId = "$select ,
+ 0 as priority
+ $from
+ WHERE $whereCommon $where
+ ORDER BY visit_last_action_time DESC
+ LIMIT 1
+ ";
+ // will use INDEX index_idsite_idvisitor (idsite, idvisitor)
+ $bindSql[] = $timeLookBack;
+ $bindSql[] = $timeLookAhead;
+ $bindSql[] = $idSite;
+ $where = ' AND idvisitor = ?';
+ $bindSql[] = $idVisitor;
+ $sqlVisitorId = "$select ,
+ 1 as priority
+ $from
+ WHERE $whereCommon $where
+ ORDER BY visit_last_action_time DESC
+ LIMIT 1
+ ";
+
+ // We join both queries and favor the one matching the visitor_id if it did match
+ $sql = " ( $sqlConfigId )
+ UNION
+ ( $sqlVisitorId )
+ ORDER BY priority DESC
+ LIMIT 1";
+ }
+
+ $visitRow = $this->getDb()->fetch($sql, $bindSql);
+
+ return $visitRow;
+ }
+
+ private function visitFieldsToQuery($valuesToUpdate)
+ {
+ $updateParts = array();
+ $sqlBind = array();
+
+ foreach ($valuesToUpdate as $name => $value) {
+ // Case where bind parameters don't work
+ if ($value === $name . ' + 1') {
+ //$name = 'visit_total_events'
+ //$value = 'visit_total_events + 1';
+ $updateParts[] = " $name = $value ";
+ } else {
+ $updateParts[] = $name . " = ?";
+ $sqlBind[] = $value;
+ }
+ }
+
+ return array($updateParts, $sqlBind);
+ }
+
+ private function getDb()
+ {
+ return Tracker::getDatabase();
+ }
+
+}
diff --git a/core/Tracker/TableLogAction.php b/core/Tracker/TableLogAction.php
index 7ae99a535e..4f4849a352 100644
--- a/core/Tracker/TableLogAction.php
+++ b/core/Tracker/TableLogAction.php
@@ -52,20 +52,6 @@ class TableLogAction
}
/**
- * @param $name
- * @param $type
- * @return string
- */
- private static function getIdActionMatchingNameAndType($name, $type)
- {
- $sql = TableLogAction::getSqlSelectActionId();
- $bind = array($name, $name, $type);
- $idAction = \Piwik\Db::fetchOne($sql, $bind);
-
- return $idAction;
- }
-
- /**
* @param $matchType
* @param $actionType
* @return string
@@ -95,62 +81,39 @@ class TableLogAction
return $sql;
}
- private static function getSqlSelectActionId()
- {
- $sql = "SELECT idaction, type, name
- FROM " . Common::prefixTable('log_action')
- . " WHERE "
- . " ( hash = CRC32(?) AND name = ? AND type = ? ) ";
-
- return $sql;
- }
-
private static function insertNewIdsAction($actionsNameAndType, $fieldNamesToInsert)
{
- $sql = "INSERT INTO " . Common::prefixTable('log_action') .
- "( name, hash, type, url_prefix ) VALUES (?,CRC32(?),?,?)";
// Then, we insert all new actions in the lookup table
$inserted = array();
foreach ($fieldNamesToInsert as $fieldName) {
list($name, $type, $urlPrefix) = $actionsNameAndType[$fieldName];
- Tracker::getDatabase()->query($sql, array($name, $name, $type, $urlPrefix));
- $actionId = Tracker::getDatabase()->lastInsertId();
-
- $inserted[$fieldName] = $actionId;
+ $actionId = self::getModel()->createNewIdAction($name, $type, $urlPrefix);
Common::printDebug("Recorded a new action (" . Action::getTypeAsString($type) . ") in the lookup table: " . $name . " (idaction = " . $actionId . ")");
+
+ $inserted[$fieldName] = $actionId;
}
return $inserted;
}
- private static function queryIdsAction($actionsNameAndType)
+ private static function getModel()
{
- $sql = TableLogAction::getSqlSelectActionId();
- $bind = array();
+ return new Model();
+ }
- $i = 0;
+ private static function queryIdsAction($actionsNameAndType)
+ {
+ $toQuery = array();
foreach ($actionsNameAndType as &$actionNameType) {
list($name, $type, $urlPrefix) = $actionNameType;
- if (empty($name)) {
- continue;
- }
- if ($i > 0) {
- $sql .= " OR ( hash = CRC32(?) AND name = ? AND type = ? ) ";
- }
- $bind[] = $name;
- $bind[] = $name;
- $bind[] = $type;
- $i++;
- }
- // Case URL & Title are empty
- if (empty($bind)) {
- return false;
+ $toQuery[] = array('name' => $name, 'type' => $type);
}
- $actionIds = Tracker::getDatabase()->fetchAll($sql, $bind);
+ $actionIds = self::getModel()->getIdsAction($toQuery);
+
return $actionIds;
}
@@ -214,7 +177,7 @@ class TableLogAction
if ($matchType == SegmentExpression::MATCH_EQUAL
|| $matchType == SegmentExpression::MATCH_NOT_EQUAL
) {
- $idAction = self::getIdActionMatchingNameAndType($valueToMatch, $actionType);
+ $idAction = self::getModel()->getIdActionMatchingNameAndType($valueToMatch, $actionType);
// if the action is not found, we hack -100 to ensure it tries to match against an integer
// otherwise binding idaction_name to "false" returns some rows for some reasons (in case &segment=pageTitle==Větrnásssssss)
if (empty($idAction)) {
diff --git a/core/Tracker/Visit.php b/core/Tracker/Visit.php
index f01f34bc19..0ce45c3659 100644
--- a/core/Tracker/Visit.php
+++ b/core/Tracker/Visit.php
@@ -327,13 +327,18 @@ class Visit implements VisitInterface
$this->printVisitorInformation();
- $idVisit = $this->insertNewVisit( $this->visitorInfo );
+ $idVisit = $this->insertNewVisit($this->visitorInfo);
$this->setVisitorColumn($visitor, 'idvisit', $idVisit);
$this->setVisitorColumn($visitor, 'visit_first_action_time', $this->request->getCurrentTimestamp());
$this->setVisitorColumn($visitor, 'visit_last_action_time', $this->request->getCurrentTimestamp());
}
+ private function getModel()
+ {
+ return new Model();
+ }
+
/**
* Returns visitor cookie
*
@@ -427,42 +432,24 @@ class Visit implements VisitInterface
}
/**
- * @return mixed
- */
- protected function insertNewVisit($visit)
- {
- $fields = array_keys($visit);
- $fields = implode(", ", $fields);
- $values = Common::getSqlStringFieldsArray($visit);
-
- $sql = "INSERT INTO " . Common::prefixTable('log_visit') . " ($fields) VALUES ($values)";
- $bind = array_values($visit);
- Tracker::getDatabase()->query($sql, $bind);
-
- return Tracker::getDatabase()->lastInsertId();
- }
-
- /**
* @param $valuesToUpdate
* @throws VisitorNotFoundInDb
*/
protected function updateExistingVisit($valuesToUpdate)
{
- list($sqlQuery, $sqlBind) = $this->getUpdateExistingVisitQuery($valuesToUpdate);
+ $idSite = $this->request->getIdSite();
+ $idVisit = (int) $this->visitorInfo['idvisit'];
- $result = Tracker::getDatabase()->query($sqlQuery, $sqlBind);
+ $wasInserted = $this->getModel()->updateVisit($idSite, $idVisit, $valuesToUpdate);
// Debug output
if (isset($valuesToUpdate['idvisitor'])) {
$valuesToUpdate['idvisitor'] = bin2hex($valuesToUpdate['idvisitor']);
}
- Common::printDebug('Updating existing visit: ' . var_export($valuesToUpdate, true));
-
- if (Tracker::getDatabase()->rowCount($result) == 0) {
- Common::printDebug("Visitor with this idvisit wasn't found in the DB.");
- Common::printDebug("$sqlQuery --- ");
- Common::printDebug($sqlBind);
+ if ($wasInserted) {
+ Common::printDebug('Updated existing visit: ' . var_export($valuesToUpdate, true));
+ } else {
throw new VisitorNotFoundInDb(
"The visitor with idvisitor=" . bin2hex($this->visitorInfo['idvisitor']) . " and idvisit=" . $this->visitorInfo['idvisit']
. " wasn't found in the DB, we fallback to a new visitor");
@@ -569,42 +556,6 @@ class Visit implements VisitInterface
return $dimensions;
}
- private function fieldsToQuery($valuesToUpdate)
- {
- $updateParts = array();
- $sqlBind = array();
-
- foreach ($valuesToUpdate as $name => $value) {
- // Case where bind parameters don't work
- if ($value === $name . ' + 1') {
- //$name = 'visit_total_events'
- //$value = 'visit_total_events + 1';
- $updateParts[] = " $name = $value ";
- } else {
- $updateParts[] = $name . " = ?";
- $sqlBind[] = $value;
- }
- }
-
- return array($updateParts, $sqlBind);
- }
-
- private function getUpdateExistingVisitQuery($valuesToUpdate)
- {
- $sqlQuery = "UPDATE " . Common::prefixTable('log_visit') . " SET %s WHERE idsite = ? AND idvisit = ?";
-
- // build sql query
- list($updateParts, $sqlBind) = $this->fieldsToQuery($valuesToUpdate);
-
- $idSite = $this->request->getIdSite();
- $idVisit = (int) $this->visitorInfo['idvisit'];
-
- $sqlQuery = sprintf($sqlQuery, implode($updateParts, ', '));
- array_push($sqlBind, $idSite, $idVisit);
-
- return array($sqlQuery, $sqlBind);
- }
-
private function getVisitStandardLength()
{
return Config::getInstance()->Tracker['visit_standard_length'];
@@ -634,4 +585,9 @@ class Visit implements VisitInterface
}
return $valuesToUpdate;
}
+
+ protected function insertNewVisit($visit)
+ {
+ return $this->getModel()->createVisit($visit);
+ }
}
diff --git a/core/Tracker/Visitor.php b/core/Tracker/Visitor.php
index ec5dd6d279..afec61849e 100644
--- a/core/Tracker/Visitor.php
+++ b/core/Tracker/Visitor.php
@@ -25,9 +25,9 @@ class Visitor
public function __construct(Request $request, $configId, $visitorInfo = array(), $customVariables = null)
{
$this->request = $request;
+ $this->configId = $configId;
$this->visitorInfo = $visitorInfo;
$this->customVariables = $customVariables;
- $this->configId = $configId;
}
/**
@@ -41,120 +41,35 @@ class Visitor
{
$this->setIsVisitorKnown(false);
- $configId = $this->configId;
-
+ $configId = $this->configId;
+ $idSite = $this->request->getIdSite();
$idVisitor = $this->request->getVisitorId();
+
$isVisitorIdToLookup = !empty($idVisitor);
if ($isVisitorIdToLookup) {
$this->visitorInfo['idvisitor'] = $idVisitor;
- Common::printDebug("Matching visitors with: visitorId=" . bin2hex($this->visitorInfo['idvisitor']) . " OR configId=" . bin2hex($configId));
+ Common::printDebug("Matching visitors with: visitorId=" . bin2hex($idVisitor) . " OR configId=" . bin2hex($configId));
} else {
Common::printDebug("Visitor doesn't have the piwik cookie...");
}
- $selectCustomVariables = '';
- // No custom var were found in the request, so let's copy the previous one in a potential conversion later
+ $numCustomVarsToRead = 0;
if (!$this->customVariables) {
- $maxCustomVariables = CustomVariables::getMaxCustomVariables();
-
- for ($index = 1; $index <= $maxCustomVariables; $index++) {
- $selectCustomVariables .= ', custom_var_k' . $index . ', custom_var_v' . $index;
- }
+ // No custom var were found in the request, so let's copy the previous one in a potential conversion later
+ $numCustomVarsToRead = CustomVariables::getMaxCustomVariables();
}
- $persistedVisitAttributes = self::getVisitFieldsPersist();
- array_unshift($persistedVisitAttributes, 'visit_first_action_time');
- array_unshift($persistedVisitAttributes, 'visit_last_action_time');
- $persistedVisitAttributes = array_unique($persistedVisitAttributes);
-
- $selectFields = implode(", ", $persistedVisitAttributes);
-
- $select = "SELECT
- $selectFields
- $selectCustomVariables
- ";
- $from = "FROM " . Common::prefixTable('log_visit');
-
+ $persistedVisitAttributes = $this->getVisitFieldsPersist();
+ $shouldMatchOneFieldOnly = $this->shouldLookupOneVisitorFieldOnly($isVisitorIdToLookup);
list($timeLookBack, $timeLookAhead) = $this->getWindowLookupThisVisit();
- $shouldMatchOneFieldOnly = $this->shouldLookupOneVisitorFieldOnly($isVisitorIdToLookup);
-
- // Two use cases:
- // 1) there is no visitor ID so we try to match only on config_id (heuristics)
- // Possible causes of no visitor ID: no browser cookie support, direct Tracking API request without visitor ID passed,
- // importing server access logs with import_logs.py, etc.
- // In this case we use config_id heuristics to try find the visitor in tahhhe past. There is a risk to assign
- // this page view to the wrong visitor, but this is better than creating artificial visits.
- // 2) there is a visitor ID and we trust it (config setting trust_visitors_cookies, OR it was set using &cid= in tracking API),
- // and in these cases, we force to look up this visitor id
- $whereCommon = "visit_last_action_time >= ? AND visit_last_action_time <= ? AND idsite = ?";
- $bindSql = array(
- $timeLookBack,
- $timeLookAhead,
- $this->request->getIdSite()
- );
-
- if ($shouldMatchOneFieldOnly) {
- if ($isVisitorIdToLookup) {
- $whereCommon .= ' AND idvisitor = ?';
- $bindSql[] = $this->visitorInfo['idvisitor'];
- } else {
- $whereCommon .= ' AND config_id = ?';
- $bindSql[] = $configId;
- }
-
- $sql = "$select
- $from
- WHERE " . $whereCommon . "
- ORDER BY visit_last_action_time DESC
- LIMIT 1";
- } // We have a config_id AND a visitor_id. We match on either of these.
- // Why do we also match on config_id?
- // we do not trust the visitor ID only. Indeed, some browsers, or browser addons,
- // cause the visitor id from the 1st party cookie to be different on each page view!
- // It is not acceptable to create a new visit every time such browser does a page view,
- // so we also backup by searching for matching config_id.
- // We use a UNION here so that each sql query uses its own INDEX
- else {
- // will use INDEX index_idsite_config_datetime (idsite, config_id, visit_last_action_time)
- $where = ' AND config_id = ?
- AND user_id IS NULL ';
- $bindSql[] = $configId;
- $sqlConfigId = "$select ,
- 0 as priority
- $from
- WHERE $whereCommon $where
- ORDER BY visit_last_action_time DESC
- LIMIT 1
- ";
- // will use INDEX index_idsite_idvisitor (idsite, idvisitor)
- $bindSql[] = $timeLookBack;
- $bindSql[] = $timeLookAhead;
- $bindSql[] = $this->request->getIdSite();
- $where = ' AND idvisitor = ?';
- $bindSql[] = $this->visitorInfo['idvisitor'];
- $sqlVisitorId = "$select ,
- 1 as priority
- $from
- WHERE $whereCommon $where
- ORDER BY visit_last_action_time DESC
- LIMIT 1
- ";
-
- // We join both queries and favor the one matching the visitor_id if it did match
- $sql = " ( $sqlConfigId )
- UNION
- ( $sqlVisitorId )
- ORDER BY priority DESC
- LIMIT 1";
- }
-
- $visitRow = Tracker::getDatabase()->fetch($sql, $bindSql);
+ $model = $this->getModel();
+ $visitRow = $model->findVisitor($idSite, $configId, $idVisitor, $persistedVisitAttributes, $numCustomVarsToRead, $shouldMatchOneFieldOnly, $isVisitorIdToLookup, $timeLookBack, $timeLookAhead);
$isNewVisitForced = $this->request->getParam('new_visit');
$isNewVisitForced = !empty($isNewVisitForced);
- $enforceNewVisit = $isNewVisitForced || Config::getInstance()->Debug['tracker_always_new_visitor'];
+ $enforceNewVisit = $isNewVisitForced || Config::getInstance()->Debug['tracker_always_new_visitor'];
if (!$enforceNewVisit
&& $visitRow
@@ -162,17 +77,16 @@ class Visitor
) {
// These values will be used throughout the request
- foreach($persistedVisitAttributes as $field) {
+ foreach ($persistedVisitAttributes as $field) {
$this->visitorInfo[$field] = $visitRow[$field];
}
- $this->visitorInfo['visit_last_action_time'] = strtotime($visitRow['visit_last_action_time']);
+ $this->visitorInfo['visit_last_action_time'] = strtotime($visitRow['visit_last_action_time']);
$this->visitorInfo['visit_first_action_time'] = strtotime($visitRow['visit_first_action_time']);
// Custom Variables copied from Visit in potential later conversion
- if (!empty($selectCustomVariables)) {
- $maxCustomVariables = CustomVariables::getMaxCustomVariables();
- for ($i = 1; $i <= $maxCustomVariables; $i++) {
+ if (!empty($numCustomVarsToRead)) {
+ for ($i = 1; $i <= $numCustomVarsToRead; $i++) {
if (isset($visitRow['custom_var_k' . $i])
&& strlen($visitRow['custom_var_k' . $i])
) {
@@ -193,7 +107,6 @@ class Visitor
last action = " . date("r", $this->visitorInfo['visit_last_action_time']) . ",
first action = " . date("r", $this->visitorInfo['visit_first_action_time']) . ",
visit_goal_buyer' = " . $this->visitorInfo['visit_goal_buyer'] . ")");
- //Common::printDebug($this->visitorInfo);
} else {
Common::printDebug("The visitor was not matched with an existing visitor...");
}
@@ -228,7 +141,8 @@ class Visitor
protected function shouldLookupOneVisitorFieldOnly($isVisitorIdToLookup)
{
$isForcedUserIdMustMatch = (false !== $this->request->getForcedUserId());
- if($isForcedUserIdMustMatch) {
+
+ if ($isForcedUserIdMustMatch) {
// if &iud was set, we must try and match both idvisitor and config_id
return false;
}
@@ -236,18 +150,18 @@ class Visitor
// This setting would be enabled for Intranet websites, to ensure that visitors using all the same computer config, same IP
// are not counted as 1 visitor. In this case, we want to enforce and trust the visitor ID from the cookie.
$trustCookiesOnly = Config::getInstance()->Tracker['trust_visitors_cookies'];
- if($isVisitorIdToLookup && $trustCookiesOnly) {
+ if ($isVisitorIdToLookup && $trustCookiesOnly) {
return true;
}
// If a &cid= was set, we force to select this visitor (or create a new one)
$isForcedVisitorIdMustMatch = ($this->request->getForcedVisitorId() != null);
- if($isForcedVisitorIdMustMatch) {
+ if ($isForcedVisitorIdMustMatch) {
return true;
}
- if( !$isVisitorIdToLookup ) {
+ if (!$isVisitorIdToLookup ) {
return true;
}
@@ -257,7 +171,7 @@ class Visitor
/**
* @return array
*/
- public static function getVisitFieldsPersist()
+ private function getVisitFieldsPersist()
{
$fields = array(
'idvisitor',
@@ -307,6 +221,10 @@ class Visitor
*/
Piwik::postEvent('Tracker.getVisitFieldsToPersist', array(&$fields));
+ array_unshift($fields, 'visit_first_action_time');
+ array_unshift($fields, 'visit_last_action_time');
+ $fields = array_unique($fields);
+
return $fields;
}
@@ -343,4 +261,9 @@ class Visitor
{
return $this->visitorKnown = $isVisitorKnown;
}
+
+ private function getModel()
+ {
+ return new Model();
+ }
}
generated by cgit v1.2.3 (git 2.25.1) at 2024-07-28 10:11:42 +0300