/// <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());
}
/// <summary>
/// Generates SQL update commands (scripts) that update the database one step to a specified version.
/// Database constraints must be removed before call to this function, and added again afterwards.
/// </summary>
/// <param name="currentVersion">Current version of the database.</param>
/// <exception cref="InvalidOperationException">Thrown if the database version too high.</exception>
private SqlUpdateCommand[] UpdateDatabaseOneVersionNoConstraints(int currentVersion)
{
int toVersion = currentVersion + 1;
if (toVersion > DatabaseDefinition.DatabaseVersion)
{
throw new InvalidOperationException("Database cannot be updated to a version greater than " + DatabaseDefinition.DatabaseVersion + ".");
}
// Hämta aktuellt databasschema
DatabaseSchema fromDatabase = new DatabaseSchema(DatabaseDefinition.GetDatabaseXmlDefinition(toVersion - 1));
DatabaseSchema toDatabase = new DatabaseSchema(DatabaseDefinition.GetDatabaseXmlDefinition(toVersion));
List <SqlUpdateCommand> updateCommands = new List <SqlUpdateCommand>();
// Add information about the update to the database
updateCommands.Add(new SqlUpdateCommand("update EasyBaseSystems set UpdatingToVersion = " + toVersion));
// Add update commands for new tables and columns
updateCommands.Add(toDatabase.GenerateSqlCreateDiff());
// Add developer update scripts/functions for the current database update
SqlUpdateCommand databaseConversion = DatabaseConversions.ConvertDatabase(toVersion);
if (databaseConversion != null)
{
updateCommands.Add(databaseConversion);
}
// Add update commands that deletes removed tables and columns
updateCommands.AddRange(toDatabase.GenerateSqlDeleteDiff());
return(updateCommands.ToArray());
}
private SqlUpdateCommand[] CollectSqlUpdateDatabase(bool databaseContainsConstraints, int toDatabaseVersion)
{
var updateCommands = new List <SqlUpdateCommand>();
int startVersion = (int)Connection.GetScalar("select top 1 DatabaseVersion from EasyBaseSystems");
if (toDatabaseVersion < startVersion)
{
throw new UpdatingDatabaseException("Du försöker att uppdatera databas med version " + startVersion + " till version " + toDatabaseVersion + ". Det är inte möjligt att nedgradera en databasversion på det sättet.");
}
var startDatabase = new DatabaseSchema(DatabaseDefinition.GetDatabaseXmlDefinition(startVersion));
var endDatabase = new DatabaseSchema(DatabaseDefinition.GetDatabaseXmlDefinition(toDatabaseVersion));
if (databaseContainsConstraints)
{
updateCommands.AddRange(startDatabase.GenerateSqlDropConstraints());
updateCommands.AddRange(endDatabase.GenerateSqlDropConstraints()); // Måste med eftersom uppdateringen kan krascha halvvägs
}
updateCommands.AddRange(GenerateSqlUpdateDatabaseToVersionNoConstraints(toDatabaseVersion));
updateCommands.AddRange(endDatabase.GenerateSqlAddDatabaseConstraints());
// Uppdatera vyer
updateCommands.AddRange(endDatabase.GenerateSqlCreateAllViews());
// Bekräfta den nya versionen i databasen
updateCommands.Add(new SqlUpdateCommand("update EasyBaseSystems set DatabaseVersion = " + toDatabaseVersion));
return(updateCommands.ToArray());
}
/// <summary>
/// Checks that the coded database and the current database schema match.
/// </summary>
/// <returns>true if the databases agree, otherwise false.</returns>
public bool CheckDatabase()
{
var savedDatabase = new DatabaseSchema(DatabaseDefinition.GetDatabaseXmlDefinition(DatabaseDefinition.DatabaseVersion));
var codedDatabase = new DatabaseSchema(GetDatabaseDefinitionXml());
return(savedDatabase == codedDatabase);
}
public void CreateBackup1(string fullPath)
{
// Current version of backup system
int backupVersion = 1;
string sql = "select top 1 [DatabaseVersion] from [EasyBaseSystems]";
int databaseVersion = Convert.ToInt32(Connection.GetScalar(sql));
if (databaseVersion != DatabaseDefinition.DatabaseVersion)
{
throw new DatabaseVersionException("Wrong database version. The database's version is " + databaseVersion + ", but the current version of the application is " + DatabaseDefinition.DatabaseVersion + ". Backup is not possible.");
}
DatabaseSchema savedDatabase = new DatabaseSchema(DatabaseDefinition.GetDatabaseXmlDefinition(DatabaseDefinition.DatabaseVersion));
StreamWriter streamWriter = new StreamWriter(fullPath);
streamWriter.Write("[" + backupVersion + ";" + DatabaseDefinition.DatabaseVersion + "]");
foreach (TableSchema table in savedDatabase.Tables)
{
StringBuilder commaSeparatedFields = new StringBuilder();
string comma = "";
foreach (FieldSchema field in table.DatabaseFields)
{
commaSeparatedFields.Append(comma + "[" + field.FieldName + "]");
comma = ", ";
}
sql = "select " + commaSeparatedFields + " from [" + table.TableName + "]";
DataTable dataTable = new DataTable();
Connection.GetTable(dataTable, sql);
streamWriter.Write("[" + table.TableName + ";" + dataTable.Rows.Count + "]");
if (dataTable.Rows.Count > 0)
{
foreach (FieldSchema field in table.DatabaseFields)
{
streamWriter.Write("[" + field.FieldName + "]");
foreach (DataRow dataRow in dataTable.Rows)
{
string data = field.SerializeData(dataRow[field.FieldName]);
streamWriter.Write(data);
}
}
}
}
streamWriter.Flush();
streamWriter.Close();
}
private string[] GetUpdatedViewTables(int startVersion, int endVersion)
{
var viewTables = new List <string>();
for (int version = startVersion + 1; version <= endVersion; version++)
{
var database = new DatabaseSchema(DatabaseDefinition.GetDatabaseXmlDefinition(version));
viewTables.AddRange(database.GetTableViewDiff());
}
return(viewTables.ToArray());
}
/// <summary>
/// Skapar en databas.
/// </summary>
/// <param name="version">Vilken databasversion som ska skapas.</param>
private void CreateDatabaseNoConstraints(int version)
{
DropAllTablesInDatabase();
DatabaseSchema database = new DatabaseSchema(DatabaseDefinition.GetDatabaseXmlDefinition(version));
//DatabaseSchema database = new DatabaseSchema(GetDatabaseDefinitionXml());
string[] sqls = database.GenerateSqlCreateNoKeys(DatabaseDefinition.DatabaseVersion);
foreach (string sql in sqls)
{
Connection.Execute(sql);
}
}
/// <summary>
/// Gets all table views that are changed between last version and current version.
/// </summary>
/// <returns>All names of changed table views as a string array.</returns>
public string[] GetTableViewDiff()
{
List <string> viewTables = new List <string>();
DatabaseSchema fromDatabase = new DatabaseSchema(DatabaseDefinition.GetDatabaseXmlDefinition(Version - 1));
foreach (var viewTable in ViewTables)
{
var fromTable = fromDatabase.GetTable(viewTable.TableName);
if (fromTable == null)
{
viewTables.Add(viewTable.TableName);
}
else
{
foreach (var field in viewTable.AllFields)
{
if (fromTable.GetField(field.FieldName) == null)
{
viewTables.Add(viewTable.TableName);
}
}
}
}
foreach (var viewTable in fromDatabase.ViewTables)
{
var toTable = GetTable(viewTable.TableName);
if (toTable == null)
{
viewTables.Add(viewTable.TableName);
}
else
{
foreach (var field in viewTable.AllFields)
{
if (toTable.GetField(field.FieldName) == null)
{
viewTables.Add(viewTable.TableName);
}
}
}
}
return(viewTables.ToArray());
}
/// <summary>
/// Bygger upp alla tabeller och vyer i en databas i enlighet med den senaste versionen. Databasen måste finnas innan den här funktionen används.
/// </summary>
public void BuildDatabase()
{
DropAllTablesInDatabase();
DatabaseSchema database = new DatabaseSchema(DatabaseDefinition.GetDatabaseXmlDefinition(DatabaseDefinition.DatabaseVersion));
string[] sqls = database.GenerateSqlCreate();
foreach (string sql in sqls)
{
Connection.Execute(sql);
}
// Write the database table
string startSql = "insert into EasyBaseSystems values (" + (int)EasyBase.Classes.EasyBaseSystemNo.CurrentApplicationNo + ", " + DatabaseDefinition.DatabaseVersion + ", " + DatabaseDefinition.DatabaseVersion + ")";
Connection.Insert(startSql);
InitDatabaseOnCreate();
}
/// <summary>
/// Generates SQL to create tables and columns that are added since last version.
/// </summary>
/// <returns>The generated SQL in an SqlUpdateCommand.</returns>
public SqlUpdateCommand GenerateSqlCreateDiff()
{
List <string> sqls = new List <string>();
DatabaseSchema fromDatabase = new DatabaseSchema(DatabaseDefinition.GetDatabaseXmlDefinition(Version - 1));
// Loopa igenom alla tabeller i nya databasen
foreach (var table in Tables)
{
var fromTable = fromDatabase.GetTable(table.TableName);
if (fromTable == null)
{
// Tabellen finns inte i gamla databasen, så den behöver skapas.
sqls.Add(table.GenerateSqlCreateTable());
}
else
{
// Tabellen finns. Kolla igenom tabellen för att se om det har tillkommit några nya kolumner.
foreach (var field in table.AllFields)
{
var fromField = fromTable.GetField(field.FieldName);
if (fromField == null)
{
// Fältet fanns inte i den gamla databasen, så det behöver skapas.
sqls.AddRange(field.GenerateSqlCreateField());
}
}
}
}
return(new SqlUpdateCommand(sqls.ToArray()));
}
public void RestoreBackup(string fullPath, int toDatabaseVersion)
{
var reader = new StreamReader(fullPath);
var header = new StringBuilder();
int backupVersion = 0;
int databaseVersion = 0;
string databaseApplicationName = "";
long totalRowsInDB = 0;
try {
// Read header
char token = ' ';
while (token != ']')
{
token = (char)reader.Read();
if (token != '[' && token != ']')
{
header.Append(token);
}
}
string[] headerArray = header.ToString().Split(";".ToCharArray());
backupVersion = int.Parse(headerArray[0]);
databaseVersion = 0;
databaseApplicationName = "";
if (backupVersion == 1)
{
databaseVersion = int.Parse(headerArray[1]);
}
else
{
databaseApplicationName = headerArray[1];
databaseVersion = int.Parse(headerArray[2]);
totalRowsInDB = long.Parse(headerArray[3]);
}
}
catch (Exception ex) {
throw new BackupException("Backupfilens huvud har ett felaktigt format.", ex);
}
if (backupVersion >= 2)
{
if (databaseApplicationName != CurrentApplication.Name)
{
throw new BackupException("Den aktuella backupen tillhör inte det här programmet. Backupen är en backup för programmet \"" + databaseApplicationName + "\".");
}
}
// Create database
var database = new DatabaseSchema(DatabaseDefinition.GetDatabaseXmlDefinition(databaseVersion));
CreateDatabaseNoConstraints(databaseVersion);
// Call function that reads all table contents from the backup file.
switch (backupVersion)
{
case 1:
RestoreBackup1(reader, database, backupVersion);
break;
case 2:
RestoreBackup2(reader, database, backupVersion, totalRowsInDB);
break;
default:
throw new NotImplementedException("Backup version " + backupVersion + " is not implemented.");
}
// Efter inläsning, uppdatera databasen till senaste versionen
RunSqlUpdateCommands(CollectSqlUpdateDatabase(false, toDatabaseVersion));
}
