From 3874d31ef04fc2962949e858642e27d32f325474 Mon Sep 17 00:00:00 2001 From: Meik Sievertsen Date: Sun, 8 Jul 2007 18:26:00 +0000 Subject: ok... someone messed up. We will update the packages with the updated database_update script. git-svn-id: file:///svn/phpbb/trunk@7844 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/install/database_update.php | 8 ++++++-- 1 file changed, 6 insertions(+), 2 deletions(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index fc5851804c..f9e3b54494 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -1440,8 +1440,7 @@ function sql_list_index($dbms, $table_name) case 'mysql_40': case 'mysql_41': $sql = 'SHOW KEYS - FROM ' . $table_name .' - WHERE Non_unique = 1'; + FROM ' . $table_name; $col = 'Key_name'; break; @@ -1461,6 +1460,11 @@ function sql_list_index($dbms, $table_name) $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) { + if (($dbms == 'mysql_40' || $dbms == 'mysql_41') && !$row['Non_unique'])) + { + continue; + } + $index_array[] = $row[$col]; } $db->sql_freeresult($result); -- cgit v1.2.1 From de1c2a2ae89bec632cd7053e8f6af5b4c26c4797 Mon Sep 17 00:00:00 2001 From: Meik Sievertsen Date: Sun, 8 Jul 2007 18:40:29 +0000 Subject: *** empty log message *** git-svn-id: file:///svn/phpbb/trunk@7845 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/install/database_update.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index f9e3b54494..8b20d0c942 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -1460,7 +1460,7 @@ function sql_list_index($dbms, $table_name) $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) { - if (($dbms == 'mysql_40' || $dbms == 'mysql_41') && !$row['Non_unique'])) + if (($dbms == 'mysql_40' || $dbms == 'mysql_41') && !$row['Non_unique']) { continue; } -- cgit v1.2.1 From 61703b1ed28ed3327f121acb7329dc04d387f418 Mon Sep 17 00:00:00 2001 From: David M Date: Mon, 9 Jul 2007 17:17:04 +0000 Subject: *** empty log message *** git-svn-id: file:///svn/phpbb/trunk@7852 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/install/database_update.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index 8b20d0c942..559de678bf 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -1001,9 +1001,9 @@ function prepare_column_data($dbms, $column_data) case 'postgres': $sql .= " {$column_type} "; + $sql .= 'NOT NULL'; $sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : ''; - $sql .= 'NOT NULL'; // Unsigned? Then add a CHECK contraint if (in_array($orig_column_type, $unsigned_types)) -- cgit v1.2.1 From c0ea9006c5baf32b5acab6ad7c0fb3cd03c2be41 Mon Sep 17 00:00:00 2001 From: David M Date: Mon, 9 Jul 2007 21:53:15 +0000 Subject: #12965 git-svn-id: file:///svn/phpbb/trunk@7854 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/install/database_update.php | 13 ++++++++++++- 1 file changed, 12 insertions(+), 1 deletion(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index 559de678bf..64a9f369fc 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -672,6 +672,17 @@ if (version_compare($current_version, '3.0.RC2', '<=')) $no_updates = false; } +if (version_compare($current_version, '3.0.RC3', '<=')) +{ + if ($map_dbms === 'postgres') + { + $sql = "SELECT SETVAL('" . FORUMS_TABLE . "_seq',(select case when max(forum_id)>0 then max(forum_id)+1 else 1 end from " . FORUMS_TABLE . '));'; + _sql($sql, $errored, $error_ary); + + $no_updates = false; + } +} + _write_result($no_updates, $errored, $error_ary); $error_ary = array(); @@ -1252,7 +1263,7 @@ function sql_index_drop($dbms, $index_name, $table_name) case 'oracle': case 'postgres': case 'sqlite': - $sql = 'DROP INDEX ' . $table_name . '_' . $index_name; + $sql = 'DROP INDEX ' . $table_name . '_' . $index_name; _sql($sql, $errored, $error_ary); break; } -- cgit v1.2.1 From 84a16e2ac6e1490260e8501146fcf940b123a61a Mon Sep 17 00:00:00 2001 From: David M Date: Mon, 9 Jul 2007 22:39:50 +0000 Subject: - better PostgreSQL support for ALTER TABLE - stress tested sql_list_index(), found some bugs in it :/ git-svn-id: file:///svn/phpbb/trunk@7855 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/install/database_update.php | 55 ++++++++++++++++++++++++++++++++------- 1 file changed, 46 insertions(+), 9 deletions(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index 64a9f369fc..33e4d406e8 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -937,6 +937,8 @@ function prepare_column_data($dbms, $column_data) $sql = ''; + $return_array = array(); + switch ($dbms) { case 'firebird': @@ -1011,14 +1013,23 @@ function prepare_column_data($dbms, $column_data) break; case 'postgres': + $return_array['column_type'] = $column_type; + $return_array['null'] = 'NOT NULL'; + + if (!is_null($column_data[1])) + { + $return_array['default'] = $column_data[1]; + } + $sql .= " {$column_type} "; - $sql .= 'NOT NULL'; + $sql .= 'NOT NULL '; $sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : ''; // Unsigned? Then add a CHECK contraint if (in_array($orig_column_type, $unsigned_types)) { + $return_array['constraint'] = "CHECK ({$column_name} >= 0)"; $sql .= " CHECK ({$column_name} >= 0)"; } break; @@ -1040,9 +1051,9 @@ function prepare_column_data($dbms, $column_data) break; } - return array( - 'column_type_sql' => $sql, - ); + $return_array['column_type_sql'] = $sql; + + return $return_array; } /** @@ -1263,7 +1274,7 @@ function sql_index_drop($dbms, $index_name, $table_name) case 'oracle': case 'postgres': case 'sqlite': - $sql = 'DROP INDEX ' . $table_name . '_' . $index_name; + $sql = 'DROP INDEX ' . $table_name . '_' . $index_name; _sql($sql, $errored, $error_ary); break; } @@ -1476,6 +1487,16 @@ function sql_list_index($dbms, $table_name) continue; } + switch ($dbms) + { + case 'firebird': + case 'oracle': + case 'postgres': + case 'sqlite': + $row[$col] = substr($row[$col], strlen($table_name) + 1); + break; + } + $index_array[] = $row[$col]; } $db->sql_freeresult($result); @@ -1519,16 +1540,32 @@ function sql_column_change($dbms, $table_name, $column_name, $column_data) break; case 'postgres': - $default_pos = strpos($column_data['column_type_sql'], ' DEFAULT'); + $sql = 'ALTER TABLE ' . $table_name . ' '; + + $sql_array = array(); + $sql_array[] = 'ALTER COLUMN ' . $column_name . ' TYPE ' . $column_data['column_type']; - if ($default_pos === false) + if ($column_data['null'] == 'NOT NULL') { - $sql = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN ' . $column_name . ' TYPE ' . $column_data['column_type_sql']; + $sql_array[] = 'ALTER COLUMN ' . $column_name . ' SET NOT NULL'; } else { - $sql = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN ' . $column_name . ' TYPE ' . substr($column_data['column_type_sql'], 0, $default_pos) . ', ALTER COLUMN ' . $column_name . ' SET ' . substr($column_data['column_type_sql'], $default_pos + 1); + $sql_array[] = 'ALTER COLUMN ' . $column_name . ' DROP NOT NULL'; + } + + if (isset($column_data['default'])) + { + $sql_array[] = 'ALTER COLUMN ' . $column_name . " SET DEFAULT '" . $column_data['default'] . "'"; } + + if (isset($column_data['constraint'])) + { + $sql_array[] = 'ALTER COLUMN ' . $column_name . " ADD '" . $column_data['constraint'] . "'"; + } + + $sql .= implode(', ', $sql_array); + _sql($sql, $errored, $error_ary); break; -- cgit v1.2.1 From 020debce6625017ed7509c444165193c3ff60ccc Mon Sep 17 00:00:00 2001 From: David M Date: Tue, 10 Jul 2007 03:54:26 +0000 Subject: thankfully, we never used this feature git-svn-id: file:///svn/phpbb/trunk@7857 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/install/database_update.php | 140 +++++++++++++++++++++++++++++++++++--- 1 file changed, 132 insertions(+), 8 deletions(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index 33e4d406e8..ab7d104cf1 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -859,18 +859,142 @@ function column_exists($dbms, $table, $column_name) { global $db; - $db->sql_return_on_error(true); + switch ($dbms) + { + case 'mysql_40': + case 'mysql_41': + $sql = "SHOW COLUMNS + FROM $table"; + $result = $db->sql_query($sql); + while ($row = $db->sql_fetchrow($result)) + { + // lower case just in case + if (strtolower($row['Field']) == $column_name) + { + $db->sql_freeresult($result); + return true; + } + } + $db->sql_freeresult($result); + return false; + break; - $sql = "SELECT $column_name FROM $table"; - $result = $db->sql_query_limit($sql, 1); - $row = $db->sql_fetchrow($result); - $db->sql_freeresult($result); + // PostgreSQL has a way of doing this in a much simpler way but would + // not allow us to support all versions of PostgreSQL + case 'postgres': + $sql = "SELECT a.attname + FROM pg_class c, pg_attribute a + WHERE c.relname = '{$table}' + AND a.attnum > 0 + AND a.attrelid = c.oid"; + $result = $db->sql_query($sql); + while ($row = $db->sql_fetchrow($result)) + { + // lower case just in case + if (strtolower($row['attname']) == $column_name) + { + $db->sql_freeresult($result); + return true; + } + } + $db->sql_freeresult($result); + return false; + break; - $error = ($db->sql_error_triggered) ? true : false; + // same deal with PostgreSQL, we must perform more complex operations than + // we technically could + case 'mssql': + $sql = "SELECT c.name + FROM syscolumns c + LEFT JOIN sysobjects o (ON c.id = o.id) + WHERE o.name = '{$table}'"; + $result = $db->sql_query($sql); + while ($row = $db->sql_fetchrow($result)) + { + // lower case just in case + if (strtolower($row['name']) == $column_name) + { + $db->sql_freeresult($result); + return true; + } + } + $db->sql_freeresult($result); + return false; + break; - $db->sql_return_on_error(false); + case 'oracle': + $sql = "SELECT column_name + FROM user_tab_columns + WHERE table_name = '{$table}'"; + $result = $db->sql_query($sql); + while ($row = $db->sql_fetchrow($result)) + { + // lower case just in case + if (strtolower($row['column_name']) == $column_name) + { + $db->sql_freeresult($result); + return true; + } + } + $db->sql_freeresult($result); + return false; + break; - return (!$error) ? true : false; + case 'firebird': + $sql = "SELECT RDB$FIELD_NAME as FNAME + FROM RDB$RELATION_FIELDS + WHERE RDB$RELATION_NAME = '{$table}'"; + $result = $db->sql_query($sql); + while ($row = $db->sql_fetchrow($result)) + { + // lower case just in case + if (strtolower($row['fname']) == $column_name) + { + $db->sql_freeresult($result); + return true; + } + } + $db->sql_freeresult($result); + return false; + break; + + // ugh, SQLite + case 'sqlite': + $sql = "SELECT sql + FROM sqlite_master + WHERE type = 'table' + AND name = '{$table}'"; + $result = $db->sql_query($sql); + $row = $db->sql_fetchrow($result); + $db->sql_freeresult($result); + + if (!$result) + { + return false; + } + + preg_match('#\((.*)\)#s', $row['sql'], $matches); + + $cols = trim($matches[1]); + $col_array = preg_split('/,(?![\s\w]+\))/m', $cols); + $column_list = array(); + + foreach ($col_array as $declaration) + { + $entities = preg_split('#\s+#', trim($declaration)); + if ($entities[0] == 'PRIMARY') + { + continue; + } + + if (strtolower($entities[0]) == $column_name) + { + return true; + } + } + return false; + break; + } } /** -- cgit v1.2.1 From 4fa1a7c8276d782b469471e062bab45fbfaa6e4f Mon Sep 17 00:00:00 2001 From: David M Date: Tue, 10 Jul 2007 03:59:08 +0000 Subject: d'oh! git-svn-id: file:///svn/phpbb/trunk@7858 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/install/database_update.php | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index ab7d104cf1..75924ed10f 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -965,19 +965,19 @@ function column_exists($dbms, $table, $column_name) WHERE type = 'table' AND name = '{$table}'"; $result = $db->sql_query($sql); - $row = $db->sql_fetchrow($result); - $db->sql_freeresult($result); if (!$result) { return false; } + $row = $db->sql_fetchrow($result); + $db->sql_freeresult($result); + preg_match('#\((.*)\)#s', $row['sql'], $matches); $cols = trim($matches[1]); $col_array = preg_split('/,(?![\s\w]+\))/m', $cols); - $column_list = array(); foreach ($col_array as $declaration) { -- cgit v1.2.1 From 2ca512113323d9eb73a5c168b375d1081c12c8cf Mon Sep 17 00:00:00 2001 From: David M Date: Wed, 11 Jul 2007 22:25:53 +0000 Subject: - This is well tested and should work, it is not pretty but it does finally bring parity across versions. Tested is RC1->RC2->RC3->RC4-dev, RC2->RC3->RC4-dev and RC3->RC4-dev git-svn-id: file:///svn/phpbb/trunk@7870 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/install/database_update.php | 230 +++++++++++++++++++++++++++++++++++--- 1 file changed, 216 insertions(+), 14 deletions(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index 75924ed10f..6346f6bccf 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -342,6 +342,16 @@ $database_update_info = array( ), ), ), + // Changes from 3.0.RC3 to the next version + '3.0.RC3' => array( + // Remove the following keys + 'change_columns' => array( + BANLIST_TABLE => array( + 'ban_reason' => array('VCHAR_UNI', ''), + 'ban_give_reason' => array('VCHAR_UNI', ''), + ), + ), + ), ); // Determine mapping database type @@ -681,6 +691,76 @@ if (version_compare($current_version, '3.0.RC3', '<=')) $no_updates = false; } + + // we check for: + // ath_opt_id + // ath_op_id + // ACL_ROLES_DATA_TABLE_ath_opt_id + // we want ACL_ROLES_DATA_TABLE_ath_op_id + + $table_index_fix = array( + ACL_ROLES_DATA_TABLE => array( + 'ath_opt_id' => 'ath_op_id', + 'ath_op_id' => 'ath_op_id', + ACL_ROLES_DATA_TABLE . '_ath_opt_id' => 'ath_op_id' + ), + STYLES_IMAGESET_DATA_TABLE => array( + 'i_id' => 'i_d', + 'i_d' => 'i_d', + STYLES_IMAGESET_DATA_TABLE . '_i_id' => 'i_d' + ) + ); + + // we need to create some indicies... + $needed_creation = array(); + + foreach ($table_index_fix as $table_name => $index_info) + { + $index_list = sql_list_fake($map_dbms, $table_name); + foreach ($index_info as $bad_index => $good_index) + { + if (in_array($bad_index, $index_list)) + { + // mysql is actually OK, it won't get a hand in this crud + switch ($map_dbms) + { + // last version, mssql had issues with index removal + case 'mssql': + $sql = 'DROP INDEX ' . $table_name . '\.' . $bad_index; + _sql($sql, $errored, $error_ary); + + $no_updates = false; + break; + + // last version, firebird, oracle, postgresql and sqlite all got bad index names + // we got kinda lucky, tho: they all support the same syntax + case 'firebird': + case 'oracle': + case 'postgres': + case 'sqlite': + $sql = 'DROP INDEX ' . $bad_index; + _sql($sql, $errored, $error_ary); + + $no_updates = false; + break; + } + + $needed_creation[$table_name][$good_index] = 1; + } + } + } + + $new_index_defs = array('ath_op_id' => array('auth_option_id'), 'i_d' => array('imageset_id')); + + foreach ($needed_creation as $bad_table => $index_repair_list) + { + foreach ($index_repair_list as $new_index => $garbage) + { + sql_create_index($map_dbms, $new_index, $bad_table, $new_index_defs[$new_index]); + $no_updates = false; + } + } + } _write_result($no_updates, $errored, $error_ary); @@ -941,9 +1021,9 @@ function column_exists($dbms, $table, $column_name) break; case 'firebird': - $sql = "SELECT RDB$FIELD_NAME as FNAME - FROM RDB$RELATION_FIELDS - WHERE RDB$RELATION_NAME = '{$table}'"; + $sql = "SELECT RDB\$FIELD_NAME as FNAME + FROM RDB\$RELATION_FIELDS + WHERE RDB\$RELATION_NAME = '{$table}'"; $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) { @@ -1384,7 +1464,7 @@ function sql_index_drop($dbms, $index_name, $table_name) switch ($dbms) { case 'mssql': - $sql = 'DROP INDEX ' . $table_name . '\.' . $index_name . ' ON ' . $table_name; + $sql = 'DROP INDEX ' . $table_name . '\.' . $index_name; _sql($sql, $errored, $error_ary); break; @@ -1496,10 +1576,14 @@ function sql_create_unique_index($dbms, $index_name, $table_name, $column) { case 'firebird': case 'postgres': - case 'mysql_40': - case 'mysql_41': case 'oracle': case 'sqlite': + $sql = 'CREATE UNIQUE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')'; + _sql($sql, $errored, $error_ary); + break; + + case 'mysql_40': + case 'mysql_41': $sql = 'CREATE UNIQUE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')'; _sql($sql, $errored, $error_ary); break; @@ -1520,10 +1604,14 @@ function sql_create_index($dbms, $index_name, $table_name, $column) { case 'firebird': case 'postgres': - case 'mysql_40': - case 'mysql_41': case 'oracle': case 'sqlite': + $sql = 'CREATE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')'; + _sql($sql, $errored, $error_ary); + break; + + case 'mysql_40': + case 'mysql_41': $sql = 'CREATE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')'; _sql($sql, $errored, $error_ary); break; @@ -1564,11 +1652,11 @@ function sql_list_index($dbms, $table_name) switch ($dbms) { case 'firebird': - $sql = "SELECT LOWER(RDB$INDEX_NAME) as index_name - FROM RDB$INDICES - WHERE RDB$RELATION_NAME = " . strtoupper($table_name) . " - AND RDB$UNIQUE_FLAG IS NULL - AND RDB$FOREIGN_KEY IS NULL"; + $sql = "SELECT LOWER(RDB\$INDEX_NAME) as index_name + FROM RDB\$INDICES + WHERE RDB\$RELATION_NAME = " . strtoupper($table_name) . " + AND RDB\$UNIQUE_FLAG IS NULL + AND RDB\$FOREIGN_KEY IS NULL"; $col = 'index_name'; break; @@ -1629,6 +1717,90 @@ function sql_list_index($dbms, $table_name) return array_map('strtolower', $index_array); } +// This is totally fake, never use it +// it exists only to mend bad update functions introduced +// * UNIQUE indices +// * PRIMARY keys +function sql_list_fake($dbms, $table_name) +{ + global $dbms_type_map, $db; + global $errored, $error_ary; + + $index_array = array(); + + if ($dbms == 'mssql') + { + $sql = "EXEC sp_statistics '$table_name'"; + $result = $db->sql_query($sql); + while ($row = $db->sql_fetchrow($result)) + { + if ($row['TYPE'] == 3) + { + $index_array[] = $row['INDEX_NAME']; + } + } + $db->sql_freeresult($result); + } + else + { + switch ($dbms) + { + case 'firebird': + $sql = "SELECT LOWER(RDB\$INDEX_NAME) as index_name + FROM RDB\$INDICES + WHERE RDB\$RELATION_NAME = " . strtoupper($table_name) . " + AND RDB\$UNIQUE_FLAG IS NULL + AND RDB\$FOREIGN_KEY IS NULL"; + $col = 'index_name'; + break; + + case 'postgres': + $sql = "SELECT ic.relname as index_name + FROM pg_class bc, pg_class ic, pg_index i + WHERE (bc.oid = i.indrelid) + AND (ic.oid = i.indexrelid) + AND (bc.relname = '" . $table_name . "') + AND (i.indisunique != 't') + AND (i.indisprimary != 't')"; + $col = 'index_name'; + break; + + case 'mysql_40': + case 'mysql_41': + $sql = 'SHOW KEYS + FROM ' . $table_name; + $col = 'Key_name'; + break; + + case 'oracle': + $sql = "SELECT index_name + FROM user_indexes + WHERE table_name = '" . $table_name . "' + AND generated = 'N'"; + break; + + case 'sqlite': + $sql = "PRAGMA index_info('" . $table_name . "');"; + $col = 'name'; + break; + } + + $result = $db->sql_query($sql); + while ($row = $db->sql_fetchrow($result)) + { + if (($dbms == 'mysql_40' || $dbms == 'mysql_41') && !$row['Non_unique']) + { + continue; + } + + $index_array[] = $row[$col]; + } + $db->sql_freeresult($result); + } + + return array_map('strtolower', $index_array); +} + /** * Change column type (not name!) */ @@ -1683,9 +1855,39 @@ function sql_column_change($dbms, $table_name, $column_name, $column_data) $sql_array[] = 'ALTER COLUMN ' . $column_name . " SET DEFAULT '" . $column_data['default'] . "'"; } + // we don't want to double up on constraints if we change different number data types if (isset($column_data['constraint'])) { - $sql_array[] = 'ALTER COLUMN ' . $column_name . " ADD '" . $column_data['constraint'] . "'"; + $constraint_sql = "SELECT consrc as constraint_data + FROM pg_constraint, pg_class bc + WHERE conrelid = bc.oid + AND bc.relname = '{$table_name}' + AND NOT EXISTS ( + SELECT * + FROM pg_constraint as c, pg_inherits as i + WHERE i.inhrelid = pg_constraint.conrelid + AND c.conname = pg_constraint.conname + AND c.consrc = pg_constraint.consrc + AND c.conrelid = i.inhparent + )"; + + $constraint_exists = false; + + $result = $db->sql_query($constraint_sql); + while ($row = $db->sql_fetchrow($result)) + { + if (trim($row['constraint_data']) == trim($column_data['constraint'])) + { + $constraint_exists = true; + break; + } + } + $db->sql_freeresult($result); + + if (!$constraint_exists) + { + $sql_array[] = "ADD '" . $column_data['constraint'] . "'"; + } } $sql .= implode(', ', $sql_array); -- cgit v1.2.1 From 49cc38ebba21dc8b26240a87a23f1d422dd74472 Mon Sep 17 00:00:00 2001 From: David M Date: Wed, 11 Jul 2007 23:27:31 +0000 Subject: - Bring MS SQL up to par git-svn-id: file:///svn/phpbb/trunk@7871 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/install/database_update.php | 9 +++++---- 1 file changed, 5 insertions(+), 4 deletions(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index 6346f6bccf..dfd9a02b3c 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -726,7 +726,7 @@ if (version_compare($current_version, '3.0.RC3', '<=')) { // last version, mssql had issues with index removal case 'mssql': - $sql = 'DROP INDEX ' . $table_name . '\.' . $bad_index; + $sql = 'DROP INDEX ' . $table_name . '.' . $bad_index; _sql($sql, $errored, $error_ary); $no_updates = false; @@ -1166,7 +1166,8 @@ function prepare_column_data($dbms, $column_data) case 'mssql': $sql .= " {$column_type} "; - if (!is_null($column_data[1])) + // we do not support MSSQL DEFAULTs for the near future + /*if (!is_null($column_data[1])) { // For hexadecimal values do not use single quotes if (strpos($column_data[1], '0x') === 0) @@ -1177,7 +1178,7 @@ function prepare_column_data($dbms, $column_data) { $sql .= 'DEFAULT (' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ') '; } - } + }*/ $sql .= 'NOT NULL'; break; @@ -1464,7 +1465,7 @@ function sql_index_drop($dbms, $index_name, $table_name) switch ($dbms) { case 'mssql': - $sql = 'DROP INDEX ' . $table_name . '\.' . $index_name; + $sql = 'DROP INDEX ' . $table_name . '.' . $index_name; _sql($sql, $errored, $error_ary); break; -- cgit v1.2.1 From 002dd8155708415b3c1c828789feccbfc087a387 Mon Sep 17 00:00:00 2001 From: Meik Sievertsen Date: Thu, 12 Jul 2007 16:14:07 +0000 Subject: some more fixes git-svn-id: file:///svn/phpbb/trunk@7875 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/install/database_update.php | 8 +++++++- 1 file changed, 7 insertions(+), 1 deletion(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index dfd9a02b3c..8862cf3991 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -661,13 +661,15 @@ if (version_compare($current_version, '3.0.RC2', '<=')) { continue; } + $new_code = str_replace('&', '&', $code); $new_code = str_replace('<', '<', $new_code); $new_code = str_replace('>', '>', $new_code); $new_code = utf8_htmlspecialchars($new_code); + $sql = 'UPDATE ' . SMILIES_TABLE . ' SET code = \'' . $db->sql_escape($new_code) . '\' - WHERE smiley_id = ' . (int)$id; + WHERE smiley_id = ' . (int) $id; $db->sql_query($sql); } @@ -761,6 +763,10 @@ if (version_compare($current_version, '3.0.RC3', '<=')) } } + // Make sure empty smiley codes do not exist + $sql = 'DELETE FROM ' . SMILIES_TABLE . " + WHERE code = ''"; + $db->sql_query($sql); } _write_result($no_updates, $errored, $error_ary); -- cgit v1.2.1 From c1531d9925585dbbbaa395ba10f16e7575aa1272 Mon Sep 17 00:00:00 2001 From: David M Date: Thu, 12 Jul 2007 17:26:48 +0000 Subject: #13179 git-svn-id: file:///svn/phpbb/trunk@7876 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/install/database_update.php | 33 ++++++++++++++++++++++++++++++--- 1 file changed, 30 insertions(+), 3 deletions(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index 8862cf3991..911fc5ce87 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -334,7 +334,7 @@ $database_update_info = array( ), // Changes from 3.0.RC2 to the next version '3.0.RC2' => array( - // Remove the following keys + // Change the following columns 'change_columns' => array( BANLIST_TABLE => array( 'ban_reason' => array('VCHAR_UNI', ''), @@ -344,12 +344,39 @@ $database_update_info = array( ), // Changes from 3.0.RC3 to the next version '3.0.RC3' => array( - // Remove the following keys + // Change the following columns 'change_columns' => array( - BANLIST_TABLE => array( + BANLIST_TABLE => array( 'ban_reason' => array('VCHAR_UNI', ''), 'ban_give_reason' => array('VCHAR_UNI', ''), ), + STYLES_TABLE => array( + 'style_id' => array('USINT', 0), + 'template_id' => array('USINT', 0), + 'theme_id' => array('USINT', 0), + 'imageset_id' => array('USINT', 0), + ), + STYLES_TEMPLATE_TABLE => array( + 'template_id' => array('USINT', 0), + ), + STYLES_TEMPLATE_DATA_TABLE => array( + 'template_id' => array('USINT', 0), + ), + STYLES_THEME_TABLE => array( + 'theme_id' => array('USINT', 0), + ), + STYLES_IMAGESET_TABLE => array( + 'imageset_id' => array('USINT', 0), + ), + STYLES_IMAGESET_DATA_TABLE => array( + 'imageset_id' => array('USINT', 0), + ), + USERS_TABLE => array( + 'user_style' => array('USINT', 0), + ), + FORUMS_TABLE => array( + 'forum_style' => array('USINT', 0), + ), ), ), ); -- cgit v1.2.1 From 75a51629454e971fd85118647d05a2a7f39931ad Mon Sep 17 00:00:00 2001 From: Meik Sievertsen Date: Thu, 19 Jul 2007 20:38:38 +0000 Subject: again, a lot of bug fixes git-svn-id: file:///svn/phpbb/trunk@7909 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/install/database_update.php | 2 ++ 1 file changed, 2 insertions(+) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index 911fc5ce87..834580c08d 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -794,6 +794,8 @@ if (version_compare($current_version, '3.0.RC3', '<=')) $sql = 'DELETE FROM ' . SMILIES_TABLE . " WHERE code = ''"; $db->sql_query($sql); + + set_config('allow_birthdays', '1'); } _write_result($no_updates, $errored, $error_ary); -- cgit v1.2.1 From 0b9dab3ff60f525978607d3e4c81198707c9d341 Mon Sep 17 00:00:00 2001 From: Nils Adermann Date: Sun, 22 Jul 2007 23:05:30 +0000 Subject: Ok, so finally we have the code to update clean usernames from RC3 :D Updating code is really a pain as all functions are the buggy old versions :( Our new method of finding clean usernames also fixes Bug #12143 git-svn-id: file:///svn/phpbb/trunk@7923 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/install/database_update.php | 409 +++++++++++++++++++++++++++++++++++++- 1 file changed, 407 insertions(+), 2 deletions(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index 834580c08d..b2e34a0418 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -84,6 +84,8 @@ $db->sql_connect($dbhost, $dbuser, $dbpasswd, $dbname, $dbport, false, false); // We do not need this any longer, unset for safety purposes unset($dbpasswd); +$user->ip = (!empty($_SERVER['REMOTE_ADDR'])) ? htmlspecialchars($_SERVER['REMOTE_ADDR']) : ''; + $sql = "SELECT config_value FROM " . CONFIG_TABLE . " WHERE config_name = 'default_lang'"; @@ -486,6 +488,377 @@ else $db->sql_query('DELETE FROM ' . CONFIG_TABLE . " WHERE config_name = 'version_update_from'"); } +// Checks/Operations that have to be completed prior to starting the update itself +$exit = false; +if (version_compare($current_version, '3.0.RC3', '<=')) +{ + $submit = (isset($_POST['resolve_conflicts'])) ? true : false; + $modify_users = request_var('modify_users', array(0 => '')); + $new_usernames = request_var('new_usernames', array(0 => ''), true); + + // the admin decided to change some usernames + if (sizeof($modify_users) && $submit) + { + $sql = 'SELECT user_id, username, user_type + FROM ' . USERS_TABLE . ' + WHERE ' . $db->sql_in_set('user_id', array_keys($modify_users)); + $result = $db->sql_query($sql); + + $users = 0; + while ($row = $db->sql_fetchrow()) + { + $users++; + $user_id = (int) $row['user_id']; + + if (isset($modify_users[$user_id])) + { + $row['action'] = $modify_users[$user_id]; + $modify_users[$user_id] = $row; + } + } + + // only if all ids really existed + if (sizeof($modify_users) == $users) + { + $user->data['user_id'] = ANONYMOUS; + include($phpbb_root_path . 'includes/functions_user.' . $phpEx); + foreach ($modify_users as $user_id => $row) + { + switch ($row['action']) + { + case 'edit': + if (isset($new_usernames[$user_id])) + { + $data = array('username' => utf8_normalize_nfc($new_usernames[$user_id])); + // Need to update config, forum, topic, posting, messages, etc. + if ($data['username'] != $row['username']) + { + $check_ary = array('username' => array( + array('string', false, $config['min_name_chars'], $config['max_name_chars']), + array('username'), + )); + // need a little trick for this to work properly + $user->data['username_clean'] = utf8_clean_string($data['username']) . 'a'; + $errors = validate_data($data, $check_ary); + + if ($errors) + { + include($phpbb_root_path . 'language/' . $language . '/ucp.' . $phpEx); + echo '
'; + foreach ($errors as $error) + { + echo '

