public void AddTest()
        {
            SqlLiteral <int> l1 = SqlDml.Literal(1);
            SqlLiteral <int> l2 = SqlDml.Literal(2);
            SqlBinary        b  = l1 + l2;

            Assert.AreEqual(b.NodeType, SqlNodeType.Add);

            b = b - ~l1;
            Assert.AreEqual(b.NodeType, SqlNodeType.Subtract);
            Assert.AreEqual(b.Right.NodeType, SqlNodeType.BitNot);

            SqlSelect s = SqlDml.Select();

            s.Columns.Add(1, "id");
            b = b / s;
            Assert.AreEqual(b.NodeType, SqlNodeType.Divide);
            Assert.AreEqual(b.Right.NodeType, SqlNodeType.SubSelect);

            SqlCast c = SqlDml.Cast(l1, SqlType.Decimal);

            Assert.AreEqual(c.NodeType, SqlNodeType.Cast);

            SqlFunctionCall l = SqlDml.CharLength(SqlDml.Literal("name"));

            b = c % l;
            Assert.AreEqual(b.NodeType, SqlNodeType.Modulo);
            Assert.AreEqual(b.Right.NodeType, SqlNodeType.FunctionCall);

            b = l1 * (-l2);
            Assert.AreEqual(b.NodeType, SqlNodeType.Multiply);
            Assert.AreEqual(b.Right.NodeType, SqlNodeType.Negate);

            SqlBatch    batch = SqlDml.Batch();
            SqlVariable v1    = SqlDml.Variable("v1", SqlType.Double);

            batch.Add(v1.Declare());
            batch.Add(SqlDml.Assign(v1, 1.0));
            s = SqlDml.Select();
            s.Columns.Add(b, "value");
            batch.Add(s);
        }
        private void DropSchema(Catalog catalog)
        {
            var schema    = catalog.DefaultSchema;
            var dropBatch = SqlDml.Batch();

            foreach (var constraint in schema.Tables.Where(t => t.TableConstraints.Count != 0).SelectMany(t => t.TableConstraints.OfType <ForeignKey>()))
            {
                if (dropBatch.Count > 31)
                {
                    ExecuteBatch(dropBatch);
                    dropBatch = SqlDml.Batch();
                }
                dropBatch.Add(SqlDdl.Alter(constraint.Table, SqlDdl.DropConstraint(constraint)));
            }

            foreach (var view in schema.Views)
            {
                if (dropBatch.Count > 31)
                {
                    ExecuteBatch(dropBatch);
                    dropBatch = SqlDml.Batch();
                }
                dropBatch.Add(SqlDdl.Drop(view));
                schema.Tables.Remove(schema.Tables[view.Name]);
            }

            foreach (var schemaTable in schema.Tables)
            {
                if (dropBatch.Count > 31)
                {
                    ExecuteBatch(dropBatch);
                    dropBatch = SqlDml.Batch();
                }
                dropBatch.Add(SqlDdl.Drop(schemaTable));
            }

            if (dropBatch.Count != 0)
            {
                ExecuteBatch(dropBatch);
            }
        }
        public void BatchTest()
        {
            var table = testSchema.CreateTable(BatchTestTable);

            CreateIdColumn(table);
            ExecuteNonQuery(SqlDdl.Create(table));
            var tableRef = SqlDml.TableRef(table);

            var singleStatementBatch = SqlDml.Batch();

            singleStatementBatch.Add(CreateInsert(tableRef));
            ExecuteNonQuery(singleStatementBatch);

            var multiStatementBatch = SqlDml.Batch();

            multiStatementBatch.Add(CreateInsert(tableRef));
            multiStatementBatch.Add(CreateInsert(tableRef));
            ExecuteNonQuery(multiStatementBatch);

            var innerEmptyBatch           = SqlDml.Batch();
            var innerSingleStatementBatch = SqlDml.Batch();

            innerSingleStatementBatch.Add(CreateInsert(tableRef));
            var innerMultiStatementBatch = SqlDml.Batch();

            innerMultiStatementBatch.Add(CreateInsert(tableRef));
            var outerBatch = SqlDml.Batch();

            outerBatch.Add(innerEmptyBatch);
            outerBatch.Add(innerSingleStatementBatch);
            outerBatch.Add(multiStatementBatch);
            ExecuteNonQuery(outerBatch);

            var countQuery = SqlDml.Select(tableRef);

            countQuery.Columns.Add(SqlDml.Count());
            Assert.AreEqual(6, Convert.ToInt32(ExecuteScalar(countQuery)));
        }
