From 5735c5176e65905f8814b935000247fc8a4a88d0 Mon Sep 17 00:00:00 2001 From: David M Date: Sun, 18 Jun 2006 15:35:32 +0000 Subject: fixes... git-svn-id: file:///svn/phpbb/trunk@6099 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/includes/acp/acp_database.php | 14 +++++++++++++- 1 file changed, 13 insertions(+), 1 deletion(-) (limited to 'phpBB/includes/acp/acp_database.php') diff --git a/phpBB/includes/acp/acp_database.php b/phpBB/includes/acp/acp_database.php index 3cdb09bb5e..6d5d97e454 100644 --- a/phpBB/includes/acp/acp_database.php +++ b/phpBB/includes/acp/acp_database.php @@ -135,6 +135,7 @@ class acp_database { case 'sqlite': $sql_data .= "BEGIN TRANSACTION;\n"; + $sqlite_version = sqlite_libversion(); break; case 'postgres': @@ -157,7 +158,6 @@ class acp_database case 'mysqli': case 'mysql4': case 'mysql': - case 'sqlite': $sql_data .= '# Table: ' . $table_name . "\n"; $sql_data .= "DROP TABLE IF EXISTS $table_name;\n"; break; @@ -167,6 +167,18 @@ class acp_database $sql_data .= "DROP TABLE $table_name;\n\\\n"; break; + case 'sqlite': + $sql_data .= '# Table: ' . $table_name . "\n"; + if (version_compare($sqlite_version, '3.0') == -1) + { + $sql_data .= "DROP TABLE $table_name;\n"; + } + else + { + $sql_data .= "DROP TABLE IF EXISTS $table_name;\n"; + } + break; + case 'postgres': case 'firebird': $sql_data .= '# Table: ' . $table_name . "\n"; -- cgit v1.2.1 From 9b0d2143bb5d8de42c10ac74cbbf81feafb7ecb4 Mon Sep 17 00:00:00 2001 From: David M Date: Tue, 20 Jun 2006 00:09:45 +0000 Subject: what? yeah... - turns out the backup issue was not a backup issue but a schema issue - let there be color git-svn-id: file:///svn/phpbb/trunk@6105 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/includes/acp/acp_database.php | 62 +++++++++++++++++++++---------------- 1 file changed, 35 insertions(+), 27 deletions(-) (limited to 'phpBB/includes/acp/acp_database.php') diff --git a/phpBB/includes/acp/acp_database.php b/phpBB/includes/acp/acp_database.php index 6d5d97e454..ed6f5e0d7b 100644 --- a/phpBB/includes/acp/acp_database.php +++ b/phpBB/includes/acp/acp_database.php @@ -23,7 +23,7 @@ class acp_database $user->add_lang('acp/database'); $this->tpl_name = 'acp_database'; - $this->page_title = 'ACP_DATABASE'; + $this->page_title = 'ACP_DATABasE'; $action = request_var('action', ''); $submit = (isset($_POST['submit'])) ? true : false; @@ -42,16 +42,16 @@ class acp_database $type = request_var('type', ''); $table = request_var('table', array('')); $format = request_var('method', ''); - $where = request_var('where', ''); + $WHERE = request_var('WHERE', ''); $store = $download = $structure = $schema_data = false; - if ($where == 'store_and_download' || $where == 'store') + if ($WHERE == 'store_and_download' || $WHERE == 'store') { $store = true; } - if ($where == 'store_and_download' || $where == 'download') + if ($WHERE == 'store_and_download' || $WHERE == 'download') { $download = true; } @@ -223,7 +223,8 @@ class acp_database { case 'mysqli': - $sql = "SELECT * FROM $table_name"; + $sql = "SELECT * + FROM $table_name"; $result = mysqli_query($db->db_connect_id, $sql, MYSQLI_USE_RESULT); if ($result != false) { @@ -290,7 +291,8 @@ class acp_database case 'mysql4': case 'mysql': - $sql = "SELECT * FROM $table_name"; + $sql = "SELECT * + FROM $table_name"; $result = mysql_unbuffered_query($sql, $db->db_connect_id); if ($result != false) @@ -361,7 +363,8 @@ class acp_database case 'sqlite': $col_types = sqlite_fetch_column_types($table_name, $db->db_connect_id); - $sql = "SELECT * FROM $table_name"; + $sql = "SELECT * + FROM $table_name"; $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) @@ -415,7 +418,7 @@ class acp_database $ary_type = $ary_name = array(); - // Grab all of the data from current table. + // Grab all of the data FROM current table. $sql = "SELECT * FROM {$table_name}"; $result = $db->sql_query($sql); @@ -440,7 +443,7 @@ class acp_database // 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"; + $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"; } } } @@ -536,7 +539,7 @@ class acp_database $ary_type = $ary_name = array(); $ident_set = false; - // Grab all of the data from current table. + // Grab all of the data FROM current table. $sql = "SELECT * FROM {$table_name}"; $result = $db->sql_query($sql); @@ -546,8 +549,8 @@ class acp_database if ($retrieved_data) { $sql = "SELECT 1 as has_identity - FROM INFORMATION_SCHEMA.COLUMNS - WHERE COLUMNPROPERTY(object_id('$table_name'), COLUMN_NAME, 'IsIdentity') = 1"; + 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'])) @@ -647,7 +650,7 @@ class acp_database $ary_type = $ary_name = array(); $ident_set = false; - // Grab all of the data from current table. + // Grab all of the data FROM current table. $sql = "SELECT * FROM {$table_name}"; $result = $db->sql_query($sql); @@ -665,8 +668,8 @@ class acp_database if ($retrieved_data) { $sql = "SELECT 1 as has_identity - FROM INFORMATION_SCHEMA.COLUMNS - WHERE COLUMNPROPERTY(object_id('$table_name'), COLUMN_NAME, 'IsIdentity') = 1"; + 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'])) @@ -758,7 +761,7 @@ class acp_database $ary_type = $ary_name = array(); - // Grab all of the data from current table. + // Grab all of the data FROM current table. $sql = "SELECT * FROM {$table_name}"; $result = $db->sql_query($sql); @@ -843,7 +846,7 @@ class acp_database case 'oracle': $ary_type = $ary_name = array(); - // Grab all of the data from current table. + // Grab all of the data FROM current table. $sql = "SELECT * FROM {$table_name}"; $result = $db->sql_query($sql); @@ -1017,7 +1020,7 @@ class acp_database case 'mssql_odbc': $sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES - WHERE TABLE_TYPE = 'BASE TABLE' + WHERE TABLE_TYPE = 'BasE TABLE' ORDER BY TABLE_NAME"; $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) @@ -1290,7 +1293,10 @@ class acp_database } $db->sql_freeresult($result); - $result = $db->sql_query("SHOW KEYS FROM $table_name"); + $sql = "SHOW KEYS + FROM $table_name"; + + $result = $db->sql_query($sql); $index = array(); while ($row = $db->sql_fetchrow($result)) @@ -1419,7 +1425,7 @@ class acp_database } $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 + $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 @@ -1432,7 +1438,7 @@ class acp_database $lines = array(); while ($row = $db->sql_fetchrow($result)) { - // Get the data from the table + // 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) . "') @@ -1489,7 +1495,7 @@ class acp_database // 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 + $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) @@ -1678,7 +1684,7 @@ class acp_database $sql_data .= "\nCREATE TABLE $table_name (\n"; - $sql = 'SELECT DISTINCT R.RDB$FIELD_NAME AS FNAME, R.RDB$NULL_FLAG AS NFLAG, R.RDB$DEFAULT_SOURCE AS DSOURCE, F.RDB$FIELD_TYPE AS FTYPE, F.RDB$FIELD_SUB_TYPE AS STYPE, F.RDB$FIELD_LENGTH AS FLEN + $sql = 'SELECT DISTINCT R.RDB$FIELD_NAME as FNAME, R.RDB$NULL_FLAG as NFLAG, R.RDB$DEFAULT_SOURCE as DSOURCE, F.RDB$FIELD_TYPE as FTYPE, F.RDB$FIELD_SUB_TYPE as STYPE, F.RDB$FIELD_LENGTH as FLEN FROM RDB$RELATION_FIELDS R JOIN RDB$FIELDS F ON R.RDB$FIELD_SOURCE=F.RDB$FIELD_NAME LEFT JOIN RDB$FIELD_DIMENSIONS D ON R.RDB$FIELD_SOURCE = D.RDB$FIELD_NAME @@ -1739,7 +1745,7 @@ class acp_database $db->sql_freeresult($result); - $sql = 'SELECT I.RDB$INDEX_NAME AS INAME, I.RDB$UNIQUE_FLAG AS UFLAG, S.RDB$FIELD_NAME AS FNAME + $sql = 'SELECT I.RDB$INDEX_NAME as INAME, I.RDB$UNIQUE_FLAG as UFLAG, S.RDB$FIELD_NAME as FNAME FROM RDB$INDICES I JOIN RDB$INDEX_SEGMENTS S ON S.RDB$INDEX_NAME=I.RDB$INDEX_NAME WHERE (I.RDB$SYSTEM_FLAG IS NULL OR I.RDB$SYSTEM_FLAG=0) AND I.RDB$FOREIGN_KEY IS NULL @@ -1783,7 +1789,7 @@ class acp_database $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) { - $sql = 'SELECT T1.RDB$DEPENDED_ON_NAME AS GEN, T1.RDB$FIELD_NAME, T1.RDB$DEPENDED_ON_TYPE + $sql = 'SELECT T1.RDB$DEPENDED_ON_NAME as GEN, T1.RDB$FIELD_NAME, T1.RDB$DEPENDED_ON_TYPE FROM RDB$DEPENDENCIES T1 WHERE (T1.RDB$DEPENDENT_NAME = \'' . $row['dname'] . '\') AND (T1.RDB$DEPENDENT_TYPE = 2 AND T1.RDB$DEPENDED_ON_TYPE = 14) @@ -1801,7 +1807,7 @@ class acp_database $sql_data .= "\nCREATE GENERATOR " . $gen_name . ";;"; $sql_data .= "\nSET GENERATOR " . $gen_name . " TO 0;;\n"; $sql_data .= "\nCREATE TRIGGER {$row['dname']} FOR $table_name"; - $sql_data .= "\nBEFORE INSERT\nAS\nBEGIN"; + $sql_data .= "\nBEFORE INSERT\nas\nBEGIN"; $sql_data .= "\n NEW.{$row['fname']} = GEN_ID(" . $gen_name . ", 1);"; $sql_data .= "\nEND;;\n"; } @@ -1812,7 +1818,9 @@ class acp_database case 'oracle': $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}'"; + $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(); -- cgit v1.2.1 From c125ae12761e99a0f11703bbbec0defb29ad90c4 Mon Sep 17 00:00:00 2001 From: David M Date: Tue, 20 Jun 2006 00:21:16 +0000 Subject: oops? git-svn-id: file:///svn/phpbb/trunk@6106 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/includes/acp/acp_database.php | 24 ++++++++++++------------ 1 file changed, 12 insertions(+), 12 deletions(-) (limited to 'phpBB/includes/acp/acp_database.php') diff --git a/phpBB/includes/acp/acp_database.php b/phpBB/includes/acp/acp_database.php index ed6f5e0d7b..fbe4f78aca 100644 --- a/phpBB/includes/acp/acp_database.php +++ b/phpBB/includes/acp/acp_database.php @@ -23,7 +23,7 @@ class acp_database $user->add_lang('acp/database'); $this->tpl_name = 'acp_database'; - $this->page_title = 'ACP_DATABasE'; + $this->page_title = 'ACP_DATABASE'; $action = request_var('action', ''); $submit = (isset($_POST['submit'])) ? true : false; @@ -42,16 +42,16 @@ class acp_database $type = request_var('type', ''); $table = request_var('table', array('')); $format = request_var('method', ''); - $WHERE = request_var('WHERE', ''); + $where = request_var('WHERE', ''); $store = $download = $structure = $schema_data = false; - if ($WHERE == 'store_and_download' || $WHERE == 'store') + if ($where == 'store_and_download' || $where == 'store') { $store = true; } - if ($WHERE == 'store_and_download' || $WHERE == 'download') + if ($where == 'store_and_download' || $where == 'download') { $download = true; } @@ -418,7 +418,7 @@ class acp_database $ary_type = $ary_name = array(); - // Grab all of the data FROM current table. + // Grab all of the data from current table. $sql = "SELECT * FROM {$table_name}"; $result = $db->sql_query($sql); @@ -539,7 +539,7 @@ class acp_database $ary_type = $ary_name = array(); $ident_set = false; - // Grab all of the data FROM current table. + // Grab all of the data from current table. $sql = "SELECT * FROM {$table_name}"; $result = $db->sql_query($sql); @@ -650,7 +650,7 @@ class acp_database $ary_type = $ary_name = array(); $ident_set = false; - // Grab all of the data FROM current table. + // Grab all of the data from current table. $sql = "SELECT * FROM {$table_name}"; $result = $db->sql_query($sql); @@ -761,7 +761,7 @@ class acp_database $ary_type = $ary_name = array(); - // Grab all of the data FROM current table. + // Grab all of the data from current table. $sql = "SELECT * FROM {$table_name}"; $result = $db->sql_query($sql); @@ -846,7 +846,7 @@ class acp_database case 'oracle': $ary_type = $ary_name = array(); - // Grab all of the data FROM current table. + // Grab all of the data from current table. $sql = "SELECT * FROM {$table_name}"; $result = $db->sql_query($sql); @@ -1020,7 +1020,7 @@ class acp_database case 'mssql_odbc': $sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES - WHERE TABLE_TYPE = 'BasE TABLE' + WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME"; $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) @@ -1438,7 +1438,7 @@ class acp_database $lines = array(); while ($row = $db->sql_fetchrow($result)) { - // Get the data FROM the table + // 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) . "') @@ -1807,7 +1807,7 @@ class acp_database $sql_data .= "\nCREATE GENERATOR " . $gen_name . ";;"; $sql_data .= "\nSET GENERATOR " . $gen_name . " TO 0;;\n"; $sql_data .= "\nCREATE TRIGGER {$row['dname']} FOR $table_name"; - $sql_data .= "\nBEFORE INSERT\nas\nBEGIN"; + $sql_data .= "\nBEFORE INSERT\nAS\nBEGIN"; $sql_data .= "\n NEW.{$row['fname']} = GEN_ID(" . $gen_name . ", 1);"; $sql_data .= "\nEND;;\n"; } -- cgit v1.2.1 From 3efe7ffb1fed7fbc858699a13e2bdf28c68badd3 Mon Sep 17 00:00:00 2001 From: David M Date: Sat, 1 Jul 2006 06:30:49 +0000 Subject: bugs? bugs. git-svn-id: file:///svn/phpbb/trunk@6133 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/includes/acp/acp_database.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'phpBB/includes/acp/acp_database.php') diff --git a/phpBB/includes/acp/acp_database.php b/phpBB/includes/acp/acp_database.php index fbe4f78aca..5d023d6205 100644 --- a/phpBB/includes/acp/acp_database.php +++ b/phpBB/includes/acp/acp_database.php @@ -42,7 +42,7 @@ class acp_database $type = request_var('type', ''); $table = request_var('table', array('')); $format = request_var('method', ''); - $where = request_var('WHERE', ''); + $where = request_var('where', ''); $store = $download = $structure = $schema_data = false; -- cgit v1.2.1 From 662e12d466ef95a8d0c90869e5009a92ce197756 Mon Sep 17 00:00:00 2001 From: David M Date: Mon, 3 Jul 2006 00:21:40 +0000 Subject: - Dramatic speed-up in SQLite backup code - Wrote *another* patch around SQLite - SQLite has a lack of (among other things) solid definition of their tables. e.g. "foo" and foo are both valid col names... Database backup and profile creation are now both aware of such "features" meh, i hope this all works... git-svn-id: file:///svn/phpbb/trunk@6140 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/includes/acp/acp_database.php | 41 ++++++++++++++++++++++++++++++++++--- 1 file changed, 38 insertions(+), 3 deletions(-) (limited to 'phpBB/includes/acp/acp_database.php') diff --git a/phpBB/includes/acp/acp_database.php b/phpBB/includes/acp/acp_database.php index 5d023d6205..1429719df2 100644 --- a/phpBB/includes/acp/acp_database.php +++ b/phpBB/includes/acp/acp_database.php @@ -361,13 +361,48 @@ class acp_database break; case 'sqlite': + // This is *not* my fault. The PHP guys forgot a call to finalize when they wrote this function. This forces all the tables to stay locked... + // They finally fixed it in 5.3 but 5.2 still have this so instead, we go and grab the column types by smashing open the sqlite_master table + // and grope around for things that remind us of datatypes... + if (version_compare(phpversion(), '5.3', '>=')) + { + $col_types = sqlite_fetch_column_types($table_name, $db->db_connect_id); + } + else + { + $sql = "SELECT sql + FROM sqlite_master + WHERE type = 'table' + AND name = '" . $table_name . "'"; + $table_data = sqlite_single_query($db->db_connect_id, $sql); + $table_data = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', '', $table_data); + $table_data = trim($table_data); + + preg_match('#\((.*)\)#s', $table_data, $matches); + + $column_list = array(); + $table_cols = explode(',', trim($matches[1])); + foreach($table_cols as $declaration) + { + $entities = preg_split('#\s+#', trim($declaration)); + $column_name = preg_replace('/"?([^"]+)"?/', '\1', $entities[0]); + + // Hit a primary key, those are not what we need :D + if (empty($entities[1])) + { + continue; + } + $col_types[$column_name] = $entities[1]; + } + } - $col_types = sqlite_fetch_column_types($table_name, $db->db_connect_id); + // Unbueffered query and the foreach make this ultra fast, we wait for nothing. $sql = "SELECT * FROM $table_name"; - $result = $db->sql_query($sql); + $result = sqlite_unbuffered_query($sql, $db->db_connect_id); + $rows = sqlite_fetch_all($result, SQLITE_ASSOC); - while ($row = $db->sql_fetchrow($result)) + foreach ($rows as $row) { $names = $data = array(); foreach ($row as $row_name => $row_data) -- cgit v1.2.1 From 1b48afeb30d9486945ea2ca8c1881c044947add5 Mon Sep 17 00:00:00 2001 From: David M Date: Mon, 3 Jul 2006 00:53:54 +0000 Subject: - Database: eh? meh. - CAPTCHA: A little easier to see which options map to which controls git-svn-id: file:///svn/phpbb/trunk@6141 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/includes/acp/acp_database.php | 14 +++++++------- 1 file changed, 7 insertions(+), 7 deletions(-) (limited to 'phpBB/includes/acp/acp_database.php') diff --git a/phpBB/includes/acp/acp_database.php b/phpBB/includes/acp/acp_database.php index 1429719df2..2ad28cc9fa 100644 --- a/phpBB/includes/acp/acp_database.php +++ b/phpBB/includes/acp/acp_database.php @@ -362,9 +362,9 @@ class acp_database case 'sqlite': // This is *not* my fault. The PHP guys forgot a call to finalize when they wrote this function. This forces all the tables to stay locked... - // They finally fixed it in 5.3 but 5.2 still have this so instead, we go and grab the column types by smashing open the sqlite_master table + // They finally fixed it in 5.1.3 but 5.1.2 and under still have this so instead, we go and grab the column types by smashing open the sqlite_master table // and grope around for things that remind us of datatypes... - if (version_compare(phpversion(), '5.3', '>=')) + if (version_compare(phpversion(), '5.1.3', '>=')) { $col_types = sqlite_fetch_column_types($table_name, $db->db_connect_id); } @@ -455,7 +455,7 @@ class acp_database // Grab all of the data from current table. $sql = "SELECT * - FROM {$table_name}"; + FROM $table_name"; $result = $db->sql_query($sql); $i_num_fields = pg_num_fields($result); @@ -576,7 +576,7 @@ class acp_database // Grab all of the data from current table. $sql = "SELECT * - FROM {$table_name}"; + FROM $table_name"; $result = $db->sql_query($sql); $retrieved_data = odbc_num_rows($result); @@ -687,7 +687,7 @@ class acp_database // Grab all of the data from current table. $sql = "SELECT * - FROM {$table_name}"; + FROM $table_name"; $result = $db->sql_query($sql); $retrieved_data = mssql_num_rows($result); @@ -798,7 +798,7 @@ class acp_database // Grab all of the data from current table. $sql = "SELECT * - FROM {$table_name}"; + FROM $table_name"; $result = $db->sql_query($sql); $i_num_fields = ibase_num_fields($result); @@ -883,7 +883,7 @@ class acp_database // Grab all of the data from current table. $sql = "SELECT * - FROM {$table_name}"; + FROM $table_name"; $result = $db->sql_query($sql); $i_num_fields = ocinumcols($result); -- cgit v1.2.1 From 6a1f1f57213c69219850785cbca3b67e782a2cca Mon Sep 17 00:00:00 2001 From: David M Date: Tue, 4 Jul 2006 03:54:41 +0000 Subject: - CAPTCHA: removed the extra slash More backup stuff - Made some things nicer for some of the DBs - Made postgreSQL work on non empty databases - Made SQLite ultra fast on restore - Properly escaped (as far as I know) the profile data fields so that one may now use reserved words as column names git-svn-id: file:///svn/phpbb/trunk@6144 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/includes/acp/acp_database.php | 88 +++++++++++++++++++++---------------- 1 file changed, 49 insertions(+), 39 deletions(-) (limited to 'phpBB/includes/acp/acp_database.php') diff --git a/phpBB/includes/acp/acp_database.php b/phpBB/includes/acp/acp_database.php index 2ad28cc9fa..be1bcc3ecd 100644 --- a/phpBB/includes/acp/acp_database.php +++ b/phpBB/includes/acp/acp_database.php @@ -366,7 +366,7 @@ class acp_database // and grope around for things that remind us of datatypes... if (version_compare(phpversion(), '5.1.3', '>=')) { - $col_types = sqlite_fetch_column_types($table_name, $db->db_connect_id); + $col_types = sqlite_fetch_column_types($db->db_connect_id, $table_name); } else { @@ -399,7 +399,7 @@ class acp_database // Unbueffered query and the foreach make this ultra fast, we wait for nothing. $sql = "SELECT * FROM $table_name"; - $result = sqlite_unbuffered_query($sql, $db->db_connect_id); + $result = sqlite_unbuffered_query($db->db_connect_id, $sql); $rows = sqlite_fetch_all($result, SQLITE_ASSOC); foreach ($rows as $row) @@ -471,12 +471,12 @@ class acp_database FROM pg_attrdef d, pg_class c WHERE (c.relname = '{$table_name}') AND (c.oid = d.adrelid) - AND d.adnum = " . strval($i+1); + 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) + 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"; } @@ -526,7 +526,7 @@ class acp_database // 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"; + $sql_data .= "INSERT INTO $table_name (" . implode(', ', $schema_fields) . ') VALUES (' . implode(', ', $schema_vals) . ");\n"; if ($store == true) { @@ -647,7 +647,7 @@ class acp_database // 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"; + $sql_data .= "INSERT INTO $table_name (" . implode(', ', $schema_fields) . ') VALUES (' . implode(', ', $schema_vals) . ");\n"; if ($store == true) { @@ -758,7 +758,7 @@ class acp_database // 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"; + $sql_data .= "INSERT INTO $table_name (" . implode(', ', $schema_fields) . ') VALUES (' . implode(', ', $schema_vals) . ");\n"; if ($store == true) { @@ -853,7 +853,7 @@ class acp_database // 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"; + $sql_data .= "INSERT INTO $table_name (" . implode(', ', $schema_fields) . ') VALUES (' . implode(', ', $schema_vals) . ");\n"; if ($store == true) { @@ -937,7 +937,7 @@ class acp_database // 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"; + $sql_data .= "INSERT INTO $table_name (" . implode(', ', $schema_fields) . ') VALUES (' . implode(', ', $schema_vals) . ");\n"; if ($store == true) { @@ -1037,9 +1037,9 @@ class acp_database break; case 'postgres': - $sql = "SELECT relname + $sql = 'SELECT relname FROM pg_stat_user_tables - ORDER BY relname;"; + ORDER BY relname'; $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) { @@ -1194,39 +1194,47 @@ class acp_database { // Strip out sql comments... remove_remarks($data); - switch (SQL_LAYER) - { - case 'firebird': - $delim = ';;'; - break; - case 'mysql': - case 'mysql4': - case 'mysqli': - case 'sqlite': - case 'postgres': - $delim = ';'; - break; + // SQLite gets improved performance when you shove all of these disk write queries at once :D + if (SQL_LAYER == 'sqlite') + { + $db->sql_query($data); + } + else + { + switch (SQL_LAYER) + { + case 'firebird': + $delim = ';;'; + break; - case 'oracle': - $delim = '/'; - break; + case 'mysql': + case 'mysql4': + case 'mysqli': + case 'postgres': + $delim = ';'; + break; - case 'mssql': - case 'mssql-odbc': - $delim = 'GO'; - break; - } - $pieces = split_sql_file($data, $delim); + case 'oracle': + $delim = '/'; + break; - $sql_count = count($pieces); - for($i = 0; $i < $sql_count; $i++) - { - $sql = trim($pieces[$i]); + case 'mssql': + case 'mssql-odbc': + $delim = 'GO'; + break; + } + $pieces = split_sql_file($data, $delim); - if (!empty($sql) && $sql[0] != '#') + $sql_count = count($pieces); + for($i = 0; $i < $sql_count; $i++) { - $db->sql_query($sql); + $sql = trim($pieces[$i]); + + if (!empty($sql) && $sql[0] != '#') + { + $db->sql_query($sql); + } } } } @@ -1456,6 +1464,7 @@ class acp_database // 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); @@ -1539,7 +1548,8 @@ class acp_database 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 "; + ORDER BY index_name, tab_name, column_name"; + $result = $db->sql_query($sql_pri_keys); $index_create = $index_rows = $primary_key = array(); -- cgit v1.2.1 From 291ab6216e4b4832fd49c237b933cc1abf83e588 Mon Sep 17 00:00:00 2001 From: David M Date: Tue, 4 Jul 2006 04:08:30 +0000 Subject: - Make sure that the table is empty before we start cramming things inside :D git-svn-id: file:///svn/phpbb/trunk@6145 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/includes/acp/acp_database.php | 30 +++++++++++++++++++++++++++--- 1 file changed, 27 insertions(+), 3 deletions(-) (limited to 'phpBB/includes/acp/acp_database.php') diff --git a/phpBB/includes/acp/acp_database.php b/phpBB/includes/acp/acp_database.php index be1bcc3ecd..52a9732e82 100644 --- a/phpBB/includes/acp/acp_database.php +++ b/phpBB/includes/acp/acp_database.php @@ -144,7 +144,8 @@ class acp_database case 'mssql': case 'mssql_odbc': - $sql_data .= "BEGIN TRANSACTION\nGO\n"; + $sql_data .= "BEGIN TRANSACTION\n"; + $sql_data .= "GO\n"; break; } @@ -164,7 +165,8 @@ class acp_database case 'oracle': $sql_data .= '# Table: ' . $table_name . "\n"; - $sql_data .= "DROP TABLE $table_name;\n\\\n"; + $sql_data .= "DROP TABLE $table_name;\n"; + $sql_data .= '\\' . "\n"; break; case 'sqlite': @@ -189,11 +191,33 @@ class acp_database case 'mssql_odbc': $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;\nGO\n"; + $sql_data .= "DROP TABLE $table_name;\n"; + $sql_data .= "GO\n"; break; } $sql_data .= $this->get_table_structure($table_name); } + // We might wanna empty out all that junk :D + else + { + switch (SQL_LAYER) + { + case 'mysqli': + case 'mysql4': + case 'mysql': + case 'mssql': + case 'mssql_odbc': + case 'oracle': + case 'postgres': + case 'firebird': + $sql_data .= 'TRUNCATE TABLE ' . $table_name . "\n"; + break; + + case 'sqlite': + $sql_data .= 'DELETE FROM ' . $table_name . "\n"; + break; + } + } // Now write the data for the first time. :) if ($store == true) { -- cgit v1.2.1 From 9532514c2a566437a9524af1dfca298da58fd40a Mon Sep 17 00:00:00 2001 From: David M Date: Mon, 24 Jul 2006 10:08:36 +0000 Subject: OK... This commit should increase the total number of BBCodes from 31 to 2040. Some things to watch out for: Each database likes to deal with binary data in its own, special way. They are, quite frankly, too cool for school. MySQL, MSSQL and Oracle all allow me to send in a default value for their binary column using a hex number. However, MSSQL forces me to send the specific data as a hex number and thus we must CAST it. PostgreSQL allows me to set a binary column, but with a twist. It demands that the default be in _octal_ and its datatype allows somewhere around a gigabyte's worth of BBCodes ( PGSQL users, we shut you down to 2040 for your own good! ) Firebird has no decent mechanism for allowing me to shuttle in binary data so I must force my way in. By virtue of triggers and a UDF, we ram in our default values. SQLite is the most bizarre of them all. They have no mechanism for turning an ASCII code into a ASCII character. Because of this, we have a trigger and a UDF (just like Firebird!) but with a twist! The UDF is defined on the PHP side of things instead of SQL. SQLite also demands that it's data be encoded before being sent off. Other notes: - SQLite installs again :D - Firebird nearly installs again :P - Database backup is not screwed up :P P.S. I hope nothing broke :D git-svn-id: file:///svn/phpbb/trunk@6209 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/includes/acp/acp_database.php | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'phpBB/includes/acp/acp_database.php') diff --git a/phpBB/includes/acp/acp_database.php b/phpBB/includes/acp/acp_database.php index 52a9732e82..7fd959b104 100644 --- a/phpBB/includes/acp/acp_database.php +++ b/phpBB/includes/acp/acp_database.php @@ -210,11 +210,11 @@ class acp_database case 'oracle': case 'postgres': case 'firebird': - $sql_data .= 'TRUNCATE TABLE ' . $table_name . "\n"; + $sql_data .= 'TRUNCATE TABLE ' . $table_name . ";\n"; break; case 'sqlite': - $sql_data .= 'DELETE FROM ' . $table_name . "\n"; + $sql_data .= 'DELETE FROM ' . $table_name . ";\n"; break; } } @@ -1686,7 +1686,7 @@ class acp_database if ($row['COLUMN_DEFAULT']) { - $line .= ' CONSTRAINT [DF_' . $table_name . '_' . $row['COLUMN_NAME'] . '] DEFAULT ' . $row['COLUMN_DEFAULT']; + $line .= ' DEFAULT ' . $row['COLUMN_DEFAULT']; } $rows[] = $line; -- cgit v1.2.1 From 7c01c5ceb967d891a27341796f404370baad5c62 Mon Sep 17 00:00:00 2001 From: Nils Adermann Date: Sun, 6 Aug 2006 18:47:11 +0000 Subject: Backup files are now named backup_[timestamp].sql.* git-svn-id: file:///svn/phpbb/trunk@6245 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/includes/acp/acp_database.php | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'phpBB/includes/acp/acp_database.php') diff --git a/phpBB/includes/acp/acp_database.php b/phpBB/includes/acp/acp_database.php index 7fd959b104..59ccb342fd 100644 --- a/phpBB/includes/acp/acp_database.php +++ b/phpBB/includes/acp/acp_database.php @@ -68,7 +68,7 @@ class acp_database @set_time_limit(1200); - $filename = time(); + $filename = 'backup_' . time(); // We set up the info needed for our on-the-fly creation :D switch ($format) @@ -1162,7 +1162,7 @@ class acp_database $delete = request_var('delete', ''); $file = request_var('file', ''); - preg_match('#^(\d{10})\.(sql(?:\.(?:gz|bz2))?)$#', $file, $matches); + preg_match('#^(backup_\d{10,})\.(sql(?:\.(?:gz|bz2))?)$#', $file, $matches); $file_name = $phpbb_root_path . 'store/' . $matches[0]; if (!(file_exists($file_name) && is_readable($file_name))) @@ -1284,7 +1284,7 @@ class acp_database $dh = opendir($dir); while (($file = readdir($dh)) !== false) { - if (preg_match('#^(\d{10})\.(sql(?:\.(?:gz|bz2))?)$#', $file, $matches)) + if (preg_match('#^backup_(\d{10,})\.(sql(?:\.(?:gz|bz2))?)$#', $file, $matches)) { $supported = in_array($matches[2], $methods); -- cgit v1.2.1