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