Exemple #4
0
        private void CreateSchema()
        {
            var schema = Catalog.DefaultSchema;

            var batch = SqlDml.Batch();
            var table = schema.CreateTable("actor");

            table.CreateColumn("actor_id", new SqlValueType("SMALLINT UNSIGNED"));
            table.CreateColumn("first_name", new SqlValueType(SqlType.VarChar, 45));
            table.CreateColumn("last_name", new SqlValueType(SqlType.VarChar, 45));
            table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");// add default value and on update event
            table.CreatePrimaryKey("pk_actor_actor_id", table.TableColumns["actor_id"]);
            var index = table.CreateIndex("inx_actor_last_name");

            index.CreateIndexColumn(table.TableColumns["last_name"]);
            batch.Add(SqlDdl.Create(table));
            batch.Add(SqlDdl.Create(index));


            table = schema.CreateTable("country");
            table.CreateColumn("country_id", new SqlValueType("SMALLINT UNSIGNED"));
            table.CreateColumn("country", new SqlValueType(SqlType.VarChar, 50));
            table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");;
            table.CreatePrimaryKey("pk_country_country_id", table.TableColumns["country_id"]);
            batch.Add(SqlDdl.Create(table));

            table = schema.CreateTable("city");
            table.CreateColumn("city_id", new SqlValueType("SMALLINT UNSIGNED"));
            table.CreateColumn("city", new SqlValueType(SqlType.VarChar, 50));
            table.CreateColumn("country_id", new SqlValueType("SMALLINT UNSIGNED"));
            table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");;
            table.CreatePrimaryKey("pk_city_city_id", table.TableColumns["city_id"]);
            var foreignKey = table.CreateForeignKey("fk_city_country");

            foreignKey.ReferencedTable = schema.Tables["country"];
            foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["country_id"]);
            foreignKey.Columns.Add(table.TableColumns["country_id"]);
            foreignKey.OnUpdate = ReferentialAction.Cascade;
            foreignKey.OnDelete = ReferentialAction.Restrict;
            index = table.CreateIndex("inx_city_country_id");
            index.CreateIndexColumn(table.TableColumns["country_id"]);
            batch.Add(SqlDdl.Create(table));
            batch.Add(SqlDdl.Create(index));

            table = schema.CreateTable("address");
            table.CreateColumn("address_id", new SqlValueType("SMALLINT UNSIGNED"));
            table.CreateColumn("address", new SqlValueType(SqlType.VarChar, 50));
            var column = table.CreateColumn("address2", new SqlValueType(SqlType.VarChar, 50));

            column.IsNullable   = true;
            column.DefaultValue = SqlDml.Native("NULL");
            table.CreateColumn("district", new SqlValueType(SqlType.VarChar, 20));
            table.CreateColumn("city_id", new SqlValueType("SMALLINT UNSIGNED"));
            column = table.CreateColumn("postal_code", new SqlValueType(SqlType.VarChar, 20));
            column.DefaultValue = SqlDml.Native("NULL");
            column.IsNullable   = true;
            table.CreateColumn("phone", new SqlValueType(SqlType.VarChar, 20));
            table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
            table.CreatePrimaryKey("pk_address_address_id", table.TableColumns["address_id"]);
            foreignKey = table.CreateForeignKey("fk_address_city");
            foreignKey.ReferencedTable = schema.Tables["city"];
            foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["city_id"]);
            foreignKey.Columns.Add(table.TableColumns["city_id"]);
            foreignKey.OnUpdate = ReferentialAction.Cascade;
            foreignKey.OnDelete = ReferentialAction.Restrict;
            index = table.CreateIndex("inx_address_city_id");
            index.CreateIndexColumn(table.TableColumns["city_id"]);
            batch.Add(SqlDdl.Create(table));
            batch.Add(SqlDdl.Create(index));

            table = schema.CreateTable("category");
            table.CreateColumn("category_id", new SqlValueType("SMALLINT UNSIGNED"));
            table.CreateColumn("name", new SqlValueType(SqlType.VarChar, 25));
            table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");;
            table.CreatePrimaryKey("pk_category_category_id", table.TableColumns["category_id"]);
            batch.Add(SqlDdl.Create(table));

            table = schema.CreateTable("film_text");
            table.CreateColumn("film_id", new SqlValueType("SMALLINT UNSIGNED"));
            table.CreateColumn("title", new SqlValueType(SqlType.VarChar, 255));
            table.CreateColumn("text", new SqlValueType(SqlType.VarCharMax));
            table.CreatePrimaryKey("pk_film_text_film_id", table.TableColumns["film_id"]);
            var fullTextIndex = table.CreateFullTextIndex("idx_film_text_title_text");

            fullTextIndex.CreateIndexColumn(table.TableColumns["title"]);
            fullTextIndex.CreateIndexColumn(table.TableColumns["text"]);
            batch.Add(SqlDdl.Create(table));

            table = schema.CreateTable("language");
            table.CreateColumn("language_id", new SqlValueType("TINYINT UNSIGNED"));
            table.CreateColumn("name", new SqlValueType(SqlType.Char, 20));
            table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
            table.CreatePrimaryKey("pk_language_language_id", table.TableColumns["language_id"]);
            batch.Add(SqlDdl.Create(table));

            table = schema.CreateTable("film");
            table.CreateColumn("film_id", new SqlValueType("SMALLINT UNSIGNED"));
            table.CreateColumn("title", new SqlValueType(SqlType.VarChar, 255));
            column = table.CreateColumn("description", new SqlValueType(SqlType.VarCharMax));
            column.DefaultValue = SqlDml.Native("NULL");
            column.IsNullable   = true;
            column = table.CreateColumn("release_year", new SqlValueType("YEAR"));
            column.DefaultValue = SqlDml.Native("NULL");
            column.IsNullable   = true;
            table.CreateColumn("language_id", new SqlValueType("TINYINT UNSIGNED"));
            column              = table.CreateColumn("original_language_id", new SqlValueType("TINYINT UNSIGNED"));
            column.IsNullable   = true;
            column.DefaultValue = SqlDml.Native("NULL");
            column              = table.CreateColumn("rental_duration", new SqlValueType("TINYINT UNSIGNED"));
            column.DefaultValue = 3;
            column              = table.CreateColumn("rental_rate", new SqlValueType(SqlType.Decimal, 5, 2));
            column.DefaultValue = 4.99;
            column              = table.CreateColumn("length", new SqlValueType("SMALLINT UNSIGNED"));
            column.DefaultValue = SqlDml.Native("NULL");
            column.IsNullable   = true;
            column              = table.CreateColumn("replacement_cost", new SqlValueType(SqlType.Decimal, 5, 2));
            column.DefaultValue = 19.99;
            table.CreateColumn("rating", new SqlValueType("ENUM('G', 'PG', 'PG-13', 'R', 'NC-17')")).DefaultValue = 'G';
            table.CreateColumn("special_features", new SqlValueType("SET('Trailers','Commentaries','Deleted Scenes','Behind the Scenes')")).DefaultValue = null;
            table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");;
            table.CreatePrimaryKey("pk_film", table.TableColumns["film_id"]);
            index = table.CreateIndex("idx_film_film_id");
            index.CreateIndexColumn(table.TableColumns["film_id"]);
            foreignKey = table.CreateForeignKey("fk_film_language");
            foreignKey.ReferencedTable = schema.Tables["language"];
            foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["language_id"]);
            foreignKey.Columns.Add(table.TableColumns["language_id"]);
            foreignKey.OnUpdate        = ReferentialAction.Cascade;
            foreignKey.OnDelete        = ReferentialAction.Restrict;
            foreignKey                 = table.CreateForeignKey("fk_film_language_1");
            foreignKey.ReferencedTable = schema.Tables["language"];
            foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["language_id"]);
            foreignKey.Columns.Add(table.TableColumns["original_language_id"]);
            foreignKey.OnUpdate = ReferentialAction.Cascade;
            foreignKey.OnDelete = ReferentialAction.Restrict;

            batch.Add(SqlDdl.Create(table));
            batch.Add(SqlDdl.Create(index));

            table = schema.CreateTable("film_actor");
            table.CreateColumn("actor_id", new SqlValueType("SMALLINT UNSIGNED"));
            table.CreateColumn("film_id", new SqlValueType("SMALLINT UNSIGNED"));
            table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
            table.CreatePrimaryKey("pk_film_actor", table.TableColumns["actor_id"], table.TableColumns["film_id"]);
            foreignKey = table.CreateForeignKey("fk_film_actor_actor");
            foreignKey.ReferencedTable = schema.Tables["actor"];
            foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["actor_id"]);
            foreignKey.Columns.Add(table.TableColumns["actor_id"]);
            foreignKey.OnUpdate        = ReferentialAction.Cascade;
            foreignKey.OnDelete        = ReferentialAction.Restrict;
            foreignKey                 = table.CreateForeignKey("fk_film_actor_film");
            foreignKey.ReferencedTable = schema.Tables["film"];
            foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["film_id"]);
            foreignKey.Columns.Add(table.TableColumns["film_id"]);
            foreignKey.OnUpdate = ReferentialAction.Cascade;
            foreignKey.OnDelete = ReferentialAction.Restrict;
            batch.Add(SqlDdl.Create(table));

            table = schema.CreateTable("film_category");
            table.CreateColumn("film_id", new SqlValueType("SMALLINT UNSIGNED"));
            table.CreateColumn("category_id", new SqlValueType("SMALLINT UNSIGNED"));
            table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
            table.CreatePrimaryKey("pk_film_category", table.TableColumns["film_id"], table.TableColumns["category_id"]);
            foreignKey = table.CreateForeignKey("fk_film_category_film");
            foreignKey.ReferencedTable = schema.Tables["film"];
            foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["film_id"]);
            foreignKey.Columns.Add(table.TableColumns["film_id"]);
            foreignKey.OnUpdate        = ReferentialAction.Cascade;
            foreignKey.OnDelete        = ReferentialAction.Restrict;
            foreignKey                 = table.CreateForeignKey("fk_film_category_category");
            foreignKey.ReferencedTable = schema.Tables["category"];
            foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["category_id"]);
            foreignKey.Columns.Add(table.TableColumns["category_id"]);
            foreignKey.OnUpdate = ReferentialAction.Cascade;
            foreignKey.OnDelete = ReferentialAction.Restrict;
            batch.Add(SqlDdl.Create(table));

            table = schema.CreateTable("store");
            table.CreateColumn("store_id", new SqlValueType("TINYINT UNSIGNED"));
            table.CreateColumn("manager_staff_id", new SqlValueType("TINYINT UNSIGNED"));
            table.CreateColumn("address_id", new SqlValueType("SMALLINT UNSIGNED"));
            table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
            table.CreatePrimaryKey("pk_store", table.TableColumns["store_id"]);
            table.CreateUniqueConstraint("idx_unique_manager", table.TableColumns["manager_staff_id"]);
            index = table.CreateIndex("idx_store_address_id");
            index.CreateIndexColumn(table.TableColumns["address_id"]);
            foreignKey = table.CreateForeignKey("fk_store_address_id");
            foreignKey.ReferencedTable = schema.Tables["address"];
            foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["address_id"]);
            foreignKey.Columns.Add(table.TableColumns["address_id"]);
            foreignKey.OnUpdate = ReferentialAction.Cascade;
            foreignKey.OnDelete = ReferentialAction.Restrict;
            batch.Add(SqlDdl.Create(table));
            batch.Add(SqlDdl.Create(index));

            table = schema.CreateTable("staff");
            table.CreateColumn("staff_id", new SqlValueType("TINYINT UNSIGNED"));
            table.CreateColumn("first_name", new SqlValueType(SqlType.VarChar, 45));
            table.CreateColumn("last_name", new SqlValueType(SqlType.VarChar, 45));
            table.CreateColumn("address_id", new SqlValueType("SMALLINT UNSIGNED"));
            column = table.CreateColumn("picture", new SqlValueType("BLOB"));
            column.DefaultValue = SqlDml.Native("NULL");
            column.IsNullable   = true;
            table.CreateColumn("email", new SqlValueType(SqlType.VarChar, 50));
            table.CreateColumn("store_id", new SqlValueType("TINYINT UNSIGNED"));
            column = table.CreateColumn("active", new SqlValueType(SqlType.Boolean));
            column.DefaultValue = SqlDml.Native("TRUE");
            table.CreateColumn("username", new SqlValueType(SqlType.VarChar, 16));
            column = table.CreateColumn("password", new SqlValueType("VARCHAR(40) BINARY"));
            column.DefaultValue = SqlDml.Native("NULL");
            column.IsNullable   = true;
            table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
            table.CreatePrimaryKey("pk_staff", table.TableColumns["staff_id"]);
            index = table.CreateIndex("idx_staff_address_id");
            index.CreateIndexColumn(table.TableColumns["address_id"]);
            var secondIndex = table.CreateIndex("idx_staff_store_id");

            secondIndex.CreateIndexColumn(table.TableColumns["store_id"]);
            foreignKey = table.CreateForeignKey("fk_staff_store");
            foreignKey.ReferencedTable = schema.Tables["store"];
            foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["store_id"]);
            foreignKey.Columns.Add(table.TableColumns["store_id"]);
            foreignKey.OnUpdate        = ReferentialAction.Cascade;
            foreignKey.OnDelete        = ReferentialAction.Restrict;
            foreignKey                 = table.CreateForeignKey("fk_staff_address");
            foreignKey.ReferencedTable = schema.Tables["address"];
            foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["address_id"]);
            foreignKey.Columns.Add(table.TableColumns["address_id"]);
            foreignKey.OnUpdate = ReferentialAction.Cascade;
            foreignKey.OnDelete = ReferentialAction.Restrict;
            batch.Add(SqlDdl.Create(table));
            batch.Add(SqlDdl.Create(index));
            batch.Add(SqlDdl.Create(secondIndex));

            foreignKey = schema.Tables["store"].CreateForeignKey("fk_store_staff");
            schema.Tables["store"].TableConstraints.Remove(foreignKey);
            foreignKey.ReferencedTable = schema.Tables["staff"];
            foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["staff_id"]);
            foreignKey.Columns.Add(schema.Tables["store"].TableColumns["manager_staff_id"]);
            foreignKey.OnUpdate = ReferentialAction.Cascade;
            foreignKey.OnDelete = ReferentialAction.Restrict;
            batch.Add(SqlDdl.Alter(schema.Tables["store"], SqlDdl.AddConstraint(foreignKey)));

            table = schema.CreateTable("customer");
            table.CreateColumn("customer_id", new SqlValueType("SMALLINT UNSIGNED"));
            table.CreateColumn("store_id", new SqlValueType("TINYINT UNSIGNED"));
            table.CreateColumn("first_name", new SqlValueType(SqlType.VarChar, 45));
            table.CreateColumn("last_name", new SqlValueType(SqlType.VarChar, 45));
            column = table.CreateColumn("email", new SqlValueType(SqlType.VarChar, 50));
            column.DefaultValue = SqlDml.Native("NULL");
            column.IsNullable   = true;
            table.CreateColumn("address_id", new SqlValueType("SMALLINT UNSIGNED"));
            column = table.CreateColumn("active", new SqlValueType(SqlType.Boolean));
            column.DefaultValue = SqlDml.Native("TRUE");
            table.CreateColumn("create_date", new SqlValueType(SqlType.DateTime));
            table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
            table.CreatePrimaryKey("pk_customer", table.TableColumns["customer_id"]);
            index = table.CreateIndex("idx_store_id");
            index.CreateIndexColumn(table.TableColumns["store_id"]);
            secondIndex = table.CreateIndex("idx_address_id");
            secondIndex.CreateIndexColumn(table.TableColumns["address_id"]);
            var thirdIndex = table.CreateIndex("idx_last_name");

            thirdIndex.CreateIndexColumn(table.TableColumns["last_name"]);
            foreignKey = table.CreateForeignKey("fk_customer_address");
            foreignKey.ReferencedTable = schema.Tables["address"];
            foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["address_id"]);
            foreignKey.Columns.Add(table.TableColumns["address_id"]);
            foreignKey.OnUpdate = ReferentialAction.Cascade;
            foreignKey.OnDelete = ReferentialAction.Restrict;
            batch.Add(SqlDdl.Create(table));
            batch.Add(SqlDdl.Create(index));
            batch.Add(SqlDdl.Create(secondIndex));
            batch.Add(SqlDdl.Create(thirdIndex));

            table = schema.CreateTable("inventory");
            table.CreateColumn("inventory_id", new SqlValueType("MEDIUMINT UNSIGNED"));
            table.CreateColumn("film_id", new SqlValueType("SMALLINT UNSIGNED"));
            table.CreateColumn("store_id", new SqlValueType("TINYINT UNSIGNED"));
            table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
            table.CreatePrimaryKey("pk_inventory", table.TableColumns["inventory_id"]);
            table.CreateIndex("idx_inventory_film_id").CreateIndexColumn(table.TableColumns["film_id"]);
            index = table.CreateIndex("idx_inventory_store_id_film_id");
            index.CreateIndexColumn(table.TableColumns["store_id"]);
            index.CreateIndexColumn(table.TableColumns["film_id"]);
            foreignKey = table.CreateForeignKey("fk_inventory_store");
            foreignKey.ReferencedTable = schema.Tables["store"];
            foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["store_id"]);
            foreignKey.Columns.Add(table.TableColumns["store_id"]);
            foreignKey.OnUpdate        = ReferentialAction.Cascade;
            foreignKey.OnDelete        = ReferentialAction.Restrict;
            foreignKey                 = table.CreateForeignKey("fk_inventory_film");
            foreignKey.ReferencedTable = schema.Tables["film"];
            foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["film_id"]);
            foreignKey.Columns.Add(table.TableColumns["film_id"]);
            foreignKey.OnUpdate = ReferentialAction.Cascade;
            foreignKey.OnDelete = ReferentialAction.Restrict;
            batch.Add(SqlDdl.Create(table));
            batch.Add(SqlDdl.Create(index));

            table = schema.CreateTable("rental");
            table.CreateColumn("rental_id", new SqlValueType(SqlType.Int32));
            table.CreateColumn("rental_date", new SqlValueType(SqlType.DateTime));
            table.CreateColumn("inventory_id", new SqlValueType("MEDIUMINT UNSIGNED"));
            table.CreateColumn("customer_id", new SqlValueType("SMALLINT UNSIGNED"));
            column = table.CreateColumn("return_date", new SqlValueType(SqlType.DateTime));
            column.DefaultValue = SqlDml.Native("NULL");
            column.IsNullable   = true;
            table.CreateColumn("staff_id", new SqlValueType("TINYINT UNSIGNED"));
            table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
            table.CreatePrimaryKey("pk_rental", table.TableColumns["rental_id"]);
            table.CreateUniqueConstraint("unique_rental_date_inventory_id_customer_id", table.TableColumns["rental_date"], table.TableColumns["inventory_id"], table.TableColumns["customer_id"]);
            index = table.CreateIndex("idx_rental_inventory_id");
            index.CreateIndexColumn(table.TableColumns["inventory_id"]);
            secondIndex = table.CreateIndex("idx_rental_customer_id");
            secondIndex.CreateIndexColumn(table.TableColumns["customer_id"]);
            thirdIndex = table.CreateIndex("idx_rental_staff_id");
            thirdIndex.CreateIndexColumn(table.TableColumns["staff_id"]);
            foreignKey = table.CreateForeignKey("fk_rental_staff");
            foreignKey.ReferencedTable = schema.Tables["staff"];
            foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["staff_id"]);
            foreignKey.Columns.Add(table.TableColumns["staff_id"]);
            foreignKey.OnUpdate        = ReferentialAction.Cascade;
            foreignKey.OnDelete        = ReferentialAction.Restrict;
            foreignKey                 = table.CreateForeignKey("fk_rental_inventory");
            foreignKey.ReferencedTable = schema.Tables["inventory"];
            foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["inventory_id"]);
            foreignKey.Columns.Add(table.TableColumns["inventory_id"]);
            batch.Add(SqlDdl.Create(table));
            batch.Add(SqlDdl.Create(index));
            batch.Add(SqlDdl.Create(secondIndex));
            batch.Add(SqlDdl.Create(thirdIndex));

            table = schema.CreateTable("payment");
            table.CreateColumn("payment_id", new SqlValueType("SMALLINT UNSIGNED"));
            table.CreateColumn("customer_id", new SqlValueType("SMALLINT UNSIGNED"));
            table.CreateColumn("staff_id", new SqlValueType("TINYINT UNSIGNED"));
            column = table.CreateColumn("rental_id", new SqlValueType(SqlType.Int32));
            column.DefaultValue = SqlDml.Native("NULL");
            column.IsNullable   = true;
            table.CreateColumn("amount", new SqlValueType(SqlType.Decimal, 5, 2));
            table.CreateColumn("payment_date", new SqlValueType(SqlType.DateTime));
            table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
            table.CreatePrimaryKey("pk_payment", table.TableColumns["payment_id"]);
            index = table.CreateIndex("idx_payment_staff_id");
            index.CreateIndexColumn(table.TableColumns["staff_id"]);
            secondIndex = table.CreateIndex("idx_payment_customer_id");
            secondIndex.CreateIndexColumn(table.TableColumns["customer_id"]);
            foreignKey = table.CreateForeignKey("fk_payment_staff");
            foreignKey.ReferencedTable = schema.Tables["staff"];
            foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["staff_id"]);
            foreignKey.Columns.Add(table.TableColumns["staff_id"]);
            foreignKey.OnUpdate        = ReferentialAction.Cascade;
            foreignKey.OnDelete        = ReferentialAction.Restrict;
            foreignKey                 = table.CreateForeignKey("fk_payment_customer");
            foreignKey.ReferencedTable = schema.Tables["customer"];
            foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["customer_id"]);
            foreignKey.Columns.Add(table.TableColumns["customer_id"]);
            foreignKey.OnUpdate        = ReferentialAction.Cascade;
            foreignKey.OnDelete        = ReferentialAction.Restrict;
            foreignKey                 = table.CreateForeignKey("fk_payment_rental");
            foreignKey.ReferencedTable = schema.Tables["rental"];
            foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["rental_id"]);
            foreignKey.Columns.Add(table.TableColumns["rental_id"]);
            batch.Add(SqlDdl.Create(table));
            batch.Add(SqlDdl.Create(index));
            batch.Add(SqlDdl.Create(secondIndex));

            table = schema.CreateTable("table1");
            table.CreateColumn("field1", new SqlValueType(SqlType.Int32));
            table.CreateColumn("field2", new SqlValueType(SqlType.VarChar, 20)).IsNullable = true;
            batch.Add(SqlDdl.Create(table));

            var view = schema.CreateView("customer_list", SqlDml.Native(@"SELECT cu.customer_id AS ID, CONCAT(cu.first_name, _utf8' ', cu.last_name) AS name, a.address AS address, a.postal_code AS `zip code`,
        a.phone AS phone, city.city AS city, country.country AS country, IF(cu.active, _utf8'active',_utf8'') AS notes, cu.store_id AS SID
        FROM customer AS cu JOIN address AS a ON cu.address_id = a.address_id JOIN city ON a.city_id = city.city_id
        JOIN country ON city.country_id = country.country_id"));

            batch.Add(SqlDdl.Create(view));

            using (var cmd = SqlConnection.CreateCommand()) {
                cmd.CommandText = SqlDriver.Compile(batch).GetCommandText();
                cmd.ExecuteNonQuery();
            }
            Catalog = SqlDriver.ExtractCatalog(SqlConnection);
        }
        protected virtual void CreateTables(Catalog catalog)
        {
            var         schema = catalog.DefaultSchema;
            Table       table;
            TableColumn column;
            var         createBatch = SqlDml.Batch();

            table = schema.CreateTable("EmployeeAddress");
            table.CreateColumn("EmployeeID", new SqlValueType(SqlType.Int32));
            table.CreateColumn("AddressID", new SqlValueType(SqlType.Int32));
            table.CreateColumn("ModifiedDate", new SqlValueType(SqlType.DateTime));
            createBatch.Add(SqlDdl.Create(table));

            table = schema.CreateTable("EmployeeDepartmentHistory");
            table.CreateColumn("EmployeeID", new SqlValueType(SqlType.Int32));
            table.CreateColumn("DepartmentID", new SqlValueType(SqlType.Int16));
            table.CreateColumn("ShiftID", new SqlValueType(SqlType.DateTime));
            table.CreateColumn("StartDate", new SqlValueType(SqlType.DateTime));
            table.CreateColumn("EndDate", new SqlValueType(SqlType.DateTime));
            table.CreateColumn("ModifiedDate", new SqlValueType(SqlType.DateTime));
            createBatch.Add(SqlDdl.Create(table));

            table = schema.CreateTable("EmployeePayHistory");
            table.CreateColumn("EmployeeID", new SqlValueType(SqlType.Int32));
            table.CreateColumn("RateChangeDate", new SqlValueType(SqlType.DateTime));
            table.CreateColumn("Rate", new SqlValueType(SqlType.Double));
            table.CreateColumn("PayFrequency", new SqlValueType(SqlType.Int16));
            table.CreateColumn("ModifiedDate", new SqlValueType(SqlType.DateTime));
            createBatch.Add(SqlDdl.Create(table));

            table = schema.CreateTable("Employee");
            table.CreateColumn("EmployeeID", new SqlValueType(SqlType.Int32));
            table.CreateColumn("NationalIDNumber", Driver.TypeMappings[typeof(string)].MapType());
            table.CreateColumn("ContactID", new SqlValueType(SqlType.Int32));
            table.CreateColumn("LoginID", Driver.TypeMappings[typeof(string)].MapType());
            table.CreateColumn("ManagerID", new SqlValueType(SqlType.Int32));
            table.CreateColumn("Title", Driver.TypeMappings[typeof(string)].MapType());
            table.CreateColumn("BirthDate", new SqlValueType(SqlType.DateTime));
            table.CreateColumn("MaritalStatus", Driver.TypeMappings[typeof(string)].MapType());
            table.CreateColumn("Gender", Driver.TypeMappings[typeof(string)].MapType());
            table.CreateColumn("HireDate", new SqlValueType(SqlType.DateTime));
            table.CreateColumn("VacationHours", new SqlValueType(SqlType.Int16));
            table.CreateColumn("SickLeaveHours", new SqlValueType(SqlType.Int16));
            table.CreateColumn("ModifiedDate", new SqlValueType(SqlType.DateTime));
            createBatch.Add(SqlDdl.Create(table));

            table = schema.CreateTable("Department");
            table.CreateColumn("DepartmentID", new SqlValueType(SqlType.Int32));
            table.CreateColumn("Name", Driver.TypeMappings[typeof(string)].MapType());
            table.CreateColumn("GroupName", Driver.TypeMappings[typeof(string)].MapType());
            table.CreateColumn("ModifiedDate", new SqlValueType(SqlType.DateTime));
            createBatch.Add(SqlDdl.Create(table));

            table = schema.CreateTable("Shift");
            table.CreateColumn("ShiftID", new SqlValueType(SqlType.Int32));
            table.CreateColumn("Name", Driver.TypeMappings[typeof(string)].MapType());
            table.CreateColumn("StartTime", new SqlValueType(SqlType.DateTime));
            table.CreateColumn("EndTime", new SqlValueType(SqlType.DateTime));
            table.CreateColumn("ModifiedDate", new SqlValueType(SqlType.DateTime));
            createBatch.Add(SqlDdl.Create(table));

            table = schema.CreateTable("JobCandidate");
            table.CreateColumn("JobCandidateID", new SqlValueType(SqlType.Int32));
            table.CreateColumn("EmployeeID", new SqlValueType(SqlType.Int32));
            table.CreateColumn("ModifiedDate", new SqlValueType(SqlType.DateTime));
            createBatch.Add(SqlDdl.Create(table));

            ExecuteBatch(createBatch);
        }