public function setUp()
{
parent::setUp();
// create two myisam tables
Piwik_Exec("CREATE TABLE table1 (a INT) ENGINE=MYISAM");
Piwik_Exec("CREATE TABLE table2 (b INT) ENGINE=MYISAM");
// create two innodb tables
Piwik_Exec("CREATE TABLE table3 (c INT) ENGINE=InnoDB");
Piwik_Exec("CREATE TABLE table4 (d INT) ENGINE=InnoDB");
}
function install()
{
try {
Piwik_Exec('ALTER TABLE ' . Piwik_Common::prefixTable('site') . " ADD `feedburnerName` VARCHAR( 100 ) DEFAULT NULL");
} catch (Exception $e) {
// mysql code error 1060: column already exists
// if there is another error we throw the exception, otherwise it is OK as we are simply reinstalling the plugin
if (!Zend_Registry::get('db')->isErrNo($e, '1060')) {
throw $e;
}
}
}
function createTable($tablename, $spec)
{
$sql = "CREATE TABLE IF NOT EXISTS " . Piwik_Common::prefixTable($tablename) . " ( {$spec} ) DEFAULT CHARSET=utf8 ";
Piwik_Exec($sql);
}
/**
* Batch insert into table from CSV (or other delimited) file.
*
* @param string $tableName Name of table
* @param array $fields Field names
* @param string $filePath Path name of a file.
* @param array $fileSpec File specifications (delimiter, line terminator, etc)
* @return bool True if successful; false otherwise
*/
public static function createTableFromCSVFile($tableName, $fields, $filePath, $fileSpec)
{
// On Windows, MySQL expects forward slashes as directory separators
if (Piwik_Common::isWindows()) {
$filePath = str_replace('\\', '/', $filePath);
}
$query = "\n\t\t\t\t'{$filePath}'\n\t\t\tREPLACE\n\t\t\tINTO TABLE\n\t\t\t\t" . $tableName;
if (isset($fileSpec['charset'])) {
$query .= ' CHARACTER SET ' . $fileSpec['charset'];
}
$fieldList = '(' . join(',', $fields) . ')';
$query .= "\n\t\t\tFIELDS TERMINATED BY\n\t\t\t\t'" . $fileSpec['delim'] . "'\n\t\t\tENCLOSED BY\n\t\t\t\t'" . $fileSpec['quote'] . "'\n\t\t";
if (isset($fileSpec['escape'])) {
$query .= " ESCAPED BY '" . $fileSpec['escape'] . "'";
}
$query .= "\n\t\t\tLINES TERMINATED BY\n\t\t\t\t'" . $fileSpec['eol'] . "'\n\t\t\t{$fieldList}\n\t\t";
/*
* First attempt: assume web server and MySQL server are on the same machine;
* this requires that the db user have the FILE privilege; however, since this is
* a global privilege, it may not be granted due to security concerns
*/
$keywords = array('');
/*
* Second attempt: using the LOCAL keyword means the client reads the file and sends it to the server;
* the LOCAL keyword may trigger a known PHP PDO_MYSQL bug when MySQL not built with --enable-local-infile
* @see http://bugs.php.net/bug.php?id=54158
*/
$openBaseDir = ini_get('open_basedir');
$safeMode = ini_get('safe_mode');
if (empty($openBaseDir) && empty($safeMode)) {
// php 5.x - LOAD DATA LOCAL INFILE is disabled if open_basedir restrictions or safe_mode enabled
$keywords[] = 'LOCAL';
}
foreach ($keywords as $keyword) {
try {
$sql = 'LOAD DATA ' . $keyword . ' INFILE ' . $query;
$result = @Piwik_Exec($sql);
if (empty($result) || $result < 0) {
continue;
}
return true;
} catch (Exception $e) {
if (!Zend_Registry::get('db')->isErrNo($e, '1148')) {
Piwik::log("LOAD DATA INFILE failed... Error was:" . $e->getMessage());
}
}
}
return false;
}
/**
* @throws Exception if non-recoverable error
*/
public function uninstall()
{
$sql = "DROP TABLE " . Piwik_Common::prefixTable('user_language');
Piwik_Exec($sql);
}
function install()
{
$queries[] = '
CREATE TABLE `' . Piwik_Common::prefixTable('report') . '` (
`idreport` INT(11) NOT NULL AUTO_INCREMENT,
`idsite` INTEGER(11) NOT NULL,
`login` VARCHAR(100) NOT NULL,
`description` VARCHAR(255) NOT NULL,
`period` VARCHAR(10) NOT NULL,
`type` VARCHAR(10) NOT NULL,
`format` VARCHAR(10) NOT NULL,
`reports` TEXT NOT NULL,
`parameters` TEXT NULL,
`ts_created` TIMESTAMP NULL,
`ts_last_sent` TIMESTAMP NULL,
`deleted` tinyint(4) NOT NULL default 0,
PRIMARY KEY (`idreport`)
) DEFAULT CHARSET=utf8';
try {
foreach ($queries as $query) {
Piwik_Exec($query);
}
} catch (Exception $e) {
if (!Zend_Registry::get('db')->isErrNo($e, '1050')) {
throw $e;
}
}
}
/**
* 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 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
* @return bool True if the bulk LOAD was used, false if we fallback to plain INSERTs
*/
public static function tableInsertBatch($tableName, $fields, $values)
{
$fieldList = '(' . join(',', $fields) . ')';
try {
// throw new Exception('');
$filePath = PIWIK_USER_PATH . '/' . Piwik_AssetManager::MERGED_FILE_DIR . $tableName . '-' . Piwik_Common::generateUniqId() . '.csv';
if (Piwik_Common::isWindows()) {
// On windows, MySQL expects slashes as directory separators
$filePath = str_replace('\\', '/', $filePath);
}
// Set up CSV delimiters, quotes, etc
$delim = "\t";
$quote = '"';
$eol = "\r\n";
$null = 'NULL';
$escape = '\\\\';
$fp = fopen($filePath, 'wb');
if (!$fp) {
throw new Exception('Error creating the tmp file ' . $filePath . ', please check that the webserver has write permission to write this file.');
}
@chmod($filePath, 0777);
foreach ($values as $row) {
$output = '';
foreach ($row as $value) {
if (!isset($value) || is_null($value) || $value === false) {
$output .= $null . $delim;
} else {
$output .= $quote . self::escapeString($value) . $quote . $delim;
}
}
// Replace delim with eol
unset($row[strlen($output) - strlen($delim)]);
$output .= $eol;
$ret = fwrite($fp, $output);
if (!$ret) {
fclose($fp);
unlink($filePath);
throw new Exception('Error writing to the tmp file ' . $filePath . ' containing the batch INSERTs.');
}
}
fclose($fp);
$query = "\n\t\t\t\t\t'{$filePath}'\n\t\t\t\tREPLACE\n\t\t\t\tINTO TABLE\n\t\t\t\t\t" . $tableName;
// hack for charset mismatch
if (!self::isDatabaseConnectionUTF8() && !isset(Zend_Registry::get('config')->database->charset)) {
$query .= ' CHARACTER SET latin1';
}
$query .= "\n\t\t\t\tFIELDS TERMINATED BY\n\t\t\t\t\t'" . $delim . "'\n\t\t\t\tENCLOSED BY\n\t\t\t\t\t'" . $quote . "'\n\t\t\t\tESCAPED BY\n\t\t\t\t\t'" . $escape . "'\n\t\t\t\tLINES TERMINATED BY\n\t\t\t\t\t\"" . $eol . "\"\n\t\t\t\t{$fieldList}\n\t\t\t";
// initial attempt with LOCAL keyword
// note: may trigger a known PHP PDO_MYSQL bug when MySQL not built with --enable-local-infile
// @see http://bugs.php.net/bug.php?id=54158
try {
$result = @Piwik_Exec('LOAD DATA LOCAL INFILE' . $query);
if (empty($result)) {
throw new Exception("LOAD DATA LOCAL INFILE failed!");
}
unlink($filePath);
return true;
} catch (Exception $e) {
}
// second attempt without LOCAL keyword if MySQL server appears to be on the same box
// note: requires that the db user have the FILE privilege; however, since this is
// a global privilege, it may not be granted due to security concerns
$dbHost = Zend_Registry::get('config')->database->host;
$localHosts = array('127.0.0.1', 'localhost', 'localhost.local', 'localhost.localdomain', 'localhost.localhost');
$hostName = @php_uname('n');
if (!empty($hostName)) {
$localHosts = array_merge($localHosts, array($hostName, $hostName . '.local', $hostName . '.localdomain', $hostName . '.localhost'));
}
if (!empty($dbHost) && !in_array($dbHost, $localHosts)) {
throw new Exception("MYSQL appears to be on a remote server");
}
$result = @Piwik_Exec('LOAD DATA INFILE' . $query);
if (empty($result)) {
throw new Exception("LOAD DATA INFILE failed!");
}
unlink($filePath);
return true;
} catch (Exception $e) {
Piwik::log("LOAD DATA INFILE failed or not supported, falling back to normal INSERTs... Error was:" . $e->getMessage(), Piwik_Log::WARN);
// if all else fails, fallback to a series of INSERTs
unlink($filePath);
self::tableInsertBatchIterate($tableName, $fields, $values);
}
return false;
}
public static function dropDatabase()
{
$dbName = Zend_Registry::get('config')->database->dbname;
Piwik_Exec("DROP DATABASE IF EXISTS " . $dbName);
}
public function install()
{
// we catch the exception
try {
$sql = "CREATE TABLE " . Piwik_Common::prefixTable('user_dashboard') . " (\n\t\t\t\t\tlogin VARCHAR( 100 ) NOT NULL ,\n\t\t\t\t\tiddashboard INT NOT NULL ,\n\t\t\t\t\tname VARCHAR( 100 ) NULL DEFAULT NULL ,\n\t\t\t\t\tlayout TEXT NOT NULL,\n\t\t\t\t\tPRIMARY KEY ( login , iddashboard )\n\t\t\t\t\t) DEFAULT CHARSET=utf8 ";
Piwik_Exec($sql);
} catch (Exception $e) {
// mysql code error 1050:table already exists
// see bug #153 http://dev.piwik.org/trac/ticket/153
if (!Zend_Registry::get('db')->isErrNo($e, '1050')) {
throw $e;
}
}
}
function install()
{
$queries[] = "\n CREATE TABLE " . Piwik_Common::prefixTable('pdf') . " (\n\t\t\t\t\tidreport INT(11) NOT NULL AUTO_INCREMENT,\n\t\t\t\t\tidsite INTEGER(11) NOT NULL,\n\t\t\t\t\tlogin VARCHAR(100) NOT NULL,\n\t\t\t\t\tdescription VARCHAR(255) NOT NULL,\n\t\t\t\t\tperiod VARCHAR(10) NULL,\n\t\t\t\t\temail_me TINYINT NULL,\n\t\t\t\t\tadditional_emails TEXT NULL,\n\t\t\t\t\treports TEXT NOT NULL,\n\t\t\t\t\tts_created TIMESTAMP NULL,\n\t\t\t\t\tts_last_sent TIMESTAMP NULL,\n\t\t\t\t\tdeleted tinyint(4) NOT NULL default '0',\n\t\t\t\t\tPRIMARY KEY (idreport)\n\t\t\t\t) DEFAULT CHARSET=utf8";
try {
foreach ($queries as $query) {
Piwik_Exec($query);
}
} catch (Exception $e) {
if (!Zend_Registry::get('db')->isErrNo($e, '1050')) {
throw $e;
}
}
}
function uninstall()
{
// remove column location_IntranetSubNetwork from the visit table
$query = "ALTER TABLE `" . Piwik_Common::prefixTable('log_visit') . "` DROP `location_IntranetSubNetwork`";
Piwik_Exec($query);
}
function uninstall()
{
// remove columns from the visit table
$query = "ALTER TABLE `" . Piwik_Common::prefixTable('log_visit') . "`\n DROP `mobile`,\n DROP `mobile_brand`,\n DROP `mobile_model`,\n DROP `mobile_id`,\n DROP `mobile_browser`,\n DROP `mobile_resolution`,\n DROP `mobile_js`,\n DROP `mobile_flash`,\n DROP `mobile_os`,\n DROP `mobile_ajax`\n DROP KEY `index_mobile`";
Piwik_Exec($query);
}
/**
* Test that purgeData works when there's no data.
*
* @group Plugins
* @group PrivacyManager
*/
public function testPurgeDataDeleteLogsNoData()
{
Piwik::truncateAllTables();
foreach (Piwik::getTablesArchivesInstalled() as $table) {
Piwik_Exec("DROP TABLE {$table}");
}
// get purge data prediction
$prediction = Piwik_PrivacyManager::getPurgeEstimate();
// perform checks on prediction
$expectedPrediction = array();
$this->assertEquals($expectedPrediction, $prediction);
// purge data
$this->_setTimeToRun();
$this->instance->deleteLogData();
$this->instance->deleteReportData();
// perform checks
$this->assertEquals(0, $this->_getTableCount('log_visit'));
$this->assertEquals(0, $this->_getTableCount('log_conversion'));
$this->assertEquals(0, $this->_getTableCount('log_link_visit_action'));
$this->assertEquals(0, $this->_getTableCount('log_conversion_item'));
$archiveTables = self::_getArchiveTableNames();
$this->assertFalse($this->_tableExists($archiveTables['numeric'][0]));
// January
$this->assertFalse($this->_tableExists($archiveTables['numeric'][1]));
// February
$this->assertFalse($this->_tableExists($archiveTables['blob'][0]));
// January
$this->assertFalse($this->_tableExists($archiveTables['blob'][1]));
// February
}
/**
* Drop database
*/
public function dropDatabase()
{
$dbName = Piwik_Config::getInstance()->database['dbname'];
Piwik_Exec("DROP DATABASE IF EXISTS " . $dbName);
}
public function uninstall()
{
$sql = "DROP TABLE " . Piwik::prefixTable('user_dashboard');
Piwik_Exec($sql);
}
function uninstall()
{
// add column hostname / hostname ext in the visit table
$query = "ALTER TABLE `" . Piwik_Common::prefixTable('log_visit') . "` DROP `location_provider`";
Piwik_Exec($query);
}
/**
* Performs database update(s)
*
* @param string $file Update script filename
* @param array $sqlarray An array of SQL queries to be executed
*/
static function updateDatabase($file, $sqlarray)
{
foreach ($sqlarray as $update => $ignoreError) {
try {
Piwik_Exec($update);
} catch (Exception $e) {
if ($ignoreError === false || !Zend_Registry::get('db')->isErrNo($e, $ignoreError)) {
$message = $file . ":\nError trying to execute the query '" . $update . "'.\nThe error was: " . $e->getMessage();
throw new Piwik_Updater_UpdateErrorException($message);
}
}
}
}
