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