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 void TestSQLBuild(ISQL builder, DBDatabase db, DBTable table) { Output("TestSQLBuild:"); Output(""); try { Output("Criterias:"); ICriteria crit1 = new Crit_Match(table, "lStortTal", MatchType.Equal, 6576547634); ICriteria crit2 = new Crit_Match(table, "txTekst", MatchType.Different, "Bent"); ICriteria crit3 = new Crit_Match(table, "sLilleTal", MatchType.IsNull); Stmt_Select stmtSelect = new Stmt_Select(); stmtSelect.AddAllColumns(table); stmtSelect.AddCriteria(crit1); stmtSelect.AddCriteria(crit2); stmtSelect.AddCriteria(crit3); Output(builder.ToSQL(stmtSelect)); stmtSelect = new Stmt_Select(); stmtSelect.AddAllColumns(table); stmtSelect.AddCriteria(new Crit_Or(crit1, crit2)); stmtSelect.AddCriteria(crit3); Output(builder.ToSQL(stmtSelect)); stmtSelect = new Stmt_Select(); stmtSelect.AddAllColumns(table); ICriteria tempCrit = new Crit_And(crit2, crit3); stmtSelect.AddCriteria(new Crit_Or(crit1, tempCrit)); Output(builder.ToSQL(stmtSelect)); stmtSelect = new Stmt_Select(); stmtSelect.AddAllColumns(table); stmtSelect.AddCriteria(new Crit_Or(new Crit_Or(crit1, crit2), crit3)); Output(builder.ToSQL(stmtSelect)); stmtSelect = new Stmt_Select(); stmtSelect.AddAllColumns(table); stmtSelect.AddCriteria(crit1); stmtSelect.AddCriteria(crit2); stmtSelect.AddCriteria(new Crit_In(table, "iTal", true, 3, 5, 254, 31)); Output(builder.ToSQL(stmtSelect)); Stmt_Select stmtSelect1 = new Stmt_Select(); stmtSelect1.AddColumn(table, "iTal"); stmtSelect = new Stmt_Select(); stmtSelect.AddAllColumns(table); stmtSelect.AddCriteria(new Crit_SubQuery(table, "iTal", stmtSelect1)); Output(builder.ToSQL(stmtSelect)); stmtSelect = new Stmt_Select(); stmtSelect.AddAllColumns(table); stmtSelect.AddCriteria(new Crit_SubQuery(table, "iTal", true, stmtSelect1)); Output(builder.ToSQL(stmtSelect)); Output(""); Output("Aggregates:"); stmtSelect = new Stmt_Select(); stmtSelect.AddColumn(table, "iTal"); stmtSelect.Distinct = true; Output(builder.ToSQL(stmtSelect)); stmtSelect = new Stmt_Select(); stmtSelect.AddTable(table); stmtSelect.AddAggregate(new Aggre_Count()); Output(builder.ToSQL(stmtSelect)); stmtSelect = new Stmt_Select(); stmtSelect.AddTable(table); stmtSelect.AddAggregate(new Aggre_Count(table, "iTal")); Output(builder.ToSQL(stmtSelect)); stmtSelect = new Stmt_Select(); stmtSelect.AddTable(table); stmtSelect.AddAggregate(new Aggre_Max(table, "iTal")); Output(builder.ToSQL(stmtSelect)); stmtSelect = new Stmt_Select(); stmtSelect.AddTable(table); stmtSelect.AddAggregate(new Aggre_Min(table, "iTal")); Output(builder.ToSQL(stmtSelect)); Output(""); Output("Create tables:"); DBTable employees = db.AddTable("Employees"); employees.AddColumn("Employee_ID", ColumnType.String, 2, ColumnFlag.PrimaryKey | ColumnFlag.NotNull); employees.AddColumn("Name", ColumnType.String, 50, ColumnFlag.NotNull); DBTable orders = db.AddTable("Orders"); orders.AddColumn("Prod_ID", ColumnType.Int, ColumnFlag.PrimaryKey | ColumnFlag.NotNull); orders.AddColumn("Product", ColumnType.String, 50, ColumnFlag.NotNull | ColumnFlag.IndexUnique); orders.AddColumn("Employee_ID", ColumnType.String, 2, ColumnFlag.NotNull); DBTable storage = db.AddTable("Storage"); storage.AddColumn("Storage_ID", ColumnType.Int, ColumnFlag.PrimaryKey | ColumnFlag.NotNull); storage.AddColumn("Prod_ID", ColumnType.Int, ColumnFlag.NotNull); storage.AddColumn("Count", ColumnType.Int, ColumnFlag.NotNull | ColumnFlag.IndexDesc); Stmt_CreateTable stmtCreate = new Stmt_CreateTable(employees); Output(builder.ToSQL(stmtCreate)); stmtCreate = new Stmt_CreateTable(orders); Output(builder.ToSQL(stmtCreate)); stmtCreate = new Stmt_CreateTable(storage); Output(builder.ToSQL(stmtCreate)); Output(""); Output("Joins:"); stmtSelect = new Stmt_Select(); stmtSelect.AddColumn(employees, "Name"); stmtSelect.AddColumn(orders, "Product"); stmtSelect.AddJoin(new Join_Inner(employees, "Employee_ID", orders, "Employee_ID")); stmtSelect.AddColumn(storage, "Count"); stmtSelect.AddJoin(new Join_Inner(orders, "Prod_ID", storage, "Prod_ID")); stmtSelect.AddCriteria(new Crit_Match(storage, "Count", MatchType.Bigger, 10)); stmtSelect.AddSort(employees, "Name", Order.Ascending); stmtSelect.AddSort(orders, "Product", Order.Descending); Output(builder.ToSQL(stmtSelect)); stmtSelect = new Stmt_Select(); stmtSelect.AddColumn(employees, "Name"); stmtSelect.AddColumn(orders, "Product"); stmtSelect.AddJoin(new Join_Left(employees, "Employee_ID", orders, "Employee_ID")); Output(builder.ToSQL(stmtSelect)); stmtSelect = new Stmt_Select(); stmtSelect.AddColumn(employees, "Name"); stmtSelect.AddColumn(orders, "Product"); stmtSelect.AddJoin(new Join_Right(employees, "Employee_ID", orders, "Employee_ID")); Output(builder.ToSQL(stmtSelect)); Output(""); Output("Misc"); DBTable employees1 = db.AddTable("Employees1"); employees1.AddColumn("Employee_ID", ColumnType.String, 2, ColumnFlag.PrimaryKey | ColumnFlag.NotNull); employees1.AddColumn("Name", ColumnType.String, 50, ColumnFlag.NotNull); stmtSelect = new Stmt_Select(); stmtSelect.AddAllColumns(employees); Stmt_Insert stmtInsert = new Stmt_Insert(employees1); stmtInsert.InsertFromSelect = stmtSelect; Output(builder.ToSQL(stmtInsert)); } catch (Exception ex) { Output("TestSQLBuild 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); } }