public RelationalDatabaseWriter(SqlEtl etl, DocumentDatabase database)
: base(etl.Configuration.FactoryName)
{
_etl = etl;
_database = database;
_logger = LoggingSource.Instance.GetLogger <RelationalDatabaseWriter>(_database.Name);
_providerFactory = GetDbProviderFactory(etl.Configuration);
_commandBuilder = _providerFactory.InitializeCommandBuilder();
_connection = _providerFactory.CreateConnection();
_connection.ConnectionString = etl.Configuration.Connection.ConnectionString;
try
{
_connection.Open();
}
catch (Exception e)
{
database.NotificationCenter.Add(AlertRaised.Create(
database.Name,
SqlEtl.SqlEtlTag,
$"SQL ETL could not open connection to {_connection.ConnectionString}",
AlertType.SqlEtl_ConnectionError,
NotificationSeverity.Error,
key: _connection.ConnectionString,
details: new ExceptionDetails(e)));
throw;
}
_tx = _connection.BeginTransaction();
_stringParserList = GenerateStringParsers();
}
public async Task PostScriptTest()
{
using (Database.DocumentsStorage.ContextPool.AllocateOperationContext(out DocumentsOperationContext context))
{
var dbDoc = await context.ReadForMemoryAsync(RequestBodyStream(), "TestSqlEtlScript");
var testScript = JsonDeserializationServer.TestSqlEtlScript(dbDoc);
var result = (SqlEtlTestScriptResult)SqlEtl.TestScript(testScript, Database, ServerStore, context);
await using (var writer = new AsyncBlittableJsonTextWriter(context, ResponseBodyStream()))
{
var djv = (DynamicJsonValue)TypeConverter.ToBlittableSupportedType(result);
writer.WriteObject(context.ReadObject(djv, "et/sql/test"));
}
}
}
public Task PostSimulateSqlReplication()
{
using (Database.DocumentsStorage.ContextPool.AllocateOperationContext(out DocumentsOperationContext context))
{
context.OpenReadTransaction();
var dbDoc = context.ReadForMemory(RequestBodyStream(), "SimulateSqlReplicationResult");
var simulateSqlReplication = JsonDeserializationServer.SimulateSqlReplication(dbDoc);
var result = SqlEtl.SimulateSqlEtl(simulateSqlReplication, Database, ServerStore, context);
using (var writer = new BlittableJsonTextWriter(context, ResponseBodyStream()))
{
var djv = (DynamicJsonValue)TypeConverter.ToBlittableSupportedType(result);
writer.WriteObject(context.ReadObject(djv, "et/sql/simulate"));
}
}
return(Task.CompletedTask);
}
public RelationalDatabaseWriter(SqlEtl etl, DocumentDatabase database)
: base(etl.Configuration.Connection.FactoryName)
{
_etl = etl;
_database = database;
_logger = LoggingSource.Instance.GetLogger <RelationalDatabaseWriter>(_database.Name);
_providerFactory = GetDbProviderFactory(etl.Configuration);
_commandBuilder = _providerFactory.InitializeCommandBuilder();
_connection = _providerFactory.CreateConnection();
var connectionString = etl.Configuration.Connection.ConnectionString;
_connection.ConnectionString = connectionString;
OpenConnection(database, etl.Configuration.Name, etl.Configuration.ConnectionStringName);
_tx = _connection.BeginTransaction();
_stringParserList = GenerateStringParsers();
}
public async Task CanTestDeletion(bool performRolledBackTransaction)
{
using (var store = GetDocumentStore())
{
CreateRdbmsSchema(store);
using (var session = store.OpenAsyncSession())
{
await session.StoreAsync(new Order
{
OrderLines = new List <OrderLine>
{
new OrderLine {
Cost = 3, Product = "Milk", Quantity = 3
},
new OrderLine {
Cost = 4, Product = "Bear", Quantity = 2
},
}
});
await session.SaveChangesAsync();
}
var result1 = store.Maintenance.Send(new PutConnectionStringOperation <SqlConnectionString>(new SqlConnectionString()
{
Name = "simulate",
ConnectionString = GetConnectionString(store),
FactoryName = "System.Data.SqlClient",
}));
Assert.NotNull(result1.RaftCommandIndex);
var database = GetDatabase(store.Database).Result;
using (database.DocumentsStorage.ContextPool.AllocateOperationContext(out DocumentsOperationContext context))
{
var result = (SqlEtlTestScriptResult)SqlEtl.TestScript(new TestSqlEtlScript
{
PerformRolledBackTransaction = performRolledBackTransaction,
DocumentId = "orders/1-A",
IsDelete = true,
Configuration = new SqlEtlConfiguration()
{
Name = "simulate",
ConnectionStringName = "simulate",
SqlTables =
{
new SqlEtlTable {
TableName = "Orders", DocumentIdColumn = "Id"
},
new SqlEtlTable {
TableName = "OrderLines", DocumentIdColumn = "OrderId"
},
new SqlEtlTable {
TableName = "NotUsedInScript", DocumentIdColumn = "OrderId"
},
},
Transforms =
{
new Transformation()
{
Collections ={ "Orders" },
Name = "OrdersAndLines",
Script = defaultScript
}
}
}
}, database, database.ServerStore, context);
Assert.Equal(0, result.TransformationErrors.Count);
Assert.Equal(0, result.LoadErrors.Count);
Assert.Equal(0, result.SlowSqlWarnings.Count);
Assert.Equal(2, result.Summary.Count);
var orderLines = result.Summary.First(x => x.TableName == "OrderLines");
Assert.Equal(1, orderLines.Commands.Length); // delete
var orders = result.Summary.First(x => x.TableName == "Orders");
Assert.Equal(1, orders.Commands.Length); // delete
}
using (var session = store.OpenAsyncSession())
{
Assert.NotNull(session.Query <Order>("orders/1-A"));
}
}
}
private IEnumerable <EtlProcess> GetRelevantProcesses <T, TConnectionString>(List <T> configurations, HashSet <string> uniqueNames) where T : EtlConfiguration <TConnectionString> where TConnectionString : ConnectionString
{
foreach (var config in configurations)
{
SqlEtlConfiguration sqlConfig = null;
RavenEtlConfiguration ravenConfig = null;
var connectionStringNotFound = false;
switch (config.EtlType)
{
case EtlType.Raven:
ravenConfig = config as RavenEtlConfiguration;
if (_databaseRecord.RavenConnectionStrings.TryGetValue(config.ConnectionStringName, out var ravenConnection))
{
ravenConfig.Initialize(ravenConnection);
}
else
{
connectionStringNotFound = true;
}
break;
case EtlType.Sql:
sqlConfig = config as SqlEtlConfiguration;
if (_databaseRecord.SqlConnectionStrings.TryGetValue(config.ConnectionStringName, out var sqlConnection))
{
sqlConfig.Initialize(sqlConnection);
}
else
{
connectionStringNotFound = true;
}
break;
default:
ThrownUnknownEtlConfiguration(config.GetType());
break;
}
if (connectionStringNotFound)
{
LogConfigurationError(config,
new List <string>
{
$"Connection string named '{config.ConnectionStringName}' was not found for {config.EtlType} ETL"
});
continue;
}
if (config.Disabled)
{
continue;
}
if (ValidateConfiguration(config, uniqueNames) == false)
{
continue;
}
var processState = GetProcessState(config.Transforms, _database, config.Name);
var whoseTaskIsIt = _database.WhoseTaskIsIt(_databaseRecord.Topology, config, processState);
if (whoseTaskIsIt != _serverStore.NodeTag)
{
continue;
}
foreach (var transform in config.Transforms)
{
if (transform.Disabled)
{
continue;
}
EtlProcess process = null;
if (sqlConfig != null)
{
process = new SqlEtl(transform, sqlConfig, _database, _serverStore);
}
if (ravenConfig != null)
{
process = new RavenEtl(transform, ravenConfig, _database, _serverStore);
}
yield return(process);
}
}
}
public async Task SimulationTest()
{
using (var store = GetDocumentStore())
{
CreateRdbmsSchema(store);
using (var session = store.OpenAsyncSession())
{
await session.StoreAsync(new Order
{
OrderLines = new List <OrderLine>
{
new OrderLine {
Cost = 3, Product = "Milk", Quantity = 3
},
new OrderLine {
Cost = 4, Product = "Bear", Quantity = 2
},
}
});
await session.SaveChangesAsync();
}
store.Maintenance.Send(new PutConnectionStringOperation <SqlConnectionString>(new SqlConnectionString()
{
Name = "simulate",
ConnectionString = GetConnectionString(store),
}));
var database = GetDatabase(store.Database).Result;
using (database.DocumentsStorage.ContextPool.AllocateOperationContext(out DocumentsOperationContext context))
using (context.OpenReadTransaction())
{
for (int i = 0; i < 2; i++)
{
var result = SqlEtl.SimulateSqlEtl(new SimulateSqlEtl
{
PerformRolledBackTransaction = i % 2 != 0,
DocumentId = "orders/1-A",
Configuration = new SqlEtlConfiguration()
{
Name = "simulate",
ConnectionStringName = "simulate",
FactoryName = "System.Data.SqlClient",
SqlTables =
{
new SqlEtlTable {
TableName = "Orders", DocumentIdColumn = "Id"
},
new SqlEtlTable {
TableName = "OrderLines", DocumentIdColumn = "OrderId"
},
},
Transforms =
{
new Transformation()
{
Collections ={ "Orders" },
Name = "OrdersAndLines",
Script = defaultScript
}
}
}
}, database, database.ServerStore, context);
Assert.Null(result.LastAlert);
Assert.Equal(2, result.Summary.Count);
var orderLines = result.Summary.First(x => x.TableName == "OrderLines");
Assert.Equal(3, orderLines.Commands.Length); // delete and two inserts
var orders = result.Summary.First(x => x.TableName == "Orders");
Assert.Equal(2, orders.Commands.Length); // delete and insert
}
}
}
}