1 files changed, 293 insertions, 17 deletions
diff --git a/phpBB/admin/db_utilities.php b/phpBB/admin/db_utilities.php
index 31f4b19c8c..a4886b19ee 100644
--- a/phpBB/admin/db_utilities.php
+++ b/phpBB/admin/db_utilities.php
@@ -72,14 +72,218 @@ function common_footer()
 // The following functions are adapted from phpMyAdmin and upgrade_20.php
 //
 //
-//	This fuction will return a talbes create definition to be used as an sql
-// statement.
+// This function is used for grabbing the sequences for postgres...
+function pg_get_sequences($db, $crlf, $backup_type)
+{
+	$get_seq_sql = "SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*' ";
+	$get_seq_sql .="AND relkind = 'S' ORDER BY relname";
+	$seq = $db->sql_query($get_seq_sql);
+	if(!$num_seq = $db->sql_numrows($seq)) {
+		$return_val = "# No Sequences Found $crlf";
+	} // End if...
+	else
+	{
+		$return_val = "# Sequences $crlf";
+		$i_seq = 0;
+		while($i_seq < $num_seq)
+		{
+			$row = sql_fetchrow($seq);
+			$sequence = $row['relname'];
+			$get_props_sql = "SELECT * FROM $sequence";
+			$seq_props = $db->sql_query($get_props_sql);
+			if($db->sql_numrows($seq_props) > 0)
+			{
+				$row1 = $db->sql_fetchrow($seq_props);
+				if($backup_type == 'structure')
+				{
+					$row['last_value'] = 1;
+				} 
+				$return_val .= "CREATE SEQUENCE $sequence start " . $row['last_value'] . ' increment ' . $row['increment_by'] . ' maxvalue ' . $row['max_value'] . ' minvalue ' . $row['min_value'] . ' cache ' . $row['cache_value'] . "; $crlf";
+			}  // End if numrows > 0
+			if(($row['last_value'] > 1) && ($backup_type != 'structure'))
+			{
+				$return_val .= "SELECT NEXTVALE('$sequence'); $crlf";
+				unset($row['last_value']);
+			}
+			$i_seq++;
+		} // End while..
+	} // End else...
+	return $returnval;
+} // End function...
+
+// The following functions will return the "CREATE TABLE syntax for the 
+// varying DBMS's 
+
+// This function returns, will return the table def's for postgres...
+function get_table_def_postgres($db, $table, $crlf)
+{
+	global $drop;
+	$schema_create = "";
+	$field_query = "
+						SELECT a.attnum,
+							a.attname AS field,
+							t.typename 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 = '$table'
+							AND a.attnum > 0
+							AND a.attrelid = c.oid
+							AND a.attypid = t.oid
+						ORDER BY a.attnum";
+	$result = $db->sql_query($field_query);
+	if(!$result)
+	{
+		$error = $db->sql_error();
+		error_die(GENERAL_ERROR, 'Failed in get_table_def (show fields) : ' . $error['message']);
+	} // end if..
+	if ($drop == 1)
+	{
+		$schema_create .= "DROP TABLE $table;$crlf";
+	} // end if
+	$schema_create .= "CREATE TABLE $table($crlf";
+	while ($row = $db->sql_fetchrow($result))
+	{
+		$sql_get_default = "SELECT d.adsrc AS rowdefault
+									FROM pg_attrdef d, pg_class c
+									WHERE (c.relname = '$table') AND (c.oid = d.adrelid) AND d.adnum = " . $row['attnum'];
+		$def_res = $db->sql_query($sql_get_default);
+		if (!$def_res)
+		{
+			unset($row['rowdefault']);
+		} // end if
+		else 
+		{
+			$row['rowdefault'] = @pg_result($def_res, 0, 'rowdefault');
+		} // end else
+		if ($row['type'] == 'bpchar')
+		{
+			// Internally stored as bpchar, but isn't accepted in a CREATE TABLE statement.
+			$row['type'] = 'char';
+		} // end if
+		$schema_create .= '	' . $row['field'] . ' ' . $row['type'];
+		if (eregi('char', $row['type']))
+		{
+			if ($row['lengthvar'] > 0)
+			{
+				$schema_create .= '(' . ($row['lengthvar'] -4) . ')';
+			} // end if($row['lenghvar']...
+		} // end if(eregi('char'...
+		if (eregi('numeric', $row['type']))
+		{
+			$schema_create .= '(';
+			$schema_create .= sprintf("%s,%s", (($row['lengthvar'] >> 16) & 0xffff), (($row['lengthvar'] - 4) & 0xffff));
+			$schema_create .= ')';
+		} // end if(eregi('numeric' ...
+		if (!empty($row['rowdefault']))
+		{
+			$schema_create .= ' DEFAULT ' . $row['rowdefault'];
+		} // end if(!empty...
+		if ($row['notnull'] == 't')
+		{
+			$schema_create .= ' NOT NULL';
+		} // end if($row['notnul'] ...
+		$schema_create .= ", $crlf";
+	} //end while loop
+	$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 = '$table') 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);
+	if(!$result)
+	{
+		$error = $db->sql_error();
+		error_die(GENERAL_ERROR, 'Failed in get_table_def (show fields) : ' . $error['message']);
+	} // end if..
+	while ( $row = $db->sql_fetchrow($result))
+	{
+		if ($row['primary_key'] == 't')
+		{
+			if (!empty($primary_key))
+			{
+				$primary_key .= ', ';
+			} // end if(!empty...
+			$primary_key .= $row['column_name'];
+			$primary_key_name = $row['index_name'];
+		} // end if($row['primary_key'] ...
+		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;
+			$index_rows[$row['index_name']]['unique'] = ($row['unique_key'] == 't') ? ' UNIQUE ' : '';
+			$index_rows[$row['index_name']]['column_names'] .= $row['column_name'] . ', ';
+		} // end else..
+	} // end while loop
+	if (!empty($index_rows))
+	{
+		while(list($idx_name, $props) = each($index_rows))
+		{
+			$props['column_names'] = ereg_replace(", $", "" , $props['column_name']);
+			$index_create .= 'CREATE ' . $props['unique'] . " INDEX $idx_name ON $table (" . $props['column_names'] . ");$crlf";
+		} // end while loop
+	} // end if(!empty($index_rows))
+	if (!empty($primary_key))
+	{
+		$schema_create .= "	CONSTRAINT $primary_key_name PRIMARY KEY ($primary_key),$crlf";
+	} // end if(!empty($primary_key)) ..
+	// Generate constraint clauses for CHECK constraints
+	$sql_checks = "
+		SELECT 
+			rcname as index_name, 
+			rcsrc 
+		FROM 
+			pg_relcheck,
+			pg_class bc
+		WHERE 
+			rcrelid = bc.oid 
+			and bc.relname = '$table'
+			and not exists 
+			(select * from pg_relcheck as c, pg_inherits as i 
+			where i.inhrelid = pg_relcheck.rcrelid 
+			and c.rcname = pg_relcheck.rcname 
+			and c.rcsrc = pg_relcheck.rcsrc 
+			and c.rcrelid = i.inhparent)
+	";
+	$result = $db->sql_query($sql_checks);
+	if (!$result)
+	{
+		$error = $db->sql_error();
+		error_die(GENERAL_ERROR, 'Failed in get_table_def (show fields) : ' . $error['message']);
+	} // end if(!$result)...
+	while ($row = $db->sql_fetchrow($result))
+	{
+		$schema_create .= '	CONSTRAINT ' . $row['index_name'] . ' CHECK ' . $row['rcsrc'] . ",$crlf";
+	} // end while loop
+	$schema_create = ereg_replace(',' . $crlf . '$', '', $schema_create);
+	$index_create = ereg_replace(',' . $crlf . '$', '', $index_create);
 
