From fd4f973440baf51b92de61cd067268f1881a9a30 Mon Sep 17 00:00:00 2001 From: Henry Sudhof Date: Thu, 12 Apr 2012 18:34:50 +0200 Subject: [ticket/9725] Create an Azure SQL compatible Schema Two problems were encountered when installing on AzureSQL: -Azure SQL does not support the ON clause of T-SQL -Azure SQL requries a clustered index AKA primary key on all tables The fix is makeshift; it introduces questionable primary indices, which should be replaced with auto--increment columns. PHPBB3-9725 --- phpBB/develop/create_schema_files.php | 20 ++++++++++++++++---- 1 file changed, 16 insertions(+), 4 deletions(-) (limited to 'phpBB/develop') diff --git a/phpBB/develop/create_schema_files.php b/phpBB/develop/create_schema_files.php index 7b1fff40fb..23ef2f6c74 100644 --- a/phpBB/develop/create_schema_files.php +++ b/phpBB/develop/create_schema_files.php @@ -552,7 +552,7 @@ foreach ($supported_dbms as $dbms) case 'mssql': $line = substr($line, 0, -2); - $line .= "\n) ON [PRIMARY]" . (($textimage) ? ' TEXTIMAGE_ON [PRIMARY]' : '') . "\n"; + $line .= "\n)";// ON [PRIMARY]" . (($textimage) ? ' TEXTIMAGE_ON [PRIMARY]' : '') . "\n"; $line .= "GO\n\n"; break; } @@ -589,7 +589,7 @@ foreach ($supported_dbms as $dbms) $line .= "\tCONSTRAINT [PK_{$table_name}] PRIMARY KEY CLUSTERED \n"; $line .= "\t(\n"; $line .= "\t\t[" . implode("],\n\t\t[", $table_data['PRIMARY_KEY']) . "]\n"; - $line .= "\t) ON [PRIMARY] \n"; + $line .= "\t) \n"; $line .= "GO\n\n"; break; @@ -684,7 +684,7 @@ foreach ($supported_dbms as $dbms) case 'mssql': $line .= ($key_data[0] == 'INDEX') ? 'CREATE INDEX' : ''; $line .= ($key_data[0] == 'UNIQUE') ? 'CREATE UNIQUE INDEX' : ''; - $line .= " [{$key_name}] ON [{$table_name}]([" . implode('], [', $key_data[1]) . "]) ON [PRIMARY]\n"; + $line .= " [{$key_name}] ON [{$table_name}]([" . implode('], [', $key_data[1]) . "]) \n"; $line .= "GO\n\n"; break; @@ -863,8 +863,9 @@ function get_schema_struct() 'KEYS' => array( 'group_id' => array('INDEX', 'group_id'), 'auth_opt_id' => array('INDEX', 'auth_option_id'), - 'auth_role_id' => array('INDEX', 'auth_role_id'), + 'auth_role_id' => array('INDEX', 'auth_role_id', 'auth_option_id'), ), + 'PRIMARY_KEY' => array('group_id', 'forum_id', 'auth_option_id', 'auth_role_id', 'auth_setting'), ); $schema_data['phpbb_acl_options'] = array( @@ -921,6 +922,7 @@ function get_schema_struct() 'auth_option_id' => array('INDEX', 'auth_option_id'), 'auth_role_id' => array('INDEX', 'auth_role_id'), ), + 'PRIMARY_KEY' => array('user_id', 'forum_id', 'auth_option_id', 'auth_role_id', 'auth_setting'), ); $schema_data['phpbb_banlist'] = array( @@ -1142,6 +1144,7 @@ function get_schema_struct() 'user_id' => array('INDEX', 'user_id'), 'notify_stat' => array('INDEX', 'notify_status'), ), + 'PRIMARY_KEY' => array('forum_id', 'user_id'), ); $schema_data['phpbb_groups'] = array( @@ -1258,6 +1261,8 @@ function get_schema_struct() 'disp_idx' => array('INDEX', 'display_on_index'), 'forum_id' => array('INDEX', 'forum_id'), ), + 'PRIMARY_KEY' => array('forum_id', 'user_id', 'group_id'), + ); $schema_data['phpbb_modules'] = array( @@ -1293,6 +1298,7 @@ function get_schema_struct() 'poll_opt_id' => array('INDEX', 'poll_option_id'), 'topic_id' => array('INDEX', 'topic_id'), ), + 'PRIMARY_KEY' => array('topic_id', 'poll_option_id'), ); $schema_data['phpbb_poll_votes'] = array( @@ -1307,6 +1313,7 @@ function get_schema_struct() 'vote_user_id' => array('INDEX', 'vote_user_id'), 'vote_user_ip' => array('INDEX', 'vote_user_ip'), ), + 'PRIMARY_KEY' => array('topic_id', 'poll_option_id', 'vote_user_id'), ); $schema_data['phpbb_posts'] = array( @@ -1433,6 +1440,7 @@ function get_schema_struct() 'author_id' => array('INDEX', 'author_id'), 'usr_flder_id' => array('INDEX', array('user_id', 'folder_id')), ), +'PRIMARY_KEY' => array('msg_id', 'user_id', 'author_id'), ); $schema_data['phpbb_profile_fields'] = array( @@ -1568,6 +1576,7 @@ function get_schema_struct() 'word_id' => array('INDEX', 'word_id'), 'post_id' => array('INDEX', 'post_id'), ), + 'PRIMARY_KEY' => array('post_id', 'word_id'), ); $schema_data['phpbb_sessions'] = array( @@ -1683,6 +1692,7 @@ function get_schema_struct() 'tid' => array('INDEX', 'template_id'), 'tfn' => array('INDEX', 'template_filename'), ), + 'PRIMARY_KEY' => array('template_id', 'template_filename'), ); $schema_data['phpbb_styles_theme'] = array( @@ -1812,6 +1822,7 @@ function get_schema_struct() 'user_id' => array('INDEX', 'user_id'), 'notify_stat' => array('INDEX', 'notify_status'), ), + 'PRIMARY_KEY' => array('topic_id', 'user_id'), ); $schema_data['phpbb_user_group'] = array( @@ -1826,6 +1837,7 @@ function get_schema_struct() 'user_id' => array('INDEX', 'user_id'), 'group_leader' => array('INDEX', 'group_leader'), ), + 'PRIMARY_KEY' => array('group_id', 'user_id'), ); $schema_data['phpbb_users'] = array( -- cgit v1.2.1 From b993fa54a5d8902590d0d6a83bbe1c581d043f46 Mon Sep 17 00:00:00 2001 From: Henry Sudhof Date: Thu, 12 Apr 2012 18:39:40 +0200 Subject: [ticket/9725] Add dummy indexes for Azure SQL Azure requires a primary AKA clustered key on every table. This adds a dummy INT column to act as key, avoiding possible duplicate entries. so that these columns can support bigger (speak: huge) tables PHPBB3-9725 --- phpBB/develop/create_schema_files.php | 27 ++++++++++++++------------- 1 file changed, 14 insertions(+), 13 deletions(-) (limited to 'phpBB/develop') diff --git a/phpBB/develop/create_schema_files.php b/phpBB/develop/create_schema_files.php index 23ef2f6c74..b869c507e5 100644 --- a/phpBB/develop/create_schema_files.php +++ b/phpBB/develop/create_schema_files.php @@ -237,6 +237,19 @@ $supported_dbms = array('firebird', 'mssql', 'mysql_40', 'mysql_41', 'oracle', ' foreach ($supported_dbms as $dbms) { + $schema_data = get_schema_struct(); + if ($dbms == 'mssql') + { + foreach ($schema_data as $table_name => $table_data) + { + if (!isset($table_data['PRIMARY_KEY'])) + { + $schema_data[$table_name]['COLUMNS']['mssqlindex'] = array('UINT', NULL, 'auto_increment'); + $schema_data[$table_name]['PRIMARY_KEY'] = 'mssqlindex'; + } + } + } + $fp = fopen($schema_path . $dbms . '_schema.sql', 'wb'); $line = ''; @@ -863,9 +876,8 @@ function get_schema_struct() 'KEYS' => array( 'group_id' => array('INDEX', 'group_id'), 'auth_opt_id' => array('INDEX', 'auth_option_id'), - 'auth_role_id' => array('INDEX', 'auth_role_id', 'auth_option_id'), + 'auth_role_id' => array('INDEX', 'auth_role_id'), ), - 'PRIMARY_KEY' => array('group_id', 'forum_id', 'auth_option_id', 'auth_role_id', 'auth_setting'), ); $schema_data['phpbb_acl_options'] = array( @@ -922,7 +934,6 @@ function get_schema_struct() 'auth_option_id' => array('INDEX', 'auth_option_id'), 'auth_role_id' => array('INDEX', 'auth_role_id'), ), - 'PRIMARY_KEY' => array('user_id', 'forum_id', 'auth_option_id', 'auth_role_id', 'auth_setting'), ); $schema_data['phpbb_banlist'] = array( @@ -1144,7 +1155,6 @@ function get_schema_struct() 'user_id' => array('INDEX', 'user_id'), 'notify_stat' => array('INDEX', 'notify_status'), ), - 'PRIMARY_KEY' => array('forum_id', 'user_id'), ); $schema_data['phpbb_groups'] = array( @@ -1261,8 +1271,6 @@ function get_schema_struct() 'disp_idx' => array('INDEX', 'display_on_index'), 'forum_id' => array('INDEX', 'forum_id'), ), - 'PRIMARY_KEY' => array('forum_id', 'user_id', 'group_id'), - ); $schema_data['phpbb_modules'] = array( @@ -1298,7 +1306,6 @@ function get_schema_struct() 'poll_opt_id' => array('INDEX', 'poll_option_id'), 'topic_id' => array('INDEX', 'topic_id'), ), - 'PRIMARY_KEY' => array('topic_id', 'poll_option_id'), ); $schema_data['phpbb_poll_votes'] = array( @@ -1313,7 +1320,6 @@ function get_schema_struct() 'vote_user_id' => array('INDEX', 'vote_user_id'), 'vote_user_ip' => array('INDEX', 'vote_user_ip'), ), - 'PRIMARY_KEY' => array('topic_id', 'poll_option_id', 'vote_user_id'), ); $schema_data['phpbb_posts'] = array( @@ -1440,7 +1446,6 @@ function get_schema_struct() 'author_id' => array('INDEX', 'author_id'), 'usr_flder_id' => array('INDEX', array('user_id', 'folder_id')), ), -'PRIMARY_KEY' => array('msg_id', 'user_id', 'author_id'), ); $schema_data['phpbb_profile_fields'] = array( @@ -1576,7 +1581,6 @@ function get_schema_struct() 'word_id' => array('INDEX', 'word_id'), 'post_id' => array('INDEX', 'post_id'), ), - 'PRIMARY_KEY' => array('post_id', 'word_id'), ); $schema_data['phpbb_sessions'] = array( @@ -1692,7 +1696,6 @@ function get_schema_struct() 'tid' => array('INDEX', 'template_id'), 'tfn' => array('INDEX', 'template_filename'), ), - 'PRIMARY_KEY' => array('template_id', 'template_filename'), ); $schema_data['phpbb_styles_theme'] = array( @@ -1822,7 +1825,6 @@ function get_schema_struct() 'user_id' => array('INDEX', 'user_id'), 'notify_stat' => array('INDEX', 'notify_status'), ), - 'PRIMARY_KEY' => array('topic_id', 'user_id'), ); $schema_data['phpbb_user_group'] = array( @@ -1837,7 +1839,6 @@ function get_schema_struct() 'user_id' => array('INDEX', 'user_id'), 'group_leader' => array('INDEX', 'group_leader'), ), - 'PRIMARY_KEY' => array('group_id', 'user_id'), ); $schema_data['phpbb_users'] = array( -- cgit v1.2.1 From a2580687ee8a557d026af75d8d6fcf477e8c435e Mon Sep 17 00:00:00 2001 From: Patrick Webster Date: Sat, 1 Mar 2014 23:14:53 -0600 Subject: [ticket/9725] Remove trailing spaces from MSSQL schema PHPBB3-9725 --- phpBB/develop/create_schema_files.php | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'phpBB/develop') diff --git a/phpBB/develop/create_schema_files.php b/phpBB/develop/create_schema_files.php index b869c507e5..b5f1202c0d 100644 --- a/phpBB/develop/create_schema_files.php +++ b/phpBB/develop/create_schema_files.php @@ -602,7 +602,7 @@ foreach ($supported_dbms as $dbms) $line .= "\tCONSTRAINT [PK_{$table_name}] PRIMARY KEY CLUSTERED \n"; $line .= "\t(\n"; $line .= "\t\t[" . implode("],\n\t\t[", $table_data['PRIMARY_KEY']) . "]\n"; - $line .= "\t) \n"; + $line .= "\t)\n"; $line .= "GO\n\n"; break; @@ -697,7 +697,7 @@ foreach ($supported_dbms as $dbms) case 'mssql': $line .= ($key_data[0] == 'INDEX') ? 'CREATE INDEX' : ''; $line .= ($key_data[0] == 'UNIQUE') ? 'CREATE UNIQUE INDEX' : ''; - $line .= " [{$key_name}] ON [{$table_name}]([" . implode('], [', $key_data[1]) . "]) \n"; + $line .= " [{$key_name}] ON [{$table_name}]([" . implode('], [', $key_data[1]) . "])\n"; $line .= "GO\n\n"; break; -- cgit v1.2.1