← Back to branch summary

~azzar1/unity/add-show-desktop-key

« back to all changes in this revision

Viewing changes to www/php/phpBB3/includes/db/db_tools.php

  • Committer: dcoles
  • Date: 2008-02-13 04:10:55 UTC
  • Revision ID: svn-v3-trunk0:2b9c9e99-6f39-0410-b283-7f802c844ae2:trunk:443
Added Forum application along with unmodifed version of phpBB3 "Olympus" 3.0.0

Show diffs side-by-side

added added

removed removed

Lines of Context:
www/php/phpBB3/includes/db/db_tools.php
 
1
<?php
 
2
/**
 
3
*
 
4
* @package dbal
 
5
* @version $Id: db_tools.php,v 1.5 2007/11/19 13:33:13 acydburn Exp $
 
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
        /**
 
29
        * Current sql layer
 
30
        */
 
31
        var $sql_layer = '';
 
32
 
 
33
        var $dbms_type_map = array(
 
34
                'mysql_41'      => array(
 
35
                        'INT:'          => 'int(%d)',
 
36
                        'BINT'          => 'bigint(20)',
 
37
                        'UINT'          => 'mediumint(8) UNSIGNED',
 
38
                        'UINT:'         => 'int(%d) UNSIGNED',
 
39
                        'TINT:'         => 'tinyint(%d)',
 
40
                        'USINT'         => 'smallint(4) UNSIGNED',
 
41
                        'BOOL'          => 'tinyint(1) UNSIGNED',
 
42
                        'VCHAR'         => 'varchar(255)',
 
43
                        'VCHAR:'        => 'varchar(%d)',
 
44
                        'CHAR:'         => 'char(%d)',
 
45
                        'XSTEXT'        => 'text',
 
46
                        'XSTEXT_UNI'=> 'varchar(100)',
 
47
                        'STEXT'         => 'text',
 
48
                        'STEXT_UNI'     => 'varchar(255)',
 
49
                        'TEXT'          => 'text',
 
50
                        'TEXT_UNI'      => 'text',
 
51
                        'MTEXT'         => 'mediumtext',
 
52
                        'MTEXT_UNI'     => 'mediumtext',
 
53
                        'TIMESTAMP'     => 'int(11) UNSIGNED',
 
54
                        'DECIMAL'       => 'decimal(5,2)',
 
55
                        'DECIMAL:'      => 'decimal(%d,2)',
 
56
                        'PDECIMAL'      => 'decimal(6,3)',
 
57
                        'PDECIMAL:'     => 'decimal(%d,3)',
 
58
                        'VCHAR_UNI'     => 'varchar(255)',
 
59
                        'VCHAR_UNI:'=> 'varchar(%d)',
 
60
                        'VCHAR_CI'      => 'varchar(255)',
 
61
                        'VARBINARY'     => 'varbinary(255)',
 
62
                ),
 
63
 
 
64
                'mysql_40'      => array(
 
65
                        'INT:'          => 'int(%d)',
 
66
                        'BINT'          => 'bigint(20)',
 
67
                        'UINT'          => 'mediumint(8) UNSIGNED',
 
68
                        'UINT:'         => 'int(%d) UNSIGNED',
 
69
                        'TINT:'         => 'tinyint(%d)',
 
70
                        'USINT'         => 'smallint(4) UNSIGNED',
 
71
                        'BOOL'          => 'tinyint(1) UNSIGNED',
 
72
                        'VCHAR'         => 'varbinary(255)',
 
73
                        'VCHAR:'        => 'varbinary(%d)',
 
74
                        'CHAR:'         => 'binary(%d)',
 
75
                        'XSTEXT'        => 'blob',
 
76
                        'XSTEXT_UNI'=> 'blob',
 
77
                        'STEXT'         => 'blob',
 
78
                        'STEXT_UNI'     => 'blob',
 
79
                        'TEXT'          => 'blob',
 
80
                        'TEXT_UNI'      => 'blob',
 
81
                        'MTEXT'         => 'mediumblob',
 
82
                        'MTEXT_UNI'     => 'mediumblob',
 
83
                        'TIMESTAMP'     => 'int(11) UNSIGNED',
 
84
                        'DECIMAL'       => 'decimal(5,2)',
 
85
                        'DECIMAL:'      => 'decimal(%d,2)',
 
86
                        'PDECIMAL'      => 'decimal(6,3)',
 
87
                        'PDECIMAL:'     => 'decimal(%d,3)',
 
88
                        'VCHAR_UNI'     => 'blob',
 
89
                        'VCHAR_UNI:'=> array('varbinary(%d)', 'limit' => array('mult', 3, 255, 'blob')),
 
90
                        'VCHAR_CI'      => 'blob',
 
91
                        'VARBINARY'     => 'varbinary(255)',
 
92
                ),
 
93
 
 
94
                'firebird'      => array(
 
95
                        'INT:'          => 'INTEGER',
 
96
                        'BINT'          => 'DOUBLE PRECISION',
 
97
                        'UINT'          => 'INTEGER',
 
98
                        'UINT:'         => 'INTEGER',
 
99
                        'TINT:'         => 'INTEGER',
 
100
                        'USINT'         => 'INTEGER',
 
101
                        'BOOL'          => 'INTEGER',
 
102
                        'VCHAR'         => 'VARCHAR(255) CHARACTER SET NONE',
 
103
                        'VCHAR:'        => 'VARCHAR(%d) CHARACTER SET NONE',
 
104
                        'CHAR:'         => 'CHAR(%d) CHARACTER SET NONE',
 
105
                        'XSTEXT'        => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
 
106
                        'STEXT'         => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
 
107
                        'TEXT'          => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
 
108
                        'MTEXT'         => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
 
109
                        'XSTEXT_UNI'=> 'VARCHAR(100) CHARACTER SET UTF8',
 
110
                        'STEXT_UNI'     => 'VARCHAR(255) CHARACTER SET UTF8',
 
111
                        'TEXT_UNI'      => 'BLOB SUB_TYPE TEXT CHARACTER SET UTF8',
 
112
                        'MTEXT_UNI'     => 'BLOB SUB_TYPE TEXT CHARACTER SET UTF8',
 
113
                        'TIMESTAMP'     => 'INTEGER',
 
114
                        'DECIMAL'       => 'DOUBLE PRECISION',
 
115
                        'DECIMAL:'      => 'DOUBLE PRECISION',
 
116
                        'PDECIMAL'      => 'DOUBLE PRECISION',
 
117
                        'PDECIMAL:'     => 'DOUBLE PRECISION',
 
118
                        'VCHAR_UNI'     => 'VARCHAR(255) CHARACTER SET UTF8',
 
119
                        'VCHAR_UNI:'=> 'VARCHAR(%d) CHARACTER SET UTF8',
 
120
                        'VCHAR_CI'      => 'VARCHAR(255) CHARACTER SET UTF8',
 
121
                        'VARBINARY'     => 'CHAR(255) CHARACTER SET NONE',
 
122
                ),
 
123
 
 
124
                'mssql'         => array(
 
125
                        'INT:'          => '[int]',
 
126
                        'BINT'          => '[float]',
 
127
                        'UINT'          => '[int]',
 
128
                        'UINT:'         => '[int]',
 
129
                        'TINT:'         => '[int]',
 
130
                        'USINT'         => '[int]',
 
131
                        'BOOL'          => '[int]',
 
132
                        'VCHAR'         => '[varchar] (255)',
 
133
                        'VCHAR:'        => '[varchar] (%d)',
 
134
                        'CHAR:'         => '[char] (%d)',
 
135
                        'XSTEXT'        => '[varchar] (1000)',
 
136
                        'STEXT'         => '[varchar] (3000)',
 
137
                        'TEXT'          => '[varchar] (8000)',
 
138
                        'MTEXT'         => '[text]',
 
139
                        'XSTEXT_UNI'=> '[varchar] (100)',
 
140
                        'STEXT_UNI'     => '[varchar] (255)',
 
141
                        'TEXT_UNI'      => '[varchar] (4000)',
 
142
                        'MTEXT_UNI'     => '[text]',
 
143
                        'TIMESTAMP'     => '[int]',
 
144
                        'DECIMAL'       => '[float]',
 
145
                        'DECIMAL:'      => '[float]',
 
146
                        'PDECIMAL'      => '[float]',
 
147
                        'PDECIMAL:'     => '[float]',
 
148
                        'VCHAR_UNI'     => '[varchar] (255)',
 
149
                        'VCHAR_UNI:'=> '[varchar] (%d)',
 
150
                        'VCHAR_CI'      => '[varchar] (255)',
 
151
                        'VARBINARY'     => '[varchar] (255)',
 
152
                ),
 
153
 
 
154
                'oracle'        => array(
 
155
                        'INT:'          => 'number(%d)',
 
156
                        'BINT'          => 'number(20)',
 
157
                        'UINT'          => 'number(8)',
 
158
                        'UINT:'         => 'number(%d)',
 
159
                        'TINT:'         => 'number(%d)',
 
160
                        'USINT'         => 'number(4)',
 
161
                        'BOOL'          => 'number(1)',
 
162
                        'VCHAR'         => 'varchar2(255)',
 
163
                        'VCHAR:'        => 'varchar2(%d)',
 
164
                        'CHAR:'         => 'char(%d)',
 
165
                        'XSTEXT'        => 'varchar2(1000)',
 
166
                        'STEXT'         => 'varchar2(3000)',
 
167
                        'TEXT'          => 'clob',
 
168
                        'MTEXT'         => 'clob',
 
169
                        'XSTEXT_UNI'=> 'varchar2(300)',
 
170
                        'STEXT_UNI'     => 'varchar2(765)',
 
171
                        'TEXT_UNI'      => 'clob',
 
172
                        'MTEXT_UNI'     => 'clob',
 
173
                        'TIMESTAMP'     => 'number(11)',
 
174
                        'DECIMAL'       => 'number(5, 2)',
 
175
                        'DECIMAL:'      => 'number(%d, 2)',
 
176
                        'PDECIMAL'      => 'number(6, 3)',
 
177
                        'PDECIMAL:'     => 'number(%d, 3)',
 
178
                        'VCHAR_UNI'     => 'varchar2(765)',
 
179
                        'VCHAR_UNI:'=> array('varchar2(%d)', 'limit' => array('mult', 3, 765, 'clob')),
 
180
                        'VCHAR_CI'      => 'varchar2(255)',
 
181
                        'VARBINARY'     => 'raw(255)',
 
182
                ),
 
183
 
 
184
                'sqlite'        => array(
 
185
                        'INT:'          => 'int(%d)',
 
186
                        'BINT'          => 'bigint(20)',
 
187
                        'UINT'          => 'INTEGER UNSIGNED', //'mediumint(8) UNSIGNED',
 
188
                        'UINT:'         => 'INTEGER UNSIGNED', // 'int(%d) UNSIGNED',
 
189
                        'TINT:'         => 'tinyint(%d)',
 
190
                        'USINT'         => 'INTEGER UNSIGNED', //'mediumint(4) UNSIGNED',
 
191
                        'BOOL'          => 'INTEGER UNSIGNED', //'tinyint(1) UNSIGNED',
 
192
                        'VCHAR'         => 'varchar(255)',
 
193
                        'VCHAR:'        => 'varchar(%d)',
 
194
                        'CHAR:'         => 'char(%d)',
 
195
                        'XSTEXT'        => 'text(65535)',
 
196
                        'STEXT'         => 'text(65535)',
 
197
                        'TEXT'          => 'text(65535)',
 
198
                        'MTEXT'         => 'mediumtext(16777215)',
 
199
                        'XSTEXT_UNI'=> 'text(65535)',
 
200
                        'STEXT_UNI'     => 'text(65535)',
 
201
                        'TEXT_UNI'      => 'text(65535)',
 
202
                        'MTEXT_UNI'     => 'mediumtext(16777215)',
 
203
                        'TIMESTAMP'     => 'INTEGER UNSIGNED', //'int(11) UNSIGNED',
 
204
                        'DECIMAL'       => 'decimal(5,2)',
 
205
                        'DECIMAL:'      => 'decimal(%d,2)',
 
206
                        'PDECIMAL'      => 'decimal(6,3)',
 
207
                        'PDECIMAL:'     => 'decimal(%d,3)',
 
208
                        'VCHAR_UNI'     => 'varchar(255)',
 
209
                        'VCHAR_UNI:'=> 'varchar(%d)',
 
210
                        'VCHAR_CI'      => 'varchar(255)',
 
211
                        'VARBINARY'     => 'blob',
 
212
                ),
 
213
 
 
214
                'postgres'      => array(
 
215
                        'INT:'          => 'INT4',
 
216
                        'BINT'          => 'INT8',
 
217
                        'UINT'          => 'INT4', // unsigned
 
218
                        'UINT:'         => 'INT4', // unsigned
 
219
                        'USINT'         => 'INT2', // unsigned
 
220
                        'BOOL'          => 'INT2', // unsigned
 
221
                        'TINT:'         => 'INT2',
 
222
                        'VCHAR'         => 'varchar(255)',
 
223
                        'VCHAR:'        => 'varchar(%d)',
 
224
                        'CHAR:'         => 'char(%d)',
 
225
                        'XSTEXT'        => 'varchar(1000)',
 
226
                        'STEXT'         => 'varchar(3000)',
 
227
                        'TEXT'          => 'varchar(8000)',
 
228
                        'MTEXT'         => 'TEXT',
 
229
                        'XSTEXT_UNI'=> 'varchar(100)',
 
230
                        'STEXT_UNI'     => 'varchar(255)',
 
231
                        'TEXT_UNI'      => 'varchar(4000)',
 
232
                        'MTEXT_UNI'     => 'TEXT',
 
233
                        'TIMESTAMP'     => 'INT4', // unsigned
 
234
                        'DECIMAL'       => 'decimal(5,2)',
 
235
                        'DECIMAL:'      => 'decimal(%d,2)',
 
236
                        'PDECIMAL'      => 'decimal(6,3)',
 
237
                        'PDECIMAL:'     => 'decimal(%d,3)',
 
238
                        'VCHAR_UNI'     => 'varchar(255)',
 
239
                        'VCHAR_UNI:'=> 'varchar(%d)',
 
240
                        'VCHAR_CI'      => 'varchar_ci',
 
241
                        'VARBINARY'     => 'bytea',
 
242
                ),
 
243
        );
 
