1: <?php
2: /**
3: * CMysqlSchema class file.
4: *
5: * @author Qiang Xue <qiang.xue@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: * CMysqlSchema is the class for retrieving metadata information from a MySQL database (version 4.1.x and 5.x).
13: *
14: * @author Qiang Xue <qiang.xue@gmail.com>
15: * @package system.db.schema.mysql
16: * @since 1.0
17: */
18: class CMysqlSchema extends CDbSchema
19: {
20: /**
21: * @var array the abstract column types mapped to physical column types.
22: * @since 1.1.6
23: */
24: public $columnTypes=array(
25: 'pk' => 'int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY',
26: 'bigpk' => 'bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY',
27: 'string' => 'varchar(255)',
28: 'text' => 'text',
29: 'integer' => 'int(11)',
30: 'bigint' => 'bigint(20)',
31: 'float' => 'float',
32: 'decimal' => 'decimal',
33: 'datetime' => 'datetime',
34: 'timestamp' => 'timestamp',
35: 'time' => 'time',
36: 'date' => 'date',
37: 'binary' => 'blob',
38: 'boolean' => 'tinyint(1)',
39: 'money' => 'decimal(19,4)',
40: );
41:
42: /**
43: * Quotes a table name for use in a query.
44: * A simple table name does not schema prefix.
45: * @param string $name table name
46: * @return string the properly quoted table name
47: * @since 1.1.6
48: */
49: public function quoteSimpleTableName($name)
50: {
51: return '`'.$name.'`';
52: }
53:
54: /**
55: * Quotes a column name for use in a query.
56: * A simple column name does not contain prefix.
57: * @param string $name column name
58: * @return string the properly quoted column name
59: * @since 1.1.6
60: */
61: public function quoteSimpleColumnName($name)
62: {
63: return '`'.$name.'`';
64: }
65:
66: /**
67: * Compares two table names.
68: * The table names can be either quoted or unquoted. This method
69: * will consider both cases.
70: * @param string $name1 table name 1
71: * @param string $name2 table name 2
72: * @return boolean whether the two table names refer to the same table.
73: */
74: public function compareTableNames($name1,$name2)
75: {
76: return parent::compareTableNames(strtolower($name1),strtolower($name2));
77: }
78:
79: /**
80: * Resets the sequence value of a table's primary key.
81: * The sequence will be reset such that the primary key of the next new row inserted
82: * will have the specified value or max value of a primary key plus one (i.e. sequence trimming).
83: * @param CDbTableSchema $table the table schema whose primary key sequence will be reset
84: * @param integer|null $value the value for the primary key of the next new row inserted.
85: * If this is not set, the next new row's primary key will have the max value of a primary
86: * key plus one (i.e. sequence trimming).
87: * @since 1.1
88: */
89: public function resetSequence($table,$value=null)
90: {
91: if($table->sequenceName===null)
92: return;
93: if($value!==null)
94: $value=(int)$value;
95: else
96: {
97: $value=(int)$this->getDbConnection()
98: ->createCommand("SELECT MAX(`{$table->primaryKey}`) FROM {$table->rawName}")
99: ->queryScalar();
100: $value++;
101: }
102: $this->getDbConnection()
103: ->createCommand("ALTER TABLE {$table->rawName} AUTO_INCREMENT=$value")
104: ->execute();
105: }
106:
107: /**
108: * Enables or disables integrity check.
109: * @param boolean $check whether to turn on or off the integrity check.
110: * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema.
111: * @since 1.1
112: */
113: public function checkIntegrity($check=true,$schema='')
114: {
115: $this->getDbConnection()->createCommand('SET FOREIGN_KEY_CHECKS='.($check?1:0))->execute();
116: }
117:
118: /**
119: * Loads the metadata for the specified table.
120: * @param string $name table name
121: * @return CMysqlTableSchema driver dependent table metadata. Null if the table does not exist.
122: */
123: protected function loadTable($name)
124: {
125: $table=new CMysqlTableSchema;
126: $this->resolveTableNames($table,$name);
127:
128: if($this->findColumns($table))
129: {
130: $this->findConstraints($table);
131: return $table;
132: }
133: else
134: return null;
135: }
136:
137: /**
138: * Generates various kinds of table names.
139: * @param CMysqlTableSchema $table the table instance
140: * @param string $name the unquoted table name
141: */
142: protected function resolveTableNames($table,$name)
143: {
144: $parts=explode('.',str_replace(array('`','"'),'',$name));
145: if(isset($parts[1]))
146: {
147: $table->schemaName=$parts[0];
148: $table->name=$parts[1];
149: $table->rawName=$this->quoteTableName($table->schemaName).'.'.$this->quoteTableName($table->name);
150: }
151: else
152: {
153: $table->name=$parts[0];
154: $table->rawName=$this->quoteTableName($table->name);
155: }
156: }
157:
158: /**
159: * Collects the table column metadata.
160: * @param CMysqlTableSchema $table the table metadata
161: * @return boolean whether the table exists in the database
162: */
163: protected function findColumns($table)
164: {
165: $sql='SHOW FULL COLUMNS FROM '.$table->rawName;
166: try
167: {
168: $columns=$this->getDbConnection()->createCommand($sql)->queryAll();
169: }
170: catch(Exception $e)
171: {
172: return false;
173: }
174: foreach($columns as $column)
175: {
176: $c=$this->createColumn($column);
177: $table->columns[$c->name]=$c;
178: if($c->isPrimaryKey)
179: {
180: if($table->primaryKey===null)
181: $table->primaryKey=$c->name;
182: elseif(is_string($table->primaryKey))
183: $table->primaryKey=array($table->primaryKey,$c->name);
184: else
185: $table->primaryKey[]=$c->name;
186: if($c->autoIncrement)
187: $table->sequenceName='';
188: }
189: }
190: return true;
191: }
192:
193: /**
194: * Creates a table column.
195: * @param array $column column metadata
196: * @return CDbColumnSchema normalized column metadata
197: */
198: protected function createColumn($column)
199: {
200: $c=new CMysqlColumnSchema;
201: $c->name=$column['Field'];
202: $c->rawName=$this->quoteColumnName($c->name);
203: $c->allowNull=$column['Null']==='YES';
204: $c->isPrimaryKey=strpos($column['Key'],'PRI')!==false;
205: $c->isForeignKey=false;
206: $c->init($column['Type'],$column['Default']);
207: $c->autoIncrement=strpos(strtolower($column['Extra']),'auto_increment')!==false;
208: if(isset($column['Comment']))
209: $c->comment=$column['Comment'];
210:
211: return $c;
212: }
213:
214: /**
215: * @return float server version.
216: */
217: protected function getServerVersion()
218: {
219: $version=$this->getDbConnection()->getAttribute(PDO::ATTR_SERVER_VERSION);
220: $digits=array();
221: preg_match('/(\d+)\.(\d+)\.(\d+)/', $version, $digits);
222: return floatval($digits[1].'.'.$digits[2].$digits[3]);
223: }
224:
225: /**
226: * Collects the foreign key column details for the given table.
227: * @param CMysqlTableSchema $table the table metadata
228: */
229: protected function findConstraints($table)
230: {
231: $row=$this->getDbConnection()->createCommand('SHOW CREATE TABLE '.$table->rawName)->queryRow();
232: $matches=array();
233: $regexp='/FOREIGN KEY\s+\(([^\)]+)\)\s+REFERENCES\s+([^\(^\s]+)\s*\(([^\)]+)\)/mi';
234: foreach($row as $sql)
235: {
236: if(preg_match_all($regexp,$sql,$matches,PREG_SET_ORDER))
237: break;
238: }
239: foreach($matches as $match)
240: {
241: $keys=array_map('trim',explode(',',str_replace(array('`','"'),'',$match[1])));
242: $fks=array_map('trim',explode(',',str_replace(array('`','"'),'',$match[3])));
243: foreach($keys as $k=>$name)
244: {
245: $table->foreignKeys[$name]=array(str_replace(array('`','"'),'',$match[2]),$fks[$k]);
246: if(isset($table->columns[$name]))
247: $table->columns[$name]->isForeignKey=true;
248: }
249: }
250: }
251:
252: /**
253: * Returns all table names in the database.
254: * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema.
255: * If not empty, the returned table names will be prefixed with the schema name.
256: * @return array all table names in the database.
257: */
258: protected function findTableNames($schema='')
259: {
260: if($schema==='')
261: return $this->getDbConnection()->createCommand('SHOW TABLES')->queryColumn();
262: $names=$this->getDbConnection()->createCommand('SHOW TABLES FROM '.$this->quoteTableName($schema))->queryColumn();
263: foreach($names as &$name)
264: $name=$schema.'.'.$name;
265: return $names;
266: }
267:
268: /**
269: * Creates a command builder for the database.
270: * This method overrides parent implementation in order to create a MySQL specific command builder
271: * @return CDbCommandBuilder command builder instance
272: * @since 1.1.13
273: */
274: protected function createCommandBuilder()
275: {
276: return new CMysqlCommandBuilder($this);
277: }
278:
279: /**
280: * Builds a SQL statement for renaming a column.
281: * @param string $table the table whose column is to be renamed. The name will be properly quoted by the method.
282: * @param string $name the old name of the column. The name will be properly quoted by the method.
283: * @param string $newName the new name of the column. The name will be properly quoted by the method.
284: * @throws CDbException if specified column is not found in given table
285: * @return string the SQL statement for renaming a DB column.
286: * @since 1.1.6
287: */
288: public function renameColumn($table, $name, $newName)
289: {
290: $db=$this->getDbConnection();
291: $row=$db->createCommand('SHOW CREATE TABLE '.$db->quoteTableName($table))->queryRow();
292: if($row===false)
293: throw new CDbException(Yii::t('yii','Unable to find "{column}" in table "{table}".',array('{column}'=>$name,'{table}'=>$table)));
294: if(isset($row['Create Table']))
295: $sql=$row['Create Table'];
296: else
297: {
298: $row=array_values($row);
299: $sql=$row[1];
300: }
301: if(preg_match_all('/^\s*[`"](.*?)[`"]\s+(.*?),?$/m',$sql,$matches))
302: {
303: foreach($matches[1] as $i=>$c)
304: {
305: if($c===$name)
306: {
307: return "ALTER TABLE ".$db->quoteTableName($table)
308: . " CHANGE ".$db->quoteColumnName($name)
309: . ' '.$db->quoteColumnName($newName).' '.$matches[2][$i];
310: }
311: }
312: }
313:
314: // try to give back a SQL anyway
315: return "ALTER TABLE ".$db->quoteTableName($table)
316: . " CHANGE ".$db->quoteColumnName($name).' '.$newName;
317: }
318:
319: /**
320: * Builds a SQL statement for dropping a foreign key constraint.
321: * @param string $name the name of the foreign key constraint to be dropped. The name will be properly quoted by the method.
322: * @param string $table the table whose foreign is to be dropped. The name will be properly quoted by the method.
323: * @return string the SQL statement for dropping a foreign key constraint.
324: * @since 1.1.6
325: */
326: public function dropForeignKey($name, $table)
327: {
328: return 'ALTER TABLE '.$this->quoteTableName($table)
329: .' DROP FOREIGN KEY '.$this->quoteColumnName($name);
330: }
331:
332:
333: /**
334: * Builds a SQL statement for removing a primary key constraint to an existing table.
335: * @param string $name the name of the primary key constraint to be removed.
336: * @param string $table the table that the primary key constraint will be removed from.
337: * @return string the SQL statement for removing a primary key constraint from an existing table.
338: * @since 1.1.13
339: */
340: public function dropPrimaryKey($name,$table)
341: {
342: return 'ALTER TABLE ' . $this->quoteTableName($table) . ' DROP PRIMARY KEY';
343:
344: }
345:
346: /**
347: * Builds a SQL statement for adding a primary key constraint to a table.
348: * @param string $name not used in the MySQL syntax, the primary key is always called PRIMARY and is reserved.
349: * @param string $table the table that the primary key constraint will be added to.
350: * @param string|array $columns comma separated string or array of columns that the primary key will consist of.
351: * @return string the SQL statement for adding a primary key constraint to an existing table.
352: * @since 1.1.14
353: */
354: public function addPrimaryKey($name,$table,$columns)
355: {
356: if(is_string($columns))
357: $columns=preg_split('/\s*,\s*/',$columns,-1,PREG_SPLIT_NO_EMPTY);
358: foreach($columns as $i=>$col)
359: $columns[$i]=$this->quoteColumnName($col);
360: return 'ALTER TABLE ' . $this->quoteTableName($table) . ' ADD PRIMARY KEY ('
361: . implode(', ', $columns). ' )';
362: }
363: }
364: