/// <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); }