244
 
 
245
        // A list of types being unsigned for better reference in some db's
 
246
        var $unsigned_types = array('UINT', 'UINT:', 'USINT', 'BOOL', 'TIMESTAMP');
 
247
        var $supported_dbms = array('firebird', 'mssql', 'mysql_40', 'mysql_41', 'oracle', 'postgres', 'sqlite');
 
248
 
 
249
        /**
 
250
        * Set this to true if you only want to return the 'to-be-executed' SQL statement(s) (as an array).
 
251
        */
 
252
        var $return_statements = false;
 
253
 
 
254
        /**
 
255
        */
 
256
        function phpbb_db_tools(&$db)
 
257
        {
 
258
                $this->db = $db;
 
259
 
 
260
                // Determine mapping database type
 
261
                switch ($this->db->sql_layer)
 
262
                {
 
263
                        case 'mysql':
 
264
                                $this->sql_layer = 'mysql_40';
 
265
                        break;
 
266
 
 
267
                        case 'mysql4':
 
268
                                if (version_compare($this->db->mysql_version, '4.1.3', '>='))
 
269
                                {
 
270
                                        $this->sql_layer = 'mysql_41';
 
271
                                }
 
272
                                else
 
273
                                {
 
274
                                        $this->sql_layer = 'mysql_40';
 
275
                                }
 
276
                        break;
 
277
 
 
278
                        case 'mysqli':
 
279
                                $this->sql_layer = 'mysql_41';
 
280
                        break;
 
281
 
 
282
                        case 'mssql':
 
283
                        case 'mssql_odbc':
 
284
                                $this->sql_layer = 'mssql';
 
285
                        break;
 
286
 
 
287
                        default:
 
288
                                $this->sql_layer = $this->db->sql_layer;
 
289
                        break;
 
290
                }
 
291
        }
 
