public function deleteGoal($idSite, $idGoal)
{
Piwik::checkUserHasAdminAccess($idSite);
Piwik_Query("UPDATE " . Piwik::prefixTable('goal') . "\n\t\t\t\t\t\t\t\t\t\tSET deleted = 1\n\t\t\t\t\t\t\t\t\t\tWHERE idsite = ? \n\t\t\t\t\t\t\t\t\t\t\tAND idgoal = ?", array($idSite, $idGoal));
Zend_Registry::get('db')->query("DELETE FROM " . Piwik::prefixTable("log_conversion") . " WHERE idgoal = ?", $idGoal);
Piwik_Common::regenerateCacheWebsiteAttributes($idSite);
}
/**
* Records the layout in the DB for the given user.
*
* @param string $login
* @param int $idDashboard
* @param string $layout
*/
protected function saveLayoutForUser($login, $idDashboard, $layout)
{
$paramsBind = array($login, $idDashboard, $layout, $layout);
Piwik_Query('INSERT INTO ' . Piwik::prefixTable('user_dashboard') . ' (login, iddashboard, layout)
VALUES (?,?,?)
ON DUPLICATE KEY UPDATE layout=?', $paramsBind);
}
/**
* Function called to save the Feedburner ID entered in the form
*
*/
function saveFeedburnerName()
{
// we save the value in the DB for an authenticated user
if (Piwik::getCurrentUserLogin() != 'anonymous') {
Piwik_Query('UPDATE ' . Piwik::prefixTable('site') . '
SET feedburnerName = ? WHERE idsite = ?', array(Piwik_Common::getRequestVar('name', '', 'string'), Piwik_Common::getRequestVar('idSite', 1, 'int')));
}
}
/**
* This method displays a text containing an help about "How to build plugins for Piwik".
* This help is then used on http://piwik.org/docs/plugins/functions
*
*/
function index()
{
$out = '';
$out .= '<i>This page aims to list the different functions you can use when programming plugins for Piwik.</i><br />';
$out .= '<b>Be careful, the following APIs may change in the near future as Piwik is still in development.</b><br />';
$out .= '<h2>General</h2>';
$out .= '<h3>Accessible from your plugin controller</h3>';
$out .= '<code>$this->date</code> = current selected <b>Piwik_Date</b> object (<a href="http://dev.piwik.org/trac/browser/trunk/core/Date.php">class</a>)<br />';
$out .= '<code>$period = Piwik_Common::getRequestVar("period");</code> - Get the current selected period<br />';
$out .= '<code>$idSite = Piwik_Common::getRequestVar("idSite");</code> - Get the selected idSite<br />';
$out .= '<code>$site = new Piwik_Site($idSite);</code> - Build the Piwik_Site object (<a href="http://dev.piwik.org/trac/browser/trunk/core/Site.php">class</a>)<br />';
$out .= '<code>$this->str_date</code> = current selected date in YYYY-MM-DD format<br />';
$out .= '<h3>Misc</h3>';
$out .= '<code>Piwik_AddMenu( $mainMenuName, $subMenuName, $url );</code> - Adds an entry to the menu in the Piwik interface (See the example in the <a href="http://dev.piwik.org/trac/browser/tags/1.0/plugins/UserCountry/UserCountry.php#L76">UserCountry Plugin file</a>)<br />';
$out .= '<code>Piwik_AddWidget( $widgetCategory, $widgetName, $controllerName, $controllerAction, $customParameters = array());</code> - Adds a widget that users can add in the dashboard, or export using the Widgets link at the top of the screen. See the example in the <a href="http://dev.piwik.org/trac/browser/tags/1.0/plugins/UserCountry/UserCountry.php#L70">UserCountry Plugin file</a> or any other plugin)<br />';
$out .= '<code>Piwik_Common::prefixTable("site")</code> = <b>' . Piwik_Common::prefixTable("site") . '</b><br />';
$out .= '<h2>User access</h2>';
$out .= '<code>Piwik::getCurrentUserLogin()</code> = <b>' . Piwik::getCurrentUserLogin() . '</b><br />';
$out .= '<code>Piwik::isUserHasSomeAdminAccess()</code> = <b>' . self::boolToString(Piwik::isUserHasSomeAdminAccess()) . '</b><br />';
$out .= '<code>Piwik::isUserHasAdminAccess( array $idSites = array(1,2) )</code> = <b>' . self::boolToString(Piwik::isUserHasAdminAccess(array(1, 2))) . '</b><br />';
$out .= '<code>Piwik::isUserHasViewAccess( array $idSites = array(1) ) </code> = <b>' . self::boolToString(Piwik::isUserHasViewAccess(array(1))) . '</b><br />';
$out .= '<code>Piwik::isUserIsSuperUser()</code> = <b>' . self::boolToString(Piwik::isUserIsSuperUser()) . '</b><br />';
$out .= '<h2>Execute SQL queries</h2>';
$txtQuery = "SELECT token_auth FROM " . Piwik_Common::prefixTable('user') . " WHERE login = ?";
$result = Piwik_FetchOne($txtQuery, array('anonymous'));
$out .= '<code>Piwik_FetchOne("' . $txtQuery . '", array("anonymous"))</code> = <b>' . var_export($result, true) . '</b><br />';
$out .= '<br />';
$query = Piwik_Query($txtQuery, array('anonymous'));
$fetched = $query->fetch();
$token_auth = $fetched['token_auth'];
$out .= '<code>$query = Piwik_Query("' . $txtQuery . '", array("anonymous"))</code><br />';
$out .= '<code>$fetched = $query->fetch();</code><br />';
$out .= 'At this point, we have: <code>$fetched[\'token_auth\'] == <b>' . var_export($token_auth, true) . '</b></code><br />';
$out .= '<h2>Example Sites information API</h2>';
$out .= '<code>Piwik_SitesManager_API::getInstance()->getSitesWithViewAccess()</code> = <b><pre>' . var_export(Piwik_SitesManager_API::getInstance()->getSitesWithViewAccess(), true) . '</pre></b><br />';
$out .= '<code>Piwik_SitesManager_API::getInstance()->getSitesWithAdminAccess()</code> = <b><pre>' . var_export(Piwik_SitesManager_API::getInstance()->getSitesWithAdminAccess(), true) . '</pre></b><br />';
$out .= '<h2>Example API Users information</h2>';
$out .= 'View the list of API methods you can call on <a href="http://piwik.org/docs/analytics-api/reference">API reference</a><br />';
$out .= 'For example you can try <code>Piwik_UsersManager_API::getInstance()->getUsersSitesFromAccess("view");</code> or <code>Piwik_UsersManager_API::getInstance()->deleteUser("userToDelete");</code><br />';
$out .= '<h2>Javascript in Piwik</h2>';
$out .= '<h3>i18n internationalization</h3>';
$out .= 'In order to translate strings within Javascript code, you can use the javascript function _pk_translate( token );.
<ul><li>The "token" parameter is the string unique key found in the translation file. For this token string to be available in Javascript, you must
suffix your token by "_js" in the language file. For example, you can add <code>\'Goals_AddGoal_js\' => \'Add Goal\',</code> in the lang/en.php file</li>
<li>You then need to instruct Piwik to load your Javascript translations for your plugin; by default, all translation strings are not loaded in Javascript for performance reasons. This can be done by calling a custom-made Smarty modifier before the Javascript code requiring translations, eg.
<code>{loadJavascriptTranslations plugins=\'$YOUR_PLUGIN_NAME\'}</code>. In our previous example, the $YOUR_PLUGIN_NAME being Goals, we would write <code>{loadJavascriptTranslations plugins=\'Goals\'}</code>
</li><li>You can then print this string from your JS code by doing <code>_pk_translate(\'Goals_AddGoal_js\');</code>.
</li></ul>';
$out .= '<h3>Reload a widget in the dashboard</h3>';
$out .= 'It is sometimes useful to reload one widget in the dashboard (for example, every 20 seconds for a real time widget, or after a setting change).
You can easily force your widget to reload in the dashboard by calling the helper function <code>$(this).parents(\'[widgetId]\').dashboardWidget(\'reload\');</code>.';
$out .= '<h2>Smarty plugins</h2>';
$out .= 'There are some builtin plugins for Smarty especially developped for Piwik. <br />
You can find them on the <a href="http://dev.piwik.org/trac/browser/trunk/core/SmartyPlugins">SVN at /trunk/core/SmartyPlugins</a>. <br />
More documentation to come about smarty plugins.<br />';
echo $out;
}
/**
* Sets the option value in the database
*
* @param string $name
* @param string $value
* @param int $autoload if set to 1, this option value will be automatically loaded; should be set to 1 for options that will always be used in the Piwik request.
*/
public function set($name, $value, $autoload = 0)
{
$autoload = (int)$autoload;
Piwik_Query('INSERT INTO `'. Piwik::prefixTable('option') . '` (option_name, option_value, autoload) '.
' VALUES (?, ?, ?) '.
' ON DUPLICATE KEY UPDATE option_value = ?',
array($name, $value, $autoload, $value));
$this->all[$name] = $value;
}
function optimizeArchiveTable()
{
$tablesPiwik = Piwik::getTablesInstalled();
$archiveTables = array_filter($tablesPiwik, array("Piwik_CoreAdminHome", "isArchiveTable"));
if (empty($archiveTables)) {
return;
}
$query = "OPTIMIZE TABLE " . implode(",", $archiveTables);
Piwik_Query($query);
}
public function test_getOption()
{
// empty table, expect false (i.e., not found)
$this->assertTrue(Piwik_GetOption('anonymous_defaultReport') === false);
// populate table, expect '1' (i.e., found)
Piwik_Query("INSERT INTO " . Piwik_Common::prefixTable('option') . " VALUES ('anonymous_defaultReport', '1',true)");
$this->assertTrue(Piwik_GetOption('anonymous_defaultReport') === '1');
// delete row (bypassing API), expect '1' (i.e., from cache)
Piwik_Query("DELETE FROM " . Piwik_Common::prefixTable('option') . " WHERE option_name = ?", array('anonymous_defaultReport'));
$this->assertTrue(Piwik_GetOption('anonymous_defaultReport') === '1');
// force cache reload, expect false (i.e., not found)
Piwik_Option::getInstance()->clearCache();
$this->assertTrue(Piwik_GetOption('anonymous_defaultReport') === false);
}
/** Log a logout */
public function logLogout($idvisit)
{
$bind = array(':idvisit' => intval($idvisit), ':now' => self::now());
$sql = '
UPDATE
' . self::loginTable() . ' AS login
SET
datetime_logout = :now,
duration = TIMESTAMPDIFF(MINUTE, datetime_login, :now)
WHERE
idvisit = :idvisit AND
datetime_logout = "0000-00-00 00:00:00"
';
return Piwik_Query($sql, $bind);
}
static function update()
{
try {
$dashboards = Piwik_FetchAll('SELECT * FROM `' . Piwik_Common::prefixTable('user_dashboard') . '`');
foreach ($dashboards as $dashboard) {
$idDashboard = $dashboard['iddashboard'];
$login = $dashboard['login'];
$layout = $dashboard['layout'];
$layout = html_entity_decode($layout);
$layout = str_replace("\\\"", "\"", $layout);
Piwik_Query('UPDATE `' . Piwik_Common::prefixTable('user_dashboard') . '` SET layout = ? WHERE iddashboard = ? AND login = ?', array($layout, $idDashboard, $login));
}
Piwik_Updater::updateDatabase(__FILE__, self::getSql());
} catch (Exception $e) {
}
}
static function update()
{
Piwik_Updater::updateDatabase(__FILE__, self::getSql());
if (!Piwik_PluginsManager::getInstance()->isPluginLoaded('PDFReports')) {
return;
}
try {
// Piwik_Common::prefixTable('pdf') has been heavily refactored to be more generic
// The following actions are taken in this update script :
// - create the new generic report table Piwik_Common::prefixTable('report')
// - migrate previous reports, if any, from Piwik_Common::prefixTable('pdf') to Piwik_Common::prefixTable('report')
// - delete Piwik_Common::prefixTable('pdf')
$reports = Piwik_FetchAll('SELECT * FROM `' . Piwik_Common::prefixTable('pdf') . '`');
foreach ($reports as $report) {
$idreport = $report['idreport'];
$idsite = $report['idsite'];
$login = $report['login'];
$description = $report['description'];
$period = $report['period'];
$format = $report['format'];
$display_format = $report['display_format'];
$email_me = $report['email_me'];
$additional_emails = $report['additional_emails'];
$reports = $report['reports'];
$ts_created = $report['ts_created'];
$ts_last_sent = $report['ts_last_sent'];
$deleted = $report['deleted'];
$parameters = array();
if (!is_null($additional_emails)) {
$parameters[Piwik_PDFReports::ADDITIONAL_EMAILS_PARAMETER] = preg_split('/,/', $additional_emails);
}
$parameters[Piwik_PDFReports::EMAIL_ME_PARAMETER] = is_null($email_me) ? Piwik_PDFReports::EMAIL_ME_PARAMETER_DEFAULT_VALUE : (bool) $email_me;
$parameters[Piwik_PDFReports::DISPLAY_FORMAT_PARAMETER] = $display_format;
Piwik_Query('INSERT INTO `' . Piwik_Common::prefixTable('report') . '` SET
idreport = ?, idsite = ?, login = ?, description = ?, period = ?,
type = ?, format = ?, reports = ?, parameters = ?, ts_created = ?,
ts_last_sent = ?, deleted = ?', array($idreport, $idsite, $login, $description, is_null($period) ? Piwik_PDFReports::DEFAULT_PERIOD : $period, Piwik_PDFReports::EMAIL_TYPE, is_null($format) ? Piwik_PDFReports::DEFAULT_REPORT_FORMAT : $format, Piwik_Common::json_encode(preg_split('/,/', $reports)), Piwik_Common::json_encode($parameters), $ts_created, $ts_last_sent, $deleted));
}
Piwik_Query('DROP TABLE `' . Piwik_Common::prefixTable('pdf') . '`');
} catch (Exception $e) {
}
}
public function test_RunAllTests()
{
parent::test_RunAllTests();
if (Test_Integration::$apiTestingLevel != Test_Integration::NO_API_TESTING) {
$this->_checkExpectedRowsInArchiveTable();
// Force Purge to happen again this table (since it was called at end of archiving)
Piwik_SetOption(Piwik_ArchiveProcessing_Period::FLAG_TABLE_PURGED . Piwik_Common::prefixTable('archive_blob_2010_12'), '2010-01-01');
Piwik_SetOption(Piwik_ArchiveProcessing_Period::FLAG_TABLE_PURGED . Piwik_Common::prefixTable('archive_blob_2011_01'), '2010-01-01');
foreach (array('archive_numeric_2010_12', 'archive_blob_2010_12', 'archive_numeric_2011_01', 'archive_blob_2011_01') as $table) {
// INSERT some ERROR records.
// We use period=range since the test below is restricted to these
Piwik_Query(" INSERT INTO `" . Piwik_Common::prefixTable($table) . "` (`idarchive` ,`name` ,`idsite` ,`date1` ,`date2` ,`period` ,`ts_archived` ,`value`)\n\t\t\t\t\tVALUES (10000, 'done', '1', '2010-12-06', '2010-12-06', '" . Piwik::$idPeriods['range'] . "', '2010-12-06' , '" . Piwik_ArchiveProcessing::DONE_ERROR . "'), \n\t\t\t\t\t\t\t(20000, 'doneX', '2', '2012-07-06', '2012-07-06', '" . Piwik::$idPeriods['range'] . "', '' , '" . Piwik_ArchiveProcessing::DONE_ERROR . "')");
}
// We've added error rows which should fail the following test
$this->_checkExpectedRowsInArchiveTable($expectPass = false);
// Test Scheduled tasks Table Optimize, and Delete old reports
Piwik_CoreAdminHome::purgeOutdatedArchives();
Piwik_CoreAdminHome::optimizeArchiveTable();
// Check that only the good reports were not deleted:
$this->_checkExpectedRowsInArchiveTable();
}
}
function recordFunnelSteps($notification)
{
$info = $notification->getNotificationInfo();
$idSite = $info['idSite'];
printDebug('Looking for funnel steps');
$funnels = Piwik_Funnels_API::getInstance()->getFunnels($idSite);
if (count($funnels) > 0) {
$idVisit = $info['idVisit'];
$idLinkVisitAction = $info['idLinkVisitAction'];
$idRefererAction = $info['idRefererAction'];
$action = $notification->getNotificationObject();
$actionName = $action->getActionName();
$sanitizedUrl = $action->getActionUrl();
$actionUrl = htmlspecialchars_decode($sanitizedUrl);
$idActionUrl = $action->getIdActionUrl();
$url = Piwik_Common::getRequestVar('url', '', 'string', $action->getRequest());
printDebug("idActionUrl" . $idActionUrl . " idSite " . $idSite . " idVisit " . $idVisit . " idRefererAction " . $idRefererAction);
# Is this the next action for a recorded funnel step?
$previous_step_action = Piwik_Query("UPDATE " . Piwik_Common::prefixTable('log_funnel_step') . "\n\t\t\t\t\t\t\t\t\t\t\t\t\tSET idaction_url_next = ?\n\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE idsite = ? \n\t\t\t\t\t\t\t\t\t\t\t\t\tAND idvisit = ? \n\t\t\t\t\t\t\t\t\t\t\t\t\tAND idaction_url = ?\n\t\t\t\t\t\t\t\t\t\t\t\t\tAND idaction_url_next is null", array($idActionUrl, $idSite, $idVisit, $idRefererAction));
}
foreach ($funnels as &$funnel) {
$steps = $funnel['steps'];
foreach ($steps as &$step) {
if ($step['url'] == $actionUrl or $step['name'] == $actionName) {
printDebug("Matched Goal Funnel " . $funnel['idfunnel'] . " Step " . $step['idstep'] . "(name: " . $step['name'] . ", url: " . $step['url'] . "). ");
$serverTime = time();
$datetimeServer = Piwik_Tracker::getDatetimeFromTimestamp($serverTime);
// Look to see if this step has already been recorded for this visit
$exists = Piwik_FetchOne("SELECT idlink_va\n\t\t\t\t\t\t\t\t\t\t\t FROM " . Piwik_Common::prefixTable('log_funnel_step') . " \n\t\t\t\t\t\t\t\t\t\t\t WHERE idsite = ? \n\t\t\t\t\t\t\t\t\t\t\t AND idfunnel = ?\n\t\t\t\t\t\t\t\t\t\t\t AND idstep = ?\n\t\t\t\t\t\t\t\t\t\t\t AND idvisit = ?", array($idSite, $funnel['idfunnel'], $step['idstep'], $idVisit));
// Record it if not
if (!$exists) {
printDebug("Recording...");
Piwik_Query("INSERT INTO " . Piwik_Common::prefixTable('log_funnel_step') . "\n\t\t\t\t\t\t\t\t\t(idvisit, idsite, idaction_url, url, \n\t\t\t\t\t\t\t\t\t idgoal, idfunnel, idstep, idlink_va, \n\t\t\t\t\t\t\t\t\t idaction_url_ref, server_time)\n\t\t\t\t\t\t\t\t\tVALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", array($idVisit, $idSite, $idActionUrl, $url, $funnel['idgoal'], $step['idfunnel'], $step['idstep'], $idLinkVisitAction, $idRefererAction, $datetimeServer));
}
}
}
}
}
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)) {
Piwik_Config::getInstance()->database['tables_prefix'] = self::$fixture->tablesPrefix;
Piwik_Common::$cachedTablePrefix = null;
}
Piwik_Query("USE " . $dbName);
$installedFixture = Piwik_GetOption('benchmark_fixture_name');
} catch (Exception $ex) {
// ignore
}
$createEmptyDatabase = $fixtureName != $installedFixture;
parent::setUpBeforeClass($dbName, $createEmptyDatabase, $createConfig = false);
// if we created an empty database, setup the fixture
if ($createEmptyDatabase) {
self::$fixture->setUp();
Piwik_SetOption('benchmark_fixture_name', $fixtureName);
}
}
/**
* Event hook that adds a row into the DB that references unused idaction AFTER LogDataPurger
* does the insert into the temporary table. When log_actions are deleted, this idaction should still
* be kept. w/ the wrong strategy, it won't be and there will be a dangling reference
* in the log_link_visit_action table.
*
* @param Piwik_Event_Notification $notification notification object
*/
public function addReferenceToUnusedAction($notification)
{
$unusedIdAction = $this->unusedIdAction;
if (empty($unusedIdAction)) {
return;
}
$tempTableName = Piwik_Common::prefixTable(Piwik_PrivacyManager_LogDataPurger::TEMP_TABLE_NAME);
$logLinkVisitActionTable = Piwik_Common::prefixTable("log_link_visit_action");
$sql = "INSERT INTO {$logLinkVisitActionTable}\n\t\t\t\t\t\t\t(idsite, idvisitor, server_time, idvisit, idaction_url, idaction_url_ref,\n\t\t\t\t\t\t\tidaction_name, idaction_name_ref, time_spent_ref_action)\n\t\t\t\t\t VALUES (1, 'abc', NOW(), 15, {$unusedIdAction}, {$unusedIdAction},\n\t\t\t\t\t\t\t {$unusedIdAction}, {$unusedIdAction}, 1000)";
Piwik_Query($sql);
}
/**
* Inserts a record in the right table (either NUMERIC or BLOB)
*
* @param Piwik_ArchiveProcessing_Record $record
*/
protected function insertRecord($record)
{
// table to use to save the data
if (is_numeric($record->value)) {
$table = $this->tableArchiveNumeric;
} else {
$table = $this->tableArchiveBlob;
}
// ignore duplicate idarchive
// @see http://dev.piwik.org/trac/ticket/987
$query = "INSERT IGNORE INTO " . $table->getTableName() . " (idarchive, idsite, date1, date2, period, ts_archived, name, value)\n\t\t\t\t\tVALUES (?,?,?,?,?,?,?,?)";
Piwik_Query($query, array($this->idArchive, $this->idsite, $this->strDateStart, $this->strDateEnd, $this->periodId, date("Y-m-d H:i:s"), $record->name, $record->value));
}
/**
* Called at the end of the archiving process.
* Does some cleaning job in the database.
*/
protected function postCompute()
{
parent::postCompute();
$blobTable = $this->tableArchiveBlob->getTableName();
$numericTable = $this->tableArchiveNumeric->getTableName();
$key = 'lastPurge_' . $blobTable;
$timestamp = Piwik_GetOption($key);
if (!$timestamp || $timestamp < time() - 86400) {
Piwik_SetOption($key, time());
// we delete out of date daily archives from table, maximum once per day
// we only delete archives processed that are older than 1 day, to not delete archives we just processed
$yesterday = Piwik_Date::factory('yesterday')->getDateTime();
$result = Piwik_FetchAll("\n\t\t\t\t\t\t\tSELECT idarchive\n\t\t\t\t\t\t\tFROM {$numericTable}\n\t\t\t\t\t\t\tWHERE name LIKE 'done%'\n\t\t\t\t\t\t\t\tAND value = " . Piwik_ArchiveProcessing::DONE_OK_TEMPORARY . "\n\t\t\t\t\t\t\t\tAND ts_archived < ?", array($yesterday));
$idArchivesToDelete = array();
if (!empty($result)) {
foreach ($result as $row) {
$idArchivesToDelete[] = $row['idarchive'];
}
$query = "DELETE \n \t\t\t\t\t\tFROM %s\n \t\t\t\t\t\tWHERE idarchive IN (" . implode(',', $idArchivesToDelete) . ")\n \t\t\t\t\t\t";
Piwik_Query(sprintf($query, $blobTable));
Piwik_Query(sprintf($query, $numericTable));
}
Piwik::log("Purging temporary archives: done [ purged archives older than {$yesterday} from {$blobTable} and {$numericTable} ] [Deleted IDs: " . implode(',', $idArchivesToDelete) . "]");
// Deleting "Custom Date Range" reports after 1 day, since they can be re-processed
// and would take up unecessary space
$query = "DELETE \n \t\t\t\t\tFROM %s\n \t\t\t\t\tWHERE period = ?\n \t\t\t\t\t\tAND ts_archived < ?";
$bind = array(Piwik::$idPeriods['range'], $yesterday);
Piwik_Query(sprintf($query, $blobTable), $bind);
Piwik_Query(sprintf($query, $numericTable), $bind);
} else {
Piwik::log("Purging temporary archives: skipped.");
}
if (!isset($this->archives)) {
return;
}
foreach ($this->archives as $archive) {
destroy($archive);
}
$this->archives = array();
}
/**
* Given a monthly archive table, will delete all reports that are now outdated,
* or reports that ended with an error
*/
public static function doPurgeOutdatedArchives($numericTable)
{
$blobTable = str_replace("numeric", "blob", $numericTable);
$key = self::FLAG_TABLE_PURGED . $blobTable;
$timestamp = Piwik_GetOption($key);
// we shall purge temporary archives after their timeout is finished, plus an extra 6 hours
// in case archiving is disabled or run once a day, we give it this extra time to run
// and re-process more recent records...
// TODO: Instead of hardcoding 6 we should put the actual number of hours between 2 archiving runs
$temporaryArchivingTimeout = self::getTodayArchiveTimeToLive();
$purgeEveryNSeconds = max($temporaryArchivingTimeout, 6 * 3600);
// we only delete archives if we are able to process them, otherwise, the browser might process reports
// when &segment= is specified (or custom date range) and would below, delete temporary archives that the
// browser is not able to process until next cron run (which could be more than 1 hour away)
if (self::isRequestAuthorizedToArchive() && (!$timestamp || $timestamp < time() - $purgeEveryNSeconds)) {
Piwik_SetOption($key, time());
// If Browser Archiving is enabled, it is likely there are many more temporary archives
// We delete more often which is safe, since reports are re-processed on demand
if (self::isBrowserTriggerArchivingEnabled()) {
$purgeArchivesOlderThan = Piwik_Date::factory(time() - 2 * $temporaryArchivingTimeout)->getDateTime();
} else {
$purgeArchivesOlderThan = Piwik_Date::factory('today')->getDateTime();
}
$result = Piwik_FetchAll("\n\t\t\t\tSELECT idarchive\n\t\t\t\tFROM {$numericTable}\n\t\t\t\tWHERE name LIKE 'done%'\n\t\t\t\t\tAND (( value = " . Piwik_ArchiveProcessing::DONE_OK_TEMPORARY . "\n\t\t\t\t\t\t AND ts_archived < ?)\n\t\t\t\t\t\t OR value = " . Piwik_ArchiveProcessing::DONE_ERROR . ")", array($purgeArchivesOlderThan));
$idArchivesToDelete = array();
if (!empty($result)) {
foreach ($result as $row) {
$idArchivesToDelete[] = $row['idarchive'];
}
$query = "DELETE \n \t\t\t\t\t\tFROM %s\n \t\t\t\t\t\tWHERE idarchive IN (" . implode(',', $idArchivesToDelete) . ")\n \t\t\t\t\t\t";
Piwik_Query(sprintf($query, $numericTable));
// Individual blob tables could be missing
try {
Piwik_Query(sprintf($query, $blobTable));
} catch (Exception $e) {
}
}
Piwik::log("Purging temporary archives: done [ purged archives older than {$purgeArchivesOlderThan} from {$blobTable} and {$numericTable} ] [Deleted IDs: " . implode(',', $idArchivesToDelete) . "]");
// Deleting "Custom Date Range" reports after 1 day, since they can be re-processed
// and would take up unecessary space
$yesterday = Piwik_Date::factory('yesterday')->getDateTime();
$query = "DELETE \n \t\t\t\t\tFROM %s\n \t\t\t\t\tWHERE period = ?\n \t\t\t\t\t\tAND ts_archived < ?";
$bind = array(Piwik::$idPeriods['range'], $yesterday);
Piwik::log("Purging Custom Range archives: done [ purged archives older than {$yesterday} from {$blobTable} and {$numericTable} ]");
Piwik_Query(sprintf($query, $numericTable), $bind);
// Individual blob tables could be missing
try {
Piwik_Query(sprintf($query, $blobTable), $bind);
} catch (Exception $e) {
}
// these tables will be OPTIMIZEd daily in a scheduled task, to claim lost space
} else {
Piwik::log("Purging temporary archives: skipped.");
}
}
/**
* 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 Piwik_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)
{
$fieldList = '(' . join(',', $fields) . ')';
$ignore = '';
if ($ignoreWhenDuplicate) {
$ignore = ' IGNORE ';
}
foreach ($values as $row) {
$toRecord = implode(', ', $row);
$query = "INSERT {$ignore}\n\t\t\t\t\tINTO " . $tableName . "\n\t\t\t\t\t{$fieldList}\n\t\t\t\t\tVALUES (" . Piwik_Archive_Array::getSqlStringFieldsArray($row) . ")";
Piwik_Query($query, $row);
}
}
function uninstall()
{
// add column hostname / hostname ext in the visit table
$query = "ALTER TABLE `" . Piwik::prefixTable('log_visit') . "` DROP `location_provider`";
Piwik_Query($query);
}
public static function truncateAllTables()
{
$tablesAlreadyInstalled = self::getTablesInstalled($forceReload = true);
foreach ($tablesAlreadyInstalled as $table) {
Piwik_Query("TRUNCATE `{$table}`");
}
}
function deleteUserLanguage($notification)
{
$userLogin = $notification->getNotificationObject();
Piwik_Query('DELETE FROM ' . Piwik_Common::prefixTable('user_language') . ' WHERE login = ?', $userLogin);
}
/**
* Sets the language for the user
*
* @param string $login
* @param string $languageCode
* @return bool
*/
public function setLanguageForUser($login, $languageCode)
{
Piwik::checkUserIsSuperUserOrTheUser($login);
if (!$this->isLanguageAvailable($languageCode)) {
return false;
}
$paramsBind = array($login, $languageCode, $languageCode);
Piwik_Query('INSERT INTO ' . Piwik_Common::prefixTable('user_language') . ' (login, language)
VALUES (?,?)
ON DUPLICATE KEY UPDATE language=?', $paramsBind);
}
/**
* Soft deletes a given Goal.
* Stats data in the archives will still be recorded, but not displayed.
*
* @param int $idSite
* @param int $idGoal
* @return void
*/
public function deleteGoal($idSite, $idGoal)
{
Piwik::checkUserHasAdminAccess($idSite);
Piwik_Query("UPDATE " . Piwik_Common::prefixTable('goal') . "\n\t\t\t\t\t\t\t\t\t\tSET deleted = 1\n\t\t\t\t\t\t\t\t\t\tWHERE idsite = ? \n\t\t\t\t\t\t\t\t\t\t\tAND idgoal = ?", array($idSite, $idGoal));
Piwik_DeleteAllRows(Piwik_Common::prefixTable("log_conversion"), "WHERE idgoal = ?", 100000, array($idGoal));
Piwik_Common::regenerateCacheWebsiteAttributes($idSite);
}
/**
* Delete goals recorded for this site
*
* @param Piwik_Event_Notification $notification notification object
*/
function deleteSiteGoals($notification)
{
$idSite =& $notification->getNotificationObject();
Piwik_Query("DELETE FROM " . Piwik_Common::prefixTable('goal') . " WHERE idsite = ? ", array($idSite));
}
/**
* Sets the language for the user
*
* @param string $login
* @param string $languageCode
*/
static public function setLanguageForUser($login, $languageCode)
{
Piwik::checkUserIsSuperUserOrTheUser($login);
$paramsBind = array($login, $languageCode, $languageCode);
Piwik_Query('INSERT INTO '.Piwik::prefixTable('user_language') .
' (login, language)
VALUES (?,?)
ON DUPLICATE KEY UPDATE language=?',
$paramsBind);
}
public function deleteFunnel($idSite, $idGoal, $idFunnel)
{
Piwik::checkUserHasAdminAccess($idSite);
Piwik_Query("UPDATE " . Piwik_Common::prefixTable('funnel') . "\n\t\t\t\t\t\t\t\t\t\tSET deleted = 1\n\t\t\t\t\t\t\t\t\t\tWHERE idsite = ? \n\t\t\t\t\t\t\t\t\t\tAND idgoal = ?\n\t\t\t\t\t\t\t\t\t\tAND idfunnel = ?", array($idSite, $idGoal, $idFunnel));
Piwik_Common::regenerateCacheWebsiteAttributes($idSite);
}
<?php
Piwik_Query('UPDATE ' . Piwik::prefixTable('log_visit') . ' SET location_ip=location_ip+CAST(POW(2,32) AS UNSIGNED) WHERE location_ip < 0;');
Piwik_Query('ALTER TABLE ' . Piwik::prefixTable('log_visit') . ' CHANGE location_ip location_ip BIGINT UNSIGNED;');
Piwik_Query('UPDATE ' . Piwik::prefixTable('logger_api_call') . ' SET caller_ip=caller_ip+CAST(POW(2,32) AS UNSIGNED) WHERE caller_ip < 0;');
Piwik_Query('ALTER TABLE ' . Piwik::prefixTable('logger_api_call') . ' CHANGE caller_ip caller_ip BIGINT UNSIGNED;');
Piwik_Query("ALTER TABLE " . Piwik::prefixTable('log_visit') . " DROP config_java");
/**
* This method displays a text containing an help about "How to build plugins for Piwik".
* This help is then used on http://dev.piwik.org/trac/wiki/Plugins/GlobalFunctions
*
*/
function index()
{
$out = '';
$out .= '<i>This page aims to list the different functions you can use when programming plugins for Piwik.</i><br>';
$out .= '<b>Be careful, the following APIs may change in the near future as Piwik is still in development.</b><br>';
$out .= '<h2>General</h2>';
$out .= '<h3>Accessible from your plugin controller</h3>';
$out .= '<code>$this->date</code> = current selected <b>Piwik_Date</b> object (<a href="http://dev.piwik.org/trac/browser/trunk/core/Date.php">class</a>)<br/>';
$out .= '<code>$period = Piwik_Common::getRequestVar("period");</code> - Get the current selected period<br/>';
$out .= '<code>$idSite = Piwik_Common::getRequestVar("idSite");</code> - Get the selected idSite<br/>';
$out .= '<code>$site = new Piwik_Site($idSite);</code> - Build the Piwik_Site object (<a href="http://dev.piwik.org/trac/browser/trunk/core/Site.php">class</a>)<br/>';
$out .= '<code>$this->str_date</code> = current selected date in YYYY-MM-DD format<br/>';
$out .= '<h3>Misc</h3>';
$out .= '<code>Piwik_AddMenu( $mainMenuName, $subMenuName, $url );</code> - Adds an entry to the menu in the Piwik interface (See the example in the <a href="http://dev.piwik.org/trac/browser/trunk/plugins/UserCountry/UserCountry.php#L146">UserCountry Plugin file</a>)<br/>';
$out .= '<code>Piwik_AddWidget( $widgetCategory, $widgetName, $controllerName, $controllerAction, $customParameters = array());</code> - Adds a widget that users can add in the dashboard, or export using the Widgets link at the top of the screen. See the example in the <a href="http://dev.piwik.org/trac/browser/trunk/plugins/UserCountry/UserCountry.php#L143">UserCountry Plugin file</a> or any other plugin)<br/>';
$out .= '<code>Piwik::prefixTable("site")</code> = <b>' . Piwik::prefixTable("site") . '</b><br/>';
$out .= '<h2>User access</h2>';
$out .= '<code>Piwik::getCurrentUserLogin()</code> = <b>' . Piwik::getCurrentUserLogin() . '</b><br/>';
$out .= '<code>Piwik::isUserHasSomeAdminAccess()</code> = <b>' . self::boolToString(Piwik::isUserHasSomeAdminAccess()) . '</b><br/>';
$out .= '<code>Piwik::isUserHasAdminAccess( array $idSites = array(1,2) )</code> = <b>' . self::boolToString(Piwik::isUserHasAdminAccess(array(1,2) )) . '</b><br/>';
$out .= '<code>Piwik::isUserHasViewAccess( array $idSites = array(1) ) </code> = <b>' . self::boolToString(Piwik::isUserHasViewAccess(array(1))) . '</b><br/>';
$out .= '<code>Piwik::isUserIsSuperUser()</code> = <b>' . self::boolToString(Piwik::isUserIsSuperUser()) . '</b><br/>';
$out .= '<h2>Execute SQL queries</h2>';
$txtQuery = "SELECT token_auth FROM ".Piwik::prefixTable('user')." WHERE login = ?";
$result = Piwik_FetchOne($txtQuery, array('anonymous'));
$out .= '<code>Piwik_FetchOne("'.$txtQuery.'", array("anonymous"))</code> = <b>' . var_export($result,true) . '</b><br/>';
$out .= '<br>';
$query = Piwik_Query($txtQuery, array('anonymous'));
$fetched = $query->fetch();
$token_auth = $fetched['token_auth'];
$out .= '<code>$query = Piwik_Query("'.$txtQuery.'", array("anonymous"))</code><br>';
$out .= '<code>$fetched = $query->fetch();</code><br>';
$out .= 'At this point, we have: <code>$fetched[\'token_auth\'] == <b>'.var_export($token_auth,true) . '</b></code><br/>';
$out .= '<h2>Example Sites information API</h2>';
$out .= '<code>Piwik_SitesManager_API::getSitesWithViewAccess()</code> = <b><pre>' .var_export(Piwik_SitesManager_API::getSitesWithViewAccess(),true) . '</pre></b><br/>';
$out .= '<code>Piwik_SitesManager_API::getSitesWithAdminAccess()</code> = <b><pre>' .var_export(Piwik_SitesManager_API::getSitesWithAdminAccess(),true) . '</pre></b><br/>';
$out .= '<h2>Example API Users information</h2>';
$out .= 'View the list of API methods you can call on <a href="http://dev.piwik.org/trac/wiki/API/Reference#Methods">API reference</a><br/>';
$out .= 'For example you can try <code>Piwik_UsersManager_API::getUsersSitesFromAccess("view");</code> or <code>Piwik_UsersManager_API::deleteUser("userToDelete");</code><br/>';
$out .= '<h2>Smarty plugins</h2>';
$out .= 'There are some builtin plugins for Smarty especially developped for Piwik. <br>
You can find them on the <a href="http://dev.piwik.org/trac/browser/trunk/core/SmartyPlugins">SVN at /trunk/core/SmartyPlugins</a>. <br>
More documentation to come about smarty plugins.<br/>';
echo $out;
}
/**
* 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 Piwik_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)
{
$fieldList = '(' . join(',', $fields) . ')';
$ignore = $ignoreWhenDuplicate ? 'IGNORE' : '';
foreach ($values as $row) {
$query = "INSERT {$ignore}\n\t\t\t\t\tINTO " . $tableName . "\n\t\t\t\t\t{$fieldList}\n\t\t\t\t\tVALUES (" . Piwik_Common::getSqlStringFieldsArray($row) . ")";
Piwik_Query($query, $row);
}
}
/**
* If the SQL profiler is enabled and if the reinit at every request is set to true,
* then we TRUNCATE the profiling information so that we only profile one visitor at a time
*
* @return void
*/
protected function initProfiler()
{
/*
* Inits the profiler
*/
if ($this->profiling) {
if ($this->reinitProfilingAtEveryRequest) {
$all = Piwik_Query('TRUNCATE TABLE ' . Piwik::prefixTable('log_profiling') . '');
}
}
}
