3
* $RCSfile: generate-sql.php,v $
5
* Gallery - a web based photo album viewer and editor
6
* Copyright (C) 2000-2006 Bharat Mediratta
8
* This program is free software; you can redistribute it and/or modify
9
* it under the terms of the GNU General Public License as published by
10
* the Free Software Foundation; either version 2 of the License, or (at
11
* your option) any later version.
13
* This program is distributed in the hope that it will be useful, but
14
* WITHOUT ANY WARRANTY; without even the implied warranty of
15
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16
* General Public License for more details.
18
* You should have received a copy of the GNU General Public License
19
* along with this program; if not, write to the Free Software
20
* Foundation, Inc., 51 Franklin Street - Fifth Floor, Boston, MA 02110-1301, USA.
22
ini_set('error_reporting', 2047);
23
if (!empty($_SERVER['SERVER_NAME'])) {
24
print "You must run this from the command line\n";
28
require_once(dirname(__FILE__) . '/XmlParser.inc');
31
foreach (array('mysql', 'postgres', 'oracle', 'db2') as $db) {
32
$output .= '## ' . $db . "\n";
33
$xmlFiles = glob('tmp/dbxml/*.xml');
34
if (empty($xmlFiles)) {
39
foreach ($xmlFiles as $xmlFile) {
40
$p =& new XmlParser();
41
$root = $p->parse($xmlFile);
43
$generatorClass = "${db}Generator";
44
$generator = new $generatorClass;
46
$base = basename($xmlFile);
47
$base = preg_replace('/\.[^\.]*$/', '', $base);
48
$output .= '# ' . $base . "\n";
49
$root[0]['base'] = $base;
50
$output .= $generator->createSql($root[0], 0, 0, null);
53
$fd = fopen('schema.tpl', 'w');
58
function createSql($node, $index, $lastPeerIndex, $parent) {
61
$child = $node['child'] = isset($node['child']) ? $node['child'] : array();
63
switch ($node['name']) {
65
$output .= "INSERT INTO DB_TABLE_PREFIXSchema (\n";
66
$output .= " DB_COLUMN_PREFIXname,\n";
67
$output .= " DB_COLUMN_PREFIXmajor,\n";
68
$output .= " DB_COLUMN_PREFIXminor\n";
69
$output .= ") VALUES(";
70
$output .= "'" . $parent['child'][0]['content'] . "', " . $child[0]['content'] . ", " .
76
/* column-name, column-type, column-size, not-null? */
77
$output .= ' DB_COLUMN_PREFIX' . $child[0]['content'];
78
$output .= ' ' . $this->columnDefinition($child);
82
$output .= "1. UNIMPLEMLENTED: $node[name]";
89
function getIndexCrc($columns) {
91
for ($i = 0; $i < count($columns); $i++) {
92
$buf .= $columns[$i]['content'];
96
* crc32 returns different results on 32-bit vs. 64-bit systems. e.g. crc32('groupId')
97
* returns -310277968 for 32-bit systems and 3984689328 on 64-bit systems. We don't
98
* completely understand the issue, but adding 2^32 for negative crc32 values
99
* (32-bit overflows?!) seems to do the trick. And we eschew the 64-bit unsafe modulo
100
* operation by using substr instead of % 100000.
101
* Note: We also want strictly positive values since we use the value in SQL index key
106
return $crc % 100000;
108
return (int)substr(crc32($buf) + pow(2, 32), -5);
112
function getNotNullElement($child) {
113
for ($i = 0; $i < count($child); $i++) {
114
if ($child[$i]['name'] == 'NOT-NULL') {
121
function getDefaultElement($child) {
122
for ($i = 0; $i < count($child); $i++) {
123
if ($child[$i]['name'] == 'DEFAULT') {
124
return $child[$i]['content'];
130
function setColumnDefinitionMap($map) {
131
$this->_columnDefinitionMap = $map;
134
function columnDefinition($child, $includeNotNull=true, $includeDefault=true) {
136
$key = $child[1]['content'] . '-' .
137
(!empty($child[2]['content']) ? $child[2]['content'] : '');
138
if (isset($this->_columnDefinitionMap[$key])) {
139
$output .= $this->_columnDefinitionMap[$key];
141
$output .= "2. UNIMPLEMLENTED: $key";
144
if ($includeDefault) {
145
$defaultValue = $this->getDefaultElement($child);
146
if (isset($defaultValue)) {
147
$output .= " DEFAULT '$defaultValue'";
151
if ($includeNotNull) {
152
if ($this->getNotNullElement($child)) {
153
$output .= ' NOT NULL';
160
function generateSchemaUpdate($child) {
161
$output = "UPDATE DB_TABLE_PREFIXSchema\n";
162
$output .= sprintf(" SET DB_COLUMN_PREFIXmajor=%d, DB_COLUMN_PREFIXminor=%d\n",
163
$child[2]['child'][0]['content'],
164
$child[2]['child'][1]['content']);
165
$output .= sprintf(" WHERE DB_COLUMN_PREFIXname='%s' AND DB_COLUMN_PREFIXmajor=%d " .
166
"AND DB_COLUMN_PREFIXminor=%d;\n\n",
167
$child[0]['content'],
168
$child[1]['child'][0]['content'],
169
(!empty($child[1]['child'][1]['content']) ?
170
$child[1]['child'][1]['content'] : 0));
174
function isPrimaryKey($child) {
175
return $this->isIndex($child) && !empty($child['attrs']['PRIMARY']);
178
function isIndex($child) {
179
return $child['name'] == 'INDEX';
183
class MySqlGenerator extends BaseGenerator {
184
function MySqlGenerator() {
185
$this->setColumnDefinitionMap(
187
'INTEGER-' => 'int(11)',
188
'INTEGER-MEDIUM' => 'int(11)',
189
'INTEGER-LARGE' => 'int(11)',
190
'BIT-LARGE' => 'int(11)',
191
'BIT-MEDIUM' => 'int(11)',
192
'STRING-SMALL' => 'varchar(32)',
193
'STRING-MEDIUM' => 'varchar(128)',
194
'STRING-LARGE' => 'varchar(255)',
195
'TEXT-SMALL' => 'text',
197
'TEXT-MEDIUM' => 'text',
198
'TEXT-LARGE' => 'longtext',
199
'BOOLEAN-' => 'int(1)',
200
'BOOLEAN-MEDIUM' => 'int(1)',
201
'TIMESTAMP-' => 'datetime'));
204
function columnDefinition($child, $includeNotNull=true, $includeDefault=true) {
205
$output = parent::columnDefinition($child, $includeNotNull, false);
207
/* MySQL -> DEFAULT expression after NOT NULL */
208
if ($includeDefault) {
209
$defaultValue = $this->getDefaultElement($child);
210
if (isset($defaultValue)) {
211
$output .= " DEFAULT '$defaultValue'";
218
function createSql($node, $index, $lastPeerIndex, $parent) {
221
$child = $node['child'] = isset($node['child']) ? $node['child'] : array();
222
switch ($node['name']) {
224
/* table-name, schema, column+, (key | index)* */
225
$output .= 'CREATE TABLE DB_TABLE_PREFIX' . $child[0]['content'] . "(\n";
226
for ($i = 2; $i < count($child); $i++) {
227
$output .= $this->createSql($child[$i], $i, count($child) - 1, $node);
228
if ($i < count($child) - 1) {
233
$output .= ") TYPE=DB_TABLE_TYPE\n";
234
/* Character set, enclosed in comments that are ignored by MySQL < 4.1.0 */
235
$output .= "/*!40100 DEFAULT CHARACTER SET utf8 */;\n\n";
238
$output .= $this->createSql($child[1], 0, 0, $node);
243
for ($i = 0; $i < count($child); $i++) {
244
$output .= ' MODIFY COLUMN DB_COLUMN_PREFIX' . $child[$i]['child'][0]['content'];
245
$output .= ' ' . $this->columnDefinition($child[$i]['child']);
246
if ($i < count($child) - 1) {
253
/* table-name, schema-from, schema-to, (add, alter, remove)+ */
254
if (count($child) > 3) {
255
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $child[0]['content'] . "\n";
256
for ($i = 3; $i < count($child); $i++) {
260
$output .= $this->createSql($child[$i], $i, count($child) - 1, $node);
264
$output .= $this->generateSchemaUpdate($child);
268
/* (column, key, index)+ */
269
for ($i = 0; $i < count($child); $i++) {
271
switch ($c['name']) {
274
$output .= ' ADD COLUMN DB_COLUMN_PREFIX' . $c['child'][0]['content'];
275
$output .= ' ' . $this->columnDefinition($c['child']);
279
$output .= ' ADD' . $this->createSql($c, 0, 0, null);
284
$output .= ' ADD INDEX ';
285
$nameKey = strtoupper('name_' . $this->getDbType());
286
$columns = $c['child'];
287
if (isset($c['attrs'][$nameKey])) {
288
$output .= $c['attrs'][$nameKey];
290
$output .= 'DB_TABLE_PREFIX' . $parent['child'][0]['content'] .
291
'_' . $this->getIndexCrc($columns);
294
for ($i = 0; $i < count($columns); $i++) {
295
$output .= 'DB_COLUMN_PREFIX' . $columns[$i]['content'];
296
if ($i < count($columns) - 1) {
304
$output .= "3. UNIMPLEMLENTED: ADD $c[name]\n";
306
if ($i < count($child) - 1) {
313
if (!isset($parent['name'])) {
314
$output .= 'DROP TABLE DB_TABLE_PREFIX' . $node['child'][0]['content'] . ";\n\n";
315
if ($node['child'][0]['content'] != 'Schema') {
316
$output .= "DELETE FROM DB_TABLE_PREFIXSchema WHERE DB_COLUMN_PREFIXname='" .
317
$node['child'][0]['content'] . "';\n\n";
319
} else if ($parent['name'] == 'CHANGE') {
320
/* (column-name, key, index)+ */
322
foreach ($child as $c) {
326
switch ($c['name']) {
328
$output .= ' DROP COLUMN DB_COLUMN_PREFIX' . $c['content'];
332
if (!empty($child[0]['attrs']['PRIMARY'])) {
333
$output .= ' DROP PRIMARY KEY';
336
* For MySQL, our UNIQUE index names are the name of the first
337
* column that is part of the index (MySQL sets the name that way
338
* for unnamed indices (they only need to be unique in each table)
340
$output .= ' DROP INDEX DB_COLUMN_PREFIX' . $c['child'][0]['content'];
346
$output .= ' DROP INDEX ';
347
$nameKey = strtoupper('name_' . $this->getDbType());
348
if (isset($child[0]['attrs'][$nameKey])) {
349
$output .= $child[0]['attrs'][$nameKey];
351
$output .= 'DB_TABLE_PREFIX' . $parent['child'][0]['content'] .
352
'_' . $this->getIndexCrc($c['child']);
357
$output .= "4. UNIMPLEMENTED: REMOVE $c[name]\n";
365
if (!empty($node['attrs']['PRIMARY'])) {
366
$output .= ' PRIMARY KEY(';
369
* In MySQL, it would be UNIQUE [INDEX] so INDEX is optional, since UNIQUE is
370
* often called a KEY and we use <key> in our XML for UNIQUE, we just use UNIQUE
371
* without INDEX here. Don't add an index name, see our REMOVE code.
373
$output .= ' UNIQUE (';
375
for ($i = 0; $i < count($child); $i++) {
376
$output .= 'DB_COLUMN_PREFIX' . $child[$i]['content'];
377
if ($i < count($child) - 1) {
386
$crc = $this->getIndexCrc($child);
387
$output .= ' INDEX DB_TABLE_PREFIX' . $parent['child'][0]['content'] . '_' . $crc . '(';
388
for ($i = 0; $i < count($child); $i++) {
389
$output .= 'DB_COLUMN_PREFIX' . $child[$i]['content'];
390
if ($i < count($child) - 1) {
398
$output .= parent::createSql($node, $index, $lastPeerIndex, $parent);
404
function getDbType() {
409
class PostgresGenerator extends BaseGenerator {
410
function PostgresGenerator() {
411
$this->setColumnDefinitionMap(
413
'INTEGER-' => 'INTEGER',
414
'INTEGER-MEDIUM' => 'INTEGER',
415
'INTEGER-LARGE' => 'INTEGER',
416
'BIT-LARGE' => 'BIT(32)',
417
'BIT-MEDIUM' => 'BIT(32)',
418
'STRING-SMALL' => 'VARCHAR(32)',
419
'STRING-MEDIUM' => 'VARCHAR(128)',
420
'STRING-LARGE' => 'VARCHAR(255)',
421
'TEXT-SMALL' => 'text',
423
'TEXT-MEDIUM' => 'text',
424
'TEXT-LARGE' => 'text',
425
'BOOLEAN-' => 'SMALLINT',
426
'BOOLEAN-MEDIUM' => 'SMALLINT',
427
'TIMESTAMP-' => 'datetime'));
430
function createSql($node, $index, $lastPeerIndex, $parent) {
433
$child = $node['child'] = isset($node['child']) ? $node['child'] : array();
434
switch ($node['name']) {
436
/* table-name, schema-from, schema-to, (add, alter, remove)+ */
437
for ($i = 3; $i < count($child); $i++) {
438
$output .= $this->createSql($child[$i], $i, count($child) - 1, $node);
440
$output .= $this->generateSchemaUpdate($child);
444
if (!isset($parent['name'])) {
445
$output .= 'DROP TABLE DB_TABLE_PREFIX' . $node['child'][0]['content'] . ";\n\n";
446
if ($node['child'][0]['content'] != 'Schema') {
447
$output .= "DELETE FROM DB_TABLE_PREFIXSchema WHERE DB_COLUMN_PREFIXname='" .
448
$node['child'][0]['content'] . "';\n\n";
450
} else if ($parent['name'] == 'CHANGE') {
451
/* (column-name, key, index)+ */
452
for ($i = 0; $i < count($child); $i++) {
454
switch ($c['name']) {
457
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $parent['child'][0]['content'];
458
$output .= ' DROP COLUMN DB_COLUMN_PREFIX' . $c['content'];
463
if (empty($c['attrs']['PRIMARY'])) {
464
$crc = $this->getIndexCrc($c['child']);
465
$output .= 'DROP INDEX DB_TABLE_PREFIX' .
466
$parent['child'][0]['content'] . '_' . $crc . ";\n\n";
468
$output .= 'ALTER TABLE DB_TABLE_PREFIX' .
469
$parent['child'][0]['content'] . ' DROP CONSTRAINT DB_TABLE_PREFIX'
470
. $parent['child'][0]['content'] . "_pkey;\n\n";
476
$output .= 'DROP INDEX ';
477
$nameKey = strtoupper('name_' . $this->getDbType());
478
if (isset($c['attrs'][$nameKey])) {
479
$output .= $c['attrs'][$nameKey];
481
$output .= 'DB_TABLE_PREFIX' . $parent['child'][0]['content'] .
482
'_' . $this->getIndexCrc($c['child']);
488
$output .= "5. UNIMPLEMENTED: REMOVE $c[name]\n";
495
/* (column, key, index)+ */
496
foreach ($child as $c) {
497
switch ($c['name']) {
499
/* Add a new column, optionally with a default value and a not null constraint
500
* In PG7, we can not set the default value in the add column statement
501
* (PG8 doesn't have this limitation though). Therefore do it in 3 steps:
502
* 1. Add the column without any options.
503
* 2. Set the default value (only affects future rows) and add the default
504
* value for existing rows.
505
* 3. Add the not-null constraint
507
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $parent['child'][0]['content'];
508
$output .= ' ADD COLUMN DB_COLUMN_PREFIX' . $c['child'][0]['content'];
509
$output .= ' ' . $this->columnDefinition($c['child'], false, false);
512
$defaultValue = $this->getDefaultElement($c['child']);
513
if (isset($defaultValue)) {
514
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $parent['child'][0]['content'];
515
$output .= ' ALTER COLUMN DB_COLUMN_PREFIX' . $c['child'][0]['content'];
516
$output .= " SET DEFAULT '$defaultValue';\n\n";
518
$output .= 'UPDATE DB_TABLE_PREFIX' . $parent['child'][0]['content'];
519
$output .= ' SET DB_COLUMN_PREFIX' . $c['child'][0]['content'];
520
$output .= " = '$defaultValue';\n\n";
523
if ($this->getNotNullElement($c['child'])) {
524
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $parent['child'][0]['content'] .
525
' ALTER DB_COLUMN_PREFIX' . $c['child'][0]['content'] .
526
" SET NOT NULL;\n\n";
532
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $parent['child'][0]['content'] .
534
if (!empty($c['attrs']['PRIMARY'])) {
535
$output .= 'PRIMARY KEY(';
537
$output .= 'UNIQUE KEY(';
539
for ($i = 0; $i < count($c['child']); $i++) {
540
$output .= 'DB_COLUMN_PREFIX' . $c['child'][$i]['content'];
541
if ($i < count($c['child']) - 1) {
551
$output .= 'CREATE INDEX ';
552
$nameKey = strtoupper('name_' . $this->getDbType());
553
$columns = $c['child'];
554
if (isset($c['attrs'][$nameKey])) {
555
$output .= $c['attrs'][$nameKey];
557
$output .= 'DB_TABLE_PREFIX' . $parent['child'][0]['content'] .
558
'_' . $this->getIndexCrc($columns);
560
$output .= ' ON ' . 'DB_TABLE_PREFIX' . $parent['child'][0]['content'] . '(';
561
for ($i = 0; $i < count($columns); $i++) {
562
$output .= 'DB_COLUMN_PREFIX' . $columns[$i]['content'];
563
if ($i < count($columns) - 1) {
572
$output .= "6. UNIMPLEMLENTED: ADD $c[name]\n";
578
/* table-name, schema, column+, (key | index)* */
579
$output .= 'CREATE TABLE DB_TABLE_PREFIX' . $child[0]['content'] . "(\n";
580
for ($i = 2; $i < count($child); $i++) {
581
if ($child[$i]['name'] != 'COLUMN') {
588
$output .= $this->createSql($child[$i], $i, count($child) - 1, $node);
589
$firstNonColumn = $i + 1;
593
for ($i = $firstNonColumn; $i < count($child); $i++) {
594
if ($child[$i]['name'] == 'INDEX') {
595
$crc = $this->getIndexCrc($child[$i]['child']);
596
$output .= 'CREATE INDEX DB_TABLE_PREFIX' . $child[0]['content'] . '_' . $crc .
597
' ON DB_TABLE_PREFIX' . $child[0]['content'] . "(";
598
for ($j = 0; $j < count($child[$i]['child']); $j++) {
599
$output .= 'DB_COLUMN_PREFIX' . $child[$i]['child'][$j]['content'];
600
if ($j < count($child[$i]['child']) - 1) {
606
if (!empty($child[$i]['attrs']['PRIMARY'])) {
607
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $child[0]['content'] .
608
' ADD PRIMARY KEY (';
609
$columns = $child[$i]['child'];
610
for ($j = 0; $j < count($columns); $j++) {
611
$output .= 'DB_COLUMN_PREFIX' . $columns[$j]['content'];
612
if ($j < count($columns) - 1) {
618
$crc = $this->getIndexCrc($child[$i]['child']);
619
$output .= 'CREATE UNIQUE INDEX DB_TABLE_PREFIX' . $child[0]['content'] .
620
'_' . $crc . ' ON DB_TABLE_PREFIX' . $child[0]['content'] . "(";
621
for ($j = 0; $j < count($child[$i]['child']); $j++) {
622
$output .= 'DB_COLUMN_PREFIX' . $child[$i]['child'][$j]['content'];
623
if ($j < count($child[$i]['child']) - 1) {
633
$output .= $this->createSql($child[1], 0, 0, $node);
638
for ($i = 0; $i < count($child); $i++) {
639
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $parent['child'][0]['content'] .
640
' ADD COLUMN DB_COLUMN_PREFIX' . $child[$i]['child'][0]['content'] . 'Temp';
642
' ' . $this->columnDefinition($child[$i]['child'], false) . ";\n\n";
643
$output .= 'UPDATE DB_TABLE_PREFIX' . $parent['child'][0]['content'] .
644
' SET DB_COLUMN_PREFIX' . $child[$i]['child'][0]['content'] . 'Temp' .
645
' = CAST(DB_COLUMN_PREFIX' . $child[$i]['child'][0]['content'] . ' AS ' .
646
$this->columnDefinition($child[$i]['child'], false) . ");\n\n";
647
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $parent['child'][0]['content'] .
648
' DROP DB_COLUMN_PREFIX' . $child[$i]['child'][0]['content'] . ";\n\n";
649
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $parent['child'][0]['content'] .
650
' RENAME DB_COLUMN_PREFIX' . $child[$i]['child'][0]['content'] . 'Temp' .
651
' to DB_COLUMN_PREFIX' . $child[$i]['child'][0]['content'] . ";\n\n";
652
if ($this->getNotNullElement($child[$i]['child'])) {
653
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $parent['child'][0]['content'] .
654
' ALTER DB_COLUMN_PREFIX' . $child[$i]['child'][0]['content'] .
655
" SET NOT NULL;\n\n";
661
$output .= parent::createSql($node, $index, $lastPeerIndex, $parent);
667
function getDbType() {
672
class OracleGenerator extends BaseGenerator {
673
function OracleGenerator() {
674
$this->setColumnDefinitionMap(
676
'INTEGER-' => 'INTEGER',
677
'INTEGER-MEDIUM' => 'INTEGER',
678
'INTEGER-LARGE' => 'INTEGER',
679
'BIT-LARGE' => 'INTEGER',
680
'BIT-MEDIUM' => 'INTEGER',
681
'STRING-SMALL' => 'VARCHAR2(32)',
682
'STRING-MEDIUM' => 'VARCHAR2(128)',
683
'STRING-LARGE' => 'VARCHAR2(255)',
684
'TEXT-SMALL' => 'VARCHAR2(4000)',
686
'TEXT-MEDIUM' => 'CLOB',
687
'TEXT-LARGE' => 'CLOB',
688
'BOOLEAN-' => 'NUMBER(1)',
689
'BOOLEAN-MEDIUM' => 'NUMBER(1)',
690
'TIMESTAMP-' => 'datetime'));
693
function createSql($node, $index, $lastPeerIndex, $parent) {
696
$child = $node['child'] = isset($node['child']) ? $node['child'] : array();
697
switch ($node['name']) {
699
/* table-name, schema-from, schema-to, (add, alter, remove)+ */
700
for ($i = 3; $i < count($child); $i++) {
701
$output .= $this->createSql($child[$i], $i, count($child) - 1, $node);
703
$output .= $this->generateSchemaUpdate($child);
707
if (!isset($parent['name'])) {
708
$output .= 'DROP TABLE DB_TABLE_PREFIX' . $node['child'][0]['content'] . ";\n\n";
709
if ($node['child'][0]['content'] != 'Schema') {
710
$output .= "DELETE FROM DB_TABLE_PREFIXSchema WHERE DB_COLUMN_PREFIXname='" .
711
$node['child'][0]['content'] . "';\n\n";
713
} else if ($parent['name'] == 'CHANGE') {
714
/* (column-name, key, index)+ */
715
foreach ($child as $c) {
716
switch ($c['name']) {
719
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $parent['child'][0]['content'];
720
$output .= "\n" . ' DROP (DB_COLUMN_PREFIX' . $c['content'] . ')';
724
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $parent['child'][0]['content'];
725
if (isset($child[0]['attrs']['PRIMARY'])) {
726
$output .= "\n DROP PRIMARY KEY";
728
$keyColumns = array();
729
foreach ($c['child'] as $keyColumn) {
730
$keyColumns[] = 'DB_COLUMN_PREFIX' . $keyColumn['content'];
732
$output .= "\n" . ' DROP UNIQUE (' . implode(', ', $keyColumns) . ')';
738
$output .= ' DROP INDEX ';
739
$nameKey = strtoupper('name_' . $this->getDbType());
740
if (isset($child[0]['attrs'][$nameKey])) {
741
$output .= $child[0]['attrs'][$nameKey];
743
$output .= 'DB_TABLE_PREFIX' . $parent['child'][0]['content'] .
744
'_' . $this->getIndexCrc($c['child']);
749
$output .= "7. UNIMPLEMENTED: REMOVE $c[name]\n";
757
/* (column, key, index)+ */
758
for ($k = 0; $k < count($child); $k++) {
760
switch ($c['name']) {
763
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $parent['child'][0]['content'];
764
$output .= "\n" . ' ADD (DB_COLUMN_PREFIX' . $c['child'][0]['content'];
765
$output .= ' ' . $this->columnDefinition($c['child']) . ')';
770
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $parent['child'][0]['content'];
771
$output .= "\n ADD ";
772
if (!empty($c['attrs']['PRIMARY'])) {
773
$output .= 'PRIMARY KEY(';
775
$output .= 'UNIQUE KEY(';
777
for ($i = 0; $i < count($c['child']); $i++) {
778
$output .= 'DB_COLUMN_PREFIX' . $c['child'][$i]['content'];
779
if ($i < count($c['child']) - 1) {
788
$output .= 'CREATE INDEX ';
789
$nameKey = strtoupper('name_' . $this->getDbType());
790
$columns = $c['child'];
791
if (isset($c['attrs'][$nameKey])) {
792
$output .= $c['attrs'][$nameKey];
794
$output .= 'DB_TABLE_PREFIX' . $parent['child'][0]['content'] .
795
'_' . $this->getIndexCrc($columns);
797
$output .= " ON DB_TABLE_PREFIX" . $parent['child'][0]['content'] . '(';
798
for ($i = 0; $i < count($columns); $i++) {
799
$output .= 'DB_COLUMN_PREFIX' . $columns[$i]['content'];
800
if ($i < count($columns) - 1) {
808
$output .= "8. UNIMPLEMLENTED: ADD $c[name]\n";
815
/* table-name, schema, column+, (key | index)* */
816
$output .= 'CREATE TABLE DB_TABLE_PREFIX' . $child[0]['content'] . "(\n";
817
for ($i = 2; $i < count($child); $i++) {
818
if ($child[$i]['name'] != 'COLUMN') {
825
$output .= $this->createSql($child[$i], $i, count($child) - 1, $node);
826
$firstNonColumn = $i + 1;
830
$keyColumns = array();
831
for ($i = $firstNonColumn; $i < count($child); $i++) {
832
if ($child[$i]['name'] == 'INDEX') {
833
$crc = $this->getIndexCrc($child[$i]['child']);
834
$output .= 'CREATE INDEX DB_TABLE_PREFIX' . $child[0]['content'] . '_' . $crc .
835
"\n " . ' ON DB_TABLE_PREFIX' . $child[0]['content'] . "(";
836
for ($j = 0; $j < count($child[$i]['child']); $j++) {
837
$output .= 'DB_COLUMN_PREFIX' . $child[$i]['child'][$j]['content'];
838
if ($j < count($child[$i]['child']) - 1) {
844
$keys[] = $child[$i];
849
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $child[0]['content'] . "\n";
850
foreach ($keys as $key) {
851
if (!empty($key['attrs']['PRIMARY'])) {
852
$output .= ' ADD PRIMARY KEY (';
854
$output .= ' ADD UNIQUE (';
856
for ($i = 0; $i < count($key['child']); $i++) {
857
$output .= 'DB_COLUMN_PREFIX' . $key['child'][$i]['content'];
858
if ($i < count($key['child']) - 1) {
868
$output .= $this->createSql($child[1], 0, 0, $node);
872
/* column-name, column-type, column-size, not-null? */
873
$output .= ' DB_COLUMN_PREFIX' . $child[0]['content'];
874
$output .= ' ' . $this->columnDefinition($child, false);
875
if (($notNull = $this->getNotNullElement($child))
876
&& (empty($notNull['attrs']['EMPTY']) || $notNull['attrs']['EMPTY'] != 'allowed')) {
877
$output .= ' NOT NULL';
883
for ($i = 0; $i < count($child); $i++) {
884
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $parent['child'][0]['content'] .
885
' ADD (DB_COLUMN_PREFIX' . $child[$i]['child'][0]['content'] . 'Temp';
886
$output .= ' ' . $this->columnDefinition($child[$i]['child'], false) . ");\n\n";
887
$output .= 'UPDATE DB_TABLE_PREFIX' . $parent['child'][0]['content'] .
888
' SET DB_COLUMN_PREFIX' . $child[$i]['child'][0]['content'] . 'Temp' .
889
' = DB_COLUMN_PREFIX' . $child[$i]['child'][0]['content'] . ";\n\n";
890
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $parent['child'][0]['content'] .
891
' DROP (DB_COLUMN_PREFIX' . $child[$i]['child'][0]['content'] . ");\n\n";
892
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $parent['child'][0]['content'] .
893
' RENAME COLUMN DB_COLUMN_PREFIX' . $child[$i]['child'][0]['content'] . 'Temp' .
894
' TO DB_COLUMN_PREFIX' . $child[$i]['child'][0]['content'] . ";\n\n";
895
if (($notNull = $this->getNotNullElement($child[$i]['child']))
896
&& (empty($notNull['attrs']['EMPTY'])
897
|| $notNull['attrs']['EMPTY'] != 'allowed')) {
898
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $parent['child'][0]['content'] .
899
' MODIFY (DB_COLUMN_PREFIX' . $child[$i]['child'][0]['content'] .
906
$output .= parent::createSql($node, $index, $lastPeerIndex, $parent);
912
function getDbType() {
918
* Notes regarding DB2 limitations on Table and Index names:
920
* DB2 currently limits the length of table names to 30 characters, and index names to 18
921
* characters. We don't have to worry about the 30 character table name problem because we force
922
* table names to be shorter than this in GalleryStorage (and it's very important that the table
923
* names we choose here match up with the ones that GalleryStorage expects). However we have
924
* (and need) no such provision for indexes because this is the only place where we define index
927
* The installer "database setup" step prefixes all tables and indexes with "gtst#" (5 chars).
928
* The installer default is "g2_" (3 chars). So if we allow room for a 5 char prefix, that
929
* leaves us 13 characters for an 18-character index name. Our index CRC values are another 5
930
* characters. That leaves us 8 characters to use for a descriptive index name. I don't know if
931
* DB2 index names are required to be unique in the database or just to the table so to avoid any
932
* risks we can't just use a prefix or suffix of the table name because it may overlap with
933
* another similar table name.
935
* So for indexes we'll use the following format:
936
* DB_TABLE_PREFIX + substr(table name, 0, 5) + substr(md5(table name), -2) + '_' + index crc
939
* <= 5 chars + 5 + 2 + 1 + 5 = <= 18
941
class Db2Generator extends BaseGenerator {
942
function Db2Generator() {
943
/* The column size is limited to 32kbyte */
944
$this->setColumnDefinitionMap(
946
'INTEGER-' => 'INTEGER',
947
'INTEGER-MEDIUM' => 'INTEGER',
948
'INTEGER-LARGE' => 'INTEGER',
949
'BIT-LARGE' => 'VARCHAR(32) FOR BIT DATA',
950
'BIT-MEDIUM' => 'VARCHAR(32) FOR BIT DATA',
951
'STRING-SMALL' => 'VARCHAR(32)',
952
'STRING-MEDIUM' => 'VARCHAR(128)',
953
'STRING-LARGE' => 'VARCHAR(255)',
954
'TEXT-SMALL' => 'VARCHAR(10000)',
955
'TEXT-' => 'VARCHAR(15000)',
956
'TEXT-MEDIUM' => 'VARCHAR(15000)',
957
'TEXT-LARGE' => 'CLOB(2G) NOT LOGGED',
958
'BOOLEAN-' => 'SMALLINT',
959
'BOOLEAN-MEDIUM' => 'SMALLINT',
960
'TIMESTAMP-' => 'datestamp'));
963
function createSql($node, $index, $lastPeerIndex, $parent) {
966
$child = $node['child'] = isset($node['child']) ? $node['child'] : array();
967
switch ($node['name']) {
969
/* table-name, schema-from, schema-to, (add, alter, remove)+ */
970
for ($i = 3; $i < count($child); $i++) {
971
$output .= $this->createSql($child[$i], $i, count($child) - 1, $node);
973
$output .= $this->generateSchemaUpdate($child);
977
if (!isset($parent['name'])) {
978
$output .= 'DROP TABLE DB_TABLE_PREFIX' . $node['child'][0]['content'] . ";\n\n";
979
if ($node['child'][0]['content'] != 'Schema') {
980
$output .= "DELETE FROM DB_TABLE_PREFIXSchema WHERE DB_COLUMN_PREFIXname='" .
981
$node['child'][0]['content'] . "';\n\n";
983
} else if ($parent['name'] == 'CHANGE') {
984
/* (column-name, key, index)+ */
985
for ($i = 0; $i < count($child); $i++) {
987
switch ($c['name']) {
990
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $parent['child'][0]['content'];
991
$output .= ' DROP COLUMN DB_COLUMN_PREFIX' . $c['content'];
996
if (empty($c['attrs']['PRIMARY'])) {
997
$crc = $this->getIndexCrc($c['child']);
998
$output .= 'DROP INDEX DB_TABLE_PREFIX' .
999
$parent['child'][0]['content'] . '_' . $crc . ";\n\n";
1001
$output .= 'ALTER TABLE DB_TABLE_PREFIX' .
1002
$parent['child'][0]['content'] . " DROP PRIMARY KEY;\n\n";
1008
$output .= 'DROP INDEX ';
1009
$nameKey = strtoupper('name_' . $this->getDbType());
1010
if (isset($c['attrs'][$nameKey])) {
1011
$output .= $c['attrs'][$nameKey];
1013
$output .= 'DB_TABLE_PREFIX' .
1014
substr($parent['child'][0]['content'], 0, 5) .
1015
substr(md5($parent['child'][0]['content']), -2) .
1016
'_' . $this->getIndexCrc($c['child']);
1022
$output .= "5. UNIMPLEMENTED: REMOVE $c[name]\n";
1029
/* (column, key, index)+ */
1030
foreach ($child as $c) {
1031
switch ($c['name']) {
1034
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $parent['child'][0]['content'];
1035
$output .= ' ADD COLUMN DB_COLUMN_PREFIX' . $c['child'][0]['content'];
1036
$output .= ' ' . $this->columnDefinition($c['child']);
1042
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $parent['child'][0]['content'] .
1044
if (!empty($c['attrs']['PRIMARY'])) {
1045
$output .= 'PRIMARY KEY(';
1047
$output .= 'UNIQUE KEY(';
1049
for ($i = 0; $i < count($c['child']); $i++) {
1050
$output .= 'DB_COLUMN_PREFIX' . $c['child'][$i]['content'];
1051
if ($i < count($c['child']) - 1) {
1061
$output .= 'CREATE INDEX ';
1062
$nameKey = strtoupper('name_' . $this->getDbType());
1063
$columns = $c['child'];
1064
if (isset($c['attrs'][$nameKey])) {
1065
$output .= $c['attrs'][$nameKey];
1067
$output .= 'DB_TABLE_PREFIX' .
1068
substr($parent['child'][0]['content'], 0, 5) .
1069
substr(md5($parent['child'][0]['content']), -2) .
1070
'_' . $this->getIndexCrc($c['child']);
1072
$output .= ' ON ' . 'DB_TABLE_PREFIX' . $parent['child'][0]['content'] . '(';
1073
for ($i = 0; $i < count($columns); $i++) {
1074
$output .= 'DB_COLUMN_PREFIX' . $columns[$i]['content'];
1075
if ($i < count($columns) - 1) {
1084
$output .= "6. UNIMPLEMLENTED: ADD $c[name]\n";
1090
/* table-name, schema, column+, (key | index)* */
1091
$output .= 'CREATE TABLE DB_TABLE_PREFIX' . $child[0]['content'] . "(\n";
1092
for ($i = 2; $i < count($child); $i++) {
1093
if ($child[$i]['name'] != 'COLUMN') {
1100
$output .= $this->createSql($child[$i], $i, count($child) - 1, $node);
1101
$firstNonColumn = $i + 1;
1103
$output .= ");\n\n";
1105
for ($i = $firstNonColumn; $i < count($child); $i++) {
1106
if ($child[$i]['name'] == 'INDEX') {
1107
$crc = $this->getIndexCrc($child[$i]['child']);
1108
$output .= 'CREATE INDEX DB_TABLE_PREFIX' .
1109
substr($child[0]['content'], 0, 5) .
1110
substr(md5($child[0]['content']), -2) . '_' . $crc .
1111
"\n " . ' ON DB_TABLE_PREFIX' . $child[0]['content'] . "(";
1112
for ($j = 0; $j < count($child[$i]['child']); $j++) {
1113
$output .= 'DB_COLUMN_PREFIX' . $child[$i]['child'][$j]['content'];
1114
if ($j < count($child[$i]['child']) - 1) {
1118
$output .= ");\n\n";
1120
if (!empty($child[$i]['attrs']['PRIMARY'])) {
1121
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $child[0]['content'] .
1122
' ADD PRIMARY KEY (';
1123
$columns = $child[$i]['child'];
1124
for ($j = 0; $j < count($columns); $j++) {
1125
$output .= 'DB_COLUMN_PREFIX' . $columns[$j]['content'];
1126
if ($j < count($columns) - 1) {
1130
$output .= ");\n\n";
1132
$crc = $this->getIndexCrc($child[$i]['child']);
1133
$output .= 'CREATE UNIQUE INDEX DB_TABLE_PREFIX' .
1134
substr($child[0]['content'], 0, 5) .
1135
substr(md5($child[0]['content']), -2) . '_' . $crc .
1136
" \n" . ' ON DB_TABLE_PREFIX' . $child[0]['content'] . "(";
1137
for ($j = 0; $j < count($child[$i]['child']); $j++) {
1138
$output .= 'DB_COLUMN_PREFIX' . $child[$i]['child'][$j]['content'];
1139
if ($j < count($child[$i]['child']) - 1) {
1143
$output .= ");\n\n";
1149
$output .= $this->createSql($child[1], 0, 0, $node);
1154
for ($i = 0; $i < count($child); $i++) {
1155
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $parent['child'][0]['content'] .
1156
' ADD COLUMN DB_COLUMN_PREFIX' . $child[$i]['child'][0]['content'] . 'Temp';
1157
$output .= ' ' . $this->columnDefinition($child[$i]['child'], false) . ";\n\n";
1158
$output .= 'UPDATE DB_TABLE_PREFIX' . $parent['child'][0]['content'] .
1159
' SET DB_COLUMN_PREFIX' . $child[$i]['child'][0]['content'] . 'Temp' .
1160
' = CAST(DB_COLUMN_PREFIX' . $child[$i]['child'][0]['content'] . ' AS ' .
1161
$this->columnDefinition($child[$i]['child'], false) . ");\n\n";
1162
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $parent['child'][0]['content'] .
1163
' DROP DB_COLUMN_PREFIX' . $child[$i]['child'][0]['content'] . ";\n\n";
1164
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $parent['child'][0]['content'] .
1165
' RENAME DB_COLUMN_PREFIX' . $child[$i]['child'][0]['content'] . 'Temp' .
1166
' to DB_COLUMN_PREFIX' . $child[$i]['child'][0]['content'] . ";\n\n";
1167
if ($this->getNotNullElement($child[$i]['child'])) {
1168
$output .= 'ALTER TABLE DB_TABLE_PREFIX' . $parent['child'][0]['content'] .
1169
' ALTER DB_COLUMN_PREFIX' . $child[$i]['child'][0]['content'] .
1170
" SET NOT NULL;\n\n";
1176
$output .= parent::createSql($node, $index, $lastPeerIndex, $parent);
1182
function getDbType() {