' . $lang[$error] . '

'; + } + echo '
'; + } + + if (!$errors) + { + $sql = 'UPDATE ' . USERS_TABLE . ' + SET ' . $db->sql_build_array('UPDATE', array( + 'username' => $data['username'], + 'username_clean' => utf8_clean_string($data['username']) + )) . ' + WHERE user_id = ' . $user_id; + $db->sql_query($sql); + + add_log('user', $user_id, 'LOG_USER_UPDATE_NAME', $row['username'], $data['username']); + user_update_name($row['username'], $data['username']); + } + } + } + break; + + case 'delete_retain': + case 'delete_remove': + if ($user_id != ANONYMOUS && $row['user_type'] != USER_FOUNDER) + { + user_delete(substr($row['action'], 7), $user_id, $row['username']); + add_log('admin', 'LOG_USER_DELETED', $row['username']); + } + break; + } + } + } + } + + // after RC3 a different utf8_clean_string function is used, this requires that + // the unique column username_clean is recalculated, during this recalculation + // duplicates might be created. Since the column has to be unique such usernames + // must not exist. We need identify them and let the admin decide what to do + // about them. + $sql = 'SELECT user_id, username, username_clean + FROM ' . USERS_TABLE; + $result = $db->sql_query($sql); + + $colliding_users = $found_names = array(); + while ($row = $db->sql_fetchrow($result)) + { + // Calculate the new clean name. If it differs from the old one we need + // to make sure there is no collision + $clean_name = utf8_new_clean_string($row['username']); + if ($clean_name != $row['username_clean']) + { + $user_id = (int) $row['user_id']; + + // If this clean name was not the result of another user already ... + if (!isset($found_names[$clean_name])) + { + // then we need to figure out whether there are any other users + // who already had this clean name with the old version + $sql = 'SELECT user_id, username + FROM ' . USERS_TABLE . ' + WHERE username_clean = \'' . $db->sql_escape($clean_name) . '\''; + $result = $db->sql_query($sql); + + $user_ids = array($user_id); + while ($row = $db->sql_fetchrow()) + { + // Make sure this clean name will still be the same with the + // new function. If it is, then we have to add it to the list + // of user ids for this clean name + if (utf8_new_clean_string($row['username']) == $clean_name) + { + $user_ids[] = (int) $row['user_id']; + } + } + $db->sql_freeresult(); + + // if we already found a collision save it + if (sizeof($user_ids) > 1) + { + $colliding_users[$clean_name] = $user_ids; + $found_names[$clean_name] = true; + } + else + { + // otherwise just mark this name as found + $found_names[$clean_name] = $user_id; + } + } + // Else, if we already found the username + else + { + // If the value in the found_names lookup table is only true ... + if ($found_names[$clean_name] === true) + { + // then the actual data was already added to $colliding_users + // and we only need to append the user_id + $colliding_users[$clean_name][] = $user_id; + } + else + { + // otherwise it still keeps the first user_id for this name + // and we need to move the data to $colliding_users, and set + // the value in the found_names lookup table to true, so + // following users will directly be appended to $colliding_users + $colliding_users[$clean_name] = array($found_names[$clean_name], $user_id); + $found_names[$clean_name] = true; + } + } + } + } + unset($found_names); + $db->sql_freeresult($result); + + // now retrieve all information about the users and let the admin decide what to do + if (sizeof($colliding_users)) + { + $exit = true; + include($phpbb_root_path . 'includes/functions_display.' . $phpEx); + include($phpbb_root_path . 'language/' . $language . '/memberlist.' . $phpEx); + include($phpbb_root_path . 'language/' . $language . '/acp/users.' . $phpEx); + + // link a few things to the correct place so we don't get any problems + $user->lang = &$lang; + $user->data['user_id'] = ANONYMOUS; + $user->date_format = $config['default_dateformat']; + + // a little trick to get all user_ids + $user_ids = call_user_func_array('array_merge', array_values($colliding_users)); + + $sql = 'SELECT session_user_id, MAX(session_time) AS session_time + FROM ' . SESSIONS_TABLE . ' + WHERE session_time >= ' . (time() - $config['session_length']) . ' + AND ' . $db->sql_in_set('session_user_id', $user_ids) . ' + GROUP BY session_user_id'; + $result = $db->sql_query($sql); + + $session_times = array(); + while ($row = $db->sql_fetchrow($result)) + { + $session_times[$row['session_user_id']] = $row['session_time']; + } + $db->sql_freeresult($result); + + $sql = 'SELECT * + FROM ' . USERS_TABLE . ' + WHERE ' . $db->sql_in_set('user_id', $user_ids); + $result = $db->sql_query($sql); + + $users = array(); + while ($row = $db->sql_fetchrow($result)) + { + if (isset($session_times[$row['user_id']])) + { + $row['session_time'] = $session_times[$row['user_id']]; + } + else + { + $row['session_time'] = 0; + } + $users[(int) $row['user_id']] = $row; + } + $db->sql_freeresult($result); + unset($session_times); + + // now display a table with all users, some information about them and options + // for the admin: keep name, change name (with text input) or delete user + $u_action = "database_update.$phpEx?language=$language&type=$inline_update"; +?> +

+
+ + + $user_ids) + { +?> +
+ + + + + + + + + + + + + + + $user_id) + { + $row = $users[$user_id]; + + $rank_title = $rank_img = ''; + get_user_rank($row['user_rank'], $row['user_posts'], $rank_title, $rank_img, $rank_img_src); + + $last_visit = (!empty($row['session_time'])) ? $row['session_time'] : $row['user_lastvisit']; + + $info = ''; + switch ($row['user_type']) + { + case USER_INACTIVE: + $info .= $lang['USER_INACTIVE']; + break; + + case USER_IGNORE: + $info .= $lang['BOT']; + break; + + case USER_FOUNDER: + $info .= $lang['FOUNDER']; + break; + + default: + $info .= $lang['USER_ACTIVE']; + } + + if ($user_id == ANONYMOUS) + { + $info = $lang['GUEST']; + } +?> + + + + + + + + + + + +
+ + + format_date($row['user_regdate']) ?>format_date($last_visit); ?>  +
+
+ +
+ + + 		+ +
+
+ +

+ +

+
+sql_query($sql); + + while ($row = $db->sql_fetchrow($result)) + { + $clean_name = utf8_new_clean_string($row['username']); + if ($clean_name != $row['username_clean']) + { + $sql = 'UPDATE ' . USERS_TABLE . ' + SET username_clean = \'' . $db->sql_escape($clean_name) . '\' + WHERE user_id = ' . (int) $row['user_id']; + $db->sql_query($sql); + } + } + } + unset($colliding_users); +} + +if ($exit) +{ +?> + + + + + + + + +
+ Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group +
+ + + + + +



@@ -876,8 +1249,6 @@ else } // Add database update to log - -$user->ip = (!empty($_SERVER['REMOTE_ADDR'])) ? htmlspecialchars($_SERVER['REMOTE_ADDR']) : ''; add_log('admin', 'LOG_UPDATE_DATABASE', $orig_version, $updates_to_version); // Now we purge the session table as well as all cache files @@ -1984,4 +2355,38 @@ function sql_column_change($dbms, $table_name, $column_name, $column_data) } } +function utf8_new_clean_string($text) +{ + static $homographs = array(); + static $utf8_case_fold_nfkc = ''; + if (empty($homographs)) + { + global $phpbb_root_path, $phpEx; + if (!function_exists('utf8_case_fold_nfkc') || !file_exists($phpbb_root_path . 'includes/utf/data/confusables.' . $phpEx)) + { + if (!file_exists($phpbb_root_path . 'install/data/confusables.' . $phpEx)) + { + global $lang; + trigger_error(sprintf($lang['UPDATE_REQUIRES_FILE'], $phpbb_root_path . 'install/data/confusables.' . $phpEx), E_USER_ERROR); + } + $homographs = include($phpbb_root_path . 'install/data/confusables.' . $phpEx); + $utf8_case_fold_nfkc = 'utf8_new_case_fold_nfkc'; + } + else + { + $homographs = include($phpbb_root_path . 'includes/utf/data/confusables.' . $phpEx); + $utf8_case_fold_nfkc = 'utf8_case_fold_nfkc'; + } + } + + $text = $utf8_case_fold_nfkc($text); + $text = strtr($text, $homographs); + // Other control characters + $text = preg_replace('#(?:[\x00-\x1F\x7F]+|(?:\xC2[\x80-\x9F])+)#', '', $text); + + // we can use trim here as all the other space characters should have been turned + // into normal ASCII spaces by now + return trim($text); +} + ?> \ No newline at end of file -- cgit v1.2.1 From d4fb1870be8f2aee08b7788b6293bb5691f6c345 Mon Sep 17 00:00:00 2001 From: Meik Sievertsen Date: Mon, 23 Jul 2007 16:25:26 +0000 Subject: needed fixes git-svn-id: file:///svn/phpbb/trunk@7929 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/install/database_update.php | 26 ++++++++++++++++++++------ 1 file changed, 20 insertions(+), 6 deletions(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index b2e34a0418..f37a9b2bc2 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -492,6 +492,16 @@ else $exit = false; if (version_compare($current_version, '3.0.RC3', '<=')) { +/*

