- Olympus now properly works with MS SQL

- DB backup system handles MS SQL - A few bug fixes to the backup system ;) git-svn-id: file:///svn/phpbb/trunk@5793 89ea8834-ac86-4346-8a33-228a782c2dd0
author: David M <davidmj@users.sourceforge.net> 2006-04-18 02:41:59 +0000
committer: David M <davidmj@users.sourceforge.net> 2006-04-18 02:41:59 +0000
commit: 03b9af1f28c0d537d8459c9820e0f49464046e33 (patch)
tree: abca94200c0c3855acda96f197363940a4835b2e /phpBB
parent: 5b3d732456c281e4beaeb2f4aeb819a535e6d1be (diff)
download: forums-03b9af1f28c0d537d8459c9820e0f49464046e33.tar
forums-03b9af1f28c0d537d8459c9820e0f49464046e33.tar.gz
forums-03b9af1f28c0d537d8459c9820e0f49464046e33.tar.bz2
forums-03b9af1f28c0d537d8459c9820e0f49464046e33.tar.xz
forums-03b9af1f28c0d537d8459c9820e0f49464046e33.zip
4 files changed, 232 insertions, 10 deletions
diff --git a/phpBB/includes/acp/acp_database.php b/phpBB/includes/acp/acp_database.php
index 45c60d2df5..c4fe351f16 100644
--- a/phpBB/includes/acp/acp_database.php
+++ b/phpBB/includes/acp/acp_database.php
@@ -126,6 +126,14 @@ class acp_database
 							case 'sqlite':
 								$sql_data .= "BEGIN TRANSACTION;\n";
 							break;
+
+							case 'postgres':
+								$sql_data .= "BEGIN;\n";
+
+							case 'mssql':
+							case 'mssql_odbc':
+								$sql_data .= "BEGIN TRANSACTION\nGO\n";
+							break;
 						}
 
 						foreach ($table as $table_name)
@@ -139,14 +147,15 @@ class acp_database
 									case 'mysql4':
 									case 'mysql':
 									case 'sqlite';
-
 										$sql_data .= '# Table: ' . $table_name . "\n";
 										$sql_data .= "DROP TABLE IF EXISTS $table_name;\n";
 									break;
 
 									case 'postgres':
+									case 'mssql':
 										$sql_data .= '# Table: ' . $table_name . "\n";
-										$sql_data .= "DROP TABLE $table_name;\n";
+										$sql_data .= "DROP TABLE $table_name;\nGO\n";
+									break;
 
 									default:
 										trigger_error('KungFuDeathGrip');
@@ -197,12 +206,14 @@ class acp_database
 											{
 												$field_set[$j] = $field[$j]->name;
 											}
-											
+
+											$search			= array('\\', "'", "\x00", "\x0a", "\x0d", "\x1a");
+											$replace		= array('\\\\\\\\', "''", '\0', '\n', '\r', '\Z');
 											$fields			= implode(', ', $field_set);
 											$values			= array();
 											$schema_insert	= 'INSERT INTO ' . $table_name . ' (' . $fields . ') VALUES (';
 
-											while ($row = mysqli_fetch_row($result))
+											while ($row = $db->sql_fetchrow($result))
 											{
 												for ($j = 0; $j < $fields_cnt; $j++)
 												{
@@ -216,7 +227,7 @@ class acp_database
 													}
 													else
 													{
-														$values[] = "'" . $row[$j] . "'";
+														$values[] = "'" . str_replace($search, $replace, $row[$j]) . "'";
 													}
 												}
 												$sql_data .= $schema_insert . implode(', ', $values) . ");\n";
@@ -268,12 +279,12 @@ class acp_database
 												$field_set[$j] = $field[$j]->name;
 											}
 
-											$search			= array('\\', "'", "\x00", "\x0a", "\x0d", "\x1a"); //\x08\\x09, not required
+											$search			= array('\\', "'", "\x00", "\x0a", "\x0d", "\x1a");
 											$replace		= array('\\\\\\\\', "''", '\0', '\n', '\r', '\Z');
 											$fields			= implode(', ', $field_set);
 											$schema_insert	= 'INSERT INTO ' . $table_name . ' (' . $fields . ') VALUES (';
 
