source: trunk/forum/includes/db/db_tools.php

Last change on this file was 702, checked in by george, 15 years ago
  • Upraveno: Aktualizace fóra.
File size: 55.4 KB
Line 
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*/
14if (!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*/
26class phpbb_db_tools
27{
28 /**
29 * Current sql layer
30 */
31 var $sql_layer = '';
32
33 /**
34 * @var object DB object
35 */
36 var $db = NULL;
37
38 /**
39 * The Column types for every database we support
40 * @var array
41 */
42 var $dbms_type_map = array(
43 'mysql_41' => array(
44 'INT:' => 'int(%d)',
45 'BINT' => 'bigint(20)',
46 'UINT' => 'mediumint(8) UNSIGNED',
47 'UINT:' => 'int(%d) UNSIGNED',
48 'TINT:' => 'tinyint(%d)',
49 'USINT' => 'smallint(4) UNSIGNED',
50 'BOOL' => 'tinyint(1) UNSIGNED',
51 'VCHAR' => 'varchar(255)',
52 'VCHAR:' => 'varchar(%d)',
53 'CHAR:' => 'char(%d)',
54 'XSTEXT' => 'text',
55 'XSTEXT_UNI'=> 'varchar(100)',
56 'STEXT' => 'text',
57 'STEXT_UNI' => 'varchar(255)',
58 'TEXT' => 'text',
59 'TEXT_UNI' => 'text',
60 'MTEXT' => 'mediumtext',
61 'MTEXT_UNI' => 'mediumtext',
62 'TIMESTAMP' => 'int(11) UNSIGNED',
63 'DECIMAL' => 'decimal(5,2)',
64 'DECIMAL:' => 'decimal(%d,2)',
65 'PDECIMAL' => 'decimal(6,3)',
66 'PDECIMAL:' => 'decimal(%d,3)',
67 'VCHAR_UNI' => 'varchar(255)',
68 'VCHAR_UNI:'=> 'varchar(%d)',
69 'VCHAR_CI' => 'varchar(255)',
70 'VARBINARY' => 'varbinary(255)',
71 ),
72
73 'mysql_40' => array(
74 'INT:' => 'int(%d)',
75 'BINT' => 'bigint(20)',
76 'UINT' => 'mediumint(8) UNSIGNED',
77 'UINT:' => 'int(%d) UNSIGNED',
78 'TINT:' => 'tinyint(%d)',
79 'USINT' => 'smallint(4) UNSIGNED',
80 'BOOL' => 'tinyint(1) UNSIGNED',
81 'VCHAR' => 'varbinary(255)',
82 'VCHAR:' => 'varbinary(%d)',
83 'CHAR:' => 'binary(%d)',
84 'XSTEXT' => 'blob',
85 'XSTEXT_UNI'=> 'blob',
86 'STEXT' => 'blob',
87 'STEXT_UNI' => 'blob',
88 'TEXT' => 'blob',
89 'TEXT_UNI' => 'blob',
90 'MTEXT' => 'mediumblob',
91 'MTEXT_UNI' => 'mediumblob',
92 'TIMESTAMP' => 'int(11) UNSIGNED',
93 'DECIMAL' => 'decimal(5,2)',
94 'DECIMAL:' => 'decimal(%d,2)',
95 'PDECIMAL' => 'decimal(6,3)',
96 'PDECIMAL:' => 'decimal(%d,3)',
97 'VCHAR_UNI' => 'blob',
98 'VCHAR_UNI:'=> array('varbinary(%d)', 'limit' => array('mult', 3, 255, 'blob')),
99 'VCHAR_CI' => 'blob',
100 'VARBINARY' => 'varbinary(255)',
101 ),
102
103 'firebird' => array(
104 'INT:' => 'INTEGER',
105 'BINT' => 'DOUBLE PRECISION',
106 'UINT' => 'INTEGER',
107 'UINT:' => 'INTEGER',
108 'TINT:' => 'INTEGER',
109 'USINT' => 'INTEGER',
110 'BOOL' => 'INTEGER',
111 'VCHAR' => 'VARCHAR(255) CHARACTER SET NONE',
112 'VCHAR:' => 'VARCHAR(%d) CHARACTER SET NONE',
113 'CHAR:' => 'CHAR(%d) CHARACTER SET NONE',
114 'XSTEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
115 'STEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
116 'TEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
117 'MTEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
118 'XSTEXT_UNI'=> 'VARCHAR(100) CHARACTER SET UTF8',
119 'STEXT_UNI' => 'VARCHAR(255) CHARACTER SET UTF8',
120 'TEXT_UNI' => 'BLOB SUB_TYPE TEXT CHARACTER SET UTF8',
121 'MTEXT_UNI' => 'BLOB SUB_TYPE TEXT CHARACTER SET UTF8',
122 'TIMESTAMP' => 'INTEGER',
123 'DECIMAL' => 'DOUBLE PRECISION',
124 'DECIMAL:' => 'DOUBLE PRECISION',
125 'PDECIMAL' => 'DOUBLE PRECISION',
126 'PDECIMAL:' => 'DOUBLE PRECISION',
127 'VCHAR_UNI' => 'VARCHAR(255) CHARACTER SET UTF8',
128 'VCHAR_UNI:'=> 'VARCHAR(%d) CHARACTER SET UTF8',
129 'VCHAR_CI' => 'VARCHAR(255) CHARACTER SET UTF8',
130 'VARBINARY' => 'CHAR(255) CHARACTER SET NONE',
131 ),
132
133 'mssql' => array(
134 'INT:' => '[int]',
135 'BINT' => '[float]',
136 'UINT' => '[int]',
137 'UINT:' => '[int]',
138 'TINT:' => '[int]',
139 'USINT' => '[int]',
140 'BOOL' => '[int]',
141 'VCHAR' => '[varchar] (255)',
142 'VCHAR:' => '[varchar] (%d)',
143 'CHAR:' => '[char] (%d)',
144 'XSTEXT' => '[varchar] (1000)',
145 'STEXT' => '[varchar] (3000)',
146 'TEXT' => '[varchar] (8000)',
147 'MTEXT' => '[text]',
148 'XSTEXT_UNI'=> '[varchar] (100)',
149 'STEXT_UNI' => '[varchar] (255)',
150 'TEXT_UNI' => '[varchar] (4000)',
151 'MTEXT_UNI' => '[text]',
152 'TIMESTAMP' => '[int]',
153 'DECIMAL' => '[float]',
154 'DECIMAL:' => '[float]',
155 'PDECIMAL' => '[float]',
156 'PDECIMAL:' => '[float]',
157 'VCHAR_UNI' => '[varchar] (255)',
158 'VCHAR_UNI:'=> '[varchar] (%d)',
159 'VCHAR_CI' => '[varchar] (255)',
160 'VARBINARY' => '[varchar] (255)',
161 ),
162
163 'oracle' => array(
164 'INT:' => 'number(%d)',
165 'BINT' => 'number(20)',
166 'UINT' => 'number(8)',
167 'UINT:' => 'number(%d)',
168 'TINT:' => 'number(%d)',
169 'USINT' => 'number(4)',
170 'BOOL' => 'number(1)',
171 'VCHAR' => 'varchar2(255)',
172 'VCHAR:' => 'varchar2(%d)',
173 'CHAR:' => 'char(%d)',
174 'XSTEXT' => 'varchar2(1000)',
175 'STEXT' => 'varchar2(3000)',
176 'TEXT' => 'clob',
177 'MTEXT' => 'clob',
178 'XSTEXT_UNI'=> 'varchar2(300)',
179 'STEXT_UNI' => 'varchar2(765)',
180 'TEXT_UNI' => 'clob',
181 'MTEXT_UNI' => 'clob',
182 'TIMESTAMP' => 'number(11)',
183 'DECIMAL' => 'number(5, 2)',
184 'DECIMAL:' => 'number(%d, 2)',
185 'PDECIMAL' => 'number(6, 3)',
186 'PDECIMAL:' => 'number(%d, 3)',
187 'VCHAR_UNI' => 'varchar2(765)',
188 'VCHAR_UNI:'=> array('varchar2(%d)', 'limit' => array('mult', 3, 765, 'clob')),
189 'VCHAR_CI' => 'varchar2(255)',
190 'VARBINARY' => 'raw(255)',
191 ),
192
193 'sqlite' => array(
194 'INT:' => 'int(%d)',
195 'BINT' => 'bigint(20)',
196 'UINT' => 'INTEGER UNSIGNED', //'mediumint(8) UNSIGNED',
197 'UINT:' => 'INTEGER UNSIGNED', // 'int(%d) UNSIGNED',
198 'TINT:' => 'tinyint(%d)',
199 'USINT' => 'INTEGER UNSIGNED', //'mediumint(4) UNSIGNED',
200 'BOOL' => 'INTEGER UNSIGNED', //'tinyint(1) UNSIGNED',
201 'VCHAR' => 'varchar(255)',
202 'VCHAR:' => 'varchar(%d)',
203 'CHAR:' => 'char(%d)',
204 'XSTEXT' => 'text(65535)',
205 'STEXT' => 'text(65535)',
206 'TEXT' => 'text(65535)',
207 'MTEXT' => 'mediumtext(16777215)',
208 'XSTEXT_UNI'=> 'text(65535)',
209 'STEXT_UNI' => 'text(65535)',
210 'TEXT_UNI' => 'text(65535)',
211 'MTEXT_UNI' => 'mediumtext(16777215)',
212 'TIMESTAMP' => 'INTEGER UNSIGNED', //'int(11) UNSIGNED',
213 'DECIMAL' => 'decimal(5,2)',
214 'DECIMAL:' => 'decimal(%d,2)',
215 'PDECIMAL' => 'decimal(6,3)',
216 'PDECIMAL:' => 'decimal(%d,3)',
217 'VCHAR_UNI' => 'varchar(255)',
218 'VCHAR_UNI:'=> 'varchar(%d)',
219 'VCHAR_CI' => 'varchar(255)',
220 'VARBINARY' => 'blob',
221 ),
222
223 'postgres' => array(
224 'INT:' => 'INT4',
225 'BINT' => 'INT8',
226 'UINT' => 'INT4', // unsigned
227 'UINT:' => 'INT4', // unsigned
228 'USINT' => 'INT2', // unsigned
229 'BOOL' => 'INT2', // unsigned
230 'TINT:' => 'INT2',
231 'VCHAR' => 'varchar(255)',
232 'VCHAR:' => 'varchar(%d)',
233 'CHAR:' => 'char(%d)',
234 'XSTEXT' => 'varchar(1000)',
235 'STEXT' => 'varchar(3000)',
236 'TEXT' => 'varchar(8000)',
237 'MTEXT' => 'TEXT',
238 'XSTEXT_UNI'=> 'varchar(100)',
239 'STEXT_UNI' => 'varchar(255)',
240 'TEXT_UNI' => 'varchar(4000)',
241 'MTEXT_UNI' => 'TEXT',
242 'TIMESTAMP' => 'INT4', // unsigned
243 'DECIMAL' => 'decimal(5,2)',
244 'DECIMAL:' => 'decimal(%d,2)',
245 'PDECIMAL' => 'decimal(6,3)',
246 'PDECIMAL:' => 'decimal(%d,3)',
247 'VCHAR_UNI' => 'varchar(255)',
248 'VCHAR_UNI:'=> 'varchar(%d)',
249 'VCHAR_CI' => 'varchar_ci',
250 'VARBINARY' => 'bytea',
251 ),
252 );
253
254 /**
255 * A list of types being unsigned for better reference in some db's
256 * @var array
257 */
258 var $unsigned_types = array('UINT', 'UINT:', 'USINT', 'BOOL', 'TIMESTAMP');
259
260 /**
261 * A list of supported DBMS. We change this class to support more DBMS, the DBMS itself only need to follow some rules.
262 * @var array
263 */
264 var $supported_dbms = array('firebird', 'mssql', 'mysql_40', 'mysql_41', 'oracle', 'postgres', 'sqlite');
265
266 /**
267 * This is set to true if user only wants to return the 'to-be-executed' SQL statement(s) (as an array).
268 * This mode has no effect on some methods (inserting of data for example). This is expressed within the methods command.
269 */
270 var $return_statements = false;
271
272 /**
273 * Constructor. Set DB Object and set {@link $return_statements return_statements}.
274 *
275 * @param phpbb_dbal $db DBAL object
276 * @param bool $return_statements True if only statements should be returned and no SQL being executed
277 */
278 function phpbb_db_tools(&$db, $return_statements = false)
279 {
280 $this->db = $db;
281 $this->return_statements = $return_statements;
282
283 // Determine mapping database type
284 switch ($this->db->sql_layer)
285 {
286 case 'mysql':
287 $this->sql_layer = 'mysql_40';
288 break;
289
290 case 'mysql4':
291 if (version_compare($this->db->sql_server_info(true), '4.1.3', '>='))
292 {
293 $this->sql_layer = 'mysql_41';
294 }
295 else
296 {
297 $this->sql_layer = 'mysql_40';
298 }
299 break;
300
301 case 'mysqli':
302 $this->sql_layer = 'mysql_41';
303 break;
304
305 case 'mssql':
306 case 'mssql_odbc':
307 $this->sql_layer = 'mssql';
308 break;
309
310 default:
311 $this->sql_layer = $this->db->sql_layer;
312 break;
313 }
314 }
315
316 /**
317 * Check if table exists
318 *
319 *
320 * @param string $table_name The table name to check for
321 * @return bool true if table exists, else false
322 */
323 function sql_table_exists($table_name)
324 {
325 $this->db->sql_return_on_error(true);
326 $result = $this->db->sql_query_limit('SELECT * FROM ' . $table_name, 1);
327 $this->db->sql_return_on_error(false);
328
329 if ($result)
330 {
331 $this->db->sql_freeresult($result);
332 return true;
333 }
334
335 return false;
336 }
337
338 /**
339 * Create SQL Table
340 *
341 * @param string $table_name The table name to create
342 * @param array $table_data Array containing table data.
343 * @return array Statements if $return_statements is true.
344 */
345 function sql_create_table($table_name, $table_data)
346 {
347 // holds the DDL for a column
348 $columns = $statements = array();
349
350 if ($this->sql_table_exists($table_name))
351 {
352 return $this->_sql_run_sql($statements);
353 }
354
355 // Begin transaction
356 $statements[] = 'begin';
357
358 // Determine if we have created a PRIMARY KEY in the earliest
359 $primary_key_gen = false;
360
361 // Determine if the table must be created with TEXTIMAGE
362 $create_textimage = false;
363
364 // Determine if the table requires a sequence
365 $create_sequence = false;
366
367 // Begin table sql statement
368 switch ($this->sql_layer)
369 {
370 case 'mssql':
371 $table_sql = 'CREATE TABLE [' . $table_name . '] (' . "\n";
372 break;
373
374 default:
375 $table_sql = 'CREATE TABLE ' . $table_name . ' (' . "\n";
376 break;
377 }
378
379 // Iterate through the columns to create a table
380 foreach ($table_data['COLUMNS'] as $column_name => $column_data)
381 {
382 // here lies an array, filled with information compiled on the column's data
383 $prepared_column = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
384
385 // here we add the definition of the new column to the list of columns
386 switch ($this->sql_layer)
387 {
388 case 'mssql':
389 $columns[] = "\t [{$column_name}] " . $prepared_column['column_type_sql_default'];
390 break;
391
392 default:
393 $columns[] = "\t {$column_name} " . $prepared_column['column_type_sql'];
394 break;
395 }
396
397 // see if we have found a primary key set due to a column definition if we have found it, we can stop looking
398 if (!$primary_key_gen)
399 {
400 $primary_key_gen = isset($prepared_column['primary_key_set']) && $prepared_column['primary_key_set'];
401 }
402
403 // create textimage DDL based off of the existance of certain column types
404 if (!$create_textimage)
405 {
406 $create_textimage = isset($prepared_column['textimage']) && $prepared_column['textimage'];
407 }
408
409 // create sequence DDL based off of the existance of auto incrementing columns
410 if (!$create_sequence && isset($prepared_column['auto_increment']) && $prepared_column['auto_increment'])
411 {
412 $create_sequence = $column_name;
413 }
414 }
415
416 // this makes up all the columns in the create table statement
417 $table_sql .= implode(",\n", $columns);
418
419 // Close the table for two DBMS and add to the statements
420 switch ($this->sql_layer)
421 {
422 case 'firebird':
423 $table_sql .= "\n);";
424 $statements[] = $table_sql;
425 break;
426
427 case 'mssql':
428 $table_sql .= "\n) ON [PRIMARY]" . (($create_textimage) ? ' TEXTIMAGE_ON [PRIMARY]' : '');
429 $statements[] = $table_sql;
430 break;
431 }
432
433 // we have yet to create a primary key for this table,
434 // this means that we can add the one we really wanted instead
435 if (!$primary_key_gen)
436 {
437 // Write primary key
438 if (isset($table_data['PRIMARY_KEY']))
439 {
440 if (!is_array($table_data['PRIMARY_KEY']))
441 {
442 $table_data['PRIMARY_KEY'] = array($table_data['PRIMARY_KEY']);
443 }
444
445 switch ($this->sql_layer)
446 {
447 case 'mysql_40':
448 case 'mysql_41':
449 case 'postgres':
450 case 'sqlite':
451 $table_sql .= ",\n\t PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ')';
452 break;
453
454 case 'firebird':
455 case 'mssql':
456 // We need the data here
457 $old_return_statements = $this->return_statements;
458 $this->return_statements = true;
459
460 $primary_key_stmts = $this->sql_create_primary_key($table_name, $table_data['PRIMARY_KEY']);
461 foreach ($primary_key_stmts as $pk_stmt)
462 {
463 $statements[] = $pk_stmt;
464 }
465
466 $this->return_statements = $old_return_statements;
467 break;
468
469 case 'oracle':
470 $table_sql .= ",\n\t CONSTRAINT pk_{$table_name} PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ')';
471 break;
472 }
473 }
474 }
475
476 // close the table
477 switch ($this->sql_layer)
478 {
479 case 'mysql_41':
480 // make sure the table is in UTF-8 mode
481 $table_sql .= "\n) CHARACTER SET `utf8` COLLATE `utf8_bin`;";
482 $statements[] = $table_sql;
483 break;
484
485 case 'mysql_40':
486 case 'sqlite':
487 $table_sql .= "\n);";
488 $statements[] = $table_sql;
489 break;
490
491 case 'postgres':
492 // do we need to add a sequence for auto incrementing columns?
493 if ($create_sequence)
494 {
495 $statements[] = "CREATE SEQUENCE {$table_name}_seq;";
496 }
497
498 $table_sql .= "\n);";
499 $statements[] = $table_sql;
500 break;
501
502 case 'oracle':
503 $table_sql .= "\n);";
504 $statements[] = $table_sql;
505
506 // do we need to add a sequence and a tigger for auto incrementing columns?
507 if ($create_sequence)
508 {
509 // create the actual sequence
510 $statements[] = "CREATE SEQUENCE {$table_name}_seq";
511
512 // the trigger is the mechanism by which we increment the counter
513 $trigger = "CREATE OR REPLACE TRIGGER t_{$table_name}\n";
514 $trigger .= "BEFORE INSERT ON {$table_name}\n";
515 $trigger .= "FOR EACH ROW WHEN (\n";
516 $trigger .= "\tnew.{$create_sequence} IS NULL OR new.{$create_sequence} = 0\n";
517 $trigger .= ")\n";
518 $trigger .= "BEGIN\n";
519 $trigger .= "\tSELECT {$table_name}_seq.nextval\n";
520 $trigger .= "\tINTO :new.{$create_sequence}\n";
521 $trigger .= "\tFROM dual\n";
522 $trigger .= "END;";
523
524 $statements[] = $trigger;
525 }
526 break;
527
528 case 'firebird':
529 if ($create_sequence)
530 {
531 $statements[] = "CREATE SEQUENCE {$table_name}_seq;";
532 }
533 break;
534 }
535
536 // Write Keys
537 if (isset($table_data['KEYS']))
538 {
539 foreach ($table_data['KEYS'] as $key_name => $key_data)
540 {
541 if (!is_array($key_data[1]))
542 {
543 $key_data[1] = array($key_data[1]);
544 }
545
546 $old_return_statements = $this->return_statements;
547 $this->return_statements = true;
548
549 $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]);
550
551 foreach ($key_stmts as $key_stmt)
552 {
553 $statements[] = $key_stmt;
554 }
555
556 $this->return_statements = $old_return_statements;
557 }
558 }
559
560 // Commit Transaction
561 $statements[] = 'commit';
562
563 return $this->_sql_run_sql($statements);
564 }
565
566 /**
567 * Handle passed database update array.
568 * Expected structure...
569 * Key being one of the following
570 * change_columns: Column changes (only type, not name)
571 * add_columns: Add columns to a table
572 * drop_keys: Dropping keys
573 * drop_columns: Removing/Dropping columns
574 * add_primary_keys: adding primary keys
575 * add_unique_index: adding an unique index
576 * add_index: adding an index
577 *
578 * The values are in this format:
579 * {TABLE NAME} => array(
580 * {COLUMN NAME} => array({COLUMN TYPE}, {DEFAULT VALUE}, {OPTIONAL VARIABLES}),
581 * {KEY/INDEX NAME} => array({COLUMN NAMES}),
582 * )
583 *
584 * For more information have a look at /develop/create_schema_files.php (only available through SVN)
585 */
586 function perform_schema_changes($schema_changes)
587 {
588 if (empty($schema_changes))
589 {
590 return;
591 }
592
593 $statements = array();
594 $sqlite = false;
595
596 // For SQLite we need to perform the schema changes in a much more different way
597 if ($this->db->sql_layer == 'sqlite' && $this->return_statements)
598 {
599 $sqlite_data = array();
600 $sqlite = true;
601 }
602
603 // Change columns?
604 if (!empty($schema_changes['change_columns']))
605 {
606 foreach ($schema_changes['change_columns'] as $table => $columns)
607 {
608 foreach ($columns as $column_name => $column_data)
609 {
610 // If the column exists we change it, else we add it ;)
611 if ($column_exists = $this->sql_column_exists($table, $column_name))
612 {
613 $result = $this->sql_column_change($table, $column_name, $column_data, true);
614 }
615 else
616 {
617 $result = $this->sql_column_add($table, $column_name, $column_data, true);
618 }
619
620 if ($sqlite)
621 {
622 if ($column_exists)
623 {
624 $sqlite_data[$table]['change_columns'][] = $result;
625 }
626 else
627 {
628 $sqlite_data[$table]['add_columns'][] = $result;
629 }
630 }
631 else if ($this->return_statements)
632 {
633 $statements = array_merge($statements, $result);
634 }
635 }
636 }
637 }
638
639 // Add columns?
640 if (!empty($schema_changes['add_columns']))
641 {
642 foreach ($schema_changes['add_columns'] as $table => $columns)
643 {
644 foreach ($columns as $column_name => $column_data)
645 {
646 // Only add the column if it does not exist yet, else change it (to be consistent)
647 if ($column_exists = $this->sql_column_exists($table, $column_name))
648 {
649 $result = $this->sql_column_change($table, $column_name, $column_data, true);
650 }
651 else
652 {
653 $result = $this->sql_column_add($table, $column_name, $column_data, true);
654 }
655
656 if ($sqlite)
657 {
658 if ($column_exists)
659 {
660 $sqlite_data[$table]['change_columns'][] = $result;
661 }
662 else
663 {
664 $sqlite_data[$table]['add_columns'][] = $result;
665 }
666 }
667 else if ($this->return_statements)
668 {
669 $statements = array_merge($statements, $result);
670 }
671 }
672 }
673 }
674
675 // Remove keys?
676 if (!empty($schema_changes['drop_keys']))
677 {
678 foreach ($schema_changes['drop_keys'] as $table => $indexes)
679 {
680 foreach ($indexes as $index_name)
681 {
682 $result = $this->sql_index_drop($table, $index_name);
683
684 if ($this->return_statements)
685 {
686 $statements = array_merge($statements, $result);
687 }
688 }
689 }
690 }
691
692 // Drop columns?
693 if (!empty($schema_changes['drop_columns']))
694 {
695 foreach ($schema_changes['drop_columns'] as $table => $columns)
696 {
697 foreach ($columns as $column)
698 {
699 // Only remove the column if it exists...
700 if ($this->sql_column_exists($table, $column))
701 {
702 $result = $this->sql_column_remove($table, $column, true);
703
704 if ($sqlite)
705 {
706 $sqlite_data[$table]['drop_columns'][] = $result;
707 }
708 else if ($this->return_statements)
709 {
710 $statements = array_merge($statements, $result);
711 }
712 }
713 }
714 }
715 }
716
717 // Add primary keys?
718 if (!empty($schema_changes['add_primary_keys']))
719 {
720 foreach ($schema_changes['add_primary_keys'] as $table => $columns)
721 {
722 $result = $this->sql_create_primary_key($table, $columns, true);
723
724 if ($sqlite)
725 {
726 $sqlite_data[$table]['primary_key'] = $result;
727 }
728 else if ($this->return_statements)
729 {
730 $statements = array_merge($statements, $result);
731 }
732 }
733 }
734
735 // Add unqiue indexes?
736 if (!empty($schema_changes['add_unique_index']))
737 {
738 foreach ($schema_changes['add_unique_index'] as $table => $index_array)
739 {
740 foreach ($index_array as $index_name => $column)
741 {
742 $result = $this->sql_create_unique_index($table, $index_name, $column);
743
744 if ($this->return_statements)
745 {
746 $statements = array_merge($statements, $result);
747 }
748 }
749 }
750 }
751
752 // Add indexes?
753 if (!empty($schema_changes['add_index']))
754 {
755 foreach ($schema_changes['add_index'] as $table => $index_array)
756 {
757 foreach ($index_array as $index_name => $column)
758 {
759 $result = $this->sql_create_index($table, $index_name, $column);
760
761 if ($this->return_statements)
762 {
763 $statements = array_merge($statements, $result);
764 }
765 }
766 }
767 }
768
769 if ($sqlite)
770 {
771 foreach ($sqlite_data as $table_name => $sql_schema_changes)
772 {
773 // Create temporary table with original data
774 $statements[] = 'begin';
775
776 $sql = "SELECT sql
777 FROM sqlite_master
778 WHERE type = 'table'
779 AND name = '{$table_name}'
780 ORDER BY type DESC, name;";
781 $result = $this->db->sql_query($sql);
782
783 if (!$result)
784 {
785 continue;
786 }
787
788 $row = $this->db->sql_fetchrow($result);
789 $this->db->sql_freeresult($result);
790
791 // Create a backup table and populate it, destroy the existing one
792 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
793 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
794 $statements[] = 'DROP TABLE ' . $table_name;
795
796 // Get the columns...
797 preg_match('#\((.*)\)#s', $row['sql'], $matches);
798
799 $plain_table_cols = trim($matches[1]);
800 $new_table_cols = preg_split('/,(?![\s\w]+\))/m', $plain_table_cols);
801 $column_list = array();
802
803 foreach ($new_table_cols as $declaration)
804 {
805 $entities = preg_split('#\s+#', trim($declaration));
806 if ($entities[0] == 'PRIMARY')
807 {
808 continue;
809 }
810 $column_list[] = $entities[0];
811 }
812
813 // note down the primary key notation because sqlite only supports adding it to the end for the new table
814 $primary_key = false;
815 $_new_cols = array();
816
817 foreach ($new_table_cols as $key => $declaration)
818 {
819 $entities = preg_split('#\s+#', trim($declaration));
820 if ($entities[0] == 'PRIMARY')
821 {
822 $primary_key = $declaration;
823 continue;
824 }
825 $_new_cols[] = $declaration;
826 }
827
828 $new_table_cols = $_new_cols;
829
830 // First of all... change columns
831 if (!empty($sql_schema_changes['change_columns']))
832 {
833 foreach ($sql_schema_changes['change_columns'] as $column_sql)
834 {
835 foreach ($new_table_cols as $key => $declaration)
836 {
837 $entities = preg_split('#\s+#', trim($declaration));
838 if (strpos($column_sql, $entities[0] . ' ') === 0)
839 {
840 $new_table_cols[$key] = $column_sql;
841 }
842 }
843 }
844 }
845
846 if (!empty($sql_schema_changes['add_columns']))
847 {
848 foreach ($sql_schema_changes['add_columns'] as $column_sql)
849 {
850 $new_table_cols[] = $column_sql;
851 }
852 }
853
854 // Now drop them...
855 if (!empty($sql_schema_changes['drop_columns']))
856 {
857 foreach ($sql_schema_changes['drop_columns'] as $column_name)
858 {
859 // Remove from column list...
860 $new_column_list = array();
861 foreach ($column_list as $key => $value)
862 {
863 if ($value === $column_name)
864 {
865 continue;
866 }
867
868 $new_column_list[] = $value;
869 }
870
871 $column_list = $new_column_list;
872
873 // Remove from table...
874 $_new_cols = array();
875 foreach ($new_table_cols as $key => $declaration)
876 {
877 $entities = preg_split('#\s+#', trim($declaration));
878 if (strpos($column_name . ' ', $entities[0] . ' ') === 0)
879 {
880 continue;
881 }
882 $_new_cols[] = $declaration;
883 }
884 $new_table_cols = $_new_cols;
885 }
886 }
887
888 // Primary key...
889 if (!empty($sql_schema_changes['primary_key']))
890 {
891 $new_table_cols[] = 'PRIMARY KEY (' . implode(', ', $sql_schema_changes['primary_key']) . ')';
892 }
893 // Add a new one or the old primary key
894 else if ($primary_key !== false)
895 {
896 $new_table_cols[] = $primary_key;
897 }
898
899 $columns = implode(',', $column_list);
900
901 // create a new table and fill it up. destroy the temp one
902 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . implode(',', $new_table_cols) . ');';
903 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
904 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
905
906 $statements[] = 'commit';
907 }
908 }
909
910 if ($this->return_statements)
911 {
912 return $statements;
913 }
914 }
915
916 /**
917 * Check if a specified column exist
918 *
919 * @param string $table Table to check the column at
920 * @param string $column_name The column to check
921 *
922 * @return bool True if column exists, else false
923 */
924 function sql_column_exists($table, $column_name)
925 {
926 switch ($this->sql_layer)
927 {
928 case 'mysql_40':
929 case 'mysql_41':
930
931 $sql = "SHOW COLUMNS FROM $table";
932 $result = $this->db->sql_query($sql);
933
934 while ($row = $this->db->sql_fetchrow($result))
935 {
936 // lower case just in case
937 if (strtolower($row['Field']) == $column_name)
938 {
939 $this->db->sql_freeresult($result);
940 return true;
941 }
942 }
943 $this->db->sql_freeresult($result);
944 return false;
945 break;
946
947 // PostgreSQL has a way of doing this in a much simpler way but would
948 // not allow us to support all versions of PostgreSQL
949 case 'postgres':
950 $sql = "SELECT a.attname
951 FROM pg_class c, pg_attribute a
952 WHERE c.relname = '{$table}'
953 AND a.attnum > 0
954 AND a.attrelid = c.oid";
955 $result = $this->db->sql_query($sql);
956 while ($row = $this->db->sql_fetchrow($result))
957 {
958 // lower case just in case
959 if (strtolower($row['attname']) == $column_name)
960 {
961 $this->db->sql_freeresult($result);
962 return true;
963 }
964 }
965 $this->db->sql_freeresult($result);
966
967 return false;
968 break;
969
970 // same deal with PostgreSQL, we must perform more complex operations than
971 // we technically could
972 case 'mssql':
973 $sql = "SELECT c.name
974 FROM syscolumns c
975 LEFT JOIN sysobjects o ON c.id = o.id
976 WHERE o.name = '{$table}'";
977 $result = $this->db->sql_query($sql);
978 while ($row = $this->db->sql_fetchrow($result))
979 {
980 // lower case just in case
981 if (strtolower($row['name']) == $column_name)
982 {
983 $this->db->sql_freeresult($result);
984 return true;
985 }
986 }
987 $this->db->sql_freeresult($result);
988 return false;
989 break;
990
991 case 'oracle':
992 $sql = "SELECT column_name
993 FROM user_tab_columns
994 WHERE LOWER(table_name) = '" . strtolower($table) . "'";
995 $result = $this->db->sql_query($sql);
996 while ($row = $this->db->sql_fetchrow($result))
997 {
998 // lower case just in case
999 if (strtolower($row['column_name']) == $column_name)
1000 {
1001 $this->db->sql_freeresult($result);
1002 return true;
1003 }
1004 }
1005 $this->db->sql_freeresult($result);
1006 return false;
1007 break;
1008
1009 case 'firebird':
1010 $sql = "SELECT RDB\$FIELD_NAME as FNAME
1011 FROM RDB\$RELATION_FIELDS
1012 WHERE RDB\$RELATION_NAME = '" . strtoupper($table) . "'";
1013 $result = $this->db->sql_query($sql);
1014 while ($row = $this->db->sql_fetchrow($result))
1015 {
1016 // lower case just in case
1017 if (strtolower($row['fname']) == $column_name)
1018 {
1019 $this->db->sql_freeresult($result);
1020 return true;
1021 }
1022 }
1023 $this->db->sql_freeresult($result);
1024 return false;
1025 break;
1026
1027 // ugh, SQLite
1028 case 'sqlite':
1029 $sql = "SELECT sql
1030 FROM sqlite_master
1031 WHERE type = 'table'
1032 AND name = '{$table}'";
1033 $result = $this->db->sql_query($sql);
1034
1035 if (!$result)
1036 {
1037 return false;
1038 }
1039
1040 $row = $this->db->sql_fetchrow($result);
1041 $this->db->sql_freeresult($result);
1042
1043 preg_match('#\((.*)\)#s', $row['sql'], $matches);
1044
1045 $cols = trim($matches[1]);
1046 $col_array = preg_split('/,(?![\s\w]+\))/m', $cols);
1047
1048 foreach ($col_array as $declaration)
1049 {
1050 $entities = preg_split('#\s+#', trim($declaration));
1051 if ($entities[0] == 'PRIMARY')
1052 {
1053 continue;
1054 }
1055
1056 if (strtolower($entities[0]) == $column_name)
1057 {
1058 return true;
1059 }
1060 }
1061 return false;
1062 break;
1063 }
1064 }
1065
1066 /**
1067 * Private method for performing sql statements (either execute them or return them)
1068 * @access private
1069 */
1070 function _sql_run_sql($statements)
1071 {
1072 if ($this->return_statements)
1073 {
1074 return $statements;
1075 }
1076
1077 // We could add error handling here...
1078 foreach ($statements as $sql)
1079 {
1080 if ($sql === 'begin')
1081 {
1082 $this->db->sql_transaction('begin');
1083 }
1084 else if ($sql === 'commit')
1085 {
1086 $this->db->sql_transaction('commit');
1087 }
1088 else
1089 {
1090 $this->db->sql_query($sql);
1091 }
1092 }
1093
1094 return true;
1095 }
1096
1097 /**
1098 * Function to prepare some column information for better usage
1099 * @access private
1100 */
1101 function sql_prepare_column_data($table_name, $column_name, $column_data)
1102 {
1103 // Get type
1104 if (strpos($column_data[0], ':') !== false)
1105 {
1106 list($orig_column_type, $column_length) = explode(':', $column_data[0]);
1107 if (!is_array($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']))
1108 {
1109 $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'], $column_length);
1110 }
1111 else
1112 {
1113 if (isset($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule']))
1114 {
1115 switch ($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule'][0])
1116 {
1117 case 'div':
1118 $column_length /= $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule'][1];
1119 $column_length = ceil($column_length);
1120 $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'][0], $column_length);
1121 break;
1122 }
1123 }
1124
1125 if (isset($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit']))
1126 {
1127 switch ($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][0])
1128 {
1129 case 'mult':
1130 $column_length *= $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][1];
1131 if ($column_length > $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][2])
1132 {
1133 $column_type = $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][3];
1134 }
1135 else
1136 {
1137 $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'][0], $column_length);
1138 }
1139 break;
1140 }
1141 }
1142 }
1143 $orig_column_type .= ':';
1144 }
1145 else
1146 {
1147 $orig_column_type = $column_data[0];
1148 $column_type = $this->dbms_type_map[$this->sql_layer][$column_data[0]];
1149 }
1150
1151 // Adjust default value if db-dependant specified
1152 if (is_array($column_data[1]))
1153 {
1154 $column_data[1] = (isset($column_data[1][$this->sql_layer])) ? $column_data[1][$this->sql_layer] : $column_data[1]['default'];
1155 }
1156
1157 $sql = '';
1158
1159 $return_array = array();
1160
1161 switch ($this->sql_layer)
1162 {
1163 case 'firebird':
1164 $sql .= " {$column_type} ";
1165 $return_array['column_type_sql_type'] = " {$column_type} ";
1166
1167 if (!is_null($column_data[1]))
1168 {
1169 $sql .= 'DEFAULT ' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ' ';
1170 $return_array['column_type_sql_default'] = ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ' ';
1171 }
1172
1173 $sql .= 'NOT NULL';
1174
1175 // This is a UNICODE column and thus should be given it's fair share
1176 if (preg_match('/^X?STEXT_UNI|VCHAR_(CI|UNI:?)/', $column_data[0]))
1177 {
1178 $sql .= ' COLLATE UNICODE';
1179 }
1180
1181 $return_array['auto_increment'] = false;
1182 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
1183 {
1184 $return_array['auto_increment'] = true;
1185 }
1186
1187 break;
1188
1189 case 'mssql':
1190 $sql .= " {$column_type} ";
1191 $sql_default = " {$column_type} ";
1192
1193 // For adding columns we need the default definition
1194 if (!is_null($column_data[1]))
1195 {
1196 // For hexadecimal values do not use single quotes
1197 if (strpos($column_data[1], '0x') === 0)
1198 {
1199 $return_array['default'] = 'DEFAULT (' . $column_data[1] . ') ';
1200 $sql_default .= $return_array['default'];
1201 }
1202 else
1203 {
1204 $return_array['default'] = 'DEFAULT (' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ') ';
1205 $sql_default .= $return_array['default'];
1206 }
1207 }
1208
1209 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
1210 {
1211// $sql .= 'IDENTITY (1, 1) ';
1212 $sql_default .= 'IDENTITY (1, 1) ';
1213 }
1214
1215 $return_array['textimage'] = $column_type === '[text]';
1216
1217 $sql .= 'NOT NULL';
1218 $sql_default .= 'NOT NULL';
1219
1220 $return_array['column_type_sql_default'] = $sql_default;
1221
1222 break;
1223
1224 case 'mysql_40':
1225 case 'mysql_41':
1226 $sql .= " {$column_type} ";
1227
1228 // For hexadecimal values do not use single quotes
1229 if (!is_null($column_data[1]) && substr($column_type, -4) !== 'text' && substr($column_type, -4) !== 'blob')
1230 {
1231 $sql .= (strpos($column_data[1], '0x') === 0) ? "DEFAULT {$column_data[1]} " : "DEFAULT '{$column_data[1]}' ";
1232 }
1233 $sql .= 'NOT NULL';
1234
1235 if (isset($column_data[2]))
1236 {
1237 if ($column_data[2] == 'auto_increment')
1238 {
1239 $sql .= ' auto_increment';
1240 }
1241 else if ($this->sql_layer === 'mysql_41' && $column_data[2] == 'true_sort')
1242 {
1243 $sql .= ' COLLATE utf8_unicode_ci';
1244 }
1245 }
1246
1247 break;
1248
1249 case 'oracle':
1250 $sql .= " {$column_type} ";
1251 $sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : '';
1252
1253 // In Oracle empty strings ('') are treated as NULL.
1254 // Therefore in oracle we allow NULL's for all DEFAULT '' entries
1255 // Oracle does not like setting NOT NULL on a column that is already NOT NULL (this happens only on number fields)
1256 if (!preg_match('/number/i', $column_type))
1257 {
1258 $sql .= ($column_data[1] === '') ? '' : 'NOT NULL';
1259 }
1260
1261 $return_array['auto_increment'] = false;
1262 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
1263 {
1264 $return_array['auto_increment'] = true;
1265 }
1266
1267 break;
1268
1269 case 'postgres':
1270 $return_array['column_type'] = $column_type;
1271
1272 $sql .= " {$column_type} ";
1273
1274 $return_array['auto_increment'] = false;
1275 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
1276 {
1277 $default_val = "nextval('{$table_name}_seq')";
1278 $return_array['auto_increment'] = true;
1279 }
1280 else if (!is_null($column_data[1]))
1281 {
1282 $default_val = "'" . $column_data[1] . "'";
1283 $return_array['null'] = 'NOT NULL';
1284 $sql .= 'NOT NULL ';
1285 }
1286
1287 $return_array['default'] = $default_val;
1288
1289 $sql .= "DEFAULT {$default_val}";
1290
1291 // Unsigned? Then add a CHECK contraint
1292 if (in_array($orig_column_type, $this->unsigned_types))
1293 {
1294 $return_array['constraint'] = "CHECK ({$column_name} >= 0)";
1295 $sql .= " CHECK ({$column_name} >= 0)";
1296 }
1297
1298 break;
1299
1300 case 'sqlite':
1301 $return_array['primary_key_set'] = false;
1302 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
1303 {
1304 $sql .= ' INTEGER PRIMARY KEY';
1305 $return_array['primary_key_set'] = true;
1306 }
1307 else
1308 {
1309 $sql .= ' ' . $column_type;
1310 }
1311
1312 $sql .= ' NOT NULL ';
1313 $sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}'" : '';
1314
1315 break;
1316 }
1317
1318 $return_array['column_type_sql'] = $sql;
1319
1320 return $return_array;
1321 }
1322
1323 /**
1324 * Add new column
1325 */
1326 function sql_column_add($table_name, $column_name, $column_data, $inline = false)
1327 {
1328 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
1329 $statements = array();
1330
1331 switch ($this->sql_layer)
1332 {
1333 case 'firebird':
1334 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD "' . strtoupper($column_name) . '" ' . $column_data['column_type_sql'];
1335 break;
1336
1337 case 'mssql':
1338 $statements[] = 'ALTER TABLE [' . $table_name . '] ADD [' . $column_name . '] ' . $column_data['column_type_sql_default'];
1339 break;
1340
1341 case 'mysql_40':
1342 case 'mysql_41':
1343 $statements[] = 'ALTER TABLE `' . $table_name . '` ADD COLUMN `' . $column_name . '` ' . $column_data['column_type_sql'];
1344 break;
1345
1346 case 'oracle':
1347 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' ' . $column_data['column_type_sql'];
1348 break;
1349
1350 case 'postgres':
1351 if (version_compare($this->db->sql_server_info(true), '8.0', '>='))
1352 {
1353 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD COLUMN "' . $column_name . '" ' . $column_data['column_type_sql'];
1354 }
1355 else
1356 {
1357 // old versions cannot add columns with default and null information
1358 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD COLUMN "' . $column_name . '" ' . $column_data['column_type'] . ' ' . $column_data['constraint'];
1359
1360 if (isset($column_data['null']))
1361 {
1362 if ($column_data['null'] == 'NOT NULL')
1363 {
1364 $statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN ' . $column_name . ' SET NOT NULL';
1365 }
1366 }
1367
1368 if (isset($column_data['default']))
1369 {
1370 $statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN ' . $column_name . ' SET DEFAULT ' . $column_data['default'];
1371 }
1372 }
1373
1374 break;
1375
1376 case 'sqlite':
1377
1378 if ($inline && $this->return_statements)
1379 {
1380 return $column_name . ' ' . $column_data['column_type_sql'];
1381 }
1382
1383 if (version_compare(sqlite_libversion(), '3.0') == -1)
1384 {
1385 $sql = "SELECT sql
1386 FROM sqlite_master
1387 WHERE type = 'table'
1388 AND name = '{$table_name}'
1389 ORDER BY type DESC, name;";
1390 $result = $this->db->sql_query($sql);
1391
1392 if (!$result)
1393 {
1394 break;
1395 }
1396
1397 $row = $this->db->sql_fetchrow($result);
1398 $this->db->sql_freeresult($result);
1399
1400 $statements[] = 'begin';
1401
1402 // Create a backup table and populate it, destroy the existing one
1403 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
1404 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
1405 $statements[] = 'DROP TABLE ' . $table_name;
1406
1407 preg_match('#\((.*)\)#s', $row['sql'], $matches);
1408
1409 $new_table_cols = trim($matches[1]);
1410 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
1411 $column_list = array();
1412
1413 foreach ($old_table_cols as $declaration)
1414 {
1415 $entities = preg_split('#\s+#', trim($declaration));
1416 if ($entities[0] == 'PRIMARY')
1417 {
1418 continue;
1419 }
1420 $column_list[] = $entities[0];
1421 }
1422
1423 $columns = implode(',', $column_list);
1424
1425 $new_table_cols = $column_name . ' ' . $column_data['column_type_sql'] . ',' . $new_table_cols;
1426
1427 // create a new table and fill it up. destroy the temp one
1428 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');';
1429 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
1430 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
1431
1432 $statements[] = 'commit';
1433 }
1434 else
1435 {
1436 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' [' . $column_data['column_type_sql'] . ']';
1437 }
1438 break;
1439 }
1440
1441 return $this->_sql_run_sql($statements);
1442 }
1443
1444 /**
1445 * Drop column
1446 */
1447 function sql_column_remove($table_name, $column_name, $inline = false)
1448 {
1449 $statements = array();
1450
1451 switch ($this->sql_layer)
1452 {
1453 case 'firebird':
1454 $statements[] = 'ALTER TABLE ' . $table_name . ' DROP "' . strtoupper($column_name) . '"';
1455 break;
1456
1457 case 'mssql':
1458 $statements[] = 'ALTER TABLE [' . $table_name . '] DROP COLUMN [' . $column_name . ']';
1459 break;
1460
1461 case 'mysql_40':
1462 case 'mysql_41':
1463 $statements[] = 'ALTER TABLE `' . $table_name . '` DROP COLUMN `' . $column_name . '`';
1464 break;
1465
1466 case 'oracle':
1467 $statements[] = 'ALTER TABLE ' . $table_name . ' DROP ' . $column_name;
1468 break;
1469
1470 case 'postgres':
1471 $statements[] = 'ALTER TABLE ' . $table_name . ' DROP COLUMN "' . $column_name . '"';
1472 break;
1473
1474 case 'sqlite':
1475
1476 if ($inline && $this->return_statements)
1477 {
1478 return $column_name;
1479 }
1480
1481 if (version_compare(sqlite_libversion(), '3.0') == -1)
1482 {
1483 $sql = "SELECT sql
1484 FROM sqlite_master
1485 WHERE type = 'table'
1486 AND name = '{$table_name}'
1487 ORDER BY type DESC, name;";
1488 $result = $this->db->sql_query($sql);
1489
1490 if (!$result)
1491 {
1492 break;
1493 }
1494
1495 $row = $this->db->sql_fetchrow($result);
1496 $this->db->sql_freeresult($result);
1497
1498 $statements[] = 'begin';
1499
1500 // Create a backup table and populate it, destroy the existing one
1501 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
1502 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
1503 $statements[] = 'DROP TABLE ' . $table_name;
1504
1505 preg_match('#\((.*)\)#s', $row['sql'], $matches);
1506
1507 $new_table_cols = trim($matches[1]);
1508 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
1509 $column_list = array();
1510
1511 foreach ($old_table_cols as $declaration)
1512 {
1513 $entities = preg_split('#\s+#', trim($declaration));
1514 if ($entities[0] == 'PRIMARY' || $entities[0] === $column_name)
1515 {
1516 continue;
1517 }
1518 $column_list[] = $entities[0];
1519 }
1520
1521 $columns = implode(',', $column_list);
1522
1523 $new_table_cols = preg_replace('/' . $column_name . '[^,]+(?:,|$)/m', '', $new_table_cols);
1524
1525 // create a new table and fill it up. destroy the temp one
1526 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');';
1527 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
1528 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
1529
1530 $statements[] = 'commit';
1531 }
1532 else
1533 {
1534 $statements[] = 'ALTER TABLE ' . $table_name . ' DROP COLUMN ' . $column_name;
1535 }
1536 break;
1537 }
1538
1539 return $this->_sql_run_sql($statements);
1540 }
1541
1542 /**
1543 * Drop Index
1544 */
1545 function sql_index_drop($table_name, $index_name)
1546 {
1547 $statements = array();
1548
1549 switch ($this->sql_layer)
1550 {
1551 case 'mssql':
1552 $statements[] = 'DROP INDEX ' . $table_name . '.' . $index_name;
1553 break;
1554
1555 case 'mysql_40':
1556 case 'mysql_41':
1557 $statements[] = 'DROP INDEX ' . $index_name . ' ON ' . $table_name;
1558 break;
1559
1560 case 'firebird':
1561 case 'oracle':
1562 case 'postgres':
1563 case 'sqlite':
1564 $statements[] = 'DROP INDEX ' . $table_name . '_' . $index_name;
1565 break;
1566 }
1567
1568 return $this->_sql_run_sql($statements);
1569 }
1570
1571 /**
1572 * Drop Table
1573 */
1574 function sql_table_drop($table_name)
1575 {
1576 $statements = array();
1577
1578 if (!$this->sql_table_exists($table_name))
1579 {
1580 return $this->_sql_run_sql($statements);
1581 }
1582
1583 // the most basic operation, get rid of the table
1584 $statements[] = 'DROP TABLE ' . $table_name;
1585
1586 switch ($this->sql_layer)
1587 {
1588 case 'firebird':
1589 $sql = 'SELECT RDB$GENERATOR_NAME as gen
1590 FROM RDB$GENERATORS
1591 WHERE RDB$SYSTEM_FLAG = 0
1592 AND RDB$GENERATOR_NAME = \'' . strtoupper($table_name) . "_GEN'";
1593 $result = $this->db->sql_query($sql);
1594
1595 // does a generator exist?
1596 if ($row = $this->db->sql_fetchrow($result))
1597 {
1598 $statements[] = "DROP GENERATOR {$row['gen']};";
1599 }
1600 $this->db->sql_freeresult($result);
1601 break;
1602
1603 case 'oracle':
1604 $sql = 'SELECT A.REFERENCED_NAME
1605 FROM USER_DEPENDENCIES A, USER_TRIGGERS B
1606 WHERE A.REFERENCED_TYPE = \'SEQUENCE\'
1607 AND A.NAME = B.TRIGGER_NAME
1608 AND B.TABLE_NAME = \'' . strtoupper($table_name) . "'";
1609 $result = $this->db->sql_query($sql);
1610
1611 // any sequences ref'd to this table's triggers?
1612 while ($row = $this->db->sql_fetchrow($result))
1613 {
1614 $statements[] = "DROP SEQUENCE {$row['referenced_name']}";
1615 }
1616 $this->db->sql_freeresult($result);
1617
1618 case 'postgres':
1619 // PGSQL does not "tightly" bind sequences and tables, we must guess...
1620 $sql = "SELECT relname
1621 FROM pg_class
1622 WHERE relkind = 'S'
1623 AND relname = '{$table_name}_seq'";
1624 $result = $this->db->sql_query($sql);
1625
1626 // We don't even care about storing the results. We already know the answer if we get rows back.
1627 if ($this->db->sql_fetchrow($result))
1628 {
1629 $statements[] = "DROP SEQUENCE {$table_name}_seq;\n";
1630 }
1631 $this->db->sql_freeresult($result);
1632 break;
1633 }
1634
1635 return $this->_sql_run_sql($statements);
1636 }
1637
1638 /**
1639 * Add primary key
1640 */
1641 function sql_create_primary_key($table_name, $column, $inline = false)
1642 {
1643 $statements = array();
1644
1645 switch ($this->sql_layer)
1646 {
1647 case 'firebird':
1648 case 'postgres':
1649 case 'mysql_40':
1650 case 'mysql_41':
1651 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD PRIMARY KEY (' . implode(', ', $column) . ')';
1652 break;
1653
1654 case 'mssql':
1655 $sql = "ALTER TABLE [{$table_name}] WITH NOCHECK ADD ";
1656 $sql .= "CONSTRAINT [PK_{$table_name}] PRIMARY KEY CLUSTERED (";
1657 $sql .= '[' . implode("],\n\t\t[", $column) . ']';
1658 $sql .= ') ON [PRIMARY]';
1659
1660 $statements[] = $sql;
1661 break;
1662
1663 case 'oracle':
1664 $statements[] = 'ALTER TABLE ' . $table_name . 'add CONSTRAINT pk_' . $table_name . ' PRIMARY KEY (' . implode(', ', $column) . ')';
1665 break;
1666
1667 case 'sqlite':
1668
1669 if ($inline && $this->return_statements)
1670 {
1671 return $column;
1672 }
1673
1674 $sql = "SELECT sql
1675 FROM sqlite_master
1676 WHERE type = 'table'
1677 AND name = '{$table_name}'
1678 ORDER BY type DESC, name;";
1679 $result = $this->db->sql_query($sql);
1680
1681 if (!$result)
1682 {
1683 break;
1684 }
1685
1686 $row = $this->db->sql_fetchrow($result);
1687 $this->db->sql_freeresult($result);
1688
1689 $statements[] = 'begin';
1690
1691 // Create a backup table and populate it, destroy the existing one
1692 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
1693 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
1694 $statements[] = 'DROP TABLE ' . $table_name;
1695
1696 preg_match('#\((.*)\)#s', $row['sql'], $matches);
1697
1698 $new_table_cols = trim($matches[1]);
1699 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
1700 $column_list = array();
1701
1702 foreach ($old_table_cols as $declaration)
1703 {
1704 $entities = preg_split('#\s+#', trim($declaration));
1705 if ($entities[0] == 'PRIMARY')
1706 {
1707 continue;
1708 }
1709 $column_list[] = $entities[0];
1710 }
1711
1712 $columns = implode(',', $column_list);
1713
1714 // create a new table and fill it up. destroy the temp one
1715 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ', PRIMARY KEY (' . implode(', ', $column) . '));';
1716 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
1717 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
1718
1719 $statements[] = 'commit';
1720 break;
1721 }
1722
1723 return $this->_sql_run_sql($statements);
1724 }
1725
1726 /**
1727 * Add unique index
1728 */
1729 function sql_create_unique_index($table_name, $index_name, $column)
1730 {
1731 $statements = array();
1732
1733 switch ($this->sql_layer)
1734 {
1735 case 'firebird':
1736 case 'postgres':
1737 case 'oracle':
1738 case 'sqlite':
1739 $statements[] = 'CREATE UNIQUE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
1740 break;
1741
1742 case 'mysql_40':
1743 case 'mysql_41':
1744 $statements[] = 'CREATE UNIQUE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
1745 break;
1746
1747 case 'mssql':
1748 $statements[] = 'CREATE UNIQUE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ') ON [PRIMARY]';
1749 break;
1750 }
1751
1752 return $this->_sql_run_sql($statements);
1753 }
1754
1755 /**
1756 * Add index
1757 */
1758 function sql_create_index($table_name, $index_name, $column)
1759 {
1760 $statements = array();
1761
1762 switch ($this->sql_layer)
1763 {
1764 case 'firebird':
1765 case 'postgres':
1766 case 'oracle':
1767 case 'sqlite':
1768 $statements[] = 'CREATE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
1769 break;
1770
1771 case 'mysql_40':
1772 case 'mysql_41':
1773 $statements[] = 'CREATE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
1774 break;
1775
1776 case 'mssql':
1777 $statements[] = 'CREATE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ') ON [PRIMARY]';
1778 break;
1779 }
1780
1781 return $this->_sql_run_sql($statements);
1782 }
1783
1784 /**
1785 * List all of the indices that belong to a table,
1786 * does not count:
1787 * * UNIQUE indices
1788 * * PRIMARY keys
1789 */
1790 function sql_list_index($table_name)
1791 {
1792 $index_array = array();
1793
1794 if ($this->sql_layer == 'mssql')
1795 {
1796 $sql = "EXEC sp_statistics '$table_name'";
1797 $result = $this->db->sql_query($sql);
1798 while ($row = $this->db->sql_fetchrow($result))
1799 {
1800 if ($row['TYPE'] == 3)
1801 {
1802 $index_array[] = $row['INDEX_NAME'];
1803 }
1804 }
1805 $this->db->sql_freeresult($result);
1806 }
1807 else
1808 {
1809 switch ($this->sql_layer)
1810 {
1811 case 'firebird':
1812 $sql = "SELECT LOWER(RDB\$INDEX_NAME) as index_name
1813 FROM RDB\$INDICES
1814 WHERE RDB\$RELATION_NAME = '" . strtoupper($table_name) . "'
1815 AND RDB\$UNIQUE_FLAG IS NULL
1816 AND RDB\$FOREIGN_KEY IS NULL";
1817 $col = 'index_name';
1818 break;
1819
1820 case 'postgres':
1821 $sql = "SELECT ic.relname as index_name
1822 FROM pg_class bc, pg_class ic, pg_index i
1823 WHERE (bc.oid = i.indrelid)
1824 AND (ic.oid = i.indexrelid)
1825 AND (bc.relname = '" . $table_name . "')
1826 AND (i.indisunique != 't')
1827 AND (i.indisprimary != 't')";
1828 $col = 'index_name';
1829 break;
1830
1831 case 'mysql_40':
1832 case 'mysql_41':
1833 $sql = 'SHOW KEYS
1834 FROM ' . $table_name;
1835 $col = 'Key_name';
1836 break;
1837
1838 case 'oracle':
1839 $sql = "SELECT index_name
1840 FROM user_indexes
1841 WHERE table_name = '" . strtoupper($table_name) . "'
1842 AND generated = 'N'
1843 AND uniqueness = 'NONUNIQUE'";
1844 $col = 'index_name';
1845 break;
1846
1847 case 'sqlite':
1848 $sql = "PRAGMA index_info('" . $table_name . "');";
1849 $col = 'name';
1850 break;
1851 }
1852
1853 $result = $this->db->sql_query($sql);
1854 while ($row = $this->db->sql_fetchrow($result))
1855 {
1856 if (($this->sql_layer == 'mysql_40' || $this->sql_layer == 'mysql_41') && !$row['Non_unique'])
1857 {
1858 continue;
1859 }
1860
1861 switch ($this->sql_layer)
1862 {
1863 case 'firebird':
1864 case 'oracle':
1865 case 'postgres':
1866 case 'sqlite':
1867 $row[$col] = substr($row[$col], strlen($table_name) + 1);
1868 break;
1869 }
1870
1871 $index_array[] = $row[$col];
1872 }
1873 $this->db->sql_freeresult($result);
1874 }
1875
1876 return array_map('strtolower', $index_array);
1877 }
1878
1879 /**
1880 * Change column type (not name!)
1881 */
1882 function sql_column_change($table_name, $column_name, $column_data, $inline = false)
1883 {
1884 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
1885 $statements = array();
1886
1887 switch ($this->sql_layer)
1888 {
1889 case 'firebird':
1890 // Change type...
1891 if (!empty($column_data['column_type_sql_default']))
1892 {
1893 $statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN "' . strtoupper($column_name) . '" TYPE ' . ' ' . $column_data['column_type_sql_type'];
1894 $statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN "' . strtoupper($column_name) . '" SET DEFAULT ' . ' ' . $column_data['column_type_sql_default'];
1895 }
1896 else
1897 {
1898 $statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN "' . strtoupper($column_name) . '" TYPE ' . ' ' . $column_data['column_type_sql_type'];
1899 }
1900 break;
1901
1902 case 'mssql':
1903 $statements[] = 'ALTER TABLE [' . $table_name . '] ALTER COLUMN [' . $column_name . '] ' . $column_data['column_type_sql'];
1904
1905 if (!empty($column_data['default']))
1906 {
1907 // Using TRANSACT-SQL for this statement because we do not want to have colliding data if statements are executed at a later stage
1908 $statements[] = "DECLARE @drop_default_name VARCHAR(100), @cmd VARCHAR(1000)
1909 SET @drop_default_name =
1910 (SELECT so.name FROM sysobjects so
1911 JOIN sysconstraints sc ON so.id = sc.constid
1912 WHERE object_name(so.parent_obj) = '{$table_name}'
1913 AND so.xtype = 'D'
1914 AND sc.colid = (SELECT colid FROM syscolumns
1915 WHERE id = object_id('{$table_name}')
1916 AND name = '{$column_name}'))
1917 IF @drop_default_name <> ''
1918 BEGIN
1919 SET @cmd = 'ALTER TABLE [{$table_name}] DROP CONSTRAINT [' + @drop_default_name + ']'
1920 EXEC(@cmd)
1921 END
1922 SET @cmd = 'ALTER TABLE [{$table_name}] ADD CONSTRAINT [DF_{$table_name}_{$column_name}_1] {$column_data['default']} FOR [{$column_name}]'
1923 EXEC(@cmd)";
1924 }
1925 break;
1926
1927 case 'mysql_40':
1928 case 'mysql_41':
1929 $statements[] = 'ALTER TABLE `' . $table_name . '` CHANGE `' . $column_name . '` `' . $column_name . '` ' . $column_data['column_type_sql'];
1930 break;
1931
1932 case 'oracle':
1933 $statements[] = 'ALTER TABLE ' . $table_name . ' MODIFY ' . $column_name . ' ' . $column_data['column_type_sql'];
1934 break;
1935
1936 case 'postgres':
1937 $sql = 'ALTER TABLE ' . $table_name . ' ';
1938
1939 $sql_array = array();
1940 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' TYPE ' . $column_data['column_type'];
1941
1942 if (isset($column_data['null']))
1943 {
1944 if ($column_data['null'] == 'NOT NULL')
1945 {
1946 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' SET NOT NULL';
1947 }
1948 else if ($column_data['null'] == 'NULL')
1949 {
1950 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' DROP NOT NULL';
1951 }
1952 }
1953
1954 if (isset($column_data['default']))
1955 {
1956 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' SET DEFAULT ' . $column_data['default'];
1957 }
1958
1959 // we don't want to double up on constraints if we change different number data types
1960 if (isset($column_data['constraint']))
1961 {
1962 $constraint_sql = "SELECT consrc as constraint_data
1963 FROM pg_constraint, pg_class bc
1964 WHERE conrelid = bc.oid
1965 AND bc.relname = '{$table_name}'
1966 AND NOT EXISTS (
1967 SELECT *
1968 FROM pg_constraint as c, pg_inherits as i
1969 WHERE i.inhrelid = pg_constraint.conrelid
1970 AND c.conname = pg_constraint.conname
1971 AND c.consrc = pg_constraint.consrc
1972 AND c.conrelid = i.inhparent
1973 )";
1974
1975 $constraint_exists = false;
1976
1977 $result = $this->db->sql_query($constraint_sql);
1978 while ($row = $this->db->sql_fetchrow($result))
1979 {
1980 if (trim($row['constraint_data']) == trim($column_data['constraint']))
1981 {
1982 $constraint_exists = true;
1983 break;
1984 }
1985 }
1986 $this->db->sql_freeresult($result);
1987
1988 if (!$constraint_exists)
1989 {
1990 $sql_array[] = 'ADD ' . $column_data['constraint'];
1991 }
1992 }
1993
1994 $sql .= implode(', ', $sql_array);
1995
1996 $statements[] = $sql;
1997 break;
1998
1999 case 'sqlite':
2000
2001 if ($inline && $this->return_statements)
2002 {
2003 return $column_name . ' ' . $column_data['column_type_sql'];
2004 }
2005
2006 $sql = "SELECT sql
2007 FROM sqlite_master
2008 WHERE type = 'table'
2009 AND name = '{$table_name}'
2010 ORDER BY type DESC, name;";
2011 $result = $this->db->sql_query($sql);
2012
2013 if (!$result)
2014 {
2015 break;
2016 }
2017
2018 $row = $this->db->sql_fetchrow($result);
2019 $this->db->sql_freeresult($result);
2020
2021 $statements[] = 'begin';
2022
2023 // Create a temp table and populate it, destroy the existing one
2024 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
2025 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
2026 $statements[] = 'DROP TABLE ' . $table_name;
2027
2028 preg_match('#\((.*)\)#s', $row['sql'], $matches);
2029
2030 $new_table_cols = trim($matches[1]);
2031 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
2032 $column_list = array();
2033
2034 foreach ($old_table_cols as $key => $declaration)
2035 {
2036 $entities = preg_split('#\s+#', trim($declaration));
2037 $column_list[] = $entities[0];
2038 if ($entities[0] == $column_name)
2039 {
2040 $old_table_cols[$key] = $column_name . ' ' . $column_data['column_type_sql'];
2041 }
2042 }
2043
2044 $columns = implode(',', $column_list);
2045
2046 // create a new table and fill it up. destroy the temp one
2047 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . implode(',', $old_table_cols) . ');';
2048 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
2049 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
2050
2051 $statements[] = 'commit';
2052
2053 break;
2054 }
2055
2056 return $this->_sql_run_sql($statements);
2057 }
2058}
2059
2060?>
Note: See TracBrowser for help on using the repository browser.