private void ShowContents(Stmt_Select statement, DBRunner runner, ISQLExecuter executer, DBConnection conn)
{
DBRowCollection coll = runner.Select(executer, conn, statement);
String line = "";
foreach (DBMetaColumn meta in coll.Meta)
{
line += meta.ColumnName + ", ";
}
Output(line.Substring(0, line.Length - 2));
foreach (DBRow r in coll)
{
line = "";
int count = r.Count;
for (int i = 0; i < count; i++)
{
line += r[i] == null ? "null, " : (r[i] + ", ");
}
Output(line.Substring(0, line.Length - 2));
}
}
public void Finished(DBRunner runner)
{
lock (runnersLock)
{
runners.Remove(runner.DatabaseName);
}
}
private void TestSmallSelect(DBRunner runner, ISQLExecuter executer, ISQL builder, DBDatabase db, DBTable table, IConnectionInfo connInfo)
{
Output("TestSmallSelect:");
Output("");
try
{
DBConnection conn = runner.OpenConnection(executer, db, connInfo);
try
{
Output("Get count");
Stmt_Select stmtSelect = new Stmt_Select();
stmtSelect.AddTable(table);
stmtSelect.AddAggregate(new Aggre_Count());
Output(builder.ToSQL(stmtSelect));
long result = runner.SelectWithSingleAggregate(executer, conn, stmtSelect);
Output("Count: " + result + " / 1");
}
finally
{
conn.Close();
}
}
catch (Exception ex)
{
Output("TestSmallSelect failed with an exception:");
Output(ex);
}
finally
{
Output("");
Output("");
}
}
public void Delete(string dbname, string callbackId)
{
var query = new DBQuery(false, callbackId);
lock (runnersLock)
{
DBRunner runner;
if (runners.TryGetValue(dbname, out runner))
{
runner.Enqueue(query);
// As we cannot determine the order in which query/open requests arrive,
// any such requests should start a new thread rather than being lost on the dying queue.
// Hence synchronoous wait for thread-end within request and within lock.
runner.WaitForStopped();
runners.Remove(dbname);
}
else
{
// Deleting a closed database, so no runner to queue the request on
// Must be inside lock so databases are not opened while being deleted
DBRunner.DeleteDatabaseNow(this, dbname, callbackId);
}
}
}
private void TestConnection(DBRunner runner, ISQLExecuter executer, DBDatabase db, IConnectionInfo connInfo)
{
Output("TestConnection:");
Output("");
try
{
Output("Open connection");
DBConnection conn = runner.OpenConnection(executer, db, connInfo);
Output("Got connection");
Output("Close connection");
conn.Close();
Output("Connection closed");
}
catch (Exception ex)
{
Output("TestConnection failed with an exception:");
Output(ex);
}
finally
{
Output("");
Output("");
}
}
private void TestSmallInsert(DBRunner runner, ISQLExecuter executer, ISQL builder, DBDatabase db, DBTable table, IConnectionInfo connInfo)
{
Output("TestSmallInsert:");
Output("");
try
{
DBConnection conn = runner.OpenConnection(executer, db, connInfo);
try
{
Output("Insert single row");
Stmt_Insert stmtInsert = new Stmt_Insert(table);
stmtInsert.AddColumns("uiNoegle", "iTal", "lStortTal", "dtDato", "bValg");
stmtInsert.AddValues(Guid.NewGuid(), 87, (long)2394287487, DateTime.Now, false);
Output(builder.ToSQL(stmtInsert));
runner.Insert(executer, conn, stmtInsert);
Output("Row inserted");
}
finally
{
conn.Close();
}
}
catch (Exception ex)
{
Output("TestSmallInsert failed with an exception:");
Output(ex);
}
finally
{
Output("");
Output("");
}
}
private void TestDatabase(DBRunner runner, ISQLExecuter executer, DBDatabase db)
{
Output("TestDatabase:");
Output("");
try
{
bool result = runner.DatabaseExists(executer, db);
Output("Database exists: " + result + " / False");
result = runner.CreateDatabase(executer, db);
Output("Create database: " + (result ? "Ok" : "Failed"));
if (result)
{
result = runner.DatabaseExists(executer, db);
Output("Database exists: " + result + " / True");
}
}
catch (Exception ex)
{
Output("TestDatabase failed with an exception:");
Output(ex);
}
finally
{
Output("");
Output("");
}
}
public static void Main()
{
try
{
DBRunner runner = new DBRunner();
ISQLExecuter executer = new DB2_SQLExecuter(new DB2_SQL());
DBDatabase database = new DBDatabase("EISST");
// Open connection to the database
Output("Open connection to database");
IConnectionInfo connInfo = new DB2_ConnectionInfo("vagn", "EISST", "EISST", "EISSTEISST");
DBConnection connection = runner.OpenConnection(executer, database, connInfo);
try
{
// Extract meta data for all the tables we need
Output("Extract table metadata");
DBTable pluginsTable = runner.GetTableMetaData(executer, connection, database, "EISST", "Plugins");
database.AddTable(pluginsTable);
DBTable gruppePostkasseTable = runner.GetTableMetaData(executer, connection, database, "EISST", "GruppePostkasse");
database.AddTable(gruppePostkasseTable);
DBTable revisionssporTable = runner.GetTableMetaData(executer, connection, database, "EISST", "Revisionsspor");
database.AddTable(revisionssporTable);
// FindDocuments(pluginsTable, gruppePostkasseTable, revisionssporTable, runner, executer, connection, "dk.hob.ei.abort.Plugin", "Abort_Grundoplysninger");
// FindDocuments(pluginsTable, gruppePostkasseTable, revisionssporTable, runner, executer, connection, "dk.hob.ei.bdb.Plugin", "BDB_Grundoplysninger");
// FindDocuments(pluginsTable, gruppePostkasseTable, revisionssporTable, runner, executer, connection, "dk.hob.ei.cancer.Plugin", "Cancer");
// FindDocuments(pluginsTable, gruppePostkasseTable, revisionssporTable, runner, executer, connection, "dk.hob.ei.commoncancer.Plugin", "CommonCancer");
// FindDocuments(pluginsTable, gruppePostkasseTable, revisionssporTable, runner, executer, connection, "dk.hob.ei.genoptraening.Plugin", "Genoptraening_Grundoplysninger");
// FindDocuments(pluginsTable, gruppePostkasseTable, revisionssporTable, runner, executer, connection, "dk.hob.ei.hbs.Plugin", "HBS_Grundoplysninger");
// FindDocuments(pluginsTable, gruppePostkasseTable, revisionssporTable, runner, executer, connection, "dk.hob.ei.injicerbarheroin.Plugin", "InjicerbarHeroin_Grundoplysninger");
// FindDocuments(pluginsTable, gruppePostkasseTable, revisionssporTable, runner, executer, connection, "dk.hob.ei.ivf.Plugin", "IVF_Grundoplysninger");
// FindDocuments(pluginsTable, gruppePostkasseTable, revisionssporTable, runner, executer, connection, "dk.hob.ei.minipas.Plugin", "MiniPas_Skema1_Grundoplysninger");
// FindDocuments(pluginsTable, gruppePostkasseTable, revisionssporTable, runner, executer, connection, "dk.hob.ei.nab.Plugin", "NAB_Grundoplysninger");
// FindDocuments(pluginsTable, gruppePostkasseTable, revisionssporTable, runner, executer, connection, "dk.hob.ei.mortality.Plugin", "Mortality_Side1", "Mortality_Side2");
// FindDocuments(pluginsTable, gruppePostkasseTable, revisionssporTable, runner, executer, connection, "dk.hob.ei.patologi.Plugin", "Patologi_Skema1");
// FindDocuments(pluginsTable, gruppePostkasseTable, revisionssporTable, runner, executer, connection, "dk.hob.ei.psykiatrilovensp3.Plugin", "Psykiatrilovensp3_Grundoplysninger");
// FindDocuments(pluginsTable, gruppePostkasseTable, revisionssporTable, runner, executer, connection, "dk.hob.ei.sengepladser.Plugin", "Sengepladser_Grundoplysninger");
// FindDocuments(pluginsTable, gruppePostkasseTable, revisionssporTable, runner, executer, connection, "dk.hob.ei.sib.Plugin", "SIB_Grundoplysninger");
// FindDocuments(pluginsTable, gruppePostkasseTable, revisionssporTable, runner, executer, connection, "dk.hob.ei.tvang1.Plugin", "Tvang_Skema1_Head");
// FindDocuments(pluginsTable, gruppePostkasseTable, revisionssporTable, runner, executer, connection, "dk.hob.ei.tvang2.Plugin", "Tvang_Skema2_Head");
FindDocuments(pluginsTable, gruppePostkasseTable, revisionssporTable, runner, executer, connection, "dk.hob.ei.tvang3.Plugin", "Tvang_Skema3_Head");
// FindDocuments(pluginsTable, gruppePostkasseTable, revisionssporTable, runner, executer, connection, "dk.hob.ei.tvang4.Plugin", "Tvang_Skema4_Head");
// FindDocuments(pluginsTable, gruppePostkasseTable, revisionssporTable, runner, executer, connection, "dk.hob.ei.tvang5.Plugin", "Tvang_Skema5_Head");
}
finally
{
Output("Close connection");
connection.Close();
}
}
catch (Exception ex)
{
Output("Exception failure: " + ex);
}
}
private void TestTable(DBRunner runner, ISQLExecuter executer, ISQL builder, DBDatabase db, DBTable table, IConnectionInfo connInfo)
{
Output("TestTable:");
Output("");
try
{
DBConnection conn = runner.OpenConnection(executer, db, connInfo);
try
{
String name = table.TableName;
bool result = runner.TableExists(executer, conn, name);
Output("Table " + name + " exists: " + result + " / False");
Output("");
Output("Create table " + name);
Stmt_CreateTable stmtCreate = new Stmt_CreateTable(table);
Output(builder.ToSQL(stmtCreate));
runner.CreateTable(executer, conn, stmtCreate);
Output("Table created");
Output("");
result = runner.TableExists(executer, conn, name);
Output("Table " + name + " exists: " + result + " / True");
Output("");
if (result)
{
Output("Drop table " + name);
Stmt_DropTable stmtDrop = new Stmt_DropTable(table);
Output(builder.ToSQL(stmtDrop));
runner.DropTable(executer, conn, stmtDrop);
Output("Table dropped");
Output("");
result = runner.TableExists(executer, conn, name);
Output("Table " + name + " exists: " + result + " / False");
}
}
finally
{
conn.Close();
}
}
catch (Exception ex)
{
Output("TestTable failed with an exception:");
Output(ex);
}
finally
{
Output("");
Output("");
}
}
private static bool HasID(DBTable table, Guid documentID, DBRunner runner, ISQLExecuter executer, DBConnection connection)
{
SQL_SelectStatement sqlSelect = new SQL_SelectStatement();
sqlSelect.AddTable(table);
sqlSelect.AddAggregate(new Aggre_Count());
sqlSelect.AddCriteria(new Crit_MatchCriteria(table, "uiSkemaID", MatchType.Equal, documentID));
return(runner.SelectWithSingleAggregate(executer, connection, sqlSelect) > 0);
}
private void ShowContents(DBRunner runner, ISQLExecuter executer, DBConnection conn, DBTable table)
{
Output("Display contents of table " + table.TableName);
Stmt_Select stmtSelect = new Stmt_Select();
stmtSelect.AddAllColumns(table);
ShowContents(stmtSelect, runner, executer, conn);
}
private void TestUnion(DBRunner runner, ISQLExecuter executer, ISQL builder, DBDatabase db, DBTable table1, DBTable table2, IConnectionInfo connInfo)
{
Output("TestUnion:");
Output("");
try
{
DBConnection conn = runner.OpenConnection(executer, db, connInfo);
try
{
Output("Insert more test rows");
Stmt_Insert stmtInsert = new Stmt_Insert(table1);
stmtInsert.AddColumns("uiNoegle", "txTekst", "iTal", "lStortTal", "dtDato", "bValg");
stmtInsert.AddValues(Guid.NewGuid(), "Giv mig en bog!", 123, (long)213142566, DateTime.Now, true);
Output(builder.ToSQL(stmtInsert));
runner.Insert(executer, conn, stmtInsert);
Output("Rows inserted");
Output("");
ShowContents(runner, executer, conn, table1);
Output("");
ShowContents(runner, executer, conn, table2);
Output("");
Stmt_Select stmtSelect1 = new Stmt_Select();
stmtSelect1.AddColumns(table1, "uiNoegle", "txTekst", "iTal", "lStortTal");
Stmt_Select stmtSelect2 = new Stmt_Select();
stmtSelect2.AddColumns(table2, "uiNoegle", "txTekst", "iTal", "lStortTal");
Stmt_Select stmtUnion = new Stmt_Select();
stmtUnion.AddColumns(table1, "uiNoegle", "txTekst", "iTal", "lStortTal");
stmtUnion.AddUnion(stmtSelect1);
stmtUnion.AddUnion(stmtSelect2);
stmtUnion.AddSort(table1, "iTal", Order.Ascending);
Output(builder.ToSQL(stmtUnion));
ShowContents(stmtUnion, runner, executer, conn);
}
finally
{
conn.Close();
}
}
catch (Exception ex)
{
Output("TestUpdate failed with an exception:");
Output(ex);
}
finally
{
Output("");
Output("");
}
}
private void TestTable2(DBRunner runner, ISQLExecuter executer, DBDatabase db, String tableName, IConnectionInfo connInfo)
{
Output("TestTable 2:");
Output("");
try
{
Output("Getting metadata for table " + tableName);
using (DBConnection conn = runner.OpenConnection(executer, db, connInfo))
{
DBTable table = runner.GetTableMetaData(executer, conn, db, tableName);
if (table == null)
{
Output("No metadata found");
}
else
{
Output("Found metadata for table: " + table.TableName + " - Columns: " + table.NumberOfColumns);
}
}
Output("");
Output("Getting metadata");
DBDatabase newDB = new DBDatabase(db.DatabaseName);
runner.GetMetaData(executer, newDB, connInfo);
if (newDB.NumberOfTables == 0)
{
Output("No tables found");
}
else
{
foreach (DBTable table in newDB.Tables)
{
Output("Table: " + table.TableName + " - Columns: " + table.NumberOfColumns);
}
}
}
catch (Exception ex)
{
Output("TestTable 2 failed with an exception:");
Output(ex);
}
finally
{
Output("");
Output("");
}
}
private static Guid FindGroupID(DBTable gruppeTable, DBRunner runner, ISQLExecuter executer, DBConnection connection)
{
SQL_SelectStatement sqlSelect = new SQL_SelectStatement();
sqlSelect.AddColumn(gruppeTable, "uiGruppeID");
sqlSelect.AddCriteria(new Crit_MatchCriteria(gruppeTable, "txNavn", MatchType.Equal, "Dødsattester midlertidig opbevaring"));
DBRow row = runner.SelectAndReturnFirstRow(executer, connection, sqlSelect);
if (row == null)
{
return(Guid.Empty);
}
return((Guid)row["uiGruppeID"]);
}
private static Guid FindPluginID(DBTable pluginsTable, DBRunner runner, ISQLExecuter executer, DBConnection connection)
{
SQL_SelectStatement sqlSelect = new SQL_SelectStatement();
sqlSelect.AddColumn(pluginsTable, "uiPluginID");
sqlSelect.AddCriteria(new Crit_MatchCriteria(pluginsTable, "txNavn", MatchType.Equal, "dk.hob.ei.mortality.Plugin"));
DBRow row = runner.SelectAndReturnFirstRow(executer, connection, sqlSelect);
if (row == null)
{
return(Guid.Empty);
}
return((Guid)row["uiPluginID"]);
}
private static void AddIDs(SQL_SelectStatement sqlSelect, DBRunner runner, ISQLExecuter executer, DBConnection connection, Dictionary <Guid, Object> ids)
{
using (DBReader reader = runner.GetReader(executer, connection, sqlSelect))
{
DBRow row;
while ((row = reader.GetNextRow()) != null)
{
if (row["uiSkemaID"] != null)
{
Guid id = (Guid)row["uiSkemaID"];
ids[id] = null;
}
}
}
}
public void Query(string dbname, TransactionsCollection queries, string callbackId)
{
lock (runnersLock)
{
DBRunner runner;
if (!runners.TryGetValue(dbname, out runner))
{
// query may start before open is scheduled
runner = new DBRunner(this, dbname, null);
runner.Start();
runners[dbname] = runner;
}
var query = new DBQuery(queries, callbackId);
runner.Enqueue(query);
}
}
public void Open(string dbname, string cbc)
{
DBRunner runner;
lock (runnersLock)
{
if (!runners.TryGetValue(dbname, out runner))
{
runner = new DBRunner(this, dbname, cbc);
runner.Start();
runners[dbname] = runner;
}
else
{
// acknowledge open if it is scheduled after first query.
// Also works for re-opening a database, but means that one cannot close a single instance of a database.
this.Ok(cbc);
}
}
}
private void TestTransactions(DBRunner runner, ISQLExecuter executer, DBDatabase db, DBTable table, IConnectionInfo connInfo)
{
Output("TestTransactions:");
Output("");
try
{
DBConnection conn = runner.OpenConnection(executer, db, connInfo);
try
{
Stmt_Insert stmtInsert;
Stmt_Select stmtSelect;
long result;
Output("Begin transaction");
DBTransaction trans = runner.CreateTransaction(executer);
trans.Begin(conn);
try
{
Output("Insert row");
stmtInsert = new Stmt_Insert(table);
stmtInsert.AddColumns("uiNoegle", "iTal", "lStortTal", "dtDato", "bValg");
stmtInsert.AddValues(Guid.NewGuid(), 87, (long)2394287487, DateTime.Now, false);
runner.Insert(executer, conn, stmtInsert);
stmtSelect = new Stmt_Select();
stmtSelect.AddTable(table);
stmtSelect.AddAggregate(new Aggre_Count());
result = runner.SelectWithSingleAggregate(executer, conn, stmtSelect);
Output("Count: " + result + " / 1");
Output("Rollback");
trans.RollbackAll();
}
catch (Exception)
{
trans.RollbackAll();
throw;
}
stmtSelect = new Stmt_Select();
stmtSelect.AddTable(table);
stmtSelect.AddAggregate(new Aggre_Count());
result = runner.SelectWithSingleAggregate(executer, conn, stmtSelect);
Output("Count: " + result + " / 0");
Output("");
Output("Begin new transaction");
trans = runner.CreateTransaction(executer);
trans.Begin(conn);
try
{
Output("Insert row");
stmtInsert = new Stmt_Insert(table);
stmtInsert.AddColumns("uiNoegle", "iTal", "lStortTal", "dtDato", "bValg");
stmtInsert.AddValues(Guid.NewGuid(), 87, (long)2394287487, DateTime.Now, false);
runner.Insert(executer, conn, stmtInsert);
stmtSelect = new Stmt_Select();
stmtSelect.AddTable(table);
stmtSelect.AddAggregate(new Aggre_Count());
result = runner.SelectWithSingleAggregate(executer, conn, stmtSelect);
Output("Count: " + result + " / 1");
Output("Commit");
trans.CommitAll();
}
catch (Exception)
{
trans.RollbackAll();
throw;
}
stmtSelect = new Stmt_Select();
stmtSelect.AddTable(table);
stmtSelect.AddAggregate(new Aggre_Count());
result = runner.SelectWithSingleAggregate(executer, conn, stmtSelect);
Output("Count: " + result + " / 1");
}
finally
{
conn.Close();
}
}
catch (Exception ex)
{
Output("TestTransactions failed with an exception:");
Output(ex);
}
finally
{
Output("");
Output("");
}
}
private void TestThread(ManualResetEvent exitEvent, Object userData)
{
try
{
ISQL builder;
ISQLExecuter executer;
IConnectionInfo connInfo;
switch ((int)userData)
{
// VistaDB
case 0:
{
String path = Path.GetDirectoryName(Assembly.GetEntryAssembly().Location);
path = Path.Combine(path, "VistaDB");
if (Directory.Exists(path))
{
Directory.Delete(path, true);
}
builder = new VistaDB_SQL();
executer = new VistaDB_SQLExecuter((VistaDB_SQL)builder, path);
connInfo = new VistaDB_ConnectionInfo(true);
break;
}
// DB2
case 1:
{
builder = new DB2_SQL();
executer = new DB2_SQLExecuter((DB2_SQL)builder);
connInfo = new DB2_ConnectionInfo("sei-backend", "testdb", "eisst", "EISSTEISST");
break;
}
default:
return;
}
// Create and start the runner
DBRunner runner = new DBRunner();
try
{
// Show provider
IProvider prov = executer.Provider;
Output("Provider: " + prov.Name + " - Version: " + prov.Version);
Output("");
// Create the database and test tables
DBDatabase db = new DBDatabase("DBTest.MyTest");
DBTable table1 = db.AddTable("Table1");
table1.AddColumn("uiNoegle", ColumnType.Guid, ColumnFlag.PrimaryKey | ColumnFlag.NotNull);
table1.AddColumn("txTekst", ColumnType.String, 20, ColumnFlag.None);
table1.AddColumn("iTal", ColumnType.Int, ColumnFlag.PrimaryKey | ColumnFlag.NotNull);
table1.AddColumn("dtDato", ColumnType.DateTime, ColumnFlag.IndexAsc);
table1.AddColumn("sLilleTal", ColumnType.Small, ColumnFlag.None);
table1.AddColumn("lStortTal", ColumnType.Long, ColumnFlag.NotNull);
table1.AddColumn("txStorTekst", ColumnType.Clob, 32 * 1024, ColumnFlag.None);
table1.AddColumn("bValg", ColumnType.Boolean, ColumnFlag.NotNull);
table1.AddColumn("biBillede", ColumnType.Blob, 10 * 1024 * 1024, ColumnFlag.Compressed);
table1.AddColumn("iAutoTaeller", ColumnType.Int, ColumnFlag.NotNull | ColumnFlag.Identity);
DBTable table2 = db.AddTable("Table2");
table2.AddColumn("uiNoegle", ColumnType.Guid, ColumnFlag.PrimaryKey | ColumnFlag.NotNull);
table2.AddColumn("txTekst", ColumnType.String, 20, ColumnFlag.None);
table2.AddColumn("iTal", ColumnType.Int, ColumnFlag.PrimaryKey | ColumnFlag.NotNull);
table2.AddColumn("dtDato", ColumnType.DateTime, ColumnFlag.IndexAsc);
table2.AddColumn("sLilleTal", ColumnType.Small, ColumnFlag.None);
table2.AddColumn("lStortTal", ColumnType.Long, ColumnFlag.NotNull);
table2.AddColumn("txStorTekst", ColumnType.Clob, 32 * 1024, ColumnFlag.None);
table2.AddColumn("bValg", ColumnType.Boolean, ColumnFlag.NotNull);
table2.AddColumn("biBillede", ColumnType.Blob, 10 * 1024 * 1024, ColumnFlag.Compressed);
TestDatabase(runner, executer, db);
TestConnection(runner, executer, db, connInfo);
TestTable(runner, executer, builder, db, table2, connInfo);
{
Output("Create table again for other tests");
DBConnection conn = runner.OpenConnection(executer, db, connInfo);
try
{
Stmt_CreateTable stmtCreate = new Stmt_CreateTable(table1);
runner.CreateTable(executer, conn, stmtCreate);
stmtCreate = new Stmt_CreateTable(table2);
runner.CreateTable(executer, conn, stmtCreate);
}
finally
{
conn.Close();
Output("");
}
}
TestTable2(runner, executer, db, "Table1", connInfo);
TestSmallInsert(runner, executer, builder, db, table1, connInfo);
TestSmallSelect(runner, executer, builder, db, table1, connInfo);
TestSmallDelete(runner, executer, builder, db, table1, connInfo);
TestTransactions(runner, executer, db, table2, connInfo);
TestUpdate(runner, executer, builder, db, table2, connInfo);
TestFunctions(runner, executer, builder, db, table2, connInfo);
TestUnion(runner, executer, builder, db, table1, table2, connInfo);
TestSQLBuild(builder, db, table1);
{
Output("Dropping testing tables");
DBConnection conn = runner.OpenConnection(executer, db, connInfo);
try
{
Stmt_DropTable stmtDrop = new Stmt_DropTable(table1);
runner.DropTable(executer, conn, stmtDrop);
stmtDrop = new Stmt_DropTable(table2);
runner.DropTable(executer, conn, stmtDrop);
}
finally
{
conn.Close();
Output("Done");
}
}
}
finally
{
runner.Close();
}
}
catch (Exception ex)
{
Output("Whole test failed with an exception:");
Output(ex);
}
}
public static void Main(String[] args)
{
try
{
DBRunner runner = new DBRunner();
ISQLExecuter executer = new DB2_SQLExecuter(new DB2_SQL());
DBDatabase database = new DBDatabase("EISST");
// Open connection to the database
Output("Open connection to database");
IConnectionInfo connInfo = new DB2_ConnectionInfo("vagn", "EISST", "EISST", "EISSTEISST");
DBConnection connection = runner.OpenConnection(executer, database, connInfo);
try
{
// Extract meta data for all the tables we need
Output("Extract table metadata");
DBTable pluginsTable = runner.GetTableMetaData(executer, connection, database, "EISST", "Plugins");
database.AddTable(pluginsTable);
DBTable grupperTable = runner.GetTableMetaData(executer, connection, database, "EISST", "Grupper");
database.AddTable(grupperTable);
DBTable gruppePostkasseTable = runner.GetTableMetaData(executer, connection, database, "EISST", "GruppePostkasse");
database.AddTable(gruppePostkasseTable);
DBTable side1Table = runner.GetTableMetaData(executer, connection, database, "EISST", "Mortality_Side1");
database.AddTable(side1Table);
DBTable side2Table = runner.GetTableMetaData(executer, connection, database, "EISST", "Mortality_Side2");
database.AddTable(side2Table);
Output("Find needed ids");
// Find the plug-in id for mortality documents
Guid pluginID = FindPluginID(pluginsTable, runner, executer, connection);
if (pluginID.Equals(Guid.Empty))
{
Output("Couldn't find plug-in ID");
return;
}
Output("Plug-in ID: " + pluginID.ToString("B").ToUpper());
// Find the group id where the documents are stored
Guid groupID = FindGroupID(grupperTable, runner, executer, connection);
if (groupID.Equals(Guid.Empty))
{
Output("Couldn't find group ID");
return;
}
Output("Group ID: " + groupID.ToString("B").ToUpper());
Output("Delete documents that are stored in the database");
FixMailBox(pluginID, groupID, gruppePostkasseTable, side1Table, side2Table, runner, executer, connection);
}
finally
{
Output("Close connection");
connection.Close();
}
}
catch (Exception ex)
{
Output("Exception failure: " + ex);
}
}
private static void FixMailBox(Guid pluginID, Guid groupID, DBTable gruppePostkasseTable, DBTable side1Table, DBTable side2Table, DBRunner runner, ISQLExecuter executer, DBConnection connection)
{
SQL_SelectStatement sqlSelect = new SQL_SelectStatement();
sqlSelect.AddColumns(gruppePostkasseTable, "uiBrevID", "uiSkemaID", "txCPRNr", "txBesked");
sqlSelect.AddCriteria(new Crit_MatchCriteria(gruppePostkasseTable, "uiGruppeID", MatchType.Equal, groupID));
sqlSelect.AddCriteria(new Crit_MatchCriteria(gruppePostkasseTable, "uiPluginID", MatchType.Equal, pluginID));
using (DBReader reader = runner.GetReader(executer, connection, sqlSelect))
{
DBRow row;
int count = 0;
try
{
while ((row = reader.GetNextRow()) != null)
{
Guid documentID = (Guid)row["uiSkemaID"];
bool deleteIt = false;
int option = 0;
if (HasID(side1Table, documentID, runner, executer, connection))
{
if (HasID(side2Table, documentID, runner, executer, connection))
{
option = 1;
deleteIt = true;
}
else if (OnlyOnePage((String)row["txBesked"]))
{
option = 2;
deleteIt = true;
}
if (deleteIt)
{
// Delete the document
Output(option + "," + row["txCPRNr"] + "," + documentID.ToString("B").ToUpper());
SQL_DeleteStatement sqlDelete = new SQL_DeleteStatement(gruppePostkasseTable);
sqlDelete.AddCriteria(new Crit_MatchCriteria(gruppePostkasseTable, "uiBrevID", MatchType.Equal, row["uiBrevID"]));
runner.Delete(executer, connection, sqlDelete);
count++;
}
}
}
}
finally
{
Output("Deleted " + count + " documents");
}
}
}
private void TestFunctions(DBRunner runner, ISQLExecuter executer, ISQL builder, DBDatabase db, DBTable table, IConnectionInfo connInfo)
{
Output("TestFunctions:");
Output("");
try
{
DBConnection conn = runner.OpenConnection(executer, db, connInfo);
try
{
Output("Insert single row");
Stmt_Insert stmtInsert = new Stmt_Insert(table);
stmtInsert.AddColumns("uiNoegle", "iTal", "lStortTal", "dtDato", "bValg");
stmtInsert.AddValues(Guid.NewGuid(), 87, (long)2394287487, DateTime.Now, false);
Output(builder.ToSQL(stmtInsert));
runner.Insert(executer, conn, stmtInsert);
stmtInsert = new Stmt_Insert(table);
stmtInsert.AddColumns("uiNoegle", "txTekst", "iTal", "lStortTal", "dtDato", "bValg");
stmtInsert.AddValues(Guid.NewGuid(), "Blåbærgrød", 87, (long)2394287487, DateTime.Now, false);
Output(builder.ToSQL(stmtInsert));
runner.Insert(executer, conn, stmtInsert);
Output("Rows inserted");
Output("");
ShowContents(runner, executer, conn, table);
Output("");
Stmt_Select stmtSelect = new Stmt_Select();
stmtSelect.AddTable(table);
IFunction func = new Func_SubString(table, "txTekst", 3, 8);
stmtSelect.AddFunction(new Func_SubString(func, 0, 2));
Output(builder.ToSQL(stmtSelect));
ShowContents(stmtSelect, runner, executer, conn);
Output("");
stmtSelect = new Stmt_Select();
stmtSelect.AddTable(table);
stmtSelect.AddFunction(new Func_ToLower(table, "txTekst"));
Output(builder.ToSQL(stmtSelect));
ShowContents(stmtSelect, runner, executer, conn);
Output("");
stmtSelect = new Stmt_Select();
stmtSelect.AddTable(table);
stmtSelect.AddFunction(new Func_ToUpper(table, "txTekst"));
Output(builder.ToSQL(stmtSelect));
ShowContents(stmtSelect, runner, executer, conn);
}
finally
{
conn.Close();
}
}
catch (Exception ex)
{
Output("TestUpdate failed with an exception:");
Output(ex);
}
finally
{
Output("");
Output("");
}
}
private void TestUpdate(DBRunner runner, ISQLExecuter executer, ISQL builder, DBDatabase db, DBTable table, IConnectionInfo connInfo)
{
Output("TestUpdate:");
Output("");
try
{
DBConnection conn = runner.OpenConnection(executer, db, connInfo);
try
{
Output("Insert more rows");
Stmt_Insert stmtInsert = new Stmt_Insert(table);
stmtInsert.AddAllColumns();
stmtInsert.AddValues(Guid.NewGuid(), "Dette er en tekst", 42, DateTime.Now, null, 6576547634);
stmtInsert.AddParameter("MEGET STOR TEKST");
stmtInsert.AddValue(true);
stmtInsert.AddParameter(new byte[432]);
Output(builder.ToSQL(stmtInsert));
runner.Insert(executer, conn, stmtInsert);
Output("Rows inserted");
Output("");
ShowContents(runner, executer, conn, table);
Output("");
Output("Update 1");
Stmt_Update stmtUpdate = new Stmt_Update(table);
stmtUpdate.AddColumns("txTekst", "iTal");
stmtUpdate.AddValues("En ny tekst", 534);
stmtUpdate.AddCriteria(new Crit_Match(table, "iTal", MatchType.Equal, 42));
Output(builder.ToSQL(stmtUpdate));
runner.Update(executer, conn, stmtUpdate);
Output("");
ShowContents(runner, executer, conn, table);
Output("");
Output("Update 2");
stmtUpdate = new Stmt_Update(table);
stmtUpdate.AddColumn("txStorTekst");
stmtUpdate.AddParameter("DETTE STÅR MED STORT!");
stmtUpdate.AddCriteria(new Crit_Match(table, "txStorTekst", MatchType.IsNull));
Output(builder.ToSQL(stmtUpdate));
runner.Update(executer, conn, stmtUpdate);
Output("");
ShowContents(runner, executer, conn, table);
Output("");
Stmt_Select stmtSelect = new Stmt_Select();
stmtSelect.AddTable(table);
stmtSelect.AddFunction(new Func_SubString(table, "txTekst", 3, 8));
Output(builder.ToSQL(stmtSelect));
ShowContents(stmtSelect, runner, executer, conn);
Output("");
Output("Delete");
Stmt_Delete stmtDelete = new Stmt_Delete(table);
stmtDelete.AddCriteria(new Crit_Match(table, "bValg", MatchType.Equal, false));
Output(builder.ToSQL(stmtDelete));
runner.Delete(executer, conn, stmtDelete);
Output("");
ShowContents(runner, executer, conn, table);
}
finally
{
conn.Close();
}
}
catch (Exception ex)
{
Output("TestUpdate failed with an exception:");
Output(ex);
}
finally
{
Output("");
Output("");
}
}
private static void FindDocuments(DBTable pluginsTable, DBTable gruppePostkasseTable, DBTable revisionssporTable, DBRunner runner, ISQLExecuter executer, DBConnection connection, String plugin, params String[] tables)
{
Output("");
Output("Find plugin ID for " + plugin);
SQL_SelectStatement sqlSelect = new SQL_SelectStatement();
sqlSelect.AddColumn(pluginsTable, "uiPluginID");
sqlSelect.AddCriteria(new Crit_MatchCriteria(pluginsTable, "txNavn", MatchType.Equal, plugin));
DBRow row = runner.SelectAndReturnFirstRow(executer, connection, sqlSelect);
if (row == null)
{
throw new Exception("Couldn't find plugin ID");
}
Guid pluginID = (Guid)row["uiPluginID"];
// Find all the document IDs
Dictionary <Guid, Object> documentIDs = new Dictionary <Guid, Object>();
Output("Find document IDs for " + gruppePostkasseTable.TableName);
sqlSelect = new SQL_SelectStatement();
sqlSelect.Distinct = true;
sqlSelect.AddColumn(gruppePostkasseTable, "uiSkemaID");
sqlSelect.AddCriteria(new Crit_MatchCriteria(gruppePostkasseTable, "uiPluginID", MatchType.Equal, pluginID));
AddIDs(sqlSelect, runner, executer, connection, documentIDs);
foreach (String tableName in tables)
{
Output("Find document IDs for " + tableName);
DBTable table = pluginsTable.Database.AddTable("EISST", tableName);
table.AddColumn("uiSkemaID", ColumnType.Guid, ColumnFlag.None);
sqlSelect = new SQL_SelectStatement();
sqlSelect.AddColumn(table, "uiSkemaID");
AddIDs(sqlSelect, runner, executer, connection, documentIDs);
}
Output("Check the revisionsspor to see which documents are missing");
sqlSelect = new SQL_SelectStatement();
sqlSelect.Distinct = true;
sqlSelect.AddColumn(revisionssporTable, "uiSkemaID");
sqlSelect.AddCriteria(new Crit_MatchCriteria(revisionssporTable, "uiPluginID", MatchType.Equal, pluginID));
sqlSelect.AddCriteria(new Crit_MatchCriteria(revisionssporTable, "iType", MatchType.Equal, (short)0));
sqlSelect.AddCriteria(new Crit_MatchCriteria(revisionssporTable, "uiSkemaID", MatchType.IsNotNull));
sqlSelect.AddSort(revisionssporTable, "uiSkemaID", Order.Ascending);
using (DBReader reader = runner.GetReader(executer, connection, sqlSelect))
{
while ((row = reader.GetNextRow()) != null)
{
Guid id = (Guid)row["uiSkemaID"];
if (!documentIDs.ContainsKey(id))
{
Output(id.ToString("B").ToUpper());
}
}
}
}
