Beispiel #1
0
        public new void TestFixtureSetUp()
        {
            db = base.OpenDbConnection();
            CustomerOrdersUseCase.DropTables(db); //Has conflicting 'Order' table

            db.DropAndCreateTable <Order>();
            db.DropAndCreateTable <Customer>();
            db.DropAndCreateTable <CustomerAddress>();
            db.DropAndCreateTable <Country>();
            db.DropAndCreateTable <AliasedCustomer>();
            db.DropAndCreateTable <AliasedCustomerAddress>();
            db.DropAndCreateTable <OldAliasedCustomer>();
            db.DropAndCreateTable <OldAliasedCustomerAddress>();
            db.DropAndCreateTable <MismatchAliasCustomer>();
            db.DropAndCreateTable <MismatchAliasAddress>();

            db.DropTable <SelfCustomer>();
            db.DropTable <MultiSelfCustomer>();
            db.DropTable <SelfCustomerAddress>();

            db.CreateTable <SelfCustomerAddress>();
            db.CreateTable <MultiSelfCustomer>();
            db.CreateTable <SelfCustomer>();

            db.CreateTable <JobApplication>();
            db.CreateTable <PhoneScreen>();
        }
        private void ResetTables()
        {
            CustomerOrdersUseCase.DropTables(db); //Has conflicting 'Order' table

            db.DropAndCreateTable <Order>();
            db.DropAndCreateTable <Customer>();
            db.DropAndCreateTable <CustomerAddress>();
            db.DropAndCreateTable <Country>();
        }
Beispiel #3
0
        public new void TestFixtureSetUp()
        {
            db = base.OpenDbConnection();
            CustomerOrdersUseCase.DropTables(db); //Has conflicting 'Order' table

            db.DropAndCreateTable <Order>();
            db.DropAndCreateTable <Customer>();
            db.DropAndCreateTable <CustomerAddress>();
            db.DropAndCreateTable <Country>();
        }
Beispiel #4
0
        public void Can_create_and_populate_tables_without_quotes()
        {
            using (var db = OpenDbConnection())
            {
                ((PostgreSqlDialectProvider)DialectProvider).Normalize = true;

                CustomerOrdersUseCase.DropTables(db); //Has conflicting 'Order' table
                db.DropTable <Order>();
                db.DropTable <CustomerAddress>();
                db.DropTable <Customer>();

                db.CreateTable <Customer>();
                db.CreateTable <CustomerAddress>();
                db.CreateTable <Order>();

                db.GetLastSql().Print();

                var customer = new Customer
                {
                    Name           = "Customer 1",
                    PrimaryAddress = new CustomerAddress
                    {
                        AddressLine1 = "1 Humpty Street",
                        City         = "Humpty Doo",
                        State        = "Northern Territory",
                        Country      = "Australia"
                    },
                    Orders = new[] {
                        new Order {
                            LineItem = "Line 1", Qty = 1, Cost = 1.99m
                        },
                        new Order {
                            LineItem = "Line 2", Qty = 2, Cost = 2.99m
                        },
                    }.ToList(),
                };

                db.Save(customer, references: true);
                db.GetLastSql().Print();

                var dbCustomer = db.SingleById <Customer>(customer.Id);
                Assert.That(dbCustomer.Name, Is.EqualTo(customer.Name));
                dbCustomer = db.SqlList <Customer>("select * from Customer where Id = @Id", new { customer.Id })[0];
                Assert.That(dbCustomer.Name, Is.EqualTo(customer.Name));

                var address = db.Single <CustomerAddress>(x => x.CustomerId == customer.Id && x.Id == customer.PrimaryAddress.Id);
                Assert.That(address.Country, Is.EqualTo("Australia"));

                var orders   = db.Select <Order>(x => x.CustomerId == customer.Id);
                var totalQty = orders.Sum(x => x.Qty);
                Assert.That(totalQty, Is.EqualTo(3));

                //PostgreSqlDialectProvider.Instance.Normalize = false;
            }
        }
        public async Task Can_select_limit_on_Table_with_References_Async()
        {
            if (Dialect == Dialect.MySql)
            {
                return; //= This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
            }
#if NETCORE
            if (Dialect == Dialect.SqlServer || Dialect == Dialect.SqlServer2012)
            {
                return; // generates "... WHERE CustomerId IN (SELECT * FROM ...)"
            }
            // when should generate "... WHERE CustomerId IN (SELECT Id FROM ...)"
            // both on .NET and .NET Core
#endif

            using (var db = OpenDbConnection())
            {
                CustomerOrdersUseCase.DropTables(db); //Has conflicting 'Order' table
                db.DropAndCreateTable <Order>();
                db.DropAndCreateTable <Customer>();
                db.DropAndCreateTable <CustomerAddress>();

                var customer1 = LoadReferencesTests.GetCustomerWithOrders("1");
                db.Save(customer1, references: true);

                var customer2 = LoadReferencesTests.GetCustomerWithOrders("2");
                db.Save(customer2, references: true);

                var results = await db.LoadSelectAsync(db.From <Customer>()
                                                       .OrderBy(x => x.Id)
                                                       .Limit(1, 1));

                //db.GetLastSql().Print();

                Assert.That(results.Count, Is.EqualTo(1));
                Assert.That(results[0].Name, Is.EqualTo("Customer 2"));
                Assert.That(results[0].PrimaryAddress.AddressLine1, Is.EqualTo("2 Humpty Street"));
                Assert.That(results[0].Orders.Count, Is.EqualTo(2));

                results = await db.LoadSelectAsync(db.From <Customer>()
                                                   .Join <CustomerAddress>()
                                                   .OrderBy(x => x.Id)
                                                   .Limit(1, 1));

                db.GetLastSql().Print();

                Assert.That(results.Count, Is.EqualTo(1));
                Assert.That(results[0].Name, Is.EqualTo("Customer 2"));
                Assert.That(results[0].PrimaryAddress.AddressLine1, Is.EqualTo("2 Humpty Street"));
                Assert.That(results[0].Orders.Count, Is.EqualTo(2));
            }
        }
        private void ResetTables()
        {
            CustomerOrdersUseCase.DropTables(db); //Has conflicting 'Order' table

            if (DialectFeatures.SchemaSupport)
            {
                db.CreateSchema <ProjectTask>();
            }
            db.DropAndCreateTable <Order>();
            db.DropAndCreateTable <Customer>();
            db.DropAndCreateTable <CustomerAddress>();
            db.DropAndCreateTable <Country>();
        }
