-

-require(dirname(__FILE__) . '/../includes/db/schema_data.php');

-require(dirname(__FILE__) . '/../phpbb/db/tools.php');

-

-// 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_40', 'mysql_41', 'oracle', 'postgres', 'sqlite');

- $line = '';

-

- case 'firebird':

- $line .= custom_data('firebird') . "\n";

- break;

-

- case 'sqlite':

- $line .= "BEGIN TRANSACTION;\n\n";

- break;

-

- break;

- }

-

- fwrite($fp, $line);

-

- foreach ($schema_data as $table_name => $table_data)

- {

- // Write comment about table

- switch ($dbms)

- {

- case 'mysql_40':

- case 'mysql_41':

- case 'firebird':

- case 'sqlite':

- fwrite($fp, "# Table: '{$table_name}'\n");

- break;

-

- case 'mssql':

- case 'oracle':

- case 'postgres':

- fwrite($fp, "/*\n\tTable: '{$table_name}'\n*/\n");

- break;

- }

-

- // Create Table statement

- $generator = $textimage = false;

- $line = '';

-

- switch ($dbms)

- {

- case 'mysql_40':

- case 'mysql_41':

- case 'firebird':

- case 'oracle':

- case 'sqlite':

- case 'postgres':

- $line = "CREATE TABLE {$table_name} (\n";

- break;

-

- case 'mssql':

- $line = "CREATE TABLE [{$table_name}] (\n";

- break;

- }

-

- // Table specific so we don't get overlap

- $modded_array = array();

-

- // Write columns one by one...

- foreach ($table_data['COLUMNS'] as $column_name => $column_data)

- {

- if (strlen($column_name) > 30)

- {

- trigger_error("Column name '$column_name' on table '$table_name' is too long. The maximum is 30 characters.", E_USER_ERROR);

- }

- if (isset($column_data[2]) && $column_data[2] == 'auto_increment' && strlen($column_name) > 26) // "${column_name}_gen"

- {

- trigger_error("Index name '${column_name}_gen' on table '$table_name' is too long. The maximum is 30 characters.", E_USER_ERROR);

- }

-

- // Get type

- if (strpos($column_data[0], ':') !== false)

- {

- list($orig_column_type, $column_length) = explode(':', $column_data[0]);

- if (!is_array($dbms_type_map[$dbms][$orig_column_type . ':']))

- {

- $column_type = sprintf($dbms_type_map[$dbms][$orig_column_type . ':'], $column_length);

- }

- else

- {

- if (isset($dbms_type_map[$dbms][$orig_column_type . ':']['rule']))

- {

- switch ($dbms_type_map[$dbms][$orig_column_type . ':']['rule'][0])

- {

- case 'div':

- $column_length /= $dbms_type_map[$dbms][$orig_column_type . ':']['rule'][1];

- $column_length = ceil($column_length);

- $column_type = sprintf($dbms_type_map[$dbms][$orig_column_type . ':'][0], $column_length);

- break;

- }

- }

-

- if (isset($dbms_type_map[$dbms][$orig_column_type . ':']['limit']))

- {

- switch ($dbms_type_map[$dbms][$orig_column_type . ':']['limit'][0])

- {

- case 'mult':

- $column_length *= $dbms_type_map[$dbms][$orig_column_type . ':']['limit'][1];

- if ($column_length > $dbms_type_map[$dbms][$orig_column_type . ':']['limit'][2])

- {

- $column_type = $dbms_type_map[$dbms][$orig_column_type . ':']['limit'][3];

- $modded_array[$column_name] = $column_type;

- }

- else

- {

- $column_type = sprintf($dbms_type_map[$dbms][$orig_column_type . ':'][0], $column_length);

- }

- break;

- }

- }

- }

- $orig_column_type .= ':';

- }

- else

- {

- $orig_column_type = $column_data[0];

- $column_type = $dbms_type_map[$dbms][$column_data[0]];

- if ($column_type == 'text' || $column_type == 'blob')

- {

- $modded_array[$column_name] = $column_type;

- }

- }

-

- // Adjust default value if db-dependent 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_40':

- case 'mysql_41':

- $line .= "\t{$column_name} {$column_type} ";

-

- // For hexadecimal values do not use single quotes

- if (!is_null($column_data[1]) && substr($column_type, -4) !== 'text' && substr($column_type, -4) !== 'blob')

- {

- $line .= (strpos($column_data[1], '0x') === 0) ? "DEFAULT {$column_data[1]} " : "DEFAULT '{$column_data[1]}' ";

- }

- $line .= 'NOT NULL';

-

- if (isset($column_data[2]))

- {

- if ($column_data[2] == 'auto_increment')

- {

- $line .= ' auto_increment';

- }

- else if ($dbms === 'mysql_41' && $column_data[2] == 'true_sort')

- {

- $line .= ' COLLATE utf8_unicode_ci';

- }

- }

-

- $line .= ",\n";

- break;

-

- case 'sqlite':

- if (isset($column_data[2]) && $column_data[2] == 'auto_increment')

- {

- $line .= "\t{$column_name} INTEGER PRIMARY KEY ";

- $generator = $column_name;

- }

- else

- {

- $line .= "\t{$column_name} {$column_type} ";

- }

-

- $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';

-

- // This is a UNICODE column and thus should be given it's fair share

- if (preg_match('/^X?STEXT_UNI|VCHAR_(CI|UNI:?)/', $column_data[0]))

- {

- $line .= ' COLLATE UNICODE';

- }

-

- $line .= ",\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]))

- {

- // 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')

- {

- $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]}' " : '';

-

- // 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')

- {

- $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";

-

- // Unsigned? Then add a CHECK contraint

- if (in_array($orig_column_type, $unsigned_types))

- {

- $line .= " CHECK ({$column_name} >= 0)";

- }

-

- $line .= ",\n";

- }

- break;

- }

- }