292
 
 
293
        /**
 
294
        * Handle passed database update array.
 
295
        * Expected structure...
 
296
        * Key being one of the following
 
297
        *       change_columns: Column changes (only type, not name)
 
298
        *       add_columns: Add columns to a table
 
299
        *       drop_keys: Dropping keys
 
300
        *       drop_columns: Removing/Dropping columns
 
301
        *       add_primary_keys: adding primary keys
 
302
        *       add_unique_index: adding an unique index
 
303
        *       add_index: adding an index
 
304
        *
 
305
        * The values are in this format:
 
306
        *               {TABLE NAME}            => array(
 
307
        *                       {COLUMN NAME}           => array({COLUMN TYPE}, {DEFAULT VALUE}, {OPTIONAL VARIABLES}),
 
308
        *                       {KEY/INDEX NAME}        => array({COLUMN NAMES}),
 
309
        *               )
 
310
        *
 
311
        * For more information have a look at /develop/create_schema_files.php (only available through CVS)
 
312
        */
 
313
        function perform_schema_changes($schema_changes)
 
314
        {
 
315
                if (empty($schema_changes))
 
316
                {
 
317
                        return;
 
318
                }
 
319
 
 
320
                $statements = array();
 
321
 
 
322
                // Change columns?
 
323
                if (!empty($schema_changes['change_columns']))
 
324
                {
 
325
                        foreach ($schema_changes['change_columns'] as $table => $columns)
 
326
                        {
 
327
                                foreach ($columns as $column_name => $column_data)
 
328
                                {
 
329
                                        $result = $this->sql_column_change($table, $column_name, $column_data);
 
330
 
 
331
                                        if ($this->return_statements)
 
332
                                        {
 
333
                                                $statements = array_merge($statements, $result);
 
334
                                        }
 
335
                                }
 
336
                        }
 
337
                }
 
338
 
 
339
                // Add columns?
 
340
                if (!empty($schema_changes['add_columns']))
 
341
                {
 
342
                        foreach ($schema_changes['add_columns'] as $table => $columns)
 
343
                        {
 
344
                                foreach ($columns as $column_name => $column_data)
 
345
                                {
 
346
                                        // Only add the column if it does not exist yet
 
347
                                        if (!$this->sql_column_exists($table, $column_name))
 
348
                                        {
 
349
                                                $result = $this->sql_column_add($table, $column_name, $column_data);
 
350
 
 
351
                                                if ($this->return_statements)
 
352
                                                {
 
353
                                                        $statements = array_merge($statements, $result);
 
354
                                                }
 
355
                                        }
 
356
                                }
 
357
                        }
 
358
                }
 
359
 
 
360
                // Remove keys?
 
361
                if (!empty($schema_changes['drop_keys']))
 
362
                {
 
363
                        foreach ($schema_changes['drop_keys'] as $table => $indexes)
 
364
                        {
 
365
                                foreach ($indexes as $index_name)
 
366
                                {
 
367
                                        $result = $this->sql_index_drop($table, $index_name);
 
368
 
 
369
                                        if ($this->return_statements)
 
370
                                        {
 
371
                                                $statements = array_merge($statements, $result);
 
372
                                        }
 
373
                                }
 
374
                        }
 
375
                }
 
376
 
 
377
                // Drop columns?
 
378
                if (!empty($schema_changes['drop_columns']))
 
379
                {
 
380
                        foreach ($schema_changes['drop_columns'] as $table => $columns)
 
381
                        {
 
382
                                foreach ($columns as $column)
 
383
                                {
 
384
                                        $result = $this->sql_column_remove($table, $column);
 
385
 
 
386
                                        if ($this->return_statements)
 
387
                                        {
 
388
                                                $statements = array_merge($statements, $result);
 
389
                                        }
 
390
                                }
 
391
                        }
 
392
                }
 
393
 
 
394
                // Add primary keys?
 
395
                if (!empty($schema_changes['add_primary_keys']))
 
396
                {
 
397
                        foreach ($schema_changes['add_primary_keys'] as $table => $columns)
 
398
                        {
 
399
                                $result = $this->sql_create_primary_key($table, $columns);
 
400
 
 
401
                                if ($this->return_statements)
 
402
                                {
 
403
                                        $statements = array_merge($statements, $result);
 
404
                                }
 
405
                        }
 
406
                }
 
407
 
 
408
                // Add unqiue indexes?
 
409
                if (!empty($schema_changes['add_unique_index']))
 
410
                {
 
411
                        foreach ($schema_changes['add_unique_index'] as $table => $index_array)
 
412
                        {
 
413
                                foreach ($index_array as $index_name => $column)
 
414
                                {
 
415
                                        $result = $this->sql_create_unique_index($table, $index_name, $column);
 
416
 
 
417
                                        if ($this->return_statements)
 
418
                                        {
 
419
                                                $statements = array_merge($statements, $result);
 
420
                                        }
 
421
                                }
 
422
                        }
 
423
                }
 
424
 
 
425
                // Add indexes?
 
426
                if (!empty($schema_changes['add_index']))
 
427
                {
 
428
                        foreach ($schema_changes['add_index'] as $table => $index_array)
 
429
                        {
 
430
                                foreach ($index_array as $index_name => $column)
 
431
                                {
 
432
                                        $result = $this->sql_create_index($table, $index_name, $column);
 
433
 
 
434
                                        if ($this->return_statements)
 
435
                                        {
 
436
                                                $statements = array_merge($statements, $result);
 
437
                                        }
 
438
                                }
 
439
                        }
 
440
                }
 
441
 
 
442
                if ($this->return_statements)
 
443
                {
 
444
                        return $statements;
 
445
                }
 
446
        }
 
447
 
 
448
        /**
 
449
        * Check if a specified column exist
 
450
        * @return bool True if column exists, else false
 
451
        */
 
452
        function sql_column_exists($table, $column_name)
 
453
        {
 
454
                switch ($this->sql_layer)
 
455
                {
 
456
                        case 'mysql_40':
 
457
                        case 'mysql_41':
 
458
 
 
459
                                $sql = "SHOW COLUMNS FROM $table";
 
460
                                $result = $this->db->sql_query($sql);
 
461
 
 
462
                                while ($row = $this->db->sql_fetchrow($result))
 
463
                                {
 
464
                                        // lower case just in case
 
465
                                        if (strtolower($row['Field']) == $column_name)
 
466
                                        {
 
467
                                                $this->db->sql_freeresult($result);
 
468
                                                return true;
 
469
                                        }
 
470
                                }
 
471
                                $this->db->sql_freeresult($result);
 
472
                                return false;
 
473
                        break;
 
474
 
 
475
                        // PostgreSQL has a way of doing this in a much simpler way but would
 
476
                        // not allow us to support all versions of PostgreSQL
 
477
                        case 'postgres':
 
478
                                $sql = "SELECT a.attname
 
479
                                        FROM pg_class c, pg_attribute a
 
480
                                        WHERE c.relname = '{$table}'
 
481
                                                AND a.attnum > 0
 
482
                                                AND a.attrelid = c.oid";
 
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['attname']) == $column_name)
 
