/** * Performs a batch insert into a specific table using either LOAD DATA INFILE or plain INSERTs, * as a fallback. On MySQL, LOAD DATA INFILE is 20x faster than a series of plain INSERTs. * * @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 $throwException Whether to throw an exception that was caught while trying * LOAD DATA INFILE, or not. * @throws Exception * @return bool True if the bulk LOAD was used, false if we fallback to plain INSERTs */ public static function tableInsertBatch($tableName, $fields, $values, $throwException = false) { $filePath = PIWIK_USER_PATH . '/tmp/assets/' . $tableName . '-' . Common::generateUniqId() . '.csv'; $filePath = SettingsPiwik::rewriteTmpPathWithInstanceId($filePath); $loadDataInfileEnabled = Config::getInstance()->General['enable_load_data_infile']; if ($loadDataInfileEnabled && Db::get()->hasBulkLoader()) { try { $fileSpec = array('delim' => "\t", 'quote' => '"', 'escape' => '\\\\', 'escapespecial_cb' => function ($str) { return str_replace(array(chr(92), chr(34)), array(chr(92) . chr(92), chr(92) . chr(34)), $str); }, 'eol' => "\r\n", 'null' => 'NULL'); // hack for charset mismatch if (!DbHelper::isDatabaseConnectionUTF8() && !isset(Config::getInstance()->database['charset'])) { $fileSpec['charset'] = 'latin1'; } self::createCSVFile($filePath, $fileSpec, $values); if (!is_readable($filePath)) { throw new Exception("File {$filePath} could not be read."); } $rc = self::createTableFromCSVFile($tableName, $fields, $filePath, $fileSpec); if ($rc) { unlink($filePath); return true; } } catch (Exception $e) { Log::info("LOAD DATA INFILE failed or not supported, falling back to normal INSERTs... Error was: %s", $e->getMessage()); if ($throwException) { throw $e; } } } // if all else fails, fallback to a series of INSERTs @unlink($filePath); self::tableInsertBatchIterate($tableName, $fields, $values); return false; }
public function getSelector() { $view = new View('@SegmentEditor/getSelector'); $idSite = Common::getRequestVar('idSite'); $this->setGeneralVariablesView($view); $segments = APIMetadata::getInstance()->getSegmentsMetadata($idSite); $segmentsByCategory = $customVariablesSegments = array(); foreach ($segments as $segment) { if ($segment['category'] == Piwik::translate('General_Visit') && ($segment['type'] == 'metric' && $segment['segment'] != 'visitIp')) { $metricsLabel = Piwik::translate('General_Metrics'); $metricsLabel[0] = strtolower($metricsLabel[0]); $segment['category'] .= ' (' . $metricsLabel . ')'; } $segmentsByCategory[$segment['category']][] = $segment; } uksort($segmentsByCategory, array($this, 'sortSegmentCategories')); $view->segmentsByCategory = $segmentsByCategory; $savedSegments = API::getInstance()->getAll($idSite); foreach ($savedSegments as &$savedSegment) { $savedSegment['name'] = Common::sanitizeInputValue($savedSegment['name']); } $view->savedSegmentsJson = Common::json_encode($savedSegments); $view->authorizedToCreateSegments = !Piwik::isUserIsAnonymous(); $view->segmentTranslations = Common::json_encode($this->getTranslations()); $out = $view->render(); return $out; }
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 function writeDebugInfo() { parent::writeDebugInfo(); if ($this->detectActionIsOutlinkOnAliasHost($this, $this->request->getIdSite())) { Common::printDebug("INFO: The outlink URL host is one of the known host for this website. "); } }
public function getMigrations(Updater $updater) { // Renaming old archived records now that the plugin is called Referrers $migrations = array(); $tables = \Piwik\DbHelper::getTablesInstalled(); foreach ($tables as $tableName) { if (strpos($tableName, 'archive_') !== false) { $migrations[] = $this->migration->db->sql('UPDATE `' . $tableName . '` SET `name`=REPLACE(`name`, \'Referers_\', \'Referrers_\') WHERE `name` LIKE \'Referers_%\''); } } $errorCodeTableNotFound = '1146'; // Rename custom segments containing Referers segments $migrations[] = $this->migration->db->sql('UPDATE `' . Common::prefixTable('segment') . '` SET `definition`=REPLACE(`definition`, \'referer\', \'referrer\') WHERE `definition` LIKE \'%referer%\'', $errorCodeTableNotFound); // Rename Referrers reports within scheduled reports $query = 'UPDATE `' . Common::prefixTable('report') . '` SET `reports`=REPLACE(`reports`, \'Referer\', \'Referrer\') WHERE `reports` LIKE \'%Referer%\''; $migrations[] = $this->migration->db->sql($query, $errorCodeTableNotFound); // Rename Referrers widgets in custom dashboards $query = 'UPDATE `' . Common::prefixTable('user_dashboard') . '` SET `layout`=REPLACE(`layout`, \'Referer\', \'Referrer\') WHERE `layout` LIKE \'%Referer%\''; $migrations[] = $this->migration->db->sql($query, $errorCodeTableNotFound); $query = 'UPDATE `' . Common::prefixTable('option') . '` SET `option_name` = \'version_ScheduledReports\' WHERE `option_name` = \'version_PDFReports\' '; $migrations[] = $this->migration->db->sql($query, Updater\Migration\Db::ERROR_CODE_DUPLICATE_ENTRY); // http://forum.piwik.org/read.php?2,106895 $migrations[] = $this->migration->plugin->activate('Referrers'); $migrations[] = $this->migration->plugin->activate('ScheduledReports'); return $migrations; }
/** * Computes the output for the given data table * * @param DataTable $table * @return string * @throws Exception */ protected function renderTable($table) { if (!$table instanceof DataTable\Map || $table->getKeyName() != 'date') { throw new Exception("RSS feeds can be generated for one specific website &idSite=X." . "\nPlease specify only one idSite or consider using &format=XML instead."); } $idSite = Common::getRequestVar('idSite', 1, 'int'); $period = Common::getRequestVar('period'); $piwikUrl = SettingsPiwik::getPiwikUrl() . "?module=CoreHome&action=index&idSite=" . $idSite . "&period=" . $period; $out = ""; $moreRecentFirst = array_reverse($table->getDataTables(), true); foreach ($moreRecentFirst as $date => $subtable) { /** @var DataTable $subtable */ $timestamp = $subtable->getMetadata(Archive\DataTableFactory::TABLE_METADATA_PERIOD_INDEX)->getDateStart()->getTimestamp(); $site = $subtable->getMetadata(Archive\DataTableFactory::TABLE_METADATA_SITE_INDEX); $pudDate = date('r', $timestamp); $dateInSiteTimezone = Date::factory($timestamp); if ($site) { $dateInSiteTimezone = $dateInSiteTimezone->setTimezone($site->getTimezone()); } $dateInSiteTimezone = $dateInSiteTimezone->toString('Y-m-d'); $thisPiwikUrl = Common::sanitizeInputValue($piwikUrl . "&date={$dateInSiteTimezone}"); $siteName = $site ? $site->getName() : ''; $title = $siteName . " on " . $date; $out .= "\t<item>\n\t\t<pubDate>{$pudDate}</pubDate>\n\t\t<guid>{$thisPiwikUrl}</guid>\n\t\t<link>{$thisPiwikUrl}</link>\n\t\t<title>{$title}</title>\n\t\t<author>http://piwik.org</author>\n\t\t<description>"; $out .= Common::sanitizeInputValue($this->renderDataTable($subtable)); $out .= "</description>\n\t</item>\n"; } $header = $this->getRssHeader(); $footer = $this->getRssFooter(); return $header . $out . $footer; }
public static function update() { $salt = Common::generateUniqId(); $config = Config::getInstance(); $superuser = $config->superuser; if (!isset($superuser['salt'])) { try { if (is_writable(Config::getLocalConfigPath())) { $superuser['salt'] = $salt; $config->superuser = $superuser; $config->forceSave(); } else { throw new \Exception('mandatory update failed'); } } catch (\Exception $e) { throw new \Piwik\UpdaterErrorException("Please edit your config/config.ini.php file and add below <code>[superuser]</code> the following line: <br /><code>salt = {$salt}</code>"); } } $plugins = $config->Plugins; if (!in_array('MultiSites', $plugins)) { try { if (is_writable(Config::getLocalConfigPath())) { $plugins[] = 'MultiSites'; $config->Plugins = $plugins; $config->forceSave(); } else { throw new \Exception('optional update failed'); } } catch (\Exception $e) { throw new \Exception("You can now enable the new MultiSites plugin in the Plugins screen in the Piwik admin!"); } } Updater::updateDatabase(__FILE__, self::getSql()); }
public function getMigrationQueries(Updater $updater) { $sqlarray = array('ALTER TABLE `' . Common::prefixTable('log_visit') . '` ADD `visit_goal_converted` VARCHAR( 1 ) NOT NULL AFTER `visit_total_time`' => 1060, 'ALTER TABLE `' . Common::prefixTable('log_visit') . '` CHANGE `visit_goal_converted` `visit_goal_converted` TINYINT(1) NOT NULL' => 1060, 'CREATE TABLE `' . Common::prefixTable('goal') . "` (\n\t\t\t\t`idsite` int(11) NOT NULL,\n\t\t\t\t`idgoal` int(11) NOT NULL,\n\t\t\t\t`name` varchar(50) NOT NULL,\n\t\t\t\t`match_attribute` varchar(20) NOT NULL,\n\t\t\t\t`pattern` varchar(255) NOT NULL,\n\t\t\t\t`pattern_type` varchar(10) NOT NULL,\n\t\t\t\t`case_sensitive` tinyint(4) NOT NULL,\n\t\t\t\t`revenue` float NOT NULL,\n\t\t\t\t`deleted` tinyint(4) NOT NULL default '0',\n\t\t\t\tPRIMARY KEY (`idsite`,`idgoal`)\n\t\t\t)" => 1050, 'CREATE TABLE `' . Common::prefixTable('log_conversion') . '` ( `idvisit` int(10) unsigned NOT NULL, `idsite` int(10) unsigned NOT NULL, `visitor_idcookie` char(32) NOT NULL, `server_time` datetime NOT NULL, `visit_server_date` date NOT NULL, `idaction` int(11) NOT NULL, `idlink_va` int(11) NOT NULL, `referer_idvisit` int(10) unsigned default NULL, `referer_type` int(10) unsigned default NULL, `referer_name` varchar(70) default NULL, `referer_keyword` varchar(255) default NULL, `visitor_returning` tinyint(1) NOT NULL, `location_country` char(3) NOT NULL, `location_continent` char(3) NOT NULL, `url` text NOT NULL, `idgoal` int(10) unsigned NOT NULL, `revenue` float default NULL, PRIMARY KEY (`idvisit`,`idgoal`), KEY `index_idsite_date` (`idsite`,`visit_server_date`) )' => 1050); $tables = DbHelper::getTablesInstalled(); foreach ($tables as $tableName) { if (preg_match('/archive_/', $tableName) == 1) { $sqlarray['CREATE INDEX index_all ON ' . $tableName . ' (`idsite`,`date1`,`date2`,`name`,`ts_archived`)'] = 1072; } } return $sqlarray; }
static function getSql() { return array('CREATE TABLE `' . Common::prefixTable('log_conversion_item') . '` ( idsite int(10) UNSIGNED NOT NULL, idvisitor BINARY(8) NOT NULL, server_time DATETIME NOT NULL, idvisit INTEGER(10) UNSIGNED NOT NULL, idorder varchar(100) NOT NULL, idaction_sku INTEGER(10) UNSIGNED NOT NULL, idaction_name INTEGER(10) UNSIGNED NOT NULL, idaction_category INTEGER(10) UNSIGNED NOT NULL, price FLOAT NOT NULL, quantity INTEGER(10) UNSIGNED NOT NULL, deleted TINYINT(1) UNSIGNED NOT NULL, PRIMARY KEY(idvisit, idorder, idaction_sku), INDEX index_idsite_servertime ( idsite, server_time ) ) DEFAULT CHARSET=utf8 ' => false, 'ALTER IGNORE TABLE `' . Common::prefixTable('log_visit') . '` ADD visitor_days_since_order SMALLINT(5) UNSIGNED NOT NULL AFTER visitor_days_since_last, ADD visit_goal_buyer TINYINT(1) NOT NULL AFTER visit_goal_converted' => false, 'ALTER IGNORE TABLE `' . Common::prefixTable('log_conversion') . '` ADD visitor_days_since_order SMALLINT(5) UNSIGNED NOT NULL AFTER visitor_days_since_first, ADD idorder varchar(100) default NULL AFTER buster, ADD items SMALLINT UNSIGNED DEFAULT NULL, ADD revenue_subtotal float default NULL, ADD revenue_tax float default NULL, ADD revenue_shipping float default NULL, ADD revenue_discount float default NULL, ADD UNIQUE KEY unique_idsite_idorder (idsite, idorder), MODIFY idgoal int(10) NOT NULL' => false); }
/** * Checks for DoNotTrack headers and if found, sets `$exclude` to `true`. */ public function checkHeaderInTracker(&$exclude) { if ($exclude) { Common::printDebug("Visit is already excluded, no need to check DoNotTrack support."); return; } if (!$this->isActive()) { Common::printDebug("DoNotTrack support is not enabled, skip check"); return; } if (isset($_SERVER['HTTP_X_DO_NOT_TRACK']) && $_SERVER['HTTP_X_DO_NOT_TRACK'] === '1' || isset($_SERVER['HTTP_DNT']) && substr($_SERVER['HTTP_DNT'], 0, 1) === '1') { $request = new Request($_REQUEST); $ua = $request->getUserAgent(); if (strpos($ua, 'MSIE') !== false || strpos($ua, 'Trident') !== false) { Common::printDebug("INTERNET EXPLORER enable DoNotTrack by default; so Piwik ignores DNT IE browsers..."); return; } Common::printDebug("DoNotTrack header found!"); $exclude = true; $trackingCookie = IgnoreCookie::getTrackingCookie(); $trackingCookie->delete(); // this is an optional supplement to the site's tracking status resource at: // /.well-known/dnt // per Tracking Preference Expression (draft) header('Tk: 1'); } else { Common::printDebug("DoNotTrack header not found"); } }
public function index() { Piwik::checkUserIsNotAnonymous(); $view = new View('@MobileMessaging/index'); $view->isSuperUser = Piwik::hasUserSuperUserAccess(); $mobileMessagingAPI = API::getInstance(); $view->delegatedManagement = $mobileMessagingAPI->getDelegatedManagement(); $view->credentialSupplied = $mobileMessagingAPI->areSMSAPICredentialProvided(); $view->accountManagedByCurrentUser = $view->isSuperUser || $view->delegatedManagement; $view->strHelpAddPhone = Piwik::translate('MobileMessaging_Settings_PhoneNumbers_HelpAdd', array(Piwik::translate('General_Settings'), Piwik::translate('MobileMessaging_SettingsMenu'))); if ($view->credentialSupplied && $view->accountManagedByCurrentUser) { $view->provider = $mobileMessagingAPI->getSMSProvider(); $view->creditLeft = $mobileMessagingAPI->getCreditLeft(); } $view->smsProviders = SMSProvider::$availableSMSProviders; // construct the list of countries from the lang files $countries = array(); foreach (Common::getCountriesList() as $countryCode => $continentCode) { if (isset(CountryCallingCodes::$countryCallingCodes[$countryCode])) { $countries[$countryCode] = array('countryName' => \Piwik\Plugins\UserCountry\countryTranslate($countryCode), 'countryCallingCode' => CountryCallingCodes::$countryCallingCodes[$countryCode]); } } $view->countries = $countries; $view->defaultCountry = Common::getCountry(LanguagesManager::getLanguageCodeForCurrentUser(), true, IP::getIpFromHeader()); $view->phoneNumbers = $mobileMessagingAPI->getPhoneNumbers(); $this->setBasicVariablesView($view); return $view->render(); }
static function getSql() { $logVisit = Common::prefixTable('log_visit'); $logConversion = Common::prefixTable('log_conversion'); $addColumns = "DROP `location_continent`,\n\t\t\t\t\t ADD `location_region` CHAR(2) NULL AFTER `location_country`,\n\t\t\t\t\t ADD `location_city` VARCHAR(255) NULL AFTER `location_region`,\n\t\t\t\t\t ADD `location_latitude` FLOAT(10, 6) NULL AFTER `location_city`,\n\t\t\t ADD `location_longitude` FLOAT(10, 6) NULL AFTER `location_latitude`"; return array("ALTER TABLE `{$logVisit}` {$addColumns}" => 1091, "ALTER TABLE `{$logConversion}` {$addColumns}" => 1091); }
/** * Tracker requests will automatically trigger the Scheduled tasks. * This is useful for users who don't setup the cron, * but still want daily/weekly/monthly PDF reports emailed automatically. * * This is similar to calling the API CoreAdminHome.runScheduledTasks */ public function runScheduledTasks() { $now = time(); // Currently, there are no hourly tasks. When there are some, // this could be too aggressive minimum interval (some hours would be skipped in case of low traffic) $minimumInterval = TrackerConfig::getConfigValue('scheduled_tasks_min_interval'); // If the user disabled browser archiving, he has already setup a cron // To avoid parallel requests triggering the Scheduled Tasks, // Get last time tasks started executing $cache = Cache::getCacheGeneral(); if ($minimumInterval <= 0 || empty($cache['isBrowserTriggerEnabled'])) { Common::printDebug("-> Scheduled tasks not running in Tracker: Browser archiving is disabled."); return; } $nextRunTime = $cache['lastTrackerCronRun'] + $minimumInterval; if (defined('DEBUG_FORCE_SCHEDULED_TASKS') && DEBUG_FORCE_SCHEDULED_TASKS || $cache['lastTrackerCronRun'] === false || $nextRunTime < $now) { $cache['lastTrackerCronRun'] = $now; Cache::setCacheGeneral($cache); Option::set('lastTrackerCronRun', $cache['lastTrackerCronRun']); Common::printDebug('-> Scheduled Tasks: Starting...'); $invokeScheduledTasksUrl = "?module=API&format=csv&convertToUnicode=0&method=CoreAdminHome.runScheduledTasks&trigger=archivephp"; $cliMulti = new CliMulti(); $cliMulti->runAsSuperUser(); $responses = $cliMulti->request(array($invokeScheduledTasksUrl)); $resultTasks = reset($responses); Common::printDebug($resultTasks); Common::printDebug('Finished Scheduled Tasks.'); } else { Common::printDebug("-> Scheduled tasks not triggered."); } Common::printDebug("Next run will be from: " . date('Y-m-d H:i:s', $nextRunTime) . ' UTC'); }
public function addSubcategories(&$subcategories) { $idSite = Common::getRequestVar('idSite', 0, 'int'); if (!$idSite) { // fallback for eg API.getReportMetadata which uses idSites $idSite = Common::getRequestVar('idSites', 0, 'int'); if (!$idSite) { return; } } $dimensions = $this->configuration->getCustomDimensionsForSite($idSite); $order = 70; foreach ($dimensions as $dimension) { if (!$dimension['active']) { continue; } $category = new Subcategory(); $category->setName($dimension['name']); if ($dimension['scope'] === CustomDimensions::SCOPE_ACTION) { $category->setCategoryId('General_Actions'); } elseif ($dimension['scope'] === CustomDimensions::SCOPE_VISIT) { $category->setCategoryId('General_Visitors'); } $category->setId('customdimension' . $dimension['idcustomdimension']); $category->setOrder($order++); $subcategories[] = $category; } }
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(); }
/** * @see ViewDataTable::main() * @return mixed */ public function render() { // If period=range, we force the sparkline to draw daily data points $period = Common::getRequestVar('period'); if ($period == 'range') { $_GET['period'] = 'day'; } $this->loadDataTableFromAPI(); // then revert the hack for potentially subsequent getRequestVar $_GET['period'] = $period; $values = $this->getValuesFromDataTable($this->dataTable); if (empty($values)) { $values = array_fill(0, 30, 0); } $graph = new \Piwik\Visualization\Sparkline(); $graph->setValues($values); $height = Common::getRequestVar('height', 0, 'int'); if (!empty($height)) { $graph->setHeight($height); } $width = Common::getRequestVar('width', 0, 'int'); if (!empty($width)) { $graph->setWidth($width); } $graph->main(); return $graph->render(); }
private function assertDuplicatesFixedInLogConversionItemTable() { $columns = array('idaction_sku', 'idaction_name', 'idaction_category', 'idaction_category2', 'idaction_category3', 'idaction_category4', 'idaction_category5'); $rows = Db::fetchAll("SELECT " . implode(',', $columns) . " FROM " . Common::prefixTable('log_conversion_item')); $expectedRows = array(array('idaction_sku' => '1', 'idaction_name' => '1', 'idaction_category' => '1', 'idaction_category2' => '4', 'idaction_category3' => '5', 'idaction_category4' => '6', 'idaction_category5' => '5'), array('idaction_sku' => '1', 'idaction_name' => '1', 'idaction_category' => '5', 'idaction_category2' => '5', 'idaction_category3' => '8', 'idaction_category4' => '4', 'idaction_category5' => '6')); $this->assertEquals($expectedRows, $rows); }
public function getMigrations(Updater $updater) { return array($this->migration->db->addColumn('goal', 'allow_multiple', 'TINYINT(4) NOT NULL', 'case_sensitive'), $this->migration->db->sql('ALTER TABLE `' . Common::prefixTable('log_conversion') . '` ADD buster int unsigned NOT NULL AFTER revenue, DROP PRIMARY KEY, ADD PRIMARY KEY (idvisit, idgoal, buster)', Updater\Migration\Db::ERROR_CODE_DUPLICATE_COLUMN)); }
public function findPhpBinary() { if (defined('PHP_BINARY')) { if ($this->isValidPhpType(PHP_BINARY)) { return PHP_BINARY . ' -q'; } if ($this->isHhvmBinary(PHP_BINARY)) { return PHP_BINARY . ' --php'; } } $bin = ''; if (!empty($_SERVER['_']) && Common::isPhpCliMode()) { $bin = $this->getPhpCommandIfValid($_SERVER['_']); } if (empty($bin) && !empty($_SERVER['argv'][0]) && Common::isPhpCliMode()) { $bin = $this->getPhpCommandIfValid($_SERVER['argv'][0]); } if (!$this->isValidPhpType($bin)) { $bin = shell_exec('which php'); } if (!$this->isValidPhpType($bin)) { $bin = shell_exec('which php5'); } if (!$this->isValidPhpType($bin)) { return false; } $bin = trim($bin); if (!$this->isValidPhpVersion($bin)) { return false; } $bin .= ' -q'; return $bin; }
static function getSql() { return array('ALTER TABLE `' . Common::prefixTable('log_visit') . '` CHANGE custom_var_k1 custom_var_k1 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v1 custom_var_v1 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k2 custom_var_k2 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v2 custom_var_v2 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k3 custom_var_k3 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v3 custom_var_v3 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k4 custom_var_k4 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v4 custom_var_v4 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k5 custom_var_k5 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v5 custom_var_v5 VARCHAR(100) DEFAULT NULL' => false, 'ALTER TABLE `' . Common::prefixTable('log_conversion') . '` CHANGE custom_var_k1 custom_var_k1 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v1 custom_var_v1 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k2 custom_var_k2 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v2 custom_var_v2 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k3 custom_var_k3 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v3 custom_var_v3 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k4 custom_var_k4 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v4 custom_var_v4 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k5 custom_var_k5 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v5 custom_var_v5 VARCHAR(100) DEFAULT NULL' => false, 'ALTER TABLE `' . Common::prefixTable('log_link_visit_action') . '` CHANGE custom_var_k1 custom_var_k1 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v1 custom_var_v1 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k2 custom_var_k2 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v2 custom_var_v2 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k3 custom_var_k3 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v3 custom_var_v3 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k4 custom_var_k4 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v4 custom_var_v4 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k5 custom_var_k5 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v5 custom_var_v5 VARCHAR(100) DEFAULT NULL' => false); }
public 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; }
/** * Performs a batch insert into a specific table using either LOAD DATA INFILE or plain INSERTs, * as a fallback. On MySQL, LOAD DATA INFILE is 20x faster than a series of plain INSERTs. * * @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 $throwException Whether to throw an exception that was caught while trying * LOAD DATA INFILE, or not. * @throws Exception * @return bool True if the bulk LOAD was used, false if we fallback to plain INSERTs */ public static function tableInsertBatch($tableName, $fields, $values, $throwException = false) { $filePath = StaticContainer::get('path.tmp') . '/assets/' . $tableName . '-' . Common::generateUniqId() . '.csv'; $loadDataInfileEnabled = Config::getInstance()->General['enable_load_data_infile']; if ($loadDataInfileEnabled && Db::get()->hasBulkLoader()) { try { $fileSpec = array('delim' => "\t", 'quote' => '"', 'escape' => '\\\\', 'escapespecial_cb' => function ($str) { return str_replace(array(chr(92), chr(34)), array(chr(92) . chr(92), chr(92) . chr(34)), $str); }, 'eol' => "\r\n", 'null' => 'NULL'); // hack for charset mismatch if (!DbHelper::isDatabaseConnectionUTF8() && !isset(Config::getInstance()->database['charset'])) { $fileSpec['charset'] = 'latin1'; } self::createCSVFile($filePath, $fileSpec, $values); if (!is_readable($filePath)) { throw new Exception("File {$filePath} could not be read."); } $rc = self::createTableFromCSVFile($tableName, $fields, $filePath, $fileSpec); if ($rc) { unlink($filePath); return true; } } catch (Exception $e) { if ($throwException) { throw $e; } } } // if all else fails, fallback to a series of INSERTs @unlink($filePath); self::tableInsertBatchIterate($tableName, $fields, $values); return false; }
/** * @see ViewDataTable::main() * @return mixed */ public function render() { $view = new View('@CoreVisualizations/_dataTableViz_sparklines.twig'); $columnsList = array(); if ($this->config->hasSparklineMetrics()) { foreach ($this->config->getSparklineMetrics() as $cols) { $columns = $cols['columns']; if (!is_array($columns)) { $columns = array($columns); } $columnsList = array_merge($columns, $columnsList); } } $view->allMetricsDocumentation = Metrics::getDefaultMetricsDocumentation(); $this->requestConfig->request_parameters_to_modify['columns'] = $columnsList; $this->requestConfig->request_parameters_to_modify['format_metrics'] = '1'; if (!empty($this->requestConfig->apiMethodToRequestDataTable)) { $this->fetchConfiguredSparklines(); } $view->sparklines = $this->config->getSortedSparklines(); $view->isWidget = Common::getRequestVar('widget', 0, 'int'); $view->titleAttributes = $this->config->title_attributes; $view->footerMessage = $this->config->show_footer_message; $view->areSparklinesLinkable = $this->config->areSparklinesLinkable(); $view->title = ''; if ($this->config->show_title) { $view->title = $this->config->title; } return $view->render(); }
/** * Return SQL to be executed in this update. * * SQL queries should be defined here, instead of in `doUpdate()`, since this method is used * in the `core:update` command when displaying the queries an update will run. If you execute * queries directly in `doUpdate()`, they won't be displayed to the user. * * @param Updater $updater * @return array ``` * array( * 'ALTER .... ' => '1234', // if the query fails, it will be ignored if the error code is 1234 * 'ALTER .... ' => false, // if an error occurs, the update will stop and fail * // and user will have to manually run the query * ) * ``` */ public function getMigrationQueries(Updater $updater) { $errorCodesToIgnore = array(1060); $tableName = Common::prefixTable('log_visit'); $updateSql = "ALTER TABLE `" . $tableName . "` CHANGE `example` `example` BOOLEAN NOT NULL"; return array(); }
/** * 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 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 function getNumWebsites() { if (!isset($this->websitesCache)) { $this->websitesCache = (int) Db::get()->fetchOne('SELECT count(idsite) FROM ' . Common::prefixTable('site')); } return $this->websitesCache; }
/** * Constructor. */ public function __construct($idSite = false) { parent::__construct(); $this->jsClass = "SegmentSelectorControl"; $this->cssIdentifier = "segmentEditorPanel"; $this->cssClass = "piwikTopControl"; $this->idSite = $idSite ?: Common::getRequestVar('idSite', false, 'int'); $this->selectedSegment = Common::getRequestVar('segment', false, 'string'); $segments = APIMetadata::getInstance()->getSegmentsMetadata($this->idSite); $segmentsByCategory = $customVariablesSegments = array(); foreach ($segments as $segment) { if ($segment['category'] == Piwik::translate('General_Visit') && ($segment['type'] == 'metric' && $segment['segment'] != 'visitIp')) { $metricsLabel = Piwik::translate('General_Metrics'); $metricsLabel[0] = strtolower($metricsLabel[0]); $segment['category'] .= ' (' . $metricsLabel . ')'; } $segmentsByCategory[$segment['category']][] = $segment; } uksort($segmentsByCategory, array($this, 'sortSegmentCategories')); $this->createRealTimeSegmentsIsEnabled = Config::getInstance()->General['enable_create_realtime_segments']; $this->segmentsByCategory = $segmentsByCategory; $this->nameOfCurrentSegment = ''; $this->isSegmentNotAppliedBecauseBrowserArchivingIsDisabled = 0; $this->availableSegments = API::getInstance()->getAll($this->idSite); foreach ($this->availableSegments as &$savedSegment) { $savedSegment['name'] = Common::sanitizeInputValue($savedSegment['name']); if (!empty($this->selectedSegment) && $this->selectedSegment == $savedSegment['definition']) { $this->nameOfCurrentSegment = $savedSegment['name']; $this->isSegmentNotAppliedBecauseBrowserArchivingIsDisabled = $this->wouldApplySegment($savedSegment) ? 0 : 1; } } $this->authorizedToCreateSegments = SegmentEditorAPI::getInstance()->isUserCanAddNewSegment($this->idSite); $this->isUserAnonymous = Piwik::isUserIsAnonymous(); $this->segmentTranslations = $this->getTranslations(); }
/** * Insert constructor. * @param string $table * @param array $columnValuePairs array(columnName => columnValue) */ public function __construct($table, $columnValuePairs) { $columns = implode('`, `', array_keys($columnValuePairs)); $bind = array_values($columnValuePairs); $sql = sprintf('INSERT INTO `%s` (`%s`) VALUES (%s)', $table, $columns, Common::getSqlStringFieldsArray($columnValuePairs)); parent::__construct($sql, $bind, static::ERROR_CODE_DUPLICATE_ENTRY); }
/** * send email to Piwik team and display nice thanks * @throws Exception */ function sendFeedback() { $email = Common::getRequestVar('email', '', 'string'); $body = Common::getRequestVar('body', '', 'string'); $category = Common::getRequestVar('category', '', 'string'); $nonce = Common::getRequestVar('nonce', '', 'string'); $view = new View('@Feedback/sendFeedback'); $view->feedbackEmailAddress = Config::getInstance()->General['feedback_email_address']; try { $minimumBodyLength = 40; if (strlen($body) < $minimumBodyLength || strpos($email, 'probe@') !== false || strpos($body, '<probe') !== false) { throw new Exception(Piwik::translate('Feedback_ExceptionBodyLength', array($minimumBodyLength))); } if (!Piwik::isValidEmailString($email)) { throw new Exception(Piwik::translate('UsersManager_ExceptionInvalidEmail')); } if (preg_match('/https?:/i', $body)) { throw new Exception(Piwik::translate('Feedback_ExceptionNoUrls')); } if (!Nonce::verifyNonce('Feedback.sendFeedback', $nonce)) { throw new Exception(Piwik::translate('General_ExceptionNonceMismatch')); } Nonce::discardNonce('Feedback.sendFeedback'); $mail = new Mail(); $mail->setFrom(Common::unsanitizeInputValue($email)); $mail->addTo($view->feedbackEmailAddress, 'Piwik Team'); $mail->setSubject('[ Feedback form - Piwik ] ' . $category); $mail->setBodyText(Common::unsanitizeInputValue($body) . "\n" . 'Piwik ' . Version::VERSION . "\n" . 'IP: ' . IP::getIpFromHeader() . "\n" . 'URL: ' . Url::getReferrer() . "\n"); @$mail->send(); } catch (Exception $e) { $view->errorString = $e->getMessage(); $view->message = $body; } return $view->render(); }