-function get_table_def($db, $table, $crlf) 
+	$schema_create .= "$crlf);$crlf";
+	if (!empty($index_create))
+	{
+		$schema_create .= $index_create;
+	} // end if(!empty($index_create))...
+	return (stripslashes($schema_create));
+} // end function get_table_def_postgres()
+
+// This function returns the "CREATE TABLE" syntax for mysql dbms...
+function get_table_def_mysql($db, $table, $crlf) 
 {
 	global $drop;
 	$schema_create = "";
-	// If the user has selected to drop existing tables when doing a restore.
+	$field_query = "SHOW FIELDS FROM $table";
+	$key_query = "SHOW KEYS FROM $table";
+// If the user has selected to drop existing tables when doing a restore.
 	// Then we add the statement to drop the tables....
 	if ($drop == 1)
 	{
@@ -89,13 +293,6 @@ function get_table_def($db, $table, $crlf)
 	$schema_create .= "CREATE TABLE $table($crlf";
 
 	// Ok lets grab the fields...
-	switch(DBMS) 
-	{
-		case 'mysql':
-			$field_query = "SHOW FIELDS FROM $table";
-			$key_query = "SHOW KEYS FROM $table";
-			break;
-	}
 	$result = $db->sql_query($field_query);
 	if(!result)
 	{
@@ -166,14 +363,86 @@ function get_table_def($db, $table, $crlf)
 	{
 		return($schema_create);
 	}
-}
+	
+} // End get_table_def_mysql
+
+
+//	This fuction will return a tables create definition to be used as an sql
+// statement.
 
 //
-// Get the data from the tables and format it as a series of INSERT statements.
+// The following functions Get the data from the tables and format it as a 
+// series of INSERT statements, for each different DBMS...
 // After every row a custom callback function $handler gets called.
 // $handler must accept one parameter ($sql_insert);
 //
-function get_table_content($db, $table, $handler)
+
+// Here is the function for postgres...
+
+function get_table_content_postgres($db, $table, $handler)
+{
+	$result = $db->sql_query("SELECT * FROM $table");
+	if (!$result)
+	{
+		$error = $db->sql_error();
+		error_die(GENERAL_ERROR, 'Faild in get_table_content (select *): ' . $error['message']);
+	} // end if(!$result)...
+	$i_num_fields = $db->sql_numfields($result);
+	for ($i = 0; $i < $i_num_fields; $i++)
+	{
+		$aryType[] = $db->sql_fieldtype($i, $result);
+		$aryName[] = $db->sql_fieldname($i, $result);
+	} // end for loop...
+	$iRec = 0;
+	while($row = $db->fetchrow($result))
+	{
+		unset($schema_vals);
+		unset($schema_fields);
+		unset($schema_insert);
+		for($i = 0; $i < $i_num_fields; $i++)
+		{
+			$strVal = $row[$aryName[$i]];
+			if (eregi("char|text|bool", $aryType[$i]))
+			{
+				$strQuote = "'";
+				$strEmpty = "";
+				$strVal = addslashes($strVal);
+			} // end if..
+			elseif (eregi("date|timestamp", $aryType[$i]))
+			{
+				if ($empty($strVal))
+				{
+					$strQuote = "";
+				}
+				else
+				{
+					$strQuote = "'";
+				}
+			} // end elseif ...
+			else
+			{
+				$strQuote = "";
+				$strEmpty = "NULL";
+			} // end else...
+			if (empty($strVal) && $strVal != "0")
+			{
+				$strVal = $strEmpty;
+			}
+			$schema_vals .= " $strQuote$strVal$strQuote,";
+			$schema_fields .= " $aryName[$i],";			
+		} // end for loop ..
+		$schema_vals = ereg_replace(",$", "", $schema_vals);
+		$schema_vals = ereg_replace("^ ", "", $schema_vals);
+		$schema_fields = ereg_replace(",$", "", $schema_fields);
+		$schema_fields = ereg_replace("^ ", "", $schema_fields);
+		$schema_insert = "INSERT INTO $table ($schema_fields) VALUES($schema_vals);";
+		$handler(trim($schema_insert));
+	} // end while loop
+	return(true);
+}// end function get_table_content_postgres...
+
+
+function get_table_content_mysql($db, $table, $handler)
 {
 	$result = $db->sql_query("SELECT * FROM $table");
 	if(!$result) 
@@ -223,6 +492,7 @@ function get_table_content($db, $table, $handler)
 	}
 	return(true);
 }