488
                                        {
 
489
                                                $this->db->sql_freeresult($result);
 
490
                                                return true;
 
491
                                        }
 
492
                                }
 
493
                                $this->db->sql_freeresult($result);
 
494
 
 
495
                                return false;
 
496
                        break;
 
497
 
 
498
                        // same deal with PostgreSQL, we must perform more complex operations than
 
499
                        // we technically could
 
500
                        case 'mssql':
 
501
                                $sql = "SELECT c.name
 
502
                                        FROM syscolumns c
 
503
                                        LEFT JOIN sysobjects o ON c.id = o.id
 
504
                                        WHERE o.name = '{$table}'";
 
505
                                $result = $this->db->sql_query($sql);
 
506
                                while ($row = $this->db->sql_fetchrow($result))
 
507
                                {
 
508
                                        // lower case just in case
 
509
                                        if (strtolower($row['name']) == $column_name)
 
510
                                        {
 
511
                                                $this->db->sql_freeresult($result);
 
512
                                                return true;
 
513
                                        }
 
514
                                }
 
515
                                $this->db->sql_freeresult($result);
 
516
                                return false;
 
517
                        break;
 
518
 
 
519
                        case 'oracle':
 
520
                                $sql = "SELECT column_name
 
521
                                        FROM user_tab_columns
 
522
                                        WHERE table_name = '{$table}'";
 
523
                                $result = $this->db->sql_query($sql);
 
524
                                while ($row = $this->db->sql_fetchrow($result))
 
525
                                {
 
526
                                        // lower case just in case
 
527
                                        if (strtolower($row['column_name']) == $column_name)
 
528
                                        {
 
529
                                                $this->db->sql_freeresult($result);
 
530
                                                return true;
 
531
                                        }
 
532
                                }
 
533
                                $this->db->sql_freeresult($result);
 
534
                                return false;
 
535
                        break;
 
536
 
 
537
                        case 'firebird':
 
538
                                $sql = "SELECT RDB\$FIELD_NAME as FNAME
 
539
                                        FROM RDB\$RELATION_FIELDS
 
540
                                        WHERE RDB\$RELATION_NAME = '{$table}'";
 
541
                                $result = $this->db->sql_query($sql);
 
542
                                while ($row = $this->db->sql_fetchrow($result))
 
543
                                {
 
544
                                        // lower case just in case
 
545
                                        if (strtolower($row['fname']) == $column_name)
 
546
                                        {
 
547
                                                $this->db->sql_freeresult($result);
 
548
                                                return true;
 
549
                                        }
 
550
                                }
 
551
                                $this->db->sql_freeresult($result);
 
552
                                return false;
 
553
                        break;
 
554
 
 
555
                        // ugh, SQLite
 
556
                        case 'sqlite':
 
557
                                $sql = "SELECT sql
 
558
                                        FROM sqlite_master
 
559
                                        WHERE type = 'table'
 
560
                                                AND name = '{$table}'";
 
561
                                $result = $this->db->sql_query($sql);
 
562
 
 
563
                                if (!$result)
 
564
                                {
 
565
                                        return false;
 
566
                                }
 
567
 
 
568
                                $row = $this->db->sql_fetchrow($result);
 
569
                                $this->db->sql_freeresult($result);
 
570
 
 
571
                                preg_match('#\((.*)\)#s', $row['sql'], $matches);
 
572
 
 
573
                                $cols = trim($matches[1]);
 
574
                                $col_array = preg_split('/,(?![\s\w]+\))/m', $cols);
 
575
 
 
576
                                foreach ($col_array as $declaration)
 
577
                                {
 
578
                                        $entities = preg_split('#\s+#', trim($declaration));
 
579
                                        if ($entities[0] == 'PRIMARY')
 
580
                                        {
 
581
                                                continue;
 
582
                                        }
 
583
 
 
584
                                        if (strtolower($entities[0]) == $column_name)
 
585
                                        {
 
586
                                                return true;
 
587
                                        }
 
588
                                }
 
589
                                return false;
 
590
                        break;
 
591
                }
 
592
        }
 
593
 
 
594
        /**
 
595
        * Private method for performing sql statements (either execute them or return them)
 
596
        * @private
 
597
        */
 
598
        function _sql_run_sql($statements)
 
599
        {
 
600
                if ($this->return_statements)
 
601
                {
 
602
                        return $statements;
 
603
                }
 
604
 
 
605
                // We could add error handling here...
 
606
                foreach ($statements as $sql)
 
607
                {
 
608
                        if ($sql === 'begin')
 
609
                        {
 
610
                                $this->db->sql_transaction('begin');
 
611
                        }
 
612
                        else if ($sql === 'commit')
 
613
                        {
 
614
                                $this->db->sql_transaction('commit');
 
615
                        }
 
616
                        else
 
617
                        {
 
618
                                $this->db->sql_query($sql);
 
619
                        }
 
620
                }
 
621
 
 
622
                return true;
 
623
        }
 
624
 
 
625
        /**
 
626
        * Function to prepare some column information for better usage
 
627
        * @private
 
628
        */
 
629
        function sql_prepare_column_data($table_name, $column_name, $column_data)
 
