From dc3bf922d9715bfd1b2105be04a9aabc84a1d7d4 Mon Sep 17 00:00:00 2001 From: wei <> Date: Thu, 12 Apr 2007 08:05:03 +0000 Subject: Refactor and add TTableGateway, System.Data.Common, System.Data.DataGateway --- .gitattributes | 27 ++ framework/Data/ActiveRecord/TActiveRecord.php | 27 +- .../Data/ActiveRecord/TActiveRecordCriteria.php | 146 +-------- framework/Data/Common/Mysql/TMysqlMetaData.php | 251 ++++++++++++++++ framework/Data/Common/Mysql/TMysqlTableColumn.php | 69 +++++ framework/Data/Common/Mysql/TMysqlTableInfo.php | 46 +++ framework/Data/Common/Pgsql/TPgsqlMetaData.php | 329 +++++++++++++++++++++ framework/Data/Common/Pgsql/TPgsqlTableColumn.php | 48 +++ framework/Data/Common/Pgsql/TPgsqlTableInfo.php | 46 +++ framework/Data/Common/TDbCommandBuilder.php | 320 ++++++++++++++++++++ framework/Data/Common/TDbMetaData.php | 104 +++++++ framework/Data/Common/TDbTableColumn.php | 182 ++++++++++++ framework/Data/Common/TDbTableInfo.php | 167 +++++++++++ framework/Exceptions/messages.txt | 11 + .../features/protected/pages/ClientScripts.page | 6 + .../features/protected/pages/MyJavascriptLib.php | 32 ++ .../features/protected/pages/TestComp.php | 20 ++ .../protected/pages/myscripts/packages.php | 10 + .../ActiveRecord/RecordEventTestCase.php | 33 +++ .../DbCommon/CommandBuilderMysqlTest.php | 19 ++ .../DbCommon/CommandBuilderPgsqlTest.php | 77 +++++ tests/simple_unit/DbCommon/MysqlColumnTest.php | 270 +++++++++++++++++ tests/simple_unit/DbCommon/PgsqlColumnTest.php | 147 +++++++++ .../I18N/MysqlMessageSourceTestCase.php | 43 +++ tests/simple_unit/SqlMap/queryForListLimitTest.php | 32 ++ tests/simple_unit/TableGateway/BaseGatewayTest.php | 94 ++++++ tests/simple_unit/TableGateway/CountTest.php | 16 + tests/simple_unit/TableGateway/DeleteByPkTest.php | 52 ++++ tests/simple_unit/TableGateway/MagicCallTest.php | 31 ++ .../TableGateway/TableGatewayPgsqlTest.php | 56 ++++ tests/simple_unit/TableGateway/TestFindByPk.php | 48 +++ 31 files changed, 2595 insertions(+), 164 deletions(-) create mode 100644 framework/Data/Common/Mysql/TMysqlMetaData.php create mode 100644 framework/Data/Common/Mysql/TMysqlTableColumn.php create mode 100644 framework/Data/Common/Mysql/TMysqlTableInfo.php create mode 100644 framework/Data/Common/Pgsql/TPgsqlMetaData.php create mode 100644 framework/Data/Common/Pgsql/TPgsqlTableColumn.php create mode 100644 framework/Data/Common/Pgsql/TPgsqlTableInfo.php create mode 100644 framework/Data/Common/TDbCommandBuilder.php create mode 100644 framework/Data/Common/TDbMetaData.php create mode 100644 framework/Data/Common/TDbTableColumn.php create mode 100644 framework/Data/Common/TDbTableInfo.php create mode 100644 tests/FunctionalTests/features/protected/pages/ClientScripts.page create mode 100644 tests/FunctionalTests/features/protected/pages/MyJavascriptLib.php create mode 100644 tests/FunctionalTests/features/protected/pages/TestComp.php create mode 100644 tests/FunctionalTests/features/protected/pages/myscripts/packages.php create mode 100644 tests/simple_unit/ActiveRecord/RecordEventTestCase.php create mode 100644 tests/simple_unit/DbCommon/CommandBuilderMysqlTest.php create mode 100644 tests/simple_unit/DbCommon/CommandBuilderPgsqlTest.php create mode 100644 tests/simple_unit/DbCommon/MysqlColumnTest.php create mode 100644 tests/simple_unit/DbCommon/PgsqlColumnTest.php create mode 100644 tests/simple_unit/I18N/MysqlMessageSourceTestCase.php create mode 100644 tests/simple_unit/SqlMap/queryForListLimitTest.php create mode 100644 tests/simple_unit/TableGateway/BaseGatewayTest.php create mode 100644 tests/simple_unit/TableGateway/CountTest.php create mode 100644 tests/simple_unit/TableGateway/DeleteByPkTest.php create mode 100644 tests/simple_unit/TableGateway/MagicCallTest.php create mode 100644 tests/simple_unit/TableGateway/TableGatewayPgsqlTest.php create mode 100644 tests/simple_unit/TableGateway/TestFindByPk.php diff --git a/.gitattributes b/.gitattributes index 11a4c16b..51a89234 100644 --- a/.gitattributes +++ b/.gitattributes @@ -1642,6 +1642,16 @@ framework/Data/ActiveRecord/Vendor/TPgsqlMetaDataInspector.php -text framework/Data/ActiveRecord/Vendor/TSqliteColumnMetaData.php -text framework/Data/ActiveRecord/Vendor/TSqliteMetaData.php -text framework/Data/ActiveRecord/Vendor/TSqliteMetaDataInspector.php -text +framework/Data/Common/Mysql/TMysqlMetaData.php -text +framework/Data/Common/Mysql/TMysqlTableColumn.php -text +framework/Data/Common/Mysql/TMysqlTableInfo.php -text +framework/Data/Common/Pgsql/TPgsqlMetaData.php -text +framework/Data/Common/Pgsql/TPgsqlTableColumn.php -text +framework/Data/Common/Pgsql/TPgsqlTableInfo.php -text +framework/Data/Common/TDbCommandBuilder.php -text +framework/Data/Common/TDbMetaData.php -text +framework/Data/Common/TDbTableColumn.php -text +framework/Data/Common/TDbTableInfo.php -text framework/Data/SqlMap/Configuration/TDiscriminator.php -text framework/Data/SqlMap/Configuration/TInlineParameterMapParser.php -text framework/Data/SqlMap/Configuration/TParameterMap.php -text @@ -2390,6 +2400,7 @@ tests/FunctionalTests/features/protected/pages/ActiveControls/Calculator2.php -t tests/FunctionalTests/features/protected/pages/ActiveControls/VisibleUpdate.page -text tests/FunctionalTests/features/protected/pages/ActiveControls/VisibleUpdate.php -text tests/FunctionalTests/features/protected/pages/ActiveControls/config.xml -text +tests/FunctionalTests/features/protected/pages/ClientScripts.page -text tests/FunctionalTests/features/protected/pages/ColorPicker.page -text tests/FunctionalTests/features/protected/pages/FeatureList.page -text tests/FunctionalTests/features/protected/pages/FeatureList.php -text @@ -2400,8 +2411,11 @@ tests/FunctionalTests/features/protected/pages/I18N/BasicI18N.php -text tests/FunctionalTests/features/protected/pages/I18N/Home.page -text tests/FunctionalTests/features/protected/pages/I18N/Home.zh_CN.page -text tests/FunctionalTests/features/protected/pages/I18N/config.xml -text +tests/FunctionalTests/features/protected/pages/MyJavascriptLib.php -text tests/FunctionalTests/features/protected/pages/RatingList.page -text +tests/FunctionalTests/features/protected/pages/TestComp.php -text tests/FunctionalTests/features/protected/pages/ValidatorEffects.page -text +tests/FunctionalTests/features/protected/pages/myscripts/packages.php -text tests/FunctionalTests/features/tests/MyTestCase.php -text tests/FunctionalTests/index.php -text tests/FunctionalTests/quickstart/ActiveControls/ActiveButtonTestCase.php -text @@ -2634,6 +2648,7 @@ tests/simple_unit/ActiveRecord/CriteriaTestCase.php -text tests/simple_unit/ActiveRecord/DeleteByPkTestCase.php -text tests/simple_unit/ActiveRecord/FindByPksTestCase.php -text tests/simple_unit/ActiveRecord/FindBySqlTestCase.php -text +tests/simple_unit/ActiveRecord/RecordEventTestCase.php -text tests/simple_unit/ActiveRecord/SqliteTestCase.php -text tests/simple_unit/ActiveRecord/UserRecordTestCase.php -text tests/simple_unit/ActiveRecord/ViewRecordTestCase.php -text @@ -2644,8 +2659,13 @@ tests/simple_unit/ActiveRecord/records/DepartmentRecord.php -text tests/simple_unit/ActiveRecord/records/SimpleUser.php -text tests/simple_unit/ActiveRecord/records/SqliteUsers.php -text tests/simple_unit/ActiveRecord/records/UserRecord.php -text +tests/simple_unit/DbCommon/CommandBuilderMysqlTest.php -text +tests/simple_unit/DbCommon/CommandBuilderPgsqlTest.php -text +tests/simple_unit/DbCommon/MysqlColumnTest.php -text +tests/simple_unit/DbCommon/PgsqlColumnTest.php -text tests/simple_unit/I18N/ChoiceFormatTest.php -text tests/simple_unit/I18N/CultureInfoTest.php -text +tests/simple_unit/I18N/MysqlMessageSourceTestCase.php -text tests/simple_unit/Soap/ContactManager.php -text tests/simple_unit/Soap/SoapTestCase.php -text tests/simple_unit/SqlMap/ActiveRecordSqlMapTest.php -text @@ -2723,6 +2743,7 @@ tests/simple_unit/SqlMap/maps/tests.xml -text tests/simple_unit/SqlMap/mssql.xml -text tests/simple_unit/SqlMap/mysql.xml -text tests/simple_unit/SqlMap/properties.config -text +tests/simple_unit/SqlMap/queryForListLimitTest.php -text tests/simple_unit/SqlMap/resources/data.db -text tests/simple_unit/SqlMap/resources/person.xml -text tests/simple_unit/SqlMap/resources/sqlmap.xml -text @@ -2763,6 +2784,12 @@ tests/simple_unit/SqlMap/scripts/sqlite/database.sql -text tests/simple_unit/SqlMap/sqlite.xml -text tests/simple_unit/SqlMap/sqlite/backup.db -text tests/simple_unit/SqlMap/sqlite/tests.db -text +tests/simple_unit/TableGateway/BaseGatewayTest.php -text +tests/simple_unit/TableGateway/CountTest.php -text +tests/simple_unit/TableGateway/DeleteByPkTest.php -text +tests/simple_unit/TableGateway/MagicCallTest.php -text +tests/simple_unit/TableGateway/TableGatewayPgsqlTest.php -text +tests/simple_unit/TableGateway/TestFindByPk.php -text tests/simple_unit/application.xml -text tests/simple_unit/unit.php -text tests/simple_unit/ws.php -text diff --git a/framework/Data/ActiveRecord/TActiveRecord.php b/framework/Data/ActiveRecord/TActiveRecord.php index 109ae9a6..70fd7b23 100644 --- a/framework/Data/ActiveRecord/TActiveRecord.php +++ b/framework/Data/ActiveRecord/TActiveRecord.php @@ -250,10 +250,7 @@ abstract class TActiveRecord extends TComponent if(is_string($criteria)) { if(!is_array($parameters) && func_num_args() > 1) - { - $parameters = func_get_args(); - array_shift($parameters); - } + $parameters = array_slice(func_get_args(),1); $criteria=new TActiveRecordCriteria($criteria,$parameters); } $gateway = $this->getRecordManager()->getRecordGateway(); @@ -309,10 +306,7 @@ abstract class TActiveRecord extends TComponent if(is_string($criteria)) { if(!is_array($parameters) && func_num_args() > 1) - { - $parameters = func_get_args(); - array_shift($parameters); - } + $parameters = array_slice(func_get_args(),1); $criteria=new TActiveRecordCriteria($criteria,$parameters); } $gateway = $this->getRecordManager()->getRecordGateway(); @@ -332,10 +326,7 @@ abstract class TActiveRecord extends TComponent if(is_string($criteria)) { if(!is_array($parameters) && func_num_args() > 1) - { - $parameters = func_get_args(); - array_shift($parameters); - } + $parameters = array_slice(func_get_args(),1); $criteria=new TActiveRecordCriteria($criteria,$parameters); } $gateway = $this->getRecordManager()->getRecordGateway(); @@ -407,10 +398,7 @@ abstract class TActiveRecord extends TComponent public function findBySql($sql,$parameters=array()) { if(!is_array($parameters) && func_num_args() > 1) - { - $parameters = func_get_args(); - array_shift($parameters); - } + $parameters = array_slice(func_get_args(),1); $gateway = $this->getRecordManager()->getRecordGateway(); $data = $gateway->findRecordsBySql($this,$sql,$parameters); $results = array(); @@ -430,11 +418,8 @@ abstract class TActiveRecord extends TComponent { if(is_string($criteria)) { - if(!is_array($parameters) && func_num_args() > 1) - { - $parameters = func_get_args(); - array_shift($parameters); - } + if(!is_array($parameters) && func_num_args() > 1) + $parameters = array_slice(func_get_args(),1); $criteria=new TActiveRecordCriteria($criteria,$parameters); } $gateway = $this->getRecordManager()->getRecordGateway(); diff --git a/framework/Data/ActiveRecord/TActiveRecordCriteria.php b/framework/Data/ActiveRecord/TActiveRecordCriteria.php index 134632e8..cc4da7c8 100644 --- a/framework/Data/ActiveRecord/TActiveRecordCriteria.php +++ b/framework/Data/ActiveRecord/TActiveRecordCriteria.php @@ -10,6 +10,8 @@ * @package System.Data.ActiveRecord */ +Prado::using('System.Data.DataGateway.TSqlCriteria'); + /** * Search criteria for Active Record. * @@ -30,125 +32,8 @@ * @package System.Data.ActiveRecord * @since 3.1 */ -class TActiveRecordCriteria extends TComponent +class TActiveRecordCriteria extends TSqlCriteria { - private $_condition; - private $_parameters; - private $_ordersBy; - private $_limit; - private $_offset; - - /** - * Creates a new criteria with given condition; - * @param string sql string after the WHERE stanza - * @param mixed named or indexed parameters, accepts as multiple arguments. - */ - public function __construct($condition=null,$parameters=array()) - { - if(!is_array($parameters) && func_num_args() > 2) - { - $parameters = func_get_args(); - array_shift($parameters); - } - $this->setCondition($condition); - $this->_parameters=new TAttributeCollection((array)$parameters); - $this->_ordersBy=new TAttributeCollection; - } - - /** - * @return TAttributeCollection list of named parameters and values. - */ - public function getParameters() - { - return $this->_parameters; - } - - /** - * @param ArrayAccess named parameters. - */ - public function setParameters($value) - { - if(!(is_array($value) || $value instanceof ArrayAccess)) - throw new TException('value must be array or ArrayAccess'); - $this->_parameters->copyFrom($value); - } - - /** - * @return boolean true if the parameter index are string base, false otherwise. - */ - public function getIsNamedParameters() - { - foreach($this->getParameters() as $k=>$v) - return is_string($k); - } - - /** - * @return TAttributeCollection ordering clause. - */ - public function getOrdersBy() - { - return $this->_ordersBy; - } - - /** - * @param ArrayAccess ordering clause. - */ - public function setOrdersBy($value) - { - if(!(is_array($value) || $value instanceof ArrayAccess)) - throw new TException('value must be array or ArrayAccess'); - $this->_ordersBy->copyFrom($value); - } - - /** - * @return string search conditions. - */ - public function getCondition() - { - return $this->_condition; - } - - /** - * Sets the search conditions to be placed after the WHERE clause in the SQL. - * @param string search conditions. - */ - public function setCondition($value) - { - $this->_condition=$value; - } - - /** - * @return int maximum number of records to return. - */ - public function getLimit() - { - return $this->_limit; - } - - /** - * @param int maximum number of records to return. - */ - public function setLimit($value) - { - $this->_limit=$value; - } - - /** - * @return int record offset. - */ - public function getOffset() - { - return $this->_offset; - } - - /** - * @param int record offset. - */ - public function setOffset($value) - { - $this->_offset=$value; - } - /** * This method is invoked before the object is deleted from the database. * The method raises 'OnDelete' event. @@ -172,31 +57,6 @@ class TActiveRecordCriteria extends TComponent { $this->raiseEvent('OnSelect', $this, $param); } - - /** - * @return string string representation of the criteria. Useful for debugging. - */ - public function __toString() - { - $str = ''; - if(strlen((string)$this->getCondition()) > 0) - $str .= '"'.(string)$this->getCondition().'"'; - $params = array(); - foreach($this->getParameters() as $k=>$v) - $params[] = "{$k} => ${v}"; - if(count($params) > 0) - $str .= ', "'.implode(', ',$params).'"'; - $orders = array(); - foreach($this->getOrdersBy() as $k=>$v) - $orders[] = "{$k} => ${v}"; - if(count($orders) > 0) - $str .= ', "'.implode(', ',$orders).'"'; - if($this->_limit !==null) - $str .= ', '.$this->_limit; - if($this->_offset !== null) - $str .= ', '.$this->_offset; - return $str; - } } ?> \ No newline at end of file diff --git a/framework/Data/Common/Mysql/TMysqlMetaData.php b/framework/Data/Common/Mysql/TMysqlMetaData.php new file mode 100644 index 00000000..ee3bd2e0 --- /dev/null +++ b/framework/Data/Common/Mysql/TMysqlMetaData.php @@ -0,0 +1,251 @@ +getDbConnection()->setActive(true); + $sql = "SHOW FULL FIELDS FROM {$table}"; + $command = $this->getDbConnection()->createCommand($sql); + $tableInfo = $this->createNewTableInfo($table); + $index=0; + foreach($command->query() as $col) + { + $col['index'] = $index++; + $this->processColumn($tableInfo,$col); + } + return $tableInfo; + } + + /** + * @param TMysqlTableInfo table information. + * @param array column information. + */ + protected function processColumn($tableInfo, $col) + { + $columnId = $col['Field']; + + $info['ColumnName'] = "`$columnId`"; //quote the column names! + $info['ColumnIndex'] = $col['index']; + if($col['Null']!=='NO') + $info['AllowNull'] = true; + if(is_int(strpos(strtolower($col['Extra']), 'auto_increment'))) + $info['AutoIncrement']=true; + if($col['Default']!=="") + $info['DefaultValue'] = $col['Default']; + + if($col['Key']==='PRI' || in_array($columnId, $tableInfo->getPrimaryKeys())) + $info['IsPrimaryKey'] = true; + if($this->isForeignKeyColumn($columnId, $tableInfo)) + $info['IsForeignKey'] = true; + if(in_array($columnId, $tableInfo->getUniqueKeys())) + $info['IsUnique'] = true; + + $info['DbType'] = $col['Type']; + $match=array(); + //find SET/ENUM values, column size, precision, and scale + if(preg_match('/\((.*)\)/', $col['Type'], $match)) + { + $info['DbType']= preg_replace('/\(.*\)/', '', $col['Type']); + + //find SET/ENUM values + if($this->isEnumSetType($info['DbType'])) + $info['DbTypeValues'] = preg_split('/\s*,\s*|\s+/', preg_replace('/\'|"/', '', $match[1])); + + //find column size, precision and scale + $pscale = array(); + if(preg_match('/(\d+)(?:,(\d+))?+/', $match[1], $pscale)) + { + if($this->isPrecisionType($info['DbType'])) + { + $info['NumericPrecision'] = intval($pscale[1]); + if(count($pscale) > 2) + $info['NumericScale'] = intval($pscale[2]); + } + else + $info['ColumnSize'] = intval($pscale[1]); + } + } + + $tableInfo->Columns[$columnId] = new TMysqlTableColumn($info); + } + + /** + * @return boolean true if column type if "numeric", "interval" or begins with "time". + */ + protected function isPrecisionType($type) + { + $type = strtolower(trim($type)); + return $type==='decimal' || $type==='dec' + || $type==='float' || $type==='double' + || $type==='double precision' || $type==='real'; + } + + /** + * @return boolean true if column type if "enum" or "set". + */ + protected function isEnumSetType($type) + { + $type = strtolower(trim($type)); + return $type==='set' || $type==='enum'; + } + + + /** + * @param string table name, may be quoted with back-ticks and may contain database name. + * @return array tuple ($schema,$table), $schema may be null. + * @throws TDbException when table name contains invalid identifier bytes. + */ + protected function getSchemaTableName($table) + { + //remove the back ticks and separate out the "database.table" + $result = explode('.', str_replace('`', '', $table)); + foreach($result as $name) + { + if(!$this->isValidIdentifier($name)) + { + $ref = 'http://dev.mysql.com/doc/refman/5.0/en/identifiers.html'; + throw new TDbException('dbcommon_invalid_identifier_name', $table, $ref); + } + } + return count($result) > 1 ? $result : array(null, $result[0]); + } + + /** + * http://dev.mysql.com/doc/refman/5.0/en/identifiers.html + * @param unknown_type $name + */ + protected function isValidIdentifier($name) + { + return !preg_match('#/|\\|.|\x00|\xFF#', $name); + } + + /** + * @param string table schema name + * @param string table name. + * @return TMysqlTableInfo + */ + protected function createNewTableInfo($table) + { + list($schemaName,$tableName) = $this->getSchemaTableName($table); + $info['SchemaName'] = $schemaName; + $info['TableName'] = $tableName; + $info['IsView'] = $this->getIsView($schemaName,$tableName); + list($primary, $foreign, $unique) = $this->getConstraintKeys($schemaName, $tableName); + return new TMysqlTableInfo($info,$primary,$foreign, $unique); + } + + /** + * @param string database name, null to use default connection database. + * @param string table or view name. + * @return boolean true if is view, false otherwise. + * @throws TDbException if table or view does not exist. + */ + protected function getIsView($schemaName,$tableName) + { + if($schemaName!==null) + $sql = "SHOW FULL TABLES FROM `{$schemaName}` LIKE :table"; + else + $sql = 'SHOW FULL TABLES LIKE :table'; + + $command = $this->getDbConnection()->createCommand($sql); + $command->bindValue(':table', $tableName); + try + { + return count($result = $command->queryRow()) > 0 && $result['Table_type']==='VIEW'; + } + catch(TDbException $e) + { + $table = $schemaName===null?$tableName:$schemaName.'.'.$tableName; + throw new TDbException('dbcommon_invalid_table_name',$table,$e->getMessage()); + } + } + + /** + * Gets the primary, foreign key, and unique column details for the given table. + * @param string schema name + * @param string table name. + * @return array tuple ($primary, $foreign, $unique) + */ + protected function getConstraintKeys($schemaName, $tableName) + { + $table = $schemaName===null ? "`{$tableName}`" : "`{$schemaName}`.`{$tableName}`"; + $sql = "SHOW INDEX FROM {$table}"; + $command = $this->getDbConnection()->createCommand($sql); + $primary = array(); + $foreign = $this->getForeignConstraints($schemaName,$tableName); + $unique = array(); + foreach($command->query() as $row) + { + if($row['Key_name']==='PRIMARY') + $primary[] = $row['Column_name']; + else if(intval($row['Non_unique'])===0) + $unique[] = $row['Column_name']; + } + return array($primary,$foreign,$unique); + } + + /** + * Gets foreign relationship constraint keys and table name + * @param string database name + * @param string table name + * @return array foreign relationship table name and keys. + */ + protected function getForeignConstraints($schemaName, $tableName) + { + $andSchema = $schemaName !== null ? 'AND TABLE_SCHEMA = :schema' : ''; + $sql = <<getDbConnection()->createCommand($sql); + $command->bindValue(':table', $tableName); + if($schemaName!==null) + $command->bindValue(':schema', $schemaName); + $fkeys=array(); + foreach($command->query() as $col) + { + $fkeys[$col['con']]['keys'][$col['col']] = $col['fkcol']; + $fkeys[$col['con']]['table'] = "`{$col['fkschema']}`.`{$col['fktable']}`"; + } + return array_values($fkeys); + } + + /** + * @param string column name. + * @param TPgsqlTableInfo table information. + * @return boolean true if column is a foreign key. + */ + protected function isForeignKeyColumn($columnId, $tableInfo) + { + foreach($tableInfo->getForeignKeys() as $fk) + { + if(in_array($columnId, array_keys($fk['keys']))) + return true; + } + return false; + } +} + +?> \ No newline at end of file diff --git a/framework/Data/Common/Mysql/TMysqlTableColumn.php b/framework/Data/Common/Mysql/TMysqlTableColumn.php new file mode 100644 index 00000000..0f013acd --- /dev/null +++ b/framework/Data/Common/Mysql/TMysqlTableColumn.php @@ -0,0 +1,69 @@ + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2007 PradoSoft + * @license http://www.pradosoft.com/license/ + * @version $Id$ + * @package System.Data.Common.Mysql + */ + +/** + * Load common TDbTableCommon class. + */ +Prado::using('System.Data.Common.TDbTableColumn'); + +/** + * Describes the column metadata of the schema for a Mysql database table. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.Common.Mysql + * @since 3.1 + */ +class TMysqlTableColumn extends TDbTableColumn +{ + /** + * @return boolean true if column will auto-increment when the column value is inserted as null. + */ + public function getAutoIncrement() + { + return $this->getInfo('AutoIncrement', false); + } + + /** + * @return boolean true if auto increment is true. + */ + public function getHasSequence() + { + return $this->getAutoIncrement(); + } + + public function getDbTypeValues() + { + return $this->getInfo('DbTypeValues'); + } + + /** + * Overrides parent implementation, returns PHP type from the db type. + * @return boolean derived PHP primitive type from the column db type. + */ + public function getPHPType() + { + switch(strtolower($this->getDbType())) + { + case 'bit': case 'bit varying': case 'real': case 'serial': case 'int': case 'integer': + return 'integer'; + case 'boolean': + return 'boolean'; + case 'bigint': case 'bigserial': case 'double precision': case 'money': case 'numeric': + return 'float'; + default: + return 'string'; + } + } +} + +?> \ No newline at end of file diff --git a/framework/Data/Common/Mysql/TMysqlTableInfo.php b/framework/Data/Common/Mysql/TMysqlTableInfo.php new file mode 100644 index 00000000..e8585730 --- /dev/null +++ b/framework/Data/Common/Mysql/TMysqlTableInfo.php @@ -0,0 +1,46 @@ + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2007 PradoSoft + * @license http://www.pradosoft.com/license/ + * @version $Id$ + * @package System.Data.Common.Mysql + */ + +/** + * Loads the base TDbTableInfo class and TMysqlTableColumn class. + */ +Prado::using('System.Data.Common.TDbTableInfo'); +Prado::using('System.Data.Common.Mysql.TMysqlTableColumn'); + +/** + * TMysqlTableInfo class provides additional table information for MySQL database. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.Common.Mysql + * @since 3.1 + */ +class TMysqlTableInfo extends TDbTableInfo +{ + /** + * @return string name of the schema this column belongs to. + */ + public function getSchemaName() + { + return $this->getInfo('SchemaName'); + } + + /** + * @return string full name of the table, database dependent. + */ + public function getTableFullName() + { + return '`'.$this->getSchemaName().'`.`'.$this->getTableName().'`'; + } +} + +?> \ No newline at end of file diff --git a/framework/Data/Common/Pgsql/TPgsqlMetaData.php b/framework/Data/Common/Pgsql/TPgsqlMetaData.php new file mode 100644 index 00000000..b789192f --- /dev/null +++ b/framework/Data/Common/Pgsql/TPgsqlMetaData.php @@ -0,0 +1,329 @@ + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2007 PradoSoft + * @license http://www.pradosoft.com/license/ + * @version $Id$ + * @package System.Data.Common.Pgsql + */ + +/** + * Load the base TDbMetaData class. + */ +Prado::using('System.Data.Common.TDbMetaData'); +Prado::using('System.Data.Common.Pgsql.TPgsqlTableInfo'); + +/** + * TPgsqlMetaData loads PostgreSQL database table and column information. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.Commom.Pgsql + * @since 3.1 + */ +class TPgsqlMetaData extends TDbMetaData +{ + private $_defaultSchema = 'public'; + + /** + * @param string default schema. + */ + public function setDefaultSchema($schema) + { + $this->_defaultSchema=$schema; + } + + /** + * @return string default schema. + */ + public function getDefaultSchema() + { + 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) + { + if(count($parts= explode('.', $table)) > 1) + return array($parts[0], $parts[1]); + else + return array($this->getDefaultSchema(),$parts[0]); + } + + /** + * Get the column definitions for given table. + * @param string table name. + * @return TPgsqlTableInfo table information. + */ + protected function createTableInfo($table) + { + list($schemaName,$tableName) = $this->getSchemaTableName($table); + + // This query is made much more complex by the addition of the 'attisserial' field. + // The subquery to get that field checks to see if there is an internally dependent + // sequence on the field. + $sql = +<< 0 AND NOT a.attisdropped + ORDER BY a.attnum +EOD; + $this->getDbConnection()->setActive(true); + $command = $this->getDbConnection()->createCommand($sql); + $command->bindValue(':table', $tableName); + $command->bindValue(':schema', $schemaName); + $tableInfo = $this->createNewTableInfo($schemaName, $tableName); + $index=0; + foreach($command->query() as $col) + { + $col['index'] = $index++; + $this->processColumn($tableInfo, $col); + } + return $tableInfo; + } + + /** + * @param string table schema name + * @param string table name. + * @return TPgsqlTableInfo + */ + protected function createNewTableInfo($schemaName,$tableName) + { + $info['SchemaName'] = $schemaName; + $info['TableName'] = $tableName; + if($this->getIsView($schemaName,$tableName)) + $info['IsView'] = true; + list($primary, $foreign, $unique) = $this->getConstraintKeys($schemaName, $tableName); + return new TPgsqlTableInfo($info,$primary,$foreign, $unique); + } + + /** + * @param string table schema name + * @param string table name. + * @return boolean true if the table is a view. + */ + protected function getIsView($schemaName,$tableName) + { + $sql = +<<getDbConnection()->setActive(true); + $command = $this->getDbConnection()->createCommand($sql); + $command->bindValue(':schema',$schemaName); + $command->bindValue(':table', $tableName); + return intval($command->queryScalar()) === 1; + } + + /** + * @param TPgsqlTableInfo table information. + * @param array column information. + */ + protected function processColumn($tableInfo, $col) + { + $columnId = $col['attname']; //use column name as column Id + + $info['ColumnName'] = '"'.$columnId.'"'; //quote the column names! + $info['ColumnIndex'] = $col['index']; + if(!$col['attnotnull']) + $info['AllowNull'] = true; + if(in_array($columnId, $tableInfo->getPrimaryKeys())) + $info['IsPrimaryKey'] = true; + if($this->isForeignKeyColumn($columnId, $tableInfo)) + $info['IsForeignKey'] = true; + if(in_array($columnId, $tableInfo->getUniqueKeys())) + $info['IsUnique'] = true; + + if($col['atttypmod'] > 0) + $info['ColumnSize'] = $col['atttypmod'] - 4; + if($col['atthasdef']) + $info['DefaultValue'] = $col['adsrc']; + if($col['attisserial'] || substr($col['adsrc'],0,8) === 'nextval(') + { + if(($sequence = $this->getSequenceName($tableInfo, $col['adsrc']))!==null) + { + $info['SequenceName'] = $sequence; + unset($info['DefaultValue']); + } + } + $matches = array(); + if(preg_match('/\((\d+)(?:,(\d+))?+\)/', $col['type'], $matches)) + { + $info['DbType'] = preg_replace('/\(\d+(?:,\d+)?\)/','',$col['type']); + if($this->isPrecisionType($info['DbType'])) + { + $info['NumericPrecision'] = intval($matches[1]); + if(count($matches) > 2) + $info['NumericScale'] = intval($matches[2]); + } + else + $info['ColumnSize'] = intval($matches[1]); + } + else + $info['DbType'] = $col['type']; + + $tableInfo->Columns[$columnId] = new TPgsqlTableColumn($info); + } + + /** + * @return string serial name if found, null otherwise. + */ + protected function getSequenceName($tableInfo,$src) + { + $matches = array(); + if(preg_match('/nextval\([^\']*\'([^\']+)\'[^\)]*\)/i',$src,$matches)) + { + if(is_int(strpos($matches[1], '.'))) + return $matches[1]; + else + return $tableInfo->getSchemaName().'.'.$matches[1]; + } + } + + /** + * @return boolean true if column type if "numeric", "interval" or begins with "time". + */ + protected function isPrecisionType($type) + { + $type = strtolower(trim($type)); + return $type==='numeric' || $type==='interval' || strpos($type, 'time')===0; + } + + /** + * Gets the primary, foreign key, and unique column details for the given table. + * @param string schema name + * @param string table name. + * @return array tuple ($primary, $foreign, $unique) + */ + protected function getConstraintKeys($schemaName, $tableName) + { + $sql = 'SELECT + pg_catalog.pg_get_constraintdef(pc.oid, true) AS consrc, + pc.contype + FROM + pg_catalog.pg_constraint pc + WHERE + pc.conrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname=:table + AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace + WHERE nspname=:schema)) + '; + $this->getDbConnection()->setActive(true); + $command = $this->getDbConnection()->createCommand($sql); + $command->bindValue(':table', $tableName); + $command->bindValue(':schema', $schemaName); + $primary = array(); + $foreign = array(); + $unique = array(); + foreach($command->query() as $row) + { + switch($row['contype']) + { + case 'p': + $primary = $this->getPrimaryKeys($row['consrc']); + break; + case 'f': + if(($fkey = $this->getForeignKeys($row['consrc']))!==null) + $foreign[] = $fkey; + break; + case 'u': + if(($ukey = $this->getUniqueKey($row['consrc']))!==null) + $unique[] = $ukey; + break; + } + } + return array($primary,$foreign,$unique); + } + + /** + * Gets the primary key field names + * @param string pgsql primary key definition + * @return array primary key field names. + */ + protected function getPrimaryKeys($src) + { + $matches = array(); + if(preg_match('/PRIMARY\s+KEY\s+\(([^\)]+)\)/i', $src, $matches)) + return preg_split('/,\s+/',$matches[1]); + return array(); + } + + /** + * @param string pgsql unique constraint definition + * @return string column id if found, null otherwise. + */ + protected function getUniqueKey($src) + { + $matches=array(); + if(preg_match('/UNIQUE\s+\(([^\)]+)\)/i', $src, $matches)) + return $matches[1]; + } + + /** + * Gets foreign relationship constraint keys and table name + * @param string pgsql foreign key definition + * @return array foreign relationship table name and keys, null otherwise + */ + protected function getForeignKeys($src) + { + $matches = array(); + $brackets = '\(([^\)]+)\)'; + $find = "/FOREIGN\s+KEY\s+{$brackets}\s+REFERENCES\s+([^\(]+){$brackets}/i"; + if(preg_match($find, $src, $matches)) + { + $keys = preg_split('/,\s+/', $matches[1]); + $fkeys = array(); + foreach(preg_split('/,\s+/', $matches[3]) as $i => $fkey) + $fkeys[$keys[$i]] = $fkey; + return array('table' => $matches[2], 'keys' => $fkeys); + } + } + + /** + * @param string column name. + * @param TPgsqlTableInfo table information. + * @return boolean true if column is a foreign key. + */ + protected function isForeignKeyColumn($columnId, $tableInfo) + { + foreach($tableInfo->getForeignKeys() as $fk) + { + if(in_array($columnId, array_keys($fk['keys']))) + return true; + } + return false; + } +} + +?> \ No newline at end of file diff --git a/framework/Data/Common/Pgsql/TPgsqlTableColumn.php b/framework/Data/Common/Pgsql/TPgsqlTableColumn.php new file mode 100644 index 00000000..66053a63 --- /dev/null +++ b/framework/Data/Common/Pgsql/TPgsqlTableColumn.php @@ -0,0 +1,48 @@ + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2007 PradoSoft + * @license http://www.pradosoft.com/license/ + * @version $Id$ + * @package System.Data.Common.Pgsql + */ + +/** + * Load common TDbTableCommon class. + */ +Prado::using('System.Data.Common.TDbTableColumn'); + +/** + * Describes the column metadata of the schema for a PostgreSQL database table. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.Common.Pgsql + * @since 3.1 + */ +class TPgsqlTableColumn extends TDbTableColumn +{ + /** + * Overrides parent implementation, returns PHP type from the db type. + * @return boolean derived PHP primitive type from the column db type. + */ + public function getPHPType() + { + switch(strtolower($this->getDbType())) + { + case 'bit': case 'bit varying': case 'real': case 'serial': case 'int': case 'integer': + return 'integer'; + case 'boolean': + return 'boolean'; + case 'bigint': case 'bigserial': case 'double precision': case 'money': case 'numeric': + return 'float'; + default: + return 'string'; + } + } +} + +?> \ No newline at end of file diff --git a/framework/Data/Common/Pgsql/TPgsqlTableInfo.php b/framework/Data/Common/Pgsql/TPgsqlTableInfo.php new file mode 100644 index 00000000..88a56635 --- /dev/null +++ b/framework/Data/Common/Pgsql/TPgsqlTableInfo.php @@ -0,0 +1,46 @@ + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2007 PradoSoft + * @license http://www.pradosoft.com/license/ + * @version $Id$ + * @package System.Data.Common.Pgsql + */ + +/** + * Loads the base TDbTableInfo class and TPgsqlTableColumn class. + */ +Prado::using('System.Data.Common.TDbTableInfo'); +Prado::using('System.Data.Common.Pgsql.TPgsqlTableColumn'); + +/** + * TPgsqlTableInfo class provides additional table information for PostgreSQL database. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.Common.Pgsql + * @since 3.1 + */ +class TPgsqlTableInfo extends TDbTableInfo +{ + /** + * @return string name of the schema this column belongs to. + */ + public function getSchemaName() + { + return $this->getInfo('SchemaName'); + } + + /** + * @return string full name of the table, database dependent. + */ + public function getTableFullName() + { + return $this->getSchemaName().'.'.$this->getTableName(); + } +} + +?> \ No newline at end of file diff --git a/framework/Data/Common/TDbCommandBuilder.php b/framework/Data/Common/TDbCommandBuilder.php new file mode 100644 index 00000000..5238a045 --- /dev/null +++ b/framework/Data/Common/TDbCommandBuilder.php @@ -0,0 +1,320 @@ + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2007 PradoSoft + * @license http://www.pradosoft.com/license/ + * @version $Id$ + * @package System.Data.Common + */ + +/** + * TDbCommandBuilder provides basic methods to create query commands for tables + * giving by {@link setTableInfo TableInfo} the property. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.Common + * @since 3.1 + */ +class TDbCommandBuilder extends TComponent +{ + private $_connection; + private $_tableInfo; + + /** + * @param TDbConnection database connection. + * @param TDbTableInfo table information. + */ + public function __construct($connection=null, $tableInfo=null) + { + $this->setDbConnection($connection); + $this->setTableInfo($tableInfo); + } + + /** + * @return TDbConnection database connection. + */ + public function getDbConnection() + { + return $this->_connection; + } + + /** + * @param TDbConnection database connection. + */ + public function setDbConnection($value) + { + $this->_connection=$value; + } + + /** + * @param TDbTableInfo table information. + */ + public function setTableInfo($value) + { + $this->_tableInfo=$value; + } + + /** + * @param TDbTableInfo table information. + */ + public function getTableInfo() + { + return $this->_tableInfo; + } + + /** + * Alters the sql to apply $limit and $offset. Default implementation is applicable + * for PostgreSQL, MySQL and SQLite. + * @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. + */ + public function applyLimitOffset($sql, $limit=-1, $offset=-1) + { + $limit = $limit!==null ? intval($limit) : -1; + $offset = $offset!==null ? intval($offset) : -1; + $limitStr = $limit >= 0 ? ' LIMIT '.$limit : ''; + $offsetStr = $offset >= 0 ? ' OFFSET '.$offset : ''; + return $sql.$limitStr.$offsetStr; + } + + /** + * @param string SQL string without existing ordering. + * @param array pairs of column names as key and direction as value. + * @return string modified SQL applied with ORDER BY. + */ + public function applyOrdering($sql, $ordering) + { + $orders=array(); + foreach($ordering as $name=>$direction) + { + $direction = strtolower($direction) == 'desc' ? 'DESC' : 'ASC'; + $column = $this->getTableInfo()->getColumn($name)->getColumnName(); + $orders[] = $column.' '.$direction; + } + if(count($orders) > 0) + $sql .= ' ORDER BY '.implode(', ', $orders); + return $sql; + } + + /** + * Computes the SQL condition for search a set of column using regular expression + * to match a string of keywords. The implementation should only uses columns + * that permit regular expression matching. This method should be implemented in + * database specific command builder classes. + * @param array list of column id for potential search condition. + * @param string string of keywords + * @return string SQL condition for regular expression matching on a set of columns. + */ + //abstract public function createRegExpSearch($columnIds, $keywords); + + /** + * Appends the $where condition to the string "SELECT * FROM tableName WHERE ". + * The tableName is obtained from the {@link setTableInfo TableInfo} property. + * @param string query condition + * @param array condition parameters. + * @return TDbCommand query command. + */ + public function createFindCommand($where, $parameters=array(), $ordering=array(), $limit=-1, $offset=-1) + { + $table = $this->getTableInfo()->getTableFullName(); + $sql = "SELECT * FROM {$table} WHERE {$where}"; + if(count($ordering) > 0) + $sql = $this->applyOrdering($sql, $ordering); + if($limit>=0 || $offset>=0) + $sql = $this->applyLimitOffset($sql, $limit, $offset); + $command = $this->createCommand($sql); + $this->bindArrayValues($command, $parameters); + return $command; + } + + /** + * Creates a count(*) command for the table described in {@link setTableInfo TableInfo}. + * @param string count condition. + * @param array binding parameters. + * @return TDbCommand count command. + */ + public function createCountCommand($where='true', $parameters=array(),$ordering=array(), $limit=-1, $offset=-1) + { + $table = $this->getTableInfo()->getTableFullName(); + $sql = "SELECT COUNT(*) FROM {$table} WHERE {$where}"; + if(count($ordering) > 0) + $sql = $this->applyOrdering($sql, $ordering); + if($limit>=0 || $offset>=0) + $sql = $this->applyLimitOffset($sql, $limit, $offset); + $command = $this->createCommand($sql); + $this->bindArrayValues($command, $parameters); + return $command; + } + + /** + * Creates a delete command for the table described in {@link setTableInfo TableInfo}. + * The conditions for delete is given by the $where argument and the parameters + * for the condition is given by $parameters. + * @param string delete condition. + * @param array delete parameters. + * @return TDbCommand delete command. + */ + public function createDeleteCommand($where,$parameters=array()) + { + $table = $this->getTableInfo()->getTableFullName(); + $command = $this->createCommand("DELETE FROM {$table} WHERE {$where}"); + $this->bindArrayValues($command, $parameters); + return $command; + } + + /** + * Creates an insert command for the table described in {@link setTableInfo TableInfo} for the given data. + * Each array key in the $data array must correspond to the column name of the table + * (if a column allows to be null, it may be omitted) to be inserted with + * the corresponding array value. + * @param array name-value pairs of new data to be inserted. + * @return TDbCommand insert command + */ + public function createInsertCommand($data) + { + $table = $this->getTableInfo()->getTableFullName(); + list($fields, $bindings) = $this->getInsertFieldBindings($data); + $command = $this->createCommand("INSERT INTO {$table}({$fields}) VALUES ($bindings)"); + $this->bindColumnValues($command, $data); + return $command; + } + + /** + * Creates an update command for the table described in {@link setTableInfo TableInfo} for the given data. + * Each array key in the $data array must correspond to the column name to be updated with the corresponding array value. + * @param array name-value pairs of data to be updated. + * @param string update condition. + * @param array update parameters. + * @return TDbCommand update command. + */ + public function createUpdateCommand($data, $where, $parameters=array()) + { + $table = $this->getTableInfo()->getTableFullName(); + if($this->hasIntegerKey($parameters)) + $fields = implode(', ', $this->getColumnBindings($data, true)); + else + $fields = implode(', ', $this->getColumnBindings($data)); + $command = $this->createCommand("UPDATE {$table} SET {$fields} WHERE {$where}"); + $this->bindArrayValues($command, array_merge($data, $parameters)); + return $command; + } + + /** + * Returns a list of insert field name and a list of binding names. + * @param object array or object to be inserted. + * @return array tuple ($fields, $bindings) + */ + protected function getInsertFieldBindings($values) + { + $fields = array(); $bindings=array(); + foreach(array_keys($values) as $name) + { + $fields[] = $this->getTableInfo()->getColumn($name)->getColumnName(); + $bindings[] = ':'.$name; + } + return array(implode(', ',$fields), implode(', ', $bindings)); + } + + /** + * Create a name-value or position-value if $position=true binding strings. + * @param array data for binding. + * @param boolean true to bind as position values. + * @return string update column names with corresponding binding substrings. + */ + protected function getColumnBindings($values, $position=false) + { + $bindings=array(); + foreach(array_keys($values) as $name) + { + $column = $this->getTableInfo()->getColumn($name)->getColumnName(); + $bindings[] = $position ? $column.' = ?' : $column.' = :'.$name; + } + return $bindings; + } + + /** + * @param string SQL query string. + * @return TDbCommand corresponding database command. + */ + public function createCommand($sql) + { + $this->getDbConnection()->setActive(true); + return $this->getDbConnection()->createCommand($sql); + } + + /** + * Bind the name-value pairs of $values where the array keys correspond to column names. + * @param TDbCommand database command. + * @param array name-value pairs. + */ + public function bindColumnValues($command, $values) + { + foreach($values as $name=>$value) + { + $column = $this->getTableInfo()->getColumn($name); + if($value === null && $column->getAllowNull()) + $command->bindValue(':'.$name, null, PDO::PARAM_NULL); + else + $command->bindValue(':'.$name, $value, $column->getPdoType()); + } + } + + /** + * @param TDbCommand database command + * @param array values for binding. + */ + public function bindArrayValues($command, $values) + { + if($this->hasIntegerKey($values)) + { + $values = array_values($values); + for($i = 0, $max=count($values); $i<$max; $i++) + $command->bindValue($i+1, $values[$i], $this->getPdoType($value)); + } + else + { + foreach($values as $name=>$value) + { + $prop = $name[0]===':' ? $name : ':'.$name; + $command->bindValue($prop, $value, $this->getPdoType($value)); + } + } + } + + /** + * @param mixed PHP value + * @return integer PDO parameter types. + */ + protected function getPdoType($value) + { + switch(gettype($value)) + { + case 'boolean': return PDO::PARAM_BOOL; + case 'integer': return PDO::PARAM_INT; + case 'string' : return PDO::PARAM_STR; + case 'NULL' : return PDO::PARAM_NULL; + } + } + + /** + * @param array + * @return boolean true if any array key is an integer. + */ + protected function hasIntegerKey($array) + { + foreach($array as $k=>$v) + { + if(gettype($k)==='integer') + return true; + } + return false; + } +} + +?> \ No newline at end of file diff --git a/framework/Data/Common/TDbMetaData.php b/framework/Data/Common/TDbMetaData.php new file mode 100644 index 00000000..477e2805 --- /dev/null +++ b/framework/Data/Common/TDbMetaData.php @@ -0,0 +1,104 @@ + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2007 PradoSoft + * @license http://www.pradosoft.com/license/ + * @version $Id$ + * @package System.Data.Common + */ + +/** + * TDbMetaData is the base class for retrieving metadata information, such as + * table and columns information, from a database connection. + * + * Use the {@link getTableInfo} method to retrieve a table information. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.Common + * @since 3.1 + */ +abstract class TDbMetaData extends TComponent +{ + private $_tableInfoCache=array(); + private $_connection; + + /** + * @param TDbConnection database connection. + */ + public function __construct($conn) + { + $this->_connection=$conn; + } + + /** + * @return TDbConnection database connection. + */ + public function getDbConnection() + { + return $this->_connection; + } + + /** + * Obtain database specific TDbMetaData class using the driver name of the database connection. + * @param TDbConnection database connection. + * @return TDbMetaData database specific TDbMetaData. + */ + public static function getMetaData($conn) + { + $conn->setActive(true); //must be connected before retrieving driver name + $driver = $conn->getDriverName(); + switch(strtolower($driver)) + { + case 'pgsql': + Prado::using('System.Data.Common.Pgsql.TPgsqlMetaData'); + return new TPgsqlMetaData($conn); + case 'mysqli': + case 'mysql': + Prado::using('System.Data.Common.Mysql.TMysqlMetaData'); + return new TMysqlMetaData($conn); + case 'sqlite': //sqlite 3 + case 'sqlite2': //sqlite 2 + Prado::using('System.Data.Common.Sqlite.TSqliteMetaData'); + return new TSqliteMetaData($conn); + case 'ibm': + Prado::using('System.Data.Common.IbmDb2.TIbmDb2MetaData'); + return new TIbmDb2MetaData($conn); + default: + throw new TDbException('ar_invalid_database_driver',$driver); + } + } + + /** + * Obtains table meta data information for the current connection and given table name. + * @param string table or view name + * @return TDbTableInfo table information. + */ + public function getTableInfo($tableName) + { + if(!isset($this->_tableInfoCache[$tableName])) + $this->_tableInfoCache[$tableName] = $this->createTableInfo($tableName); + return $this->_tableInfoCache[$tableName]; + } + + /** + * Creates a command builder for a given table name. + * @param string table name. + * @return TDbCommandBuilder command builder instance for the given table. + */ + public function createCommandBuilder($tableName) + { + return $this->getTableInfo($tableName)->createCommandBuilder($this->getDbConnection()); + } + + /** + * This method should be implemented by decendent classes. + * @return TDbTableInfo driver dependent create builder. + */ + abstract protected function createTableInfo($tableName); +} + +?> \ No newline at end of file diff --git a/framework/Data/Common/TDbTableColumn.php b/framework/Data/Common/TDbTableColumn.php new file mode 100644 index 00000000..4d9bd8a0 --- /dev/null +++ b/framework/Data/Common/TDbTableColumn.php @@ -0,0 +1,182 @@ + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2007 PradoSoft + * @license http://www.pradosoft.com/license/ + * @version $Id$ + * @package System.Data.Common + */ + +/** + * TDbTableColumn class describes the column meta data of the schema for a database table. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.Common + * @since 3.1 + */ +class TDbTableColumn extends TComponent +{ + const UNDEFINED_VALUE= INF; //use infinity for undefined value + + private $_info=array(); + + /** + * Sets the table column meta data. + * @param array table column information. + */ + public function __construct($columnInfo) + { + $this->_info=$columnInfo; + } + + /** + * @param string information array key name + * @param mixed default value if information array value is null + * @return mixed information array value. + */ + protected function getInfo($name,$default=null) + { + return isset($this->_info[$name]) ? $this->_info[$name] : $default; + } + + /** + * @param string information array key name + * @param mixed new information array value. + */ + protected function setInfo($name,$value) + { + $this->_info[$name]=$value; + } + + /** + * Returns the derived PHP primitive type from the db type. Default returns 'string'. + * @return string derived PHP primitive type from the column db type. + */ + public function getPHPType() + { + return 'string'; + } + + /** + * @param integer PDO bind param/value types. + */ + public function getPdoType() + { + switch($this->getPHPType()) + { + case 'boolean': return PDO::PARAM_BOOL; + case 'integer': return PDO::PARAM_INT; + case 'string' : return PDO::PARAM_STR; + } + } + + /** + * @return string name of the column in the table (identifier quoted). + */ + public function getColumnName() + { + return $this->getInfo('ColumnName'); + } + + /** + * @return string size of the column. + */ + public function getColumnSize() + { + return $this->getInfo('ColumnSize'); + } + + /** + * @return integer zero-based ordinal position of the column in the table. + */ + public function getColumnIndex() + { + return $this->getInfo('ColumnIndex'); + } + + /** + * @return string column type. + */ + public function getDbType() + { + return $this->getInfo('DbType'); + } + + /** + * @return boolean specifies whether value Null is allowed, default is false. + */ + public function getAllowNull() + { + return $this->getInfo('AllowNull',false); + } + + /** + * @return mixed default column value if column value was null. + */ + public function getDefaultValue() + { + return $this->getInfo('DefaultValue', self::UNDEFINED_VALUE); + } + + /** + * @return string precision of the column data, if the data is numeric. + */ + public function getNumericPrecision() + { + return $this->getInfo('NumericPrecision'); + } + + /** + * @return string scale of the column data, if the data is numeric. + */ + public function getNumericScale() + { + return $this->getInfo('NumericScale'); + } + + /** + * @return boolean whether this column is a primary key for the table, default is false. + */ + public function getIsPrimaryKey() + { + return $this->getInfo('IsPrimaryKey',false); + } + + /** + * @return boolean whether this column is a foreign key, default is false. + */ + public function getIsForeignKey() + { + return $this->getInfo('IsForeignKey',false); + } + + /** + * @return boolean whether a unique constraint applies to this column, default is false. + */ + public function getIsUnique() + { + return $this->getInfo('IsUnique', false); + } + + /** + * @param string sequence name, only applicable if column is a sequence + */ + public function getSequenceName() + { + return $this->getInfo('SequenceName'); + } + + /** + * @return boolean whether the column is a sequence. + */ + public function hasSequence() + { + return $this->getSequenceName()!==null; + } +} + +?> \ No newline at end of file diff --git a/framework/Data/Common/TDbTableInfo.php b/framework/Data/Common/TDbTableInfo.php new file mode 100644 index 00000000..9b7f4392 --- /dev/null +++ b/framework/Data/Common/TDbTableInfo.php @@ -0,0 +1,167 @@ + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2007 PradoSoft + * @license http://www.pradosoft.com/license/ + * @version $Id$ + * @package System.Data.Common + */ + +/** + * TDbTableInfo class describes the meta data of a database table. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.Common + * @since 3.1 + */ +class TDbTableInfo extends TComponent +{ + private $_info=array(); + + private $_primaryKeys; + private $_foreignKeys; + private $_uniqueKeys; + + private $_columns; + + private $_lowercase; + + /** + * Sets the database table meta data information. + * @param array table column information. + */ + public function __construct($tableInfo,$primary=array(),$foreign=array(), $unique=array()) + { + $this->_info=$tableInfo; + $this->_primaryKeys=$primary; + $this->_foreignKeys=$foreign; + $this->_uniqueKeys=$unique; + $this->_columns=new TMap; + } + + /** + * @param TDbConnection database connection. + * @return TDbCommandBuilder new command builder + */ + public function createCommandBuilder($connection) + { + Prado::using('System.Data.Common.TDbCommandBuilder'); + return new TDbCommandBuilder($connection,$this); + } + + /** + * @param string information array key name + * @param mixed default value if information array value is null + * @return mixed information array value. + */ + protected function getInfo($name,$default=null) + { + return isset($this->_info[$name]) ? $this->_info[$name] : $default; + } + + /** + * @param string information array key name + * @param mixed new information array value. + */ + protected function setInfo($name,$value) + { + $this->_info[$name]=$value; + } + + /** + * @return string name of the table this column belongs to. + */ + public function getTableName() + { + return $this->getInfo('TableName'); + } + + /** + * @return string full name of the table, database dependent. + */ + public function getTableFullName() + { + return $this->getTableName(); + } + + /** + * @return boolean whether the table is a view, default is false. + */ + public function getIsView() + { + return $this->getInfo('IsView',false); + } + + /** + * @return TMap TDbTableColumn column meta data. + */ + public function getColumns() + { + return $this->_columns; + } + + /** + * @param string column id + * @return TDbTableColumn column information. + */ + public function getColumn($name) + { + return $this->_columns->itemAt($name); + } + + /** + * @param array list of column Id, empty to get all columns. + * @return array table column names (identifier quoted) + */ + public function getColumnNames() + { + $names=array(); + foreach($this->getColumns() as $column) + $names[] = $column->getColumnName(); + return $names; + } + + /** + * @return string[] names of primary key columns. + */ + public function getPrimaryKeys() + { + return $this->_primaryKeys; + } + + /** + * @return array tuples of foreign table and column name. + */ + public function getForeignKeys() + { + return $this->_foreignKeys; + } + + /** + * @return array unique column ids. + */ + public function getUniqueKeys() + { + return $this->_uniqueKeys; + } + + /** + * @return array lowercased column key names mapped to normal column ids. + */ + public function getLowerCaseColumnNames() + { + if($this->_lowercase===null) + { + $this->_lowercase=array(); + foreach($this->getColumns()->getKeys() as $key) + $this->_lowercase[strtolower($key)] = $key; + } + return $this->_lowercase; + } +} + +?> \ No newline at end of file diff --git a/framework/Exceptions/messages.txt b/framework/Exceptions/messages.txt index 9072cbc3..0bd11361 100644 --- a/framework/Exceptions/messages.txt +++ b/framework/Exceptions/messages.txt @@ -366,6 +366,17 @@ dbcommand_column_empty = TDbCommand returned an empty result and could not o dbdatareader_rewind_invalid = TDbDataReader is a forward-only stream. It can only be traversed once. dbtransaction_transaction_inactive = TDbTransaction is inactive. +dbcommandbuilder_value_must_not_be_null = Property {0} must not be null as defined by column '{2}' in table '{1}'. + +dbcommon_invalid_table_name = Database table '{0}' not found. Error Msg: {1}. +dbcommon_invalid_identifier_name = Invalid database identifier name '{0}', see {1} for details. +dbtablegateway_invalid_criteria = Invalid criteria object, must be a string or instance of TSqlCriteria. +dbtablegateway_no_primary_key_found = Table '{0}' does not contain any primary key fields. +dbtablegateway_missing_pk_values = Missing primary key values in forming IN(key1, key2, ...) for table '{0}'. +dbtablegateway_pk_value_count_mismatch = Composite key value count mismatch in forming IN( (key1, key2, ..), (key3, key4, ..)) for table '{0}'. +dbtablegateway_mismatch_args_exception = TTableGateway finder method '{0}' expects {1} parameters but found only {2} parameters instead. +dbtablegateway_mismatch_column_name = In dynamic __call() method '{0}', no matching columns were found, valid columns for table '{2}' are '{1}'. + directorycachedependency_directory_invalid = TDirectoryCacheDependency.Directory {0} does not refer to a valid directory. cachedependencylist_cachedependency_required = Only objects implementing ICacheDependency can be added into TCacheDependencyList. diff --git a/tests/FunctionalTests/features/protected/pages/ClientScripts.page b/tests/FunctionalTests/features/protected/pages/ClientScripts.page new file mode 100644 index 00000000..18aca48b --- /dev/null +++ b/tests/FunctionalTests/features/protected/pages/ClientScripts.page @@ -0,0 +1,6 @@ + + + + + + \ No newline at end of file diff --git a/tests/FunctionalTests/features/protected/pages/MyJavascriptLib.php b/tests/FunctionalTests/features/protected/pages/MyJavascriptLib.php new file mode 100644 index 00000000..964b48a5 --- /dev/null +++ b/tests/FunctionalTests/features/protected/pages/MyJavascriptLib.php @@ -0,0 +1,32 @@ +_manager = $owner->getClientScript(); + $owner->onPreRenderComplete = array($this, 'registerScriptLoader'); + } + + public static function registerPackage(TControl $control, $name) + { + static $instance; + if($instance===null) + $instance=new self($control->getPage()); + $instance->_packages[$name]=true; + } + + protected function registerScriptLoader() + { + $dir = dirname(__FILE__).'/myscripts'; //contains my javascript files + $scripts = array_keys($this->_packages); + $url = $this->_manager->registerJavascriptPackages($dir, $scripts); + $this->_manager->registerScriptFile($url,$url); + } +} + +?> \ No newline at end of file diff --git a/tests/FunctionalTests/features/protected/pages/TestComp.php b/tests/FunctionalTests/features/protected/pages/TestComp.php new file mode 100644 index 00000000..f9d02c77 --- /dev/null +++ b/tests/FunctionalTests/features/protected/pages/TestComp.php @@ -0,0 +1,20 @@ +_class=$value; + } + + public function onPreRender($param) + { + parent::onPreRender($param); + MyJavascriptLib::registerPackage($this,$this->_class); + } +} + +?> \ No newline at end of file diff --git a/tests/FunctionalTests/features/protected/pages/myscripts/packages.php b/tests/FunctionalTests/features/protected/pages/myscripts/packages.php new file mode 100644 index 00000000..41561a71 --- /dev/null +++ b/tests/FunctionalTests/features/protected/pages/myscripts/packages.php @@ -0,0 +1,10 @@ + \ No newline at end of file diff --git a/tests/simple_unit/ActiveRecord/RecordEventTestCase.php b/tests/simple_unit/ActiveRecord/RecordEventTestCase.php new file mode 100644 index 00000000..c669bb72 --- /dev/null +++ b/tests/simple_unit/ActiveRecord/RecordEventTestCase.php @@ -0,0 +1,33 @@ +setDbConnection($conn); + } + + function testFindByPk() + { + $user1 = UserRecord::finder()->findByPk('admin'); + $this->assertNotNull($user1); + } + + function test_same_data_returns_same_object() + { + $criteria = new TActiveRecordCriteria('username = ?', 'admin'); + $criteria->OnSelect = array($this, 'logger'); + $user1 = UserRecord::finder()->find($criteria); + //var_dump($user1); + } + + function logger($sender, $param) + { + var_dump($param->Command->Text); + } +} + +?> \ No newline at end of file diff --git a/tests/simple_unit/DbCommon/CommandBuilderMysqlTest.php b/tests/simple_unit/DbCommon/CommandBuilderMysqlTest.php new file mode 100644 index 00000000..500d5277 --- /dev/null +++ b/tests/simple_unit/DbCommon/CommandBuilderMysqlTest.php @@ -0,0 +1,19 @@ +mysql_meta_data()->getTableInfo("tests.table1"); + } +} + +?> \ No newline at end of file diff --git a/tests/simple_unit/DbCommon/CommandBuilderPgsqlTest.php b/tests/simple_unit/DbCommon/CommandBuilderPgsqlTest.php new file mode 100644 index 00000000..8bf2848e --- /dev/null +++ b/tests/simple_unit/DbCommon/CommandBuilderPgsqlTest.php @@ -0,0 +1,77 @@ +pgsql_meta_data()->createCommandBuilder('address'); + $address=array( + 'username' => 'Username', + 'phone' => 121987, + 'field1_boolean' => true, + 'field2_date' => '1213', + 'field3_double' => 121.1, + 'field4_integer' => 345, + 'field6_time' => time(), + 'field7_timestamp' => time(), + 'field8_money' => '121.12', + 'field9_numeric' => 984.22, + 'int_fk1'=>1, + 'int_fk2'=>1, + ); + $insert = $builder->createInsertCommand($address); + $sql = 'INSERT INTO public.address("username", "phone", "field1_boolean", "field2_date", "field3_double", "field4_integer", "field6_time", "field7_timestamp", "field8_money", "field9_numeric", "int_fk1", "int_fk2") VALUES (:username, :phone, :field1_boolean, :field2_date, :field3_double, :field4_integer, :field6_time, :field7_timestamp, :field8_money, :field9_numeric, :int_fk1, :int_fk2)'; + $this->assertEqual($sql, $insert->Text); + } + + function test_update_command() + { + $builder = $this->pgsql_meta_data()->createCommandBuilder('address'); + $data = array( + 'phone' => 9809, + 'int_fk1' => 1212, + ); + $update = $builder->createUpdateCommand($data, '1'); + $sql = 'UPDATE public.address SET "phone" = :phone, "int_fk1" = :int_fk1 WHERE 1'; + $this->assertEqual($sql, $update->Text); + } + + function test_delete_command() + { + $builder = $this->pgsql_meta_data()->createCommandBuilder('address'); + $where = 'phone is NULL'; + $delete = $builder->createDeleteCommand($where); + $sql = 'DELETE FROM public.address WHERE phone is NULL'; + $this->assertEqual($sql, $delete->Text); + } + + function test_select_limit() + { + $meta = $this->pgsql_meta_data(); + $builder = $meta->createCommandBuilder('address'); + $query = 'SELECT * FROM '.$meta->getTableInfo('address')->getTableFullName(); + + $limit = $builder->createLimitCondition($query, 1); + $expect = $query.' LIMIT 1'; + $this->assertEqual($expect, $limit); + + $limit = $builder->createLimitCondition($query, -1, 10); + $expect = $query.' OFFSET 10'; + $this->assertEqual($expect, $limit); + + $limit = $builder->createLimitCondition($query, 2, 3); + $expect = $query.' LIMIT 2 OFFSET 3'; + $this->assertEqual($expect, $limit); + } +} + +?> \ No newline at end of file diff --git a/tests/simple_unit/DbCommon/MysqlColumnTest.php b/tests/simple_unit/DbCommon/MysqlColumnTest.php new file mode 100644 index 00000000..d8bb8194 --- /dev/null +++ b/tests/simple_unit/DbCommon/MysqlColumnTest.php @@ -0,0 +1,270 @@ +create_meta_data()->getTableInfo('table1'); + $this->assertEqual(count($table->getColumns()), 18); + + $columns['id'] = array( + 'ColumnName' => '`id`', + 'ColumnSize' => 10, + 'ColumnIndex' => 0, + 'DbType' => 'int unsigned', + 'AllowNull' => false, + 'DefaultValue' => TDbTableColumn::UNDEFINED_VALUE, + 'NumericPrecision' => null, + 'NumericScale' => null, + 'IsPrimaryKey' => true, + 'IsForeignKey' => false, + 'IsUnique' => false, + 'SequenceName' => null, + 'AutoIncrement' => true, + ); + + $columns['name'] = array( + 'ColumnName' => '`name`', + 'ColumnSize' => 45, + 'ColumnIndex' => 1, + 'DbType' => 'varchar', + 'AllowNull' => false, + 'DefaultValue' => TDbTableColumn::UNDEFINED_VALUE, + 'NumericPrecision' => null, + 'NumericScale' => null, + 'IsPrimaryKey' => true, + 'IsForeignKey' => false, + 'IsUnique' => false, + 'SequenceName' => null, + 'AutoIncrement' => false, + ); + + $columns['field1'] = array( + 'ColumnName' => '`field1`', + 'ColumnSize' => 4, + 'ColumnIndex' => 2, + 'DbType' => 'tinyint', + 'AllowNull' => false, + 'DefaultValue' => TDbTableColumn::UNDEFINED_VALUE, + 'NumericPrecision' => null, + 'NumericScale' => null, + 'IsPrimaryKey' => false, + 'IsForeignKey' => false, + 'IsUnique' => false, + 'SequenceName' => null, + 'AutoIncrement' => false, + ); + + $columns['field2_text'] = array( + 'ColumnName' => '`field2_text`', + 'ColumnSize' => null, + 'ColumnIndex' => 3, + 'DbType' => 'text', + 'AllowNull' => true, + 'DefaultValue' => TDbTableColumn::UNDEFINED_VALUE, + 'NumericPrecision' => null, + 'NumericScale' => null, + 'IsPrimaryKey' => false, + 'IsForeignKey' => false, + 'IsUnique' => false, + 'SequenceName' => null, + 'AutoIncrement' => false, + ); + + $columns['field3_date'] = array( + 'ColumnName' => '`field3_date`', + 'ColumnSize' => null, + 'ColumnIndex' => 4, + 'DbType' => 'date', + 'AllowNull' => true, + 'DefaultValue' => '2007-02-25', + 'NumericPrecision' => null, + 'NumericScale' => null, + 'IsPrimaryKey' => false, + 'IsForeignKey' => false, + 'IsUnique' => false, + 'SequenceName' => null, + 'AutoIncrement' => false, + ); + + $columns['field4_float'] = array( + 'ColumnName' => '`field4_float`', + 'ColumnSize' => null, + 'ColumnIndex' => 5, + 'DbType' => 'float', + 'AllowNull' => false, + 'DefaultValue' => 10, + 'NumericPrecision' => null, + 'NumericScale' => null, + 'IsPrimaryKey' => false, + 'IsForeignKey' => false, + 'IsUnique' => false, + 'SequenceName' => null, + 'AutoIncrement' => false, + ); + + $columns['field5_float'] = array( + 'ColumnName' => '`field5_float`', + 'ColumnSize' => null, + 'ColumnIndex' => 6, + 'DbType' => 'float', + 'AllowNull' => false, + 'DefaultValue' => TDbTableColumn::UNDEFINED_VALUE, + 'NumericPrecision' => 5, + 'NumericScale' => 4, + 'IsPrimaryKey' => false, + 'IsForeignKey' => false, + 'IsUnique' => false, + 'SequenceName' => null, + 'AutoIncrement' => false, + ); + + $columns['field6_double'] = array( + 'ColumnName' => '`field6_double`', + 'ColumnSize' => null, + 'ColumnIndex' => 7, + 'DbType' => 'double', + 'AllowNull' => false, + 'DefaultValue' => TDbTableColumn::UNDEFINED_VALUE, + 'NumericPrecision' => null, + 'NumericScale' => null, + 'IsPrimaryKey' => false, + 'IsForeignKey' => false, + 'IsUnique' => false, + 'SequenceName' => null, + 'AutoIncrement' => false, + ); + + $columns['field7_datetime'] = array( + 'ColumnName' => '`field7_datetime`', + 'ColumnSize' => null, + 'ColumnIndex' => 8, + 'DbType' => 'datetime', + 'AllowNull' => false, + 'DefaultValue' => TDbTableColumn::UNDEFINED_VALUE, + 'NumericPrecision' => null, + 'NumericScale' => null, + 'IsPrimaryKey' => false, + 'IsForeignKey' => false, + 'IsUnique' => false, + 'SequenceName' => null, + 'AutoIncrement' => false, + ); + + $columns['field8_timestamp'] = array( + 'ColumnName' => '`field8_timestamp`', + 'ColumnSize' => null, + 'ColumnIndex' => 9, + 'DbType' => 'timestamp', + 'AllowNull' => true, + 'DefaultValue' => 'CURRENT_TIMESTAMP', + 'NumericPrecision' => null, + 'NumericScale' => null, + 'IsPrimaryKey' => false, + 'IsForeignKey' => false, + 'IsUnique' => false, + 'SequenceName' => null, + 'AutoIncrement' => false, + ); + + $columns['field9_time'] = array( + 'ColumnName' => '`field9_time`', + 'ColumnSize' => null, + 'ColumnIndex' => 10, + 'DbType' => 'time', + 'AllowNull' => false, + 'DefaultValue' => TDbTableColumn::UNDEFINED_VALUE, + 'NumericPrecision' => null, + 'NumericScale' => null, + 'IsPrimaryKey' => false, + 'IsForeignKey' => false, + 'IsUnique' => false, + 'SequenceName' => null, + 'AutoIncrement' => false, + ); + + $columns['field10_year'] = array( + 'ColumnName' => '`field10_year`', + 'ColumnSize' => 4, + 'ColumnIndex' => 11, + 'DbType' => 'year', + 'AllowNull' => false, + 'DefaultValue' => TDbTableColumn::UNDEFINED_VALUE, + 'NumericPrecision' => null, + 'NumericScale' => null, + 'IsPrimaryKey' => false, + 'IsForeignKey' => false, + 'IsUnique' => false, + 'SequenceName' => null, + 'AutoIncrement' => false, + ); + + $columns['field11_enum'] = array( + 'ColumnName' => '`field11_enum`', + 'ColumnSize' => null, + 'ColumnIndex' => 12, + 'DbType' => 'enum', + 'AllowNull' => false, + 'DefaultValue' => 'one', + 'NumericPrecision' => null, + 'NumericScale' => null, + 'IsPrimaryKey' => false, + 'IsForeignKey' => false, + 'IsUnique' => false, + 'SequenceName' => null, + 'AutoIncrement' => false, + 'DbTypeValues' => array('one', 'two', 'three'), + ); + + $columns['field12_SET'] = array( + 'ColumnName' => '`field12_SET`', + 'ColumnSize' => null, + 'ColumnIndex' => 13, + 'DbType' => 'set', + 'AllowNull' => false, + 'DefaultValue' => TDbTableColumn::UNDEFINED_VALUE, + 'NumericPrecision' => null, + 'NumericScale' => null, + 'IsPrimaryKey' => false, + 'IsForeignKey' => false, + 'IsUnique' => false, + 'SequenceName' => null, + 'AutoIncrement' => false, + 'DbTypeValues' => array('blue', 'red', 'green'), + ); + + $this->assertColumn($columns, $table); + + $this->assertNull($table->getSchemaName()); + $this->assertEqual('table1', $table->getTableName()); + $this->assertEqual(array('id', 'name'), $table->getPrimaryKeys()); + $this->assertEqual(array('fk3'), $table->getUniqueKeys()); + } + + function assertColumn($columns, $table) + { + foreach($columns as $id=>$asserts) + { + $column = $table->Columns[$id]; + foreach($asserts as $property=>$assert) + { + $ofAssert= var_export($assert,true); + $value = $column->{$property}; + $ofValue = var_export($value, true); + $this->assertEqual($value, $assert, + "Column [{$id}] {$property} value {$ofValue} did not match {$ofAssert}"); + } + } + } +} + +?> \ No newline at end of file diff --git a/tests/simple_unit/DbCommon/PgsqlColumnTest.php b/tests/simple_unit/DbCommon/PgsqlColumnTest.php new file mode 100644 index 00000000..0f633725 --- /dev/null +++ b/tests/simple_unit/DbCommon/PgsqlColumnTest.php @@ -0,0 +1,147 @@ +create_meta_data()->getTableInfo('public.address'); + $this->assertEqual(count($table->getColumns()), 14); + + $columns['id'] = array( + 'ColumnName' => '"id"', + 'ColumnSize' => null, + 'ColumnIndex' => 0, + 'DbType' => 'integer', + 'AllowNull' => false, + 'DefaultValue' => TDbTableColumn::UNDEFINED_VALUE, + 'NumericPrecision' => null, + 'NumericScale' => null, + 'IsPrimaryKey' => true, + 'IsForeignKey' => false, + 'IsUnique' => false, + 'SequenceName' => 'public.address_id_seq', + ); + + $columns['username'] = array( + 'ColumnName' => '"username"', + 'ColumnSize' => 128, + 'ColumnIndex' => 1, + 'DbType' => 'character varying', + 'AllowNull' => false, + 'DefaultValue' => TDbTableColumn::UNDEFINED_VALUE, + 'NumericPrecision' => null, + 'NumericScale' => null, + 'IsPrimaryKey' => false, + 'IsForeignKey' => false, + 'IsUnique' => true, + 'SequenceName' => null, + ); + + $columns['phone'] = array( + 'ColumnName' => '"phone"', + 'ColumnSize' => 40, + 'ColumnIndex' => 2, + 'DbType' => 'character', + 'AllowNull' => false, + 'DefaultValue' => "'hello'::bpchar", + 'NumericPrecision' => null, + 'NumericScale' => null, + 'IsPrimaryKey' => false, + 'IsForeignKey' => false, + 'IsUnique' => true, + 'SequenceName' => null, + ); + + $columns['field1_boolean'] = array( + 'ColumnName' => '"field1_boolean"', + 'ColumnSize' => null, + 'ColumnIndex' => 3, + 'DbType' => 'boolean', + 'AllowNull' => false, + 'DefaultValue' => TDbTableColumn::UNDEFINED_VALUE, + 'NumericPrecision' => null, + 'NumericScale' => null, + 'IsPrimaryKey' => false, + 'IsForeignKey' => false, + 'IsUnique' => false, + 'SequenceName' => null, + ); + + $columns['field4_integer'] = array( + 'ColumnName' => '"field4_integer"', + 'ColumnSize' => null, + 'ColumnIndex' => 6, + 'DbType' => 'integer', + 'AllowNull' => false, + 'DefaultValue' => "1", + 'NumericPrecision' => null, + 'NumericScale' => null, + 'IsPrimaryKey' => false, + 'IsForeignKey' => true, + 'IsUnique' => false, + 'SequenceName' => null, + ); + + $columns['field7_timestamp'] = array( + 'ColumnName' => '"field7_timestamp"', + 'ColumnSize' => 2, + 'ColumnIndex' => 9, + 'DbType' => 'timestamp without time zone', + 'AllowNull' => false, + 'DefaultValue' => TDbTableColumn::UNDEFINED_VALUE, + 'NumericPrecision' => 6, + 'NumericScale' => null, + 'IsPrimaryKey' => false, + 'IsForeignKey' => false, + 'IsUnique' => false, + 'SequenceName' => null, + ); + + $columns['field9_numeric'] = array( + 'ColumnName' => '"field9_numeric"', + 'ColumnSize' => 393220, + 'ColumnIndex' => 11, + 'DbType' => 'numeric', + 'AllowNull' => false, + 'DefaultValue' => TDbTableColumn::UNDEFINED_VALUE, + 'NumericPrecision' => 6, + 'NumericScale' => 4, + 'IsPrimaryKey' => false, + 'IsForeignKey' => false, + 'IsUnique' => false, + 'SequenceName' => null, + ); + $this->assertColumn($columns, $table); + + $this->assertEqual('public', $table->getSchemaName()); + $this->assertEqual('address', $table->getTableName()); + $this->assertEqual(array('id'), $table->getPrimaryKeys()); + $this->assertEqual(array('username', 'phone'), $table->getUniqueKeys()); + } + + function assertColumn($columns, $table) + { + foreach($columns as $id=>$asserts) + { + $column = $table->Columns[$id]; + foreach($asserts as $property=>$assert) + { + $ofAssert= var_export($assert,true); + $value = $column->{$property}; + $ofValue = var_export($value, true); + $this->assertEqual($value, $assert, + "Column [{$id}] {$property} value {$ofValue} did not match {$ofAssert}"); + } + } + } +} + +?> \ No newline at end of file diff --git a/tests/simple_unit/I18N/MysqlMessageSourceTestCase.php b/tests/simple_unit/I18N/MysqlMessageSourceTestCase.php new file mode 100644 index 00000000..9f48d499 --- /dev/null +++ b/tests/simple_unit/I18N/MysqlMessageSourceTestCase.php @@ -0,0 +1,43 @@ +_source===null) + { + $this->_source = new MessageSource_MySQL('mysq://prado:prado@localhost/i18n_test'); + $this->_source->setCulture('en_AU'); + } + return $this->_source; + } + + function test_source() + { + $source = $this->get_source(); + $this->assertEqual(3, count($source->catalogues())); + } + + function test_load_source() + { + $source = $this->get_source(); + $this->assertTrue($source->load()); + } + + function test_message_format() + { + $formatter = new MessageFormat($this->get_source()); + var_dump($formatter->format('Hello')); + var_dump($formatter->format('Goodbye')); + //$this->assertEqual($formatter->format('Hello'),'G\'day Mate!'); + + //$this->assertEqual($formatter->format('Goodbye'), 'Goodbye'); + } +} + +?> \ No newline at end of file diff --git a/tests/simple_unit/SqlMap/queryForListLimitTest.php b/tests/simple_unit/SqlMap/queryForListLimitTest.php new file mode 100644 index 00000000..ceacbcdc --- /dev/null +++ b/tests/simple_unit/SqlMap/queryForListLimitTest.php @@ -0,0 +1,32 @@ +initSqlMap(); + + //force autoload + new Account; + } + + function resetDatabase() + { + $this->initScript('account-init.sql'); + } + + function test_accounts_limit_2() + { + $list1 = $this->sqlmap->queryForList('GetAllAccountsAsArrayListViaResultClass', null, null, 2); + //var_dump($list1); + } +} + +?> \ No newline at end of file diff --git a/tests/simple_unit/TableGateway/BaseGatewayTest.php b/tests/simple_unit/TableGateway/BaseGatewayTest.php new file mode 100644 index 00000000..825f2d0e --- /dev/null +++ b/tests/simple_unit/TableGateway/BaseGatewayTest.php @@ -0,0 +1,94 @@ +gateway1===null) + { + $conn = new TDbConnection('pgsql:host=localhost;dbname=test', 'test','test'); + $this->gateway1 = new TTableGateway('address', $conn); + } + return $this->gateway1; + } + + /** + * @return TTableGateway + */ + function getGateway2() + { + if($this->gateway2===null) + { + $conn = new TDbConnection('pgsql:host=localhost;dbname=test', 'test','test'); + $this->gateway2 = new TTableGateway('department_sections', $conn); + } + return $this->gateway2; + } + + function setup() + { + $this->delete_all(); + } + + function add_record1() + { + $result = $this->getGateway()->insert($this->get_record1()); + $this->assertTrue(intval($result) > 0); + } + function add_record2() + { + $result = $this->getGateway()->insert($this->get_record2()); + $this->assertTrue(intval($result) > 0); + } + function get_record1() + { + return array( + 'username' => 'Username', + 'phone' => 121987, + 'field1_boolean' => true, + 'field2_date' => '2007-12-25', + 'field3_double' => 121.1, + 'field4_integer' => 3, + 'field5_text' => 'asdasd', + 'field6_time' => '12:40:00', + 'field7_timestamp' => 'NOW', + 'field8_money' => '$121.12', + 'field9_numeric' => 98.2232, + 'int_fk1'=>1, + 'int_fk2'=>1, + ); + } + + + function get_record2() + { + return array( + 'username' => 'record2', + 'phone' => 45233, + 'field1_boolean' => false, + 'field2_date' => '2004-10-05', + 'field3_double' => 1221.1, + 'field4_integer' => 2, + 'field5_text' => 'hello world', + 'field6_time' => '22:40:00', + 'field7_timestamp' => 'NOW', + 'field8_money' => '$1121.12', + 'field9_numeric' => 8.2213, + 'int_fk1'=>1, + 'int_fk2'=>1, + ); + } + function delete_all() + { + $this->getGateway()->deleteAll('true'); + } +} +?> \ No newline at end of file diff --git a/tests/simple_unit/TableGateway/CountTest.php b/tests/simple_unit/TableGateway/CountTest.php new file mode 100644 index 00000000..56ffb19b --- /dev/null +++ b/tests/simple_unit/TableGateway/CountTest.php @@ -0,0 +1,16 @@ +getGateway2()->count(); + $this->assertEqual(44,$result); + + $result = $this->getGateway2()->count('department_id = ?', 1); + $this->assertEqual(4, $result); + } +} +?> \ No newline at end of file diff --git a/tests/simple_unit/TableGateway/DeleteByPkTest.php b/tests/simple_unit/TableGateway/DeleteByPkTest.php new file mode 100644 index 00000000..120b63e9 --- /dev/null +++ b/tests/simple_unit/TableGateway/DeleteByPkTest.php @@ -0,0 +1,52 @@ +add_record1(); + $id = $this->getGateway()->getLastInsertId(); + $deleted = $this->getGateway()->deleteByPk($id); + + $this->assertEqual(1, $deleted); + } + + function test_delete_by_multiple_pk() + { + $this->add_record1(); + $id1 = $this->getGateway()->getLastInsertId(); + $this->add_record2(); + $id2 = $this->getGateway()->getLastInsertId(); + + $deleted = $this->getGateway()->deleteByPk($id1, $id2); + + $this->assertEqual(2, $deleted); + } + + function test_delete_by_multiple_pk2() + { + $this->add_record1(); + $id1 = $this->getGateway()->getLastInsertId(); + $this->add_record2(); + $id2 = $this->getGateway()->getLastInsertId(); + + $deleted = $this->getGateway()->deleteByPk(array($id1, $id2)); + + $this->assertEqual(2, $deleted); + } + + function test_delete_by_multiple_pk3() + { + $this->add_record1(); + $id1 = $this->getGateway()->getLastInsertId(); + $this->add_record2(); + $id2 = $this->getGateway()->getLastInsertId(); + + $deleted = $this->getGateway()->deleteByPk(array(array($id1), array($id2))); + + $this->assertEqual(2, $deleted); + } +} +?> \ No newline at end of file diff --git a/tests/simple_unit/TableGateway/MagicCallTest.php b/tests/simple_unit/TableGateway/MagicCallTest.php new file mode 100644 index 00000000..c0df313d --- /dev/null +++ b/tests/simple_unit/TableGateway/MagicCallTest.php @@ -0,0 +1,31 @@ +add_record1(); $this->add_record2(); + + $result = $this->getGateway()->findByUsername("record2"); + $this->assertEqual($result['username'], 'record2'); + } + + function test_combined_and_or() + { + $this->add_record1(); $this->add_record2(); + + $result = $this->getGateway()->findAllByUsername_OR_phone('Username', '45233')->readAll(); + $this->assertEqual(2, count($result)); + } + + function test_no_result() + { + $this->add_record1(); $this->add_record2(); + $result = $this->getGateway()->findAllByUsername_and_phone('Username', '45233')->readAll(); + + $this->assertEqual(0, count($result)); + } +} +?> \ No newline at end of file diff --git a/tests/simple_unit/TableGateway/TableGatewayPgsqlTest.php b/tests/simple_unit/TableGateway/TableGatewayPgsqlTest.php new file mode 100644 index 00000000..973e8d21 --- /dev/null +++ b/tests/simple_unit/TableGateway/TableGatewayPgsqlTest.php @@ -0,0 +1,56 @@ +add_record1(); + $address = array('username' => 'tester 1', 'field5_text'=>null); + $result = $this->getGateway()->update($address, 'username = ?', 'Username'); + $this->assertTrue($result); + + $test = $this->getGateway()->find('username = ?', 'tester 1'); + unset($test['id']); + $expect = $this->get_record1(); + $expect['username'] = 'tester 1'; + $expect['field5_text'] = null; + unset($expect['field7_timestamp']); unset($test['field7_timestamp']); + $this->assertEqual($expect, $test); + + $this->assertTrue($this->getGateway()->deleteAll('username = ?', 'tester 1')); + } + + function test_update_named() + { + $this->add_record1(); + $address = array('username' => 'tester 1', 'field5_text'=>null); + $result = $this->getGateway()->update($address, 'username = :name', array(':name'=>'Username')); + $this->assertTrue($result); + + $test = $this->getGateway()->find('username = :name', array(':name'=>'tester 1')); + unset($test['id']); + $expect = $this->get_record1(); + $expect['username'] = 'tester 1'; + $expect['field5_text'] = null; + unset($expect['field7_timestamp']); unset($test['field7_timestamp']); + $this->assertEqual($expect, $test); + + $this->assertTrue($this->getGateway()->deleteAll('username = :name', array(':name'=>'tester 1'))); + } + + function test_find_all() + { + $this->add_record1(); + $this->add_record2(); + + $results = $this->getGateway()->findAll('true')->readAll(); + $this->assertEqual(count($results), 2); + + $result = $this->getGateway()->findBySql('SELECT username FROM address WHERE phone = ?', '45233')->read(); + $this->assertEqual($result['username'], 'record2'); + } + +} +?> \ No newline at end of file diff --git a/tests/simple_unit/TableGateway/TestFindByPk.php b/tests/simple_unit/TableGateway/TestFindByPk.php new file mode 100644 index 00000000..b9a25edf --- /dev/null +++ b/tests/simple_unit/TableGateway/TestFindByPk.php @@ -0,0 +1,48 @@ +add_record1(); + $id = $this->getGateway()->getLastInsertId(); + $result = $this->getGateway()->findByPk($id); + + $record1 = $this->get_record1(); + + //clean and ignore some fields + unset($result['id']); + unset($result['field7_timestamp']); + unset($record1['field7_timestamp']); + $result['phone'] = intval($result['phone']); + $result['field9_numeric'] = floatval($result['field9_numeric']); + + $this->assertEqual($record1, $result); + } + + function test_composite_key() + { + $gateway = $this->getGateway2(); + + $result = $gateway->findByPk(1,1); + $expect = array("department_id" => 1, "section_id" => 1, "order" => 0); + $this->assertEqual($expect, $result); + } + + function test_find_all_keys() + { + $gateway = $this->getGateway2(); + + $result = $gateway->findAllByPks(array(1,1), array(3,13))->readAll(); + + $expect = array( + array("department_id" => 1, "section_id" => 1, "order" => 0), + array("department_id" => 3, "section_id" => 13, "order" => 0)); + + $this->assertEqual($expect, $result); + + } +} +?> \ No newline at end of file -- cgit v1.2.3