diff options
author | MateBartus <mate.bartus@gmail.com> | 2015-02-22 16:22:10 +0100 |
---|---|---|
committer | MateBartus <mate.bartus@gmail.com> | 2015-02-27 16:18:50 +0100 |
commit | 193c5b86b4b6049a9bf5abc5251d5479493a10df (patch) | |
tree | 9d9c817c5f10dbf47ad0d44ff37ab6c512a38f66 | |
parent | 3657d7a85a5489f55ced17326064e63924303928 (diff) | |
download | forums-193c5b86b4b6049a9bf5abc5251d5479493a10df.tar forums-193c5b86b4b6049a9bf5abc5251d5479493a10df.tar.gz forums-193c5b86b4b6049a9bf5abc5251d5479493a10df.tar.bz2 forums-193c5b86b4b6049a9bf5abc5251d5479493a10df.tar.xz forums-193c5b86b4b6049a9bf5abc5251d5479493a10df.zip |
[ticket/12466] Move classes from acp_database.php to their own files
* Moving classes from acp_database.php to phpbb/db/extractor namespace,
also into separate files
* Adding DocBlocks and property visibility to classes
* Removing globals from code
* Passing former globals to base_extractor's constructor
* Adding DB extractor as a service, also implementing the extractor interface
as well as the extractor factory.
PHPBB3-12466
-rw-r--r-- | phpBB/config/default/container/services_db.yml | 61 | ||||
-rw-r--r-- | phpBB/includes/acp/acp_database.php | 1662 | ||||
-rw-r--r-- | phpBB/phpbb/db/extractor/base_extractor.php | 252 | ||||
-rw-r--r-- | phpBB/phpbb/db/extractor/exception/extractor_not_initialized_exception.php | 24 | ||||
-rw-r--r-- | phpBB/phpbb/db/extractor/exception/invalid_format_exception.php | 22 | ||||
-rw-r--r-- | phpBB/phpbb/db/extractor/extractor_interface.php | 80 | ||||
-rw-r--r-- | phpBB/phpbb/db/extractor/factory.php | 79 | ||||
-rw-r--r-- | phpBB/phpbb/db/extractor/mssql_extractor.php | 524 | ||||
-rw-r--r-- | phpBB/phpbb/db/extractor/mysql_extractor.php | 403 | ||||
-rw-r--r-- | phpBB/phpbb/db/extractor/oracle_extractor.php | 265 | ||||
-rw-r--r-- | phpBB/phpbb/db/extractor/postgres_extractor.php | 338 | ||||
-rw-r--r-- | phpBB/phpbb/db/extractor/sqlite3_extractor.php | 151 | ||||
-rw-r--r-- | phpBB/phpbb/db/extractor/sqlite_extractor.php | 149 |
13 files changed, 2350 insertions, 1660 deletions
diff --git a/phpBB/config/default/container/services_db.yml b/phpBB/config/default/container/services_db.yml index 6afc5a60a9..ae2707b9a5 100644 --- a/phpBB/config/default/container/services_db.yml +++ b/phpBB/config/default/container/services_db.yml @@ -19,6 +19,67 @@ services: arguments: - @dbal.conn.driver +# ----- DB Extractor ----- + dbal.extractor.factory: + class: phpbb\db\extractor\factory + arguments: + - @dbal.conn.driver + - @service_container + + dbal.extractor: + class: phpbb\db\extractor\extractor_interface + factory: ["@dbal.extractor.factory", get] + +# ----- DB Extractors for different drivers ----- +# Scope MUST be prototype for all the handlers to work correctly. + dbal.extractor.extractors.mssql_extractor: + class: phpbb\db\extractor\mssql_extractor + scope: prototype + arguments: + - %core.root_path% + - @request + - @dbal.conn.driver + + dbal.extractor.extractors.mysql_extractor: + class: phpbb\db\extractor\mysql_extractor + scope: prototype + arguments: + - %core.root_path% + - @request + - @dbal.conn.driver + + dbal.extractor.extractors.oracle_extractor: + class: phpbb\db\extractor\oracle_extractor + scope: prototype + arguments: + - %core.root_path% + - @request + - @dbal.conn.driver + + dbal.extractor.extractors.postgres_extractor: + class: phpbb\db\extractor\postgres_extractor + scope: prototype + arguments: + - %core.root_path% + - @request + - @dbal.conn.driver + + dbal.extractor.extractors.sqlite3_extractor: + class: phpbb\db\extractor\sqlite3_extractor + scope: prototype + arguments: + - %core.root_path% + - @request + - @dbal.conn.driver + + dbal.extractor.extractors.sqlite_extractor: + class: phpbb\db\extractor\sqlite_extractor + scope: prototype + arguments: + - %core.root_path% + - @request + - @dbal.conn.driver + # ----- Migrator ----- migrator: class: phpbb\db\migrator diff --git a/phpBB/includes/acp/acp_database.php b/phpBB/includes/acp/acp_database.php index 25cddaa5d4..984301a38f 100644 --- a/phpBB/includes/acp/acp_database.php +++ b/phpBB/includes/acp/acp_database.php @@ -90,36 +90,9 @@ class acp_database $time = time(); $filename = 'backup_' . $time . '_' . unique_id(); - switch ($db->get_sql_layer()) - { - case 'mysqli': - case 'mysql4': - case 'mysql': - $extractor = new mysql_extractor($format, $filename, $time, $download, $store); - break; - - case 'sqlite': - $extractor = new sqlite_extractor($format, $filename, $time, $download, $store); - break; - case 'sqlite3': - $extractor = new sqlite3_extractor($format, $filename, $time, $download, $store); - break; - - case 'postgres': - $extractor = new postgres_extractor($format, $filename, $time, $download, $store); - break; - - case 'oracle': - $extractor = new oracle_extractor($format, $filename, $time, $download, $store); - break; - - case 'mssql': - case 'mssql_odbc': - case 'mssqlnative': - $extractor = new mssql_extractor($format, $filename, $time, $download, $store); - break; - } + $extractor = $phpbb_container->get('dbal.extractor'); + $extractor->init_extractor($format, $filename, $time, $download, $store); $extractor->write_start($table_prefix); @@ -461,1637 +434,6 @@ class acp_database } } -class base_extractor -{ - var $fh; - var $fp; - var $write; - var $close; - var $store; - var $download; - var $time; - var $format; - var $run_comp = false; - - function base_extractor($format, $filename, $time, $download = false, $store = false) - { - global $request; - - $this->download = $download; - $this->store = $store; - $this->time = $time; - $this->format = $format; - - switch ($format) - { - case 'text': - $ext = '.sql'; - $open = 'fopen'; - $this->write = 'fwrite'; - $this->close = 'fclose'; - $mimetype = 'text/x-sql'; - break; - case 'bzip2': - $ext = '.sql.bz2'; - $open = 'bzopen'; - $this->write = 'bzwrite'; - $this->close = 'bzclose'; - $mimetype = 'application/x-bzip2'; - break; - case 'gzip': - $ext = '.sql.gz'; - $open = 'gzopen'; - $this->write = 'gzwrite'; - $this->close = 'gzclose'; - $mimetype = 'application/x-gzip'; - break; - } - - if ($download == true) - { - $name = $filename . $ext; - header('Cache-Control: private, no-cache'); - header("Content-Type: $mimetype; name=\"$name\""); - header("Content-disposition: attachment; filename=$name"); - - switch ($format) - { - case 'bzip2': - ob_start(); - break; - - case 'gzip': - if (strpos($request->header('Accept-Encoding'), 'gzip') !== false && strpos(strtolower($request->header('User-Agent')), 'msie') === false) - { - ob_start('ob_gzhandler'); - } - else - { - $this->run_comp = true; - } - break; - } - } - - if ($store == true) - { - global $phpbb_root_path; - $file = $phpbb_root_path . 'store/' . $filename . $ext; - - $this->fp = $open($file, 'w'); - - if (!$this->fp) - { - trigger_error('FILE_WRITE_FAIL', E_USER_ERROR); - } - } - } - - function write_end() - { - static $close; - - if ($this->store) - { - if ($close === null) - { - $close = $this->close; - } - $close($this->fp); - } - - // bzip2 must be written all the way at the end - if ($this->download && $this->format === 'bzip2') - { - $c = ob_get_clean(); - echo bzcompress($c); - } - } - - function flush($data) - { - static $write; - if ($this->store === true) - { - if ($write === null) - { - $write = $this->write; - } - $write($this->fp, $data); - } - - if ($this->download === true) - { - if ($this->format === 'bzip2' || $this->format === 'text' || ($this->format === 'gzip' && !$this->run_comp)) - { - echo $data; - } - - // we can write the gzip data as soon as we get it - if ($this->format === 'gzip') - { - if ($this->run_comp) - { - echo gzencode($data); - } - else - { - ob_flush(); - flush(); - } - } - } - } -} - -class mysql_extractor extends base_extractor -{ - function write_start($table_prefix) - { - $sql_data = "#\n"; - $sql_data .= "# phpBB Backup Script\n"; - $sql_data .= "# Dump of tables for $table_prefix\n"; - $sql_data .= "# DATE : " . gmdate("d-m-Y H:i:s", $this->time) . " GMT\n"; - $sql_data .= "#\n"; - $this->flush($sql_data); - } - - function write_table($table_name) - { - global $db; - static $new_extract; - - if ($new_extract === null) - { - if ($db->get_sql_layer() === 'mysqli' || version_compare($db->sql_server_info(true), '3.23.20', '>=')) - { - $new_extract = true; - } - else - { - $new_extract = false; - } - } - - if ($new_extract) - { - $this->new_write_table($table_name); - } - else - { - $this->old_write_table($table_name); - } - } - - function write_data($table_name) - { - global $db; - if ($db->get_sql_layer() === 'mysqli') - { - $this->write_data_mysqli($table_name); - } - else - { - $this->write_data_mysql($table_name); - } - } - - function write_data_mysqli($table_name) - { - global $db; - $sql = "SELECT * - FROM $table_name"; - $result = mysqli_query($db->get_db_connect_id(), $sql, MYSQLI_USE_RESULT); - if ($result != false) - { - $fields_cnt = mysqli_num_fields($result); - - // Get field information - $field = mysqli_fetch_fields($result); - $field_set = array(); - - for ($j = 0; $j < $fields_cnt; $j++) - { - $field_set[] = $field[$j]->name; - } - - $search = array("\\", "'", "\x00", "\x0a", "\x0d", "\x1a", '"'); - $replace = array("\\\\", "\\'", '\0', '\n', '\r', '\Z', '\\"'); - $fields = implode(', ', $field_set); - $sql_data = 'INSERT INTO ' . $table_name . ' (' . $fields . ') VALUES '; - $first_set = true; - $query_len = 0; - $max_len = get_usable_memory(); - - while ($row = mysqli_fetch_row($result)) - { - $values = array(); - if ($first_set) - { - $query = $sql_data . '('; - } - else - { - $query .= ',('; - } - - for ($j = 0; $j < $fields_cnt; $j++) - { - if (!isset($row[$j]) || is_null($row[$j])) - { - $values[$j] = 'NULL'; - } - else if (($field[$j]->flags & 32768) && !($field[$j]->flags & 1024)) - { - $values[$j] = $row[$j]; - } - else - { - $values[$j] = "'" . str_replace($search, $replace, $row[$j]) . "'"; - } - } - $query .= implode(', ', $values) . ')'; - - $query_len += strlen($query); - if ($query_len > $max_len) - { - $this->flush($query . ";\n\n"); - $query = ''; - $query_len = 0; - $first_set = true; - } - else - { - $first_set = false; - } - } - mysqli_free_result($result); - - // check to make sure we have nothing left to flush - if (!$first_set && $query) - { - $this->flush($query . ";\n\n"); - } - } - } - - function write_data_mysql($table_name) - { - global $db; - $sql = "SELECT * - FROM $table_name"; - $result = mysql_unbuffered_query($sql, $db->get_db_connect_id()); - - if ($result != false) - { - $fields_cnt = mysql_num_fields($result); - - // Get field information - $field = array(); - for ($i = 0; $i < $fields_cnt; $i++) - { - $field[] = mysql_fetch_field($result, $i); - } - $field_set = array(); - - for ($j = 0; $j < $fields_cnt; $j++) - { - $field_set[] = $field[$j]->name; - } - - $search = array("\\", "'", "\x00", "\x0a", "\x0d", "\x1a", '"'); - $replace = array("\\\\", "\\'", '\0', '\n', '\r', '\Z', '\\"'); - $fields = implode(', ', $field_set); - $sql_data = 'INSERT INTO ' . $table_name . ' (' . $fields . ') VALUES '; - $first_set = true; - $query_len = 0; - $max_len = get_usable_memory(); - - while ($row = mysql_fetch_row($result)) - { - $values = array(); - if ($first_set) - { - $query = $sql_data . '('; - } - else - { - $query .= ',('; - } - - for ($j = 0; $j < $fields_cnt; $j++) - { - if (!isset($row[$j]) || is_null($row[$j])) - { - $values[$j] = 'NULL'; - } - else if ($field[$j]->numeric && ($field[$j]->type !== 'timestamp')) - { - $values[$j] = $row[$j]; - } - else - { - $values[$j] = "'" . str_replace($search, $replace, $row[$j]) . "'"; - } - } - $query .= implode(', ', $values) . ')'; - - $query_len += strlen($query); - if ($query_len > $max_len) - { - $this->flush($query . ";\n\n"); - $query = ''; - $query_len = 0; - $first_set = true; - } - else - { - $first_set = false; - } - } - mysql_free_result($result); - - // check to make sure we have nothing left to flush - if (!$first_set && $query) - { - $this->flush($query . ";\n\n"); - } - } - } - - function new_write_table($table_name) - { - global $db; - - $sql = 'SHOW CREATE TABLE ' . $table_name; - $result = $db->sql_query($sql); - $row = $db->sql_fetchrow($result); - - $sql_data = '# Table: ' . $table_name . "\n"; - $sql_data .= "DROP TABLE IF EXISTS $table_name;\n"; - $this->flush($sql_data . $row['Create Table'] . ";\n\n"); - - $db->sql_freeresult($result); - } - - function old_write_table($table_name) - { - global $db; - - $sql_data = '# Table: ' . $table_name . "\n"; - $sql_data .= "DROP TABLE IF EXISTS $table_name;\n"; - $sql_data .= "CREATE TABLE $table_name(\n"; - $rows = array(); - - $sql = "SHOW FIELDS - FROM $table_name"; - $result = $db->sql_query($sql); - - while ($row = $db->sql_fetchrow($result)) - { - $line = ' ' . $row['Field'] . ' ' . $row['Type']; - - if (!is_null($row['Default'])) - { - $line .= " DEFAULT '{$row['Default']}'"; - } - - if ($row['Null'] != 'YES') - { - $line .= ' NOT NULL'; - } - - if ($row['Extra'] != '') - { - $line .= ' ' . $row['Extra']; - } - - $rows[] = $line; - } - $db->sql_freeresult($result); - - $sql = "SHOW KEYS - FROM $table_name"; - - $result = $db->sql_query($sql); - - $index = array(); - while ($row = $db->sql_fetchrow($result)) - { - $kname = $row['Key_name']; - - if ($kname != 'PRIMARY') - { - if ($row['Non_unique'] == 0) - { - $kname = "UNIQUE|$kname"; - } - } - - if ($row['Sub_part']) - { - $row['Column_name'] .= '(' . $row['Sub_part'] . ')'; - } - $index[$kname][] = $row['Column_name']; - } - $db->sql_freeresult($result); - - foreach ($index as $key => $columns) - { - $line = ' '; - - if ($key == 'PRIMARY') - { - $line .= 'PRIMARY KEY (' . implode(', ', $columns) . ')'; - } - else if (strpos($key, 'UNIQUE') === 0) - { - $line .= 'UNIQUE ' . substr($key, 7) . ' (' . implode(', ', $columns) . ')'; - } - else if (strpos($key, 'FULLTEXT') === 0) - { - $line .= 'FULLTEXT ' . substr($key, 9) . ' (' . implode(', ', $columns) . ')'; - } - else - { - $line .= "KEY $key (" . implode(', ', $columns) . ')'; - } - - $rows[] = $line; - } - - $sql_data .= implode(",\n", $rows); - $sql_data .= "\n);\n\n"; - - $this->flush($sql_data); - } -} - -class sqlite_extractor extends base_extractor -{ - function write_start($prefix) - { - $sql_data = "--\n"; - $sql_data .= "-- phpBB Backup Script\n"; - $sql_data .= "-- Dump of tables for $prefix\n"; - $sql_data .= "-- DATE : " . gmdate("d-m-Y H:i:s", $this->time) . " GMT\n"; - $sql_data .= "--\n"; - $sql_data .= "BEGIN TRANSACTION;\n"; - $this->flush($sql_data); - } - - function write_table($table_name) - { - global $db; - $sql_data = '-- Table: ' . $table_name . "\n"; - $sql_data .= "DROP TABLE $table_name;\n"; - - $sql = "SELECT sql - FROM sqlite_master - WHERE type = 'table' - AND name = '" . $db->sql_escape($table_name) . "' - ORDER BY type DESC, name;"; - $result = $db->sql_query($sql); - $row = $db->sql_fetchrow($result); - $db->sql_freeresult($result); - - // Create Table - $sql_data .= $row['sql'] . ";\n"; - - $result = $db->sql_query("PRAGMA index_list('" . $db->sql_escape($table_name) . "');"); - - $ar = array(); - while ($row = $db->sql_fetchrow($result)) - { - $ar[] = $row; - } - $db->sql_freeresult($result); - - foreach ($ar as $value) - { - if (strpos($value['name'], 'autoindex') !== false) - { - continue; - } - - $result = $db->sql_query("PRAGMA index_info('" . $db->sql_escape($value['name']) . "');"); - - $fields = array(); - while ($row = $db->sql_fetchrow($result)) - { - $fields[] = $row['name']; - } - $db->sql_freeresult($result); - - $sql_data .= 'CREATE ' . ($value['unique'] ? 'UNIQUE ' : '') . 'INDEX ' . $value['name'] . ' on ' . $table_name . ' (' . implode(', ', $fields) . ");\n"; - } - - $this->flush($sql_data . "\n"); - } - - function write_data($table_name) - { - global $db; - - $col_types = sqlite_fetch_column_types($db->get_db_connect_id(), $table_name); - - $sql = "SELECT * - FROM $table_name"; - $result = sqlite_unbuffered_query($db->get_db_connect_id(), $sql); - $rows = sqlite_fetch_all($result, SQLITE_ASSOC); - $sql_insert = 'INSERT INTO ' . $table_name . ' (' . implode(', ', array_keys($col_types)) . ') VALUES ('; - foreach ($rows as $row) - { - foreach ($row as $column_name => $column_data) - { - if (is_null($column_data)) - { - $row[$column_name] = 'NULL'; - } - else if ($column_data == '') - { - $row[$column_name] = "''"; - } - else if (strpos($col_types[$column_name], 'text') !== false || strpos($col_types[$column_name], 'char') !== false || strpos($col_types[$column_name], 'blob') !== false) - { - $row[$column_name] = sanitize_data_generic(str_replace("'", "''", $column_data)); - } - } - $this->flush($sql_insert . implode(', ', $row) . ");\n"); - } - } - - function write_end() - { - $this->flush("COMMIT;\n"); - parent::write_end(); - } -} - -class sqlite3_extractor extends base_extractor -{ - function write_start($prefix) - { - $sql_data = "--\n"; - $sql_data .= "-- phpBB Backup Script\n"; - $sql_data .= "-- Dump of tables for $prefix\n"; - $sql_data .= "-- DATE : " . gmdate("d-m-Y H:i:s", $this->time) . " GMT\n"; - $sql_data .= "--\n"; - $sql_data .= "BEGIN TRANSACTION;\n"; - $this->flush($sql_data); - } - - function write_table($table_name) - { - global $db; - $sql_data = '-- Table: ' . $table_name . "\n"; - $sql_data .= "DROP TABLE $table_name;\n"; - - $sql = "SELECT sql - FROM sqlite_master - WHERE type = 'table' - AND name = '" . $db->sql_escape($table_name) . "' - ORDER BY name ASC;"; - $result = $db->sql_query($sql); - $row = $db->sql_fetchrow($result); - $db->sql_freeresult($result); - - // Create Table - $sql_data .= $row['sql'] . ";\n"; - - $result = $db->sql_query("PRAGMA index_list('" . $db->sql_escape($table_name) . "');"); - - while ($row = $db->sql_fetchrow($result)) - { - if (strpos($row['name'], 'autoindex') !== false) - { - continue; - } - - $result2 = $db->sql_query("PRAGMA index_info('" . $db->sql_escape($row['name']) . "');"); - - $fields = array(); - while ($row2 = $db->sql_fetchrow($result2)) - { - $fields[] = $row2['name']; - } - $db->sql_freeresult($result2); - - $sql_data .= 'CREATE ' . ($row['unique'] ? 'UNIQUE ' : '') . 'INDEX ' . $row['name'] . ' ON ' . $table_name . ' (' . implode(', ', $fields) . ");\n"; - } - $db->sql_freeresult($result); - - $this->flush($sql_data . "\n"); - } - - function write_data($table_name) - { - global $db; - - $result = $db->sql_query("PRAGMA table_info('" . $db->sql_escape($table_name) . "');"); - - $col_types = array(); - while ($row = $db->sql_fetchrow($result)) - { - $col_types[$row['name']] = $row['type']; - } - $db->sql_freeresult($result); - - $sql_insert = 'INSERT INTO ' . $table_name . ' (' . implode(', ', array_keys($col_types)) . ') VALUES ('; - - $sql = "SELECT * - FROM $table_name"; - $result = $db->sql_query($sql); - - while ($row = $db->sql_fetchrow($result)) - { - foreach ($row as $column_name => $column_data) - { - if (is_null($column_data)) - { - $row[$column_name] = 'NULL'; - } - else if ($column_data === '') - { - $row[$column_name] = "''"; - } - else if (stripos($col_types[$column_name], 'text') !== false || stripos($col_types[$column_name], 'char') !== false || stripos($col_types[$column_name], 'blob') !== false) - { - $row[$column_name] = sanitize_data_generic(str_replace("'", "''", $column_data)); - } - } - $this->flush($sql_insert . implode(', ', $row) . ");\n"); - } - } - - function write_end() - { - $this->flush("COMMIT;\n"); - parent::write_end(); - } -} - -class postgres_extractor extends base_extractor -{ - function write_start($prefix) - { - $sql_data = "--\n"; - $sql_data .= "-- phpBB Backup Script\n"; - $sql_data .= "-- Dump of tables for $prefix\n"; - $sql_data .= "-- DATE : " . gmdate("d-m-Y H:i:s", $this->time) . " GMT\n"; - $sql_data .= "--\n"; - $sql_data .= "BEGIN TRANSACTION;\n"; - $this->flush($sql_data); - } - - function write_table($table_name) - { - global $db; - static $domains_created = array(); - - $sql = "SELECT a.domain_name, a.data_type, a.character_maximum_length, a.domain_default - FROM INFORMATION_SCHEMA.domains a, INFORMATION_SCHEMA.column_domain_usage b - WHERE a.domain_name = b.domain_name - AND b.table_name = '{$table_name}'"; - $result = $db->sql_query($sql); - while ($row = $db->sql_fetchrow($result)) - { - if (empty($domains_created[$row['domain_name']])) - { - $domains_created[$row['domain_name']] = true; - //$sql_data = "DROP DOMAIN {$row['domain_name']};\n"; - $sql_data = "CREATE DOMAIN {$row['domain_name']} as {$row['data_type']}"; - if (!empty($row['character_maximum_length'])) - { - $sql_data .= '(' . $row['character_maximum_length'] . ')'; - } - $sql_data .= ' NOT NULL'; - if (!empty($row['domain_default'])) - { - $sql_data .= ' DEFAULT ' . $row['domain_default']; - } - $this->flush($sql_data . ";\n"); - } - } - - $sql_data = '-- Table: ' . $table_name . "\n"; - $sql_data .= "DROP TABLE $table_name;\n"; - // PGSQL does not "tightly" bind sequences and tables, we must guess... - $sql = "SELECT relname - FROM pg_class - WHERE relkind = 'S' - AND relname = '{$table_name}_seq'"; - $result = $db->sql_query($sql); - // We don't even care about storing the results. We already know the answer if we get rows back. - if ($db->sql_fetchrow($result)) - { - $sql_data .= "DROP SEQUENCE {$table_name}_seq;\n"; - $sql_data .= "CREATE SEQUENCE {$table_name}_seq;\n"; - } - $db->sql_freeresult($result); - - $field_query = "SELECT a.attnum, a.attname as field, t.typname as type, a.attlen as length, a.atttypmod as lengthvar, a.attnotnull as notnull - FROM pg_class c, pg_attribute a, pg_type t - WHERE c.relname = '" . $db->sql_escape($table_name) . "' - AND a.attnum > 0 - AND a.attrelid = c.oid - AND a.atttypid = t.oid - ORDER BY a.attnum"; - $result = $db->sql_query($field_query); - - $sql_data .= "CREATE TABLE $table_name(\n"; - $lines = array(); - while ($row = $db->sql_fetchrow($result)) - { - // Get the data from the table - $sql_get_default = "SELECT pg_get_expr(d.adbin, d.adrelid) as rowdefault - FROM pg_attrdef d, pg_class c - WHERE (c.relname = '" . $db->sql_escape($table_name) . "') - AND (c.oid = d.adrelid) - AND d.adnum = " . $row['attnum']; - $def_res = $db->sql_query($sql_get_default); - $def_row = $db->sql_fetchrow($def_res); - $db->sql_freeresult($def_res); - - if (empty($def_row)) - { - unset($row['rowdefault']); - } - else - { - $row['rowdefault'] = $def_row['rowdefault']; - } - - if ($row['type'] == 'bpchar') - { - // Internally stored as bpchar, but isn't accepted in a CREATE TABLE statement. - $row['type'] = 'char'; - } - - $line = ' ' . $row['field'] . ' ' . $row['type']; - - if (strpos($row['type'], 'char') !== false) - { - if ($row['lengthvar'] > 0) - { - $line .= '(' . ($row['lengthvar'] - 4) . ')'; - } - } - - if (strpos($row['type'], 'numeric') !== false) - { - $line .= '('; - $line .= sprintf("%s,%s", (($row['lengthvar'] >> 16) & 0xffff), (($row['lengthvar'] - 4) & 0xffff)); - $line .= ')'; - } - - if (isset($row['rowdefault'])) - { - $line .= ' DEFAULT ' . $row['rowdefault']; - } - - if ($row['notnull'] == 't') - { - $line .= ' NOT NULL'; - } - - $lines[] = $line; - } - $db->sql_freeresult($result); - - // Get the listing of primary keys. - $sql_pri_keys = "SELECT ic.relname as index_name, bc.relname as tab_name, ta.attname as column_name, i.indisunique as unique_key, i.indisprimary as primary_key - FROM pg_class bc, pg_class ic, pg_index i, pg_attribute ta, pg_attribute ia - WHERE (bc.oid = i.indrelid) - AND (ic.oid = i.indexrelid) - AND (ia.attrelid = i.indexrelid) - AND (ta.attrelid = bc.oid) - AND (bc.relname = '" . $db->sql_escape($table_name) . "') - AND (ta.attrelid = i.indrelid) - AND (ta.attnum = i.indkey[ia.attnum-1]) - ORDER BY index_name, tab_name, column_name"; - - $result = $db->sql_query($sql_pri_keys); - - $index_create = $index_rows = $primary_key = array(); - - // We do this in two steps. It makes placing the comma easier - while ($row = $db->sql_fetchrow($result)) - { - if ($row['primary_key'] == 't') - { - $primary_key[] = $row['column_name']; - $primary_key_name = $row['index_name']; - } - else - { - // We have to store this all this info because it is possible to have a multi-column key... - // we can loop through it again and build the statement - $index_rows[$row['index_name']]['table'] = $table_name; - $index_rows[$row['index_name']]['unique'] = ($row['unique_key'] == 't') ? true : false; - $index_rows[$row['index_name']]['column_names'][] = $row['column_name']; - } - } - $db->sql_freeresult($result); - - if (!empty($index_rows)) - { - foreach ($index_rows as $idx_name => $props) - { - $index_create[] = 'CREATE ' . ($props['unique'] ? 'UNIQUE ' : '') . "INDEX $idx_name ON $table_name (" . implode(', ', $props['column_names']) . ");"; - } - } - - if (!empty($primary_key)) - { - $lines[] = " CONSTRAINT $primary_key_name PRIMARY KEY (" . implode(', ', $primary_key) . ")"; - } - - // Generate constraint clauses for CHECK constraints - $sql_checks = "SELECT conname as index_name, consrc - FROM pg_constraint, pg_class bc - WHERE conrelid = bc.oid - AND bc.relname = '" . $db->sql_escape($table_name) . "' - AND NOT EXISTS ( - SELECT * - FROM pg_constraint as c, pg_inherits as i - WHERE i.inhrelid = pg_constraint.conrelid - AND c.conname = pg_constraint.conname - AND c.consrc = pg_constraint.consrc - AND c.conrelid = i.inhparent - )"; - $result = $db->sql_query($sql_checks); - - // Add the constraints to the sql file. - while ($row = $db->sql_fetchrow($result)) - { - if (!is_null($row['consrc'])) - { - $lines[] = ' CONSTRAINT ' . $row['index_name'] . ' CHECK ' . $row['consrc']; - } - } - $db->sql_freeresult($result); - - $sql_data .= implode(", \n", $lines); - $sql_data .= "\n);\n"; - - if (!empty($index_create)) - { - $sql_data .= implode("\n", $index_create) . "\n\n"; - } - $this->flush($sql_data); - } - - function write_data($table_name) - { - global $db; - // Grab all of the data from current table. - $sql = "SELECT * - FROM $table_name"; - $result = $db->sql_query($sql); - - $i_num_fields = pg_num_fields($result); - $seq = ''; - - for ($i = 0; $i < $i_num_fields; $i++) - { - $ary_type[] = pg_field_type($result, $i); - $ary_name[] = pg_field_name($result, $i); - - $sql = "SELECT pg_get_expr(d.adbin, d.adrelid) as rowdefault - FROM pg_attrdef d, pg_class c - WHERE (c.relname = '{$table_name}') - AND (c.oid = d.adrelid) - AND d.adnum = " . strval($i + 1); - $result2 = $db->sql_query($sql); - if ($row = $db->sql_fetchrow($result2)) - { - // Determine if we must reset the sequences - if (strpos($row['rowdefault'], "nextval('") === 0) - { - $seq .= "SELECT SETVAL('{$table_name}_seq',(select case when max({$ary_name[$i]})>0 then max({$ary_name[$i]})+1 else 1 end FROM {$table_name}));\n"; - } - } - } - - $this->flush("COPY $table_name (" . implode(', ', $ary_name) . ') FROM stdin;' . "\n"); - while ($row = $db->sql_fetchrow($result)) - { - $schema_vals = array(); - - // Build the SQL statement to recreate the data. - for ($i = 0; $i < $i_num_fields; $i++) - { - $str_val = $row[$ary_name[$i]]; - - if (preg_match('#char|text|bool|bytea#i', $ary_type[$i])) - { - $str_val = str_replace(array("\n", "\t", "\r", "\b", "\f", "\v"), array('\n', '\t', '\r', '\b', '\f', '\v'), addslashes($str_val)); - $str_empty = ''; - } - else - { - $str_empty = '\N'; - } - - if (empty($str_val) && $str_val !== '0') - { - $str_val = $str_empty; - } - - $schema_vals[] = $str_val; - } - - // Take the ordered fields and their associated data and build it - // into a valid sql statement to recreate that field in the data. - $this->flush(implode("\t", $schema_vals) . "\n"); - } - $db->sql_freeresult($result); - $this->flush("\\.\n"); - - // Write out the sequence statements - $this->flush($seq); - } - - function write_end() - { - $this->flush("COMMIT;\n"); - parent::write_end(); - } -} - -class mssql_extractor extends base_extractor -{ - function write_end() - { - $this->flush("COMMIT\nGO\n"); - parent::write_end(); - } - - function write_start($prefix) - { - $sql_data = "--\n"; - $sql_data .= "-- phpBB Backup Script\n"; - $sql_data .= "-- Dump of tables for $prefix\n"; - $sql_data .= "-- DATE : " . gmdate("d-m-Y H:i:s", $this->time) . " GMT\n"; - $sql_data .= "--\n"; - $sql_data .= "BEGIN TRANSACTION\n"; - $sql_data .= "GO\n"; - $this->flush($sql_data); - } - - function write_table($table_name) - { - global $db; - $sql_data = '-- Table: ' . $table_name . "\n"; - $sql_data .= "IF OBJECT_ID(N'$table_name', N'U') IS NOT NULL\n"; - $sql_data .= "DROP TABLE $table_name;\n"; - $sql_data .= "GO\n"; - $sql_data .= "\nCREATE TABLE [$table_name] (\n"; - $rows = array(); - - $text_flag = false; - - $sql = "SELECT COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') as IS_IDENTITY - FROM INFORMATION_SCHEMA.COLUMNS - WHERE TABLE_NAME = '$table_name'"; - $result = $db->sql_query($sql); - - while ($row = $db->sql_fetchrow($result)) - { - $line = "\t[{$row['COLUMN_NAME']}] [{$row['DATA_TYPE']}]"; - - if ($row['DATA_TYPE'] == 'text') - { - $text_flag = true; - } - - if ($row['IS_IDENTITY']) - { - $line .= ' IDENTITY (1 , 1)'; - } - - if ($row['CHARACTER_MAXIMUM_LENGTH'] && $row['DATA_TYPE'] !== 'text') - { - $line .= ' (' . $row['CHARACTER_MAXIMUM_LENGTH'] . ')'; - } - - if ($row['IS_NULLABLE'] == 'YES') - { - $line .= ' NULL'; - } - else - { - $line .= ' NOT NULL'; - } - - if ($row['COLUMN_DEFAULT']) - { - $line .= ' DEFAULT ' . $row['COLUMN_DEFAULT']; - } - - $rows[] = $line; - } - $db->sql_freeresult($result); - - $sql_data .= implode(",\n", $rows); - $sql_data .= "\n) ON [PRIMARY]"; - - if ($text_flag) - { - $sql_data .= " TEXTIMAGE_ON [PRIMARY]"; - } - - $sql_data .= "\nGO\n\n"; - $rows = array(); - - $sql = "SELECT CONSTRAINT_NAME, COLUMN_NAME - FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE - WHERE TABLE_NAME = '$table_name'"; - $result = $db->sql_query($sql); - while ($row = $db->sql_fetchrow($result)) - { - if (!sizeof($rows)) - { - $sql_data .= "ALTER TABLE [$table_name] WITH NOCHECK ADD\n"; - $sql_data .= "\tCONSTRAINT [{$row['CONSTRAINT_NAME']}] PRIMARY KEY CLUSTERED \n\t(\n"; - } - $rows[] = "\t\t[{$row['COLUMN_NAME']}]"; - } - if (sizeof($rows)) - { - $sql_data .= implode(",\n", $rows); - $sql_data .= "\n\t) ON [PRIMARY] \nGO\n"; - } - $db->sql_freeresult($result); - - $index = array(); - $sql = "EXEC sp_statistics '$table_name'"; - $result = $db->sql_query($sql); - while ($row = $db->sql_fetchrow($result)) - { - if ($row['TYPE'] == 3) - { - $index[$row['INDEX_NAME']][] = '[' . $row['COLUMN_NAME'] . ']'; - } - } - $db->sql_freeresult($result); - - foreach ($index as $index_name => $column_name) - { - $index[$index_name] = implode(', ', $column_name); - } - - foreach ($index as $index_name => $columns) - { - $sql_data .= "\nCREATE INDEX [$index_name] ON [$table_name]($columns) ON [PRIMARY]\nGO\n"; - } - $this->flush($sql_data); - } - - function write_data($table_name) - { - global $db; - - if ($db->get_sql_layer() === 'mssql') - { - $this->write_data_mssql($table_name); - } - else if($db->get_sql_layer() === 'mssqlnative') - { - $this->write_data_mssqlnative($table_name); - } - else - { - $this->write_data_odbc($table_name); - } - } - - function write_data_mssql($table_name) - { - global $db; - $ary_type = $ary_name = array(); - $ident_set = false; - $sql_data = ''; - - // Grab all of the data from current table. - $sql = "SELECT * - FROM $table_name"; - $result = $db->sql_query($sql); - - $retrieved_data = mssql_num_rows($result); - - $i_num_fields = mssql_num_fields($result); - - for ($i = 0; $i < $i_num_fields; $i++) - { - $ary_type[$i] = mssql_field_type($result, $i); - $ary_name[$i] = mssql_field_name($result, $i); - } - - if ($retrieved_data) - { - $sql = "SELECT 1 as has_identity - FROM INFORMATION_SCHEMA.COLUMNS - WHERE COLUMNPROPERTY(object_id('$table_name'), COLUMN_NAME, 'IsIdentity') = 1"; - $result2 = $db->sql_query($sql); - $row2 = $db->sql_fetchrow($result2); - if (!empty($row2['has_identity'])) - { - $sql_data .= "\nSET IDENTITY_INSERT $table_name ON\nGO\n"; - $ident_set = true; - } - $db->sql_freeresult($result2); - } - - while ($row = $db->sql_fetchrow($result)) - { - $schema_vals = $schema_fields = array(); - - // Build the SQL statement to recreate the data. - for ($i = 0; $i < $i_num_fields; $i++) - { - $str_val = $row[$ary_name[$i]]; - - if (preg_match('#char|text|bool|varbinary#i', $ary_type[$i])) - { - $str_quote = ''; - $str_empty = "''"; - $str_val = sanitize_data_mssql(str_replace("'", "''", $str_val)); - } - else if (preg_match('#date|timestamp#i', $ary_type[$i])) - { - if (empty($str_val)) - { - $str_quote = ''; - } - else - { - $str_quote = "'"; - } - } - else - { - $str_quote = ''; - $str_empty = 'NULL'; - } - - if (empty($str_val) && $str_val !== '0' && !(is_int($str_val) || is_float($str_val))) - { - $str_val = $str_empty; - } - - $schema_vals[$i] = $str_quote . $str_val . $str_quote; - $schema_fields[$i] = $ary_name[$i]; - } - - // Take the ordered fields and their associated data and build it - // into a valid sql statement to recreate that field in the data. - $sql_data .= "INSERT INTO $table_name (" . implode(', ', $schema_fields) . ') VALUES (' . implode(', ', $schema_vals) . ");\nGO\n"; - - $this->flush($sql_data); - $sql_data = ''; - } - $db->sql_freeresult($result); - - if ($retrieved_data && $ident_set) - { - $sql_data .= "\nSET IDENTITY_INSERT $table_name OFF\nGO\n"; - } - $this->flush($sql_data); - } - - function write_data_mssqlnative($table_name) - { - global $db; - $ary_type = $ary_name = array(); - $ident_set = false; - $sql_data = ''; - - // Grab all of the data from current table. - $sql = "SELECT * FROM $table_name"; - $db->mssqlnative_set_query_options(array('Scrollable' => SQLSRV_CURSOR_STATIC)); - $result = $db->sql_query($sql); - - $retrieved_data = $db->mssqlnative_num_rows($result); - - if (!$retrieved_data) - { - $db->sql_freeresult($result); - return; - } - - $sql = "SELECT COLUMN_NAME, DATA_TYPE - FROM INFORMATION_SCHEMA.COLUMNS - WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = '" . $db->sql_escape($table_name) . "'"; - $result_fields = $db->sql_query($sql); - - $i_num_fields = 0; - while ($row = $db->sql_fetchrow($result_fields)) - { - $ary_type[$i_num_fields] = $row['DATA_TYPE']; - $ary_name[$i_num_fields] = $row['COLUMN_NAME']; - $i_num_fields++; - } - $db->sql_freeresult($result_fields); - - $sql = "SELECT 1 as has_identity - FROM INFORMATION_SCHEMA.COLUMNS - WHERE COLUMNPROPERTY(object_id('$table_name'), COLUMN_NAME, 'IsIdentity') = 1"; - $result2 = $db->sql_query($sql); - $row2 = $db->sql_fetchrow($result2); - - if (!empty($row2['has_identity'])) - { - $sql_data .= "\nSET IDENTITY_INSERT $table_name ON\nGO\n"; - $ident_set = true; - } - $db->sql_freeresult($result2); - - while ($row = $db->sql_fetchrow($result)) - { - $schema_vals = $schema_fields = array(); - - // Build the SQL statement to recreate the data. - for ($i = 0; $i < $i_num_fields; $i++) - { - $str_val = $row[$ary_name[$i]]; - - // defaults to type number - better quote just to be safe, so check for is_int too - if (is_int($ary_type[$i]) || preg_match('#char|text|bool|varbinary#i', $ary_type[$i])) - { - $str_quote = ''; - $str_empty = "''"; - $str_val = sanitize_data_mssql(str_replace("'", "''", $str_val)); - } - else if (preg_match('#date|timestamp#i', $ary_type[$i])) - { - if (empty($str_val)) - { - $str_quote = ''; - } - else - { - $str_quote = "'"; - } - } - else - { - $str_quote = ''; - $str_empty = 'NULL'; - } - - if (empty($str_val) && $str_val !== '0' && !(is_int($str_val) || is_float($str_val))) - { - $str_val = $str_empty; - } - - $schema_vals[$i] = $str_quote . $str_val . $str_quote; - $schema_fields[$i] = $ary_name[$i]; - } - - // Take the ordered fields and their associated data and build it - // into a valid sql statement to recreate that field in the data. - $sql_data .= "INSERT INTO $table_name (" . implode(', ', $schema_fields) . ') VALUES (' . implode(', ', $schema_vals) . ");\nGO\n"; - - $this->flush($sql_data); - $sql_data = ''; - } - $db->sql_freeresult($result); - - if ($ident_set) - { - $sql_data .= "\nSET IDENTITY_INSERT $table_name OFF\nGO\n"; - } - $this->flush($sql_data); - } - - function write_data_odbc($table_name) - { - global $db; - $ary_type = $ary_name = array(); - $ident_set = false; - $sql_data = ''; - - // Grab all of the data from current table. - $sql = "SELECT * - FROM $table_name"; - $result = $db->sql_query($sql); - - $retrieved_data = odbc_num_rows($result); - - if ($retrieved_data) - { - $sql = "SELECT 1 as has_identity - FROM INFORMATION_SCHEMA.COLUMNS - WHERE COLUMNPROPERTY(object_id('$table_name'), COLUMN_NAME, 'IsIdentity') = 1"; - $result2 = $db->sql_query($sql); - $row2 = $db->sql_fetchrow($result2); - if (!empty($row2['has_identity'])) - { - $sql_data .= "\nSET IDENTITY_INSERT $table_name ON\nGO\n"; - $ident_set = true; - } - $db->sql_freeresult($result2); - } - - $i_num_fields = odbc_num_fields($result); - - for ($i = 0; $i < $i_num_fields; $i++) - { - $ary_type[$i] = odbc_field_type($result, $i + 1); - $ary_name[$i] = odbc_field_name($result, $i + 1); - } - - while ($row = $db->sql_fetchrow($result)) - { - $schema_vals = $schema_fields = array(); - - // Build the SQL statement to recreate the data. - for ($i = 0; $i < $i_num_fields; $i++) - { - $str_val = $row[$ary_name[$i]]; - - if (preg_match('#char|text|bool|varbinary#i', $ary_type[$i])) - { - $str_quote = ''; - $str_empty = "''"; - $str_val = sanitize_data_mssql(str_replace("'", "''", $str_val)); - } - else if (preg_match('#date|timestamp#i', $ary_type[$i])) - { - if (empty($str_val)) - { - $str_quote = ''; - } - else - { - $str_quote = "'"; - } - } - else - { - $str_quote = ''; - $str_empty = 'NULL'; - } - - if (empty($str_val) && $str_val !== '0' && !(is_int($str_val) || is_float($str_val))) - { - $str_val = $str_empty; - } - - $schema_vals[$i] = $str_quote . $str_val . $str_quote; - $schema_fields[$i] = $ary_name[$i]; - } - - // Take the ordered fields and their associated data and build it - // into a valid sql statement to recreate that field in the data. - $sql_data .= "INSERT INTO $table_name (" . implode(', ', $schema_fields) . ') VALUES (' . implode(', ', $schema_vals) . ");\nGO\n"; - - $this->flush($sql_data); - - $sql_data = ''; - - } - $db->sql_freeresult($result); - - if ($retrieved_data && $ident_set) - { - $sql_data .= "\nSET IDENTITY_INSERT $table_name OFF\nGO\n"; - } - $this->flush($sql_data); - } - -} - -class oracle_extractor extends base_extractor -{ - function write_table($table_name) - { - global $db, $request; - - $sql_data = '-- Table: ' . $table_name . "\n"; - $sql_data .= "DROP TABLE $table_name\n/\n"; - $sql_data .= "\nCREATE TABLE $table_name (\n"; - - $sql = "SELECT COLUMN_NAME, DATA_TYPE, DATA_PRECISION, DATA_LENGTH, NULLABLE, DATA_DEFAULT - FROM ALL_TAB_COLS - WHERE table_name = '{$table_name}'"; - $result = $db->sql_query($sql); - - $rows = array(); - while ($row = $db->sql_fetchrow($result)) - { - $line = ' "' . $row['column_name'] . '" ' . $row['data_type']; - - if ($row['data_type'] !== 'CLOB') - { - if ($row['data_type'] !== 'VARCHAR2' && $row['data_type'] !== 'CHAR') - { - $line .= '(' . $row['data_precision'] . ')'; - } - else - { - $line .= '(' . $row['data_length'] . ')'; - } - } - - if (!empty($row['data_default'])) - { - $line .= ' DEFAULT ' . $row['data_default']; - } - - if ($row['nullable'] == 'N') - { - $line .= ' NOT NULL'; - } - $rows[] = $line; - } - $db->sql_freeresult($result); - - $sql = "SELECT A.CONSTRAINT_NAME, A.COLUMN_NAME - FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B - WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME - AND B.CONSTRAINT_TYPE = 'P' - AND A.TABLE_NAME = '{$table_name}'"; - $result = $db->sql_query($sql); - - $primary_key = array(); - $contraint_name = ''; - while ($row = $db->sql_fetchrow($result)) - { - $constraint_name = '"' . $row['constraint_name'] . '"'; - $primary_key[] = '"' . $row['column_name'] . '"'; - } - $db->sql_freeresult($result); - - if (sizeof($primary_key)) - { - $rows[] = " CONSTRAINT {$constraint_name} PRIMARY KEY (" . implode(', ', $primary_key) . ')'; - } - - $sql = "SELECT A.CONSTRAINT_NAME, A.COLUMN_NAME - FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B - WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME - AND B.CONSTRAINT_TYPE = 'U' - AND A.TABLE_NAME = '{$table_name}'"; - $result = $db->sql_query($sql); - - $unique = array(); - $contraint_name = ''; - while ($row = $db->sql_fetchrow($result)) - { - $constraint_name = '"' . $row['constraint_name'] . '"'; - $unique[] = '"' . $row['column_name'] . '"'; - } - $db->sql_freeresult($result); - - if (sizeof($unique)) - { - $rows[] = " CONSTRAINT {$constraint_name} UNIQUE (" . implode(', ', $unique) . ')'; - } - - $sql_data .= implode(",\n", $rows); - $sql_data .= "\n)\n/\n"; - - $sql = "SELECT A.REFERENCED_NAME, C.* - FROM USER_DEPENDENCIES A, USER_TRIGGERS B, USER_SEQUENCES C - WHERE A.REFERENCED_TYPE = 'SEQUENCE' - AND A.NAME = B.TRIGGER_NAME - AND B.TABLE_NAME = '{$table_name}' - AND C.SEQUENCE_NAME = A.REFERENCED_NAME"; - $result = $db->sql_query($sql); - - $type = $request->variable('type', ''); - - while ($row = $db->sql_fetchrow($result)) - { - $sql_data .= "\nDROP SEQUENCE \"{$row['referenced_name']}\"\n/\n"; - $sql_data .= "\nCREATE SEQUENCE \"{$row['referenced_name']}\""; - - if ($type == 'full') - { - $sql_data .= ' START WITH ' . $row['last_number']; - } - - $sql_data .= "\n/\n"; - } - $db->sql_freeresult($result); - - $sql = "SELECT DESCRIPTION, WHEN_CLAUSE, TRIGGER_BODY - FROM USER_TRIGGERS - WHERE TABLE_NAME = '{$table_name}'"; - $result = $db->sql_query($sql); - while ($row = $db->sql_fetchrow($result)) - { - $sql_data .= "\nCREATE OR REPLACE TRIGGER {$row['description']}WHEN ({$row['when_clause']})\n{$row['trigger_body']}\n/\n"; - } - $db->sql_freeresult($result); - - $sql = "SELECT A.INDEX_NAME, B.COLUMN_NAME - FROM USER_INDEXES A, USER_IND_COLUMNS B - WHERE A.UNIQUENESS = 'NONUNIQUE' - AND A.INDEX_NAME = B.INDEX_NAME - AND B.TABLE_NAME = '{$table_name}'"; - $result = $db->sql_query($sql); - - $index = array(); - - while ($row = $db->sql_fetchrow($result)) - { - $index[$row['index_name']][] = $row['column_name']; - } - - foreach ($index as $index_name => $column_names) - { - $sql_data .= "\nCREATE INDEX $index_name ON $table_name(" . implode(', ', $column_names) . ")\n/\n"; - } - $db->sql_freeresult($result); - $this->flush($sql_data); - } - - function write_data($table_name) - { - global $db; - $ary_type = $ary_name = array(); - - // Grab all of the data from current table. - $sql = "SELECT * - FROM $table_name"; - $result = $db->sql_query($sql); - - $i_num_fields = ocinumcols($result); - - for ($i = 0; $i < $i_num_fields; $i++) - { - $ary_type[$i] = ocicolumntype($result, $i + 1); - $ary_name[$i] = ocicolumnname($result, $i + 1); - } - - $sql_data = ''; - - while ($row = $db->sql_fetchrow($result)) - { - $schema_vals = $schema_fields = array(); - - // Build the SQL statement to recreate the data. - for ($i = 0; $i < $i_num_fields; $i++) - { - // Oracle uses uppercase - we use lowercase - $str_val = $row[strtolower($ary_name[$i])]; - - if (preg_match('#char|text|bool|raw|clob#i', $ary_type[$i])) - { - $str_quote = ''; - $str_empty = "''"; - $str_val = sanitize_data_oracle($str_val); - } - else if (preg_match('#date|timestamp#i', $ary_type[$i])) - { - if (empty($str_val)) - { - $str_quote = ''; - } - else - { - $str_quote = "'"; - } - } - else - { - $str_quote = ''; - $str_empty = 'NULL'; - } - - if (empty($str_val) && $str_val !== '0') - { - $str_val = $str_empty; - } - - $schema_vals[$i] = $str_quote . $str_val . $str_quote; - $schema_fields[$i] = '"' . $ary_name[$i] . '"'; - } - - // Take the ordered fields and their associated data and build it - // into a valid sql statement to recreate that field in the data. - $sql_data = "INSERT INTO $table_name (" . implode(', ', $schema_fields) . ') VALUES (' . implode(', ', $schema_vals) . ")\n/\n"; - - $this->flush($sql_data); - } - $db->sql_freeresult($result); - } - - function write_start($prefix) - { - $sql_data = "--\n"; - $sql_data .= "-- phpBB Backup Script\n"; - $sql_data .= "-- Dump of tables for $prefix\n"; - $sql_data .= "-- DATE : " . gmdate("d-m-Y H:i:s", $this->time) . " GMT\n"; - $sql_data .= "--\n"; - $this->flush($sql_data); - } -} - // get how much space we allow for a chunk of data, very similar to phpMyAdmin's way of doing things ;-) (hey, we only do this for MySQL anyway :P) function get_usable_memory() { diff --git a/phpBB/phpbb/db/extractor/base_extractor.php b/phpBB/phpbb/db/extractor/base_extractor.php new file mode 100644 index 0000000000..547c85f066 --- /dev/null +++ b/phpBB/phpbb/db/extractor/base_extractor.php @@ -0,0 +1,252 @@ +<?php +/** +* +* This file is part of the phpBB Forum Software package. +* +* @copyright (c) phpBB Limited <https://www.phpbb.com> +* @license GNU General Public License, version 2 (GPL-2.0) +* +* For full copyright and license information, please see +* the docs/CREDITS.txt file. +* +*/ + +namespace phpbb\db\extractor; + +use phpbb\db\extractor\exception\invalid_format_exception; +use phpbb\db\extractor\exception\extractor_not_initialized_exception; + +/** + * Abstract base class for database extraction + */ +abstract class base_extractor implements extractor_interface +{ + /** + * @var string phpBB root path + */ + protected $phpbb_root_path; + + /** + * @var \phpbb\request\request_interface + */ + protected $request; + + /** + * @var \phpbb\db\driver\driver_interface + */ + protected $db; + + /** + * @var bool + */ + protected $download; + + /** + * @var bool + */ + protected $store; + + /** + * @var int + */ + protected $time; + + /** + * @var string + */ + protected $format; + + /** + * @var resource + */ + protected $fp; + + /** + * @var string + */ + protected $write; + + /** + * @var string + */ + protected $close; + + /** + * @var bool + */ + protected $run_comp; + + /** + * @var bool + */ + protected $is_initialized; + + /** + * Constructor + * + * @param string $phpbb_root_path + * @param \phpbb\request\request_interface $request + * @param \phpbb\db\driver\driver_interface $db + */ + public function __construct($phpbb_root_path, \phpbb\request\request_interface $request, \phpbb\db\driver\driver_interface $db) + { + $this->phpbb_root_path = $phpbb_root_path; + $this->request = $request; + $this->db = $db; + $this->fp = null; + + $this->is_initialized = false; + } + + /** + * {@inheritdoc} + */ + public function init_extractor($format, $filename, $time, $download = false, $store = false) + { + $this->download = $download; + $this->store = $store; + $this->time = $time; + $this->format = $format; + + switch ($format) + { + case 'text': + $ext = '.sql'; + $open = 'fopen'; + $this->write = 'fwrite'; + $this->close = 'fclose'; + $mimetype = 'text/x-sql'; + break; + case 'bzip2': + $ext = '.sql.bz2'; + $open = 'bzopen'; + $this->write = 'bzwrite'; + $this->close = 'bzclose'; + $mimetype = 'application/x-bzip2'; + break; + case 'gzip': + $ext = '.sql.gz'; + $open = 'gzopen'; + $this->write = 'gzwrite'; + $this->close = 'gzclose'; + $mimetype = 'application/x-gzip'; + break; + default: + throw new invalid_format_exception(); + break; + } + + if ($download === true) + { + $name = $filename . $ext; + header('Cache-Control: private, no-cache'); + header("Content-Type: $mimetype; name=\"$name\""); + header("Content-disposition: attachment; filename=$name"); + + switch ($format) + { + case 'bzip2': + ob_start(); + break; + + case 'gzip': + if (strpos($this->request->header('Accept-Encoding'), 'gzip') !== false && strpos(strtolower($this->request->header('User-Agent')), 'msie') === false) + { + ob_start('ob_gzhandler'); + } + else + { + $this->run_comp = true; + } + break; + } + } + + if ($store === true) + { + $file = $this->phpbb_root_path . 'store/' . $filename . $ext; + + $this->fp = $open($file, 'w'); + + if (!$this->fp) + { + trigger_error('FILE_WRITE_FAIL', E_USER_ERROR); + } + } + + $this->is_initialized = true; + } + + /** + * {@inheritdoc} + */ + public function write_end() + { + static $close; + + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + if ($this->store) + { + if ($close === null) + { + $close = $this->close; + } + $close($this->fp); + } + + // bzip2 must be written all the way at the end + if ($this->download && $this->format === 'bzip2') + { + $c = ob_get_clean(); + echo bzcompress($c); + } + } + + /** + * {@inheritdoc} + */ + public function flush($data) + { + static $write; + + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + if ($this->store === true) + { + if ($write === null) + { + $write = $this->write; + } + $write($this->fp, $data); + } + + if ($this->download === true) + { + if ($this->format === 'bzip2' || $this->format === 'text' || ($this->format === 'gzip' && !$this->run_comp)) + { + echo $data; + } + + // we can write the gzip data as soon as we get it + if ($this->format === 'gzip') + { + if ($this->run_comp) + { + echo gzencode($data); + } + else + { + ob_flush(); + flush(); + } + } + } + } +} diff --git a/phpBB/phpbb/db/extractor/exception/extractor_not_initialized_exception.php b/phpBB/phpbb/db/extractor/exception/extractor_not_initialized_exception.php new file mode 100644 index 0000000000..62eb434be1 --- /dev/null +++ b/phpBB/phpbb/db/extractor/exception/extractor_not_initialized_exception.php @@ -0,0 +1,24 @@ +<?php +/** +* +* This file is part of the phpBB Forum Software package. +* +* @copyright (c) phpBB Limited <https://www.phpbb.com> +* @license GNU General Public License, version 2 (GPL-2.0) +* +* For full copyright and license information, please see +* the docs/CREDITS.txt file. +* +*/ + +namespace phpbb\db\extractor\exception; + +use phpbb\exception\runtime_exception; + +/** +* This exception is thrown when invalid format is given to the extractor +*/ +class extractor_not_initialized_exception extends runtime_exception +{ + +} diff --git a/phpBB/phpbb/db/extractor/exception/invalid_format_exception.php b/phpBB/phpbb/db/extractor/exception/invalid_format_exception.php new file mode 100644 index 0000000000..6be24cb5dc --- /dev/null +++ b/phpBB/phpbb/db/extractor/exception/invalid_format_exception.php @@ -0,0 +1,22 @@ +<?php +/** +* +* This file is part of the phpBB Forum Software package. +* +* @copyright (c) phpBB Limited <https://www.phpbb.com> +* @license GNU General Public License, version 2 (GPL-2.0) +* +* For full copyright and license information, please see +* the docs/CREDITS.txt file. +* +*/ + +namespace phpbb\db\extractor\exception; + +/** +* This exception is thrown when invalid format is given to the extractor +*/ +class invalid_format_exception extends \InvalidArgumentException +{ + +} diff --git a/phpBB/phpbb/db/extractor/extractor_interface.php b/phpBB/phpbb/db/extractor/extractor_interface.php new file mode 100644 index 0000000000..ff45df9bb7 --- /dev/null +++ b/phpBB/phpbb/db/extractor/extractor_interface.php @@ -0,0 +1,80 @@ +<?php +/** +* +* This file is part of the phpBB Forum Software package. +* +* @copyright (c) phpBB Limited <https://www.phpbb.com> +* @license GNU General Public License, version 2 (GPL-2.0) +* +* For full copyright and license information, please see +* the docs/CREDITS.txt file. +* +*/ + +namespace phpbb\db\extractor; + +/** +* Database extractor interface +*/ +interface extractor_interface +{ + /** + * Start the extraction of the database + * + * This function initialize the database extraction. It is required to call this + * function before calling any other extractor functions. + * + * @param string $format + * @param string $filename + * @param int $time + * @param bool $download + * @param bool $store + * @return null + * @throws \phpbb\db\extractor\exception\invalid_format_exception when $format is invalid + */ + public function init_extractor($format, $filename, $time, $download = false, $store = false); + + /** + * Writes header comments to the database backup + * + * @param string $table_prefix prefix of phpBB database tables + * @return null + * @throws \phpbb\db\extractor\exception\extractor_not_initialized_exception when calling this function before init_extractor() + */ + public function write_start($table_prefix); + + /** + * Closes file and/or dumps download data + * + * @return null + * @throws \phpbb\db\extractor\exception\extractor_not_initialized_exception when calling this function before init_extractor() + */ + public function write_end(); + + /** + * Extracts database table structure + * + * @param string $table_name name of the database table + * @return null + * @throws \phpbb\db\extractor\exception\extractor_not_initialized_exception when calling this function before init_extractor() + */ + public function write_table($table_name); + + /** + * Extracts data from database table + * + * @param string $table_name name of the database table + * @return null + * @throws \phpbb\db\extractor\exception\extractor_not_initialized_exception when calling this function before init_extractor() + */ + public function write_data($table_name); + + /** + * Writes data to file/download content + * + * @param string $data + * @return null + * @throws \phpbb\db\extractor\exception\extractor_not_initialized_exception when calling this function before init_extractor() + */ + public function flush($data); +} diff --git a/phpBB/phpbb/db/extractor/factory.php b/phpBB/phpbb/db/extractor/factory.php new file mode 100644 index 0000000000..a1ffb65595 --- /dev/null +++ b/phpBB/phpbb/db/extractor/factory.php @@ -0,0 +1,79 @@ +<?php +/** +* +* This file is part of the phpBB Forum Software package. +* +* @copyright (c) phpBB Limited <https://www.phpbb.com> +* @license GNU General Public License, version 2 (GPL-2.0) +* +* For full copyright and license information, please see +* the docs/CREDITS.txt file. +* +*/ + +namespace phpbb\db\extractor; + +/** +* A factory which serves the suitable extractor instance for the given dbal +*/ +class factory +{ + /** + * @var \phpbb\db\driver\driver_interface + */ + protected $db; + + /** + * @var \Symfony\Component\DependencyInjection\ContainerInterface + */ + protected $container; + + /** + * Extractor factory constructor + * + * @param \phpbb\db\driver\driver_interface $db + * @param \Symfony\Component\DependencyInjection\ContainerInterface $container + */ + public function __construct(\phpbb\db\driver\driver_interface $db, \Symfony\Component\DependencyInjection\ContainerInterface $container) + { + $this->db = $db; + $this->container = $container; + } + + /** + * DB extractor factory getter + * + * @return \phpbb\db\extractor\extractor_interface an appropriate instance of the database extractor for the used database driver + * @throws \InvalidArgumentException when the database driver is unknown + */ + public function get() + { + // Return the appropriate DB extractor + if ($this->db instanceof \phpbb\db\driver\mssql || $this->db instanceof \phpbb\db\driver\mssql_base) + { + return $this->container->get('dbal.extractor.extractors.mssql_extractor'); + } + else if ($this->db instanceof \phpbb\db\driver\mysql_base) + { + return $this->container->get('dbal.extractor.extractors.mysql_extractor'); + } + else if ($this->db instanceof \phpbb\db\driver\oracle) + { + return $this->container->get('dbal.extractor.extractors.oracle_extractor'); + } + else if ($this->db instanceof \phpbb\db\driver\postgres) + { + return $this->container->get('dbal.extractor.extractors.postgres_extractor'); + } + else if ($this->db instanceof \phpbb\db\driver\sqlite) + { + return $this->container->get('dbal.extractor.extractors.sqlite_extractor'); + } + else if ($this->db instanceof \phpbb\db\driver\sqlite3) + { + return $this->container->get('dbal.extractor.extractors.sqlite3_extractor'); + } + + throw new \InvalidArgumentException('Invalid database driver given'); + } +} diff --git a/phpBB/phpbb/db/extractor/mssql_extractor.php b/phpBB/phpbb/db/extractor/mssql_extractor.php new file mode 100644 index 0000000000..d0aa78f1f5 --- /dev/null +++ b/phpBB/phpbb/db/extractor/mssql_extractor.php @@ -0,0 +1,524 @@ +<?php +/** +* +* This file is part of the phpBB Forum Software package. +* +* @copyright (c) phpBB Limited <https://www.phpbb.com> +* @license GNU General Public License, version 2 (GPL-2.0) +* +* For full copyright and license information, please see +* the docs/CREDITS.txt file. +* +*/ + +namespace phpbb\db\extractor; + +use phpbb\db\extractor\exception\extractor_not_initialized_exception; + +class mssql_extractor extends base_extractor +{ + /** + * Writes closing line(s) to database backup + * + * @return null + * @throws \phpbb\db\extractor\exception\extractor_not_initialized_exception when calling this function before init_extractor() + */ + public function write_end() + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + $this->flush("COMMIT\nGO\n"); + parent::write_end(); + } + + /** + * {@inheritdoc} + */ + public function write_start($table_prefix) + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + $sql_data = "--\n"; + $sql_data .= "-- phpBB Backup Script\n"; + $sql_data .= "-- Dump of tables for $table_prefix\n"; + $sql_data .= "-- DATE : " . gmdate("d-m-Y H:i:s", $this->time) . " GMT\n"; + $sql_data .= "--\n"; + $sql_data .= "BEGIN TRANSACTION\n"; + $sql_data .= "GO\n"; + $this->flush($sql_data); + } + + /** + * {@inheritdoc} + */ + public function write_table($table_name) + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + $sql_data = '-- Table: ' . $table_name . "\n"; + $sql_data .= "IF OBJECT_ID(N'$table_name', N'U') IS NOT NULL\n"; + $sql_data .= "DROP TABLE $table_name;\n"; + $sql_data .= "GO\n"; + $sql_data .= "\nCREATE TABLE [$table_name] (\n"; + $rows = array(); + + $text_flag = false; + + $sql = "SELECT COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') as IS_IDENTITY + FROM INFORMATION_SCHEMA.COLUMNS + WHERE TABLE_NAME = '$table_name'"; + $result = $this->db->sql_query($sql); + + while ($row = $this->db->sql_fetchrow($result)) + { + $line = "\t[{$row['COLUMN_NAME']}] [{$row['DATA_TYPE']}]"; + + if ($row['DATA_TYPE'] == 'text') + { + $text_flag = true; + } + + if ($row['IS_IDENTITY']) + { + $line .= ' IDENTITY (1 , 1)'; + } + + if ($row['CHARACTER_MAXIMUM_LENGTH'] && $row['DATA_TYPE'] !== 'text') + { + $line .= ' (' . $row['CHARACTER_MAXIMUM_LENGTH'] . ')'; + } + + if ($row['IS_NULLABLE'] == 'YES') + { + $line .= ' NULL'; + } + else + { + $line .= ' NOT NULL'; + } + + if ($row['COLUMN_DEFAULT']) + { + $line .= ' DEFAULT ' . $row['COLUMN_DEFAULT']; + } + + $rows[] = $line; + } + $this->db->sql_freeresult($result); + + $sql_data .= implode(",\n", $rows); + $sql_data .= "\n) ON [PRIMARY]"; + + if ($text_flag) + { + $sql_data .= " TEXTIMAGE_ON [PRIMARY]"; + } + + $sql_data .= "\nGO\n\n"; + $rows = array(); + + $sql = "SELECT CONSTRAINT_NAME, COLUMN_NAME + FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE + WHERE TABLE_NAME = '$table_name'"; + $result = $this->db->sql_query($sql); + while ($row = $this->db->sql_fetchrow($result)) + { + if (!sizeof($rows)) + { + $sql_data .= "ALTER TABLE [$table_name] WITH NOCHECK ADD\n"; + $sql_data .= "\tCONSTRAINT [{$row['CONSTRAINT_NAME']}] PRIMARY KEY CLUSTERED \n\t(\n"; + } + $rows[] = "\t\t[{$row['COLUMN_NAME']}]"; + } + if (sizeof($rows)) + { + $sql_data .= implode(",\n", $rows); + $sql_data .= "\n\t) ON [PRIMARY] \nGO\n"; + } + $this->db->sql_freeresult($result); + + $index = array(); + $sql = "EXEC sp_statistics '$table_name'"; + $result = $this->db->sql_query($sql); + while ($row = $this->db->sql_fetchrow($result)) + { + if ($row['TYPE'] == 3) + { + $index[$row['INDEX_NAME']][] = '[' . $row['COLUMN_NAME'] . ']'; + } + } + $this->db->sql_freeresult($result); + + foreach ($index as $index_name => $column_name) + { + $index[$index_name] = implode(', ', $column_name); + } + + foreach ($index as $index_name => $columns) + { + $sql_data .= "\nCREATE INDEX [$index_name] ON [$table_name]($columns) ON [PRIMARY]\nGO\n"; + } + $this->flush($sql_data); + } + + /** + * {@inheritdoc} + */ + public function write_data($table_name) + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + if ($this->db->get_sql_layer() === 'mssql') + { + $this->write_data_mssql($table_name); + } + else if($this->db->get_sql_layer() === 'mssqlnative') + { + $this->write_data_mssqlnative($table_name); + } + else + { + $this->write_data_odbc($table_name); + } + } + + /** + * Extracts data from database table (for MSSQL driver) + * + * @param string $table_name name of the database table + * @return null + * @throws \phpbb\db\extractor\exception\extractor_not_initialized_exception when calling this function before init_extractor() + */ + protected function write_data_mssql($table_name) + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + $ary_type = $ary_name = array(); + $ident_set = false; + $sql_data = ''; + + // Grab all of the data from current table. + $sql = "SELECT * + FROM $table_name"; + $result = $this->db->sql_query($sql); + + $retrieved_data = mssql_num_rows($result); + + $i_num_fields = mssql_num_fields($result); + + for ($i = 0; $i < $i_num_fields; $i++) + { + $ary_type[$i] = mssql_field_type($result, $i); + $ary_name[$i] = mssql_field_name($result, $i); + } + + if ($retrieved_data) + { + $sql = "SELECT 1 as has_identity + FROM INFORMATION_SCHEMA.COLUMNS + WHERE COLUMNPROPERTY(object_id('$table_name'), COLUMN_NAME, 'IsIdentity') = 1"; + $result2 = $this->db->sql_query($sql); + $row2 = $this->db->sql_fetchrow($result2); + if (!empty($row2['has_identity'])) + { + $sql_data .= "\nSET IDENTITY_INSERT $table_name ON\nGO\n"; + $ident_set = true; + } + $this->db->sql_freeresult($result2); + } + + while ($row = $this->db->sql_fetchrow($result)) + { + $schema_vals = $schema_fields = array(); + + // Build the SQL statement to recreate the data. + for ($i = 0; $i < $i_num_fields; $i++) + { + $str_val = $row[$ary_name[$i]]; + + if (preg_match('#char|text|bool|varbinary#i', $ary_type[$i])) + { + $str_quote = ''; + $str_empty = "''"; + $str_val = sanitize_data_mssql(str_replace("'", "''", $str_val)); + } + else if (preg_match('#date|timestamp#i', $ary_type[$i])) + { + if (empty($str_val)) + { + $str_quote = ''; + } + else + { + $str_quote = "'"; + } + } + else + { + $str_quote = ''; + $str_empty = 'NULL'; + } + + if (empty($str_val) && $str_val !== '0' && !(is_int($str_val) || is_float($str_val))) + { + $str_val = $str_empty; + } + + $schema_vals[$i] = $str_quote . $str_val . $str_quote; + $schema_fields[$i] = $ary_name[$i]; + } + + // Take the ordered fields and their associated data and build it + // into a valid sql statement to recreate that field in the data. + $sql_data .= "INSERT INTO $table_name (" . implode(', ', $schema_fields) . ') VALUES (' . implode(', ', $schema_vals) . ");\nGO\n"; + + $this->flush($sql_data); + $sql_data = ''; + } + $this->db->sql_freeresult($result); + + if ($retrieved_data && $ident_set) + { + $sql_data .= "\nSET IDENTITY_INSERT $table_name OFF\nGO\n"; + } + $this->flush($sql_data); + } + + /** + * Extracts data from database table (for MSSQL Native driver) + * + * @param string $table_name name of the database table + * @return null + * @throws \phpbb\db\extractor\exception\extractor_not_initialized_exception when calling this function before init_extractor() + */ + protected function write_data_mssqlnative($table_name) + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + $ary_type = $ary_name = array(); + $ident_set = false; + $sql_data = ''; + + // Grab all of the data from current table. + $sql = "SELECT * FROM $table_name"; + $this->db->mssqlnative_set_query_options(array('Scrollable' => SQLSRV_CURSOR_STATIC)); + $result = $this->db->sql_query($sql); + + $retrieved_data = $this->db->mssqlnative_num_rows($result); + + if (!$retrieved_data) + { + $this->db->sql_freeresult($result); + return; + } + + $sql = "SELECT COLUMN_NAME, DATA_TYPE + FROM INFORMATION_SCHEMA.COLUMNS + WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = '" . $this->db->sql_escape($table_name) . "'"; + $result_fields = $this->db->sql_query($sql); + + $i_num_fields = 0; + while ($row = $this->db->sql_fetchrow($result_fields)) + { + $ary_type[$i_num_fields] = $row['DATA_TYPE']; + $ary_name[$i_num_fields] = $row['COLUMN_NAME']; + $i_num_fields++; + } + $this->db->sql_freeresult($result_fields); + + $sql = "SELECT 1 as has_identity + FROM INFORMATION_SCHEMA.COLUMNS + WHERE COLUMNPROPERTY(object_id('$table_name'), COLUMN_NAME, 'IsIdentity') = 1"; + $result2 = $this->db->sql_query($sql); + $row2 = $this->db->sql_fetchrow($result2); + + if (!empty($row2['has_identity'])) + { + $sql_data .= "\nSET IDENTITY_INSERT $table_name ON\nGO\n"; + $ident_set = true; + } + $this->db->sql_freeresult($result2); + + while ($row = $this->db->sql_fetchrow($result)) + { + $schema_vals = $schema_fields = array(); + + // Build the SQL statement to recreate the data. + for ($i = 0; $i < $i_num_fields; $i++) + { + $str_val = $row[$ary_name[$i]]; + + // defaults to type number - better quote just to be safe, so check for is_int too + if (is_int($ary_type[$i]) || preg_match('#char|text|bool|varbinary#i', $ary_type[$i])) + { + $str_quote = ''; + $str_empty = "''"; + $str_val = sanitize_data_mssql(str_replace("'", "''", $str_val)); + } + else if (preg_match('#date|timestamp#i', $ary_type[$i])) + { + if (empty($str_val)) + { + $str_quote = ''; + } + else + { + $str_quote = "'"; + } + } + else + { + $str_quote = ''; + $str_empty = 'NULL'; + } + + if (empty($str_val) && $str_val !== '0' && !(is_int($str_val) || is_float($str_val))) + { + $str_val = $str_empty; + } + + $schema_vals[$i] = $str_quote . $str_val . $str_quote; + $schema_fields[$i] = $ary_name[$i]; + } + + // Take the ordered fields and their associated data and build it + // into a valid sql statement to recreate that field in the data. + $sql_data .= "INSERT INTO $table_name (" . implode(', ', $schema_fields) . ') VALUES (' . implode(', ', $schema_vals) . ");\nGO\n"; + + $this->flush($sql_data); + $sql_data = ''; + } + $this->db->sql_freeresult($result); + + if ($ident_set) + { + $sql_data .= "\nSET IDENTITY_INSERT $table_name OFF\nGO\n"; + } + $this->flush($sql_data); + } + + /** + * Extracts data from database table (for ODBC driver) + * + * @param string $table_name name of the database table + * @return null + * @throws \phpbb\db\extractor\exception\extractor_not_initialized_exception when calling this function before init_extractor() + */ + protected function write_data_odbc($table_name) + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + $ary_type = $ary_name = array(); + $ident_set = false; + $sql_data = ''; + + // Grab all of the data from current table. + $sql = "SELECT * + FROM $table_name"; + $result = $this->db->sql_query($sql); + + $retrieved_data = odbc_num_rows($result); + + if ($retrieved_data) + { + $sql = "SELECT 1 as has_identity + FROM INFORMATION_SCHEMA.COLUMNS + WHERE COLUMNPROPERTY(object_id('$table_name'), COLUMN_NAME, 'IsIdentity') = 1"; + $result2 = $this->db->sql_query($sql); + $row2 = $this->db->sql_fetchrow($result2); + if (!empty($row2['has_identity'])) + { + $sql_data .= "\nSET IDENTITY_INSERT $table_name ON\nGO\n"; + $ident_set = true; + } + $this->db->sql_freeresult($result2); + } + + $i_num_fields = odbc_num_fields($result); + + for ($i = 0; $i < $i_num_fields; $i++) + { + $ary_type[$i] = odbc_field_type($result, $i + 1); + $ary_name[$i] = odbc_field_name($result, $i + 1); + } + + while ($row = $this->db->sql_fetchrow($result)) + { + $schema_vals = $schema_fields = array(); + + // Build the SQL statement to recreate the data. + for ($i = 0; $i < $i_num_fields; $i++) + { + $str_val = $row[$ary_name[$i]]; + + if (preg_match('#char|text|bool|varbinary#i', $ary_type[$i])) + { + $str_quote = ''; + $str_empty = "''"; + $str_val = sanitize_data_mssql(str_replace("'", "''", $str_val)); + } + else if (preg_match('#date|timestamp#i', $ary_type[$i])) + { + if (empty($str_val)) + { + $str_quote = ''; + } + else + { + $str_quote = "'"; + } + } + else + { + $str_quote = ''; + $str_empty = 'NULL'; + } + + if (empty($str_val) && $str_val !== '0' && !(is_int($str_val) || is_float($str_val))) + { + $str_val = $str_empty; + } + + $schema_vals[$i] = $str_quote . $str_val . $str_quote; + $schema_fields[$i] = $ary_name[$i]; + } + + // Take the ordered fields and their associated data and build it + // into a valid sql statement to recreate that field in the data. + $sql_data .= "INSERT INTO $table_name (" . implode(', ', $schema_fields) . ') VALUES (' . implode(', ', $schema_vals) . ");\nGO\n"; + + $this->flush($sql_data); + + $sql_data = ''; + + } + $this->db->sql_freeresult($result); + + if ($retrieved_data && $ident_set) + { + $sql_data .= "\nSET IDENTITY_INSERT $table_name OFF\nGO\n"; + } + $this->flush($sql_data); + } +} diff --git a/phpBB/phpbb/db/extractor/mysql_extractor.php b/phpBB/phpbb/db/extractor/mysql_extractor.php new file mode 100644 index 0000000000..34e309c19e --- /dev/null +++ b/phpBB/phpbb/db/extractor/mysql_extractor.php @@ -0,0 +1,403 @@ +<?php +/** +* +* This file is part of the phpBB Forum Software package. +* +* @copyright (c) phpBB Limited <https://www.phpbb.com> +* @license GNU General Public License, version 2 (GPL-2.0) +* +* For full copyright and license information, please see +* the docs/CREDITS.txt file. +* +*/ + +namespace phpbb\db\extractor; + +use phpbb\db\extractor\exception\extractor_not_initialized_exception; + +class mysql_extractor extends base_extractor +{ + /** + * {@inheritdoc} + */ + public function write_start($table_prefix) + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + $sql_data = "#\n"; + $sql_data .= "# phpBB Backup Script\n"; + $sql_data .= "# Dump of tables for $table_prefix\n"; + $sql_data .= "# DATE : " . gmdate("d-m-Y H:i:s", $this->time) . " GMT\n"; + $sql_data .= "#\n"; + $this->flush($sql_data); + } + + /** + * {@inheritdoc} + */ + public function write_table($table_name) + { + static $new_extract; + + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + if ($new_extract === null) + { + if ($this->db->get_sql_layer() === 'mysqli' || version_compare($this->db->sql_server_info(true), '3.23.20', '>=')) + { + $new_extract = true; + } + else + { + $new_extract = false; + } + } + + if ($new_extract) + { + $this->new_write_table($table_name); + } + else + { + $this->old_write_table($table_name); + } + } + + /** + * {@inheritdoc} + */ + public function write_data($table_name) + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + if ($this->db->get_sql_layer() === 'mysqli') + { + $this->write_data_mysqli($table_name); + } + else + { + $this->write_data_mysql($table_name); + } + } + + /** + * Extracts data from database table (for MySQLi driver) + * + * @param string $table_name name of the database table + * @return null + * @throws \phpbb\db\extractor\exception\extractor_not_initialized_exception when calling this function before init_extractor() + */ + protected function write_data_mysqli($table_name) + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + $sql = "SELECT * + FROM $table_name"; + $result = mysqli_query($this->db->get_db_connect_id(), $sql, MYSQLI_USE_RESULT); + if ($result != false) + { + $fields_cnt = mysqli_num_fields($result); + + // Get field information + $field = mysqli_fetch_fields($result); + $field_set = array(); + + for ($j = 0; $j < $fields_cnt; $j++) + { + $field_set[] = $field[$j]->name; + } + + $search = array("\\", "'", "\x00", "\x0a", "\x0d", "\x1a", '"'); + $replace = array("\\\\", "\\'", '\0', '\n', '\r', '\Z', '\\"'); + $fields = implode(', ', $field_set); + $sql_data = 'INSERT INTO ' . $table_name . ' (' . $fields . ') VALUES '; + $first_set = true; + $query_len = 0; + $max_len = get_usable_memory(); + + while ($row = mysqli_fetch_row($result)) + { + $values = array(); + if ($first_set) + { + $query = $sql_data . '('; + } + else + { + $query .= ',('; + } + + for ($j = 0; $j < $fields_cnt; $j++) + { + if (!isset($row[$j]) || is_null($row[$j])) + { + $values[$j] = 'NULL'; + } + else if (($field[$j]->flags & 32768) && !($field[$j]->flags & 1024)) + { + $values[$j] = $row[$j]; + } + else + { + $values[$j] = "'" . str_replace($search, $replace, $row[$j]) . "'"; + } + } + $query .= implode(', ', $values) . ')'; + + $query_len += strlen($query); + if ($query_len > $max_len) + { + $this->flush($query . ";\n\n"); + $query = ''; + $query_len = 0; + $first_set = true; + } + else + { + $first_set = false; + } + } + mysqli_free_result($result); + + // check to make sure we have nothing left to flush + if (!$first_set && $query) + { + $this->flush($query . ";\n\n"); + } + } + } + + /** + * Extracts data from database table (for MySQL driver) + * + * @param string $table_name name of the database table + * @return null + * @throws \phpbb\db\extractor\exception\extractor_not_initialized_exception when calling this function before init_extractor() + */ + protected function write_data_mysql($table_name) + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + $sql = "SELECT * + FROM $table_name"; + $result = mysql_unbuffered_query($sql, $this->db->get_db_connect_id()); + + if ($result != false) + { + $fields_cnt = mysql_num_fields($result); + + // Get field information + $field = array(); + for ($i = 0; $i < $fields_cnt; $i++) + { + $field[] = mysql_fetch_field($result, $i); + } + $field_set = array(); + + for ($j = 0; $j < $fields_cnt; $j++) + { + $field_set[] = $field[$j]->name; + } + + $search = array("\\", "'", "\x00", "\x0a", "\x0d", "\x1a", '"'); + $replace = array("\\\\", "\\'", '\0', '\n', '\r', '\Z', '\\"'); + $fields = implode(', ', $field_set); + $sql_data = 'INSERT INTO ' . $table_name . ' (' . $fields . ') VALUES '; + $first_set = true; + $query_len = 0; + $max_len = get_usable_memory(); + + while ($row = mysql_fetch_row($result)) + { + $values = array(); + if ($first_set) + { + $query = $sql_data . '('; + } + else + { + $query .= ',('; + } + + for ($j = 0; $j < $fields_cnt; $j++) + { + if (!isset($row[$j]) || is_null($row[$j])) + { + $values[$j] = 'NULL'; + } + else if ($field[$j]->numeric && ($field[$j]->type !== 'timestamp')) + { + $values[$j] = $row[$j]; + } + else + { + $values[$j] = "'" . str_replace($search, $replace, $row[$j]) . "'"; + } + } + $query .= implode(', ', $values) . ')'; + + $query_len += strlen($query); + if ($query_len > $max_len) + { + $this->flush($query . ";\n\n"); + $query = ''; + $query_len = 0; + $first_set = true; + } + else + { + $first_set = false; + } + } + mysql_free_result($result); + + // check to make sure we have nothing left to flush + if (!$first_set && $query) + { + $this->flush($query . ";\n\n"); + } + } + } + + /** + * Extracts database table structure (for MySQLi or MySQL 3.23.20+) + * + * @param string $table_name name of the database table + * @return null + * @throws \phpbb\db\extractor\exception\extractor_not_initialized_exception when calling this function before init_extractor() + */ + protected function new_write_table($table_name) + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + $sql = 'SHOW CREATE TABLE ' . $table_name; + $result = $this->db->sql_query($sql); + $row = $this->db->sql_fetchrow($result); + + $sql_data = '# Table: ' . $table_name . "\n"; + $sql_data .= "DROP TABLE IF EXISTS $table_name;\n"; + $this->flush($sql_data . $row['Create Table'] . ";\n\n"); + + $this->db->sql_freeresult($result); + } + + /** + * Extracts database table structure (for MySQL verisons older than 3.23.20) + * + * @param string $table_name name of the database table + * @return null + * @throws \phpbb\db\extractor\exception\extractor_not_initialized_exception when calling this function before init_extractor() + */ + protected function old_write_table($table_name) + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + $sql_data = '# Table: ' . $table_name . "\n"; + $sql_data .= "DROP TABLE IF EXISTS $table_name;\n"; + $sql_data .= "CREATE TABLE $table_name(\n"; + $rows = array(); + + $sql = "SHOW FIELDS + FROM $table_name"; + $result = $this->db->sql_query($sql); + + while ($row = $this->db->sql_fetchrow($result)) + { + $line = ' ' . $row['Field'] . ' ' . $row['Type']; + + if (!is_null($row['Default'])) + { + $line .= " DEFAULT '{$row['Default']}'"; + } + + if ($row['Null'] != 'YES') + { + $line .= ' NOT NULL'; + } + + if ($row['Extra'] != '') + { + $line .= ' ' . $row['Extra']; + } + + $rows[] = $line; + } + $this->db->sql_freeresult($result); + + $sql = "SHOW KEYS + FROM $table_name"; + + $result = $this->db->sql_query($sql); + + $index = array(); + while ($row = $this->db->sql_fetchrow($result)) + { + $kname = $row['Key_name']; + + if ($kname != 'PRIMARY') + { + if ($row['Non_unique'] == 0) + { + $kname = "UNIQUE|$kname"; + } + } + + if ($row['Sub_part']) + { + $row['Column_name'] .= '(' . $row['Sub_part'] . ')'; + } + $index[$kname][] = $row['Column_name']; + } + $this->db->sql_freeresult($result); + + foreach ($index as $key => $columns) + { + $line = ' '; + + if ($key == 'PRIMARY') + { + $line .= 'PRIMARY KEY (' . implode(', ', $columns) . ')'; + } + else if (strpos($key, 'UNIQUE') === 0) + { + $line .= 'UNIQUE ' . substr($key, 7) . ' (' . implode(', ', $columns) . ')'; + } + else if (strpos($key, 'FULLTEXT') === 0) + { + $line .= 'FULLTEXT ' . substr($key, 9) . ' (' . implode(', ', $columns) . ')'; + } + else + { + $line .= "KEY $key (" . implode(', ', $columns) . ')'; + } + + $rows[] = $line; + } + + $sql_data .= implode(",\n", $rows); + $sql_data .= "\n);\n\n"; + + $this->flush($sql_data); + } +} diff --git a/phpBB/phpbb/db/extractor/oracle_extractor.php b/phpBB/phpbb/db/extractor/oracle_extractor.php new file mode 100644 index 0000000000..05f7b8ac95 --- /dev/null +++ b/phpBB/phpbb/db/extractor/oracle_extractor.php @@ -0,0 +1,265 @@ +<?php +/** +* +* This file is part of the phpBB Forum Software package. +* +* @copyright (c) phpBB Limited <https://www.phpbb.com> +* @license GNU General Public License, version 2 (GPL-2.0) +* +* For full copyright and license information, please see +* the docs/CREDITS.txt file. +* +*/ + +namespace phpbb\db\extractor; + +use phpbb\db\extractor\exception\extractor_not_initialized_exception; + +class oracle_extractor extends base_extractor +{ + /** + * {@inheritdoc} + */ + public function write_table($table_name) + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + $sql_data = '-- Table: ' . $table_name . "\n"; + $sql_data .= "DROP TABLE $table_name\n/\n"; + $sql_data .= "\nCREATE TABLE $table_name (\n"; + + $sql = "SELECT COLUMN_NAME, DATA_TYPE, DATA_PRECISION, DATA_LENGTH, NULLABLE, DATA_DEFAULT + FROM ALL_TAB_COLS + WHERE table_name = '{$table_name}'"; + $result = $this->db->sql_query($sql); + + $rows = array(); + while ($row = $this->db->sql_fetchrow($result)) + { + $line = ' "' . $row['column_name'] . '" ' . $row['data_type']; + + if ($row['data_type'] !== 'CLOB') + { + if ($row['data_type'] !== 'VARCHAR2' && $row['data_type'] !== 'CHAR') + { + $line .= '(' . $row['data_precision'] . ')'; + } + else + { + $line .= '(' . $row['data_length'] . ')'; + } + } + + if (!empty($row['data_default'])) + { + $line .= ' DEFAULT ' . $row['data_default']; + } + + if ($row['nullable'] == 'N') + { + $line .= ' NOT NULL'; + } + $rows[] = $line; + } + $this->db->sql_freeresult($result); + + $sql = "SELECT A.CONSTRAINT_NAME, A.COLUMN_NAME + FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B + WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME + AND B.CONSTRAINT_TYPE = 'P' + AND A.TABLE_NAME = '{$table_name}'"; + $result = $this->db->sql_query($sql); + + $primary_key = array(); + $constraint_name = ''; + while ($row = $this->db->sql_fetchrow($result)) + { + $constraint_name = '"' . $row['constraint_name'] . '"'; + $primary_key[] = '"' . $row['column_name'] . '"'; + } + $this->db->sql_freeresult($result); + + if (sizeof($primary_key)) + { + $rows[] = " CONSTRAINT {$constraint_name} PRIMARY KEY (" . implode(', ', $primary_key) . ')'; + } + + $sql = "SELECT A.CONSTRAINT_NAME, A.COLUMN_NAME + FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B + WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME + AND B.CONSTRAINT_TYPE = 'U' + AND A.TABLE_NAME = '{$table_name}'"; + $result = $this->db->sql_query($sql); + + $unique = array(); + $constraint_name = ''; + while ($row = $this->db->sql_fetchrow($result)) + { + $constraint_name = '"' . $row['constraint_name'] . '"'; + $unique[] = '"' . $row['column_name'] . '"'; + } + $this->db->sql_freeresult($result); + + if (sizeof($unique)) + { + $rows[] = " CONSTRAINT {$constraint_name} UNIQUE (" . implode(', ', $unique) . ')'; + } + + $sql_data .= implode(",\n", $rows); + $sql_data .= "\n)\n/\n"; + + $sql = "SELECT A.REFERENCED_NAME, C.* + FROM USER_DEPENDENCIES A, USER_TRIGGERS B, USER_SEQUENCES C + WHERE A.REFERENCED_TYPE = 'SEQUENCE' + AND A.NAME = B.TRIGGER_NAME + AND B.TABLE_NAME = '{$table_name}' + AND C.SEQUENCE_NAME = A.REFERENCED_NAME"; + $result = $this->db->sql_query($sql); + + $type = $this->request->variable('type', ''); + + while ($row = $this->db->sql_fetchrow($result)) + { + $sql_data .= "\nDROP SEQUENCE \"{$row['referenced_name']}\"\n/\n"; + $sql_data .= "\nCREATE SEQUENCE \"{$row['referenced_name']}\""; + + if ($type == 'full') + { + $sql_data .= ' START WITH ' . $row['last_number']; + } + + $sql_data .= "\n/\n"; + } + $this->db->sql_freeresult($result); + + $sql = "SELECT DESCRIPTION, WHEN_CLAUSE, TRIGGER_BODY + FROM USER_TRIGGERS + WHERE TABLE_NAME = '{$table_name}'"; + $result = $this->db->sql_query($sql); + while ($row = $this->db->sql_fetchrow($result)) + { + $sql_data .= "\nCREATE OR REPLACE TRIGGER {$row['description']}WHEN ({$row['when_clause']})\n{$row['trigger_body']}\n/\n"; + } + $this->db->sql_freeresult($result); + + $sql = "SELECT A.INDEX_NAME, B.COLUMN_NAME + FROM USER_INDEXES A, USER_IND_COLUMNS B + WHERE A.UNIQUENESS = 'NONUNIQUE' + AND A.INDEX_NAME = B.INDEX_NAME + AND B.TABLE_NAME = '{$table_name}'"; + $result = $this->db->sql_query($sql); + + $index = array(); + + while ($row = $this->db->sql_fetchrow($result)) + { + $index[$row['index_name']][] = $row['column_name']; + } + + foreach ($index as $index_name => $column_names) + { + $sql_data .= "\nCREATE INDEX $index_name ON $table_name(" . implode(', ', $column_names) . ")\n/\n"; + } + $this->db->sql_freeresult($result); + $this->flush($sql_data); + } + + /** + * {@inheritdoc} + */ + public function write_data($table_name) + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + $ary_type = $ary_name = array(); + + // Grab all of the data from current table. + $sql = "SELECT * + FROM $table_name"; + $result = $this->db->sql_query($sql); + + $i_num_fields = ocinumcols($result); + + for ($i = 0; $i < $i_num_fields; $i++) + { + $ary_type[$i] = ocicolumntype($result, $i + 1); + $ary_name[$i] = ocicolumnname($result, $i + 1); + } + + $sql_data = ''; + + while ($row = $this->db->sql_fetchrow($result)) + { + $schema_vals = $schema_fields = array(); + + // Build the SQL statement to recreate the data. + for ($i = 0; $i < $i_num_fields; $i++) + { + // Oracle uses uppercase - we use lowercase + $str_val = $row[strtolower($ary_name[$i])]; + + if (preg_match('#char|text|bool|raw|clob#i', $ary_type[$i])) + { + $str_quote = ''; + $str_empty = "''"; + $str_val = sanitize_data_oracle($str_val); + } + else if (preg_match('#date|timestamp#i', $ary_type[$i])) + { + if (empty($str_val)) + { + $str_quote = ''; + } + else + { + $str_quote = "'"; + } + } + else + { + $str_quote = ''; + $str_empty = 'NULL'; + } + + if (empty($str_val) && $str_val !== '0') + { + $str_val = $str_empty; + } + + $schema_vals[$i] = $str_quote . $str_val . $str_quote; + $schema_fields[$i] = '"' . $ary_name[$i] . '"'; + } + + // Take the ordered fields and their associated data and build it + // into a valid sql statement to recreate that field in the data. + $sql_data = "INSERT INTO $table_name (" . implode(', ', $schema_fields) . ') VALUES (' . implode(', ', $schema_vals) . ")\n/\n"; + + $this->flush($sql_data); + } + $this->db->sql_freeresult($result); + } + + /** + * {@inheritdoc} + */ + public function write_start($table_prefix) + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + $sql_data = "--\n"; + $sql_data .= "-- phpBB Backup Script\n"; + $sql_data .= "-- Dump of tables for $table_prefix\n"; + $sql_data .= "-- DATE : " . gmdate("d-m-Y H:i:s", $this->time) . " GMT\n"; + $sql_data .= "--\n"; + $this->flush($sql_data); + } +} diff --git a/phpBB/phpbb/db/extractor/postgres_extractor.php b/phpBB/phpbb/db/extractor/postgres_extractor.php new file mode 100644 index 0000000000..9eff1f568d --- /dev/null +++ b/phpBB/phpbb/db/extractor/postgres_extractor.php @@ -0,0 +1,338 @@ +<?php +/** +* +* This file is part of the phpBB Forum Software package. +* +* @copyright (c) phpBB Limited <https://www.phpbb.com> +* @license GNU General Public License, version 2 (GPL-2.0) +* +* For full copyright and license information, please see +* the docs/CREDITS.txt file. +* +*/ + +namespace phpbb\db\extractor; + +use phpbb\db\extractor\exception\extractor_not_initialized_exception; + +class postgres_extractor extends base_extractor +{ + /** + * {@inheritdoc} + */ + public function write_start($table_prefix) + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + $sql_data = "--\n"; + $sql_data .= "-- phpBB Backup Script\n"; + $sql_data .= "-- Dump of tables for $table_prefix\n"; + $sql_data .= "-- DATE : " . gmdate("d-m-Y H:i:s", $this->time) . " GMT\n"; + $sql_data .= "--\n"; + $sql_data .= "BEGIN TRANSACTION;\n"; + $this->flush($sql_data); + } + + /** + * {@inheritdoc} + */ + public function write_table($table_name) + { + static $domains_created = array(); + + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + $sql = "SELECT a.domain_name, a.data_type, a.character_maximum_length, a.domain_default + FROM INFORMATION_SCHEMA.domains a, INFORMATION_SCHEMA.column_domain_usage b + WHERE a.domain_name = b.domain_name + AND b.table_name = '{$table_name}'"; + $result = $this->db->sql_query($sql); + while ($row = $this->db->sql_fetchrow($result)) + { + if (empty($domains_created[$row['domain_name']])) + { + $domains_created[$row['domain_name']] = true; + //$sql_data = "DROP DOMAIN {$row['domain_name']};\n"; + $sql_data = "CREATE DOMAIN {$row['domain_name']} as {$row['data_type']}"; + if (!empty($row['character_maximum_length'])) + { + $sql_data .= '(' . $row['character_maximum_length'] . ')'; + } + $sql_data .= ' NOT NULL'; + if (!empty($row['domain_default'])) + { + $sql_data .= ' DEFAULT ' . $row['domain_default']; + } + $this->flush($sql_data . ";\n"); + } + } + + $sql_data = '-- Table: ' . $table_name . "\n"; + $sql_data .= "DROP TABLE $table_name;\n"; + // PGSQL does not "tightly" bind sequences and tables, we must guess... + $sql = "SELECT relname + FROM pg_class + WHERE relkind = 'S' + AND relname = '{$table_name}_seq'"; + $result = $this->db->sql_query($sql); + // We don't even care about storing the results. We already know the answer if we get rows back. + if ($this->db->sql_fetchrow($result)) + { + $sql_data .= "DROP SEQUENCE {$table_name}_seq;\n"; + $sql_data .= "CREATE SEQUENCE {$table_name}_seq;\n"; + } + $this->db->sql_freeresult($result); + + $field_query = "SELECT a.attnum, a.attname as field, t.typname as type, a.attlen as length, a.atttypmod as lengthvar, a.attnotnull as notnull + FROM pg_class c, pg_attribute a, pg_type t + WHERE c.relname = '" . $this->db->sql_escape($table_name) . "' + AND a.attnum > 0 + AND a.attrelid = c.oid + AND a.atttypid = t.oid + ORDER BY a.attnum"; + $result = $this->db->sql_query($field_query); + + $sql_data .= "CREATE TABLE $table_name(\n"; + $lines = array(); + while ($row = $this->db->sql_fetchrow($result)) + { + // Get the data from the table + $sql_get_default = "SELECT pg_get_expr(d.adbin, d.adrelid) as rowdefault + FROM pg_attrdef d, pg_class c + WHERE (c.relname = '" . $this->db->sql_escape($table_name) . "') + AND (c.oid = d.adrelid) + AND d.adnum = " . $row['attnum']; + $def_res = $this->db->sql_query($sql_get_default); + $def_row = $this->db->sql_fetchrow($def_res); + $this->db->sql_freeresult($def_res); + + if (empty($def_row)) + { + unset($row['rowdefault']); + } + else + { + $row['rowdefault'] = $def_row['rowdefault']; + } + + if ($row['type'] == 'bpchar') + { + // Internally stored as bpchar, but isn't accepted in a CREATE TABLE statement. + $row['type'] = 'char'; + } + + $line = ' ' . $row['field'] . ' ' . $row['type']; + + if (strpos($row['type'], 'char') !== false) + { + if ($row['lengthvar'] > 0) + { + $line .= '(' . ($row['lengthvar'] - 4) . ')'; + } + } + + if (strpos($row['type'], 'numeric') !== false) + { + $line .= '('; + $line .= sprintf("%s,%s", (($row['lengthvar'] >> 16) & 0xffff), (($row['lengthvar'] - 4) & 0xffff)); + $line .= ')'; + } + + if (isset($row['rowdefault'])) + { + $line .= ' DEFAULT ' . $row['rowdefault']; + } + + if ($row['notnull'] == 't') + { + $line .= ' NOT NULL'; + } + + $lines[] = $line; + } + $this->db->sql_freeresult($result); + + // Get the listing of primary keys. + $sql_pri_keys = "SELECT ic.relname as index_name, bc.relname as tab_name, ta.attname as column_name, i.indisunique as unique_key, i.indisprimary as primary_key + FROM pg_class bc, pg_class ic, pg_index i, pg_attribute ta, pg_attribute ia + WHERE (bc.oid = i.indrelid) + AND (ic.oid = i.indexrelid) + AND (ia.attrelid = i.indexrelid) + AND (ta.attrelid = bc.oid) + AND (bc.relname = '" . $this->db->sql_escape($table_name) . "') + AND (ta.attrelid = i.indrelid) + AND (ta.attnum = i.indkey[ia.attnum-1]) + ORDER BY index_name, tab_name, column_name"; + + $result = $this->db->sql_query($sql_pri_keys); + + $index_create = $index_rows = $primary_key = array(); + + // We do this in two steps. It makes placing the comma easier + while ($row = $this->db->sql_fetchrow($result)) + { + if ($row['primary_key'] == 't') + { + $primary_key[] = $row['column_name']; + $primary_key_name = $row['index_name']; + } + else + { + // We have to store this all this info because it is possible to have a multi-column key... + // we can loop through it again and build the statement + $index_rows[$row['index_name']]['table'] = $table_name; + $index_rows[$row['index_name']]['unique'] = ($row['unique_key'] == 't') ? true : false; + $index_rows[$row['index_name']]['column_names'][] = $row['column_name']; + } + } + $this->db->sql_freeresult($result); + + if (!empty($index_rows)) + { + foreach ($index_rows as $idx_name => $props) + { + $index_create[] = 'CREATE ' . ($props['unique'] ? 'UNIQUE ' : '') . "INDEX $idx_name ON $table_name (" . implode(', ', $props['column_names']) . ");"; + } + } + + if (!empty($primary_key)) + { + $lines[] = " CONSTRAINT $primary_key_name PRIMARY KEY (" . implode(', ', $primary_key) . ")"; + } + + // Generate constraint clauses for CHECK constraints + $sql_checks = "SELECT conname as index_name, consrc + FROM pg_constraint, pg_class bc + WHERE conrelid = bc.oid + AND bc.relname = '" . $this->db->sql_escape($table_name) . "' + AND NOT EXISTS ( + SELECT * + FROM pg_constraint as c, pg_inherits as i + WHERE i.inhrelid = pg_constraint.conrelid + AND c.conname = pg_constraint.conname + AND c.consrc = pg_constraint.consrc + AND c.conrelid = i.inhparent + )"; + $result = $this->db->sql_query($sql_checks); + + // Add the constraints to the sql file. + while ($row = $this->db->sql_fetchrow($result)) + { + if (!is_null($row['consrc'])) + { + $lines[] = ' CONSTRAINT ' . $row['index_name'] . ' CHECK ' . $row['consrc']; + } + } + $this->db->sql_freeresult($result); + + $sql_data .= implode(", \n", $lines); + $sql_data .= "\n);\n"; + + if (!empty($index_create)) + { + $sql_data .= implode("\n", $index_create) . "\n\n"; + } + $this->flush($sql_data); + } + + /** + * {@inheritdoc} + */ + public function write_data($table_name) + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + // Grab all of the data from current table. + $sql = "SELECT * + FROM $table_name"; + $result = $this->db->sql_query($sql); + + $i_num_fields = pg_num_fields($result); + $seq = ''; + + for ($i = 0; $i < $i_num_fields; $i++) + { + $ary_type[] = pg_field_type($result, $i); + $ary_name[] = pg_field_name($result, $i); + + $sql = "SELECT pg_get_expr(d.adbin, d.adrelid) as rowdefault + FROM pg_attrdef d, pg_class c + WHERE (c.relname = '{$table_name}') + AND (c.oid = d.adrelid) + AND d.adnum = " . strval($i + 1); + $result2 = $this->db->sql_query($sql); + if ($row = $this->db->sql_fetchrow($result2)) + { + // Determine if we must reset the sequences + if (strpos($row['rowdefault'], "nextval('") === 0) + { + $seq .= "SELECT SETVAL('{$table_name}_seq',(select case when max({$ary_name[$i]})>0 then max({$ary_name[$i]})+1 else 1 end FROM {$table_name}));\n"; + } + } + } + + $this->flush("COPY $table_name (" . implode(', ', $ary_name) . ') FROM stdin;' . "\n"); + while ($row = $this->db->sql_fetchrow($result)) + { + $schema_vals = array(); + + // Build the SQL statement to recreate the data. + for ($i = 0; $i < $i_num_fields; $i++) + { + $str_val = $row[$ary_name[$i]]; + + if (preg_match('#char|text|bool|bytea#i', $ary_type[$i])) + { + $str_val = str_replace(array("\n", "\t", "\r", "\b", "\f", "\v"), array('\n', '\t', '\r', '\b', '\f', '\v'), addslashes($str_val)); + $str_empty = ''; + } + else + { + $str_empty = '\N'; + } + + if (empty($str_val) && $str_val !== '0') + { + $str_val = $str_empty; + } + + $schema_vals[] = $str_val; + } + + // Take the ordered fields and their associated data and build it + // into a valid sql statement to recreate that field in the data. + $this->flush(implode("\t", $schema_vals) . "\n"); + } + $this->db->sql_freeresult($result); + $this->flush("\\.\n"); + + // Write out the sequence statements + $this->flush($seq); + } + + /** + * Writes closing line(s) to database backup + * + * @return null + * @throws \phpbb\db\extractor\exception\extractor_not_initialized_exception when calling this function before init_extractor() + */ + public function write_end() + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + $this->flush("COMMIT;\n"); + parent::write_end(); + } +} diff --git a/phpBB/phpbb/db/extractor/sqlite3_extractor.php b/phpBB/phpbb/db/extractor/sqlite3_extractor.php new file mode 100644 index 0000000000..ce8da6a652 --- /dev/null +++ b/phpBB/phpbb/db/extractor/sqlite3_extractor.php @@ -0,0 +1,151 @@ +<?php +/** +* +* This file is part of the phpBB Forum Software package. +* +* @copyright (c) phpBB Limited <https://www.phpbb.com> +* @license GNU General Public License, version 2 (GPL-2.0) +* +* For full copyright and license information, please see +* the docs/CREDITS.txt file. +* +*/ + +namespace phpbb\db\extractor; + +use phpbb\db\extractor\exception\extractor_not_initialized_exception; + +class sqlite3_extractor extends base_extractor +{ + /** + * {@inheritdoc} + */ + public function write_start($table_prefix) + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + $sql_data = "--\n"; + $sql_data .= "-- phpBB Backup Script\n"; + $sql_data .= "-- Dump of tables for $table_prefix\n"; + $sql_data .= "-- DATE : " . gmdate("d-m-Y H:i:s", $this->time) . " GMT\n"; + $sql_data .= "--\n"; + $sql_data .= "BEGIN TRANSACTION;\n"; + $this->flush($sql_data); + } + + /** + * {@inheritdoc} + */ + public function write_table($table_name) + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + $sql_data = '-- Table: ' . $table_name . "\n"; + $sql_data .= "DROP TABLE $table_name;\n"; + + $sql = "SELECT sql + FROM sqlite_master + WHERE type = 'table' + AND name = '" . $this->db->sql_escape($table_name) . "' + ORDER BY name ASC;"; + $result = $this->db->sql_query($sql); + $row = $this->db->sql_fetchrow($result); + $this->db->sql_freeresult($result); + + // Create Table + $sql_data .= $row['sql'] . ";\n"; + + $result = $this->db->sql_query("PRAGMA index_list('" . $this->db->sql_escape($table_name) . "');"); + + while ($row = $this->db->sql_fetchrow($result)) + { + if (strpos($row['name'], 'autoindex') !== false) + { + continue; + } + + $result2 = $this->db->sql_query("PRAGMA index_info('" . $this->db->sql_escape($row['name']) . "');"); + + $fields = array(); + while ($row2 = $this->db->sql_fetchrow($result2)) + { + $fields[] = $row2['name']; + } + $this->db->sql_freeresult($result2); + + $sql_data .= 'CREATE ' . ($row['unique'] ? 'UNIQUE ' : '') . 'INDEX ' . $row['name'] . ' ON ' . $table_name . ' (' . implode(', ', $fields) . ");\n"; + } + $this->db->sql_freeresult($result); + + $this->flush($sql_data . "\n"); + } + + /** + * {@inheritdoc} + */ + public function write_data($table_name) + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + $result = $this->db->sql_query("PRAGMA table_info('" . $this->db->sql_escape($table_name) . "');"); + + $col_types = array(); + while ($row = $this->db->sql_fetchrow($result)) + { + $col_types[$row['name']] = $row['type']; + } + $this->db->sql_freeresult($result); + + $sql_insert = 'INSERT INTO ' . $table_name . ' (' . implode(', ', array_keys($col_types)) . ') VALUES ('; + + $sql = "SELECT * + FROM $table_name"; + $result = $this->db->sql_query($sql); + + while ($row = $this->db->sql_fetchrow($result)) + { + foreach ($row as $column_name => $column_data) + { + if (is_null($column_data)) + { + $row[$column_name] = 'NULL'; + } + else if ($column_data === '') + { + $row[$column_name] = "''"; + } + else if (stripos($col_types[$column_name], 'text') !== false || stripos($col_types[$column_name], 'char') !== false || stripos($col_types[$column_name], 'blob') !== false) + { + $row[$column_name] = sanitize_data_generic(str_replace("'", "''", $column_data)); + } + } + $this->flush($sql_insert . implode(', ', $row) . ");\n"); + } + } + + /** + * Writes closing line(s) to database backup + * + * @return null + * @throws \phpbb\db\extractor\exception\extractor_not_initialized_exception when calling this function before init_extractor() + */ + public function write_end() + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + $this->flush("COMMIT;\n"); + parent::write_end(); + } +} diff --git a/phpBB/phpbb/db/extractor/sqlite_extractor.php b/phpBB/phpbb/db/extractor/sqlite_extractor.php new file mode 100644 index 0000000000..2734e23235 --- /dev/null +++ b/phpBB/phpbb/db/extractor/sqlite_extractor.php @@ -0,0 +1,149 @@ +<?php +/** +* +* This file is part of the phpBB Forum Software package. +* +* @copyright (c) phpBB Limited <https://www.phpbb.com> +* @license GNU General Public License, version 2 (GPL-2.0) +* +* For full copyright and license information, please see +* the docs/CREDITS.txt file. +* +*/ + +namespace phpbb\db\extractor; + +use phpbb\db\extractor\exception\extractor_not_initialized_exception; + +class sqlite_extractor extends base_extractor +{ + /** + * {@inheritdoc} + */ + public function write_start($table_prefix) + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + $sql_data = "--\n"; + $sql_data .= "-- phpBB Backup Script\n"; + $sql_data .= "-- Dump of tables for $table_prefix\n"; + $sql_data .= "-- DATE : " . gmdate("d-m-Y H:i:s", $this->time) . " GMT\n"; + $sql_data .= "--\n"; + $sql_data .= "BEGIN TRANSACTION;\n"; + $this->flush($sql_data); + } + + /** + * {@inheritdoc} + */ + public function write_table($table_name) + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + $sql_data = '-- Table: ' . $table_name . "\n"; + $sql_data .= "DROP TABLE $table_name;\n"; + + $sql = "SELECT sql + FROM sqlite_master + WHERE type = 'table' + AND name = '" . $this->db->sql_escape($table_name) . "' + ORDER BY type DESC, name;"; + $result = $this->db->sql_query($sql); + $row = $this->db->sql_fetchrow($result); + $this->db->sql_freeresult($result); + + // Create Table + $sql_data .= $row['sql'] . ";\n"; + + $result = $this->db->sql_query("PRAGMA index_list('" . $this->db->sql_escape($table_name) . "');"); + + $ar = array(); + while ($row = $this->db->sql_fetchrow($result)) + { + $ar[] = $row; + } + $this->db->sql_freeresult($result); + + foreach ($ar as $value) + { + if (strpos($value['name'], 'autoindex') !== false) + { + continue; + } + + $result = $this->db->sql_query("PRAGMA index_info('" . $this->db->sql_escape($value['name']) . "');"); + + $fields = array(); + while ($row = $this->db->sql_fetchrow($result)) + { + $fields[] = $row['name']; + } + $this->db->sql_freeresult($result); + + $sql_data .= 'CREATE ' . ($value['unique'] ? 'UNIQUE ' : '') . 'INDEX ' . $value['name'] . ' on ' . $table_name . ' (' . implode(', ', $fields) . ");\n"; + } + + $this->flush($sql_data . "\n"); + } + + /** + * {@inheritdoc} + */ + public function write_data($table_name) + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + $col_types = sqlite_fetch_column_types($this->db->get_db_connect_id(), $table_name); + + $sql = "SELECT * + FROM $table_name"; + $result = sqlite_unbuffered_query($this->db->get_db_connect_id(), $sql); + $rows = sqlite_fetch_all($result, SQLITE_ASSOC); + $sql_insert = 'INSERT INTO ' . $table_name . ' (' . implode(', ', array_keys($col_types)) . ') VALUES ('; + foreach ($rows as $row) + { + foreach ($row as $column_name => $column_data) + { + if (is_null($column_data)) + { + $row[$column_name] = 'NULL'; + } + else if ($column_data == '') + { + $row[$column_name] = "''"; + } + else if (strpos($col_types[$column_name], 'text') !== false || strpos($col_types[$column_name], 'char') !== false || strpos($col_types[$column_name], 'blob') !== false) + { + $row[$column_name] = sanitize_data_generic(str_replace("'", "''", $column_data)); + } + } + $this->flush($sql_insert . implode(', ', $row) . ");\n"); + } + } + + /** + * Writes closing line(s) to database backup + * + * @return null + * @throws \phpbb\db\extractor\exception\extractor_not_initialized_exception when calling this function before init_extractor() + */ + public function write_end() + { + if (!$this->is_initialized) + { + throw new extractor_not_initialized_exception(); + } + + $this->flush("COMMIT;\n"); + parent::write_end(); + } +} |