From 3f0149b5b74d4017ba7f4ca28aa8a3e2053db964 Mon Sep 17 00:00:00 2001 From: "Christophe.Boulain" <> Date: Mon, 15 Jun 2009 07:49:42 +0000 Subject: Primilary import of new db stuff --- framework/Db/TDataSourceConfig.php | 168 +++++ framework/Db/TDbCommand.php | 340 +++++++++ framework/Db/TDbConnection.php | 759 +++++++++++++++++++++ framework/Db/TDbDataReader.php | 221 ++++++ framework/Db/TDbTransaction.php | 111 +++ framework/Db/schema/TDbColumnSchema.php | 145 ++++ framework/Db/schema/TDbCommandBuilder.php | 656 ++++++++++++++++++ framework/Db/schema/TDbCriteria.php | 166 +++++ framework/Db/schema/TDbExpression.php | 49 ++ framework/Db/schema/TDbSchema.php | 193 ++++++ framework/Db/schema/TDbTableSchema.php | 76 +++ framework/Db/schema/mssql/TMssqlColumnSchema.php | 57 ++ framework/Db/schema/mssql/TMssqlCommandBuilder.php | 303 ++++++++ framework/Db/schema/mssql/TMssqlPdoAdapter.php | 74 ++ framework/Db/schema/mssql/TMssqlSchema.php | 312 +++++++++ framework/Db/schema/mssql/TMssqlTableSchema.php | 35 + framework/Db/schema/mysql/TMysqlColumnSchema.php | 46 ++ framework/Db/schema/mysql/TMysqlSchema.php | 205 ++++++ framework/Db/schema/mysql/TMysqlTableSchema.php | 26 + framework/Db/schema/oci/TOciColumnSchema.php | 56 ++ framework/Db/schema/oci/TOciCommandBuilder.php | 122 ++++ framework/Db/schema/oci/TOciSchema.php | 278 ++++++++ framework/Db/schema/oci/TOciTableSchema.php | 28 + framework/Db/schema/pgsql/TPgsqlColumnSchema.php | 58 ++ framework/Db/schema/pgsql/TPgsqlSchema.php | 284 ++++++++ framework/Db/schema/pgsql/TPgsqlTableSchema.php | 27 + framework/Db/schema/sqlite/TSqliteColumnSchema.php | 35 + .../Db/schema/sqlite/TSqliteCommandBuilder.php | 43 ++ framework/Db/schema/sqlite/TSqliteSchema.php | 134 ++++ 29 files changed, 5007 insertions(+) create mode 100755 framework/Db/TDataSourceConfig.php create mode 100755 framework/Db/TDbCommand.php create mode 100755 framework/Db/TDbConnection.php create mode 100755 framework/Db/TDbDataReader.php create mode 100755 framework/Db/TDbTransaction.php create mode 100755 framework/Db/schema/TDbColumnSchema.php create mode 100755 framework/Db/schema/TDbCommandBuilder.php create mode 100755 framework/Db/schema/TDbCriteria.php create mode 100755 framework/Db/schema/TDbExpression.php create mode 100755 framework/Db/schema/TDbSchema.php create mode 100755 framework/Db/schema/TDbTableSchema.php create mode 100755 framework/Db/schema/mssql/TMssqlColumnSchema.php create mode 100755 framework/Db/schema/mssql/TMssqlCommandBuilder.php create mode 100755 framework/Db/schema/mssql/TMssqlPdoAdapter.php create mode 100755 framework/Db/schema/mssql/TMssqlSchema.php create mode 100755 framework/Db/schema/mssql/TMssqlTableSchema.php create mode 100755 framework/Db/schema/mysql/TMysqlColumnSchema.php create mode 100755 framework/Db/schema/mysql/TMysqlSchema.php create mode 100755 framework/Db/schema/mysql/TMysqlTableSchema.php create mode 100755 framework/Db/schema/oci/TOciColumnSchema.php create mode 100755 framework/Db/schema/oci/TOciCommandBuilder.php create mode 100755 framework/Db/schema/oci/TOciSchema.php create mode 100755 framework/Db/schema/oci/TOciTableSchema.php create mode 100755 framework/Db/schema/pgsql/TPgsqlColumnSchema.php create mode 100755 framework/Db/schema/pgsql/TPgsqlSchema.php create mode 100755 framework/Db/schema/pgsql/TPgsqlTableSchema.php create mode 100755 framework/Db/schema/sqlite/TSqliteColumnSchema.php create mode 100755 framework/Db/schema/sqlite/TSqliteCommandBuilder.php create mode 100755 framework/Db/schema/sqlite/TSqliteSchema.php diff --git a/framework/Db/TDataSourceConfig.php b/framework/Db/TDataSourceConfig.php new file mode 100755 index 00000000..56d87bc0 --- /dev/null +++ b/framework/Db/TDataSourceConfig.php @@ -0,0 +1,168 @@ + + * @author Christophe Boulain configuration for database connections. + * + * Example usage: mysql connection + * + * + * + * + * + * + * + * + * Usage in php: + * + * class Home extends TPage + * { + * function onLoad($param) + * { + * $db = $this->Application->Modules['db1']->DbConnection; + * $db->createCommand('...'); //... + * } + * } + * + * + * The properties of are those of the class TDbConnection. + * Set {@link setConnectionClass} attribute for a custom database connection class + * that extends the TDbConnection class. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data + * @since 3.1 + */ +class TDataSourceConfig extends TModule +{ + private $_connID=''; + private $_conn; + private $_connClass='System.Db.TDbConnection'; + + /** + * Initalize the database connection properties from attributes in tag. + * @param TXmlDocument xml configuration. + */ + public function init($xml) + { + if($this->getApplication()->getConfigurationType()==TApplication::CONFIG_TYPE_PHP) + { + if(isset($xml['database']) && is_array($xml['database'])) + { + $db=$this->getDbConnection(); + foreach($xml['database'] as $name=>$value) + $db->setSubProperty($name,$value); + } + } + else + { + if($prop=$xml->getElementByTagName('database')) + { + $db=$this->getDbConnection(); + foreach($prop->getAttributes() as $name=>$value) + $db->setSubproperty($name,$value); + } + } + } + + /** + * The module ID of another TDataSourceConfig. The {@link getDbConnection DbConnection} + * property of this configuration will equal to {@link getDbConnection DbConnection} + * of the given TDataSourceConfig module. + * @param string module ID. + */ + public function setConnectionID($value) + { + $this->_connID=$value; + } + + /** + * @return string connection module ID. + */ + public function getConnectionID() + { + return $this->_connID; + } + + /** + * Gets the TDbConnection from another module if {@link setConnectionID ConnectionID} + * is supplied and valid. Otherwise, a connection of type given by + * {@link setConnectionClass ConnectionClass} is created. + * @return TDbConnection database connection. + */ + public function getDbConnection() + { + if($this->_conn===null) + { + if($this->_connID!=='') + $this->_conn = $this->findConnectionByID($this->getConnectionID()); + else + $this->_conn = Prado::createComponent($this->getConnectionClass()); + } + return $this->_conn; + } + + /** + * Alias for getDbConnection(). + * @return TDbConnection database connection. + */ + public function getDatabase() + { + return $this->getDbConnection(); + } + + /** + * @param string Database connection class name to be created. + */ + public function getConnectionClass() + { + return $this->_connClass; + } + + /** + * The database connection class name to be created when {@link getDbConnection} + * method is called and {@link setConnectionID ConnectionID} is null. The + * {@link setConnectionClass ConnectionClass} property must be set before + * calling {@link getDbConnection} if you wish to create the connection using the + * given class name. + * @param string Database connection class name. + * @throws TConfigurationException when database connection is already established. + */ + public function setConnectionClass($value) + { + if($this->_conn!==null) + throw new TConfigurationException('datasource_dbconnection_exists', $value); + $this->_connClass=$value; + } + + /** + * Finds the database connection instance from the Application modules. + * @param string Database connection module ID. + * @return TDbConnection database connection. + * @throws TConfigurationException when module is not of TDbConnection or TDataSourceConfig. + */ + protected function findConnectionByID($id) + { + $conn = $this->getApplication()->getModule($id); + if($conn instanceof TDbConnection) + return $conn; + else if($conn instanceof TDataSourceConfig) + return $conn->getDbConnection(); + else + throw new TConfigurationException('datasource_dbconnection_invalid',$id); + } +} diff --git a/framework/Db/TDbCommand.php b/framework/Db/TDbCommand.php new file mode 100755 index 00000000..df98a943 --- /dev/null +++ b/framework/Db/TDbCommand.php @@ -0,0 +1,340 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * TDbCommand represents an SQL statement to execute against a database. + * + * It is usually created by calling {@link TDbConnection::createCommand}. + * The SQL statement to be executed may be set via {@link setText Text}. + * + * To execute a non-query SQL (such as insert, delete, update), call + * {@link execute}. To execute an SQL statement that returns result data set + * (such as SELECT), use {@link query} or its convenient versions {@link queryRow}, + * {@link queryColumn}, or {@link queryScalar}. + * + * If an SQL statement returns results (such as a SELECT SQL), the results + * can be accessed via the returned {@link TDbDataReader}. + * + * TDbCommand supports SQL statment preparation and parameter binding. + * Call {@link bindParam} to bind a PHP variable to a parameter in SQL. + * Call {@link bindValue} to bind a value to an SQL parameter. + * When binding a parameter, the SQL statement is automatically prepared. + * You may also call {@link prepare} to explicitly prepare an SQL statement. + * + * @author Qiang Xue + * @version $Id$ + * @package system.db + * @since 1.0 + */ +class TDbCommand extends TComponent +{ + private $_connection; + private $_text=''; + private $_statement=null; + private $_params; + + /** + * Constructor. + * @param TDbConnection the database connection + * @param string the SQL statement to be executed + */ + public function __construct(TDbConnection $connection,$text) + { + $this->_connection=$connection; + $this->setText($text); + } + + /** + * Set the statement to null when serializing. + */ + public function __sleep() + { + $this->_statement=null; + return array_keys(get_object_vars($this)); + } + + /** + * @return string the SQL statement to be executed + */ + public function getText() + { + return $this->_text; + } + + /** + * Specifies the SQL statement to be executed. + * Any previous execution will be terminated or cancel. + * @param string the SQL statement to be executed + */ + public function setText($value) + { + $this->_text=$value; + $this->cancel(); + } + + /** + * @return TDbConnection the connection associated with this command + */ + public function getConnection() + { + return $this->_connection; + } + + /** + * @return PDOStatement the underlying PDOStatement for this command + * It could be null if the statement is not prepared yet. + */ + public function getPdoStatement() + { + return $this->_statement; + } + + /** + * Prepares the SQL statement to be executed. + * For complex SQL statement that is to be executed multiple times, + * this may improve performance. + * For SQL statement with binding parameters, this method is invoked + * automatically. + */ + public function prepare() + { + if($this->_statement==null) + { + try + { + $this->_statement=$this->getConnection()->getPdoInstance()->prepare($this->getText()); + $this->_params=array(); + } + catch(Exception $e) + { + Prado::log('Error in preparing SQL: '.$this->getText(),TLogger::ERROR,'system.db.TDbCommand'); + throw new TDbException('TDbCommand failed to prepare the SQL statement: {0}', + $e->getMessage()); + } + } + } + + /** + * Cancels the execution of the SQL statement. + */ + public function cancel() + { + $this->_statement=null; + } + + /** + * Binds a parameter to the SQL statement to be executed. + * @param mixed Parameter identifier. For a prepared statement + * using named placeholders, this will be a parameter name of + * the form :name. For a prepared statement using question mark + * placeholders, this will be the 1-indexed position of the parameter. + * @param mixed Name of the PHP variable to bind to the SQL statement parameter + * @param int SQL data type of the parameter. If null, the type is determined by the PHP type of the value. + * @param int length of the data type + * @see http://www.php.net/manual/en/function.PDOStatement-bindParam.php + */ + public function bindParameter($name, &$value, $dataType=null, $length=null) + { + $this->prepare(); + if($dataType===null) + $this->_statement->bindParam($name,$value,$this->_connection->getPdoType(gettype($value))); + else if($length===null) + $this->_statement->bindParam($name,$value,$dataType); + else + $this->_statement->bindParam($name,$value,$dataType,$length); + if($this->_connection->enableParamLogging) + $this->_params[]=$name.'=['.gettype($value).']'; + } + + + /** + * Binds a value to a parameter. + * @param mixed Parameter identifier. For a prepared statement + * using named placeholders, this will be a parameter name of + * the form :name. For a prepared statement using question mark + * placeholders, this will be the 1-indexed position of the parameter. + * @param mixed The value to bind to the parameter + * @param int SQL data type of the parameter. If null, the type is determined by the PHP type of the value. + * @see http://www.php.net/manual/en/function.PDOStatement-bindValue.php + */ + public function bindValue($name, $value, $dataType=null) + { + $this->prepare(); + if($dataType===null) + $this->_statement->bindValue($name,$value,$this->_connection->getPdoType(gettype($value))); + else + $this->_statement->bindValue($name,$value,$dataType); + if($this->_connection->enableParamLogging) + $this->_params[]=$name.'='.var_export($value,true); + } + + /** + * Executes the SQL statement. + * This method is meant only for executing non-query SQL statement. + * No result set will be returned. + * @return integer number of rows affected by the execution. + * @throws CException execution failed + */ + public function execute() + { + $params=$this->_connection->enableParamLogging && !empty($this->_params) ? '. Bind with parameter ' . implode(', ',$this->_params) : ''; + Prado::trace('Executing SQL: '.$this->getText().$params,'system.db.TDbCommand'); + try + { + /*if($this->_connection->enableProfiling) + Yii::beginProfile('system.db.TDbCommand.execute('.$this->getText().')','system.db.TDbCommand.execute'); + */ + if($this->_statement instanceof PDOStatement) + { + $this->_statement->execute(); + $n=$this->_statement->rowCount(); + } + else + $n=$this->getConnection()->getPdoInstance()->exec($this->getText()); + + /*if($this->_connection->enableProfiling) + Yii::endProfile('system.db.TDbCommand.execute('.$this->getText().')','system.db.TDbCommand.execute'); + */ + return $n; + } + catch(Exception $e) + { + /*if($this->_connection->enableProfiling) + Yii::endProfile('system.db.TDbCommand.execute('.$this->getText().')','system.db.TDbCommand.execute'); + */ + Prado::log('Error in executing SQL: '.$this->getText().$params,TLogger::ERROR,'system.db.TDbCommand'); + throw new TDbException('TDbCommand failed to execute the SQL statement: {0}', + $e->getMessage()); + } + } + + /** + * @return String prepared SQL text for debugging purposes. + */ + public function getDebugStatementText() + { + if(Prado::getApplication()->getMode() === TApplicationMode::Debug) + { + $params=$this->_connection->enableParamLogging && !empty($this->_params) ? '. Bind with parameter ' . implode(', ',$this->_params) : ''; + return $this->_statement instanceof PDOStatement ? + $this->_statement->queryString.$params + : $this->getText().$params; + + } + } + + /** + * Executes the SQL statement and returns query result. + * This method is for executing an SQL query that returns result set. + * @return TDbDataReader the reader object for fetching the query result + * @throws CException execution failed + */ + public function query() + { + return $this->queryInternal('',0); + } + + /** + * Executes the SQL statement and returns all rows. + * @param boolean whether each row should be returned as an associated array with + * column names as the keys or the array keys are column indexes (0-based). + * @return array all rows of the query result. Each array element is an array representing a row. + * An empty array is returned if the query results in nothing. + * @throws CException execution failed + */ + public function queryAll($fetchAssociative=true) + { + return $this->queryInternal('fetchAll',$fetchAssociative ? PDO::FETCH_ASSOC : PDO::FETCH_NUM); + } + + /** + * Executes the SQL statement and returns the first row of the result. + * This is a convenient method of {@link query} when only the first row of data is needed. + * @param boolean whether the row should be returned as an associated array with + * column names as the keys or the array keys are column indexes (0-based). + * @return array the first row of the query result, false if no result. + * @throws CException execution failed + */ + public function queryRow($fetchAssociative=true) + { + return $this->queryInternal('fetch',$fetchAssociative ? PDO::FETCH_ASSOC : PDO::FETCH_NUM); + } + + /** + * Executes the SQL statement and returns the value of the first column in the first row of data. + * This is a convenient method of {@link query} when only a single scalar + * value is needed (e.g. obtaining the count of the records). + * @return mixed the value of the first column in the first row of the query result. False is returned if there is no value. + * @throws CException execution failed + */ + public function queryScalar() + { + $result=$this->queryInternal('fetchColumn',0); + if(is_resource($result) && get_resource_type($result)==='stream') + return stream_get_contents($result); + else + return $result; + } + + /** + * Executes the SQL statement and returns the first column of the result. + * This is a convenient method of {@link query} when only the first column of data is needed. + * Note, the column returned will contain the first element in each row of result. + * @return array the first column of the query result. Empty array if no result. + * @throws CException execution failed + */ + public function queryColumn() + { + return $this->queryInternal('fetchAll',PDO::FETCH_COLUMN); + } + + /** + * @param string method of PDOStatement to be called + * @param mixed the first parameter to be passed to the method + * @return mixed the method execution result + */ + private function queryInternal($method,$mode) + { + $params=$this->_connection->enableParamLogging && !empty($this->_params) ? '. Bind with parameter ' . implode(', ',$this->_params) : ''; + Prado::trace('Querying SQL: '.$this->getText().$params,'system.db.TDbCommand'); + try + { + /*if($this->_connection->enableProfiling) + Yii::beginProfile('system.db.TDbCommand.query('.$this->getText().')','system.db.TDbCommand.query'); + */ + if($this->_statement instanceof PDOStatement) + $this->_statement->execute(); + else + $this->_statement=$this->getConnection()->getPdoInstance()->query($this->getText()); + + if($method==='') + $result=new TDbDataReader($this); + else + { + $result=$this->_statement->{$method}($mode); + $this->_statement->closeCursor(); + } + + /*if($this->_connection->enableProfiling) + Yii::endProfile('system.db.TDbCommand.query('.$this->getText().')','system.db.TDbCommand.query'); + */ + return $result; + } + catch(Exception $e) + { +/* if($this->_connection->enableProfiling) + Yii::endProfile('system.db.TDbCommand.query('.$this->getText().')','system.db.TDbCommand.query'); + */ + Prado::log('Error in querying SQL: '.$this->getText().$params,TLogger::ERROR,'system.db.TDbCommand'); + throw new TDbException('TDbCommand failed to execute the SQL statement: {0}', + $e->getMessage()); + } + } +} diff --git a/framework/Db/TDbConnection.php b/framework/Db/TDbConnection.php new file mode 100755 index 00000000..c386f62b --- /dev/null +++ b/framework/Db/TDbConnection.php @@ -0,0 +1,759 @@ + + * @author Christophe Boulain + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using ('System.Db.TDbCommand'); +prado::using ('System.Db.TDbTransaction'); + +/** + * TDbConnection represents a connection to a database. + * + * This is a port of {@link http://www.yiiframework.com Yii} {@link http://www.yiiframework.com/ CDbConnection} + * + * TDbConnection works together with {@link TDbCommand}, {@link TDbDataReader} + * and {@link TDbTransaction} to provide data access to various DBMS + * in a common set of APIs. They are a thin wrapper of the {@link http://www.php.net/manual/en/ref.pdo.php PDO} + * PHP extension. + * + * To establish a connection, set {@link setActive active} to true after + * specifying {@link connectionString}, {@link username} and {@link password}. + * + * The following example shows how to create a TDbConnection instance and establish + * the actual connection: + * 
+ * $connection=new TDbConnection($dsn,$username,$password);
+ * $connection->active=true;
+ *
+ * + * After the DB connection is established, one can execute an SQL statement like the following: + * 
+ * $command=$connection->createCommand($sqlStatement);
+ * $command->execute();   // a non-query SQL statement execution
+ * // or execute an SQL query and fetch the result set
+ * $reader=$command->query();
+ *
+ * // each $row is an array representing a row of data
+ * foreach($reader as $row) ...
+ *
+ * + * One can do prepared SQL execution and bind parameters to the prepared SQL: + * 
+ * $command=$connection->createCommand($sqlStatement);
+ * $command->bindParam($name1,$value1);
+ * $command->bindParam($name2,$value2);
+ * $command->execute();
+ *
+ * + * To use transaction, do like the following: + * 
+ * $transaction=$connection->beginTransaction();
+ * try
+ * {
+ *    $connection->createCommand($sql1)->execute();
+ *    $connection->createCommand($sql2)->execute();
+ *    //.... other SQL executions
+ *    $transaction->commit();
+ * }
+ * catch(Exception $e)
+ * {
+ *    $transaction->rollBack();
+ * }
+ *
+ * + * TDbConnection also provides a set of methods to support setting and querying + * of certain DBMS attributes, such as {@link getNullConversion nullConversion}. + * + * @author Qiang Xue + * @author Christophe.Boulain + * @version $Id$ + * @package System.Db + * @since 3.2 + */ +class TDbConnection extends TComponent +{ + /** + * @var string The Data Source Name, or DSN, contains the information required to connect to the database. + * @see http://www.php.net/manual/en/function.PDO-construct.php + */ + public $connectionString; + /** + * @var string the username for establishing DB connection. Defaults to empty string. + */ + public $username=''; + /** + * @var string the password for establishing DB connection. Defaults to empty string. + */ + public $password=''; + /** + * @var integer number of seconds that table metadata can remain valid in cache. + * Use 0 or negative value to indicate not caching schema. + * If greater than 0 and the primary cache is enabled, the table metadata will be cached. + * @see schemaCachingExclude + */ + public $schemaCachingDuration=0; + /** + * @var array list of tables whose metadata should NOT be cached. Defaults to empty array. + * @see schemaCachingDuration + */ + public $schemaCachingExclude=array(); + /** + * @var boolean whether the database connection should be automatically established + * the component is being initialized. Defaults to true. Note, this property is only + * effective when the TDbConnection object is used as an application component. + */ + public $autoConnect=true; + /** + * @var string the charset used for database connection. The property is only used + * for MySQL and PostgreSQL databases. Defaults to null, meaning using default charset + * as specified by the database. + */ + public $charset; + /** + * @var boolean whether to turn on prepare emulation. Defaults to false, meaning PDO + * will use the native prepare support if available. For some databases (such as MySQL), + * this may need to be set true so that PDO can emulate the prepare support to bypass + * the buggy native prepare support. Note, this property is only effective for PHP 5.1.3 or above. + */ + public $emulatePrepare=false; + /** + * @var boolean whether to log the values that are bound to a prepare SQL statement. + * Defaults to false. During development, you may consider setting this property to true + * so that parameter values bound to SQL statements are logged for debugging purpose. + * You should be aware that logging parameter values could be expensive and have significant + * impact on the performance of your application. + */ + public $enableParamLogging=false; + /** + * @var boolean whether to enable profiling the SQL statements being executed. + * Defaults to false. This should be mainly enabled and used during development + * to find out the bottleneck of SQL executions. + */ + public $enableProfiling=false; + + private $_attributes=array(); + private $_active=false; + private $_pdo; + private $_transaction; + private $_schema; + + + /** + * Constructor. + * Note, the DB connection is not established when this connection + * instance is created. Set {@link setActive active} property to true + * to establish the connection. + * @param string The Data Source Name, or DSN, contains the information required to connect to the database. + * @param string The user name for the DSN string. + * @param string The password for the DSN string. + * @see http://www.php.net/manual/en/function.PDO-construct.php + */ + public function __construct($dsn='',$username='',$password='') + { + $this->connectionString=$dsn; + $this->username=$username; + $this->password=$password; + } + + /** + * Close the connection when serializing. + */ + public function __sleep() + { + $this->close(); + return array_keys(get_object_vars($this)); + } + + /** + * @return array list of available PDO drivers + * @see http://www.php.net/manual/en/function.PDO-getAvailableDrivers.php + */ + public static function getAvailableDrivers() + { + return PDO::getAvailableDrivers(); + } + + /** + * Initializes the component. + * This method is required by {@link IApplicationComponent} and is invoked by application + * when the TDbConnection is used as an application component. + * If you override this method, make sure to call the parent implementation + * so that the component can be marked as initialized. + */ + public function init() + { + if($this->autoConnect) + $this->setActive(true); + } + + /** + * @return boolean whether the DB connection is established + */ + public function getActive() + { + return $this->_active; + } + + /** + * Open or close the DB connection. + * @param boolean whether to open or close DB connection + * @throws CException if connection fails + */ + public function setActive($value) + { + if($value!=$this->_active) + { + if($value) + $this->open(); + else + $this->close(); + } + } + + /** + * Opens DB connection if it is currently not + * @throws CException if connection fails + */ + protected function open() + { + if($this->_pdo===null) + { + if(empty($this->connectionString)) + throw new TDbException('TDbConnection.connectionString cannot be empty.'); + try + { + Prado::trace('Opening DB connection','system.db.TDbConnection'); + $this->_pdo=$this->createPdoInstance(); + $this->initConnection($this->_pdo); + $this->_active=true; + } + catch(PDOException $e) + { + throw new TDbException('TDbConnection failed to open the DB connection: {0}',$e->getMessage()); + } + } + } + + /** + * Closes the currently active DB connection. + * It does nothing if the connection is already closed. + */ + protected function close() + { + Prado::trace('Closing DB connection','system.db.TDbConnection'); + $this->_pdo=null; + $this->_active=false; + $this->_schema=null; + } + + /** + * Creates the PDO instance. + * When some functionalities are missing in the pdo driver, we may use + * an adapter class to provides them. + * @return PDO the pdo instance + * @since 1.0.4 + */ + protected function createPdoInstance() + { + $pdoClass='PDO'; + if(($pos=strpos($this->connectionString,':'))!==false) + { + $driver=strtolower(substr($this->connectionString,0,$pos)); + if($driver==='mssql' || $driver==='dblib') + { + prado::using('System.Db.schema.mssql.TMssqlPdoAdapter'); + $pdoClass='TMssqlPdoAdapter'; + } + } + return new $pdoClass($this->connectionString,$this->username, + $this->password,$this->_attributes); + } + + /** + * Initializes the open db connection. + * This method is invoked right after the db connection is established. + * The default implementation is to set the charset for MySQL and PostgreSQL database connections. + * @param PDO the PDO instance + */ + protected function initConnection($pdo) + { + $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); + if($this->emulatePrepare && constant('PDO::ATTR_EMULATE_PREPARES')) + $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,true); + if($this->charset!==null) + { + switch ($pdo->getAttribute(PDO::ATTR_DRIVER_NAME)) + { + case 'mysql': + $stmt = $pdo->prepare('SET CHARACTER SET ?'); + break; + case 'pgsql': + $stmt = $pdo->prepare('SET client_encoding TO ?'); + break; + case 'sqlite': + $stmt = $pdo->prepare ('SET NAMES ?'); + break; + } + $stmt->execute(array($this->charset)); + } + } + + /** + * @return PDO the PDO instance, null if the connection is not established yet + */ + public function getPdoInstance() + { + return $this->_pdo; + } + + /** + * Creates a command for execution. + * @param string SQL statement associated with the new command. + * @return TDbCommand the DB command + * @throws CException if the connection is not active + */ + public function createCommand($sql) + { + if($this->getActive()) + return new TDbCommand($this,$sql); + else + throw new TDbException('TDbConnection is inactive and cannot perform any DB operations.'); + } + + /** + * @return TDbTransaction the currently active transaction. Null if no active transaction. + */ + public function getCurrentTransaction() + { + if($this->_transaction!==null) + { + if($this->_transaction->getActive()) + return $this->_transaction; + } + return null; + } + + /** + * Starts a transaction. + * @return TDbTransaction the transaction initiated + * @throws CException if the connection is not active + */ + public function beginTransaction() + { + if($this->getActive()) + { + $this->_pdo->beginTransaction(); + return $this->_transaction=new TDbTransaction($this); + } + else + throw new TDbException('TDbConnection is inactive and cannot perform any DB operations.'); + } + + /** + * @return TDbSchema the database schema for the current connection + * @throws CException if the connection is not active yet + */ + public function getSchema() + { + if($this->_schema!==null) + return $this->_schema; + else + { + if(!$this->getActive()) + throw new TDbException('TDbConnection is inactive and cannot perform any DB operations.'); + $driver=$this->getDriverName(); + switch(strtolower($driver)) + { + case 'pgsql': // PostgreSQL + prado::using('System.db.schema.TPgsqlSchema'); + return $this->_schema=new TPgsqlSchema($this); + case 'mysqli': // MySQL + case 'mysql': + prado::using('System.db.schema.TMysqlSchema'); + return $this->_schema=new TMysqlSchema($this); + case 'sqlite': // sqlite 3 + case 'sqlite2': // sqlite 2 + prado::using('System.db.schema.TSqliteSchema'); + return $this->_schema=new TSqliteSchema($this); + case 'mssql': // Mssql driver on windows hosts + case 'dblib': // dblib drivers on linux (and maybe others os) hosts + prado::using('System.db.schema.TMssqlSchema'); + return $this->_schema=new TMssqlSchema($this); + case 'oci': // Oracle driver + prado::using('System.db.schema.TOciSchema'); + return $this->_schema=new TOciSchema($this); + case 'ibm': + default: + throw new TDbException('TDbConnection does not support reading schema for {0} database.', + $driver); + } + } + } + + /** + * Returns the SQL command builder for the current DB connection. + * @return TDbCommandBuilder the command builder + */ + public function getCommandBuilder() + { + return $this->getSchema()->getCommandBuilder(); + } + + /** + * Returns the ID of the last inserted row or sequence value. + * @param string name of the sequence object (required by some DBMS) + * @return string the row ID of the last row inserted, or the last value retrieved from the sequence object + * @see http://www.php.net/manual/en/function.PDO-lastInsertId.php + */ + public function getLastInsertID($sequenceName='') + { + if($this->getActive()) + return $this->_pdo->lastInsertId($sequenceName); + else + throw new TDbException('TDbConnection is inactive and cannot perform any DB operations.'); + } + + /** + * Quotes a string value for use in a query. + * @param string string to be quoted + * @return string the properly quoted string + * @see http://www.php.net/manual/en/function.PDO-quote.php + */ + public function quoteString($str) + { + if($this->getActive()) + return $this->_pdo->quote($str); + else + throw new TDbException('TDbConnection is inactive and cannot perform any DB operations.'); + } + + /** + * Quotes a table name for use in a query. + * @param string table name + * @return string the properly quoted table name + */ + public function quoteTableName($name) + { + return $this->getSchema()->quoteTableName($name); + } + + /** + * Quotes a column name for use in a query. + * @param string column name + * @return string the properly quoted column name + */ + public function quoteColumnName($name) + { + return $this->getSchema()->quoteColumnName($name); + } + + /** + * Determines the PDO type for the specified PHP type. + * @param string The PHP type (obtained by gettype() call). + * @return integer the corresponding PDO type + */ + public function getPdoType($type) + { + static $map=array + ( + 'boolean'=>PDO::PARAM_BOOL, + 'integer'=>PDO::PARAM_INT, + 'string'=>PDO::PARAM_STR, + 'NULL'=>PDO::PARAM_NULL, + ); + return isset($map[$type]) ? $map[$type] : PDO::PARAM_STR; + } + + /** + * @return TDbColumnCaseMode the case of the column names + */ + public function getColumnCase() + { + switch($this->getAttribute(PDO::ATTR_CASE)) + { + case PDO::CASE_NATURAL: + return TDbColumnCaseMode::Preserved; + case PDO::CASE_LOWER: + return TDbColumnCaseMode::LowerCase; + case PDO::CASE_UPPER: + return TDbColumnCaseMode::UpperCase; + } + } + + /** + * @param TDbColumnCaseMode the case of the column names + */ + public function setColumnCase($value) + { + switch(TPropertyValue::ensureEnum($value,'TDbColumnCaseMode')) + { + case TDbColumnCaseMode::Preserved: + $value=PDO::CASE_NATURAL; + break; + case TDbColumnCaseMode::LowerCase: + $value=PDO::CASE_LOWER; + break; + case TDbColumnCaseMode::UpperCase: + $value=PDO::CASE_UPPER; + break; + } + $this->setAttribute(PDO::ATTR_CASE,$value); + } + /** + * @return TDbNullConversionMode how the null and empty strings are converted + */ + public function getNullConversion() + { + switch($this->getAttribute(PDO::ATTR_ORACLE_NULLS)) + { + case PDO::NULL_NATURAL: + return TDbNullConversionMode::Preserved; + case PDO::NULL_EMPTY_STRING: + return TDbNullConversionMode::EmptyStringToNull; + case PDO::NULL_TO_STRING: + return TDbNullConversionMode::NullToEmptyString; + } + } + + /** + * @param TDbNullConversionMode how the null and empty strings are converted + */ + public function setNullConversion($value) + { + switch(TPropertyValue::ensureEnum($value,'TDbNullConversionMode')) + { + case TDbNullConversionMode::Preserved: + $value=PDO::NULL_NATURAL; + break; + case TDbNullConversionMode::EmptyStringToNull: + $value=PDO::NULL_EMPTY_STRING; + break; + case TDbNullConversionMode::NullToEmptyString: + $value=PDO::NULL_TO_STRING; + break; + } + $this->setAttribute(PDO::ATTR_ORACLE_NULLS,$value); + } + + /** + * @return boolean whether creating or updating a DB record will be automatically committed. + * Some DBMS (such as sqlite) may not support this feature. + */ + public function getAutoCommit() + { + return $this->getAttribute(PDO::ATTR_AUTOCOMMIT); + } + + /** + * @param boolean whether creating or updating a DB record will be automatically committed. + * Some DBMS (such as sqlite) may not support this feature. + */ + public function setAutoCommit($value) + { + $this->setAttribute(PDO::ATTR_AUTOCOMMIT,$value); + } + + /** + * @return boolean whether the connection is persistent or not + * Some DBMS (such as sqlite) may not support this feature. + */ + public function getPersistent() + { + return $this->getAttribute(PDO::ATTR_PERSISTENT); + } + + /** + * @param boolean whether the connection is persistent or not + * Some DBMS (such as sqlite) may not support this feature. + */ + public function setPersistent($value) + { + return $this->setAttribute(PDO::ATTR_PERSISTENT,$value); + } + + /** + * @return string name of the DB driver + */ + public function getDriverName() + { + return $this->getAttribute(PDO::ATTR_DRIVER_NAME); + } + + /** + * @return string the version information of the DB driver + */ + public function getClientVersion() + { + return $this->getAttribute(PDO::ATTR_CLIENT_VERSION); + } + + /** + * @return string the status of the connection + * Some DBMS (such as sqlite) may not support this feature. + */ + public function getConnectionStatus() + { + return $this->getAttribute(PDO::ATTR_CONNECTION_STATUS); + } + + /** + * @return boolean whether the connection performs data prefetching + */ + public function getPrefetch() + { + return $this->getAttribute(PDO::ATTR_PREFETCH); + } + + /** + * @return string the information of DBMS server + */ + public function getServerInfo() + { + return $this->getAttribute(PDO::ATTR_SERVER_INFO); + } + + /** + * @return string the version information of DBMS server + */ + public function getServerVersion() + { + return $this->getAttribute(PDO::ATTR_SERVER_VERSION); + } + + /** + * @return int timeout settings for the connection + */ + public function getTimeout() + { + return $this->getAttribute(PDO::ATTR_TIMEOUT); + } + + /** + * Obtains a specific DB connection attribute information. + * @param int the attribute to be queried + * @return mixed the corresponding attribute information + * @see http://www.php.net/manual/en/function.PDO-getAttribute.php + */ + public function getAttribute($name) + { + if($this->getActive()) + return $this->_pdo->getAttribute($name); + else + throw new TDbException('TDbConnection is inactive and cannot perform any DB operations.'); + } + + /** + * Sets an attribute on the database connection. + * @param int the attribute to be set + * @param mixed the attribute value + * @see http://www.php.net/manual/en/function.PDO-setAttribute.php + */ + public function setAttribute($name,$value) + { + if($this->_pdo instanceof PDO) + $this->_pdo->setAttribute($name,$value); + else + $this->_attributes[$name]=$value; + } + + /** + * Returns the statistical results of SQL executions. + * The results returned include the number of SQL statements executed and + * the total time spent. + * In order to use this method, {@link enableProfiling} has to be set true. + * @return array the first element indicates the number of SQL statements executed, + * and the second element the total time spent in SQL execution. + * @since 1.0.6 + */ + public function getStats() + { + /*$logger=Yii::getLogger(); + $timings=$logger->getProfilingResults(null,'system.db.TDbCommand.query'); + $count=count($timings); + $time=array_sum($timings); + $timings=$logger->getProfilingResults(null,'system.db.TDbCommand.execute'); + $count+=count($timings); + $time+=array_sum($timings); + return array($count,$time);*/ + } + + /** + * Getters & Setters to provide BC with prado-3.1 + */ + public function getConnectionString() { return $this->connectionString; } + public function getUsername () { return $this->username; } + public function getPassword () { return $this->password; } + public function getCharset () { return $this->charset; } + public function getSchemaCachingDuration() { return $this->schemaCachingDuration; } + public function getSchemaCachingExclude () { return $this->schemaCachingExclude; } + public function getAutoConnect () { return $this->autoConnect; } + public function getEmulatePrepare () { return $this->emulatePrepare; } + public function getEnableParamLogging () { return $this->enableParamLogging; } + public function getEnableProfiling () { return $this->enableProfiling; } + + public function setConnectionString($value) { $this->connectionString=TPropertyValue::ensureString($value); } + public function setUsername ($value) { $this->username=TPropertyValue::ensureString($value); } + public function setPassword ($value) { $this->password=TPropertyValue::ensureString($value); } + public function setCharset ($value) { $this->charset=TPropertyValue::ensureString($value); } + public function setSchemaCachingDuration ($value) { $this->schemaCachingDuration=TPropertyValue::ensureInteger($value); } + public function setSchemaCachingExclude ($value) { $this->username=TPropertyValue::ensureArray($value); } + public function setAutoConnect ($value) { $this->autoConnect = TPropertyValue::ensureBoolean($value); } + public function setEnablePrepare ($value) { $this->emulatePrepare = TPropertyValue::ensureBoolean($value); } + public function setEnableParamLogging ($value) { $this->enableParamLogging = TPropertyValue::ensureBoolean($value); } + public function setEnableProfiling ($value) { $this->enableProfiling = TPropertyValue::ensureBoolean ($value) ; } +} + + +/** + * TDbColumnCaseMode + * + * @author Qiang Xue + * @version $Id$ + * @package System.Db + * @since 3.0 + */ +class TDbColumnCaseMode extends TEnumerable +{ + /** + * Column name cases are kept as is from the database + */ + const Preserved='Preserved'; + /** + * Column names are converted to lower case + */ + const LowerCase='LowerCase'; + /** + * Column names are converted to upper case + */ + const UpperCase='UpperCase'; +} + +/** + * TDbNullConversionMode + * + * @author Qiang Xue + * @version $Id$ + * @package System.Db + * @since 3.0 + */ +class TDbNullConversionMode extends TEnumerable +{ + /** + * No conversion is performed for null and empty values. + */ + const Preserved='Preserved'; + /** + * NULL is converted to empty string + */ + const NullToEmptyString='NullToEmptyString'; + /** + * Empty string is converted to NULL + */ + const EmptyStringToNull='EmptyStringToNull'; +} diff --git a/framework/Db/TDbDataReader.php b/framework/Db/TDbDataReader.php new file mode 100755 index 00000000..87fee5ed --- /dev/null +++ b/framework/Db/TDbDataReader.php @@ -0,0 +1,221 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * TDbDataReader represents a forward-only stream of rows from a query result set. + * + * To read the current row of data, call {@link read}. The method {@link readAll} + * returns all the rows in a single array. + * + * One can also retrieve the rows of data in TDbDataReader by using foreach: + * 
+ * foreach($reader as $row)
+ *     // $row represents a row of data
+ *
+ * Since TDbDataReader is a forward-only stream, you can only traverse it once. + * + * It is possible to use a specific mode of data fetching by setting + * {@link setFetchMode FetchMode}. See {@link http://www.php.net/manual/en/function.PDOStatement-setFetchMode.php} + * for more details. + * + * @author Qiang Xue + * @version $Id$ + * @package system.db + * @since 1.0 + */ +class TDbDataReader extends TComponent implements Iterator +{ + private $_statement; + private $_closed=false; + private $_row; + private $_index=-1; + + /** + * Constructor. + * @param TDbCommand the command generating the query result + */ + public function __construct(TDbCommand $command) + { + $this->_statement=$command->getPdoStatement(); + $this->_statement->setFetchMode(PDO::FETCH_ASSOC); + } + + /** + * Binds a column to a PHP variable. + * When rows of data are being fetched, the corresponding column value + * will be set in the variable. Note, the fetch mode must include PDO::FETCH_BOUND. + * @param mixed Number of the column (1-indexed) or name of the column + * in the result set. If using the column name, be aware that the name + * should match the case of the column, as returned by the driver. + * @param mixed Name of the PHP variable to which the column will be bound. + * @param int Data type of the parameter + * @see http://www.php.net/manual/en/function.PDOStatement-bindColumn.php + */ + public function bindColumn($column, &$value, $dataType=null) + { + if($dataType===null) + $this->_statement->bindColumn($column,$value); + else + $this->_statement->bindColumn($column,$value,$dataType); + } + + /** + * @see http://www.php.net/manual/en/function.PDOStatement-setFetchMode.php + */ + public function setFetchMode($mode) + { + $params=func_get_args(); + call_user_func_array(array($this->_statement,'setFetchMode'),$params); + } + + /** + * Advances the reader to the next row in a result set. + * @return array|false the current row, false if no more row available + */ + public function read() + { + return $this->_statement->fetch(); + } + + /** + * Returns a single column from the next row of a result set. + * @param int zero-based column index + * @return mixed|false the column of the current row, false if no more row available + */ + public function readColumn($columnIndex) + { + return $this->_statement->fetchColumn($columnIndex); + } + + /** + * Returns an object populated with the next row of data. + * @param string class name of the object to be created and populated + * @param array Elements of this array are passed to the constructor + * @return mixed|false the populated object, false if no more row of data available + */ + public function readObject($className,$fields) + { + return $this->_statement->fetchObject($className,$fields); + } + + /** + * Reads the whole result set into an array. + * @return array the result set (each array element represents a row of data). + * An empty array will be returned if the result contains no row. + */ + public function readAll() + { + return $this->_statement->fetchAll(); + } + + /** + * Advances the reader to the next result when reading the results of a batch of statements. + * This method is only useful when there are multiple result sets + * returned by the query. Not all DBMS support this feature. + */ + public function nextResult() + { + return $this->_statement->nextRowset(); + } + + /** + * Closes the reader. + * This frees up the resources allocated for executing this SQL statement. + * Read attemps after this method call are unpredictable. + */ + public function close() + { + $this->_statement->closeCursor(); + $this->_closed=true; + } + + /** + * @return boolean whether the reader is closed or not. + */ + public function getIsClosed() + { + return $this->_closed; + } + + /** + * @return int number of rows contained in the result. + * Note, most DBMS may not give a meaningful count. + * In this case, use "SELECT COUNT(*) FROM tableName" to obtain the number of rows. + */ + public function getRowCount() + { + return $this->_statement->rowCount(); + } + + /** + * @return int the number of columns in the result set. + * Note, even there's no row in the reader, this still gives correct column number. + */ + public function getColumnCount() + { + return $this->_statement->columnCount(); + } + + /** + * Resets the iterator to the initial state. + * This method is required by the interface Iterator. + * @throws CException if this method is invoked twice + */ + public function rewind() + { + if($this->_index<0) + { + $this->_row=$this->_statement->fetch(); + $this->_index=0; + } + else + throw new TDbException('dbdatareader_rewind_invalid'); + } + + /** + * Returns the index of the current row. + * This method is required by the interface Iterator. + * @return integer the index of the current row. + */ + public function key() + { + return $this->_index; + } + + /** + * Returns the current row. + * This method is required by the interface Iterator. + * @return mixed the current row. + */ + public function current() + { + return $this->_row; + } + + /** + * Moves the internal pointer to the next row. + * This method is required by the interface Iterator. + */ + public function next() + { + $this->_row=$this->_statement->fetch(); + $this->_index++; + } + + /** + * Returns whether there is a row of data at current position. + * This method is required by the interface Iterator. + * @return boolean whether there is a row of data at current position. + */ + public function valid() + { + return $this->_row!==false; + } +} diff --git a/framework/Db/TDbTransaction.php b/framework/Db/TDbTransaction.php new file mode 100755 index 00000000..ff9924d3 --- /dev/null +++ b/framework/Db/TDbTransaction.php @@ -0,0 +1,111 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +Prado::using('System.Db.TDbDataReader'); + + +/** + * TDbTransaction represents a DB transaction. + * + * It is usually created by calling {@link TDbConnection::beginTransaction}. + * + * The following code is a common scenario of using transactions: + * 
+ * $transaction=$connection->beginTransaction();
+ * try
+ * {
+ *    $connection->createCommand($sql1)->execute();
+ *    $connection->createCommand($sql2)->execute();
+ *    //.... other SQL executions
+ *    $transaction->commit();
+ * }
+ * catch(Exception $e)
+ * {
+ *    $transaction->rollBack();
+ * }
+ *
+ * + * @author Qiang Xue + * @version $Id$ + * @package system.db + * @since 1.0 + */ +class TDbTransaction extends TComponent +{ + private $_connection=null; + private $_active; + + /** + * Constructor. + * @param TDbConnection the connection associated with this transaction + * @see TDbConnection::beginTransaction + */ + public function __construct(TDbConnection $connection) + { + $this->_connection=$connection; + $this->setActive(true); + } + + /** + * Commits a transaction. + * @throws CException if the transaction or the DB connection is not active. + */ + public function commit() + { + if($this->_active && $this->_connection->getActive()) + { + Prado::trace('Committing transaction','system.db.TDbTransaction'); + $this->_connection->getPdoInstance()->commit(); + $this->_active=false; + } + else + throw new TDbException('dbtransaction_transaction_inactive'); + } + + /** + * Rolls back a transaction. + * @throws CException if the transaction or the DB connection is not active. + */ + public function rollback() + { + if($this->_active && $this->_connection->getActive()) + { + Prado::trace('Rolling back transaction','system.db.TDbTransaction'); + $this->_connection->getPdoInstance()->rollBack(); + $this->_active=false; + } + else + throw new TDbException('dbtransaction_transaction_inactive'); + } + + /** + * @return TDbConnection the DB connection for this transaction + */ + public function getConnection() + { + return $this->_connection; + } + + /** + * @return boolean whether this transaction is active + */ + public function getActive() + { + return $this->_active; + } + + /** + * @param boolean whether this transaction is active + */ + protected function setActive($value) + { + $this->_active=TPropertyValue::ensureBoolean($value); + } +} diff --git a/framework/Db/schema/TDbColumnSchema.php b/framework/Db/schema/TDbColumnSchema.php new file mode 100755 index 00000000..094ef8e0 --- /dev/null +++ b/framework/Db/schema/TDbColumnSchema.php @@ -0,0 +1,145 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.db.schema.TDbExpression'); + +/** + * TDbColumnSchema class describes the column meta data of a database table. + * + * @author Qiang Xue + * @version $Id$ + * @package system.db.schema + * @since 1.0 + */ +class TDbColumnSchema extends TComponent +{ + /** + * @var string name of this column (without quotes). + */ + public $name; + /** + * @var string raw name of this column. This is the quoted name that can be used in SQL queries. + */ + public $rawName; + /** + * @var boolean whether this column can be null. + */ + public $allowNull; + /** + * @var string the DB type of this column. + */ + public $dbType; + /** + * @var string the PHP type of this column. + */ + public $type; + /** + * @var mixed default value of this column + */ + public $defaultValue; + /** + * @var integer size of the column. + */ + public $size; + /** + * @var integer precision of the column data, if it is numeric. + */ + public $precision; + /** + * @var integer scale of the column data, if it is numeric. + */ + public $scale; + /** + * @var boolean whether this column is a primary key + */ + public $isPrimaryKey; + /** + * @var boolean whether this column is a foreign key + */ + public $isForeignKey; + + + /** + * Initializes the column with its DB type and default value. + * This sets up the column's PHP type, size, precision, scale as well as default value. + * @param string the column's DB type + * @param mixed the default value + */ + public function init($dbType, $defaultValue) + { + $this->dbType=$dbType; + $this->extractType($dbType); + $this->extractLimit($dbType); + if($defaultValue!==null) + $this->extractDefault($defaultValue); + } + + /** + * Extracts the PHP type from DB type. + * @param string DB type + */ + protected function extractType($dbType) + { + if(stripos($dbType,'int')!==false) + $this->type='integer'; + else if(stripos($dbType,'bool')!==false) + $this->type='boolean'; + else if(preg_match('/(real|floa|doub)/i',$dbType)) + $this->type='double'; + else + $this->type='string'; + } + + /** + * Extracts size, precision and scale information from column's DB type. + * @param string the column's DB type + */ + protected function extractLimit($dbType) + { + if(strpos($dbType,'(') && preg_match('/\((.*)\)/',$dbType,$matches)) + { + $values=explode(',',$matches[1]); + $this->size=$this->precision=(int)$values[0]; + if(isset($values[1])) + $this->scale=(int)$values[1]; + } + } + + /** + * Extracts the default value for the column. + * The value is typecasted to correct PHP type. + * @param mixed the default value obtained from metadata + */ + protected function extractDefault($defaultValue) + { + $this->defaultValue=$this->typecast($defaultValue); + } + + /** + * Converts the input value to the type that this column is of. + * @param mixed input value + * @return mixed converted value + */ + public function typecast($value) + { + if(gettype($value)===$this->type || $value===null || $value instanceof TDbExpression) + return $value; + if($value==='') + return $this->type==='string' ? '' : null; + switch($this->type) + { + case 'integer': return (integer)$value; + case 'boolean': return (boolean)$value; + case 'double': return (double)$value; + case 'string': return (string)$value; + default: return $value; + } + } +} diff --git a/framework/Db/schema/TDbCommandBuilder.php b/framework/Db/schema/TDbCommandBuilder.php new file mode 100755 index 00000000..1ddd2fbb --- /dev/null +++ b/framework/Db/schema/TDbCommandBuilder.php @@ -0,0 +1,656 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.db.schema.TDbSchema'); +prado::using('System.db.schema.TDbCriteria'); + +/** + * TDbCommandBuilder provides basic methods to create query commands for tables. + * + * @author Qiang Xue + * @version $Id$ + * @package system.db.schema + * @since 1.0 + */ +class TDbCommandBuilder extends TComponent +{ + const PARAM_PREFIX=':yp'; + + private $_schema; + private $_connection; + + /** + * @param TDbSchema the schema for this command builder + */ + public function __construct($schema) + { + $this->_schema=$schema; + $this->_connection=$schema->getDbConnection(); + } + + /** + * @return TDbConnection database connection. + */ + public function getDbConnection() + { + return $this->_connection; + } + + /** + * @return TDbSchema the schema for this command builder. + */ + public function getSchema() + { + return $this->_schema; + } + + /** + * Returns the last insertion ID for the specified table. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @return mixed last insertion id. Null is returned if no sequence name. + */ + public function getLastInsertID($table) + { + $this->ensureTable($table); + if($table->sequenceName!==null) + return $this->_connection->getLastInsertID($table->sequenceName); + else + return null; + } + + /** + * Creates a SELECT command for a single table. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param TDbCriteria the query criteria + * @return TDbCommand query command. + */ + public function createFindCommand($table,$criteria) + { + $this->ensureTable($table); + $select=is_array($criteria->select) ? implode(', ',$criteria->select) : $criteria->select; + $sql="SELECT {$select} FROM {$table->rawName}"; + $sql=$this->applyJoin($sql,$criteria->join); + $sql=$this->applyCondition($sql,$criteria->condition); + $sql=$this->applyGroup($sql,$criteria->group); + $sql=$this->applyHaving($sql,$criteria->having); + $sql=$this->applyOrder($sql,$criteria->order); + $sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset); + $command=$this->_connection->createCommand($sql); + $this->bindValues($command,$criteria->params); + return $command; + } + + /** + * Creates a COUNT(*) command for a single table. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param TDbCriteria the query criteria + * @return TDbCommand query command. + */ + public function createCountCommand($table,$criteria) + { + $this->ensureTable($table); + $criteria->select='COUNT(*)'; + return $this->createFindCommand($table,$criteria); + } + + /** + * Creates a DELETE command. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param TDbCriteria the query criteria + * @return TDbCommand delete command. + */ + public function createDeleteCommand($table,$criteria) + { + $this->ensureTable($table); + $sql="DELETE FROM {$table->rawName}"; + $sql=$this->applyJoin($sql,$criteria->join); + $sql=$this->applyCondition($sql,$criteria->condition); + $sql=$this->applyGroup($sql,$criteria->group); + $sql=$this->applyHaving($sql,$criteria->having); + $sql=$this->applyOrder($sql,$criteria->order); + $sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset); + $command=$this->_connection->createCommand($sql); + $this->bindValues($command,$criteria->params); + return $command; + } + + /** + * Creates an INSERT command. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param array data to be inserted (column name=>column value). If a key is not a valid column name, the corresponding value will be ignored. + * @return TDbCommand insert command + */ + public function createInsertCommand($table,$data) + { + $this->ensureTable($table); + $fields=array(); + $values=array(); + $placeholders=array(); + $i=0; + foreach($data as $name=>$value) + { + if(($column=$table->getColumn($name))!==null && ($value!==null || $column->allowNull)) + { + $fields[]=$column->rawName; + if($value instanceof TDbExpression) + $placeholders[]=(string)$value; + else + { + $placeholders[]=self::PARAM_PREFIX.$i; + $values[self::PARAM_PREFIX.$i]=$column->typecast($value); + $i++; + } + } + } + $sql="INSERT INTO {$table->rawName} (".implode(', ',$fields).') VALUES ('.implode(', ',$placeholders).')'; + $command=$this->_connection->createCommand($sql); + + foreach($values as $name=>$value) + $command->bindValue($name,$value); + + return $command; + } + + /** + * Creates an UPDATE command. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param array list of columns to be updated (name=>value) + * @param TDbCriteria the query criteria + * @return TDbCommand update command. + */ + public function createUpdateCommand($table,$data,$criteria) + { + $this->ensureTable($table); + $fields=array(); + $values=array(); + $bindByPosition=isset($criteria->params[0]); + $i=0; + foreach($data as $name=>$value) + { + if(($column=$table->getColumn($name))!==null) + { + if($value instanceof TDbExpression) + $fields[]=$column->rawName.'='.(string)$value; + else if($bindByPosition) + { + $fields[]=$column->rawName.'=?'; + $values[]=$column->typecast($value); + } + else + { + $fields[]=$column->rawName.'='.self::PARAM_PREFIX.$i; + $values[self::PARAM_PREFIX.$i]=$column->typecast($value); + $i++; + } + } + } + if($fields===array()) + throw new TDbException('No columns are being updated for table "{0}".', + $table->name); + $sql="UPDATE {$table->rawName} SET ".implode(', ',$fields); + $sql=$this->applyJoin($sql,$criteria->join); + $sql=$this->applyCondition($sql,$criteria->condition); + $sql=$this->applyOrder($sql,$criteria->order); + $sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset); + + $command=$this->_connection->createCommand($sql); + $this->bindValues($command,array_merge($values,$criteria->params)); + + return $command; + } + + /** + * Creates an UPDATE command that increments/decrements certain columns. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param TDbCriteria the query criteria + * @param array counters to be updated (counter increments/decrements indexed by column names.) + * @return TDbCommand the created command + * @throws CException if no counter is specified + */ + public function createUpdateCounterCommand($table,$counters,$criteria) + { + $this->ensureTable($table); + $fields=array(); + foreach($counters as $name=>$value) + { + if(($column=$table->getColumn($name))!==null) + { + $value=(int)$value; + if($value<0) + $fields[]="{$column->rawName}={$column->rawName}-".(-$value); + else + $fields[]="{$column->rawName}={$column->rawName}+".$value; + } + } + if($fields!==array()) + { + $sql="UPDATE {$table->rawName} SET ".implode(', ',$fields); + $sql=$this->applyJoin($sql,$criteria->join); + $sql=$this->applyCondition($sql,$criteria->condition); + $sql=$this->applyOrder($sql,$criteria->order); + $sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset); + $command=$this->_connection->createCommand($sql); + $this->bindValues($command,$criteria->params); + return $command; + } + else + throw new TDbException('No counter columns are being updated for table "{0}".', + $table->name); + } + + /** + * Creates a command based on a given SQL statement. + * @param string the explicitly specified SQL statement + * @param array parameters that will be bound to the SQL statement + * @return TDbCommand the created command + */ + public function createSqlCommand($sql,$params=array()) + { + $command=$this->_connection->createCommand($sql); + $this->bindValues($command,$params); + return $command; + } + + /** + * Alters the SQL to apply JOIN clause. + * @param string the SQL statement to be altered + * @param string the JOIN clause (starting with join type, such as INNER JOIN) + * @return string the altered SQL statement + */ + public function applyJoin($sql,$join) + { + if($join!=='') + return $sql.' '.$join; + else + return $sql; + } + + /** + * Alters the SQL to apply WHERE clause. + * @param string the SQL statement without WHERE clause + * @param string the WHERE clause (without WHERE keyword) + * @return string the altered SQL statement + */ + public function applyCondition($sql,$condition) + { + if($condition!=='') + return $sql.' WHERE '.$condition; + else + return $sql; + } + + /** + * Alters the SQL to apply ORDER BY. + * @param string SQL statement without ORDER BY. + * @param string column ordering + * @return string modified SQL applied with ORDER BY. + */ + public function applyOrder($sql,$orderBy) + { + if($orderBy!=='') + return $sql.' ORDER BY '.$orderBy; + else + return $sql; + } + + /** + * Alters the SQL to apply LIMIT and OFFSET. + * Default implementation is applicable for PostgreSQL, MySQL and SQLite. + * @param string SQL query string without LIMIT and OFFSET. + * @param integer maximum number of rows, -1 to ignore limit. + * @param integer row offset, -1 to ignore offset. + * @return string SQL with LIMIT and OFFSET + */ + public function applyLimit($sql,$limit,$offset) + { + if($limit>=0) + $sql.=' LIMIT '.(int)$limit; + if($offset>0) + $sql.=' OFFSET '.(int)$offset; + return $sql; + } + + /** + * Alters the SQL to apply GROUP BY. + * @param string SQL query string without GROUP BY. + * @param string GROUP BY + * @return string SQL with GROUP BY. + */ + public function applyGroup($sql,$group) + { + if($group!=='') + return $sql.' GROUP BY '.$group; + else + return $sql; + } + + /** + * Alters the SQL to apply HAVING. + * @param string SQL query string without HAVING + * @param string HAVING + * @return string SQL with HAVING + * @since 1.0.1 + */ + public function applyHaving($sql,$having) + { + if($having!=='') + return $sql.' HAVING '.$having; + else + return $sql; + } + + /** + * Binds parameter values for an SQL command. + * @param TDbCommand database command + * @param array values for binding (integer-indexed array for question mark placeholders, string-indexed array for named placeholders) + */ + public function bindValues($command, $values) + { + if(($n=count($values))===0) + return; + if(isset($values[0])) // question mark placeholders + { + for($i=0;$i<$n;++$i) + $command->bindValue($i+1,$values[$i]); + } + else // named placeholders + { + foreach($values as $name=>$value) + { + if($name[0]!==':') + $name=':'.$name; + $command->bindValue($name,$value); + } + } + } + + /** + * Creates a query criteria. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param mixed query condition or criteria. + * If a string, it is treated as query condition (the WHERE clause); + * If an array, it is treated as the initial values for constructing a {@link TDbCriteria} object; + * Otherwise, it should be an instance of {@link TDbCriteria}. + * @param array parameters to be bound to an SQL statement. + * This is only used when the first parameter is a string (query condition). + * In other cases, please use {@link TDbCriteria::params} to set parameters. + * @return TDbCriteria the created query criteria + * @throws CException if the condition is not string, array and TDbCriteria + */ + public function createCriteria($condition='',$params=array()) + { + if(is_array($condition)) + $criteria=new TDbCriteria($condition); + else if($condition instanceof TDbCriteria) + $criteria=clone $condition; + else + { + $criteria=new TDbCriteria; + $criteria->condition=$condition; + $criteria->params=$params; + } + return $criteria; + } + + /** + * Creates a query criteria with the specified primary key. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param mixed primary key value(s). Use array for multiple primary keys. For composite key, each key value must be an array (column name=>column value). + * @param mixed query condition or criteria. + * If a string, it is treated as query condition; + * If an array, it is treated as the initial values for constructing a {@link TDbCriteria}; + * Otherwise, it should be an instance of {@link TDbCriteria}. + * @param array parameters to be bound to an SQL statement. + * This is only used when the second parameter is a string (query condition). + * In other cases, please use {@link TDbCriteria::params} to set parameters. + * @return TDbCriteria the created query criteria + */ + public function createPkCriteria($table,$pk,$condition='',$params=array()) + { + $this->ensureTable($table); + $criteria=$this->createCriteria($condition,$params); + if(!is_array($pk)) // single key + $pk=array($pk); + if(is_array($table->primaryKey) && !isset($pk[0]) && $pk!==array()) // single composite key + $pk=array($pk); + $condition=$this->createInCondition($table,$table->primaryKey,$pk); + if($criteria->condition!=='') + $criteria->condition=$condition.' AND ('.$criteria->condition.')'; + else + $criteria->condition=$condition; + + return $criteria; + } + + /** + * Generates the expression for selecting rows of specified primary key values. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param array list of primary key values to be selected within + * @param string column prefix (ended with dot). If null, it will be the table name + * @return string the expression for selection + */ + public function createPkCondition($table,$values,$prefix=null) + { + $this->ensureTable($table); + return $this->createInCondition($table,$table->primaryKey,$values,$prefix); + } + + /** + * Creates a query criteria with the specified column values. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param array column values that should be matched in the query (name=>value) + * @param mixed query condition or criteria. + * If a string, it is treated as query condition; + * If an array, it is treated as the initial values for constructing a {@link TDbCriteria}; + * Otherwise, it should be an instance of {@link TDbCriteria}. + * @param array parameters to be bound to an SQL statement. + * This is only used when the second parameter is a string (query condition). + * In other cases, please use {@link TDbCriteria::params} to set parameters. + * @return TDbCriteria the created query criteria + */ + public function createColumnCriteria($table,$columns,$condition='',$params=array()) + { + $this->ensureTable($table); + $criteria=$this->createCriteria($condition,$params); + $bindByPosition=isset($criteria->params[0]); + $conditions=array(); + $values=array(); + $i=0; + foreach($columns as $name=>$value) + { + if(($column=$table->getColumn($name))!==null) + { + if($value!==null) + { + if($bindByPosition) + { + $conditions[]=$table->rawName.'.'.$column->rawName.'=?'; + $values[]=$value; + } + else + { + $conditions[]=$table->rawName.'.'.$column->rawName.'='.self::PARAM_PREFIX.$i; + $values[self::PARAM_PREFIX.$i]=$value; + $i++; + } + } + else + $conditions[]=$table->rawName.'.'.$column->rawName.' IS NULL'; + } + else + throw new TDbException('Table "{0}" does not have a column named "{1}".', + $table->name,$name); + } + $criteria->params=array_merge($values,$criteria->params); + if(isset($conditions[0])) + { + if($criteria->condition!=='') + $criteria->condition=implode(' AND ',$conditions).' AND ('.$criteria->condition.')'; + else + $criteria->condition=implode(' AND ',$conditions); + } + return $criteria; + } + + /** + * Generates the expression for searching the specified keywords within a list of columns. + * The search expression is generated using the 'LIKE' SQL syntax. + * Every word in the keywords must be present and appear in at least one of the columns. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param array list of column names for potential search condition. + * @param mixed search keywords. This can be either a string with space-separated keywords or an array of keywords. + * @param string optional column prefix (with dot at the end). If null, the table name will be used as the prefix. + * @param boolean whether the search is case-sensitive. Defaults to true. This parameter + * has been available since version 1.0.4. + * @return string SQL search condition matching on a set of columns. An empty string is returned + * if either the column array or the keywords are empty. + */ + public function createSearchCondition($table,$columns,$keywords,$prefix=null,$caseSensitive=true) + { + $this->ensureTable($table); + if(!is_array($keywords)) + $keywords=preg_split('/\s+/u',$keywords,-1,PREG_SPLIT_NO_EMPTY); + if(empty($keywords)) + return ''; + if($prefix===null) + $prefix=$table->rawName.'.'; + $conditions=array(); + foreach($columns as $name) + { + if(($column=$table->getColumn($name))===null) + throw new TDbException('Table "{0}" does not have a column named "{0}".', + $table->name,$name); + $condition=array(); + foreach($keywords as $keyword) + { + if($caseSensitive) + $condition[]=$prefix.$column->rawName.' LIKE '.$this->_connection->quoteValue('%'.$keyword.'%'); + else + $condition[]='LOWER('.$prefix.$column->rawName.') LIKE LOWER('.$this->_connection->quoteValue('%'.$keyword.'%').')'; + } + $conditions[]=implode(' AND ',$condition); + } + return '('.implode(' OR ',$conditions).')'; + } + + /** + * Generates the expression for selecting rows of specified primary key values. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param mixed the column name(s). It can be either a string indicating a single column + * or an array of column names. If the latter, it stands for a composite key. + * @param array list of key values to be selected within + * @param string column prefix (ended with dot). If null, it will be the table name + * @return string the expression for selection + * @since 1.0.4 + */ + public function createInCondition($table,$columnName,$values,$prefix=null) + { + if(($n=count($values))<1) + return '0=1'; + + $this->ensureTable($table); + + if($prefix===null) + $prefix=$table->rawName.'.'; + + $db=$this->_connection; + + if(is_array($columnName) && count($columnName)===1) + $columnName=reset($columnName); + + if(is_string($columnName)) // simple key + { + if(!isset($table->columns[$columnName])) + throw new TDbException('Table "{0}" does not have a column named "{1}".', + $table->name, $columnName); + $column=$table->columns[$columnName]; + + foreach($values as &$value) + { + $value=$column->typecast($value); + if(is_string($value)) + $value=$db->quoteValue($value); + } + if($n===1) + return $prefix.$column->rawName.($values[0]===null?' IS NULL':'='.$values[0]); + else + return $prefix.$column->rawName.' IN ('.implode(', ',$values).')'; + } + else if(is_array($columnName)) // composite key: $values=array(array('pk1'=>'v1','pk2'=>'v2'),array(...)) + { + foreach($columnName as $name) + { + if(!isset($table->columns[$name])) + throw new TDbException('Table "{0}" does not have a column named "{1}".', + $table->name, $name); + + for($i=0;$i<$n;++$i) + { + if(isset($values[$i][$name])) + { + $value=$table->columns[$name]->typecast($values[$i][$name]); + if(is_string($value)) + $values[$i][$name]=$db->quoteValue($value); + else + $values[$i][$name]=$value; + } + else + throw new TDbException('The value for the column "{1}" is not supplied when querying the table "{0}".', + $table->name,$name); + } + } + if(count($values)===1) + { + $entries=array(); + foreach($values[0] as $name=>$value) + $entries[]=$prefix.$table->columns[$name]->rawName.($value===null?' IS NULL':'='.$value); + return implode(' AND ',$entries); + } + + return $this->createCompositeInCondition($table,$values,$prefix); + } + else + throw new TDbException('Column name must be either a string or an array.'); + } + + /** + * Generates the expression for selecting rows with specified composite key values. + * @param TDbTableSchema the table schema + * @param array list of primary key values to be selected within + * @param string column prefix (ended with dot) + * @return string the expression for selection + * @since 1.0.4 + */ + protected function createCompositeInCondition($table,$values,$prefix) + { + $keyNames=array(); + foreach(array_keys($values[0]) as $name) + $keyNames[]=$prefix.$table->columns[$name]->rawName; + $vs=array(); + foreach($values as $value) + $vs[]='('.implode(', ',$value).')'; + return '('.implode(', ',$keyNames).') IN ('.implode(', ',$vs).')'; + } + + /** + * Checks if the parameter is a valid table schema. + * If it is a string, the corresponding table schema will be retrieved. + * @param mixed table schema ({@link TDbTableSchema}) or table name (string). + * If this refers to a valid table name, this parameter will be returned with the corresponding table schema. + * @throws TDbException if the table name is not valid + * @since 1.0.4 + */ + protected function ensureTable(&$table) + { + if(is_string($table) && ($table=$this->_schema->getTable($tableName=$table))===null) + throw new TDbException('Table "{0}" does not exist.', + $tableName); + } +} diff --git a/framework/Db/schema/TDbCriteria.php b/framework/Db/schema/TDbCriteria.php new file mode 100755 index 00000000..f6bbe7c3 --- /dev/null +++ b/framework/Db/schema/TDbCriteria.php @@ -0,0 +1,166 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * TDbCriteria represents a query criteria, such as conditions, ordering by, limit/offset. + * + * @author Qiang Xue + * @version $Id$ + * @package system.db.schema + * @since 1.0 + */ +class TDbCriteria +{ + /** + * @var mixed the columns being selected. This refers to the SELECT clause in an SQL + * statement. The property can be either a string (column names separated by commas) + * or an array of column names. Defaults to '*', meaning all columns. + */ + public $select='*'; + /** + * @var string query condition. This refers to the WHERE clause in an SQL statement. + * For example, age>31 AND team=1. + */ + public $condition=''; + /** + * @var array list of query parameter values indexed by parameter placeholders. + * For example, array(':name'=>'Dan', ':age'=>31). + */ + public $params=array(); + /** + * @var integer maximum number of records to be returned. If less than 0, it means no limit. + */ + public $limit=-1; + /** + * @var integer zero-based offset from where the records are to be returned. If less than 0, it means starting from the beginning. + */ + public $offset=-1; + /** + * @var string how to sort the query results. This refers to the ORDER BY clause in an SQL statement. + */ + public $order=''; + /** + * @var string how to group the query results. This refers to the GROUP BY clause in an SQL statement. + * For example, 'projectID, teamID'. + */ + public $group=''; + /** + * @var string how to join with other tables. This refers to the JOIN clause in an SQL statement. + * For example, 'LEFT JOIN users ON users.id=authorID'. + */ + public $join=''; + /** + * @var string the condition to be applied with GROUP-BY clause. + * For example, 'SUM(revenue)<50000'. + * @since 1.0.1 + */ + public $having=''; + + /** + * Constructor. + * @param array criteria initial property values (indexed by property name) + */ + public function __construct($data=array()) + { + foreach($data as $name=>$value) + $this->$name=$value; + } + + /** + * Merges with another criteria. + * In general, the merging makes the resulting criteria more restrictive. + * For example, if both criterias have conditions, they will be 'AND' together. + * Also, the criteria passed as the parameter takes precedence in case + * two options cannot be merged (e.g. LIMIT, OFFSET). + * @param TDbCriteria the criteria to be merged with. + * @param boolean whether to use 'AND' to merge condition and having options. + * If false, 'OR' will be used instead. Defaults to 'AND'. This parameter has been + * available since version 1.0.6. + * @since 1.0.5 + */ + public function mergeWith($criteria,$useAnd=true) + { + $and=$useAnd ? 'AND' : 'OR'; + if(is_array($criteria)) + $criteria=new self($criteria); + if($this->select!==$criteria->select) + { + if($this->select==='*') + $this->select=$criteria->select; + else if($criteria->select!=='*') + { + $select1=is_string($this->select)?preg_split('/\s*,\s*/',trim($this->select),-1,PREG_SPLIT_NO_EMPTY):$this->select; + $select2=is_string($criteria->select)?preg_split('/\s*,\s*/',trim($criteria->select),-1,PREG_SPLIT_NO_EMPTY):$criteria->select; + $this->select=array_merge($select1,array_diff($select2,$select1)); + } + } + + if($this->condition!==$criteria->condition) + { + if($this->condition==='') + $this->condition=$criteria->condition; + else if($criteria->condition!=='') + $this->condition="({$this->condition}) $and ({$criteria->condition})"; + } + + if($this->params!==$criteria->params) + $this->params=array_merge($this->params,$criteria->params); + + if($criteria->limit>0) + $this->limit=$criteria->limit; + + if($criteria->offset>=0) + $this->offset=$criteria->offset; + + if($this->order!==$criteria->order) + { + if($this->order==='') + $this->order=$criteria->order; + else if($criteria->order!=='') + $this->order.=', '.$criteria->order; + } + + if($this->group!==$criteria->group) + { + if($this->group==='') + $this->group=$criteria->group; + else if($criteria->group!=='') + $this->group.=', '.$criteria->group; + } + + if($this->join!==$criteria->join) + { + if($this->join==='') + $this->join=$criteria->join; + else if($criteria->join!=='') + $this->join.=' '.$criteria->join; + } + + if($this->having!==$criteria->having) + { + if($this->having==='') + $this->having=$criteria->having; + else if($criteria->having!=='') + $this->having="({$this->having}) $and ({$criteria->having})"; + } + } + + /** + * @return array the array representation of the criteria + * @since 1.0.6 + */ + public function toArray() + { + $result=array(); + foreach(array('select', 'condition', 'params', 'limit', 'offset', 'order', 'group', 'join', 'having') as $name) + $result[$name]=$this->$name; + return $result; + } +} diff --git a/framework/Db/schema/TDbExpression.php b/framework/Db/schema/TDbExpression.php new file mode 100755 index 00000000..58ffb498 --- /dev/null +++ b/framework/Db/schema/TDbExpression.php @@ -0,0 +1,49 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * TDbExpression represents a DB expression that does not need escaping. + * TDbExpression is mainly used in {@link CActiveRecord} as attribute values. + * When inserting or updating a {@link CActiveRecord}, attribute values of + * type TDbExpression will be directly put into the corresponding SQL statement + * without escaping. A typical usage is that an attribute is set with 'NOW()' + * expression so that saving the record would fill the corresponding column + * with the current DB server timestamp. + * + * @author Qiang Xue + * @version $Id$ + * @package system.db.schema + * @since 1.0.2 + */ +class TDbExpression extends TComponent +{ + /** + * @var string the DB expression + */ + public $expression; + + /** + * Constructor. + * @param string the DB expression + */ + public function __construct($expression) + { + $this->expression=$expression; + } + + /** + * String magic method + * @return string the DB expression + */ + public function __toString() + { + return $this->expression; + } +} \ No newline at end of file diff --git a/framework/Db/schema/TDbSchema.php b/framework/Db/schema/TDbSchema.php new file mode 100755 index 00000000..f7e889ec --- /dev/null +++ b/framework/Db/schema/TDbSchema.php @@ -0,0 +1,193 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.db.schema.TDbCommandBuilder'); + +/** + * TDbSchema is the base class for retrieving metadata information. + * + * @author Qiang Xue + * @version $Id$ + * @package system.db.schema + * @since 1.0 + */ +abstract class TDbSchema extends TComponent +{ + private $_tableNames=array(); + private $_tables=array(); + private $_connection; + private $_builder; + private $_cacheExclude=array(); + + /** + * Creates a table instance representing the metadata for the named table. + * @return TDbTableSchema driver dependent table metadata, null if the table does not exist. + */ + abstract protected function createTable($name); + + /** + * Constructor. + * @param TDbConnection database connection. + */ + public function __construct($conn) + { + $conn->setActive(true); + $this->_connection=$conn; + foreach($conn->schemaCachingExclude as $name) + $this->_cacheExclude[$name]=true; + } + + /** + * @return TDbConnection database connection. The connection is active. + */ + public function getDbConnection() + { + return $this->_connection; + } + + /** + * Obtains the metadata for the named table. + * @param string table name + * @return TDbTableSchema table metadata. Null if the named table does not exist. + */ + public function getTable($name) + { + if(isset($this->_tables[$name])) + return $this->_tables[$name]; + else if(!isset($this->_cacheExclude[$name]) && ($duration=$this->_connection->schemaCachingDuration)>0 && ($cache=prado::getApplication()->getCache())!==null) + { + $key='prado:dbschema'.$this->_connection->connectionString.':'.$this->_connection->username.':'.$name; + if(($table=$cache->get($key))===false) + { + $table=$this->createTable($name); + $cache->set($key,$table,$duration); + } + return $this->_tables[$name]=$table; + } + else + return $this->_tables[$name]=$this->createTable($name); + } + + /** + * Returns the metadata for all tables in the database. + * @param string the schema of the tables. Defaults to empty string, meaning the current or default schema. + * @return array the metadata for all tables in the database. + * Each array element is an instance of {@link TDbTableSchema} (or its child class). + * The array keys are table names. + * @since 1.0.2 + */ + public function getTables($schema='') + { + $tables=array(); + foreach($this->getTableNames($schema) as $name) + $tables[$name]=$this->getTable($name); + return $tables; + } + + /** + * Returns all table names in the database. + * @param string the schema of the tables. Defaults to empty string, meaning the current or default schema. + * If not empty, the returned table names will be prefixed with the schema name. + * @return array all table names in the database. + * @since 1.0.2 + */ + public function getTableNames($schema='') + { + if(!isset($this->_tableNames[$schema])) + $this->_tableNames[$schema]=$this->findTableNames($schema); + return $this->_tableNames[$schema]; + } + + /** + * @return TDbCommandBuilder the SQL command builder for this connection. + */ + public function getCommandBuilder() + { + if($this->_builder!==null) + return $this->_builder; + else + return $this->_builder=$this->createCommandBuilder(); + } + + /** + * Refreshes the schema. + * This method resets the loaded table metadata and command builder + * so that they can be recreated to reflect the change of schema. + */ + public function refresh() + { + $this->_tables=array(); + $this->_builder=null; + } + + /** + * Quotes a table name for use in a query. + * @param string table name + * @return string the properly quoted table name + */ + public function quoteTableName($name) + { + return "'".$name."'"; + } + + /** + * Quotes a column name for use in a query. + * @param string column name + * @return string the properly quoted column name + */ + public function quoteColumnName($name) + { + return '"'.$name.'"'; + } + + /** + * Compares two table names. + * The table names can be either quoted or unquoted. This method + * will consider both cases. + * @param string table name 1 + * @param string table name 2 + * @return boolean whether the two table names refer to the same table. + */ + public function compareTableNames($name1,$name2) + { + $name1=str_replace(array('"','`',"'"),'',$name1); + $name2=str_replace(array('"','`',"'"),'',$name2); + if(($pos=strrpos($name1,'.'))!==false) + $name1=substr($name1,$pos+1); + if(($pos=strrpos($name2,'.'))!==false) + $name2=substr($name2,$pos+1); + return $name1===$name2; + } + + /** + * Creates a command builder for the database. + * This method may be overridden by child classes to create a DBMS-specific command builder. + * @return TDbCommandBuilder command builder instance + */ + protected function createCommandBuilder() + { + return new TDbCommandBuilder($this); + } + + /** + * Returns all table names in the database. + * This method should be overridden by child classes in order to support this feature + * because the default implemenation simply throws an exception. + * @param string the schema of the tables. Defaults to empty string, meaning the current or default schema. + * If not empty, the returned table names will be prefixed with the schema name. + * @return array all table names in the database. + * @since 1.0.2 + */ + protected function findTableNames($schema='') + { + throw new TDbException('{0} does not support fetching all table names.', + get_class($this)); + } +} diff --git a/framework/Db/schema/TDbTableSchema.php b/framework/Db/schema/TDbTableSchema.php new file mode 100755 index 00000000..338ba96b --- /dev/null +++ b/framework/Db/schema/TDbTableSchema.php @@ -0,0 +1,76 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * TDbTableSchema is the base class for representing the metadata of a database table. + * + * It may be extended by different DBMS driver to provide DBMS-specific table metadata. + * + * TDbTableSchema provides the following information about a table: + *
    + *
  • {@link name}
    • + *
  • {@link rawName}
    • + *
  • {@link columns}
    • + *
  • {@link primaryKey}
    • + *
  • {@link foreignKeys}
    • + *
  • {@link sequenceName}
    • + *
+ * + * @author Qiang Xue + * @version $Id$ + * @package system.db.schema + * @since 1.0 + */ +class TDbTableSchema extends TComponent +{ + /** + * @var string name of this table. + */ + public $name; + /** + * @var string raw name of this table. This is the quoted version of table name with optional schema name. It can be directly used in SQLs. + */ + public $rawName; + /** + * @var string|array primary key name of this table. If composite key, an array of key names is returned. + */ + public $primaryKey; + /** + * @var string sequence name for the primary key. Null if no sequence. + */ + public $sequenceName; + /** + * @var array foreign keys of this table. The array is indexed by column name. Each value is an array of foreign table name and foreign column name. + */ + public $foreignKeys=array(); + /** + * @var array column metadata of this table. Each array element is a TDbColumnSchema object, indexed by column names. + */ + public $columns=array(); + + /** + * Gets the named column metadata. + * This is a convenient method for retrieving a named column even if it does not exist. + * @param string column name + * @return TDbColumnSchema metadata of the named column. Null if the named column does not exist. + */ + public function getColumn($name) + { + return isset($this->columns[$name]) ? $this->columns[$name] : null; + } + + /** + * @return array list of column names + */ + public function getColumnNames() + { + return array_keys($this->columns); + } +} diff --git a/framework/Db/schema/mssql/TMssqlColumnSchema.php b/framework/Db/schema/mssql/TMssqlColumnSchema.php new file mode 100755 index 00000000..32498e85 --- /dev/null +++ b/framework/Db/schema/mssql/TMssqlColumnSchema.php @@ -0,0 +1,57 @@ + + * @author Christophe Boulain + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.db.schema.TDbColumnSchema'); + +/** + * TMssqlColumnSchema class describes the column meta data of a MSSQL table. + * + * @author Qiang Xue + * @author Christophe Boulain + * @version $Id$ + * @package system.db.schema.mssql + * @since 1.0.4 + */ +class TMssqlColumnSchema extends TDbColumnSchema +{ + /** + * Extracts the PHP type from DB type. + * @param string DB type + */ + protected function extractType($dbType) + { + if(strpos($dbType,'bigint')!==false || strpos($dbType,'float')!==false || strpos($dbType,'real')!==false) + $this->type='double'; + else if(strpos($dbType,'int')!==false || strpos($dbType,'smallint')!==false || strpos($dbType,'tinyint')) + $this->type='integer'; + else if(strpos($dbType,'bit')!==false) + $this->type='boolean'; + else + $this->type='string'; + } + + protected function extractDefault($defaultValue) + { + if($this->dbType==='timestamp' ) + $this->defaultValue=null; + else + parent::extractDefault(str_replace(array('(',')',"'"), '', $defaultValue)); + } + + /** + * Extracts size, precision and scale information from column's DB type. + * We do nothing here, since sizes and precisions have been computed before. + * @param string the column's DB type + */ + protected function extractLimit($dbType) + { + } +} diff --git a/framework/Db/schema/mssql/TMssqlCommandBuilder.php b/framework/Db/schema/mssql/TMssqlCommandBuilder.php new file mode 100755 index 00000000..f3c68436 --- /dev/null +++ b/framework/Db/schema/mssql/TMssqlCommandBuilder.php @@ -0,0 +1,303 @@ + + * @author Christophe Boulain + * @author Wei Zhuo + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using ('System.db.schame.TDbCommandBuilder'); + +/** + * TMssqlCommandBuilder provides basic methods to create query commands for tables for Mssql Servers. + * + * @author Qiang Xue + * @author Christophe Boulain + * @author Wei Zhuo + * @version $Id$ + * @package system.db.schema.mssql + * @since 1.0.4 + */ +class TMssqlCommandBuilder extends TDbCommandBuilder +{ + /** + * Returns the last insertion ID for the specified table. + * Override parent implemantation since PDO mssql driver does not provide this method + * @param TDbTableSchema the table metadata + * @return mixed last insertion id. Null is returned if no sequence name. + */ + public function getLastInsertID($table) + { + if($table->sequenceName!==null) + return $this->getDbConnection()->createCommand('SELECT SCOPE_IDENTITY()')->queryScalar(); + else + return null; + } + + /** + * Creates a COUNT(*) command for a single table. + * Override parent implementation to remove the order clause of criteria if it exists + * @param TDbTableSchema the table metadata + * @param TDbCriteria the query criteria + * @return TDbCommand query command. + */ + public function createCountCommand($table,$criteria) + { + $criteria->order=''; + return parent::createCountCommand($table, $criteria); + } + + /** + * Creates a SELECT command for a single table. + * Override parent implementation to check if an orderby clause if specified when querying with an offset + * @param TDbTableSchema the table metadata + * @param TDbCriteria the query criteria + * @return TDbCommand query command. + */ + public function createFindCommand($table,$criteria) + { + $criteria=$this->checkCriteria($table,$criteria); + return parent::createFindCommand($table,$criteria); + + } + + /** + * Creates an UPDATE command. + * Override parent implementation because mssql don't want to update an identity column + * @param TDbTableSchema the table metadata + * @param array list of columns to be updated (name=>value) + * @param TDbCriteria the query criteria + * @return TDbCommand update command. + */ + public function createUpdateCommand($table,$data,$criteria) + { + $criteria=$this->checkCriteria($table,$criteria); + $fields=array(); + $values=array(); + $bindByPosition=isset($criteria->params[0]); + foreach($data as $name=>$value) + { + if(($column=$table->getColumn($name))!==null) + { + if ($table->sequenceName !== null && $column->isPrimaryKey === true) continue; + if($value instanceof TDbExpression) + $fields[]=$column->rawName.'='.(string)$value; + else if($bindByPosition) + { + $fields[]=$column->rawName.'=?'; + $values[]=$column->typecast($value); + } + else + { + $fields[]=$column->rawName.'=:'.$name; + $values[':'.$name]=$column->typecast($value); + } + } + } + if($fields===array()) + throw new TDbException('No columns are being updated for table "{0}".', + $table->name); + $sql="UPDATE {$table->rawName} SET ".implode(', ',$fields); + $sql=$this->applyJoin($sql,$criteria->join); + $sql=$this->applyCondition($sql,$criteria->condition); + $sql=$this->applyOrder($sql,$criteria->order); + $sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset); + + $command=$this->getDbConnection()->createCommand($sql); + $this->bindValues($command,array_merge($values,$criteria->params)); + + return $command; + } + + /** + * Creates a DELETE command. + * Override parent implementation to check if an orderby clause if specified when querying with an offset + * @param TDbTableSchema the table metadata + * @param TDbCriteria the query criteria + * @return TDbCommand delete command. + */ + public function createDeleteCommand($table,$criteria) + { + $criteria=$this->checkCriteria($table, $criteria); + return parent::createDeleteCommand($table, $criteria); + } + + /** + * Creates an UPDATE command that increments/decrements certain columns. + * Override parent implementation to check if an orderby clause if specified when querying with an offset + * @param TDbTableSchema the table metadata + * @param TDbCriteria the query criteria + * @param array counters to be updated (counter increments/decrements indexed by column names.) + * @return TDbCommand the created command + * @throws CException if no counter is specified + */ + public function createUpdateCounterCommand($table,$counters,$criteria) + { + $criteria=$this->checkCriteria($table, $criteria); + return parent::createUpdateCounterCommand($table, $counters, $criteria); + } + + /** + * This is a port from Prado Framework. + * + * Overrides parent implementation. Alters the sql to apply $limit and $offset. + * The idea for limit with offset is done by modifying the sql on the fly + * with numerous assumptions on the structure of the sql string. + * The modification is done with reference to the notes from + * http://troels.arvin.dk/db/rdbms/#select-limit-offset + * + * + * SELECT * FROM ( + * SELECT TOP n * FROM ( + * SELECT TOP z columns -- (z=n+skip) + * FROM tablename + * ORDER BY key ASC + * ) AS FOO ORDER BY key DESC -- ('FOO' may be anything) + * ) AS BAR ORDER BY key ASC -- ('BAR' may be anything) + * + * + * Regular expressions are used to alter the SQL query. The resulting SQL query + * may be malformed for complex queries. The following restrictions apply + * + *
    + *
  • + * In particular, commas should NOT + * be used as part of the ordering expression or identifier. Commas must only be + * used for separating the ordering clauses. + *
    • + *
  • + * In the ORDER BY clause, the column name should NOT be be qualified + * with a table name or view name. Alias the column names or use column index. + *
    • + *
  • + * No clauses should follow the ORDER BY clause, e.g. no COMPUTE or FOR clauses. + *
    • + * + * @param string SQL query string. + * @param integer maximum number of rows, -1 to ignore limit. + * @param integer row offset, -1 to ignore offset. + * @return string SQL with limit and offset. + * + * @author Wei Zhuo + */ + public function applyLimit($sql, $limit, $offset) + { + $limit = $limit!==null ? intval($limit) : -1; + $offset = $offset!==null ? intval($offset) : -1; + if ($limit > 0 && $offset <= 0) //just limit + $sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $limit", $sql); + else if($limit > 0 && $offset > 0) + $sql = $this->rewriteLimitOffsetSql($sql, $limit,$offset); + return $sql; + } + + /** + * Rewrite sql to apply $limit > and $offset > 0 for MSSQL database. + * See http://troels.arvin.dk/db/rdbms/#select-limit-offset + * @param string sql query + * @param integer $limit > 0 + * @param integer $offset > 0 + * @return sql modified sql query applied with limit and offset. + * + * @author Wei Zhuo + */ + protected function rewriteLimitOffsetSql($sql, $limit, $offset) + { + $fetch = $limit+$offset; + $sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $fetch", $sql); + $ordering = $this->findOrdering($sql); + + $orginalOrdering = $this->joinOrdering($ordering); + $reverseOrdering = $this->joinOrdering($this->reverseDirection($ordering)); + $sql = "SELECT * FROM (SELECT TOP {$limit} * FROM ($sql) as [__inner top table__] {$reverseOrdering}) as [__outer top table__] {$orginalOrdering}"; + return $sql; + } + + /** + * Base on simplified syntax http://msdn2.microsoft.com/en-us/library/aa259187(SQL.80).aspx + * + * @param string $sql + * @return array ordering expression as key and ordering direction as value + * + * @author Wei Zhuo + */ + protected function findOrdering($sql) + { + if(!preg_match('/ORDER BY/i', $sql)) + return array(); + $matches=array(); + $ordering=array(); + preg_match_all('/(ORDER BY)[\s"\[](.*)(ASC|DESC)?(?:[\s"\[]|$|COMPUTE|FOR)/i', $sql, $matches); + if(count($matches)>1 && count($matches[2]) > 0) + { + $parts = explode(',', $matches[2][0]); + foreach($parts as $part) + { + $subs=array(); + if(preg_match_all('/(.*)[\s"\]](ASC|DESC)$/i', trim($part), $subs)) + { + if(count($subs) > 1 && count($subs[2]) > 0) + { + $ordering[$subs[1][0]] = $subs[2][0]; + } + //else what? + } + else + $ordering[trim($part)] = 'ASC'; + } + } + return $ordering; + } + + /** + * @param array ordering obtained from findOrdering() + * @return string concat the orderings + * + * @author Wei Zhuo + */ + protected function joinOrdering($orders) + { + if(count($orders)>0) + { + $str=array(); + foreach($orders as $column => $direction) + $str[] = $column.' '.$direction; + return 'ORDER BY '.implode(', ', $str); + } + } + + /** + * @param array original ordering + * @return array ordering with reversed direction. + * + * @author Wei Zhuo + */ + protected function reverseDirection($orders) + { + foreach($orders as $column => $direction) + $orders[$column] = strtolower(trim($direction))==='desc' ? 'ASC' : 'DESC'; + return $orders; + } + + + /** + * Checks if the criteria has an order by clause when using offset/limit. + * Override parent implementation to check if an orderby clause if specified when querying with an offset + * If not, order it by pk. + * @param TMssqlTableSchema table schema + * @param TDbCriteria criteria + * @return TDbCrireria the modified criteria + */ + protected function checkCriteria($table, $criteria) + { + if ($criteria->offset > 0 && $criteria->order==='') + { + $criteria->order=is_array($table->primaryKey)?implode(',',$table->primaryKey):$table->primaryKey; + } + return $criteria; + } +} diff --git a/framework/Db/schema/mssql/TMssqlPdoAdapter.php b/framework/Db/schema/mssql/TMssqlPdoAdapter.php new file mode 100755 index 00000000..0f9a4288 --- /dev/null +++ b/framework/Db/schema/mssql/TMssqlPdoAdapter.php @@ -0,0 +1,74 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * This is an extension of default PDO class for mssql driver only + * It provides some missing functionalities of pdo driver + * @author Christophe Boulain + * @version $Id$ + * @package system.db.schema.mssql + * @since 1.0.4 + */ +class TMssqlPdoAdapter extends PDO +{ + /** + * Get the last inserted id value + * MSSQL doesn't support sequence, so, argument is ignored + * + * @param string sequence name. Defaults to null + * @return int last inserted id + */ + public function lastInsertId ($sequence=NULL) + { + return $this->query('SELECT SCOPE_IDENTITY()')->fetchColumn(); + } + + /** + * Begin a transaction + * + * Is is necessary to override pdo's method, as mssql pdo drivers + * does not support transaction + * + * @return boolean + */ + public function beginTransaction () + { + $this->exec('BEGIN TRANSACTION'); + return true; + } + + /** + * Commit a transaction + * + * Is is necessary to override pdo's method, as mssql pdo drivers + * does not support transaction + * + * @return boolean + */ + public function commit () + { + $this->exec('COMMIT TRANSACTION'); + return true; + } + + /** + * Rollback a transaction + * + * Is is necessary to override pdo's method, ac mssql pdo drivers + * does not support transaction + * + * @return boolean + */ + public function rollBack () + { + $this->exec('ROLLBACK TRANSACTION'); + return true; + } +} diff --git a/framework/Db/schema/mssql/TMssqlSchema.php b/framework/Db/schema/mssql/TMssqlSchema.php new file mode 100755 index 00000000..296439a3 --- /dev/null +++ b/framework/Db/schema/mssql/TMssqlSchema.php @@ -0,0 +1,312 @@ + + * @author Christophe Boulain + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.db.schema.TDbSchema'); + +/** + * TMssqlSchema is the class for retrieving metadata information from a MS SQL Server database. + * + * @author Qiang Xue + * @author Christophe Boulain + * @version $Id$ + * @package system.db.schema.mssql + * @since 1.0.4 + */ +class TMssqlSchema extends TDbSchema +{ + const DEFAULT_SCHEMA='dbo'; + + + /** + * Quotes a table name for use in a query. + * @param string table name + * @return string the properly quoted table name + */ + public function quoteTableName($name) + { + if (strpos($name,'.')===false) + return '['.$name.']'; + $names=explode('.',$name); + foreach ($names as &$n) + $n = '['.$n.']'; + return implode('.',$names); + } + + /** + * Quotes a column name for use in a query. + * @param string column name + * @return string the properly quoted column name + */ + public function quoteColumnName($name) + { + return '['.$name.']'; + } + + /** + * Compares two table names. + * The table names can be either quoted or unquoted. This method + * will consider both cases. + * @param string table name 1 + * @param string table name 2 + * @return boolean whether the two table names refer to the same table. + */ + public function compareTableNames($name1,$name2) + { + $name1=str_replace(array('[',']'),'',$name1); + $name1=str_replace(array('[',']'),'',$name2); + return parent::compareTableNames(strtolower($name1),strtolower($name2)); + } + + /** + * Creates a table instance representing the metadata for the named table. + * @return CMysqlTableSchema driver dependent table metadata. Null if the table does not exist. + */ + protected function createTable($name) + { + $table=new TMssqlTableSchema; + $this->resolveTableNames($table,$name); + //if (!in_array($table->name, $this->tableNames)) return null; + $table->primaryKey=$this->findPrimaryKey($table); + $table->foreignKeys=$this->findForeignKeys($table); + if($this->findColumns($table)) + { + return $table; + } + else + return null; + } + + /** + * Generates various kinds of table names. + * @param CMysqlTableSchema the table instance + * @param string the unquoted table name + */ + protected function resolveTableNames($table,$name) + { + $parts=explode('.',str_replace(array('[',']'),'',$name)); + if(($c=count($parts))==3) + { + // Catalog name, schema name and table name provided + $table->catalogName=$parts[0]; + $table->schemaName=$parts[1]; + $table->name=$parts[2]; + $table->rawName=$this->quoteTableName($table->catalogName).'.'.$this->quoteTableName($table->schemaName).'.'.$this->quoteTableName($table->name); + } + elseif ($c==2) + { + // Only schema name and table name provided + $table->name=$parts[1]; + $table->schemaName=$parts[0]; + $table->rawName=$this->quoteTableName($table->schemaName).'.'.$this->quoteTableName($table->name); + } + else + { + // Only the name given, we need to get at least the schema name + //if (empty($this->_schemaNames)) $this->findTableNames(); + $table->name=$parts[0]; + $table->schemaName=self::DEFAULT_SCHEMA; + $table->rawName=$this->quoteTableName($table->schemaName).'.'.$this->quoteTableName($table->name); + } + } + + /** + * Gets the primary key column(s) details for the given table. + * @param TMssqlTableSchema table + * @return mixed primary keys (null if no pk, string if only 1 column pk, or array if composite pk) + */ + protected function findPrimaryKey($table) + { + $kcu='INFORMATION_SCHEMA.KEY_COLUMN_USAGE'; + $tc='INFORMATION_SCHEMA.TABLE_CONSTRAINTS'; + if (isset($table->catalogName)) + { + $kcu=$table->catalogName.'.'.$kcu; + $tc=$table->catalogName.'.'.$tc; + } + + $sql = <<quoteTableName($kcu)} k + LEFT JOIN {$this->quoteTableName($tc)} c + ON k.table_name = c.table_name + AND k.constraint_name = c.constraint_name + WHERE c.constraint_type ='PRIMARY KEY' + AND k.table_name = :table + AND k.table_schema = :schema +EOD; + $command = $this->getDbConnection()->createCommand($sql); + $command->bindValue(':table', $table->name); + $command->bindValue(':schema', $table->schemaName); + $primary=$command->queryColumn(); + switch (count($primary)) + { + case 0: // No primary key on table + $primary=null; + break; + case 1: // Only 1 primary key + $primary=$primary[0]; + break; + } + return $primary; + } + + /** + * Gets foreign relationship constraint keys and table name + * @param TMssqlTableSchema table + * @return array foreign relationship table name and keys. + */ + protected function findForeignKeys($table) + { + $rc='INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS'; + $kcu='INFORMATION_SCHEMA.KEY_COLUMN_USAGE'; + if (isset($table->catalogName)) + { + $kcu=$table->catalogName.'.'.$kcu; + $rc=$table->catalogName.'.'.$rc; + } + + //From http://msdn2.microsoft.com/en-us/library/aa175805(SQL.80).aspx + $sql = <<quoteTableName($rc)} RC + JOIN {$this->quoteTableName($kcu)} KCU1 + ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG + AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA + AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME + JOIN {$this->quoteTableName($kcu)} KCU2 + ON KCU2.CONSTRAINT_CATALOG = + RC.UNIQUE_CONSTRAINT_CATALOG + AND KCU2.CONSTRAINT_SCHEMA = + RC.UNIQUE_CONSTRAINT_SCHEMA + AND KCU2.CONSTRAINT_NAME = + RC.UNIQUE_CONSTRAINT_NAME + AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION + WHERE KCU1.TABLE_NAME = :table +EOD; + $command = $this->getDbConnection()->createCommand($sql); + $command->bindValue(':table', $table->name); + $fkeys=array(); + foreach($command->queryAll() as $info) + { + $fkeys[$info['FK_COLUMN_NAME']]=array($info['UQ_TABLE_NAME'],$info['UQ_COLUMN_NAME'],); + + } + return $fkeys; + } + + + /** + * Collects the table column metadata. + * @param CMysqlTableSchema the table metadata + * @return boolean whether the table exists in the database + */ + protected function findColumns($table) + { + $where=array(); + $where[]="TABLE_NAME='".$table->name."'"; + if (isset($table->catalogName)) + $where[]="TABLE_CATALOG='".$table->catalogName."'"; + if (isset($table->schemaName)) + $where[]="TABLE_SCHEMA='".$table->schemaName."'"; + $sql="SELECT *, columnproperty(object_id(table_schema+'.'+table_name), column_name, 'IsIdentity') as IsIdentity ". + "FROM INFORMATION_SCHEMA.COLUMNS WHERE ".join(' AND ',$where); + if (($columns=$this->getDbConnection()->createCommand($sql)->queryAll())===array()) + return false; + + foreach($columns as $column) + { + $c=$this->createColumn($column); + if (is_array($table->primaryKey)) + $c->isPrimaryKey=in_array($c->name, $table->primaryKey); + else + $c->isPrimaryKey=strcasecmp($c->name,$table->primaryKey)===0; + + $c->isForeignKey=isset($table->foreignKeys[$c->name]); + $table->columns[$c->name]=$c; + if ($column['IsIdentity']==1 && $table->sequenceName===null) + $table->sequenceName=''; + + } + return true; + } + + /** + * Creates a table column. + * @param array column metadata + * @return TDbColumnSchema normalized column metadata + */ + protected function createColumn($column) + { + $c=new TMssqlColumnSchema; + $c->name=$column['COLUMN_NAME']; + $c->rawName=$this->quoteColumnName($c->name); + $c->allowNull=$column['IS_NULLABLE']=='YES'; + if ($column['NUMERIC_PRECISION_RADIX']!==null) + { + // We have a numeric datatype + $c->size=$c->precision=$column['NUMERIC_PRECISION']!==null?(int)$column['NUMERIC_PRECISION']:null; + $c->scale=$column['NUMERIC_SCALE']!==null?(int)$column['NUMERIC_SCALE']:null; + } + elseif ($column['DATA_TYPE']=='image' || $column['DATA_TYPE']=='text') + $c->size=$c->precision=null; + else + $c->size=$c->precision=($column['CHARACTER_MAXIMUM_LENGTH']!== null)?(int)$column['CHARACTER_MAXIMUM_LENGTH']:null; + + $c->init($column['DATA_TYPE'],$column['COLUMN_DEFAULT']); + return $c; + } + + /** + * Returns all table names in the database. + * @return array all table names in the database. + * @since 1.0.4 + */ + protected function findTableNames($schema='') + { + if($schema==='') + $schema=self::DEFAULT_SCHEMA; + $sql=<<getDbConnection()->createCommand($sql); + $command->bindParam(":schema", $schema); + $rows=$command->queryAll(); + $names=array(); + foreach ($rows as $row) + { + if ($schema == self::DEFAULT_SCHEMA) + $names[]=$row['TABLE_NAME']; + else + $names[]=$schema.'.'.$row['TABLE_SCHEMA'].'.'.$row['TABLE_NAME']; + } + + return $names; + } + + /** + * Creates a command builder for the database. + * This method overrides parent implementation in order to create a MSSQL specific command builder + * @return TDbCommandBuilder command builder instance + */ + protected function createCommandBuilder() + { + return new TMssqlCommandBuilder($this); + } +} diff --git a/framework/Db/schema/mssql/TMssqlTableSchema.php b/framework/Db/schema/mssql/TMssqlTableSchema.php new file mode 100755 index 00000000..1be690d9 --- /dev/null +++ b/framework/Db/schema/mssql/TMssqlTableSchema.php @@ -0,0 +1,35 @@ + + * @author Christophe Boulain + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.db.TDbTableSchema'); + +/** + * TMssqlTableSchema represents the metadata for a MSSQL table. + * + * @author Qiang Xue + * @author Christophe Boulain + * @version $Id$ + * @package system.db.schema.mssql + * @since 1.0.4 + */ +class TMssqlTableSchema extends TDbTableSchema +{ + /** + * @var string name of the catalog (database) that this table belongs to. + * Defaults to null, meaning no schema (or the current database). + */ + public $catalogName; + /** + * @var string name of the schema that this table belongs to. + * Defaults to null, meaning no schema (or the current database owner). + */ + public $schemaName; +} diff --git a/framework/Db/schema/mysql/TMysqlColumnSchema.php b/framework/Db/schema/mysql/TMysqlColumnSchema.php new file mode 100755 index 00000000..d956d2c1 --- /dev/null +++ b/framework/Db/schema/mysql/TMysqlColumnSchema.php @@ -0,0 +1,46 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.Db.schema.TDbColumnSchema'); + +/** + * TMysqlColumnSchema class describes the column meta data of a MySQL table. + * + * @author Qiang Xue + * @version $Id$ + * @package system.db.schema.mysql + * @since 1.0 + */ +class TMysqlColumnSchema extends TDbColumnSchema +{ + /** + * Extracts the PHP type from DB type. + * @param string DB type + */ + protected function extractType($dbType) + { + if(strpos($dbType,'bigint')!==false || strpos($dbType,'float')!==false || strpos($dbType,'double')!==false) + $this->type='double'; + else if(strpos($dbType,'bool')!==false || $dbType==='tinyint(1)') + $this->type='boolean'; + else if(strpos($dbType,'int')!==false || strpos($dbType,'bit')!==false) + $this->type='integer'; + else + $this->type='string'; + } + + protected function extractDefault($defaultValue) + { + if($this->dbType==='timestamp' && $defaultValue==='CURRENT_TIMESTAMP') + $this->defaultValue=null; + else + parent::extractDefault($defaultValue); + } +} diff --git a/framework/Db/schema/mysql/TMysqlSchema.php b/framework/Db/schema/mysql/TMysqlSchema.php new file mode 100755 index 00000000..2364bad2 --- /dev/null +++ b/framework/Db/schema/mysql/TMysqlSchema.php @@ -0,0 +1,205 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.db.schema.TDbSchema'); +prado::using('System.db.schema.mysql.TMysqlTableSchema'); +prado::using('System.db.schema.mysql.TMysqlColumnSchema'); + +/** + * TMysqlSchema is the class for retrieving metadata information from a MySQL database (version 4.1.x and 5.x). + * + * @author Qiang Xue + * @version $Id$ + * @package system.db.schema.mysql + * @since 1.0 + */ +class TMysqlSchema extends TDbSchema +{ + private $_tableNames; + private $_schemaNames; + + /** + * Quotes a table name for use in a query. + * @param string table name + * @return string the properly quoted table name + */ + public function quoteTableName($name) + { + return '`'.$name.'`'; + } + + /** + * Quotes a column name for use in a query. + * @param string column name + * @return string the properly quoted column name + */ + public function quoteColumnName($name) + { + return '`'.$name.'`'; + } + + /** + * Compares two table names. + * The table names can be either quoted or unquoted. This method + * will consider both cases. + * @param string table name 1 + * @param string table name 2 + * @return boolean whether the two table names refer to the same table. + */ + public function compareTableNames($name1,$name2) + { + return parent::compareTableNames(strtolower($name1),strtolower($name2)); + } + + /** + * Creates a table instance representing the metadata for the named table. + * @return TMysqlTableSchema driver dependent table metadata. Null if the table does not exist. + */ + protected function createTable($name) + { + $table=new TMysqlTableSchema; + $this->resolveTableNames($table,$name); + + if($this->findColumns($table)) + { + $this->findConstraints($table); + return $table; + } + else + return null; + } + + /** + * Generates various kinds of table names. + * @param TMysqlTableSchema the table instance + * @param string the unquoted table name + */ + protected function resolveTableNames($table,$name) + { + $parts=explode('.',str_replace('`','',$name)); + if(isset($parts[1])) + { + $table->schemaName=$parts[0]; + $table->name=$parts[1]; + $table->rawName=$this->quoteTableName($table->schemaName).'.'.$this->quoteTableName($table->name); + } + else + { + $table->name=$parts[0]; + $table->rawName=$this->quoteTableName($table->name); + } + } + + /** + * Collects the table column metadata. + * @param TMysqlTableSchema the table metadata + * @return boolean whether the table exists in the database + */ + protected function findColumns($table) + { + $sql='SHOW COLUMNS FROM '.$table->rawName; + try + { + $columns=$this->getDbConnection()->createCommand($sql)->queryAll(); + } + catch(Exception $e) + { + return false; + } + foreach($columns as $column) + { + $c=$this->createColumn($column); + $table->columns[$c->name]=$c; + if($c->isPrimaryKey) + { + if($table->primaryKey===null) + $table->primaryKey=$c->name; + else if(is_string($table->primaryKey)) + $table->primaryKey=array($table->primaryKey,$c->name); + else + $table->primaryKey[]=$c->name; + if(strpos(strtolower($column['Extra']),'auto_increment')!==false) + $table->sequenceName=''; + } + } + return true; + } + + /** + * Creates a table column. + * @param array column metadata + * @return TDbColumnSchema normalized column metadata + */ + protected function createColumn($column) + { + $c=new TMysqlColumnSchema; + $c->name=$column['Field']; + $c->rawName=$this->quoteColumnName($c->name); + $c->allowNull=$column['Null']==='YES'; + $c->isPrimaryKey=strpos($column['Key'],'PRI')!==false; + $c->isForeignKey=false; + $c->init($column['Type'],$column['Default']); + return $c; + } + + /** + * @return float server version. + */ + protected function getServerVersion() + { + $version=$this->getDbConnection()->getAttribute(PDO::ATTR_SERVER_VERSION); + $digits=array(); + preg_match('/(\d+)\.(\d+)\.(\d+)/', $version, $digits); + return floatval($digits[1].'.'.$digits[2].$digits[3]); + } + + /** + * Collects the foreign key column details for the given table. + * @param TMysqlTableSchema the table metadata + */ + protected function findConstraints($table) + { + $row=$this->getDbConnection()->createCommand('SHOW CREATE TABLE '.$table->rawName)->queryRow(); + $matches=array(); + $regexp='/FOREIGN KEY\s+\(([^\)]+)\)\s+REFERENCES\s+([^\(^\s]+)\s*\(([^\)]+)\)/mi'; + foreach($row as $sql) + { + if(preg_match_all($regexp,$sql,$matches,PREG_SET_ORDER)) + break; + } + $foreign = array(); + foreach($matches as $match) + { + $keys=array_map('trim',explode(',',str_replace('`','',$match[1]))); + $fks=array_map('trim',explode(',',str_replace('`','',$match[3]))); + foreach($keys as $k=>$name) + { + $table->foreignKeys[$name]=array(str_replace('`','',$match[2]),$fks[$k]); + if(isset($table->columns[$name])) + $table->columns[$name]->isForeignKey=true; + } + } + } + + /** + * Returns all table names in the database. + * @return array all table names in the database. + * @since 1.0.2 + */ + protected function findTableNames($schema='') + { + if($schema==='') + return $this->getDbConnection()->createCommand('SHOW TABLES')->queryColumn(); + $names=$this->getDbConnection()->createCommand('SHOW TABLES FROM '.$this->quoteTableName($schema))->queryColumn(); + foreach($names as &$name) + $name=$schema.'.'.$name; + return $names; + } +} diff --git a/framework/Db/schema/mysql/TMysqlTableSchema.php b/framework/Db/schema/mysql/TMysqlTableSchema.php new file mode 100755 index 00000000..dacb1b43 --- /dev/null +++ b/framework/Db/schema/mysql/TMysqlTableSchema.php @@ -0,0 +1,26 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ +prado::using('System.db.schema.TDbTableSchema'); +/** + * TMysqlTableSchema represents the metadata for a MySQL table. + * + * @author Qiang Xue + * @version $Id$ + * @package system.db.schema.mysql + * @since 1.0 + */ +class TMysqlTableSchema extends TDbTableSchema +{ + /** + * @var string name of the schema (database) that this table belongs to. + * Defaults to null, meaning no schema (or the current database). + */ + public $schemaName; +} diff --git a/framework/Db/schema/oci/TOciColumnSchema.php b/framework/Db/schema/oci/TOciColumnSchema.php new file mode 100755 index 00000000..04730f82 --- /dev/null +++ b/framework/Db/schema/oci/TOciColumnSchema.php @@ -0,0 +1,56 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.Db.schema.TDbColumnSchema'); + +/** + * TOciColumnSchema class describes the column meta data of a Oracle table. + * + * @author Ricardo Grana + * @version $Id: TOciColumnSchema.php + * @package system.db.schema.oci + * @since 1.0.5 + */ +class TOciColumnSchema extends TDbColumnSchema +{ + /** + * Extracts the PHP type from DB type. + * @param string DB type + */ + protected function extractOraType($dbType){ + if(strpos($dbType,'FLOAT')!==false) return 'double'; + + if ((strpos($dbType,'NUMBER')!==false) or + (strpos($dbType,'INTEGER')!==false)) + { + if(strpos($dbType,'(') && preg_match('/\((.*)\)/',$dbType,$matches)) + { + $values=explode(',',$matches[1]); + if(isset($values[1]) and (((int)$values[1]) > 0)) + return 'double'; + else return 'integer'; + } + }else{ + return 'string'; + } + } + protected function extractType($dbType) + { + $this->type=$this->extractOraType($dbType); + } + + protected function extractDefault($defaultValue) + { + if(strpos($dbType,'timestamp')!==false) + $this->defaultValue=null; + else + parent::extractDefault($defaultValue); + } +} diff --git a/framework/Db/schema/oci/TOciCommandBuilder.php b/framework/Db/schema/oci/TOciCommandBuilder.php new file mode 100755 index 00000000..fc8b8dc9 --- /dev/null +++ b/framework/Db/schema/oci/TOciCommandBuilder.php @@ -0,0 +1,122 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.db.schema.TDbCommandBuilder'); + +/** + * TOciCommandBuilder provides basic methods to create query commands for tables. + * + * @author Ricardo Grana + * @version $Id$ + * @package system.db.schema.oci + * @since 1.0.5 + */ +class TOciCommandBuilder extends TDbCommandBuilder +{ + /** + * @var integer the last insertion ID + */ + public $returnID; + + /** + * Returns the last insertion ID for the specified table. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @return mixed last insertion id. Null is returned if no sequence name. + */ + public function getLastInsertID($table) + { + return $this->returnID; + } + + /** + * Alters the SQL to apply LIMIT and OFFSET. + * Default implementation is applicable for PostgreSQL, MySQL and SQLite. + * @param string SQL query string without LIMIT and OFFSET. + * @param integer maximum number of rows, -1 to ignore limit. + * @param integer row offset, -1 to ignore offset. + * @return string SQL with LIMIT and OFFSET + */ + public function applyLimit($sql,$limit,$offset) + { + if (($limit < 0) and ($offset < 0)) return $sql; + + $filters = array(); + if($offset>0){ + $filters[] = 'rowNumId >= '.(int)$offset; + } + + if($limit>=0){ + $filters[]= 'rownum <= '.(int)$limit; + } + + if (count($filters) > 0){ + $filter = implode(' and ', $filters); + $filter= " WHERE ".$filter; + }else{ + $filter = ''; + } + + + $sql = <<column value). If a key is not a valid column name, the corresponding value will be ignored. + * @return TDbCommand insert command + */ + public function createInsertCommand($table,$data) + { + $this->ensureTable($table); + $fields=array(); + $values=array(); + $placeholders=array(); + foreach($data as $name=>$value) + { + if(($column=$table->getColumn($name))!==null && ($value!==null || $column->allowNull)) + { + $fields[]=$column->rawName; + if($value instanceof TDbExpression) + $placeholders[]=(string)$value; + else + { + $placeholders[]=':'.$name; + $values[':'.$name]=$column->typecast($value); + } + } + } + + $sql="INSERT INTO {$table->rawName} (".implode(', ',$fields).') VALUES ('.implode(', ',$placeholders).')'; + + if(is_string($table->primaryKey)) + { + $sql.=" RETURNING ".$table->primaryKey." INTO :RETURN_ID"; + $command=$this->getDbConnection()->createCommand($sql); + $command->bindParam(':RETURN_ID', $this->returnID, PDO::PARAM_INT, 12); + $table->sequenceName='RETURN_ID'; + } + else + $command=$this->getDbConnection()->createCommand($sql); + + foreach($values as $name=>$value) + $command->bindValue($name,$value); + + return $command; + } +} \ No newline at end of file diff --git a/framework/Db/schema/oci/TOciSchema.php b/framework/Db/schema/oci/TOciSchema.php new file mode 100755 index 00000000..5c4dd8e2 --- /dev/null +++ b/framework/Db/schema/oci/TOciSchema.php @@ -0,0 +1,278 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.db.schema.TDbSchema'); + +/** + * TOciSchema is the class for retrieving metadata information from a PostgreSQL database. + * + * @author Ricardo Grana + * @version $Id$ + * @package system.db.schema.oci + * @since 1.0.5 + */ +class TOciSchema extends TDbSchema +{ + private $_defaultSchema = ''; + private $_sequences=array(); + + /** + * Quotes a table name for use in a query. + * @param string table name + * @return string the properly quoted table name + */ + public function quoteTableName($name) + { + return $name; + } + + /** + * Quotes a column name for use in a query. + * @param string column name + * @return string the properly quoted column name + */ + public function quoteColumnName($name) + { + return $name; + } + + /** + * Creates a command builder for the database. + * This method may be overridden by child classes to create a DBMS-specific command builder. + * @return TDbCommandBuilder command builder instance + */ + protected function createCommandBuilder() + { + return new TOciCommandBuilder($this); + } + + /** + * @param string default schema. + */ + public function setDefaultSchema($schema) + { + $this->_defaultSchema=$schema; + } + + /** + * @return string default schema. + */ + public function getDefaultSchema() + { + if (!strlen($this->_defaultSchema)) + { + $this->setDefaultSchema(strtoupper($this->getDbConnection()->username)); + } + + return $this->_defaultSchema; + } + + /** + * @param string table name with optional schema name prefix, uses default schema name prefix is not provided. + * @return array tuple as ($schemaName,$tableName) + */ + protected function getSchemaTableName($table) + { + $table = strtoupper($table); + if(count($parts= explode('.', str_replace('"','',$table))) > 1) + return array($parts[0], $parts[1]); + else + return array($this->getDefaultSchema(),$parts[0]); + } + + /** + * Creates a table instance representing the metadata for the named table. + * @return TDbTableSchema driver dependent table metadata. + */ + protected function createTable($name) + { + $table=new TOciTableSchema; + $this->resolveTableNames($table,$name); + + if(!$this->findColumns($table)) + return null; + $this->findConstraints($table); + + return $table; + } + + /** + * Generates various kinds of table names. + * @param TOciTableSchema the table instance + * @param string the unquoted table name + */ + protected function resolveTableNames($table,$name) + { + $parts=explode('.',str_replace('"','',$name)); + if(isset($parts[1])) + { + $schemaName=$parts[0]; + $tableName=$parts[1]; + } + else + { + $schemaName=$this->getDefaultSchema(); + $tableName=$parts[0]; + } + + $table->name=$tableName; + $table->schemaName=$schemaName; + if($schemaName===$this->getDefaultSchema()) + $table->rawName=$this->quoteTableName($tableName); + else + $table->rawName=$this->quoteTableName($schemaName).'.'.$this->quoteTableName($tableName); + } + + /** + * Collects the table column metadata. + * @param TOciTableSchema the table metadata + * @return boolean whether the table exists in the database + */ + protected function findColumns($table) + { + list($schemaName,$tableName) = $this->getSchemaTableName($table->name); + + $sql=<< 0 then ',' || a.data_scale else '' end + || ')' + when data_type = 'DATE' then '' + else '(' || to_char(a.data_length) || ')' + end as data_type, + a.nullable, a.data_default, + ( SELECT D.constraint_type + FROM ALL_CONS_COLUMNS C + inner join ALL_constraints D on D.OWNER = C.OWNER and D.constraint_name = C.constraint_name + WHERE C.OWNER = B.OWNER + and C.table_name = B.object_name + and C.column_name = A.column_name + and D.constraint_type = 'P') as Key +FROM ALL_TAB_COLUMNS A +inner join ALL_OBJECTS B ON b.owner = a.owner and ltrim(B.OBJECT_NAME) = ltrim(A.TABLE_NAME) +WHERE + a.owner = '{$schemaName}' + and b.object_type = 'TABLE' + and b.object_name = '{$tableName}' +ORDER by a.column_id +EOD; + + $command=$this->getDbConnection()->createCommand($sql); + + if(($columns=$command->queryAll())===array()){ + return false; + } + + foreach($columns as $column) + { + $c=$this->createColumn($column); + + $table->columns[$c->name]=$c; + if($c->isPrimaryKey) + { + if($table->primaryKey===null) + $table->primaryKey=$c->name; + else if(is_string($table->primaryKey)) + $table->primaryKey=array($table->primaryKey,$c->name); + else + $table->primaryKey[]=$c->name; + } + } + return true; + } + + /** + * Creates a table column. + * @param array column metadata + * @return TDbColumnSchema normalized column metadata + */ + protected function createColumn($column) + { + $c=new TOciColumnSchema; + $c->name=$column['COLUMN_NAME']; + $c->rawName=$this->quoteColumnName($c->name); + $c->allowNull=$column['NULLABLE']==='Y'; + $c->isPrimaryKey=strpos($column['KEY'],'P')!==false; + $c->isForeignKey=false; + $c->init($column['DATA_TYPE'],$column['DATA_DEFAULT']); + + return $c; + } + + /** + * Collects the primary and foreign key column details for the given table. + * @param TOciTableSchema the table metadata + */ + protected function findConstraints($table) + { + $sql=<< 'P' + order by d.constraint_name, c.position +EOD; + $command=$this->getDbConnection()->createCommand($sql); + foreach($command->queryAll() as $row) + { + if($row['constraint_type']==='R') // foreign key + { + $name = $row["COLUMN_NAME"]; + $table->foreignKeys[$name]=array($row["TABLE_REF"], array($row["COLUMN_REF"])); + if(isset($table->columns[$name])) + $table->columns[$name]->isForeignKey=true; + } + + } + } + + + /** + * Returns all table names in the database. + * @return array all table names in the database. + */ + protected function findTableNames($schema='') + { + if($schema==='') + { + $sql=<<getDbConnection()->createCommand($sql); + } + else + { + $sql=<<getDbConnection()->createCommand($sql); + $command->bindParam(':schema',$schema); + } + + $rows=$command->queryAll(); + $names=array(); + foreach($rows as $row) + { + if($schema===$this->getDefaultSchema()) + $names[]=$row['table_name']; + else + $names[]=$row['schema_name'].'.'.$row['table_name']; + } + return $names; + } +} diff --git a/framework/Db/schema/oci/TOciTableSchema.php b/framework/Db/schema/oci/TOciTableSchema.php new file mode 100755 index 00000000..3f34292d --- /dev/null +++ b/framework/Db/schema/oci/TOciTableSchema.php @@ -0,0 +1,28 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.db.schema.TDbTableSchame'); + +/** + * TOciTableSchema represents the metadata for a Ora table. + * + * @author Ricardo Grana + * @version $Id$ + * @package system.db.schema.oci + * @since 1.0.5 + */ +class TOciTableSchema extends TDbTableSchema +{ + /** + * @var string name of the schema (database) that this table belongs to. + * Defaults to null, meaning no schema (or the current database). + */ + public $schemaName; +} diff --git a/framework/Db/schema/pgsql/TPgsqlColumnSchema.php b/framework/Db/schema/pgsql/TPgsqlColumnSchema.php new file mode 100755 index 00000000..00c01d94 --- /dev/null +++ b/framework/Db/schema/pgsql/TPgsqlColumnSchema.php @@ -0,0 +1,58 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.db.schema.TDbColumnSchema'); + +/** + * TPgsqlColumnSchema class describes the column meta data of a PostgreSQL table. + * + * @author Qiang Xue + * @version $Id$ + * @package system.db.schema.pgsql + * @since 1.0 + */ +class TPgsqlColumnSchema extends TDbColumnSchema +{ + /** + * Extracts the PHP type from DB type. + * @param string DB type + */ + protected function extractType($dbType) + { + if(strpos($dbType,'integer')!==false || strpos($dbType,'oid')===0) + $this->type='integer'; + else if(strpos($dbType,'bool')!==false) + $this->type='boolean'; + else if(preg_match('/(real|float|double)/',$dbType)) + $this->type='double'; + else + $this->type='string'; + } + + /** + * Extracts the default value for the column. + * The value is typecasted to correct PHP type. + * @param mixed the default value obtained from metadata + */ + protected function extractDefault($defaultValue) + { + if($defaultValue==='true') + $this->defaultValue=true; + else if($defaultValue==='false') + $this->defaultValue=false; + else if(strpos($defaultValue,'nextval')===0) + $this->defaultValue=null; + else if(preg_match('/\'(.*)\'::/',$defaultValue,$matches)) + $this->defaultValue=$this->typecast(str_replace("''","'",$matches[1])); + else if(preg_match('/^-?\d+(\.\d*)?$/',$defaultValue,$matches)) + $this->defaultValue=$this->typecast($defaultValue); + // else is null + } +} diff --git a/framework/Db/schema/pgsql/TPgsqlSchema.php b/framework/Db/schema/pgsql/TPgsqlSchema.php new file mode 100755 index 00000000..a539dce2 --- /dev/null +++ b/framework/Db/schema/pgsql/TPgsqlSchema.php @@ -0,0 +1,284 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.db.schema.TDbSchema'); + +/** + * TPgsqlSchema is the class for retrieving metadata information from a PostgreSQL database. + * + * @author Qiang Xue + * @version $Id$ + * @package system.db.schema.pgsql + * @since 1.0 + */ +class TPgsqlSchema extends TDbSchema +{ + const DEFAULT_SCHEMA='public'; + private $_sequences=array(); + + /** + * Quotes a table name for use in a query. + * @param string table name + * @return string the properly quoted table name + */ + public function quoteTableName($name) + { + return '"'.$name.'"'; + } + + /** + * Creates a table instance representing the metadata for the named table. + * @return TDbTableSchema driver dependent table metadata. + */ + protected function createTable($name) + { + $table=new TPgsqlTableSchema; + $this->resolveTableNames($table,$name); + if(!$this->findColumns($table)) + return null; + $this->findConstraints($table); + + if(is_string($table->primaryKey) && isset($this->_sequences[$table->primaryKey])) + $table->sequenceName=$this->_sequences[$table->primaryKey]; + + return $table; + } + + /** + * Generates various kinds of table names. + * @param TPgsqlTableSchema the table instance + * @param string the unquoted table name + */ + protected function resolveTableNames($table,$name) + { + $parts=explode('.',str_replace('"','',$name)); + if(isset($parts[1])) + { + $schemaName=$parts[0]; + $tableName=$parts[1]; + } + else + { + $schemaName=self::DEFAULT_SCHEMA; + $tableName=$parts[0]; + } + + $table->name=$tableName; + $table->schemaName=$schemaName; + if($schemaName===self::DEFAULT_SCHEMA) + $table->rawName=$this->quoteTableName($tableName); + else + $table->rawName=$this->quoteTableName($schemaName).'.'.$this->quoteTableName($tableName); + } + + /** + * Collects the table column metadata. + * @param TPgsqlTableSchema the table metadata + * @return boolean whether the table exists in the database + */ + protected function findColumns($table) + { + $sql=<< 0 AND NOT a.attisdropped + AND a.attrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname=:table + AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = :schema)) +ORDER BY a.attnum +EOD; + $command=$this->getDbConnection()->createCommand($sql); + $command->bindValue(':table',$table->name); + $command->bindValue(':schema',$table->schemaName); + + if(($columns=$command->queryAll())===array()) + return false; + + foreach($columns as $column) + { + $c=$this->createColumn($column); + $table->columns[$c->name]=$c; + + if(stripos($column['adsrc'],'nextval')===0 && preg_match('/nextval\([^\']*\'([^\']+)\'[^\)]*\)/i',$column['adsrc'],$matches)) + { + if(strpos($matches[1],'.')!==false || $table->schemaName===self::DEFAULT_SCHEMA) + $this->_sequences[$c->name]=$matches[1]; + else + $this->_sequences[$c->name]=$table->schemaName.'.'.$matches[1]; + } + } + return true; + } + + /** + * Creates a table column. + * @param array column metadata + * @return TDbColumnSchema normalized column metadata + */ + protected function createColumn($column) + { + $c=new TPgsqlColumnSchema; + $c->name=$column['attname']; + $c->rawName=$this->quoteColumnName($c->name); + $c->allowNull=!$column['attnotnull']; + $c->isPrimaryKey=false; + $c->isForeignKey=false; + + $c->init($column['type'],$column['atthasdef'] ? $column['adsrc'] : null); + + return $c; + } + + /** + * Collects the primary and foreign key column details for the given table. + * @param TPgsqlTableSchema the table metadata + */ + protected function findConstraints($table) + { + $sql=<<getDbConnection()->createCommand($sql); + $command->bindValue(':table',$table->name); + $command->bindValue(':schema',$table->schemaName); + foreach($command->queryAll() as $row) + { + if($row['contype']==='p') // primary key + $this->findPrimaryKey($table,$row['indkey']); + else if($row['contype']==='f') // foreign key + $this->findForeignKey($table,$row['consrc']); + } + } + + /** + * Collects primary key information. + * @param TPgsqlTableSchema the table metadata + * @param string pgsql primary key index list + */ + protected function findPrimaryKey($table,$indices) + { + $indices=implode(', ',preg_split('/\s+/',$indices)); + $sql=<<getDbConnection()->createCommand($sql); + $command->bindValue(':table',$table->name); + $command->bindValue(':schema',$table->schemaName); + foreach($command->queryAll() as $row) + { + $name=$row['attname']; + if(isset($table->columns[$name])) + { + $table->columns[$name]->isPrimaryKey=true; + if($table->primaryKey===null) + $table->primaryKey=$name; + else if(is_string($table->primaryKey)) + $table->primaryKey=array($table->primaryKey,$name); + else + $table->primaryKey[]=$name; + } + } + } + + /** + * Collects foreign key information. + * @param TPgsqlTableSchema the table metadata + * @param string pgsql foreign key definition + */ + protected function findForeignKey($table,$src) + { + $matches=array(); + $brackets='\(([^\)]+)\)'; + $pattern="/FOREIGN\s+KEY\s+{$brackets}\s+REFERENCES\s+([^\(]+){$brackets}/i"; + if(preg_match($pattern,str_replace('"','',$src),$matches)) + { + $keys=preg_split('/,\s+/', $matches[1]); + $tableName=$matches[2]; + $fkeys=preg_split('/,\s+/', $matches[3]); + foreach($keys as $i=>$key) + { + $table->foreignKeys[$key]=array($tableName,$fkeys[$i]); + if(isset($table->columns[$key])) + $table->columns[$key]->isForeignKey=true; + } + } + } + + /** + * Returns all table names in the database. + * @return array all table names in the database. + * @since 1.0.2 + */ + protected function findTableNames($schema='') + { + if($schema==='') + $schema=self::DEFAULT_SCHEMA; + $sql=<<getDbConnection()->createCommand($sql); + $command->bindParam(':schema',$schema); + $rows=$command->queryAll(); + $names=array(); + foreach($rows as $row) + { + if($schema===self::DEFAULT_SCHEMA) + $names[]=$row['table_name']; + else + $names[]=$row['schema_name'].'.'.$row['table_name']; + } + return $names; + } +} diff --git a/framework/Db/schema/pgsql/TPgsqlTableSchema.php b/framework/Db/schema/pgsql/TPgsqlTableSchema.php new file mode 100755 index 00000000..d63ccc04 --- /dev/null +++ b/framework/Db/schema/pgsql/TPgsqlTableSchema.php @@ -0,0 +1,27 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.db.schema.TDbTableSchema'); + +/** + * TPgsqlTable represents the metadata for a PostgreSQL table. + * + * @author Qiang Xue + * @version $Id$ + * @package system.db.schema.pgsql + * @since 1.0 + */ +class TPgsqlTableSchema extends TDbTableSchema +{ + /** + * @var string name of the schema that this table belongs to. + */ + public $schemaName; +} diff --git a/framework/Db/schema/sqlite/TSqliteColumnSchema.php b/framework/Db/schema/sqlite/TSqliteColumnSchema.php new file mode 100755 index 00000000..a6194fcd --- /dev/null +++ b/framework/Db/schema/sqlite/TSqliteColumnSchema.php @@ -0,0 +1,35 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.db.schema.TDbColumnSchema'); + +/** + * TSqliteColumnSchema class describes the column meta data of a SQLite table. + * + * @author Qiang Xue + * @version $Id$ + * @package system.db.schema.sqlite + * @since 1.0 + */ +class TSqliteColumnSchema extends TDbColumnSchema +{ + /** + * Extracts the default value for the column. + * The value is typecasted to correct PHP type. + * @param mixed the default value obtained from metadata + */ + protected function extractDefault($defaultValue) + { + if($this->type==='string') // PHP 5.2.6 adds single quotes while 5.2.0 doesn't + $this->defaultValue=trim($defaultValue,"'\""); + else + $this->defaultValue=$this->typecast($defaultValue); + } +} diff --git a/framework/Db/schema/sqlite/TSqliteCommandBuilder.php b/framework/Db/schema/sqlite/TSqliteCommandBuilder.php new file mode 100755 index 00000000..ed73160a --- /dev/null +++ b/framework/Db/schema/sqlite/TSqliteCommandBuilder.php @@ -0,0 +1,43 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.db.schema.TDbCommandBuilder'); + +/** + * TSqliteCommandBuilder provides basic methods to create query commands for SQLite tables. + * + * @author Qiang Xue + * @version $Id$ + * @package system.db.schema.sqlite + * @since 1.0 + */ +class TSqliteCommandBuilder extends TDbCommandBuilder +{ + /** + * Generates the expression for selecting rows with specified composite key values. + * This method is overridden because SQLite does not support the default + * IN expression with composite columns. + * @param TDbTableSchema the table schema + * @param array list of primary key values to be selected within + * @param string column prefix (ended with dot) + * @return string the expression for selection + * @since 1.0.4 + */ + protected function createCompositeInCondition($table,$values,$prefix) + { + $keyNames=array(); + foreach(array_keys($values[0]) as $name) + $keyNames[]=$prefix.$table->columns[$name]->rawName; + $vs=array(); + foreach($values as $value) + $vs[]=implode("||','||",$value); + return implode("||','||",$keyNames).' IN ('.implode(', ',$vs).')'; + } +} diff --git a/framework/Db/schema/sqlite/TSqliteSchema.php b/framework/Db/schema/sqlite/TSqliteSchema.php new file mode 100755 index 00000000..1c6ba78f --- /dev/null +++ b/framework/Db/schema/sqlite/TSqliteSchema.php @@ -0,0 +1,134 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.db.schema.TDbSchema'); +prado::using('System.db.schema.TDbTableSchema'); +prado::using('System.db.schema.sqlite.TSqliteColumnSchema'); +prado::using('System.db.schema.sqlite.TSqliteCommandBuilder'); + +/** + * TSqliteSchema is the class for retrieving metadata information from a SQLite (2/3) database. + * + * @author Qiang Xue + * @version $Id$ + * @package system.db.schema.sqlite + * @since 1.0 + */ +class TSqliteSchema extends TDbSchema +{ + /** + * Returns all table names in the database. + * @param string the schema of the tables. This is not used for sqlite database. + * @return array all table names in the database. + * @since 1.0.2 + */ + protected function findTableNames($schema='') + { + $sql="SELECT DISTINCT tbl_name FROM sqlite_master WHERE tbl_name<>'sqlite_sequence'"; + return $this->getDbConnection()->createCommand($sql)->queryColumn(); + } + + /** + * Creates a command builder for the database. + * @return TSqliteCommandBuilder command builder instance + */ + protected function createCommandBuilder() + { + return new TSqliteCommandBuilder($this); + } + + /** + * Creates a table instance representing the metadata for the named table. + * @return TDbTableSchema driver dependent table metadata. Null if the table does not exist. + */ + protected function createTable($name) + { + $db=$this->getDbConnection(); + + $table=new TDbTableSchema; + $table->name=$name; + $table->rawName=$this->quoteTableName($name); + + if($this->findColumns($table)) + { + $this->findConstraints($table); + return $table; + } + else + return null; + } + + /** + * Collects the table column metadata. + * @param TDbTableSchema the table metadata + * @return boolean whether the table exists in the database + */ + protected function findColumns($table) + { + $sql="PRAGMA table_info({$table->rawName})"; + $columns=$this->getDbConnection()->createCommand($sql)->queryAll(); + if(empty($columns)) + return false; + + foreach($columns as $column) + { + $c=$this->createColumn($column); + $table->columns[$c->name]=$c; + if($c->isPrimaryKey) + { + if($table->primaryKey===null) + $table->primaryKey=$c->name; + else if(is_string($table->primaryKey)) + $table->primaryKey=array($table->primaryKey,$c->name); + else + $table->primaryKey[]=$c->name; + } + } + if(is_string($table->primaryKey) && !strncasecmp($table->columns[$table->primaryKey]->dbType,'int',3)) + $table->sequenceName=''; + + return true; + } + + /** + * Collects the foreign key column details for the given table. + * @param TDbTableSchema the table metadata + */ + protected function findConstraints($table) + { + $foreignKeys=array(); + $sql="PRAGMA foreign_key_list({$table->rawName})"; + $keys=$this->getDbConnection()->createCommand($sql)->queryAll(); + foreach($keys as $key) + { + $column=$table->columns[$key['from']]; + $column->isForeignKey=true; + $foreignKeys[$key['from']]=array($key['table'],$key['to']); + } + $table->foreignKeys=$foreignKeys; + } + + /** + * Creates a table column. + * @param array column metadata + * @return TDbColumnSchema normalized column metadata + */ + protected function createColumn($column) + { + $c=new TSqliteColumnSchema; + $c->name=$column['name']; + $c->rawName=$this->quoteColumnName($c->name); + $c->allowNull=!$column['notnull']; + $c->isPrimaryKey=$column['pk']!=0; + $c->isForeignKey=false; + $c->init(strtolower($column['type']),$column['dflt_value']); + return $c; + } +} -- cgit v1.2.3