<?
/*
phpMSSQLDump v1.0
Author: Jonathan Hilgeman
Date: 10/23/2009
Description:
Creates a SQL dump of one or more tables in MSSQL database. The dump is in
MSSQL-friendly format.
EASY-TO-READ DISCLAIMER:
This is a very basic tool. It should be able to dump a table's schema in a
CREATE TABLE format, with proper constraints (e.g. primary keys), proper
handling of identity columns, and proper ownership. It should also be able
to dump table data, including identity inserts and binary data.
I do not have access to a wide variety of MSSQL databases that make use of
different character sets, nor do I have access to many different server
versions, so this has only undergone limited testing. It seems to work well
with the databases that I have, which contain data using mostly US English
characters, and residing on MSSQL 2000 Enterprise and 2005 Express servers.
This was inspired by some original coding by Mr. NAZEEM.M.J. at WisdomBay,
who created a stored procedure called SQL_DUMP. The procedure had a lot of
critical problems and missing features, but it made a good starting point.
You can find the original stored procedure here:
http://www.wisdombay.com/downs/sqldump.php
It also makes use of a great multi-dimensional-array-sorting function that
I came across a while back. All I know is that it was coded by Ichier2003,
and it's called array_csort().
Please feel free to give me any feedback using the contact form on my site
at http://www.sitecreative.com.
USAGE EXAMPLE #1:
// Large databases can take a while to process
set_time_limit(0);
// Connect to the database
$db = mssql_connect("dbhost","username","password");
// Dump all tables in the database called myDatabase
$MSSQLDump = new phpMSSQLDump($db,"myDatabase");
$MSSQLDump->dumpSQL();
// Write the resulting SQL to a file
file_put_contents("mssqldump.sql",$MSSQLDump->resultsSQL);
USAGE EXAMPLE #2:
// Large databases can take a while to process
set_time_limit(0);
// Connect to the database
$db = mssql_connect("dbhost","username","password");
// Dump all tables in the database called myDatabase
$MSSQLDump = new phpMSSQLDump($db,"myDatabase");
// Set some additional options
$MSSQLDump->tableName = array("myTable","anotherTable","andOneMore"); // Only dump these three tables
$MSSQLDump->stripComments = true; // Leave off the comments in the output
$MSSQLDump->stripTabs = true; // Don't indent the output
$MSSQLDump->dumpData = false; // Don't dump the data (usually much faster!)
$MSSQLDump->dumpSchema = true; // This is the default, but just so you know it's there...
$MSSQLDump->debugMode = false; // Again, the default.
// Go!
$MSSQLDump->dumpSQL();
// Write the resulting SQL to a file
file_put_contents("mssqldump.sql",$MSSQLDump->resultsSQL);
*/
class phpMSSQLDump
{
public $tableName = "ALL"; // ALL is a keyword that pull all of the tables in the database
public $dumpData = true; // Whether to dump data or not
public $dumpSchema = true; // Whether to dump the schema or not
public $dumpDataLimit = "NO LIMIT"; // Set this to a # to only dump the first # of data records per table
public $stripComments = false; // False = add the comments in
public $stripTabs = false; // False = use tabs to indent the code
public $debugMode = false; // This generates a LOT of debug data - don't use it on production servers
public $resultsSQL = ""; // Contains the results of the dump
// Normally you don't touch these
public $dbName = ""; // Name of the database (usually set when you create a new instance of the class)
public $dbLink = ""; // Just a placeholder for the database link
public $tables = array(); // Holds the parsed table structures
public $lineBreak = "\r\n"; // Pretty tandard
public $queryEnd = ";";
public $tabChar = "\t";
// Construct with a Database Connection
public function __construct($dbLink,$dbName = "")
{
if(is_resource($dbLink))
{
$this->dbLink = $dbLink;
if($dbName)
{
if(!mssql_select_db($dbName,$dbLink))
{
return false;
}
}
return true;
}
else
{
return false;
}
}
protected function debugMsg($msg)
{
if($this->debugMode)
{
print $msg . "<br>\n";
flush();
}
}
protected function array_csort() { //coded by Ichier2003
$args = func_get_args();
$marray = array_shift($args);
$msortline = "return(array_multisort(";
$i = 0;
foreach ($args as $arg) {
$i++;
if (is_string($arg)) {
foreach ($marray as $row) {
$a = strtoupper($row[$arg]);
$sortarr[$i][] = $a;
}
} else {
$sortarr[$i] = $arg;
}
$msortline .= "\$sortarr[".$i."],";
}
$msortline .= "\$marray));";
eval($msortline);
return $marray;
}
protected function dbQuery($query)
{
$this->debugMsg("Query: " . $query . "\n");
if(function_exists("ob_start"))
{
ob_start();
$results = mssql_query($query,$this->dbLink);
// Get error message
$buffer=explode("(severity",ob_get_contents());
$buffermsgpieces = explode("message:",$buffer[0]);
$message = (IsSet($buffermsgpieces[1]) ? $buffermsgpieces[1] : "");
$sql_error = $message;
ob_end_clean();
// Failed query?
if($results === false)
{
$this->debugMsg("Query: $query\n Failure Message: $sql_error\n");
return array("msg" => $sql_error,"query" => $query);
}
else
{
return $results;
}
}
else
{
// No output buffering functions, so run it and hope for the best!
$results = mssql_query($query,$this->dbLink);
if($results === false)
{
$this->debugMsg("Query: $query\n Failure Message: $sql_error\n");
return array("msg" => mssql_get_last_message(),"query" => $query);
}
else
{
return $results;
}
}
}
protected function getTableStructures($tableName,$tableOwner)
{
$this->debugMsg("[$tableOwner].[$tableName] :: Getting columns in table...");
// Dump Table Structure(s)
// ==========================================================================================
// case isnullable when 0 then 'NOT NULL' else 'NULL' end as nullable,
$query = "select b.name as table_name,
a.name as column_name,
c.name as type,
a.length as length,
a.xprec as precisions,
a.xscale as scale,
isnullable,
c.collation,
a.colorder,
a.status
from syscolumns a,
sysobjects b,
systypes c
where b.xtype='U'
and a.id=b.id
and a.xusertype=c.xtype
and b.name='$tableName'
order by b.name,a.colorder";
$results = $this->dbQuery($query);
if(!is_array($results))
{
if(mssql_num_rows($results) > 0)
{
while($row = mssql_fetch_assoc($results))
{
// Create SQL Line
$ColName = $row["column_name"];
$Type = $row["type"];
$Length = $row["length"];
$Precision = $row["precisions"];
$Scale = $row["scale"];
// $Nullable = ($row["isnullable"] == 0 ? false : true);
$row["isnullable"] = ($row["isnullable"] ? true : false);
$Nullable = ($row["isnullable"] ? "null" : "not null");
$Collat = $row["collation"];
$ColOrder = $row["colorder"];
$Status = $row["status"];
$IsIdentity = ($Status == 128);
if($IsIdentity)
{
$this->tables[$tableName]["HasIdentityColumn"] = true;
// Get seed and increment values
$this->debugMsg("[$tableOwner].[$tableName] :: Getting seed/increment values for identity column...");
$query2 = " select ident_seed('[$tableOwner].[$tableName]') as seed,
ident_incr('[$tableOwner].[$tableName]') as increment
from information_schema.tables
where objectproperty(object_id('[$tableOwner].[$tableName]'),'TableHasIdentity') = 1
and table_type = 'base table' and table_name='$tableName' and table_schema='$tableOwner'";
$results2 = $this->dbQuery($query2);
if(!is_array($results2))
{
if(mssql_num_rows($results2) > 0)
{
$row2 = mssql_fetch_assoc($results2);
$IdentitySQL = " IDENTITY(" . $row2["seed"] . "," . $row2["increment"] . ")";
$row["identity"] = $row2["seed"] . "," . $row2["increment"];
}
else
{
return array("result" => false,"msg" => "Could not find seed/increment values for identity column!","query" => $query2);
}
}
else
{
return array("result" => false,"msg" => $results2["msg"],"query" => $results2["query"]);
}
}
else
{
$IdentitySQL = "";
}
$SQLColDefinition = "[" . $ColName . "]";
if(in_array($Type,array("varchar","char")))
{
$SQLColDefinition .= " $Type($Length) collate $Collat $Nullable";
}
elseif(in_array($Type,array("numeric","decimal")))
{
$SQLColDefinition .= " $Type($Precision,$Scale) $Nullable";
}
elseif(in_array($Type,array("datetime","int","image","tinyint","smallint","bigint","bit","money","smallmoney","float","real","smalldatetime","ntext","sysname","uniqueidentifier")))
{
$SQLColDefinition .= " $Type" . $IdentitySQL . " $Nullable";
}
elseif(in_array($Type,array("nchar","nvarchar","binary","varbinary")))
{
$SQLColDefinition .= " $Type($Length) $Nullable";
}
elseif($Type == "text")
{
$SQLColDefinition .= " $Type collate $Collat $Nullable";
}
elseif($Type == "timestamp")
{
$SQLColDefinition .= " $Type";
}
// Store Parsed Definition
$row["primarykey"] = false; // By default, columns are not part of the primary key
$this->tables[$tableName]["Cols"][$ColName] = $row;
$this->tables[$tableName]["ColSQLDefs"][$ColName] = $SQLColDefinition;
}
$this->tables[$tableName]["Cols"] = $this->array_csort($this->tables[$tableName]["Cols"],"colorder",SORT_ASC);
$this->tables[$tableName]["TableSQLDef"] = "CREATE TABLE [$tableOwner].[$tableName] (" . $this->lineBreak . $this->tabChar . join("," . $this->lineBreak . $this->tabChar,$this->tables[$tableName]["ColSQLDefs"]) . "$this->lineBreak)";
}
else
{
return array("result" => false,"msg" => "Could not find any data for selected tables!","query" => $query);
}
}
else
{
return array("result" => false,"msg" => $results["msg"],"query" => $results["query"]);
}
return true;
}
protected function getUniqueKeyConstraints($tableName,$tableOwner)
{
// Get Unique Key Constraints
// ==========================================================================================
$this->debugMsg("[$tableOwner].[$tableName] :: Getting unique keys...");
$query = "select distinct a.name as table_name,
b.name as constraint_name,
b.xtype as constraint_type
from sysobjects a,
sysobjects b,
sysindexes c,
sysindexkeys d,
syscolumns e
where b.xtype='UQ'
and b.parent_obj=a.id
and c.indid > 1
and c.id=b.parent_obj
and c.name=b.name
and c.id=d.id
and c.indid=d.indid
and d.id=e.id
and d.colid=e.colid
and a.name='$tableName'";
$results = $this->dbQuery($query);
if(!is_array($results))
{
if(mssql_num_rows($results) > 0)
{
while($row = mssql_fetch_assoc($results))
{
// Pull columns involved in the primary key
$UQConstraintName = $row["constraint_name"];
$UQColumnNames = array();
$this->debugMsg("[$tableOwner].[$tableName] :: \tGetting columns in unique key $UQConstraintName...");
$query2 = " select e.name as column_name
from sysobjects a,
sysobjects b,
sysindexes c,
sysindexkeys d,
syscolumns e
where b.xtype='UQ'
and b.parent_obj=a.id
and b.parent_obj=c.id
and c.indid > 1
and c.name=b.name
and c.id=d.id
and c.indid=d.indid
and d.id=e.id
and d.colid=e.colid
and b.name='$UQConstraintName'";
$results2 = $this->dbQuery($query2);
if(!is_array($results2))
{
if(mssql_num_rows($results2) > 0)
{
while($row2 = mssql_fetch_assoc($results2))
{
$ColName = $row2["column_name"];
$UQColumnNames[] = "[" . $ColName . "]";
}
}
else
{
return array("result" => false,"msg" => "Could not pull the column names for a unique key!","query" => $query2);
}
}
else
{
return array("result" => false,"msg" => $results2["msg"],"query" => $results2["query"]);
}
$SQLConstraintDefinition = "CONSTRAINT " . $UQConstraintName . " UNIQUE(" . join(",",$UQColumnNames) . ")";
$this->tables[$tableName]["UQConstraintSQLDefs"][$UQConstraintName] = $SQLConstraintDefinition;
}
$this->tables[$tableName]["TableUQConstraintsSQLDef"] = "ALTER TABLE [$tableOwner].[$tableName] ADD " . $this->lineBreak . $this->tabChar . join("," . $this->lineBreak . $this->tabChar,$this->tables[$tableName]["UQConstraintSQLDefs"]) . "$this->lineBreak";
}
else
{
// Tables are not required to have constraints - not an error.
}
}
else
{
return array("result" => false,"msg" => $results["msg"],"query" => $results["query"]);
}
return true;
}
protected function getPrimaryKey($tableName,$tableOwner)
{
// Get Primary Key Constraints
// ==========================================================================================
$this->debugMsg("[$tableOwner].[$tableName] :: Getting primary key...");
$query = "select distinct a.name as table_name,
b.name as constraint_name,
b.xtype as constraint_type
from sysobjects a,
sysobjects b,
sysindexes c,
sysindexkeys d,
syscolumns e
where b.xtype='PK'
and b.parent_obj=a.id
and b.parent_obj=c.id
and c.indid=1
and c.id=d.id
and c.indid=d.indid
and d.id=e.id
and d.colid=e.colid
and a.status >= 0
and a.name='$tableName'";
$results = $this->dbQuery($query);
if(!is_array($results))
{
if(mssql_num_rows($results) > 0)
{
while($row = mssql_fetch_assoc($results))
{
// Pull columns involved in the primary key
$PKConstraintName = $row["constraint_name"];
$PKColumnNames = array();
$this->debugMsg("[$tableOwner].[$tableName] :: \tGetting columns in primary key $PKConstraintName...");
$query2 = " select e.name as column_name
from sysobjects a,
sysobjects b,
sysindexes c,
sysindexkeys d,
syscolumns e
where b.xtype='PK'
and b.parent_obj=a.id
and b.parent_obj=c.id
and c.indid=1
and c.id=d.id
and c.indid=d.indid
and d.id=e.id
and d.colid=e.colid
and b.name='$PKConstraintName'";
$results2 = $this->dbQuery($query2);
if(!is_array($results2))
{
if(mssql_num_rows($results2) > 0)
{
while($row2 = mssql_fetch_assoc($results2))
{
$ColName = $row2["column_name"];
$PKColumnNames[] = "[" . $ColName . "]";
$this->tables[$tableName]["Cols"][$ColName]["primarykey"] = true;
}
}
else
{
return array("result" => false,"msg" => "Could not pull the column names for the primary key!","query" => $query2);
}
}
else
{
return array("result" => false,"msg" => $results2["msg"],"query" => $results2["query"]);
}
}
$this->tables[$tableName]["TablePKConstraintsSQLDef"] = "ALTER TABLE [$tableOwner].[$tableName] ADD CONSTRAINT " . $PKConstraintName . " PRIMARY KEY(" . join(",",$PKColumnNames) . ")";
}
else
{
// Tables are not required to have constraints - not an error.
}
}
else
{
return array("result" => false,"msg" => $results["msg"],"query" => $results["query"]);
}
return true;
}
protected function getDefaultValues($tableName,$tableOwner)
{
// Get Default Constraints (Column Default Values)
// ==========================================================================================
$this->debugMsg("[$tableOwner].[$tableName] :: Getting default values for columns...");
$query = "select b.name as table_name,
a.name as column_name,
d.text as default_value,
e.name as constraint_name
from syscolumns a,
sysobjects b,
systypes c,
syscomments d,
sysobjects e
where b.xtype='U'
and a.id=b.id
and a.xusertype=c.xtype
and a.cdefault=d.id
and e.id=d.id
and b.name='$tableName'
order by a.colorder";
$results = $this->dbQuery($query);
if(!is_array($results))
{
if(mssql_num_rows($results) > 0)
{
while($row = mssql_fetch_assoc($results))
{
// Create SQL Line
$ColName = $row["column_name"];
$ConstraintName = $row["constraint_name"];
// $DefaultValue = substr($row["default_value"],1,strlen($row["default_value"])-2);
$DefaultValue = $row["default_value"];
$SQLConstraintDefinition = "CONSTRAINT $ConstraintName DEFAULT " . $DefaultValue . " FOR " . $ColName;
// Store Parsed Definition
$this->tables[$tableName]["Cols"][$ColName]["default"] = $DefaultValue;
$this->tables[$tableName]["DefaultConstraintsSQLDef"][$ColName] = $SQLConstraintDefinition;
}
$this->tables[$tableName]["TableDefaultConstraintsSQLDef"] = "ALTER TABLE [$tableOwner].[$tableName] ADD " . $this->lineBreak . $this->tabChar . join("," . $this->lineBreak . $this->tabChar,$this->tables[$tableName]["DefaultConstraintsSQLDef"]) . "$this->lineBreak";
}
else
{
// Tables are not required to have constraints - not an error.
}
}
else
{
return array("result" => false,"msg" => $results["msg"],"query" => $results["query"]);
}
return true;
}
protected function getForeignKeyConstraints($tableName,$tableOwner)
{
// Get Foreign Key Constraints
// ==========================================================================================
$this->debugMsg("[$tableOwner].[$tableName] :: Getting foreign keys...");
$query = "select distinct a.name as constraint_name,
a.xtype as type,
c.name as table_name,
d.name as parent_table
from sysforeignkeys b,
sysobjects a,
sysobjects c,
sysobjects d,
syscolumns e,
syscolumns f
where a.id=b.constid
and c.id=b.fkeyid
and d.id=b.rkeyid
and b.fkeyid=e.id
and b.fkey=e.colid
and b.rkeyid=f.id
and b.rkey=f.colid
and c.name='$tableName'";
$results = $this->dbQuery($query);
if(!is_array($results))
{
if(mssql_num_rows($results) > 0)
{
while($row = mssql_fetch_assoc($results))
{
// Pull columns involved in the primary key
$ParentTable = $row["parent_table"];
$FKConstraintName = $row["constraint_name"];
$FKColumnNames_Foreign = array();
$FKColumnNames_Reference = array();
$this->debugMsg("[$tableOwner].[$tableName] :: \tGetting columns in foreign key $FKConstraintName...");
$query2 = " select e.name as foreign_key_column,
f.name as reference_column
from sysforeignkeys b,
sysobjects a,
sysobjects c,
sysobjects d,
syscolumns e,
syscolumns f
where a.id=b.constid
and c.id=b.fkeyid
and d.id=b.rkeyid
and b.fkeyid=e.id
and b.fkey=e.colid
and b.rkeyid=f.id
and b.rkey=f.colid
and a.name='$FKConstraintName'";
$results2 = $this->dbQuery($query2);
if(!is_array($results2))
{
if(mssql_num_rows($results2) > 0)
{
while($row2 = mssql_fetch_assoc($results2))
{
$Foreign_ColName = $row2["foreign_key_column"];
$Reference_ColName = $row2["foreign_key_column"];
$FKColumnNames_Foreign[] = "[" . $Foreign_ColName . "]";
$FKColumnNames_Reference[] = "[" . $Reference_ColName . "]";
}
}
else
{
return array("result" => false,"msg" => "Could not pull the column names for a foreign key!","query" => $query2);
}
}
else
{
return array("result" => false,"msg" => $results2["msg"],"query" => $results2["query"]);
}
$SQLConstraintDefinition = "CONSTRAINT " . $FKConstraintName . " FOREIGN KEY(" . join(",",$FKColumnNames_Foreign) . ") REFERENCES [$ParentTable](" . join(",",$FKColumnNames_Reference) . ")";
$this->tables[$tableName]["FKConstraintSQLDefs"][$FKConstraintName] = $SQLConstraintDefinition;
}
$this->tables[$tableName]["TableFKConstraintsSQLDef"] = "ALTER TABLE [$tableOwner].[$tableName] ADD " . $this->lineBreak . $this->tabChar . join("," . $this->lineBreak . $this->tabChar,$this->tables[$tableName]["FKConstraintSQLDefs"]) . "$this->lineBreak";
}
else
{
// Tables are not required to have constraints - not an error.
}
}
else
{
return array("result" => false,"msg" => $results["msg"],"query" => $results["query"]);
}
return true;
}
protected function buildTableList()
{
// Check Table(s) Existence
$this->debugMsg("Discovering all tables...");
$query = "select table_name, table_schema owner from information_schema.tables, sysobjects where table_name = sysobjects.[name] and table_type = 'BASE TABLE' and status >= 0 ";
if($this->tableName <> "ALL")
{
if(is_array($this->tableName))
{
$arrTableNames = array();
foreach($this->tableName as $tmpTableName)
{
$arrTableNames[] = "'" . $tmpTableName . "'";
}
$query .= "and table_name in (" . join(",",$arrTableNames) . ")";
}
else
{
$query .= "and table_name='" . $this->tableName . "'";
}
}
$results = $this->dbQuery($query);
if(!is_array($results))
{
if(mssql_num_rows($results) > 0)
{
$this->tables = array();
while($row = mssql_fetch_assoc($results))
{
$this->tables[$row["table_name"]] = array("owner" => $row["owner"]);
}
}
else
{
return array("result" => false,"msg" => "Could not find any tables matching that criteria!","query" => $query);
}
}
else
{
return array("result" => false,"msg" => $results["msg"],"query" => $results["query"]);
}
}
protected function generateHeaderComments()
{
// Please leave the following line at the top of every SQL dump generated with this tool.
// If someone besides you is looking at a SQL dump and has any comments or questions,
// they will know how it was generated and where to look for more info.
$SQL = "-- Created using phpMSSQLDump v" . $this->version . $this->lineBreak;
$SQL .= "-- by Jonathan Hilgeman of SiteCreative.com" . $this->lineBreak;
$SQL .= "-- http://www.sitecreative.com" . $this->lineBreak;
$SQL .= $this->lineBreak;
$SQL .= "-- NOTE: When importing large dump files, especially those with files stored" . $this->lineBreak;
$SQL .= "-- in image columns, use the osql.exe command line program (included with" . $this->lineBreak;
$SQL .= "-- SQL Server) with the -i switch." . $this->lineBreak;
$SQL .= $this->lineBreak;
$SQL .= "-- TO DO:" . $this->lineBreak;
$SQL .= "-- . Add filegroup discovery for proper placement of keys and text fields." . $this->lineBreak;
$SQL .= "-- . Add clustered discovery for keys." . $this->lineBreak;
$SQL .= "-- . Add option to include NOCHECK for keys." . $this->lineBreak;
$SQL .= "-- . Add option to drop tables before adding." . $this->lineBreak;
$SQL .= "-- . Add trigger dumping." . $this->lineBreak;
$SQL .= $this->lineBreak;
$SQL .= "-- Version 1.0" . $this->lineBreak;
$SQL .= "-- . Adapted queries from sqldump stored procedure." . $this->lineBreak;
$SQL .= "-- . Used existing column ordering." . $this->lineBreak;
$SQL .= "-- . Added brackets to column names for non-standard columns." . $this->lineBreak;
$SQL .= "-- . Added brackets to table names for non-standard tables." . $this->lineBreak;
$SQL .= "-- . Added usage of the current default constraint names." . $this->lineBreak;
$SQL .= "-- . Improved the efficiency of checking column types." . $this->lineBreak;
$SQL .= "-- . Added IDENTITY property with proper seed and increment values." . $this->lineBreak;
$SQL .= "-- . Rewrote data dump from scratch." . $this->lineBreak;
$SQL .= "-- . Added option to return the structure in a multi-dimensional PHP array." . $this->lineBreak;
$SQL .= "-- . Added table owner to table names." . $this->lineBreak;
$SQL .= "-- . Added proper handling of NULL values." . $this->lineBreak;
$SQL .= $this->lineBreak;
$SQL .= "-- Parameters:" . $this->lineBreak;
$SQL .= "-- Dump Schema: " . ($this->dumpSchema ? "Yes" : "No") . $this->lineBreak;
$SQL .= "-- Dump Data: " . ($this->dumpData ? "Yes" : "No") . $this->lineBreak;
$SQL .= "-- Table(s): ";
// Generate table list
if($this->tableName == "ALL")
{
$SQL .= "(All Tables)" . $this->lineBreak;
foreach($this->tables as $tableName => $table)
{
$SQL .= "-- " . $tableName . $this->lineBreak;
}
}
elseif(is_array($this->tableName))
{
$SQL .= "(Specific Tables)" . $this->lineBreak;
foreach($this->tableName as $tmpTableName)
{
$SQL .= "-- " . $tmpTableName . (!IsSet($this->tables[$tmpTableName]) ? " -- not found!" : "") . $this->lineBreak;
}
}
else
{
$SQL .= "(Single Table)" . $this->lineBreak;
foreach($this->tables as $tableName => $table)
{
$SQL .= "-- " . $tableName . $this->lineBreak;
}
}
$SQL .= $this->lineBreak;
return $SQL;
}
protected function generateTableSchema($tableName,$table)
{
$this->debugMsg("Dumping schema for $tableName...");
$SQL = "";
if(!$this->stripComments)
{
$SQL .= "---------------------------------------------------------------------------" . $this->lineBreak;
$SQL .= "-- " . $tableName . " :: Table Creation" . $this->lineBreak;
$SQL .= "---------------------------------------------------------------------------" . $this->lineBreak;
}
$SQL .= $table["TableSQLDef"] . $this->queryEnd . $this->lineBreak . $this->lineBreak;
unset($this->tables[$tableName]["TableSQLDef"]);
unset($this->tables[$tableName]["ColSQLDefs"]);
if(IsSet($table["TableDefaultConstraintsSQLDef"]))
{
if(!$this->stripComments)
{
$SQL .= "---------------------------------------------------------------------------" . $this->lineBreak;
$SQL .= "-- " . $tableName . " :: Default Values" . $this->lineBreak;
$SQL .= "---------------------------------------------------------------------------" . $this->lineBreak;
}
$SQL .= $table["TableDefaultConstraintsSQLDef"] . $this->queryEnd . $this->lineBreak . $this->lineBreak;
unset($this->tables[$tableName]["TableDefaultConstraintsSQLDef"]);
unset($this->tables[$tableName]["DefaultConstraintsSQLDef"]);
}
if(IsSet($table["TablePKConstraintsSQLDef"]))
{
if(!$this->stripComments)
{
$SQL .= "---------------------------------------------------------------------------" . $this->lineBreak;
$SQL .= "-- " . $tableName . " :: Primary Key" . $this->lineBreak;
$SQL .= "---------------------------------------------------------------------------" . $this->lineBreak;
}
$SQL .= $table["TablePKConstraintsSQLDef"] . $this->queryEnd . $this->lineBreak . $this->lineBreak;
unset($this->tables[$tableName]["TablePKConstraintsSQLDef"]);
}
if(IsSet($table["TableUQConstraintsSQLDef"]))
{
if(!$this->stripComments)
{
$SQL .= "---------------------------------------------------------------------------" . $this->lineBreak;
$SQL .= "-- " . $tableName . " :: Unique Keys" . $this->lineBreak;
$SQL .= "---------------------------------------------------------------------------" . $this->lineBreak;
}
$SQL .= $table["TableUQConstraintsSQLDef"] . $this->queryEnd . $this->lineBreak . $this->lineBreak;
unset($this->tables[$tableName]["TableUQConstraintsSQLDef"]);
}
if(IsSet($table["TableFKConstraintsSQLDef"]))
{
if(!$this->stripComments)
{
$SQL .= "---------------------------------------------------------------------------" . $this->lineBreak;
$SQL .= "-- " . $tableName . " :: Foreign Key Constraints" . $this->lineBreak;
$SQL .= "---------------------------------------------------------------------------" . $this->lineBreak;
}
$SQL .= $table["TableFKConstraintsSQLDef"] . $this->queryEnd . $this->lineBreak . $this->lineBreak;
unset($this->tables[$tableName]["TableFKConstraintsSQLDef"]);
}
return $SQL;
}
protected function genererateTableData($tableName,$table)
{
$tableOwner = $table["owner"];
$SQL = "";
if(!$this->stripComments)
{
$SQL .= "---------------------------------------------------------------------------" . $this->lineBreak;
$SQL .= "-- " . $tableName . " :: Data Dump" . $this->lineBreak;
$SQL .= "---------------------------------------------------------------------------" . $this->lineBreak;
}
$NumericTypes = array("tinyint","smallint","int","bigint","bit","decimal","float","money","numeric","real","small_money");
$BinaryTypes = array("binary","image","varbinary");
$tableCols = $table["Cols"];
$query = "SELECT " . (is_numeric($this->dumpDataLimit) ? "TOP ".$this->dumpDataLimit : "") . " * FROM [" . $table["owner"] . "].[$tableName]";
$results = $this->dbQuery($query);
if(!is_array($results))
{
$this->debugMsg("Dumping data for $tableName... (" . mssql_num_rows($results) . " rows)");
if(mssql_num_rows($results) > 0)
{
// Optional identity insert ON
if($table["HasIdentityColumn"])
{
$tmpQuery = "SET IDENTITY_INSERT [$tableOwner].[$tableName] ON" . $this->queryEnd;
$SQL .= $tmpQuery . $this->lineBreak;
}
// Dump the data
$rowCount = 0;
$IntervalTS = time();
while($row = mssql_fetch_assoc($results))
{
$rowCount++;
$InsertQuery = "INSERT INTO [$tableOwner].[$tableName] ";
$InsertColumns = array();
$InsertValues = array();
foreach($tableCols as $ColName => $Col)
{
$InsertColumns[] = "[" . $ColName . "]";
if(is_null($row[$ColName]))
{
$InsertValues[] = "NULL";
}
else
{
if(in_array($Col["type"],$NumericTypes))
{
$InsertValues[] = $row[$ColName];
}
elseif(in_array($Col["type"],$BinaryTypes))
{
$arrData = unpack("H*hex", $row[$ColName]);
$InsertValues[] = "0x" . $arrData["hex"];
}
else
{
$InsertValues[] = "'" . str_replace("'","''",$row[$ColName]) . "'";
}
}
}
$InsertQuery .= "(" . join(",",$InsertColumns) . ") VALUES (" . join(",",$InsertValues) . ")";
$SQL .= $InsertQuery . $this->queryEnd . $this->lineBreak;
if( (($rowCount % 1000) == 0) )
{
$this->debugMsg("Dumped $rowCount rows so far... (" . (time() - $IntervalTS) . " seconds since last 1000 rows)");
$IntervalTS = time();
}
}
// Optional identity insert OFF
if($table["HasIdentityColumn"])
{
$tmpQuery = "SET IDENTITY_INSERT [$tableOwner].[$tableName] OFF" . $this->queryEnd;
$SQL .= $tmpQuery . $this->lineBreak;
}
$SQL .= $this->lineBreak;
}
else
{
if(!$this->stripComments)
{
$SQL .= "-- No rows in table!" . $this->lineBreak . $this->lineBreak;
}
}
}
else
{
return array("result" => false,"msg" => $results["msg"],"query" => $results["query"]);
}
return $SQL;
}
public function dumpSQL()
{
// Dump Nothing?
if(!$this->dumpSchema && !$this->dumpData) return array("result" => false,"msg" => "DumpSchema and DumpData cannot both be false!","query" => "");
// Tab Character
$this->tabChar = $this->stripTabs ? "" : $this->tabChar;
// Build $this->tables array
$this->buildTableList();
// We always get the table schema(s) - it will help with creating proper INSERTs, even if we're not dumping schema(s)
foreach($this->tables as $tableName => $table)
{
// Initial Table Values
$this->tables[$tableName]["HasIdentityColumn"] = false;
$tableOwner = $table["owner"];
$results = $this->getTableStructures($tableName,$tableOwner);
if($results !== true) return $results;
$results = $this->getDefaultValues($tableName,$tableOwner);
if($results !== true) return $results;
$results = $this->getPrimaryKey($tableName,$tableOwner);
if($results !== true) return $results;
$results = $this->getUniqueKeyConstraints($tableName,$tableOwner);
if($results !== true) return $results;
$results = $this->getForeignKeyConstraints($tableName,$tableOwner);
if($results !== true) return $results;
}
// Sort tables
$this->debugMsg("Sorting tables...");
ksort($this->tables); // Sort Alphabetically
// Start with the header comments
if(!$this->stripComments)
$this->resultsSQL .= $this->generateHeaderComments();
// Create a big text variable containing the whole dump
foreach($this->tables as $tableName => $table)
{
// Dump the Schema
if($this->dumpSchema)
$this->resultsSQL .= $this->generateTableSchema($tableName,$table);
// Dump Data
if($this->dumpData)
{
$results = $this->genererateTableData($tableName,$table);
if(is_array($results)) return $results;
$this->resultsSQL .= $results;
}
}
// End of Dump
$this->resultsSQL .= "-- END" . $this->lineBreak;
// Return values
$Return = array();
$Return["result"] = true;
$Return["SQL"] = $this->resultsSQL;
$Return["Schema"] = $this->tables;
return $Return;
}
}
?>