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); } } } }