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