+ +

Clean Usernames

+ +
+

Please note that this can take a while... Please do not stop the script.

+*/ + + flush(); + $submit = (isset($_POST['resolve_conflicts'])) ? true : false; $modify_users = request_var('modify_users', array(0 => '')); $new_usernames = request_var('new_usernames', array(0 => ''), true); @@ -505,7 +515,7 @@ if (version_compare($current_version, '3.0.RC3', '<=')) $result = $db->sql_query($sql); $users = 0; - while ($row = $db->sql_fetchrow()) + while ($row = $db->sql_fetchrow($result)) { $users++; $user_id = (int) $row['user_id']; @@ -516,6 +526,7 @@ if (version_compare($current_version, '3.0.RC3', '<=')) $modify_users[$user_id] = $row; } } + $db->sql_freeresult($result); // only if all ids really existed if (sizeof($modify_users) == $users) @@ -554,10 +565,11 @@ if (version_compare($current_version, '3.0.RC3', '<=')) if (!$errors) { + // We use utf8_new_clean_string() here to make sure the new one is really used. $sql = 'UPDATE ' . USERS_TABLE . ' SET ' . $db->sql_build_array('UPDATE', array( 'username' => $data['username'], - 'username_clean' => utf8_clean_string($data['username']) + 'username_clean' => utf8_new_clean_string($data['username']) )) . ' WHERE user_id = ' . $user_id; $db->sql_query($sql); @@ -588,7 +600,8 @@ if (version_compare($current_version, '3.0.RC3', '<=')) // must not exist. We need identify them and let the admin decide what to do // about them. $sql = 'SELECT user_id, username, username_clean - FROM ' . USERS_TABLE; + FROM ' . USERS_TABLE . ' + ORDER BY user_id'; $result = $db->sql_query($sql); $colliding_users = $found_names = array(); @@ -609,10 +622,10 @@ if (version_compare($current_version, '3.0.RC3', '<=')) $sql = 'SELECT user_id, username FROM ' . USERS_TABLE . ' WHERE username_clean = \'' . $db->sql_escape($clean_name) . '\''; - $result = $db->sql_query($sql); + $result2 = $db->sql_query($sql); $user_ids = array($user_id); - while ($row = $db->sql_fetchrow()) + while ($row = $db->sql_fetchrow($result2)) { // Make sure this clean name will still be the same with the // new function. If it is, then we have to add it to the list @@ -622,7 +635,7 @@ if (version_compare($current_version, '3.0.RC3', '<=')) $user_ids[] = (int) $row['user_id']; } } - $db->sql_freeresult(); + $db->sql_freeresult($result2); // if we already found a collision save it if (sizeof($user_ids) > 1) @@ -832,6 +845,7 @@ if (version_compare($current_version, '3.0.RC3', '<=')) $db->sql_query($sql); } } + $db->sql_freeresult($result); } unset($colliding_users); } -- cgit v1.2.1 From 2ee8394efa222c24edc68f917d22e0da5aefc119 Mon Sep 17 00:00:00 2001 From: Meik Sievertsen Date: Mon, 23 Jul 2007 18:59:46 +0000 Subject: *** empty log message *** git-svn-id: file:///svn/phpbb/trunk@7932 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/install/database_update.php | 6 ++++-- 1 file changed, 4 insertions(+), 2 deletions(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index f37a9b2bc2..fb10a62fda 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -492,7 +492,8 @@ else $exit = false; if (version_compare($current_version, '3.0.RC3', '<=')) { -/*