-

- switch ($dbms)

- {

- case 'firebird':

- // 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_40':

- case 'mysql_41':

- 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 '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";

- $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;

-

- case 'sqlite':

- // 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]);

- }

-

- if (strlen($table_name . $key_name) > 30)

- {

- trigger_error("Index name '${table_name}_$key_name' on table '$table_name' is too long. The maximum is 30 characters.", E_USER_ERROR);

- }

-

- switch ($dbms)

- {

- case 'mysql_40':

- case 'mysql_41':

- $line .= ($key_data[0] == 'INDEX') ? "\tKEY" : '';

- $line .= ($key_data[0] == 'UNIQUE') ? "\tUNIQUE" : '';

- foreach ($key_data[1] as $key => $col_name)

- {

- if (isset($modded_array[$col_name]))

- {

- switch ($modded_array[$col_name])

- {

- case 'text':

- case 'blob':

- $key_data[1][$key] = $col_name . '(255)';

- break;

- }

- }

- }

- $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':

- $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' : '';

-

- $line .= " {$table_name}_{$key_name} ON {$table_name} (" . implode(', ', $key_data[1]) . ");\n";

- break;

- }

- }

- }

-

- switch ($dbms)

- {

- case 'mysql_40':

- // Remove last line delimiter...

- $line = substr($line, 0, -2);

- $line .= "\n);\n\n";

- break;

-

- case 'mysql_41':

- // Remove last line delimiter...

- $line = substr($line, 0, -2);

- $line .= "\n) CHARACTER SET `utf8` COLLATE `utf8_bin`;\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 . ' 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 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";

- $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 . "\n");

- }

-

- $line = '';

-

- // Write custom function at the end for some db's

- switch ($dbms)

- {

- case 'mssql':

- // No need to do this, no transaction support for schema changes

- //$line = "\nCOMMIT\nGO\n\n";

- break;

-

- case 'sqlite':

- $line = "\nCOMMIT;";

- break;

-

- case 'postgres':

- $line = "\nCOMMIT;";