?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
further updates
[phpbb.git] / phpBB / install / database_update.php
blob0145c2746a20f3f272aff8eede487df4a18e251e
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.RC8';
12
13 // Return if we "just include it" to find out for which version the database update is responsuble for
14 if (defined('IN_PHPBB') && defined('IN_INSTALL'))
15 {
16 return;
17 }
18
19 /**
20 */
21 define('IN_PHPBB', true);
22 define('IN_INSTALL', true);
23
24 $phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : './../';
25 $phpEx = substr(strrchr(__FILE__, '.'), 1);
26
27 // Report all errors, except notices
28 //error_reporting(E_ALL ^ E_NOTICE);
29 error_reporting(E_ALL);
30
31 @set_time_limit(0);
32
33 // Include essential scripts
34 include($phpbb_root_path . 'config.' . $phpEx);
35
36 if (!isset($dbms))
37 {
38 die("Please read: <a href='../docs/INSTALL.html'>INSTALL.html</a> before attempting to update.");
39 }
40
41 // Load Extensions
42 if (!empty($load_extensions))
43 {
44 $load_extensions = explode(',', $load_extensions);
45
46 foreach ($load_extensions as $extension)
47 {
48 @dl(trim($extension));
49 }
50 }
51
52 // Include files
53 require($phpbb_root_path . 'includes/acm/acm_' . $acm_type . '.' . $phpEx);
54 require($phpbb_root_path . 'includes/cache.' . $phpEx);
55 require($phpbb_root_path . 'includes/template.' . $phpEx);
56 require($phpbb_root_path . 'includes/session.' . $phpEx);
57 require($phpbb_root_path . 'includes/auth.' . $phpEx);
58
59 require($phpbb_root_path . 'includes/functions.' . $phpEx);
60
61 if (file_exists($phpbb_root_path . 'includes/functions_content.' . $phpEx))
62 {
63 require($phpbb_root_path . 'includes/functions_content.' . $phpEx);
64 }
65
66 require($phpbb_root_path . 'includes/functions_admin.' . $phpEx);
67 require($phpbb_root_path . 'includes/constants.' . $phpEx);
68 require($phpbb_root_path . 'includes/db/' . $dbms . '.' . $phpEx);
69 require($phpbb_root_path . 'includes/utf/utf_tools.' . $phpEx);
70
71 // If we are on PHP >= 6.0.0 we do not need some code
72 if (version_compare(PHP_VERSION, '6.0.0-dev', '>='))
73 {
74 /**
75 * @ignore
76 */
77 define('STRIP', false);
78 }
79 else
80 {
81 set_magic_quotes_runtime(0);
82 define('STRIP', (get_magic_quotes_gpc()) ? true : false);
83 }
84
85 $user = new user();
86 $cache = new cache();
87 $db = new $sql_db();
88
89 // Add own hook handler, if present. :o
90 if (file_exists($phpbb_root_path . 'includes/hooks/index.' . $phpEx))
91 {
92 require($phpbb_root_path . 'includes/hooks/index.' . $phpEx);
93 $phpbb_hook = new phpbb_hook(array('exit_handler', 'phpbb_user_session_handler', 'append_sid', array('template', 'display')));
94
95 foreach ($cache->obtain_hooks() as $hook)
96 {
97 @include($phpbb_root_path . 'includes/hooks/' . $hook . '.' . $phpEx);
98 }
99 }
100 else
101 {
102 $phpbb_hook = false;
103 }
104
105 // Connect to DB
106 $db->sql_connect($dbhost, $dbuser, $dbpasswd, $dbname, $dbport, false, false);
107
108 // We do not need this any longer, unset for safety purposes
109 unset($dbpasswd);
110
111 $user->ip = (!empty($_SERVER['REMOTE_ADDR'])) ? htmlspecialchars($_SERVER['REMOTE_ADDR']) : '';
112
113 $sql = "SELECT config_value
114 FROM " . CONFIG_TABLE . "
115 WHERE config_name = 'default_lang'";
116 $result = $db->sql_query($sql);
117 $row = $db->sql_fetchrow($result);
118 $db->sql_freeresult($result);
119
120 $language = basename(request_var('language', ''));
121
122 if (!$language)
123 {
124 $language = $row['config_value'];
125 }
126
127 if (!file_exists($phpbb_root_path . 'language/' . $language))
128 {
129 die('No language found!');
130 }
131
132 // And finally, load the relevant language files
133 include($phpbb_root_path . 'language/' . $language . '/common.' . $phpEx);
134 include($phpbb_root_path . 'language/' . $language . '/acp/common.' . $phpEx);
135 include($phpbb_root_path . 'language/' . $language . '/install.' . $phpEx);
136
137 // Set PHP error handler to ours
138 //set_error_handler('msg_handler');
139
140 // Define some variables for the database update
141 $inline_update = (request_var('type', 0)) ? true : false;
142
143 // Database column types mapping
144 $dbms_type_map = array(
145 'mysql_41' => array(
146 'INT:' => 'int(%d)',
147 'BINT' => 'bigint(20)',
148 'UINT' => 'mediumint(8) UNSIGNED',
149 'UINT:' => 'int(%d) UNSIGNED',
150 'TINT:' => 'tinyint(%d)',
151 'USINT' => 'smallint(4) UNSIGNED',
152 'BOOL' => 'tinyint(1) UNSIGNED',
153 'VCHAR' => 'varchar(255)',
154 'VCHAR:' => 'varchar(%d)',
155 'CHAR:' => 'char(%d)',
156 'XSTEXT' => 'text',
157 'XSTEXT_UNI'=> 'varchar(100)',
158 'STEXT' => 'text',
159 'STEXT_UNI' => 'varchar(255)',
160 'TEXT' => 'text',
161 'TEXT_UNI' => 'text',
162 'MTEXT' => 'mediumtext',
163 'MTEXT_UNI' => 'mediumtext',
164 'TIMESTAMP' => 'int(11) UNSIGNED',
165 'DECIMAL' => 'decimal(5,2)',
166 'VCHAR_UNI' => 'varchar(255)',
167 'VCHAR_UNI:'=> 'varchar(%d)',
168 'VCHAR_CI' => 'varchar(255)',
169 'VARBINARY' => 'varbinary(255)',
170 ),
171
172 'mysql_40' => array(
173 'INT:' => 'int(%d)',
174 'BINT' => 'bigint(20)',
175 'UINT' => 'mediumint(8) UNSIGNED',
176 'UINT:' => 'int(%d) UNSIGNED',
177 'TINT:' => 'tinyint(%d)',
178 'USINT' => 'smallint(4) UNSIGNED',
179 'BOOL' => 'tinyint(1) UNSIGNED',
180 'VCHAR' => 'varbinary(255)',
181 'VCHAR:' => 'varbinary(%d)',
182 'CHAR:' => 'binary(%d)',
183 'XSTEXT' => 'blob',
184 'XSTEXT_UNI'=> 'blob',
185 'STEXT' => 'blob',
186 'STEXT_UNI' => 'blob',
187 'TEXT' => 'blob',
188 'TEXT_UNI' => 'blob',
189 'MTEXT' => 'mediumblob',
190 'MTEXT_UNI' => 'mediumblob',
191 'TIMESTAMP' => 'int(11) UNSIGNED',
192 'DECIMAL' => 'decimal(5,2)',
193 'VCHAR_UNI' => 'blob',
194 'VCHAR_UNI:'=> array('varbinary(%d)', 'limit' => array('mult', 3, 255, 'blob')),
195 'VCHAR_CI' => 'blob',
196 'VARBINARY' => 'varbinary(255)',
197 ),
198
199 'firebird' => array(
200 'INT:' => 'INTEGER',
201 'BINT' => 'DOUBLE PRECISION',
202 'UINT' => 'INTEGER',
203 'UINT:' => 'INTEGER',
204 'TINT:' => 'INTEGER',
205 'USINT' => 'INTEGER',
206 'BOOL' => 'INTEGER',
207 'VCHAR' => 'VARCHAR(255) CHARACTER SET NONE',
208 'VCHAR:' => 'VARCHAR(%d) CHARACTER SET NONE',
209 'CHAR:' => 'CHAR(%d) CHARACTER SET NONE',
210 'XSTEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
211 'STEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
212 'TEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
213 'MTEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
214 'XSTEXT_UNI'=> 'VARCHAR(100) CHARACTER SET UTF8',
215 'STEXT_UNI' => 'VARCHAR(255) CHARACTER SET UTF8',
216 'TEXT_UNI' => 'BLOB SUB_TYPE TEXT CHARACTER SET UTF8',
217 'MTEXT_UNI' => 'BLOB SUB_TYPE TEXT CHARACTER SET UTF8',
218 'TIMESTAMP' => 'INTEGER',
219 'DECIMAL' => 'DOUBLE PRECISION',
220 'VCHAR_UNI' => 'VARCHAR(255) CHARACTER SET UTF8',
221 'VCHAR_UNI:'=> 'VARCHAR(%d) CHARACTER SET UTF8',
222 'VCHAR_CI' => 'VARCHAR(255) CHARACTER SET UTF8',
223 'VARBINARY' => 'CHAR(255) CHARACTER SET NONE',
224 ),
225
226 'mssql' => array(
227 'INT:' => '[int]',
228 'BINT' => '[float]',
229 'UINT' => '[int]',
230 'UINT:' => '[int]',
231 'TINT:' => '[int]',
232 'USINT' => '[int]',
233 'BOOL' => '[int]',
234 'VCHAR' => '[varchar] (255)',
235 'VCHAR:' => '[varchar] (%d)',
236 'CHAR:' => '[char] (%d)',
237 'XSTEXT' => '[varchar] (1000)',
238 'STEXT' => '[varchar] (3000)',
239 'TEXT' => '[varchar] (8000)',
240 'MTEXT' => '[text]',
241 'XSTEXT_UNI'=> '[varchar] (100)',
242 'STEXT_UNI' => '[varchar] (255)',
243 'TEXT_UNI' => '[varchar] (4000)',
244 'MTEXT_UNI' => '[text]',
245 'TIMESTAMP' => '[int]',
246 'DECIMAL' => '[float]',
247 'VCHAR_UNI' => '[varchar] (255)',
248 'VCHAR_UNI:'=> '[varchar] (%d)',
249 'VCHAR_CI' => '[varchar] (255)',
250 'VARBINARY' => '[varchar] (255)',
251 ),
252
253 'oracle' => array(
254 'INT:' => 'number(%d)',
255 'BINT' => 'number(20)',
256 'UINT' => 'number(8)',
257 'UINT:' => 'number(%d)',
258 'TINT:' => 'number(%d)',
259 'USINT' => 'number(4)',
260 'BOOL' => 'number(1)',
261 'VCHAR' => 'varchar2(255)',
262 'VCHAR:' => 'varchar2(%d)',
263 'CHAR:' => 'char(%d)',
264 'XSTEXT' => 'varchar2(1000)',
265 'STEXT' => 'varchar2(3000)',
266 'TEXT' => 'clob',
267 'MTEXT' => 'clob',
268 'XSTEXT_UNI'=> 'varchar2(300)',
269 'STEXT_UNI' => 'varchar2(765)',
270 'TEXT_UNI' => 'clob',
271 'MTEXT_UNI' => 'clob',
272 'TIMESTAMP' => 'number(11)',
273 'DECIMAL' => 'number(5, 2)',
274 'VCHAR_UNI' => 'varchar2(765)',
275 'VCHAR_UNI:'=> array('varchar2(%d)', 'limit' => array('mult', 3, 765, 'clob')),
276 'VCHAR_CI' => 'varchar2(255)',
277 'VARBINARY' => 'raw(255)',
278 ),
279
280 'sqlite' => array(
281 'INT:' => 'int(%d)',
282 'BINT' => 'bigint(20)',
283 'UINT' => 'INTEGER UNSIGNED', //'mediumint(8) UNSIGNED',
284 'UINT:' => 'INTEGER UNSIGNED', // 'int(%d) UNSIGNED',
285 'TINT:' => 'tinyint(%d)',
286 'USINT' => 'INTEGER UNSIGNED', //'mediumint(4) UNSIGNED',
287 'BOOL' => 'INTEGER UNSIGNED', //'tinyint(1) UNSIGNED',
288 'VCHAR' => 'varchar(255)',
289 'VCHAR:' => 'varchar(%d)',
290 'CHAR:' => 'char(%d)',
291 'XSTEXT' => 'text(65535)',
292 'STEXT' => 'text(65535)',
293 'TEXT' => 'text(65535)',
294 'MTEXT' => 'mediumtext(16777215)',
295 'XSTEXT_UNI'=> 'text(65535)',
296 'STEXT_UNI' => 'text(65535)',
297 'TEXT_UNI' => 'text(65535)',
298 'MTEXT_UNI' => 'mediumtext(16777215)',
299 'TIMESTAMP' => 'INTEGER UNSIGNED', //'int(11) UNSIGNED',
300 'DECIMAL' => 'decimal(5,2)',
301 'VCHAR_UNI' => 'varchar(255)',
302 'VCHAR_UNI:'=> 'varchar(%d)',
303 'VCHAR_CI' => 'varchar(255)',
304 'VARBINARY' => 'blob',
305 ),
306
307 'postgres' => array(
308 'INT:' => 'INT4',
309 'BINT' => 'INT8',
310 'UINT' => 'INT4', // unsigned
311 'UINT:' => 'INT4', // unsigned
312 'USINT' => 'INT2', // unsigned
313 'BOOL' => 'INT2', // unsigned
314 'TINT:' => 'INT2',
315 'VCHAR' => 'varchar(255)',
316 'VCHAR:' => 'varchar(%d)',
317 'CHAR:' => 'char(%d)',
318 'XSTEXT' => 'varchar(1000)',
319 'STEXT' => 'varchar(3000)',
320 'TEXT' => 'varchar(8000)',
321 'MTEXT' => 'TEXT',
322 'XSTEXT_UNI'=> 'varchar(100)',
323 'STEXT_UNI' => 'varchar(255)',
324 'TEXT_UNI' => 'varchar(4000)',
325 'MTEXT_UNI' => 'TEXT',
326 'TIMESTAMP' => 'INT4', // unsigned
327 'DECIMAL' => 'decimal(5,2)',
328 'VCHAR_UNI' => 'varchar(255)',
329 'VCHAR_UNI:'=> 'varchar(%d)',
330 'VCHAR_CI' => 'varchar_ci',
331 'VARBINARY' => 'bytea',
332 ),
333 );
334
335 // A list of types being unsigned for better reference in some db's
336 $unsigned_types = array('UINT', 'UINT:', 'USINT', 'BOOL', 'TIMESTAMP');
337
338 // Only an example, but also commented out
339 $database_update_info = array(
340 // Changes from 3.0.RC2 to the next version
341 '3.0.RC2' => array(
342 // Change the following columns
343 'change_columns' => array(
344 BANLIST_TABLE => array(
345 'ban_reason' => array('VCHAR_UNI', ''),
346 'ban_give_reason' => array('VCHAR_UNI', ''),
347 ),
348 ),
349 ),
350 // Changes from 3.0.RC3 to the next version
351 '3.0.RC3' => array(
352 // Change the following columns
353 'change_columns' => array(
354 BANLIST_TABLE => array(
355 'ban_reason' => array('VCHAR_UNI', ''),
356 'ban_give_reason' => array('VCHAR_UNI', ''),
357 ),
358 STYLES_TABLE => array(
359 'style_id' => array('USINT', 0),
360 'template_id' => array('USINT', 0),
361 'theme_id' => array('USINT', 0),
362 'imageset_id' => array('USINT', 0),
363 ),
364 STYLES_TEMPLATE_TABLE => array(
365 'template_id' => array('USINT', 0),
366 ),
367 STYLES_TEMPLATE_DATA_TABLE => array(
368 'template_id' => array('USINT', 0),
369 ),
370 STYLES_THEME_TABLE => array(
371 'theme_id' => array('USINT', 0),
372 ),
373 STYLES_IMAGESET_TABLE => array(
374 'imageset_id' => array('USINT', 0),
375 ),
376 STYLES_IMAGESET_DATA_TABLE => array(
377 'imageset_id' => array('USINT', 0),
378 ),
379 USERS_TABLE => array(
380 'user_style' => array('USINT', 0),
381 ),
382 FORUMS_TABLE => array(
383 'forum_style' => array('USINT', 0),
384 ),
385 GROUPS_TABLE => array(
386 'group_avatar_type' => array('TINT:2', 0),
387 'group_avatar_width' => array('USINT', 0),
388 'group_avatar_height' => array('USINT', 0),
389 ),
390 ),
391 ),
392 // Changes from 3.0.RC4 to the next version
393 '3.0.RC4' => array(
394 // Change the following columns
395 'change_columns' => array(
396 STYLES_TABLE => array(
397 'style_id' => array('USINT', NULL, 'auto_increment'),
398 'template_id' => array('USINT', 0),
399 'theme_id' => array('USINT', 0),
400 'imageset_id' => array('USINT', 0),
401 ),
402 STYLES_TEMPLATE_TABLE => array(
403 'template_id' => array('USINT', NULL, 'auto_increment'),
404 ),
405 STYLES_TEMPLATE_DATA_TABLE => array(
406 'template_id' => array('USINT', 0),
407 ),
408 STYLES_THEME_TABLE => array(
409 'theme_id' => array('USINT', NULL, 'auto_increment'),
410 ),
411 STYLES_IMAGESET_TABLE => array(
412 'imageset_id' => array('USINT', NULL, 'auto_increment'),
413 ),
414 STYLES_IMAGESET_DATA_TABLE => array(
415 'imageset_id' => array('USINT', 0),
416 ),
417 USERS_TABLE => array(
418 'user_style' => array('USINT', 0),
419 ),
420 FORUMS_TABLE => array(
421 'forum_style' => array('USINT', 0),
422 ),
423 GROUPS_TABLE => array(
424 'group_avatar_width' => array('USINT', 0),
425 'group_avatar_height' => array('USINT', 0),
426 ),
427 ),
428 ),
429 // Changes from 3.0.RC5 to the next version
430 '3.0.RC5' => array(
431 // Add the following columns
432 'add_columns' => array(
433 USERS_TABLE => array(
434 'user_form_salt' => array('VCHAR_UNI:32', ''),
435 ),
436 ),
437 // Change the following columns
438 'change_columns' => array(
439 POSTS_TABLE => array(
440 'bbcode_uid' => array('VCHAR:8', ''),
441 ),
442 PRIVMSGS_TABLE => array(
443 'bbcode_uid' => array('VCHAR:8', ''),
444 ),
445 USERS_TABLE => array(
446 'user_sig_bbcode_uid' => array('VCHAR:8', ''),
447 ),
448 ),
449 ),
450 // Changes from 3.0.RC6 to the next version
451 '3.0.RC6' => array(
452 // Change the following columns
453 'change_columns' => array(
454 FORUMS_TABLE => array(
455 'forum_desc_uid' => array('VCHAR:8', ''),
456 'forum_rules_uid' => array('VCHAR:8', ''),
457 ),
458 GROUPS_TABLE => array(
459 'group_desc_uid' => array('VCHAR:8', ''),
460 ),
461 USERS_TABLE => array(
462 'user_newpasswd' => array('VCHAR_UNI:40', ''),
463 ),
464 ),
465 ),
466 );
467
468 // Determine mapping database type
469 switch ($db->sql_layer)
470 {
471 case 'mysql':
472 $map_dbms = 'mysql_40';
473 break;
474
475 case 'mysql4':
476 if (version_compare($db->mysql_version, '4.1.3', '>='))
477 {
478 $map_dbms = 'mysql_41';
479 }
480 else
481 {
482 $map_dbms = 'mysql_40';
483 }
484 break;
485
486 case 'mysqli':
487 $map_dbms = 'mysql_41';
488 break;
489
490 case 'mssql':
491 case 'mssql_odbc':
492 $map_dbms = 'mssql';
493 break;
494
495 default:
496 $map_dbms = $db->sql_layer;
497 break;
498 }
499
500 $error_ary = array();
501 $errored = false;
502
503 header('Content-type: text/html; charset=UTF-8');
504
505 ?>
506 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
507 <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']; ?>">
508 <head>
509
510 <meta http-equiv="content-type" content="text/html; charset=UTF-8" />
511 <meta http-equiv="content-language" content="<?php echo $lang['USER_LANG']; ?>" />
512 <meta http-equiv="content-style-type" content="text/css" />
513 <meta http-equiv="imagetoolbar" content="no" />
514
515 <title><?php echo $lang['UPDATING_TO_LATEST_STABLE']; ?></title>
516
517 <link href="../adm/style/admin.css" rel="stylesheet" type="text/css" media="screen" />
518
519 </head>
520
521 <body>
522 <div id="wrap">
523 <div id="page-header">&nbsp;</div>
524
525 <div id="page-body">
526 <div id="acp">
527 <div class="panel">
528 <span class="corners-top"><span></span></span>
529 <div id="content">
530 <div id="main">
531
532 <h1><?php echo $lang['UPDATING_TO_LATEST_STABLE']; ?></h1>
533
534 <br />
535
536 <p><?php echo $lang['DATABASE_TYPE']; ?> :: <strong><?php echo $db->sql_layer; ?></strong><br />
537 <?php
538
539 // To let set_config() calls succeed, we need to make the config array available globally
540 $config = array();
541 $sql = 'SELECT *
542 FROM ' . CONFIG_TABLE;
543 $result = $db->sql_query($sql);
544
545 while ($row = $db->sql_fetchrow($result))
546 {
547 $config[$row['config_name']] = $row['config_value'];
548 }
549 $db->sql_freeresult($result);
550
551 echo $lang['PREVIOUS_VERSION'] . ' :: <strong>' . $config['version'] . '</strong><br />';
552 echo $lang['UPDATED_VERSION'] . ' :: <strong>' . $updates_to_version . '</strong></p>';
553
554 $current_version = str_replace('rc', 'RC', strtolower($config['version']));
555 $latest_version = str_replace('rc', 'RC', strtolower($updates_to_version));
556 $orig_version = $config['version'];
557
558 // If the latest version and the current version are 'unequal', we will update the version_update_from, else we do not update anything.
559 if ($inline_update)
560 {
561 if ($current_version !== $latest_version)
562 {
563 set_config('version_update_from', $orig_version);
564 }
565 }
566 else
567 {
568 // If not called from the update script, we will actually remove the traces
569 $db->sql_query('DELETE FROM ' . CONFIG_TABLE . " WHERE config_name = 'version_update_from'");
570 }
571
572 // Checks/Operations that have to be completed prior to starting the update itself
573 $exit = false;
574 if (version_compare($current_version, '3.0.RC4', '<='))
575 {
576 // Define missing language entries...
577 if (!isset($lang['CLEANING_USERNAMES']))
578 {
579 $lang = array_merge($lang, array(
580 'CLEANING_USERNAMES' => 'Cleaning usernames',
581 'LONG_SCRIPT_EXECUTION' => 'Please note that this can take a while... Please do not stop the script.',
582 'CHANGE_CLEAN_NAMES' => 'The method used to make sure a username is not used by multiple users has been changed. There are some users which have the same name when compared with the new method. You have to delete or rename these users to make sure that each name is only used by one user before you can proceed.',
583 'USER_ACTIVE' => 'Active user',
584 'USER_INACTIVE' => 'Inactive user',
585 'BOT' => 'Spider/Robot',
586 'UPDATE_REQUIRES_FILE' => 'The updater requires that the following file is present: %s',
587
588 'DELETE_USER_REMOVE' => 'Delete user and remove posts',
589 'DELETE_USER_RETAIN' => 'Delete user but keep posts',
590 'EDIT_USERNAME' => 'Edit username',
591 'KEEP_OLD_NAME' => 'Keep username',
592 'NEW_USERNAME' => 'New username',
593 ));
594 }
595 ?>
596 <br /><br />
597
598 <h1><?php echo $lang['CLEANING_USERNAMES']; ?></h1>
599
600 <br />
601
602 <?php
603 flush();
604
605 $submit = (isset($_POST['resolve_conflicts'])) ? true : false;
606 $modify_users = request_var('modify_users', array(0 => ''));
607 $new_usernames = request_var('new_usernames', array(0 => ''), true);
608
609 if (!class_exists('utf_new_normalizer'))
610 {
611 if (!file_exists($phpbb_root_path . 'install/data/new_normalizer.' . $phpEx))
612 {
613 global $lang;
614 trigger_error(sprintf($lang['UPDATE_REQUIRES_FILE'], $phpbb_root_path . 'install/data/new_normalizer.' . $phpEx), E_USER_ERROR);
615 }
616 include($phpbb_root_path . 'install/data/new_normalizer.' . $phpEx);
617 }
618
619 // the admin decided to change some usernames
620 if (sizeof($modify_users) && $submit)
621 {
622 $sql = 'SELECT user_id, username, user_type
623 FROM ' . USERS_TABLE . '
624 WHERE ' . $db->sql_in_set('user_id', array_keys($modify_users));
625 $result = $db->sql_query($sql);
626
627 $users = 0;
628 while ($row = $db->sql_fetchrow($result))
629 {
630 $users++;
631 $user_id = (int) $row['user_id'];
632
633 if (isset($modify_users[$user_id]))
634 {
635 $row['action'] = $modify_users[$user_id];
636 $modify_users[$user_id] = $row;
637 }
638 }
639 $db->sql_freeresult($result);
640
641 // only if all ids really existed
642 if (sizeof($modify_users) == $users)
643 {
644 $user->data['user_id'] = ANONYMOUS;
645 include($phpbb_root_path . 'includes/functions_user.' . $phpEx);
646 foreach ($modify_users as $user_id => $row)
647 {
648 switch ($row['action'])
649 {
650 case 'edit':
651 if (isset($new_usernames[$user_id]))
652 {
653 $data = array('username' => utf8_new_normalize_nfc($new_usernames[$user_id]));
654 // Need to update config, forum, topic, posting, messages, etc.
655 if ($data['username'] != $row['username'])
656 {
657 $check_ary = array('username' => array(
658 array('string', false, $config['min_name_chars'], $config['max_name_chars']),
659 array('username'),
660 ));
661 // need a little trick for this to work properly
662 $user->data['username_clean'] = utf8_clean_string($data['username']) . 'a';
663 $errors = validate_data($data, $check_ary);
664
665 if ($errors)
666 {
667 include($phpbb_root_path . 'language/' . $language . '/ucp.' . $phpEx);
668 echo '<div class="errorbox">';
669 foreach ($errors as $error)
670 {
671 echo '<p>' . $lang[$error] . '</p>';
672 }
673 echo '</div>';
674 }
675
676 if (!$errors)
677 {
678 $sql = 'UPDATE ' . USERS_TABLE . '
679 SET ' . $db->sql_build_array('UPDATE', array(
680 'username' => $data['username'],
681 'username_clean' => utf8_clean_string($data['username'])
682 )) . '
683 WHERE user_id = ' . $user_id;
684 $db->sql_query($sql);
685
686 add_log('user', $user_id, 'LOG_USER_UPDATE_NAME', $row['username'], $data['username']);
687 user_update_name($row['username'], $data['username']);
688 }
689 }
690 }
691 break;
692
693 case 'delete_retain':
694 case 'delete_remove':
695 if ($user_id != ANONYMOUS && $row['user_type'] != USER_FOUNDER)
696 {
697 user_delete(substr($row['action'], 7), $user_id, $row['username']);
698 add_log('admin', 'LOG_USER_DELETED', $row['username']);
699 }
700 break;
701 }
702 }
703 }
704 }
705 ?>
706
707 <p><?php echo $lang['LONG_SCRIPT_EXECUTION']; ?></p>
708 <p><?php echo $lang['PROGRESS']; ?> :: <strong>
709
710 <?php
711 flush();
712
713 // after RC3 a different utf8_clean_string function is used, this requires that
714 // the unique column username_clean is recalculated, during this recalculation
715 // duplicates might be created. Since the column has to be unique such usernames
716 // must not exist. We need identify them and let the admin decide what to do
717 // about them.
718 $sql = 'SELECT user_id, username, username_clean
719 FROM ' . USERS_TABLE . '
720 ORDER BY user_id ASC';
721 $result = $db->sql_query($sql);
722
723 $colliding_users = $found_names = array();
724 $echos = 0;
725
726 while ($row = $db->sql_fetchrow($result))
727 {
728 // Calculate the new clean name. If it differs from the old one we need
729 // to make sure there is no collision
730 $clean_name = utf8_new_clean_string($row['username']);
731
732 if ($clean_name != $row['username_clean'])
733 {
734 // Check if there would be a collission, if not put it up for changing
735 $user_id = (int) $row['user_id'];
736
737 // If this clean name was not the result of another user already ...
738 if (!isset($found_names[$clean_name]))
739 {
740 // then we need to figure out whether there are any other users
741 // who already had this clean name with the old version
742 $sql = 'SELECT user_id, username
743 FROM ' . USERS_TABLE . '
744 WHERE username_clean = \'' . $db->sql_escape($clean_name) . '\'';
745 $result2 = $db->sql_query($sql);
746
747 $user_ids = array($user_id);
748 while ($row = $db->sql_fetchrow($result2))
749 {
750 // For not trimmed entries this could happen, yes. ;)
751 if ($row['user_id'] == $user_id)
752 {
753 continue;
754 }
755
756 // Make sure this clean name will still be the same with the
757 // new function. If it is, then we have to add it to the list
758 // of user ids for this clean name
759 if (utf8_new_clean_string($row['username']) == $clean_name)
760 {
761 $user_ids[] = (int) $row['user_id'];
762 }
763 }
764 $db->sql_freeresult($result2);
765
766 // if we already found a collision save it
767 if (sizeof($user_ids) > 1)
768 {
769 $colliding_users[$clean_name] = $user_ids;
770 $found_names[$clean_name] = true;
771 }
772 else
773 {
774 // otherwise just mark this name as found
775 $found_names[$clean_name] = $user_id;
776 }
777 }
778 // Else, if we already found the username
779 else
780 {
781 // If the value in the found_names lookup table is only true ...
782 if ($found_names[$clean_name] === true)
783 {
784 // then the actual data was already added to $colliding_users
785 // and we only need to append the user_id
786 $colliding_users[$clean_name][] = $user_id;
787 }
788 else
789 {
790 // otherwise it still keeps the first user_id for this name
791 // and we need to move the data to $colliding_users, and set
792 // the value in the found_names lookup table to true, so
793 // following users will directly be appended to $colliding_users
794 $colliding_users[$clean_name] = array($found_names[$clean_name], $user_id);
795 $found_names[$clean_name] = true;
796 }
797 }
798 }
799
800 if (($echos % 1000) == 0)
801 {
802 echo '.';
803 flush();
804 }
805 $echos++;
806 }
807 $db->sql_freeresult($result);
808
809 _write_result(false, $errored, $error_ary);
810
811 // now retrieve all information about the users and let the admin decide what to do
812 if (sizeof($colliding_users))
813 {
814 $exit = true;
815 include($phpbb_root_path . 'includes/functions_display.' . $phpEx);
816 include($phpbb_root_path . 'language/' . $language . '/memberlist.' . $phpEx);
817 include($phpbb_root_path . 'language/' . $language . '/acp/users.' . $phpEx);
818
819 // link a few things to the correct place so we don't get any problems
820 $user->lang = &$lang;
821 $user->data['user_id'] = ANONYMOUS;
822 $user->date_format = $config['default_dateformat'];
823
824 // a little trick to get all user_ids
825 $user_ids = call_user_func_array('array_merge', array_values($colliding_users));
826
827 $sql = 'SELECT session_user_id, MAX(session_time) AS session_time
828 FROM ' . SESSIONS_TABLE . '
829 WHERE session_time >= ' . (time() - $config['session_length']) . '
830 AND ' . $db->sql_in_set('session_user_id', $user_ids) . '
831 GROUP BY session_user_id';
832 $result = $db->sql_query($sql);
833
834 $session_times = array();
835 while ($row = $db->sql_fetchrow($result))
836 {
837 $session_times[$row['session_user_id']] = $row['session_time'];
838 }
839 $db->sql_freeresult($result);
840
841 $sql = 'SELECT *
842 FROM ' . USERS_TABLE . '
843 WHERE ' . $db->sql_in_set('user_id', $user_ids);
844 $result = $db->sql_query($sql);
845
846 $users = array();
847 while ($row = $db->sql_fetchrow($result))
848 {
849 if (isset($session_times[$row['user_id']]))
850 {
851 $row['session_time'] = $session_times[$row['user_id']];
852 }
853 else
854 {
855 $row['session_time'] = 0;
856 }
857 $users[(int) $row['user_id']] = $row;
858 }
859 $db->sql_freeresult($result);
860 unset($session_times);
861
862 // now display a table with all users, some information about them and options
863 // for the admin: keep name, change name (with text input) or delete user
864 $u_action = "database_update.$phpEx?language=$language&amp;type=$inline_update";
865 ?>
866 <br /><br />
867
868 <p><?php echo $lang['CHANGE_CLEAN_NAMES']; ?></p>
869 <form id="change_clean_names" method="post" action="<?php echo $u_action; ?>">
870
871
872 <?php
873 foreach ($colliding_users as $clean_name => $user_ids)
874 {
875 ?>
876 <fieldset class="tabulated">
877 <table>
878 <caption><?php echo sprintf($lang['COLLIDING_CLEAN_USERNAME'], $clean_name); ?></caption>
879 <thead>
880 <tr>
881 <th><?php echo $lang['RANK']; ?> <?php echo $lang['USERNAME']; ?></th>
882 <th><?php echo $lang['POSTS']; ?></th>
883 <th><?php echo $lang['INFORMATION']; ?></th>
884 <th><?php echo $lang['JOINED']; ?></th>
885 <th><?php echo $lang['LAST_ACTIVE']; ?></th>
886 <th><?php echo $lang['ACTION']; ?></th>
887 <th><?php echo $lang['NEW_USERNAME']; ?></th>
888 </tr>
889 </thead>
890 <tbody>
891 <?php
892 foreach ($user_ids as $i => $user_id)
893 {
894 $row = $users[$user_id];
895
896 $rank_title = $rank_img = '';
897 get_user_rank($row['user_rank'], $row['user_posts'], $rank_title, $rank_img, $rank_img_src);
898
899 $last_visit = (!empty($row['session_time'])) ? $row['session_time'] : $row['user_lastvisit'];
900
901 $info = '';
902 switch ($row['user_type'])
903 {
904 case USER_INACTIVE:
905 $info .= $lang['USER_INACTIVE'];
906 break;
907
908 case USER_IGNORE:
909 $info .= $lang['BOT'];
910 break;
911
912 case USER_FOUNDER:
913 $info .= $lang['FOUNDER'];
914 break;
915
916 default:
917 $info .= $lang['USER_ACTIVE'];
918 }
919
920 if ($user_id == ANONYMOUS)
921 {
922 $info = $lang['GUEST'];
923 }
924 ?>
925 <tr class="bg<?php echo ($i % 2) + 1; ?>">
926 <td>
927 <span class="rank-img"><?php echo ($rank_img) ? $rank_img : $rank_title; ?></span><br />
928 <?php echo get_username_string('full', $row['user_id'], $row['username'], $row['user_colour']); ?>
929 </td>
930 <td class="posts"><?php echo $row['user_posts']; ?></td>
931 <td class="info"><?php echo $info; ?></td>
932 <td><?php echo $user->format_date($row['user_regdate']) ?></td>
933 <td><?php echo (empty($last_visit)) ? ' - ' : $user->format_date($last_visit); ?>&nbsp;</td>
934 <td>
935 <label><input type="radio" class="radio" id="keep_user_<?php echo $user_id; ?>" name="modify_users[<?php echo $user_id; ?>]" value="keep" checked="checked" /> <?php echo $lang['KEEP_OLD_NAME']; ?></label><br />
936 <label><input type="radio" class="radio" id="edit_user_<?php echo $user_id; ?>" name="modify_users[<?php echo $user_id; ?>]" value="edit" /> <?php echo $lang['EDIT_USERNAME']; ?></label><br />
937 <?php
938 // some users must not be deleted
939 if ($user_id != ANONYMOUS && $row['user_type'] != USER_FOUNDER)
940 {
941 ?>
942 <label><input type="radio" class="radio" id="delete_user_retain_<?php echo $user_id; ?>" name="modify_users[<?php echo $user_id; ?>]" value="delete_retain" /> <?php echo $lang['DELETE_USER_RETAIN']; ?></label><br />
943 <label><input type="radio" class="radio" id="delete_user_remove_<?php echo $user_id; ?>" name="modify_users[<?php echo $user_id; ?>]" value="delete_remove" /> <?php echo $lang['DELETE_USER_REMOVE']; ?></label>
944 <?php
945 }
946 ?>
947 </td>
948 <td>
949 <input id="new_username_<?php echo $user_id; ?>" type="text" name="new_usernames[<?php echo $user_id; ?>]" value="<?php echo $row['username']; ?>" />
950 </td>
951 </tr>
952 <?php
953 }
954 ?>
955 </tbody>
956 </table>
957 </fieldset>
958 <?php
959 }
960 ?>
961 <p class="quick">
962 <input class="button2" id="resolve_conflicts" type="submit" name="resolve_conflicts" value="<?php echo $lang['SUBMIT']; ?>" />
963 </p>
964 </form>
965 <?php
966 }
967 else if (sizeof($found_names))
968 {
969 $sql = 'SELECT user_id, username, username_clean
970 FROM ' . USERS_TABLE . '
971 WHERE ' . $db->sql_in_set('user_id', array_values($found_names));
972 $result = $db->sql_query($sql);
973
974 $found_names = array();
975 while ($row = $db->sql_fetchrow($result))
976 {
977 $clean_name = utf8_new_clean_string($row['username']);
978
979 if ($clean_name != $row['username_clean'])
980 {
981 $user_id = (int) $row['user_id'];
982 $found_names[$user_id] = $clean_name;
983
984 // impossible unique clean name
985 $sql = 'UPDATE ' . USERS_TABLE . "
986 SET username_clean = ' {$user_id}'
987 WHERE user_id = {$user_id}";
988 $db->sql_query($sql);
989 }
990 }
991 $db->sql_freeresult($result);
992
993 foreach ($found_names as $user_id => $clean_name)
994 {
995 $sql = 'UPDATE ' . USERS_TABLE . '
996 SET username_clean = \'' . $db->sql_escape($clean_name) . '\'
997 WHERE user_id = ' . $user_id;
998 $db->sql_query($sql);
999 }
1000 }
1001 unset($found_names);
1002 unset($colliding_users);
1003 }
1004
1005 if ($exit)
1006 {
1007 ?>
1008
1009 </div>
1010 </div>
1011 <span class="corners-bottom"><span></span></span>
1012 </div>
1013 </div>
1014 </div>
1015
1016 <div id="page-footer">
1017 Powered by <a href="http://www.phpbb.com/">phpBB</a> &copy; 2000, 2002, 2005, 2007 phpBB Group
1018 </div>
1019 </div>
1020
1021 </body>
1022 </html>
1023
1024 <?php
1025 if (function_exists('exit_handler'))
1026 {
1027 exit_handler();
1028 }
1029 }
1030
1031 // Schema updates
1032 ?>
1033 <br /><br />
1034
1035 <h1><?php echo $lang['UPDATE_DATABASE_SCHEMA']; ?></h1>
1036
1037 <br />
1038 <p><?php echo $lang['PROGRESS']; ?> :: <strong>
1039
1040 <?php
1041
1042 flush();
1043
1044 // We go through the schema changes from the lowest to the highest version
1045 // We skip those versions older than the current version
1046 $no_updates = true;
1047 foreach ($database_update_info as $version => $schema_changes)
1048 {
1049 if (version_compare($version, $current_version, '<'))
1050 {
1051 continue;
1052 }
1053
1054 if (!sizeof($schema_changes))
1055 {
1056 continue;
1057 }
1058
1059 $no_updates = false;
1060
1061 // Change columns?
1062 if (!empty($schema_changes['change_columns']))
1063 {
1064 foreach ($schema_changes['change_columns'] as $table => $columns)
1065 {
1066 foreach ($columns as $column_name => $column_data)
1067 {
1068 sql_column_change($map_dbms, $table, $column_name, $column_data);
1069 }
1070 }
1071 }
1072
1073 // Add columns?
1074 if (!empty($schema_changes['add_columns']))
1075 {
1076 foreach ($schema_changes['add_columns'] as $table => $columns)
1077 {
1078 foreach ($columns as $column_name => $column_data)
1079 {
1080 // Only add the column if it does not exist yet
1081 if (!column_exists($map_dbms, $table, $column_name))
1082 {
1083 sql_column_add($map_dbms, $table, $column_name, $column_data);
1084 }
1085 }
1086 }
1087 }
1088
1089 // Remove keys?
1090 if (!empty($schema_changes['drop_keys']))
1091 {
1092 foreach ($schema_changes['drop_keys'] as $table => $indexes)
1093 {
1094 foreach ($indexes as $index_name)
1095 {
1096 sql_index_drop($map_dbms, $index_name, $table);
1097 }
1098 }
1099 }
1100
1101 // Drop columns?
1102 if (!empty($schema_changes['drop_columns']))
1103 {
1104 foreach ($schema_changes['drop_columns'] as $table => $columns)
1105 {
1106 foreach ($columns as $column)
1107 {
1108 sql_column_remove($map_dbms, $table, $column);
1109 }
1110 }
1111 }
1112
1113 // Add primary keys?
1114 if (!empty($schema_changes['add_primary_keys']))
1115 {
1116 foreach ($schema_changes['add_primary_keys'] as $table => $columns)
1117 {
1118 sql_create_primary_key($map_dbms, $table, $columns);
1119 }
1120 }
1121
1122 // Add unqiue indexes?
1123 if (!empty($schema_changes['add_unique_index']))
1124 {
1125 foreach ($schema_changes['add_unique_index'] as $table => $index_array)
1126 {
1127 foreach ($index_array as $index_name => $column)
1128 {
1129 sql_create_unique_index($map_dbms, $index_name, $table, $column);
1130 }
1131 }
1132 }
1133
1134 // Add indexes?
1135 if (!empty($schema_changes['add_index']))
1136 {
1137 foreach ($schema_changes['add_index'] as $table => $index_array)
1138 {
1139 foreach ($index_array as $index_name => $column)
1140 {
1141 sql_create_index($map_dbms, $index_name, $table, $column);
1142 }
1143 }
1144 }
1145 }
1146
1147 _write_result($no_updates, $errored, $error_ary);
1148
1149 // Data updates
1150 $error_ary = array();
1151 $errored = $no_updates = false;
1152
1153 ?>
1154
1155 <br /><br />
1156 <h1><?php echo $lang['UPDATING_DATA']; ?></h1>
1157 <br />
1158 <p><?php echo $lang['PROGRESS']; ?> :: <strong>
1159
1160 <?php
1161
1162 flush();
1163
1164 $no_updates = true;
1165
1166 // some code magic
1167 if (version_compare($current_version, '3.0.RC2', '<='))
1168 {
1169 $smileys = array();
1170 $sql = 'SELECT smiley_id, code
1171 FROM ' . SMILIES_TABLE;
1172
1173 $result = $db->sql_query($sql);
1174
1175 while ($row = $db->sql_fetchrow($result))
1176 {
1177 $smileys[$row['smiley_id']] = $row['code'];
1178 }
1179 $db->sql_freeresult($result);
1180
1181 foreach ($smileys as $id => $code)
1182 {
1183 // 2.0 only entitized lt and gt; We need to do something about double quotes.
1184 if (strchr($code, '"') === false)
1185 {
1186 continue;
1187 }
1188
1189 $new_code = str_replace('&amp;', '&', $code);
1190 $new_code = str_replace('&lt;', '<', $new_code);
1191 $new_code = str_replace('&gt;', '>', $new_code);
1192 $new_code = utf8_htmlspecialchars($new_code);
1193
1194 $sql = 'UPDATE ' . SMILIES_TABLE . '
1195 SET code = \'' . $db->sql_escape($new_code) . '\'
1196 WHERE smiley_id = ' . (int) $id;
1197 $db->sql_query($sql);
1198 }
1199
1200 $index_list = sql_list_index($map_dbms, ACL_ROLES_DATA_TABLE);
1201
1202 if (in_array('ath_opt_id', $index_list))
1203 {
1204 sql_index_drop($map_dbms, 'ath_opt_id', ACL_ROLES_DATA_TABLE);
1205 sql_create_index($map_dbms, 'ath_op_id', ACL_ROLES_DATA_TABLE, array('auth_option_id'));
1206 }
1207
1208 $no_updates = false;
1209 }
1210
1211 if (version_compare($current_version, '3.0.RC3', '<='))
1212 {
1213 if ($map_dbms === 'postgres')
1214 {
1215 $sql = "SELECT SETVAL('" . FORUMS_TABLE . "_seq',(select case when max(forum_id)>0 then max(forum_id)+1 else 1 end from " . FORUMS_TABLE . '));';
1216 _sql($sql, $errored, $error_ary);
1217 }
1218
1219 // we check for:
1220 // ath_opt_id
1221 // ath_op_id
1222 // ACL_ROLES_DATA_TABLE_ath_opt_id
1223 // we want ACL_ROLES_DATA_TABLE_ath_op_id
1224
1225 $table_index_fix = array(
1226 ACL_ROLES_DATA_TABLE => array(
1227 'ath_opt_id' => 'ath_op_id',
1228 'ath_op_id' => 'ath_op_id',
1229 ACL_ROLES_DATA_TABLE . '_ath_opt_id' => 'ath_op_id'
1230 ),
1231 STYLES_IMAGESET_DATA_TABLE => array(
1232 'i_id' => 'i_d',
1233 'i_d' => 'i_d',
1234 STYLES_IMAGESET_DATA_TABLE . '_i_id' => 'i_d'
1235 )
1236 );
1237
1238 // we need to create some indicies...
1239 $needed_creation = array();
1240
1241 foreach ($table_index_fix as $table_name => $index_info)
1242 {
1243 $index_list = sql_list_fake($map_dbms, $table_name);
1244 foreach ($index_info as $bad_index => $good_index)
1245 {
1246 if (in_array($bad_index, $index_list))
1247 {
1248 // mysql is actually OK, it won't get a hand in this crud
1249 switch ($map_dbms)
1250 {
1251 // last version, mssql had issues with index removal
1252 case 'mssql':
1253 $sql = 'DROP INDEX ' . $table_name . '.' . $bad_index;
1254 _sql($sql, $errored, $error_ary);
1255 break;
1256
1257 // last version, firebird, oracle, postgresql and sqlite all got bad index names
1258 // we got kinda lucky, tho: they all support the same syntax
1259 case 'firebird':
1260 case 'oracle':
1261 case 'postgres':
1262 case 'sqlite':
1263 $sql = 'DROP INDEX ' . $bad_index;
1264 _sql($sql, $errored, $error_ary);
1265 break;
1266 }
1267
1268 // If the good index already exist we do not need to create it again...
1269 if (($map_dbms == 'mysql_40' || $map_dbms == 'mysql_41') && $bad_index == $good_index)
1270 {
1271 }
1272 else
1273 {
1274 $needed_creation[$table_name][$good_index] = 1;
1275 }
1276 }
1277 }
1278 }
1279
1280 $new_index_defs = array('ath_op_id' => array('auth_option_id'), 'i_d' => array('imageset_id'));
1281
1282 foreach ($needed_creation as $bad_table => $index_repair_list)
1283 {
1284 foreach ($index_repair_list as $new_index => $garbage)
1285 {
1286 sql_create_index($map_dbms, $new_index, $bad_table, $new_index_defs[$new_index]);
1287 $no_updates = false;
1288 }
1289 }
1290
1291 // Make sure empty smiley codes do not exist
1292 $sql = 'DELETE FROM ' . SMILIES_TABLE . "
1293 WHERE code = ''";
1294 _sql($sql, $errored, $error_ary);
1295
1296 set_config('allow_birthdays', '1');
1297 set_config('cron_lock', '0', true);
1298
1299 $no_updates = false;
1300 }
1301
1302 if (version_compare($current_version, '3.0.RC4', '<='))
1303 {
1304 $update_auto_increment = array(
1305 STYLES_TABLE => 'style_id',
1306 STYLES_TEMPLATE_TABLE => 'template_id',
1307 STYLES_THEME_TABLE => 'theme_id',
1308 STYLES_IMAGESET_TABLE => 'imageset_id'
1309 );
1310
1311 $sql = 'SELECT *
1312 FROM ' . STYLES_TABLE . '
1313 WHERE style_id = 0';
1314 $result = _sql($sql, $errored, $error_ary);
1315 $bad_style_row = $db->sql_fetchrow($result);
1316 $db->sql_freeresult($result);
1317
1318 if ($bad_style_row)
1319 {
1320 $sql = 'SELECT MAX(style_id) as max_id
1321 FROM ' . STYLES_TABLE;
1322 $result = _sql($sql, $errored, $error_ary);
1323 $row = $db->sql_fetchrow($result);
1324 $db->sql_freeresult($result);
1325
1326 $proper_id = $row['max_id'] + 1;
1327
1328 _sql('UPDATE ' . STYLES_TABLE . " SET style_id = $proper_id WHERE style_id = 0", $errored, $error_ary);
1329 _sql('UPDATE ' . FORUMS_TABLE . " SET forum_style = $proper_id WHERE forum_style = 0", $errored, $error_ary);
1330 _sql('UPDATE ' . USERS_TABLE . " SET user_style = $proper_id WHERE user_style = 0", $errored, $error_ary);
1331
1332 $sql = 'SELECT config_value
1333 FROM ' . CONFIG_TABLE . "
1334 WHERE config_name = 'default_style'";
1335 $result = _sql($sql, $errored, $error_ary);
1336 $style_config = $db->sql_fetchrow($result);
1337 $db->sql_freeresult($result);
1338
1339 if ($style_config['config_value'] === '0')
1340 {
1341 set_config('default_style', (string) $proper_id);
1342 }
1343 }
1344
1345 $sql = 'SELECT *
1346 FROM ' . STYLES_TEMPLATE_TABLE . '
1347 WHERE template_id = 0';
1348 $result = _sql($sql, $errored, $error_ary);
1349 $bad_style_row = $db->sql_fetchrow($result);
1350 $db->sql_freeresult($result);
1351
1352 if ($bad_style_row)
1353 {
1354 $sql = 'SELECT MAX(template_id) as max_id
1355 FROM ' . STYLES_TEMPLATE_TABLE;
1356 $result = _sql($sql, $errored, $error_ary);
1357 $row = $db->sql_fetchrow($result);
1358 $db->sql_freeresult($result);
1359
1360 $proper_id = $row['max_id'] + 1;
1361
1362 _sql('UPDATE ' . STYLES_TABLE . " SET template_id = $proper_id WHERE template_id = 0", $errored, $error_ary);
1363 }
1364
1365 $sql = 'SELECT *
1366 FROM ' . STYLES_THEME_TABLE . '
1367 WHERE theme_id = 0';
1368 $result = _sql($sql, $errored, $error_ary);
1369 $bad_style_row = $db->sql_fetchrow($result);
1370 $db->sql_freeresult($result);
1371
1372 if ($bad_style_row)
1373 {
1374 $sql = 'SELECT MAX(theme_id) as max_id
1375 FROM ' . STYLES_THEME_TABLE;
1376 $result = _sql($sql, $errored, $error_ary);
1377 $row = $db->sql_fetchrow($result);
1378 $db->sql_freeresult($result);
1379
1380 $proper_id = $row['max_id'] + 1;
1381
1382 _sql('UPDATE ' . STYLES_TABLE . " SET theme_id = $proper_id WHERE theme_id = 0", $errored, $error_ary);
1383 }
1384
1385 $sql = 'SELECT *
1386 FROM ' . STYLES_IMAGESET_TABLE . '
1387 WHERE imageset_id = 0';
1388 $result = _sql($sql, $errored, $error_ary);
1389 $bad_style_row = $db->sql_fetchrow($result);
1390 $db->sql_freeresult($result);
1391
1392 if ($bad_style_row)
1393 {
1394 $sql = 'SELECT MAX(imageset_id) as max_id
1395 FROM ' . STYLES_IMAGESET_TABLE;
1396 $result = _sql($sql, $errored, $error_ary);
1397 $row = $db->sql_fetchrow($result);
1398 $db->sql_freeresult($result);
1399
1400 $proper_id = $row['max_id'] + 1;
1401
1402 _sql('UPDATE ' . STYLES_TABLE . " SET imageset_id = $proper_id WHERE imageset_id = 0", $errored, $error_ary);
1403 _sql('UPDATE ' . STYLES_IMAGESET_DATA_TABLE . " SET imageset_id = $proper_id WHERE imageset_id = 0", $errored, $error_ary);
1404 }
1405
1406 if ($map_dbms == 'mysql_40' || $map_dbms == 'mysql_41')
1407 {
1408 foreach ($update_auto_increment as $auto_table_name => $auto_column_name)
1409 {
1410 $sql = "SELECT MAX({$auto_column_name}) as max_id
1411 FROM {$auto_table_name}";
1412 $result = _sql($sql, $errored, $error_ary);
1413 $row = $db->sql_fetchrow($result);
1414 $db->sql_freeresult($result);
1415
1416 $max_id = ((int) $row['max_id']) + 1;
1417 _sql("ALTER TABLE {$auto_table_name} AUTO_INCREMENT = {$max_id}", $errored, $error_ary);
1418 }
1419
1420 $no_updates = false;
1421 }
1422 else if ($map_dbms == 'postgres')
1423 {
1424 foreach ($update_auto_increment as $auto_table_name => $auto_column_name)
1425 {
1426 $sql = "SELECT SETVAL('" . $auto_table_name . "_seq',(select case when max({$auto_column_name})>0 then max({$auto_column_name})+1 else 1 end from " . $auto_table_name . '));';
1427 _sql($sql, $errored, $error_ary);
1428 }
1429
1430 $sql = 'DROP SEQUENCE ' . STYLES_TEMPLATE_DATA_TABLE . '_seq';
1431 _sql($sql, $errored, $error_ary);
1432 }
1433 else if ($map_dbms == 'firebird')
1434 {
1435 $sql = 'DROP TRIGGER t_' . STYLES_TEMPLATE_DATA_TABLE;
1436 _sql($sql, $errored, $error_ary);
1437
1438 $sql = 'DROP GENERATOR ' . STYLES_TEMPLATE_DATA_TABLE . '_gen';
1439 _sql($sql, $errored, $error_ary);
1440 }
1441 else if ($map_dbms == 'oracle')
1442 {
1443 $sql = 'DROP TRIGGER t_' . STYLES_TEMPLATE_DATA_TABLE;
1444 _sql($sql, $errored, $error_ary);
1445
1446 $sql = 'DROP SEQUENCE ' . STYLES_TEMPLATE_DATA_TABLE . '_seq';
1447 _sql($sql, $errored, $error_ary);
1448 }
1449 else if ($map_dbms == 'mssql')
1450 {
1451 // we use transactions because we need to have a working DB at the end of all of this
1452 $db->sql_transaction('begin');
1453
1454 $sql = 'SELECT *
1455 FROM ' . STYLES_TEMPLATE_DATA_TABLE;
1456 $result = _sql($sql, $errored, $error_ary);
1457 $old_style_rows = array();
1458 while ($row = $db->sql_fetchrow($result))
1459 {
1460 $old_style_rows[] = $row;
1461 }
1462 $db->sql_freeresult($result);
1463
1464 // death to the table, it is evil!
1465 $sql = 'DROP TABLE ' . STYLES_TEMPLATE_DATA_TABLE;
1466 _sql($sql, $errored, $error_ary);
1467
1468 // the table of awesomeness, praise be to it (or something)
1469 $sql = 'CREATE TABLE [' . STYLES_TEMPLATE_DATA_TABLE . "] (
1470 [template_id] [int] DEFAULT (0) NOT NULL ,
1471 [template_filename] [varchar] (100) DEFAULT ('') NOT NULL ,
1472 [template_included] [varchar] (8000) DEFAULT ('') NOT NULL ,
1473 [template_mtime] [int] DEFAULT (0) NOT NULL ,
1474 [template_data] [text] DEFAULT ('') NOT NULL
1475 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]";
1476 _sql($sql, $errored, $error_ary);
1477
1478 // index? index
1479 $sql = 'CREATE INDEX [tid] ON [' . STYLES_TEMPLATE_DATA_TABLE . ']([template_id]) ON [PRIMARY]';
1480 _sql($sql, $errored, $error_ary);
1481
1482 // yet another index
1483 $sql = 'CREATE INDEX [tfn] ON [' . STYLES_TEMPLATE_DATA_TABLE . ']([template_filename]) ON [PRIMARY]';
1484 _sql($sql, $errored, $error_ary);
1485
1486 foreach ($old_style_rows as $return_row)
1487 {
1488 _sql('INSERT INTO ' . STYLES_TEMPLATE_DATA_TABLE . ' ' . $db->sql_build_array('INSERT', $return_row), $errored, $error_ary);
1489 }
1490
1491 $db->sql_transaction('commit');
1492 }
1493
1494 // Setting this here again because new installations may not have it...
1495 set_config('cron_lock', '0', true);
1496 set_config('ldap_port', '');
1497 set_config('ldap_user_filter', '');
1498
1499 $no_updates = false;
1500 }
1501
1502 if (version_compare($current_version, '3.0.RC5', '<='))
1503 {
1504 // In case the user is having the bot mediapartner google "as is", adjust it.
1505 $sql = 'UPDATE ' . BOTS_TABLE . "
1506 SET bot_agent = '" . $db->sql_escape('Mediapartners-Google') . "'
1507 WHERE bot_agent = '" . $db->sql_escape('Mediapartners-Google/') . "'";
1508 _sql($sql, $errored, $error_ary);
1509
1510 set_config('form_token_lifetime', '7200');
1511 set_config('form_token_mintime', '0');
1512 set_config('min_time_reg', '5');
1513 set_config('min_time_terms', '2');
1514 set_config('form_token_sid_guests', '1');
1515
1516 $db->sql_transaction('begin');
1517
1518 $sql = 'SELECT forum_id, forum_password
1519 FROM ' . FORUMS_TABLE;
1520 $result = _sql($sql, $errored, $error_ary);
1521
1522 while ($row = $db->sql_fetchrow($result))
1523 {
1524 if (!empty($row['forum_password']))
1525 {
1526 _sql('UPDATE ' . FORUMS_TABLE . " SET forum_password = '" . md5($row['forum_password']) . "' WHERE forum_id = {$row['forum_id']}", $errored, $error_ary);
1527 }
1528 }
1529 $db->sql_freeresult($result);
1530
1531 $db->sql_transaction('commit');
1532
1533 $no_updates = false;
1534 }
1535
1536 _write_result($no_updates, $errored, $error_ary);
1537
1538 $error_ary = array();
1539 $errored = $no_updates = false;
1540
1541 ?>
1542
1543 <br /><br />
1544 <h1><?php echo $lang['UPDATE_VERSION_OPTIMIZE']; ?></h1>
1545 <br />
1546 <p><?php echo $lang['PROGRESS']; ?> :: <strong>
1547
1548 <?php
1549
1550 flush();
1551
1552 // update the version
1553 $sql = "UPDATE " . CONFIG_TABLE . "
1554 SET config_value = '$updates_to_version'
1555 WHERE config_name = 'version'";
1556 _sql($sql, $errored, $error_ary);
1557
1558 // Reset permissions
1559 $sql = 'UPDATE ' . USERS_TABLE . "
1560 SET user_permissions = ''";
1561 _sql($sql, $errored, $error_ary);
1562
1563 /* Optimize/vacuum analyze the tables where appropriate
1564 // this should be done for each version in future along with
1565 // the version number update
1566 switch ($db->sql_layer)
1567 {
1568 case 'mysql':
1569 case 'mysqli':
1570 case 'mysql4':
1571 $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';
1572 _sql($sql, $errored, $error_ary);
1573 break;
1574
1575 case 'postgresql':
1576 _sql("VACUUM ANALYZE", $errored, $error_ary);
1577 break;
1578 }
1579 */
1580
1581 _write_result($no_updates, $errored, $error_ary);
1582
1583 ?>
1584
1585 <br />
1586 <h1><?php echo $lang['UPDATE_COMPLETED']; ?></h1>
1587
1588 <br />
1589
1590 <?php
1591
1592 if (!$inline_update)
1593 {
1594 ?>
1595
1596 <p style="color:red"><?php echo $lang['UPDATE_FILES_NOTICE']; ?></p>
1597
1598 <p><?php echo $lang['COMPLETE_LOGIN_TO_BOARD']; ?></p>
1599
1600 <?php
1601 }
1602 else
1603 {
1604 ?>
1605
1606 <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>
1607
1608 <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>
1609
1610 <?php
1611 }
1612
1613 // Add database update to log
1614 add_log('admin', 'LOG_UPDATE_DATABASE', $orig_version, $updates_to_version);
1615
1616 // Now we purge the session table as well as all cache files
1617 $cache->purge();
1618
1619 ?>
1620
1621 </div>
1622 </div>
1623 <span class="corners-bottom"><span></span></span>
1624 </div>
1625 </div>
1626 </div>
1627
1628 <div id="page-footer">
1629 Powered by phpBB &copy; 2000, 2002, 2005, 2007 <a href="http://www.phpbb.com/">phpBB Group</a>
1630 </div>
1631 </div>
1632
1633 </body>
1634 </html>
1635
1636 <?php
1637
1638 if (function_exists('exit_handler'))
1639 {
1640 exit_handler();
1641 }
1642
1643
1644 /**
1645 * Function for triggering an sql statement
1646 */
1647 function _sql($sql, &$errored, &$error_ary, $echo_dot = true)
1648 {
1649 global $db;
1650
1651 if (defined('DEBUG_EXTRA'))
1652 {
1653 echo "<br />\n{$sql}\n<br />";
1654 }
1655
1656 $db->sql_return_on_error(true);
1657
1658 $result = $db->sql_query($sql);
1659 if ($db->sql_error_triggered)
1660 {
1661 $errored = true;
1662 $error_ary['sql'][] = $db->sql_error_sql;
1663 $error_ary['error_code'][] = $db->_sql_error();
1664 }
1665
1666 $db->sql_return_on_error(false);
1667
1668 if ($echo_dot)
1669 {
1670 echo ". \n";
1671 flush();
1672 }
1673
1674 return $result;
1675 }
1676
1677 function _write_result($no_updates, $errored, $error_ary)
1678 {
1679 global $lang;
1680
1681 if ($no_updates)
1682 {
1683 echo ' ' . $lang['NO_UPDATES_REQUIRED'] . '</strong></p>';
1684 }
1685 else
1686 {
1687 echo ' <span class="success">' . $lang['DONE'] . '</span></strong><br />' . $lang['RESULT'] . ' :: ';
1688
1689 if ($errored)
1690 {
1691 echo ' <strong>' . $lang['SOME_QUERIES_FAILED'] . '</strong> <ul>';
1692
1693 for ($i = 0; $i < sizeof($error_ary['sql']); $i++)
1694 {
1695 echo '<li>' . $lang['ERROR'] . ' :: <strong>' . htmlspecialchars($error_ary['error_code'][$i]['message']) . '</strong><br />';
1696 echo $lang['SQL'] . ' :: <strong>' . htmlspecialchars($error_ary['sql'][$i]) . '</strong><br /><br /></li>';
1697 }
1698
1699 echo '</ul> <br /><br />' . $lang['SQL_FAILURE_EXPLAIN'] . '</p>';
1700 }
1701 else
1702 {
1703 echo '<strong>' . $lang['NO_ERRORS'] . '</strong></p>';
1704 }
1705 }
1706 }
1707
1708 /**
1709 * Check if a specified column exist
1710 */
1711 function column_exists($dbms, $table, $column_name)
1712 {
1713 global $db;
1714
1715 switch ($dbms)
1716 {
1717 case 'mysql_40':
1718 case 'mysql_41':
1719 $sql = "SHOW COLUMNS
1720 FROM $table";
1721 $result = $db->sql_query($sql);
1722 while ($row = $db->sql_fetchrow($result))
1723 {
1724 // lower case just in case
1725 if (strtolower($row['Field']) == $column_name)
1726 {
1727 $db->sql_freeresult($result);
1728 return true;
1729 }
1730 }
1731 $db->sql_freeresult($result);
1732 return false;
1733 break;
1734
1735 // PostgreSQL has a way of doing this in a much simpler way but would
1736 // not allow us to support all versions of PostgreSQL
1737 case 'postgres':
1738 $sql = "SELECT a.attname
1739 FROM pg_class c, pg_attribute a
1740 WHERE c.relname = '{$table}'
1741 AND a.attnum > 0
1742 AND a.attrelid = c.oid";
1743 $result = $db->sql_query($sql);
1744 while ($row = $db->sql_fetchrow($result))
1745 {
1746 // lower case just in case
1747 if (strtolower($row['attname']) == $column_name)
1748 {
1749 $db->sql_freeresult($result);
1750 return true;
1751 }
1752 }
1753 $db->sql_freeresult($result);
1754 return false;
1755 break;
1756
1757 // same deal with PostgreSQL, we must perform more complex operations than
1758 // we technically could
1759 case 'mssql':
1760 $sql = "SELECT c.name
1761 FROM syscolumns c
1762 LEFT JOIN sysobjects o ON c.id = o.id
1763 WHERE o.name = '{$table}'";
1764 $result = $db->sql_query($sql);
1765 while ($row = $db->sql_fetchrow($result))
1766 {
1767 // lower case just in case
1768 if (strtolower($row['name']) == $column_name)
1769 {
1770 $db->sql_freeresult($result);
1771 return true;
1772 }
1773 }
1774 $db->sql_freeresult($result);
1775 return false;
1776 break;
1777
1778 case 'oracle':
1779 $sql = "SELECT column_name
1780 FROM user_tab_columns
1781 WHERE table_name = '{$table}'";
1782 $result = $db->sql_query($sql);
1783 while ($row = $db->sql_fetchrow($result))
1784 {
1785 // lower case just in case
1786 if (strtolower($row['column_name']) == $column_name)
1787 {
1788 $db->sql_freeresult($result);
1789 return true;
1790 }
1791 }
1792 $db->sql_freeresult($result);
1793 return false;
1794 break;
1795
1796 case 'firebird':
1797 $sql = "SELECT RDB\$FIELD_NAME as FNAME
1798 FROM RDB\$RELATION_FIELDS
1799 WHERE RDB\$RELATION_NAME = '{$table}'";
1800 $result = $db->sql_query($sql);
1801 while ($row = $db->sql_fetchrow($result))
1802 {
1803 // lower case just in case
1804 if (strtolower($row['fname']) == $column_name)
1805 {
1806 $db->sql_freeresult($result);
1807 return true;
1808 }
1809 }
1810 $db->sql_freeresult($result);
1811 return false;
1812 break;
1813
1814 // ugh, SQLite
1815 case 'sqlite':
1816 $sql = "SELECT sql
1817 FROM sqlite_master
1818 WHERE type = 'table'
1819 AND name = '{$table}'";
1820 $result = $db->sql_query($sql);
1821
1822 if (!$result)
1823 {
1824 return false;
1825 }
1826
1827 $row = $db->sql_fetchrow($result);
1828 $db->sql_freeresult($result);
1829
1830 preg_match('#\((.*)\)#s', $row['sql'], $matches);
1831
1832 $cols = trim($matches[1]);
1833 $col_array = preg_split('/,(?![\s\w]+\))/m', $cols);
1834
1835 foreach ($col_array as $declaration)
1836 {
1837 $entities = preg_split('#\s+#', trim($declaration));
1838 if ($entities[0] == 'PRIMARY')
1839 {
1840 continue;
1841 }
1842
1843 if (strtolower($entities[0]) == $column_name)
1844 {
1845 return true;
1846 }
1847 }
1848 return false;
1849 break;
1850 }
1851 }
1852
1853 /**
1854 * Function to prepare some column information for better usage
1855 */
1856 function prepare_column_data($dbms, $column_data, $table_name, $column_name)
1857 {
1858 global $dbms_type_map, $unsigned_types;
1859
1860 // Get type
1861 if (strpos($column_data[0], ':') !== false)
1862 {
1863 list($orig_column_type, $column_length) = explode(':', $column_data[0]);
1864
1865 if (!is_array($dbms_type_map[$dbms][$orig_column_type . ':']))
1866 {
1867 $column_type = sprintf($dbms_type_map[$dbms][$orig_column_type . ':'], $column_length);
1868 }
1869 else
1870 {
1871 if (isset($dbms_type_map[$dbms][$orig_column_type . ':']['rule']))
1872 {
1873 switch ($dbms_type_map[$dbms][$orig_column_type . ':']['rule'][0])
1874 {
1875 case 'div':
1876 $column_length /= $dbms_type_map[$dbms][$orig_column_type . ':']['rule'][1];
1877 $column_length = ceil($column_length);
1878 $column_type = sprintf($dbms_type_map[$dbms][$orig_column_type . ':'][0], $column_length);
1879 break;
1880 }
1881 }
1882
1883 if (isset($dbms_type_map[$dbms][$orig_column_type . ':']['limit']))
1884 {
1885 switch ($dbms_type_map[$dbms][$orig_column_type . ':']['limit'][0])
1886 {
1887 case 'mult':
1888 $column_length *= $dbms_type_map[$dbms][$orig_column_type . ':']['limit'][1];
1889 if ($column_length > $dbms_type_map[$dbms][$orig_column_type . ':']['limit'][2])
1890 {
1891 $column_type = $dbms_type_map[$dbms][$orig_column_type . ':']['limit'][3];
1892 }
1893 else
1894 {
1895 $column_type = sprintf($dbms_type_map[$dbms][$orig_column_type . ':'][0], $column_length);
1896 }
1897 break;
1898 }
1899 }
1900 }
1901 $orig_column_type .= ':';
1902 }
1903 else
1904 {
1905 $orig_column_type = $column_data[0];
1906 $column_type = $dbms_type_map[$dbms][$column_data[0]];
1907 }
1908
1909 // Adjust default value if db-dependant specified
1910 if (is_array($column_data[1]))
1911 {
1912 $column_data[1] = (isset($column_data[1][$dbms])) ? $column_data[1][$dbms] : $column_data[1]['default'];
1913 }
1914
1915 $sql = '';
1916 $return_array = array();
1917
1918 switch ($dbms)
1919 {
1920 case 'firebird':
1921 $sql .= " {$column_type} ";
1922
1923 if (!is_null($column_data[1]))
1924 {
1925 $sql .= 'DEFAULT ' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ' ';
1926 }
1927
1928 $sql .= 'NOT NULL';
1929
1930 // This is a UNICODE column and thus should be given it's fair share
1931 if (preg_match('/^X?STEXT_UNI|VCHAR_(CI|UNI:?)/', $column_data[0]))
1932 {
1933 $sql .= ' COLLATE UNICODE';
1934 }
1935
1936 break;
1937
1938 case 'mssql':
1939 $sql .= " {$column_type} ";
1940 $sql_default = " {$column_type} ";
1941
1942 // For adding columns we need the default definition
1943 if (!is_null($column_data[1]))
1944 {
1945 // For hexadecimal values do not use single quotes
1946 if (strpos($column_data[1], '0x') === 0)
1947 {
1948 $sql_default .= 'DEFAULT (' . $column_data[1] . ') ';
1949 }
1950 else
1951 {
1952 $sql_default .= 'DEFAULT (' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ') ';
1953 }
1954 }
1955
1956 $sql .= 'NOT NULL';
1957 $sql_default .= 'NOT NULL';
1958
1959 $return_array['column_type_sql_default'] = $sql_default;
1960 break;
1961
1962 case 'mysql_40':
1963 case 'mysql_41':
1964 $sql .= " {$column_type} ";
1965
1966 // For hexadecimal values do not use single quotes
1967 if (!is_null($column_data[1]) && substr($column_type, -4) !== 'text' && substr($column_type, -4) !== 'blob')
1968 {
1969 $sql .= (strpos($column_data[1], '0x') === 0) ? "DEFAULT {$column_data[1]} " : "DEFAULT '{$column_data[1]}' ";
1970 }
1971 $sql .= 'NOT NULL';
1972
1973 if (isset($column_data[2]))
1974 {
1975 if ($column_data[2] == 'auto_increment')
1976 {
1977 $sql .= ' auto_increment';
1978 }
1979 else if ($dbms === 'mysql_41' && $column_data[2] == 'true_sort')
1980 {
1981 $sql .= ' COLLATE utf8_unicode_ci';
1982 }
1983 }
1984
1985 break;
1986
1987 case 'oracle':
1988 $sql .= " {$column_type} ";
1989 $sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : '';
1990
1991 // In Oracle empty strings ('') are treated as NULL.
1992 // Therefore in oracle we allow NULL's for all DEFAULT '' entries
1993 // Oracle does not like setting NOT NULL on a column that is already NOT NULL (this happens only on number fields)
1994 if (preg_match('/number/i', $column_type))
1995 {
1996 $sql .= ($column_data[1] === '') ? '' : 'NOT NULL';
1997 }
1998 break;
1999
2000 case 'postgres':
2001 $return_array['column_type'] = $column_type;
2002
2003 $sql .= " {$column_type} ";
2004
2005 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
2006 {
2007 $default_val = "nextval('{$table_name}_seq')";
2008 }
2009 else if (!is_null($column_data[1]))
2010 {
2011 $default_val = "'" . $column_data[1] . "'";
2012 $return_array['null'] = 'NOT NULL';
2013 $sql .= 'NOT NULL ';
2014 }
2015
2016 $return_array['default'] = $default_val;
2017
2018 $sql .= "DEFAULT {$default_val}";
2019
2020 // Unsigned? Then add a CHECK contraint
2021 if (in_array($orig_column_type, $unsigned_types))
2022 {
2023 $return_array['constraint'] = "CHECK ({$column_name} >= 0)";
2024 $sql .= " CHECK ({$column_name} >= 0)";
2025 }
2026 break;
2027
2028 case 'sqlite':
2029 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
2030 {
2031 $sql .= ' INTEGER PRIMARY KEY';
2032 }
2033 else
2034 {
2035 $sql .= ' ' . $column_type;
2036 }
2037
2038 $sql .= ' NOT NULL ';
2039 $sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}'" : '';
2040 break;
2041 }
2042
2043 $return_array['column_type_sql'] = $sql;
2044
2045 return $return_array;
2046 }
2047
2048 /**
2049 * Add new column
2050 */
2051 function sql_column_add($dbms, $table_name, $column_name, $column_data)
2052 {
2053 global $errored, $error_ary;
2054
2055 $column_data = prepare_column_data($dbms, $column_data, $table_name, $column_name);
2056
2057 switch ($dbms)
2058 {
2059 case 'firebird':
2060 $sql = 'ALTER TABLE "' . $table_name . '" ADD "' . $column_name . '" ' . $column_data['column_type_sql'];
2061 _sql($sql, $errored, $error_ary);
2062 break;
2063
2064 case 'mssql':
2065 $sql = 'ALTER TABLE [' . $table_name . '] ADD [' . $column_name . '] ' . $column_data['column_type_sql_default'];
2066 _sql($sql, $errored, $error_ary);
2067 break;
2068
2069 case 'mysql_40':
2070 case 'mysql_41':
2071 $sql = 'ALTER TABLE `' . $table_name . '` ADD COLUMN `' . $column_name . '` ' . $column_data['column_type_sql'];
2072 _sql($sql, $errored, $error_ary);
2073 break;
2074
2075 case 'oracle':
2076 $sql = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' ' . $column_data['column_type_sql'];
2077 _sql($sql, $errored, $error_ary);
2078 break;
2079
2080 case 'postgres':
2081 $sql = 'ALTER TABLE ' . $table_name . ' ADD COLUMN "' . $column_name . '" ' . $column_data['column_type_sql'];
2082 _sql($sql, $errored, $error_ary);
2083 break;
2084
2085 case 'sqlite':
2086 if (version_compare(sqlite_libversion(), '3.0') == -1)
2087 {
2088 global $db;
2089 $sql = "SELECT sql
2090 FROM sqlite_master
2091 WHERE type = 'table'
2092 AND name = '{$table_name}'
2093 ORDER BY type DESC, name;";
2094 $result = $db->sql_query($sql);
2095
2096 if (!$result)
2097 {
2098 break;
2099 }
2100
2101 $row = $db->sql_fetchrow($result);
2102 $db->sql_freeresult($result);
2103
2104 $db->sql_transaction('begin');
2105
2106 // Create a backup table and populate it, destroy the existing one
2107 $db->sql_query(preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']));
2108 $db->sql_query('INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name);
2109 $db->sql_query('DROP TABLE ' . $table_name);
2110
2111 preg_match('#\((.*)\)#s', $row['sql'], $matches);
2112
2113 $new_table_cols = trim($matches[1]);
2114 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
2115 $column_list = array();
2116
2117 foreach ($old_table_cols as $declaration)
2118 {
2119 $entities = preg_split('#\s+#', trim($declaration));
2120 if ($entities[0] == 'PRIMARY')
2121 {
2122 continue;
2123 }
2124 $column_list[] = $entities[0];
2125 }
2126
2127 $columns = implode(',', $column_list);
2128
2129 $new_table_cols = $column_name . ' ' . $column_data['column_type_sql'] . ',' . $new_table_cols;
2130
2131 // create a new table and fill it up. destroy the temp one
2132 $db->sql_query('CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');');
2133 $db->sql_query('INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;');
2134 $db->sql_query('DROP TABLE ' . $table_name . '_temp');
2135
2136 $db->sql_transaction('commit');
2137 }
2138 else
2139 {
2140 $sql = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' [' . $column_data['column_type_sql'] . ']';
2141 _sql($sql, $errored, $error_ary);
2142 }
2143 break;
2144 }
2145 }
2146
2147 /**
2148 * Drop column
2149 */
2150 function sql_column_remove($dbms, $table_name, $column_name)
2151 {
2152 global $errored, $error_ary;
2153
2154 switch ($dbms)
2155 {
2156 case 'firebird':
2157 $sql = 'ALTER TABLE "' . $table_name . '" DROP "' . $column_name . '"';
2158 _sql($sql, $errored, $error_ary);
2159 break;
2160
2161 case 'mssql':
2162 $sql = 'ALTER TABLE [' . $table_name . '] DROP COLUMN [' . $column_name . ']';
2163 _sql($sql, $errored, $error_ary);
2164 break;
2165
2166 case 'mysql_40':
2167 case 'mysql_41':
2168 $sql = 'ALTER TABLE `' . $table_name . '` DROP COLUMN `' . $column_name . '`';
2169 _sql($sql, $errored, $error_ary);
2170 break;
2171
2172 case 'oracle':
2173 $sql = 'ALTER TABLE ' . $table_name . ' DROP ' . $column_name;
2174 _sql($sql, $errored, $error_ary);
2175 break;
2176
2177 case 'postgres':
2178 $sql = 'ALTER TABLE ' . $table_name . ' DROP COLUMN "' . $column_name . '"';
2179 _sql($sql, $errored, $error_ary);
2180 break;
2181
2182 case 'sqlite':
2183 if (version_compare(sqlite_libversion(), '3.0') == -1)
2184 {
2185 global $db;
2186 $sql = "SELECT sql
2187 FROM sqlite_master
2188 WHERE type = 'table'
2189 AND name = '{$table_name}'
2190 ORDER BY type DESC, name;";
2191 $result = $db->sql_query($sql);
2192
2193 if (!$result)
2194 {
2195 break;
2196 }
2197
2198 $row = $db->sql_fetchrow($result);
2199 $db->sql_freeresult($result);
2200
2201 $db->sql_transaction('begin');
2202
2203 // Create a backup table and populate it, destroy the existing one
2204 $db->sql_query(preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']));
2205 $db->sql_query('INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name);
2206 $db->sql_query('DROP TABLE ' . $table_name);
2207
2208 preg_match('#\((.*)\)#s', $row['sql'], $matches);
2209
2210 $new_table_cols = trim($matches[1]);
2211 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
2212 $column_list = array();
2213
2214 foreach ($old_table_cols as $declaration)
2215 {
2216 $entities = preg_split('#\s+#', trim($declaration));
2217 if ($entities[0] == 'PRIMARY' || $entities[0] === $column_name)
2218 {
2219 continue;
2220 }
2221 $column_list[] = $entities[0];
2222 }
2223
2224 $columns = implode(',', $column_list);
2225
2226 $new_table_cols = $new_table_cols = preg_replace('/' . $column_name . '[^,]+(?:,|$)/m', '', $new_table_cols);
2227
2228 // create a new table and fill it up. destroy the temp one
2229 $db->sql_query('CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');');
2230 $db->sql_query('INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;');
2231 $db->sql_query('DROP TABLE ' . $table_name . '_temp');
2232
2233 $db->sql_transaction('commit');
2234 }
2235 else
2236 {
2237 $sql = 'ALTER TABLE ' . $table_name . ' DROP COLUMN ' . $column_name;
2238 _sql($sql, $errored, $error_ary);
2239 }
2240 break;
2241 }
2242 }
2243
2244 function sql_index_drop($dbms, $index_name, $table_name)
2245 {
2246 global $dbms_type_map, $db;
2247 global $errored, $error_ary;
2248
2249 switch ($dbms)
2250 {
2251 case 'mssql':
2252 $sql = 'DROP INDEX ' . $table_name . '.' . $index_name;
2253 _sql($sql, $errored, $error_ary);
2254 break;
2255
2256 case 'mysql_40':
2257 case 'mysql_41':
2258 $sql = 'DROP INDEX ' . $index_name . ' ON ' . $table_name;
2259 _sql($sql, $errored, $error_ary);
2260 break;
2261
2262 case 'firebird':
2263 case 'oracle':
2264 case 'postgres':
2265 case 'sqlite':
2266 $sql = 'DROP INDEX ' . $table_name . '_' . $index_name;
2267 _sql($sql, $errored, $error_ary);
2268 break;
2269 }
2270 }
2271
2272 function sql_create_primary_key($dbms, $table_name, $column)
2273 {
2274 global $dbms_type_map, $db;
2275 global $errored, $error_ary;
2276
2277 switch ($dbms)
2278 {
2279 case 'firebird':
2280 case 'postgres':
2281 $sql = 'ALTER TABLE ' . $table_name . ' ADD PRIMARY KEY (' . implode(', ', $column) . ')';
2282 _sql($sql, $errored, $error_ary);
2283 break;
2284
2285 case 'mssql':
2286 $sql = "ALTER TABLE [{$table_name}] WITH NOCHECK ADD ";
2287 $sql .= "CONSTRAINT [PK_{$table_name}] PRIMARY KEY CLUSTERED (";
2288 $sql .= '[' . implode("],\n\t\t[", $column) . ']';
2289 $sql .= ') ON [PRIMARY]';
2290 _sql($sql, $errored, $error_ary);
2291 break;
2292
2293 case 'mysql_40':
2294 case 'mysql_41':
2295 $sql = 'ALTER TABLE ' . $table_name . ' ADD PRIMARY KEY (' . implode(', ', $column) . ')';
2296 _sql($sql, $errored, $error_ary);
2297 break;
2298
2299 case 'oracle':
2300 $sql = 'ALTER TABLE ' . $table_name . 'add CONSTRAINT pk_' . $table_name . ' PRIMARY KEY (' . implode(', ', $column) . ')';
2301 _sql($sql, $errored, $error_ary);
2302 break;
2303
2304 case 'sqlite':
2305 $sql = "SELECT sql
2306 FROM sqlite_master
2307 WHERE type = 'table'
2308 AND name = '{$table_name}'
2309 ORDER BY type DESC, name;";
2310 $result = _sql($sql, $errored, $error_ary);
2311
2312 if (!$result)
2313 {
2314 break;
2315 }
2316
2317 $row = $db->sql_fetchrow($result);
2318 $db->sql_freeresult($result);
2319
2320 $db->sql_transaction('begin');
2321
2322 // Create a backup table and populate it, destroy the existing one
2323 $db->sql_query(preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']));
2324 $db->sql_query('INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name);
2325 $db->sql_query('DROP TABLE ' . $table_name);
2326
2327 preg_match('#\((.*)\)#s', $row['sql'], $matches);
2328
2329 $new_table_cols = trim($matches[1]);
2330 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
2331 $column_list = array();
2332
2333 foreach ($old_table_cols as $declaration)
2334 {
2335 $entities = preg_split('#\s+#', trim($declaration));
2336 if ($entities[0] == 'PRIMARY')
2337 {
2338 continue;
2339 }
2340 $column_list[] = $entities[0];
2341 }
2342
2343 $columns = implode(',', $column_list);
2344
2345 // create a new table and fill it up. destroy the temp one
2346 $db->sql_query('CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ', PRIMARY KEY (' . implode(', ', $column) . '));');
2347 $db->sql_query('INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;');
2348 $db->sql_query('DROP TABLE ' . $table_name . '_temp');
2349
2350 $db->sql_transaction('commit');
2351 break;
2352 }
2353 }
2354
2355 function sql_create_unique_index($dbms, $index_name, $table_name, $column)
2356 {
2357 global $dbms_type_map, $db;
2358 global $errored, $error_ary;
2359
2360 switch ($dbms)
2361 {
2362 case 'firebird':
2363 case 'postgres':
2364 case 'oracle':
2365 case 'sqlite':
2366 $sql = 'CREATE UNIQUE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
2367 _sql($sql, $errored, $error_ary);
2368 break;
2369
2370 case 'mysql_40':
2371 case 'mysql_41':
2372 $sql = 'CREATE UNIQUE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
2373 _sql($sql, $errored, $error_ary);
2374 break;
2375
2376 case 'mssql':
2377 $sql = 'CREATE UNIQUE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ') ON [PRIMARY]';
2378 _sql($sql, $errored, $error_ary);
2379 break;
2380 }
2381 }
2382
2383 function sql_create_index($dbms, $index_name, $table_name, $column)
2384 {
2385 global $dbms_type_map, $db;
2386 global $errored, $error_ary;
2387
2388 switch ($dbms)
2389 {
2390 case 'firebird':
2391 case 'postgres':
2392 case 'oracle':
2393 case 'sqlite':
2394 $sql = 'CREATE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
2395 _sql($sql, $errored, $error_ary);
2396 break;
2397
2398 case 'mysql_40':
2399 case 'mysql_41':
2400 $sql = 'CREATE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
2401 _sql($sql, $errored, $error_ary);
2402 break;
2403
2404 case 'mssql':
2405 $sql = 'CREATE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ') ON [PRIMARY]';
2406 _sql($sql, $errored, $error_ary);
2407 break;
2408 }
2409 }
2410
2411 // List all of the indices that belong to a table,
2412 // does not count:
2413 // * UNIQUE indices
2414 // * PRIMARY keys
2415 function sql_list_index($dbms, $table_name)
2416 {
2417 global $dbms_type_map, $db;
2418 global $errored, $error_ary;
2419
2420 $index_array = array();
2421
2422 if ($dbms == 'mssql')
2423 {
2424 $sql = "EXEC sp_statistics '$table_name'";
2425 $result = $db->sql_query($sql);
2426 while ($row = $db->sql_fetchrow($result))
2427 {
2428 if ($row['TYPE'] == 3)
2429 {
2430 $index_array[] = $row['INDEX_NAME'];
2431 }
2432 }
2433 $db->sql_freeresult($result);
2434 }
2435 else
2436 {
2437 switch ($dbms)
2438 {
2439 case 'firebird':
2440 $sql = "SELECT LOWER(RDB\$INDEX_NAME) as index_name
2441 FROM RDB\$INDICES
2442 WHERE RDB\$RELATION_NAME = " . strtoupper($table_name) . "
2443 AND RDB\$UNIQUE_FLAG IS NULL
2444 AND RDB\$FOREIGN_KEY IS NULL";
2445 $col = 'index_name';
2446 break;
2447
2448 case 'postgres':
2449 $sql = "SELECT ic.relname as index_name
2450 FROM pg_class bc, pg_class ic, pg_index i
2451 WHERE (bc.oid = i.indrelid)
2452 AND (ic.oid = i.indexrelid)
2453 AND (bc.relname = '" . $table_name . "')
2454 AND (i.indisunique != 't')
2455 AND (i.indisprimary != 't')";
2456 $col = 'index_name';
2457 break;
2458
2459 case 'mysql_40':
2460 case 'mysql_41':
2461 $sql = 'SHOW KEYS
2462 FROM ' . $table_name;
2463 $col = 'Key_name';
2464 break;
2465
2466 case 'oracle':
2467 $sql = "SELECT index_name
2468 FROM user_indexes
2469 WHERE table_name = '" . $table_name . "'
2470 AND generated = 'N'";
2471 break;
2472
2473 case 'sqlite':
2474 $sql = "PRAGMA index_info('" . $table_name . "');";
2475 $col = 'name';
2476 break;
2477 }
2478
2479 $result = $db->sql_query($sql);
2480 while ($row = $db->sql_fetchrow($result))
2481 {
2482 if (($dbms == 'mysql_40' || $dbms == 'mysql_41') && !$row['Non_unique'])
2483 {
2484 continue;
2485 }
2486
2487 switch ($dbms)
2488 {
2489 case 'firebird':
2490 case 'oracle':
2491 case 'postgres':
2492 case 'sqlite':
2493 $row[$col] = substr($row[$col], strlen($table_name) + 1);
2494 break;
2495 }
2496
2497 $index_array[] = $row[$col];
2498 }
2499 $db->sql_freeresult($result);
2500 }
2501
2502 return array_map('strtolower', $index_array);
2503 }
2504
2505 // This is totally fake, never use it
2506 // it exists only to mend bad update functions introduced
2507 // * UNIQUE indices
2508 // * PRIMARY keys
2509 function sql_list_fake($dbms, $table_name)
2510 {
2511 global $dbms_type_map, $db;
2512 global $errored, $error_ary;
2513
2514 $index_array = array();
2515
2516 if ($dbms == 'mssql')
2517 {
2518 $sql = "EXEC sp_statistics '$table_name'";
2519 $result = $db->sql_query($sql);
2520 while ($row = $db->sql_fetchrow($result))
2521 {
2522 if ($row['TYPE'] == 3)
2523 {
2524 $index_array[] = $row['INDEX_NAME'];
2525 }
2526 }
2527 $db->sql_freeresult($result);
2528 }
2529 else
2530 {
2531 switch ($dbms)
2532 {
2533 case 'firebird':
2534 $sql = "SELECT LOWER(RDB\$INDEX_NAME) as index_name
2535 FROM RDB\$INDICES
2536 WHERE RDB\$RELATION_NAME = " . strtoupper($table_name) . "
2537 AND RDB\$UNIQUE_FLAG IS NULL
2538 AND RDB\$FOREIGN_KEY IS NULL";
2539 $col = 'index_name';
2540 break;
2541
2542 case 'postgres':
2543 $sql = "SELECT ic.relname as index_name
2544 FROM pg_class bc, pg_class ic, pg_index i
2545 WHERE (bc.oid = i.indrelid)
2546 AND (ic.oid = i.indexrelid)
2547 AND (bc.relname = '" . $table_name . "')
2548 AND (i.indisunique != 't')
2549 AND (i.indisprimary != 't')";
2550 $col = 'index_name';
2551 break;
2552
2553 case 'mysql_40':
2554 case 'mysql_41':
2555 $sql = 'SHOW KEYS
2556 FROM ' . $table_name;
2557 $col = 'Key_name';
2558 break;
2559
2560 case 'oracle':
2561 $sql = "SELECT index_name
2562 FROM user_indexes
2563 WHERE table_name = '" . $table_name . "'
2564 AND generated = 'N'";
2565 break;
2566
2567 case 'sqlite':
2568 $sql = "PRAGMA index_info('" . $table_name . "');";
2569 $col = 'name';
2570 break;
2571 }
2572
2573 $result = $db->sql_query($sql);
2574 while ($row = $db->sql_fetchrow($result))
2575 {
2576 if (($dbms == 'mysql_40' || $dbms == 'mysql_41') && !$row['Non_unique'])
2577 {
2578 continue;
2579 }
2580
2581 $index_array[] = $row[$col];
2582 }
2583 $db->sql_freeresult($result);
2584 }
2585
2586 return array_map('strtolower', $index_array);
2587 }
2588
2589 /**
2590 * Change column type (not name!)
2591 */
2592 function sql_column_change($dbms, $table_name, $column_name, $column_data)
2593 {
2594 global $dbms_type_map, $db;
2595 global $errored, $error_ary;
2596
2597 $column_data = prepare_column_data($dbms, $column_data, $table_name, $column_name);
2598
2599 switch ($dbms)
2600 {
2601 case 'firebird':
2602 // Change type...
2603 $sql = 'ALTER TABLE "' . $table_name . '" ALTER COLUMN "' . $column_name . '" TYPE ' . ' ' . $column_data['column_type_sql'];
2604 _sql($sql, $errored, $error_ary);
2605 break;
2606
2607 case 'mssql':
2608 $sql = 'ALTER TABLE [' . $table_name . '] ALTER COLUMN [' . $column_name . '] ' . $column_data['column_type_sql'];
2609 _sql($sql, $errored, $error_ary);
2610 break;
2611
2612 case 'mysql_40':
2613 case 'mysql_41':
2614 $sql = 'ALTER TABLE `' . $table_name . '` CHANGE `' . $column_name . '` `' . $column_name . '` ' . $column_data['column_type_sql'];
2615 _sql($sql, $errored, $error_ary);
2616 break;
2617
2618 case 'oracle':
2619 $sql = 'ALTER TABLE ' . $table_name . ' MODIFY ' . $column_name . ' ' . $column_data['column_type_sql'];
2620 _sql($sql, $errored, $error_ary);
2621 break;
2622
2623 case 'postgres':
2624 $sql = 'ALTER TABLE ' . $table_name . ' ';
2625
2626 $sql_array = array();
2627 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' TYPE ' . $column_data['column_type'];
2628
2629 if (isset($column_data['null']))
2630 {
2631 if ($column_data['null'] == 'NOT NULL')
2632 {
2633 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' SET NOT NULL';
2634 }
2635 else if ($column_data['null'] == 'NULL')
2636 {
2637 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' DROP NOT NULL';
2638 }
2639 }
2640
2641 if (isset($column_data['default']))
2642 {
2643 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' SET DEFAULT ' . $column_data['default'];
2644 }
2645
2646 // we don't want to double up on constraints if we change different number data types
2647 if (isset($column_data['constraint']))
2648 {
2649 $constraint_sql = "SELECT consrc as constraint_data
2650 FROM pg_constraint, pg_class bc
2651 WHERE conrelid = bc.oid
2652 AND bc.relname = '{$table_name}'
2653 AND NOT EXISTS (
2654 SELECT *
2655 FROM pg_constraint as c, pg_inherits as i
2656 WHERE i.inhrelid = pg_constraint.conrelid
2657 AND c.conname = pg_constraint.conname
2658 AND c.consrc = pg_constraint.consrc
2659 AND c.conrelid = i.inhparent
2660 )";
2661
2662 $constraint_exists = false;
2663
2664 $result = $db->sql_query($constraint_sql);
2665 while ($row = $db->sql_fetchrow($result))
2666 {
2667 if (trim($row['constraint_data']) == trim($column_data['constraint']))
2668 {
2669 $constraint_exists = true;
2670 break;
2671 }
2672 }
2673 $db->sql_freeresult($result);
2674
2675 if (!$constraint_exists)
2676 {
2677 $sql_array[] = 'ADD ' . $column_data['constraint'];
2678 }
2679 }
2680
2681 $sql .= implode(', ', $sql_array);
2682
2683 _sql($sql, $errored, $error_ary);
2684 break;
2685
2686 case 'sqlite':
2687
2688 $sql = "SELECT sql
2689 FROM sqlite_master
2690 WHERE type = 'table'
2691 AND name = '{$table_name}'
2692 ORDER BY type DESC, name;";
2693 $result = _sql($sql, $errored, $error_ary);
2694
2695 if (!$result)
2696 {
2697 break;
2698 }
2699
2700 $row = $db->sql_fetchrow($result);
2701 $db->sql_freeresult($result);
2702
2703 $db->sql_transaction('begin');
2704
2705 // Create a temp table and populate it, destroy the existing one
2706 $db->sql_query(preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']));
2707 $db->sql_query('INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name);
2708 $db->sql_query('DROP TABLE ' . $table_name);
2709
2710 preg_match('#\((.*)\)#s', $row['sql'], $matches);
2711
2712 $new_table_cols = trim($matches[1]);
2713 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
2714 $column_list = array();
2715
2716 foreach ($old_table_cols as $key => $declaration)
2717 {
2718 $entities = preg_split('#\s+#', trim($declaration));
2719 $column_list[] = $entities[0];
2720 if ($entities[0] == $column_name)
2721 {
2722 $old_table_cols[$key] = $column_name . ' ' . $column_data['column_type_sql'];
2723 }
2724 }
2725
2726 $columns = implode(',', $column_list);
2727
2728 // create a new table and fill it up. destroy the temp one
2729 $db->sql_query('CREATE TABLE ' . $table_name . ' (' . implode(',', $old_table_cols) . ');');
2730 $db->sql_query('INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;');
2731 $db->sql_query('DROP TABLE ' . $table_name . '_temp');
2732
2733 $db->sql_transaction('commit');
2734
2735 break;
2736 }
2737 }
2738
2739 function utf8_new_clean_string($text)
2740 {
2741 static $homographs = array();
2742 static $utf8_case_fold_nfkc = '';
2743 if (empty($homographs))
2744 {
2745 global $phpbb_root_path, $phpEx;
2746 if (!function_exists('utf8_case_fold_nfkc') || !file_exists($phpbb_root_path . 'includes/utf/data/confusables.' . $phpEx))
2747 {
2748 if (!file_exists($phpbb_root_path . 'install/data/confusables.' . $phpEx))
2749 {
2750 global $lang;
2751 trigger_error(sprintf($lang['UPDATE_REQUIRES_FILE'], $phpbb_root_path . 'install/data/confusables.' . $phpEx), E_USER_ERROR);
2752 }
2753 $homographs = include($phpbb_root_path . 'install/data/confusables.' . $phpEx);
2754 $utf8_case_fold_nfkc = 'utf8_new_case_fold_nfkc';
2755 }
2756 else
2757 {
2758 $homographs = include($phpbb_root_path . 'includes/utf/data/confusables.' . $phpEx);
2759 $utf8_case_fold_nfkc = 'utf8_case_fold_nfkc';
2760 }
2761 }
2762
2763 $text = $utf8_case_fold_nfkc($text);
2764 $text = strtr($text, $homographs);
2765 // Other control characters
2766 $text = preg_replace('#(?:[\x00-\x1F\x7F]+|(?:\xC2[\x80-\x9F])+)#', '', $text);
2767
2768 // we can use trim here as all the other space characters should have been turned
2769 // into normal ASCII spaces by now
2770 return trim($text);
2771 }
2772
2773 ?>
phpbb is a php-based BBS (buletin board system)