-											while ($row = mysql_fetch_row($result))
+											while ($row = $db->sql_fetchrow($result))
 											{
 												$values = array();
 
@@ -453,6 +464,103 @@ class acp_database
 										$db->sql_freeresult($result);
 									break;
 
+									case 'mssql':
+									case 'mssql_odbc':
+										$aryType = $aryName = array();
+										
+										// Grab all of the data from current table.
+										$sql = "SELECT * FROM {$table_name}";
+										$result = $db->sql_query($sql);
+
+										$retrieved_data = $db->sql_numrows($result);
+
+										if ($retrieved_data)
+										{
+											$sql_data .= "\nSET IDENTITY_INSERT $table_name ON\n";
+										}
+
+										$i_num_fields = mssql_num_fields($result);
+
+										for ($i = 0; $i < $i_num_fields; $i++)
+										{
+											$aryType[] = mssql_field_type($result, $i);
+											$aryName[] = mssql_field_name($result, $i);
+										}
+
+										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++)
+											{
+												$strVal = $row[$aryName[$i]];
+
+												if (preg_match('#char|text|bool#i', $aryType[$i]))
+												{
+													$strQuote = "'";
+													$strEmpty = '';
+													$strVal = addslashes($strVal);
+												}
+												else if (preg_match('#date|timestamp#i', $aryType[$i]))
+												{
+													if (empty($strVal))
+													{
+														$strQuote = '';
+													}
+													else
+													{
+														$strQuote = "'";
+													}
+												}
+												else
+												{
+													$strQuote = '';
+													$strEmpty = 'NULL';
+												}
+
+												if (empty($strVal) && $strVal !== '0')
+												{
+													$strVal = $strEmpty;
+												}
+
+												$schema_vals[] = $strQuote . $strVal . $strQuote;
+												$schema_fields[] = $aryName[$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";
+
+											if ($store == true)
+											{
+												$write($fp, $sql_data);
+											}
+
+											if ($download == true)
+											{
+												if (!empty($oper))
+												{
+													echo $oper($sql_data);
+												}
+												else
+												{
+													echo $sql_data;
+												}
+											}
+
+											$sql_data = '';
+
+										}
+
+										if ($retrieved_data)
+										{
+											$sql_data .= "\nSET IDENTITY_INSERT $table_name OFF\n";
+										}
+
+										$db->sql_freeresult($result);
+									break;
+
 									default:
 										trigger_error('KungFuDeathGrip');
 								}
@@ -462,8 +570,14 @@ class acp_database
 						switch (SQL_LAYER)
 						{
 							case 'sqlite':
+							case 'postgres':
 								$sql_data .= "COMMIT;";
 							break;
+
+							case 'mssql':
+							case 'mssql_odbc':
+								$sql_data .= "COMMIT\nGO";
+							break;
 						}
 						
 						if ($store == true)
@@ -533,6 +647,20 @@ class acp_database
 								$db->sql_freeresult($result);
 							break;
 
+							case 'mssql':
+							case 'mssql_odbc':
+								$sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME";
+								$result = $db->sql_query($sql);
+								while ($row = $db->sql_fetchrow($result))
+								{
+									if (strpos($row['TABLE_NAME'] . '_', $table_prefix) === 0)
+									{
+										$tables[] = $row['TABLE_NAME'];
+									}
+								}
+								$db->sql_freeresult($result);
+							break;
+
 							default:
 								trigger_error('KungFuDeathGrip');
 						}
@@ -972,6 +1100,97 @@ class acp_database
 				}
 			break;
 
+			case 'mssql':
+			case 'mssql_odbc':
+				$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 .= ' CONSTRAINT [DF_' . $table_name . '_' . $row['COLUMN_NAME'] . '] 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();
+				$line = '';
+
+				$sql = "SELECT CONSTRAINT_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = '$table_name'";
+				$result = $db->sql_query($sql);
+				if ($db->sql_numrows($result))
+				{
+					$sql_data .= "ALTER TABLE [$table_name] WITH NOCHECK ADD\n";
+					while ($row = $db->sql_fetchrow($result))
+					{
+						if (!sizeof($rows))
+						{
+							$sql_data .= "\tCONSTRAINT [{$row['CONSTRAINT_NAME']}] PRIMARY KEY  CLUSTERED \n\t(\n";
+						}
+						$rows[] = "\t\t[{$row['COLUMN_NAME']}]";
+					}
+					$sql_data .= implode(",\n", $rows);
+					$sql_data .= "\n\t)  ON [PRIMARY] \nGO\n";
+				}
+				$db->sql_freeresult($result);
+				$rows = array();
+
+				$sql = "EXEC sp_helpindex '$table_name'";
+				$result = $db->sql_query($sql);
+				if ($db->sql_numrows($result))
+				{
+					while ($row = $db->sql_fetchrow($result))
+					{
+						if ($row['index_description'] == 'nonclustered located on PRIMARY')
+						{
+							$sql_data .= "\nCREATE  INDEX [{$row['index_name']}] ON [$table_name]([{$row['index_keys']}]) ON [PRIMARY]\nGO\n";
+						}
+					}
+				}
+				$db->sql_freeresult($result);
+			break;
+
 			default:
 				trigger_error('KungFuDeathGrip');
 		}
