/**
* @group Plugins
*/
public function test_AddAndGet_AnotherSegment()
{
$name = 'name';
$definition = 'searches>1,visitIp!=127.0.0.1';
$idSegment = API::getInstance()->add($name, $definition, $idSite = 1, $autoArchive = 1, $enabledAllUsers = 1);
$this->assertEquals($idSegment, 1);
// Testing get()
$segment = API::getInstance()->get($idSegment);
$expected = array('idsegment' => '1', 'name' => $name, 'definition' => $definition, 'login' => 'superUserLogin', 'enable_all_users' => '1', 'enable_only_idsite' => '1', 'auto_archive' => '1', 'ts_last_edit' => null, 'deleted' => '0');
unset($segment['ts_created']);
$this->assertEquals($segment, $expected);
// There is a segment to process for this particular site
$model = Factory::getModel('Piwik\\Plugins\\SegmentEditor');
$segments = $model->getSegmentsToAutoArchive($idSite);
unset($segments[0]['ts_created']);
$this->assertEquals($segments, array($expected));
// There is no segment to process for a non existing site
try {
$model->getSegmentsToAutoArchive(33);
$this->fail();
} catch (Exception $e) {
// expected
}
// There is no segment to process across all sites
$segments = $model->getSegmentsToAutoArchive($idSite = false);
$this->assertEquals($segments, array());
}
public function test_makeLogVisitsQueryString_whenSegment()
{
$model = Factory::getModel('Piwik\\Plugins\\Live');
list($sql, $bind) = $model->makeLogVisitsQueryString($idSite = 1, $period = 'month', $date = '2010-01-01', $segment = 'customVariablePageName1==Test', $offset = 0, $limit = 100, $visitorId = 'abc', $minTimestamp = false, $filterSortOrder = false);
$expectedSql = ' SELECT sub.* FROM
(
SELECT log_inner.*
FROM (
SELECT log_visit.*
FROM ' . Common::prefixTable('log_visit') . ' AS log_visit
LEFT JOIN ' . Common::prefixTable('log_link_visit_action') . ' AS log_link_visit_action
ON log_link_visit_action.idvisit = log_visit.idvisit
WHERE ( log_visit.idsite in (?)
AND log_visit.idvisitor = ?
AND log_visit.visit_last_action_time >= ?
AND log_visit.visit_last_action_time <= ? )
AND ( log_link_visit_action.custom_var_k1 = ? )
ORDER BY idsite, visit_last_action_time DESC
LIMIT 100
) AS log_inner
ORDER BY idsite, visit_last_action_time DESC
LIMIT 100
) AS sub
GROUP BY sub.idvisit
ORDER BY sub.visit_last_action_time DESC
';
$expectedBind = array('1', Common::hex2bin('abc'), '2010-01-01 00:00:00', '2010-02-01 00:00:00', 'Test');
$this->assertEquals(SegmentTest::removeExtraWhiteSpaces($expectedSql), SegmentTest::removeExtraWhiteSpaces($sql));
$this->assertEquals(SegmentTest::removeExtraWhiteSpaces($expectedBind), SegmentTest::removeExtraWhiteSpaces($bind));
}
public function insertAll($rows)
{
$generic = Factory::getGeneric();
$rowsSql = array();
foreach ($rows as $row) {
$values = array();
foreach ($row as $name => $value) {
if (is_null($value)) {
$values[] = 'NULL';
} else {
if (is_numeric($value)) {
$values[] = $value;
} else {
if (!ctype_print($value)) {
$values[] = $generic->bin2dbRawInsert($value);
} else {
if (is_bool($value)) {
$values[] = $value ? '1' : '0';
} else {
$values[] = "'{$value}'";
}
}
}
}
}
$rowsSql[] = "(" . implode(',', $values) . ")";
}
$sql = 'INSERT INTO ' . $this->table . ' VALUES ' . implode(',', $rowsSql);
$this->db->query($sql);
}
/**
* @param array $config
*/
public function __construct($config)
{
$this->config = $config;
$this->maxLifetime = ini_get('session.gc_maxlifetime');
$this->model = Factory::getModel(__NAMESPACE__);
$this->model->setConfig($config);
}
/**
* uses tracker db
*/
public function insertNew($goal)
{
$Generic = Factory::getGeneric($this->db);
// pg is throwing error when empty values are given for 'FLOAT' columns
if (empty($goal['revenue'])) {
unset($goal['revenue']);
}
if (empty($goal['revenue_subtotal'])) {
unset($goal['revenue_subtotal']);
}
if (empty($goal['revenue_tax'])) {
unset($goal['revenue_tax']);
}
if (empty($goal['revenue_shipping'])) {
unset($goal['revenue_shipping']);
}
if (empty($goal['revenue_discount'])) {
unset($goal['revenue_discount']);
}
$fields = implode(', ', array_keys($goal));
$bindFields = Common::getSqlStringFieldsArray($goal);
$goal['idvisitor'] = $Generic->bin2db($goal['idvisitor']);
$sql = 'INSERT INTO ' . $this->table . '( ' . $fields . ' ) ' . 'VALUES ( ' . $bindFields . ' ) ';
$bind = array_values($goal);
$result = $Generic->insertIgnore($sql, $bind);
return $result;
}
public function purgeUnused($dimensionMetadataProvider)
{
$this->dimensionMetadataProvider = $dimensionMetadataProvider;
// get current max visit ID in log tables w/ idaction references.
$maxIds = $this->getMaxIdsInLogTables();
$this->generic = Factory::getGeneric($this->db);
$this->createTempTable();
// do large insert (inserting everything before maxIds) w/o locking tables...
$this->insertActionsToKeep($maxIds, $deleteOlderThanMax = true);
// ... then do small insert w/ locked tables to minimize the amount of time tables are locked.
$this->generic->beginTransaction();
$this->lockLogTables($this->generic);
$this->insertActionsToKeep($maxIds, $deleteOlderThanMax = false);
// Mysql uses 'INSERT IGNORE' which is not available on pgsql 9.1.
// We can do the generic->insertIgnore, but checking for exception for every
// single query can be a time taking endeavour. To avoid, 'INSERT's are
// carried on normally. Before deleting unused actions, we will remove
// duplicate rows from the temporary table.
$this->deleteDuplicatesFromTempTable();
// delete before unlocking tables so there's no chance a new log row that references an
// unused action will be inserted.
$this->deleteUnusedActions();
// unlock the log tables. In pgsql, locks are released on commit/rollback
$this->generic->commit();
$this->generic = null;
}
public function updateConversions($values, $idvisit)
{
$Generic = Factory::getGeneric($this->db);
$binary_columns = array('idvisitor');
foreach ($binary_columns as $bc) {
if (array_key_exists($bc, $values)) {
$values[$bc] = $Generic->bin2db($values[$bc]);
}
}
$updateParts = $sqlBind = array();
foreach ($values as $name => $value) {
// Case where bind parameters don't work
if (strpos($value, $name) !== false) {
//$name = 'visit_total_events'
//$value = 'visit_total_events + 1';
$updateParts[] = " {$name} = {$value} ";
} else {
$updateParts[] = $name . " = ?";
$sqlBind[] = $value;
}
}
array_push($sqlBind, $idvisit);
$sql = 'UPDATE ' . $this->table . ' SET ' . implode(', ', $updateParts) . ' ' . 'WHERE idvisit = ?';
$result = $this->db->query($sql, $sqlBind);
}
public function __construct($processNewSegmentsFrom, Model $segmentEditorModel = null, Cache $segmentListCache = null, Date $now = null, LoggerInterface $logger = null)
{
$this->processNewSegmentsFrom = $processNewSegmentsFrom;
$this->segmentEditorModel = $segmentEditorModel ?: Factory::getModel('Piwik\\Plugins\\SegmentEditor');
$this->segmentListCache = $segmentListCache ?: new Transient();
$this->now = $now ?: Date::factory('now');
$this->logger = $logger ?: StaticContainer::get('Psr\\Log\\LoggerInterface');
}
public function getCronArchiveTokenAuth(&$tokens)
{
$model = Factory::getModel(__NAMESPACE__);
$superUsers = $model->getUsersHavingSuperUserAccess();
foreach ($superUsers as $superUser) {
$tokens[] = $superUser['token_auth'];
}
}
/**
* Constructor.
*
* @param ArchiveInvalidator $invalidator
* @param DuplicateActionRemover $duplicateActionRemover
* @param Actions $actionsAccess
* @param LoggerInterface $logger
*/
public function __construct(ArchiveInvalidator $invalidator = null, DuplicateActionRemover $duplicateActionRemover = null, Actions $actionsAccess = null, LoggerInterface $logger = null)
{
parent::__construct();
$this->archiveInvalidator = $invalidator ?: new ArchiveInvalidator();
$this->duplicateActionRemover = $duplicateActionRemover ?: Factory::getModel('Piwik\\Plugins\\CoreAdminHome\\Model', 'DuplicateActionRemover');
$this->actionsAccess = $actionsAccess ?: new Actions();
$this->logger = $logger ?: StaticContainer::get('Psr\\Log\\LoggerInterface');
}
protected function loadSettings()
{
$SiteSetting = Factory::getDAO('site_setting');
$settings = $SiteSetting->getByIdsite($this->idSite);
$flat = array();
foreach ($settings as $setting) {
$flat[$setting['setting_name']] = unserialize($setting['setting_value']);
}
return $flat;
}
/**
* Performs a batch insert into a specific table by iterating through the data
*
* NOTE: you should use tableInsertBatch() which will fallback to this function if LOAD DATA INFILE not available
*
* @param string $tableName PREFIXED table name! you must call Common::prefixTable() before passing the table name
* @param array $fields array of unquoted field names
* @param array $values array of data to be inserted
* @param bool $ignoreWhenDuplicate Ignore new rows that contain unique key values that duplicate old rows
*/
public static function tableInsertBatchIterate($tableName, $fields, $values, $ignoreWhenDuplicate = true)
{
$isArchive = strpos($tableName, 'archive');
if ($isArchive === false) {
$Generic = Factory::getGeneric();
$Generic->insertIgnoreBatch($tableName, $fields, $values, $ignoreWhenDuplicate);
} else {
// archive_blob_* tables need special handling for the "value" column
$Archive = Factory::getDAO('archive');
$Archive->insertIgnoreBatch($tableName, $fields, $values, $ignoreWhenDuplicate);
}
}
function __construct($processor)
{
parent::__construct($processor);
if ($processor->getParams()->getSite()->isEcommerceEnabled()) {
$this->maximumRowsInDataTableLevelZero = self::MAX_ROWS_WHEN_ECOMMERCE;
$this->maximumRowsInSubDataTable = self::MAX_ROWS_WHEN_ECOMMERCE;
} else {
$this->maximumRowsInDataTableLevelZero = Config::getInstance()->General['datatable_archiving_maximum_rows_custom_variables'];
$this->maximumRowsInSubDataTable = Config::getInstance()->General['datatable_archiving_maximum_rows_subtable_custom_variables'];
}
$this->Generic = Factory::getGeneric();
}
protected function invalidatedArchiveIdRows($archiveTable, $idSites)
{
$sql = "SELECT idsite, date1, date2, period, name,\n STRING_AGG(idarchive || '.' || value, ',' ORDER BY ts_archived DESC) as archives\n FROM {$archiveTable}\n WHERE name LIKE 'done%'\n AND value IN (" . ArchiveWriter::DONE_INVALIDATED . ',' . ArchiveWriter::DONE_OK . ',' . ArchiveWriter::DONE_OK_TEMPORARY . ")\n AND idsite IN (" . implode(',', $idSites) . ")\n GROUP BY idsite, date1, date2, period, name";
$rows = $this->db->fetchAll($sql);
if ($this->isBlob($archiveTable)) {
$Generic = Factory::getGeneric();
foreach ($rows as &$row) {
$parts = explode('.', $row['archives']);
$row['archives'] = $parts[0] . '.' . $Generic->db2bin($parts[1]);
}
}
return $rows;
}
public function __construct(Model $model = null, Date $purgeCustomRangesOlderThan = null, LoggerInterface $logger = null)
{
# Overriding the $model object injected by the DI container.
# DI Container injects the Model class irrespective of the database used
# (Mysql or Pgsql). Also, it doesn't set the Db object on the Model
# class.
$this->model = Factory::getModel('Piwik\\DataAccess');
$this->purgeCustomRangesOlderThan = $purgeCustomRangesOlderThan ?: self::getDefaultCustomRangeToPurgeAgeThreshold();
$this->yesterday = Date::factory('yesterday');
$this->today = Date::factory('today');
$this->now = time();
$this->logger = $logger ?: StaticContainer::get('Psr\\Log\\LoggerInterface');
}
/**
* Records the layout in the DB for the given user.
*
* @param string $login
* @param int $idDashboard
* @param string $layout
*/
public function saveLayout($login, $idDashboard, $layout)
{
$generic = Factory::getGeneric($this->db);
$sql = 'SELECT * FROM ' . $this->table . ' ' . 'WHERE login = ? AND iddashboard = ? FOR UPDATE';
$row = $this->db->query($sql, array($login, $idDashboard));
if ($row) {
$sql = 'UPDATE ' . $this->table . ' SET ' . ' layout = ? ' . 'WHERE login = ? AND iddashboard = ?';
$bind = array($layout, $login, $idDashboard);
} else {
$sql = 'INSERT INTO ' . $this->table . '(login, iddashboard, layout) ' . 'VALUES (?, ?, ?)';
$bind = array($login, $idDashboard, $layout);
}
$this->db->query($sql, $bind);
}
public function getAllSiteUrls()
{
$model = Factory::getModel(__NAMESPACE__);
$siteIds = $model->getSitesId();
$siteUrls = array();
if (empty($siteIds)) {
return array();
}
foreach ($siteIds as $siteId) {
$siteId = (int) $siteId;
$siteUrls[$siteId] = $model->getSiteUrlsFromId($siteId);
}
return $siteUrls;
}
public function setForUser($login, $languageCode)
{
$generic = Factory::getGeneric($this->db);
$generic->beginTransaction();
$sql = 'SELECT login, language FROM ' . $this->table . ' ' . 'WHERE login = ?';
$row = $this->db->fetchRow($sql, array($login));
if ($row) {
$sql = 'UPDATE ' . $this->table . ' SET ' . ' language = ? ' . 'WHERE login = ?';
$this->db->query($sql, array($languageCode, $login));
} else {
$this->db->insert($this->table, array('login' => $login, 'language' => $languageCode));
}
$generic->commit();
}
public function setUp()
{
parent::setUp();
\Piwik\Plugin\Manager::getInstance()->loadPlugin('UsersManager');
\Piwik\Plugin\Manager::getInstance()->installLoadedPlugins();
// setup the access layer
FakeAccess::setIdSitesView(array(1, 2));
FakeAccess::setIdSitesAdmin(array(3, 4));
//finally we set the user as a Super User by default
FakeAccess::$superUser = true;
FakeAccess::$superUserLogin = '******';
$this->api = API::getInstance();
$this->model = Factory::getModel('Piwik\\Plugins\\UsersManager');
}
public function fetchAll()
{
$generic = Factory::getGeneric();
$generic->checkByteaOutput();
$sql = 'SELECT *, idvisitor::text AS idvisitor_text FROM ' . $this->table;
$rows = $this->db->fetchAll($sql);
while (list($k, $row) = each($rows)) {
if (!empty($row['idvisitor'])) {
$rows[$k]['idvisitor'] = $generic->db2bin($row['idvisitor_text']);
}
unset($rows[$k]['idvisitor_text']);
}
reset($rows);
return $rows;
}
/**
* Get / allocate / reserve a new id for the current sequence. Important: Getting the next id will fail in case
* no such sequence exists. Make sure to create one if needed, see {@link create()}.
*
* @return int
* @throws Exception
*/
public function getNextId()
{
$Generic = Factory::getGeneric($this->db);
$Generic->beginTransaction();
$sql = 'UPDATE ' . $this->table . ' SET value = value + 1 WHERE name = ?';
$result = $this->db->query($sql, array($this->name));
$rowCount = $result->rowCount();
if (1 !== $rowCount) {
$Generic->rollback();
throw new \Exception("Sequence '" . $this->name . "' not found.");
}
$createdId = $this->getCurrentId();
$Generic->commit();
return (int) $createdId;
}
public function upsert($idSite, $settingName, $settingValue)
{
$Generic = Factory::getGeneric();
$Generic->beginTransaction();
$sql = "SELECT * FROM {$this->table} WHERE idsite = ? AND setting_name = ? FOR UPDATE";
$row = $this->db->fetchOne($sql, array($idSite, $settingName));
if ($row) {
$sql = "UPDATE {$this->table} SET\n setting_value = ?\n WHERE idsite = ? AND setting_name = ?";
$this->db->query($sql, array($settingValue, $idSite, $settingName));
} else {
$sql = "INSERT INTO {$this->table} (idsite, setting_name, setting_value) VALUES (?, ?, ?)";
$this->db->query($sql, array($idSite, $settingName, $settingValue));
}
$Generic->commit();
}
/**
* recordProfiling
*
* Makes an entry in the table for the query if query does not exist.
* If the query exists, the row will be update.
* The query column has a unique key.
* Uses tracker db
* @param string $query
* @param int $count
* @param string $time
* @return void
*/
public function recordProfiling($query, $count, $time)
{
$generic = Factory::getGeneric($this->db);
$generic->beginTransaction();
$sql = 'SELECT query, count, sum_time_ms ' . 'FROM ' . $this->table . ' ' . 'WHERE query = ? FOR UPDATE';
$row = $this->db->fetchOne($sql, array($query));
if ($row) {
$sql = 'UPDATE ' . $this->table . ' SET ' . " count = count + {$count}, sum_time_ms = sum_time_msg + {$time} " . 'WHERE query = ?';
$this->db->query($sql, array($query));
} else {
$sql = 'INSERT INTO ' . $this->table . '(query, count, sum_time_ms) ' . "VALUES (?, ?, ?)";
$this->db->query($sql, array($query, $count, $time));
}
$generic->commit();
}
public function addRecord($name, $value, $autoload)
{
$generic = Factory::getGeneric($this->db);
$generic->beginTransaction();
$sql = 'SELECT * FROM ' . $this->table . ' WHERE option_name = ?';
$row = $this->db->fetchOne($sql, array($name));
if ($row) {
$sql = 'UPDATE ' . $this->table . ' SET option_value = ? ' . 'WHERE option_name = ?';
$bind = array($value, $name);
} else {
$sql = 'INSERT INTO ' . $this->table . ' (option_name, option_value, autoload) ' . 'VALUES (?, ?, ?) ';
$bind = array($name, $value, $autoload);
}
$this->db->query($sql, $bind);
$generic->commit();
}
public function createConversion($conversion)
{
$fields = implode(", ", array_keys($conversion));
$bindFields = Common::getSqlStringFieldsArray($conversion);
$table = Common::prefixTable('log_conversion');
$db = $this->getDb();
$Generic = Factory::getGeneric($db);
$sql = "INSERT INTO {$table} ({$fields}) VALUES ({$bindFields}) ";
if (!empty($conversion['idvisitor'])) {
$conversion['idvisitor'] = $Generic->bin2db($conversion['idvisitor']);
}
$bind = array_values($conversion);
$result = $Generic->insertIgnore($sql, $bind);
// If a record was inserted, we return true
return $db->rowCount($result) > 0;
}
private function getFirstTypeIfOnlyOneIsInUse()
{
$types = $this->typeManager->getAllTypes();
if (count($types) === 1) {
// only one type is in use, use this one for the wording
return reset($types);
} else {
// multiple types are activated, check whether only one is actually in use
$model = Factory::getModel(__NAMESPACE__);
$typeIds = $model->getUsedTypeIds();
if (count($typeIds) === 1) {
$typeManager = new TypeManager();
return $typeManager->getType(reset($typeIds));
}
}
}
/**
* Sets the language for the user
*
* @param string $login
* @param string $languageCode
* @return bool
*/
public function setLanguageForUser($login, $languageCode)
{
$Generic = Factory::getGeneric();
$Generic->beginTransaction();
$sql = 'SELECT * FROM ' . $this->table . ' WHERE login = ? FOR UPDATE';
$row = $this->db->query($sql, array($login));
if ($row) {
$sql = 'UPDATE ' . $this->table . ' SET language = ? WHERE login = ?';
$bind = array($languageCode, $login);
} else {
$sql = 'INSERT INTO ' . $this->table . ' (login, language) VALUES (?, ?)';
$bind = array($login, $languageCode);
}
$this->db->query($sql, $bind);
$Generic->commit();
return true;
}
/**
* Write Session - commit data to resource
*
* @param string $id
* @param mixed $data
* @param integer $maxLifetime
* @return boolean
*/
public function write($id, $data, $maxLifetime)
{
$Generic = Factory::getGeneric();
$sql = 'SELECT ' . $this->config['dataColumn'] . ' FROM ' . $this->config['name'] . ' WHERE ' . $this->config['primary'] . ' = ? FOR UPDATE';
$Generic->beginTransaction();
$row = $this->db->fetchOne($sql, array($id));
if ($row) {
$sql = 'UPDATE ' . $this->config['name'] . ' SET ' . $this->config['modifiedColumn'] . ' = ?,' . $this->config['lifetimeColumn'] . ' = ?,' . $this->config['dataColumn'] . ' = ? ' . ' WHERE ' . $this->config['primary'] . ' = ?';
$values = array(time(), $maxLifetime, $data, $id);
} else {
$sql = 'INSERT INTO ' . $this->config['name'] . ' (' . $this->config['primary'] . ',' . $this->config['modifiedColumn'] . ',' . $this->config['lifetimeColumn'] . ',' . $this->config['dataColumn'] . ')' . ' VALUES (?,?,?,?)';
$values = array($id, time(), $maxLifetime, $data);
}
$this->db->query($sql, $values);
$Generic->commit();
return true;
}
function prepareTestDatabaseConfig(Config $config)
{
$testDb = $config->database_tests;
Factory::setTest(true);
if ('@USERNAME@' !== $testDb['username']) {
return;
// testDb is already configured, we do not want to overwrite any existing settings.
}
$db = $config->database;
$testDb['username'] = $db['username'];
if (empty($testDb['password'])) {
$testDb['password'] = $db['password'];
}
if (empty($testDb['host'])) {
$testDb['host'] = $db['host'];
}
$testDb['tables_prefix'] = '';
// tables_prefix has to be empty for UI tests
$config->database_tests = $testDb;
$config->forceSave();
}
protected function paramsRecord($idvisit, $idsite, $idvisitor, $server_time, $url, $name, $ref_url, $ref_name, $time_spent, $custom_value, $custom_variables, $actionIdsCached)
{
$Generic = Factory::getGeneric($this->db);
$insert = array('idvisit' => $idvisit, 'idsite' => $idsite, 'idvisitor' => $Generic->bin2db($idvisitor), 'server_time' => $server_time, 'idaction_url' => $url, 'idaction_name' => $name, 'idaction_url_ref' => $ref_url, 'idaction_name_ref' => $ref_name, 'time_spent_ref_action' => $time_spent);
$integerCols = array('idsite', 'idvisit', 'idaction_url', 'idaction_url_ref', 'idaction_name', 'idaction_name_ref', 'idaction_event_category', 'idaction_event_action', 'time_spent_ref_action');
foreach ($actionIdsCached as $field => $idAction) {
if (in_array($field, $integerCols) && is_bool($idAction)) {
$insert[$field] = (int) $idAction;
} else {
$insert[$field] = $idAction;
}
}
if (!empty($custom_value)) {
$insert[Action::DB_COLUMN_CUSTOM_FLOAT] = $custom_value;
}
$insert = array_merge($insert, $custom_variables);
$fields = implode(', ', array_keys($insert));
$bind = array_values($insert);
$values = Common::getSqlStringFieldsArray($insert);
$sql = 'INSERT INTO ' . $this->table . '( ' . $fields . ') VALUES ( ' . $values . ')';
return array($sql, $bind, $insert);
}
