public void CreateCatalog()
{
var connectionString = "Filename=./sqlite.db; Mode=ReadWriteCreate";
var catalog = new Catalog("test_db");
var schema = catalog.AddSchema(_schemaName);
var table = schema.AddTable("Test");
var colPk = table.AddColumn("TestId", DbType.Int64);
colPk.IsPrimaryKey = true;
var colName = table.AddColumn("Name", DbType.String);
colName.MaxLength = 256;
colName.IsNullable = false;
var builder = new SqliteScriptBuilder();
var sql = builder.BuildCreateScript(catalog);
IParameterFactory parameterFactory = new SqliteParameterFactory();
IConnectionFactory connectionFactory = new SqliteConnectionFactory();
using (var connection = connectionFactory.Open(connectionString))
using (var command = new Command(connection, parameterFactory))
{
var affected = command.Execute(sql);
//Assert.IsNotNull(dataSet);
//Assert.AreEqual(1, dataSet.Tables.Count);
//Assert.IsTrue(dataSet.Tables[0].Rows.Count > 0);
}
}
public void Constructor()
{
IParameterFactory parameterFactory = new SqliteParameterFactory();
IConnectionFactory connectionFactory = new SqliteConnectionFactory();
using (var connection = connectionFactory.Open(_connectionString))
using (var command = new Command(connection, parameterFactory))
{
Assert.IsNotNull(command);
}
}
public void ExecuteScalor()
{
IParameterFactory parameterFactory = new SqliteParameterFactory();
IConnectionFactory connectionFactory = new SqliteConnectionFactory();
ISqlStatementBuilder sqlBuilder = new SqliteStatementBuilder(parameterFactory);
using (var connection = connectionFactory.Open(_connectionString))
using (var command = new Command(connection, parameterFactory))
{
var sql = sqlBuilder.GetExistsStatement <Test>();
var exists = command.ExecuteScalar <bool>(sql);
Assert.IsTrue(exists);
sql = sqlBuilder.GetExistsStatement <DoesNotExist>();
exists = command.ExecuteScalar <bool>(sql);
Assert.IsFalse(exists);
}
}
//[Ignore]
public void SqlLite_Integration()
{
var logger = new ConsoleLogger(LogLevel.Debug);
// CORRECT DATA AND INITIAL LOAD
using (var cn = new SqlServerConnectionFactory(InputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(2, cn.Execute(@"
UPDATE [Order Details] SET UnitPrice = 14.40, Quantity = 42 WHERE OrderId = 10253 AND ProductId = 39;
UPDATE Orders SET CustomerID = 'CHOPS', Freight = 22.98 WHERE OrderId = 10254;
"));
}
using (var outer = new ConfigurationContainer(new CSharpModule()).CreateScope(TestFile + "?Mode=init", logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new CSharpModule(), new SqlServerModule(), new SqliteModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
using (var cn = new SqliteConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(2155, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM NorthWindStar;"));
Assert.AreEqual(2155, cn.ExecuteScalar <int>("SELECT Inserts FROM NorthWindControl WHERE Entity = 'Order Details' AND BatchId = 1 LIMIT 1;"));
}
// FIRST DELTA, NO CHANGES
using (var outer = new ConfigurationContainer(new CSharpModule()).CreateScope(TestFile, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new CSharpModule(), new SqlServerModule(), new SqliteModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
using (var cn = new SqliteConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(2155, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM NorthWindStar;"));
Assert.AreEqual(0, cn.ExecuteScalar <int>("SELECT Inserts+Updates+Deletes FROM NorthWindControl WHERE Entity = 'Order Details' AND BatchId = 9 LIMIT 1;"));
}
// CHANGE 2 FIELDS IN 1 RECORD IN MASTER TABLE THAT WILL CAUSE CALCULATED FIELD TO BE UPDATED TOO
using (var cn = new SqlServerConnectionFactory(InputConnection).GetConnection()) {
cn.Open();
const string sql = @"UPDATE [Order Details] SET UnitPrice = 15, Quantity = 40 WHERE OrderId = 10253 AND ProductId = 39;";
Assert.AreEqual(1, cn.Execute(sql));
}
using (var outer = new ConfigurationContainer(new CSharpModule()).CreateScope(TestFile, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new CSharpModule(), new SqlServerModule(), new SqliteModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
using (var cn = new SqliteConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(1, cn.ExecuteScalar <int>("SELECT Updates FROM NorthWindControl WHERE Entity = 'Order Details' AND BatchId = 17 LIMIT 1;"));
Assert.AreEqual(15.0M, cn.ExecuteScalar <decimal>("SELECT OrderDetailsUnitPrice FROM NorthWindStar WHERE OrderDetailsOrderId= 10253 AND OrderDetailsProductId = 39;"));
Assert.AreEqual(40, cn.ExecuteScalar <int>("SELECT OrderDetailsQuantity FROM NorthWindStar WHERE OrderDetailsOrderId= 10253 AND OrderDetailsProductId = 39;"));
Assert.AreEqual(15.0 * 40, cn.ExecuteScalar <int>("SELECT OrderDetailsExtendedPrice FROM NorthWindStar WHERE OrderDetailsOrderId= 10253 AND OrderDetailsProductId = 39;"));
}
// CHANGE 1 RECORD'S CUSTOMERID AND FREIGHT ON ORDERS TABLE
using (var cn = new SqlServerConnectionFactory(InputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(1, cn.Execute("UPDATE Orders SET CustomerID = 'VICTE', Freight = 20.11 WHERE OrderId = 10254;"));
}
using (var outer = new ConfigurationContainer(new CSharpModule()).CreateScope(TestFile, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new CSharpModule(), new SqlServerModule(), new SqliteModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
using (var cn = new SqliteConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(1, cn.ExecuteScalar <int>("SELECT Updates FROM NorthWindControl WHERE Entity = 'Orders' AND BatchId = 26;"));
Assert.AreEqual("VICTE", cn.ExecuteScalar <string>("SELECT OrdersCustomerId FROM NorthWindStar WHERE OrderDetailsOrderId= 10254;"));
Assert.AreEqual(20.11M, cn.ExecuteScalar <decimal>("SELECT OrdersFreight FROM NorthWindStar WHERE OrderDetailsOrderId= 10254;"));
}
}