From 7768d67e226b2a5aef9b624bf8badad8e558cb87 Mon Sep 17 00:00:00 2001 From: Meik Sievertsen Date: Wed, 12 Jul 2006 16:39:15 +0000 Subject: this little file should help us in creating/editing all schema files. This file already includes the proposed NULL/NOT NULL/DEFAULT changes. The resulting schema needs to be tested - postgresql schema lacks it's CHECK values and UNSIGNED has been added. MSSQL no longer uses alter stable statements to create defaults. git-svn-id: file:///svn/phpbb/trunk@6173 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/develop/create_schema_files.php | 1842 +++++++++++++++++++++++++++++++++ 1 file changed, 1842 insertions(+) create mode 100644 phpBB/develop/create_schema_files.php (limited to 'phpBB/develop/create_schema_files.php') diff --git a/phpBB/develop/create_schema_files.php b/phpBB/develop/create_schema_files.php new file mode 100644 index 0000000000..c76deae0a7 --- /dev/null +++ b/phpBB/develop/create_schema_files.php @@ -0,0 +1,1842 @@ + array( + 'INT:' => 'int(%d)', + 'BINT' => 'bigint(20)', + 'UINT' => 'mediumint(8) UNSIGNED', + 'UINT:' => 'int(%d) UNSIGNED', + 'TINT:' => 'tinyint(%d)', + 'USINT' => 'smallint(4) UNSIGNED', + 'BOOL' => 'tinyint(1) UNSIGNED', + 'VCHAR' => 'varchar(255)', + 'VCHAR:' => 'varchar(%d)', + 'CHAR:' => 'char(%d)', + 'XSTEXT' => 'text', + 'STEXT' => 'text', + 'TEXT' => 'text', + 'MTEXT' => 'mediumtext', + 'TIMESTAMP' => 'int(11) UNSIGNED', + 'DECIMAL' => 'decimal(5,2)', + 'VCHAR_BIN' => 'varchar(252) BINARY', + 'VCHAR_CI' => 'varchar(255)', + ), + + 'firebird' => array( + 'INT:' => 'INTEGER', + 'BINT' => 'DOUBLE PRECISION', + 'UINT' => 'INTEGER', + 'UINT:' => 'INTEGER', + 'TINT:' => 'INTEGER', + 'USINT' => 'INTEGER', + 'BOOL' => 'INTEGER', + 'VCHAR' => 'VARCHAR(255)', + 'VCHAR:' => 'VARCHAR(%d)', + 'CHAR:' => 'CHAR(%d)', + 'XSTEXT' => 'BLOB SUB_TYPE TEXT', + 'STEXT' => 'BLOB SUB_TYPE TEXT', + 'TEXT' => 'BLOB SUB_TYPE TEXT', + 'MTEXT' => 'BLOB SUB_TYPE TEXT', + 'TIMESTAMP' => 'INTEGER', + 'DECIMAL' => 'DOUBLE PRECISION', + 'VCHAR_BIN' => 'VARCHAR(252)', + 'VCHAR_CI' => 'VARCHAR(255)', + ), + + 'mssql' => array( + 'INT:' => '[int]', + 'BINT' => '[float]', + 'UINT' => '[int]', + 'UINT:' => '[int]', + 'TINT:' => '[int]', + 'USINT' => '[int]', + 'BOOL' => '[int]', + 'VCHAR' => '[varchar] (255)', + 'VCHAR:' => '[varchar] (%d)', + 'CHAR:' => '[char] (%d)', + 'XSTEXT' => '[varchar] (1000)', + 'STEXT' => '[varchar] (3000)', + 'TEXT' => '[varchar] (8000)', + 'MTEXT' => '[text]', + 'TIMESTAMP' => '[int]', + 'DECIMAL' => '[float]', + 'VCHAR_BIN' => '[nvarchar] (252)', + 'VCHAR_CI' => '[varchar] (255)', + ), + + 'oracle' => array( + 'INT:' => 'number(%d)', + 'BINT' => 'number(20)', + 'UINT' => 'number(8) UNSIGNED', + 'UINT:' => 'number(%d) UNSIGNED', + 'TINT:' => 'number(%d)', + 'USINT' => 'number(4) UNSIGNED', + 'BOOL' => 'number(1) UNSIGNED', + 'VCHAR' => 'varchar2(255)', + 'VCHAR:' => 'varchar2(%d)', + 'CHAR:' => 'char(%d)', + 'XSTEXT' => 'varchar2(1000)', + 'STEXT' => 'varchar2(3000)', + 'TEXT' => 'clob', + 'MTEXT' => 'clob', + 'TIMESTAMP' => 'number(11) UNSIGNED', + 'DECIMAL' => 'number(5, 2)', + 'VCHAR_BIN' => 'varchar2(252)', + 'VCHAR_CI' => 'varchar2(255)', + ), + + 'sqlite' => array( + 'INT:' => 'int(%d)', + 'BINT' => 'bigint(20)', + 'UINT' => 'mediumint(8) UNSIGNED', + 'UINT:' => 'int(%d) UNSIGNED', + 'TINT:' => 'tinyint(%d)', + 'USINT' => 'mediumint(4) UNSIGNED', + 'BOOL' => 'tinyint(1) UNSIGNED', + 'VCHAR' => 'varchar(255)', + 'VCHAR:' => 'varchar(%d)', + 'CHAR:' => 'char(%d)', + 'XSTEXT' => 'text(65535)', + 'STEXT' => 'text(65535)', + 'TEXT' => 'text(65535)', + 'MTEXT' => 'mediumtext(16777215)', + 'TIMESTAMP' => 'int(11) UNSIGNED', + 'DECIMAL' => 'decimal(5,2)', + 'VCHAR_BIN' => 'varchar(252)', + 'VCHAR_CI' => 'varchar(255)', + ), + + 'postgres' => array( + 'INT:' => 'INT4', + 'BINT' => 'INT8', + 'UINT' => 'INT4 UNSIGNED', + 'UINT:' => 'INT4 UNSIGNED', + 'USINT' => 'INT2 UNSIGNED', + 'BOOL' => 'INT2 UNSIGNED', + 'TINT:' => 'INT2', + 'VCHAR' => 'varchar(255)', + 'VCHAR:' => 'varchar(%d)', + 'CHAR:' => 'char(%d)', + 'XSTEXT' => 'varchar(1000)', + 'STEXT' => 'varchar(3000)', + 'TEXT' => 'varchar(8000)', + 'MTEXT' => 'TEXT', + 'TIMESTAMP' => 'INT4 UNSIGNED', + 'DECIMAL' => 'decimal(5,2)', + 'VCHAR_BIN' => 'varchar(252)', + 'VCHAR_CI' => 'varchar_ci', + ), +); + +foreach (array('firebird', 'mssql', 'mysql', 'oracle', 'postgres', 'sqlite') as $dbms) +{ + $fp = fopen($schema_path . '_' . $dbms . '_schema.sql', 'wt'); + + $line = ''; + + // Write Header + switch ($dbms) + { + case 'mysql': + $line = "#\n# MySQL Schema for phpBB 3.x - (c) phpBB Group, 2005\n#\n# \$I" . "d: $\n#\n\n"; + break; + + case 'firebird': + $line = "#\n# Firebird Schema for phpBB 3.x - (c) phpBB Group, 2005\n#\n# \$I" . "d: $\n#\n\n"; + break; + + case 'sqlite': + $line = "#\n# SQLite Schema for phpBB 3.x - (c) phpBB Group, 2005\n#\n# \$I" . "d: $\n#\n\n"; + $line .= "BEGIN TRANSACTION;\n\n"; + break; + + case 'mssql': + $line = "/*\n MSSQL Schema for phpBB 3.x - (c) phpBB Group, 2005\n\n \$I" . "d: $\n\n*/\n\n"; + $line .= "BEGIN TRANSACTION\nGO\n\n"; + break; + + case 'oracle': + $line = "/*\n Oracle Schema for phpBB 3.x - (c) phpBB Group, 2005\n\n \$I" . "d: $\n\n*/\n\n"; + $line .= oracle_custom_data() . "\n"; + break; + + case 'postgres': + $line = "/*\n PostgreSQL Schema for phpBB 3.x - (c) phpBB Group, 2005\n\n \$I" . "d: $\n\n*/\n\n"; + $line .= "BEGIN;\n\n"; + $line .= postgres_custom_data() . "\n"; + break; + } + + fwrite($fp, $line); + + foreach ($schema_data as $table_name => $table_data) + { + // Write comment about table + switch ($dbms) + { + case 'mysql': + case 'firebird': + case 'sqlite': + fwrite($fp, "# Table: '{$table_name}'\n"); + break; + + case 'mssql': + case 'oracle': + case 'postgres': + fwrite($fp, "/* Table: '{$table_name}' */\n"); + break; + } + + // Create Table statement + $generator = $textimage = false; + $line = ''; + + switch ($dbms) + { + case 'mysql': + case 'firebird': + case 'oracle': + case 'sqlite': + case 'postgres': + $line = "CREATE TABLE {$table_name} (\n"; + break; + + case 'mssql': + $line = "CREATE TABLE [{$table_name}] (\n"; + break; + } + + // Write columns one by one... + foreach ($table_data['COLUMNS'] as $column_name => $column_data) + { + // Get type + if (strpos($column_data[0], ':') !== false) + { + list($column_type, $column_length) = explode(':', $column_data[0]); + + $column_type = sprintf($dbms_type_map[$dbms][$column_type . ':'], $column_length); + } + else + { + $column_type = $dbms_type_map[$dbms][$column_data[0]]; + } + + switch ($dbms) + { + case 'mysql': + $line .= "\t{$column_name} {$column_type} "; + $line .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : ''; + $line .= 'NOT NULL'; + + if (isset($column_data[2]) && $column_data[2] == 'auto_increment') + { + $line .= ' auto_increment'; + } + + $line .= ",\n"; + break; + + case 'sqlite': + if (isset($column_data[2]) && $column_data[2] == 'auto_increment') + { + $line .= "\t{$column_name} INTEGER "; + } + else + { + $line .= "\t{$column_name} {$column_type} "; + } + + if (isset($table_data['PRIMARY_KEY'])) + { + $table_data['PRIMARY_KEY'] = (!is_array($table_data['PRIMARY_KEY'])) ? array($table_data['PRIMARY_KEY']) : $table_data['PRIMARY_KEY']; + + if (in_array($column_name, $table_data['PRIMARY_KEY'])) + { + $line .= 'PRIMARY KEY '; + } + } + + $line .= 'NOT NULL '; + $line .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}'" : ''; + $line .= ",\n"; + break; + + case 'firebird': + $line .= "\t{$column_name} {$column_type} "; + + if (!is_null($column_data[1])) + { + $line .= 'DEFAULT ' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ' '; + } + + $line .= "NOT NULL,\n"; + + if (isset($column_data[2]) && $column_data[2] == 'auto_increment') + { + $generator = $column_name; + } + break; + + case 'mssql': + if ($column_type == '[text]') + { + $textimage = true; + } + + $line .= "\t[{$column_name}] {$column_type} "; + if (!is_null($column_data[1])) + { + $line .= 'DEFAULT (' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ') '; + } + + if (isset($column_data[2]) && $column_data[2] == 'auto_increment') + { + $line .= 'IDENTITY (1, 1) '; + } + + $line .= 'NOT NULL'; + $line .= " ,\n"; + break; + + case 'oracle': + $line .= "\t{$column_name} {$column_type} "; + $line .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : ''; + $line .= "NOT NULL,\n"; + + if (isset($column_data[2]) && $column_data[2] == 'auto_increment') + { + $generator = $column_name; + } + break; + + case 'postgres': + $line .= "\t{$column_name} {$column_type} "; + + if (isset($column_data[2]) && $column_data[2] == 'auto_increment') + { + $line .= "DEFAULT nextval('{$table_name}_seq'),\n"; + + // Make sure the sequence will be created before creating the table + $line = "CREATE SEQUENCE {$table_name}_seq;\n\n" . $line; + } + else + { + $line .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : ''; + $line .= "NOT NULL,\n"; + } + break; + } + } + + switch ($dbms) + { + case 'firebird': + // Remove last line delimiter... + $line = substr($line, 0, -2); + $line .= "\n);;\n\n"; + break; + + case 'sqlite': + // Remove last line delimiter... + $line = substr($line, 0, -2); + $line .= "\n);\n\n"; + break; + + case 'mssql': + $line = substr($line, 0, -2); + $line .= "\n) ON [PRIMARY]" . (($textimage) ? ' TEXTIMAGE_ON [PRIMARY]' : '') . "\n"; + $line .= "GO\n\n"; + break; + } + + // Write primary key + if (isset($table_data['PRIMARY_KEY'])) + { + if (!is_array($table_data['PRIMARY_KEY'])) + { + $table_data['PRIMARY_KEY'] = array($table_data['PRIMARY_KEY']); + } + + switch ($dbms) + { + case 'mysql': + case 'postgres': + $line .= "\tPRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . "),\n"; + break; + + case 'firebird': + $line .= "ALTER TABLE {$table_name} ADD PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ");;\n\n"; + break; + + case 'mssql': + $line .= "ALTER TABLE [{$table_name}] WITH NOCHECK ADD \n"; + $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 .= "GO\n\n"; + break; + + case 'oracle': + $line .= "\tCONSTRAINT pk_{$table_name} PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . "),\n"; + break; + } + } + + switch ($dbms) + { + case 'oracle': + // UNIQUE contrains to be added? + if (isset($table_data['KEYS'])) + { + foreach ($table_data['KEYS'] as $key_name => $key_data) + { + if (!is_array($key_data[1])) + { + $key_data[1] = array($key_data[1]); + } + + if ($key_data[0] == 'UNIQUE') + { + $line .= "\tCONSTRAINT u_phpbb_{$key_name} UNIQUE (" . implode(', ', $key_data[1]) . "),\n"; + } + } + } + + // Remove last line delimiter... + $line = substr($line, 0, -2); + $line .= "\n)\n/\n\n"; + break; + + case 'postgres': + // Remove last line delimiter... + $line = substr($line, 0, -2); + $line .= "\n);\n\n"; + break; + } + + // Write Keys + if (isset($table_data['KEYS'])) + { + foreach ($table_data['KEYS'] as $key_name => $key_data) + { + if (!is_array($key_data[1])) + { + $key_data[1] = array($key_data[1]); + } + + switch ($dbms) + { + case 'mysql': + $line .= ($key_data[0] == 'INDEX') ? "\tKEY" : ''; + $line .= ($key_data[0] == 'UNIQUE') ? "\tUNIQUE" : ''; + $line .= ' ' . $key_name . ' (' . implode(', ', $key_data[1]) . "),\n"; + break; + + case 'firebird': + $line .= ($key_data[0] == 'INDEX') ? 'CREATE INDEX' : ''; + $line .= ($key_data[0] == 'UNIQUE') ? 'CREATE UNIQUE INDEX' : ''; + + $line .= ' ' . $table_name . '_' . $key_name . ' ON ' . $table_name . '(' . implode(', ', $key_data[1]) . ");;\n"; + break; + + 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 .= "GO\n\n"; + break; + + case 'oracle': + if ($key_data[0] == 'UNIQUE') + { + continue; + } + + $line .= ($key_data[0] == 'INDEX') ? 'CREATE INDEX' : ''; + + $line .= " {$table_name}_{$key_name} ON {$table_name} (" . implode(', ', $key_data[1]) . ")\n"; + $line .= "/\n"; + break; + + case 'sqlite': + case 'postgres': + $line .= ($key_data[0] == 'INDEX') ? 'CREATE INDEX' : ''; + $line .= ($key_data[0] == 'UNIQUE') ? 'CREATE UNIQUE INDEX' : ''; + + $line .= " {$table_name}_{$key_name} ON {$table_name} (" . implode(', ', $key_data[1]) . ");\n"; + break; + } + } + } + + switch ($dbms) + { + case 'mysql': + // Remove last line delimiter... + $line = substr($line, 0, -2); + $line .= "\n);\n\n"; + break; + + // Create Generator + case 'firebird': + if ($generator !== false) + { + $line .= "\nCREATE GENERATOR {$table_name}_gen;;\n"; + $line .= 'SET GENERATOR ' . $table_name . "_gen TO 0;;\n\n"; + + $line .= 'CREATE TRIGGER t_' . $table_name . '_gen FOR ' . $table_name . "\n"; + $line .= "BEFORE INSERT\nAS\nBEGIN\n"; + $line .= "\tNEW.{$generator} = GEN_ID({$table_name}_gen, 1);\nEND;;\n\n"; + } + break; + + case 'oracle': + if ($generator !== false) + { + $line .= "\nCREATE SEQUENCE {$table_name}_seq\n/\n\n"; + + $line .= "CREATE OR REPLACE TRIGGER ai_{$table_name}_seq\n"; + $line .= "BEFORE INSERT ON {$table_name}\n"; + $line .= "FOR EACH ROW WHEN (\n"; + $line .= "\tnew.{$generator} IS NULL OR new.{$generator} = 0\n"; + $line .= ")\nBEGIN\n"; + $line .= "\tSELECT {$table_name}_seq.nextval\n"; + $line .= "\tINTO :new.{$generator}\n"; + $line .= "\tFROM dual;\nEND;\n/\n\n"; + } + break; + } + + fwrite($fp, $line); + } + + $line = ''; + + // Write custom function at the end for some db's + switch ($dbms) + { + case 'firebird': + $line = "\n\nDECLARE EXTERNAL FUNCTION STRLEN\n"; + $line .= "\tCSTRING(32767)\n"; + $line .= "RETURNS INTEGER BY VALUE\n"; + $line .= "ENTRY_POINT 'IB_UDF_strlen' MODULE_NAME 'ib_udf';;\n\n"; + + $line .= "DECLARE EXTERNAL FUNCTION LOWER CSTRING(80)\n"; + $line .= "RETURNS CSTRING(80) FREE_IT \n"; + $line .= "ENTRY_POINT 'IB_UDF_lower' MODULE_NAME 'ib_udf';;\n\n"; + break; + + case 'mssql': + $line = "\nCOMMIT\nGO\n\n"; + break; + + case 'sqlite': + $line = "\nCOMMIT;"; + break; + } + + fwrite($fp, $line); + fclose($fp); +} + + +/** +* Define the basic structure +* The format: +* array('{TABLE_NAME}' => {TABLE_DATA}) +* {TABLE_DATA}: +* COLUMNS = array({column_name} = array({column_type}, {default}, {auto_increment})) +* PRIMARY_KEY = {column_name(s)} +* KEYS = array({key_name} = array({key_type}, {column_name(s)})), +* +* Column Types: +* INT:x => SIGNED int(x) +* BINT => BIGINT +* UINT => mediumint(8) UNSIGNED +* UINT:x => int(x) UNSIGNED +* TINT:x => tinyint(x) +* USINT => smallint(4) UNSIGNED (for _order columns) +* BOOL => tinyint(1) UNSIGNED +* VCHAR => varchar(255) +* CHAR:x => char(x) +* XSTEXT => text for storing 1000 characters (topic_title for example) +* STEXT => text for storing 3000 characters (normal input field with a max of 255 single-byte chars) +* TEXT => text for storing 8000 characters (short text, descriptions, comments, etc.) +* MTEXT => mediumtext (post text, large text) +* VCHAR:x => varchar(x) +* TIMESTAMP => int(11) UNSIGNED +* DECIMAL => decimal number (5,2) +* VCHAR_BIN => varchar(252) BINARY +* VCHAR_CI => varchar_ci for postgresql, others VCHAR +*/ +function get_schema_struct() +{ + $schema_data = array(); + + $schema_data['phpbb_attachments'] = array( + 'COLUMNS' => array( + 'attach_id' => array('UINT', NULL, 'auto_increment'), + 'post_msg_id' => array('UINT', 0), + 'topic_id' => array('UINT', 0), + 'in_message' => array('BOOL', 0), + 'poster_id' => array('UINT', 0), + 'pysical_filename' => array('VCHAR', ''), + 'real_filename' => array('VCHAR', ''), + 'download_count' => array('UINT', 0), + 'comment' => array('TEXT', ''), + 'extension' => array('VCHAR:100', ''), + 'mimetype' => array('VCHAR:100', ''), + 'filesize' => array('UINT:20', 0), + 'filetime' => array('TIMESTAMP', 0), + 'thumbnail' => array('BOOL', 0), + ), + 'PRIMARY_KEY' => 'attach_id', + 'KEYS' => array( + 'filetime' => array('INDEX', 'filetime'), + 'post_msg_id' => array('INDEX', 'post_msg_id'), + 'topic_id' => array('INDEX', 'topic_id'), + 'poster_id' => array('INDEX', 'poster_id'), + 'filesize' => array('INDEX', 'filesize'), + ), + ); + + $schema_data['phpbb_acl_groups'] = array( + 'COLUMNS' => array( + 'group_id' => array('UINT', 0), + 'forum_id' => array('UINT', 0), + 'auth_option_id' => array('UINT', 0), + 'auth_role_id' => array('UINT', 0), + 'auth_setting' => array('TINT:2', 0), + ), + 'KEYS' => array( + 'group_id' => array('INDEX', 'group_id'), + 'auth_option_id' => array('INDEX', 'auth_option_id'), + ), + ); + + $schema_data['phpbb_acl_options'] = array( + 'COLUMNS' => array( + 'auth_option_id' => array('UINT', NULL, 'auto_increment'), + 'auth_option' => array('VCHAR:50', ''), + 'is_global' => array('BOOL', 0), + 'is_local' => array('BOOL', 0), + 'founder_only' => array('BOOL', 0), + ), + 'PRIMARY_KEY' => 'auth_option_id', + 'KEYS' => array( + 'auth_option' => array('INDEX', 'auth_option'), + ), + ); + + $schema_data['phpbb_acl_roles'] = array( + 'COLUMNS' => array( + 'role_id' => array('UINT', NULL, 'auto_increment'), + 'role_name' => array('VCHAR', ''), + 'role_description' => array('TEXT', ''), + 'role_type' => array('VCHAR:10', ''), + 'role_order' => array('USINT', 0), + ), + 'PRIMARY_KEY' => 'role_id', + 'KEYS' => array( + 'role_type' => array('INDEX', 'role_type'), + 'role_order' => array('INDEX', 'role_order'), + ), + ); + + $schema_data['phpbb_acl_roles_data'] = array( + 'COLUMNS' => array( + 'role_id' => array('UINT', 0), + 'auth_option_id' => array('UINT', 0), + 'auth_setting' => array('TINT:2', 0), + ), + 'PRIMARY_KEY' => array('role_id', 'auth_option_id'), + ); + + $schema_data['phpbb_acl_users'] = array( + 'COLUMNS' => array( + 'user_id' => array('UINT', 0), + 'forum_id' => array('UINT', 0), + 'auth_option_id' => array('UINT', 0), + 'auth_role_id' => array('UINT', 0), + 'auth_setting' => array('TINT:2', 0), + ), + 'KEYS' => array( + 'user_id' => array('INDEX', 'user_id'), + 'auth_option_id' => array('INDEX', 'auth_option_id'), + ), + ); + + $schema_data['phpbb_banlist'] = array( + 'COLUMNS' => array( + 'ban_id' => array('UINT', NULL, 'auto_increment'), + 'ban_userid' => array('UINT', 0), + 'ban_ip' => array('VCHAR:40', ''), + 'ban_email' => array('VCHAR:100', ''), + 'ban_start' => array('TIMESTAMP', 0), + 'ban_end' => array('TIMESTAMP', 0), + 'ban_exclude' => array('BOOL', 0), + 'ban_reason' => array('STEXT', ''), + 'ban_give_reason' => array('STEXT', ''), + ), + 'PRIMARY_KEY' => 'ban_id', + ); + + $schema_data['phpbb_bbcodes'] = array( + 'COLUMNS' => array( + 'bbcode_id' => array('TINT:3', 0), + 'bbcode_tag' => array('VCHAR:16', ''), + 'display_on_posting' => array('BOOL', 0), + 'bbcode_match' => array('VCHAR', ''), + 'bbcode_tpl' => array('MTEXT', ''), + 'first_pass_match' => array('VCHAR', ''), + 'first_pass_replace' => array('VCHAR', ''), + 'second_pass_match' => array('VCHAR', ''), + 'second_pass_replace' => array('MTEXT', ''), + ), + 'PRIMARY_KEY' => 'bbcode_id', + 'KEYS' => array( + 'display_in_posting' => array('INDEX', 'display_on_posting'), + ), + ); + + $schema_data['phpbb_bookmarks'] = array( + 'COLUMNS' => array( + 'topic_id' => array('UINT', 0), + 'user_id' => array('UINT', 0), + 'order_id' => array('UINT', 0), + ), + 'KEYS' => array( + 'order_id' => array('INDEX', 'order_id'), + 'topic_user_id' => array('INDEX', array('topic_id', 'user_id')), + ), + ); + + $schema_data['phpbb_bots'] = array( + 'COLUMNS' => array( + 'bot_id' => array('UINT', NULL, 'auto_increment'), + 'bot_active' => array('BOOL', 1), + 'bot_name' => array('STEXT', ''), + 'user_id' => array('UINT', 0), + 'bot_agent' => array('VCHAR', ''), + 'bot_ip' => array('VCHAR', ''), + ), + 'PRIMARY_KEY' => 'bot_id', + 'KEYS' => array( + 'bot_active' => array('INDEX', 'bot_active'), + ), + ); + + $schema_data['phpbb_config'] = array( + 'COLUMNS' => array( + 'config_name' => array('VCHAR', ''), + 'config_value' => array('VCHAR', ''), + 'is_dynamic' => array('BOOL', 0), + ), + 'PRIMARY_KEY' => 'config_name', + 'KEYS' => array( + 'is_dynamic' => array('INDEX', 'is_dynamic'), + ), + ); + + $schema_data['phpbb_confirm'] = array( + 'COLUMNS' => array( + 'confirm_id' => array('CHAR:32', ''), + 'session_id' => array('CHAR:32', ''), + 'confirm_type' => array('TINT:3', 0), + 'code' => array('VCHAR:8', ''), + ), + 'PRIMARY_KEY' => array('session_id', 'confirm_id'), + ); + + $schema_data['phpbb_disallow'] = array( + 'COLUMNS' => array( + 'disallow_id' => array('UINT', NULL, 'auto_increment'), + 'disallow_username' => array('VCHAR', ''), + ), + 'PRIMARY_KEY' => 'disallow_id', + ); + + $schema_data['phpbb_drafts'] = array( + 'COLUMNS' => array( + 'draft_id' => array('UINT', NULL, 'auto_increment'), + 'user_id' => array('UINT', 0), + 'topic_id' => array('UINT', 0), + 'forum_id' => array('UINT', 0), + 'save_time' => array('TIMESTAMP', 0), + 'draft_subject' => array('XSTEXT', ''), + 'draft_message' => array('MTEXT', ''), + ), + 'PRIMARY_KEY' => 'draft_id', + 'KEYS' => array( + 'save_time' => array('INDEX', 'save_time'), + ), + ); + + $schema_data['phpbb_extensions'] = array( + 'COLUMNS' => array( + 'extension_id' => array('UINT', NULL, 'auto_increment'), + 'group_id' => array('UINT', 0), + 'extension' => array('VCHAR:100', ''), + ), + 'PRIMARY_KEY' => 'extension_id', + ); + + $schema_data['phpbb_extension_groups'] = array( + 'COLUMNS' => array( + 'group_id' => array('UINT', NULL, 'auto_increment'), + 'group_name' => array('VCHAR', ''), + 'cat_id' => array('TINT:2', 0), + 'allow_group' => array('BOOL', 0), + 'download_mode' => array('BOOL', 1), + 'upload_icon' => array('VCHAR', ''), + 'max_filesize' => array('UINT:20', 0), + 'allowed_forums' => array('TEXT', ''), + 'allow_in_pm' => array('BOOL', 0), + ), + 'PRIMARY_KEY' => 'group_id', + ); + + $schema_data['phpbb_forums'] = array( + 'COLUMNS' => array( + 'forum_id' => array('UINT', NULL, 'auto_increment'), + 'parent_id' => array('UINT', 0), + 'left_id' => array('UINT', 0), + 'right_id' => array('UINT', 0), + 'forum_parents' => array('MTEXT', ''), + 'forum_name' => array('STEXT', ''), + 'forum_desc' => array('TEXT', ''), + 'forum_desc_bitfield' => array('UINT:11', ''), + 'forum_desc_uid' => array('VCHAR:5', ''), + 'forum_link' => array('VCHAR', ''), + 'forum_password' => array('VCHAR:40', ''), + 'forum_style' => array('TINT:4', 0), + 'forum_image' => array('VCHAR', ''), + 'forum_rules' => array('TEXT', ''), + 'forum_rules_link' => array('VCHAR', ''), + 'forum_rules_bitfield' => array('UINT:11', 0), + 'forum_rules_uid' => array('VCHAR:5', ''), + 'forum_topics_per_page' => array('TINT:4', 0), + 'forum_type' => array('TINT:4', 0), + 'forum_status' => array('TINT:4', 0), + 'forum_posts' => array('UINT', 0), + 'forum_topics' => array('UINT', 0), + 'forum_topics_real' => array('UINT', 0), + 'forum_last_post_id' => array('UINT', 0), + 'forum_last_poster_id' => array('UINT', 0), + 'forum_last_post_time' => array('TIMESTAMP', 0), + 'forum_last_poster_name'=> array('VCHAR', ''), + 'forum_flags' => array('TINT:4', 32), + 'display_on_index' => array('BOOL', 1), + 'enable_indexing' => array('BOOL', 1), + 'enable_icons' => array('BOOL', 1), + 'enable_prune' => array('BOOL', 0), + 'prune_next' => array('TIMESTAMP', 0), + 'prune_days' => array('TINT:4', 0), + 'prune_viewed' => array('TINT:4', 0), + 'prune_freq' => array('TINT:4', 0), + ), + 'PRIMARY_KEY' => 'forum_id', + 'KEYS' => array( + 'left_right_id' => array('INDEX', array('left_id', 'right_id')), + 'forum_last_post_id' => array('INDEX', 'forum_last_post_id'), + ), + ); + + $schema_data['phpbb_forums_access'] = array( + 'COLUMNS' => array( + 'forum_id' => array('UINT', 0), + 'user_id' => array('UINT', 0), + 'session_id' => array('CHAR:32', ''), + ), + 'PRIMARY_KEY' => array('forum_id', 'user_id', 'session_id'), + ); + + $schema_data['phpbb_forums_track'] = array( + 'COLUMNS' => array( + 'user_id' => array('UINT', 0), + 'forum_id' => array('UINT', 0), + 'mark_time' => array('TIMESTAMP', 0), + ), + 'PRIMARY_KEY' => array('user_id', 'forum_id'), + ); + + $schema_data['phpbb_forums_watch'] = array( + 'COLUMNS' => array( + 'forum_id' => array('UINT', 0), + 'user_id' => array('UINT', 0), + 'notify_status' => array('BOOL', 0), + ), + 'KEYS' => array( + 'forum_id' => array('INDEX', 'forum_id'), + 'user_id' => array('INDEX', 'user_id'), + 'notify_status' => array('INDEX', 'notify_status'), + ), + ); + + $schema_data['phpbb_groups'] = array( + 'COLUMNS' => array( + 'group_id' => array('UINT', NULL, 'auto_increment'), + 'group_type' => array('TINT:4', 1), + 'group_name' => array('VCHAR_CI', ''), + 'group_desc' => array('TEXT', ''), + 'group_desc_bitfield' => array('UINT:11', 0), + 'group_desc_uid' => array('VCHAR:5', ''), + 'group_display' => array('BOOL', 0), + 'group_avatar' => array('VCHAR', ''), + 'group_avatar_type' => array('TINT:4', 0), + 'group_avatar_width' => array('TINT:4', 0), + 'group_avatar_height' => array('TINT:4', 0), + 'group_rank' => array('UINT', 0), + 'group_colour' => array('VCHAR:6', ''), + 'group_sig_chars' => array('UINT', 0), + 'group_receive_pm' => array('BOOL', 0), + 'group_message_limit' => array('UINT', 0), + 'group_legend' => array('BOOL', 1), + ), + 'PRIMARY_KEY' => 'group_id', + 'KEYS' => array( + 'group_legend' => array('INDEX', 'group_legend'), + ), + ); + + $schema_data['phpbb_icons'] = array( + 'COLUMNS' => array( + 'icons_id' => array('UINT', NULL, 'auto_increment'), + 'icons_url' => array('VCHAR', ''), + 'icons_width' => array('TINT:4', 0), + 'icons_height' => array('TINT:4', 0), + 'icons_order' => array('UINT', 0), + 'display_on_posting' => array('BOOL', 1), + ), + 'PRIMARY_KEY' => 'icons_id', + ); + + $schema_data['phpbb_lang'] = array( + 'COLUMNS' => array( + 'lang_id' => array('TINT:4', NULL, 'auto_increment'), + 'lang_iso' => array('VCHAR:5', ''), + 'lang_dir' => array('VCHAR:30', ''), + 'lang_english_name' => array('VCHAR:100', ''), + 'lang_local_name' => array('VCHAR:255', ''), + 'lang_author' => array('VCHAR:255', ''), + ), + 'PRIMARY_KEY' => 'lang_id', + 'KEYS' => array( + 'lang_iso' => array('INDEX', 'lang_iso'), + ), + ); + + $schema_data['phpbb_log'] = array( + 'COLUMNS' => array( + 'log_id' => array('UINT', NULL, 'auto_increment'), + 'log_type' => array('TINT:4', 0), + 'user_id' => array('UINT', 0), + 'forum_id' => array('UINT', 0), + 'topic_id' => array('UINT', 0), + 'reportee_id' => array('UINT', 0), + 'log_ip' => array('VCHAR:40', ''), + 'log_time' => array('TIMESTAMP', 0), + 'log_operation' => array('TEXT', ''), + 'log_data' => array('MTEXT', ''), + ), + 'PRIMARY_KEY' => 'log_id', + 'KEYS' => array( + 'log_type' => array('INDEX', 'log_type'), + 'forum_id' => array('INDEX', 'forum_id'), + 'topic_id' => array('INDEX', 'topic_id'), + 'reportee_id' => array('INDEX', 'reportee_id'), + 'user_id' => array('INDEX', 'user_id'), + ), + ); + + $schema_data['phpbb_moderator_cache'] = array( + 'COLUMNS' => array( + 'forum_id' => array('UINT', 0), + 'user_id' => array('UINT', 0), + 'username' => array('VCHAR', ''), + 'group_id' => array('UINT', 0), + 'group_name' => array('VCHAR', ''), + 'display_on_index' => array('BOOL', 1), + ), + 'KEYS' => array( + 'display_on_index' => array('INDEX', 'display_on_index'), + 'forum_id' => array('INDEX', 'forum_id'), + ), + ); + + $schema_data['phpbb_modules'] = array( + 'COLUMNS' => array( + 'module_id' => array('UINT', NULL, 'auto_increment'), + 'module_enabled' => array('BOOL', 1), + 'module_display' => array('BOOL', 1), + 'module_name' => array('VCHAR', ''), + 'module_class' => array('VCHAR:10', ''), + 'parent_id' => array('UINT', 0), + 'left_id' => array('UINT', 0), + 'right_id' => array('UINT', 0), + 'module_langname' => array('VCHAR', ''), + 'module_mode' => array('VCHAR', ''), + 'module_auth' => array('VCHAR', ''), + ), + 'PRIMARY_KEY' => 'module_id', + 'KEYS' => array( + 'left_right_id' => array('INDEX', array('left_id', 'right_id')), + 'module_enabled' => array('INDEX', 'module_enabled'), + 'class_left_id' => array('INDEX', array('module_class', 'left_id')), + ), + ); + + $schema_data['phpbb_poll_options'] = array( + 'COLUMNS' => array( + 'poll_option_id' => array('TINT:4', 0), + 'topic_id' => array('UINT', 0), + 'poll_option_text' => array('TEXT', ''), + 'poll_option_total' => array('UINT', 0), + ), + 'KEYS' => array( + 'poll_option_id' => array('INDEX', 'poll_option_id'), + 'topic_id' => array('INDEX', 'topic_id'), + ), + ); + + $schema_data['phpbb_poll_votes'] = array( + 'COLUMNS' => array( + 'topic_id' => array('UINT', 0), + 'poll_option_id' => array('TINT:4', 0), + 'vote_user_id' => array('UINT', 0), + 'vote_user_ip' => array('VCHAR:40', ''), + ), + 'KEYS' => array( + 'topic_id' => array('INDEX', 'topic_id'), + 'vote_user_id' => array('INDEX', 'vote_user_id'), + 'vote_user_ip' => array('INDEX', 'vote_user_ip'), + ), + ); + + $schema_data['phpbb_posts'] = array( + 'COLUMNS' => array( + 'post_id' => array('UINT', NULL, 'auto_increment'), + 'topic_id' => array('UINT', 0), + 'forum_id' => array('UINT', 0), + 'poster_id' => array('UINT', 0), + 'icon_id' => array('UINT', 0), + 'poster_ip' => array('VCHAR:40', ''), + 'post_time' => array('TIMESTAMP', 0), + 'post_approved' => array('BOOL', 1), + 'post_reported' => array('BOOL', 0), + 'enable_bbcode' => array('BOOL', 1), + 'enable_smilies' => array('BOOL', 1), + 'enable_magic_url' => array('BOOL', 1), + 'enable_sig' => array('BOOL', 1), + 'post_username' => array('VCHAR', ''), + 'post_subject' => array('XSTEXT', ''), + 'post_text' => array('MTEXT', ''), + 'post_checksum' => array('VCHAR:32', ''), + 'post_encoding' => array('VCHAR:20', 'iso-8859-1'), + 'post_attachment' => array('BOOL', 0), + 'bbcode_bitfield' => array('UINT:11', 0), + 'bbcode_uid' => array('VCHAR:5', ''), + 'post_edit_time' => array('TIMESTAMP', 0), + 'post_edit_reason' => array('STEXT', ''), + 'post_edit_user' => array('UINT', 0), + 'post_edit_count' => array('USINT', 0), + 'post_edit_locked' => array('BOOL', 0), + ), + 'PRIMARY_KEY' => 'post_id', + 'KEYS' => array( + 'forum_id' => array('INDEX', 'forum_id'), + 'topic_id' => array('INDEX', 'topic_id'), + 'poster_ip' => array('INDEX', 'poster_ip'), + 'poster_id' => array('INDEX', 'poster_id'), + 'post_approved' => array('INDEX', 'post_approved'), + 'post_time' => array('INDEX', 'post_time'), + ), + ); + + $schema_data['phpbb_privmsgs'] = array( + 'COLUMNS' => array( + 'msg_id' => array('UINT', NULL, 'auto_increment'), + 'root_level' => array('UINT', 0), + 'author_id' => array('UINT', 0), + 'icon_id' => array('UINT', 0), + 'author_ip' => array('VCHAR:40', ''), + 'message_time' => array('TIMESTAMP', 0), + 'enable_bbcode' => array('BOOL', 1), + 'enable_smilies' => array('BOOL', 1), + 'enable_magic_url' => array('BOOL', 1), + 'enable_sig' => array('BOOL', 1), + 'message_subject' => array('XSTEXT', ''), + 'message_text' => array('MTEXT', ''), + 'message_edit_reason' => array('STEXT', ''), + 'message_edit_user' => array('UINT', 0), + 'message_encoding' => array('VCHAR:20', 'iso-8859-1'), + 'message_attachment' => array('BOOL', 0), + 'bbcode_bitfield' => array('UINT:11', 0), + 'bbcode_uid' => array('VCHAR:5', ''), + 'message_edit_time' => array('TIMESTAMP', 0), + 'message_edit_count' => array('USINT', 0), + 'to_address' => array('TEXT', ''), + 'bcc_address' => array('TEXT', ''), + ), + 'PRIMARY_KEY' => 'msg_id', + 'KEYS' => array( + 'author_ip' => array('INDEX', 'author_ip'), + 'message_time' => array('INDEX', 'message_time'), + 'author_id' => array('INDEX', 'author_id'), + 'root_level' => array('INDEX', 'root_level'), + ), + ); + + $schema_data['phpbb_privmsgs_folder'] = array( + 'COLUMNS' => array( + 'folder_id' => array('UINT', NULL, 'auto_increment'), + 'user_id' => array('UINT', 0), + 'folder_name' => array('VCHAR', ''), + 'pm_count' => array('UINT', 0), + ), + 'PRIMARY_KEY' => 'folder_id', + 'KEYS' => array( + 'user_id' => array('INDEX', 'user_id'), + ), + ); + + $schema_data['phpbb_privmsgs_rules'] = array( + 'COLUMNS' => array( + 'rule_id' => array('UINT', NULL, 'auto_increment'), + 'user_id' => array('UINT', 0), + 'rule_check' => array('UINT', 0), + 'rule_connection' => array('UINT', 0), + 'rule_string' => array('VCHAR', ''), + 'rule_user_id' => array('UINT', 0), + 'rule_group_id' => array('UINT', 0), + 'rule_action' => array('UINT', 0), + 'rule_folder_id' => array('UINT', 0), + ), + 'PRIMARY_KEY' => 'rule_id', + ); + + $schema_data['phpbb_privmsgs_to'] = array( + 'COLUMNS' => array( + 'msg_id' => array('UINT', 0), + 'user_id' => array('UINT', 0), + 'author_id' => array('UINT', 0), + 'deleted' => array('BOOL', 0), + 'new' => array('BOOL', 1), + 'unread' => array('BOOL', 1), + 'replied' => array('BOOL', 0), + 'marked' => array('BOOL', 0), + 'forwarded' => array('BOOL', 0), + 'folder_id' => array('UINT', 0), + ), + 'KEYS' => array( + 'msg_id' => array('INDEX', 'msg_id'), + 'user_folder_id' => array('INDEX', array('user_id', 'folder_id')), + ), + ); + + $schema_data['phpbb_profile_fields'] = array( + 'COLUMNS' => array( + 'field_id' => array('UINT', NULL, 'auto_increment'), + 'field_name' => array('VCHAR', ''), + 'field_type' => array('TINT:4', 0), + 'field_ident' => array('VCHAR:20', ''), + 'field_length' => array('VCHAR:20', ''), + 'field_minlen' => array('VCHAR', ''), + 'field_maxlen' => array('VCHAR', ''), + 'field_novalue' => array('VCHAR', ''), + 'field_default_value' => array('VCHAR', ''), + 'field_validation' => array('VCHAR:20', ''), + 'field_required' => array('BOOL', 0), + 'field_show_on_reg' => array('BOOL', 0), + 'field_hide' => array('BOOL', 0), + 'field_no_view' => array('BOOL', 0), + 'field_active' => array('BOOL', 0), + 'field_order' => array('UINT', 0), + ), + 'PRIMARY_KEY' => 'field_id', + 'KEYS' => array( + 'field_type' => array('INDEX', 'field_type'), + 'field_order' => array('INDEX', 'field_order'), + ), + ); + + $schema_data['phpbb_profile_fields_data'] = array( + 'COLUMNS' => array( + 'user_id' => array('UINT', 0), + ), + 'PRIMARY_KEY' => 'user_id', + ); + + $schema_data['phpbb_profile_fields_lang'] = array( + 'COLUMNS' => array( + 'field_id' => array('UINT', 0), + 'lang_id' => array('UINT', 0), + 'option_id' => array('UINT', 0), + 'field_type' => array('TINT:4', 0), + 'value' => array('VCHAR', ''), + ), + 'PRIMARY_KEY' => array('field_id', 'lang_id', 'option_id'), + ); + + $schema_data['phpbb_profile_lang'] = array( + 'COLUMNS' => array( + 'field_id' => array('UINT', 0), + 'lang_id' => array('UINT', 0), + 'lang_name' => array('VCHAR', ''), + 'lang_explain' => array('TEXT', ''), + 'lang_default_value' => array('VCHAR', ''), + ), + 'PRIMARY_KEY' => array('field_id', 'lang_id'), + ); + + $schema_data['phpbb_ranks'] = array( + 'COLUMNS' => array( + 'rank_id' => array('UINT', NULL, 'auto_increment'), + 'rank_title' => array('VCHAR', ''), + 'rank_min' => array('UINT', 0), + 'rank_special' => array('BOOL', 0), + 'rank_image' => array('VCHAR', ''), + ), + 'PRIMARY_KEY' => 'rank_id', + ); + + $schema_data['phpbb_reports'] = array( + 'COLUMNS' => array( + 'report_id' => array('UINT', NULL, 'auto_increment'), + 'reason_id' => array('USINT', 0), + 'post_id' => array('UINT', 0), + 'user_id' => array('UINT', 0), + 'user_notify' => array('BOOL', 0), + 'report_closed' => array('BOOL', 0), + 'report_time' => array('TIMESTAMP', 0), + 'report_text' => array('MTEXT', ''), + ), + 'PRIMARY_KEY' => 'report_id', + ); + + $schema_data['phpbb_reports_reasons'] = array( + 'COLUMNS' => array( + 'reason_id' => array('USINT', NULL, 'auto_increment'), + 'reason_title' => array('VCHAR', ''), + 'reason_description' => array('MTEXT', ''), + 'reason_order' => array('USINT', 0), + ), + 'PRIMARY_KEY' => 'reason_id', + ); + + $schema_data['phpbb_search_results'] = array( + 'COLUMNS' => array( + 'search_key' => array('VCHAR:32', ''), + 'search_time' => array('TIMESTAMP', 0), + 'search_keywords' => array('MTEXT', ''), + 'search_authors' => array('MTEXT', ''), + ), + 'PRIMARY_KEY' => 'search_key', + ); + + $schema_data['phpbb_search_wordlist'] = array( + 'COLUMNS' => array( + 'word_text' => array('VCHAR_BIN', ''), + 'word_id' => array('UINT', NULL, 'auto_increment'), + 'word_common' => array('BOOL', 0), + ), + 'PRIMARY_KEY' => 'word_text', + 'KEYS' => array( + 'word_id' => array('INDEX', 'word_id'), + ), + ); + + $schema_data['phpbb_search_wordmatch'] = array( + 'COLUMNS' => array( + 'post_id' => array('UINT', 0), + 'word_id' => array('UINT', 0), + 'title_match' => array('BOOL', 0), + ), + 'KEYS' => array( + 'word_id' => array('INDEX', 'word_id'), + ), + ); + + $schema_data['phpbb_sessions'] = array( + 'COLUMNS' => array( + 'session_id' => array('CHAR:32', ''), + 'session_user_id' => array('UINT', 0), + 'session_last_visit' => array('TIMESTAMP', 0), + 'session_start' => array('TIMESTAMP', 0), + 'session_time' => array('TIMESTAMP', 0), + 'session_ip' => array('VCHAR:40', ''), + 'session_browser' => array('VCHAR:150', ''), + 'session_page' => array('VCHAR', ''), + 'session_viewonline' => array('BOOL', 1), + 'session_autologin' => array('BOOL', 0), + 'session_admin' => array('BOOL', 0), + ), + 'PRIMARY_KEY' => 'session_id', + 'KEYS' => array( + 'session_time' => array('INDEX', 'session_time'), + 'session_user_id' => array('INDEX', 'session_user_id'), + ), + ); + + $schema_data['phpbb_sessions_keys'] = array( + 'COLUMNS' => array( + 'key_id' => array('CHAR:32', ''), + 'user_id' => array('UINT', 0), + 'last_ip' => array('VCHAR:40', ''), + 'last_login' => array('TIMESTAMP', 0), + ), + 'PRIMARY_KEY' => array('key_id', 'user_id'), + 'KEYS' => array( + 'last_login' => array('INDEX', 'last_login'), + ), + ); + + $schema_data['phpbb_sitelist'] = array( + 'COLUMNS' => array( + 'site_id' => array('UINT', NULL, 'auto_increment'), + 'site_ip' => array('VCHAR:40', ''), + 'site_hostname' => array('VCHAR', ''), + 'ip_exclude' => array('BOOL', 0), + ), + 'PRIMARY_KEY' => 'site_id', + ); + + $schema_data['phpbb_smilies'] = array( + 'COLUMNS' => array( + 'smiley_id' => array('UINT', NULL, 'auto_increment'), + 'code' => array('VCHAR:50', ''), + 'emotion' => array('VCHAR:50', ''), + 'smiley_url' => array('VCHAR:50', ''), + 'smiley_width' => array('TINT:4', 0), + 'smiley_height' => array('TINT:4', 0), + 'smiley_order' => array('UINT', 0), + 'display_on_posting'=> array('BOOL', 1), + ), + 'PRIMARY_KEY' => 'smiley_id', + 'KEYS' => array( + 'display_on_posting' => array('INDEX', 'display_on_posting'), + ), + ); + + $schema_data['phpbb_styles'] = array( + 'COLUMNS' => array( + 'style_id' => array('TINT:4', NULL, 'auto_increment'), + 'style_name' => array('VCHAR', ''), + 'style_copyright' => array('VCHAR', ''), + 'style_active' => array('BOOL', 1), + 'template_id' => array('TINT:4', 0), + 'theme_id' => array('TINT:4', 0), + 'imageset_id' => array('TINT:4', 0), + ), + 'PRIMARY_KEY' => 'style_id', + 'KEYS' => array( + 'style_name' => array('UNIQUE', 'style_name'), + 'template_id' => array('INDEX', 'template_id'), + 'theme_id' => array('INDEX', 'theme_id'), + 'imageset_id' => array('INDEX', 'imageset_id'), + ), + ); + + $schema_data['phpbb_styles_template'] = array( + 'COLUMNS' => array( + 'template_id' => array('TINT:4', NULL, 'auto_increment'), + 'template_name' => array('VCHAR', ''), + 'template_copyright' => array('VCHAR', ''), + 'template_path' => array('VCHAR:100', ''), + 'bbcode_bitfield' => array('UINT:11', 6921), + 'template_storedb' => array('BOOL', 0), + ), + 'PRIMARY_KEY' => 'template_id', + 'KEYS' => array( + 'template_name' => array('UNIQUE', 'template_name'), + ), + ); + + $schema_data['phpbb_styles_template_data'] = array( + 'COLUMNS' => array( + 'template_id' => array('TINT:4', NULL, 'auto_increment'), + 'template_filename' => array('VCHAR:100', ''), + 'template_included' => array('TEXT', ''), + 'template_mtime' => array('TIMESTAMP', 0), + 'template_data' => array('MTEXT', ''), + ), + 'KEYS' => array( + 'template_id' => array('INDEX', 'template_id'), + 'template_filename' => array('INDEX', 'template_filename'), + ), + ); + + $schema_data['phpbb_styles_theme'] = array( + 'COLUMNS' => array( + 'theme_id' => array('TINT:4', NULL, 'auto_increment'), + 'theme_name' => array('VCHAR', ''), + 'theme_copyright' => array('VCHAR', ''), + 'theme_path' => array('VCHAR:100', ''), + 'theme_storedb' => array('BOOL', 0), + 'theme_mtime' => array('TIMESTAMP', 0), + 'theme_data' => array('MTEXT', ''), + ), + 'PRIMARY_KEY' => 'theme_id', + 'KEYS' => array( + 'theme_name' => array('UNIQUE', 'theme_name'), + ), + ); + + $schema_data['phpbb_styles_imageset'] = array( + 'COLUMNS' => array( + 'imageset_id' => array('TINT:4', NULL, 'auto_increment'), + 'imageset_name' => array('VCHAR', ''), + 'imageset_copyright' => array('VCHAR', ''), + 'imageset_path' => array('VCHAR:100', ''), + 'site_logo' => array('VCHAR:200', ''), + 'btn_post' => array('VCHAR:200', ''), + 'btn_post_pm' => array('VCHAR:200', ''), + 'btn_reply' => array('VCHAR:200', ''), + 'btn_reply_pm' => array('VCHAR:200', ''), + 'btn_locked' => array('VCHAR:200', ''), + 'btn_profile' => array('VCHAR:200', ''), + 'btn_pm' => array('VCHAR:200', ''), + 'btn_delete' => array('VCHAR:200', ''), + 'btn_info' => array('VCHAR:200', ''), + 'btn_quote' => array('VCHAR:200', ''), + 'btn_search' => array('VCHAR:200', ''), + 'btn_edit' => array('VCHAR:200', ''), + 'btn_report' => array('VCHAR:200', ''), + 'btn_email' => array('VCHAR:200', ''), + 'btn_www' => array('VCHAR:200', ''), + 'btn_icq' => array('VCHAR:200', ''), + 'btn_aim' => array('VCHAR:200', ''), + 'btn_yim' => array('VCHAR:200', ''), + 'btn_msnm' => array('VCHAR:200', ''), + 'btn_jabber' => array('VCHAR:200', ''), + 'btn_online' => array('VCHAR:200', ''), + 'btn_offline' => array('VCHAR:200', ''), + 'btn_friend' => array('VCHAR:200', ''), + 'btn_foe' => array('VCHAR:200', ''), + 'icon_unapproved' => array('VCHAR:200', ''), + 'icon_reported' => array('VCHAR:200', ''), + 'icon_attach' => array('VCHAR:200', ''), + 'icon_post' => array('VCHAR:200', ''), + 'icon_post_new' => array('VCHAR:200', ''), + 'icon_post_latest' => array('VCHAR:200', ''), + 'icon_post_newest' => array('VCHAR:200', ''), + 'forum' => array('VCHAR:200', ''), + 'forum_new' => array('VCHAR:200', ''), + 'forum_locked' => array('VCHAR:200', ''), + 'forum_link' => array('VCHAR:200', ''), + 'sub_forum' => array('VCHAR:200', ''), + 'sub_forum_new' => array('VCHAR:200', ''), + 'folder' => array('VCHAR:200', ''), + 'folder_moved' => array('VCHAR:200', ''), + 'folder_posted' => array('VCHAR:200', ''), + 'folder_new' => array('VCHAR:200', ''), + 'folder_new_posted' => array('VCHAR:200', ''), + 'folder_hot' => array('VCHAR:200', ''), + 'folder_hot_posted' => array('VCHAR:200', ''), + 'folder_hot_new' => array('VCHAR:200', ''), + 'folder_hot_new_posted' => array('VCHAR:200', ''), + 'folder_locked' => array('VCHAR:200', ''), + 'folder_locked_posted' => array('VCHAR:200', ''), + 'folder_locked_new' => array('VCHAR:200', ''), + 'folder_locked_new_posted' => array('VCHAR:200', ''), + 'folder_locked_announce' => array('VCHAR:200', ''), + 'folder_locked_announce_new' => array('VCHAR:200', ''), + 'folder_locked_announce_posted' => array('VCHAR:200', ''), + 'folder_locked_announce_new_posted' => array('VCHAR:200', ''), + 'folder_locked_global' => array('VCHAR:200', ''), + 'folder_locked_global_new' => array('VCHAR:200', ''), + 'folder_locked_global_posted' => array('VCHAR:200', ''), + 'folder_locked_global_new_posted' => array('VCHAR:200', ''), + 'folder_locked_sticky' => array('VCHAR:200', ''), + 'folder_locked_sticky_new' => array('VCHAR:200', ''), + 'folder_locked_sticky_posted' => array('VCHAR:200', ''), + 'folder_locked_sticky_new_posted' => array('VCHAR:200', ''), + 'folder_sticky' => array('VCHAR:200', ''), + 'folder_sticky_posted' => array('VCHAR:200', ''), + 'folder_sticky_new' => array('VCHAR:200', ''), + 'folder_sticky_new_posted' => array('VCHAR:200', ''), + 'folder_announce' => array('VCHAR:200', ''), + 'folder_announce_posted' => array('VCHAR:200', ''), + 'folder_announce_new' => array('VCHAR:200', ''), + 'folder_announce_new_posted'=> array('VCHAR:200', ''), + 'folder_global' => array('VCHAR:200', ''), + 'folder_global_posted' => array('VCHAR:200', ''), + 'folder_global_new' => array('VCHAR:200', ''), + 'folder_global_new_posted' => array('VCHAR:200', ''), + 'poll_left' => array('VCHAR:200', ''), + 'poll_center' => array('VCHAR:200', ''), + 'poll_right' => array('VCHAR:200', ''), + 'attach_progress_bar' => array('VCHAR:200', ''), + 'user_icon1' => array('VCHAR:200', ''), + 'user_icon2' => array('VCHAR:200', ''), + 'user_icon3' => array('VCHAR:200', ''), + 'user_icon4' => array('VCHAR:200', ''), + 'user_icon5' => array('VCHAR:200', ''), + 'user_icon6' => array('VCHAR:200', ''), + 'user_icon7' => array('VCHAR:200', ''), + 'user_icon8' => array('VCHAR:200', ''), + 'user_icon9' => array('VCHAR:200', ''), + 'user_icon10' => array('VCHAR:200', ''), + ), + 'PRIMARY_KEY' => 'imageset_id', + 'KEYS' => array( + 'imageset_name' => array('UNIQUE', 'imageset_name'), + ), + ); + + $schema_data['phpbb_topics'] = array( + 'COLUMNS' => array( + 'topic_id' => array('UINT', NULL, 'auto_increment'), + 'forum_id' => array('UINT', 0), + 'icon_id' => array('UINT', 0), + 'topic_attachment' => array('BOOL', 0), + 'topic_approved' => array('BOOL', 1), + 'topic_reported' => array('BOOL', 0), + 'topic_title' => array('XSTEXT', ''), + 'topic_poster' => array('UINT', 0), + 'topic_time' => array('TIMESTAMP', 0), + 'topic_time_limit' => array('TIMESTAMP', 0), + 'topic_views' => array('UINT', 0), + 'topic_replies' => array('UINT', 0), + 'topic_replies_real' => array('UINT', 0), + 'topic_status' => array('TINT:3', 0), + 'topic_type' => array('TINT:3', 0), + 'topic_first_post_id' => array('UINT', 0), + 'topic_first_poster_name' => array('VCHAR', ''), + 'topic_last_post_id' => array('UINT', 0), + 'topic_last_poster_id' => array('UINT', 0), + 'topic_last_poster_name' => array('VCHAR', ''), + 'topic_last_post_time' => array('TIMESTAMP', 0), + 'topic_last_view_time' => array('TIMESTAMP', 0), + 'topic_moved_id' => array('UINT', 0), + 'topic_bumped' => array('BOOL', 0), + 'topic_bumper' => array('UINT', 0), + 'poll_title' => array('XSTEXT', ''), + 'poll_start' => array('TIMESTAMP', 0), + 'poll_length' => array('TIMESTAMP', 0), + 'poll_max_options' => array('TINT:4', 1), + 'poll_last_vote' => array('TIMESTAMP', 0), + 'poll_vote_change' => array('BOOL', 0), + ), + 'PRIMARY_KEY' => 'topic_id', + 'KEYS' => array( + 'forum_id' => array('INDEX', 'forum_id'), + 'forum_id_type' => array('INDEX', array('forum_id', 'topic_type')), + 'topic_last_post_time' => array('INDEX', 'topic_last_post_time'), + ), + ); + + $schema_data['phpbb_topics_track'] = array( + 'COLUMNS' => array( + 'user_id' => array('UINT', 0), + 'topic_id' => array('UINT', 0), + 'forum_id' => array('UINT', 0), + 'mark_time' => array('TIMESTAMP', 0), + ), + 'PRIMARY_KEY' => array('user_id', 'topic_id'), + 'KEYS' => array( + 'forum_id' => array('INDEX', 'forum_id'), + ), + ); + + $schema_data['phpbb_topics_posted'] = array( + 'COLUMNS' => array( + 'user_id' => array('UINT', 0), + 'topic_id' => array('UINT', 0), + 'topic_posted' => array('BOOL', 0), + ), + 'PRIMARY_KEY' => array('user_id', 'topic_id'), + ); + + $schema_data['phpbb_topics_watch'] = array( + 'COLUMNS' => array( + 'topic_id' => array('UINT', 0), + 'user_id' => array('UINT', 0), + 'notify_status' => array('BOOL', 0), + ), + 'KEYS' => array( + 'topic_id' => array('INDEX', 'topic_id'), + 'user_id' => array('INDEX', 'user_id'), + 'notify_status' => array('INDEX', 'notify_status'), + ), + ); + + $schema_data['phpbb_user_group'] = array( + 'COLUMNS' => array( + 'group_id' => array('UINT', 0), + 'user_id' => array('UINT', 0), + 'group_leader' => array('BOOL', 0), + 'user_pending' => array('BOOL', 1), + ), + 'KEYS' => array( + 'group_id' => array('INDEX', 'group_id'), + 'user_id' => array('INDEX', 'user_id'), + 'group_leader' => array('INDEX', 'group_leader'), + ), + ); + + $schema_data['phpbb_users'] = array( + 'COLUMNS' => array( + 'user_id' => array('UINT', NULL, 'auto_increment'), + 'user_type' => array('TINT:2', 0), + 'group_id' => array('UINT', 3), + 'user_permissions' => array('MTEXT', ''), + 'user_perm_from' => array('UINT', 0), + 'user_ip' => array('VCHAR:40', ''), + 'user_regdate' => array('TIMESTAMP', 0), + 'username' => array('VCHAR_CI', ''), + 'user_password' => array('VCHAR:40', ''), + 'user_passchg' => array('TIMESTAMP', 0), + 'user_email' => array('VCHAR:100', ''), + 'user_email_hash' => array('BINT', 0), + 'user_birthday' => array('VCHAR:10', ''), + 'user_lastvisit' => array('TIMESTAMP', 0), + 'user_lastmark' => array('TIMESTAMP', 0), + 'user_lastpost_time' => array('TIMESTAMP', 0), + 'user_lastpage' => array('VCHAR:200', ''), + 'user_last_confirm_key' => array('VCHAR:10', ''), + 'user_last_search' => array('TIMESTAMP', 0), + 'user_warnings' => array('TINT:4', 0), + 'user_last_warning' => array('TIMESTAMP', 0), + 'user_login_attempts' => array('TINT:4', 0), + 'user_posts' => array('UINT', 0), + 'user_lang' => array('VCHAR:30', ''), + 'user_timezone' => array('DECIMAL', 0), + 'user_dst' => array('BOOL', 0), + 'user_dateformat' => array('VCHAR:30', 'd M Y H:i'), + 'user_style' => array('TINT:4', 0), + 'user_rank' => array('UINT', 0), + 'user_colour' => array('VCHAR:6', ''), + 'user_new_privmsg' => array('TINT:4', 0), + 'user_unread_privmsg' => array('TINT:4', 0), + 'user_last_privmsg' => array('TIMESTAMP', 0), + 'user_message_rules' => array('BOOL', 0), + 'user_full_folder' => array('INT:11', -3), + 'user_emailtime' => array('TIMESTAMP', 0), + 'user_topic_show_days' => array('USINT', 0), + 'user_topic_sortby_type' => array('VCHAR:1', 't'), + 'user_topic_sortby_dir' => array('VCHAR:1', 'd'), + 'user_post_show_days' => array('USINT', 0), + 'user_post_sortby_type' => array('VCHAR:1', 't'), + 'user_post_sortby_dir' => array('VCHAR:1', 'a'), + 'user_notify' => array('BOOL', 0), + 'user_notify_pm' => array('BOOL', 1), + 'user_notify_type' => array('TINT:4', 0), + 'user_allow_pm' => array('BOOL', 1), + 'user_allow_email' => array('BOOL', 1), + 'user_allow_viewonline' => array('BOOL', 1), + 'user_allow_viewemail' => array('BOOL', 1), + 'user_allow_massemail' => array('BOOL', 1), + 'user_options' => array('UINT:11', 893), + 'user_avatar' => array('VCHAR', ''), + 'user_avatar_type' => array('TINT:2', 0), + 'user_avatar_width' => array('TINT:4', 0), + 'user_avatar_height' => array('TINT:4', 0), + 'user_sig' => array('MTEXT', ''), + 'user_sig_bbcode_uid' => array('VCHAR:5', ''), + 'user_sig_bbcode_bitfield' => array('UINT:11', 0), + 'user_from' => array('VCHAR:100', ''), + 'user_icq' => array('VCHAR:15', ''), + 'user_aim' => array('VCHAR', ''), + 'user_yim' => array('VCHAR', ''), + 'user_msnm' => array('VCHAR', ''), + 'user_jabber' => array('VCHAR', ''), + 'user_website' => array('VCHAR:200', ''), + 'user_occ' => array('VCHAR', ''), + 'user_interests' => array('TEXT', ''), + 'user_actkey' => array('VCHAR:32', ''), + 'user_newpasswd' => array('VCHAR:32', ''), + ), + 'PRIMARY_KEY' => 'user_id', + 'KEYS' => array( + 'user_birthday' => array('INDEX', 'user_birthday'), + 'user_email_hash' => array('INDEX', 'user_email_hash'), + 'user_type' => array('INDEX', 'user_type'), + 'username' => array('INDEX', 'username'), + ), + ); + + $schema_data['phpbb_warnings'] = array( + 'COLUMNS' => array( + 'warning_id' => array('UINT', NULL, 'auto_increment'), + 'user_id' => array('UINT', 0), + 'post_id' => array('UINT', 0), + 'log_id' => array('UINT', 0), + 'warning_time' => array('TIMESTAMP', 0), + ), + 'PRIMARY_KEY' => 'warning_id', + ); + + $schema_data['phpbb_words'] = array( + 'COLUMNS' => array( + 'word_id' => array('UINT', NULL, 'auto_increment'), + 'word' => array('VCHAR', ''), + 'replacement' => array('VCHAR', ''), + ), + 'PRIMARY_KEY' => 'word_id', + ); + + $schema_data['phpbb_zebra'] = array( + 'COLUMNS' => array( + 'user_id' => array('UINT', 0), + 'zebra_id' => array('UINT', 0), + 'friend' => array('BOOL', 0), + 'foe' => array('BOOL', 0), + ), + 'KEYS' => array( + 'user_id' => array('INDEX', 'user_id'), + 'zebra_id' => array('INDEX', 'zebra_id'), + ), + ); + + return $schema_data; +} + + +/** +* Data put into the header for oracle +*/ +function oracle_custom_data() +{ + return << LOWER($2)' LANGUAGE SQL STRICT; +CREATE FUNCTION _varchar_ci_greater_equals(varchar_ci, varchar_ci) RETURNS boolean AS 'SELECT LOWER($1) >= LOWER($2)' LANGUAGE SQL STRICT; + +/* Operators */ +CREATE OPERATOR <( + PROCEDURE = _varchar_ci_less_than, + LEFTARG = varchar_ci, + RIGHTARG = varchar_ci, + COMMUTATOR = >, + NEGATOR = >=, + RESTRICT = scalarltsel, + JOIN = scalarltjoinsel); + +CREATE OPERATOR <=( + PROCEDURE = _varchar_ci_less_equal, + LEFTARG = varchar_ci, + RIGHTARG = varchar_ci, + COMMUTATOR = >=, + NEGATOR = >, + RESTRICT = scalarltsel, + JOIN = scalarltjoinsel); + +CREATE OPERATOR >( + PROCEDURE = _varchar_ci_greater_than, + LEFTARG = varchar_ci, + RIGHTARG = varchar_ci, + COMMUTATOR = <, + NEGATOR = <=, + RESTRICT = scalargtsel, + JOIN = scalargtjoinsel); + +CREATE OPERATOR >=( + PROCEDURE = _varchar_ci_greater_equals, + LEFTARG = varchar_ci, + RIGHTARG = varchar_ci, + COMMUTATOR = <=, + NEGATOR = <, + RESTRICT = scalargtsel, + JOIN = scalargtjoinsel); + +CREATE OPERATOR <>( + PROCEDURE = _varchar_ci_not_equal, + LEFTARG = varchar_ci, + RIGHTARG = varchar_ci, + COMMUTATOR = <>, + NEGATOR = =, + RESTRICT = neqsel, + JOIN = neqjoinsel); + +CREATE OPERATOR =( + PROCEDURE = _varchar_ci_equal, + LEFTARG = varchar_ci, + RIGHTARG = varchar_ci, + COMMUTATOR = =, + NEGATOR = <>, + RESTRICT = eqsel, + JOIN = eqjoinsel, + HASHES, + MERGES, + SORT1= <); +EOF; +} + +?> \ No newline at end of file -- cgit v1.2.1 From c4f2430645dbc8cba38c1ea3f08366034bba7127 Mon Sep 17 00:00:00 2001 From: Meik Sievertsen Date: Thu, 13 Jul 2006 12:51:56 +0000 Subject: - renamed the following columns: comment -> attach_comment new, forwarded, unread, marked, deleted -> pm_new, pm_forwarded, pm_unread, pm_marked, pm_deleted module_name -> module_basename value -> lang_value - every column is now NOT NULL - every column is now having a DEFAULT value - hopefully mostly consistent across every db schema - untested schemas: sqlite, oracle, firebird git-svn-id: file:///svn/phpbb/trunk@6177 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/develop/create_schema_files.php | 65 ++++++++++++++++++++++------------- 1 file changed, 41 insertions(+), 24 deletions(-) (limited to 'phpBB/develop/create_schema_files.php') diff --git a/phpBB/develop/create_schema_files.php b/phpBB/develop/create_schema_files.php index c76deae0a7..45b3035606 100644 --- a/phpBB/develop/create_schema_files.php +++ b/phpBB/develop/create_schema_files.php @@ -8,6 +8,8 @@ * * This file creates new schema files for every database. * The filenames will be prefixed with an underscore to not overwrite the current schema files. +* +* If you overwrite the original schema files please make sure you save the file with UNIX linefeeds. */ die("Please read the first lines of this script for instructions on how to enable it"); @@ -89,11 +91,11 @@ $dbms_type_map = array( 'oracle' => array( 'INT:' => 'number(%d)', 'BINT' => 'number(20)', - 'UINT' => 'number(8) UNSIGNED', - 'UINT:' => 'number(%d) UNSIGNED', + 'UINT' => 'number(8)', + 'UINT:' => 'number(%d)', 'TINT:' => 'number(%d)', - 'USINT' => 'number(4) UNSIGNED', - 'BOOL' => 'number(1) UNSIGNED', + 'USINT' => 'number(4)', + 'BOOL' => 'number(1)', 'VCHAR' => 'varchar2(255)', 'VCHAR:' => 'varchar2(%d)', 'CHAR:' => 'char(%d)', @@ -101,7 +103,7 @@ $dbms_type_map = array( 'STEXT' => 'varchar2(3000)', 'TEXT' => 'clob', 'MTEXT' => 'clob', - 'TIMESTAMP' => 'number(11) UNSIGNED', + 'TIMESTAMP' => 'number(11)', 'DECIMAL' => 'number(5, 2)', 'VCHAR_BIN' => 'varchar2(252)', 'VCHAR_CI' => 'varchar2(255)', @@ -131,10 +133,10 @@ $dbms_type_map = array( 'postgres' => array( 'INT:' => 'INT4', 'BINT' => 'INT8', - 'UINT' => 'INT4 UNSIGNED', - 'UINT:' => 'INT4 UNSIGNED', - 'USINT' => 'INT2 UNSIGNED', - 'BOOL' => 'INT2 UNSIGNED', + 'UINT' => 'INT4', // unsigned + 'UINT:' => 'INT4', // unsigned + 'USINT' => 'INT2', // unsigned + 'BOOL' => 'INT2', // unsigned 'TINT:' => 'INT2', 'VCHAR' => 'varchar(255)', 'VCHAR:' => 'varchar(%d)', @@ -143,13 +145,16 @@ $dbms_type_map = array( 'STEXT' => 'varchar(3000)', 'TEXT' => 'varchar(8000)', 'MTEXT' => 'TEXT', - 'TIMESTAMP' => 'INT4 UNSIGNED', + 'TIMESTAMP' => 'INT4', // unsigned 'DECIMAL' => 'decimal(5,2)', 'VCHAR_BIN' => 'varchar(252)', 'VCHAR_CI' => 'varchar_ci', ), ); +// A list of types being unsigned for better reference in some db's +$unsigned_types = array('UINT', 'UINT:', 'USINT', 'BOOL', 'TIMESTAMP'); + foreach (array('firebird', 'mssql', 'mysql', 'oracle', 'postgres', 'sqlite') as $dbms) { $fp = fopen($schema_path . '_' . $dbms . '_schema.sql', 'wt'); @@ -234,12 +239,14 @@ foreach (array('firebird', 'mssql', 'mysql', 'oracle', 'postgres', 'sqlite') as // Get type if (strpos($column_data[0], ':') !== false) { - list($column_type, $column_length) = explode(':', $column_data[0]); + list($orig_column_type, $column_length) = explode(':', $column_data[0]); - $column_type = sprintf($dbms_type_map[$dbms][$column_type . ':'], $column_length); + $column_type = sprintf($dbms_type_map[$dbms][$orig_column_type . ':'], $column_length); + $orig_column_type .= ':'; } else { + $orig_column_type = $column_data[0]; $column_type = $dbms_type_map[$dbms][$column_data[0]]; } @@ -344,7 +351,15 @@ foreach (array('firebird', 'mssql', 'mysql', 'oracle', 'postgres', 'sqlite') as else { $line .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : ''; - $line .= "NOT NULL,\n"; + $line .= "NOT NULL"; + + // Unsigned? Then add a CHECK contraint + if (in_array($orig_column_type, $unsigned_types)) + { + $line .= " CHECK ({$column_name} >= 0)"; + } + + $line .= ",\n"; } break; } @@ -530,7 +545,7 @@ foreach (array('firebird', 'mssql', 'mysql', 'oracle', 'postgres', 'sqlite') as break; } - fwrite($fp, $line); + fwrite($fp, $line . "\n"); } $line = ''; @@ -554,6 +569,7 @@ foreach (array('firebird', 'mssql', 'mysql', 'oracle', 'postgres', 'sqlite') as break; case 'sqlite': + case 'postgres': $line = "\nCOMMIT;"; break; } @@ -606,7 +622,7 @@ function get_schema_struct() 'pysical_filename' => array('VCHAR', ''), 'real_filename' => array('VCHAR', ''), 'download_count' => array('UINT', 0), - 'comment' => array('TEXT', ''), + 'attach_comment' => array('TEXT', ''), 'extension' => array('VCHAR:100', ''), 'mimetype' => array('VCHAR:100', ''), 'filesize' => array('UINT:20', 0), @@ -828,7 +844,7 @@ function get_schema_struct() 'forum_parents' => array('MTEXT', ''), 'forum_name' => array('STEXT', ''), 'forum_desc' => array('TEXT', ''), - 'forum_desc_bitfield' => array('UINT:11', ''), + 'forum_desc_bitfield' => array('UINT:11', 0), 'forum_desc_uid' => array('VCHAR:5', ''), 'forum_link' => array('VCHAR', ''), 'forum_password' => array('VCHAR:40', ''), @@ -992,7 +1008,7 @@ function get_schema_struct() 'module_id' => array('UINT', NULL, 'auto_increment'), 'module_enabled' => array('BOOL', 1), 'module_display' => array('BOOL', 1), - 'module_name' => array('VCHAR', ''), + 'module_basename' => array('VCHAR', ''), 'module_class' => array('VCHAR:10', ''), 'parent_id' => array('UINT', 0), 'left_id' => array('UINT', 0), @@ -1143,12 +1159,12 @@ function get_schema_struct() 'msg_id' => array('UINT', 0), 'user_id' => array('UINT', 0), 'author_id' => array('UINT', 0), - 'deleted' => array('BOOL', 0), - 'new' => array('BOOL', 1), - 'unread' => array('BOOL', 1), - 'replied' => array('BOOL', 0), - 'marked' => array('BOOL', 0), - 'forwarded' => array('BOOL', 0), + 'pm_deleted' => array('BOOL', 0), + 'pm_new' => array('BOOL', 1), + 'pm_unread' => array('BOOL', 1), + 'pm_replied' => array('BOOL', 0), + 'pm_marked' => array('BOOL', 0), + 'pm_forwarded' => array('BOOL', 0), 'folder_id' => array('UINT', 0), ), 'KEYS' => array( @@ -1196,7 +1212,7 @@ function get_schema_struct() 'lang_id' => array('UINT', 0), 'option_id' => array('UINT', 0), 'field_type' => array('TINT:4', 0), - 'value' => array('VCHAR', ''), + 'lang_value' => array('VCHAR', ''), ), 'PRIMARY_KEY' => array('field_id', 'lang_id', 'option_id'), ); @@ -1836,6 +1852,7 @@ CREATE OPERATOR =( HASHES, MERGES, SORT1= <); + EOF; } -- cgit v1.2.1 From 99a7ce5bbe92b1313a22db2d8dad51046dca26d9 Mon Sep 17 00:00:00 2001 From: Meik Sievertsen Date: Mon, 17 Jul 2006 15:32:46 +0000 Subject: fix the schema files as well as other tiny bugs. git-svn-id: file:///svn/phpbb/trunk@6190 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/develop/create_schema_files.php | 14 +++++++++----- 1 file changed, 9 insertions(+), 5 deletions(-) (limited to 'phpBB/develop/create_schema_files.php') diff --git a/phpBB/develop/create_schema_files.php b/phpBB/develop/create_schema_files.php index 45b3035606..4f102112c2 100644 --- a/phpBB/develop/create_schema_files.php +++ b/phpBB/develop/create_schema_files.php @@ -154,8 +154,9 @@ $dbms_type_map = array( // A list of types being unsigned for better reference in some db's $unsigned_types = array('UINT', 'UINT:', 'USINT', 'BOOL', 'TIMESTAMP'); +$supported_dbms = array('firebird', 'mssql', 'mysql', 'oracle', 'postgres', 'sqlite'); -foreach (array('firebird', 'mssql', 'mysql', 'oracle', 'postgres', 'sqlite') as $dbms) +foreach ($supported_dbms as $dbms) { $fp = fopen($schema_path . '_' . $dbms . '_schema.sql', 'wt'); @@ -619,7 +620,7 @@ function get_schema_struct() 'topic_id' => array('UINT', 0), 'in_message' => array('BOOL', 0), 'poster_id' => array('UINT', 0), - 'pysical_filename' => array('VCHAR', ''), + 'physical_filename' => array('VCHAR', ''), 'real_filename' => array('VCHAR', ''), 'download_count' => array('UINT', 0), 'attach_comment' => array('TEXT', ''), @@ -845,6 +846,7 @@ function get_schema_struct() 'forum_name' => array('STEXT', ''), 'forum_desc' => array('TEXT', ''), 'forum_desc_bitfield' => array('UINT:11', 0), + 'forum_desc_options' => array('UINT:11', 0), 'forum_desc_uid' => array('VCHAR:5', ''), 'forum_link' => array('VCHAR', ''), 'forum_password' => array('VCHAR:40', ''), @@ -853,6 +855,7 @@ function get_schema_struct() 'forum_rules' => array('TEXT', ''), 'forum_rules_link' => array('VCHAR', ''), 'forum_rules_bitfield' => array('UINT:11', 0), + 'forum_rules_options' => array('UINT:11', 0), 'forum_rules_uid' => array('VCHAR:5', ''), 'forum_topics_per_page' => array('TINT:4', 0), 'forum_type' => array('TINT:4', 0), @@ -919,6 +922,7 @@ function get_schema_struct() 'group_name' => array('VCHAR_CI', ''), 'group_desc' => array('TEXT', ''), 'group_desc_bitfield' => array('UINT:11', 0), + 'group_desc_options' => array('UINT:11', 0), 'group_desc_uid' => array('VCHAR:5', ''), 'group_display' => array('BOOL', 0), 'group_avatar' => array('VCHAR', ''), @@ -953,7 +957,7 @@ function get_schema_struct() $schema_data['phpbb_lang'] = array( 'COLUMNS' => array( 'lang_id' => array('TINT:4', NULL, 'auto_increment'), - 'lang_iso' => array('VCHAR:5', ''), + 'lang_iso' => array('VCHAR:30', ''), 'lang_dir' => array('VCHAR:30', ''), 'lang_english_name' => array('VCHAR:100', ''), 'lang_local_name' => array('VCHAR:255', ''), @@ -1149,7 +1153,7 @@ function get_schema_struct() 'rule_user_id' => array('UINT', 0), 'rule_group_id' => array('UINT', 0), 'rule_action' => array('UINT', 0), - 'rule_folder_id' => array('UINT', 0), + 'rule_folder_id' => array('INT:4', 0), ), 'PRIMARY_KEY' => 'rule_id', ); @@ -1165,7 +1169,7 @@ function get_schema_struct() 'pm_replied' => array('BOOL', 0), 'pm_marked' => array('BOOL', 0), 'pm_forwarded' => array('BOOL', 0), - 'folder_id' => array('UINT', 0), + 'folder_id' => array('INT:4', 0), ), 'KEYS' => array( 'msg_id' => array('INDEX', 'msg_id'), -- cgit v1.2.1 From ced8624b8e86bc6aac143163e538f87376319079 Mon Sep 17 00:00:00 2001 From: Meik Sievertsen Date: Tue, 1 Aug 2006 15:29:47 +0000 Subject: - fixing some bugs - shortening some db columns to meet the requirements - correctly increase/decrease user post counts - fix the topic title length bug(s) git-svn-id: file:///svn/phpbb/trunk@6224 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/develop/create_schema_files.php | 89 ++++++++++++++++++++++------------- 1 file changed, 55 insertions(+), 34 deletions(-) (limited to 'phpBB/develop/create_schema_files.php') diff --git a/phpBB/develop/create_schema_files.php b/phpBB/develop/create_schema_files.php index 4f102112c2..9586de2d6b 100644 --- a/phpBB/develop/create_schema_files.php +++ b/phpBB/develop/create_schema_files.php @@ -44,6 +44,7 @@ $dbms_type_map = array( 'DECIMAL' => 'decimal(5,2)', 'VCHAR_BIN' => 'varchar(252) BINARY', 'VCHAR_CI' => 'varchar(255)', + 'VARBINARY' => 'varbinary(255)', ), 'firebird' => array( @@ -65,6 +66,7 @@ $dbms_type_map = array( 'DECIMAL' => 'DOUBLE PRECISION', 'VCHAR_BIN' => 'VARCHAR(252)', 'VCHAR_CI' => 'VARCHAR(255)', + 'VARBINARY' => 'CHAR(255)', ), 'mssql' => array( @@ -86,6 +88,7 @@ $dbms_type_map = array( 'DECIMAL' => '[float]', 'VCHAR_BIN' => '[nvarchar] (252)', 'VCHAR_CI' => '[varchar] (255)', + 'VARBINARY' => '[varbinary] (255)', ), 'oracle' => array( @@ -107,6 +110,7 @@ $dbms_type_map = array( 'DECIMAL' => 'number(5, 2)', 'VCHAR_BIN' => 'varchar2(252)', 'VCHAR_CI' => 'varchar2(255)', + 'VARBINARY' => 'raw(255)', ), 'sqlite' => array( @@ -128,6 +132,7 @@ $dbms_type_map = array( 'DECIMAL' => 'decimal(5,2)', 'VCHAR_BIN' => 'varchar(252)', 'VCHAR_CI' => 'varchar(255)', + 'VARBINARY' => 'blob', ), 'postgres' => array( @@ -149,6 +154,7 @@ $dbms_type_map = array( 'DECIMAL' => 'decimal(5,2)', 'VCHAR_BIN' => 'varchar(252)', 'VCHAR_CI' => 'varchar_ci', + 'VARBINARY' => 'bytea', ), ); @@ -251,11 +257,22 @@ foreach ($supported_dbms as $dbms) $column_type = $dbms_type_map[$dbms][$column_data[0]]; } + // Adjust default value if db-dependant specified + if (is_array($column_data[1])) + { + $column_data[1] = (isset($column_data[1][$dbms])) ? $column_data[1][$dbms] : $column_data[1]['default']; + } + switch ($dbms) { case 'mysql': $line .= "\t{$column_name} {$column_type} "; - $line .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : ''; + + // For hexadecimal values do not use single quotes + if (!is_null($column_data[1])) + { + $line .= (strpos($column_data[1], '0x') === 0) ? "DEFAULT {$column_data[1]} " : "DEFAULT '{$column_data[1]}' "; + } $line .= 'NOT NULL'; if (isset($column_data[2]) && $column_data[2] == 'auto_increment') @@ -725,6 +742,7 @@ function get_schema_struct() 'COLUMNS' => array( 'bbcode_id' => array('TINT:3', 0), 'bbcode_tag' => array('VCHAR:16', ''), + 'bbcode_helpline' => array('VCHAR', ''), 'display_on_posting' => array('BOOL', 0), 'bbcode_match' => array('VCHAR', ''), 'bbcode_tpl' => array('MTEXT', ''), @@ -845,7 +863,7 @@ function get_schema_struct() 'forum_parents' => array('MTEXT', ''), 'forum_name' => array('STEXT', ''), 'forum_desc' => array('TEXT', ''), - 'forum_desc_bitfield' => array('UINT:11', 0), + 'forum_desc_bitfield' => array('VARBINARY', array('default' => '', 'mssql' => '0x', 'postgres' => '\000')), 'forum_desc_options' => array('UINT:11', 0), 'forum_desc_uid' => array('VCHAR:5', ''), 'forum_link' => array('VCHAR', ''), @@ -854,7 +872,7 @@ function get_schema_struct() 'forum_image' => array('VCHAR', ''), 'forum_rules' => array('TEXT', ''), 'forum_rules_link' => array('VCHAR', ''), - 'forum_rules_bitfield' => array('UINT:11', 0), + 'forum_rules_bitfield' => array('VARBINARY', array('default' => '', 'mssql' => '0x', 'postgres' => '\000')), 'forum_rules_options' => array('UINT:11', 0), 'forum_rules_uid' => array('VCHAR:5', ''), 'forum_topics_per_page' => array('TINT:4', 0), @@ -921,7 +939,7 @@ function get_schema_struct() 'group_type' => array('TINT:4', 1), 'group_name' => array('VCHAR_CI', ''), 'group_desc' => array('TEXT', ''), - 'group_desc_bitfield' => array('UINT:11', 0), + 'group_desc_bitfield' => array('VARBINARY', array('default' => '', 'mssql' => '0x', 'postgres' => '\000')), 'group_desc_options' => array('UINT:11', 0), 'group_desc_uid' => array('VCHAR:5', ''), 'group_display' => array('BOOL', 0), @@ -1077,8 +1095,9 @@ function get_schema_struct() 'post_checksum' => array('VCHAR:32', ''), 'post_encoding' => array('VCHAR:20', 'iso-8859-1'), 'post_attachment' => array('BOOL', 0), - 'bbcode_bitfield' => array('UINT:11', 0), + 'bbcode_bitfield' => array('VARBINARY', array('default' => '', 'mssql' => '0x', 'postgres' => '\000')), 'bbcode_uid' => array('VCHAR:5', ''), + 'post_postcount' => array('BOOL', 1), 'post_edit_time' => array('TIMESTAMP', 0), 'post_edit_reason' => array('STEXT', ''), 'post_edit_user' => array('UINT', 0), @@ -1092,6 +1111,7 @@ function get_schema_struct() 'poster_ip' => array('INDEX', 'poster_ip'), 'poster_id' => array('INDEX', 'poster_id'), 'post_approved' => array('INDEX', 'post_approved'), + 'post_postcount' => array('INDEX', 'post_postcount'), 'post_time' => array('INDEX', 'post_time'), ), ); @@ -1114,7 +1134,7 @@ function get_schema_struct() 'message_edit_user' => array('UINT', 0), 'message_encoding' => array('VCHAR:20', 'iso-8859-1'), 'message_attachment' => array('BOOL', 0), - 'bbcode_bitfield' => array('UINT:11', 0), + 'bbcode_bitfield' => array('VARBINARY', array('default' => '', 'mssql' => '0x', 'postgres' => '\000')), 'bbcode_uid' => array('VCHAR:5', ''), 'message_edit_time' => array('TIMESTAMP', 0), 'message_edit_count' => array('USINT', 0), @@ -1386,7 +1406,7 @@ function get_schema_struct() 'template_name' => array('VCHAR', ''), 'template_copyright' => array('VCHAR', ''), 'template_path' => array('VCHAR:100', ''), - 'bbcode_bitfield' => array('UINT:11', 6921), + 'bbcode_bitfield' => array('VARBINARY', array('default' => '', 'mysql' => '0x90D8', 'mssql' => '0x90D8', 'oracle' => '90D8', 'postgres' => '\220\330')), 'template_storedb' => array('BOOL', 0), ), 'PRIMARY_KEY' => 'template_id', @@ -1445,6 +1465,7 @@ function get_schema_struct() 'btn_search' => array('VCHAR:200', ''), 'btn_edit' => array('VCHAR:200', ''), 'btn_report' => array('VCHAR:200', ''), + 'btn_warn' => array('VCHAR:200', ''), 'btn_email' => array('VCHAR:200', ''), 'btn_www' => array('VCHAR:200', ''), 'btn_icq' => array('VCHAR:200', ''), @@ -1471,41 +1492,41 @@ function get_schema_struct() 'sub_forum_new' => array('VCHAR:200', ''), 'folder' => array('VCHAR:200', ''), 'folder_moved' => array('VCHAR:200', ''), - 'folder_posted' => array('VCHAR:200', ''), + 'folder_post' => array('VCHAR:200', ''), 'folder_new' => array('VCHAR:200', ''), - 'folder_new_posted' => array('VCHAR:200', ''), + 'folder_new_post' => array('VCHAR:200', ''), 'folder_hot' => array('VCHAR:200', ''), - 'folder_hot_posted' => array('VCHAR:200', ''), + 'folder_hot_post' => array('VCHAR:200', ''), 'folder_hot_new' => array('VCHAR:200', ''), - 'folder_hot_new_posted' => array('VCHAR:200', ''), - 'folder_locked' => array('VCHAR:200', ''), - 'folder_locked_posted' => array('VCHAR:200', ''), - 'folder_locked_new' => array('VCHAR:200', ''), - 'folder_locked_new_posted' => array('VCHAR:200', ''), - 'folder_locked_announce' => array('VCHAR:200', ''), - 'folder_locked_announce_new' => array('VCHAR:200', ''), - 'folder_locked_announce_posted' => array('VCHAR:200', ''), - 'folder_locked_announce_new_posted' => array('VCHAR:200', ''), - 'folder_locked_global' => array('VCHAR:200', ''), - 'folder_locked_global_new' => array('VCHAR:200', ''), - 'folder_locked_global_posted' => array('VCHAR:200', ''), - 'folder_locked_global_new_posted' => array('VCHAR:200', ''), - 'folder_locked_sticky' => array('VCHAR:200', ''), - 'folder_locked_sticky_new' => array('VCHAR:200', ''), - 'folder_locked_sticky_posted' => array('VCHAR:200', ''), - 'folder_locked_sticky_new_posted' => array('VCHAR:200', ''), + 'folder_hot_new_post' => array('VCHAR:200', ''), + 'folder_lock' => array('VCHAR:200', ''), + 'folder_lock_post' => array('VCHAR:200', ''), + 'folder_lock_new' => array('VCHAR:200', ''), + 'folder_lock_new_post' => array('VCHAR:200', ''), + 'folder_lock_announce' => array('VCHAR:200', ''), + 'folder_lock_announce_new' => array('VCHAR:200', ''), + 'folder_lock_announce_post' => array('VCHAR:200', ''), + 'folder_lock_announce_new_post' => array('VCHAR:200', ''), + 'folder_lock_global' => array('VCHAR:200', ''), + 'folder_lock_global_new' => array('VCHAR:200', ''), + 'folder_lock_global_post' => array('VCHAR:200', ''), + 'folder_lock_global_new_post' => array('VCHAR:200', ''), + 'folder_lock_sticky' => array('VCHAR:200', ''), + 'folder_lock_sticky_new' => array('VCHAR:200', ''), + 'folder_lock_sticky_post' => array('VCHAR:200', ''), + 'folder_lock_sticky_new_post' => array('VCHAR:200', ''), 'folder_sticky' => array('VCHAR:200', ''), - 'folder_sticky_posted' => array('VCHAR:200', ''), + 'folder_sticky_post' => array('VCHAR:200', ''), 'folder_sticky_new' => array('VCHAR:200', ''), - 'folder_sticky_new_posted' => array('VCHAR:200', ''), + 'folder_sticky_new_post' => array('VCHAR:200', ''), 'folder_announce' => array('VCHAR:200', ''), - 'folder_announce_posted' => array('VCHAR:200', ''), + 'folder_announce_post' => array('VCHAR:200', ''), 'folder_announce_new' => array('VCHAR:200', ''), - 'folder_announce_new_posted'=> array('VCHAR:200', ''), + 'folder_announce_new_post' => array('VCHAR:200', ''), 'folder_global' => array('VCHAR:200', ''), - 'folder_global_posted' => array('VCHAR:200', ''), + 'folder_global_post' => array('VCHAR:200', ''), 'folder_global_new' => array('VCHAR:200', ''), - 'folder_global_new_posted' => array('VCHAR:200', ''), + 'folder_global_new_post' => array('VCHAR:200', ''), 'poll_left' => array('VCHAR:200', ''), 'poll_center' => array('VCHAR:200', ''), 'poll_right' => array('VCHAR:200', ''), @@ -1677,7 +1698,7 @@ function get_schema_struct() 'user_avatar_height' => array('TINT:4', 0), 'user_sig' => array('MTEXT', ''), 'user_sig_bbcode_uid' => array('VCHAR:5', ''), - 'user_sig_bbcode_bitfield' => array('UINT:11', 0), + 'user_sig_bbcode_bitfield' => array('VARBINARY', array('default' => '', 'mssql' => '0x', 'postgres' => '\000')), 'user_from' => array('VCHAR:100', ''), 'user_icq' => array('VCHAR:15', ''), 'user_aim' => array('VCHAR', ''), -- cgit v1.2.1 From b4b901b82552e639cae00c4dc38542cf6fe2d873 Mon Sep 17 00:00:00 2001 From: Meik Sievertsen Date: Sat, 5 Aug 2006 15:49:28 +0000 Subject: - fixed some bugs - made imageset naming more consistent - updated every schema to be consistent and also fixed it (every db should install fine now) - git-svn-id: file:///svn/phpbb/trunk@6237 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/develop/create_schema_files.php | 445 +++++++++++++++++++++------------- 1 file changed, 282 insertions(+), 163 deletions(-) (limited to 'phpBB/develop/create_schema_files.php') diff --git a/phpBB/develop/create_schema_files.php b/phpBB/develop/create_schema_files.php index 9586de2d6b..2758e88373 100644 --- a/phpBB/develop/create_schema_files.php +++ b/phpBB/develop/create_schema_files.php @@ -43,7 +43,7 @@ $dbms_type_map = array( 'TIMESTAMP' => 'int(11) UNSIGNED', 'DECIMAL' => 'decimal(5,2)', 'VCHAR_BIN' => 'varchar(252) BINARY', - 'VCHAR_CI' => 'varchar(255)', + 'VCHAR_CI' => 'varchar(252)', 'VARBINARY' => 'varbinary(255)', ), @@ -65,7 +65,7 @@ $dbms_type_map = array( 'TIMESTAMP' => 'INTEGER', 'DECIMAL' => 'DOUBLE PRECISION', 'VCHAR_BIN' => 'VARCHAR(252)', - 'VCHAR_CI' => 'VARCHAR(255)', + 'VCHAR_CI' => 'VARCHAR(252)', 'VARBINARY' => 'CHAR(255)', ), @@ -87,7 +87,7 @@ $dbms_type_map = array( 'TIMESTAMP' => '[int]', 'DECIMAL' => '[float]', 'VCHAR_BIN' => '[nvarchar] (252)', - 'VCHAR_CI' => '[varchar] (255)', + 'VCHAR_CI' => '[varchar] (252)', 'VARBINARY' => '[varbinary] (255)', ), @@ -109,18 +109,18 @@ $dbms_type_map = array( 'TIMESTAMP' => 'number(11)', 'DECIMAL' => 'number(5, 2)', 'VCHAR_BIN' => 'varchar2(252)', - 'VCHAR_CI' => 'varchar2(255)', + 'VCHAR_CI' => 'varchar2(252)', 'VARBINARY' => 'raw(255)', ), 'sqlite' => array( 'INT:' => 'int(%d)', 'BINT' => 'bigint(20)', - 'UINT' => 'mediumint(8) UNSIGNED', - 'UINT:' => 'int(%d) UNSIGNED', + 'UINT' => 'INTEGER UNSIGNED', //'mediumint(8) UNSIGNED', + 'UINT:' => 'INTEGER UNSIGNED', // 'int(%d) UNSIGNED', 'TINT:' => 'tinyint(%d)', - 'USINT' => 'mediumint(4) UNSIGNED', - 'BOOL' => 'tinyint(1) UNSIGNED', + 'USINT' => 'INTEGER UNSIGNED', //'mediumint(4) UNSIGNED', + 'BOOL' => 'INTEGER UNSIGNED', //'tinyint(1) UNSIGNED', 'VCHAR' => 'varchar(255)', 'VCHAR:' => 'varchar(%d)', 'CHAR:' => 'char(%d)', @@ -128,10 +128,10 @@ $dbms_type_map = array( 'STEXT' => 'text(65535)', 'TEXT' => 'text(65535)', 'MTEXT' => 'mediumtext(16777215)', - 'TIMESTAMP' => 'int(11) UNSIGNED', + 'TIMESTAMP' => 'INTEGER UNSIGNED', //'int(11) UNSIGNED', 'DECIMAL' => 'decimal(5,2)', 'VCHAR_BIN' => 'varchar(252)', - 'VCHAR_CI' => 'varchar(255)', + 'VCHAR_CI' => 'varchar(252)', 'VARBINARY' => 'blob', ), @@ -177,6 +177,7 @@ foreach ($supported_dbms as $dbms) case 'firebird': $line = "#\n# Firebird Schema for phpBB 3.x - (c) phpBB Group, 2005\n#\n# \$I" . "d: $\n#\n\n"; + $line .= custom_data('firebird') . "\n"; break; case 'sqlite': @@ -191,13 +192,13 @@ foreach ($supported_dbms as $dbms) case 'oracle': $line = "/*\n Oracle Schema for phpBB 3.x - (c) phpBB Group, 2005\n\n \$I" . "d: $\n\n*/\n\n"; - $line .= oracle_custom_data() . "\n"; + $line .= custom_data('oracle') . "\n"; break; case 'postgres': $line = "/*\n PostgreSQL Schema for phpBB 3.x - (c) phpBB Group, 2005\n\n \$I" . "d: $\n\n*/\n\n"; $line .= "BEGIN;\n\n"; - $line .= postgres_custom_data() . "\n"; + $line .= custom_data('postgres') . "\n"; break; } @@ -217,7 +218,7 @@ foreach ($supported_dbms as $dbms) case 'mssql': case 'oracle': case 'postgres': - fwrite($fp, "/* Table: '{$table_name}' */\n"); + fwrite($fp, "/*\n\tTable: '{$table_name}'\n*/\n"); break; } @@ -286,23 +287,14 @@ foreach ($supported_dbms as $dbms) case 'sqlite': if (isset($column_data[2]) && $column_data[2] == 'auto_increment') { - $line .= "\t{$column_name} INTEGER "; + $line .= "\t{$column_name} INTEGER PRIMARY KEY "; + $generator = $column_name; } else { $line .= "\t{$column_name} {$column_type} "; } - if (isset($table_data['PRIMARY_KEY'])) - { - $table_data['PRIMARY_KEY'] = (!is_array($table_data['PRIMARY_KEY'])) ? array($table_data['PRIMARY_KEY']) : $table_data['PRIMARY_KEY']; - - if (in_array($column_name, $table_data['PRIMARY_KEY'])) - { - $line .= 'PRIMARY KEY '; - } - } - $line .= 'NOT NULL '; $line .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}'" : ''; $line .= ",\n"; @@ -331,9 +323,18 @@ foreach ($supported_dbms as $dbms) } $line .= "\t[{$column_name}] {$column_type} "; + if (!is_null($column_data[1])) { - $line .= 'DEFAULT (' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ') '; + // For hexadecimal values do not use single quotes + if (strpos($column_data[1], '0x') === 0) + { + $line .= 'DEFAULT (' . $column_data[1] . ') '; + } + else + { + $line .= 'DEFAULT (' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ') '; + } } if (isset($column_data[2]) && $column_data[2] == 'auto_increment') @@ -348,7 +349,10 @@ foreach ($supported_dbms as $dbms) case 'oracle': $line .= "\t{$column_name} {$column_type} "; $line .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : ''; - $line .= "NOT NULL,\n"; + + // In Oracle empty strings ('') are treated as NULL. + // Therefore in oracle we allow NULL's for all DEFAULT '' entries + $line .= ($column_data[1] === '') ? ",\n" : "NOT NULL,\n"; if (isset($column_data[2]) && $column_data[2] == 'auto_increment') { @@ -391,12 +395,6 @@ foreach ($supported_dbms as $dbms) $line .= "\n);;\n\n"; break; - case 'sqlite': - // Remove last line delimiter... - $line = substr($line, 0, -2); - $line .= "\n);\n\n"; - break; - case 'mssql': $line = substr($line, 0, -2); $line .= "\n) ON [PRIMARY]" . (($textimage) ? ' TEXTIMAGE_ON [PRIMARY]' : '') . "\n"; @@ -423,6 +421,13 @@ foreach ($supported_dbms as $dbms) $line .= "ALTER TABLE {$table_name} ADD PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ");;\n\n"; break; + case 'sqlite': + if ($generator === false || !in_array($generator, $table_data['PRIMARY_KEY'])) + { + $line .= "\tPRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . "),\n"; + } + break; + case 'mssql': $line .= "ALTER TABLE [{$table_name}] WITH NOCHECK ADD \n"; $line .= "\tCONSTRAINT [PK_{$table_name}] PRIMARY KEY CLUSTERED \n"; @@ -468,6 +473,12 @@ foreach ($supported_dbms as $dbms) $line = substr($line, 0, -2); $line .= "\n);\n\n"; break; + + case 'sqlite': + // Remove last line delimiter... + $line = substr($line, 0, -2); + $line .= "\n);\n\n"; + break; } // Write Keys @@ -518,7 +529,7 @@ foreach ($supported_dbms as $dbms) case 'postgres': $line .= ($key_data[0] == 'INDEX') ? 'CREATE INDEX' : ''; $line .= ($key_data[0] == 'UNIQUE') ? 'CREATE UNIQUE INDEX' : ''; - + $line .= " {$table_name}_{$key_name} ON {$table_name} (" . implode(', ', $key_data[1]) . ");\n"; break; } @@ -540,7 +551,7 @@ foreach ($supported_dbms as $dbms) $line .= "\nCREATE GENERATOR {$table_name}_gen;;\n"; $line .= 'SET GENERATOR ' . $table_name . "_gen TO 0;;\n\n"; - $line .= 'CREATE TRIGGER t_' . $table_name . '_gen FOR ' . $table_name . "\n"; + $line .= 'CREATE TRIGGER t_' . $table_name . ' FOR ' . $table_name . "\n"; $line .= "BEFORE INSERT\nAS\nBEGIN\n"; $line .= "\tNEW.{$generator} = GEN_ID({$table_name}_gen, 1);\nEND;;\n\n"; } @@ -551,7 +562,7 @@ foreach ($supported_dbms as $dbms) { $line .= "\nCREATE SEQUENCE {$table_name}_seq\n/\n\n"; - $line .= "CREATE OR REPLACE TRIGGER ai_{$table_name}_seq\n"; + $line .= "CREATE OR REPLACE TRIGGER t_{$table_name}\n"; $line .= "BEFORE INSERT ON {$table_name}\n"; $line .= "FOR EACH ROW WHEN (\n"; $line .= "\tnew.{$generator} IS NULL OR new.{$generator} = 0\n"; @@ -572,14 +583,69 @@ foreach ($supported_dbms as $dbms) switch ($dbms) { case 'firebird': - $line = "\n\nDECLARE EXTERNAL FUNCTION STRLEN\n"; - $line .= "\tCSTRING(32767)\n"; - $line .= "RETURNS INTEGER BY VALUE\n"; - $line .= "ENTRY_POINT 'IB_UDF_strlen' MODULE_NAME 'ib_udf';;\n\n"; - - $line .= "DECLARE EXTERNAL FUNCTION LOWER CSTRING(80)\n"; - $line .= "RETURNS CSTRING(80) FREE_IT \n"; - $line .= "ENTRY_POINT 'IB_UDF_lower' MODULE_NAME 'ib_udf';;\n\n"; + $line = << array('TINT:2', 0), ), 'KEYS' => array( - 'group_id' => array('INDEX', 'group_id'), - 'auth_option_id' => array('INDEX', 'auth_option_id'), + 'group_id' => array('INDEX', 'group_id'), + 'auth_opt_id' => array('INDEX', 'auth_option_id'), ), ); @@ -753,7 +819,7 @@ function get_schema_struct() ), 'PRIMARY_KEY' => 'bbcode_id', 'KEYS' => array( - 'display_in_posting' => array('INDEX', 'display_on_posting'), + 'display_on_post' => array('INDEX', 'display_on_posting'), ), ); @@ -786,7 +852,7 @@ function get_schema_struct() $schema_data['phpbb_config'] = array( 'COLUMNS' => array( - 'config_name' => array('VCHAR', ''), + 'config_name' => array('VCHAR:252', ''), 'config_value' => array('VCHAR', ''), 'is_dynamic' => array('BOOL', 0), ), @@ -809,7 +875,7 @@ function get_schema_struct() $schema_data['phpbb_disallow'] = array( 'COLUMNS' => array( 'disallow_id' => array('UINT', NULL, 'auto_increment'), - 'disallow_username' => array('VCHAR', ''), + 'disallow_username' => array('VCHAR:252', ''), ), 'PRIMARY_KEY' => 'disallow_id', ); @@ -898,7 +964,7 @@ function get_schema_struct() 'PRIMARY_KEY' => 'forum_id', 'KEYS' => array( 'left_right_id' => array('INDEX', array('left_id', 'right_id')), - 'forum_last_post_id' => array('INDEX', 'forum_last_post_id'), + 'forum_lastpost_id' => array('INDEX', 'forum_last_post_id'), ), ); @@ -929,7 +995,7 @@ function get_schema_struct() 'KEYS' => array( 'forum_id' => array('INDEX', 'forum_id'), 'user_id' => array('INDEX', 'user_id'), - 'notify_status' => array('INDEX', 'notify_status'), + 'notify_stat' => array('INDEX', 'notify_status'), ), ); @@ -970,6 +1036,9 @@ function get_schema_struct() 'display_on_posting' => array('BOOL', 1), ), 'PRIMARY_KEY' => 'icons_id', + 'KEYS' => array( + 'display_on_posting' => array('INDEX', 'display_on_posting'), + ), ); $schema_data['phpbb_lang'] = array( @@ -1014,13 +1083,13 @@ function get_schema_struct() 'COLUMNS' => array( 'forum_id' => array('UINT', 0), 'user_id' => array('UINT', 0), - 'username' => array('VCHAR', ''), + 'username' => array('VCHAR:252', ''), 'group_id' => array('UINT', 0), 'group_name' => array('VCHAR', ''), 'display_on_index' => array('BOOL', 1), ), 'KEYS' => array( - 'display_on_index' => array('INDEX', 'display_on_index'), + 'disp_idx' => array('INDEX', 'display_on_index'), 'forum_id' => array('INDEX', 'forum_id'), ), ); @@ -1055,7 +1124,7 @@ function get_schema_struct() 'poll_option_total' => array('UINT', 0), ), 'KEYS' => array( - 'poll_option_id' => array('INDEX', 'poll_option_id'), + 'poll_opt_id' => array('INDEX', 'poll_option_id'), 'topic_id' => array('INDEX', 'topic_id'), ), ); @@ -1089,7 +1158,7 @@ function get_schema_struct() 'enable_smilies' => array('BOOL', 1), 'enable_magic_url' => array('BOOL', 1), 'enable_sig' => array('BOOL', 1), - 'post_username' => array('VCHAR', ''), + 'post_username' => array('VCHAR:252', ''), 'post_subject' => array('XSTEXT', ''), 'post_text' => array('MTEXT', ''), 'post_checksum' => array('VCHAR:32', ''), @@ -1193,7 +1262,7 @@ function get_schema_struct() ), 'KEYS' => array( 'msg_id' => array('INDEX', 'msg_id'), - 'user_folder_id' => array('INDEX', array('user_id', 'folder_id')), + 'usr_flder_id' => array('INDEX', array('user_id', 'folder_id')), ), ); @@ -1218,8 +1287,8 @@ function get_schema_struct() ), 'PRIMARY_KEY' => 'field_id', 'KEYS' => array( - 'field_type' => array('INDEX', 'field_type'), - 'field_order' => array('INDEX', 'field_order'), + 'fld_type' => array('INDEX', 'field_type'), + 'fld_ordr' => array('INDEX', 'field_order'), ), ); @@ -1299,13 +1368,13 @@ function get_schema_struct() $schema_data['phpbb_search_wordlist'] = array( 'COLUMNS' => array( - 'word_text' => array('VCHAR_BIN', ''), 'word_id' => array('UINT', NULL, 'auto_increment'), + 'word_text' => array('VCHAR_BIN', ''), 'word_common' => array('BOOL', 0), ), - 'PRIMARY_KEY' => 'word_text', + 'PRIMARY_KEY' => 'word_id', 'KEYS' => array( - 'word_id' => array('INDEX', 'word_id'), + 'wrd_txt' => array('UNIQUE', 'word_text'), ), ); @@ -1377,14 +1446,14 @@ function get_schema_struct() ), 'PRIMARY_KEY' => 'smiley_id', 'KEYS' => array( - 'display_on_posting' => array('INDEX', 'display_on_posting'), + 'display_on_post' => array('INDEX', 'display_on_posting'), ), ); $schema_data['phpbb_styles'] = array( 'COLUMNS' => array( 'style_id' => array('TINT:4', NULL, 'auto_increment'), - 'style_name' => array('VCHAR', ''), + 'style_name' => array('VCHAR:252', ''), 'style_copyright' => array('VCHAR', ''), 'style_active' => array('BOOL', 1), 'template_id' => array('TINT:4', 0), @@ -1403,7 +1472,7 @@ function get_schema_struct() $schema_data['phpbb_styles_template'] = array( 'COLUMNS' => array( 'template_id' => array('TINT:4', NULL, 'auto_increment'), - 'template_name' => array('VCHAR', ''), + 'template_name' => array('VCHAR:252', ''), 'template_copyright' => array('VCHAR', ''), 'template_path' => array('VCHAR:100', ''), 'bbcode_bitfield' => array('VARBINARY', array('default' => '', 'mysql' => '0x90D8', 'mssql' => '0x90D8', 'oracle' => '90D8', 'postgres' => '\220\330')), @@ -1411,7 +1480,7 @@ function get_schema_struct() ), 'PRIMARY_KEY' => 'template_id', 'KEYS' => array( - 'template_name' => array('UNIQUE', 'template_name'), + 'tmplte_nm' => array('UNIQUE', 'template_name'), ), ); @@ -1424,15 +1493,15 @@ function get_schema_struct() 'template_data' => array('MTEXT', ''), ), 'KEYS' => array( - 'template_id' => array('INDEX', 'template_id'), - 'template_filename' => array('INDEX', 'template_filename'), + 'tid' => array('INDEX', 'template_id'), + 'tfn' => array('INDEX', 'template_filename'), ), ); $schema_data['phpbb_styles_theme'] = array( 'COLUMNS' => array( 'theme_id' => array('TINT:4', NULL, 'auto_increment'), - 'theme_name' => array('VCHAR', ''), + 'theme_name' => array('VCHAR:252', ''), 'theme_copyright' => array('VCHAR', ''), 'theme_path' => array('VCHAR:100', ''), 'theme_storedb' => array('BOOL', 0), @@ -1448,89 +1517,108 @@ function get_schema_struct() $schema_data['phpbb_styles_imageset'] = array( 'COLUMNS' => array( 'imageset_id' => array('TINT:4', NULL, 'auto_increment'), - 'imageset_name' => array('VCHAR', ''), + 'imageset_name' => array('VCHAR:252', ''), 'imageset_copyright' => array('VCHAR', ''), 'imageset_path' => array('VCHAR:100', ''), + 'site_logo' => array('VCHAR:200', ''), - 'btn_post' => array('VCHAR:200', ''), - 'btn_post_pm' => array('VCHAR:200', ''), - 'btn_reply' => array('VCHAR:200', ''), - 'btn_reply_pm' => array('VCHAR:200', ''), - 'btn_locked' => array('VCHAR:200', ''), - 'btn_profile' => array('VCHAR:200', ''), - 'btn_pm' => array('VCHAR:200', ''), - 'btn_delete' => array('VCHAR:200', ''), - 'btn_info' => array('VCHAR:200', ''), - 'btn_quote' => array('VCHAR:200', ''), - 'btn_search' => array('VCHAR:200', ''), - 'btn_edit' => array('VCHAR:200', ''), - 'btn_report' => array('VCHAR:200', ''), - 'btn_warn' => array('VCHAR:200', ''), - 'btn_email' => array('VCHAR:200', ''), - 'btn_www' => array('VCHAR:200', ''), - 'btn_icq' => array('VCHAR:200', ''), - 'btn_aim' => array('VCHAR:200', ''), - 'btn_yim' => array('VCHAR:200', ''), - 'btn_msnm' => array('VCHAR:200', ''), - 'btn_jabber' => array('VCHAR:200', ''), - 'btn_online' => array('VCHAR:200', ''), - 'btn_offline' => array('VCHAR:200', ''), - 'btn_friend' => array('VCHAR:200', ''), - 'btn_foe' => array('VCHAR:200', ''), - 'icon_unapproved' => array('VCHAR:200', ''), - 'icon_reported' => array('VCHAR:200', ''), - 'icon_attach' => array('VCHAR:200', ''), - 'icon_post' => array('VCHAR:200', ''), - 'icon_post_new' => array('VCHAR:200', ''), - 'icon_post_latest' => array('VCHAR:200', ''), - 'icon_post_newest' => array('VCHAR:200', ''), - 'forum' => array('VCHAR:200', ''), - 'forum_new' => array('VCHAR:200', ''), - 'forum_locked' => array('VCHAR:200', ''), - 'forum_link' => array('VCHAR:200', ''), - 'sub_forum' => array('VCHAR:200', ''), - 'sub_forum_new' => array('VCHAR:200', ''), - 'folder' => array('VCHAR:200', ''), - 'folder_moved' => array('VCHAR:200', ''), - 'folder_post' => array('VCHAR:200', ''), - 'folder_new' => array('VCHAR:200', ''), - 'folder_new_post' => array('VCHAR:200', ''), - 'folder_hot' => array('VCHAR:200', ''), - 'folder_hot_post' => array('VCHAR:200', ''), - 'folder_hot_new' => array('VCHAR:200', ''), - 'folder_hot_new_post' => array('VCHAR:200', ''), - 'folder_lock' => array('VCHAR:200', ''), - 'folder_lock_post' => array('VCHAR:200', ''), - 'folder_lock_new' => array('VCHAR:200', ''), - 'folder_lock_new_post' => array('VCHAR:200', ''), - 'folder_lock_announce' => array('VCHAR:200', ''), - 'folder_lock_announce_new' => array('VCHAR:200', ''), - 'folder_lock_announce_post' => array('VCHAR:200', ''), - 'folder_lock_announce_new_post' => array('VCHAR:200', ''), - 'folder_lock_global' => array('VCHAR:200', ''), - 'folder_lock_global_new' => array('VCHAR:200', ''), - 'folder_lock_global_post' => array('VCHAR:200', ''), - 'folder_lock_global_new_post' => array('VCHAR:200', ''), - 'folder_lock_sticky' => array('VCHAR:200', ''), - 'folder_lock_sticky_new' => array('VCHAR:200', ''), - 'folder_lock_sticky_post' => array('VCHAR:200', ''), - 'folder_lock_sticky_new_post' => array('VCHAR:200', ''), - 'folder_sticky' => array('VCHAR:200', ''), - 'folder_sticky_post' => array('VCHAR:200', ''), - 'folder_sticky_new' => array('VCHAR:200', ''), - 'folder_sticky_new_post' => array('VCHAR:200', ''), - 'folder_announce' => array('VCHAR:200', ''), - 'folder_announce_post' => array('VCHAR:200', ''), - 'folder_announce_new' => array('VCHAR:200', ''), - 'folder_announce_new_post' => array('VCHAR:200', ''), - 'folder_global' => array('VCHAR:200', ''), - 'folder_global_post' => array('VCHAR:200', ''), - 'folder_global_new' => array('VCHAR:200', ''), - 'folder_global_new_post' => array('VCHAR:200', ''), + 'upload_bar' => array('VCHAR:200', ''), 'poll_left' => array('VCHAR:200', ''), 'poll_center' => array('VCHAR:200', ''), 'poll_right' => array('VCHAR:200', ''), - 'attach_progress_bar' => array('VCHAR:200', ''), + 'icon_friend' => array('VCHAR:200', ''), + 'icon_foe' => array('VCHAR:200', ''), + + 'forum_link' => array('VCHAR:200', ''), + 'forum_read' => array('VCHAR:200', ''), + 'forum_read_locked' => array('VCHAR:200', ''), + 'forum_read_subforum' => array('VCHAR:200', ''), + 'forum_unread' => array('VCHAR:200', ''), + 'forum_unread_locked' => array('VCHAR:200', ''), + 'forum_unread_subforum' => array('VCHAR:200', ''), + + 'topic_moved' => array('VCHAR:200', ''), + + 'topic_read' => array('VCHAR:200', ''), + 'topic_read_mine' => array('VCHAR:200', ''), + 'topic_read_hot' => array('VCHAR:200', ''), + 'topic_read_hot_mine' => array('VCHAR:200', ''), + 'topic_read_locked' => array('VCHAR:200', ''), + 'topic_read_locked_mine' => array('VCHAR:200', ''), + + 'topic_unread' => array('VCHAR:200', ''), + 'topic_unread_mine' => array('VCHAR:200', ''), + 'topic_unread_hot' => array('VCHAR:200', ''), + 'topic_unread_hot_mine' => array('VCHAR:200', ''), + 'topic_unread_locked' => array('VCHAR:200', ''), + 'topic_unread_locked_mine' => array('VCHAR:200', ''), + + 'sticky_read' => array('VCHAR:200', ''), + 'sticky_read_mine' => array('VCHAR:200', ''), + 'sticky_read_locked' => array('VCHAR:200', ''), + 'sticky_read_locked_mine' => array('VCHAR:200', ''), + 'sticky_unread' => array('VCHAR:200', ''), + 'sticky_unread_mine' => array('VCHAR:200', ''), + 'sticky_unread_locked' => array('VCHAR:200', ''), + 'sticky_unread_locked_mine' => array('VCHAR:200', ''), + + 'announce_read' => array('VCHAR:200', ''), + 'announce_read_mine' => array('VCHAR:200', ''), + 'announce_read_locked' => array('VCHAR:200', ''), + 'announce_read_locked_mine' => array('VCHAR:200', ''), + 'announce_unread' => array('VCHAR:200', ''), + 'announce_unread_mine' => array('VCHAR:200', ''), + 'announce_unread_locked' => array('VCHAR:200', ''), + 'announce_unread_locked_mine' => array('VCHAR:200', ''), + + 'global_read' => array('VCHAR:200', ''), + 'global_read_mine' => array('VCHAR:200', ''), + 'global_read_locked' => array('VCHAR:200', ''), + 'global_read_locked_mine' => array('VCHAR:200', ''), + 'global_unread' => array('VCHAR:200', ''), + 'global_unread_mine' => array('VCHAR:200', ''), + 'global_unread_locked' => array('VCHAR:200', ''), + 'global_unread_locked_mine' => array('VCHAR:200', ''), + + 'pm_read' => array('VCHAR:200', ''), + 'pm_unread' => array('VCHAR:200', ''), + + 'icon_contact_aim' => array('VCHAR:200', ''), + 'icon_contact_email' => array('VCHAR:200', ''), + 'icon_contact_icq' => array('VCHAR:200', ''), + 'icon_contact_jabber' => array('VCHAR:200', ''), + 'icon_contact_msnm' => array('VCHAR:200', ''), + 'icon_contact_pm' => array('VCHAR:200', ''), + 'icon_contact_yahoo' => array('VCHAR:200', ''), + 'icon_contact_www' => array('VCHAR:200', ''), + + 'icon_post_delete' => array('VCHAR:200', ''), + 'icon_post_edit' => array('VCHAR:200', ''), + 'icon_post_info' => array('VCHAR:200', ''), + 'icon_post_quote' => array('VCHAR:200', ''), + 'icon_post_report' => array('VCHAR:200', ''), + 'icon_post_target' => array('VCHAR:200', ''), + 'icon_post_target_unread' => array('VCHAR:200', ''), + + 'icon_topic_attach' => array('VCHAR:200', ''), + 'icon_topic_latest' => array('VCHAR:200', ''), + 'icon_topic_newest' => array('VCHAR:200', ''), + 'icon_topic_reported' => array('VCHAR:200', ''), + 'icon_topic_unapproved' => array('VCHAR:200', ''), + + 'icon_user_online' => array('VCHAR:200', ''), + 'icon_user_offline' => array('VCHAR:200', ''), + 'icon_user_profile' => array('VCHAR:200', ''), + 'icon_user_search' => array('VCHAR:200', ''), + 'icon_user_warn' => array('VCHAR:200', ''), + + 'button_pm_forward' => array('VCHAR:200', ''), + 'button_pm_new' => array('VCHAR:200', ''), + 'button_pm_reply' => array('VCHAR:200', ''), + 'button_topic_locked' => array('VCHAR:200', ''), + 'button_topic_new' => array('VCHAR:200', ''), + 'button_topic_reply' => array('VCHAR:200', ''), + 'user_icon1' => array('VCHAR:200', ''), 'user_icon2' => array('VCHAR:200', ''), 'user_icon3' => array('VCHAR:200', ''), @@ -1544,7 +1632,7 @@ function get_schema_struct() ), 'PRIMARY_KEY' => 'imageset_id', 'KEYS' => array( - 'imageset_name' => array('UNIQUE', 'imageset_name'), + 'imgset_nm' => array('UNIQUE', 'imageset_name'), ), ); @@ -1584,9 +1672,9 @@ function get_schema_struct() ), 'PRIMARY_KEY' => 'topic_id', 'KEYS' => array( - 'forum_id' => array('INDEX', 'forum_id'), - 'forum_id_type' => array('INDEX', array('forum_id', 'topic_type')), - 'topic_last_post_time' => array('INDEX', 'topic_last_post_time'), + 'forum_id' => array('INDEX', 'forum_id'), + 'forum_id_type' => array('INDEX', array('forum_id', 'topic_type')), + 'last_post_time' => array('INDEX', 'topic_last_post_time'), ), ); @@ -1621,7 +1709,7 @@ function get_schema_struct() 'KEYS' => array( 'topic_id' => array('INDEX', 'topic_id'), 'user_id' => array('INDEX', 'user_id'), - 'notify_status' => array('INDEX', 'notify_status'), + 'notify_stat' => array('INDEX', 'notify_status'), ), ); @@ -1758,11 +1846,14 @@ function get_schema_struct() /** -* Data put into the header for oracle +* Data put into the header for various dbms */ -function oracle_custom_data() +function custom_data($dbms) { - return << LOWER($2)' LANGUAGE SQL STRICT; CREATE FUNCTION _varchar_ci_greater_equals(varchar_ci, varchar_ci) RETURNS boolean AS 'SELECT LOWER($1) >= LOWER($2)' LANGUAGE SQL STRICT; -/* Operators */ +/* + Operators +*/ CREATE OPERATOR <( PROCEDURE = _varchar_ci_less_than, LEFTARG = varchar_ci, @@ -1879,6 +1973,31 @@ CREATE OPERATOR =( SORT1= <); EOF; + break; + + case 'firebird': + return << \ No newline at end of file -- cgit v1.2.1 From 53085a4c78b3004d1e4adf8e06b0617f7f8a288b Mon Sep 17 00:00:00 2001 From: Meik Sievertsen Date: Wed, 9 Aug 2006 21:03:46 +0000 Subject: - load tracking updates. Need to be tested on a clean installation too - at the moment only tiny quirks are noticed at area51. - reported bugs fixed git-svn-id: file:///svn/phpbb/trunk@6256 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/develop/create_schema_files.php | 44 ++++++++++++++++++++++++++++++----- 1 file changed, 38 insertions(+), 6 deletions(-) (limited to 'phpBB/develop/create_schema_files.php') diff --git a/phpBB/develop/create_schema_files.php b/phpBB/develop/create_schema_files.php index 2758e88373..a853d8a032 100644 --- a/phpBB/develop/create_schema_files.php +++ b/phpBB/develop/create_schema_files.php @@ -42,7 +42,7 @@ $dbms_type_map = array( 'MTEXT' => 'mediumtext', 'TIMESTAMP' => 'int(11) UNSIGNED', 'DECIMAL' => 'decimal(5,2)', - 'VCHAR_BIN' => 'varchar(252) BINARY', + 'VCHAR_BIN' => 'varchar(252) /*!40101 CHARACTER SET utf8 */ BINARY', 'VCHAR_CI' => 'varchar(252)', 'VARBINARY' => 'varbinary(255)', ), @@ -64,7 +64,7 @@ $dbms_type_map = array( 'MTEXT' => 'BLOB SUB_TYPE TEXT', 'TIMESTAMP' => 'INTEGER', 'DECIMAL' => 'DOUBLE PRECISION', - 'VCHAR_BIN' => 'VARCHAR(252)', + 'VCHAR_BIN' => 'VARCHAR(252) CHARACTER SET UNICODE_FSS', 'VCHAR_CI' => 'VARCHAR(252)', 'VARBINARY' => 'CHAR(255)', ), @@ -108,7 +108,7 @@ $dbms_type_map = array( 'MTEXT' => 'clob', 'TIMESTAMP' => 'number(11)', 'DECIMAL' => 'number(5, 2)', - 'VCHAR_BIN' => 'varchar2(252)', + 'VCHAR_BIN' => 'nvarchar2(252)', 'VCHAR_CI' => 'varchar2(252)', 'VARBINARY' => 'raw(255)', ), @@ -130,7 +130,7 @@ $dbms_type_map = array( 'MTEXT' => 'mediumtext(16777215)', 'TIMESTAMP' => 'INTEGER UNSIGNED', //'int(11) UNSIGNED', 'DECIMAL' => 'decimal(5,2)', - 'VCHAR_BIN' => 'varchar(252)', + 'VCHAR_BIN' => 'nvarchar(252)', 'VCHAR_CI' => 'varchar(252)', 'VARBINARY' => 'blob', ), @@ -182,7 +182,7 @@ foreach ($supported_dbms as $dbms) case 'sqlite': $line = "#\n# SQLite Schema for phpBB 3.x - (c) phpBB Group, 2005\n#\n# \$I" . "d: $\n#\n\n"; - $line .= "BEGIN TRANSACTION;\n\n"; + $line .= "BEGIN TRANSACTION;;\n\n"; break; case 'mssql': @@ -477,7 +477,7 @@ foreach ($supported_dbms as $dbms) case 'sqlite': // Remove last line delimiter... $line = substr($line, 0, -2); - $line .= "\n);\n\n"; + $line .= "\n);;\n\n"; break; } @@ -526,6 +526,12 @@ foreach ($supported_dbms as $dbms) break; case 'sqlite': + $line .= ($key_data[0] == 'INDEX') ? 'CREATE INDEX' : ''; + $line .= ($key_data[0] == 'UNIQUE') ? 'CREATE UNIQUE INDEX' : ''; + + $line .= " {$table_name}_{$key_name} ON {$table_name} (" . implode(', ', $key_data[1]) . ");;\n"; + break; + case 'postgres': $line .= ($key_data[0] == 'INDEX') ? 'CREATE INDEX' : ''; $line .= ($key_data[0] == 'UNIQUE') ? 'CREATE UNIQUE INDEX' : ''; @@ -653,6 +659,17 @@ EOF; break; case 'sqlite': + $line = ' +CREATE TRIGGER "t_phpbb_styles_template" +AFTER INSERT ON "phpbb_styles_template" +FOR EACH ROW WHEN NEW.bbcode_bitfield = \'\' +BEGIN + UPDATE phpbb_styles_template SET bbcode_bitfield = binary_insert(1) WHERE template_id = NEW.template_id; +END;; + +COMMIT;;'; + break; + case 'postgres': $line = "\nCOMMIT;"; break; @@ -802,6 +819,12 @@ function get_schema_struct() 'ban_give_reason' => array('STEXT', ''), ), 'PRIMARY_KEY' => 'ban_id', + 'KEYS' => array( + 'ban_end' => array('INDEX', 'ban_end'), + 'ban_user' => array('INDEX', array('ban_userid', 'ban_exclude')), + 'ban_email' => array('INDEX', array('ban_email', 'ban_exclude')), + 'ban_ip' => array('INDEX', array('ban_ip', 'ban_exclude')), + ), ); $schema_data['phpbb_bbcodes'] = array( @@ -870,6 +893,9 @@ function get_schema_struct() 'code' => array('VCHAR:8', ''), ), 'PRIMARY_KEY' => array('session_id', 'confirm_id'), + 'KEYS' => array( + 'confirm_type' => array('INDEX', 'confirm_type'), + ), ); $schema_data['phpbb_disallow'] = array( @@ -1245,6 +1271,9 @@ function get_schema_struct() 'rule_folder_id' => array('INT:4', 0), ), 'PRIMARY_KEY' => 'rule_id', + 'KEYS' => array( + 'user_id' => array('INDEX', 'user_id'), + ), ); $schema_data['phpbb_privmsgs_to'] = array( @@ -1262,6 +1291,7 @@ function get_schema_struct() ), 'KEYS' => array( 'msg_id' => array('INDEX', 'msg_id'), + 'author_id' => array('INDEX', 'author_id'), 'usr_flder_id' => array('INDEX', array('user_id', 'folder_id')), ), ); @@ -1386,6 +1416,7 @@ function get_schema_struct() ), 'KEYS' => array( 'word_id' => array('INDEX', 'word_id'), + 'post_id' => array('INDEX', 'post_id'), ), ); @@ -1675,6 +1706,7 @@ function get_schema_struct() 'forum_id' => array('INDEX', 'forum_id'), 'forum_id_type' => array('INDEX', array('forum_id', 'topic_type')), 'last_post_time' => array('INDEX', 'topic_last_post_time'), + 'fid_time_moved' => array('INDEX', array('forum_id', 'topic_last_post_time', 'topic_moved_id')), ), ); -- cgit v1.2.1 From 8405f0d324fd42bec2f775986e69e5d8cf548ebf Mon Sep 17 00:00:00 2001 From: Meik Sievertsen Date: Sat, 12 Aug 2006 13:14:39 +0000 Subject: sql_in_set changes git-svn-id: file:///svn/phpbb/trunk@6271 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/develop/create_schema_files.php | 96 ++++------------------------------- 1 file changed, 11 insertions(+), 85 deletions(-) (limited to 'phpBB/develop/create_schema_files.php') diff --git a/phpBB/develop/create_schema_files.php b/phpBB/develop/create_schema_files.php index a853d8a032..bab008436f 100644 --- a/phpBB/develop/create_schema_files.php +++ b/phpBB/develop/create_schema_files.php @@ -182,7 +182,7 @@ foreach ($supported_dbms as $dbms) case 'sqlite': $line = "#\n# SQLite Schema for phpBB 3.x - (c) phpBB Group, 2005\n#\n# \$I" . "d: $\n#\n\n"; - $line .= "BEGIN TRANSACTION;;\n\n"; + $line .= "BEGIN TRANSACTION;\n\n"; break; case 'mssql': @@ -477,7 +477,7 @@ foreach ($supported_dbms as $dbms) case 'sqlite': // Remove last line delimiter... $line = substr($line, 0, -2); - $line .= "\n);;\n\n"; + $line .= "\n);\n\n"; break; } @@ -529,7 +529,7 @@ foreach ($supported_dbms as $dbms) $line .= ($key_data[0] == 'INDEX') ? 'CREATE INDEX' : ''; $line .= ($key_data[0] == 'UNIQUE') ? 'CREATE UNIQUE INDEX' : ''; - $line .= " {$table_name}_{$key_name} ON {$table_name} (" . implode(', ', $key_data[1]) . ");;\n"; + $line .= " {$table_name}_{$key_name} ON {$table_name} (" . implode(', ', $key_data[1]) . ");\n"; break; case 'postgres': @@ -588,86 +588,12 @@ foreach ($supported_dbms as $dbms) // Write custom function at the end for some db's switch ($dbms) { - case 'firebird': - $line = << array('MTEXT', ''), 'forum_name' => array('STEXT', ''), 'forum_desc' => array('TEXT', ''), - 'forum_desc_bitfield' => array('VARBINARY', array('default' => '', 'mssql' => '0x', 'postgres' => '\000')), + 'forum_desc_bitfield' => array('VCHAR:252', ''), 'forum_desc_options' => array('UINT:11', 0), 'forum_desc_uid' => array('VCHAR:5', ''), 'forum_link' => array('VCHAR', ''), @@ -964,7 +890,7 @@ function get_schema_struct() 'forum_image' => array('VCHAR', ''), 'forum_rules' => array('TEXT', ''), 'forum_rules_link' => array('VCHAR', ''), - 'forum_rules_bitfield' => array('VARBINARY', array('default' => '', 'mssql' => '0x', 'postgres' => '\000')), + 'forum_rules_bitfield' => array('VCHAR:252', ''), 'forum_rules_options' => array('UINT:11', 0), 'forum_rules_uid' => array('VCHAR:5', ''), 'forum_topics_per_page' => array('TINT:4', 0), @@ -1031,7 +957,7 @@ function get_schema_struct() 'group_type' => array('TINT:4', 1), 'group_name' => array('VCHAR_CI', ''), 'group_desc' => array('TEXT', ''), - 'group_desc_bitfield' => array('VARBINARY', array('default' => '', 'mssql' => '0x', 'postgres' => '\000')), + 'group_desc_bitfield' => array('VCHAR:252', ''), 'group_desc_options' => array('UINT:11', 0), 'group_desc_uid' => array('VCHAR:5', ''), 'group_display' => array('BOOL', 0), @@ -1190,7 +1116,7 @@ function get_schema_struct() 'post_checksum' => array('VCHAR:32', ''), 'post_encoding' => array('VCHAR:20', 'iso-8859-1'), 'post_attachment' => array('BOOL', 0), - 'bbcode_bitfield' => array('VARBINARY', array('default' => '', 'mssql' => '0x', 'postgres' => '\000')), + 'bbcode_bitfield' => array('VCHAR:252', ''), 'bbcode_uid' => array('VCHAR:5', ''), 'post_postcount' => array('BOOL', 1), 'post_edit_time' => array('TIMESTAMP', 0), @@ -1229,7 +1155,7 @@ function get_schema_struct() 'message_edit_user' => array('UINT', 0), 'message_encoding' => array('VCHAR:20', 'iso-8859-1'), 'message_attachment' => array('BOOL', 0), - 'bbcode_bitfield' => array('VARBINARY', array('default' => '', 'mssql' => '0x', 'postgres' => '\000')), + 'bbcode_bitfield' => array('VCHAR:252', ''), 'bbcode_uid' => array('VCHAR:5', ''), 'message_edit_time' => array('TIMESTAMP', 0), 'message_edit_count' => array('USINT', 0), @@ -1506,7 +1432,7 @@ function get_schema_struct() 'template_name' => array('VCHAR:252', ''), 'template_copyright' => array('VCHAR', ''), 'template_path' => array('VCHAR:100', ''), - 'bbcode_bitfield' => array('VARBINARY', array('default' => '', 'mysql' => '0x90D8', 'mssql' => '0x90D8', 'oracle' => '90D8', 'postgres' => '\220\330')), + 'bbcode_bitfield' => array('VCHAR:252', 'kNg='), 'template_storedb' => array('BOOL', 0), ), 'PRIMARY_KEY' => 'template_id', @@ -1818,7 +1744,7 @@ function get_schema_struct() 'user_avatar_height' => array('TINT:4', 0), 'user_sig' => array('MTEXT', ''), 'user_sig_bbcode_uid' => array('VCHAR:5', ''), - 'user_sig_bbcode_bitfield' => array('VARBINARY', array('default' => '', 'mssql' => '0x', 'postgres' => '\000')), + 'user_sig_bbcode_bitfield' => array('VCHAR:252', ''), 'user_from' => array('VCHAR:100', ''), 'user_icq' => array('VCHAR:15', ''), 'user_aim' => array('VCHAR', ''), -- cgit v1.2.1 From f5cabf864c7bf29f5415a1ed315dc08b35f759c2 Mon Sep 17 00:00:00 2001 From: Meik Sievertsen Date: Sat, 12 Aug 2006 15:10:43 +0000 Subject: bugfixes git-svn-id: file:///svn/phpbb/trunk@6275 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/develop/create_schema_files.php | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) (limited to 'phpBB/develop/create_schema_files.php') diff --git a/phpBB/develop/create_schema_files.php b/phpBB/develop/create_schema_files.php index bab008436f..5923b0a388 100644 --- a/phpBB/develop/create_schema_files.php +++ b/phpBB/develop/create_schema_files.php @@ -759,11 +759,11 @@ function get_schema_struct() 'bbcode_tag' => array('VCHAR:16', ''), 'bbcode_helpline' => array('VCHAR', ''), 'display_on_posting' => array('BOOL', 0), - 'bbcode_match' => array('VCHAR', ''), + 'bbcode_match' => array('TEXT', ''), 'bbcode_tpl' => array('MTEXT', ''), - 'first_pass_match' => array('VCHAR', ''), - 'first_pass_replace' => array('VCHAR', ''), - 'second_pass_match' => array('VCHAR', ''), + 'first_pass_match' => array('MTEXT', ''), + 'first_pass_replace' => array('MTEXT', ''), + 'second_pass_match' => array('MTEXT', ''), 'second_pass_replace' => array('MTEXT', ''), ), 'PRIMARY_KEY' => 'bbcode_id', -- cgit v1.2.1 From 2ab9ebad06870c7d37154d7844c29a47d741f106 Mon Sep 17 00:00:00 2001 From: Meik Sievertsen Date: Sat, 12 Aug 2006 17:06:38 +0000 Subject: firebird :P git-svn-id: file:///svn/phpbb/trunk@6279 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/develop/create_schema_files.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'phpBB/develop/create_schema_files.php') diff --git a/phpBB/develop/create_schema_files.php b/phpBB/develop/create_schema_files.php index 5923b0a388..1cc5494e53 100644 --- a/phpBB/develop/create_schema_files.php +++ b/phpBB/develop/create_schema_files.php @@ -64,7 +64,7 @@ $dbms_type_map = array( 'MTEXT' => 'BLOB SUB_TYPE TEXT', 'TIMESTAMP' => 'INTEGER', 'DECIMAL' => 'DOUBLE PRECISION', - 'VCHAR_BIN' => 'VARCHAR(252) CHARACTER SET UNICODE_FSS', + 'VCHAR_BIN' => 'VARCHAR(84) CHARACTER SET UNICODE_FSS', 'VCHAR_CI' => 'VARCHAR(252)', 'VARBINARY' => 'CHAR(255)', ), -- cgit v1.2.1