public static void RestoreTable(XmlReader rdr, TableDefn table, bool idCol, Hashtable hash)
{
string tablename = table.TableName;
string pluralisedTablename = Pluralizer.Pluralize(tablename);
while (rdr.Read())
{
if (rdr.NodeType == XmlNodeType.Whitespace)
{
}
else if (rdr.NodeType == XmlNodeType.Element && rdr.Name == tablename)
{
RestoreRow(rdr, table, idCol, hash);
}
else if (rdr.NodeType == XmlNodeType.EndElement && rdr.Name == pluralisedTablename)
{
// End of this table
break;
}
else
{
throw new Exception("XML format error in RestoreTable.");
}
}
}
public static void BackupTable(string tablename, BackupFile file, TableDefn table)
{
file.AppendLine(" <" + Pluralizer.Pluralize(tablename) + ">");
string query = "SELECT " + table.SelectCols() + " FROM " + tablename + ";";
IDbConnection dbConn = Connection.Me.GetConnection();
try
{
dbConn.Open();
IDbCommand dbCmd = dbConn.CreateCommand();
dbCmd.CommandType = CommandType.Text;
dbCmd.CommandText = query;
using (IDataReader rdr = dbCmd.ExecuteReader())
{
while (rdr.Read())
{
file.AppendLine(" <" + tablename + ">");
for (int i = 0; i < rdr.FieldCount; i++)
{
//Console.WriteLine(rdr.GetName(i) + "=" + rdr.GetValue(i).ToString());
string key = rdr.GetName(i);
object fldvalue = rdr.GetValue(i);
if (fldvalue != DBNull.Value)
{
fldvalue = Convert.ChangeType(fldvalue, rdr.GetFieldType(i));
string clrtype = table.FindColumn(key).CLRType().Name;
if (fldvalue != null)
{
file.AppendLine(" <" + key + ">" + FormatValue(fldvalue, clrtype) + "</" + key + ">");
}
}
}
file.AppendLine(" </" + tablename + ">");
}
}
}
catch (Exception ex)
{
throw new Exception("There was a problem loading table from query [" + query + "] - " + ex.Message, ex);
}
finally
{
dbConn.Close();
dbConn = null;
}
file.AppendLine(" </" + Pluralizer.Pluralize(tablename) + ">");
}
private void LoadDefinition(string filename)
{
int state = 0;
FileAccess handle = new FileAccess(filename);
if (!handle.IsOpen())
{
return;
}
while (!handle.EndOfStream)
{
string s = handle.ReadLine();
List <string> words = s.Wordise();
if (words.Count > 0) // Skip blank lines
{
if (state == 0) // The Database name must be the first thing we see
{
if (words[0].ToUpper() == "DATABASE")
{
if (words.Count == 2)
{
DatabaseName = words[1];
Tables = new List <TableDefn>();
state = 1;
}
else
{
Log.Me.Error("DATABASE statement in " + filename + " either missing database name or has too many values. Statement is: " + s);
return;
}
}
else
{
Log.Me.Error("Missing DATABASE statement in first line of " + filename);
return;
}
}
else if (state == 1) // Looking for the start of a Table
{
if (words[0].ToUpper() == "TABLE")
{
if (words.Count == 2)
{
table = new TableDefn();
table.TableName = words[1];
table.Columns = new List <ColumnDefn>();
Tables.Add(table);
state = 2;
}
else
{
Log.Me.Error("TABLE statement in " + filename + " either missing table name or has too many values. Statement is: " + s);
return;
}
}
else
{
Log.Me.Error("Missing TABLE statement in " + filename);
return;
}
}
else // The only other state is 2, which is looking for a column definition or END
{
if (words.Count == 1)
{
if (words[0].ToUpper() == "END")
{
// End of one table definition, set state to look for the next.
state = 1;
}
else
{
Log.Me.Error("Table definition entry with just one word, and it isn't END, in " + filename + ". Statement is: " + s);
return;
}
}
else // Must have at least 2 entries which will be parsed as column name and type
{
ColumnDefn c = new ColumnDefn();
c.ColumnName = words[0];
c.ColumnType = words[1];
c.IsIdentity = false;
c.IsNullable = true;
c.IsIndex = false;
c.FKey = null;
bool not = false;
int i = 2;
while (i < words.Count)
{
// Parse optional modifiers
if (words[i].ToUpper() == "IDENTITY")
{
c.IsIdentity = true; c.IsNullable = false;
}
else if (words[i].ToUpper() == "INDEX")
{
c.IsIndex = true;
}
else if (words[i].ToUpper() == "NOT")
{
not = true;
}
else if (words[i].ToUpper() == "NULL")
{
if (not)
{
c.IsNullable = false;
}
else
{
c.IsNullable = true;
}
not = false;
}
else if (words[i].ToUpper() == "FKEY")
{
if (i == words.Count)
{
Log.Me.Error("Missing foreign key after FKEY in " + filename + ". Statement is: " + s);
return;
}
else
{
++i;
c.FKey = words[i];
}
}
else
{
Log.Me.Error("Illegal column modifier " + words[i] + " in " + filename + ". Statement is: " + s);
return;
}
++i;
}
// Check for conflicts
if (c.IsIdentity && c.IsIndex)
{
Log.Me.Error("Identity column cannot also be an Index in " + filename + ". Statement is: " + s);
return;
}
if (c.IsIdentity && c.IsNullable)
{
Log.Me.Error("Identity column cannot be nullable in " + filename + ". Statement is: " + s);
return;
}
if (c.IsIdentity && c.FKey != null)
{
Log.Me.Error("Identity column cannot also be a foreign key in " + filename + ". Statement is: " + s);
return;
}
if (not)
{
Log.Me.Error("NOT on its own without NULL in " + filename + ". Statement is: " + s);
return;
}
table.Columns.Add(c);
}
}
}
}
handle.Close();
}
public static void RestoreRow(XmlReader rdr, TableDefn table, bool idCol, Hashtable hash)
{
string tablename = table.TableName;
bool inCell = false;
string key = null;
string value = null;
while (rdr.Read())
{
if (rdr.NodeType == XmlNodeType.Whitespace)
{
}
else if (rdr.NodeType == XmlNodeType.Element && !inCell)
{
key = rdr.Name; value = null; inCell = true;
}
else if (rdr.NodeType == XmlNodeType.Text && inCell)
{
value = rdr.Value;
}
else if (rdr.NodeType == XmlNodeType.EndElement && inCell && rdr.Name == key)
{
// Save Key and Value
ColumnDefn column = table.FindColumn(key);
if (column != null && column.ColumnType.ToUpper() != "BLOB")
{
string colType = column.CLRType().Name;
string colValue = null;
if ((value == null || value.Length == 0) && column.IsNullable)
{
colValue = "null";
}
else if (colType == "String")
{
colValue = "'" + RemoveNewline(EscapeSlashQuote(value)) + "'";
}
else if (colType == "DateTime")
{
DateTime dt = value.ToDateTime().ToLocalTime();
colValue = "'" + dt.ToString("yyyy-MM-dd'T'HH:mm:ss") + "'";
}
else if (colType == "Boolean")
{
if (value == "true")
{
colValue = "1";
}
else
{
colValue = "0";
}
}
else
{
colValue = value;
}
hash.Add(column.ColumnName, colValue);
}
else
{
throw new Exception("Could not restore column " + key);
}
inCell = false;
}
else if (rdr.NodeType == XmlNodeType.EndElement && !inCell && rdr.Name == tablename)
{
// End node closes row OK
break;
}
else
{
throw new Exception("XML format error in RestoreRow.");
}
}
string[] FVArray = AddSeparator(hash, ',');
string cols = FVArray[0];
string vals = FVArray[1];
string sql = "";
if (idCol)
{
sql += "SET IDENTITY_INSERT " + tablename + " ON; ";
}
sql += "INSERT INTO " + tablename + " (" + cols + ") VALUES (" + vals + "); ";
if (idCol)
{
sql += "SET IDENTITY_INSERT " + tablename + " OFF; ";
}
ExecSQL(sql);
//Clear out Hashtable to handle multiple records
hash.Clear();
}
public static void RestoreDatabase(string server, string database, string repository, Builder definition, string backup)
{
ExecSQL("USE " + database);
// Confirm that tables are empty
foreach (TableDefn t in definition.Tables)
{
if (HasContent(t.TableName))
{
Console.WriteLine("Cannot restore database " + database + " some of its tables have content");
return;
}
}
Hashtable hash = new Hashtable();
BackupFile f = new BackupFile(repository, database);
string archive = null;
if (backup == null)
{
archive = f.ArchiveLatest;
}
else
{
if (backup.Right(4) != ".zip")
{
Console.WriteLine("Cannot restore database " + database + " from " + backup + " - it must be a zip file");
return;
}
archive = repository + Tools.PathDelimiter + backup;
if (!File.Exists(archive))
{
Console.WriteLine("Cannot restore database " + database + " from " + archive + " - it isn't there!");
return;
}
if (File.Exists(f.ChangeExt(archive, "xml")))
{
Console.WriteLine("Cannot restore database " + database + " from " + archive + " - xml file is also in repository.");
return;
}
}
Console.WriteLine($"Using {archive}");
string filename = f.ChangeExt(archive, "xml");
if (File.Exists(filename))
{
File.Delete(filename);
}
Tools.UnZip(archive, repository);
if (!File.Exists(filename))
{
Console.WriteLine("Sleeping for 1000msecs");
Tools.Sleep(1000);
if (!File.Exists(filename))
{
throw new Exception("Backup " + filename + " not in " + archive);
}
}
bool inDatabase = false;
using (XmlReader rdr = XmlReader.Create(f.OpenStream(filename)))
{
while (rdr.Read())
{
if (rdr.NodeType == XmlNodeType.XmlDeclaration && !inDatabase && rdr.Name == "xml")
{
}
else if (rdr.NodeType == XmlNodeType.Whitespace)
{
}
// Confirm we have the Database start node
else if (rdr.NodeType == XmlNodeType.Element && !inDatabase && rdr.Name == database)
{
inDatabase = true;
}
else if (rdr.NodeType == XmlNodeType.Element && inDatabase)
{
TableDefn table = definition.FindTable(rdr.Name);
if (table == null)
{
Console.WriteLine("Could not find table: " + rdr.Name);
System.Environment.Exit(0);
}
Console.WriteLine("Starting to restore table " + table.TableName);
bool idCol = table.HasIdentityColumn && Connection.Me.Type != "MySQL";
RestoreTable(rdr, table, idCol, hash);
}
else if (rdr.NodeType == XmlNodeType.EndElement && inDatabase && rdr.Name == database)
{
// Have found the Database end node
break;
}
else
{
Console.WriteLine("Unexpected node: " + rdr.Name + " type: " + rdr.NodeType.ToString());
System.Environment.Exit(0);
}
}
}
File.Delete(filename);
}
public static bool CheckTable(string tablename, TableDefn table, bool warn)
{
string query;
if (Connection.Me.Type == "MySQL")
{
query = "SELECT * FROM " + tablename + " LIMIT 1;";
}
else
{
query = "SELECT TOP 1 * FROM " + tablename + ";";
}
List <string> fields = new List <string>();
IDbConnection dbConn = Connection.Me.GetConnection();
try
{
dbConn.Open();
IDbCommand dbCmd = dbConn.CreateCommand();
dbCmd.CommandType = CommandType.Text;
dbCmd.CommandText = query;
using (IDataReader rdr = dbCmd.ExecuteReader())
{
while (rdr.Read())
{
for (int i = 0; i < rdr.FieldCount; i++)
{
//Console.WriteLine(rdr.GetName(i) + "=" + rdr.GetValue(i).ToString());
string key = rdr.GetName(i);
Type type = rdr.GetFieldType(i);
ColumnDefn col = table.FindColumn(key);
if (col == null)
{
warn = true;
Console.WriteLine("Database table " + tablename + " has additional column " + key);
}
else
{
if (type.Name != col.CLRType().Name)
{
// For some reason MySQL bit fields come through as UInt64, so don't report that
// and MSSQL renders FLOAT as Doubles, so we'll ignore that too
if (col.ColumnType == "BIT" && type.Name == "UInt64")
{
}
else if (col.ColumnType == "FLOAT" && type.Name == "Double")
{
}
else
{
warn = true;
Console.WriteLine("Table: " + tablename + ", Column: " + key + ", Database Type: " + type.Name + ", Definition: " + col.ColumnType);
}
}
fields.Add(key);
}
}
}
}
if (fields.Count == 0)
{
Console.WriteLine("Cannot check table " + tablename + " because it is empty.");
}
else
{
foreach (ColumnDefn col in table.Columns)
{
if (!fields.Contains(col.ColumnName))
{
warn = true;
Console.WriteLine("Column " + col.ColumnName + " is missing from " + tablename);
}
}
}
return(warn);
}
catch (Exception ex)
{
throw new Exception("There was a problem loading table from query [" + query + "] - " + ex.Message, ex);
}
finally
{
dbConn.Close();
dbConn = null;
}
}
public static void ReportTable(string tablename, TableDefn table, int width)
{
string query = "SELECT " + table.SelectCols() + " FROM " + tablename + ";";
int capacity = 4096;
StringBuilder sb = new StringBuilder(capacity);
int rows = 0;
IDbConnection dbConn = Connection.Me.GetConnection();
try
{
dbConn.Open();
IDbCommand dbCmd = dbConn.CreateCommand();
dbCmd.CommandType = CommandType.Text;
dbCmd.CommandText = query;
using (IDataReader rdr = dbCmd.ExecuteReader())
{
while (rdr.Read())
{
++rows;
for (int i = 0; i < rdr.FieldCount; i++)
{
object fldvalue = rdr.GetValue(i);
if (fldvalue != DBNull.Value)
{
string value = Convert.ChangeType(fldvalue, rdr.GetFieldType(i)).ToString().TrimEnd();
string type = table.FindColumn(rdr.GetName(i)).ColumnType;
if (type == "BIT")
{
if (value == "0" || value.ToLower() == "false")
{
value = "false";
}
else if (value == "1" || value.ToLower() == "true")
{
value = "true";
}
else
{
Console.WriteLine(value);
}
}
else if (type == "DATETIME")
{
value = value.Left(10);
}
if (sb.Length + value.Length > capacity)
{
capacity = capacity * 2;
sb.EnsureCapacity(capacity);
}
sb.Append(EscapeXML(value));
}
}
}
}
if (rows > 0)
{
string hash = MakeHash(sb.ToString());
Console.WriteLine(tablename.PadRight(width) + " has " + rows.ToString().PadRight(5) + " rows and hash=" + hash);
}
else
{
Console.WriteLine(tablename.PadRight(width) + " is empty.");
}
}
catch (Exception ex)
{
throw new Exception("There was a problem loading table from query [" + query + "] - " + ex.Message, ex);
}
finally
{
dbConn.Close();
dbConn = null;
}
}