+/* +

Clean Usernames

@@ -729,6 +730,7 @@ if (version_compare($current_version, '3.0.RC3', '<=')) // for the admin: keep name, change name (with text input) or delete user $u_action = "database_update.$phpEx?language=$language&type=$inline_update"; ?> +

@@ -788,7 +790,7 @@ if (version_compare($current_version, '3.0.RC3', '<=')) ?> - +
-- cgit v1.2.1 From 0a8a67eb10ddb5e8e493392ad7df3afe20d5ecd1 Mon Sep 17 00:00:00 2001 From: Nils Adermann Date: Tue, 24 Jul 2007 08:57:24 +0000 Subject: should speed things up a bit git-svn-id: file:///svn/phpbb/trunk@7933 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/install/database_update.php | 32 +++++++++++++++++++++++--------- 1 file changed, 23 insertions(+), 9 deletions(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index fb10a62fda..082d9a530e 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -566,11 +566,10 @@ if (version_compare($current_version, '3.0.RC3', '<=')) if (!$errors) { - // We use utf8_new_clean_string() here to make sure the new one is really used. $sql = 'UPDATE ' . USERS_TABLE . ' SET ' . $db->sql_build_array('UPDATE', array( 'username' => $data['username'], - 'username_clean' => utf8_new_clean_string($data['username']) + 'username_clean' => utf8_clean_string($data['username']) )) . ' WHERE user_id = ' . $user_id; $db->sql_query($sql); @@ -602,7 +601,7 @@ if (version_compare($current_version, '3.0.RC3', '<=')) // about them. $sql = 'SELECT user_id, username, username_clean FROM ' . USERS_TABLE . ' - ORDER BY user_id'; + ORDER BY user_id ASC'; $result = $db->sql_query($sql); $colliding_users = $found_names = array(); @@ -622,7 +621,9 @@ if (version_compare($current_version, '3.0.RC3', '<=')) // who already had this clean name with the old version $sql = 'SELECT user_id, username FROM ' . USERS_TABLE . ' - WHERE username_clean = \'' . $db->sql_escape($clean_name) . '\''; + WHERE username_clean = \'' . $db->sql_escape($clean_name) . '\' + AND user_id > ' . $user_id . ' + ORDER BY user_id'; $result2 = $db->sql_query($sql); $user_ids = array($user_id); @@ -672,7 +673,6 @@ if (version_compare($current_version, '3.0.RC3', '<=')) } } } - unset($found_names); $db->sql_freeresult($result); // now retrieve all information about the users and let the admin decide what to do @@ -833,22 +833,36 @@ if (version_compare($current_version, '3.0.RC3', '<=')) else { $sql = 'SELECT user_id, username, username_clean - FROM ' . USERS_TABLE; + FROM ' . USERS_TABLE . ' + WHERE ' . $db->sql_in_set('user_id', array_values($found_names)); $result = $db->sql_query($sql); - + + $found_names = array(); while ($row = $db->sql_fetchrow($result)) { $clean_name = utf8_new_clean_string($row['username']); if ($clean_name != $row['username_clean']) { + $user_id = (int) $row['user_id']; + $found_names[$user_id] = $clean_name; + // impossible unique clean name $sql = 'UPDATE ' . USERS_TABLE . ' - SET username_clean = \'' . $db->sql_escape($clean_name) . '\' - WHERE user_id = ' . (int) $row['user_id']; + SET username_clean = \' ' . $user_id . '\' + WHERE user_id = ' . $user_id; $db->sql_query($sql); } } $db->sql_freeresult($result); + + foreach ($found_names as $user_id => $clean_name) + { + $sql = 'UPDATE ' . USERS_TABLE . ' + SET username_clean = \'' . $db->sql_escape($clean_name) . '\' + WHERE user_id = ' . $user_id; + $db->sql_query($sql); + } } + unset($found_names); unset($colliding_users); } -- cgit v1.2.1 From 306581d905c508e6d4a7bafebce1b90dbedbbf4a Mon Sep 17 00:00:00 2001 From: Meik Sievertsen Date: Tue, 24 Jul 2007 15:17:47 +0000 Subject: this should work fine now... git-svn-id: file:///svn/phpbb/trunk@7937 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/install/database_update.php | 66 +++++++++++++++++++++++++++++---------- 1 file changed, 49 insertions(+), 17 deletions(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index 082d9a530e..74ffdc8c8a 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -1,14 +1,14 @@ + if (!isset($lang['CLEANING_USERNAMES'])) + { + $lang['CLEANING_USERNAMES'] = 'Cleaning usernames'; + } + + if (!isset($lang['LONG_SCRIPT_EXECUTION'])) + { + $lang['LONG_SCRIPT_EXECUTION'] = 'Please note that this can take a while... Please do not stop the script.'; + } + +?> +



-

Clean Usernames

+


-

Please note that this can take a while... Please do not stop the script.

-*/ + + +

