?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
db_tools from static to non-static due to the db dependancy (#31245)
[phpbb.git] / phpBB / includes / db / db_tools.php
blobdb9d8cb19c98d6a886f3a499947f704271419e1b
1 <?php
2 /**
3 *
4 * @package dbal
5 * @version $Id$
6 * @copyright (c) 2007 phpBB Group
7 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
8 *
9 */
10
11 /**
12 * @ignore
13 */
14 if (!defined('IN_PHPBB'))
15 {
16 exit;
17 }
18
19 /**
20 * Database Tools for handling cross-db actions such as altering columns, etc.
21 * Currently not supported is returning SQL for creating tables.
22 *
23 * @package dbal
24 * @note currently not used within phpBB3, but may be utilized later.
25 */
26 class phpbb_db_tools
27 {
28 public $db = NULL;
29
30 public $dbms_type_map = array(
31 'mysql' => array(
32 'INT:' => 'int(%d)',
33 'BINT' => 'bigint(20)',
34 'UINT' => 'mediumint(8) UNSIGNED',
35 'UINT:' => 'int(%d) UNSIGNED',
36 'TINT:' => 'tinyint(%d)',
37 'USINT' => 'smallint(4) UNSIGNED',
38 'BOOL' => 'tinyint(1) UNSIGNED',
39 'VCHAR' => 'varchar(255)',
40 'VCHAR:' => 'varchar(%d)',
41 'CHAR:' => 'char(%d)',
42 'XSTEXT' => 'text',
43 'XSTEXT_UNI'=> 'varchar(100)',
44 'STEXT' => 'text',
45 'STEXT_UNI' => 'varchar(255)',
46 'TEXT' => 'text',
47 'TEXT_UNI' => 'text',
48 'MTEXT' => 'mediumtext',
49 'MTEXT_UNI' => 'mediumtext',
50 'TIMESTAMP' => 'int(11) UNSIGNED',
51 'DECIMAL' => 'decimal(5,2)',
52 'DECIMAL:' => 'decimal(%d,2)',
53 'PDECIMAL' => 'decimal(6,3)',
54 'PDECIMAL:' => 'decimal(%d,3)',
55 'VCHAR_UNI' => 'varchar(255)',
56 'VCHAR_UNI:'=> 'varchar(%d)',
57 'VCHAR_CI' => 'varchar(255)',
58 'VARBINARY' => 'varbinary(255)',
59 ),
60
61 'firebird' => array(
62 'INT:' => 'INTEGER',
63 'BINT' => 'DOUBLE PRECISION',
64 'UINT' => 'INTEGER',
65 'UINT:' => 'INTEGER',
66 'TINT:' => 'INTEGER',
67 'USINT' => 'INTEGER',
68 'BOOL' => 'INTEGER',
69 'VCHAR' => 'VARCHAR(255) CHARACTER SET NONE',
70 'VCHAR:' => 'VARCHAR(%d) CHARACTER SET NONE',
71 'CHAR:' => 'CHAR(%d) CHARACTER SET NONE',
72 'XSTEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
73 'STEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
74 'TEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
75 'MTEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
76 'XSTEXT_UNI'=> 'VARCHAR(100) CHARACTER SET UTF8',
77 'STEXT_UNI' => 'VARCHAR(255) CHARACTER SET UTF8',
78 'TEXT_UNI' => 'BLOB SUB_TYPE TEXT CHARACTER SET UTF8',
79 'MTEXT_UNI' => 'BLOB SUB_TYPE TEXT CHARACTER SET UTF8',
80 'TIMESTAMP' => 'INTEGER',
81 'DECIMAL' => 'DOUBLE PRECISION',
82 'DECIMAL:' => 'DOUBLE PRECISION',
83 'PDECIMAL' => 'DOUBLE PRECISION',
84 'PDECIMAL:' => 'DOUBLE PRECISION',
85 'VCHAR_UNI' => 'VARCHAR(255) CHARACTER SET UTF8',
86 'VCHAR_UNI:'=> 'VARCHAR(%d) CHARACTER SET UTF8',
87 'VCHAR_CI' => 'VARCHAR(255) CHARACTER SET UTF8',
88 'VARBINARY' => 'CHAR(255) CHARACTER SET NONE',
89 ),
90
91 'mssql' => array(
92 'INT:' => '[int]',
93 'BINT' => '[float]',
94 'UINT' => '[int]',
95 'UINT:' => '[int]',
96 'TINT:' => '[int]',
97 'USINT' => '[int]',
98 'BOOL' => '[int]',
99 'VCHAR' => '[varchar] (255)',
100 'VCHAR:' => '[varchar] (%d)',
101 'CHAR:' => '[char] (%d)',
102 'XSTEXT' => '[varchar] (1000)',
103 'STEXT' => '[varchar] (3000)',
104 'TEXT' => '[varchar] (8000)',
105 'MTEXT' => '[text]',
106 'XSTEXT_UNI'=> '[varchar] (100)',
107 'STEXT_UNI' => '[varchar] (255)',
108 'TEXT_UNI' => '[varchar] (4000)',
109 'MTEXT_UNI' => '[text]',
110 'TIMESTAMP' => '[int]',
111 'DECIMAL' => '[float]',
112 'DECIMAL:' => '[float]',
113 'PDECIMAL' => '[float]',
114 'PDECIMAL:' => '[float]',
115 'VCHAR_UNI' => '[varchar] (255)',
116 'VCHAR_UNI:'=> '[varchar] (%d)',
117 'VCHAR_CI' => '[varchar] (255)',
118 'VARBINARY' => '[varchar] (255)',
119 ),
120
121 'oracle' => array(
122 'INT:' => 'number(%d)',
123 'BINT' => 'number(20)',
124 'UINT' => 'number(8)',
125 'UINT:' => 'number(%d)',
126 'TINT:' => 'number(%d)',
127 'USINT' => 'number(4)',
128 'BOOL' => 'number(1)',
129 'VCHAR' => 'varchar2(255 char)',
130 'VCHAR:' => 'varchar2(%d char)',
131 'CHAR:' => 'char(%d char)',
132 'XSTEXT' => 'varchar2(1000 char)',
133 'STEXT' => 'varchar2(3000 char)',
134 'TEXT' => 'clob',
135 'MTEXT' => 'clob',
136 'XSTEXT_UNI'=> 'varchar2(100 char)',
137 'STEXT_UNI' => 'varchar2(255 char)',
138 'TEXT_UNI' => 'clob',
139 'MTEXT_UNI' => 'clob',
140 'TIMESTAMP' => 'number(11)',
141 'DECIMAL' => 'number(5, 2)',
142 'DECIMAL:' => 'number(%d, 2)',
143 'PDECIMAL' => 'number(6, 3)',
144 'PDECIMAL:' => 'number(%d, 3)',
145 'VCHAR_UNI' => 'varchar2(255 char)',
146 'VCHAR_UNI:'=> 'varchar2(%d char)',
147 'VCHAR_CI' => 'varchar2(255 char)',
148 'VARBINARY' => 'raw(255)',
149 ),
150
151 'sqlite' => array(
152 'INT:' => 'int(%d)',
153 'BINT' => 'bigint(20)',
154 'UINT' => 'INTEGER UNSIGNED', //'mediumint(8) UNSIGNED',
155 'UINT:' => 'INTEGER UNSIGNED', // 'int(%d) UNSIGNED',
156 'TINT:' => 'tinyint(%d)',
157 'USINT' => 'INTEGER UNSIGNED', //'mediumint(4) UNSIGNED',
158 'BOOL' => 'INTEGER UNSIGNED', //'tinyint(1) UNSIGNED',
159 'VCHAR' => 'varchar(255)',
160 'VCHAR:' => 'varchar(%d)',
161 'CHAR:' => 'char(%d)',
162 'XSTEXT' => 'text(65535)',
163 'STEXT' => 'text(65535)',
164 'TEXT' => 'text(65535)',
165 'MTEXT' => 'mediumtext(16777215)',
166 'XSTEXT_UNI'=> 'text(65535)',
167 'STEXT_UNI' => 'text(65535)',
168 'TEXT_UNI' => 'text(65535)',
169 'MTEXT_UNI' => 'mediumtext(16777215)',
170 'TIMESTAMP' => 'INTEGER UNSIGNED', //'int(11) UNSIGNED',
171 'DECIMAL' => 'decimal(5,2)',
172 'DECIMAL:' => 'decimal(%d,2)',
173 'PDECIMAL' => 'decimal(6,3)',
174 'PDECIMAL:' => 'decimal(%d,3)',
175 'VCHAR_UNI' => 'varchar(255)',
176 'VCHAR_UNI:'=> 'varchar(%d)',
177 'VCHAR_CI' => 'varchar(255)',
178 'VARBINARY' => 'blob',
179 ),
180
181 'db2' => array(
182 'INT:' => 'integer',
183 'BINT' => 'float',
184 'UINT' => 'integer',
185 'UINT:' => 'integer',
186 'TINT:' => 'smallint',
187 'USINT' => 'smallint',
188 'BOOL' => 'smallint',
189 'VCHAR' => 'varchar(255)',
190 'VCHAR:' => 'varchar(%d)',
191 'CHAR:' => 'char(%d)',
192 'XSTEXT' => 'clob(65K)',
193 'STEXT' => 'varchar(3000)',
194 'TEXT' => 'clob(65K)',
195 'MTEXT' => 'clob(16M)',
196 'XSTEXT_UNI'=> 'varchar(100)',
197 'STEXT_UNI' => 'varchar(255)',
198 'TEXT_UNI' => 'clob(65K)',
199 'MTEXT_UNI' => 'clob(16M)',
200 'TIMESTAMP' => 'integer',
201 'DECIMAL' => 'float',
202 'VCHAR_UNI' => 'varchar(255)',
203 'VCHAR_UNI:'=> 'varchar(%d)',
204 'VCHAR_CI' => 'varchar(255)',
205 'VARBINARY' => 'varchar(255)',
206 ),
207
208 'postgres' => array(
209 'INT:' => 'INT4',
210 'BINT' => 'INT8',
211 'UINT' => 'INT4', // unsigned
212 'UINT:' => 'INT4', // unsigned
213 'USINT' => 'INT2', // unsigned
214 'BOOL' => 'INT2', // unsigned
215 'TINT:' => 'INT2',
216 'VCHAR' => 'varchar(255)',
217 'VCHAR:' => 'varchar(%d)',
218 'CHAR:' => 'char(%d)',
219 'XSTEXT' => 'varchar(1000)',
220 'STEXT' => 'varchar(3000)',
221 'TEXT' => 'varchar(8000)',
222 'MTEXT' => 'TEXT',
223 'XSTEXT_UNI'=> 'varchar(100)',
224 'STEXT_UNI' => 'varchar(255)',
225 'TEXT_UNI' => 'varchar(4000)',
226 'MTEXT_UNI' => 'TEXT',
227 'TIMESTAMP' => 'INT4', // unsigned
228 'DECIMAL' => 'decimal(5,2)',
229 'DECIMAL:' => 'decimal(%d,2)',
230 'PDECIMAL' => 'decimal(6,3)',
231 'PDECIMAL:' => 'decimal(%d,3)',
232 'VCHAR_UNI' => 'varchar(255)',
233 'VCHAR_UNI:'=> 'varchar(%d)',
234 'VCHAR_CI' => 'varchar(255)',
235 'VARBINARY' => 'bytea',
236 ),
237 );
238
239 // A list of types being unsigned for better reference in some db's
240 public $unsigned_types = array('UINT', 'UINT:', 'USINT', 'BOOL', 'TIMESTAMP');
241 public $supported_dbms = array('firebird', 'mssql', 'mysql', 'oracle', 'postgres', 'sqlite', 'db2');
242
243 /**
244 * Set this to true if you only want to return the 'to-be-executed' SQL statement(s) (as an array).
245 */
246 public $return_statements = false;
247
248 public function __construct($db, $return_statements = false)
249 {
250 $this->db = $db;
251 $this->return_statements = $return_statements;
252 }
253
254 /**
255 * Handle passed database update array.
256 * Expected structure...
257 * Key being one of the following
258 * change_columns: Column changes (only type, not name)
259 * add_columns: Add columns to a table
260 * drop_keys: Dropping keys
261 * drop_columns: Removing/Dropping columns
262 * add_primary_keys: adding primary keys
263 * add_unique_index: adding an unique index
264 * add_index: adding an index
265 *
266 * The values are in this format:
267 * {TABLE NAME} => array(
268 * {COLUMN NAME} => array({COLUMN TYPE}, {DEFAULT VALUE}, {OPTIONAL VARIABLES}),
269 * {KEY/INDEX NAME} => array({COLUMN NAMES}),
270 * )
271 *
272 * For more information have a look at /install/schemas/schema_data.php (only available through CVS)
273 */
274 public function perform_schema_changes($schema_changes)
275 {
276 if (empty($schema_changes))
277 {
278 return;
279 }
280
281 $statements = array();
282
283 // Change columns?
284 if (!empty($schema_changes['change_columns']))
285 {
286 foreach ($schema_changes['change_columns'] as $table => $columns)
287 {
288 foreach ($columns as $column_name => $column_data)
289 {
290 $result = $this->sql_column_change($table, $column_name, $column_data);
291
292 if ($this->return_statements)
293 {
294 $statements = array_merge($statements, $result);
295 }
296 }
297 }
298 }
299
300 // Add columns?
301 if (!empty($schema_changes['add_columns']))
302 {
303 foreach ($schema_changes['add_columns'] as $table => $columns)
304 {
305 foreach ($columns as $column_name => $column_data)
306 {
307 // Only add the column if it does not exist yet
308 if (!$this->sql_column_exists($table, $column_name))
309 {
310 $result = $this->sql_column_add($table, $column_name, $column_data);
311
312 if ($this->return_statements)
313 {
314 $statements = array_merge($statements, $result);
315 }
316 }
317 }
318 }
319 }
320
321 // Remove keys?
322 if (!empty($schema_changes['drop_keys']))
323 {
324 foreach ($schema_changes['drop_keys'] as $table => $indexes)
325 {
326 foreach ($indexes as $index_name)
327 {
328 $result = $this->sql_index_drop($table, $index_name);
329
330 if ($this->return_statements)
331 {
332 $statements = array_merge($statements, $result);
333 }
334 }
335 }
336 }
337
338 // Drop columns?
339 if (!empty($schema_changes['drop_columns']))
340 {
341 foreach ($schema_changes['drop_columns'] as $table => $columns)
342 {
343 foreach ($columns as $column)
344 {
345 $result = $this->sql_column_remove($table, $column);
346
347 if ($this->return_statements)
348 {
349 $statements = array_merge($statements, $result);
350 }
351 }
352 }
353 }
354
355 // Add primary keys?
356 if (!empty($schema_changes['add_primary_keys']))
357 {
358 foreach ($schema_changes['add_primary_keys'] as $table => $columns)
359 {
360 $result = $this->sql_create_primary_key($table, $columns);
361
362 if ($this->return_statements)
363 {
364 $statements = array_merge($statements, $result);
365 }
366 }
367 }
368
369 // Add unqiue indexes?
370 if (!empty($schema_changes['add_unique_index']))
371 {
372 foreach ($schema_changes['add_unique_index'] as $table => $index_array)
373 {
374 foreach ($index_array as $index_name => $column)
375 {
376 $result = $this->sql_create_unique_index($table, $index_name, $column);
377
378 if ($this->return_statements)
379 {
380 $statements = array_merge($statements, $result);
381 }
382 }
383 }
384 }
385
386 // Add indexes?
387 if (!empty($schema_changes['add_index']))
388 {
389 foreach ($schema_changes['add_index'] as $table => $index_array)
390 {
391 foreach ($index_array as $index_name => $column)
392 {
393 $result = $this->sql_create_unique_index($table, $index_name, $column);
394
395 if ($this->return_statements)
396 {
397 $statements = array_merge($statements, $result);
398 }
399 }
400 }
401 }
402
403 if ($this->return_statements)
404 {
405 return $statements;
406 }
407 }
408
409 /**
410 * Check if a specified column exist
411 * @return bool True if column exists, else false
412 */
413 public function sql_column_exists($table, $column_name)
414 {
415 switch ($this->db->dbms_type)
416 {
417 case 'mysql':
418
419 $sql = "SHOW COLUMNS FROM $table";
420 $result = $this->db->sql_query($sql);
421
422 while ($row = $this->db->sql_fetchrow($result))
423 {
424 // lower case just in case
425 if (strtolower($row['Field']) == $column_name)
426 {
427 $this->db->sql_freeresult($result);
428 return true;
429 }
430 }
431 $this->db->sql_freeresult($result);
432 return false;
433 break;
434
435 // PostgreSQL has a way of doing this in a much simpler way but would
436 // not allow us to support all versions of PostgreSQL
437 case 'postgres':
438 $sql = "SELECT a.attname
439 FROM pg_class c, pg_attribute a
440 WHERE c.relname = '{$table}'
441 AND a.attnum > 0
442 AND a.attrelid = c.oid";
443 $result = $this->db->sql_query($sql);
444 while ($row = $this->db->sql_fetchrow($result))
445 {
446 // lower case just in case
447 if (strtolower($row['attname']) == $column_name)
448 {
449 $this->db->sql_freeresult($result);
450 return true;
451 }
452 }
453 $this->db->sql_freeresult($result);
454
455 return false;
456 break;
457
458 // same deal with PostgreSQL, we must perform more complex operations than
459 // we technically could
460 case 'mssql':
461 $sql = "SELECT c.name
462 FROM syscolumns c
463 LEFT JOIN sysobjects o ON c.id = o.id
464 WHERE o.name = '{$table}'";
465 $result = $this->db->sql_query($sql);
466 while ($row = $this->db->sql_fetchrow($result))
467 {
468 // lower case just in case
469 if (strtolower($row['name']) == $column_name)
470 {
471 $this->db->sql_freeresult($result);
472 return true;
473 }
474 }
475 $this->db->sql_freeresult($result);
476 return false;
477 break;
478
479 case 'oracle':
480 $sql = "SELECT column_name
481 FROM user_tab_columns
482 WHERE table_name = '{$table}'";
483 $result = $this->db->sql_query($sql);
484 while ($row = $this->db->sql_fetchrow($result))
485 {
486 // lower case just in case
487 if (strtolower($row['column_name']) == $column_name)
488 {
489 $this->db->sql_freeresult($result);
490 return true;
491 }
492 }
493 $this->db->sql_freeresult($result);
494 return false;
495 break;
496
497 case 'firebird':
498 $sql = "SELECT RDB\$FIELD_NAME as FNAME
499 FROM RDB\$RELATION_FIELDS
500 WHERE RDB\$RELATION_NAME = '{$table}'";
501 $result = $this->db->sql_query($sql);
502 while ($row = $this->db->sql_fetchrow($result))
503 {
504 // lower case just in case
505 if (strtolower($row['fname']) == $column_name)
506 {
507 $this->db->sql_freeresult($result);
508 return true;
509 }
510 }
511 $this->db->sql_freeresult($result);
512 return false;
513 break;
514
515 case 'db2':
516 $sql = "SELECT colname
517 FROM syscat.columns
518 WHERE tabname = '$table'";
519 $result = $this->db->sql_query($sql);
520 while ($row = $this->db->sql_fetchrow($result))
521 {
522 // lower case just in case
523 if (strtolower($row['colname']) == $column_name)
524 {
525 $this->db->sql_freeresult($result);
526 return true;
527 }
528 }
529 $this->db->sql_freeresult($result);
530 return false;
531 break;
532
533 // ugh, SQLite
534 case 'sqlite':
535 $sql = "SELECT sql
536 FROM sqlite_master
537 WHERE type = 'table'
538 AND name = '{$table}'";
539 $result = $this->db->sql_query($sql);
540
541 if (!$result)
542 {
543 return false;
544 }
545
546 $row = $this->db->sql_fetchrow($result);
547 $this->db->sql_freeresult($result);
548
549 preg_match('#\((.*)\)#s', $row['sql'], $matches);
550
551 $cols = trim($matches[1]);
552 $col_array = preg_split('/,(?![\s\w]+\))/m', $cols);
553
554 foreach ($col_array as $declaration)
555 {
556 $entities = preg_split('#\s+#', trim($declaration));
557 if ($entities[0] == 'PRIMARY')
558 {
559 continue;
560 }
561
562 if (strtolower($entities[0]) == $column_name)
563 {
564 return true;
565 }
566 }
567 return false;
568 break;
569 }
570 }
571
572 /**
573 * Private method for performing sql statements (either execute them or return them)
574 * @access private
575 */
576 private function _sql_run_sql($statements)
577 {
578 if ($this->return_statements)
579 {
580 return $statements;
581 }
582
583 // We could add error handling here...
584 foreach ($statements as $sql)
585 {
586 if ($sql === 'begin')
587 {
588 $this->db->sql_transaction('begin');
589 }
590 else if ($sql === 'commit')
591 {
592 $this->db->sql_transaction('commit');
593 }
594 else
595 {
596 $this->db->sql_query($sql);
597 }
598 }
599
600 return true;
601 }
602
603 /**
604 * Function to prepare some column information for better usage
605 * @access private
606 */
607 private function sql_prepare_column_data($table_name, $column_name, $column_data)
608 {
609 // Get type
610 if (strpos($column_data[0], ':') !== false)
611 {
612 list($orig_column_type, $column_length) = explode(':', $column_data[0]);
613
614 if (!is_array($this->dbms_type_map[$this->db->dbms_type][$orig_column_type . ':']))
615 {
616 $column_type = sprintf($this->dbms_type_map[$this->db->dbms_type][$orig_column_type . ':'], $column_length);
617 }
618
619 $orig_column_type .= ':';
620 }
621 else
622 {
623 $orig_column_type = $column_data[0];
624 $column_type = $this->dbms_type_map[$this->db->dbms_type][$column_data[0]];
625 }
626
627 // Adjust default value if db-dependant specified
628 if (is_array($column_data[1]))
629 {
630 $column_data[1] = (isset($column_data[1][$this->db->dbms_type])) ? $column_data[1][$this->db->dbms_type] : $column_data[1]['default'];
631 }
632
633 $sql = '';
634
635 $return_array = array();
636
637 switch ($this->db->dbms_type)
638 {
639 case 'firebird':
640 $sql .= " {$column_type} ";
641
642 if (!is_null($column_data[1]))
643 {
644 $sql .= 'DEFAULT ' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ' ';
645 }
646
647 $sql .= 'NOT NULL';
648
649 // This is a UNICODE column and thus should be given it's fair share
650 if (preg_match('/^X?STEXT_UNI|VCHAR_(CI|UNI:?)/', $column_data[0]))
651 {
652 $sql .= ' COLLATE UNICODE';
653 }
654
655 $return_array['auto_increment'] = false;
656 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
657 {
658 $return_array['auto_increment'] = true;
659 }
660
661 break;
662
663 case 'mssql':
664 $sql .= " {$column_type} ";
665 $sql_default = " {$column_type} ";
666
667 // For adding columns we need the default definition
668 if (!is_null($column_data[1]))
669 {
670 // For hexadecimal values do not use single quotes
671 if (strpos($column_data[1], '0x') === 0)
672 {
673 $sql_default .= 'DEFAULT (' . $column_data[1] . ') ';
674 }
675 else
676 {
677 $sql_default .= 'DEFAULT (' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ') ';
678 }
679 }
680
681 $return_array['textimage'] = $column_type === '[text]';
682
683 $sql .= 'NOT NULL';
684 $sql_default .= 'NOT NULL';
685
686 $return_array['column_type_sql_default'] = $sql_default;
687 break;
688
689 case 'mysql':
690 $sql .= " {$column_type} ";
691
692 // For hexadecimal values do not use single quotes
693 if (!is_null($column_data[1]) && substr($column_type, -4) !== 'text' && substr($column_type, -4) !== 'blob')
694 {
695 $sql .= (strpos($column_data[1], '0x') === 0) ? "DEFAULT {$column_data[1]} " : "DEFAULT '{$column_data[1]}' ";
696 }
697 $sql .= 'NOT NULL';
698
699 if (isset($column_data[2]))
700 {
701 if ($column_data[2] == 'auto_increment')
702 {
703 $sql .= ' auto_increment';
704 }
705 else if ($column_data[2] == 'true_sort')
706 {
707 $sql .= ' COLLATE utf8_unicode_ci';
708 }
709 }
710
711 break;
712
713 case 'oracle':
714 $sql .= " {$column_type} ";
715 $sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : '';
716
717 // In Oracle empty strings ('') are treated as NULL.
718 // Therefore in oracle we allow NULL's for all DEFAULT '' entries
719 // Oracle does not like setting NOT NULL on a column that is already NOT NULL (this happens only on number fields)
720 if (preg_match('/number/i', $column_type))
721 {
722 $sql .= ($column_data[1] === '') ? '' : 'NOT NULL';
723 }
724
725 $return_array['auto_increment'] = false;
726 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
727 {
728 $return_array['auto_increment'] = true;
729 }
730 break;
731
732 case 'postgres':
733 $return_array['column_type'] = $column_type;
734
735 $sql .= " {$column_type} ";
736
737 $return_array['auto_increment'] = false;
738 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
739 {
740 $default_val = "nextval('{$table_name}_seq')";
741 $return_array['auto_increment'] = true;
742 }
743 else if (!is_null($column_data[1]))
744 {
745 $default_val = "'" . $column_data[1] . "'";
746 $return_array['null'] = 'NOT NULL';
747 $sql .= 'NOT NULL ';
748 }
749
750 $return_array['default'] = $default_val;
751
752 $sql .= "DEFAULT {$default_val}";
753
754 // Unsigned? Then add a CHECK contraint
755 if (in_array($orig_column_type, $this->unsigned_types))
756 {
757 $return_array['constraint'] = "CHECK ({$column_name} >= 0)";
758 $sql .= " CHECK ({$column_name} >= 0)";
759 }
760 break;
761
762 case 'sqlite':
763 $return_array['primary_key_set'] = false;
764 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
765 {
766 $sql .= ' INTEGER PRIMARY KEY';
767 $return_array['primary_key_set'] = true;
768 }
769 else
770 {
771 $sql .= ' ' . $column_type;
772 }
773
774 $sql .= ' NOT NULL ';
775 $sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}'" : '';
776 break;
777
778 case 'db2':
779 $sql .= " {$column_type} NOT NULL";
780
781 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
782 {
783 $sql .= ' GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1)';
784 }
785 else
786 {
787 if (preg_match('/^(integer|smallint|float)$/', $column_type))
788 {
789 $sql .= " DEFAULT {$column_data[1]}";
790 }
791 else
792 {
793 $sql .= " DEFAULT '{$column_data[1]}'";
794 }
795 }
796 break;
797 }
798
799 $return_array['column_type_sql'] = $sql;
800
801 return $return_array;
802 }
803
804 public function sql_create_table($table_name, $table_data)
805 {
806 // holds the DDL for a column
807 $columns = array();
808
809 $table_sql = 'CREATE TABLE ' . $table_name . ' (' . "\n";
810
811 // Determine if we have created a PRIMARY KEY in the earliest
812 $primary_key_gen = false;
813
814 // Determine if the table must be created with TEXTIMAGE
815 $create_textimage = false;
816
817 // Determine if the table requires a sequence
818 $create_sequence = false;
819
820 foreach ($table_data['COLUMNS'] as $column_name => $column_data)
821 {
822 // here lies an array, filled with information compiled on the column's data
823 $prepared_column = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
824
825 // here we add the definition of the new column to the list of columns
826 $columns[] = "\t {$column_name} " . $prepared_column['column_type_sql'];
827
828 // see if we have found a primary key set due to a column definition,
829 // if we have found it, we can stop looking
830 if (!$primary_key_gen)
831 {
832 $primary_key_gen = isset($prepared_column['primary_key_set']) && $prepared_column['primary_key_set'];
833 }
834
835 // create textimage DDL based off of the existance of certain column types
836 if (!$create_textimage)
837 {
838 $create_textimage = isset($prepared_column['textimage']) && $prepared_column['textimage'];
839 }
840
841 // create sequence DDL based off of the existance of auto incrementing columns
842 if (!$create_sequence && isset($prepared_column['auto_increment']) && $prepared_column['auto_increment'])
843 {
844 $create_sequence = $column_name;
845 }
846 }
847
848 // this makes up all the columns in the create table statement
849 $table_sql .= implode(",\n", $columns);
850
851 switch ($this->db->dbms_type)
852 {
853 case 'firebird':
854 $table_sql .= "\n);";
855 $statements[] = $table_sql;
856 break;
857
858 case 'mssql':
859 $table_sql .= "\n) ON [PRIMARY]" . (($create_textimage) ? ' TEXTIMAGE_ON [PRIMARY]' : '');
860 $statements[] = $table_sql;
861 break;
862 }
863
864 // we have yet to create a primary key for this table,
865 // this means that we can add the one we really wanted instead
866 if (!$primary_key_gen)
867 {
868 // Write primary key
869 if (isset($table_data['PRIMARY_KEY']))
870 {
871 if (!is_array($table_data['PRIMARY_KEY']))
872 {
873 $table_data['PRIMARY_KEY'] = array($table_data['PRIMARY_KEY']);
874 }
875
876 switch ($this->db->dbms_type)
877 {
878 case 'mysql':
879 case 'postgres':
880 case 'db2':
881 case 'sqlite':
882 $table_sql .= ",\n\t PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ')';
883 break;
884
885 case 'firebird':
886 case 'mssql':
887 $primary_key_stmts = $this->sql_create_primary_key($table_name, $table_data['PRIMARY_KEY']);
888 foreach ($primary_key_stmts as $pk_stmt)
889 {
890 $statements[] = $pk_stmt;
891 }
892 break;
893
894 case 'oracle':
895 $table_sql .= ",\n\t CONSTRAINT pk_{$table_name} PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ')';
896 break;
897 }
898 }
899 }
900
901
902 // close the table
903 switch ($this->db->dbms_type)
904 {
905 case 'mysql':
906 // make sure the table is in UTF-8 mode
907 $table_sql .= "\n) CHARACTER SET `utf8` COLLATE `utf8_bin`;";
908 $statements[] = $table_sql;
909 break;
910
911 case 'postgres':
912 // do we need to add a sequence for auto incrementing columns?
913 if ($create_sequence)
914 {
915 $statements[] = "CREATE SEQUENCE {$table_name}_seq;";
916 }
917
918 $table_sql .= "\n);";
919 $statements[] = $table_sql;
920 break;
921
922 case 'db2':
923 case 'sqlite':
924 $table_sql .= "\n);";
925 $statements[] = $table_sql;
926 break;
927
928 case 'oracle':
929 $table_sql .= "\n);";
930 $statements[] = $table_sql;
931
932 // do we need to add a sequence and a tigger for auto incrementing columns?
933 if ($create_sequence)
934 {
935 // create the actual sequence
936 $statements[] = "CREATE SEQUENCE {$table_name}_seq";
937
938 // the trigger is the mechanism by which we increment the counter
939 $trigger = "CREATE OR REPLACE TRIGGER t_{$table_name}\n";
940 $trigger .= "BEFORE INSERT ON {$table_name}\n";
941 $trigger .= "FOR EACH ROW WHEN (\n";
942 $trigger .= "\tnew.{$create_sequence} IS NULL OR new.{$create_sequence} = 0\n";
943 $trigger .= ")\n";
944 $trigger .= "BEGIN\n";
945 $trigger .= "\tSELECT {$table_name}_seq.nextval\n";
946 $trigger .= "\tINTO :new.{$create_sequence}\n";
947 $trigger .= "\tFROM dual\n";
948 $trigger .= "END;";
949
950 $statements[] = $trigger;
951 }
952 break;
953
954 case 'firebird':
955 if ($create_sequence)
956 {
957 $statements[] = "CREATE SEQUENCE {$table_name}_seq;";
958 }
959 break;
960 }
961
962 // Write Keys
963 if (isset($table_data['KEYS']))
964 {
965 foreach ($table_data['KEYS'] as $key_name => $key_data)
966 {
967 if (!is_array($key_data[1]))
968 {
969 $key_data[1] = array($key_data[1]);
970 }
971
972 $key_stmts = ($key_data[0] == 'UNIQUE') ? $this->sql_create_unique_index($table_name, $key_name, $key_data[1]) : $this->sql_create_index($table_name, $key_name, $key_data[1]);
973 foreach ($key_stmts as $key_stmt)
974 {
975 $statements[] = $key_stmt;
976 }
977 }
978 }
979
980 return $this->_sql_run_sql($statements);
981 }
982
983 /**
984 * Add new column
985 */
986 public function sql_column_add($table_name, $column_name, $column_data)
987 {
988 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
989 $statements = array();
990
991 switch ($this->db->dbms_type)
992 {
993 case 'firebird':
994 $statements[] = 'ALTER TABLE "' . $table_name . '" ADD "' . $column_name . '" ' . $column_data['column_type_sql'];
995 break;
996
997 case 'mssql':
998 $statements[] = 'ALTER TABLE [' . $table_name . '] ADD [' . $column_name . '] ' . $column_data['column_type_sql_default'];
999 break;
1000
1001 case 'mysql':
1002 $statements[] = 'ALTER TABLE `' . $table_name . '` ADD COLUMN `' . $column_name . '` ' . $column_data['column_type_sql'];
1003 break;
1004
1005 case 'oracle':
1006 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' ' . $column_data['column_type_sql'];
1007 break;
1008
1009 case 'postgres':
1010 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD COLUMN "' . $column_name . '" ' . $column_data['column_type_sql'];
1011 break;
1012
1013 case 'db2':
1014 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' ' . $column_data['column_type_sql'];
1015 break;
1016
1017 case 'sqlite':
1018 $sql = "SELECT sql
1019 FROM sqlite_master
1020 WHERE type = 'table'
1021 AND name = '{$table_name}'
1022 ORDER BY type DESC, name;";
1023 $result = $this->db->sql_query($sql);
1024
1025 if (!$result)
1026 {
1027 break;
1028 }
1029
1030 $row = $this->db->sql_fetchrow($result);
1031 $this->db->sql_freeresult($result);
1032
1033 $statements[] = 'begin';
1034
1035 // Create a backup table and populate it, destroy the existing one
1036 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
1037 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
1038 $statements[] = 'DROP TABLE ' . $table_name;
1039
1040 preg_match('#\((.*)\)#s', $row['sql'], $matches);
1041
1042 $new_table_cols = trim($matches[1]);
1043 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
1044 $column_list = array();
1045
1046 foreach ($old_table_cols as $declaration)
1047 {
1048 $entities = preg_split('#\s+#', trim($declaration));
1049 if ($entities[0] == 'PRIMARY')
1050 {
1051 continue;
1052 }
1053 $column_list[] = $entities[0];
1054 }
1055
1056 $columns = implode(',', $column_list);
1057
1058 $new_table_cols = $column_name . ' ' . $column_data['column_type_sql'] . ',' . $new_table_cols;
1059
1060 // create a new table and fill it up. destroy the temp one
1061 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');';
1062 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
1063 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
1064
1065 $statements[] = 'commit';
1066 break;
1067 }
1068
1069 return $this->_sql_run_sql($statements);
1070 }
1071
1072 /**
1073 * Drop column
1074 */
1075 public function sql_column_remove($table_name, $column_name)
1076 {
1077 $statements = array();
1078
1079 switch ($this->db->dbms_type)
1080 {
1081 case 'firebird':
1082 $statements[] = 'ALTER TABLE "' . $table_name . '" DROP "' . $column_name . '"';
1083 break;
1084
1085 case 'mssql':
1086 $statements[] = 'ALTER TABLE [' . $table_name . '] DROP COLUMN [' . $column_name . ']';
1087 break;
1088
1089 case 'mysql':
1090 $statements[] = 'ALTER TABLE `' . $table_name . '` DROP COLUMN `' . $column_name . '`';
1091 break;
1092
1093 case 'oracle':
1094 $statements[] = 'ALTER TABLE ' . $table_name . ' DROP ' . $column_name;
1095 break;
1096
1097 case 'postgres':
1098 $statements[] = 'ALTER TABLE ' . $table_name . ' DROP COLUMN "' . $column_name . '"';
1099 break;
1100
1101 case 'db2':
1102 $statements[] = 'ALTER TABLE ' . $table_name . ' DROP ' . $column_name;
1103 break;
1104
1105 case 'sqlite':
1106 $sql = "SELECT sql
1107 FROM sqlite_master
1108 WHERE type = 'table'
1109 AND name = '{$table_name}'
1110 ORDER BY type DESC, name;";
1111 $result = $this->db->sql_query($sql);
1112
1113 if (!$result)
1114 {
1115 break;
1116 }
1117
1118 $row = $this->db->sql_fetchrow($result);
1119 $this->db->sql_freeresult($result);
1120
1121 $statements[] = 'begin';
1122
1123 // Create a backup table and populate it, destroy the existing one
1124 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
1125 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
1126 $statements[] = 'DROP TABLE ' . $table_name;
1127
1128 preg_match('#\((.*)\)#s', $row['sql'], $matches);
1129
1130 $new_table_cols = trim($matches[1]);
1131 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
1132 $column_list = array();
1133
1134 foreach ($old_table_cols as $declaration)
1135 {
1136 $entities = preg_split('#\s+#', trim($declaration));
1137 if ($entities[0] == 'PRIMARY' || $entities[0] === $column_name)
1138 {
1139 continue;
1140 }
1141 $column_list[] = $entities[0];
1142 }
1143
1144 $columns = implode(',', $column_list);
1145
1146 $new_table_cols = $new_table_cols = preg_replace('/' . $column_name . '[^,]+(?:,|$)/m', '', $new_table_cols);
1147
1148 // create a new table and fill it up. destroy the temp one
1149 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');';
1150 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
1151 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
1152
1153 $statements[] = 'commit';
1154 break;
1155 }
1156
1157 return $this->_sql_run_sql($statements);
1158 }
1159
1160 /**
1161 * Drop Index
1162 */
1163 public function sql_index_drop($table_name, $index_name)
1164 {
1165 $statements = array();
1166
1167 switch ($this->db->dbms_type)
1168 {
1169 case 'mssql':
1170 $statements[] = 'DROP INDEX ' . $table_name . '.' . $index_name;
1171 break;
1172
1173 case 'mysql':
1174 $statements[] = 'DROP INDEX ' . $index_name . ' ON ' . $table_name;
1175 break;
1176
1177 case 'firebird':
1178 case 'oracle':
1179 case 'postgres':
1180 case 'sqlite':
1181 case 'db2':
1182 $statements[] = 'DROP INDEX ' . $table_name . '_' . $index_name;
1183 break;
1184 }
1185
1186 return $this->_sql_run_sql($statements);
1187 }
1188
1189 /**
1190 * Drop Table
1191 */
1192 public function sql_table_drop($table_name)
1193 {
1194 $statements = array();
1195
1196 // the most basic operation, get rid of the table
1197 $statements[] = 'DROP TABLE ' . $table_name;
1198
1199 switch ($this->db->dbms_type)
1200 {
1201 case 'firebird':
1202 $sql = 'SELECT RDB$GENERATOR_NAME as gen
1203 FROM RDB$GENERATORS
1204 WHERE RDB$SYSTEM_FLAG = 0
1205 AND RDB$GENERATOR_NAME = \'' . strtoupper($table_name) . "_GEN'";
1206 $result = $this->db->sql_query($sql);
1207
1208 // does a generator exist?
1209 if ($row = $this->db->sql_fetchrow($result))
1210 {
1211 $statements[] = "DROP GENERATOR {$row['gen']};";
1212 }
1213 $this->db->sql_freeresult($result);
1214 break;
1215
1216 case 'oracle':
1217 $sql = 'SELECT A.REFERENCED_NAME
1218 FROM USER_DEPENDENCIES A, USER_TRIGGERS B
1219 WHERE A.REFERENCED_TYPE = \'SEQUENCE\'
1220 AND A.NAME = B.TRIGGER_NAME
1221 AND B.TABLE_NAME = \'' . strtoupper($table_name) . "'";
1222 $result = $this->db->sql_query($sql);
1223
1224 // any sequences ref'd to this table's triggers?
1225 while ($row = $this->db->sql_fetchrow($result))
1226 {
1227 $statements[] = "DROP SEQUENCE {$row['referenced_name']}";
1228 }
1229 $this->db->sql_freeresult($result);
1230
1231 case 'postgres':
1232 // PGSQL does not "tightly" bind sequences and tables, we must guess...
1233 $sql = "SELECT relname
1234 FROM pg_class
1235 WHERE relkind = 'S'
1236 AND relname = '{$table_name}_seq'";
1237 $result = $this->db->sql_query($sql);
1238
1239 // We don't even care about storing the results. We already know the answer if we get rows back.
1240 if ($this->db->sql_fetchrow($result))
1241 {
1242 $statements[] = "DROP SEQUENCE {$table_name}_seq;\n";
1243 }
1244 $this->db->sql_freeresult($result);
1245 break;
1246 }
1247
1248 return $this->_sql_run_sql($statements);
1249 }
1250
1251 /**
1252 * Add primary key
1253 */
1254 public function sql_create_primary_key($table_name, $column)
1255 {
1256 $statements = array();
1257
1258 switch ($this->db->dbms_type)
1259 {
1260 case 'firebird':
1261 case 'postgres':
1262 case 'mysql':
1263 case 'db2':
1264 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD PRIMARY KEY (' . implode(', ', $column) . ')';
1265 break;
1266
1267 case 'mssql':
1268 $sql = "ALTER TABLE [{$table_name}] WITH NOCHECK ADD ";
1269 $sql .= "CONSTRAINT [PK_{$table_name}] PRIMARY KEY CLUSTERED (";
1270 $sql .= '[' . implode("],\n\t\t[", $column) . ']';
1271 $sql .= ') ON [PRIMARY]';
1272
1273 $statements[] = $sql;
1274 break;
1275
1276 case 'oracle':
1277 $statements[] = 'ALTER TABLE ' . $table_name . 'add CONSTRAINT pk_' . $table_name . ' PRIMARY KEY (' . implode(', ', $column) . ')';
1278 break;
1279
1280 case 'sqlite':
1281 $sql = "SELECT sql
1282 FROM sqlite_master
1283 WHERE type = 'table'
1284 AND name = '{$table_name}'
1285 ORDER BY type DESC, name;";
1286 $result = $this->db->sql_query($sql);
1287
1288 if (!$result)
1289 {
1290 break;
1291 }
1292
1293 $row = $this->db->sql_fetchrow($result);
1294 $this->db->sql_freeresult($result);
1295
1296 $statements[] = 'begin';
1297
1298 // Create a backup table and populate it, destroy the existing one
1299 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
1300 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
1301 $statements[] = 'DROP TABLE ' . $table_name;
1302
1303 preg_match('#\((.*)\)#s', $row['sql'], $matches);
1304
1305 $new_table_cols = trim($matches[1]);
1306 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
1307 $column_list = array();
1308
1309 foreach ($old_table_cols as $declaration)
1310 {
1311 $entities = preg_split('#\s+#', trim($declaration));
1312 if ($entities[0] == 'PRIMARY')
1313 {
1314 continue;
1315 }
1316 $column_list[] = $entities[0];
1317 }
1318
1319 $columns = implode(',', $column_list);
1320
1321 // create a new table and fill it up. destroy the temp one
1322 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ', PRIMARY KEY (' . implode(', ', $column) . '));';
1323 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
1324 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
1325
1326 $statements[] = 'commit';
1327 break;
1328 }
1329
1330 return $this->_sql_run_sql($statements);
1331 }
1332
1333 /**
1334 * Add unique index
1335 */
1336 public function sql_create_unique_index($table_name, $index_name, $column)
1337 {
1338 $statements = array();
1339
1340 switch ($this->db->dbms_type)
1341 {
1342 case 'firebird':
1343 case 'postgres':
1344 case 'oracle':
1345 case 'sqlite':
1346 case 'db2':
1347 $statements[] = 'CREATE UNIQUE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
1348 break;
1349
1350 case 'mysql':
1351 $statements[] = 'CREATE UNIQUE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
1352 break;
1353
1354 case 'mssql':
1355 $statements[] = 'CREATE UNIQUE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ') ON [PRIMARY]';
1356 break;
1357 }
1358
1359 return $this->_sql_run_sql($statements);
1360 }
1361
1362 /**
1363 * Add index
1364 */
1365 public function sql_create_index($table_name, $index_name, $column)
1366 {
1367 $statements = array();
1368
1369 switch ($this->db->dbms_type)
1370 {
1371 case 'firebird':
1372 case 'postgres':
1373 case 'oracle':
1374 case 'sqlite':
1375 case 'db2':
1376 $statements[] = 'CREATE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
1377 break;
1378
1379 case 'mysql':
1380 $statements[] = 'CREATE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
1381 break;
1382
1383 case 'mssql':
1384 $statements[] = 'CREATE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ') ON [PRIMARY]';
1385 break;
1386 }
1387
1388 return $this->_sql_run_sql($statements);
1389 }
1390
1391 /**
1392 * List all of the indices that belong to a table,
1393 * does not count:
1394 * * UNIQUE indices
1395 * * PRIMARY keys
1396 */
1397 public function sql_list_index($table_name)
1398 {
1399 $index_array = array();
1400
1401 if ($this->db->dbms_type == 'mssql')
1402 {
1403 $sql = "EXEC sp_statistics '$table_name'";
1404 $result = $this->db->sql_query($sql);
1405 while ($row = $this->db->sql_fetchrow($result))
1406 {
1407 if ($row['TYPE'] == 3)
1408 {
1409 $index_array[] = $row['INDEX_NAME'];
1410 }
1411 }
1412 $this->db->sql_freeresult($result);
1413 }
1414 else
1415 {
1416 switch ($this->db->dbms_type)
1417 {
1418 case 'firebird':
1419 $sql = "SELECT LOWER(RDB\$INDEX_NAME) as index_name
1420 FROM RDB\$INDICES
1421 WHERE RDB\$RELATION_NAME = " . strtoupper($table_name) . "
1422 AND RDB\$UNIQUE_FLAG IS NULL
1423 AND RDB\$FOREIGN_KEY IS NULL";
1424 $col = 'index_name';
1425 break;
1426
1427 case 'postgres':
1428 $sql = "SELECT ic.relname as index_name
1429 FROM pg_class bc, pg_class ic, pg_index i
1430 WHERE (bc.oid = i.indrelid)
1431 AND (ic.oid = i.indexrelid)
1432 AND (bc.relname = '" . $table_name . "')
1433 AND (i.indisunique != 't')
1434 AND (i.indisprimary != 't')";
1435 $col = 'index_name';
1436 break;
1437
1438 case 'mysql':
1439 $sql = 'SHOW KEYS
1440 FROM ' . $table_name;
1441 $col = 'Key_name';
1442 break;
1443
1444 case 'oracle':
1445 $sql = "SELECT index_name
1446 FROM user_indexes
1447 WHERE table_name = '" . $table_name . "'
1448 AND generated = 'N'";
1449 break;
1450
1451 case 'sqlite':
1452 $sql = "PRAGMA index_info('" . $table_name . "');";
1453 $col = 'name';
1454 break;
1455
1456 case 'db2':
1457 $sql = "SELECT indname
1458 FROM SYSCAT.INDEXES
1459 WHERE TABNAME = '$table_name'
1460 AND UNIQUERULE <> 'P'";
1461 $col = 'name';
1462 }
1463
1464 $result = $this->db->sql_query($sql);
1465 while ($row = $this->db->sql_fetchrow($result))
1466 {
1467 if ($this->db->dbms_type == 'mysql' && !$row['Non_unique'])
1468 {
1469 continue;
1470 }
1471
1472 switch ($this->db->dbms_type)
1473 {
1474 case 'firebird':
1475 case 'oracle':
1476 case 'postgres':
1477 case 'sqlite':
1478 case 'db2':
1479 $row[$col] = substr($row[$col], strlen($table_name) + 1);
1480 break;
1481 }
1482
1483 $index_array[] = $row[$col];
1484 }
1485 $this->db->sql_freeresult($result);
1486 }
1487
1488 return array_map('strtolower', $index_array);
1489 }
1490
1491 /**
1492 * Change column type (not name!)
1493 */
1494 public function sql_column_change($table_name, $column_name, $column_data)
1495 {
1496 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
1497 $statements = array();
1498
1499 switch ($this->db->dbms_type)
1500 {
1501 case 'firebird':
1502 // Change type...
1503 $statements[] = 'ALTER TABLE "' . $table_name . '" ALTER COLUMN "' . $column_name . '" TYPE ' . ' ' . $column_data['column_type_sql'];
1504 break;
1505
1506 case 'mssql':
1507 $statements[] = 'ALTER TABLE [' . $table_name . '] ALTER COLUMN [' . $column_name . '] ' . $column_data['column_type_sql'];
1508 break;
1509
1510 case 'mysql':
1511 $statements[] = 'ALTER TABLE `' . $table_name . '` CHANGE `' . $column_name . '` `' . $column_name . '` ' . $column_data['column_type_sql'];
1512 break;
1513
1514 case 'oracle':
1515 $statements[] = 'ALTER TABLE ' . $table_name . ' MODIFY ' . $column_name . ' ' . $column_data['column_type_sql'];
1516 break;
1517
1518 case 'db2':
1519 $statements[] = 'ALTER TABLE ' . $table_name . ' ALTER ' . $column_name . ' SET DATA TYPE ' . $column_data['column_type_sql'];
1520 break;
1521
1522 case 'postgres':
1523 $sql = 'ALTER TABLE ' . $table_name . ' ';
1524
1525 $sql_array = array();
1526 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' TYPE ' . $column_data['column_type'];
1527
1528 if (isset($column_data['null']))
1529 {
1530 if ($column_data['null'] == 'NOT NULL')
1531 {
1532 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' SET NOT NULL';
1533 }
1534 else if ($column_data['null'] == 'NULL')
1535 {
1536 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' DROP NOT NULL';
1537 }
1538 }
1539
1540 if (isset($column_data['default']))
1541 {
1542 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' SET DEFAULT ' . $column_data['default'];
1543 }
1544
1545 // we don't want to double up on constraints if we change different number data types
1546 if (isset($column_data['constraint']))
1547 {
1548 $constraint_sql = "SELECT consrc as constraint_data
1549 FROM pg_constraint, pg_class bc
1550 WHERE conrelid = bc.oid
1551 AND bc.relname = '{$table_name}'
1552 AND NOT EXISTS (
1553 SELECT *
1554 FROM pg_constraint as c, pg_inherits as i
1555 WHERE i.inhrelid = pg_constraint.conrelid
1556 AND c.conname = pg_constraint.conname
1557 AND c.consrc = pg_constraint.consrc
1558 AND c.conrelid = i.inhparent
1559 )";
1560
1561 $constraint_exists = false;
1562
1563 $result = $this->db->sql_query($constraint_sql);
1564 while ($row = $this->db->sql_fetchrow($result))
1565 {
1566 if (trim($row['constraint_data']) == trim($column_data['constraint']))
1567 {
1568 $constraint_exists = true;
1569 break;
1570 }
1571 }
1572 $this->db->sql_freeresult($result);
1573
1574 if (!$constraint_exists)
1575 {
1576 $sql_array[] = 'ADD ' . $column_data['constraint'];
1577 }
1578 }
1579
1580 $sql .= implode(', ', $sql_array);
1581
1582 $statements[] = $sql;
1583 break;
1584
1585 case 'sqlite':
1586 $sql = "SELECT sql
1587 FROM sqlite_master
1588 WHERE type = 'table'
1589 AND name = '{$table_name}'
1590 ORDER BY type DESC, name;";
1591 $result = $this->db->sql_query($sql);
1592
1593 if (!$result)
1594 {
1595 break;
1596 }
1597
1598 $row = $this->db->sql_fetchrow($result);
1599 $this->db->sql_freeresult($result);
1600
1601 $statements[] = 'begin';
1602
1603 // Create a temp table and populate it, destroy the existing one
1604 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
1605 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
1606 $statements[] = 'DROP TABLE ' . $table_name;
1607
1608 preg_match('#\((.*)\)#s', $row['sql'], $matches);
1609
1610 $new_table_cols = trim($matches[1]);
1611 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
1612 $column_list = array();
1613
1614 foreach ($old_table_cols as $key => $declaration)
1615 {
1616 $entities = preg_split('#\s+#', trim($declaration));
1617 $column_list[] = $entities[0];
1618 if ($entities[0] == $column_name)
1619 {
1620 $old_table_cols[$key] = $column_name . ' ' . $column_data['column_type_sql'];
1621 }
1622 }
1623
1624 $columns = implode(',', $column_list);
1625
1626 // create a new table and fill it up. destroy the temp one
1627 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . implode(',', $old_table_cols) . ');';
1628 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
1629 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
1630
1631 $statements[] = 'commit';
1632
1633 break;
1634 }
1635
1636 return $this->_sql_run_sql($statements);
1637 }
1638 }
1639
1640 ?>
phpbb is a php-based BBS (buletin board system)