Exponential 6.0.13 Exponential Enterprise Open Source CMS - PHP Class Reference

ezpgsqlschema.php
Go to the documentation of this file.
1 <?php
18 class eZPgsqlSchema extends eZDBSchemaInterface
19 {
20 const SHOW_TABLES_QUERY = '
21 SELECT n.nspname as "Schema",
22 c.relname as "Name",
23 CASE c.relkind
24 WHEN \'r\' THEN \'table\'
25 WHEN \'v\' THEN \'view\'
26 WHEN \'i\' THEN \'index\'
27 WHEN \'S\' THEN \'sequence\'
28 WHEN \'s\' THEN \'special\'
29 END as "Type",
30 u.usename as "Owner"
31 FROM pg_catalog.pg_class c
32 LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
33 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
34 WHERE c.relkind IN (\'r\',\'\')
35 AND n.nspname NOT IN (\'pg_catalog\', \'pg_toast\')
36 AND pg_catalog.pg_table_is_visible(c.oid)
37 ORDER BY 1, 2';
38
39 const FETCH_TABLE_OID_QUERY = '
40 SELECT c.oid,
41 n.nspname,
42 c.relname
43 FROM pg_catalog.pg_class c
44 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
45 WHERE pg_catalog.pg_table_is_visible(c.oid)
46 AND c.relname ~ \'^<<tablename>>$\'
47 ORDER BY 2, 3';
48
49 const FETCH_TABLE_DEF_QUERY = '
50 SELECT a.attname,
51 pg_catalog.format_type(a.atttypid, a.atttypmod),
52 (SELECT substring(pg_get_expr(d.adbin, d.adrelid) for 128) FROM pg_catalog.pg_attrdef d
53 WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as default,
54 a.attnotnull, a.attnum
55 FROM pg_catalog.pg_attribute a
56 WHERE a.attrelid = \'<<oid>>\' AND a.attnum > 0 AND NOT a.attisdropped
57 ORDER BY a.attnum';
58
59 const FETCH_INDEX_DEF_QUERY = '
60 SELECT c.relname, i.*
61 FROM pg_catalog.pg_index i, pg_catalog.pg_class c
62 WHERE indrelid = \'<<oid>>\'
63 AND i.indexrelid = c.oid';
64
65 const FETCH_INDEX_COL_NAMES_QUERY = '
66 SELECT a.attnum, a.attname
67 FROM pg_catalog.pg_attribute a
68 WHERE a.attrelid = \'<<indexrelid>>\' AND a.attnum IN (<<attids>>) AND NOT a.attisdropped
69 ORDER BY a.attnum';
70
71 function schema( $params = array() )
72 {
73 $params = array_merge( array( 'meta_data' => false,
74 'format' => 'generic',
75 'sort_columns' => true,
76 'sort_indexes' => true ),
77 $params );
78 $schema = array();
79
80 if ( $this->Schema === false )
81 {
82 $resultArray = $this->DBInstance->arrayQuery( eZPgsqlSchema::SHOW_TABLES_QUERY );
83
84 foreach( $resultArray as $row )
85 {
86 $table_name = $row['Name'];
87 if ( !isset( $params['table_include'] ) or
88 ( is_array( $params['table_include'] ) and
89 in_array( $table_name, $params['table_include'] ) ) )
90 {
91 $schema_table['name'] = $table_name;
92 $schema_table['fields'] = $this->fetchTableFields( $table_name, $params );
93 $schema_table['indexes'] = $this->fetchTableIndexes( $table_name, $params );
94
95 $schema[$table_name] = $schema_table;
96 }
97 }
98 $this->transformSchema( $schema, $params['format'] == 'local' );
99 ksort( $schema );
100 $this->Schema = $schema;
101 }
102 else
103 {
104 $this->transformSchema( $this->Schema, $params['format'] == 'local' );
105 $schema = $this->Schema;
106 }
107 return $schema;
108 }
109
113 function fetchTableFields( $table, $params )
114 {
115 $fields = array();
116
117 $resultArray = $this->DBInstance->arrayQuery( str_replace( '<<tablename>>', $table, eZPgsqlSchema::FETCH_TABLE_OID_QUERY ) );
118 $row = $resultArray[0];
119 $oid = $row['oid'];
120
121 $resultArray = $this->DBInstance->arrayQuery( str_replace( '<<oid>>', $oid, eZPgsqlSchema::FETCH_TABLE_DEF_QUERY ) );
122 foreach( $resultArray as $row )
123 {
124 $field = array();
125 $autoinc = false;
126 $field['type'] = $this->parseType( $row['format_type'], $field['length'] );
127 if ( !$field['length'] )
128 {
129 unset( $field['length'] );
130 }
131
132 $field['not_null'] = 0;
133 if ( $row['attnotnull'] == 't' )
134 {
135 $field['not_null'] = '1';
136 }
137
138 $field['default'] = false;
139 if ( !$field['not_null'] )
140 {
141 if ( $row['default'] === null )
142 $field['default'] = null;
143 else
144 $field['default'] = $this->parseDefault ( $row['default'], $autoinc );
145 }
146 else
147 {
148 $field['default'] = (string)$this->parseDefault ( $row['default'], $autoinc );
149 }
150
151 $numericTypes = array( 'float', 'int' );
152 $blobTypes = array( 'tinytext', 'text', 'mediumtext', 'longtext' );
153 $charTypes = array( 'varchar', 'char' );
154 if ( in_array( $field['type'], $charTypes ) )
155 {
156 if ( !$field['not_null'] )
157 {
158 if ( $field['default'] === null )
159 {
160 $field['default'] = null;
161 }
162 else if ( $field['default'] === false )
163 {
164 $field['default'] = '';
165 }
166 }
167 }
168 else if ( in_array( $field['type'], $numericTypes ) )
169 {
170 if ( $field['default'] === false )
171 {
172 if ( $field['not_null'] )
173 {
174 $field['default'] = 0;
175 }
176 }
177 else if ( $field['type'] == 'int' )
178 {
179 if ( $field['not_null'] or
180 is_numeric( $field['default'] ) )
181 {
182 $field['default'] = (int)$field['default'];
183 }
184 }
185 else if ( $field['type'] == 'float' )
186 {
187 if ( $field['not_null'] or
188 is_numeric( $field['default'] ) )
189 {
190 $field['default'] = (float)$field['default'];
191 }
192 }
193 }
194 else if ( in_array( $field['type'], $blobTypes ) )
195 {
196 // We do not want default for blobs.
197 $field['default'] = false;
198 }
199
200 if ( $autoinc )
201 {
202 unset( $field['length'] );
203 $field['not_null'] = 0;
204 $field['default'] = false;
205 $field['type'] = 'auto_increment';
206 }
207
208 if ( !$field['not_null'] )
209 unset( $field['not_null'] );
210
211 $fields[$row['attname']] = $field;
212 }
213 if ( $params['sort_columns'] )
214 {
215 ksort( $fields );
216 }
217
218 return $fields;
219 }
220
224 function fetchTableIndexes( $table, $params )
225 {
226 $metaData = false;
227 if ( isset( $params['meta_data'] ) )
228 {
229 $metaData = $params['meta_data'];
230 }
231
232 $indexes = array();
233
234 $resultArray = $this->DBInstance->arrayQuery( str_replace( '<<tablename>>', $table, eZPgsqlSchema::FETCH_TABLE_OID_QUERY ) );
235 $row = $resultArray[0];
236 $oid = $row['oid'];
237
238 $resultArray = $this->DBInstance->arrayQuery( str_replace( '<<oid>>', $oid, eZPgsqlSchema::FETCH_INDEX_DEF_QUERY ) );
239
240 foreach( $resultArray as $row )
241 {
242 $fields = array();
243 $kn = $row['relname'];
244
245 $column_id_array = explode( ' ', $row['indkey'] );
246 if ( $row['indisprimary'] == 't' )
247 {
248 // If the name of the key matches our primary key naming standard
249 // we change the name to PRIMARY, this makes it 100% similar to
250 // primary keys in MySQL
251 $correctName = $this->primaryKeyIndexName( $table, $kn, $column_id_array );
252 if ( strlen( $correctName ) > 63 )
253 {
254 eZDebug::writeError( "The index name '$correctName' (" . strlen( $correctName ) . ") exceeds 63 characters which is the PostgreSQL limit for names" );
255 }
256 if ( $kn == $correctName )
257 {
258 $kn = 'PRIMARY';
259 }
260
261 // Extra meta data:
262 // Include the name of the index that postgresql will use
263 if ( $metaData )
264 {
265 $indexes[$kn]['postgresql:name'] = $correctName;
266 }
267
268 $indexes[$kn]['type'] = 'primary';
269 }
270 else
271 {
272 $indexes[$kn]['type'] = $row['indisunique'] == 't' ? 'unique' : 'non-unique';
273 }
274
275 /* getting fieldnames requires yet another query and it doesn't return it 'in order' either.
276 * grumbl, stupid pgsql :) */
277 $att_ids = join( ', ', $column_id_array );
278 $query = str_replace( '<<indexrelid>>', $row['indrelid'], eZPgsqlSchema::FETCH_INDEX_COL_NAMES_QUERY );
279 $query = str_replace( '<<attids>>', $att_ids, $query );
280
281 $fieldsArray = $this->DBInstance->arrayQuery( $query );
282 foreach( $fieldsArray as $fields_row )
283 {
284 $fields[$fields_row['attnum']] = $fields_row['attname'];
285 }
286 foreach ( $column_id_array as $rank => $id )
287 {
288 $indexes[$kn]['fields'][$rank] = $fields[$id];
289 }
290 }
291 if ( $params['sort_indexes'] )
292 {
293 ksort( $indexes );
294 }
295
296 return $indexes;
297 }
298
299 function parseType( $type_info, &$length_info )
300 {
301 preg_match ( "@([a-z ]*)(\‍(([0-9]*|[0-9]*,[0-9]*)\‍))?@", $type_info, $matches );
302 if ( isset( $matches[3] ) )
303 {
304 $length_info = $matches[3];
305 if ( is_numeric( $length_info ) )
306 $length_info = (int)$length_info;
307 }
308 $type = $this->convertToStandardType ( $matches[1], $length_info );
309 return $type;
310 }
311
312 function isTypeLengthSupported( $pgType )
313 {
314 switch ( $pgType )
315 {
316 case 'integer':
317 case 'double precision':
318 case 'real':
319 case 'bigint':
320 {
321 return false;
322 } break;
323 }
324 return true;
325 }
326
327 function convertFromStandardType( $type, &$length )
328 {
329 switch ( $type )
330 {
331 case 'char':
332 return 'character';
333
334 case 'int':
335 return 'integer';
336
337 case 'bigint':
338 return 'bigint';
339
340 case 'varchar':
341 return 'character varying';
342
343 case 'longtext':
344 return 'text';
345
346 case 'mediumtext':
347 return 'text';
348
349 case 'text':
350 return 'text';
351
352 case 'float':
353 return 'real';
354
355 case 'double':
356 return 'double precision';
357
358 case 'decimal':
359 return 'numeric';
360
361 default:
362 die ( "ERROR UNHANDLED TYPE: $type\n" );
363 }
364 }
365
373 function primaryKeyIndexName( $tableName, $indexName, $fields )
374 {
375 return $tableName . '_pkey';
376 }
377
378 function convertToStandardType( $type, &$length )
379 {
380 switch ( $type )
381 {
382 case 'bigint':
383 $length = 20;
384 return 'bigint';
385
386 case 'integer':
387 $length = 11;
388 return 'int';
389
390 case 'character varying':
391 return 'varchar';
392
393 case 'text':
394 return 'longtext';
395
396 case 'double precision':
397 return 'double';
398
399 case 'real':
400 return 'float';
401
402 case 'character':
403 return 'char';
404
405 case 'numeric':
406 return 'decimal';
407
408 default:
409 die ( "ERROR UNHANDLED TYPE: $type\n" );
410 }
411 }
412
413 function parseDefault( $default, &$autoinc )
414 {
415 if ( preg_match( "@^NULL::.*@", $default, $matches ) )
416 {
417 return null;
418 }
419
420 // postgresql 7.x: nextval('ezbasket_id_seq'::text)
421 // postgresql 8.x: nextval(('ezbasket_id_seq'::text)::regclass)
422 if ( preg_match( "@^nextval\‍(\‍(?'([a-z_]+_id_seq)'::text\‍)@", $default, $matches ) )
423 {
424 $autoinc = 1;
425 return '';
426 }
427
428 if ( preg_match( "@^\‍(?([^()]*)\‍)?::double precision@", $default, $matches ) )
429 {
430 return $matches[1];
431 }
432
433 if ( preg_match( "@^(.*)::bigint@", $default, $matches ) )
434 {
435 return $matches[1];
436 }
437
438 if ( preg_match( "@^'(.*)'::character\ varying$@", $default, $matches ) )
439 {
440 return $matches[1];
441 }
442
443 if ( preg_match( "@^'(.*)'::[a-zA-Z ]+$@", $default, $matches ) )
444 {
445 return $matches[1];
446 }
447
448 if ( preg_match( "@^'(.*)'$@", $default, $matches ) )
449 {
450 return $matches[1];
451 }
452
453 return $default;
454 }
455
464 function generateAddIndexSql( $table_name, $index_name, $def, $params, $withClosure = true )
465 {
466 $diffFriendly = isset( $params['diff_friendly'] ) ? $params['diff_friendly'] : false;
467 $postgresqlCompatible = isset( $params['compatible_sql'] ) ? $params['compatible_sql'] : false;
468
469 $spacing = $postgresqlCompatible ? "\n " : " ";
470 switch ( $def['type'] )
471 {
472 case 'primary':
473 {
474 $pkeyName = $this->primaryKeyIndexName( $table_name, $index_name, $def['fields'] );
475 if ( strlen( $pkeyName ) > 63 )
476 {
477 eZDebug::writeError( "The primary key '$pkeyName' (" . strlen( $pkeyName ) . ") exceeds 63 characters which is the PostgreSQL limit for names" );
478 }
479 $sql = "ALTER TABLE ONLY $table_name" . $spacing . "ADD CONSTRAINT $pkeyName PRIMARY KEY";
480 } break;
481
482 case 'non-unique':
483 {
484 $sql = "CREATE INDEX $index_name ON $table_name USING btree";
485 } break;
486
487 case 'unique':
488 {
489 $sql = "CREATE UNIQUE INDEX $index_name ON $table_name USING btree";
490 } break;
491 }
492
493 $sql .= ( $diffFriendly ? " (\n " : ( $postgresqlCompatible ? ' (' : ' ( ' ) );
494 $i = 0;
495 foreach ( $def['fields'] as $fieldDef )
496 {
497 if ( $i > 0 )
498 {
499 $sql .= $diffFriendly ? ",\n " : ', ';
500 }
501 if ( is_array( $fieldDef ) )
502 {
503 $fieldName = $fieldDef['name'];
504 }
505 else
506 {
507 $fieldName = $fieldDef;
508 }
509 if ( in_array( $fieldName, $this->reservedKeywordList() ) )
510 {
511 $sql .= '"' . $fieldName . '"';
512 }
513 else
514 {
515 $sql .= $fieldName;
516 }
517 ++$i;
518 }
519
520 $sql .= ( $diffFriendly ? "\n)" : ( $postgresqlCompatible ? ')' : ' )' ) );
521
522 return $sql . ( $withClosure ? ";\n" : "" );
523 }
524
528 function generateDropIndexSql( $table_name, $index_name, $def, $withClosure = true )
529 {
530 if ($def['type'] == 'primary' )
531 {
532 $sql = "ALTER TABLE $table_name DROP CONSTRAINT "
533 . $this->primaryKeyIndexName( $table_name, $index_name, $def['fields'] );
534 }
535 else
536 {
537 $sql = "DROP INDEX $index_name";
538 }
539 return $sql . ( $withClosure ? ";\n" : "" );
540 }
541
545 function generateFieldDef( $table_name, $field_name, $def, $add_default_not_null = true, $params )
546 {
547 $diffFriendly = isset( $params['diff_friendly'] ) ? $params['diff_friendly'] : false;
548
549 if ( in_array( $field_name, $this->reservedKeywordList() ) )
550 {
551 $sql_def = '"' . $field_name . '"';
552 }
553 else
554 {
555 $sql_def = $field_name;
556 }
557
558 $sql_def .= ( $diffFriendly ? "\n " : " " );
559 if ( $def['type'] != 'auto_increment' )
560 {
561 $pgType = eZPgsqlSchema::convertFromStandardType( $def['type'], $def['length'] );
562 $sql_def .= $pgType;
563 if ( eZPgsqlSchema::isTypeLengthSupported( $pgType ) and isset( $def['length'] ) && $def['length'] )
564 {
565 $sql_def .= "({$def['length']})";
566 }
567 if ( $add_default_not_null )
568 {
569 $defaultDef = eZPGSQLSchema::generateDefaultDef( false, false, $def, $params );
570 if ( $defaultDef )
571 {
572 $sql_def .= ( $diffFriendly ? "\n " : " " );
573 $sql_def .= rtrim( $defaultDef );
574 }
575 $nullDef = eZPGSQLSchema::generateNullDef( false, false, $def, $params );
576 if ( $nullDef )
577 {
578 $sql_def .= ( $diffFriendly ? "\n " : " " );
579 $sql_def .= trim( $nullDef );
580 }
581 }
582 }
583 else
584 {
585 if ( $diffFriendly )
586 {
587 $sql_def .= "integer\n DEFAULT nextval('{$table_name}_{$field_name}_seq'::text)\n NOT NULL";
588 }
589 else
590 {
591 $sql_def .= "integer DEFAULT nextval('{$table_name}_{$field_name}_seq'::text) NOT NULL";
592 }
593 }
594 return $sql_def;
595 }
596
600 function generateDefaultDef( $table_name, $field_name, $def, $params )
601 {
602 $postgresqlCompatible = isset( $params['compatible_sql'] ) ? $params['compatible_sql'] : false;
603 $sql_def = '';
604 if ( $table_name and $field_name )
605 {
606 $sql_def .= "ALTER TABLE $table_name ALTER $field_name SET ";
607 }
608 if ( array_key_exists( 'default', $def ) and
609 $def['default'] !== false )
610 {
611 if ( $def['default'] === null )
612 {
613 if ( !$postgresqlCompatible )
614 $sql_def .= "DEFAULT NULL ";
615 }
616 else if ( $def['default'] !== false )
617 {
618 if ( $def['type'] == 'int' )
619 {
620 $sql_def .= "DEFAULT {$def['default']} ";
621 }
622 else if ( $def['type'] == 'float' )
623 {
624 $sql_def .= "DEFAULT {$def['default']}::real ";
625 }
626 else if ( $def['type'] == 'double' )
627 {
628 $sql_def .= "DEFAULT {$def['default']}::double precision ";
629 }
630 else if ( $def['type'] == 'varchar' )
631 {
632 $sql_def .= "DEFAULT '{$def['default']}'::character varying ";
633 }
634 else if ( $def['type'] == 'char' )
635 {
636 $sql_def .= "DEFAULT '{$def['default']}'::bpchar ";
637 }
638 else
639 {
640 $sql_def .= "DEFAULT '{$def['default']}' ";
641 }
642 }
643 }
644 else if ( $table_name and $field_name )
645 {
646 return false;
647 }
648 return $sql_def;
649 }
650
654 function generateNullDef( $table_name, $field_name, $def, $params )
655 {
656 $sql_def = '';
657 if ( $table_name and $field_name )
658 {
659 $sql_def .= "ALTER TABLE $table_name ALTER $field_name SET ";
660 }
661 if ( isset( $def['not_null'] ) && ( $def['not_null'] ) )
662 {
663 $sql_def .= 'NOT NULL ';
664 }
665 else if ( $table_name and $field_name )
666 {
667 return false;
668 }
669 return $sql_def;
670 }
671
675 function generateAddFieldSql( $table_name, $field_name, $def, $params )
676 {
677 $sql = "ALTER TABLE $table_name ADD COLUMN ";
678 $sql .= eZPgsqlSchema::generateFieldDef( $table_name, $field_name, $def, false, $params ) . ";\n";
679 $defaultSQL = eZPGSQLSchema::generateDefaultDef( $table_name, $field_name, $def, $params );
680 if ( $defaultSQL )
681 $sql .= $defaultSQL . ";\n";
682 $nullSQL = eZPGSQLSchema::generateNullDef( $table_name, $field_name, $def, $params );
683 if ( $nullSQL )
684 $sql .= $nullSQL . ";\n";
685 $sql .= "\n";
686 return $sql;
687 }
688
692 function generateDropFieldSql( $table_name, $field_name, $params )
693 {
694 if ( in_array( $field_name, $this->reservedKeywordList() ) )
695 {
696 $field_name = '"' . $field_name . '"';
697 }
698
699 $sql = "ALTER TABLE $table_name DROP COLUMN $field_name";
700
701 return $sql . ";\n";
702 }
703
707 function generateAlterFieldSql( $table_name, $field_name, $def, $params )
708 {
709 $sql = "ALTER TABLE $table_name RENAME COLUMN $field_name TO " . $field_name . "_tmp;\n";
710 $sql .= "ALTER TABLE $table_name ADD COLUMN ";
711 $sql .= eZPgsqlSchema::generateFieldDef( $table_name, $field_name, $def, false, $params ) . ";\n";
712 $defaultSQL = eZPGSQLSchema::generateDefaultDef( $table_name, $field_name, $def, $params );
713 if ( $defaultSQL )
714 $sql .= $defaultSQL . ";\n";
715 $nullSQL = eZPGSQLSchema::generateNullDef( $table_name, $field_name, $def, $params );
716 if ( $nullSQL )
717 $sql .= $nullSQL . ";\n";
718 $sql .= "UPDATE $table_name SET $field_name=" . $field_name . "_tmp;\n";
719 $sql .= "ALTER TABLE $table_name DROP COLUMN " . $field_name . "_tmp;\n\n";
720 return $sql;
721 }
722
723 function generateTableSchema( $table, $table_def, $params )
724 {
725 $arrays = $this->generateTableArrays( $table, $table_def, $params, true );
726 return ( join( "\n\n", $arrays['sequences'] ) . "\n" .
727 join( "\n\n", $arrays['tables'] ) . "\n" .
728 join( "\n\n", $arrays['indexes'] ) . "\n" .
729 join( "\n\n", $arrays['constraints'] ) . "\n" );
730 }
731
732 function generateTableSQLList( $table, $table_def, $params, $separateTypes )
733 {
734 $arrays = $this->generateTableArrays( $table, $table_def, $params, false );
735
736 // If we have to separate the types the current array is sufficient
737 if ( $separateTypes )
738 return $arrays;
739 return array_merge( $arrays['sequences'],
740 $arrays['tables'],
741 $arrays['indexes'],
742 $arrays['constraints'] );
743 }
744
752 function generateTableArrays( $table, $table_def, $params, $withClosure = true )
753 {
754 $diffFriendly = isset( $params['diff_friendly'] ) ? $params['diff_friendly'] : false;
755 $postgresqlCompatible = isset( $params['compatible_sql'] ) ? $params['compatible_sql'] : false;
756
757 $arrays = array( 'sequences' => array(),
758 'tables' => array(),
759 'indexes' => array(),
760 'constraints' => array() );
761
762 $sql_fields = array();
763
764 $spacing = $postgresqlCompatible ? ' ' : ' ';
765
766 // First we need to check if we use auto increment fields as
767 // sequences need to exist before we use them
768 foreach ( $table_def['fields'] as $field_name => $field_def )
769 {
770 if ( $field_def['type'] == 'auto_increment' )
771 {
772 $sequenceFields = array( "CREATE SEQUENCE {$table}_{$field_name}_seq",
773 "START 1",
774 "INCREMENT 1",
775 "MAXVALUE 9223372036854775807",
776 "MINVALUE 1",
777 "CACHE 1" );
778 $arrays['sequences'][] = join( "\n$spacing", $sequenceFields ) . ( $withClosure ? ';' : '' );
779 }
780 }
781
782 $sql = "CREATE TABLE $table (\n";
783 $fields = $table_def['fields'];
784 foreach ( $fields as $field_name => $field_def )
785 {
786 $sql_fields[] = $spacing . eZPgsqlSchema::generateFieldDef( $table, $field_name, $field_def, true, $params );
787 }
788 $sql .= join( ",\n", $sql_fields ) . ( $withClosure ? "\n);" : "\n)" );
789 $arrays['tables'][] = $sql;
790
791 foreach ( $table_def['indexes'] as $index_name => $index_def )
792 {
793 if ( $index_def['type'] != 'primary' )
794 {
795 $arrays['indexes'][] = eZPgsqlSchema::generateAddIndexSql( $table, $index_name, $index_def, $params, $withClosure );
796 }
797 }
798 foreach ( $table_def['indexes'] as $index_name => $index_def )
799 {
800 if ( $index_def['type'] == 'primary' )
801 {
802 $arrays['constraints'][] = eZPgsqlSchema::generateAddIndexSql( $table, $index_name, $index_def, $params, $withClosure );
803 }
804 }
805
806 return $arrays;
807 }
808
809
815 function generateTableInsertSQLList( $tableName, $tableDef, $dataEntries, $params, $withClosure = true )
816 {
817 $sqlList = eZDBSchemaInterface::generateTableInsertSQLList( $tableName, $tableDef, $dataEntries, $params, $withClosure );
818
819 foreach ( $tableDef['fields'] as $fieldName => $fieldDef )
820 {
821 if ( $fieldDef['type'] == 'auto_increment' )
822 {
823 $sql = "SELECT setval('" . $tableName . "_{$fieldName}_seq',max(" . $fieldName . ")+1) FROM " . $tableName;
824 if ( $withClosure )
825 $sql .= ";";
826 $sqlList[] = $sql;
827 }
828 }
829 return $sqlList;
830 }
831
832 function generateSchemaFile( $schema, $params = array() )
833 {
834 $sql = '';
835 $postgresqlCompatible = isset( $params['compatible_sql'] ) ? $params['compatible_sql'] : false;
836
837 $i = 0;
838 $allArrays = array( 'sequences' => array(),
839 'tables' => array(),
840 'indexes' => array(),
841 'constraints' => array() );
842
843 foreach ( $schema as $table => $tableDef )
844 {
845 // Skip the info structure, this is not a table
846 if ( $table == '_info' )
847 continue;
848
849 $arrays = $this->generateTableArrays( $table, $tableDef, $params, true );
850 if ( $postgresqlCompatible )
851 {
852 $allArrays['sequences'] = array_merge( $allArrays['sequences'],
853 $arrays['sequences'] );
854 $allArrays['tables'] = array_merge( $allArrays['tables'],
855 $arrays['tables'] );
856 $allArrays['indexes'] = array_merge( $allArrays['indexes'],
857 $arrays['indexes'] );
858 $allArrays['constraints'] = array_merge( $allArrays['constraints'],
859 $arrays['constraints'] );
860 }
861 else
862 {
863 if ( $i > 0 )
864 $sql .= "\n\n";
865 ++$i;
866
867 $sql .= ( join( "\n", $arrays['sequences'] ) . "\n" .
868 join( "\n", $arrays['tables'] ) . "\n" .
869 join( "\n", $arrays['indexes'] ) . "\n" .
870 join( "\n", $arrays['constraints'] ) . "\n" );
871 }
872 }
873
874 if ( $postgresqlCompatible )
875 {
876 $sql = ( str_repeat( "\n", 11 ) .
877 join( str_repeat( "\n", 8 ), $allArrays['sequences'] ) . str_repeat( "\n", 8 ) .
878 join( str_repeat( "\n", 8 ), $allArrays['tables'] ) . str_repeat( "\n", 8 ) .
879 join( str_repeat( "\n", 7 ), $allArrays['indexes'] ) . str_repeat( "\n", 8 ) .
880 join( str_repeat( "\n", 7 ), $allArrays['constraints'] ) . str_repeat( "\n", 8 ) );
881 }
882
883 return $sql;
884 }
885
889 function generateDropTable( $table )
890 {
891 return "DROP TABLE $table;\n";
892 }
893
894 function escapeSQLString( $value )
895 {
896 return pg_escape_string( $value );
897 }
898
899 function schemaType()
900 {
901 return 'postgresql';
902 }
903
904 function schemaName()
905 {
906 return 'PostgreSQL';
907 }
908
912 function reservedKeywordList()
913 {
914 return array( 'abort',
915 'absolute',
916 'access',
917 'action',
918 'add',
919 'after',
920 'aggregate',
921 'all',
922 'alter',
923 'analyse',
924 'analyze',
925 'and',
926 'any',
927 'as',
928 'asc',
929 'assertion',
930 'assignment',
931 'at',
932 'authorization',
933 'backward',
934 'before',
935 'begin',
936 'between',
937 'bigint',
938 'binary',
939 'bit',
940 'boolean',
941 'both',
942 'by',
943 'cache',
944 'called',
945 'cascade',
946 'case',
947 'cast',
948 'chain',
949 'char',
950 'character',
951 'characteristics',
952 'check',
953 'checkpoint',
954 'class',
955 'close',
956 'cluster',
957 'coalesce',
958 'collate',
959 'column',
960 'comment',
961 'commit',
962 'committed',
963 'constraint',
964 'constraints',
965 'conversion',
966 'convert',
967 'copy',
968 'create',
969 'createdb',
970 'createuser',
971 'cross',
972 'current_date',
973 'current_time',
974 'current_timestamp',
975 'current_user',
976 'cursor',
977 'cycle',
978 'database',
979 'day',
980 'deallocate',
981 'dec',
982 'decimal',
983 'declare',
984 'default',
985 'deferrable',
986 'deferred',
987 'definer',
988 'delete',
989 'delimiter',
990 'delimiters',
991 'desc',
992 'distinct',
993 'do',
994 'domain',
995 'double',
996 'drop',
997 'each',
998 'else',
999 'encoding',
1000 'encrypted',
1001 'end',
1002 'escape',
1003 'except',
1004 'exclusive',
1005 'execute',
1006 'exists',
1007 'explain',
1008 'external',
1009 'extract',
1010 'false',
1011 'fetch',
1012 'float',
1013 'for',
1014 'force',
1015 'foreign',
1016 'forward',
1017 'freeze',
1018 'from',
1019 'full',
1020 'function',
1021 'get',
1022 'global',
1023 'grant',
1024 'group',
1025 'handler',
1026 'having',
1027 'hour',
1028 'ilike',
1029 'immediate',
1030 'immutable',
1031 'implicit',
1032 'in',
1033 'increment',
1034 'index',
1035 'inherits',
1036 'initially',
1037 'inner',
1038 'inout',
1039 'input',
1040 'insensitive',
1041 'insert',
1042 'instead',
1043 'int',
1044 'integer',
1045 'intersect',
1046 'interval',
1047 'into',
1048 'invoker',
1049 'is',
1050 'isnull',
1051 'isolation',
1052 'join',
1053 'key',
1054 'lancompiler',
1055 'language',
1056 'leading',
1057 'left',
1058 'level',
1059 'like',
1060 'limit',
1061 'listen',
1062 'load',
1063 'local',
1064 'localtime',
1065 'localtimestamp',
1066 'location',
1067 'lock',
1068 'match',
1069 'maxvalue',
1070 'minute',
1071 'minvalue',
1072 'mode',
1073 'month',
1074 'move',
1075 'names',
1076 'national',
1077 'natural',
1078 'nchar',
1079 'new',
1080 'next',
1081 'no',
1082 'nocreatedb',
1083 'nocreateuser',
1084 'none',
1085 'not',
1086 'nothing',
1087 'notify',
1088 'notnull',
1089 'null',
1090 'nullif',
1091 'numeric',
1092 'of',
1093 'off',
1094 'offset',
1095 'oids',
1096 'old',
1097 'on',
1098 'only',
1099 'operator',
1100 'option',
1101 'or',
1102 'order',
1103 'out',
1104 'outer',
1105 'overlaps',
1106 'overlay',
1107 'owner',
1108 'partial',
1109 'password',
1110 'path',
1111 'pendant',
1112 'placing',
1113 'position',
1114 'precision',
1115 'prepare',
1116 'primary',
1117 'prior',
1118 'privileges',
1119 'procedural',
1120 'procedure',
1121 'read',
1122 'real',
1123 'recheck',
1124 'references',
1125 'reindex',
1126 'relative',
1127 'rename',
1128 'replace',
1129 'reset',
1130 'restrict',
1131 'returns',
1132 'revoke',
1133 'right',
1134 'rollback',
1135 'row',
1136 'rule',
1137 'schema',
1138 'scroll',
1139 'second',
1140 'security',
1141 'select',
1142 'sequence',
1143 'serializable',
1144 'session',
1145 'session_user',
1146 'set',
1147 'setof',
1148 'share',
1149 'show',
1150 'similar',
1151 'simple',
1152 'smallint',
1153 'some',
1154 'stable',
1155 'start',
1156 'statement',
1157 'statistics',
1158 'stdin',
1159 'stdout',
1160 'storage',
1161 'strict',
1162 'substring',
1163 'sysid',
1164 'table',
1165 'temp',
1166 'template',
1167 'temporary',
1168 'then',
1169 'time',
1170 'timestamp',
1171 'to',
1172 'toast',
1173 'trailing',
1174 'transaction',
1175 'treat',
1176 'trigger',
1177 'trim',
1178 'true',
1179 'truncate',
1180 'trusted',
1181 'type',
1182 'unencrypted',
1183 'union',
1184 'unique',
1185 'unknown',
1186 'unlisten',
1187 'until',
1188 'update',
1189 'usage',
1190 'user',
1191 'using',
1192 'vacuum',
1193 'valid',
1194 'validator',
1195 'values',
1196 'varchar',
1197 'varying',
1198 'verbose',
1199 'version',
1200 'view',
1201 'volatile',
1202 'when',
1203 'where',
1204 'with',
1205 'without',
1206 'work',
1207 'write',
1208 'year',
1209 'zone' );
1210 }
1211 }
1212 ?>
eZDBSchemaInterface
This class provide interface for DB schema library.
Definition: ezdbschemainterface.php:62
eZDBSchemaInterface\generateTableInsertSQLList
generateTableInsertSQLList( $tableName, $tableDef, $dataEntries, $params, $withClosure=true)
Definition: ezdbschemainterface.php:669
eZDBSchemaInterface\$Schema
$Schema
Definition: ezdbschemainterface.php:1373
eZDBSchemaInterface\transformSchema
transformSchema(&$schema, $toLocal)
Definition: ezdbschemainterface.php:1046
eZDebug\writeError
static writeError( $string, $label="", $backgroundClass="")
Definition: ezdebug.php:545
eZPgsqlSchema
Handles schemas for PostgreSQL.
Definition: ezpgsqlschema.php:19
eZPgsqlSchema\SHOW_TABLES_QUERY
const SHOW_TABLES_QUERY
Definition: ezpgsqlschema.php:20
eZPgsqlSchema\convertToStandardType
convertToStandardType( $type, &$length)
Definition: ezpgsqlschema.php:378
eZPgsqlSchema\schemaName
schemaName()
Definition: ezpgsqlschema.php:904
eZPgsqlSchema\parseType
parseType( $type_info, &$length_info)
Definition: ezpgsqlschema.php:299
eZPgsqlSchema\FETCH_INDEX_DEF_QUERY
const FETCH_INDEX_DEF_QUERY
Definition: ezpgsqlschema.php:59
eZPgsqlSchema\generateFieldDef
generateFieldDef( $table_name, $field_name, $def, $add_default_not_null=true, $params)
Definition: ezpgsqlschema.php:545
eZPgsqlSchema\fetchTableFields
fetchTableFields( $table, $params)
Definition: ezpgsqlschema.php:113
eZPgsqlSchema\fetchTableIndexes
fetchTableIndexes( $table, $params)
Definition: ezpgsqlschema.php:224
eZPgsqlSchema\FETCH_INDEX_COL_NAMES_QUERY
const FETCH_INDEX_COL_NAMES_QUERY
Definition: ezpgsqlschema.php:65
eZPgsqlSchema\reservedKeywordList
reservedKeywordList()
Definition: ezpgsqlschema.php:912
eZPgsqlSchema\convertFromStandardType
convertFromStandardType( $type, &$length)
Definition: ezpgsqlschema.php:327
eZPgsqlSchema\primaryKeyIndexName
primaryKeyIndexName( $tableName, $indexName, $fields)
Definition: ezpgsqlschema.php:373
eZPgsqlSchema\FETCH_TABLE_DEF_QUERY
const FETCH_TABLE_DEF_QUERY
Definition: ezpgsqlschema.php:49
eZPgsqlSchema\generateDefaultDef
generateDefaultDef( $table_name, $field_name, $def, $params)
Definition: ezpgsqlschema.php:600
eZPgsqlSchema\FETCH_TABLE_OID_QUERY
const FETCH_TABLE_OID_QUERY
Definition: ezpgsqlschema.php:39
eZPgsqlSchema\schemaType
schemaType()
Definition: ezpgsqlschema.php:899
eZPgsqlSchema\parseDefault
parseDefault( $default, &$autoinc)
Definition: ezpgsqlschema.php:413
eZPgsqlSchema\generateTableInsertSQLList
generateTableInsertSQLList( $tableName, $tableDef, $dataEntries, $params, $withClosure=true)
Definition: ezpgsqlschema.php:815
eZPgsqlSchema\generateAddFieldSql
generateAddFieldSql( $table_name, $field_name, $def, $params)
Definition: ezpgsqlschema.php:675
eZPgsqlSchema\generateDropIndexSql
generateDropIndexSql( $table_name, $index_name, $def, $withClosure=true)
Definition: ezpgsqlschema.php:528
eZPgsqlSchema\generateAlterFieldSql
generateAlterFieldSql( $table_name, $field_name, $def, $params)
Definition: ezpgsqlschema.php:707
eZPgsqlSchema\generateSchemaFile
generateSchemaFile( $schema, $params=array())
Definition: ezpgsqlschema.php:832
eZPgsqlSchema\schema
schema( $params=array())
Definition: ezpgsqlschema.php:71
eZPgsqlSchema\generateNullDef
generateNullDef( $table_name, $field_name, $def, $params)
Definition: ezpgsqlschema.php:654
eZPgsqlSchema\generateDropTable
generateDropTable( $table)
Definition: ezpgsqlschema.php:889
eZPgsqlSchema\generateDropFieldSql
generateDropFieldSql( $table_name, $field_name, $params)
Definition: ezpgsqlschema.php:692
eZPgsqlSchema\escapeSQLString
escapeSQLString( $value)
Definition: ezpgsqlschema.php:894
eZPgsqlSchema\generateAddIndexSql
generateAddIndexSql( $table_name, $index_name, $def, $params, $withClosure=true)
Definition: ezpgsqlschema.php:464
eZPgsqlSchema\generateTableSQLList
generateTableSQLList( $table, $table_def, $params, $separateTypes)
Definition: ezpgsqlschema.php:732
eZPgsqlSchema\generateTableArrays
generateTableArrays( $table, $table_def, $params, $withClosure=true)
Definition: ezpgsqlschema.php:752
eZPgsqlSchema\generateTableSchema
generateTableSchema( $table, $table_def, $params)
Definition: ezpgsqlschema.php:723
eZPgsqlSchema\isTypeLengthSupported
isTypeLengthSupported( $pgType)
Definition: ezpgsqlschema.php:312
Copyright © 1998 – 2026 7x. All rights reserved. Licensed under the GNU GPLv2+.