Exponential 6.0.13 Exponential Enterprise Open Source CMS - PHP Class Reference

ezmysqlschema.php
Go to the documentation of this file.
1 <?php
18 class eZMysqlSchema extends eZDBSchemaInterface
19 {
20 function schema( $params = array() )
21 {
22 $params = array_merge( array( 'meta_data' => false,
23 'format' => 'generic',
24 'sort_columns' => true,
25 'sort_indexes' => true ),
26 $params );
27 $schema = array();
28
29 if ( $this->Schema === false )
30 {
31 $tableArray = $this->DBInstance->arrayQuery( "SHOW TABLES" );
32
33 foreach( $tableArray as $tableNameArray )
34 {
35 $table_name = current( $tableNameArray );
36 if ( !isset( $params['table_include'] ) or
37 ( is_array( $params['table_include'] ) and
38 in_array( $table_name, $params['table_include'] ) ) )
39 {
40 $schema_table['name'] = $table_name;
41 $schema_table['fields'] = $this->fetchTableFields( $table_name, $params );
42 $schema_table['indexes'] = $this->fetchTableIndexes( $table_name, $params );
43
44 $schema[$table_name] = $schema_table;
45 }
46 }
47 $this->transformSchema( $schema, $params['format'] == 'local' );
48 ksort( $schema );
49 $this->Schema = $schema;
50 }
51 else
52 {
53 $this->transformSchema( $this->Schema, $params['format'] == 'local' );
54 $schema = $this->Schema;
55 }
56 return $schema;
57 }
58
64 function fetchTableFields( $table, $params )
65 {
66 $fields = array();
67
68 $resultArray = $this->DBInstance->arrayQuery( "DESCRIBE $table" );
69
70 foreach( $resultArray as $row )
71 {
72 $field = array();
73 $field['type'] = $this->parseType ( $row['Type'], $field['length'] );
74 if ( !$field['length'] )
75 {
76 unset( $field['length'] );
77 }
78 $field['not_null'] = 0;
79 if ( $row['Null'] != 'YES' )
80 {
81 $field['not_null'] = '1';
82 }
83 $field['default'] = false;
84 if ( !$field['not_null'] )
85 {
86 if ( $row['Default'] === null )
87 $field['default'] = null;
88 else
89 $field['default'] = (string)$row['Default'];
90 }
91 else
92 {
93 $field['default'] = (string)$row['Default'];
94 }
95
96 $numericTypes = array( 'float', 'int' );
97 $blobTypes = array( 'tinytext', 'text', 'mediumtext', 'longtext' );
98 $charTypes = array( 'varchar', 'char' );
99 if ( in_array( $field['type'], $charTypes ) )
100 {
101 if ( !$field['not_null'] )
102 {
103 if ( $field['default'] === null )
104 {
105 $field['default'] = null;
106 }
107 else if ( $field['default'] === false )
108 {
109 $field['default'] = '';
110 }
111 }
112 }
113 else if ( in_array( $field['type'], $numericTypes ) )
114 {
115 if ( $field['default'] === false )
116 {
117 if ( $field['not_null'] )
118 {
119 $field['default'] = 0;
120 }
121 }
122 else if ( $field['type'] == 'int' )
123 {
124 if ( $field['not_null'] or
125 is_numeric( $field['default'] ) )
126 $field['default'] = (int)$field['default'];
127 }
128 else if ( $field['type'] == 'float' or
129 is_numeric( $field['default'] ) )
130 {
131 if ( $field['not_null'] or
132 is_numeric( $field['default'] ) )
133 $field['default'] = (float)$field['default'];
134 }
135 }
136 else if ( in_array( $field['type'], $blobTypes ) )
137 {
138 // We do not want default for blobs.
139 $field['default'] = false;
140 }
141
142 if ( strpos ( $row['Extra'], 'auto_increment' ) !== false )
143 {
144 unset( $field['length'] );
145 $field['not_null'] = 0;
146 $field['default'] = false;
147 $field['type'] = 'auto_increment';
148 }
149
150 if ( !$field['not_null'] )
151 unset( $field['not_null'] );
152
153 $fields[$row['Field']] = $field;
154 }
155 if ( $params['sort_columns'] )
156 {
157 ksort( $fields );
158 }
159
160 return $fields;
161 }
162
166 function fetchTableIndexes( $table, $params )
167 {
168 $metaData = false;
169 if ( isset( $params['meta_data'] ) )
170 {
171 $metaData = $params['meta_data'];
172 }
173
174 $indexes = array();
175
176 $resultArray = $this->DBInstance->arrayQuery( "SHOW INDEX FROM $table" );
177
178 foreach( $resultArray as $row )
179 {
180 $kn = $row['Key_name'];
181
182 if ( $kn == 'PRIMARY' )
183 {
184 $indexes[$kn]['type'] = 'primary';
185 }
186 else
187 {
188 $indexes[$kn]['type'] = $row['Non_unique'] ? 'non-unique' : 'unique';
189 }
190
191 $indexFieldDef = array( 'name' => $row['Column_name'] );
192
193 // Include length if one is defined
194 if ( $row['Sub_part'] )
195 {
196 $indexFieldDef['mysql:length'] = (int)$row['Sub_part'];
197 }
198
199 // Check if we have any entries other than 'name', if not we skip the array definition
200 if ( count( array_diff( array_keys( $indexFieldDef ), array( 'name' ) ) ) == 0 )
201 {
202 $indexFieldDef = $indexFieldDef['name'];
203 }
204 $indexes[$kn]['fields'][$row['Seq_in_index'] - 1] = $indexFieldDef;
205 }
206 if ( $params['sort_indexes'] )
207 {
208 ksort( $indexes );
209 }
210
211 return $indexes;
212 }
213
214 function parseType( $type_info, &$length_info )
215 {
216 preg_match ( "@([a-z ]*)(\‍(([0-9]*|[0-9]*,[0-9]*)\‍))?@", $type_info, $matches );
217 if ( isset( $matches[3] ) )
218 {
219 $length_info = $matches[3];
220 if ( is_numeric( $length_info ) )
221 $length_info = (int)$length_info;
222 }
223 return $matches[1];
224 }
225
229 function generateAddIndexSql( $table_name, $index_name, $def, $params, $isEmbedded = false )
230 {
231 $diffFriendly = isset( $params['diff_friendly'] ) ? $params['diff_friendly'] : false;
232 // If the output should compatible with existing MySQL dumps
233 $mysqlCompatible = isset( $params['compatible_sql'] ) ? $params['compatible_sql'] : false;
234 $sql = '';
235
236 // Will be set to true when primary key is inside CREATE TABLE
237 if ( !$isEmbedded )
238 {
239 $sql .= "ALTER TABLE $table_name ADD";
240 $sql .= " ";
241 }
242
243 switch ( $def['type'] )
244 {
245 case 'primary':
246 {
247 $sql .= 'PRIMARY KEY';
248 if ( $mysqlCompatible )
249 $sql .= " ";
250 } break;
251
252 case 'non-unique':
253 {
254 if ( $isEmbedded )
255 {
256 $sql .= "KEY $index_name";
257 }
258 else
259 {
260 $sql .= "INDEX $index_name";
261 }
262 } break;
263
264 case 'unique':
265 {
266 if ( $isEmbedded )
267 {
268 $sql .= "UNIQUE KEY $index_name";
269 }
270 else
271 {
272 $sql .= "UNIQUE $index_name";
273 }
274 } break;
275 }
276
277 $sql .= ( $diffFriendly ? " (\n " : ( $mysqlCompatible ? " (" : " ( " ) );
278 $fields = $def['fields'];
279 $i = 0;
280 foreach ( $fields as $fieldDef )
281 {
282 if ( $i > 0 )
283 {
284 $sql .= $diffFriendly ? ",\n " : ( $mysqlCompatible ? ',' : ', ' );
285 }
286 if ( is_array( $fieldDef ) )
287 {
288 $sql .= $fieldDef['name'];
289 if ( isset( $fieldDef['mysql:length'] ) )
290 {
291 if ( $diffFriendly )
292 {
293 $sql .= "(\n";
294 $sql .= " " . str_repeat( ' ', strlen( $fieldDef['name'] ) );
295 }
296 else
297 {
298 $sql .= $mysqlCompatible ? "(" : "( ";
299 }
300 $sql .= $fieldDef['mysql:length'];
301 if ( $diffFriendly )
302 {
303 $sql .= ")";
304 }
305 else
306 {
307 $sql .= $mysqlCompatible ? ")" : " )";
308 }
309 }
310 }
311 else
312 {
313 $sql .= $fieldDef;
314 }
315 ++$i;
316 }
317 $sql .= ( $diffFriendly ? "\n)" : ( $mysqlCompatible ? ")" : " )" ) );
318
319 if ( !$isEmbedded )
320 {
321 return $sql . ";\n";
322 }
323 return $sql;
324 }
325
329 function generateDropIndexSql( $table_name, $index_name, $def, $params )
330 {
331 $sql = '';
332 $sql .= "ALTER TABLE $table_name DROP ";
333
334 if ( $def['type'] == 'primary' )
335 {
336 $sql .= 'PRIMARY KEY';
337 }
338 else
339 {
340 $sql .= "INDEX $index_name";
341 }
342 return $sql . ";\n";
343 }
344
348 function generateFieldDef( $field_name, $def, &$skip_primary, $params = null )
349 {
350 $diffFriendly = isset( $params['diff_friendly'] ) ? $params['diff_friendly'] : false;
351 // If the output should compatible with existing MySQL dumps
352 $mysqlCompatible = isset( $params['compatible_sql'] ) ? $params['compatible_sql'] : false;
353
354 $sql_def = $field_name . ' ';
355 $defaultText = $mysqlCompatible ? "default" : "DEFAULT";
356
357 if ( $def['type'] != 'auto_increment' )
358 {
359 $defList = array();
360 $type = $def['type'];
361 if ( isset( $def['length'] ) )
362 {
363 $type .= "({$def['length']})";
364 }
365 $defList[] = $type;
366 if ( isset( $def['not_null'] ) && ( $def['not_null'] ) )
367 {
368 $defList[] = 'NOT NULL';
369 }
370 if ( array_key_exists( 'default', $def ) )
371 {
372 if ( $def['default'] === null )
373 {
374 $defList[] = "$defaultText NULL";
375 }
376 else if ( $def['default'] !== false )
377 {
378 $defList[] = "$defaultText '{$def['default']}'";
379 }
380 }
381 else if ( $def['type'] == 'varchar' )
382 {
383 $defList[] = "$defaultText ''";
384 }
385 $sql_def .= join( $diffFriendly ? "\n " : " ", $defList );
386 $skip_primary = false;
387 }
388 else
389 {
390 $incrementText = $mysqlCompatible ? "auto_increment" : "AUTO_INCREMENT";
391 if ( $diffFriendly )
392 {
393 $sql_def .= "int(11)\n NOT NULL\n $incrementText";
394 }
395 else
396 {
397 $sql_def .= "int(11) NOT NULL $incrementText";
398 }
399 $skip_primary = true;
400 }
401 return $sql_def;
402 }
403
407 function generateAddFieldSql( $table_name, $field_name, $def, $params )
408 {
409 $sql = "ALTER TABLE $table_name ADD COLUMN ";
410 $sql .= eZMysqlSchema::generateFieldDef ( $field_name, $def, $dummy );
411
412 return $sql . ";\n";
413 }
414
418 function generateAlterFieldSql( $table_name, $field_name, $def, $params )
419 {
420 $sql = "ALTER TABLE $table_name CHANGE COLUMN $field_name ";
421 $sql .= eZMysqlSchema::generateFieldDef ( $field_name, $def, $dummy );
422
423 return $sql . ";\n";
424 }
425
429 function generateTableSchema( $tableName, $table, $params )
430 {
431 return $this->generateTableSQL( $tableName, $table, $params, false, false );
432 }
433
437 function generateTableSQLList( $tableName, $table, $params, $separateTypes )
438 {
439 return $this->generateTableSQL( $tableName, $table, $params, true, $separateTypes );
440 }
441
449 function generateTableSQL( $tableName, $tableDef, $params, $asArray, $separateTypes = false )
450 {
451 $diffFriendly = isset( $params['diff_friendly'] ) ? $params['diff_friendly'] : false;
452 $mysqlCompatible = isset( $params['compatible_sql'] ) ? $params['compatible_sql'] : false;
453
454 if ( $asArray )
455 {
456 if ( $separateTypes )
457 {
458 $sqlList = array( 'tables' => array() );
459 }
460 else
461 {
462 $sqlList = array();
463 }
464 }
465
466 $sql = '';
467 $skip_pk = false;
468 $sql_fields = array();
469 $sql .= "CREATE TABLE $tableName (\n";
470
471 $fields = $tableDef['fields'];
472
473 foreach ( $fields as $field_name => $field_def )
474 {
475 $sql_fields[] = ' ' . eZMysqlSchema::generateFieldDef( $field_name, $field_def, $skip_pk_flag, $params );
476 if ( $skip_pk_flag )
477 {
478 $skip_pk = true;
479 }
480 }
481
482 // Make sure the order is as defined by 'offset'
483 $indexes = $tableDef['indexes'];
484
485 // We need to add all keys in table definition
486 foreach ( $indexes as $index_name => $index_def )
487 {
488 $sql_fields[] = ( $diffFriendly ? '' : ' ' ) . eZMysqlSchema::generateAddIndexSql( $tableName, $index_name, $index_def, $params, true );
489 }
490 $sql .= join( ",\n", $sql_fields );
491 $sql .= "\n)";
492
493 // Add some extra table options if they are required
494 $extraOptions = array();
495 if ( isset( $params['table_type'] ) and $params['table_type'] )
496 {
497 $typeName = $this->tableStorageTypeName( $params['table_type'] );
498 if ( $typeName )
499 {
500 $extraOptions[] = "ENGINE=" . $typeName;
501 }
502 }
503 if ( isset( $params['table_charset'] ) and $params['table_charset'] )
504 {
505 $charsetName = $this->tableCharsetName( $params['table_charset'] );
506 if ( $charsetName )
507 {
508 $extraOptions[] = "DEFAULT CHARACTER SET " . $charsetName;
509 }
510 }
511 if ( isset( $tableDef['options'] ) )
512 {
513 foreach( $tableDef['options'] as $optionType => $optionValue )
514 {
515 $optionText = $this->generateTableOption( $tableName, $tableDef, $optionType, $optionValue, $params );
516 if ( $optionText )
517 $extraOptions[] = $optionText;
518 }
519 }
520
521 if ( count( $extraOptions ) > 0 )
522 {
523 $sql .= " " . implode( $diffFriendly ? "\n" : " ", $extraOptions );
524 }
525
526 if ( $asArray )
527 {
528 if ( $separateTypes )
529 {
530 $sqlList['tables'][] = $sql;
531 }
532 else
533 {
534 $sqlList[] = $sql;
535 }
536 }
537 else
538 {
539 $sql .= ";\n";
540
541 if ( $mysqlCompatible )
542 {
543 $sql .= "\n\n\n";
544 }
545 }
546
547 return $asArray ? $sqlList : $sql;
548 }
549
556 function generateTableOption( $tableName, $tableDef, $optionType, $optionValue, $params )
557 {
558 switch ( $optionType )
559 {
560 case 'mysql:delay_key_write':
561 {
562 if ( $optionValue )
563 return 'DELAY_KEY_WRITE=1';
564 } break;
565 }
566 return false;
567 }
568
572 function tableCharsetName( $charset )
573 {
574 $charset = eZCharsetInfo::realCharsetCode( $charset );
575 // Convert charset names into something MySQL will understand
576 $charsetMapping = array( 'iso-8859-1' => 'latin1',
577 'iso-8859-2' => 'latin2',
578 'iso-8859-8' => 'hebrew',
579 'iso-8859-7' => 'greek',
580 'iso-8859-9' => 'latin5',
581 'iso-8859-13' => 'latin7',
582 'windows-1250' => 'cp1250',
583 'windows-1251' => 'cp1251',
584 'windows-1256' => 'cp1256',
585 'windows-1257' => 'cp1257',
586 'utf-8' => 'utf8',
587 'koi8-r' => 'koi8r',
588 'koi8-u' => 'koi8u' );
589 $charset = strtolower( $charset );
590 if ( isset( $charsetMapping[$charset] ) )
591 return $charsetMapping[$charset];
592 return $charset;
593 }
594
602 function tableStorageTypeName( $type )
603 {
604 $type = strtolower( $type );
605 switch ( $type )
606 {
607 case 'bdb':
608 {
609 return 'BDB';
610 }
611
612 case 'myisam':
613 {
614 return 'MyISAM';
615 }
616
617 case 'innodb':
618 {
619 return 'InnoDB';
620 }
621 }
622 return false;
623 }
624
628 function generateDropTable( $table, $params )
629 {
630 return "DROP TABLE $table;\n";
631 }
632
638 function isMultiInsertSupported()
639 {
640 if ( $this->DBInstance instanceof eZDBInterface )
641 {
642 $versionInfo = $this->DBInstance->databaseServerVersion();
643
644 // We require MySQL 3.22.5 to use multi-insert queries
645 // http://dev.mysql.com/doc/mysql/en/INSERT.html
646 return ( version_compare( $versionInfo['string'], '3.22.5' ) >= 0 );
647 }
648 return true;
649 }
650
651 function escapeSQLString( $value )
652 {
653 if ( $this->DBInstance instanceof eZMySQLiDB )
654 {
655 return $this->DBInstance->escapeString( $value );
656 }
657
658 return mysqli_real_escape_string( $value );
659 }
660
661 function schemaType()
662 {
663 return 'mysql';
664 }
665
666 function schemaName()
667 {
668 return 'MySQL';
669 }
670
671 }
672 ?>
eZCharsetInfo\realCharsetCode
static realCharsetCode( $charsetCode)
Definition: ezcharsetinfo.php:186
eZDBInterface
Definition: ezdbinterface.php:20
eZDBSchemaInterface
This class provide interface for DB schema library.
Definition: ezdbschemainterface.php:62
eZDBSchemaInterface\$Schema
$Schema
Definition: ezdbschemainterface.php:1373
eZDBSchemaInterface\transformSchema
transformSchema(&$schema, $toLocal)
Definition: ezdbschemainterface.php:1046
eZMySQLiDB
The eZMySQLiDB class provides MySQL implementation of the database interface.
Definition: ezmysqlidb.php:21
eZMysqlSchema
Handles schemas for MySQL.
Definition: ezmysqlschema.php:19
eZMysqlSchema\escapeSQLString
escapeSQLString( $value)
Definition: ezmysqlschema.php:651
eZMysqlSchema\schemaName
schemaName()
Definition: ezmysqlschema.php:666
eZMysqlSchema\fetchTableFields
fetchTableFields( $table, $params)
Definition: ezmysqlschema.php:64
eZMysqlSchema\isMultiInsertSupported
isMultiInsertSupported()
Definition: ezmysqlschema.php:638
eZMysqlSchema\generateTableOption
generateTableOption( $tableName, $tableDef, $optionType, $optionValue, $params)
Definition: ezmysqlschema.php:556
eZMysqlSchema\schema
schema( $params=array())
Definition: ezmysqlschema.php:20
eZMysqlSchema\tableCharsetName
tableCharsetName( $charset)
Definition: ezmysqlschema.php:572
eZMysqlSchema\tableStorageTypeName
tableStorageTypeName( $type)
Definition: ezmysqlschema.php:602
eZMysqlSchema\schemaType
schemaType()
Definition: ezmysqlschema.php:661
eZMysqlSchema\generateAddIndexSql
generateAddIndexSql( $table_name, $index_name, $def, $params, $isEmbedded=false)
Definition: ezmysqlschema.php:229
eZMysqlSchema\parseType
parseType( $type_info, &$length_info)
Definition: ezmysqlschema.php:214
eZMysqlSchema\generateTableSQLList
generateTableSQLList( $tableName, $table, $params, $separateTypes)
Definition: ezmysqlschema.php:437
eZMysqlSchema\generateFieldDef
generateFieldDef( $field_name, $def, &$skip_primary, $params=null)
Definition: ezmysqlschema.php:348
eZMysqlSchema\generateDropTable
generateDropTable( $table, $params)
Definition: ezmysqlschema.php:628
eZMysqlSchema\generateAlterFieldSql
generateAlterFieldSql( $table_name, $field_name, $def, $params)
Definition: ezmysqlschema.php:418
eZMysqlSchema\fetchTableIndexes
fetchTableIndexes( $table, $params)
Definition: ezmysqlschema.php:166
eZMysqlSchema\generateTableSQL
generateTableSQL( $tableName, $tableDef, $params, $asArray, $separateTypes=false)
Definition: ezmysqlschema.php:449
eZMysqlSchema\generateAddFieldSql
generateAddFieldSql( $table_name, $field_name, $def, $params)
Definition: ezmysqlschema.php:407
eZMysqlSchema\generateDropIndexSql
generateDropIndexSql( $table_name, $index_name, $def, $params)
Definition: ezmysqlschema.php:329
eZMysqlSchema\generateTableSchema
generateTableSchema( $tableName, $table, $params)
Definition: ezmysqlschema.php:429
Copyright © 1998 – 2026 7x. All rights reserved. Licensed under the GNU GPLv2+.