protected function loadSettings()
{
$sql = "SELECT `setting_name`, `setting_value` FROM " . $this->getTableName() . " WHERE idsite = ?";
$bind = array($this->idSite);
$settings = $this->db->fetchAll($sql, $bind);
$flat = array();
foreach ($settings as $setting) {
$flat[$setting['setting_name']] = unserialize($setting['setting_value']);
}
return $flat;
}
/**
* Connects to the DB
*
* @throws Exception if there was an error connecting the DB
*/
public function connect()
{
if (self::$profiling) {
$timer = $this->initProfiler();
}
$config = PiwikDb::getDatabaseConfig();
$this->connection = @new PDO($this->dsn, $this->username, $this->password, $config['driver_options']);
$this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// we may want to setAttribute(PDO::ATTR_TIMEOUT ) to a few seconds (default is 60) in case the DB is locked
// the piwik.php would stay waiting for the database... bad!
// we delete the password from this object "just in case" it could be printed
$this->password = '';
/*
* Lazy initialization via MYSQL_ATTR_INIT_COMMAND depends
* on mysqlnd support, PHP version, and OS.
* see ZF-7428 and http://bugs.php.net/bug.php?id=47224
*/
if (!empty($this->charset)) {
$sql = "SET NAMES '" . $this->charset . "'";
$this->connection->exec($sql);
}
if (self::$profiling && isset($timer)) {
$this->recordQueryProfile('connect', $timer);
}
}
public function execute()
{
$isPiwikInstalling = !Config::getInstance()->existsLocalConfig();
if ($isPiwikInstalling) {
// Skip the diagnostic if Piwik is being installed
return array();
}
$label = $this->translator->translate('Installation_DatabaseAbilities');
$optionTable = Common::prefixTable('option');
$testOptionNames = array('test_system_check1', 'test_system_check2');
$loadDataInfile = false;
$errorMessage = null;
try {
$loadDataInfile = Db\BatchInsert::tableInsertBatch($optionTable, array('option_name', 'option_value'), array(array($testOptionNames[0], '1'), array($testOptionNames[1], '2')), $throwException = true);
} catch (\Exception $ex) {
$errorMessage = str_replace("\n", "<br/>", $ex->getMessage());
}
// delete the temporary rows that were created
Db::exec("DELETE FROM `{$optionTable}` WHERE option_name IN ('" . implode("','", $testOptionNames) . "')");
if ($loadDataInfile) {
return array(DiagnosticResult::singleResult($label, DiagnosticResult::STATUS_OK, 'LOAD DATA INFILE'));
}
$comment = sprintf('LOAD DATA INFILE<br/>%s<br/>%s', $this->translator->translate('Installation_LoadDataInfileUnavailableHelp', array('LOAD DATA INFILE', 'FILE')), $this->translator->translate('Installation_LoadDataInfileRecommended'));
if ($errorMessage) {
$comment .= sprintf('<br/><strong>%s:</strong> %s<br/>%s', $this->translator->translate('General_Error'), $errorMessage, 'Troubleshooting: <a target="_blank" href="?module=Proxy&action=redirect&url=http://piwik.org/faq/troubleshooting/%23faq_194">FAQ on piwik.org</a>');
}
return array(DiagnosticResult::singleResult($label, DiagnosticResult::STATUS_WARNING, $comment));
}
public function install()
{
$queries[] = 'CREATE TABLE `' . Common::prefixTable('segment') . '` (
`idsegment` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`definition` TEXT NOT NULL,
`login` VARCHAR(100) NOT NULL,
`enable_all_users` tinyint(4) NOT NULL default 0,
`enable_only_idsite` INTEGER(11) NULL,
`auto_archive` tinyint(4) NOT NULL default 0,
`ts_created` TIMESTAMP NULL,
`ts_last_edit` TIMESTAMP NULL,
`deleted` tinyint(4) NOT NULL default 0,
PRIMARY KEY (`idsegment`)
) DEFAULT CHARSET=utf8';
try {
foreach ($queries as $query) {
Db::exec($query);
}
} catch (Exception $e) {
if (!Db::get()->isErrNo($e, '1050')) {
throw $e;
}
}
}
public static function setUpBeforeClass()
{
$dbName = false;
if (!empty($GLOBALS['PIWIK_BENCHMARK_DATABASE'])) {
$dbName = $GLOBALS['PIWIK_BENCHMARK_DATABASE'];
}
// connect to database
self::createTestConfig();
self::connectWithoutDatabase();
// create specified fixture (global var not set, use default no-data fixture (see end of this file))
if (empty($GLOBALS['PIWIK_BENCHMARK_FIXTURE'])) {
$fixtureName = 'Piwik_Test_Fixture_EmptyOneSite';
} else {
$fixtureName = 'Piwik_Test_Fixture_' . $GLOBALS['PIWIK_BENCHMARK_FIXTURE'];
}
self::$fixture = new $fixtureName();
// figure out if the desired fixture has already been setup, and if not empty the database
$installedFixture = false;
try {
if (isset(self::$fixture->tablesPrefix)) {
Config::getInstance()->database['tables_prefix'] = self::$fixture->tablesPrefix;
}
Db::query("USE " . $dbName);
$installedFixture = \Piwik\Option::get('benchmark_fixture_name');
} catch (Exception $ex) {
// ignore
}
$createEmptyDatabase = $fixtureName != $installedFixture;
parent::_setUpBeforeClass($dbName, $createEmptyDatabase);
// if we created an empty database, setup the fixture
if ($createEmptyDatabase) {
self::$fixture->setUp();
\Piwik\Option::set('benchmark_fixture_name', $fixtureName);
}
}
private static function migrateConfigSuperUserToDb()
{
$config = Config::getInstance();
if (!$config->existsLocalConfig()) {
return;
}
try {
$superUser = $config->superuser;
} catch (\Exception $e) {
$superUser = null;
}
if (!empty($superUser['bridge']) || empty($superUser) || empty($superUser['login'])) {
// there is a super user which is not from the config but from the bridge, that means we already have
// a super user in the database
return;
}
$userApi = UsersManagerApi::getInstance();
try {
Db::get()->insert(Common::prefixTable('user'), array('login' => $superUser['login'], 'password' => $superUser['password'], 'alias' => $superUser['login'], 'email' => $superUser['email'], 'token_auth' => $userApi->getTokenAuth($superUser['login'], $superUser['password']), 'date_registered' => Date::now()->getDatetime(), 'superuser_access' => 1));
} catch (\Exception $e) {
echo "There was an issue, but we proceed: " . $e->getMessage();
}
if (array_key_exists('salt', $superUser)) {
$salt = $superUser['salt'];
} else {
$salt = Common::generateUniqId();
}
$config->General['salt'] = $salt;
$config->superuser = array();
$config->forceSave();
}
private function assertDuplicatesFixedInLogConversionItemTable()
{
$columns = array('idaction_sku', 'idaction_name', 'idaction_category', 'idaction_category2', 'idaction_category3', 'idaction_category4', 'idaction_category5');
$rows = Db::fetchAll("SELECT " . implode(',', $columns) . " FROM " . Common::prefixTable('log_conversion_item'));
$expectedRows = array(array('idaction_sku' => '1', 'idaction_name' => '1', 'idaction_category' => '1', 'idaction_category2' => '4', 'idaction_category3' => '5', 'idaction_category4' => '6', 'idaction_category5' => '5'), array('idaction_sku' => '1', 'idaction_name' => '1', 'idaction_category' => '5', 'idaction_category2' => '5', 'idaction_category3' => '8', 'idaction_category4' => '4', 'idaction_category5' => '6'));
$this->assertEquals($expectedRows, $rows);
}
private function createStorage($idSite = null)
{
if (!isset($idSite)) {
$idSite = $this->idSite;
}
return new Storage(Db::get(), $idSite);
}
public function test_constructor_shouldNotEstablishADatabaseConnection()
{
Db::destroyDatabaseObject();
$this->assertNotDbConnectionCreated();
$this->createSettingsInstance();
$this->assertNotDbConnectionCreated();
}
public static function disconnectDatabase()
{
if (isset(self::$db)) {
self::$db->disconnect();
self::$db = null;
}
}
/**
* Analyzes numeric & blob tables for a single table date (ie, `'2015_01'`) and returns
* statistics including:
*
* - number of archives present
* - number of invalidated archives
* - number of temporary archives
* - number of error archives
* - number of segment archives
* - number of numeric rows
* - number of blob rows
*
* @param string $tableDate ie `'2015_01'`
* @return array
*/
public function getArchiveTableAnalysis($tableDate)
{
$numericQueryEmptyRow = array('count_archives' => '-', 'count_invalidated_archives' => '-', 'count_temporary_archives' => '-', 'count_error_archives' => '-', 'count_segment_archives' => '-', 'count_numeric_rows' => '-');
$tableDate = str_replace("`", "", $tableDate);
// for sanity
$numericTable = Common::prefixTable("archive_numeric_{$tableDate}");
$blobTable = Common::prefixTable("archive_blob_{$tableDate}");
// query numeric table
$sql = "SELECT CONCAT_WS('.', idsite, date1, date2, period) AS label,\n SUM(CASE WHEN name LIKE 'done%' THEN 1 ELSE 0 END) AS count_archives,\n SUM(CASE WHEN name LIKE 'done%' AND value = ? THEN 1 ELSE 0 END) AS count_invalidated_archives,\n SUM(CASE WHEN name LIKE 'done%' AND value = ? THEN 1 ELSE 0 END) AS count_temporary_archives,\n SUM(CASE WHEN name LIKE 'done%' AND value = ? THEN 1 ELSE 0 END) AS count_error_archives,\n SUM(CASE WHEN name LIKE 'done%' AND CHAR_LENGTH(name) > 32 THEN 1 ELSE 0 END) AS count_segment_archives,\n SUM(CASE WHEN name NOT LIKE 'done%' THEN 1 ELSE 0 END) AS count_numeric_rows,\n 0 AS count_blob_rows\n FROM `{$numericTable}`\n GROUP BY idsite, date1, date2, period";
$rows = Db::fetchAll($sql, array(ArchiveWriter::DONE_INVALIDATED, ArchiveWriter::DONE_OK_TEMPORARY, ArchiveWriter::DONE_ERROR));
// index result
$result = array();
foreach ($rows as $row) {
$result[$row['label']] = $row;
}
// query blob table & manually merge results (no FULL OUTER JOIN in mysql)
$sql = "SELECT CONCAT_WS('.', idsite, date1, date2, period) AS label,\n COUNT(*) AS count_blob_rows\n FROM `{$blobTable}`\n GROUP BY idsite, date1, date1, period";
foreach (Db::fetchAll($sql) as $blobStatsRow) {
$label = $blobStatsRow['label'];
if (isset($result[$label])) {
$result[$label] = array_merge($result[$label], $blobStatsRow);
} else {
$result[$label] = $blobStatsRow + $numericQueryEmptyRow;
}
}
return $result;
}
public static function getUsersBySite($idsite)
{
$getRegularUsers = Db::fetchAll("SELECT login,\n (SELECT email FROM " . Common::prefixTable('user') . " WHERE login = acc.login) AS email\n FROM " . Common::prefixTable('access') . " AS acc WHERE idsite = ?", array($idsite));
$getSuperUsers = Db::fetchAll("SELECT login,email FROM " . Common::prefixTable('user') . " WHERE superuser_access = 1");
$getUsers = array_merge($getRegularUsers, $getSuperUsers);
return $getUsers;
}
public static function update($idvisitor, $name = false, $email = false, $phone = false, $comments = false)
{
if ($name == false && $email == false && $phone == false && $comments == false) {
return false;
}
$buildQuery = "";
$argSet = array();
$argOnUpdate = array();
$argSet[] = @Common::hex2bin($idvisitor);
if ($name) {
$argSet[] = $name;
$argOnUpdate[] = $name;
$buildQuery .= " name = ?,";
}
if ($email) {
$argSet[] = $email;
$argOnUpdate[] = $email;
$buildQuery .= " email = ?,";
}
if ($phone) {
$argSet[] = $phone;
$argOnUpdate[] = $phone;
$buildQuery .= " phone = ?,";
}
if ($comments) {
$argSet[] = $comments;
$argOnUpdate[] = $comments;
$buildQuery .= " comments = ?,";
}
$buildQuery = trim(substr_replace($buildQuery, "", -1));
$arguments = array_merge($argSet, $argOnUpdate);
Db::query("INSERT INTO " . Common::prefixTable('chat_personnal_informations') . " SET idvisitor = ?, {$buildQuery} ON DUPLICATE KEY UPDATE {$buildQuery}", $arguments);
return true;
}
/**
* @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;
}
/**
* Authenticates user
*
* @return AuthResult
*/
public function authenticate()
{
$rootLogin = Config::getInstance()->superuser['login'];
$rootPassword = Config::getInstance()->superuser['password'];
$rootToken = API::getInstance()->getTokenAuth($rootLogin, $rootPassword);
if (is_null($this->login)) {
if ($this->token_auth === $rootToken) {
return new AuthResult(AuthResult::SUCCESS_SUPERUSER_AUTH_CODE, $rootLogin, $this->token_auth);
}
$login = Db::fetchOne('SELECT login
FROM ' . Common::prefixTable('user') . '
WHERE token_auth = ?', array($this->token_auth));
if (!empty($login)) {
return new AuthResult(AuthResult::SUCCESS, $login, $this->token_auth);
}
} else {
if (!empty($this->login)) {
if ($this->login === $rootLogin && $this->getHashTokenAuth($rootLogin, $rootToken) === $this->token_auth || $rootToken === $this->token_auth) {
$this->setTokenAuth($rootToken);
return new AuthResult(AuthResult::SUCCESS_SUPERUSER_AUTH_CODE, $rootLogin, $this->token_auth);
}
$login = $this->login;
$userToken = Db::fetchOne('SELECT token_auth
FROM ' . Common::prefixTable('user') . '
WHERE login = ?', array($login));
if (!empty($userToken) && ($this->getHashTokenAuth($login, $userToken) === $this->token_auth || $userToken === $this->token_auth)) {
$this->setTokenAuth($userToken);
return new AuthResult(AuthResult::SUCCESS, $login, $userToken);
}
}
}
return new AuthResult(AuthResult::FAILURE, $this->login, $this->token_auth);
}
public static function setUpBeforeClass()
{
parent::setUpBeforeClass();
// note: not sure why I have to manually install plugin
\Piwik\Plugin\Manager::getInstance()->loadPlugin('CustomAlerts')->install();
$result = Fixture::updateDatabase();
if ($result === false) {
throw new \Exception("Failed to update pre-2.0 database (nothing to update).");
}
// truncate log tables so old data won't be re-archived
foreach (array('log_visit', 'log_link_visit_action', 'log_conversion', 'log_conversion_item') as $table) {
Db::query("TRUNCATE TABLE " . Common::prefixTable($table));
}
// add two visits from same visitor on dec. 29
$t = Fixture::getTracker(1, '2012-12-29 01:01:30', $defaultInit = true);
$t->setUrl('http://site.com/index.htm');
$t->setIp('136.5.3.2');
Fixture::checkResponse($t->doTrackPageView('incredible title!'));
$t->setForceVisitDateTime('2012-12-29 03:01:30');
$t->setUrl('http://site.com/other/index.htm');
$t->DEBUG_APPEND_URL = '&_idvc=2';
// make sure visit is marked as returning
Fixture::checkResponse($t->doTrackPageView('other incredible title!'));
// launch archiving
VisitFrequencyApi::getInstance()->get(1, 'year', '2012-12-29');
}
public static function disconnectCachedDbConnection()
{
// code redundancy w/ above is on purpose; above disconnectDatabase depends on method that can potentially be overridden
if (!is_null(self::$db)) {
self::$db->disconnect();
self::$db = null;
}
}
/**
* @param int $idSite The id of a site. If you want to get settings for a not yet created site just pass an empty value ("0")
* @param string $idType If no typeId is given, the type of the site will be used.
*
* @throws \Exception
*/
public function __construct($idSite, $idType)
{
$this->idSite = $idSite;
$this->idType = $idType;
$this->storage = new Storage(Db::get(), $this->idSite);
$this->pluginName = 'MeasurableSettings';
$this->init();
}
protected function write(array $record)
{
$sql = sprintf('INSERT INTO %s (tag, timestamp, level, message) VALUES (?, ?, ?, ?)', Common::prefixTable('logger_message'));
$queryLog = Db::isQueryLogEnabled();
Db::enableQueryLog(false);
Db::query($sql, array($record['extra']['class'], $record['datetime']->format('Y-m-d H:i:s'), $record['level_name'], trim($record['formatted'])));
Db::enableQueryLog($queryLog);
}
public function test_getSitesAndDatesOfRowsUsingDuplicates_ReturnsTheServerTimeAndIdSite_OfRowsUsingSpecifiedActionIds()
{
$row = array('idsite' => 3, 'idvisitor' => pack("H*", DuplicateActions::DUMMY_IDVISITOR), 'server_time' => '2012-02-13 00:00:00', 'idvisit' => 5, 'idorder' => 6, 'price' => 15, 'quantity' => 21, 'deleted' => 1, 'idaction_sku' => 3, 'idaction_name' => 3, 'idaction_category' => 12, 'idaction_category2' => 3, 'idaction_category3' => 3, 'idaction_category4' => 3, 'idaction_category5' => 3);
Db::query("INSERT INTO " . Common::prefixTable('log_conversion_item') . " (" . implode(", ", array_keys($row)) . ") VALUES ('" . implode("', '", array_values($row)) . "')");
$expectedResult = array(array('idsite' => 1, 'server_time' => '2012-02-01'), array('idsite' => 3, 'server_time' => '2012-02-13'));
$actualResult = $this->duplicateActionRemover->getSitesAndDatesOfRowsUsingDuplicates('log_conversion_item', array(4, 6, 12));
$this->assertEquals($expectedResult, $actualResult);
}
/**
* Removes a list of actions from the log_action table by ID.
*
* @param int[] $idActions
*/
public function delete($idActions)
{
foreach ($idActions as &$id) {
$id = (int) $id;
}
$table = Common::prefixTable('log_action');
$sql = "DELETE FROM {$table} WHERE idaction IN (" . implode(",", $idActions) . ")";
Db::query($sql);
}
public function tearDown()
{
// clean up your test here if needed
$tables = ArchiveTableCreator::getTablesArchivesInstalled();
if (!empty($tables)) {
Db::dropTables($tables);
}
parent::tearDown();
}
/**
* @group Core
*/
public function testOptimize()
{
// make sure optimizing myisam tables works
$this->assertTrue(Db::optimizeTables(array('table1', 'table2')) !== false);
// make sure optimizing both myisam & innodb results in optimizations
$this->assertTrue(Db::optimizeTables(array('table1', 'table2', 'table3', 'table4')) !== false);
// make sure innodb tables are skipped
$this->assertTrue(Db::optimizeTables(array('table3', 'table4')) === false);
}
/**
* Returns all stored segments that are available for the given site and login.
*
* @param string $userLogin
* @param int $idSite Whether to return stored segments for a specific idSite, or all of them. If supplied, must be a valid site ID.
* @return array
*/
public function getAllSegmentsForSite($idSite, $userLogin)
{
$bind = array($idSite, $userLogin);
$sql = $this->buildQuerySortedByName('(enable_only_idsite = ? OR enable_only_idsite = 0)
AND deleted = 0
AND (enable_all_users = 1 OR login = ?)');
$segments = Db::get()->fetchAll($sql, $bind);
return $segments;
}
private function getLdapUserLogins()
{
$rows = Db::fetchAll("SELECT login from " . Common::prefixTable('user') . " WHERE password LIKE '{LDAP}%'");
$result = array();
foreach ($rows as $row) {
$result[] = $row['login'];
}
return $result;
}
public function onSiteDeleted($idSite)
{
// we do not delete logs here on purpose (you can run these queries on the log_ tables to delete all data)
Cache::deleteCacheWebsiteAttributes($idSite);
$archiveInvalidator = new ArchiveInvalidator();
$archiveInvalidator->forgetRememberedArchivedReportsToInvalidateForSite($idSite);
$measurableStorage = new Storage(Db::get(), $idSite);
$measurableStorage->deleteAllValues();
}
public function test_UpdateCommand_ReturnsCorrectExitCode_WhenErrorOccurs()
{
// create a blob table, then drop it manually so update 2.10.0-b10 will fail
$tableName = ArchiveTableCreator::getBlobTable(Date::factory('2015-01-01'));
Db::exec("DROP TABLE {$tableName}");
$result = $this->applicationTester->run(array('command' => 'core:update', '--yes' => true));
$this->assertEquals(1, $result, $this->getCommandDisplayOutputErrorMessage());
$this->assertContains("Piwik could not be updated! See above for more information.", $this->applicationTester->getDisplay());
}
public function test_getUrlToCheckForLatestAvailableVersion()
{
$version = Version::VERSION;
$phpVersion = urlencode(PHP_VERSION);
$mysqlVersion = Db::get()->getServerVersion();
$url = urlencode(Url::getCurrentUrlWithoutQueryString());
$urlToCheck = $this->channel->getUrlToCheckForLatestAvailableVersion();
$this->assertStringStartsWith("http://api.piwik.org/1.0/getLatestVersion/?piwik_version={$version}&php_version={$phpVersion}&mysql_version={$mysqlVersion}&release_channel=my_channel&url={$url}&trigger=&timezone=", $urlToCheck);
}
/**
* Returns migrations to hash existing password with bcrypt.
* @param Migration[] $queries
* @return Migration[]
*/
private function getUserPasswordMigrations($queries)
{
$db = Db::get();
$userTable = Common::prefixTable($this->userTable);
$users = $db->fetchAll('SELECT `login`, `password` FROM `' . $userTable . '` WHERE LENGTH(`password`) = 32');
foreach ($users as $user) {
$queries[] = $this->migration->db->boundSql('UPDATE `' . $userTable . '`' . ' SET `password` = ?' . ' WHERE `login` = ?', [password_hash($user['password'], PASSWORD_BCRYPT), $user['login']]);
}
return $queries;
}
public static function getUnreadConversations($login)
{
$conversations = array();
$rows = Db::fetchAll("SELECT idvisitor, lastviewed, lastsent FROM " . Common::prefixTable('chat_history_admin') . " WHERE login = ? AND lastsent > lastviewed", array($login));
$rows = ChatCommon::formatRows($rows);
foreach ($rows as $row) {
$conversations[$row['idvisitor']] = $row;
}
return $conversations;
}
