/// <summary>
/// write the file clean.sql that removes all data from the database, for easy resetting of the database with clean test data
/// </summary>
/// <param name="AStore"></param>
/// <param name="AFilename"></param>
public static void WriteDBClean(TDataDefinitionStore AStore, string AFilename)
{
StreamWriter sw = new StreamWriter(AFilename + ".new");
sw.WriteLine("-- Generated with nant generateORMTables");
List <TTable> tables = AStore.GetTables();
tables = TTableSort.TopologicalSort(AStore, tables);
tables.Reverse();
foreach (TTable t in tables)
{
sw.WriteLine("DELETE FROM " + t.strName + ";");
}
sw.Close();
TTextFile.UpdateFile(AFilename);
}
/// <summary>
/// write an ordered list of tables, ordered by foreign key dependancies
/// </summary>
/// <param name="AStore"></param>
/// <param name="AFilename"></param>
public static void WriteTableList(TDataDefinitionStore AStore, string AFilename)
{
string templateDir = TAppSettingsManager.GetValue("TemplateDir", true);
ProcessTemplate Template = new ProcessTemplate(templateDir + Path.DirectorySeparatorChar +
"ORM" + Path.DirectorySeparatorChar +
"TableList.cs");
// load default header with license and copyright
Template.SetCodelet("GPLFILEHEADER", ProcessTemplate.LoadEmptyFileComment(templateDir));
List <TTable> tables = AStore.GetTables();
tables = TTableSort.TopologicalSort(AStore, tables);
string namesCodelet = string.Empty;
string namesCodeletCustomReport = string.Empty;
foreach (TTable t in tables)
{
namesCodelet += "list.Add(\"" + t.strName + "\");" + Environment.NewLine;
if (t.AvailableForCustomReport)
{
namesCodeletCustomReport += "list.Add(\"" + t.strName + "\");" + Environment.NewLine;
}
}
Template.AddToCodelet("DBTableNames", namesCodelet);
Template.AddToCodelet("DBTableNamesAvailableForCustomReport", namesCodeletCustomReport);
List <TSequence> Sequences = AStore.GetSequences();
namesCodelet = string.Empty;
foreach (TSequence s in Sequences)
{
namesCodelet += "list.Add(\"" + s.strName + "\");" + Environment.NewLine;
}
Template.AddToCodelet("DBSequenceNames", namesCodelet);
Template.FinishWriting(AFilename, ".cs", true);
}
/// <summary>
/// write the file clean.sql that removes all data from the database, for easy resetting of the database with clean test data
/// </summary>
/// <param name="AStore"></param>
/// <param name="AFilename"></param>
private static void WriteDBClean(TDataDefinitionStore AStore, string AFilename)
{
StreamWriter sw = new StreamWriter(AFilename + ".new");
sw.WriteLine("-- Generated with nant createSQLStatements");
List <TTable> tables = AStore.GetTables();
tables = TTableSort.TopologicalSort(AStore, tables);
tables.Reverse();
foreach (TTable t in tables)
{
sw.WriteLine("DELETE FROM " + t.strName + ";");
}
sw.Close();
TTextFile.UpdateFile(AFilename);
System.Console.WriteLine("Success: file written: {0}", AFilename);
}
/// <summary>
/// write the file clean.sql that removes all data from the database, for easy resetting of the database with clean test data
/// </summary>
/// <param name="AStore"></param>
/// <param name="AFilename"></param>
private static void WriteDBClean(TDataDefinitionStore AStore, string AFilename)
{
StreamWriter sw = new StreamWriter(AFilename + ".new");
sw.WriteLine("-- Generated with nant createSQLStatements");
List <TTable> tables = AStore.GetTables();
tables = TTableSort.TopologicalSort(AStore, tables);
tables.Reverse();
// drop link between s_user and p_partner, for example used for self-service
sw.WriteLine("UPDATE s_user SET p_partner_key_n = NULL;");
foreach (TTable t in tables)
{
sw.WriteLine("DELETE FROM " + t.strName + ";");
}
sw.Close();
TTextFile.UpdateFile(AFilename);
System.Console.WriteLine("Success: file written: {0}", AFilename);
}
/// <summary>
/// create a database file or the scripts for creating a database.
/// for SQLite that is directly the file,
/// for the other database systems the sql create table scripts etc are written
/// </summary>
/// <param name="AStore"></param>
/// <param name="ATargetDatabase"></param>
/// <param name="AOutputFile"></param>
public static void WriteSQL(TDataDefinitionStore AStore, eDatabaseType ATargetDatabase, String AOutputFile)
{
System.Console.WriteLine("Writing file to {0}...", AOutputFile);
FileStream outPutFileStream = new FileStream(AOutputFile, FileMode.Create, FileAccess.Write);
StreamWriter sw = new StreamWriter(outPutFileStream);
if (ATargetDatabase == eDatabaseType.MySQL)
{
sw.WriteLine("SET AUTOCOMMIT=0;");
sw.WriteLine("SET FOREIGN_KEY_CHECKS=0;");
}
List <TTable> Tables = AStore.GetTables();
Tables = TTableSort.TopologicalSort(AStore, Tables);
foreach (TTable Table in Tables)
{
if (!WriteTable(ATargetDatabase, sw, Table, true))
{
Environment.Exit(1);
}
}
if (ATargetDatabase == eDatabaseType.PostgreSQL)
{
foreach (TTable Table in Tables)
{
DumpConstraints(sw, Table, eInclude.eOnlyForeign, true);
}
}
WriteSequences(sw, ATargetDatabase, AStore, true);
sw.Close();
System.Console.WriteLine("Success: file written: {0}", AOutputFile);
string createTablesWithoutConstraints =
Path.GetDirectoryName(AOutputFile) + Path.DirectorySeparatorChar +
Path.GetFileNameWithoutExtension(AOutputFile) + "_withoutConstraints.sql";
outPutFileStream = new FileStream(
createTablesWithoutConstraints,
FileMode.Create, FileAccess.Write);
sw = new StreamWriter(outPutFileStream);
if (ATargetDatabase == eDatabaseType.PostgreSQL)
{
sw.WriteLine("SET client_min_messages TO WARNING;");
}
else if (ATargetDatabase == eDatabaseType.MySQL)
{
sw.WriteLine("SET AUTOCOMMIT=0;");
sw.WriteLine("SET FOREIGN_KEY_CHECKS=0;");
}
foreach (TTable Table in Tables)
{
if (!WriteTable(ATargetDatabase, sw, Table))
{
Environment.Exit(1);
}
}
WriteSequences(sw, ATargetDatabase, AStore, true);
sw.Close();
System.Console.WriteLine("Success: file written: {0}", createTablesWithoutConstraints);
string removeAllTablesFile =
Path.GetDirectoryName(AOutputFile) + Path.DirectorySeparatorChar +
Path.GetFileNameWithoutExtension(AOutputFile) + "_remove.sql";
outPutFileStream = new FileStream(
removeAllTablesFile,
FileMode.Create, FileAccess.Write);
sw = new StreamWriter(outPutFileStream);
Tables = TTableSort.TopologicalSort(AStore, Tables);
Tables.Reverse();
if (ATargetDatabase == eDatabaseType.MySQL)
{
sw.WriteLine("SET foreign_key_checks = 0;");
}
foreach (TTable Table in Tables)
{
sw.WriteLine("DROP TABLE IF EXISTS " + Table.strName + " CASCADE;");
}
if (ATargetDatabase == eDatabaseType.Sqlite)
{
// see http://www.mail-archive.com/[email protected]/msg05123.html
// see http://www.sqlite.org/faq.html#q1
// no sequences in sqlite
}
else if (ATargetDatabase == eDatabaseType.MySQL)
{
// also no sequences in Mysql
// see http://dev.mysql.com/doc/refman/5.0/en/information-functions.html for a workaround
// look for CREATE TABLE sequence and LAST_INSERT_ID
List <TSequence> Sequences = AStore.GetSequences();
foreach (TSequence Sequence in Sequences)
{
sw.WriteLine("DROP TABLE IF EXISTS " + Sequence.strName + ";");
}
}
else
{
List <TSequence> Sequences = AStore.GetSequences();
foreach (TSequence Sequence in Sequences)
{
sw.WriteLine("DROP SEQUENCE IF EXISTS " + Sequence.strName + ";");
}
}
sw.Close();
System.Console.WriteLine("Success: file written: {0}", removeAllTablesFile);
WriteDBClean(AStore, Path.GetDirectoryName(AOutputFile) + Path.DirectorySeparatorChar +
Path.GetFileNameWithoutExtension(AOutputFile) + "_clean.sql");
string LoadDataFile = System.IO.Path.GetDirectoryName(AOutputFile) +
System.IO.Path.DirectorySeparatorChar +
"loaddata.sh";
outPutFileStream = new FileStream(LoadDataFile,
FileMode.Create, FileAccess.Write);
sw = new StreamWriter(outPutFileStream);
// todo: sw.WriteLine("echo \"load tables\"; psql petra < petra0203_tables.sql");
foreach (TTable Table in Tables)
{
// Dump indexes
sw.WriteLine("echo " + Table.strName + "; psql petra < " + Table.strName + ".sql");
}
// todo: sw.WriteLine("echo \"load constraints\"; psql petra < petra0203_constraints.sql");
// todo: sw.WriteLine("echo \"load indexes\"; psql petra < petra0203_indexes.sql");
sw.Close();
outPutFileStream.Close();
System.Console.WriteLine("Success: file written: {0}", LoadDataFile);
string CreateTablesFile = System.IO.Path.GetDirectoryName(AOutputFile) +
System.IO.Path.DirectorySeparatorChar +
"createtables-" + ATargetDatabase.ToString() + ".sql";
outPutFileStream = new FileStream(CreateTablesFile,
FileMode.Create, FileAccess.Write);
sw = new StreamWriter(outPutFileStream);
if (ATargetDatabase == eDatabaseType.PostgreSQL)
{
sw.WriteLine("SET client_min_messages TO WARNING;");
}
else if (ATargetDatabase == eDatabaseType.MySQL)
{
sw.WriteLine("SET AUTOCOMMIT=0;");
sw.WriteLine("SET FOREIGN_KEY_CHECKS=0;");
}
foreach (TTable Table in Tables)
{
if (!WriteTable(ATargetDatabase, sw, Table))
{
Environment.Exit(1);
}
}
WriteSequences(sw, ATargetDatabase, AStore, false);
sw.Close();
outPutFileStream.Close();
System.Console.WriteLine("Success: file written: {0}", CreateTablesFile);
string CreateConstraintsAndIndexesFile = System.IO.Path.GetDirectoryName(AOutputFile) +
System.IO.Path.DirectorySeparatorChar +
"createconstraints-" + ATargetDatabase.ToString() + ".sql";
outPutFileStream = new FileStream(CreateConstraintsAndIndexesFile,
FileMode.Create, FileAccess.Write);
sw = new StreamWriter(outPutFileStream);
foreach (TTable Table in Tables)
{
if (ATargetDatabase == eDatabaseType.Sqlite)
{
// see http://www.sqlite.org/omitted.html:
// sqlite does not support Alter table add constraint
}
else
{
DumpConstraints(sw, Table, eInclude.eOnlyForeign, true);
}
}
foreach (TTable Table in Tables)
{
// Dump indexes
DumpIndexes(ATargetDatabase, sw, Table, eInclude.eIncludeSeparate, true);
}
sw.Close();
outPutFileStream.Close();
System.Console.WriteLine("Success: file written: {0}", CreateConstraintsAndIndexesFile);
}