public void Integration()
{
var builder = new ContainerBuilder();
builder.RegisterModule(new RootModule(@"Shorthand.xml"));
var container = builder.Build();
// 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;
"));
}
var root = ResolveRoot(container, Cfg, true);
var response = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, response.Code);
Assert.AreEqual(string.Empty, response.Message);
using (var cn = new PostgreSqlConnectionFactory(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
root = ResolveRoot(container, Cfg, false);
response = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, response.Code);
Assert.AreEqual(string.Empty, response.Message);
using (var cn = new PostgreSqlConnectionFactory(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));
}
root = ResolveRoot(container, Cfg, false);
response = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, response.Code);
Assert.AreEqual(string.Empty, response.Message);
using (var cn = new PostgreSqlConnectionFactory(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.0, 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;"));
}
root = ResolveRoot(container, Cfg, false);
response = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, response.Code);
Assert.AreEqual(string.Empty, response.Message);
using (var cn = new PostgreSqlConnectionFactory(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 LIMIT 1;"));
Assert.AreEqual(20.11, cn.ExecuteScalar <decimal>("SELECT OrdersFreight FROM NorthWindStar WHERE OrderDetailsOrderID= 10254;"));
}
}
public void Delete_Integration()
{
var builder = new ContainerBuilder();
builder.RegisterModule(new RootModule(@"Shorthand.xml"));
var container = builder.Build();
// INITIALIZE INPUT
using (var cn = new SqlServerConnectionFactory(InputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(3, cn.Execute(@"
IF OBJECT_ID('TestDeletes') IS NOT NULL
DROP TABLE [TestDeletes];
CREATE TABLE [TestDeletes](
[TextValue] NVARCHAR(64),
[Id] INT,
[NumericValue] INT,
[RowVersion] ROWVERSION,
CONSTRAINT PK_TestDeletes_Id_NumericValue PRIMARY KEY ([Id], [NumericValue])
)
INSERT INTO [TestDeletes]([TextValue],[Id],[NumericValue]) VALUES('One',1,1);
INSERT INTO [TestDeletes]([TextValue],[Id],[NumericValue]) VALUES('Two',2,2);
INSERT INTO [TestDeletes]([TextValue],[Id],[NumericValue]) VALUES('Three',3,3);
"));
}
const string cfg = @"<cfg name='TestDeletes' mode='@(Mode)'>
<connections>
<add name='input' provider='sqlserver' database='NorthWind' />
<add name='output' provider='sqlserver' database='NorthWindStar' />
</connections>
<entities>
<add name='TestDeletes' alias='Data' delete='true' version='RowVersion'>
<fields>
<add name='TextValue' />
<add name='Id' primary-key='true' type='int' />
<add name='NumericValue' primary-key='true' type='int' />
<add name='RowVersion' type='byte[]' length='8' />
</fields>
</add>
</entities>
</cfg>";
// RUN INIT AND TEST
var root = ResolveRoot(container, cfg, InitMode());
var responseSql = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, responseSql.Code);
Assert.AreEqual(string.Empty, responseSql.Message);
using (var cn = new SqlServerConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(3, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM TestDeletesStar;"));
}
// FIRST DELTA, NO CHANGES
root = ResolveRoot(container, cfg, DefaultMode());
responseSql = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, responseSql.Code);
Assert.AreEqual(string.Empty, responseSql.Message);
using (var cn = new SqlServerConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(3, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM TestDeletesStar;"));
}
// DELETE Row 2, Two
using (var cn = new SqlServerConnectionFactory(InputConnection).GetConnection()) {
cn.Open();
const string sql = @"DELETE FROM [TestDeletes] WHERE [Id] = 2 AND [NumericValue] = 2;";
Assert.AreEqual(1, cn.Execute(sql));
}
// RUN AND CHECK, SHOULD STILL HAVE 3 RECORDS, but one marked TflDeleted = 1
root = ResolveRoot(container, cfg, DefaultMode());
responseSql = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, responseSql.Code);
Assert.AreEqual(string.Empty, responseSql.Message);
using (var cn = new SqlServerConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(3, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM TestDeletesStar;"));
Assert.AreEqual(1, cn.ExecuteScalar <decimal>("SELECT COUNT(*) FROM TestDeletesStar WHERE TflDeleted = 1;"));
}
// RUN AGAIN
root = ResolveRoot(container, cfg, DefaultMode());
responseSql = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, responseSql.Code);
Assert.AreEqual(string.Empty, responseSql.Message);
using (var cn = new SqlServerConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(3, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM TestDeletesStar;"));
Assert.AreEqual(1, cn.ExecuteScalar <decimal>("SELECT COUNT(*) FROM TestDeletesStar WHERE TflDeleted = 1;"));
}
// UN-DELETE Row 2, Two
using (var cn = new SqlServerConnectionFactory(InputConnection).GetConnection()) {
cn.Open();
const string sql = @"INSERT INTO [TestDeletes]([TextValue],[Id],[NumericValue]) VALUES('Two',2,2);";
Assert.AreEqual(1, cn.Execute(sql));
}
// RUN AND CHECK
root = ResolveRoot(container, cfg, DefaultMode());
responseSql = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, responseSql.Code);
Assert.AreEqual(string.Empty, responseSql.Message);
using (var cn = new SqlServerConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(3, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM TestDeletesStar WHERE TflDeleted = 0;"));
}
}
public void SqlCe_Integration()
{
var builder = new ContainerBuilder();
builder.RegisterModule(new RootModule(@"Shorthand.xml"));
var container = builder.Build();
// CORRECT DATA AND INITIAL LOAD
using (var cn = new SqlServerConnectionFactory(InputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(3, 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;
UPDATE Customers SET ContactName = 'Palle Ibsen' WHERE CustomerID = 'VAFFE';
"));
}
// RUN INIT AND TEST
var root = ResolveRoot(container, TestFile, true);
var responseSql = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, responseSql.Code);
Assert.AreEqual(string.Empty, responseSql.Message);
using (var cn = new SqlCeConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(2155, cn.ExecuteScalar <int>("SELECT TOP 1 Inserts FROM NorthWindControl WHERE Entity = 'Order Details' AND BatchId = 1;"));
// Assert.AreEqual(2155, cn.ExecuteScalar<int>("SELECT COUNT(*) FROM NorthWindFlat;"));
}
// FIRST DELTA, NO CHANGES
root = ResolveRoot(container, TestFile, false);
responseSql = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, responseSql.Code);
Assert.AreEqual(string.Empty, responseSql.Message);
using (var cn = new SqlCeConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(2155, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM [NorthWindOrder DetailsTable];"));
Assert.AreEqual(0, cn.ExecuteScalar <int>("SELECT TOP 1 Inserts+Updates+Deletes FROM NorthWindControl WHERE Entity = 'Order Details' AND BatchId = 9;"));
Assert.AreEqual(2155, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM NorthWindFlat;"));
}
// 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));
}
// RUN AND CHECK
root = ResolveRoot(container, TestFile, false);
responseSql = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, responseSql.Code);
Assert.AreEqual(string.Empty, responseSql.Message);
using (var cn = new SqlCeConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(1, cn.ExecuteScalar <int>("SELECT TOP 1 Updates FROM NorthWindControl WHERE Entity = 'Order Details' AND BatchId = 17;"));
Assert.AreEqual(15.0M, cn.ExecuteScalar <decimal>("SELECT OrderDetailsUnitPrice FROM NorthWindFlat WHERE OrderDetailsOrderId= 10253 AND OrderDetailsProductId = 39;"));
Assert.AreEqual(40, cn.ExecuteScalar <int>("SELECT OrderDetailsQuantity FROM NorthWindFlat WHERE OrderDetailsOrderId= 10253 AND OrderDetailsProductId = 39;"));
Assert.AreEqual(15.0 * 40, cn.ExecuteScalar <int>("SELECT OrderDetailsExtendedPrice FROM NorthWindFlat 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;"));
}
root = ResolveRoot(container, TestFile, false);
responseSql = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, responseSql.Code);
Assert.AreEqual(string.Empty, responseSql.Message);
using (var cn = new SqlCeConnectionFactory(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 NorthWindFlat WHERE OrderDetailsOrderId= 10254;"));
Assert.AreEqual(20.11M, cn.ExecuteScalar <decimal>("SELECT OrdersFreight FROM NorthWindFlat WHERE OrderDetailsOrderId= 10254;"));
Assert.AreEqual(26, cn.ExecuteScalar <int>("SELECT TflBatchId FROM NorthWindFlat WHERE OrderDetailsOrderId= 10254;"));
}
// CHANGE A CUSTOMER'S CONTACT NAME FROM Palle Ibsen TO Paul Ibsen
using (var cn = new SqlServerConnectionFactory(InputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(1, cn.Execute("UPDATE Customers SET ContactName = 'Paul Ibsen' WHERE CustomerID = 'VAFFE';"));
}
root = ResolveRoot(container, TestFile, false);
responseSql = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, responseSql.Code);
Assert.AreEqual(string.Empty, responseSql.Message);
using (var cn = new SqlCeConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(1, cn.ExecuteScalar <int>("SELECT Updates FROM NorthWindControl WHERE Entity = 'Customers' AND BatchId = 35;"));
Assert.AreEqual("Paul Ibsen", cn.ExecuteScalar <string>("SELECT DISTINCT CustomersContactName FROM NorthWindFlat WHERE OrdersCustomerID = 'VAFFE';"));
Assert.AreEqual(35, cn.ExecuteScalar <int>("SELECT DISTINCT TflBatchId FROM NorthWindFlat WHERE OrdersCustomerID = 'VAFFE';"), "The TflBatchId should be updated on the master to indicate a change has occured.");
}
}
public void Integration()
{
var builder = new ContainerBuilder();
builder.RegisterModule(new RootModule(@"Shorthand.xml"));
var container = builder.Build();
var pool = new SingleNodeConnectionPool(new Uri(ElasticConnection.Url));
var settings = new ConnectionConfiguration(pool);
var client = new ElasticLowLevelClient(settings);
// CORRECT DATA AND INITIAL LOAD
using (var cn = new SqlServerConnectionFactory(InputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(3, 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;
UPDATE Customers SET ContactName = 'Palle Ibsen' WHERE CustomerID = 'VAFFE';
"));
}
var root = ResolveRoot(container, SqlTestFile, true);
var responseSql = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, responseSql.Code);
root = ResolveRoot(container, ElasticTestFile, true);
var responseElastic = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, responseElastic.Code);
Assert.AreEqual(2155, client.Count <DynamicResponse>("northwind", "star", "{\"query\" : { \"match_all\" : { }}}").Body["count"].Value);
// FIRST DELTA, NO CHANGES
root = ResolveRoot(container, ElasticTestFile, false);
responseElastic = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, responseElastic.Code);
Assert.AreEqual(string.Empty, responseElastic.Message);
Assert.AreEqual(2155, client.Count <DynamicResponse>("northwind", "star", "{\"query\" : { \"match_all\" : { }}}").Body["count"].Value);
// 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));
}
// RUN AND CHECK SQL
root = ResolveRoot(container, SqlTestFile, false);
responseSql = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, responseSql.Code);
Assert.AreEqual(string.Empty, responseSql.Message);
using (var cn = new SqlServerConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(1, cn.ExecuteScalar <int>("SELECT TOP 1 Updates FROM NorthWindControl WHERE Entity = 'Order Details' AND BatchId = 9;"));
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;"));
}
// RUN AND CHECK ELASTIC
root = ResolveRoot(container, ElasticTestFile, false);
responseElastic = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, responseElastic.Code);
Assert.AreEqual(string.Empty, responseElastic.Message);
var response = client.Search <DynamicResponse>(
"northwind",
"star", @"{
""query"" : {
""constant_score"" : {
""filter"" : {
""bool"" : {
""must"" : [
{ ""term"" : {""orderdetailsorderid"" : 10253}},
{ ""term"" : {""orderdetailsproductid"" : 39}}
]
}
}
}
}
}");
var hits = (response.Body["hits"]["hits"] as ElasticsearchDynamicValue).Value as IList <object>;
var hit = hits[0] as IDictionary <string, object>;
var source = hit["_source"] as IDictionary <string, object>;
Assert.AreEqual(source["orderdetailsunitprice"], 15.0);
Assert.AreEqual(source["orderdetailsquantity"], (long)40);
Assert.AreEqual(source["orderdetailsextendedprice"], 40 * 15.0);
// 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;"));
}
// RUN AND CHECK SQL
root = ResolveRoot(container, SqlTestFile, false);
responseSql = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, responseSql.Code);
Assert.AreEqual(string.Empty, responseSql.Message);
using (var cn = new SqlServerConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(1, cn.ExecuteScalar <int>("SELECT Updates FROM NorthWindControl WHERE Entity = 'Orders' AND BatchId = 18;"));
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;"));
}
// RUN AND CHECK ELASTIC
root = ResolveRoot(container, ElasticTestFile, false);
responseElastic = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, responseElastic.Code);
Assert.AreEqual(string.Empty, responseElastic.Message);
response = client.Search <DynamicResponse>(
"northwind",
"star",
@"{
""query"" : {
""constant_score"" : {
""filter"" : {
""bool"" : {
""must"" : [
{ ""term"" : {""orderdetailsorderid"" : 10254}}
]
}
}
}
}
}");
hits = (response.Body["hits"]["hits"] as ElasticsearchDynamicValue).Value as IList <object>;
hit = hits[0] as IDictionary <string, object>;
source = hit["_source"] as IDictionary <string, object>;
Assert.AreEqual(source["orderscustomerid"], "VICTE");
Assert.AreEqual(source["ordersfreight"], 20.11);
}
public void Integration()
{
var builder = new ContainerBuilder();
builder.RegisterModule(new RootModule(@"Shorthand.xml"));
var container = builder.Build();
// 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;
"));
}
var root = ResolveRoot(container, TestFile, true);
var response = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, response.Code);
Assert.AreEqual(string.Empty, response.Message);
using (var reader = IndexReader.Open(FSDirectory.Open(new DirectoryInfo(Path.Combine(OutputConnection.Folder, "Order Details"))), true)) {
Assert.AreEqual(2155, reader.NumDocs());
}
// FIRST DELTA, NO CHANGES
root = ResolveRoot(container, TestFile, false);
response = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, response.Code);
Assert.AreEqual(string.Empty, response.Message);
using (var reader = IndexReader.Open(FSDirectory.Open(new DirectoryInfo(Path.Combine(OutputConnection.Folder, "Order Details"))), true)) {
Assert.AreEqual(2155, reader.NumDocs());
}
// 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));
}
root = ResolveRoot(container, TestFile, false);
response = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, response.Code);
Assert.AreEqual(string.Empty, response.Message);
using (var searcher = new IndexSearcher(FSDirectory.Open(new DirectoryInfo(Path.Combine(OutputConnection.Folder, "Order Details"))), true)) {
var hits = searcher.Search(new TermQuery(new Term("TflId", "1025339")), null, 1);
Assert.AreEqual(1, hits.TotalHits);
var hit = searcher.Doc(hits.ScoreDocs[0].Doc);
Assert.AreEqual(15.0d, Convert.ToDecimal(hit.Get("OrderDetailsUnitPrice")));
Assert.AreEqual(40, Convert.ToInt32(hit.Get("OrderDetailsQuantity")));
Assert.AreEqual(40 * 15.0d, Convert.ToDecimal(hit.Get("OrderDetailsExtendedPrice")));
}
// 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;"));
}
root = ResolveRoot(container, TestFile, false);
response = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, response.Code);
Assert.AreEqual(string.Empty, response.Message);
using (var searcher = new IndexSearcher(FSDirectory.Open(new DirectoryInfo(Path.Combine(OutputConnection.Folder, "Orders"))), true)) {
var hits = searcher.Search(new TermQuery(new Term("OrdersOrderID", NumericUtils.IntToPrefixCoded(10254))), 1);
Assert.AreEqual(1, hits.TotalHits);
var hit = searcher.Doc(hits.ScoreDocs[0].Doc);
Assert.AreEqual("VICTE", hit.Get("OrdersCustomerID"));
Assert.AreEqual(20.11d, Convert.ToDecimal(hit.Get("OrdersFreight")));
}
}
public void SqlServer_Integration()
{
var builder = new ContainerBuilder();
builder.RegisterModule(new RootModule(@"Shorthand.xml"));
var container = builder.Build();
// PUT INVENTORY BACK IN ORGINAL LOCATION
using (var cn = new SqlServerConnectionFactory(InputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(1, cn.Execute(@"
UPDATE Inventory SET StorageLocationKey = '152C36A9-1C87-4841-BD73-801E4BB7097A' WHERE InventoryKey = '7C9D2F61-5BEE-4A79-85B9-3DFE01B55EC3';
"));
}
// RUN INIT AND TEST
var root = ResolveRoot(container, TestFile, true);
var responseSql = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, responseSql.Code);
Assert.AreEqual(string.Empty, responseSql.Message);
using (var cn = new SqlServerConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(4, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM InventoryStar;"));
Assert.AreEqual("Scope-Indiana", cn.ExecuteScalar <string>("SELECT Warehouse FROM InventoryStar WHERE InventoryKey = '7C9D2F61-5BEE-4A79-85B9-3DFE01B55EC3'"));
Assert.AreEqual(4, cn.ExecuteScalar <int>("SELECT TOP 1 Inserts FROM InventoryControl WHERE Entity = 'Inventory' AND BatchId = 1;"));
}
// FIRST DELTA, NO CHANGES
root = ResolveRoot(container, TestFile, false);
responseSql = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, responseSql.Code);
Assert.AreEqual(string.Empty, responseSql.Message);
using (var cn = new SqlServerConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(4, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM InventoryStar;"));
Assert.AreEqual(0, cn.ExecuteScalar <int>("SELECT TOP 1 Inserts+Updates+Deletes FROM InventoryControl WHERE Entity = 'Inventory' AND BatchId = 8;"));
Assert.AreEqual("Scope-Indiana", cn.ExecuteScalar <string>("SELECT Warehouse FROM InventoryStar WHERE InventoryKey = '7C9D2F61-5BEE-4A79-85B9-3DFE01B55EC3'"));
}
// MOVE INVENTORY
using (var cn = new SqlServerConnectionFactory(InputConnection).GetConnection()) {
cn.Open();
const string sql = @"UPDATE Inventory SET StorageLocationKey = '11111111-2222-3333-4444-555555555555' WHERE InventoryKey = '7C9D2F61-5BEE-4A79-85B9-3DFE01B55EC3';";
Assert.AreEqual(1, cn.Execute(sql));
}
// RUN AND CHECK
root = ResolveRoot(container, TestFile, false);
responseSql = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, responseSql.Code);
Assert.AreEqual(string.Empty, responseSql.Message);
using (var cn = new SqlServerConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual("Fitted", cn.ExecuteScalar <string>("SELECT Warehouse FROM InventoryStar WHERE InventoryKey = '7C9D2F61-5BEE-4A79-85B9-3DFE01B55EC3'"));
}
// MOVE INVENTORY AGAIN
using (var cn = new SqlServerConnectionFactory(InputConnection).GetConnection()) {
cn.Open();
const string sql = @"UPDATE Inventory SET StorageLocationKey = '6BAE6E58-356E-4639-8DC6-036B0A5DD529' WHERE InventoryKey = '7C9D2F61-5BEE-4A79-85B9-3DFE01B55EC3';";
Assert.AreEqual(1, cn.Execute(sql));
}
// RUN AND CHECK
root = ResolveRoot(container, TestFile, false);
responseSql = new PipelineAction(root, new PipelineContext(new DebugLogger(), root)).Execute();
Assert.AreEqual(200, responseSql.Code);
Assert.AreEqual(string.Empty, responseSql.Message);
using (var cn = new SqlServerConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual("Scope-Indiana", cn.ExecuteScalar <string>("SELECT Warehouse FROM InventoryStar WHERE InventoryKey = '7C9D2F61-5BEE-4A79-85B9-3DFE01B55EC3'"));
}
}