?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
(a little test for later merges)
[phpbb.git] / phpBB / install / database_update.php
blobce223cf302da583f9cab9dd074311e03ee58a37f
1 <?php
2 /**
3 *
4 * @package install
5 * @version $Id$
6 * @copyright (c) 2006 phpBB Group
7 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
8 *
9 */
10
11 $updates_to_version = '3.0.6-RC3';
12
13 // Enter any version to update from to test updates. The version within the db will not be updated.
14 $debug_from_version = false;
15
16 // Which oldest version does this updater support?
17 $oldest_from_version = '3.0.0';
18
19 // Return if we "just include it" to find out for which version the database update is responsible for
20 if (defined('IN_PHPBB') && defined('IN_INSTALL'))
21 {
22 return;
23 }
24
25 /**
26 */
27 define('IN_PHPBB', true);
28 define('IN_INSTALL', true);
29
30 $phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : './../';
31 $phpEx = substr(strrchr(__FILE__, '.'), 1);
32
33 // Report all errors, except notices
34 //error_reporting(E_ALL ^ E_NOTICE);
35 error_reporting(E_ALL);
36
37 @set_time_limit(0);
38
39 // Include essential scripts
40 include($phpbb_root_path . 'config.' . $phpEx);
41
42 if (!defined('PHPBB_INSTALLED') || empty($dbms) || empty($acm_type))
43 {
44 die("Please read: <a href='../docs/INSTALL.html'>INSTALL.html</a> before attempting to update.");
45 }
46
47 // Load Extensions
48 if (!empty($load_extensions))
49 {
50 $load_extensions = explode(',', $load_extensions);
51
52 foreach ($load_extensions as $extension)
53 {
54 @dl(trim($extension));
55 }
56 }
57
58 // Include files
59 require($phpbb_root_path . 'includes/acm/acm_' . $acm_type . '.' . $phpEx);
60 require($phpbb_root_path . 'includes/cache.' . $phpEx);
61 require($phpbb_root_path . 'includes/template.' . $phpEx);
62 require($phpbb_root_path . 'includes/session.' . $phpEx);
63 require($phpbb_root_path . 'includes/auth.' . $phpEx);
64
65 require($phpbb_root_path . 'includes/functions.' . $phpEx);
66
67 if (file_exists($phpbb_root_path . 'includes/functions_content.' . $phpEx))
68 {
69 require($phpbb_root_path . 'includes/functions_content.' . $phpEx);
70 }
71
72 require($phpbb_root_path . 'includes/functions_admin.' . $phpEx);
73 require($phpbb_root_path . 'includes/constants.' . $phpEx);
74 require($phpbb_root_path . 'includes/db/' . $dbms . '.' . $phpEx);
75 require($phpbb_root_path . 'includes/utf/utf_tools.' . $phpEx);
76
77 // If we are on PHP >= 6.0.0 we do not need some code
78 if (version_compare(PHP_VERSION, '6.0.0-dev', '>='))
79 {
80 /**
81 * @ignore
82 */
83 define('STRIP', false);
84 }
85 else
86 {
87 @set_magic_quotes_runtime(0);
88 define('STRIP', (get_magic_quotes_gpc()) ? true : false);
89 }
90
91 $user = new user();
92 $cache = new cache();
93 $db = new $sql_db();
94
95 // Add own hook handler, if present. :o
96 if (file_exists($phpbb_root_path . 'includes/hooks/index.' . $phpEx))
97 {
98 require($phpbb_root_path . 'includes/hooks/index.' . $phpEx);
99 $phpbb_hook = new phpbb_hook(array('exit_handler', 'phpbb_user_session_handler', 'append_sid', array('template', 'display')));
100
101 foreach ($cache->obtain_hooks() as $hook)
102 {
103 @include($phpbb_root_path . 'includes/hooks/' . $hook . '.' . $phpEx);
104 }
105 }
106 else
107 {
108 $phpbb_hook = false;
109 }
110
111 // Connect to DB
112 $db->sql_connect($dbhost, $dbuser, $dbpasswd, $dbname, $dbport, false, false);
113
114 // We do not need this any longer, unset for safety purposes
115 unset($dbpasswd);
116
117 $user->ip = (!empty($_SERVER['REMOTE_ADDR'])) ? htmlspecialchars($_SERVER['REMOTE_ADDR']) : '';
118
119 $sql = "SELECT config_value
120 FROM " . CONFIG_TABLE . "
121 WHERE config_name = 'default_lang'";
122 $result = $db->sql_query($sql);
123 $row = $db->sql_fetchrow($result);
124 $db->sql_freeresult($result);
125
126 $language = basename(request_var('language', ''));
127
128 if (!$language)
129 {
130 $language = $row['config_value'];
131 }
132
133 if (!file_exists($phpbb_root_path . 'language/' . $language))
134 {
135 die('No language found!');
136 }
137
138 // And finally, load the relevant language files
139 include($phpbb_root_path . 'language/' . $language . '/common.' . $phpEx);
140 include($phpbb_root_path . 'language/' . $language . '/acp/common.' . $phpEx);
141 include($phpbb_root_path . 'language/' . $language . '/install.' . $phpEx);
142
143 // Set PHP error handler to ours
144 //set_error_handler('msg_handler');
145
146 // Define some variables for the database update
147 $inline_update = (request_var('type', 0)) ? true : false;
148
149 // To let set_config() calls succeed, we need to make the config array available globally
150 $config = array();
151
152 $sql = 'SELECT *
153 FROM ' . CONFIG_TABLE;
154 $result = $db->sql_query($sql);
155
156 while ($row = $db->sql_fetchrow($result))
157 {
158 $config[$row['config_name']] = $row['config_value'];
159 }
160 $db->sql_freeresult($result);
161
162 // We do not include DB Tools here, because we can not be sure the file is up-to-date ;)
163 // Instead, this file defines a clean db_tools version (we are also not able to provide a different file, else the database update will not work standalone)
164 $db_tools = new updater_db_tools($db, true);
165
166 $database_update_info = database_update_info();
167
168 $error_ary = array();
169 $errored = false;
170
171 header('Content-type: text/html; charset=UTF-8');
172
173 ?>
174 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
175 <html xmlns="http://www.w3.org/1999/xhtml" dir="<?php echo $lang['DIRECTION']; ?>" lang="<?php echo $lang['USER_LANG']; ?>" xml:lang="<?php echo $lang['USER_LANG']; ?>">
176 <head>
177
178 <meta http-equiv="content-type" content="text/html; charset=UTF-8" />
179 <meta http-equiv="content-language" content="<?php echo $lang['USER_LANG']; ?>" />
180 <meta http-equiv="content-style-type" content="text/css" />
181 <meta http-equiv="imagetoolbar" content="no" />
182
183 <title><?php echo $lang['UPDATING_TO_LATEST_STABLE']; ?></title>
184
185 <link href="../adm/style/admin.css" rel="stylesheet" type="text/css" media="screen" />
186
187 </head>
188
189 <body>
190 <div id="wrap">
191 <div id="page-header">&nbsp;</div>
192
193 <div id="page-body">
194 <div id="acp">
195 <div class="panel">
196 <span class="corners-top"><span></span></span>
197 <div id="content">
198 <div id="main" class="install-body">
199
200 <h1><?php echo $lang['UPDATING_TO_LATEST_STABLE']; ?></h1>
201
202 <br />
203
204 <p><?php echo $lang['DATABASE_TYPE']; ?> :: <strong><?php echo $db->sql_layer; ?></strong><br />
205 <?php
206
207 if ($debug_from_version !== false)
208 {
209 $config['version'] = $debug_from_version;
210 }
211
212 echo $lang['PREVIOUS_VERSION'] . ' :: <strong>' . $config['version'] . '</strong><br />';
213 echo $lang['UPDATED_VERSION'] . ' :: <strong>' . $updates_to_version . '</strong></p>';
214
215 $current_version = str_replace('rc', 'RC', strtolower($config['version']));
216 $latest_version = str_replace('rc', 'RC', strtolower($updates_to_version));
217 $orig_version = $config['version'];
218
219 // Fill DB version
220 if (empty($config['dbms_version']))
221 {
222 set_config('dbms_version', $db->sql_server_info(true));
223 }
224
225 // Firebird update from Firebord 2.0 to 2.1+ required?
226 if ($db->sql_layer == 'firebird')
227 {
228 // We do not trust any PHP5 function enabled, we will simply test for a function new in 2.1
229 $db->sql_return_on_error(true);
230
231 $sql = 'SELECT 1 FROM RDB$DATABASE
232 WHERE BIN_AND(10, 1) = 0';
233 $result = $db->sql_query($sql);
234
235 if (!$result || $db->sql_error_triggered)
236 {
237 echo '<br /><br />';
238 echo '<h1>' . $lang['ERROR'] . '</h1><br />';
239
240 echo '<p>' . $lang['FIREBIRD_DBMS_UPDATE_REQUIRED'] . '</p>';
241
242 _print_footer();
243
244 exit_handler();
245 exit;
246 }
247
248 $db->sql_freeresult($result);
249 $db->sql_return_on_error(false);
250 }
251
252 // MySQL update from MySQL 3.x/4.x to > 4.1.x required?
253 if ($db->sql_layer == 'mysql' || $db->sql_layer == 'mysql4' || $db->sql_layer == 'mysqli')
254 {
255 // Verify by fetching column... if the column type matches the new type we update dbms_version...
256 $sql = "SHOW COLUMNS FROM " . CONFIG_TABLE;
257 $result = $db->sql_query($sql);
258
259 $column_type = '';
260 while ($row = $db->sql_fetchrow($result))
261 {
262 $field = strtolower($row['Field']);
263
264 if ($field == 'config_value')
265 {
266 $column_type = strtolower($row['Type']);
267 break;
268 }
269 }
270 $db->sql_freeresult($result);
271
272 // If column type is blob, but mysql version says we are on > 4.1.3, then the schema needs an update
273 if (strpos($column_type, 'blob') !== false && version_compare($db->sql_server_info(true), '4.1.3', '>='))
274 {
275 echo '<br /><br />';
276 echo '<h1>' . $lang['ERROR'] . '</h1><br />';
277
278 echo '<p>' . sprintf($lang['MYSQL_SCHEMA_UPDATE_REQUIRED'], $config['dbms_version'], $db->sql_server_info(true)) . '</p>';
279
280 _print_footer();
281
282 exit_handler();
283 exit;
284 }
285 }
286
287 // Now check if the user wants to update from a version we no longer support updates from
288 if (version_compare($current_version, $oldest_from_version, '<'))
289 {
290 echo '<br /><br /><h1>' . $lang['ERROR'] . '</h1><br />';
291 echo '<p>' . sprintf($lang['DB_UPDATE_NOT_SUPPORTED'], $oldest_from_version, $current_version) . '</p>';
292
293 _print_footer();
294 exit_handler();
295 exit;
296 }
297
298 // If the latest version and the current version are 'unequal', we will update the version_update_from, else we do not update anything.
299 if ($inline_update)
300 {
301 if ($current_version !== $latest_version)
302 {
303 set_config('version_update_from', $orig_version);
304 }
305 }
306 else
307 {
308 // If not called from the update script, we will actually remove the traces
309 $db->sql_query('DELETE FROM ' . CONFIG_TABLE . " WHERE config_name = 'version_update_from'");
310 }
311
312 // Schema updates
313 ?>
314 <br /><br />
315
316 <h1><?php echo $lang['UPDATE_DATABASE_SCHEMA']; ?></h1>
317
318 <br />
319 <p><?php echo $lang['PROGRESS']; ?> :: <strong>
320
321 <?php
322
323 flush();
324
325 // We go through the schema changes from the lowest to the highest version
326 // We try to also include versions 'in-between'...
327 $no_updates = true;
328 $versions = array_keys($database_update_info);
329 for ($i = 0; $i < sizeof($versions); $i++)
330 {
331 $version = $versions[$i];
332 $schema_changes = $database_update_info[$version];
333
334 $next_version = (isset($versions[$i + 1])) ? $versions[$i + 1] : $updates_to_version;
335
336 // If the installed version to be updated to is < than the current version, and if the current version is >= as the version to be updated to next, we will skip the process
337 if (version_compare($version, $current_version, '<') && version_compare($current_version, $next_version, '>='))
338 {
339 continue;
340 }
341
342 if (!sizeof($schema_changes))
343 {
344 continue;
345 }
346
347 $no_updates = false;
348
349 $statements = $db_tools->perform_schema_changes($schema_changes);
350
351 foreach ($statements as $sql)
352 {
353 _sql($sql, $errored, $error_ary);
354 }
355 }
356
357 _write_result($no_updates, $errored, $error_ary);
358
359 // Data updates
360 $error_ary = array();
361 $errored = $no_updates = false;
362
363 ?>
364
365 <br /><br />
366 <h1><?php echo $lang['UPDATING_DATA']; ?></h1>
367 <br />
368 <p><?php echo $lang['PROGRESS']; ?> :: <strong>
369
370 <?php
371
372 flush();
373
374 $no_updates = true;
375 $versions = array_keys($database_update_info);
376
377 // some code magic
378 for ($i = 0; $i < sizeof($versions); $i++)
379 {
380 $version = $versions[$i];
381 $next_version = (isset($versions[$i + 1])) ? $versions[$i + 1] : $updates_to_version;
382
383 // If the installed version to be updated to is < than the current version, and if the current version is >= as the version to be updated to next, we will skip the process
384 if (version_compare($version, $current_version, '<') && version_compare($current_version, $next_version, '>='))
385 {
386 continue;
387 }
388
389 change_database_data($no_updates, $version);
390 }
391
392 _write_result($no_updates, $errored, $error_ary);
393
394 $error_ary = array();
395 $errored = $no_updates = false;
396
397 ?>
398
399 <br /><br />
400 <h1><?php echo $lang['UPDATE_VERSION_OPTIMIZE']; ?></h1>
401 <br />
402 <p><?php echo $lang['PROGRESS']; ?> :: <strong>
403
404 <?php
405
406 flush();
407
408 if ($debug_from_version === false)
409 {
410 // update the version
411 $sql = "UPDATE " . CONFIG_TABLE . "
412 SET config_value = '$updates_to_version'
413 WHERE config_name = 'version'";
414 _sql($sql, $errored, $error_ary);
415 }
416
417 // Reset permissions
418 $sql = 'UPDATE ' . USERS_TABLE . "
419 SET user_permissions = '',
420 user_perm_from = 0";
421 _sql($sql, $errored, $error_ary);
422
423 // Update the dbms version if everything is ok...
424 set_config('dbms_version', $db->sql_server_info(true));
425
426 /* Optimize/vacuum analyze the tables where appropriate
427 // this should be done for each version in future along with
428 // the version number update
429 switch ($db->sql_layer)
430 {
431 case 'mysql':
432 case 'mysqli':
433 case 'mysql4':
434 $sql = 'OPTIMIZE TABLE ' . $table_prefix . 'auth_access, ' . $table_prefix . 'banlist, ' . $table_prefix . 'categories, ' . $table_prefix . 'config, ' . $table_prefix . 'disallow, ' . $table_prefix . 'forum_prune, ' . $table_prefix . 'forums, ' . $table_prefix . 'groups, ' . $table_prefix . 'posts, ' . $table_prefix . 'posts_text, ' . $table_prefix . 'privmsgs, ' . $table_prefix . 'privmsgs_text, ' . $table_prefix . 'ranks, ' . $table_prefix . 'search_results, ' . $table_prefix . 'search_wordlist, ' . $table_prefix . 'search_wordmatch, ' . $table_prefix . 'sessions_keys' . $table_prefix . 'smilies, ' . $table_prefix . 'themes, ' . $table_prefix . 'themes_name, ' . $table_prefix . 'topics, ' . $table_prefix . 'topics_watch, ' . $table_prefix . 'user_group, ' . $table_prefix . 'users, ' . $table_prefix . 'vote_desc, ' . $table_prefix . 'vote_results, ' . $table_prefix . 'vote_voters, ' . $table_prefix . 'words';
435 _sql($sql, $errored, $error_ary);
436 break;
437
438 case 'postgresql':
439 _sql("VACUUM ANALYZE", $errored, $error_ary);
440 break;
441 }
442 */
443
444 _write_result($no_updates, $errored, $error_ary);
445
446 ?>
447
448 <br />
449 <h1><?php echo $lang['UPDATE_COMPLETED']; ?></h1>
450
451 <br />
452
453 <?php
454
455 if (!$inline_update)
456 {
457 ?>
458
459 <p style="color:red"><?php echo $lang['UPDATE_FILES_NOTICE']; ?></p>
460
461 <p><?php echo $lang['COMPLETE_LOGIN_TO_BOARD']; ?></p>
462
463 <?php
464 }
465 else
466 {
467 ?>
468
469 <p><?php echo ((isset($lang['INLINE_UPDATE_SUCCESSFUL'])) ? $lang['INLINE_UPDATE_SUCCESSFUL'] : 'The database update was successful. Now you need to continue the update process.'); ?></p>
470
471 <p><a href="<?php echo append_sid("{$phpbb_root_path}install/index.{$phpEx}", "mode=update&amp;sub=file_check&amp;lang=$language"); ?>" class="button1"><?php echo (isset($lang['CONTINUE_UPDATE_NOW'])) ? $lang['CONTINUE_UPDATE_NOW'] : 'Continue the update process now'; ?></a></p>
472
473 <?php
474 }
475
476 // Add database update to log
477 add_log('admin', 'LOG_UPDATE_DATABASE', $orig_version, $updates_to_version);
478
479 // Now we purge the session table as well as all cache files
480 $cache->purge();
481
482 _print_footer();
483
484 garbage_collection();
485
486 if (function_exists('exit_handler'))
487 {
488 exit_handler();
489 }
490
491 /**
492 * Print out footer
493 */
494 function _print_footer()
495 {
496 echo <<<EOF
497 </div>
498 </div>
499 <span class="corners-bottom"><span></span></span>
500 </div>
501 </div>
502 </div>
503
504 <div id="page-footer">
505 Powered by phpBB &copy; 2000, 2002, 2005, 2007 <a href="http://www.phpbb.com/">phpBB Group</a>
506 </div>
507 </div>
508
509 </body>
510 </html>
511 EOF;
512 }
513
514 /**
515 * Function for triggering an sql statement
516 */
517 function _sql($sql, &$errored, &$error_ary, $echo_dot = true)
518 {
519 global $db;
520
521 if (defined('DEBUG_EXTRA'))
522 {
523 echo "<br />\n{$sql}\n<br />";
524 }
525
526 $db->sql_return_on_error(true);
527
528 $result = $db->sql_query($sql);
529 if ($db->sql_error_triggered)
530 {
531 $errored = true;
532 $error_ary['sql'][] = $db->sql_error_sql;
533 $error_ary['error_code'][] = $db->sql_error_returned;
534 }
535
536 $db->sql_return_on_error(false);
537
538 if ($echo_dot)
539 {
540 echo ". \n";
541 flush();
542 }
543
544 return $result;
545 }
546
547 function _write_result($no_updates, $errored, $error_ary)
548 {
549 global $lang;
550
551 if ($no_updates)
552 {
553 echo ' ' . $lang['NO_UPDATES_REQUIRED'] . '</strong></p>';
554 }
555 else
556 {
557 echo ' <span class="success">' . $lang['DONE'] . '</span></strong><br />' . $lang['RESULT'] . ' :: ';
558
559 if ($errored)
560 {
561 echo ' <strong>' . $lang['SOME_QUERIES_FAILED'] . '</strong> <ul>';
562
563 for ($i = 0; $i < sizeof($error_ary['sql']); $i++)
564 {
565 echo '<li>' . $lang['ERROR'] . ' :: <strong>' . htmlspecialchars($error_ary['error_code'][$i]['message']) . '</strong><br />';
566 echo $lang['SQL'] . ' :: <strong>' . htmlspecialchars($error_ary['sql'][$i]) . '</strong><br /><br /></li>';
567 }
568
569 echo '</ul> <br /><br />' . $lang['SQL_FAILURE_EXPLAIN'] . '</p>';
570 }
571 else
572 {
573 echo '<strong>' . $lang['NO_ERRORS'] . '</strong></p>';
574 }
575 }
576 }
577
578 function _add_modules($modules_to_install)
579 {
580 global $phpbb_root_path, $phpEx, $db;
581
582 include_once($phpbb_root_path . 'includes/acp/acp_modules.' . $phpEx);
583
584 $_module = new acp_modules();
585
586 foreach ($modules_to_install as $module_mode => $module_data)
587 {
588 $_module->module_class = $module_data['class'];
589
590 // Determine parent id first
591 $sql = 'SELECT module_id
592 FROM ' . MODULES_TABLE . "
593 WHERE module_class = '" . $db->sql_escape($module_data['class']) . "'
594 AND module_langname = '" . $db->sql_escape($module_data['cat']) . "'
595 AND module_mode = ''
596 AND module_basename = ''";
597 $result = $db->sql_query($sql);
598
599 // There may be more than one categories with the same name
600 $categories = array();
601 while ($row = $db->sql_fetchrow($result))
602 {
603 $categories[] = (int) $row['module_id'];
604 }
605 $db->sql_freeresult($result);
606
607 if (!sizeof($categories))
608 {
609 continue;
610 }
611
612 // Add the module to all categories found
613 foreach ($categories as $parent_id)
614 {
615 // Check if the module already exists
616 $sql = 'SELECT *
617 FROM ' . MODULES_TABLE . "
618 WHERE module_basename = '" . $db->sql_escape($module_data['base']) . "'
619 AND module_class = '" . $db->sql_escape($module_data['class']) . "'
620 AND module_langname = '" . $db->sql_escape($module_data['title']) . "'
621 AND module_mode = '" . $db->sql_escape($module_mode) . "'
622 AND module_auth = '" . $db->sql_escape($module_data['auth']) . "'
623 AND parent_id = {$parent_id}";
624 $result = $db->sql_query($sql);
625 $row = $db->sql_fetchrow($result);
626 $db->sql_freeresult($result);
627
628 // If it exists, we simply continue with the next category
629 if ($row)
630 {
631 continue;
632 }
633
634 // Build the module sql row
635 $module_row = array(
636 'module_basename' => $module_data['base'],
637 'module_enabled' => (isset($module_data['enabled'])) ? (int) $module_data['enabled'] : 1,
638 'module_display' => (isset($module_data['display'])) ? (int) $module_data['display'] : 1,
639 'parent_id' => $parent_id,
640 'module_class' => $module_data['class'],
641 'module_langname' => $module_data['title'],
642 'module_mode' => $module_mode,
643 'module_auth' => $module_data['auth'],
644 );
645
646 $_module->update_module_data($module_row, true);
647
648 // Ok, do we need to re-order the module, move it up or down?
649 if (!isset($module_data['after']))
650 {
651 continue;
652 }
653
654 $after_mode = $module_data['after'][0];
655 $after_langname = $module_data['after'][1];
656
657 // First of all, get the module id for the module this one has to be placed after
658 $sql = 'SELECT left_id
659 FROM ' . MODULES_TABLE . "
660 WHERE module_class = '" . $db->sql_escape($module_data['class']) . "'
661 AND module_basename = '" . $db->sql_escape($module_data['base']) . "'
662 AND module_langname = '" . $db->sql_escape($after_langname) . "'
663 AND module_mode = '" . $db->sql_escape($after_mode) . "'
664 AND parent_id = '{$parent_id}'";
665 $result = $db->sql_query($sql);
666 $first_left_id = (int) $db->sql_fetchfield('left_id');
667 $db->sql_freeresult($result);
668
669 if (!$first_left_id)
670 {
671 continue;
672 }
673
674 // Ok, count the number of modules between $after_mode and the added module
675 $sql = 'SELECT COUNT(module_id) as num_modules
676 FROM ' . MODULES_TABLE . "
677 WHERE module_class = '" . $db->sql_escape($module_data['class']) . "'
678 AND parent_id = {$parent_id}
679 AND left_id BETWEEN {$first_left_id} AND {$module_row['left_id']}
680 GROUP BY left_id
681 ORDER BY left_id";
682 $result = $db->sql_query($sql);
683 $steps = (int) $db->sql_fetchfield('num_modules');
684 $db->sql_freeresult($result);
685
686 // We need to substract 2
687 $steps -= 2;
688
689 if ($steps <= 0)
690 {
691 continue;
692 }
693
694 // Ok, move module up $num_modules times. ;)
695 $_module->move_module_by($module_row, 'move_up', $steps);
696 }
697 }
698
699 $_module->remove_cache_file();
700 }
701
702 /****************************************************************************
703 * ADD YOUR DATABASE SCHEMA CHANGES HERE *
704 *****************************************************************************/
705 function database_update_info()
706 {
707 return array(
708 // Changes from 3.0.0 to the next version
709 '3.0.0' => array(
710 // Add the following columns
711 'add_columns' => array(
712 FORUMS_TABLE => array(
713 'display_subforum_list' => array('BOOL', 1),
714 ),
715 SESSIONS_TABLE => array(
716 'session_forum_id' => array('UINT', 0),
717 ),
718 ),
719 'add_index' => array(
720 SESSIONS_TABLE => array(
721 'session_forum_id' => array('session_forum_id'),
722 ),
723 GROUPS_TABLE => array(
724 'group_legend_name' => array('group_legend', 'group_name'),
725 ),
726 ),
727 'drop_keys' => array(
728 GROUPS_TABLE => array('group_legend'),
729 ),
730 ),
731 // No changes from 3.0.1-RC1 to 3.0.1
732 '3.0.1-RC1' => array(),
733 // No changes from 3.0.1 to 3.0.2-RC1
734 '3.0.1' => array(),
735 // Changes from 3.0.2-RC1 to 3.0.2-RC2
736 '3.0.2-RC1' => array(
737 'change_columns' => array(
738 DRAFTS_TABLE => array(
739 'draft_subject' => array('STEXT_UNI', ''),
740 ),
741 FORUMS_TABLE => array(
742 'forum_last_post_subject' => array('STEXT_UNI', ''),
743 ),
744 POSTS_TABLE => array(
745 'post_subject' => array('STEXT_UNI', '', 'true_sort'),
746 ),
747 PRIVMSGS_TABLE => array(
748 'message_subject' => array('STEXT_UNI', ''),
749 ),
750 TOPICS_TABLE => array(
751 'topic_title' => array('STEXT_UNI', '', 'true_sort'),
752 'topic_last_post_subject' => array('STEXT_UNI', ''),
753 ),
754 ),
755 'drop_keys' => array(
756 SESSIONS_TABLE => array('session_forum_id'),
757 ),
758 'add_index' => array(
759 SESSIONS_TABLE => array(
760 'session_fid' => array('session_forum_id'),
761 ),
762 ),
763 ),
764 // No changes from 3.0.2-RC2 to 3.0.2
765 '3.0.2-RC2' => array(),
766
767 // Changes from 3.0.2 to 3.0.3-RC1
768 '3.0.2' => array(
769 // Add the following columns
770 'add_columns' => array(
771 STYLES_TEMPLATE_TABLE => array(
772 'template_inherits_id' => array('UINT:4', 0),
773 'template_inherit_path' => array('VCHAR', ''),
774 ),
775 GROUPS_TABLE => array(
776 'group_max_recipients' => array('UINT', 0),
777 ),
778 ),
779 ),
780
781 // No changes from 3.0.3-RC1 to 3.0.3
782 '3.0.3-RC1' => array(),
783
784 // Changes from 3.0.3 to 3.0.4-RC1
785 '3.0.3' => array(
786 'add_columns' => array(
787 PROFILE_FIELDS_TABLE => array(
788 'field_show_profile' => array('BOOL', 0),
789 ),
790 ),
791 'change_columns' => array(
792 STYLES_TABLE => array(
793 'style_id' => array('UINT', NULL, 'auto_increment'),
794 'template_id' => array('UINT', 0),
795 'theme_id' => array('UINT', 0),
796 'imageset_id' => array('UINT', 0),
797 ),
798 STYLES_IMAGESET_TABLE => array(
799 'imageset_id' => array('UINT', NULL, 'auto_increment'),
800 ),
801 STYLES_IMAGESET_DATA_TABLE => array(
802 'image_id' => array('UINT', NULL, 'auto_increment'),
803 'imageset_id' => array('UINT', 0),
804 ),
805 STYLES_THEME_TABLE => array(
806 'theme_id' => array('UINT', NULL, 'auto_increment'),
807 ),
808 STYLES_TEMPLATE_TABLE => array(
809 'template_id' => array('UINT', NULL, 'auto_increment'),
810 ),
811 STYLES_TEMPLATE_DATA_TABLE => array(
812 'template_id' => array('UINT', 0),
813 ),
814 FORUMS_TABLE => array(
815 'forum_style' => array('UINT', 0),
816 ),
817 USERS_TABLE => array(
818 'user_style' => array('UINT', 0),
819 ),
820 ),
821 ),
822
823 // Changes from 3.0.4-RC1 to 3.0.4
824 '3.0.4-RC1' => array(),
825
826 // Changes from 3.0.4 to 3.0.5-RC1
827 '3.0.4' => array(
828 'change_columns' => array(
829 FORUMS_TABLE => array(
830 'forum_style' => array('UINT', 0),
831 ),
832 ),
833 ),
834
835 // No changes from 3.0.5-RC1 to 3.0.5
836 '3.0.5-RC1' => array(),
837
838 // Changes from 3.0.5 to 3.0.6-RC1
839 '3.0.5' => array(
840 'add_columns' => array(
841 CONFIRM_TABLE => array(
842 'attempts' => array('UINT', 0),
843 ),
844 USERS_TABLE => array(
845 'user_new' => array('BOOL', 1),
846 'user_reminded' => array('TINT:4', 0),
847 'user_reminded_time'=> array('TIMESTAMP', 0),
848 ),
849 GROUPS_TABLE => array(
850 'group_skip_auth' => array('BOOL', 0, 'after' => 'group_founder_manage'),
851 ),
852 PRIVMSGS_TABLE => array(
853 'message_reported' => array('BOOL', 0),
854 ),
855 REPORTS_TABLE => array(
856 'pm_id' => array('UINT', 0),
857 ),
858 PROFILE_FIELDS_TABLE => array(
859 'field_show_on_vt' => array('BOOL', 0),
860 ),
861 FORUMS_TABLE => array(
862 'forum_options' => array('UINT:20', 0),
863 ),
864 ),
865 'change_columns' => array(
866 USERS_TABLE => array(
867 'user_options' => array('UINT:11', 230271),
868 ),
869 ),
870 'add_index' => array(
871 REPORTS_TABLE => array(
872 'post_id' => array('post_id'),
873 'pm_id' => array('pm_id'),
874 ),
875 POSTS_TABLE => array(
876 'post_username' => array('post_username'),
877 ),
878 ),
879 ),
880 // Changes from 3.0.6-RC1 to 3.0.6-RC2
881 '3.0.6-RC1' => array(
882 'drop_keys' => array(
883 LOG_TABLE => array('log_time'),
884 ),
885 ),
886 // No changes from 3.0.6-RC2 to 3.0.6-RC3
887 '3.0.6-RC2' => array(),
888 );
889 }
890
891 /****************************************************************************
892 * ADD YOUR DATABASE DATA CHANGES HERE *
893 * REMEMBER: You NEED to enter a schema array above and a data array here, *
894 * even if both or one of them are empty. *
895 *****************************************************************************/
896 function change_database_data(&$no_updates, $version)
897 {
898 global $db, $errored, $error_ary, $config, $phpbb_root_path, $phpEx;
899
900 switch ($version)
901 {
902 case '3.0.0':
903
904 $sql = 'UPDATE ' . TOPICS_TABLE . "
905 SET topic_last_view_time = topic_last_post_time
906 WHERE topic_last_view_time = 0";
907 _sql($sql, $errored, $error_ary);
908
909 // Update smiley sizes
910 $smileys = array('icon_e_surprised.gif', 'icon_eek.gif', 'icon_cool.gif', 'icon_lol.gif', 'icon_mad.gif', 'icon_razz.gif', 'icon_redface.gif', 'icon_cry.gif', 'icon_evil.gif', 'icon_twisted.gif', 'icon_rolleyes.gif', 'icon_exclaim.gif', 'icon_question.gif', 'icon_idea.gif', 'icon_arrow.gif', 'icon_neutral.gif', 'icon_mrgreen.gif', 'icon_e_ugeek.gif');
911
912 foreach ($smileys as $smiley)
913 {
914 if (file_exists($phpbb_root_path . 'images/smilies/' . $smiley))
915 {
916 list($width, $height) = getimagesize($phpbb_root_path . 'images/smilies/' . $smiley);
917
918 $sql = 'UPDATE ' . SMILIES_TABLE . '
919 SET smiley_width = ' . $width . ', smiley_height = ' . $height . "
920 WHERE smiley_url = '" . $db->sql_escape($smiley) . "'";
921
922 _sql($sql, $errored, $error_ary);
923 }
924 }
925
926 $no_updates = false;
927 break;
928
929 // No changes from 3.0.1-RC1 to 3.0.1
930 case '3.0.1-RC1':
931 break;
932
933 // changes from 3.0.1 to 3.0.2-RC1
934 case '3.0.1':
935
936 set_config('referer_validation', '1');
937 set_config('check_attachment_content', '1');
938 set_config('mime_triggers', 'body|head|html|img|plaintext|a href|pre|script|table|title');
939
940 $no_updates = false;
941 break;
942
943 // No changes from 3.0.2-RC1 to 3.0.2-RC2
944 case '3.0.2-RC1':
945 break;
946
947 // No changes from 3.0.2-RC2 to 3.0.2
948 case '3.0.2-RC2':
949 break;
950
951 // Changes from 3.0.2 to 3.0.3-RC1
952 case '3.0.2':
953 set_config('enable_queue_trigger', '0');
954 set_config('queue_trigger_posts', '3');
955
956 set_config('pm_max_recipients', '0');
957
958 // Set maximum number of recipients for the registered users, bots, guests group
959 $sql = 'UPDATE ' . GROUPS_TABLE . ' SET group_max_recipients = 5
960 WHERE ' . $db->sql_in_set('group_name', array('GUESTS', 'REGISTERED', 'REGISTERED_COPPA', 'BOTS'));
961 _sql($sql, $errored, $error_ary);
962
963 // Not prefilling yet
964 set_config('dbms_version', '');
965
966 // Add new permission u_masspm_group and duplicate settings from u_masspm
967 include_once($phpbb_root_path . 'includes/acp/auth.' . $phpEx);
968 $auth_admin = new auth_admin();
969
970 // Only add the new permission if it does not already exist
971 if (empty($auth_admin->acl_options['id']['u_masspm_group']))
972 {
973 $auth_admin->acl_add_option(array('global' => array('u_masspm_group')));
974
975 // Now the tricky part, filling the permission
976 $old_id = $auth_admin->acl_options['id']['u_masspm'];
977 $new_id = $auth_admin->acl_options['id']['u_masspm_group'];
978
979 $tables = array(ACL_GROUPS_TABLE, ACL_ROLES_DATA_TABLE, ACL_USERS_TABLE);
980
981 foreach ($tables as $table)
982 {
983 $sql = 'SELECT *
984 FROM ' . $table . '
985 WHERE auth_option_id = ' . $old_id;
986 $result = _sql($sql, $errored, $error_ary);
987
988 $sql_ary = array();
989 while ($row = $db->sql_fetchrow($result))
990 {
991 $row['auth_option_id'] = $new_id;
992 $sql_ary[] = $row;
993 }
994 $db->sql_freeresult($result);
995
996 if (sizeof($sql_ary))
997 {
998 $db->sql_multi_insert($table, $sql_ary);
999 }
1000 }
1001
1002 // Remove any old permission entries
1003 $auth_admin->acl_clear_prefetch();
1004 }
1005
1006 /**
1007 * Do not resync post counts here. An admin may do this later from the ACP
1008 $start = 0;
1009 $step = ($config['num_posts']) ? (max((int) ($config['num_posts'] / 5), 20000)) : 20000;
1010
1011 $sql = 'UPDATE ' . USERS_TABLE . ' SET user_posts = 0';
1012 _sql($sql, $errored, $error_ary);
1013
1014 do
1015 {
1016 $sql = 'SELECT COUNT(post_id) AS num_posts, poster_id
1017 FROM ' . POSTS_TABLE . '
1018 WHERE post_id BETWEEN ' . ($start + 1) . ' AND ' . ($start + $step) . '
1019 AND post_postcount = 1 AND post_approved = 1
1020 GROUP BY poster_id';
1021 $result = _sql($sql, $errored, $error_ary);
1022
1023 if ($row = $db->sql_fetchrow($result))
1024 {
1025 do
1026 {
1027 $sql = 'UPDATE ' . USERS_TABLE . " SET user_posts = user_posts + {$row['num_posts']} WHERE user_id = {$row['poster_id']}";
1028 _sql($sql, $errored, $error_ary);
1029 }
1030 while ($row = $db->sql_fetchrow($result));
1031
1032 $start += $step;
1033 }
1034 else
1035 {
1036 $start = 0;
1037 }
1038 $db->sql_freeresult($result);
1039 }
1040 while ($start);
1041 */
1042
1043 $sql = 'UPDATE ' . MODULES_TABLE . '
1044 SET module_auth = \'acl_a_email && cfg_email_enable\'
1045 WHERE module_class = \'acp\'
1046 AND module_basename = \'email\'';
1047 _sql($sql, $errored, $error_ary);
1048
1049 $no_updates = false;
1050 break;
1051
1052 // Changes from 3.0.3-RC1 to 3.0.3
1053 case '3.0.3-RC1':
1054 $sql = 'UPDATE ' . LOG_TABLE . "
1055 SET log_operation = 'LOG_DELETE_TOPIC'
1056 WHERE log_operation = 'LOG_TOPIC_DELETED'";
1057 _sql($sql, $errored, $error_ary);
1058
1059 $no_updates = false;
1060 break;
1061
1062 // Changes from 3.0.3 to 3.0.4-RC1
1063 case '3.0.3':
1064 // Update the Custom Profile Fields based on previous settings to the new format
1065 $sql = 'SELECT field_id, field_required, field_show_on_reg, field_hide
1066 FROM ' . PROFILE_FIELDS_TABLE;
1067 $result = _sql($sql, $errored, $error_ary);
1068
1069 while ($row = $db->sql_fetchrow($result))
1070 {
1071 $sql_ary = array(
1072 'field_required' => 0,
1073 'field_show_on_reg' => 0,
1074 'field_hide' => 0,
1075 'field_show_profile'=> 0,
1076 );
1077
1078 if ($row['field_required'])
1079 {
1080 $sql_ary['field_required'] = $sql_ary['field_show_on_reg'] = $sql_ary['field_show_profile'] = 1;
1081 }
1082 else if ($row['field_show_on_reg'])
1083 {
1084 $sql_ary['field_show_on_reg'] = $sql_ary['field_show_profile'] = 1;
1085 }
1086 else if ($row['field_hide'])
1087 {
1088 // Only administrators and moderators can see this CPF, if the view is enabled, they can see it, otherwise just admins in the acp_users module
1089 $sql_ary['field_hide'] = 1;
1090 }
1091 else
1092 {
1093 // equivelant to "none", which is the "Display in user control panel" option
1094 $sql_ary['field_show_profile'] = 1;
1095 }
1096
1097 _sql('UPDATE ' . PROFILE_FIELDS_TABLE . ' SET ' . $db->sql_build_array('UPDATE', $sql_ary) . ' WHERE field_id = ' . $row['field_id'], $errored, $error_ary);
1098 }
1099 $no_updates = false;
1100
1101 break;
1102
1103 // Changes from 3.0.4-RC1 to 3.0.4
1104 case '3.0.4-RC1':
1105 break;
1106
1107 // Changes from 3.0.4 to 3.0.5-RC1
1108 case '3.0.4':
1109
1110 // Captcha config variables
1111 set_config('captcha_gd_wave', 0);
1112 set_config('captcha_gd_3d_noise', 1);
1113 set_config('captcha_gd_fonts', 1);
1114 set_config('confirm_refresh', 1);
1115
1116 // Maximum number of keywords
1117 set_config('max_num_search_keywords', 10);
1118
1119 // Remove static config var and put it back as dynamic variable
1120 $sql = 'UPDATE ' . CONFIG_TABLE . "
1121 SET is_dynamic = 1
1122 WHERE config_name = 'search_indexing_state'";
1123 _sql($sql, $errored, $error_ary);
1124
1125 // Hash old MD5 passwords
1126 $sql = 'SELECT user_id, user_password
1127 FROM ' . USERS_TABLE . '
1128 WHERE user_pass_convert = 1';
1129 $result = _sql($sql, $errored, $error_ary);
1130
1131 while ($row = $db->sql_fetchrow($result))
1132 {
1133 if (strlen($row['user_password']) == 32)
1134 {
1135 $sql_ary = array(
1136 'user_password' => phpbb_hash($row['user_password']),
1137 );
1138
1139 _sql('UPDATE ' . USERS_TABLE . ' SET ' . $db->sql_build_array('UPDATE', $sql_ary) . ' WHERE user_id = ' . $row['user_id'], $errored, $error_ary);
1140 }
1141 }
1142 $db->sql_freeresult($result);
1143
1144 // Adjust bot entry
1145 $sql = 'UPDATE ' . BOTS_TABLE . "
1146 SET bot_agent = 'ichiro/'
1147 WHERE bot_agent = 'ichiro/2'";
1148 _sql($sql, $errored, $error_ary);
1149
1150
1151 // Before we are able to add a unique key to auth_option, we need to remove duplicate entries
1152
1153 // We get duplicate entries first
1154 $sql = 'SELECT auth_option
1155 FROM ' . ACL_OPTIONS_TABLE . '
1156 GROUP BY auth_option
1157 HAVING COUNT(*) >= 2';
1158 $result = $db->sql_query($sql);
1159
1160 $auth_options = array();
1161 while ($row = $db->sql_fetchrow($result))
1162 {
1163 $auth_options[] = $row['auth_option'];
1164 }
1165 $db->sql_freeresult($result);
1166
1167 // Remove specific auth options
1168 if (!empty($auth_options))
1169 {
1170 foreach ($auth_options as $option)
1171 {
1172 // Select auth_option_ids... the largest id will be preserved
1173 $sql = 'SELECT auth_option_id
1174 FROM ' . ACL_OPTIONS_TABLE . "
1175 WHERE auth_option = '" . $db->sql_escape($option) . "'
1176 ORDER BY auth_option_id DESC";
1177 // sql_query_limit not possible here, due to bug in postgresql layer
1178 $result = $db->sql_query($sql);
1179
1180 // Skip first row, this is our original auth option we want to preserve
1181 $row = $db->sql_fetchrow($result);
1182
1183 while ($row = $db->sql_fetchrow($result))
1184 {
1185 // Ok, remove this auth option...
1186 _sql('DELETE FROM ' . ACL_OPTIONS_TABLE . ' WHERE auth_option_id = ' . $row['auth_option_id'], $errored, $error_ary);
1187 _sql('DELETE FROM ' . ACL_ROLES_DATA_TABLE . ' WHERE auth_option_id = ' . $row['auth_option_id'], $errored, $error_ary);
1188 _sql('DELETE FROM ' . ACL_GROUPS_TABLE . ' WHERE auth_option_id = ' . $row['auth_option_id'], $errored, $error_ary);
1189 _sql('DELETE FROM ' . ACL_USERS_TABLE . ' WHERE auth_option_id = ' . $row['auth_option_id'], $errored, $error_ary);
1190 }
1191 $db->sql_freeresult($result);
1192 }
1193 }
1194
1195 // Now make auth_option UNIQUE, by dropping the old index and adding a UNIQUE one.
1196 $changes = array(
1197 'drop_keys' => array(
1198 ACL_OPTIONS_TABLE => array('auth_option'),
1199 ),
1200 'add_unique_index' => array(
1201 ACL_OPTIONS_TABLE => array(
1202 'auth_option' => array('auth_option'),
1203 ),
1204 ),
1205 );
1206
1207 global $db_tools;
1208
1209 $statements = $db_tools->perform_schema_changes($changes);
1210
1211 foreach ($statements as $sql)
1212 {
1213 _sql($sql, $errored, $error_ary);
1214 }
1215
1216 $no_updates = false;
1217
1218 break;
1219
1220 // No changes from 3.0.5-RC1 to 3.0.5
1221 case '3.0.5-RC1':
1222 break;
1223
1224 // Changes from 3.0.5 to 3.0.6-RC1
1225 case '3.0.5':
1226 // Let's see if the GD Captcha can be enabled... we simply look for what *is* enabled...
1227 if (!empty($config['captcha_gd']) && !isset($config['captcha_plugin']))
1228 {
1229 set_config('captcha_plugin', 'phpbb_captcha_gd');
1230 }
1231 else if (!isset($config['captcha_plugin']))
1232 {
1233 set_config('captcha_plugin', 'phpbb_captcha_nogd');
1234 }
1235
1236 // Entries for the Feed Feature
1237 set_config('feed_enable', '0');
1238 set_config('feed_limit', '10');
1239
1240 set_config('feed_overall_forums', '1');
1241 set_config('feed_overall_forums_limit', '15');
1242
1243 set_config('feed_overall_topics', '0');
1244 set_config('feed_overall_topics_limit', '15');
1245
1246 set_config('feed_forum', '1');
1247 set_config('feed_topic', '1');
1248 set_config('feed_item_statistics', '1');
1249
1250 // Entries for smiley pagination
1251 set_config('smilies_per_page', '50');
1252
1253 // Entry for reporting PMs
1254 set_config('allow_pm_report', '1');
1255
1256 // Install modules
1257 $modules_to_install = array(
1258 'feed' => array(
1259 'base' => 'board',
1260 'class' => 'acp',
1261 'title' => 'ACP_FEED_SETTINGS',
1262 'auth' => 'acl_a_board',
1263 'cat' => 'ACP_BOARD_CONFIGURATION',
1264 'after' => array('signature', 'ACP_SIGNATURE_SETTINGS')
1265 ),
1266 'warnings' => array(
1267 'base' => 'users',
1268 'class' => 'acp',
1269 'title' => 'ACP_USER_WARNINGS',
1270 'auth' => 'acl_a_user',
1271 'display' => 0,
1272 'cat' => 'ACP_CAT_USERS',
1273 'after' => array('feedback', 'ACP_USER_FEEDBACK')
1274 ),
1275 'send_statistics' => array(
1276 'base' => 'send_statistics',
1277 'class' => 'acp',
1278 'title' => 'ACP_SEND_STATISTICS',
1279 'auth' => 'acl_a_server',
1280 'cat' => 'ACP_SERVER_CONFIGURATION'
1281 ),
1282 'setting_forum_copy' => array(
1283 'base' => 'permissions',
1284 'class' => 'acp',
1285 'title' => 'ACP_FORUM_PERMISSIONS_COPY',
1286 'auth' => 'acl_a_fauth && acl_a_authusers && acl_a_authgroups && acl_a_mauth',
1287 'cat' => 'ACP_FORUM_BASED_PERMISSIONS',
1288 'after' => array('setting_forum_local', 'ACP_FORUM_PERMISSIONS')
1289 ),
1290 'pm_reports' => array(
1291 'base' => 'pm_reports',
1292 'class' => 'mcp',
1293 'title' => 'MCP_PM_REPORTS_OPEN',
1294 'auth' => 'aclf_m_report',
1295 'cat' => 'MCP_REPORTS'
1296 ),
1297 'pm_reports_closed' => array(
1298 'base' => 'pm_reports',
1299 'class' => 'mcp',
1300 'title' => 'MCP_PM_REPORTS_CLOSED',
1301 'auth' => 'aclf_m_report',
1302 'cat' => 'MCP_REPORTS'
1303 ),
1304 'pm_report_details' => array(
1305 'base' => 'pm_reports',
1306 'class' => 'mcp',
1307 'title' => 'MCP_PM_REPORT_DETAILS',
1308 'auth' => 'aclf_m_report',
1309 'cat' => 'MCP_REPORTS'
1310 ),
1311 );
1312
1313 _add_modules($modules_to_install);
1314
1315 // Add newly_registered group... but check if it already exists (we always supported running the updater on any schema)
1316 $sql = 'SELECT group_id
1317 FROM ' . GROUPS_TABLE . "
1318 WHERE group_name = 'NEWLY_REGISTERED'";
1319 $result = $db->sql_query($sql);
1320 $group_id = (int) $db->sql_fetchfield('group_id');
1321 $db->sql_freeresult($result);
1322
1323 if (!$group_id)
1324 {
1325 $sql = 'INSERT INTO ' . GROUPS_TABLE . " (group_name, group_type, group_founder_manage, group_colour, group_legend, group_avatar, group_desc, group_desc_uid, group_max_recipients) VALUES ('NEWLY_REGISTERED', 3, 0, '', 0, '', '', '', 5)";
1326 _sql($sql, $errored, $error_ary);
1327
1328 $group_id = $db->sql_nextid();
1329 }
1330
1331 // Insert new user role... at the end of the chain
1332 $sql = 'SELECT role_id
1333 FROM ' . ACL_ROLES_TABLE . "
1334 WHERE role_name = 'ROLE_USER_NEW_MEMBER'
1335 AND role_type = 'u_'";
1336 $result = $db->sql_query($sql);
1337 $u_role = (int) $db->sql_fetchfield('role_id');
1338 $db->sql_freeresult($result);
1339
1340 if (!$u_role)
1341 {
1342 $sql = 'SELECT MAX(role_order) as max_order_id
1343 FROM ' . ACL_ROLES_TABLE . "
1344 WHERE role_type = 'u_'";
1345 $result = $db->sql_query($sql);
1346 $next_order_id = (int) $db->sql_fetchfield('max_order_id');
1347 $db->sql_freeresult($result);
1348
1349 $next_order_id++;
1350
1351 $sql = 'INSERT INTO ' . ACL_ROLES_TABLE . " (role_name, role_description, role_type, role_order) VALUES ('ROLE_USER_NEW_MEMBER', 'ROLE_DESCRIPTION_USER_NEW_MEMBER', 'u_', $next_order_id)";
1352 _sql($sql, $errored, $error_ary);
1353 $u_role = $db->sql_nextid();
1354
1355 if (!$errored)
1356 {
1357 // Now add the correct data to the roles...
1358 // The standard role says that new users are not able to send a PM, Mass PM, are not able to PM groups
1359 $sql = 'INSERT INTO ' . ACL_ROLES_DATA_TABLE . " (role_id, auth_option_id, auth_setting) SELECT $u_role, auth_option_id, 0 FROM " . ACL_OPTIONS_TABLE . " WHERE auth_option LIKE 'u_%' AND auth_option IN ('u_sendpm', 'u_masspm', 'u_masspm_group')";
1360 _sql($sql, $errored, $error_ary);
1361
1362 // Add user role to group
1363 $sql = 'INSERT INTO ' . ACL_GROUPS_TABLE . " (group_id, forum_id, auth_option_id, auth_role_id, auth_setting) VALUES ($group_id, 0, 0, $u_role, 0)";
1364 _sql($sql, $errored, $error_ary);
1365 }
1366 }
1367
1368 // Insert new forum role
1369 $sql = 'SELECT role_id
1370 FROM ' . ACL_ROLES_TABLE . "
1371 WHERE role_name = 'ROLE_FORUM_NEW_MEMBER'
1372 AND role_type = 'f_'";
1373 $result = $db->sql_query($sql);
1374 $f_role = (int) $db->sql_fetchfield('role_id');
1375 $db->sql_freeresult($result);
1376
1377 if (!$f_role)
1378 {
1379 $sql = 'SELECT MAX(role_order) as max_order_id
1380 FROM ' . ACL_ROLES_TABLE . "
1381 WHERE role_type = 'f_'";
1382 $result = $db->sql_query($sql);
1383 $next_order_id = (int) $db->sql_fetchfield('max_order_id');
1384 $db->sql_freeresult($result);
1385
1386 $next_order_id++;
1387
1388 $sql = 'INSERT INTO ' . ACL_ROLES_TABLE . " (role_name, role_description, role_type, role_order) VALUES ('ROLE_FORUM_NEW_MEMBER', 'ROLE_DESCRIPTION_FORUM_NEW_MEMBER', 'f_', $next_order_id)";
1389 _sql($sql, $errored, $error_ary);
1390 $f_role = $db->sql_nextid();
1391
1392 if (!$errored)
1393 {
1394 $sql = 'INSERT INTO ' . ACL_ROLES_DATA_TABLE . " (role_id, auth_option_id, auth_setting) SELECT $f_role, auth_option_id, 0 FROM " . ACL_OPTIONS_TABLE . " WHERE auth_option LIKE 'f_%' AND auth_option IN ('f_noapprove')";
1395 _sql($sql, $errored, $error_ary);
1396 }
1397 }
1398
1399 // Set every members user_new column to 0 (old users) only if there is no one yet (this makes sure we do not execute this more than once)
1400 $sql = 'SELECT 1
1401 FROM ' . USERS_TABLE . '
1402 WHERE user_new = 0';
1403 $result = $db->sql_query_limit($sql, 1);
1404 $row = $db->sql_fetchrow($result);
1405 $db->sql_freeresult($result);
1406
1407 if (!$row)
1408 {
1409 $sql = 'UPDATE ' . USERS_TABLE . ' SET user_new = 0';
1410 _sql($sql, $errored, $error_ary);
1411 }
1412
1413 // Newly registered users limit
1414 if (!isset($config['new_member_post_limit']))
1415 {
1416 set_config('new_member_post_limit', (!empty($config['enable_queue_trigger'])) ? $config['queue_trigger_posts'] : 0);
1417 }
1418
1419 if (!isset($config['new_member_group_default']))
1420 {
1421 set_config('new_member_group_default', 0);
1422 }
1423
1424 // To mimick the old "feature" we will assign the forum role to every forum, regardless of the setting (this makes sure there are no "this does not work!!!! YUO!!!" posts...
1425 // Check if the role is already assigned...
1426 $sql = 'SELECT forum_id
1427 FROM ' . ACL_GROUPS_TABLE . '
1428 WHERE group_id = ' . $group_id . '
1429 AND auth_role_id = ' . $f_role;
1430 $result = $db->sql_query($sql);
1431 $is_options = (int) $db->sql_fetchfield('forum_id');
1432 $db->sql_freeresult($result);
1433
1434 // Not assigned at all... :/
1435 if (!$is_options)
1436 {
1437 // Get postable forums
1438 $sql = 'SELECT forum_id
1439 FROM ' . FORUMS_TABLE . '
1440 WHERE forum_type != ' . FORUM_LINK;
1441 $result = $db->sql_query($sql);
1442
1443 while ($row = $db->sql_fetchrow($result))
1444 {
1445 _sql('INSERT INTO ' . ACL_GROUPS_TABLE . ' (group_id, forum_id, auth_option_id, auth_role_id, auth_setting) VALUES (' . $group_id . ', ' . (int) $row['forum_id'] . ', 0, ' . $f_role . ', 0)', $errored, $error_ary);
1446 }
1447 $db->sql_freeresult($result);
1448 }
1449
1450 // Clear permissions...
1451 include_once($phpbb_root_path . 'includes/acp/auth.' . $phpEx);
1452 $auth_admin = new auth_admin();
1453 $auth_admin->acl_clear_prefetch();
1454
1455 if (!isset($config['allow_avatar']))
1456 {
1457 if ($config['allow_avatar_upload'] || $config['allow_avatar_local'] || $config['allow_avatar_remote'])
1458 {
1459 set_config('allow_avatar', '1');
1460 }
1461 else
1462 {
1463 set_config('allow_avatar', '0');
1464 }
1465 }
1466
1467 if (!isset($config['allow_avatar_remote_upload']))
1468 {
1469 if ($config['allow_avatar_remote'] && $config['allow_avatar_upload'])
1470 {
1471 set_config('allow_avatar_remote_upload', '1');
1472 }
1473 else
1474 {
1475 set_config('allow_avatar_remote_upload', '0');
1476 }
1477 }
1478
1479 // Minimum number of characters
1480 if (!isset($config['min_post_chars']))
1481 {
1482 set_config('min_post_chars', '1');
1483 }
1484
1485 if (!isset($config['allow_quick_reply']))
1486 {
1487 set_config('allow_quick_reply', '1');
1488 }
1489
1490 // Set every members user_options column to enable
1491 // bbcode, smilies and URLs for signatures by default
1492 $sql = 'SELECT user_options
1493 FROM ' . USERS_TABLE . '
1494 WHERE user_type IN (' . USER_NORMAL . ', ' . USER_FOUNDER . ')';
1495 $result = $db->sql_query_limit($sql, 1);
1496 $user_option = (int) $db->sql_fetchfield('user_options');
1497 $db->sql_freeresult($result);
1498
1499 // Check if we already updated the database by checking bit 15 which we used to store the sig_bbcode option
1500 if (!($user_option & 1 << 15))
1501 {
1502 // 229376 is the added value to enable all three signature options
1503 $sql = 'UPDATE ' . USERS_TABLE . ' SET user_options = user_options + 229376';
1504 _sql($sql, $errored, $error_ary);
1505 }
1506
1507 if (!isset($config['delete_time']))
1508 {
1509 set_config('delete_time', $config['edit_time']);
1510 }
1511
1512 $no_updates = false;
1513 break;
1514
1515 // No changes from 3.0.6-RC1 to 3.0.6-RC2
1516 case '3.0.6-RC1':
1517 break;
1518
1519 // Changes from 3.0.6-RC2 to 3.0.6-RC3
1520 case '3.0.6-RC2':
1521
1522 // Update the Custom Profile Fields based on previous settings to the new format
1523 $sql = 'UPDATE ' . PROFILE_FIELDS_TABLE . '
1524 SET field_show_on_vt = 1
1525 WHERE field_hide = 0
1526 AND (field_required = 1 OR field_show_on_reg = 1 OR field_show_profile = 1)';
1527 _sql($sql, $errored, $error_ary);
1528 $no_updates = false;
1529
1530 break;
1531 }
1532 }
1533
1534
1535 /**
1536 * Database Tools for handling cross-db actions such as altering columns, etc.
1537 * Currently not supported is returning SQL for creating tables.
1538 *
1539 * @package dbal
1540 */
1541 class updater_db_tools
1542 {
1543 /**
1544 * Current sql layer
1545 */
1546 var $sql_layer = '';
1547
1548 /**
1549 * @var object DB object
1550 */
1551 var $db = NULL;
1552
1553 /**
1554 * The Column types for every database we support
1555 * @var array
1556 */
1557 var $dbms_type_map = array(
1558 'mysql_41' => array(
1559 'INT:' => 'int(%d)',
1560 'BINT' => 'bigint(20)',
1561 'UINT' => 'mediumint(8) UNSIGNED',
1562 'UINT:' => 'int(%d) UNSIGNED',
1563 'TINT:' => 'tinyint(%d)',
1564 'USINT' => 'smallint(4) UNSIGNED',
1565 'BOOL' => 'tinyint(1) UNSIGNED',
1566 'VCHAR' => 'varchar(255)',
1567 'VCHAR:' => 'varchar(%d)',
1568 'CHAR:' => 'char(%d)',
1569 'XSTEXT' => 'text',
1570 'XSTEXT_UNI'=> 'varchar(100)',
1571 'STEXT' => 'text',
1572 'STEXT_UNI' => 'varchar(255)',
1573 'TEXT' => 'text',
1574 'TEXT_UNI' => 'text',
1575 'MTEXT' => 'mediumtext',
1576 'MTEXT_UNI' => 'mediumtext',
1577 'TIMESTAMP' => 'int(11) UNSIGNED',
1578 'DECIMAL' => 'decimal(5,2)',
1579 'DECIMAL:' => 'decimal(%d,2)',
1580 'PDECIMAL' => 'decimal(6,3)',
1581 'PDECIMAL:' => 'decimal(%d,3)',
1582 'VCHAR_UNI' => 'varchar(255)',
1583 'VCHAR_UNI:'=> 'varchar(%d)',
1584 'VCHAR_CI' => 'varchar(255)',
1585 'VARBINARY' => 'varbinary(255)',
1586 ),
1587
1588 'mysql_40' => array(
1589 'INT:' => 'int(%d)',
1590 'BINT' => 'bigint(20)',
1591 'UINT' => 'mediumint(8) UNSIGNED',
1592 'UINT:' => 'int(%d) UNSIGNED',
1593 'TINT:' => 'tinyint(%d)',
1594 'USINT' => 'smallint(4) UNSIGNED',
1595 'BOOL' => 'tinyint(1) UNSIGNED',
1596 'VCHAR' => 'varbinary(255)',
1597 'VCHAR:' => 'varbinary(%d)',
1598 'CHAR:' => 'binary(%d)',
1599 'XSTEXT' => 'blob',
1600 'XSTEXT_UNI'=> 'blob',
1601 'STEXT' => 'blob',
1602 'STEXT_UNI' => 'blob',
1603 'TEXT' => 'blob',
1604 'TEXT_UNI' => 'blob',
1605 'MTEXT' => 'mediumblob',
1606 'MTEXT_UNI' => 'mediumblob',
1607 'TIMESTAMP' => 'int(11) UNSIGNED',
1608 'DECIMAL' => 'decimal(5,2)',
1609 'DECIMAL:' => 'decimal(%d,2)',
1610 'PDECIMAL' => 'decimal(6,3)',
1611 'PDECIMAL:' => 'decimal(%d,3)',
1612 'VCHAR_UNI' => 'blob',
1613 'VCHAR_UNI:'=> array('varbinary(%d)', 'limit' => array('mult', 3, 255, 'blob')),
1614 'VCHAR_CI' => 'blob',
1615 'VARBINARY' => 'varbinary(255)',
1616 ),
1617
1618 'firebird' => array(
1619 'INT:' => 'INTEGER',
1620 'BINT' => 'DOUBLE PRECISION',
1621 'UINT' => 'INTEGER',
1622 'UINT:' => 'INTEGER',
1623 'TINT:' => 'INTEGER',
1624 'USINT' => 'INTEGER',
1625 'BOOL' => 'INTEGER',
1626 'VCHAR' => 'VARCHAR(255) CHARACTER SET NONE',
1627 'VCHAR:' => 'VARCHAR(%d) CHARACTER SET NONE',
1628 'CHAR:' => 'CHAR(%d) CHARACTER SET NONE',
1629 'XSTEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
1630 'STEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
1631 'TEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
1632 'MTEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
1633 'XSTEXT_UNI'=> 'VARCHAR(100) CHARACTER SET UTF8',
1634 'STEXT_UNI' => 'VARCHAR(255) CHARACTER SET UTF8',
1635 'TEXT_UNI' => 'BLOB SUB_TYPE TEXT CHARACTER SET UTF8',
1636 'MTEXT_UNI' => 'BLOB SUB_TYPE TEXT CHARACTER SET UTF8',
1637 'TIMESTAMP' => 'INTEGER',
1638 'DECIMAL' => 'DOUBLE PRECISION',
1639 'DECIMAL:' => 'DOUBLE PRECISION',
1640 'PDECIMAL' => 'DOUBLE PRECISION',
1641 'PDECIMAL:' => 'DOUBLE PRECISION',
1642 'VCHAR_UNI' => 'VARCHAR(255) CHARACTER SET UTF8',
1643 'VCHAR_UNI:'=> 'VARCHAR(%d) CHARACTER SET UTF8',
1644 'VCHAR_CI' => 'VARCHAR(255) CHARACTER SET UTF8',
1645 'VARBINARY' => 'CHAR(255) CHARACTER SET NONE',
1646 ),
1647
1648 'mssql' => array(
1649 'INT:' => '[int]',
1650 'BINT' => '[float]',
1651 'UINT' => '[int]',
1652 'UINT:' => '[int]',
1653 'TINT:' => '[int]',
1654 'USINT' => '[int]',
1655 'BOOL' => '[int]',
1656 'VCHAR' => '[varchar] (255)',
1657 'VCHAR:' => '[varchar] (%d)',
1658 'CHAR:' => '[char] (%d)',
1659 'XSTEXT' => '[varchar] (1000)',
1660 'STEXT' => '[varchar] (3000)',
1661 'TEXT' => '[varchar] (8000)',
1662 'MTEXT' => '[text]',
1663 'XSTEXT_UNI'=> '[varchar] (100)',
1664 'STEXT_UNI' => '[varchar] (255)',
1665 'TEXT_UNI' => '[varchar] (4000)',
1666 'MTEXT_UNI' => '[text]',
1667 'TIMESTAMP' => '[int]',
1668 'DECIMAL' => '[float]',
1669 'DECIMAL:' => '[float]',
1670 'PDECIMAL' => '[float]',
1671 'PDECIMAL:' => '[float]',
1672 'VCHAR_UNI' => '[varchar] (255)',
1673 'VCHAR_UNI:'=> '[varchar] (%d)',
1674 'VCHAR_CI' => '[varchar] (255)',
1675 'VARBINARY' => '[varchar] (255)',
1676 ),
1677
1678 'oracle' => array(
1679 'INT:' => 'number(%d)',
1680 'BINT' => 'number(20)',
1681 'UINT' => 'number(8)',
1682 'UINT:' => 'number(%d)',
1683 'TINT:' => 'number(%d)',
1684 'USINT' => 'number(4)',
1685 'BOOL' => 'number(1)',
1686 'VCHAR' => 'varchar2(255)',
1687 'VCHAR:' => 'varchar2(%d)',
1688 'CHAR:' => 'char(%d)',
1689 'XSTEXT' => 'varchar2(1000)',
1690 'STEXT' => 'varchar2(3000)',
1691 'TEXT' => 'clob',
1692 'MTEXT' => 'clob',
1693 'XSTEXT_UNI'=> 'varchar2(300)',
1694 'STEXT_UNI' => 'varchar2(765)',
1695 'TEXT_UNI' => 'clob',
1696 'MTEXT_UNI' => 'clob',
1697 'TIMESTAMP' => 'number(11)',
1698 'DECIMAL' => 'number(5, 2)',
1699 'DECIMAL:' => 'number(%d, 2)',
1700 'PDECIMAL' => 'number(6, 3)',
1701 'PDECIMAL:' => 'number(%d, 3)',
1702 'VCHAR_UNI' => 'varchar2(765)',
1703 'VCHAR_UNI:'=> array('varchar2(%d)', 'limit' => array('mult', 3, 765, 'clob')),
1704 'VCHAR_CI' => 'varchar2(255)',
1705 'VARBINARY' => 'raw(255)',
1706 ),
1707
1708 'sqlite' => array(
1709 'INT:' => 'int(%d)',
1710 'BINT' => 'bigint(20)',
1711 'UINT' => 'INTEGER UNSIGNED', //'mediumint(8) UNSIGNED',
1712 'UINT:' => 'INTEGER UNSIGNED', // 'int(%d) UNSIGNED',
1713 'TINT:' => 'tinyint(%d)',
1714 'USINT' => 'INTEGER UNSIGNED', //'mediumint(4) UNSIGNED',
1715 'BOOL' => 'INTEGER UNSIGNED', //'tinyint(1) UNSIGNED',
1716 'VCHAR' => 'varchar(255)',
1717 'VCHAR:' => 'varchar(%d)',
1718 'CHAR:' => 'char(%d)',
1719 'XSTEXT' => 'text(65535)',
1720 'STEXT' => 'text(65535)',
1721 'TEXT' => 'text(65535)',
1722 'MTEXT' => 'mediumtext(16777215)',
1723 'XSTEXT_UNI'=> 'text(65535)',
1724 'STEXT_UNI' => 'text(65535)',
1725 'TEXT_UNI' => 'text(65535)',
1726 'MTEXT_UNI' => 'mediumtext(16777215)',
1727 'TIMESTAMP' => 'INTEGER UNSIGNED', //'int(11) UNSIGNED',
1728 'DECIMAL' => 'decimal(5,2)',
1729 'DECIMAL:' => 'decimal(%d,2)',
1730 'PDECIMAL' => 'decimal(6,3)',
1731 'PDECIMAL:' => 'decimal(%d,3)',
1732 'VCHAR_UNI' => 'varchar(255)',
1733 'VCHAR_UNI:'=> 'varchar(%d)',
1734 'VCHAR_CI' => 'varchar(255)',
1735 'VARBINARY' => 'blob',
1736 ),
1737
1738 'postgres' => array(
1739 'INT:' => 'INT4',
1740 'BINT' => 'INT8',
1741 'UINT' => 'INT4', // unsigned
1742 'UINT:' => 'INT4', // unsigned
1743 'USINT' => 'INT2', // unsigned
1744 'BOOL' => 'INT2', // unsigned
1745 'TINT:' => 'INT2',
1746 'VCHAR' => 'varchar(255)',
1747 'VCHAR:' => 'varchar(%d)',
1748 'CHAR:' => 'char(%d)',
1749 'XSTEXT' => 'varchar(1000)',
1750 'STEXT' => 'varchar(3000)',
1751 'TEXT' => 'varchar(8000)',
1752 'MTEXT' => 'TEXT',
1753 'XSTEXT_UNI'=> 'varchar(100)',
1754 'STEXT_UNI' => 'varchar(255)',
1755 'TEXT_UNI' => 'varchar(4000)',
1756 'MTEXT_UNI' => 'TEXT',
1757 'TIMESTAMP' => 'INT4', // unsigned
1758 'DECIMAL' => 'decimal(5,2)',
1759 'DECIMAL:' => 'decimal(%d,2)',
1760 'PDECIMAL' => 'decimal(6,3)',
1761 'PDECIMAL:' => 'decimal(%d,3)',
1762 'VCHAR_UNI' => 'varchar(255)',
1763 'VCHAR_UNI:'=> 'varchar(%d)',
1764 'VCHAR_CI' => 'varchar_ci',
1765 'VARBINARY' => 'bytea',
1766 ),
1767 );
1768
1769 /**
1770 * A list of types being unsigned for better reference in some db's
1771 * @var array
1772 */
1773 var $unsigned_types = array('UINT', 'UINT:', 'USINT', 'BOOL', 'TIMESTAMP');
1774
1775 /**
1776 * A list of supported DBMS. We change this class to support more DBMS, the DBMS itself only need to follow some rules.
1777 * @var array
1778 */
1779 var $supported_dbms = array('firebird', 'mssql', 'mysql_40', 'mysql_41', 'oracle', 'postgres', 'sqlite');
1780
1781 /**
1782 * This is set to true if user only wants to return the 'to-be-executed' SQL statement(s) (as an array).
1783 * This mode has no effect on some methods (inserting of data for example). This is expressed within the methods command.
1784 */
1785 var $return_statements = false;
1786
1787 /**
1788 * Constructor. Set DB Object and set {@link $return_statements return_statements}.
1789 *
1790 * @param phpbb_dbal $db DBAL object
1791 * @param bool $return_statements True if only statements should be returned and no SQL being executed
1792 */
1793 function updater_db_tools(&$db, $return_statements = false)
1794 {
1795 $this->db = $db;
1796 $this->return_statements = $return_statements;
1797
1798 // Determine mapping database type
1799 switch ($this->db->sql_layer)
1800 {
1801 case 'mysql':
1802 $this->sql_layer = 'mysql_40';
1803 break;
1804
1805 case 'mysql4':
1806 if (version_compare($this->db->sql_server_info(true), '4.1.3', '>='))
1807 {
1808 $this->sql_layer = 'mysql_41';
1809 }
1810 else
1811 {
1812 $this->sql_layer = 'mysql_40';
1813 }
1814 break;
1815
1816 case 'mysqli':
1817 $this->sql_layer = 'mysql_41';
1818 break;
1819
1820 case 'mssql':
1821 case 'mssql_odbc':
1822 $this->sql_layer = 'mssql';
1823 break;
1824
1825 default:
1826 $this->sql_layer = $this->db->sql_layer;
1827 break;
1828 }
1829 }
1830
1831 /**
1832 * Handle passed database update array.
1833 * Expected structure...
1834 * Key being one of the following
1835 * change_columns: Column changes (only type, not name)
1836 * add_columns: Add columns to a table
1837 * drop_keys: Dropping keys
1838 * drop_columns: Removing/Dropping columns
1839 * add_primary_keys: adding primary keys
1840 * add_unique_index: adding an unique index
1841 * add_index: adding an index
1842 *
1843 * The values are in this format:
1844 * {TABLE NAME} => array(
1845 * {COLUMN NAME} => array({COLUMN TYPE}, {DEFAULT VALUE}, {OPTIONAL VARIABLES}),
1846 * {KEY/INDEX NAME} => array({COLUMN NAMES}),
1847 * )
1848 *
1849 * For more information have a look at /develop/create_schema_files.php (only available through SVN)
1850 */
1851 function perform_schema_changes($schema_changes)
1852 {
1853 if (empty($schema_changes))
1854 {
1855 return;
1856 }
1857
1858 $statements = array();
1859 $sqlite = false;
1860
1861 // For SQLite we need to perform the schema changes in a much more different way
1862 if ($this->db->sql_layer == 'sqlite' && $this->return_statements)
1863 {
1864 $sqlite_data = array();
1865 $sqlite = true;
1866 }
1867
1868 // Change columns?
1869 if (!empty($schema_changes['change_columns']))
1870 {
1871 foreach ($schema_changes['change_columns'] as $table => $columns)
1872 {
1873 foreach ($columns as $column_name => $column_data)
1874 {
1875 // If the column exists we change it, else we add it ;)
1876 if ($column_exists = $this->sql_column_exists($table, $column_name))
1877 {
1878 $result = $this->sql_column_change($table, $column_name, $column_data, true);
1879 }
1880 else
1881 {
1882 $result = $this->sql_column_add($table, $column_name, $column_data, true);
1883 }
1884
1885 if ($sqlite)
1886 {
1887 if ($column_exists)
1888 {
1889 $sqlite_data[$table]['change_columns'][] = $result;
1890 }
1891 else
1892 {
1893 $sqlite_data[$table]['add_columns'][] = $result;
1894 }
1895 }
1896 else if ($this->return_statements)
1897 {
1898 $statements = array_merge($statements, $result);
1899 }
1900 }
1901 }
1902 }
1903
1904 // Add columns?
1905 if (!empty($schema_changes['add_columns']))
1906 {
1907 foreach ($schema_changes['add_columns'] as $table => $columns)
1908 {
1909 foreach ($columns as $column_name => $column_data)
1910 {
1911 // Only add the column if it does not exist yet
1912 if ($column_exists = $this->sql_column_exists($table, $column_name))
1913 {
1914 continue;
1915 // This is commented out here because it can take tremendous time on updates
1916 // $result = $this->sql_column_change($table, $column_name, $column_data, true);
1917 }
1918 else
1919 {
1920 $result = $this->sql_column_add($table, $column_name, $column_data, true);
1921 }
1922
1923 if ($sqlite)
1924 {
1925 if ($column_exists)
1926 {
1927 continue;
1928 // $sqlite_data[$table]['change_columns'][] = $result;
1929 }
1930 else
1931 {
1932 $sqlite_data[$table]['add_columns'][] = $result;
1933 }
1934 }
1935 else if ($this->return_statements)
1936 {
1937 $statements = array_merge($statements, $result);
1938 }
1939 }
1940 }
1941 }
1942
1943 // Remove keys?
1944 if (!empty($schema_changes['drop_keys']))
1945 {
1946 foreach ($schema_changes['drop_keys'] as $table => $indexes)
1947 {
1948 foreach ($indexes as $index_name)
1949 {
1950 if (!$this->sql_index_exists($table, $index_name))
1951 {
1952 continue;
1953 }
1954
1955 $result = $this->sql_index_drop($table, $index_name);
1956
1957 if ($this->return_statements)
1958 {
1959 $statements = array_merge($statements, $result);
1960 }
1961 }
1962 }
1963 }
1964
1965 // Drop columns?
1966 if (!empty($schema_changes['drop_columns']))
1967 {
1968 foreach ($schema_changes['drop_columns'] as $table => $columns)
1969 {
1970 foreach ($columns as $column)
1971 {
1972 // Only remove the column if it exists...
1973 if ($this->sql_column_exists($table, $column))
1974 {
1975 $result = $this->sql_column_remove($table, $column, true);
1976
1977 if ($sqlite)
1978 {
1979 $sqlite_data[$table]['drop_columns'][] = $result;
1980 }
1981 else if ($this->return_statements)
1982 {
1983 $statements = array_merge($statements, $result);
1984 }
1985 }
1986 }
1987 }
1988 }
1989
1990 // Add primary keys?
1991 if (!empty($schema_changes['add_primary_keys']))
1992 {
1993 foreach ($schema_changes['add_primary_keys'] as $table => $columns)
1994 {
1995 $result = $this->sql_create_primary_key($table, $columns, true);
1996
1997 if ($sqlite)
1998 {
1999 $sqlite_data[$table]['primary_key'] = $result;
2000 }
2001 else if ($this->return_statements)
2002 {
2003 $statements = array_merge($statements, $result);
2004 }
2005 }
2006 }
2007
2008 // Add unqiue indexes?
2009 if (!empty($schema_changes['add_unique_index']))
2010 {
2011 foreach ($schema_changes['add_unique_index'] as $table => $index_array)
2012 {
2013 foreach ($index_array as $index_name => $column)
2014 {
2015 if ($this->sql_unique_index_exists($table, $index_name))
2016 {
2017 continue;
2018 }
2019
2020 $result = $this->sql_create_unique_index($table, $index_name, $column);
2021
2022 if ($this->return_statements)
2023 {
2024 $statements = array_merge($statements, $result);
2025 }
2026 }
2027 }
2028 }
2029
2030 // Add indexes?
2031 if (!empty($schema_changes['add_index']))
2032 {
2033 foreach ($schema_changes['add_index'] as $table => $index_array)
2034 {
2035 foreach ($index_array as $index_name => $column)
2036 {
2037 if ($this->sql_index_exists($table, $index_name))
2038 {
2039 continue;
2040 }
2041
2042 $result = $this->sql_create_index($table, $index_name, $column);
2043
2044 if ($this->return_statements)
2045 {
2046 $statements = array_merge($statements, $result);
2047 }
2048 }
2049 }
2050 }
2051
2052 if ($sqlite)
2053 {
2054 foreach ($sqlite_data as $table_name => $sql_schema_changes)
2055 {
2056 // Create temporary table with original data
2057 $statements[] = 'begin';
2058
2059 $sql = "SELECT sql
2060 FROM sqlite_master
2061 WHERE type = 'table'
2062 AND name = '{$table_name}'
2063 ORDER BY type DESC, name;";
2064 $result = $this->db->sql_query($sql);
2065
2066 if (!$result)
2067 {
2068 continue;
2069 }
2070
2071 $row = $this->db->sql_fetchrow($result);
2072 $this->db->sql_freeresult($result);
2073
2074 // Create a backup table and populate it, destroy the existing one
2075 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
2076 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
2077 $statements[] = 'DROP TABLE ' . $table_name;
2078
2079 // Get the columns...
2080 preg_match('#\((.*)\)#s', $row['sql'], $matches);
2081
2082 $plain_table_cols = trim($matches[1]);
2083 $new_table_cols = preg_split('/,(?![\s\w]+\))/m', $plain_table_cols);
2084 $column_list = array();
2085
2086 foreach ($new_table_cols as $declaration)
2087 {
2088 $entities = preg_split('#\s+#', trim($declaration));
2089 if ($entities[0] == 'PRIMARY')
2090 {
2091 continue;
2092 }
2093 $column_list[] = $entities[0];
2094 }
2095
2096 // note down the primary key notation because sqlite only supports adding it to the end for the new table
2097 $primary_key = false;
2098 $_new_cols = array();
2099
2100 foreach ($new_table_cols as $key => $declaration)
2101 {
2102 $entities = preg_split('#\s+#', trim($declaration));
2103 if ($entities[0] == 'PRIMARY')
2104 {
2105 $primary_key = $declaration;
2106 continue;
2107 }
2108 $_new_cols[] = $declaration;
2109 }
2110
2111 $new_table_cols = $_new_cols;
2112
2113 // First of all... change columns
2114 if (!empty($sql_schema_changes['change_columns']))
2115 {
2116 foreach ($sql_schema_changes['change_columns'] as $column_sql)
2117 {
2118 foreach ($new_table_cols as $key => $declaration)
2119 {
2120 $entities = preg_split('#\s+#', trim($declaration));
2121 if (strpos($column_sql, $entities[0] . ' ') === 0)
2122 {
2123 $new_table_cols[$key] = $column_sql;
2124 }
2125 }
2126 }
2127 }
2128
2129 if (!empty($sql_schema_changes['add_columns']))
2130 {
2131 foreach ($sql_schema_changes['add_columns'] as $column_sql)
2132 {
2133 $new_table_cols[] = $column_sql;
2134 }
2135 }
2136
2137 // Now drop them...
2138 if (!empty($sql_schema_changes['drop_columns']))
2139 {
2140 foreach ($sql_schema_changes['drop_columns'] as $column_name)
2141 {
2142 // Remove from column list...
2143 $new_column_list = array();
2144 foreach ($column_list as $key => $value)
2145 {
2146 if ($value === $column_name)
2147 {
2148 continue;
2149 }
2150
2151 $new_column_list[] = $value;
2152 }
2153
2154 $column_list = $new_column_list;
2155
2156 // Remove from table...
2157 $_new_cols = array();
2158 foreach ($new_table_cols as $key => $declaration)
2159 {
2160 $entities = preg_split('#\s+#', trim($declaration));
2161 if (strpos($column_name . ' ', $entities[0] . ' ') === 0)
2162 {
2163 continue;
2164 }
2165 $_new_cols[] = $declaration;
2166 }
2167 $new_table_cols = $_new_cols;
2168 }
2169 }
2170
2171 // Primary key...
2172 if (!empty($sql_schema_changes['primary_key']))
2173 {
2174 $new_table_cols[] = 'PRIMARY KEY (' . implode(', ', $sql_schema_changes['primary_key']) . ')';
2175 }
2176 // Add a new one or the old primary key
2177 else if ($primary_key !== false)
2178 {
2179 $new_table_cols[] = $primary_key;
2180 }
2181
2182 $columns = implode(',', $column_list);
2183
2184 // create a new table and fill it up. destroy the temp one
2185 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . implode(',', $new_table_cols) . ');';
2186 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
2187 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
2188
2189 $statements[] = 'commit';
2190 }
2191 }
2192
2193 if ($this->return_statements)
2194 {
2195 return $statements;
2196 }
2197 }
2198
2199 /**
2200 * Check if a specified column exist
2201 *
2202 * @param string $table Table to check the column at
2203 * @param string $column_name The column to check
2204 *
2205 * @return bool True if column exists, else false
2206 */
2207 function sql_column_exists($table, $column_name)
2208 {
2209 switch ($this->sql_layer)
2210 {
2211 case 'mysql_40':
2212 case 'mysql_41':
2213
2214 $sql = "SHOW COLUMNS FROM $table";
2215 $result = $this->db->sql_query($sql);
2216
2217 while ($row = $this->db->sql_fetchrow($result))
2218 {
2219 // lower case just in case
2220 if (strtolower($row['Field']) == $column_name)
2221 {
2222 $this->db->sql_freeresult($result);
2223 return true;
2224 }
2225 }
2226 $this->db->sql_freeresult($result);
2227 return false;
2228 break;
2229
2230 // PostgreSQL has a way of doing this in a much simpler way but would
2231 // not allow us to support all versions of PostgreSQL
2232 case 'postgres':
2233 $sql = "SELECT a.attname
2234 FROM pg_class c, pg_attribute a
2235 WHERE c.relname = '{$table}'
2236 AND a.attnum > 0
2237 AND a.attrelid = c.oid";
2238 $result = $this->db->sql_query($sql);
2239 while ($row = $this->db->sql_fetchrow($result))
2240 {
2241 // lower case just in case
2242 if (strtolower($row['attname']) == $column_name)
2243 {
2244 $this->db->sql_freeresult($result);
2245 return true;
2246 }
2247 }
2248 $this->db->sql_freeresult($result);
2249
2250 return false;
2251 break;
2252
2253 // same deal with PostgreSQL, we must perform more complex operations than
2254 // we technically could
2255 case 'mssql':
2256 $sql = "SELECT c.name
2257 FROM syscolumns c
2258 LEFT JOIN sysobjects o ON c.id = o.id
2259 WHERE o.name = '{$table}'";
2260 $result = $this->db->sql_query($sql);
2261 while ($row = $this->db->sql_fetchrow($result))
2262 {
2263 // lower case just in case
2264 if (strtolower($row['name']) == $column_name)
2265 {
2266 $this->db->sql_freeresult($result);
2267 return true;
2268 }
2269 }
2270 $this->db->sql_freeresult($result);
2271 return false;
2272 break;
2273
2274 case 'oracle':
2275 $sql = "SELECT column_name
2276 FROM user_tab_columns
2277 WHERE LOWER(table_name) = '" . strtolower($table) . "'";
2278 $result = $this->db->sql_query($sql);
2279 while ($row = $this->db->sql_fetchrow($result))
2280 {
2281 // lower case just in case
2282 if (strtolower($row['column_name']) == $column_name)
2283 {
2284 $this->db->sql_freeresult($result);
2285 return true;
2286 }
2287 }
2288 $this->db->sql_freeresult($result);
2289 return false;
2290 break;
2291
2292 case 'firebird':
2293 $sql = "SELECT RDB\$FIELD_NAME as FNAME
2294 FROM RDB\$RELATION_FIELDS
2295 WHERE RDB\$RELATION_NAME = '" . strtoupper($table) . "'";
2296 $result = $this->db->sql_query($sql);
2297 while ($row = $this->db->sql_fetchrow($result))
2298 {
2299 // lower case just in case
2300 if (strtolower($row['fname']) == $column_name)
2301 {
2302 $this->db->sql_freeresult($result);
2303 return true;
2304 }
2305 }
2306 $this->db->sql_freeresult($result);
2307 return false;
2308 break;
2309
2310 // ugh, SQLite
2311 case 'sqlite':
2312 $sql = "SELECT sql
2313 FROM sqlite_master
2314 WHERE type = 'table'
2315 AND name = '{$table}'";
2316 $result = $this->db->sql_query($sql);
2317
2318 if (!$result)
2319 {
2320 return false;
2321 }
2322
2323 $row = $this->db->sql_fetchrow($result);
2324 $this->db->sql_freeresult($result);
2325
2326 preg_match('#\((.*)\)#s', $row['sql'], $matches);
2327
2328 $cols = trim($matches[1]);
2329 $col_array = preg_split('/,(?![\s\w]+\))/m', $cols);
2330
2331 foreach ($col_array as $declaration)
2332 {
2333 $entities = preg_split('#\s+#', trim($declaration));
2334 if ($entities[0] == 'PRIMARY')
2335 {
2336 continue;
2337 }
2338
2339 if (strtolower($entities[0]) == $column_name)
2340 {
2341 return true;
2342 }
2343 }
2344 return false;
2345 break;
2346 }
2347 }
2348
2349 /**
2350 * Check if a specified index exists in table. Does not return PRIMARY KEY and UNIQUE indexes.
2351 *
2352 * @param string $table_name Table to check the index at
2353 * @param string $index_name The index name to check
2354 *
2355 * @return bool True if index exists, else false
2356 */
2357 function sql_index_exists($table_name, $index_name)
2358 {
2359 if ($this->sql_layer == 'mssql')
2360 {
2361 $sql = "EXEC sp_statistics '$table_name'";
2362 $result = $this->db->sql_query($sql);
2363
2364 while ($row = $this->db->sql_fetchrow($result))
2365 {
2366 if ($row['TYPE'] == 3)
2367 {
2368 if (strtolower($row['INDEX_NAME']) == strtolower($index_name))
2369 {
2370 $this->db->sql_freeresult($result);
2371 return true;
2372 }
2373 }
2374 }
2375 $this->db->sql_freeresult($result);
2376
2377 return false;
2378 }
2379
2380 switch ($this->sql_layer)
2381 {
2382 case 'firebird':
2383 $sql = "SELECT LOWER(RDB\$INDEX_NAME) as index_name
2384 FROM RDB\$INDICES
2385 WHERE RDB\$RELATION_NAME = '" . strtoupper($table_name) . "'
2386 AND RDB\$UNIQUE_FLAG IS NULL
2387 AND RDB\$FOREIGN_KEY IS NULL";
2388 $col = 'index_name';
2389 break;
2390
2391 case 'postgres':
2392 $sql = "SELECT ic.relname as index_name
2393 FROM pg_class bc, pg_class ic, pg_index i
2394 WHERE (bc.oid = i.indrelid)
2395 AND (ic.oid = i.indexrelid)
2396 AND (bc.relname = '" . $table_name . "')
2397 AND (i.indisunique != 't')
2398 AND (i.indisprimary != 't')";
2399 $col = 'index_name';
2400 break;
2401
2402 case 'mysql_40':
2403 case 'mysql_41':
2404 $sql = 'SHOW KEYS
2405 FROM ' . $table_name;
2406 $col = 'Key_name';
2407 break;
2408
2409 case 'oracle':
2410 $sql = "SELECT index_name
2411 FROM user_indexes
2412 WHERE table_name = '" . strtoupper($table_name) . "'
2413 AND generated = 'N'
2414 AND uniqueness = 'NONUNIQUE'";
2415 $col = 'index_name';
2416 break;
2417
2418 case 'sqlite':
2419 $sql = "PRAGMA index_list('" . $table_name . "');";
2420 $col = 'name';
2421 break;
2422 }
2423
2424 $result = $this->db->sql_query($sql);
2425 while ($row = $this->db->sql_fetchrow($result))
2426 {
2427 if (($this->sql_layer == 'mysql_40' || $this->sql_layer == 'mysql_41') && !$row['Non_unique'])
2428 {
2429 continue;
2430 }
2431
2432 // These DBMS prefix index name with the table name
2433 switch ($this->sql_layer)
2434 {
2435 case 'firebird':
2436 case 'oracle':
2437 case 'postgres':
2438 case 'sqlite':
2439 $row[$col] = substr($row[$col], strlen($table_name) + 1);
2440 break;
2441 }
2442
2443 if (strtolower($row[$col]) == strtolower($index_name))
2444 {
2445 $this->db->sql_freeresult($result);
2446 return true;
2447 }
2448 }
2449 $this->db->sql_freeresult($result);
2450
2451 return false;
2452 }
2453
2454 /**
2455 * Check if a specified UNIQUE index exists in table.
2456 *
2457 * @param string $table_name Table to check the index at
2458 * @param string $index_name The index name to check
2459 *
2460 * @return bool True if index exists, else false
2461 */
2462 function sql_unique_index_exists($table_name, $index_name)
2463 {
2464 if ($this->sql_layer == 'mssql')
2465 {
2466 $sql = "EXEC sp_statistics '$table_name'";
2467 $result = $this->db->sql_query($sql);
2468
2469 while ($row = $this->db->sql_fetchrow($result))
2470 {
2471 // Usually NON_UNIQUE is the column we want to check, but we allow for both
2472 if ($row['TYPE'] == 3)
2473 {
2474 if (strtolower($row['INDEX_NAME']) == strtolower($index_name))
2475 {
2476 $this->db->sql_freeresult($result);
2477 return true;
2478 }
2479 }
2480 }
2481 $this->db->sql_freeresult($result);
2482 return false;
2483 }
2484
2485 switch ($this->sql_layer)
2486 {
2487 case 'firebird':
2488 $sql = "SELECT LOWER(RDB\$INDEX_NAME) as index_name
2489 FROM RDB\$INDICES
2490 WHERE RDB\$RELATION_NAME = '" . strtoupper($table_name) . "'
2491 AND RDB\$UNIQUE_FLAG IS NOT NULL
2492 AND RDB\$FOREIGN_KEY IS NULL";
2493 $col = 'index_name';
2494 break;
2495
2496 case 'postgres':
2497 $sql = "SELECT ic.relname as index_name, i.indisunique
2498 FROM pg_class bc, pg_class ic, pg_index i
2499 WHERE (bc.oid = i.indrelid)
2500 AND (ic.oid = i.indexrelid)
2501 AND (bc.relname = '" . $table_name . "')
2502 AND (i.indisprimary != 't')";
2503 $col = 'index_name';
2504 break;
2505
2506 case 'mysql_40':
2507 case 'mysql_41':
2508 $sql = 'SHOW KEYS
2509 FROM ' . $table_name;
2510 $col = 'Key_name';
2511 break;
2512
2513 case 'oracle':
2514 $sql = "SELECT index_name, table_owner
2515 FROM user_indexes
2516 WHERE table_name = '" . strtoupper($table_name) . "'
2517 AND generated = 'N'
2518 AND uniqueness = 'UNIQUE'
2519 AND index_name LIKE 'U_%'";
2520 $col = 'index_name';
2521 break;
2522
2523 case 'sqlite':
2524 $sql = "PRAGMA index_list('" . $table_name . "') WHERE unique = 1;";
2525 $col = 'name';
2526 break;
2527 }
2528
2529 $result = $this->db->sql_query($sql);
2530 while ($row = $this->db->sql_fetchrow($result))
2531 {
2532 if (($this->sql_layer == 'mysql_40' || $this->sql_layer == 'mysql_41') && ($row['Non_unique'] || $row[$col] == 'PRIMARY'))
2533 {
2534 continue;
2535 }
2536
2537 if ($this->sql_layer == 'sqlite' && !$row['unique'])
2538 {
2539 continue;
2540 }
2541
2542 if ($this->sql_layer == 'postgres' && $row['indisunique'] != 't')
2543 {
2544 continue;
2545 }
2546
2547 // These DBMS prefix index name with the table name
2548 switch ($this->sql_layer)
2549 {
2550 case 'oracle':
2551 $row[$col] = substr($row[$col], strlen('U_' . $row['table_owner']) + 1);
2552 break;
2553
2554 case 'firebird':
2555 case 'postgres':
2556 case 'sqlite':
2557 $row[$col] = substr($row[$col], strlen($table_name) + 1);
2558 break;
2559 }
2560
2561 if (strtolower($row[$col]) == strtolower($index_name))
2562 {
2563 $this->db->sql_freeresult($result);
2564 return true;
2565 }
2566 }
2567 $this->db->sql_freeresult($result);
2568
2569 return false;
2570 }
2571
2572 /**
2573 * Private method for performing sql statements (either execute them or return them)
2574 * @access private
2575 */
2576 function _sql_run_sql($statements)
2577 {
2578 if ($this->return_statements)
2579 {
2580 return $statements;
2581 }
2582
2583 // We could add error handling here...
2584 foreach ($statements as $sql)
2585 {
2586 if ($sql === 'begin')
2587 {
2588 $this->db->sql_transaction('begin');
2589 }
2590 else if ($sql === 'commit')
2591 {
2592 $this->db->sql_transaction('commit');
2593 }
2594 else
2595 {
2596 $this->db->sql_query($sql);
2597 }
2598 }
2599
2600 return true;
2601 }
2602
2603 /**
2604 * Function to prepare some column information for better usage
2605 * @access private
2606 */
2607 function sql_prepare_column_data($table_name, $column_name, $column_data)
2608 {
2609 // Get type
2610 if (strpos($column_data[0], ':') !== false)
2611 {
2612 list($orig_column_type, $column_length) = explode(':', $column_data[0]);
2613 if (!is_array($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']))
2614 {
2615 $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'], $column_length);
2616 }
2617 else
2618 {
2619 if (isset($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule']))
2620 {
2621 switch ($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule'][0])
2622 {
2623 case 'div':
2624 $column_length /= $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule'][1];
2625 $column_length = ceil($column_length);
2626 $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'][0], $column_length);
2627 break;
2628 }
2629 }
2630
2631 if (isset($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit']))
2632 {
2633 switch ($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][0])
2634 {
2635 case 'mult':
2636 $column_length *= $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][1];
2637 if ($column_length > $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][2])
2638 {
2639 $column_type = $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][3];
2640 }
2641 else
2642 {
2643 $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'][0], $column_length);
2644 }
2645 break;
2646 }
2647 }
2648 }
2649 $orig_column_type .= ':';
2650 }
2651 else
2652 {
2653 $orig_column_type = $column_data[0];
2654 $column_type = $this->dbms_type_map[$this->sql_layer][$column_data[0]];
2655 }
2656
2657 // Adjust default value if db-dependant specified
2658 if (is_array($column_data[1]))
2659 {
2660 $column_data[1] = (isset($column_data[1][$this->sql_layer])) ? $column_data[1][$this->sql_layer] : $column_data[1]['default'];
2661 }
2662
2663 $sql = '';
2664
2665 $return_array = array();
2666
2667 switch ($this->sql_layer)
2668 {
2669 case 'firebird':
2670 $sql .= " {$column_type} ";
2671 $return_array['column_type_sql_type'] = " {$column_type} ";
2672
2673 if (!is_null($column_data[1]))
2674 {
2675 $sql .= 'DEFAULT ' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ' ';
2676 $return_array['column_type_sql_default'] = ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ' ';
2677 }
2678
2679 $sql .= 'NOT NULL';
2680
2681 // This is a UNICODE column and thus should be given it's fair share
2682 if (preg_match('/^X?STEXT_UNI|VCHAR_(CI|UNI:?)/', $column_data[0]))
2683 {
2684 $sql .= ' COLLATE UNICODE';
2685 }
2686
2687 $return_array['auto_increment'] = false;
2688 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
2689 {
2690 $return_array['auto_increment'] = true;
2691 }
2692
2693 break;
2694
2695 case 'mssql':
2696 $sql .= " {$column_type} ";
2697 $sql_default = " {$column_type} ";
2698
2699 // For adding columns we need the default definition
2700 if (!is_null($column_data[1]))
2701 {
2702 // For hexadecimal values do not use single quotes
2703 if (strpos($column_data[1], '0x') === 0)
2704 {
2705 $return_array['default'] = 'DEFAULT (' . $column_data[1] . ') ';
2706 $sql_default .= $return_array['default'];
2707 }
2708 else
2709 {
2710 $return_array['default'] = 'DEFAULT (' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ') ';
2711 $sql_default .= $return_array['default'];
2712 }
2713 }
2714
2715 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
2716 {
2717 // $sql .= 'IDENTITY (1, 1) ';
2718 $sql_default .= 'IDENTITY (1, 1) ';
2719 }
2720
2721 $return_array['textimage'] = $column_type === '[text]';
2722
2723 $sql .= 'NOT NULL';
2724 $sql_default .= 'NOT NULL';
2725
2726 $return_array['column_type_sql_default'] = $sql_default;
2727
2728 break;
2729
2730 case 'mysql_40':
2731 case 'mysql_41':
2732 $sql .= " {$column_type} ";
2733
2734 // For hexadecimal values do not use single quotes
2735 if (!is_null($column_data[1]) && substr($column_type, -4) !== 'text' && substr($column_type, -4) !== 'blob')
2736 {
2737 $sql .= (strpos($column_data[1], '0x') === 0) ? "DEFAULT {$column_data[1]} " : "DEFAULT '{$column_data[1]}' ";
2738 }
2739 $sql .= 'NOT NULL';
2740
2741 if (isset($column_data[2]))
2742 {
2743 if ($column_data[2] == 'auto_increment')
2744 {
2745 $sql .= ' auto_increment';
2746 }
2747 else if ($this->sql_layer === 'mysql_41' && $column_data[2] == 'true_sort')
2748 {
2749 $sql .= ' COLLATE utf8_unicode_ci';
2750 }
2751 }
2752
2753 break;
2754
2755 case 'oracle':
2756 $sql .= " {$column_type} ";
2757 $sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : '';
2758
2759 // In Oracle empty strings ('') are treated as NULL.
2760 // Therefore in oracle we allow NULL's for all DEFAULT '' entries
2761 // Oracle does not like setting NOT NULL on a column that is already NOT NULL (this happens only on number fields)
2762 if (!preg_match('/number/i', $column_type))
2763 {
2764 $sql .= ($column_data[1] === '') ? '' : 'NOT NULL';
2765 }
2766
2767 $return_array['auto_increment'] = false;
2768 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
2769 {
2770 $return_array['auto_increment'] = true;
2771 }
2772
2773 break;
2774
2775 case 'postgres':
2776 $return_array['column_type'] = $column_type;
2777
2778 $sql .= " {$column_type} ";
2779
2780 $return_array['auto_increment'] = false;
2781 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
2782 {
2783 $default_val = "nextval('{$table_name}_seq')";
2784 $return_array['auto_increment'] = true;
2785 }
2786 else if (!is_null($column_data[1]))
2787 {
2788 $default_val = "'" . $column_data[1] . "'";
2789 $return_array['null'] = 'NOT NULL';
2790 $sql .= 'NOT NULL ';
2791 }
2792
2793 $return_array['default'] = $default_val;
2794
2795 $sql .= "DEFAULT {$default_val}";
2796
2797 // Unsigned? Then add a CHECK contraint
2798 if (in_array($orig_column_type, $this->unsigned_types))
2799 {
2800 $return_array['constraint'] = "CHECK ({$column_name} >= 0)";
2801 $sql .= " CHECK ({$column_name} >= 0)";
2802 }
2803
2804 break;
2805
2806 case 'sqlite':
2807 $return_array['primary_key_set'] = false;
2808 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
2809 {
2810 $sql .= ' INTEGER PRIMARY KEY';
2811 $return_array['primary_key_set'] = true;
2812 }
2813 else
2814 {
2815 $sql .= ' ' . $column_type;
2816 }
2817
2818 $sql .= ' NOT NULL ';
2819 $sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}'" : '';
2820
2821 break;
2822 }
2823
2824 $return_array['column_type_sql'] = $sql;
2825
2826 return $return_array;
2827 }
2828
2829 /**
2830 * Add new column
2831 */
2832 function sql_column_add($table_name, $column_name, $column_data, $inline = false)
2833 {
2834 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
2835 $statements = array();
2836
2837 switch ($this->sql_layer)
2838 {
2839 case 'firebird':
2840 // Does not support AFTER statement, only POSITION (and there you need the column position)
2841 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD "' . strtoupper($column_name) . '" ' . $column_data['column_type_sql'];
2842 break;
2843
2844 case 'mssql':
2845 // Does not support AFTER, only through temporary table
2846 $statements[] = 'ALTER TABLE [' . $table_name . '] ADD [' . $column_name . '] ' . $column_data['column_type_sql_default'];
2847 break;
2848
2849 case 'mysql_40':
2850 case 'mysql_41':
2851 $after = (!empty($column_data['after'])) ? ' AFTER ' . $column_data['after'] : '';
2852 $statements[] = 'ALTER TABLE `' . $table_name . '` ADD COLUMN `' . $column_name . '` ' . $column_data['column_type_sql'] . $after;
2853 break;
2854
2855 case 'oracle':
2856 // Does not support AFTER, only through temporary table
2857 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' ' . $column_data['column_type_sql'];
2858 break;
2859
2860 case 'postgres':
2861 // Does not support AFTER, only through temporary table
2862 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD COLUMN "' . $column_name . '" ' . $column_data['column_type_sql'];
2863 break;
2864
2865 case 'sqlite':
2866
2867 if ($inline && $this->return_statements)
2868 {
2869 return $column_name . ' ' . $column_data['column_type_sql'];
2870 }
2871
2872 if (version_compare(sqlite_libversion(), '3.0') == -1)
2873 {
2874 $sql = "SELECT sql
2875 FROM sqlite_master
2876 WHERE type = 'table'
2877 AND name = '{$table_name}'
2878 ORDER BY type DESC, name;";
2879 $result = $this->db->sql_query($sql);
2880
2881 if (!$result)
2882 {
2883 break;
2884 }
2885
2886 $row = $this->db->sql_fetchrow($result);
2887 $this->db->sql_freeresult($result);
2888
2889 $statements[] = 'begin';
2890
2891 // Create a backup table and populate it, destroy the existing one
2892 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
2893 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
2894 $statements[] = 'DROP TABLE ' . $table_name;
2895
2896 preg_match('#\((.*)\)#s', $row['sql'], $matches);
2897
2898 $new_table_cols = trim($matches[1]);
2899 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
2900 $column_list = array();
2901
2902 foreach ($old_table_cols as $declaration)
2903 {
2904 $entities = preg_split('#\s+#', trim($declaration));
2905 if ($entities[0] == 'PRIMARY')
2906 {
2907 continue;
2908 }
2909 $column_list[] = $entities[0];
2910 }
2911
2912 $columns = implode(',', $column_list);
2913
2914 $new_table_cols = $column_name . ' ' . $column_data['column_type_sql'] . ',' . $new_table_cols;
2915
2916 // create a new table and fill it up. destroy the temp one
2917 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');';
2918 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
2919 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
2920
2921 $statements[] = 'commit';
2922 }
2923 else
2924 {
2925 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' [' . $column_data['column_type_sql'] . ']';
2926 }
2927 break;
2928 }
2929
2930 return $this->_sql_run_sql($statements);
2931 }
2932
2933 /**
2934 * Drop column
2935 */
2936 function sql_column_remove($table_name, $column_name, $inline = false)
2937 {
2938 $statements = array();
2939
2940 switch ($this->sql_layer)
2941 {
2942 case 'firebird':
2943 $statements[] = 'ALTER TABLE ' . $table_name . ' DROP "' . strtoupper($column_name) . '"';
2944 break;
2945
2946 case 'mssql':
2947 $statements[] = 'ALTER TABLE [' . $table_name . '] DROP COLUMN [' . $column_name . ']';
2948 break;
2949
2950 case 'mysql_40':
2951 case 'mysql_41':
2952 $statements[] = 'ALTER TABLE `' . $table_name . '` DROP COLUMN `' . $column_name . '`';
2953 break;
2954
2955 case 'oracle':
2956 $statements[] = 'ALTER TABLE ' . $table_name . ' DROP ' . $column_name;
2957 break;
2958
2959 case 'postgres':
2960 $statements[] = 'ALTER TABLE ' . $table_name . ' DROP COLUMN "' . $column_name . '"';
2961 break;
2962
2963 case 'sqlite':
2964
2965 if ($inline && $this->return_statements)
2966 {
2967 return $column_name;
2968 }
2969
2970 if (version_compare(sqlite_libversion(), '3.0') == -1)
2971 {
2972 $sql = "SELECT sql
2973 FROM sqlite_master
2974 WHERE type = 'table'
2975 AND name = '{$table_name}'
2976 ORDER BY type DESC, name;";
2977 $result = $this->db->sql_query($sql);
2978
2979 if (!$result)
2980 {
2981 break;
2982 }
2983
2984 $row = $this->db->sql_fetchrow($result);
2985 $this->db->sql_freeresult($result);
2986
2987 $statements[] = 'begin';
2988
2989 // Create a backup table and populate it, destroy the existing one
2990 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
2991 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
2992 $statements[] = 'DROP TABLE ' . $table_name;
2993
2994 preg_match('#\((.*)\)#s', $row['sql'], $matches);
2995
2996 $new_table_cols = trim($matches[1]);
2997 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
2998 $column_list = array();
2999
3000 foreach ($old_table_cols as $declaration)
3001 {
3002 $entities = preg_split('#\s+#', trim($declaration));
3003 if ($entities[0] == 'PRIMARY' || $entities[0] === $column_name)
3004 {
3005 continue;
3006 }
3007 $column_list[] = $entities[0];
3008 }
3009
3010 $columns = implode(',', $column_list);
3011
3012 $new_table_cols = $new_table_cols = preg_replace('/' . $column_name . '[^,]+(?:,|$)/m', '', $new_table_cols);
3013
3014 // create a new table and fill it up. destroy the temp one
3015 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');';
3016 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
3017 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
3018
3019 $statements[] = 'commit';
3020 }
3021 else
3022 {
3023 $statements[] = 'ALTER TABLE ' . $table_name . ' DROP COLUMN ' . $column_name;
3024 }
3025 break;
3026 }
3027
3028 return $this->_sql_run_sql($statements);
3029 }
3030
3031 /**
3032 * Drop Index
3033 */
3034 function sql_index_drop($table_name, $index_name)
3035 {
3036 $statements = array();
3037
3038 switch ($this->sql_layer)
3039 {
3040 case 'mssql':
3041 $statements[] = 'DROP INDEX ' . $table_name . '.' . $index_name;
3042 break;
3043
3044 case 'mysql_40':
3045 case 'mysql_41':
3046 $statements[] = 'DROP INDEX ' . $index_name . ' ON ' . $table_name;
3047 break;
3048
3049 case 'firebird':
3050 case 'oracle':
3051 case 'postgres':
3052 case 'sqlite':
3053 $statements[] = 'DROP INDEX ' . $table_name . '_' . $index_name;
3054 break;
3055 }
3056
3057 return $this->_sql_run_sql($statements);
3058 }
3059
3060 /**
3061 * Add primary key
3062 */
3063 function sql_create_primary_key($table_name, $column, $inline = false)
3064 {
3065 $statements = array();
3066
3067 switch ($this->sql_layer)
3068 {
3069 case 'firebird':
3070 case 'postgres':
3071 case 'mysql_40':
3072 case 'mysql_41':
3073 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD PRIMARY KEY (' . implode(', ', $column) . ')';
3074 break;
3075
3076 case 'mssql':
3077 $sql = "ALTER TABLE [{$table_name}] WITH NOCHECK ADD ";
3078 $sql .= "CONSTRAINT [PK_{$table_name}] PRIMARY KEY CLUSTERED (";
3079 $sql .= '[' . implode("],\n\t\t[", $column) . ']';
3080 $sql .= ') ON [PRIMARY]';
3081
3082 $statements[] = $sql;
3083 break;
3084
3085 case 'oracle':
3086 $statements[] = 'ALTER TABLE ' . $table_name . 'add CONSTRAINT pk_' . $table_name . ' PRIMARY KEY (' . implode(', ', $column) . ')';
3087 break;
3088
3089 case 'sqlite':
3090
3091 if ($inline && $this->return_statements)
3092 {
3093 return $column;
3094 }
3095
3096 $sql = "SELECT sql
3097 FROM sqlite_master
3098 WHERE type = 'table'
3099 AND name = '{$table_name}'
3100 ORDER BY type DESC, name;";
3101 $result = $this->db->sql_query($sql);
3102
3103 if (!$result)
3104 {
3105 break;
3106 }
3107
3108 $row = $this->db->sql_fetchrow($result);
3109 $this->db->sql_freeresult($result);
3110
3111 $statements[] = 'begin';
3112
3113 // Create a backup table and populate it, destroy the existing one
3114 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
3115 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
3116 $statements[] = 'DROP TABLE ' . $table_name;
3117
3118 preg_match('#\((.*)\)#s', $row['sql'], $matches);
3119
3120 $new_table_cols = trim($matches[1]);
3121 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
3122 $column_list = array();
3123
3124 foreach ($old_table_cols as $declaration)
3125 {
3126 $entities = preg_split('#\s+#', trim($declaration));
3127 if ($entities[0] == 'PRIMARY')
3128 {
3129 continue;
3130 }
3131 $column_list[] = $entities[0];
3132 }
3133
3134 $columns = implode(',', $column_list);
3135
3136 // create a new table and fill it up. destroy the temp one
3137 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ', PRIMARY KEY (' . implode(', ', $column) . '));';
3138 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
3139 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
3140
3141 $statements[] = 'commit';
3142 break;
3143 }
3144
3145 return $this->_sql_run_sql($statements);
3146 }
3147
3148 /**
3149 * Add unique index
3150 */
3151 function sql_create_unique_index($table_name, $index_name, $column)
3152 {
3153 $statements = array();
3154
3155 switch ($this->sql_layer)
3156 {
3157 case 'firebird':
3158 case 'postgres':
3159 case 'oracle':
3160 case 'sqlite':
3161 $statements[] = 'CREATE UNIQUE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
3162 break;
3163
3164 case 'mysql_40':
3165 case 'mysql_41':
3166 $statements[] = 'CREATE UNIQUE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
3167 break;
3168
3169 case 'mssql':
3170 $statements[] = 'CREATE UNIQUE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ') ON [PRIMARY]';
3171 break;
3172 }
3173
3174 return $this->_sql_run_sql($statements);
3175 }
3176
3177 /**
3178 * Add index
3179 */
3180 function sql_create_index($table_name, $index_name, $column)
3181 {
3182 $statements = array();
3183
3184 switch ($this->sql_layer)
3185 {
3186 case 'firebird':
3187 case 'postgres':
3188 case 'oracle':
3189 case 'sqlite':
3190 $statements[] = 'CREATE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
3191 break;
3192
3193 case 'mysql_40':
3194 case 'mysql_41':
3195 $statements[] = 'CREATE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
3196 break;
3197
3198 case 'mssql':
3199 $statements[] = 'CREATE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ') ON [PRIMARY]';
3200 break;
3201 }
3202
3203 return $this->_sql_run_sql($statements);
3204 }
3205
3206 /**
3207 * Change column type (not name!)
3208 */
3209 function sql_column_change($table_name, $column_name, $column_data, $inline = false)
3210 {
3211 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
3212 $statements = array();
3213
3214 switch ($this->sql_layer)
3215 {
3216 case 'firebird':
3217 // Change type...
3218 if (!empty($column_data['column_type_sql_default']))
3219 {
3220 $statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN "' . strtoupper($column_name) . '" TYPE ' . ' ' . $column_data['column_type_sql_type'];
3221 $statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN "' . strtoupper($column_name) . '" SET DEFAULT ' . ' ' . $column_data['column_type_sql_default'];
3222 }
3223 else
3224 {
3225 $statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN "' . strtoupper($column_name) . '" TYPE ' . ' ' . $column_data['column_type_sql'];
3226 }
3227 break;
3228
3229 case 'mssql':
3230 $statements[] = 'ALTER TABLE [' . $table_name . '] ALTER COLUMN [' . $column_name . '] ' . $column_data['column_type_sql'];
3231
3232 if (!empty($column_data['default']))
3233 {
3234 // Using TRANSACT-SQL for this statement because we do not want to have colliding data if statements are executed at a later stage
3235 $statements[] = "DECLARE @drop_default_name VARCHAR(100), @cmd VARCHAR(1000)
3236 SET @drop_default_name =
3237 (SELECT so.name FROM sysobjects so
3238 JOIN sysconstraints sc ON so.id = sc.constid
3239 WHERE object_name(so.parent_obj) = '{$table_name}'
3240 AND so.xtype = 'D'
3241 AND sc.colid = (SELECT colid FROM syscolumns
3242 WHERE id = object_id('{$table_name}')
3243 AND name = '{$column_name}'))
3244 IF @drop_default_name <> ''
3245 BEGIN
3246 SET @cmd = 'ALTER TABLE [{$table_name}] DROP CONSTRAINT [' + @drop_default_name + ']'
3247 EXEC(@cmd)
3248 END
3249 SET @cmd = 'ALTER TABLE [{$table_name}] ADD CONSTRAINT [DF_{$table_name}_{$column_name}_1] {$column_data['default']} FOR [{$column_name}]'
3250 EXEC(@cmd)";
3251 }
3252 break;
3253
3254 case 'mysql_40':
3255 case 'mysql_41':
3256 $statements[] = 'ALTER TABLE `' . $table_name . '` CHANGE `' . $column_name . '` `' . $column_name . '` ' . $column_data['column_type_sql'];
3257 break;
3258
3259 case 'oracle':
3260 $statements[] = 'ALTER TABLE ' . $table_name . ' MODIFY ' . $column_name . ' ' . $column_data['column_type_sql'];
3261 break;
3262
3263 case 'postgres':
3264 $sql = 'ALTER TABLE ' . $table_name . ' ';
3265
3266 $sql_array = array();
3267 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' TYPE ' . $column_data['column_type'];
3268
3269 if (isset($column_data['null']))
3270 {
3271 if ($column_data['null'] == 'NOT NULL')
3272 {
3273 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' SET NOT NULL';
3274 }
3275 else if ($column_data['null'] == 'NULL')
3276 {
3277 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' DROP NOT NULL';
3278 }
3279 }
3280
3281 if (isset($column_data['default']))
3282 {
3283 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' SET DEFAULT ' . $column_data['default'];
3284 }
3285
3286 // we don't want to double up on constraints if we change different number data types
3287 if (isset($column_data['constraint']))
3288 {
3289 $constraint_sql = "SELECT consrc as constraint_data
3290 FROM pg_constraint, pg_class bc
3291 WHERE conrelid = bc.oid
3292 AND bc.relname = '{$table_name}'
3293 AND NOT EXISTS (
3294 SELECT *
3295 FROM pg_constraint as c, pg_inherits as i
3296 WHERE i.inhrelid = pg_constraint.conrelid
3297 AND c.conname = pg_constraint.conname
3298 AND c.consrc = pg_constraint.consrc
3299 AND c.conrelid = i.inhparent
3300 )";
3301
3302 $constraint_exists = false;
3303
3304 $result = $this->db->sql_query($constraint_sql);
3305 while ($row = $this->db->sql_fetchrow($result))
3306 {
3307 if (trim($row['constraint_data']) == trim($column_data['constraint']))
3308 {
3309 $constraint_exists = true;
3310 break;
3311 }
3312 }
3313 $this->db->sql_freeresult($result);
3314
3315 if (!$constraint_exists)
3316 {
3317 $sql_array[] = 'ADD ' . $column_data['constraint'];
3318 }
3319 }
3320
3321 $sql .= implode(', ', $sql_array);
3322
3323 $statements[] = $sql;
3324 break;
3325
3326 case 'sqlite':
3327
3328 if ($inline && $this->return_statements)
3329 {
3330 return $column_name . ' ' . $column_data['column_type_sql'];
3331 }
3332
3333 $sql = "SELECT sql
3334 FROM sqlite_master
3335 WHERE type = 'table'
3336 AND name = '{$table_name}'
3337 ORDER BY type DESC, name;";
3338 $result = $this->db->sql_query($sql);
3339
3340 if (!$result)
3341 {
3342 break;
3343 }
3344
3345 $row = $this->db->sql_fetchrow($result);
3346 $this->db->sql_freeresult($result);
3347
3348 $statements[] = 'begin';
3349
3350 // Create a temp table and populate it, destroy the existing one
3351 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
3352 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
3353 $statements[] = 'DROP TABLE ' . $table_name;
3354
3355 preg_match('#\((.*)\)#s', $row['sql'], $matches);
3356
3357 $new_table_cols = trim($matches[1]);
3358 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
3359 $column_list = array();
3360
3361 foreach ($old_table_cols as $key => $declaration)
3362 {
3363 $entities = preg_split('#\s+#', trim($declaration));
3364 $column_list[] = $entities[0];
3365 if ($entities[0] == $column_name)
3366 {
3367 $old_table_cols[$key] = $column_name . ' ' . $column_data['column_type_sql'];
3368 }
3369 }
3370
3371 $columns = implode(',', $column_list);
3372
3373 // create a new table and fill it up. destroy the temp one
3374 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . implode(',', $old_table_cols) . ');';
3375 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
3376 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
3377
3378 $statements[] = 'commit';
3379
3380 break;
3381 }
3382
3383 return $this->_sql_run_sql($statements);
3384 }
3385 }
3386
3387 ?>
phpbb is a php-based BBS (buletin board system)