1: <?php
  2: /**
  3:  * CCubridSchema class file.
  4:  *
  5:  * @author Esen Sagynov <kadismal@gmail.com>
  6:  * @link http://www.yiiframework.com/
  7:  * @copyright 2008-2013 Yii Software LLC
  8:  * @license http://www.yiiframework.com/license/
  9:  */
 10: 
 11: /**
 12:  * CCubridSchema is the class for retrieving metadata information from a CUBRID database (version 8.4.0 and later).
 13:  *
 14:  * @author Esen Sagynov <kadismal@gmail.com>
 15:  * @package system.db.schema.cubrid
 16:  * @since 1.1.16
 17:  */
 18: class CCubridSchema extends CDbSchema
 19: {
 20:     public $columnTypes=array(
 21:         'pk' => 'INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY',
 22:         // same as STRING or CHARACTER VARYING
 23:         'string' => 'VARCHAR(255)',
 24:         'text' => 'VARCHAR(65535)',
 25:         'integer' => 'INTEGER',
 26:         'float' => 'NUMERIC',
 27:         'real' => 'NUMERIC',
 28:         'decimal' => 'NUMERIC',
 29:         'datetime' => 'DATETIME',
 30:         'timestamp' => 'TIMESTAMP',
 31:         'time' => 'TIME',
 32:         'date' => 'DATE',
 33:         'binary' => 'BIT VARYING',
 34:         'bool' => 'SHORT',
 35:         'boolean' => 'SHORT',
 36:         'money' => 'NUMERIC(19,4)',
 37:     );
 38: 
 39:     /**
 40:     * Quotes a table name for use in a query.
 41:     * A simple table name does not schema prefix.
 42:     * @param string $name table name
 43:     * @return string the properly quoted table name
 44:     */
 45:     public function quoteSimpleTableName($name)
 46:     {
 47:         return '`'.$name.'`';
 48:     }
 49: 
 50:     /**
 51:     * Quotes a column name for use in a query.
 52:     * A simple column name does not contain prefix.
 53:     * @param string $name column name
 54:     * @return string the properly quoted column name
 55:     */
 56:     public function quoteSimpleColumnName($name)
 57:     {
 58:         return '`'.$name.'`';
 59:     }
 60: 
 61:     /**
 62:      * Compares two table names.
 63:      * The table names can be either quoted or unquoted. This method
 64:      * will consider both cases.
 65:      * @param string $name1 table name 1
 66:      * @param string $name2 table name 2
 67:      * @return boolean whether the two table names refer to the same table.
 68:      */
 69:     public function compareTableNames($name1,$name2)
 70:     {
 71:         return parent::compareTableNames(strtolower($name1),strtolower($name2));
 72:     }
 73: 
 74:     /**
 75:      * Resets the sequence value of a table's primary key.
 76:      * The sequence will be reset such that the primary key of the next new row inserted
 77:      * will have the specified value or 1.
 78:      * @param CDbTableSchema $table the table schema whose primary key sequence will be reset
 79:      * @param mixed $value the value for the primary key of the next new row inserted. If this is not set,
 80:      * the next new row's primary key will have a value 1.
 81:      */
 82:     public function resetSequence($table,$value=null)
 83:     {
 84:         if($table->sequenceName!==null)
 85:         {
 86:             if($value===null)
 87:                 $value=$this->getDbConnection()->createCommand("SELECT MAX(`{$table->primaryKey}`) FROM {$table->rawName}")->queryScalar()+1;
 88:             else
 89:                 $value=(int)$value;
 90:             $this->getDbConnection()->createCommand("ALTER TABLE {$table->rawName} AUTO_INCREMENT=$value")->execute();
 91:         }
 92:     }
 93: 
 94:     /**
 95:      * Creates a table instance representing the metadata for the named table.
 96:      * @param string $name table name
 97:      * @return CCubridTableSchema driver dependent table metadata. Null if the table does not exist.
 98:      */
 99:     protected function loadTable($name)
100:     {
101:         $table=new CCubridTableSchema;
102:         $this->resolveTableNames($table,$name);
103: 
104:         if($this->findColumns($table))
105:         {
106:             $this->findPrimaryKeys($table);
107:             $this->findConstraints($table);
108:             return $table;
109:         }
110:         else
111:             return null;
112:     }
113: 
114:     /**
115:      * Generates various kinds of table names.
116:      * @param CCubridTableSchema $table the table instance
117:      * @param string $name the unquoted table name
118:      */
119:     protected function resolveTableNames($table,$name)
120:     {
121:         $parts=explode('.',str_replace('`','',$name));
122:         if(isset($parts[1]))
123:         {
124:             $table->schemaName=$parts[0];
125:             $table->name=$parts[1];
126:             $table->rawName=$this->quoteTableName($table->schemaName).'.'.$this->quoteTableName($table->name);
127:         }
128:         else
129:         {
130:             $table->name=$parts[0];
131:             $table->rawName=$this->quoteTableName($table->name);
132:         }
133:     }
134: 
135:     /**
136:      * Collects the table column metadata.
137:      * @param CCubridTableSchema $table the table metadata
138:      * @return boolean whether the table exists in the database
139:      */
140:     protected function findColumns($table)
141:     {
142:         // it may be good to use CUBRID PHP API to retrieve column info.
143:         $sql='SHOW COLUMNS FROM '.$table->rawName;
144:         try
145:         {
146:             $columns=$this->getDbConnection()->createCommand($sql)->queryAll();
147:         }
148:         catch(Exception $e)
149:         {
150:             return false;
151:         }
152:         foreach($columns as $column)
153:         {
154:             $c=$this->createColumn($column);
155:             $table->columns[$c->name]=$c;
156:         }
157:         return true;
158:     }
159: 
160:     /**
161:      * Creates a table column.
162:      * @param array $column column metadata
163:      * @return CDbColumnSchema normalized column metadata
164:      */
165:     protected function createColumn($column)
166:     {
167:         $c=new CCubridColumnSchema;
168:         $c->name=$column['Field'];
169:         $c->rawName=$this->quoteColumnName($c->name);
170:         $c->allowNull=$column['Null']==='YES';
171:         $c->isPrimaryKey=strpos($column['Key'],'PRI')!==false;
172:         $c->isForeignKey=false;
173:         $c->init($column['Type'],$column['Default']);
174:         $c->autoIncrement=strpos(strtolower($column['Extra']),'auto_increment')!==false;
175: 
176:         return $c;
177:     }
178: 
179:     /**
180:      * @return float server version.
181:      */
182:     protected function getServerVersion()
183:     {
184:         $version=$this->getDbConnection()->getAttribute(PDO::ATTR_SERVER_VERSION);
185:         $digits=array();
186:         preg_match('/(\d+)\.(\d+)\.(\d+).(\d+)/', $version, $digits);
187:         return floatval($digits[1].'.'.$digits[2].$digits[3].'.'.$digits[4]);
188:     }
189: 
190:     /**
191:      * Collects the foreign key column details for the given table.
192:      * @param CCubridTableSchema $table the table metadata
193:      */
194:     protected function findConstraints($table)
195:     {
196:         $schemas=$this->getDbConnection()->getPdoInstance()->cubrid_schema(PDO::CUBRID_SCH_IMPORTED_KEYS,$table->name);
197: 
198:         foreach($schemas as $schema)
199:         {
200:             $table->foreignKeys[$schema["FKCOLUMN_NAME"]]=array($schema["PKTABLE_NAME"],$schema["PKCOLUMN_NAME"]);
201:             if(isset($table->columns[$schema["FKCOLUMN_NAME"]]))
202:                 $table->columns[$schema["FKCOLUMN_NAME"]]->isForeignKey=true;
203:         }
204:     }
205: 
206:     /**
207:      * Collects the primary key column details for the given table.
208:      * @param CCubridTableSchema $table the table metadata
209:      */
210:     protected function findPrimaryKeys($table)
211:     {
212:         $pks=$this->getDbConnection()->getPdoInstance()->cubrid_schema(PDO::CUBRID_SCH_PRIMARY_KEY,$table->name);
213: 
214:         foreach($pks as $pk)
215:         {
216:             $c = $table->columns[$pk['ATTR_NAME']];
217:             $c->isPrimaryKey = true;
218: 
219:             if($table->primaryKey===null)
220:                 $table->primaryKey=$c->name;
221:             elseif(is_string($table->primaryKey))
222:                 $table->primaryKey=array($table->primaryKey,$c->name);
223:             else
224:                 $table->primaryKey[]=$c->name;
225:             if($c->autoIncrement)
226:                 $table->sequenceName='';
227:         }
228:     }
229: 
230:     /**
231:      * Returns all table names in the database.
232:      * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema.
233:      * If not empty, the returned table names will be prefixed with the schema name.
234:      * @return array all table names in the database.
235:      */
236:     protected function findTableNames($schema='')
237:     {
238:         // CUBRID does not allow to look into another database from within another connection.
239:         // If necessary user has to establish a connection to that particular database and
240:         // query to show all tables. For this reason if a user executes this funtion
241:         // we will return all table names of the currently connected database.
242:         return $this->getDbConnection()->createCommand('SHOW TABLES')->queryColumn();
243:     }
244: }
245: