static function getSql($schema = 'Myisam')
{
return array(
'ALTER TABLE `'. Piwik_Common::prefixTable('user_dashboard') .'`
CHANGE `layout` `layout` TEXT NOT NULL' => false,
);
}
static function getSql($schema = 'Myisam')
{
return array(
'ALTER TABLE `'. Piwik_Common::prefixTable('log_visit') .'`
ADD INDEX index_idsite_idvisitor (idsite, idvisitor)' => false,
);
}
/**
* Authenticates user
*
* @return Piwik_Auth_Result
*/
public function authenticate()
{
$rootLogin = Piwik_Config::getInstance()->superuser['login'];
$rootPassword = Piwik_Config::getInstance()->superuser['password'];
$rootToken = Piwik_UsersManager_API::getInstance()->getTokenAuth($rootLogin, $rootPassword);
if (is_null($this->login)) {
if ($this->token_auth === $rootToken) {
return new Piwik_Auth_Result(Piwik_Auth_Result::SUCCESS_SUPERUSER_AUTH_CODE, $rootLogin, $this->token_auth);
}
$login = Piwik_FetchOne('SELECT login
FROM ' . Piwik_Common::prefixTable('user') . '
WHERE token_auth = ?', array($this->token_auth));
if (!empty($login)) {
return new Piwik_Auth_Result(Piwik_Auth_Result::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 Piwik_Auth_Result(Piwik_Auth_Result::SUCCESS_SUPERUSER_AUTH_CODE, $rootLogin, $this->token_auth);
}
$login = $this->login;
$userToken = Piwik_FetchOne('SELECT token_auth
FROM ' . Piwik_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 Piwik_Auth_Result(Piwik_Auth_Result::SUCCESS, $login, $userToken);
}
}
}
return new Piwik_Auth_Result(Piwik_Auth_Result::FAILURE, $this->login, $this->token_auth);
}
static function getSql($schema = 'Myisam')
{
return array('ALTER TABLE `' . Piwik_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 `' . Piwik_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 `' . Piwik_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);
}
static function getSql($schema = 'Myisam')
{
return array(
'ALTER TABLE `'. Piwik_Common::prefixTable('site') .'`
ADD ecommerce TINYINT DEFAULT 0' => false,
);
}
static function getSql($schema = 'Myisam')
{
if(!Piwik::isTimezoneSupportEnabled())
{
return array();
}
// @see http://bugs.php.net/46111
$timezones = timezone_identifiers_list();
$brokenTZ = array();
foreach ($timezones as $timezone) {
$testDate = "2008-08-19 13:00:00 " . $timezone;
if (!strtotime($testDate)) {
$brokenTZ[] = $timezone;
}
}
$timezoneList = '"'. implode('","', $brokenTZ) . '"';
return array(
'UPDATE '. Piwik_Common::prefixTable('site') .'
SET timezone = "UTC"
WHERE timezone IN ('. $timezoneList .')' => false,
'UPDATE `'. Piwik_Common::prefixTable('option') .'`
SET option_value = "UTC"
WHERE option_name = "SitesManager_DefaultTimezone"
AND option_value IN ('. $timezoneList .')' => false,
);
}
static function getSql($schema = 'Myisam')
{
return array(
'CREATE TABLE `'. Piwik_Common::prefixTable('option') .'` (
idoption BIGINT NOT NULL AUTO_INCREMENT ,
option_name VARCHAR( 64 ) NOT NULL ,
option_value LONGTEXT NOT NULL ,
PRIMARY KEY ( idoption , option_name )
)' => false,
// 0.1.7 [463]
'ALTER IGNORE TABLE `'. Piwik_Common::prefixTable('log_visit') .'`
CHANGE `location_provider` `location_provider` VARCHAR( 100 ) DEFAULT NULL' => '1054',
// 0.1.7 [470]
'ALTER TABLE `'. Piwik_Common::prefixTable('logger_api_call') .'`
CHANGE `parameter_names_default_values` `parameter_names_default_values` TEXT,
CHANGE `parameter_values` `parameter_values` TEXT,
CHANGE `returned_value` `returned_value` TEXT' => false,
'ALTER TABLE `'. Piwik_Common::prefixTable('logger_error') .'`
CHANGE `message` `message` TEXT' => false,
'ALTER TABLE `'. Piwik_Common::prefixTable('logger_exception') .'`
CHANGE `message` `message` TEXT' => false,
'ALTER TABLE `'. Piwik_Common::prefixTable('logger_message') .'`
CHANGE `message` `message` TEXT' => false,
// 0.2.2 [489]
'ALTER IGNORE TABLE `'. Piwik_Common::prefixTable('site') .'`
CHANGE `feedburnerName` `feedburnerName` VARCHAR( 100 ) DEFAULT NULL' => '1054',
);
}
/**
* Convert segment expression to an action ID or an SQL expression.
*
* This method is used as a sqlFilter-callback for the segments of this plugin.
* Usually, these callbacks only return a value that should be compared to the
* column in the database. In this case, that doesn't work since multiple IDs
* can match an expression (e.g. "pageUrl=@foo").
*/
function getIdActionFromSegment($string, $sqlField, $matchType = '==')
{
// Field is visit_*_idaction_url or visit_*_idaction_name
$actionType = strpos($sqlField, '_name') === false ? Piwik_Tracker_Action::TYPE_ACTION_URL : Piwik_Tracker_Action::TYPE_ACTION_NAME;
// exact matches work by returning the id directly
if ($matchType == Piwik_SegmentExpression::MATCH_EQUAL || $matchType == Piwik_SegmentExpression::MATCH_NOT_EQUAL) {
$sql = Piwik_Tracker_Action::getSqlSelectActionId();
$bind = array($string, $string, $actionType);
$idAction = Piwik_FetchOne($sql, $bind);
// if the action is not found, we hack -100 to ensure it tries to match against an integer
// otherwise binding idaction_name to "false" returns some rows for some reasons (in case &segment=pageTitle==Větrnásssssss)
if (empty($idAction)) {
$idAction = -100;
}
return $idAction;
}
// now, we handle the cases =@ (contains) and !@ (does not contain)
// build the expression based on the match type
$sql = 'SELECT idaction FROM ' . Piwik_Common::prefixTable('log_action') . ' WHERE ';
switch ($matchType) {
case '=@':
// use concat to make sure, no %s occurs because some plugins use %s in their sql
$sql .= '( name LIKE CONCAT("%", ?, "%") AND type = ' . $actionType . ' )';
break;
case '!@':
$sql .= '( name NOT LIKE CONCAT("%", ?, "%") AND type = ' . $actionType . ' )';
break;
default:
throw new Exception("This match type is not available for action-segments.");
break;
}
return array('SQL' => $sql, 'bind' => $string);
}
static function getSql($schema = 'Myisam')
{
return array('CREATE TABLE `' . Piwik_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 `' . Piwik_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 `' . Piwik_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);
}
static function getSql($schema = 'Myisam')
{
return array(
'ALTER TABLE `'. Piwik_Common::prefixTable('option') .'`
CHANGE `option_name` `option_name` VARCHAR(255) NOT NULL' => false,
);
}
static function getSql($schema = 'Myisam')
{
return array(
'ALTER TABLE `'. Piwik_Common::prefixTable('log_action') .'`
CHANGE `name` `name` TEXT' => false,
);
}
static function getSql($schema = 'Myisam')
{
$logVisit = Piwik_Common::prefixTable('log_visit');
$logConversion = Piwik_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);
}
static function getSql($schema = 'Myisam')
{
$sqlarray = array('ALTER TABLE `' . Piwik_Common::prefixTable('log_visit') . '`
ADD `visit_goal_converted` VARCHAR( 1 ) NOT NULL AFTER `visit_total_time`' => false, 'ALTER IGNORE TABLE `' . Piwik_Common::prefixTable('log_visit') . '`
CHANGE `visit_goal_converted` `visit_goal_converted` TINYINT(1) NOT NULL' => false, 'CREATE TABLE `' . Piwik_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)" => false, 'CREATE TABLE `' . Piwik_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_visit_server_date` date 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`)
)' => false);
$tables = Piwik::getTablesInstalled();
foreach ($tables as $tableName) {
if (preg_match('/archive_/', $tableName) == 1) {
$sqlarray['CREATE INDEX index_all ON ' . $tableName . ' (`idsite`,`date1`,`date2`,`name`,`ts_archived`)'] = false;
}
}
return $sqlarray;
}
static function getSql($schema = 'Myisam')
{
return array('UPDATE `' . Piwik_Common::prefixTable('log_visit') . '`
SET location_ip=location_ip+CAST(POW(2,32) AS UNSIGNED) WHERE location_ip < 0' => false, 'ALTER TABLE `' . Piwik_Common::prefixTable('log_visit') . '`
CHANGE `location_ip` `location_ip` BIGINT UNSIGNED NOT NULL' => false, 'UPDATE `' . Piwik_Common::prefixTable('logger_api_call') . '`
SET caller_ip=caller_ip+CAST(POW(2,32) AS UNSIGNED) WHERE caller_ip < 0' => false, 'ALTER TABLE `' . Piwik_Common::prefixTable('logger_api_call') . '`
CHANGE `caller_ip` `caller_ip` BIGINT UNSIGNED' => false);
}
static function getSql($schema = 'Myisam')
{
return array('ALTER TABLE `' . Piwik_Common::prefixTable('access') . '`
CHANGE `login` `login` VARCHAR( 100 ) NOT NULL' => false, 'ALTER TABLE `' . Piwik_Common::prefixTable('user') . '`
CHANGE `login` `login` VARCHAR( 100 ) NOT NULL' => false, 'ALTER TABLE `' . Piwik_Common::prefixTable('user_dashboard') . '`
CHANGE `login` `login` VARCHAR( 100 ) NOT NULL' => '1146', 'ALTER TABLE `' . Piwik_Common::prefixTable('user_language') . '`
CHANGE `login` `login` VARCHAR( 100 ) NOT NULL' => '1146');
}
static function getSql($schema = 'Myisam')
{
return array('ALTER TABLE `' . Piwik_Common::prefixTable('log_visit') . '`
ADD `config_java` TINYINT(1) NOT NULL AFTER `config_flash`' => '1060', 'ALTER TABLE `' . Piwik_Common::prefixTable('log_visit') . '`
ADD `config_quicktime` TINYINT(1) NOT NULL AFTER `config_director`' => '1060', 'ALTER TABLE `' . Piwik_Common::prefixTable('log_visit') . '`
ADD `config_gears` TINYINT(1) NOT NULL AFTER `config_windowsmedia`,
ADD `config_silverlight` TINYINT(1) NOT NULL AFTER `config_gears`' => false);
}
static function getSql($schema = 'Myisam')
{
return array('ALTER DATABASE `' . Piwik_Config::getInstance()->database['dbname'] . '` DEFAULT CHARACTER SET utf8' => false, 'ALTER TABLE `' . Piwik_Common::prefixTable('log_visit') . '`
DROP INDEX index_idsite_datetime_config,
DROP INDEX index_idsite_idvisit,
ADD INDEX index_idsite_config_datetime (idsite, config_id, visit_last_action_time),
ADD INDEX index_idsite_datetime (idsite, visit_last_action_time)' => false);
}
static function getSql($schema = 'Myisam')
{
return array('ALTER TABLE `' . Piwik_Common::prefixTable('goal') . '`
ADD `allow_multiple` tinyint(4) NOT NULL AFTER case_sensitive' => false, 'ALTER TABLE `' . Piwik_Common::prefixTable('log_conversion') . '`
ADD buster int unsigned NOT NULL AFTER revenue,
DROP PRIMARY KEY,
ADD PRIMARY KEY (idvisit, idgoal, buster)' => false);
}
function getCollectionUrlFromID($id, $date, $period)
{
$pidSite = Piwik_FetchOne("SELECT idSite FROM " . Piwik_Common::prefixTable("site_collections") . " WHERE cID = ?", $id);
Piwik_cdebug::clog('getCollectionUrlFromID: ' . 'http://piwik.slub-dresde.de/index.php?module=CoreHome&action=index&date=' . $date . '&period=' . $period . '&idSite=' . $pidSite);
if (is_numeric($pidSite)) {
return "http://piwik.slub-dresden.de/index.php?module=CoreHome&action=index&date=" . $date . "&period=" . $period . "&idSite=" . $pidSite;
}
}
static function getSql($schema = 'Myisam')
{
return array(
'DELETE FROM `'. Piwik_Common::prefixTable('user_dashboard') ."`
WHERE layout LIKE '%.getLastVisitsGraph%'
OR layout LIKE '%.getLastVisitsReturningGraph%'" => false,
);
}
static function getSql($schema = 'Myisam')
{
return array(
'ALTER TABLE `'. Piwik_Common::prefixTable('log_visit') .'`
CHANGE `location_ip` `location_ip` INT UNSIGNED NOT NULL' => false,
'ALTER TABLE `'. Piwik_Common::prefixTable('logger_api_call') .'`
CHANGE `caller_ip` `caller_ip` INT UNSIGNED' => false,
);
}
static function getSql($schema = 'Myisam')
{
return array(
'ALTER TABLE `'. Piwik_Common::prefixTable('pdf') .'`
ADD COLUMN `format` VARCHAR(10)' => false,
'UPDATE `'. Piwik_Common::prefixTable('pdf') .'`
SET format = "pdf"' => false,
);
}
static function getSql($schema = 'Myisam')
{
$action = Piwik_Common::prefixTable('log_action');
$duplicates = Piwik_Common::prefixTable('log_action_duplicates');
$visitAction = Piwik_Common::prefixTable('log_link_visit_action');
$conversion = Piwik_Common::prefixTable('log_conversion');
$visit = Piwik_Common::prefixTable('log_visit');
return array(" ALTER TABLE `{$action}` \n\t\t \tADD `url_prefix` TINYINT(2) NULL AFTER `type`;\n\t\t " => 1060, " UPDATE `{$action}`\n\t\t\t\tSET\n\t\t\t\t url_prefix = IF (\n\t\t\t\t\tLEFT(name, 11) = 'http://www.', 1, IF (\n\t\t\t\t\t LEFT(name, 7) = 'http://', 0, IF (\n\t\t\t\t\t\tLEFT(name, 12) = 'https://www.', 3, IF (\n\t\t\t\t\t\t LEFT(name, 8) = 'https://', 2, NULL\n\t\t\t\t\t\t)\n\t\t\t\t\t )\n\t\t\t\t\t)\n\t\t\t\t ),\n\t\t\t\t name = IF (\n\t\t\t\t\turl_prefix = 0, SUBSTRING(name, 8), IF (\n\t\t\t\t\t url_prefix = 1, SUBSTRING(name, 12), IF (\n\t\t\t\t\t\turl_prefix = 2, SUBSTRING(name, 9), IF (\n\t\t\t\t\t\t url_prefix = 3, SUBSTRING(name, 13), name\n\t\t\t\t\t\t)\n\t\t\t\t\t )\n\t\t\t\t\t)\n\t\t\t\t ),\n\t\t\t\t hash = CRC32(name)\n\t\t\t\tWHERE\n\t\t\t\t type = 1 AND\n\t\t\t\t url_prefix IS NULL;\n\t\t\t" => false, " DROP TABLE IF EXISTS `{$duplicates}`;\n\t\t\t" => false, " CREATE TABLE `{$duplicates}` (\n\t\t\t\t `before` int(10) unsigned NOT NULL,\n\t\t\t\t `after` int(10) unsigned NOT NULL,\n\t\t\t\t KEY `mainkey` (`before`)\n\t\t\t\t) ENGINE=MyISAM;\n\t\t\t" => false, " INSERT INTO `{$duplicates}` (\n\t\t\t\t SELECT \n\t\t\t\t\taction.idaction AS `before`,\n\t\t\t\t\tcanonical.idaction AS `after`\n\t\t\t\t FROM\n\t\t\t\t\t(\n\t\t\t\t\t SELECT\n\t\t\t\t\t\tname,\n\t\t\t\t\t\thash,\n\t\t\t\t\t\tMIN(idaction) AS idaction\n\t\t\t\t\t FROM\n\t\t\t\t\t\t`{$action}` AS action_canonical_base\n\t\t\t\t\t WHERE\n\t\t\t\t\t\ttype = 1 AND\n\t\t\t\t\t\turl_prefix IS NOT NULL\n\t\t\t\t\t GROUP BY name, hash # only grouping by name would be case-insensitive\n\t\t\t\t\t HAVING COUNT(idaction) > 1\n\t\t\t\t\t)\n\t\t\t\t\tAS canonical\n\t\t\t\t LEFT JOIN\n\t\t\t\t\t`{$action}` AS action\n\t\t\t\t\tON (action.type = 1 AND canonical.hash = action.hash) # use index (type, hash)\n\t\t\t\t\tAND canonical.name = action.name\n\t\t\t\t\tAND canonical.idaction != action.idaction\n\t\t\t\t);\n\t\t\t" => false, " UPDATE\n\t\t\t\t `{$visitAction}` AS link\n\t\t\t\tLEFT JOIN\n\t\t\t\t `{$duplicates}` AS duplicates_idaction_url\n\t\t\t\t ON link.idaction_url = duplicates_idaction_url.before\n\t\t\t\tSET\n\t\t\t\t link.idaction_url = duplicates_idaction_url.after\n\t\t\t\tWHERE\n\t\t\t\t duplicates_idaction_url.after IS NOT NULL;\n\t\t\t" => false, " UPDATE\n\t\t\t\t `{$visitAction}` AS link\n\t\t\t\tLEFT JOIN\n\t\t\t\t `{$duplicates}` AS duplicates_idaction_url_ref\n\t\t\t\t ON link.idaction_url_ref = duplicates_idaction_url_ref.before\n\t\t\t\tSET\n\t\t\t\t link.idaction_url_ref = duplicates_idaction_url_ref.after\n\t\t\t\tWHERE\n\t\t\t\t duplicates_idaction_url_ref.after IS NOT NULL;\n\t\t\t" => false, " UPDATE\n\t\t\t\t `{$conversion}` AS conversion\n\t\t\t\tLEFT JOIN\n\t\t\t\t `{$duplicates}` AS duplicates\n\t\t\t\t ON conversion.idaction_url = duplicates.before\n\t\t\t\tSET\n\t\t\t\t conversion.idaction_url = duplicates.after\n\t\t\t\tWHERE\n\t\t\t\t duplicates.after IS NOT NULL;\n\t\t\t" => false, " UPDATE\n\t\t\t\t `{$visit}` AS visit\n\t\t\t\tLEFT JOIN\n\t\t\t\t `{$duplicates}` AS duplicates_entry\n\t\t\t\t ON visit.visit_entry_idaction_url = duplicates_entry.before\n\t\t\t\tSET\n\t\t\t\t visit.visit_entry_idaction_url = duplicates_entry.after\n\t\t\t\tWHERE\n\t\t\t\t duplicates_entry.after IS NOT NULL;\n\t\t\t" => false, " UPDATE\n\t\t\t\t `{$visit}` AS visit\n\t\t\t\tLEFT JOIN\n\t\t\t\t `{$duplicates}` AS duplicates_exit\n\t\t\t\t ON visit.visit_exit_idaction_url = duplicates_exit.before\n\t\t\t\tSET\n\t\t\t\t visit.visit_exit_idaction_url = duplicates_exit.after\n\t\t\t\tWHERE\n\t\t\t\t duplicates_exit.after IS NOT NULL;\n\t\t\t" => false, " DELETE action FROM\n\t\t\t\t `{$action}` AS action\n\t\t\t\tLEFT JOIN\n\t\t\t\t `{$duplicates}` AS duplicates\n\t\t\t\t ON action.idaction = duplicates.before\n\t\t\t\tWHERE\n\t\t\t\t duplicates.after IS NOT NULL;\n\t\t\t" => false, " DROP TABLE `{$duplicates}`;\n\t\t\t" => false);
}
/**
* 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>piwik.dashboardObject.reloadEnclosingWidget($(this));</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;
}
static function getSql($schema = 'Myisam')
{
return array(
'ALTER TABLE `'. Piwik_Common::prefixTable('log_conversion') .'`
CHANGE `idlink_va` `idlink_va` INT(11) DEFAULT NULL' => false,
'ALTER TABLE `'. Piwik_Common::prefixTable('log_conversion') .'`
CHANGE `idaction` `idaction` INT(11) DEFAULT NULL' => '1054',
);
}
static function getSql($schema = 'Myisam')
{
return array('CREATE TABLE `' . Piwik_Common::prefixTable('session') . '` (
id CHAR(32) NOT NULL,
modified INTEGER,
lifetime INTEGER,
data TEXT,
PRIMARY KEY ( id )
) DEFAULT CHARSET=utf8' => false);
}
static function getSql($schema = 'Myisam')
{
return array('ALTER TABLE `' . Piwik_Common::prefixTable('log_link_visit_action') . '`
CHANGE `idaction_url` `idaction_url` INT( 10 ) UNSIGNED NULL DEFAULT NULL' => false, 'ALTER TABLE `' . Piwik_Common::prefixTable('log_visit') . '`
ADD visit_total_searches SMALLINT(5) UNSIGNED NOT NULL AFTER `visit_total_actions`' => 1060, 'ALTER TABLE `' . Piwik_Common::prefixTable('site') . '`
ADD sitesearch TINYINT DEFAULT 1 AFTER `excluded_parameters`,
ADD sitesearch_keyword_parameters TEXT NOT NULL AFTER `sitesearch`,
ADD sitesearch_category_parameters TEXT NOT NULL AFTER `sitesearch_keyword_parameters`' => 1060, 'UPDATE `' . Piwik_Common::prefixTable('site') . '`
SET `sitesearch` = 1' => false);
}
public static function getGoals($idSite)
{
$goals = Piwik_FetchAll("SELECT * \n\t\t\t\t\t\t\t\t\t\t\tFROM " . Piwik_Common::prefixTable('goal') . " \n\t\t\t\t\t\t\t\t\t\t\tWHERE idsite = ?\n\t\t\t\t\t\t\t\t\t\t\t\tAND deleted = 0", $idSite);
$cleanedGoals = array();
foreach ($goals as &$goal) {
unset($goal['idsite']);
$cleanedGoals[$goal['idgoal']] = $goal;
}
return $cleanedGoals;
}
/**
* Returns the layout in the DB for the given user, or false if the layout has not been set yet.
* Parameters must be checked BEFORE this function call
*
* @param string $login
* @param int $idDashboard
* @param string|false $layout
*/
protected function getLayoutForUser($login, $idDashboard)
{
$paramsBind = array($login, $idDashboard);
$return = Piwik_FetchAll('SELECT layout
FROM ' . Piwik_Common::prefixTable('user_dashboard') . ' WHERE login = ?
AND iddashboard = ?', $paramsBind);
if (count($return) == 0) {
return false;
}
return $return[0]['layout'];
}
static function getSql($schema = 'Myisam')
{
return array(
'CREATE INDEX index_type_name
ON '. Piwik_Common::prefixTable('log_action') .' (type, name(15))' => false,
'CREATE INDEX index_idsite_date
ON '. Piwik_Common::prefixTable('log_visit') .' (idsite, visit_server_date)' => false,
'DROP INDEX index_idsite ON '. Piwik_Common::prefixTable('log_visit') => false,
'DROP INDEX index_visit_server_date ON '. Piwik_Common::prefixTable('log_visit') => false,
);
}