+

:: +sql_query($sql); $colliding_users = $found_names = array(); + $echos = 0; + while ($row = $db->sql_fetchrow($result)) { // Calculate the new clean name. If it differs from the old one we need // to make sure there is no collision $clean_name = utf8_new_clean_string($row['username']); + if ($clean_name != $row['username_clean']) { + // Check if there would be a collission, if not put it up for changing $user_id = (int) $row['user_id']; // If this clean name was not the result of another user already ... @@ -621,14 +639,18 @@ if (version_compare($current_version, '3.0.RC3', '<=')) // who already had this clean name with the old version $sql = 'SELECT user_id, username FROM ' . USERS_TABLE . ' - WHERE username_clean = \'' . $db->sql_escape($clean_name) . '\' - AND user_id > ' . $user_id . ' - ORDER BY user_id'; + WHERE username_clean = \'' . $db->sql_escape($clean_name) . '\''; $result2 = $db->sql_query($sql); $user_ids = array($user_id); while ($row = $db->sql_fetchrow($result2)) { + // For not trimmed entries this could happen, yes. ;) + if ($row['user_id'] == $user_id) + { + continue; + } + // Make sure this clean name will still be the same with the // new function. If it is, then we have to add it to the list // of user ids for this clean name @@ -672,6 +694,13 @@ if (version_compare($current_version, '3.0.RC3', '<=')) } } } + + if (($echos % 1000) == 0) + { + echo '.'; + flush(); + } + $echos++; } $db->sql_freeresult($result); @@ -730,7 +759,8 @@ if (version_compare($current_version, '3.0.RC3', '<=')) // for the admin: keep name, change name (with text input) or delete user $u_action = "database_update.$phpEx?language=$language&type=$inline_update"; ?> -

+



+

@@ -830,7 +860,7 @@ if (version_compare($current_version, '3.0.RC3', '<=')) sql_fetchrow($result)) { $clean_name = utf8_new_clean_string($row['username']); + if ($clean_name != $row['username_clean']) { $user_id = (int) $row['user_id']; $found_names[$user_id] = $clean_name; + // impossible unique clean name - $sql = 'UPDATE ' . USERS_TABLE . ' - SET username_clean = \' ' . $user_id . '\' - WHERE user_id = ' . $user_id; + $sql = 'UPDATE ' . USERS_TABLE . " + SET username_clean = ' {$user_id}' + WHERE user_id = {$user_id}"; $db->sql_query($sql); } } -- cgit v1.2.1 From 4ca00cba39a0169bd521cbe7e9bd24d3bf3c1cd2 Mon Sep 17 00:00:00 2001 From: Meik Sievertsen Date: Wed, 25 Jul 2007 16:06:11 +0000 Subject: please have a second look at the change within session.php - we had a few "doubled" keys within the db... git-svn-id: file:///svn/phpbb/trunk@7946 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/install/database_update.php | 4 ++++ 1 file changed, 4 insertions(+) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index 74ffdc8c8a..395eefc1f1 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -608,6 +608,8 @@ if (version_compare($current_version, '3.0.RC3', '<='))

:: purge(); +exit; + ?> -- cgit v1.2.1 From d54b42a04b377ae0d3b6b1d327326eca17dbe528 Mon Sep 17 00:00:00 2001 From: Nils Adermann Date: Wed, 25 Jul 2007 17:58:39 +0000 Subject: - cron now uses a locking variable to make sure it does not spawn too many webserver processes [Bug #12741] git-svn-id: file:///svn/phpbb/trunk@7947 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/install/database_update.php | 1 + 1 file changed, 1 insertion(+) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index 395eefc1f1..d255688f55 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -1233,6 +1233,7 @@ if (version_compare($current_version, '3.0.RC3', '<=')) $db->sql_query($sql); set_config('allow_birthdays', '1'); + set_config('cron_lock', '0', true); } _write_result($no_updates, $errored, $error_ary); -- cgit v1.2.1 From a43ead8ee7db832871db9cd2c4f294108398f4b0 Mon Sep 17 00:00:00 2001 From: Meik Sievertsen Date: Thu, 26 Jul 2007 15:51:11 +0000 Subject: do not get too excited git-svn-id: file:///svn/phpbb/trunk@7954 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/install/database_update.php | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index d255688f55..ffdedf0fd9 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -10,6 +10,7 @@ $updates_to_version = '3.0.RC4'; +// Return if we "just include it" to find out for which version the database update is responsuble for if (defined('IN_PHPBB') && defined('IN_INSTALL')) { return; @@ -20,7 +21,7 @@ if (defined('IN_PHPBB') && defined('IN_INSTALL')) define('IN_PHPBB', true); define('IN_INSTALL', true); -$phpbb_root_path = './../'; +$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : './../'; $phpEx = substr(strrchr(__FILE__, '.'), 1); // Report all errors, except notices -- cgit v1.2.1 From 5d52b5185300af5d13dc5db7062759bce7ee949e Mon Sep 17 00:00:00 2001 From: Meik Sievertsen Date: Sat, 28 Jul 2007 10:41:55 +0000 Subject: let it work under different languages. git-svn-id: file:///svn/phpbb/trunk@7967 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/install/database_update.php | 32 ++++++++++++++++++++++++-------- 1 file changed, 24 insertions(+), 8 deletions(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index ffdedf0fd9..ffd4774c59 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -493,16 +493,25 @@ else $exit = false; if (version_compare($current_version, '3.0.RC3', '<=')) { + // Define missing language entries... if (!isset($lang['CLEANING_USERNAMES'])) { - $lang['CLEANING_USERNAMES'] = 'Cleaning usernames'; + $lang = array_merge($lang, array( + 'CLEANING_USERNAMES' => 'Cleaning usernames', + 'LONG_SCRIPT_EXECUTION' => 'Please note that this can take a while... Please do not stop the script.', + '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.', + 'USER_ACTIVE' => 'Active user', + 'USER_INACTIVE' => 'Inactive user', + 'BOT' => 'Spider/Robot', + 'UPDATE_REQUIRES_FILE' => 'The updater requires that the following file is present: %s', + + 'DELETE_USER_REMOVE' => 'Delete user and remove posts', + 'DELETE_USER_RETAIN' => 'Delete user but keep posts', + 'EDIT_USERNAME' => 'Edit username', + 'KEEP_OLD_NAME' => 'Keep username', + 'NEW_USERNAME' => 'New username', + )); } - - if (!isset($lang['LONG_SCRIPT_EXECUTION'])) - { - $lang['LONG_SCRIPT_EXECUTION'] = 'Please note that this can take a while... Please do not stop the script.'; - } - ?>



@@ -1212,7 +1221,14 @@ if (version_compare($current_version, '3.0.RC3', '<=')) break; } - $needed_creation[$table_name][$good_index] = 1; + // If the good index already exist we do not need to create it again... + if (($map_dbms == 'mysql_40' || $map_dbms == 'mysql_41') && $bad_index == $good_index) + { + } + else + { + $needed_creation[$table_name][$good_index] = 1; + } } } } -- cgit v1.2.1 From 1db9e62e084e29638e785c93f917e6e2acc13986 Mon Sep 17 00:00:00 2001 From: Meik Sievertsen Date: Sat, 28 Jul 2007 11:47:02 +0000 Subject: same right for all. ;) Groups table using same column types as the users table for avatars. git-svn-id: file:///svn/phpbb/trunk@7968 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/install/database_update.php | 16 ++++++++-------- 1 file changed, 8 insertions(+), 8 deletions(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index ffd4774c59..34f0aebb12 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -380,6 +380,11 @@ $database_update_info = array( FORUMS_TABLE => array( 'forum_style' => array('USINT', 0), ), + GROUPS_TABLE => array( + 'group_avatar_type' => array('TINT:2', 0), + 'group_avatar_width' => array('USINT', 0), + 'group_avatar_height' => array('USINT', 0), + ), ), ), ); @@ -467,7 +472,6 @@ while ($row = $db->sql_fetchrow($result)) } $db->sql_freeresult($result); - echo $lang['PREVIOUS_VERSION'] . ' :: ' . $config['version'] . '
'; echo $lang['UPDATED_VERSION'] . ' :: ' . $updates_to_version . ''; @@ -1164,8 +1168,6 @@ if (version_compare($current_version, '3.0.RC3', '<=')) { $sql = "SELECT SETVAL('" . FORUMS_TABLE . "_seq',(select case when max(forum_id)>0 then max(forum_id)+1 else 1 end from " . FORUMS_TABLE . '));'; _sql($sql, $errored, $error_ary); - - $no_updates = false; } // we check for: @@ -1204,8 +1206,6 @@ if (version_compare($current_version, '3.0.RC3', '<=')) case 'mssql': $sql = 'DROP INDEX ' . $table_name . '.' . $bad_index; _sql($sql, $errored, $error_ary); - - $no_updates = false; break; // last version, firebird, oracle, postgresql and sqlite all got bad index names @@ -1216,8 +1216,6 @@ if (version_compare($current_version, '3.0.RC3', '<=')) case 'sqlite': $sql = 'DROP INDEX ' . $bad_index; _sql($sql, $errored, $error_ary); - - $no_updates = false; break; } @@ -1247,10 +1245,12 @@ if (version_compare($current_version, '3.0.RC3', '<=')) // Make sure empty smiley codes do not exist $sql = 'DELETE FROM ' . SMILIES_TABLE . " WHERE code = ''"; - $db->sql_query($sql); + _sql($sql, $errored, $error_ary); set_config('allow_birthdays', '1'); set_config('cron_lock', '0', true); + + $no_updates = false; } _write_result($no_updates, $errored, $error_ary); -- cgit v1.2.1