public void TryIt()
{
const string xml = @"
<add name='TestProcess'>
<entities>
<add name='TestData'>
<rows>
<add Field1='11' Field2='12' Field3='13' />
<add Field1='xx' Field2='' Field3='100.8' />
</rows>
<fields>
<add name='Field1' v='contains(1)' message-field='Message' />
<add name='Field2' v='required().is(int)' message-field='Message' />
<add name='Field3' v='matches(^[0-9/.]{5}$)' message-field='Message' help='Field3 must be a 5 digit number (including decimal pt.)' />
</fields>
<calculated-fields>
<add name='Message' length='1000' default='' />
</calculated-fields>
</add>
</entities>
</add>";
var logger = new ConsoleLogger(LogLevel.Debug);
using (var outer = new ConfigurationContainer().CreateScope(xml, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container().CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
var output = controller.Read().ToArray();
var message = process.Entities.First().CalculatedFields.First(cf => cf.Name == "Message");
var valid = process.Entities.First().CalculatedFields.First(cf => cf.Name == "TestDataValid");
Assert.IsFalse((bool)output[0][valid]);
Assert.AreEqual("Field3 must be a 5 digit number (including decimal pt.)", output[0][message].ToString().Replace("|", " ").TrimEnd());
Assert.AreEqual("Field1 must contain 1. Field2 is required. Field2's value is incompatable with the int data type.", output[1][message].ToString().Replace("|", " ").TrimEnd());
}
}
}
/// <summary>
/// Initializes a new instance of <see cref="SingleHierarchyConfigurationUIHostAdapter"/>.
/// </summary>
/// <param name="hostConfiguration">The <see cref="HostAdapterConfiguration"/> that contains information on which assemblies to load into the designer.</param>
/// <param name="serviceProvider">An <see cref="IServiceProvider"/> used to obtain services.</param>
public SingleHierarchyConfigurationUIHostAdapter(HostAdapterConfiguration hostConfiguration, IServiceProvider serviceProvider)
{
this.serviceProvider = serviceProvider;
editorUI = new ConfigurationEditorUI();
elementHost = new ElementHost {
Child = editorUI, Dock = DockStyle.Fill
};
editorControl = new UserControl()
{
Dock = DockStyle.Fill
};
editorControl.Controls.Add(elementHost);
container = new ConfigurationContainer(serviceProvider);
container.RegisterInstance(new AssemblyLocator(hostConfiguration.PluginDirectory));
container.RegisterInstance <IWindowsFormsEditorService>(this);
container.RegisterInstance <IUIServiceWpf>(this);
container.RegisterInstance <IUIService>(this);
container.RegisterInstance <IAssemblyDiscoveryService>(this);
container.DiscoverSubstituteTypesFromAssemblies();
elementHost.Resize += ElementHostResize;
applicationModel = container.Resolve <IApplicationModel>();
editorUI.DataContext = applicationModel;
validationModel = applicationModel.ValidationModel;
applicationModel.SelectedElementChanged += SelectedElementChanged;
validationErrorsChanged = validationModel.ValidationResults as INotifyCollectionChanged;
if (validationErrorsChanged != null)
{
validationErrorsChanged.CollectionChanged += ValidationErrorsCollectionChanged;
}
}
public void RunWithDefaults()
{
var xml = @"
<add name='TestProcess'>
<entities>
<add name='TestData'>
<rows>
<add Field1='present' Field2='here' Field3='' />
<add Field1='' Field2='present' Field3='here' />
</rows>
<fields>
<add name='Field1' v='required()' default='x' />
<add name='Field2' v='required()' default='present' />
<add name='Field3' v='required()' default='default' default-empty='true' />
</fields>
</add>
</entities>
</add>";
var logger = new ConsoleLogger(LogLevel.Debug);
using (var cfgScope = new ConfigurationContainer().CreateScope(xml, logger)) {
var process = cfgScope.Resolve <Process>();
using (var scope = new Container().CreateScope(process, logger)) {
var output = scope.Resolve <IProcessController>().Read().ToArray();
Assert.AreEqual(true, output[0][process.GetField("Field1Valid")]);
Assert.AreEqual(true, output[0][process.GetField("Field2Valid")]);
Assert.AreEqual(true, output[0][process.GetField("Field3Valid")], "Field3 is valid without a value because it gets a default value when null or blank.");
Assert.AreEqual(false, output[1][process.GetField("Field1Valid")], "Field1 is not valid without a value because it only gets a default value when it's null.");
Assert.AreEqual(true, output[1][process.GetField("Field2Valid")], "Field2 is valid and the default value being the same doesn't break it (like it used to).");
Assert.AreEqual(true, output[1][process.GetField("Field3Valid")]);
}
}
}
public void Run()
{
var xml = @"
<add name='TestProcess'>
<entities>
<add name='TestData'>
<rows>
<add Field1='present' Field2='here' Field3='' />
<add Field1='' Field2='present' Field3='here' />
</rows>
<fields>
<add name='Field1' v='required()' />
<add name='Field2' v='required()' />
<add name='Field3' v='required()' />
</fields>
</add>
</entities>
</add>";
var logger = new ConsoleLogger(LogLevel.Debug);
using (var cfgScope = new ConfigurationContainer().CreateScope(xml, logger)) {
var process = cfgScope.Resolve <Process>();
using (var scope = new Container().CreateScope(process, logger)) {
var output = scope.Resolve <IProcessController>().Read().ToArray();
Assert.AreEqual(true, output[0][process.GetField("Field1Valid")]);
Assert.AreEqual(true, output[0][process.GetField("Field2Valid")]);
Assert.AreEqual(false, output[0][process.GetField("Field3Valid")]);
Assert.AreEqual(false, output[1][process.GetField("Field1Valid")]);
Assert.AreEqual(true, output[1][process.GetField("Field2Valid")]);
Assert.AreEqual(true, output[1][process.GetField("Field3Valid")]);
}
}
}
public void InstancesExist()
{
var cfg = @"<cfg name='test'>
<connections>
<add name='input' provider='aws' service='connect' command='list-instances' />
</connections>
<entities>
<add name='entity'>
<fields>
<add name='arn' primary-key='true' />
<add name='CreatedTime' type='datetime' />
<add name='Id' length='100' />
<add name='IdentityManagementType' />
<add name='InboundCallsEnabled' type='bool' />
<add name='InstanceAlias' length='62' />
<add name='InstanceStatus' />
<add name='OutboundCallsEnabled' type='bool' />
<add name='ServiceRole' length='512' />
</fields>
</add>
</entities>
</cfg>";
Environment.SetEnvironmentVariable("AWS_PROFILE", "vlad");
var logger = new ConsoleLogger(LogLevel.Debug);
using (var outer = new ConfigurationContainer().CreateScope(cfg, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new AmazonConnectProviderModule(process)).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
IRow[] rows = controller.Read().ToArray();
Assert.AreNotEqual(0, rows.Count());
}
}
}
public void Write750()
{
const string xml = @"<add name='TestProcess' mode='init'>
<parameters>
<add name='Size' type='int' value='100000' />
<add name='MDOP' type='int' value='2' />
</parameters>
<connections>
<add name='input' provider='bogus' seed='1' />
<add name='output' provider='solr' core='bogus' server='localhost' folder='D:\Java\solr-7.5.0\cores' version='7.5.0' path='solr' port='8983' max-degree-of-parallelism='@[MDOP]' request-timeout='100' />
</connections>
<entities>
<add name='Contact' size='@[Size]' insert-size='255'>
<fields>
<add name='FirstName' />
<add name='LastName' />
<add name='Stars' type='byte' min='1' max='5' />
<add name='Reviewers' type='int' min='0' max='500' />
</fields>
</add>
</entities>
</add>";
var logger = new ConsoleLogger(LogLevel.Info);
using (var outer = new ConfigurationContainer().CreateScope(xml, logger, new Dictionary <string, string>()
{
{ "MDOP", "2" }
})) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new BogusModule(), new SolrModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
Assert.AreEqual(process.Entities.First().Inserts, (uint)100000);
}
}
}
public void Run()
{
var xml = @"
<add name='Test Min Length'>
<entities>
<add name='Test'>
<rows>
<add Field1='abcdefghijklmnopqrstuvwxyz' Field2='abcdefghijklmnopqrstuvwxyz' Field3='abcdefghijklmnopqrstuvwxyz' />
<add Field1='0123456789' Field2='0123456789' Field3='0123456789' />
</rows>
<fields>
<add name='Field1' v='minLength(10)' />
<add name='Field2' v='minLength(20)' />
<add name='Field3' v='minLength(30)' />
</fields>
</add>
</entities>
</add>";
var logger = new ConsoleLogger(LogLevel.Debug);
using (var cfgScope = new ConfigurationContainer().CreateScope(xml, logger)) {
var process = cfgScope.Resolve <Process>();
using (var scope = new Container().CreateScope(process, logger)) {
var output = scope.Resolve <IProcessController>().Read().ToArray();
Assert.AreEqual(true, output[0][process.GetField("Field1Valid")], "valid because input is longer than 10 characters");
Assert.AreEqual(true, output[0][process.GetField("Field2Valid")], "valid because input is longer than 20 characters");
Assert.AreEqual(false, output[0][process.GetField("Field3Valid")], "invalid because input is shorter than 30 characters");
Assert.AreEqual(true, output[1][process.GetField("Field1Valid")], "valid because input is exactly 10 characters");
Assert.AreEqual(false, output[1][process.GetField("Field2Valid")], "invalid because input is shorter than 20 characters");
Assert.AreEqual(false, output[1][process.GetField("Field3Valid")], "invalid because input is shorter than 30 characters");
}
}
}
public void ReadSchema()
{
const string xml = @"<add name='Excel'>
<connections>
<add name='input' provider='excel' file='c:\temp\bogus.xlsx'>
<types>
<add type='byte' />
<add type='int' />
<add type='string' />
</types>
</add>
<add name='output' provider='internal' />
</connections>
<entities>
<add name='BogusStar' alias='Contact' />
</entities>
</add>";
var logger = new ConsoleLogger(LogLevel.Debug);
using (var outer = new ConfigurationContainer().CreateScope(xml, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new ExcelModule()).CreateScope(process, logger)) {
var schemaReader = inner.ResolveNamed <ISchemaReader>(process.Connections.First().Key);
var schema = schemaReader.Read();
var entity = schema.Entities.First();
Assert.AreEqual(5, entity.Fields.Count);
Assert.AreEqual("int", entity.Fields[0].Type);
Assert.AreEqual("string", entity.Fields[1].Type);
Assert.AreEqual("string", entity.Fields[2].Type);
Assert.AreEqual("byte", entity.Fields[3].Type);
Assert.AreEqual("int", entity.Fields[4].Type);
}
}
}
public void IgnoringAsterisk()
{
var logger = new ConsoleLogger();
using (var outer = new ConfigurationContainer().CreateScope(TestCfg1, logger)) {
// get and test process
var process = outer.Resolve <Process>();
foreach (var error in process.Errors())
{
Console.WriteLine(error);
}
Assert.AreEqual(0, process.Errors().Length);
using (var inner = new Container(new AdoProviderModule()).CreateScope(process, logger)) {
var context = inner.ResolveNamed <InputContext>("TestFact");
var filter = context.Process.Entities[0].Filter[0];
var actual = context.SqlSelectFacetFromInput(filter, new NullConnectionFactory()
{
AdoProvider = AdoProvider.SqlServer, SupportsLimit = false
});
Assert.AreEqual("SELECT f2 + ' (' + CAST(COUNT(*) AS NVARCHAR(32)) + ')' AS From, f2 AS To FROM Fact WHERE (1=2) GROUP BY f2 ORDER BY f2 ASC", actual);
}
}
}
public void Run()
{
var xml = @"
<add name='TestProcess'>
<entities>
<add name='TestData'>
<rows>
<add Field1='9' Field2='10' Field3='11' />
<add Field1='9' Field2='9' Field3='9' />
</rows>
<fields>
<add name='Field1' />
<add name='Field2' />
<add name='Field3' />
</fields>
<calculated-fields>
<add name='All9' type='bool' t='copy(Field1,Field2,Field3)' v='all(9)' valid-field='All9' />
</calculated-fields>
</add>
</entities>
</add>";
var logger = new ConsoleLogger(LogLevel.Debug);
using (var cfgScope = new ConfigurationContainer().CreateScope(xml, logger)) {
var process = cfgScope.Resolve <Process>();
using (var scope = new Container().CreateScope(process, logger)) {
var output = scope.Resolve <IProcessController>().Read().ToArray();
Assert.AreEqual(false, output[0][process.GetField("All9")]);
Assert.AreEqual(true, output[1][process.GetField("All9")]);
}
}
}
public void ReadPage2()
{
const string xml = @"<add name='TestProcess'>
<connections>
<add name='input' provider='elasticsearch' server='localhost' index='bogus' port='9200' version='7.9.3' scroll='30s' />
</connections>
<entities>
<add name='contact' page='2' size='5'>
<order>
<add field='identity' />
</order>
<fields>
<add name='identity' type='int' />
<add name='firstname' />
<add name='lastname' />
<add name='stars' type='byte' />
<add name='reviewers' type='int' />
<add name='names' />
</fields>
</add>
</entities>
</add>";
var logger = new ConsoleLogger(LogLevel.Debug);
using (var outer = new ConfigurationContainer().CreateScope(xml, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new ElasticsearchModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
var rows = process.Entities.First().Rows;
Assert.AreEqual(5, rows.Count);
Assert.AreEqual("Mitchell", rows[0]["firstname"]);
}
}
}
public Shell()
{
InitializeComponent();
container = new ConfigurationContainer();
container.RegisterInstance <IUIServiceWpf>(this);
container.RegisterInstance <IWindowsFormsEditorService>(this);
container.RegisterInstance <IUIService>(this);
container.RegisterInstance <IAssemblyDiscoveryService>(new LoadedAssembliesDiscoveryService());
container.DiscoverSubstituteTypesFromAssemblies();
applicationModel = container.Resolve <IApplicationModel>();
DataContext = applicationModel;
applicationModel.New();
applicationModel.OnCloseAction = () => RequestClose();
InputBindings.Add(new InputBinding(applicationModel.NewConfigurationCommand, new KeyGesture(Key.N, ModifierKeys.Control)));
InputBindings.Add(new InputBinding(applicationModel.SaveConfigurationCommand, new KeyGesture(Key.S, ModifierKeys.Control)));
InputBindings.Add(new InputBinding(applicationModel.SaveAsConfigurationCommand, new KeyGesture(Key.A, ModifierKeys.Control)));
InputBindings.Add(new InputBinding(applicationModel.OpenConfigurationCommand, new KeyGesture(Key.O, ModifierKeys.Control)));
}
[Ignore] // not tested yet
public void Lucene_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().CreateScope(TestFile + "?Mode=init", logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new LuceneModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
using (var reader = IndexReader.Open(FSDirectory.Open(new DirectoryInfo(Path.Combine(OutputConnection.Folder, "NorthWindStar"))), true)) {
Assert.AreEqual(2155, reader.NumDocs());
}
// FIRST DELTA, NO CHANGES
using (var outer = new ConfigurationContainer().CreateScope(TestFile, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container().CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
using (var reader = IndexReader.Open(FSDirectory.Open(new DirectoryInfo(Path.Combine(OutputConnection.Folder, "NorthWindStar"))), 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));
}
using (var outer = new ConfigurationContainer().CreateScope(TestFile, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container().CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
using (var searcher = new IndexSearcher(FSDirectory.Open(new DirectoryInfo(Path.Combine(OutputConnection.Folder, "NorthWindStar"))), true)) {
var booleanQuery = new BooleanQuery
{
{ new TermQuery(new Term("OrderDetailsOrderID", NumericUtils.IntToPrefixCoded(10253))), Occur.MUST },
{ new TermQuery(new Term("OrderDetailsProductID", NumericUtils.IntToPrefixCoded(39))), Occur.MUST }
};
var hits = searcher.Search(booleanQuery, null, 1);
Assert.AreEqual(1, hits.TotalHits);
var hit = searcher.Doc(hits.ScoreDocs[0].Doc);
Assert.AreEqual(15.0M, Convert.ToDecimal(hit.Get("OrderDetailsUnitPrice")));
Assert.AreEqual(40, Convert.ToInt32(hit.Get("OrderDetailsQuantity")));
Assert.AreEqual(40 * 15.0M, 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;"));
}
using (var outer = new ConfigurationContainer().CreateScope(TestFile, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container().CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
using (var searcher = new IndexSearcher(FSDirectory.Open(new DirectoryInfo(Path.Combine(OutputConnection.Folder, "NorthWindStar"))), true)) {
var hits = searcher.Search(new TermQuery(new Term("OrderDetailsOrderID", NumericUtils.IntToPrefixCoded(10254))), 1);
Assert.AreNotEqual(0, hits.TotalHits);
var hit = searcher.Doc(hits.ScoreDocs[0].Doc);
Assert.AreEqual("VICTE", hit.Get("OrdersCustomerID"));
Assert.AreEqual(20.11M, Convert.ToDecimal(hit.Get("OrdersFreight")));
}
}
static void Run(RunOptions options)
{
var logger = new ConsoleLogger(options.LogLevel);
var operations = new List <Autofac.Core.IModule> {
new JintTransformModule(),
new RazorTransformModule(),
new FluidTransformModule(),
new HumanizeModule()
};
// todo: add lambda parser, etc
using (var outer = new ConfigurationContainer(operations.ToArray()).CreateScope(options.ArrangementWithMode(), logger, options.GetParameters())) {
var process = outer.Resolve <Process>();
if (process.Errors().Any())
{
Environment.Exit(1);
}
var providers = new List <Autofac.Core.IModule> {
new ConsoleProviderModule()
};
var output = process.GetOutputConnection();
if (output == null || output.Provider == "internal" || output.Provider == "console")
{
logger.SuppressConsole();
if (options.Format == "csv")
{
output.Provider = "file"; // delimited file
output.Delimiter = ",";
output.Stream = true;
output.Synchronous = true; // got odd results when using Async methods
output.File = "dummy.csv";
providers.Add(new CsvHelperProviderModule(Console.OpenStandardOutput()));
}
else
{
output.Provider = "json";
output.Stream = true;
output.Format = "json";
output.File = "dummy.json";
providers.Add(new JsonProviderModule(Console.OpenStandardOutput()));
}
}
else
{
providers.Add(new CsvHelperProviderModule());
providers.Add(new JsonProviderModule());
}
// PROVIDERS
providers.Add(new AdoProviderModule());
providers.Add(new BogusModule());
providers.Add(new SqliteModule());
providers.Add(new SqlServerModule());
providers.Add(new PostgreSqlModule());
providers.Add(new MySqlModule());
providers.Add(new ElasticsearchModule());
providers.Add(new RazorProviderModule());
// solr
var modules = providers.Union(operations).ToArray();
using (var inner = new Container(modules).CreateScope(process, logger)) {
inner.Resolve <IProcessController>().Execute();
}
}
}
//[Ignore]
public void SqlServer_Integration()
{
// If you need the Northwind database, it's here: https://github.com/microsoft/sql-server-samples/raw/master/samples/databases/northwind-pubs/instnwnd.sql
/* You'll also need to:
* ALTER TABLE [Order Details] ADD [RowVersion] ROWVERSION;
* ALTER TABLE [Orders] ADD [RowVersion] ROWVERSION;
* ALTER TABLE [Customers] ADD [RowVersion] ROWVERSION;
* ALTER TABLE [Employees] ADD [RowVersion] ROWVERSION;
* ALTER TABLE [Products] ADD [RowVersion] ROWVERSION;
* ALTER TABLE [Suppliers] ADD [RowVersion] ROWVERSION;
* ALTER TABLE [Categories] ADD [RowVersion] ROWVERSION;
* ALTER TABLE [Shippers] ADD [RowVersion] ROWVERSION;
* CREATE DATABASE TflNorthwind; */
var logger = new ConsoleLogger();
// CORRECT DATA AND INITIAL LOAD
using (var cn = new SqlServerConnectionFactory(InputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(5, 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';
UPDATE Suppliers SET Region = '' WHERE SupplierID = 10;
UPDATE [Order Details] SET Quantity = 5 WHERE OrderId = 10568 AND ProductID = 10;
"));
}
// RUN INIT AND TEST
using (var outer = new ConfigurationContainer(new CSharpModule()).CreateScope(TestFile + "&Mode=init", logger: logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new AdoProviderModule(), new SqlServerModule(), new CSharpModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
using (var cn = new SqlServerConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(2155, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM NorthWindStar;"));
Assert.AreEqual(2155, cn.ExecuteScalar <int>("SELECT TOP 1 Inserts FROM NorthWindControl WHERE Entity = 'Order Details' AND BatchId = 1;"));
Assert.AreEqual(5, cn.ExecuteScalar <int>("SELECT TOP 1 TflBatchId FROM NorthWindStar;"), 0.0, "Should be 5, for Products (last one with fk)");
Assert.AreEqual(2155, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM NorthWindFlat;"));
}
// FIRST DELTA, NO CHANGES
using (var outer = new ConfigurationContainer(new CSharpModule()).CreateScope(TestFile, logger: logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new AdoProviderModule(), new SqlServerModule(), new CSharpModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
using (var cn = new SqlServerConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(2155, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM NorthWindStar;"));
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
using (var outer = new ConfigurationContainer(new CSharpModule()).CreateScope(TestFile, logger: logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new AdoProviderModule(), new SqlServerModule(), new CSharpModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
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 = 17;"));
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;"));
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;"));
}
using (var outer = new ConfigurationContainer(new CSharpModule()).CreateScope(TestFile, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new AdoProviderModule(), new SqlServerModule(), new CSharpModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
using (var cn = new SqlServerConnectionFactory(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;"));
Assert.AreEqual(26, cn.ExecuteScalar <int>("SELECT TflBatchId FROM NorthWindStar WHERE OrderDetailsOrderId= 10254;"));
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';"));
}
using (var outer = new ConfigurationContainer(new CSharpModule()).CreateScope(TestFile, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new AdoProviderModule(), new SqlServerModule(), new CSharpModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
using (var cn = new SqlServerConnectionFactory(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 NorthWindStar WHERE OrdersCustomerID = 'VAFFE';"));
Assert.AreEqual(35, cn.ExecuteScalar <int>("SELECT DISTINCT TflBatchId FROM NorthWindStar WHERE OrdersCustomerID = 'VAFFE';"), "The TflBatchId should be updated on the master to indicate a change has occured.");
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.");
}
// CHANGE A SUPPLIER REGION WHICH SHOULD AFFECT 51 RECORDS AND ALSO 1 ORDER DETAIL RECORD
using (var cn = new SqlServerConnectionFactory(InputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(1, cn.Execute("UPDATE Suppliers SET Region = 'BSH' WHERE SupplierID = 10;"));
Assert.AreEqual(1, cn.Execute("UPDATE [Order Details] SET Quantity = 6 WHERE OrderId = 10568 AND ProductID = 10;"));
}
using (var outer = new ConfigurationContainer(new CSharpModule()).CreateScope(TestFile, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new AdoProviderModule(), new SqlServerModule(), new CSharpModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
using (var cn = new SqlServerConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(1, cn.ExecuteScalar <int>("SELECT Updates FROM NorthWindControl WHERE Entity = 'Order Details' AND BatchId = 41;"));
Assert.AreEqual(1, cn.ExecuteScalar <int>("SELECT Updates FROM NorthWindControl WHERE Entity = 'Suppliers' AND BatchId = 46;"));
Assert.AreEqual(1, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM NorthWindStar WHERE TflBatchId = 45;"));
Assert.AreEqual(51, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM NorthWindStar WHERE TflBatchId = 46;"));
Assert.AreEqual(1, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM NorthWindFlat WHERE TflBatchId = 45;"));
Assert.AreEqual(51, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM NorthWindFlat WHERE TflBatchId = 46;"));
}
}
public void Test()
{
var cfg = $@"<cfg name='TestTransformThenValidate' mode='@[Mode]'>
<parameters>
<add name='Mode' value='default' />
</parameters>
<connections>
<add name='input' provider='sqlserver' server='localhost' database='Junk' user='******' password='******' />
</connections>
<entities>
<add name='TestData' alias='Data'>
<fields>
<add name='Id' type='int' primary-key='true' />
<add name='Field1' t='replace(\,,)' v='is(int)' valid-field='Valid' message-field='Message' />
</fields>
<calculated-fields>
<add name='Valid' type='bool' default='true' />
<add name='Message' length='255' />
</calculated-fields>
</add>
</entities>
</cfg>
";
var logger = new DebugLogger(LogLevel.Debug);
using (var outer = new ConfigurationContainer().CreateScope(cfg, logger)) {
var process = outer.Resolve <Process>();
if (process.Errors().Any())
{
foreach (var error in process.Errors())
{
System.Diagnostics.Debug.Write(error);
}
}
else
{
using (var inner = new Container(new AdoProviderModule(), new SqlServerModule(process)).CreateScope(process, logger)) {
var connectionFactory = inner.ResolveNamed <IConnectionFactory>("TestTransformThenValidateinput");
using (var cn = connectionFactory.GetConnection("Test")) {
// set up test data
try {
cn.Execute("DROP TABLE TestData;");
} catch (System.Exception) {
}
cn.Execute("CREATE TABLE TestData(Id INT NOT NULL PRIMARY KEY,Field1 NVARCHAR(60));");
cn.Execute("INSERT INTO TestData(Id,Field1) VALUES(@Id,@Field1)", new { Id = 1, Field1 = "1,001" });
cn.Execute("INSERT INTO TestData(Id,Field1) VALUES(@Id,@Field1)", new { Id = 2, Field1 = "2.002" });
}
var controller = inner.Resolve <IProcessController>();
var rows = controller.Read().ToArray();
Assert.AreEqual(rows.Length, 2);
Assert.IsTrue((bool)rows[0][process.Entities.First().GetField("Valid")]);
Assert.IsFalse((bool)rows[1][process.Entities.First().GetField("Valid")]);
}
}
}
}
public void SlaveGetsInserts_Integration()
{
// SETUP
using (var cn = new SqlServerConnectionFactory(InputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(3, cn.Execute(@"
IF OBJECT_ID('MasterTable') IS NULL
BEGIN
create table MasterTable(
Id int not null primary key,
d1 nvarchar(64) not null,
d2 nvarchar(64) not null
);
END
IF OBJECT_ID('SlaveTable') IS NULL
BEGIN
create table SlaveTable(
Id int not null primary key,
d3 nvarchar(64) not null,
d4 nvarchar(64) not null
);
END
TRUNCATE TABLE MasterTable;
TRUNCATE TABLE SlaveTable;
INSERT INTO MasterTable(Id,d1,d2)VALUES(1,'d1','d2');
INSERT INTO MasterTable(Id,d1,d2)VALUES(2,'d3','d4');
INSERT INTO SlaveTable(Id,d3,d4)VALUES(1,'d5','d6');
"));
}
var logger = new ConsoleLogger(LogLevel.Debug);
// RUN INIT AND TEST
using (var outer = new ConfigurationContainer().CreateScope($@"Files\SlaveGetsInsert.xml?Mode=init&User={Tester.User}&Pw={Tester.Pw}", logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new SqlServerModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
Assert.AreEqual((uint)2, process.Entities.First().Inserts);
Assert.AreEqual((uint)1, process.Entities.Last().Inserts);
}
}
using (var cn = new SqlServerConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(2, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM TestMasterTable;"));
Assert.AreEqual(1, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM TestSlaveTable;"));
}
// FIRST DELTA, NO CHANGES
using (var outer = new ConfigurationContainer().CreateScope($@"Files\SlaveGetsInsert.xml?User={Tester.User}&Pw={Tester.Pw}", logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new SqlServerModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
Assert.AreEqual((uint)0, process.Entities.First().Inserts);
Assert.AreEqual((uint)0, process.Entities.Last().Inserts);
}
}
using (var cn = new SqlServerConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(2, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM TestMasterTable;"));
Assert.AreEqual(1, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM TestSlaveTable;"));
Assert.AreEqual(2, cn.ExecuteScalar <int>("select Id from TestStar where d3 = '' and d4 = '';"));
}
// insert into slave
using (var cn = new SqlServerConnectionFactory(InputConnection).GetConnection()) {
cn.Open();
const string sql = @"INSERT INTO SlaveTable(Id,d3,d4)VALUES(2,'d7','d8');";
Assert.AreEqual(1, cn.Execute(sql));
}
// RUN AND CHECK
using (var outer = new ConfigurationContainer().CreateScope($@"Files\SlaveGetsInsert.xml?User={Tester.User}&Pw={Tester.Pw}", logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new SqlServerModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
Assert.AreEqual((uint)0, process.Entities.First().Inserts);
Assert.AreEqual((uint)1, process.Entities.Last().Inserts);
}
}
using (var cn = new SqlServerConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(2, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM TestMasterTable;"));
Assert.AreEqual(2, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM TestSlaveTable;"));
Assert.AreEqual(0, cn.ExecuteScalar <int>("select Id from TestStar where d3 = '' and d4 = '';"));
Assert.AreEqual(2, cn.ExecuteScalar <int>("select Id from TestStar where d3 = 'd7' and d4 = 'd8';"));
}
}
[Ignore] // this is not working yet
public void SqlServer_Elasticsearch_Integration()
{
var logger = new ConsoleLogger(LogLevel.Debug);
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';
"));
}
using (var outer = new ConfigurationContainer().CreateScope(SqlTestFile + "?Mode=init", logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container().CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
using (var outer = new ConfigurationContainer().CreateScope(ElasticTestFile + "?Mode=init", logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container().CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
Assert.AreEqual(2155, client.Count <DynamicResponse>("northwind", "star", "{\"query\" : { \"match_all\" : { }}}").Body["count"].Value);
// FIRST DELTA, NO CHANGES
using (var outer = new ConfigurationContainer().CreateScope(ElasticTestFile, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container().CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
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
using (var outer = new ConfigurationContainer().CreateScope(SqlTestFile, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container().CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
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
using (var outer = new ConfigurationContainer().CreateScope(ElasticTestFile, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container().CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
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
using (var outer = new ConfigurationContainer().CreateScope(SqlTestFile, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container().CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
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
using (var outer = new ConfigurationContainer().CreateScope(ElasticTestFile, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container().CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
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);
}
/// <summary>
/// Gets the service object of the specified type.
/// </summary>
/// <param name="serviceType">An object that specifies the type of service object to get.</param>
/// <returns>A service object of type <paramref name="serviceType"/>.</returns>
public object GetService(Type serviceType)
{
return(container.Resolve(serviceType));
}
//[Ignore]
public void SqlCe_Integration()
{
var logger = new DebugLogger(LogLevel.Debug);
// 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
using (var outer = new ConfigurationContainer(new JintTransformModule()).CreateScope(TestFile + "?Mode=init", logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new JintTransformModule(), new SqlServerModule(), new SqlCeModule()).CreateScope(process, new DebugLogger(LogLevel.Debug))) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
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
using (var outer = new ConfigurationContainer(new JintTransformModule()).CreateScope(TestFile, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new JintTransformModule(), new SqlServerModule(), new SqlCeModule()).CreateScope(process, new DebugLogger(LogLevel.Debug))) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
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
using (var outer = new ConfigurationContainer(new JintTransformModule()).CreateScope(TestFile, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new JintTransformModule(), new SqlServerModule(), new SqlCeModule()).CreateScope(process, new DebugLogger(LogLevel.Debug))) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
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;"));
}
using (var outer = new ConfigurationContainer(new JintTransformModule()).CreateScope(TestFile, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new JintTransformModule(), new SqlServerModule(), new SqlCeModule()).CreateScope(process, new DebugLogger(LogLevel.Debug))) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
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';"));
}
using (var outer = new ConfigurationContainer(new JintTransformModule()).CreateScope(TestFile, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new JintTransformModule(), new SqlServerModule(), new SqlCeModule()).CreateScope(process, new DebugLogger(LogLevel.Debug))) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
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.");
}
}
protected override void Act()
{
saveOperation = container.Resolve <SaveOperation>();
}
static void Run(RunOptions options)
{
var logger = new ConsoleLogger(options.LogLevel);
var operations = new List <Autofac.Core.IModule> {
new JintTransformModule(),
new RazorTransformModule(),
new FluidTransformModule(),
new HumanizeModule(),
new LambdaParserModule()
};
// todo: geoCode, etc
using (var outer = new ConfigurationContainer(operations.ToArray()).CreateScope(options.ArrangementWithMode(), logger, options.GetParameters())) {
var process = outer.Resolve <Process>();
if (options.Mode != "default" && options.Mode != process.Mode)
{
process.Mode = options.Mode;
process.Load();
}
if (process.Errors().Any())
{
var context = new PipelineContext(logger, process);
context.Error("The configuration has errors.");
foreach (var error in process.Errors())
{
context.Error(error);
}
Environment.Exit(1);
}
var providers = new List <Autofac.Core.IModule> {
new ConsoleProviderModule()
};
var output = process.GetOutputConnection();
if (output == null || output.Provider == "internal" || output.Provider == "console")
{
logger.SuppressConsole();
if (options.Format == "csv")
{
output.Provider = "file"; // delimited file
output.Delimiter = ",";
output.Stream = true;
output.Synchronous = true; // got odd results when using Async methods
output.File = "dummy.csv";
providers.Add(new CsvHelperProviderModule(Console.OpenStandardOutput()));
}
else
{
output.Provider = "json";
output.Stream = true;
output.Format = "json";
output.File = "dummy.json";
providers.Add(new JsonProviderModule(Console.OpenStandardOutput()));
}
}
else
{
providers.Add(new CsvHelperProviderModule());
providers.Add(new JsonProviderModule());
}
// PROVIDERS
providers.Add(new AdoProviderModule());
providers.Add(new BogusModule());
providers.Add(new SqliteModule());
providers.Add(new SqlServerModule());
providers.Add(new PostgreSqlModule());
providers.Add(new MySqlModule());
providers.Add(new ElasticsearchModule());
providers.Add(new RazorProviderModule());
providers.Add(new AwsCloudWatchProviderModule());
providers.Add(new AmazonConnectProviderModule());
providers.Add(new MailModule());
// solr
var modules = providers.Union(operations).ToArray();
if (options.Mode.ToLower() == "schema")
{
using (var inner = new Container(modules).CreateScope(process, logger)) {
process = new SchemaService(inner).GetProcess(process);
process.Connections.Clear();
Console.WriteLine(process.Serialize());
Environment.Exit(0);
}
}
else if (process.Entities.Count == 1 && !process.Entities[0].Fields.Any(f => f.Input))
{
using (var inner = new Container(modules).CreateScope(process, logger)) {
if (new SchemaService(inner).Help(process))
{
process.Load();
}
else
{
Console.Error.WriteLine($"Unable to detect fields in {process.Entities[0].Name}.");
Environment.Exit(1);
}
}
}
using (var inner = new Container(modules).CreateScope(process, logger)) {
inner.Resolve <IProcessController>().Execute();
}
}
}
public void CheckUpdateMasterQuery()
{
const string xml = @"
<cfg name='Test'>
<entities>
<add name='Fact'>
<fields>
<add name='f1' type='int' primary-key='true' />
<add name='f2' />
<add name='d1' type='int' />
</fields>
</add>
<add name='Dim'>
<fields>
<add name='d1' type='int' primary-key='true' />
<add name='d2' />
<add name='sd1' type='int' />
</fields>
</add>
<add name='SubDim'>
<fields>
<add name='sd1' type='int' primary-key='true' />
<add name='sd2' />
</fields>
</add>
</entities>
<relationships>
<add left-entity='Fact' left-field='d1' right-entity='Dim' right-field='d1' />
<add left-entity='Dim' left-field='sd1' right-entity='SubDim' right-field='sd1' />
</relationships>
</cfg>
";
var logger = new ConsoleLogger();
using (var outer = new ConfigurationContainer().CreateScope(xml, logger)) {
// get and test process
var process = outer.Resolve <Process>();
foreach (var error in process.Errors())
{
Console.WriteLine(error);
}
Assert.AreEqual(0, process.Errors().Length);
// setup test
var fact = process.Entities[0];
var dimension = process.Entities[1];
fact.BatchId = 1;
dimension.BatchId = 2;
var context = new PipelineContext(logger, process, dimension);
var writer = new PostgreSqlUpdateMasterKeysQueryWriter(context, new NullConnectionFactory());
var entityStatus = new Transformalize.EntityStatus(context)
{
Modified = true,
MasterUpserted = true,
HasForeignKeys = true
};
var expected = @"
UPDATE TestFactTable A
SET B7 = B.B7, A2 = @TflBatchId
FROM TestDimTable B
WHERE (A.A7 = B.B5)
AND (B.B2 = @TflBatchId OR A.A2 >= @MasterTflBatchId)
";
var actual = writer.Write(entityStatus);
Assert.AreEqual(expected, actual);
}
}
//[Ignore("Needs local sql server and mysql databases and you have to set the password (above)")]
public void 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().CreateScope(TestFile + $"?Mode=init&Password={Password}", logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new MySqlModule(), new SqlServerModule(), new JintTransformModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
using (var cn = new MySqlConnectionFactory(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().CreateScope(TestFile + $"?Password={Password}", logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new MySqlModule(), new SqlServerModule(), new JintTransformModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
using (var cn = new MySqlConnectionFactory(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().CreateScope(TestFile + $"?Password={Password}", logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new MySqlModule(), new SqlServerModule(), new JintTransformModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
using (var cn = new MySqlConnectionFactory(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().CreateScope(TestFile + $"?Password={Password}", logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new MySqlModule(), new SqlServerModule(), new JintTransformModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
using (var cn = new MySqlConnectionFactory(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;"));
}
}
public void Delete_Integration()
{
var logger = new ConsoleLogger(LogLevel.Debug);
var cfg = $@"files\DeleteIntegration.xml?User={Tester.User}&Pw={Tester.Pw}";
// 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);
"));
}
// RUN INIT AND TEST
using (var outer = new ConfigurationContainer().CreateScope(cfg + "&Mode=init", logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new SqlServerModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
Assert.AreEqual((uint)3, process.Entities.First().Inserts);
Assert.AreEqual((uint)0, process.Entities.First().Updates);
Assert.AreEqual((uint)0, process.Entities.First().Deletes);
}
}
using (var cn = new SqlServerConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(3, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM TestDeletesStar;"));
}
// FIRST DELTA, NO CHANGES
using (var outer = new ConfigurationContainer().CreateScope(cfg, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new SqlServerModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
Assert.AreEqual((uint)0, process.Entities.First().Inserts);
Assert.AreEqual((uint)0, process.Entities.First().Updates);
Assert.AreEqual((uint)0, process.Entities.First().Deletes);
}
}
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
using (var outer = new ConfigurationContainer().CreateScope(cfg, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new SqlServerModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
Assert.AreEqual((uint)0, process.Entities.First().Inserts);
Assert.AreEqual((uint)0, process.Entities.First().Updates);
Assert.AreEqual((uint)1, process.Entities.First().Deletes);
}
}
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
using (var outer = new ConfigurationContainer().CreateScope(cfg, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new SqlServerModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
Assert.AreEqual((uint)0, process.Entities.First().Inserts);
Assert.AreEqual((uint)0, process.Entities.First().Updates);
Assert.AreEqual((uint)0, process.Entities.First().Deletes);
}
}
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
using (var outer = new ConfigurationContainer().CreateScope(cfg, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new SqlServerModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
Assert.AreEqual((uint)0, process.Entities.First().Inserts);
Assert.AreEqual((uint)1, process.Entities.First().Updates);
Assert.AreEqual((uint)0, process.Entities.First().Deletes);
}
}
using (var cn = new SqlServerConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(3, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM TestDeletesStar WHERE TflDeleted = 0;"));
}
}