630
        {
 
631
                // Get type
 
632
                if (strpos($column_data[0], ':') !== false)
 
633
                {
 
634
                        list($orig_column_type, $column_length) = explode(':', $column_data[0]);
 
635
 
 
636
                        if (!is_array($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']))
 
637
                        {
 
638
                                $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'], $column_length);
 
639
                        }
 
640
                        else
 
641
                        {
 
642
                                if (isset($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule']))
 
643
                                {
 
644
                                        switch ($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule'][0])
 
645
                                        {
 
646
                                                case 'div':
 
647
                                                        $column_length /= $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule'][1];
 
648
                                                        $column_length = ceil($column_length);
 
649
                                                        $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'][0], $column_length);
 
650
                                                break;
 
651
                                        }
 
652
                                }
 
653
 
 
654
                                if (isset($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit']))
 
655
                                {
 
656
                                        switch ($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][0])
 
657
                                        {
 
658
                                                case 'mult':
 
659
                                                        $column_length *= $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][1];
 
660
                                                        if ($column_length > $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][2])
 
661
                                                        {
 
662
                                                                $column_type = $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][3];
 
663
                                                        }
 
664
                                                        else
 
665
                                                        {
 
666
                                                                $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'][0], $column_length);
 
667
                                                        }
 
668
                                                break;
 
669
                                        }
 
670
                                }
 
671
                        }
 
672
                        $orig_column_type .= ':';
 
673
                }
 
674
                else
 
675
                {
 
676
                        $orig_column_type = $column_data[0];
 
677
                        $column_type = $this->dbms_type_map[$this->sql_layer][$column_data[0]];
 
678
                }
 
679
 
 
680
                // Adjust default value if db-dependant specified
 
681
                if (is_array($column_data[1]))
 
682
                {
 
683
                        $column_data[1] = (isset($column_data[1][$this->sql_layer])) ? $column_data[1][$this->sql_layer] : $column_data[1]['default'];
 
684
                }
 
685
 
 
686
                $sql = '';
 
687
 
 
688
                $return_array = array();
 
689
 
 
690
                switch ($this->sql_layer)
 
691
                {
 
692
                        case 'firebird':
 
693
                                $sql .= " {$column_type} ";
 
694
 
 
695
                                if (!is_null($column_data[1]))
 
696
                                {
 
697
                                        $sql .= 'DEFAULT ' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ' ';
 
698
                                }
 
699
 
 
700
                                $sql .= 'NOT NULL';
 
701
 
 
702
                                // This is a UNICODE column and thus should be given it's fair share
 
703
                                if (preg_match('/^X?STEXT_UNI|VCHAR_(CI|UNI:?)/', $column_data[0]))
 
704
                                {
 
705
                                        $sql .= ' COLLATE UNICODE';
 
706
                                }
 
707
 
 
708
                        break;
 
709
 
 
710
                        case 'mssql':
 
711
                                $sql .= " {$column_type} ";
 
712
                                $sql_default = " {$column_type} ";
 
713
 
 
714
                                // For adding columns we need the default definition
 
715
                                if (!is_null($column_data[1]))
 
716
                                {
 
717
                                        // For hexadecimal values do not use single quotes
 
718
                                        if (strpos($column_data[1], '0x') === 0)
 
719
                                        {
 
720
                                                $sql_default .= 'DEFAULT (' . $column_data[1] . ') ';
 
721
                                        }
 
722
                                        else
 
723
                                        {
 
724
                                                $sql_default .= 'DEFAULT (' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ') ';
 
725
                                        }
 
726
                                }
 
727
 
 
728
                                $sql .= 'NOT NULL';
 
729
                                $sql_default .= 'NOT NULL';
 
730
 
 
731
                                $return_array['column_type_sql_default'] = $sql_default;
 
732
                        break;
 
733
 
 
734
                        case 'mysql_40':
 
735
                        case 'mysql_41':
 
736
                                $sql .= " {$column_type} ";
 
737
 
 
738
                                // For hexadecimal values do not use single quotes
 
739
                                if (!is_null($column_data[1]) && substr($column_type, -4) !== 'text' && substr($column_type, -4) !== 'blob')
 
740
                                {
 
741
                                        $sql .= (strpos($column_data[1], '0x') === 0) ? "DEFAULT {$column_data[1]} " : "DEFAULT '{$column_data[1]}' ";
 
742
                                }
 
743
                                $sql .= 'NOT NULL';
 
744
 
 
745
                                if (isset($column_data[2]))
 
746
                                {
 
747
                                        if ($column_data[2] == 'auto_increment')
 
748
                                        {
 
749
                                                $sql .= ' auto_increment';
 
750
                                        }
 
751
                                        else if ($this->sql_layer === 'mysql_41' && $column_data[2] == 'true_sort')
 
752
                                        {
 
753
                                                $sql .= ' COLLATE utf8_unicode_ci';
 
754
                                        }
 
755
                                }
 
756
 
 
757
                        break;
 
758
 
 
759
                        case 'oracle':
 
760
                                $sql .= " {$column_type} ";
 
761
                                $sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : '';
 
762
 
 
763
                                // In Oracle empty strings ('') are treated as NULL.
 
764
                                // Therefore in oracle we allow NULL's for all DEFAULT '' entries
 
765
                                // Oracle does not like setting NOT NULL on a column that is already NOT NULL (this happens only on number fields)
 
766
                                if (preg_match('/number/i', $column_type))
 
767
                                {
 
768
                                        $sql .= ($column_data[1] === '') ? '' : 'NOT NULL';
 
769
                                }
 
770
                        break;
 
771
 
 
772
                        case 'postgres':
 
773
                                $return_array['column_type'] = $column_type;
 
774
 
 
775
                                $sql .= " {$column_type} ";
 
776
 
 
777
                                if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
 
778
                                {
 
779
                                        $default_val = "nextval('{$table_name}_seq')";
 
780
                                }
 
781
                                else if (!is_null($column_data[1]))
 
782
                                {
 
783
                                        $default_val = "'" . $column_data[1] . "'";
 
784
                                        $return_array['null'] = 'NOT NULL';
 
785
                                        $sql .= 'NOT NULL ';
 
786
                                }
 
787
 
 
788
                                $return_array['default'] = $default_val;
 
789
 
 
790
                                $sql .= "DEFAULT {$default_val}";
 
791
 
 
792
                                // Unsigned? Then add a CHECK contraint
 
793
                                if (in_array($orig_column_type, $this->unsigned_types))
 
794
                                {
 
795
                                        $return_array['constraint'] = "CHECK ({$column_name} >= 0)";
 
796
                                        $sql .= " CHECK ({$column_name} >= 0)";
 
797
                                }
 
798
                        break;
 
799
 
 
800
                        case 'sqlite':
 
801
                                if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
 
802
                                {
 
803
                                        $sql .= ' INTEGER PRIMARY KEY';
 
804
                                }
 
805
                                else
 
806
                                {
 
807
                                        $sql .= ' ' . $column_type;
 
808
                                }
 
809
 
 
810
                                $sql .= ' NOT NULL ';
 
811
                                $sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}'" : '';
 
812
                        break;
 
813
                }
 
814
 
 
815
                $return_array['column_type_sql'] = $sql;
 
816
 
 
817
                return $return_array;
 
818
        }
 
819
 
 
820
        /**
 
821
        * Add new column
 
822
        */
 
823
        function sql_column_add($table_name, $column_name, $column_data)
 
824
        {
 
825
                $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
 
826
                $statements = array();
 
827
 
 
828
                switch ($this->sql_layer)
 
829
                {
 
830
                        case 'firebird':
 
831
                                $statements[] = 'ALTER TABLE "' . $table_name . '" ADD "' . $column_name . '" ' . $column_data['column_type_sql'];
 
832
                        break;
 
833
 
 
834
                        case 'mssql':
 
835
                                $statements[] = 'ALTER TABLE [' . $table_name . '] ADD [' . $column_name . '] ' . $column_data['column_type_sql_default'];
 
836
                        break;
 
837
 
 
838
                        case 'mysql_40':
 
839
                        case 'mysql_41':
 
840
                                $statements[] = 'ALTER TABLE `' . $table_name . '` ADD COLUMN `' . $column_name . '` ' . $column_data['column_type_sql'];
 
841
                        break;
 
842
 
 
843
                        case 'oracle':
 
844
                                $statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' ' . $column_data['column_type_sql'];
 
845
                        break;
 
846
 
 
847
                        case 'postgres':
 
848
                                $statements[] = 'ALTER TABLE ' . $table_name . ' ADD COLUMN "' . $column_name . '" ' . $column_data['column_type_sql'];
 
849
                        break;
 
850
 
 
851
                        case 'sqlite':
 
852
                                if (version_compare(sqlite_libversion(), '3.0') == -1)
 
853
                                {
 
854
                                        $sql = "SELECT sql
 
855
                                                FROM sqlite_master
 
856
                                                WHERE type = 'table'
 
857
                                                        AND name = '{$table_name}'
 
858
                                                ORDER BY type DESC, name;";
 
859
                                        $result = $this->db->sql_query($sql);
 
860
 
 
861
                                        if (!$result)
 
862
                                        {
 
863
                                                break;
 
864
                                        }
 
865
 
 
866
                                        $row = $this->db->sql_fetchrow($result);
 
867
                                        $this->db->sql_freeresult($result);
 
868
 
 
869
                                        $statements[] = 'begin';
 
870
 
 
871
                                        // Create a backup table and populate it, destroy the existing one
 
872
                                        $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
 
873
                                        $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
 
874
                                        $statements[] = 'DROP TABLE ' . $table_name;
 
875
 
 
876
                                        preg_match('#\((.*)\)#s', $row['sql'], $matches);
 
877
 
 
878
                                        $new_table_cols = trim($matches[1]);
 
879
                                        $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
 
880
                                        $column_list = array();
 
881
 
 
882
                                        foreach ($old_table_cols as $declaration)
 
883
                                        {
 
884
                                                $entities = preg_split('#\s+#', trim($declaration));
 
885
                                                if ($entities[0] == 'PRIMARY')
 
886
                                                {
 
887
                                                        continue;
 
888
                                                }
 
889
                                                $column_list[] = $entities[0];
 
890
                                        }
 
891
 
 
892
                                        $columns = implode(',', $column_list);
 
893
 
 
894
                                        $new_table_cols = $column_name . ' ' . $column_data['column_type_sql'] . ',' . $new_table_cols;
 
895
 
 
896
                                        // create a new table and fill it up. destroy the temp one
 
897
                                        $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');';
 
898
                                        $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
 
899
                                        $statements[] = 'DROP TABLE ' . $table_name . '_temp';
 
900
 
 
901
                                        $statements[] = 'commit';
 
902
                                }
 
903
                                else
 
904
                                {
 
905
                                        $statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' [' . $column_data['column_type_sql'] . ']';
 
906
                                }
 
907
                        break;
 
908
                }
 
909
 
 
910
                return $this->_sql_run_sql($statements);
 
911
        }
 
912
 
 
913
        /**
 
914
        * Drop column
 
915
        */
 
916
        function sql_column_remove($table_name, $column_name)
 
917
        {
 
918
                $statements = array();
 
919
 
 
920
                switch ($this->sql_layer)
 
921
                {
 
922
                        case 'firebird':
 
923
                                $statements[] = 'ALTER TABLE "' . $table_name . '" DROP "' . $column_name . '"';
 
924
                        break;
 
925
 
 
926
                        case 'mssql':
 
927
                                $statements[] = 'ALTER TABLE [' . $table_name . '] DROP COLUMN [' . $column_name . ']';
 
928
                        break;
 
929
 
 
930
                        case 'mysql_40':
 
931
                        case 'mysql_41':
 
932
                                $statements[] = 'ALTER TABLE `' . $table_name . '` DROP COLUMN `' . $column_name . '`';
 
933
                        break;
 
934
 
 
935
                        case 'oracle':
 
936
                                $statements[] = 'ALTER TABLE ' . $table_name . ' DROP ' . $column_name;
 
937
                        break;
 
938
 
 
939
                        case 'postgres':
 
940
                                $statements[] = 'ALTER TABLE ' . $table_name . ' DROP COLUMN "' . $column_name . '"';
 
941
                        break;
 
942
 
 
943
                        case 'sqlite':
 
944
                                if (version_compare(sqlite_libversion(), '3.0') == -1)
 
945
                                {
 
946
                                        $sql = "SELECT sql
 
947
                                                FROM sqlite_master
 
948
                                                WHERE type = 'table'
 
949
                                                        AND name = '{$table_name}'
 
950
                                                ORDER BY type DESC, name;";
 
951
                                        $result = $this->db->sql_query($sql);
 
952
 
 
953
                                        if (!$result)
 
954
                                        {
 
955
                                                break;
 
956
                                        }
 
957
 
 
958
                                        $row = $this->db->sql_fetchrow($result);
 
959
                                        $this->db->sql_freeresult($result);
 
960
 
 
961
                                        $statements[] = 'begin';
 
962
 
 
963
                                        // Create a backup table and populate it, destroy the existing one
 
964
                                        $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
 
965
                                        $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
 
966
                                        $statements[] = 'DROP TABLE ' . $table_name;
 
967
 
 
968
                                        preg_match('#\((.*)\)#s', $row['sql'], $matches);
 
969
 
 
970
                                        $new_table_cols = trim($matches[1]);
 
971
                                        $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
 
972
                                        $column_list = array();
 
973
 
 
974
                                        foreach ($old_table_cols as $declaration)
 
975
                                        {
 
976
                                                $entities = preg_split('#\s+#', trim($declaration));
 
977
                                                if ($entities[0] == 'PRIMARY' || $entities[0] === $column_name)
 
978
                                                {
 
979
                                                        continue;
 
980
                                                }
 
981
                                                $column_list[] = $entities[0];
 
982
                                        }
 
983
 
 
984
                                        $columns = implode(',', $column_list);
 
985
 
 
986
                                        $new_table_cols = $new_table_cols = preg_replace('/' . $column_name . '[^,]+(?:,|$)/m', '', $new_table_cols);
 
987
 
 
988
                                        // create a new table and fill it up. destroy the temp one
 
989
                                        $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');';
 
990
                                        $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
 
991
                                        $statements[] = 'DROP TABLE ' . $table_name . '_temp';
 
992
 
 
993
                                        $statements[] = 'commit';
 
994
                                }
 
995
                                else
 
996
                                {
 
997
                                        $statements[] = 'ALTER TABLE ' . $table_name . ' DROP COLUMN ' . $column_name;
 
998
                                }
 
999
                        break;
 
1000
                }
 
1001
 
 
1002
                return $this->_sql_run_sql($statements);
 
1003
        }
 
1004
 
 
1005
        /**
 
1006
        * Drop Index
 
1007
        */
 
1008
        function sql_index_drop($table_name, $index_name)
 
1009
        {
 
1010
                $statements = array();
 
1011
 
 
1012
                switch ($this->sql_layer)
 
1013
                {
 
1014
                        case 'mssql':
 
1015
                                $statements[] = 'DROP INDEX ' . $table_name . '.' . $index_name;
 
1016
                        break;
 
1017
 
 
1018
                        case 'mysql_40':
 
1019
                        case 'mysql_41':
 
1020
                                $statements[] = 'DROP INDEX ' . $index_name . ' ON ' . $table_name;
 
1021
                        break;
 
1022
 
 
1023
                        case 'firebird':
 
1024
                        case 'oracle':
 
1025
                        case 'postgres':
 
1026
                        case 'sqlite':
 
1027
                                $statements[] = 'DROP INDEX ' . $table_name . '_' . $index_name;
 
1028
                        break;
 
1029
                }
 
1030
 
 
1031
                return $this->_sql_run_sql($statements);
 
1032
        }
 
1033
 
 
1034
        /**
 
1035
        * Add primary key
 
1036
        */
 
1037
        function sql_create_primary_key($table_name, $column)
 
1038
        {
 
1039
                $statements = array();
 
1040
 
 
1041
                switch ($this->sql_layer)
 
1042
                {
 
1043
                        case 'firebird':
 
1044
                        case 'postgres':
 
1045
                                $statements[] = 'ALTER TABLE ' . $table_name . ' ADD PRIMARY KEY (' . implode(', ', $column) . ')';
 
1046
                        break;
 
1047
 
 
1048
                        case 'mssql':
 
1049
                                $sql = "ALTER TABLE [{$table_name}] WITH NOCHECK ADD ";
 
1050
                                $sql .= "CONSTRAINT [PK_{$table_name}] PRIMARY KEY  CLUSTERED (";
 
1051
                                $sql .= '[' . implode("],\n\t\t[", $column) . ']';
 
1052
                                $sql .= ') ON [PRIMARY]';
 
1053
 
 
1054
                                $statements[] = $sql;
 
1055
                        break;
 
1056
 
 
1057
                        case 'mysql_40':
 
1058
                        case 'mysql_41':
 
1059
                                $statements[] = 'ALTER TABLE ' . $table_name . ' ADD PRIMARY KEY (' . implode(', ', $column) . ')';
 
1060
                        break;
 
1061
 
 
1062
                        case 'oracle':
 
1063
                                $statements[] = 'ALTER TABLE ' . $table_name . 'add CONSTRAINT pk_' . $table_name . ' PRIMARY KEY (' . implode(', ', $column) . ')';
 
1064
                        break;
 
1065
 
 
1066
                        case 'sqlite':
 
1067
                                $sql = "SELECT sql
 
1068
                                        FROM sqlite_master
 
1069
                                        WHERE type = 'table'
 
1070
                                                AND name = '{$table_name}'
 
1071
                                        ORDER BY type DESC, name;";
 
1072
                                $result = $this->db->sql_query($sql);
 
1073
 
 
1074
                                if (!$result)
 
1075
                                {
 
1076
                                        break;
 
1077
                                }
 
1078
 
 
1079
                                $row = $this->db->sql_fetchrow($result);
 
1080
                                $this->db->sql_freeresult($result);
 
1081
 
 
1082
                                $statements[] = 'begin';
 
1083
 
 
1084
                                // Create a backup table and populate it, destroy the existing one
 
1085
                                $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
 
1086
                                $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
 
1087
                                $statements[] = 'DROP TABLE ' . $table_name;
 
1088
 
 
1089
                                preg_match('#\((.*)\)#s', $row['sql'], $matches);
 
1090
 
 
1091
                                $new_table_cols = trim($matches[1]);
 
1092
                                $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
 
1093
                                $column_list = array();
 
1094
 
 
1095
                                foreach ($old_table_cols as $declaration)
 
1096
                                {
 
1097
                                        $entities = preg_split('#\s+#', trim($declaration));
 
1098
                                        if ($entities[0] == 'PRIMARY')
 
1099
                                        {
 
1100
                                                continue;
 
1101
                                        }
 
1102
                                        $column_list[] = $entities[0];
 
1103
                                }
 
1104
 
 
1105
                                $columns = implode(',', $column_list);
 
1106
 
 
1107
                                // create a new table and fill it up. destroy the temp one
 
1108
                                $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ', PRIMARY KEY (' . implode(', ', $column) . '));';
 
1109
                                $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
 
1110
                                $statements[] = 'DROP TABLE ' . $table_name . '_temp';
 
1111
 
 
1112
                                $statements[] = 'commit';
 
1113
                        break;
 
1114
                }
 
1115
 
 
1116
                return $this->_sql_run_sql($statements);
 
1117
        }
 
1118
 
 
1119
        /**
 
1120
        * Add unique index
 
1121
        */
 
1122
        function sql_create_unique_index($table_name, $index_name, $column)
 
1123
        {
 
1124
                $statements = array();
 
1125
 
 
1126
                switch ($this->sql_layer)
 
1127
                {
 
1128
                        case 'firebird':
 
1129
                        case 'postgres':
 
1130
                        case 'oracle':
 
1131
                        case 'sqlite':
 
1132
                                $statements[] = 'CREATE UNIQUE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
 
1133
                        break;
 
1134
 
 
1135
                        case 'mysql_40':
 
1136
                        case 'mysql_41':
 
1137
                                $statements[] = 'CREATE UNIQUE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
 
1138
                        break;
 
1139
 
 
1140
                        case 'mssql':
 
1141
                                $statements[] = 'CREATE UNIQUE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ') ON [PRIMARY]';
 
1142
                        break;
 
1143
                }
 
1144
 
 
1145
                return $this->_sql_run_sql($statements);
 
1146
        }
 
1147
 
 
1148
        /**
 
1149
        * Add index
 
1150
        */
 
1151
        function sql_create_index($table_name, $index_name, $column)
 
1152
        {
 
1153
                $statements = array();
 
1154
 
 
1155
                switch ($this->sql_layer)
 
1156
                {
 
1157
                        case 'firebird':
 
1158
                        case 'postgres':
 
1159
                        case 'oracle':
 
1160
                        case 'sqlite':
 
1161
                                $statements[] = 'CREATE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
 
1162
                        break;
 
1163
 
 
1164
                        case 'mysql_40':
 
1165
                        case 'mysql_41':
 
1166
                                $statements[] = 'CREATE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
 
1167
                        break;
 
1168
 
 
1169
                        case 'mssql':
 
1170
                                $statements[] = 'CREATE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ') ON [PRIMARY]';
 
1171
                        break;
 
1172
                }
 
1173
 
 
1174
                return $this->_sql_run_sql($statements);
 
1175
        }
 