diff --git a/phpBB/includes/db/mssql.php b/phpBB/includes/db/mssql.php
index 0e30a2670a..dde20bed8f 100644
--- a/phpBB/includes/db/mssql.php
+++ b/phpBB/includes/db/mssql.php
@@ -98,6 +98,8 @@ class dbal_mssql extends dbal
 		{
 			global $cache;
 
+			$query = preg_replace('#FROM \(([^)]*)\)(,|[\n\r\t ]+(?:WHERE|LEFT JOIN)) #', 'FROM \1\2 ', $query);
+
 			// EXPLAIN only in extra debug mode
 			if (defined('DEBUG_EXTRA'))
 			{
@@ -109,7 +111,6 @@ class dbal_mssql extends dbal
 			if (!$this->query_result)
 			{
 				$this->num_queries++;
-				
 				if (($this->query_result = @mssql_query($query, $this->db_connect_id)) === false)
 				{
 					$this->sql_error($query);
@@ -318,7 +319,7 @@ class dbal_mssql extends dbal
 	{
 		return array(
 			'message'	=> @mssql_get_last_message($this->db_connect_id),
-			'code'		=> ''
+			'code'		=> @mssql_query("SELECT @@ERROR", $this->db_connect_id)
 		);
 	}
 
diff --git a/phpBB/install/schemas/mssql_schema.sql b/phpBB/install/schemas/mssql_schema.sql
index 2cd34cfd4e..93f80cf5e5 100644
--- a/phpBB/install/schemas/mssql_schema.sql
+++ b/phpBB/install/schemas/mssql_schema.sql
@@ -83,6 +83,7 @@ ALTER TABLE [phpbb_auth_groups] WITH NOCHECK ADD
 	CONSTRAINT [DF_auth_g_group_id] DEFAULT (0) FOR [group_id],
 	CONSTRAINT [DF_auth_g_forum_id] DEFAULT (0) FOR [forum_id],
 	CONSTRAINT [DF_auth_g_auth_option_id] DEFAULT (0) FOR [auth_option_id],
+	CONSTRAINT [DF_auth_g_auth_role_id] DEFAULT (0) FOR [auth_role_id],
 	CONSTRAINT [DF_auth_g_auth_setting] DEFAULT (0) FOR [auth_setting]
 GO
 
@@ -195,6 +196,7 @@ ALTER TABLE [phpbb_auth_users] WITH NOCHECK ADD
 	CONSTRAINT [DF_auth_u_user_id] DEFAULT (0) FOR [user_id],
 	CONSTRAINT [DF_auth_u_forum_id] DEFAULT (0) FOR [forum_id],
 	CONSTRAINT [DF_auth_u_auth_option_id] DEFAULT (0) FOR [auth_option_id],
+	CONSTRAINT [DF_auth_u_auth_role_id] DEFAULT (0) FOR [auth_role_id],
 	CONSTRAINT [DF_auth_u_auth_setting] DEFAULT (0) FOR [auth_setting]
 GO
 
diff --git a/phpBB/install/schemas/schema_data.sql b/phpBB/install/schemas/schema_data.sql
index 4174c287f3..9375dd15cc 100644
--- a/phpBB/install/schemas/schema_data.sql
+++ b/phpBB/install/schemas/schema_data.sql
@@ -445,7 +445,7 @@ INSERT INTO phpbb_bots (bot_id, bot_active, bot_name, user_id, bot_agent, bot_ip
 # MSSQL IDENTITY phpbb_bots OFF #
 
 
-# MSSQL IDENTITY phpbb_modules OFF #
+# MSSQL IDENTITY phpbb_modules ON #
 
 # -- Modules
author	David M <davidmj@users.sourceforge.net>	2006-04-18 02:41:59 +0000
committer	David M <davidmj@users.sourceforge.net>	2006-04-18 02:41:59 +0000
commit	03b9af1f28c0d537d8459c9820e0f49464046e33 (patch)
tree	abca94200c0c3855acda96f197363940a4835b2e /phpBB
parent	5b3d732456c281e4beaeb2f4aeb819a535e6d1be (diff)
download	forums-03b9af1f28c0d537d8459c9820e0f49464046e33.tar forums-03b9af1f28c0d537d8459c9820e0f49464046e33.tar.gz forums-03b9af1f28c0d537d8459c9820e0f49464046e33.tar.bz2 forums-03b9af1f28c0d537d8459c9820e0f49464046e33.tar.xz forums-03b9af1f28c0d537d8459c9820e0f49464046e33.zip