From fd4b8d9f45d1707035021bc19b8d5bc17ede66ce Mon Sep 17 00:00:00 2001 From: wei <> Date: Mon, 12 Feb 2007 12:46:11 +0000 Subject: Add IBM DB2 driver for active record. --- framework/Data/ActiveRecord/Vendor/TDbMetaData.php | 38 ++++-- .../Data/ActiveRecord/Vendor/TDbMetaDataCommon.php | 2 +- .../ActiveRecord/Vendor/TIbmColumnMetaData.php | 149 +++++++++++++++++++++ .../Data/ActiveRecord/Vendor/TIbmMetaData.php | 80 +++++++++++ .../ActiveRecord/Vendor/TIbmMetaDataInspector.php | 113 ++++++++++++++++ .../Data/ActiveRecord/Vendor/TMysqlMetaData.php | 22 ++- .../Vendor/TMysqlMetaDataInspector.php | 7 +- .../ActiveRecord/Vendor/TPgsqlColumnMetaData.php | 19 ++- .../Data/ActiveRecord/Vendor/TPgsqlMetaData.php | 26 +++- .../Vendor/TPgsqlMetaDataInspector.php | 19 ++- .../Data/ActiveRecord/Vendor/TSqliteMetaData.php | 19 ++- .../Vendor/TSqliteMetaDataInspector.php | 5 +- 12 files changed, 468 insertions(+), 31 deletions(-) create mode 100644 framework/Data/ActiveRecord/Vendor/TIbmColumnMetaData.php create mode 100644 framework/Data/ActiveRecord/Vendor/TIbmMetaData.php create mode 100644 framework/Data/ActiveRecord/Vendor/TIbmMetaDataInspector.php (limited to 'framework/Data/ActiveRecord/Vendor') diff --git a/framework/Data/ActiveRecord/Vendor/TDbMetaData.php b/framework/Data/ActiveRecord/Vendor/TDbMetaData.php index 38a82aef..3a959ba4 100644 --- a/framework/Data/ActiveRecord/Vendor/TDbMetaData.php +++ b/framework/Data/ActiveRecord/Vendor/TDbMetaData.php @@ -20,8 +20,7 @@ * @version $Id$ * @package System.Data.ActiveRecord.Vendor * @since 3.1 - */ - + */ abstract class TDbMetaData extends TComponent { private $_primaryKeys=array(); @@ -91,6 +90,11 @@ abstract class TDbMetaData extends TComponent return $this->_columns[$name]; } + public function getColumnNames() + { + return array_keys($this->_columns); + } + /** * Post process the rows after returning from a 1 row query. * @param mixed row data, may be null. @@ -187,7 +191,7 @@ abstract class TDbMetaData extends TComponent foreach($keys as $i => $key) { $value = isset($values[$i]) ? $values[$i] : $values[$key]; - $command->bindValue(':'.$key, $value); + $this->bindValue($command, ':'.$key, $value); } $command->prepare(); } @@ -249,7 +253,7 @@ abstract class TDbMetaData extends TComponent /** * Gets a comma delimited string of name parameters for update. -x * @param array name value pairs of columns for update. + * @param array name value pairs of columns for update. * @return string update named parameter string. */ protected function getUpdateBindings($columns) @@ -294,19 +298,27 @@ x * @param array name value pairs of columns for update. if($criteria->getIsNamedParameters()) { foreach($criteria->getParameters() as $name=>$value) - $command->bindValue($name,$value); + $this->bindValue($command, $name, $value); } else { $index=1; foreach($criteria->getParameters() as $value) - $command->bindValue($index++,$value); + $this->bindValue($command, $index++,$value); } } $command->prepare(); return $command; } + protected function bindValue($command, $name, $value) + { + if(is_bool($value)) + $command->bindValue($name,$value, PDO::PARAM_BOOL); + else + $command->bindValue($name,$value); + } + /** * Bind parameter values. */ @@ -316,9 +328,9 @@ x * @param array name value pairs of columns for update. foreach($parameters as $key=>$value) { if(is_string($key)) - $command->bindValue($key,$value); + $this->bindValue($command,$key,$value); else - $command->bindValue($index++,$value); + $this->bindValue($command, $index++,$value); } $command->prepare(); } @@ -345,5 +357,15 @@ x * @param array name value pairs of columns for update. return implode(', ', $fields); } + /** + * @param string ordering column name + * @param string ordering direction + * @return string DESC or ASC + */ + protected function getOrdering($by, $direction) + { + $dir = strtolower($direction) == 'desc' ? 'DESC' : 'ASC'; + return $this->getColumn($by)->getName(). ' '.$dir; + } } ?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/Vendor/TDbMetaDataCommon.php b/framework/Data/ActiveRecord/Vendor/TDbMetaDataCommon.php index 826654dc..a41e87ad 100644 --- a/framework/Data/ActiveRecord/Vendor/TDbMetaDataCommon.php +++ b/framework/Data/ActiveRecord/Vendor/TDbMetaDataCommon.php @@ -186,7 +186,7 @@ abstract class TDbMetaDataCommon extends TDbMetaData return $command; } - + /** * SQL command to delete records by criteria * @param TDbConnection database connection. diff --git a/framework/Data/ActiveRecord/Vendor/TIbmColumnMetaData.php b/framework/Data/ActiveRecord/Vendor/TIbmColumnMetaData.php new file mode 100644 index 00000000..65e40a59 --- /dev/null +++ b/framework/Data/ActiveRecord/Vendor/TIbmColumnMetaData.php @@ -0,0 +1,149 @@ + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2007 PradoSoft + * @license http://www.pradosoft.com/license/ + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + */ + +/** + * TIbmColumnMetaData class. + * + * Column details for IBM DB2 database. Using php_pdo_ibm.dll extension. + * + * @author Cesar Ramos + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + * @since 3.1 + */ +class TIbmColumnMetaData extends TComponent +{ + private $_name; + private $_type; + private $_length; + private $_autoIncrement; + private $_default; + private $_notNull=true; + + private $_isPrimary=null; + + private $_property; + + /** + * Initialize column meta data. + * + * @param string column name. + * @param string column data type. + * @param string column data length. + * @param boolean column can not be null. + * @param string serial name. + * @param string default value. + */ + public function __construct($name,$type,$length,$notNull,$autoIncrement,$default,$primary) + { + $this->_property = $name; + $this->_name=$name; + $this->_type=$type; + $this->_length=$length; + $this->_notNull=$notNull; + $this->_autoIncrement=$autoIncrement; + $this->_default=$default; + $this->_isPrimary=$primary; + } + + /** + * @return string quoted column name. + */ + public function getName() + { + return $this->_name; + } + + /** + * @return string active record property name + */ + public function getProperty() + { + return $this->_property; + } + + /** + * @return boolean true if column is a sequence, false otherwise. + */ + public function hasSequence() + { + return $this->_autoIncrement; + } + + /** + * @return null no sequence name. + */ + public function getSequenceName() + { + return null; + } + + /** + * @return boolean true if the column is a primary key, or part of a composite primary key. + */ + public function getIsPrimaryKey() + { + return $this->_isPrimary; + } + + /** + * @return string column type + */ + public function getType() + { + return $this->_type; + } + + + /** + * @return boolean false if column can be null, true otherwise. + */ + public function getNotNull() + { + return $this->_notNull; + } + + /** + * @return boolean true if column has default value, false otherwise. + */ + public function hasDefault() + { + return $this->_default !== null; + } + + /** + * @return string default column value. + */ + public function getDefaultValue() + { + return $this->_default; + } + + /** + * @return string PHP primative type derived from the column type. + */ + public function getPHPType() + { + switch(strtolower($this->_type)) + { + case 'smallint': case 'integer': + return 'integer'; + case 'real': case 'float': case 'double': case 'decimal': case 'bigint': + return 'float'; + default: + return 'string'; + } + } + +} + +?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/Vendor/TIbmMetaData.php b/framework/Data/ActiveRecord/Vendor/TIbmMetaData.php new file mode 100644 index 00000000..4f923406 --- /dev/null +++ b/framework/Data/ActiveRecord/Vendor/TIbmMetaData.php @@ -0,0 +1,80 @@ + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2007 PradoSoft + * @license http://www.pradosoft.com/license/ + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + */ +Prado::using('System.Data.ActiveRecord.Vendor.TDbMetaDataCommon'); + +/** + * TIbmMetaData class. + * + * Column details for IBM DB2 database. Using php_pdo_ibm.dll extension. + * + * Does not support LIMIT and OFFSET criterias. + * + * @author Cesar Ramos + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + * @since 3.1 + */ +class TIbmMetaData extends TDbMetaDataCommon +{ + /** + * Build the SQL search string from the criteria object for IBM DB2 database. + * @param TDbConnection database connection. + * @param TActiveRecordCriteria search criteria. + * @return string SQL search. + */ + protected function getSqlFromCriteria($conn, $criteria) + { + if($criteria===null) return ''; + $sql = ''; + if(($condition = $criteria->getCondition())!==null) + $sql .= ' WHERE '.$condition; + $orders=array(); + foreach($criteria->getOrdersBy() as $by=>$ordering) + $orders[] = $this->getOrdering($by, $ordering); + if(count($orders) > 0) + $sql .= ' ORDER BY '.implode(', ', $orders); + //if(($limit = $criteria->getLimit())!==null) + //{ + // $sql .= ' FETCH FIRST '.intval($limit).' ROWS ONLY'; + //} + return strlen($sql) > 0 ? $sql : ''; + } + + /** + * Lowercase the data keys, IBM DB2 returns uppercase column names + * @param mixed record row + * @return array record row + */ + public function postQueryRow($row) + { + if(!is_array($row)) return $row; + $result=array(); + foreach($row as $k=>$v) + $result[strtolower($k)]=$v; + return $result; + } + + /** + * Lowercase the data keys, IBM DB2 returns uppercase column names + * @param mixed record row + * @return array record row + */ + public function postQuery($rows) + { + $data = array(); + foreach($rows as $k=>$v) + $data[$k] = $this->postQueryRow($v); + return $data; + } +} +?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/Vendor/TIbmMetaDataInspector.php b/framework/Data/ActiveRecord/Vendor/TIbmMetaDataInspector.php new file mode 100644 index 00000000..0ccc05ae --- /dev/null +++ b/framework/Data/ActiveRecord/Vendor/TIbmMetaDataInspector.php @@ -0,0 +1,113 @@ + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2007 PradoSoft + * @license http://www.pradosoft.com/license/ + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + */ +Prado::using('System.Data.ActiveRecord.Vendor.TDbMetaDataInspector'); +Prado::using('System.Data.ActiveRecord.Vendor.TIbmColumnMetaData'); +Prado::using('System.Data.ActiveRecord.Vendor.TIbmMetaData'); + +/** + * TIbmMetaDataInspector class. + * + * Column details for IBM DB2 database. Using php_pdo_ibm.dll extension. + * + * @author Cesar Ramos + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + * @since 3.1 + */ +class TIbmMetaDataInspector extends TDbMetaDataInspector +{ + private $_schema; + + /** + * @param string default schema. + */ + public function setSchema($schema) + { + $this->_schema=$schema; + } + + /** + * @return string default schema. + */ + public function getSchema() + { + return $this->_schema; + } + /** + * Get the column definitions for given table. + * @param string table name. + * @return array column name value pairs of column meta data. + */ + protected function getColumnDefinitions($table) + { + if(count($parts= explode('.', $table)) > 1) + { + $tablename = $parts[1]; + $schema = $parts[0]; + } + else + { + $tablename = $parts[0]; + $schema = $this->getSchema(); + } + $sql="SELECT * FROM SYSCAT.COLUMNS WHERE TABNAME='".strtoupper($tablename)."'"; + if ($schema) + $sql=$sql." AND TABSCHEMA='".strtoupper($schema)."'"; + + $conn = $this->getDbConnection(); + $conn->setActive(true); + $command = $conn->createCommand($sql); + $command->prepare(); + $result=$command->query($sql); + foreach ($result as $col) + $cols[strtolower($col['COLNAME'])] = $this->getColumnMetaData($col); + return $cols; + } + + protected function getColumnMetaData($col) + { + $name = strtolower($col['COLNAME']); + $type = $col['TYPENAME']; + $length = $col['LENGTH']; + $notNull = $col['NULLS']==='N'; + $autoIncrement=$col['IDENTITY']==='N'; + $default = $col['DEFAULT']; + $primaryKey = $col['KEYSEQ']?1:0; + return new TIbmColumnMetaData($name,$type,$length,$notNull,$autoIncrement,$default,$primaryKey); + } + + /** + * Not implemented, IBM does not always have foreign key constraints. + */ + protected function getConstraintKeys($table) + { + return array('primary'=>array(), 'foreign'=>array()); + } + + /** + * Create a new instance of meta data. + * @param string table name + * @param array column meta data + * @param array primary key meta data + * @param array foreign key meta data. + * @return TDbMetaData table meta data. + */ + protected function createMetaData($table, $columns, $primary, $foreign) + { + $pks = array(); + foreach($columns as $name=>$column) + if($column->getIsPrimaryKey()) + $pks[] = $name; + return new TIbmMetaData($table,$columns,$pks); + } +} +?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/Vendor/TMysqlMetaData.php b/framework/Data/ActiveRecord/Vendor/TMysqlMetaData.php index 0cbd0b7c..03aba53f 100644 --- a/framework/Data/ActiveRecord/Vendor/TMysqlMetaData.php +++ b/framework/Data/ActiveRecord/Vendor/TMysqlMetaData.php @@ -1,4 +1,4 @@ - 0 ? $sql : ''; } - protected function getOrdering($by, $direction) + public function getSearchRegExpCriteria($fields, $keywords) { - $dir = strtolower($direction) == 'desc' ? 'DESC' : 'ASC'; - return $this->getColumn($by)->getName(). ' '.$dir; + if(strlen(trim($keywords)) == 0) return ''; + $words = preg_split('/\s/', preg_quote($keywords, '\'')); + $result = array(); + foreach($fields as $field) + { + $column = $this->getColumn($field); + $result[] = $this->getRegexpCriteriaStr($column->getName(), $words); + } + return '('.implode(' OR ', $result).')'; + } + + protected function getRegexpCriteriaStr($column, $words) + { + $regexp = implode('|', $words); + return "({$column} REGEXP '{$regexp}')"; } + } ?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/Vendor/TMysqlMetaDataInspector.php b/framework/Data/ActiveRecord/Vendor/TMysqlMetaDataInspector.php index 5e438e37..23c483d1 100644 --- a/framework/Data/ActiveRecord/Vendor/TMysqlMetaDataInspector.php +++ b/framework/Data/ActiveRecord/Vendor/TMysqlMetaDataInspector.php @@ -1,4 +1,4 @@ -createCommand($sql); $command->prepare(); foreach($command->query() as $col) - $cols[$col['Field']] = $this->getColumnMetaData($col); + $cols[strtolower($col['Field'])] = $this->getColumnMetaData($col); return $cols; } @@ -51,7 +51,8 @@ class TMysqlMetaDataInspector extends TDbMetaDataInspector $autoIncrement=is_int(strpos(strtolower($col['Extra']), 'auto_increment')); $default = $col['Default']; $primaryKey = $col['Key']==='PRI'; - return new TMysqlColumnMetaData($col['Field'],$name,$type,$notNull,$autoIncrement,$default,$primaryKey); + return new TMysqlColumnMetaData(strtolower($col['Field']),$name,$type, + $notNull,$autoIncrement,$default,$primaryKey); } /** diff --git a/framework/Data/ActiveRecord/Vendor/TPgsqlColumnMetaData.php b/framework/Data/ActiveRecord/Vendor/TPgsqlColumnMetaData.php index a51c435d..2774bb54 100644 --- a/framework/Data/ActiveRecord/Vendor/TPgsqlColumnMetaData.php +++ b/framework/Data/ActiveRecord/Vendor/TPgsqlColumnMetaData.php @@ -44,13 +44,30 @@ class TPgsqlColumnMetaData extends TComponent { $this->_property=$property; $this->_name=$name; - $this->_type=$type; $this->_length=$length; + $this->processType($type); $this->_notNull=$notNull; $this->_sequenceName=$serial; $this->_default=$default; } + protected function processType($type) + { + if(is_int($pos=strpos($type, '('))) + { + $match=array(); + if(preg_match('/\((.*)\)/', $type, $match)) + { + $this->_length=floatval($match[1]); + $this->_type = substr($type,0,$pos); + } + else + $this->_type = $type; + } + else + $this->_type = $type; + } + /** * @return string quoted column name. */ diff --git a/framework/Data/ActiveRecord/Vendor/TPgsqlMetaData.php b/framework/Data/ActiveRecord/Vendor/TPgsqlMetaData.php index d968267a..45e9c7e4 100644 --- a/framework/Data/ActiveRecord/Vendor/TPgsqlMetaData.php +++ b/framework/Data/ActiveRecord/Vendor/TPgsqlMetaData.php @@ -48,11 +48,31 @@ class TPgsqlMetaData extends TDbMetaDataCommon return strlen($sql) > 0 ? $sql : ''; } - protected function getOrdering($by, $direction) + public function getSearchRegExpCriteria($fields, $keywords) { - $dir = strtolower($direction) == 'desc' ? 'DESC' : 'ASC'; - return $this->getColumn($by)->getName(). ' '.$dir; + if(strlen(trim($keywords)) == 0) return ''; + $words = preg_split('/\s/', preg_quote($keywords, '\'')); + $result = array(); + foreach($fields as $field) + { + $column = $this->getColumn($field); + if($this->isSearchableColumn($column)) + $result[] = $this->getRegexpCriteriaStr($column->getName(), $words); + } + return '('.implode(' OR ', $result).')'; } + + protected function isSearchableColumn($column) + { + $type = strtolower($column->getType()); + return $type === 'character varying' || $type === 'varchar' || + $type === 'character' || $type === 'char' || $type === 'text'; + } + protected function getRegexpCriteriaStr($column, $words) + { + $regexp = implode('|', $words); + return "({$column} ~ '{$regexp}')"; + } } ?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/Vendor/TPgsqlMetaDataInspector.php b/framework/Data/ActiveRecord/Vendor/TPgsqlMetaDataInspector.php index 2f7202cf..16326353 100644 --- a/framework/Data/ActiveRecord/Vendor/TPgsqlMetaDataInspector.php +++ b/framework/Data/ActiveRecord/Vendor/TPgsqlMetaDataInspector.php @@ -130,7 +130,7 @@ EOD; $command->bindValue(':schema', $schema); $cols = array(); foreach($command->query() as $col) - $cols[$col['attname']] = $this->getColumnMetaData($schema,$col); + $cols[strtolower($col['attname'])] = $this->getColumnMetaData($schema,$col); return $cols; } @@ -148,19 +148,26 @@ EOD; // A specific constant in the 7.0 source, the length is offset by 4. $length = $col['atttypmod'] > 0 ? $col['atttypmod'] - 4 : null; $notNull = $col['attnotnull']; - $serial = $col['attisserial'] ? $schema.'.'.$this->getSerialName($col['adsrc']) : null; + $nextval_serial = substr($col['adsrc'],0,8) === 'nextval('; + $serial = $col['attisserial'] || $nextval_serial ? $this->getSerialName($schema,$col['adsrc']) : null; $default = $serial === null && $col['atthasdef'] ? $col['adsrc'] : null; - return new TPgsqlColumnMetaData($col['attname'],$name,$type,$length,$notNull,$serial,$default); + return new TPgsqlColumnMetaData(strtolower($col['attname']),$name, + $type,$length,$notNull,$serial,$default); } /** * @return string serial name if found, null otherwise. */ - protected function getSerialName($src) + protected function getSerialName($schema,$src) { $matches = array(); - if(preg_match('/nextval\(\'([^\']+)\'::regclass\)/i',$src,$matches)) - return $matches[1]; + if(preg_match('/nextval\([^\']*\'([^\']+)\'[^\)]*\)/i',$src,$matches)) + { + if(is_int(strpos($matches[1], '.'))) + return $matches[1]; + else + return $schema.'.'.$matches[1]; + } } /** diff --git a/framework/Data/ActiveRecord/Vendor/TSqliteMetaData.php b/framework/Data/ActiveRecord/Vendor/TSqliteMetaData.php index c82a99ad..c44d73cb 100644 --- a/framework/Data/ActiveRecord/Vendor/TSqliteMetaData.php +++ b/framework/Data/ActiveRecord/Vendor/TSqliteMetaData.php @@ -48,10 +48,23 @@ class TSqliteMetaData extends TDbMetaDataCommon return strlen($sql) > 0 ? $sql : ''; } - protected function getOrdering($by, $direction) + public function getSearchRegExpCriteria($fields, $keywords) { - $dir = strtolower($direction) == 'desc' ? 'DESC' : 'ASC'; - return $this->getColumn($by)->getName(). ' '.$dir; + if(strlen(trim($keywords)) == 0) return ''; + $words = array(); + preg_match_all('/([a-zA-Z0-9-+]+)/', $keywords, $words); + $result = array(); + foreach($fields as $field) + $result[] = $this->getLikeCriteriaStr($this->getColumn($field)->getName(), $words[0]); + return '('.implode(' OR ', $result).')'; + } + + protected function getLikeCriteriaStr($column, $words) + { + $result=array(); + foreach($words as $word) + $result[] = "({$column} LIKE \"%{$word}%\")"; + return '('.implode(' AND ', $result).')'; } /** diff --git a/framework/Data/ActiveRecord/Vendor/TSqliteMetaDataInspector.php b/framework/Data/ActiveRecord/Vendor/TSqliteMetaDataInspector.php index 3621c666..1d4599a8 100644 --- a/framework/Data/ActiveRecord/Vendor/TSqliteMetaDataInspector.php +++ b/framework/Data/ActiveRecord/Vendor/TSqliteMetaDataInspector.php @@ -55,7 +55,7 @@ class TSqliteMetaDataInspector extends TDbMetaDataInspector $command->prepare(); $cols = array(); foreach($command->query() as $col) - $cols[$col['name']] = $this->getColumnMetaData($col); + $cols[strtolower($col['name'])] = $this->getColumnMetaData($col); return $cols; } @@ -73,7 +73,8 @@ class TSqliteMetaDataInspector extends TDbMetaDataInspector $primary = $col['pk']==='1'; $autoIncrement = strtolower($type)==='integer' && $primary; $default = $col['dflt_value']; - return new TSqliteColumnMetaData($col['name'],$name,$type,$notNull,$autoIncrement,$default,$primary); + return new TSqliteColumnMetaData(strtolower($col['name']),$name,$type, + $notNull,$autoIncrement,$default,$primary); } /** -- cgit v1.2.3