diff options
| -rw-r--r-- | phpBB/install/database_update.php | 254 |
1 files changed, 254 insertions, 0 deletions
diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index 3d32a82cc6..77b5f44502 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -2226,6 +2226,260 @@ class updater_db_tools } /** + * Check if table exists + * + * + * @param string $table_name The table name to check for + * @return bool true if table exists, else false + */ + function sql_table_exists($table_name) + { + $this->db->sql_return_on_error(true); + $result = $this->db->sql_query_limit('SELECT * FROM ' . $table_name, 1); + $this->db->sql_return_on_error(false); + + if ($result) + { + $this->db->sql_freeresult($result); + return true; + } + + return false; + } + + /** + * Create SQL Table + * + * @param string $table_name The table name to create + * @param array $table_data Array containing table data. + * @return array Statements if $return_statements is true. + */ + function sql_create_table($table_name, $table_data) + { + // holds the DDL for a column + $columns = $statements = array(); + + if ($this->sql_table_exists($table_name)) + { + return $this->_sql_run_sql($statements); + } + + // Begin transaction + $statements[] = 'begin'; + + // Determine if we have created a PRIMARY KEY in the earliest + $primary_key_gen = false; + + // Determine if the table must be created with TEXTIMAGE + $create_textimage = false; + + // Determine if the table requires a sequence + $create_sequence = false; + + // Begin table sql statement + switch ($this->sql_layer) + { + case 'mssql': + case 'mssqlnative': + $table_sql = 'CREATE TABLE [' . $table_name . '] (' . "\n"; + break; + + default: + $table_sql = 'CREATE TABLE ' . $table_name . ' (' . "\n"; + break; + } + + // Iterate through the columns to create a table + foreach ($table_data['COLUMNS'] as $column_name => $column_data) + { + // here lies an array, filled with information compiled on the column's data + $prepared_column = $this->sql_prepare_column_data($table_name, $column_name, $column_data); + + // here we add the definition of the new column to the list of columns + switch ($this->sql_layer) + { + case 'mssql': + case 'mssqlnative': + $columns[] = "\t [{$column_name}] " . $prepared_column['column_type_sql_default']; + break; + + default: + $columns[] = "\t {$column_name} " . $prepared_column['column_type_sql']; + break; + } + + // see if we have found a primary key set due to a column definition if we have found it, we can stop looking + if (!$primary_key_gen) + { + $primary_key_gen = isset($prepared_column['primary_key_set']) && $prepared_column['primary_key_set']; + } + + // create textimage DDL based off of the existance of certain column types + if (!$create_textimage) + { + $create_textimage = isset($prepared_column['textimage']) && $prepared_column['textimage']; + } + + // create sequence DDL based off of the existance of auto incrementing columns + if (!$create_sequence && isset($prepared_column['auto_increment']) && $prepared_column['auto_increment']) + { + $create_sequence = $column_name; + } + } + + // this makes up all the columns in the create table statement + $table_sql .= implode(",\n", $columns); + + // Close the table for two DBMS and add to the statements + switch ($this->sql_layer) + { + case 'firebird': + $table_sql .= "\n);"; + $statements[] = $table_sql; + break; + + case 'mssql': + case 'mssqlnative': + $table_sql .= "\n) ON [PRIMARY]" . (($create_textimage) ? ' TEXTIMAGE_ON [PRIMARY]' : ''); + $statements[] = $table_sql; + break; + } + + // we have yet to create a primary key for this table, + // this means that we can add the one we really wanted instead + if (!$primary_key_gen) + { + // 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 ($this->sql_layer) + { + case 'mysql_40': + case 'mysql_41': + case 'postgres': + case 'sqlite': + $table_sql .= ",\n\t PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ')'; + break; + + case 'firebird': + case 'mssql': + case 'mssqlnative': + // We need the data here + $old_return_statements = $this->return_statements; + $this->return_statements = true; + + $primary_key_stmts = $this->sql_create_primary_key($table_name, $table_data['PRIMARY_KEY']); + foreach ($primary_key_stmts as $pk_stmt) + { + $statements[] = $pk_stmt; + } + + $this->return_statements = $old_return_statements; + break; + + case 'oracle': + $table_sql .= ",\n\t CONSTRAINT pk_{$table_name} PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ')'; + break; + } + } + } + + // close the table + switch ($this->sql_layer) + { + case 'mysql_41': + // make sure the table is in UTF-8 mode + $table_sql .= "\n) CHARACTER SET `utf8` COLLATE `utf8_bin`;"; + $statements[] = $table_sql; + break; + + case 'mysql_40': + case 'sqlite': + $table_sql .= "\n);"; + $statements[] = $table_sql; + break; + + case 'postgres': + // do we need to add a sequence for auto incrementing columns? + if ($create_sequence) + { + $statements[] = "CREATE SEQUENCE {$table_name}_seq;"; + } + + $table_sql .= "\n);"; + $statements[] = $table_sql; + break; + + case 'oracle': + $table_sql .= "\n);"; + $statements[] = $table_sql; + + // do we need to add a sequence and a tigger for auto incrementing columns? + if ($create_sequence) + { + // create the actual sequence + $statements[] = "CREATE SEQUENCE {$table_name}_seq"; + + // the trigger is the mechanism by which we increment the counter + $trigger = "CREATE OR REPLACE TRIGGER t_{$table_name}\n"; + $trigger .= "BEFORE INSERT ON {$table_name}\n"; + $trigger .= "FOR EACH ROW WHEN (\n"; + $trigger .= "\tnew.{$create_sequence} IS NULL OR new.{$create_sequence} = 0\n"; + $trigger .= ")\n"; + $trigger .= "BEGIN\n"; + $trigger .= "\tSELECT {$table_name}_seq.nextval\n"; + $trigger .= "\tINTO :new.{$create_sequence}\n"; + $trigger .= "\tFROM dual\n"; + $trigger .= "END;"; + + $statements[] = $trigger; + } + break; + + case 'firebird': + if ($create_sequence) + { + $statements[] = "CREATE SEQUENCE {$table_name}_seq;"; + } + 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]); + } + + $old_return_statements = $this->return_statements; + $this->return_statements = true; + + $key_stmts = ($key_data[0] == 'UNIQUE') ? $this->sql_create_unique_index($table_name, $key_name, $key_data[1]) : $this->sql_create_index($table_name, $key_name, $key_data[1]); + + foreach ($key_stmts as $key_stmt) + { + $statements[] = $key_stmt; + } + + $this->return_statements = $old_return_statements; + } + } + + // Commit Transaction + $statements[] = 'commit'; + + return $this->_sql_run_sql($statements); + } + + /** * Handle passed database update array. * Expected structure... * Key being one of the following |