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