+
 function output_table_content($content)
 {
 	global $backup_sql;
@@ -334,17 +604,23 @@ if(isset($perform))
 			$backup_sql .= "# Dump of tables for $dbname\n";
 			$backup_sql .= "#\n# DATE : " .  gmdate("d-m-Y H:i:s", time()) . " GMT\n";
 			$backup_sql .= "#\n";
+			if($dbms == 'postgres')
+			{
+				$backup_sql = "\n" . pg_get_sequences($db, "\n", $backup_type);
+			}
 			for($i = 0; $i < count($tables); $i++)
 			{
 				$table_name = $tables[$i];
+				$table_def_function = "get_table_def_" . DBMS;
+				$table_content_function = "get_table_content_" . DBMS;
 				if($backup_type != 'data')
 				{
 					$backup_sql .= "#\n# TABLE: " . $table_prefix . $table_name . "\n#\n";
-					$backup_sql .= get_table_def($db, $table_prefix . $table_name, "\n") . "\n";
+					$backup_sql .= $table_def_function($db, $table_prefix . $table_name, "\n") . "\n";
 				} 
 				if($backup_type != 'structure')
 				{
-					get_table_content($db, $table_prefix . $table_name, "output_table_content");
+					$table_content_function($db, $table_prefix . $table_name, "output_table_content");
 				}
 			}
 			// move forward with sending the file across...
@@ -402,7 +678,7 @@ if(isset($perform))
 								echo "Executing: $sql\n<br>";
 							}
 							$result = $db->sql_query($sql);
-							if(!$result)
+							if(!$result && (!(DBMS == 'postgres' && eregi("drop table", $sql))))
 							{
 								$error = $db->sql_error();
 								error_die(GENERAL_ERROR, 'Error importing backup file : ' . $error['message'] . "\n$sql");