1176
 
 
1177
        /**
 
1178
        * List all of the indices that belong to a table,
 
1179
        * does not count:
 
1180
        * * UNIQUE indices
 
1181
        * * PRIMARY keys
 
1182
        */
 
1183
        function sql_list_index($table_name)
 
1184
        {
 
1185
                $index_array = array();
 
1186
 
 
1187
                if ($this->sql_layer == 'mssql')
 
1188
                {
 
1189
                        $sql = "EXEC sp_statistics '$table_name'";
 
1190
                        $result = $this->db->sql_query($sql);
 
1191
                        while ($row = $this->db->sql_fetchrow($result))
 
1192
                        {
 
1193
                                if ($row['TYPE'] == 3)
 
1194
                                {
 
1195
                                        $index_array[] = $row['INDEX_NAME'];
 
1196
                                }
 
1197
                        }
 
1198
                        $this->db->sql_freeresult($result);
 
1199
                }
 
1200
                else
 
1201
                {
 
1202
                        switch ($this->sql_layer)
 
1203
                        {
 
1204
                                case 'firebird':
 
1205
                                        $sql = "SELECT LOWER(RDB\$INDEX_NAME) as index_name
 
1206
                                                FROM RDB\$INDICES
 
1207
                                                WHERE RDB\$RELATION_NAME = " . strtoupper($table_name) . "
 
1208
                                                        AND RDB\$UNIQUE_FLAG IS NULL
 
1209
                                                        AND RDB\$FOREIGN_KEY IS NULL";
 
1210
                                        $col = 'index_name';
 
1211
                                break;
 
1212
 
 
1213
                                case 'postgres':
 
1214
                                        $sql = "SELECT ic.relname as index_name
 
1215
                                                FROM pg_class bc, pg_class ic, pg_index i
 
1216
                                                WHERE (bc.oid = i.indrelid)
 
1217
                                                        AND (ic.oid = i.indexrelid)
 
1218
                                                        AND (bc.relname = '" . $table_name . "')
 
1219
                                                        AND (i.indisunique != 't')
 
1220
                                                        AND (i.indisprimary != 't')";
 
1221
                                        $col = 'index_name';
 
1222
                                break;
 
1223
 
 
1224
                                case 'mysql_40':
 
1225
                                case 'mysql_41':
 
1226
                                        $sql = 'SHOW KEYS
 
1227
                                                FROM ' . $table_name;
 
1228
                                        $col = 'Key_name';
 
1229
                                break;
 
1230
 
 
1231
                                case 'oracle':
 
1232
                                        $sql = "SELECT index_name
 
1233
                                                FROM user_indexes
 
1234
                                                WHERE table_name = '" . $table_name . "'
 
1235
                                                        AND generated = 'N'";
 
1236
                                break;
 
1237
 
 
1238
                                case 'sqlite':
 
1239
                                        $sql = "PRAGMA index_info('" . $table_name . "');";
 
1240
                                        $col = 'name';
 
1241
                                break;
 
1242
                        }
 
1243
 
 
1244
                        $result = $this->db->sql_query($sql);
 
1245
                        while ($row = $this->db->sql_fetchrow($result))
 
1246
                        {
 
1247
                                if (($this->sql_layer == 'mysql_40' || $this->sql_layer == 'mysql_41') && !$row['Non_unique'])
 
1248
                                {
 
1249
                                        continue;
 
1250
                                }
 
1251
 
 
1252
                                switch ($this->sql_layer)
 
1253
                                {
 
1254
                                        case 'firebird':
 
1255
                                        case 'oracle':
 
1256
                                        case 'postgres':
 
1257
                                        case 'sqlite':
 
1258
                                                $row[$col] = substr($row[$col], strlen($table_name) + 1);
 
1259
                                        break;
 
1260
                                }
 
1261
 
 
1262
                                $index_array[] = $row[$col];
 
1263
                        }
 
1264
                        $this->db->sql_freeresult($result);
 
1265
                }
 
1266
 
 
1267
                return array_map('strtolower', $index_array);
 
1268
        }
 