Beispiel #7
0
        public void Can_select_limit_on_Table_with_References()
        {
            //This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
            if (Dialect == Dialect.AnyMySql)
            {
                return;
            }

            //Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
            if ((Dialect & Dialect.AnySqlServer) == Dialect)
            {
                return;
            }

            using (var db = OpenDbConnection())
            {
                CustomerOrdersUseCase.DropTables(db); //Has conflicting 'Order' table
                db.DropAndCreateTable <Order>();
                db.DropAndCreateTable <Customer>();
                db.DropAndCreateTable <CustomerAddress>();

                var customer1 = LoadReferencesTests.GetCustomerWithOrders("1");
                db.Save(customer1, references: true);

                var customer2 = LoadReferencesTests.GetCustomerWithOrders("2");
                db.Save(customer2, references: true);

                var results = db.LoadSelect(db.From <Customer>()
                                            .OrderBy(x => x.Id)
                                            .Limit(1, 1));

                //db.GetLastSql().Print();

                Assert.That(results.Count, Is.EqualTo(1));
                Assert.That(results[0].Name, Is.EqualTo("Customer 2"));
                Assert.That(results[0].PrimaryAddress.AddressLine1, Is.EqualTo("2 Humpty Street"));
                Assert.That(results[0].Orders.Count, Is.EqualTo(2));

                results = db.LoadSelect(db.From <Customer>()
                                        .Join <CustomerAddress>()
                                        .OrderBy(x => x.Id)
                                        .Limit(1, 1));

                db.GetLastSql().Print();

                Assert.That(results.Count, Is.EqualTo(1));
                Assert.That(results[0].Name, Is.EqualTo("Customer 2"));
                Assert.That(results[0].PrimaryAddress.AddressLine1, Is.EqualTo("2 Humpty Street"));
                Assert.That(results[0].Orders.Count, Is.EqualTo(2));
            }
        }
        public async Task Can_select_limit_on_Table_with_References_Async()
        {
            using (var db = OpenDbConnection())
            {
                CustomerOrdersUseCase.DropTables(db); //Has conflicting 'Order' table
                db.DropAndCreateTable <Order>();
                db.DropAndCreateTable <Customer>();
                db.DropAndCreateTable <CustomerAddress>();

                var customer1 = LoadReferencesTests.GetCustomerWithOrders("1");
                db.Save(customer1, references: true);

                var customer2 = LoadReferencesTests.GetCustomerWithOrders("2");
                db.Save(customer2, references: true);

                var results = await db.LoadSelectAsync <Customer>(q => q
                                                                  .OrderBy(x => x.Id)
                                                                  .Limit(1, 1));

                //db.GetLastSql().Print();

                Assert.That(results.Count, Is.EqualTo(1));
                Assert.That(results[0].Name, Is.EqualTo("Customer 2"));
                Assert.That(results[0].PrimaryAddress.AddressLine1, Is.EqualTo("2 Humpty Street"));
                Assert.That(results[0].Orders.Count, Is.EqualTo(2));

                results = await db.LoadSelectAsync <Customer>(q => q
                                                              .Join <CustomerAddress>()
                                                              .OrderBy(x => x.Id)
                                                              .Limit(1, 1));

                db.GetLastSql().Print();

                Assert.That(results.Count, Is.EqualTo(1));
                Assert.That(results[0].Name, Is.EqualTo("Customer 2"));
                Assert.That(results[0].PrimaryAddress.AddressLine1, Is.EqualTo("2 Humpty Street"));
                Assert.That(results[0].Orders.Count, Is.EqualTo(2));
            }
        }
        public void Can_get_GetTableNamesWithRowCounts_of_keyword_table()
        {
            using (var db = OpenDbConnection())
            {
                CustomerOrdersUseCase.DropTables(db); //Has conflicting 'Order' table
                db.CreateTable <Order>();

                3.Times(i => db.Insert(new Order {
                    CustomerId = i + 1, LineItem = $"Field{i+1}"
                }));

                var tableNames = db.GetTableNamesWithRowCounts(live: true);
                Assert.That(tableNames.Count, Is.GreaterThan(0));

                var table1Name = db.GetDialectProvider().GetTableName(typeof(Order).GetModelMetadata()).StripDbQuotes();

                var table1Pos = IndexOf(tableNames, x => x.Key.EqualsIgnoreCase(table1Name) && x.Value == 3);
                Assert.That(table1Pos, Is.GreaterThanOrEqualTo(0));

                tableNames = db.GetTableNamesWithRowCounts(live: false);
                Assert.That(tableNames.Any(x => x.Key.EqualsIgnoreCase(table1Name)));
            }
        }