aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorDavid M <davidmj@users.sourceforge.net>2006-12-23 17:50:48 +0000
committerDavid M <davidmj@users.sourceforge.net>2006-12-23 17:50:48 +0000
commit6d0a00a446396ab72d8fb0475f4cd1c8ab9fa151 (patch)
treeb63ff460ba8ef1fec4ef5b5fae3a9dddf6d252e9
parent9b6e76a808ed7bc4c62ad98df596db45e8f5fabb (diff)
downloadforums-6d0a00a446396ab72d8fb0475f4cd1c8ab9fa151.tar
forums-6d0a00a446396ab72d8fb0475f4cd1c8ab9fa151.tar.gz
forums-6d0a00a446396ab72d8fb0475f4cd1c8ab9fa151.tar.bz2
forums-6d0a00a446396ab72d8fb0475f4cd1c8ab9fa151.tar.xz
forums-6d0a00a446396ab72d8fb0475f4cd1c8ab9fa151.zip
#6492
git-svn-id: file:///svn/phpbb/trunk@6793 89ea8834-ac86-4346-8a33-228a782c2dd0
Diffstat
-rw-r--r--phpBB/develop/create_schema_files.php5
-rw-r--r--phpBB/install/database_update.php149
-rw-r--r--phpBB/install/schemas/firebird_schema.sql4
-rw-r--r--phpBB/install/schemas/mssql_schema.sql10
-rw-r--r--phpBB/install/schemas/mysql_40_schema.sql3
-rw-r--r--phpBB/install/schemas/mysql_41_schema.sql3
-rw-r--r--phpBB/install/schemas/oracle_schema.sql7
-rw-r--r--phpBB/install/schemas/postgres_schema.sql5
-rw-r--r--phpBB/install/schemas/sqlite_schema.sql5
9 files changed, 166 insertions, 25 deletions
diff --git a/phpBB/develop/create_schema_files.php b/phpBB/develop/create_schema_files.php
index 3b51a15bd4..f17623da44 100644
--- a/phpBB/develop/create_schema_files.php
+++ b/phpBB/develop/create_schema_files.php
@@ -1934,10 +1934,7 @@ function get_schema_struct()
'friend' => array('BOOL', 0),
'foe' => array('BOOL', 0),
),
- 'KEYS' => array(
- 'user_id' => array('INDEX', 'user_id'),
- 'zebra_id' => array('INDEX', 'zebra_id'),
- ),
+ 'PRIMARY_KEY' => array('user_id', 'zebra_id'),
);
return $schema_data;
diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php
index 0f3f5b3aa2..6c1f030d6b 100644
--- a/phpBB/install/database_update.php
+++ b/phpBB/install/database_update.php
@@ -321,6 +321,23 @@ $database_update_info = array(
),
),
),
+ // Latest version
+ '3.0.b5' => array(
+ // Remove the following keys
+ 'drop_keys' => array(
+ ZEBRA_TABLE => array(
+ 'user_id',
+ 'zebra_id',
+ ),
+ ),
+ // Add the following primary keys
+ 'add_primary_keys' => array(
+ ZEBRA_TABLE => array(
+ 'user_id',
+ 'zebra_id',
+ ),
+ ),
+ ),
);
// Determine mapping database type
@@ -472,6 +489,27 @@ foreach ($database_update_info as $version => $schema_changes)
}
}
}
+
+ // Remove keys?
+ if (!empty($schema_changes['drop_keys']))
+ {
+ foreach ($schema_changes['drop_keys'] as $table => $indexes)
+ {
+ foreach ($indexes as $index_name)
+ {
+ sql_index_drop($map_dbms, $index_name, $table);
+ }
+ }
+ }
+
+ // Add primary keys?
+ if (!empty($schema_changes['add_primary_keys']))
+ {
+ foreach ($schema_changes['add_primary_keys'] as $table => $columns)
+ {
+ sql_create_primary_key($map_dbms, $table, $columns);
+ }
+ }
}
_write_result($no_updates, $errored, $error_ary);
@@ -968,6 +1006,117 @@ function sql_column_add($dbms, $table_name, $column_name, $column_data)
}
}
+function sql_index_drop($dbms, $index_name, $table_name)
+{
+ global $dbms_type_map, $db;
+ global $errored, $error_ary;
+
+ switch ($dbms)
+ {
+ case 'mssql':
+ $sql = 'DROP INDEX ' . $table_name . '\.' . $index_name . ' ON ' . $table_name;
+ _sql($sql, $errored, $error_ary);
+ break;
+
+ case 'mysql_40':
+ case 'mysql_41':
+ $sql = 'DROP INDEX ' . $index_name . ' ON ' . $table_name;
+ _sql($sql, $errored, $error_ary);
+ break;
+
+ case 'firebird':
+ case 'oracle':
+ case 'postgres':
+ case 'sqlite':
+ $sql = 'DROP INDEX ' . $table_name . '_' . $index_name;
+ _sql($sql, $errored, $error_ary);
+ break;
+ }
+}
+
+function sql_create_primary_key($dbms, $table_name, $column)
+{
+ global $dbms_type_map, $db;
+ global $errored, $error_ary;
+
+ switch ($dbms)
+ {
+ case 'firebird':
+ case 'postgres':
+ $sql = 'ALTER TABLE ' . $table_name . ' ADD PRIMARY KEY (' . implode(', ', $column) . ')';
+ _sql($sql, $errored, $error_ary);
+ break;
+
+ case 'mssql':
+ $sql = "ALTER TABLE [{$table_name}] WITH NOCHECK ADD ";
+ $sql .= "CONSTRAINT [PK_{$table_name}] PRIMARY KEY CLUSTERED (";
+ $sql .= '[' . implode("],\n\t\t[", $column) . ']';
+ $sql .= ') ON [PRIMARY]';
+ _sql($sql, $errored, $error_ary);
+ break;
+
+ case 'mysql_40':
+ case 'mysql_41':
+ $sql = 'ALTER TABLE ' . $table_name . ' ADD PRIMARY KEY (' . implode(', ', $column) . ')';
+ _sql($sql, $errored, $error_ary);
+ break;
+
+ case 'oracle':
+ $sql = 'ALTER TABLE ' . $table_name . 'add CONSTRAINT pk_' . $table_name . ' PRIMARY KEY (' . implode(', ', $column) . ')';
+ _sql($sql, $errored, $error_ary);
+ break;
+
+ case 'sqlite':
+ $sql = "SELECT sql
+ FROM sqlite_master
+ WHERE type = 'table'
+ AND name = '{$table_name}'
+ ORDER BY type DESC, name;";
+ $result = _sql($sql, $errored, $error_ary);
+
+ if (!$result)
+ {
+ break;
+ }
+
+ $row = $db->sql_fetchrow($result);
+ $db->sql_freeresult($result);
+
+ $db->sql_transaction('begin');
+
+ // Create a backup table and populate it, destroy the existing one
+ $db->sql_query(preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']));
+ $db->sql_query('INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name);
+ $db->sql_query('DROP TABLE ' . $table_name);
+
+ preg_match('#\((.*)\)#s', $row['sql'], $matches);
+
+ $new_table_cols = trim($matches[1]);
+ $old_table_cols = preg_split('/,(?=[\\sa-z])/im', $new_table_cols);
+ $column_list = array();
+
+ foreach ($old_table_cols as $declaration)
+ {
+ $entities = preg_split('#\s+#', trim($declaration));
+ if ($entities == 'PRIMARY')
+ {
+ continue;
+ }
+ $column_list[] = $entities[0];
+ }
+
+ $columns = implode(',', $column_list);
+
+ // create a new table and fill it up. destroy the temp one
+ $db->sql_query('CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ', PRIMARY KEY (' . implode(', ', $column) . '));');
+ $db->sql_query('INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;');
+ $db->sql_query('DROP TABLE ' . $table_name . '_temp');
+
+ $db->sql_transaction('commit');
+ break;
+ }
+}
+
/**
* Change column type (not name!)
*/
diff --git a/phpBB/install/schemas/firebird_schema.sql b/phpBB/install/schemas/firebird_schema.sql
index 0f01640a85..fe38795ee8 100644
--- a/phpBB/install/schemas/firebird_schema.sql
+++ b/phpBB/install/schemas/firebird_schema.sql
@@ -1502,6 +1502,6 @@ CREATE TABLE phpbb_zebra (
foe INTEGER DEFAULT 0 NOT NULL
);;
-CREATE INDEX phpbb_zebra_user_id ON phpbb_zebra(user_id);;
-CREATE INDEX phpbb_zebra_zebra_id ON phpbb_zebra(zebra_id);;
+ALTER TABLE phpbb_zebra ADD PRIMARY KEY (user_id, zebra_id);;
+
diff --git a/phpBB/install/schemas/mssql_schema.sql b/phpBB/install/schemas/mssql_schema.sql
index 888f66f472..8c00685f68 100644
--- a/phpBB/install/schemas/mssql_schema.sql
+++ b/phpBB/install/schemas/mssql_schema.sql
@@ -1744,10 +1744,12 @@ CREATE TABLE [phpbb_zebra] (
) ON [PRIMARY]
GO
-CREATE INDEX [user_id] ON [phpbb_zebra]([user_id]) ON [PRIMARY]
-GO
-
-CREATE INDEX [zebra_id] ON [phpbb_zebra]([zebra_id]) ON [PRIMARY]
+ALTER TABLE [phpbb_zebra] WITH NOCHECK ADD
+ CONSTRAINT [PK_phpbb_zebra] PRIMARY KEY CLUSTERED
+ (
+ [user_id],
+ [zebra_id]
+ ) ON [PRIMARY]
GO
diff --git a/phpBB/install/schemas/mysql_40_schema.sql b/phpBB/install/schemas/mysql_40_schema.sql
index 27d36edd12..ee3d290c42 100644
--- a/phpBB/install/schemas/mysql_40_schema.sql
+++ b/phpBB/install/schemas/mysql_40_schema.sql
@@ -1066,8 +1066,7 @@ CREATE TABLE phpbb_zebra (
zebra_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
friend tinyint(1) UNSIGNED DEFAULT '0' NOT NULL,
foe tinyint(1) UNSIGNED DEFAULT '0' NOT NULL,
- KEY user_id (user_id),
- KEY zebra_id (zebra_id)
+ PRIMARY KEY (user_id, zebra_id)
);
diff --git a/phpBB/install/schemas/mysql_41_schema.sql b/phpBB/install/schemas/mysql_41_schema.sql
index 4b6d4b008b..c203a8a574 100644
--- a/phpBB/install/schemas/mysql_41_schema.sql
+++ b/phpBB/install/schemas/mysql_41_schema.sql
@@ -1066,8 +1066,7 @@ CREATE TABLE phpbb_zebra (
zebra_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
friend tinyint(1) UNSIGNED DEFAULT '0' NOT NULL,
foe tinyint(1) UNSIGNED DEFAULT '0' NOT NULL,
- KEY user_id (user_id),
- KEY zebra_id (zebra_id)
+ PRIMARY KEY (user_id, zebra_id)
) CHARACTER SET `utf8` COLLATE `utf8_bin`;
diff --git a/phpBB/install/schemas/oracle_schema.sql b/phpBB/install/schemas/oracle_schema.sql
index 7a5f05c274..f65d367356 100644
--- a/phpBB/install/schemas/oracle_schema.sql
+++ b/phpBB/install/schemas/oracle_schema.sql
@@ -1935,12 +1935,9 @@ CREATE TABLE phpbb_zebra (
user_id number(8) DEFAULT '0' NOT NULL,
zebra_id number(8) DEFAULT '0' NOT NULL,
friend number(1) DEFAULT '0' NOT NULL,
- foe number(1) DEFAULT '0' NOT NULL
+ foe number(1) DEFAULT '0' NOT NULL,
+ CONSTRAINT pk_phpbb_zebra PRIMARY KEY (user_id, zebra_id)
)
/
-CREATE INDEX phpbb_zebra_user_id ON phpbb_zebra (user_id)
-/
-CREATE INDEX phpbb_zebra_zebra_id ON phpbb_zebra (zebra_id)
-/
diff --git a/phpBB/install/schemas/postgres_schema.sql b/phpBB/install/schemas/postgres_schema.sql
index 5eb8775adf..ccd33c4f56 100644
--- a/phpBB/install/schemas/postgres_schema.sql
+++ b/phpBB/install/schemas/postgres_schema.sql
@@ -1335,11 +1335,10 @@ CREATE TABLE phpbb_zebra (
user_id INT4 DEFAULT '0' NOT NULL CHECK (user_id >= 0),
zebra_id INT4 DEFAULT '0' NOT NULL CHECK (zebra_id >= 0),
friend INT2 DEFAULT '0' NOT NULL CHECK (friend >= 0),
- foe INT2 DEFAULT '0' NOT NULL CHECK (foe >= 0)
+ foe INT2 DEFAULT '0' NOT NULL CHECK (foe >= 0),
+ PRIMARY KEY (user_id, zebra_id)
);
-CREATE INDEX phpbb_zebra_user_id ON phpbb_zebra (user_id);
-CREATE INDEX phpbb_zebra_zebra_id ON phpbb_zebra (zebra_id);
COMMIT; \ No newline at end of file
diff --git a/phpBB/install/schemas/sqlite_schema.sql b/phpBB/install/schemas/sqlite_schema.sql
index 9ddbb6bb8b..4d697f3b15 100644
--- a/phpBB/install/schemas/sqlite_schema.sql
+++ b/phpBB/install/schemas/sqlite_schema.sql
@@ -1033,11 +1033,10 @@ CREATE TABLE phpbb_zebra (
user_id INTEGER UNSIGNED NOT NULL DEFAULT '0',
zebra_id INTEGER UNSIGNED NOT NULL DEFAULT '0',
friend INTEGER UNSIGNED NOT NULL DEFAULT '0',
- foe INTEGER UNSIGNED NOT NULL DEFAULT '0'
+ foe INTEGER UNSIGNED NOT NULL DEFAULT '0',
+ PRIMARY KEY (user_id, zebra_id)
);
-CREATE INDEX phpbb_zebra_user_id ON phpbb_zebra (user_id);
-CREATE INDEX phpbb_zebra_zebra_id ON phpbb_zebra (zebra_id);
COMMIT; \ No newline at end of file
generated by cgit v1.2.1 (git 2.21.0) at 2025-05-20 04:42:05 +0000