1269
 
 
1270
        /**
 
1271
        * Change column type (not name!)
 
1272
        */
 
1273
        function sql_column_change($table_name, $column_name, $column_data)
 
1274
        {
 
1275
                $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
 
1276
                $statements = array();
 
1277
 
 
1278
                switch ($this->sql_layer)
 
1279
                {
 
1280
                        case 'firebird':
 
1281
                                // Change type...
 
1282
                                $statements[] = 'ALTER TABLE "' . $table_name . '" ALTER COLUMN "' . $column_name . '" TYPE ' . ' ' . $column_data['column_type_sql'];
 
1283
                        break;
 
1284
 
 
1285
                        case 'mssql':
 
1286
                                $statements[] = 'ALTER TABLE [' . $table_name . '] ALTER COLUMN [' . $column_name . '] ' . $column_data['column_type_sql'];
 
1287
                        break;
 
1288
 
 
1289
                        case 'mysql_40':
 
1290
                        case 'mysql_41':
 
1291
                                $statements[] = 'ALTER TABLE `' . $table_name . '` CHANGE `' . $column_name . '` `' . $column_name . '` ' . $column_data['column_type_sql'];
 
1292
                        break;
 
1293
 
 
1294
                        case 'oracle':
 
1295
                                $statements[] = 'ALTER TABLE ' . $table_name . ' MODIFY ' . $column_name . ' ' . $column_data['column_type_sql'];
 
1296
                        break;
 
1297
 
 
1298
                        case 'postgres':
 
1299
                                $sql = 'ALTER TABLE ' . $table_name . ' ';
 
1300
 
 
1301
                                $sql_array = array();
 
1302
                                $sql_array[] = 'ALTER COLUMN ' . $column_name . ' TYPE ' . $column_data['column_type'];
 
1303
 
 
1304
                                if (isset($column_data['null']))
 
1305
                                {
 
1306
                                        if ($column_data['null'] == 'NOT NULL')
 
1307
                                        {
 
1308
                                                $sql_array[] = 'ALTER COLUMN ' . $column_name . ' SET NOT NULL';
 
1309
                                        }
 
1310
                                        else if ($column_data['null'] == 'NULL')
 
1311
                                        {
 
1312
                                                $sql_array[] = 'ALTER COLUMN ' . $column_name . ' DROP NOT NULL';
 
1313
                                        }
 
1314
                                }
 
1315
 
 
1316
                                if (isset($column_data['default']))
 
1317
                                {
 
1318
                                        $sql_array[] = 'ALTER COLUMN ' . $column_name . ' SET DEFAULT ' . $column_data['default'];
 
1319
                                }
 
1320
 
 
1321
                                // we don't want to double up on constraints if we change different number data types
 
1322
                                if (isset($column_data['constraint']))
 
1323
                                {
 
1324
                                        $constraint_sql = "SELECT consrc as constraint_data
 
1325
                                                                FROM pg_constraint, pg_class bc
 
1326
                                                                WHERE conrelid = bc.oid
 
1327
                                                                        AND bc.relname = '{$table_name}'
 
1328
                                                                        AND NOT EXISTS (
 
1329
                                                                                SELECT *
 
1330
                                                                                        FROM pg_constraint as c, pg_inherits as i
 
1331
                                                                                        WHERE i.inhrelid = pg_constraint.conrelid
 
1332
                                                                                                AND c.conname = pg_constraint.conname
 
1333
                                                                                                AND c.consrc = pg_constraint.consrc
 
1334
                                                                                                AND c.conrelid = i.inhparent
 
1335
                                                                        )";
 
1336
 
 
1337
                                        $constraint_exists = false;
 
1338
 
 
1339
                                        $result = $this->db->sql_query($constraint_sql);
 
1340
                                        while ($row = $this->db->sql_fetchrow($result))
 
1341
                                        {
 
1342
                                                if (trim($row['constraint_data']) == trim($column_data['constraint']))
 
1343
                                                {
 
1344
                                                        $constraint_exists = true;
 
1345
                                                        break;
 
1346
                                                }
 
1347
                                        }
 
1348
                                        $this->db->sql_freeresult($result);
 
1349
 
 
1350
                                        if (!$constraint_exists)
 
1351
                                        {
 
1352
                                                $sql_array[] = 'ADD ' . $column_data['constraint'];
 
1353
                                        }
 
1354
                                }
 
1355
 
 
1356
                                $sql .= implode(', ', $sql_array);
 
1357
 
 
1358
                                $statements[] = $sql;
 
1359
                        break;
 
1360
 
 
1361
                        case 'sqlite':
 
1362
 
 
1363
                                $sql = "SELECT sql
 
1364
                                        FROM sqlite_master
 
1365
                                        WHERE type = 'table'
 
1366
                                                AND name = '{$table_name}'
 
1367
                                        ORDER BY type DESC, name;";
 
1368
                                $result = $this->db->sql_query($sql);
 
1369
 
 
1370
                                if (!$result)
 
1371
                                {
 
1372
                                        break;
 
1373
                                }
 
1374
 
 
1375
                                $row = $this->db->sql_fetchrow($result);
 
1376
                                $this->db->sql_freeresult($result);
 
1377
 
 
1378
                                $statements[] = 'begin';
 
1379
 
 
1380
                                // Create a temp table and populate it, destroy the existing one
 
1381
                                $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
 
1382
                                $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
 
1383
                                $statements[] = 'DROP TABLE ' . $table_name;
 
1384
 
 
1385
                                preg_match('#\((.*)\)#s', $row['sql'], $matches);
 
1386
 
 
1387
                                $new_table_cols = trim($matches[1]);
 
1388
                                $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
 
1389
                                $column_list = array();
 
1390
 
 
1391
                                foreach ($old_table_cols as $key => $declaration)
 
1392
                                {
 
1393
                                        $entities = preg_split('#\s+#', trim($declaration));
 
1394
                                        $column_list[] = $entities[0];
 
1395
                                        if ($entities[0] == $column_name)
 
1396
                                        {
 
1397
                                                $old_table_cols[$key] = $column_name . ' ' . $column_data['column_type_sql'];
 
1398
                                        }
 
1399
                                }
 
1400
 
 
1401
                                $columns = implode(',', $column_list);
 
1402
 
 
1403
                                // create a new table and fill it up. destroy the temp one
 
1404
                                $statements[] = 'CREATE TABLE ' . $table_name . ' (' . implode(',', $old_table_cols) . ');';
 
1405
                                $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
 
1406
                                $statements[] = 'DROP TABLE ' . $table_name . '_temp';
 
1407
 
 
1408
                                $statements[] = 'commit';
 
1409
 
 
1410
                        break;
 
1411
                }
 
1412
 
 
1413
                return $this->_sql_run_sql($statements);
 
1414
        }
 
1415
}
 
1416
 
 
1417
?>
 
 
b'\\ No newline at end of file'