diff options
Diffstat (limited to 'core/Db')
-rw-r--r-- | core/Db/Adapter.php | 166 | ||||
-rw-r--r-- | core/Db/Adapter/Mysqli.php (renamed from core/Db/Mysqli.php) | 49 | ||||
-rw-r--r-- | core/Db/Adapter/Pdo/Mssql.php | 253 | ||||
-rw-r--r-- | core/Db/Adapter/Pdo/Mysql.php (renamed from core/Db/Pdo/Mysql.php) | 47 | ||||
-rw-r--r-- | core/Db/Adapter/Pdo/Pgsql.php (renamed from core/Db/Pdo/Pgsql.php) | 84 | ||||
-rw-r--r-- | core/Db/Schema.php | 282 | ||||
-rw-r--r-- | core/Db/Schema/Myisam.php | 484 |
7 files changed, 1302 insertions, 63 deletions
diff --git a/core/Db/Adapter.php b/core/Db/Adapter.php new file mode 100644 index 0000000000..7a3b4fccf6 --- /dev/null +++ b/core/Db/Adapter.php @@ -0,0 +1,166 @@ +<?php +/** + * Piwik - Open source web analytics + * + * @link http://piwik.org + * @license http://www.gnu.org/licenses/gpl-3.0.html Gpl v3 or later + * @version $Id$ + * + * @category Piwik + * @package Piwik + */ + +/** + * @package Piwik + * @subpackage Piwik_Db + */ +class Piwik_Db_Adapter +{ + /** + * Create adapter + * + * @param string $adapterName database adapter name + * @param array $dbInfos database connection info + * @return mixed (Piwik_Db_Adapter_Mysqli, Piwik_Db_Adapter_Pdo_Mysql, etc) + */ + public static function factory($adapterName, & $dbInfos) + { + if($dbInfos['port'][0] == '/') + { + $dbInfos['unix_socket'] = $dbInfos['port']; + unset($dbInfos['host']); + unset($dbInfos['port']); + } + + // not used by Zend Framework + unset($dbInfos['tables_prefix']); + unset($dbInfos['adapter']); + unset($dbInfos['schema']); + + $className = self::getAdapterClassName($adapterName); + $adapter = new $className($dbInfos); + $adapter->getConnection(); + + Zend_Db_Table::setDefaultAdapter($adapter); + + // we don't want the connection information to appear in the logs + $adapter->resetConfig(); + + return $adapter; + } + + /** + * Get adapter class name + * + * @param string $adapterName + * @return string + */ + private static function getAdapterClassName($adapterName) + { + return 'Piwik_Db_Adapter_' . str_replace(' ', '_', ucwords(str_replace('_', ' ', strtolower($adapterName)))); + } + + /** + * Get default port for named adapter + * + * @param string $adapterName + * @return int + */ + public static function getDefaultPortForAdapter($adapterName) + { + $className = self::getAdapterClassName($adapterName); + return call_user_func(array($className, 'getDefaultPort')); + } + + /** + * Get list of adapters + * + * @return array + */ + public static function getAdapters() + { + static $adapterNames = array( + // currently supported by Piwik + 'Pdo_Mysql', + 'Mysqli', + + // other adapters supported by Zend_Db +// 'Pdo_Pgsql', +// 'Pdo_Mssql', +// 'Sqlsrv', +// 'Pdo_Ibm', +// 'Db2', +// 'Pdo_Oci', +// 'Oracle', + ); + + $adapters = array(); + + foreach($adapterNames as $adapterName) + { + $className = 'Piwik_Db_Adapter_'.$adapterName; + if(call_user_func(array($className, 'isEnabled'))) + { + $adapters[strtoupper($adapterName)] = call_user_func(array($className, 'getDefaultPort')); + } + } + + return $adapters; + } +} + +/** + * @package Piwik + * @subpackage Piwik_Db + */ +interface Piwik_Db_Adapter_Interface +{ + /** + * Reset the configuration variables in this adapter. + */ + public function resetConfig(); + + /** + * Return default port. + * + * @return int + */ + public static function getDefaultPort(); + + /** + * Check database server version + * + * @throws Exception if database version is less than required version + */ + public function checkServerVersion(); + + /** + * Returns true if this adapter's required extensions are enabled + * + * @return bool + */ + public static function isEnabled(); + + /** + * Returns true if this adapter supports blobs as fields + * + * @return bool + */ + public function hasBlobDataType(); + + /** + * Test error number + * + * @param Exception $e + * @param string $errno + * @return bool + */ + public function isErrNo($e, $errno); + + /** + * Is the connection character set equal to utf8? + * + * @return bool + */ + public function isConnectionUTF8(); +} diff --git a/core/Db/Mysqli.php b/core/Db/Adapter/Mysqli.php index 6f62f9f546..37bb46540f 100644 --- a/core/Db/Mysqli.php +++ b/core/Db/Adapter/Mysqli.php @@ -12,8 +12,9 @@ /** * @package Piwik + * @subpackage Piwik_Db */ -class Piwik_Db_Mysqli extends Zend_Db_Adapter_Mysqli implements Piwik_Db_iAdapter +class Piwik_Db_Adapter_Mysqli extends Zend_Db_Adapter_Mysqli implements Piwik_Db_Adapter_Interface { public function __construct($config) { @@ -43,15 +44,29 @@ class Piwik_Db_Mysqli extends Zend_Db_Adapter_Mysqli implements Piwik_Db_iAdapte */ public function checkServerVersion() { - $databaseVersion = $this->getServerVersion(); + $serverVersion = $this->getServerVersion(); $requiredVersion = Zend_Registry::get('config')->General->minimum_mysql_version; - if(version_compare($databaseVersion, $requiredVersion) === -1) + if(version_compare($serverVersion, $requiredVersion) === -1) { - throw new Exception(Piwik_TranslateException('General_ExceptionDatabaseVersion', array('MySQL', $databaseVersion, $requiredVersion))); + throw new Exception(Piwik_TranslateException('General_ExceptionDatabaseVersion', array('MySQL', $serverVersion, $requiredVersion))); } } /** + * Check client version compatibility against database server + */ + public function checkClientVersion() + { + $serverVersion = $this->getServerVersion(); + $clientVersion = $this->getClientVersion(); + if(version_compare($serverVersion, '5') >= 0 + && version_compare($clientVersion, '5') < 0) + { + throw new Exception(Piwik_TranslateException('General_ExceptionIncompatibleClientServerVersions', array('MySQL', $clientVersion, $serverVersion))); + } + } + + /** * Returns true if this adapter's required extensions are enabled * * @return bool @@ -59,7 +74,7 @@ class Piwik_Db_Mysqli extends Zend_Db_Adapter_Mysqli implements Piwik_Db_iAdapte public static function isEnabled() { $extensions = @get_loaded_extensions(); - return in_array('mysqli', $extensions) && function_exists('mysqli_set_charset'); + return in_array('mysqli', $extensions); } /** @@ -81,6 +96,15 @@ class Piwik_Db_Mysqli extends Zend_Db_Adapter_Mysqli implements Piwik_Db_iAdapte */ public function isErrNo($e, $errno) { + if(is_null($this->_connection)) + { + if(preg_match('/(?:\[|\s)([0-9]{4})(?:\]|\s)/', $e->getMessage(), $match)) + { + return $match[1] == $errno; + } + return mysqli_connect_errno() == $errno; + } + return mysqli_errno($this->_connection) == $errno; } @@ -114,4 +138,19 @@ class Piwik_Db_Mysqli extends Zend_Db_Adapter_Mysqli implements Piwik_Db_iAdapte $charset = mysqli_character_set_name($this->_connection); return $charset === 'utf8'; } + + /** + * Get client version + * + * @return string + */ + public function getClientVersion() + { + $this->_connect(); + $version = $this->_connection->server_version; + $major = (int) ($version / 10000); + $minor = (int) ($version % 10000 / 100); + $revision = (int) ($version % 100); + return $major . '.' . $minor . '.' . $revision; + } } diff --git a/core/Db/Adapter/Pdo/Mssql.php b/core/Db/Adapter/Pdo/Mssql.php new file mode 100644 index 0000000000..7246c3838e --- /dev/null +++ b/core/Db/Adapter/Pdo/Mssql.php @@ -0,0 +1,253 @@ +<?php +/** + * Piwik - Open source web analytics + * + * @link http://piwik.org + * @license http://www.gnu.org/licenses/gpl-3.0.html Gpl v3 or later + * @version $Id$ + * + * @category Piwik + * @package Piwik + */ + +/** + * @package Piwik + * @subpackage Piwik_Db + */ +class Piwik_Db_Pdo_Mssql extends Zend_Db_Adapter_Pdo_Mssql implements Piwik_Db_Adapter_Interface +{ + /** + * Returns connection handle + * + * @return resource + */ + public function getConnection() + { + // if we already have a PDO object, no need to re-connect. + if ($this->_connection) + { + return $this->_connection; + } + + $this->_pdoType = "sqlsrv"; + // get the dsn first, because some adapters alter the $_pdoType + //$dsn = $this->_dsn(); + + // check for PDO extension + if (!extension_loaded('pdo')) + { + /** + * @see Zend_Db_Adapter_Exception + */ + throw new Zend_Db_Adapter_Exception('The PDO extension is required for this adapter but the extension is not loaded'); + } + + // check the PDO driver is available + if (!in_array($this->_pdoType, PDO::getAvailableDrivers())) + { + /** + * @see Zend_Db_Adapter_Exception + */ + throw new Zend_Db_Adapter_Exception('The ' . $this->_pdoType . ' driver is not currently installed'); + } + + // create PDO connection + $q = $this->_profiler->queryStart('connect', Zend_Db_Profiler::CONNECT); + + // add the persistence flag if we find it in our config array + if (isset($this->_config['persistent']) && ($this->_config['persistent'] == true)) + { + $this->_config['driver_options'][PDO::ATTR_PERSISTENT] = true; + } + + try { + $serverName = $this->_config["host"]; + $database = $this->_config["dbname"]; + if(is_null($database)) + { + $database = 'master'; + } + $uid = $this->_config['username']; + $pwd = $this->_config['password']; + if($this->_config["port"] != "") + { + $serverName = $serverName.",".$this->_config["port"]; + } + + $this->_connection = new PDO( "sqlsrv:$serverName", $uid, $pwd, array( 'Database' => $database )); + + + if( $this->_connection === false ) + { + die( self::FormatErrors( sqlsrv_errors() ) ); + } + + /* + $this->_connection = new PDO( + $dsn, + $this->_config['username'], + $this->_config['password'], + $this->_config['driver_options'] + ); + */ + + $this->_profiler->queryEnd($q); + + // set the PDO connection to perform case-folding on array keys, or not + $this->_connection->setAttribute(PDO::ATTR_CASE, $this->_caseFolding); + $this->_connection->setAttribute(PDO::SQLSRV_ENCODING_UTF8, true); + + + // always use exceptions. + $this->_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); + + return $this->_connection; + } catch (PDOException $e) { + /** + * @see Zend_Db_Adapter_Exception + */ + throw new Zend_Db_Adapter_Exception($e->getMessage(), $e->getCode(), $e); + } + } + + /** + * Reset the configuration variables in this adapter. + */ + public function resetConfig() + { + $this->_config = array(); + } + + /** + * Return default port. + * + * @return int + */ + public static function getDefaultPort() + { + return 1433; + } + + /** + * Check MSSQL version + */ + public function checkServerVersion() + { + $serverVersion = $this->getServerVersion(); + $requiredVersion = Zend_Registry::get('config')->General->minimum_mssql_version; + if(version_compare($serverVersion, $requiredVersion) === -1) + { + throw new Exception(Piwik_TranslateException('General_ExceptionDatabaseVersion', array('MSSQL', $serverVersion, $requiredVersion))); + } + + } + + public function getServerVersion() + { + try + { + $stmt = $this->query("SELECT CAST(SERVERPROPERTY('productversion') as VARCHAR) as productversion"); + $result = $stmt->fetchAll(Zend_Db::FETCH_NUM); + if (count($result)) + { + return $result[0][0]; + } + } + catch (PDOException $e) + { + } + + return null; + } + + /** + * Check client version compatibility against database server + */ + public function checkClientVersion() + { + $serverVersion = $this->getServerVersion(); + $clientVersion = $this->getClientVersion(); + if(version_compare($serverVersion, '10') >= 0 + && version_compare($clientVersion, '10') < 0) + { + throw new Exception(Piwik_TranslateException('General_ExceptionIncompatibleClientServerVersions', array('MSSQL', $clientVersion, $serverVersion))); + } + } + + /** + * Returns true if this adapter's required extensions are enabled + * + * @return bool + */ + public static function isEnabled() + { + $extensions = @get_loaded_extensions(); + return in_array('PDO', $extensions) && in_array('pdo_sqlsrv', $extensions); + } + + /** + * Returns true if this adapter supports blobs as fields + * + * @return bool + */ + public function hasBlobDataType() + { + return true; + } + + /** + * Test error number + * + * @param Exception $e + * @param string $errno + * @return bool + */ + public function isErrNo($e, $errno) + { + if(preg_match('/(?:\[|\s)([0-9]{4})(?:\]|\s)/', $e->getMessage(), $match)) + { + return $match[1] == $errno; + } + return false; + } + + /** + * Is the connection character set equal to utf8? + * + * @return bool + */ + public function isConnectionUTF8() + { + //check the getconnection, it's specified on the connection string. + return true; + } + + /** + * Retrieve client version in PHP style + * + * @return string + */ + public function getClientVersion() + { + $this->_connect(); + try + { + $version = $this->_connection->getAttribute(PDO::ATTR_CLIENT_VERSION); + $requiredVersion = Zend_Registry::get('config')->General->minimum_mssql_client_version; + if(version_compare($version['DriverVer'], $requiredVersion) === -1) + { + throw new Exception(Piwik_TranslateException('General_ExceptionDatabaseVersion', array('MSSQL', $serverVersion, $requiredVersion))); + } + else + { + return $version['DriverVer']; + } + } + catch (PDOException $e) + { + // In case of the driver doesn't support getting attributes + } + + return null; + } +} diff --git a/core/Db/Pdo/Mysql.php b/core/Db/Adapter/Pdo/Mysql.php index 86f804e76b..4e8f8793a1 100644 --- a/core/Db/Pdo/Mysql.php +++ b/core/Db/Adapter/Pdo/Mysql.php @@ -12,8 +12,9 @@ /** * @package Piwik + * @subpackage Piwik_Db */ -class Piwik_Db_Pdo_Mysql extends Zend_Db_Adapter_Pdo_Mysql implements Piwik_Db_iAdapter +class Piwik_Db_Adapter_Pdo_Mysql extends Zend_Db_Adapter_Pdo_Mysql implements Piwik_Db_Adapter_Interface { /** * Returns connection handle @@ -67,15 +68,29 @@ class Piwik_Db_Pdo_Mysql extends Zend_Db_Adapter_Pdo_Mysql implements Piwik_Db_i */ public function checkServerVersion() { - $databaseVersion = $this->getServerVersion(); + $serverVersion = $this->getServerVersion(); $requiredVersion = Zend_Registry::get('config')->General->minimum_mysql_version; - if(version_compare($databaseVersion, $requiredVersion) === -1) + if(version_compare($serverVersion, $requiredVersion) === -1) { - throw new Exception(Piwik_TranslateException('General_ExceptionDatabaseVersion', array('MySQL', $databaseVersion, $requiredVersion))); + throw new Exception(Piwik_TranslateException('General_ExceptionDatabaseVersion', array('MySQL', $serverVersion, $requiredVersion))); } } /** + * Check client version compatibility against database server + */ + public function checkClientVersion() + { + $serverVersion = $this->getServerVersion(); + $clientVersion = $this->getClientVersion(); + if(version_compare($serverVersion, '5') >= 0 + && version_compare($clientVersion, '5') < 0) + { + throw new Exception(Piwik_TranslateException('General_ExceptionIncompatibleClientServerVersions', array('MySQL', $clientVersion, $serverVersion))); + } + } + + /** * Returns true if this adapter's required extensions are enabled * * @return bool @@ -83,7 +98,7 @@ class Piwik_Db_Pdo_Mysql extends Zend_Db_Adapter_Pdo_Mysql implements Piwik_Db_i public static function isEnabled() { $extensions = @get_loaded_extensions(); - return in_array('PDO', $extensions) && in_array('pdo_mysql', $extensions); + return in_array('PDO', $extensions) && in_array('pdo_mysql', $extensions) && in_array('mysql', PDO::getAvailableDrivers()); } /** @@ -105,7 +120,7 @@ class Piwik_Db_Pdo_Mysql extends Zend_Db_Adapter_Pdo_Mysql implements Piwik_Db_i */ public function isErrNo($e, $errno) { - if(preg_match('/ ([0-9]{4}) /', $e->getMessage(), $match)) + if(preg_match('/(?:\[|\s)([0-9]{4})(?:\]|\s)/', $e->getMessage(), $match)) { return $match[1] == $errno; } @@ -123,4 +138,24 @@ class Piwik_Db_Pdo_Mysql extends Zend_Db_Adapter_Pdo_Mysql implements Piwik_Db_i $charset = $charsetInfo[0]['Value']; return $charset === 'utf8'; } + + /** + * Retrieve client version in PHP style + * + * @return string + */ + public function getClientVersion() + { + $this->_connect(); + try { + $version = $this->_connection->getAttribute(PDO::ATTR_CLIENT_VERSION); + $matches = null; + if (preg_match('/((?:[0-9]{1,2}\.){1,3}[0-9]{1,2})/', $version, $matches)) { + return $matches[1]; + } + } catch (PDOException $e) { + // In case of the driver doesn't support getting attributes + } + return null; + } } diff --git a/core/Db/Pdo/Pgsql.php b/core/Db/Adapter/Pdo/Pgsql.php index 8c3cf0a6c5..f83a023b01 100644 --- a/core/Db/Pdo/Pgsql.php +++ b/core/Db/Adapter/Pdo/Pgsql.php @@ -12,8 +12,9 @@ /** * @package Piwik + * @subpackage Piwik_Db */ -class Piwik_Db_Pdo_Pgsql extends Zend_Db_Adapter_Pdo_Pgsql implements Piwik_Db_iAdapter +class Piwik_Db_Adapter_Pdo_Pgsql extends Zend_Db_Adapter_Pdo_Pgsql implements Piwik_Db_Adapter_Interface { /** * Reset the configuration variables in this adapter. @@ -39,11 +40,18 @@ class Piwik_Db_Pdo_Pgsql extends Zend_Db_Adapter_Pdo_Pgsql implements Piwik_Db_i public function checkServerVersion() { $databaseVersion = $this->getServerVersion(); - $requiredVersion = Zend_Registry::get('config')->General->minimum_pgsql_version; - if(version_compare($databaseVersion, $requiredVersion) === -1) - { - throw new Exception(Piwik_TranslateException('General_ExceptionDatabaseVersion', array('PostgreSQL', $databaseVersion, $requiredVersion))); - } + $requiredVersion = Zend_Registry::get('config')->General->minimum_pgsql_version; + if(version_compare($databaseVersion, $requiredVersion) === -1) + { + throw new Exception(Piwik_TranslateException('General_ExceptionDatabaseVersion', array('PostgreSQL', $databaseVersion, $requiredVersion))); + } + } + + /** + * Check client version compatibility against database server + */ + public function checkClientVersion() + { } /** @@ -53,10 +61,6 @@ class Piwik_Db_Pdo_Pgsql extends Zend_Db_Adapter_Pdo_Pgsql implements Piwik_Db_i */ public static function isEnabled() { - /** - * @todo This adapter is incomplete. - */ - return false; $extensions = @get_loaded_extensions(); return in_array('PDO', $extensions) && in_array('pdo_pgsql', $extensions); } @@ -76,32 +80,6 @@ class Piwik_Db_Pdo_Pgsql extends Zend_Db_Adapter_Pdo_Pgsql implements Piwik_Db_i } /** - * Pre-process SQL to handle MySQL-isms - * - * @return string - */ - public function preprocessSql($query) - { - $search = array( - // In MySQL, OPTION is still a reserved keyword; Piwik uses - // backticking in case table_prefix is empty. - '`', - - // MySQL implicitly does 'ORDER BY column' when there's a - // 'GROUP BY column'; Piwik uses 'ORDER BY NULL' when order - // doesn't matter, for better performance. - 'ORDER BY NULL', - ); - - $replace = array( - '', - '', - ); - - $query = str_replace($search, $replace, $query); - } - - /** * Test error number * * @param Exception $e @@ -128,6 +106,10 @@ class Piwik_Db_Pdo_Pgsql extends Zend_Db_Adapter_Pdo_Pgsql implements Piwik_Db_i // PostgreSQL: column "%s" of relation "%s" already exists '1060' => '42701', + // MySQL: Duplicate key name '%s' + // PostgreSQL: relation "%s" already exists + '1061' => '42P07', + // MySQL: Duplicate entry '%s' for key '%s' // PostgreSQL: duplicate key violates unique constraint '1062' => '23505', @@ -160,24 +142,22 @@ class Piwik_Db_Pdo_Pgsql extends Zend_Db_Adapter_Pdo_Pgsql implements Piwik_Db_i } /** - * Returns a list of the tables in the database. + * Retrieve client version in PHP style * - * Replaces parent::listTables() which uses subqueries. - * @see ZF-8046 - * - * @return array + * @return string */ - public function listTables() + public function getClientVersion() { - $sql = "SELECT c.relname AS table_name " - . "FROM pg_catalog.pg_class c " - . "JOIN pg_catalog.pg_roles r ON r.oid = c.relowner " - . "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace " - . "WHERE n.nspname <> 'pg_catalog' " - . "AND n.nspname !~ '^pg_toast' " - . "AND pg_catalog.pg_table_is_visible(c.oid) " - . "AND c.relkind = 'r' "; - - return $this->fetchCol($sql); + $this->_connect(); + try { + $version = $this->_connection->getAttribute(PDO::ATTR_CLIENT_VERSION); + $matches = null; + if (preg_match('/((?:[0-9]{1,2}\.){1,3}[0-9]{1,2})/', $version, $matches)) { + return $matches[1]; + } + } catch (PDOException $e) { + // In case of the driver doesn't support getting attributes + } + return null; } } diff --git a/core/Db/Schema.php b/core/Db/Schema.php new file mode 100644 index 0000000000..7afe91d0e8 --- /dev/null +++ b/core/Db/Schema.php @@ -0,0 +1,282 @@ +<?php +/** + * Piwik - Open source web analytics + * + * @link http://piwik.org + * @license http://www.gnu.org/licenses/gpl-3.0.html Gpl v3 or later + * @version $Id$ + * + * @category Piwik + * @package Piwik + */ + +/** + * Schema abstraction + * + * Note: no relation to the ZF proposals for Zend_Db_Schema_Manager + * + * @package Piwik + * @subpackage Piwik_Db + */ +class Piwik_Db_Schema +{ + static private $instance = null; + + private $schema = null; + + /** + * Returns the singleton Piwik_Db_Schema + * + * @return Piwik_Db_Schema + */ + static public function getInstance() + { + if (self::$instance === null) + { + $c = __CLASS__; + self::$instance = new $c(); + } + return self::$instance; + } + + /** + * Get schema class name + * + * @param string $schemaName + * @return string + */ + private static function getSchemaClassName($schemaName) + { + return 'Piwik_Db_Schema_' . str_replace(' ', '_', ucwords(str_replace('_', ' ', strtolower($schemaName)))); + } + + /** + * Get list of schemas + * + * @return array + */ + public static function getSchemas($adapterName) + { + static $allSchemaNames = array( + // MySQL storage engines + 'MYSQL' => array( + 'Myisam', +// 'Innodb', +// 'Infinidb', + ), + + // Microsoft SQL Server +// 'MSSQL' => array( 'Mssql' ), + + // PostgreSQL +// 'PDO_PGSQL' => array( 'Pgsql' ), + + // IBM DB2 +// 'IBM' => array( 'Ibm' ), + + // Oracle +// 'OCI' => array( 'Oci' ), + ); + + $adapterName = strtoupper($adapterName); + switch($adapterName) + { + case 'PDO_MYSQL': + case 'MYSQLI': + $adapterName = 'MYSQL'; + break; + + case 'PDO_MSSQL': + case 'SQLSRV': + $adapterName = 'MSSQL'; + break; + + case 'PDO_IBM': + case 'DB2': + $adapterName = 'IBM'; + break; + + case 'PDO_OCI': + case 'ORACLE': + $adapterName = 'OCI'; + break; + } + $schemaNames = $allSchemaNames[$adapterName]; + + $schemas = array(); + + foreach($schemaNamess as $schemaName) + { + $className = 'Piwik_Db_Schema_'.$schemaName; + if(call_user_func(array($className, 'isAvailable'))) + { + $schemas[] = $schemaName; + } + } + + return $schemas; + } + + /** + * Load schema + */ + private function loadSchema() + { + $schema = null; + Piwik_PostEvent('Schema.loadSchema', $schema); + if($schema === null) + { + $config = Zend_Registry::get('config'); + $dbInfos = $config->database->toArray(); + if(isset($dbInfos['schema'])) + { + $schemaName = $dbInfos['schema']; + } + else + { + $schemaName = 'Myisam'; + } + $className = self::getSchemaClassName($schemaName); + $schema = new $className(); + } + $this->schema = $schema; + } + + /** + * Returns an instance that subclasses Piwik_Db_Schema + * + * @return Piwik_Db_Schema_Interface + */ + private function getSchema() + { + if ($this->schema === null) + { + $this->loadSchema(); + } + return $this->schema; + } + + /** + * Get the SQL to create a specific Piwik table + * + * @return string SQL + */ + public function getTableCreateSql( $tableName ) + { + return $this->getSchema()->getTableCreateSql($tableName); + } + + /** + * Get the SQL to create Piwik tables + * + * @return array of strings containing SQL + */ + public function getTablesCreateSql() + { + return $this->getSchema()->getTablesCreateSql(); + } + + /** + * Create database + */ + public function createDatabase( $dbName = null ) + { + $this->getSchema()->createDatabase($dbName); + } + + /** + * Drop database + */ + public function dropDatabase() + { + $this->getSchema()->dropDatabase(); + } + + /** + * Create all tables + */ + public function createTables() + { + $this->getSchema()->createTables(); + } + + /** + * Creates an entry in the User table for the "anonymous" user. + */ + public function createAnonymousUser() + { + $this->getSchema()->createAnonymousUser(); + } + + /** + * Truncate all tables + */ + public function truncateAllTables() + { + $this->getSchema()->truncateAllTables(); + } + + /** + * Drop specific tables + */ + public function dropTables( $doNotDelete = array() ) + { + $this->getSchema()->dropTables($doNotDelete); + } + + /** + * Names of all the prefixed tables in piwik + * Doesn't use the DB + * + * @return array Table names + */ + public function getTablesNames() + { + return $this->getSchema()->getTablesNames(); + } + + /** + * Get list of tables installed + * + * @param bool $forceReload Invalidate cache + * @param string $idSite + * @return array Tables installed + */ + public function getTablesInstalled($forceReload = true, $idSite = null) + { + return $this->getSchema()->getTablesInstalled($forceReload, $idSite); + } + + /** + * Returns true if Piwik tables exist + * + * @return bool True if tables exist; false otherwise + */ + public function hasTables() + { + return $this->getSchema()->hasTables(); + } +} + +/** + * @package Piwik + */ +interface Piwik_Db_Schema_Interface +{ + static public function isAvailable(); + + public function getTableCreateSql($tableName); + public function getTablesCreateSql(); + + public function createDatabase( $dbName = null ); + public function dropDatabase(); + + public function createTables(); + public function createAnonymousUser(); + public function truncateAllTables(); + public function dropTables( $doNotDelete = array() ); + + public function getTablesNames(); + public function getTablesInstalled($forceReload = true, $idSite = null); + public function hasTables(); +} diff --git a/core/Db/Schema/Myisam.php b/core/Db/Schema/Myisam.php new file mode 100644 index 0000000000..790e07db38 --- /dev/null +++ b/core/Db/Schema/Myisam.php @@ -0,0 +1,484 @@ +<?php +/** + * Piwik - Open source web analytics + * + * @link http://piwik.org + * @license http://www.gnu.org/licenses/gpl-3.0.html Gpl v3 or later + * @version $Id$ + * + * @category Piwik + * @package Piwik + */ + +/** + * MySQL schema + * + * @package Piwik + * @subpackage Piwik_Db + */ +class Piwik_Db_Schema_Myisam implements Piwik_Db_Schema_Interface +{ + /** + * Is this MySQL storage engine available? + * + * @param string $engineName + * @return bool True if available and enabled; false otherwise + */ + static private function hasStorageEngine($engineName) + { + $db = Zend_Registry::get('db'); + $allEngines = $db->fetchAssoc('SHOW ENGINES'); + if(key_exists($engineName, $allEngines)) + { + $support = $allEngines[$engineName]['Support']; + return $support == 'DEFAULT' || $support == 'YES'; + } + return false; + } + + /** + * Is this schema available? + * + * @return bool True if schema is available; false otherwise + */ + static public function isAvailable() + { + return self::hasStorageEngine('MyISAM'); + } + + /** + * Get the SQL to create Piwik tables + * + * @return array of strings containing SQL + */ + public function getTablesCreateSql() + { + $config = Zend_Registry::get('config'); + $prefixTables = $config->database->tables_prefix; + $tables = array( + 'user' => "CREATE TABLE {$prefixTables}user ( + login VARCHAR(100) NOT NULL, + password CHAR(32) NOT NULL, + alias VARCHAR(45) NOT NULL, + email VARCHAR(100) NOT NULL, + token_auth CHAR(32) NOT NULL, + date_registered TIMESTAMP NULL, + PRIMARY KEY(login), + UNIQUE KEY uniq_keytoken(token_auth) + ) DEFAULT CHARSET=utf8 + ", + + 'access' => "CREATE TABLE {$prefixTables}access ( + login VARCHAR(100) NOT NULL, + idsite INTEGER UNSIGNED NOT NULL, + access VARCHAR(10) NULL, + PRIMARY KEY(login, idsite) + ) DEFAULT CHARSET=utf8 + ", + + 'site' => "CREATE TABLE {$prefixTables}site ( + idsite INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT, + name VARCHAR(90) NOT NULL, + main_url VARCHAR(255) NOT NULL, + ts_created TIMESTAMP NULL, + timezone VARCHAR( 50 ) NOT NULL, + currency CHAR( 3 ) NOT NULL, + excluded_ips TEXT NOT NULL, + excluded_parameters VARCHAR ( 255 ) NOT NULL, + PRIMARY KEY(idsite) + ) DEFAULT CHARSET=utf8 + ", + + 'site_url' => "CREATE TABLE {$prefixTables}site_url ( + idsite INTEGER(10) UNSIGNED NOT NULL, + url VARCHAR(255) NOT NULL, + PRIMARY KEY(idsite, url) + ) DEFAULT CHARSET=utf8 + ", + + 'goal' => " CREATE TABLE `{$prefixTables}goal` ( + `idsite` int(11) NOT NULL, + `idgoal` int(11) NOT NULL, + `name` varchar(50) NOT NULL, + `match_attribute` varchar(20) NOT NULL, + `pattern` varchar(255) NOT NULL, + `pattern_type` varchar(10) NOT NULL, + `case_sensitive` tinyint(4) NOT NULL, + `revenue` float NOT NULL, + `deleted` tinyint(4) NOT NULL default '0', + PRIMARY KEY (`idsite`,`idgoal`) + ) DEFAULT CHARSET=utf8 + ", + + 'logger_message' => "CREATE TABLE {$prefixTables}logger_message ( + idlogger_message INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + timestamp TIMESTAMP NULL, + message TEXT NULL, + PRIMARY KEY(idlogger_message) + ) DEFAULT CHARSET=utf8 + ", + + 'logger_api_call' => "CREATE TABLE {$prefixTables}logger_api_call ( + idlogger_api_call INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + class_name VARCHAR(255) NULL, + method_name VARCHAR(255) NULL, + parameter_names_default_values TEXT NULL, + parameter_values TEXT NULL, + execution_time FLOAT NULL, + caller_ip INT UNSIGNED NULL, + timestamp TIMESTAMP NULL, + returned_value TEXT NULL, + PRIMARY KEY(idlogger_api_call) + ) DEFAULT CHARSET=utf8 + ", + + 'logger_error' => "CREATE TABLE {$prefixTables}logger_error ( + idlogger_error INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + timestamp TIMESTAMP NULL, + message TEXT NULL, + errno INTEGER UNSIGNED NULL, + errline INTEGER UNSIGNED NULL, + errfile VARCHAR(255) NULL, + backtrace TEXT NULL, + PRIMARY KEY(idlogger_error) + ) DEFAULT CHARSET=utf8 + ", + + 'logger_exception' => "CREATE TABLE {$prefixTables}logger_exception ( + idlogger_exception INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + timestamp TIMESTAMP NULL, + message TEXT NULL, + errno INTEGER UNSIGNED NULL, + errline INTEGER UNSIGNED NULL, + errfile VARCHAR(255) NULL, + backtrace TEXT NULL, + PRIMARY KEY(idlogger_exception) + ) DEFAULT CHARSET=utf8 + ", + + 'log_action' => "CREATE TABLE {$prefixTables}log_action ( + idaction INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT, + name TEXT, + hash INTEGER(10) UNSIGNED NOT NULL, + type TINYINT UNSIGNED NULL, + PRIMARY KEY(idaction), + INDEX index_type_hash (type, hash) + ) DEFAULT CHARSET=utf8 + ", + + 'log_visit' => "CREATE TABLE {$prefixTables}log_visit ( + idvisit INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT, + idsite INTEGER(10) UNSIGNED NOT NULL, + visitor_localtime TIME NOT NULL, + visitor_idcookie CHAR(32) NOT NULL, + visitor_returning TINYINT(1) NOT NULL, + visit_first_action_time DATETIME NOT NULL, + visit_last_action_time DATETIME NOT NULL, + visit_server_date DATE NOT NULL, + visit_exit_idaction_url INTEGER(11) NOT NULL, + visit_entry_idaction_url INTEGER(11) NOT NULL, + visit_total_actions SMALLINT(5) UNSIGNED NOT NULL, + visit_total_time SMALLINT(5) UNSIGNED NOT NULL, + visit_goal_converted TINYINT(1) NOT NULL, + referer_type INTEGER UNSIGNED NULL, + referer_name VARCHAR(70) NULL, + referer_url TEXT NOT NULL, + referer_keyword VARCHAR(255) NULL, + config_md5config CHAR(32) NOT NULL, + config_os CHAR(3) NOT NULL, + config_browser_name VARCHAR(10) NOT NULL, + config_browser_version VARCHAR(20) NOT NULL, + config_resolution VARCHAR(9) NOT NULL, + config_pdf TINYINT(1) NOT NULL, + config_flash TINYINT(1) NOT NULL, + config_java TINYINT(1) NOT NULL, + config_director TINYINT(1) NOT NULL, + config_quicktime TINYINT(1) NOT NULL, + config_realplayer TINYINT(1) NOT NULL, + config_windowsmedia TINYINT(1) NOT NULL, + config_gears TINYINT(1) NOT NULL, + config_silverlight TINYINT(1) NOT NULL, + config_cookie TINYINT(1) NOT NULL, + location_ip INT UNSIGNED NOT NULL, + location_browser_lang VARCHAR(20) NOT NULL, + location_country CHAR(3) NOT NULL, + location_continent CHAR(3) NOT NULL, + PRIMARY KEY(idvisit), + INDEX index_idsite_idvisit (idsite, idvisit), + INDEX index_idsite_date_config (idsite, visit_server_date, config_md5config(8)), + INDEX index_idsite_datetime_config (idsite, visit_last_action_time, config_md5config(8)) + ) DEFAULT CHARSET=utf8 + ", + + 'log_conversion' => "CREATE TABLE `{$prefixTables}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, + idaction_url int(11) default NULL, + idlink_va int(11) default 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), + INDEX index_idsite_datetime ( idsite, server_time ) + ) DEFAULT CHARSET=utf8 + ", + + 'log_link_visit_action' => "CREATE TABLE {$prefixTables}log_link_visit_action ( + idlink_va INTEGER(11) NOT NULL AUTO_INCREMENT, + idvisit INTEGER(10) UNSIGNED NOT NULL, + idaction_url INTEGER(10) UNSIGNED NOT NULL, + idaction_url_ref INTEGER(10) UNSIGNED NOT NULL, + idaction_name INTEGER(10) UNSIGNED, + time_spent_ref_action INTEGER(10) UNSIGNED NOT NULL, + PRIMARY KEY(idlink_va), + INDEX index_idvisit(idvisit) + ) DEFAULT CHARSET=utf8 + ", + + 'log_profiling' => "CREATE TABLE {$prefixTables}log_profiling ( + query TEXT NOT NULL, + count INTEGER UNSIGNED NULL, + sum_time_ms FLOAT NULL, + UNIQUE KEY query(query(100)) + ) DEFAULT CHARSET=utf8 + ", + + 'option' => "CREATE TABLE `{$prefixTables}option` ( + option_name VARCHAR( 64 ) NOT NULL, + option_value LONGTEXT NOT NULL, + autoload TINYINT NOT NULL DEFAULT '1', + PRIMARY KEY ( option_name ) + ) DEFAULT CHARSET=utf8 + ", + + 'archive_numeric' => "CREATE TABLE {$prefixTables}archive_numeric ( + idarchive INTEGER UNSIGNED NOT NULL, + name VARCHAR(255) NOT NULL, + idsite INTEGER UNSIGNED NULL, + date1 DATE NULL, + date2 DATE NULL, + period TINYINT UNSIGNED NULL, + ts_archived DATETIME NULL, + value FLOAT NULL, + PRIMARY KEY(idarchive, name), + INDEX index_idsite_dates_period(idsite, date1, date2, period, ts_archived), + INDEX index_period_archived(period, ts_archived) + ) DEFAULT CHARSET=utf8 + ", + + 'archive_blob' => "CREATE TABLE {$prefixTables}archive_blob ( + idarchive INTEGER UNSIGNED NOT NULL, + name VARCHAR(255) NOT NULL, + idsite INTEGER UNSIGNED NULL, + date1 DATE NULL, + date2 DATE NULL, + period TINYINT UNSIGNED NULL, + ts_archived DATETIME NULL, + value MEDIUMBLOB NULL, + PRIMARY KEY(idarchive, name), + INDEX index_period_archived(period, ts_archived) + ) DEFAULT CHARSET=utf8 + ", + ); + return $tables; + } + + /** + * Get the SQL to create a specific Piwik table + * + * @param string $tableName + * @return string SQL + */ + public function getTableCreateSql( $tableName ) + { + $tables = Piwik::getTablesCreateSql(); + + if(!isset($tables[$tableName])) + { + throw new Exception("The table '$tableName' SQL creation code couldn't be found."); + } + + return $tables[$tableName]; + } + + /** + * Names of all the prefixed tables in piwik + * Doesn't use the DB + * + * @return array Table names + */ + public function getTablesNames() + { + $aTables = array_keys($this->getTablesCreateSql()); + $config = Zend_Registry::get('config'); + $prefixTables = $config->database->tables_prefix; + $return = array(); + foreach($aTables as $table) + { + $return[] = $prefixTables.$table; + } + return $return; + } + + private $tablesInstalled = null; + + /** + * Get list of tables installed + * + * @param bool $forceReload Invalidate cache + * @param string $idSite + * @return array Tables installed + */ + public function getTablesInstalled($forceReload = true, $idSite = null) + { + if(is_null($this->tablesInstalled) + || $forceReload === true) + { + $db = Zend_Registry::get('db'); + $config = Zend_Registry::get('config'); + $prefixTables = $config->database->tables_prefix; + + $allTables = $db->fetchCol("SHOW TABLES"); + + // all the tables to be installed + $allMyTables = $this->getTablesNames(); + + // we get the intersection between all the tables in the DB and the tables to be installed + $tablesInstalled = array_intersect($allMyTables, $allTables); + + // at this point we have only the piwik tables which is good + // but we still miss the piwik generated tables (using the class Piwik_TablePartitioning) + $idSiteInSql = "no"; + if(!is_null($idSite)) + { + $idSiteInSql = $idSite; + } + $allArchiveNumeric = $db->fetchCol("/* SHARDING_ID_SITE = ".$idSiteInSql." */ + SHOW TABLES LIKE '".$prefixTables."archive_numeric%'"); + $allArchiveBlob = $db->fetchCol("/* SHARDING_ID_SITE = ".$idSiteInSql." */ + SHOW TABLES LIKE '".$prefixTables."archive_blob%'"); + + $allTablesReallyInstalled = array_merge($tablesInstalled, $allArchiveNumeric, $allArchiveBlob); + + $this->tablesInstalled = $allTablesReallyInstalled; + } + return $this->tablesInstalled; + } + + /** + * Do tables exist? + * + * @return bool True if tables exist; false otherwise + */ + public function hasTables() + { + return count($this->getTablesInstalled()) != 0; + } + + /** + * Create database + * + * @param string $dbName + */ + public function createDatabase( $dbName = null ) + { + if(is_null($dbName)) + { + $dbName = Zend_Registry::get('config')->database->dbname; + } + Piwik_Exec("CREATE DATABASE IF NOT EXISTS ".$dbName); + } + + /** + * Drop database + */ + public function dropDatabase() + { + $dbName = Zend_Registry::get('config')->database->dbname; + Piwik_Exec("DROP DATABASE IF EXISTS " . $dbName); + + } + + /** + * Create all tables + */ + public function createTables() + { + $db = Zend_Registry::get('db'); + $config = Zend_Registry::get('config'); + $prefixTables = $config->database->tables_prefix; + + $tablesAlreadyInstalled = $this->getTablesInstalled(); + $tablesToCreate = $this->getTablesCreateSql(); + unset($tablesToCreate['archive_blob']); + unset($tablesToCreate['archive_numeric']); + + foreach($tablesToCreate as $tableName => $tableSql) + { + $tableName = $prefixTables . $tableName; + if(!in_array($tableName, $tablesAlreadyInstalled)) + { + $db->query( $tableSql ); + } + } + } + + /** + * Creates an entry in the User table for the "anonymous" user. + */ + public function createAnonymousUser() + { + // The anonymous user is the user that is assigned by default + // note that the token_auth value is anonymous, which is assigned by default as well in the Login plugin + $db = Zend_Registry::get('db'); + $db->query("INSERT INTO ". Piwik_Common::prefixTable("user") . " + VALUES ( 'anonymous', '', 'anonymous', 'anonymous@example.org', 'anonymous', '".Piwik_Date::factory('now')->getDatetime()."' );" ); + } + + /** + * Truncate all tables + */ + public function truncateAllTables() + { + $tablesAlreadyInstalled = $this->getTablesInstalled($forceReload = true); + foreach($tablesAlreadyInstalled as $table) + { + Piwik_Query("TRUNCATE `$table`"); + } + } + + /** + * Drop specific tables + * + * @param array $doNotDelete Names of tables to not delete + */ + public function dropTables( $doNotDelete = array() ) + { + $tablesAlreadyInstalled = $this->getTablesInstalled(); + $db = Zend_Registry::get('db'); + + $doNotDeletePattern = '/('.implode('|',$doNotDelete).')/'; + + foreach($tablesAlreadyInstalled as $tableName) + { + if( count($doNotDelete) == 0 + || (!in_array($tableName,$doNotDelete) + && !preg_match($doNotDeletePattern,$tableName) + ) + ) + { + $db->query("DROP TABLE `$tableName`"); + } + } + } +} |