/// <summary>
/// Generates SQL to delete tables and columns that are deleted since last version.
/// </summary>
/// <returns>The generated SQL in an SQLUpdateCommand.</returns>
public SqlUpdateCommand[] GenerateSqlDeleteDiff()
{
// Ta bort databaskolumner som har tagits bort
List <SqlUpdateCommand> updateCommands = new List <SqlUpdateCommand>();
DatabaseSchema fromDatabase = new DatabaseSchema(DatabaseDefinition.GetDatabaseXmlDefinition(Version - 1));
foreach (var fromTable in fromDatabase.Tables)
{
var toTable = GetTable(fromTable.TableName);
if (toTable == null)
{
updateCommands.Add(fromTable.GenerateSqlDropTableNoConstraints());
}
else
{
// Kolla alla kolumner, om någon inte finns i den nya databasen ska den bort.
foreach (var fromField in fromTable.AllFields)
{
FieldSchema field = toTable.GetField(fromField.FieldName);
// Om fältet har tagits bort i den aktuella databasen, eller har omvandlats till ett vyfält i den nya, ska den bort från databasen.
if (field == null || field.IsViewField)
{
updateCommands.Add(fromField.GenerateSqlDropDefaultValueConstraint());
updateCommands.Add(fromField.GenerateSqlDropFieldNoKeys());
}
}
}
}
return(updateCommands.ToArray());
}
public override bool Equals(object obj)
{
if (obj == null)
{
return(false);
}
FieldSchema field2 = obj as FieldSchema;
// Kolla igenom alla egenskaper och jämför dem
if (TableName != field2.TableName)
{
return(false);
}
if (FieldName != field2.FieldName)
{
return(false);
}
if (FieldType != field2.FieldType)
{
return(false);
}
if (AllowNull != field2.AllowNull)
{
return(false);
}
return(true);
}
public static bool operator ==(UniqueKey uniqueKey1, UniqueKey uniqueKey2)
{
if ((object)uniqueKey1 == null && (object)uniqueKey2 == null)
{
return(true);
}
else if ((object)uniqueKey1 == null || (object)uniqueKey2 == null)
{
return(false);
}
if (uniqueKey1.Fields.Count != uniqueKey2.Fields.Count)
{
return(false);
}
// Kolla igenom alla egenskaper och jämför dem
if (uniqueKey1.TableName != uniqueKey2.TableName)
{
return(false);
}
if (uniqueKey1.UniqueKeyGroup != uniqueKey2.UniqueKeyGroup)
{
return(false);
}
foreach (FieldSchema field1 in uniqueKey1.Fields)
{
FieldSchema field2 = uniqueKey2.GetField(field1.FieldName);
if (field1 != field2)
{
return(false);
}
}
return(true);
}
/// <summary>
/// Adds a field to a unique key in a list of unique keys. If the unique key is not in the list, a new unique key will be added to the list.
/// </summary>
/// <param name="uniqueKeys">List of unique keys.</param>
/// <param name="tableName">Name of the current database table.</param>
/// <param name="uniqueKeyGroup">The group ID of the unique key within its entity.</param>
/// <param name="field">Field to att do unique key.</param>
private void AddToUniqueKeys(List <UniqueKey> uniqueKeys, string tableName, int uniqueKeyGroup, FieldSchema field)
{
foreach (UniqueKey uniqueKey in uniqueKeys)
{
if (uniqueKey.TableName == tableName && uniqueKey.UniqueKeyGroup == uniqueKeyGroup)
{
uniqueKey.Fields.Add(field);
return;
}
}
// If we get here, there was no appropriate unique key in collection, so we need to add one
UniqueKey newUniqueKey = new UniqueKey(tableName, uniqueKeyGroup);
newUniqueKey.Fields.Add(field);
uniqueKeys.Add(newUniqueKey);
}
private void ParseXml(string xmlString)
{
XmlReader reader = XmlReader.Create(new StringReader(xmlString));
reader.ReadToFollowing("Database");
if (reader.MoveToAttribute("Version"))
{
int version;
if (int.TryParse(reader.Value, out version))
{
Version = version;
}
else
{
throw new DatabaseSchemaException("Database version tag is unreadable.");
}
}
else
{
throw new DatabaseSchemaException("Database tag is missing its version attribute.");
}
while (reader.ReadToFollowing("Table"))
{
string tableName = "";
bool isView = false;
List <FieldSchema> fields = new List <FieldSchema>();
List <UniqueKey> uniqueKeys = new List <UniqueKey>();
while (reader.MoveToNextAttribute())
{
string attributeName = reader.Name;
switch (attributeName)
{
case "Name":
tableName = reader.Value;
break;
case "IsView":
isView = (reader.Value == "true");
break;
default:
throw new NotImplementedException("'" + attributeName + "' on table '" + tableName + "' is not a valid table attribute.");
}
}
if (tableName == "")
{
throw new DatabaseSchemaException("Table must have a name.");
}
while (reader.Read() && reader.NodeType != XmlNodeType.Element && reader.NodeType != XmlNodeType.EndElement)
{
;
}
if (reader.NodeType != XmlNodeType.EndElement && !reader.EOF)
{
if (reader.Name == "Fields")
{
while (reader.Read() && reader.NodeType != XmlNodeType.Element && reader.NodeType != XmlNodeType.EndElement)
{
;
}
while (reader.NodeType != XmlNodeType.EndElement && !reader.EOF)
{
FieldSchema field;
if (reader.Name == "PrimaryKeyField")
{
string fieldName = "";
string fieldType = "";
bool allowNull = false;
bool isIdentity = false;
while (reader.MoveToNextAttribute())
{
string attributeName = reader.Name;
switch (attributeName)
{
case "Name":
fieldName = reader.Value;
break;
case "Type":
fieldType = reader.Value;
break;
case "AllowNull":
allowNull = Convert.ToBoolean(reader.Value);
break;
case "IsIdentity":
isIdentity = Convert.ToBoolean(reader.Value);
break;
default:
throw new NotImplementedException("'" + attributeName + "' on field '" + fieldName + "' on table '" + tableName + "' is not a valid primary key field attribute.");
}
}
if (fieldName == "")
{
throw new DatabaseSchemaException("Primary key field for table '" + tableName + "' must have a name.");
}
if (fieldType == "")
{
throw new DatabaseSchemaException("Primary key field '" + fieldName + "' on table '" + tableName + "' must have a type.");
}
field = new PrimaryKeyFieldSchema(tableName, fieldName, fieldType, allowNull, isIdentity);
}
else if (reader.Name == "ForeignKeyField")
{
string fieldName = "";
string fieldType = "";
string targetTableName = "";
string targetFieldName = "";
bool allowNull = false;
List <int> uniqueKeyGroups = new List <int>();
while (reader.MoveToNextAttribute())
{
string attributeName = reader.Name;
switch (attributeName)
{
case "Name":
fieldName = reader.Value;
break;
case "Type":
fieldType = reader.Value;
break;
case "TargetTable":
targetTableName = reader.Value;
break;
case "TargetField":
targetFieldName = reader.Value;
break;
case "AllowNull":
allowNull = Convert.ToBoolean(reader.Value);
break;
case "UniqueKeyGroup":
string[] keyGroups = reader.Value.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
foreach (string sKeyGroup in keyGroups)
{
uniqueKeyGroups.Add(int.Parse(sKeyGroup));
}
break;
default:
throw new NotImplementedException("'" + attributeName + "' on field '" + fieldName + "' on table '" + tableName + "' is not a valid foreign key field attribute.");
}
}
if (fieldName == "")
{
throw new DatabaseSchemaException("Foreign key field for table '" + tableName + "' must have a name.");
}
if (fieldType == "")
{
throw new DatabaseSchemaException("Foreign key field '" + fieldName + "' on table '" + tableName + "' must have a type.");
}
if (targetTableName == "")
{
throw new DatabaseSchemaException("Foreign key field '" + fieldName + "' on table '" + tableName + "' must have a target table.");
}
if (targetFieldName == "")
{
throw new DatabaseSchemaException("Foreign key field '" + fieldName + "' on table '" + tableName + "' must have a target field.");
}
field = new ForeignKeyFieldSchema(tableName, fieldName, fieldType, targetTableName, targetFieldName, allowNull);
if (uniqueKeyGroups.Count > 0)
{
foreach (int uniqueKeyGroup in uniqueKeyGroups)
{
AddToUniqueKeys(uniqueKeys, tableName, uniqueKeyGroup, field);
}
}
}
else if (reader.Name == "Field")
{
string fieldName = "";
string fieldType = "";
bool allowNull = false;
List <int> uniqueKeyGroups = new List <int>();
while (reader.MoveToNextAttribute())
{
string attributeName = reader.Name;
switch (attributeName)
{
case "Name":
fieldName = reader.Value;
break;
case "Type":
fieldType = reader.Value;
break;
case "AllowNull":
allowNull = Convert.ToBoolean(reader.Value);
break;
case "UniqueKeyGroup":
string[] keyGroups = reader.Value.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
foreach (string sKeyGroup in keyGroups)
{
uniqueKeyGroups.Add(int.Parse(sKeyGroup));
}
break;
default:
throw new NotImplementedException("'" + attributeName + "' on field '" + fieldName + "' on table '" + tableName + "' is not a valid field attribute.");
}
}
if (fieldName == "")
{
throw new DatabaseSchemaException("Field on table '" + tableName + "' must have a name.");
}
if (fieldType == "")
{
throw new DatabaseSchemaException("Field '" + fieldName + "' table '" + tableName + "' must have a type.");
}
field = new FieldSchema(tableName, fieldName, fieldType, allowNull);
if (uniqueKeyGroups.Count > 0)
{
foreach (int uniqueKeyGroup in uniqueKeyGroups)
{
AddToUniqueKeys(uniqueKeys, tableName, uniqueKeyGroup, field);
}
}
}
else if (reader.Name == "ViewField")
{
string fieldName = "";
string fieldType = "";
bool allowNull = false;
while (reader.MoveToNextAttribute())
{
string attributeName = reader.Name;
switch (attributeName)
{
case "Name":
fieldName = reader.Value;
break;
case "Type":
fieldType = reader.Value;
break;
case "AllowNull":
allowNull = Convert.ToBoolean(reader.Value);
break;
default:
throw new NotImplementedException("'" + attributeName + "' on field '" + fieldName + "' on table '" + tableName + "' is not a valid field attribute.");
}
}
if (fieldName == "")
{
throw new DatabaseSchemaException("Field on table '" + tableName + "' must have a name.");
}
if (fieldType == "")
{
throw new DatabaseSchemaException("Field '" + fieldName + "' table '" + tableName + "' must have a type.");
}
field = new ViewFieldSchema(tableName, fieldName, fieldType, allowNull);
}
else
{
throw new DatabaseSchemaException("When initiating table '" + tableName + "', tags '<PrimaryKeyField>, <ForeighKeyField> or <Field>' was expected, but got tag '<" + reader.Name + ">'");
}
fields.Add(field);
while (reader.Read() && reader.NodeType != XmlNodeType.Element && reader.NodeType != XmlNodeType.EndElement)
{
;
}
}
}
else
{
throw new DatabaseSchemaException("When initiating table '" + tableName + "', tag '<Fields>' was expected, but got tag '<" + reader.Name + ">'");
}
Tables.Add(new TableSchema(tableName, isView, fields.ToArray(), uniqueKeys.ToArray()));
}
}
}
public static bool operator ==(TableSchema table1, TableSchema table2)
{
if ((object)table1 == null && (object)table2 == null)
{
return(true);
}
else if ((object)table1 == null || (object)table2 == null)
{
return(false);
}
// Om tabellerna har olika antal fält så är det olika
if (table1.AllFields.Count != table2.AllFields.Count)
{
return(false);
}
// Kolla igenom alla fält och jämför dem
foreach (FieldSchema field1 in table1.AllFields)
{
FieldSchema field2 = table2.GetField(field1.FieldName);
// Om fältet inte finns i den andra tabellen
if (field2 == null)
{
return(false);
}
// Om fälten är olika så returnera false
if (field1 != field2)
{
return(false);
}
}
// Om tabellerna har olika antal unika nycklar så är de olika
if (table1.UniqueKeys.Count != table2.UniqueKeys.Count)
{
return(false);
}
// Kolla igenom alla unika nycklar och jämför dem
foreach (UniqueKey uniqueKey1 in table1.UniqueKeys)
{
UniqueKey uniqueKey2 = table2.GetUniqueKey(uniqueKey1.UniqueKeyGroup);
// Om den unika nyckeln inte finns i den andra tabellen
if (uniqueKey2 == null)
{
return(false);
}
// Om fälten är olika så returnera false
if (uniqueKey1 != uniqueKey2)
{
return(false);
}
}
return(true);
}
public void RestoreBackup2(StreamReader reader, DatabaseSchema database, int backupVersion, long totalRowsInDB)
{
char token = ' ';
char lastToken = ' ';
StringBuilder tableHeader = new StringBuilder();
StringBuilder fieldName = new StringBuilder();
StringBuilder sBackupVersion = new StringBuilder();
StringBuilder sDatabaseVersion = new StringBuilder();
TableSchema currentTable = null;
FieldSchema currentField = null;
bool readingTableHeader = false;
bool readingFieldName = false;
string tableName = "";
int rowCount = 0;
DataTable dataTable = new DataTable();
bool readingFieldData = false;
StringBuilder fieldColumn = new StringBuilder();
bool expectingColumnName = false;
char lastLastToken = ' ';
int fieldsRead = 0;
int tableCount = 0;
while (true)
{
if (!reader.EndOfStream)
{
token = (char)reader.Read();
}
if (token == '[' && lastToken != '\\' && !readingTableHeader && !expectingColumnName && !readingFieldData) // Start of table header
{
readingTableHeader = true;
tableHeader = new StringBuilder();
}
else if (token == '[' && lastToken != '\\' && !readingTableHeader && expectingColumnName) // Start of table header
{
fieldName = new StringBuilder();
readingFieldName = true;
expectingColumnName = false;
}
else if (token == '[' && ((lastToken == '¤' && lastLastToken != '\\' && readingFieldData) || (readingFieldData && currentField.FieldType == "bit")) || reader.EndOfStream) // End of column data
{
readingFieldData = false;
string sData;
// Liten fuling
if (reader.EndOfStream)
{
fieldColumn.Append(token);
}
if (currentField.FieldType != "bit")
{
sData = fieldColumn.ToString().Substring(0, fieldColumn.ToString().Length - 1);
}
else
{
sData = fieldColumn.ToString();
}
string[] array = currentField.SplitDataString(sData);
for (int i = 0; i < rowCount; i++)
{
dataTable.Rows[i][fieldName.ToString()] = array[i];
}
fieldsRead++;
if (fieldsRead == database.GetTable(tableName).DatabaseFields.Count)
{
if (currentTable.PrimaryKey.IsIdentity)
{
Connection.Execute("set identity_insert " + currentTable.TableName + " on;");
}
// Save the table in the database
foreach (DataRow dataRow in dataTable.Rows)
{
string comma = "";
StringBuilder commaSeparatedFieldNames = new StringBuilder();
foreach (FieldSchema f in currentTable.DatabaseFields)
{
commaSeparatedFieldNames.Append(comma + f.FieldName);
comma = ", ";
}
comma = "";
StringBuilder commaSeparatedFieldValues = new StringBuilder();
foreach (FieldSchema f in currentTable.DatabaseFields)
{
commaSeparatedFieldValues.Append(comma + f.DeSerializeData((string)dataRow[f.FieldName]));
comma = ", ";
}
string sql = "insert into [" + currentTable.TableName + "] (" + commaSeparatedFieldNames + ") values (" + commaSeparatedFieldValues + ");";
Connection.Execute(sql);
}
if (currentTable.PrimaryKey.IsIdentity)
{
Connection.Execute("set identity_insert " + currentTable.TableName + " off;");
}
tableCount += 1;
// OnProgress(tableCount, database.Tables.Count());
// Reset most values
fieldsRead = 0;
readingTableHeader = true;
tableHeader = new StringBuilder();
}
else
{
readingFieldName = true;
fieldName = new StringBuilder();
fieldColumn = new StringBuilder();
}
if (reader.EndOfStream)
{
break;
}
}
else if (token == ']' && lastToken != '\\' && readingTableHeader) // End of table header
{
readingTableHeader = false;
string[] array = tableHeader.ToString().Split(";".ToCharArray());
tableName = array[0];
currentTable = database.GetTable(tableName);
rowCount = int.Parse(array[1]);
OnProgress(tableCount, database.Tables.Count(), "Reading table '" + tableName + "'");
// Init data table
dataTable = new DataTable();
foreach (FieldSchema field in currentTable.DatabaseFields)
{
dataTable.Columns.Add(field.FieldName, typeof(string));
}
for (int i = 1; i <= rowCount; i++)
{
DataRow dataRow = dataTable.NewRow();
dataTable.Rows.Add(dataRow);
}
if (rowCount > 0)
{
fieldColumn = new StringBuilder();
expectingColumnName = true;
}
}
else if (token == ']' && lastToken != '\\' && readingFieldName) // End of field header
{
readingFieldName = false;
readingFieldData = true;
currentField = currentTable.GetField(fieldName.ToString());
}
else if (readingTableHeader)
{
tableHeader.Append(token);
}
else if (readingFieldName)
{
fieldName.Append(token);
}
else if (readingFieldData)
{
fieldColumn.Append(token);
}
lastLastToken = lastToken;
lastToken = token;
}
reader.Close();
if (tableCount < database.Tables.Count)
{
tableCount = database.Tables.Count;
OnProgress(tableCount, database.Tables.Count, "Restore completed!");
}
// Uppdatera databasen till senaste versionen
//RunSqlUpdateCommands(CollectSqlUpdateDatabase(false));
}
