|
443
by dcoles
Added Forum application along with unmodifed version of phpBB3 "Olympus" 3.0.0 |
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 |
?>
|