public void _09_CreateTableWithIndex()
{
DBDatabase db = ConnectDb();
try
{
DBQuery tbl = DBQuery.Create.Table("CustomTable")
.Add("col1", DbType.Int32, DBColumnFlags.PrimaryKey | DBColumnFlags.AutoAssign)
.Add("col2", DbType.String, 50)
.Add("col3", DbType.String, 255);
//create 3 indexes on this table for the other columns
DBQuery idx1 = DBQuery.Create.Index("CT_ColBoth").Unique().On("CustomTable")
.Columns("col1", Order.Ascending, "col2", Order.Ascending);
DBQuery idx2 = DBQuery.Create.Index(true, "CT_Col2").On("CustomTable")
.Add("col2");
DBQuery idx3 = DBQuery.Create.Index("CT_Col1").On("CustomTable")
.Add("col1", Order.Ascending);
if (db.GetProperties().CheckSupports(DBSchemaTypes.CommandScripts))
{
DBScript create = DBQuery.Script(tbl, idx1, idx2, idx3);
TestContext.WriteLine(create.ToSQLString(db));
db.ExecuteNonQuery(create);
}
else //scripts are not supported so we are going to execute individually
{
db.ExecuteNonQuery(tbl);
db.ExecuteNonQuery(idx1);
db.ExecuteNonQuery(idx2);
db.ExecuteNonQuery(idx3);
}
}
finally
{
DBQuery droptbl = DBQuery.Drop.Table("CustomTable");
DBQuery dropidx1 = DBQuery.Drop.Index("CT_ColBoth").On("CustomTable");
DBQuery dropidx2 = DBQuery.Drop.Index("CT_Col2").On("CustomTable");
DBQuery dropidx3 = DBQuery.Drop.Index("CT_Col1").On("CustomTable");
if (db.GetProperties().CheckSupports(DBSchemaTypes.CommandScripts))
{
DBScript dropall = DBScript.Script(dropidx3, dropidx2, dropidx1, droptbl);
TestContext.WriteLine("\r\n" + dropall.ToSQLString(db));
db.ExecuteNonQuery(dropall);
}
else
{
db.ExecuteNonQuery(dropidx1);
db.ExecuteNonQuery(dropidx2);
db.ExecuteNonQuery(dropidx3);
db.ExecuteNonQuery(droptbl);
}
}
}
public void _10_CreateTableWithForeignKeys()
{
DBDatabase db = ConnectDb();
//Create the persons table
DBQuery createPersons = DBQuery.Create.Table("DSQL_Persons")
.Add("Person_ID", DbType.Int32, DBColumnFlags.AutoAssign | DBColumnFlags.PrimaryKey)
.Add("Person_Name", DbType.String, 50);
TestContext.WriteLine(createPersons.ToSQLString(db));
db.ExecuteNonQuery(createPersons);
//Create the orders table
DBQuery createOrders = DBQuery.Create.Table("DSQL_Orders")
.Add("Order_ID", DbType.Int32, DBColumnFlags.AutoAssign | DBColumnFlags.PrimaryKey)
.Add("Ordered_By", DbType.Int32)
.Add("Ordered_Date", DbType.DateTime)
.Add("Signed_By", DbType.Int32, DBColumnFlags.Nullable)
.Constraints(
DBConstraint.ForeignKey().Column("Ordered_By") //unnamed foreign key first
.References("DSQL_Persons").Column("Person_ID"),
DBConstraint.ForeignKey("Orders_Signed_By_2_Persons_PersonID").Column("Signed_By")
.References("DSQL_Persons").Column("Person_ID")
.OnDelete(DBFKAction.Cascade)
.OnUpdate(DBFKAction.Cascade)
);
//Execute the Create Table statements
TestContext.WriteLine(createOrders.ToSQLString(db));
db.ExecuteNonQuery(createOrders);
try
{
bool scripts = db.GetProperties().CheckSupports(DBSchemaTypes.CommandScripts);
DBParam pname = DBParam.Param("name", DbType.String);
DBScript insertperson = DBQuery.Script(
DBQuery.InsertInto("DSQL_Persons").Field("Person_Name").Value(pname),
DBQuery.Select(DBFunction.LastID())
);
//Insert one row into the persons table
pname.Value = "First Person";
TestContext.WriteLine(insertperson.ToSQLString(db));
int firstpid = Convert.ToInt32(this.ExecuteScalarScript(db, insertperson, scripts));
//And another row
pname.Value = "Second Person";
int secondpid = Convert.ToInt32(this.ExecuteScalarScript(db, insertperson, scripts));
//Create an order with orderedby = firstpid and signedby = secondpid
DBParam orderedby = DBParam.ParamWithValue(DbType.Int32, firstpid);
DBParam signedby = DBParam.ParamWithValue(DbType.Int32, secondpid);
DBScript insertorder = DBQuery.Script(
DBQuery.InsertInto("DSQL_Orders")
.Field("Ordered_By").Value(orderedby)
.Field("Ordered_Date").Value(DBFunction.GetDate())
.Field("Signed_By").Value(signedby),
DBQuery.Select(DBFunction.LastID())
);
TestContext.WriteLine(insertorder.ToSQLString(db));
int orderid = Convert.ToInt32(this.ExecuteScalarScript(db, insertorder, scripts));
//Now try to create an order that breaks referential integrity
orderedby.Value = -100;
try
{
orderid = Convert.ToInt32(db.ExecuteScalar(insertorder));
throw new InvalidOperationException("We should not be able to insert these rows. FAILED test");
}
catch (InvalidOperationException)
{
throw;
}
catch (Exception ex)
{
TestContext.WriteLine("Sucessfully caught an exception that breaks referential integrity");
}
//Finally check the cascading deletes
//check the Orders table count
//delete a person row
//ensure that the corresponding row for Signed By FK was deleted
DBQuery getcount = DBQuery.SelectCount().From("DSQL_ORDERS");
int ordercount = Convert.ToInt32(
db.ExecuteScalar(getcount));
DBQuery del = DBQuery.DeleteFrom("DSQL_Persons")
.WhereField("Person_ID", Compare.Equals, DBParam.ParamWithValue(secondpid));
int removed = db.ExecuteNonQuery(del);
Assert.AreEqual(1, removed);
TestContext.WriteLine("Removed a single row from the persons table");
int newordercount = Convert.ToInt32(db.ExecuteScalar(getcount));
//Make sure the orders row has been deleted
Assert.AreEqual(newordercount, ordercount - 1);
TestContext.WriteLine("Validated that the corresponding row in the orders table has been removed too");
}
finally
{
//Clean up tables in order
db.ExecuteNonQuery(DBQuery.Drop.Table("DSQL_Orders"));
db.ExecuteNonQuery(DBQuery.Drop.Table("DSQL_Persons"));
TestContext.WriteLine("Database has been cleaned up");
}
}