Example #1
0
        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);
                }
            }
        }
Example #2
0
        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");
            }
        }