private bool ReplicateDeletionsToDestination(SqlReplicationConfig cfg, IEnumerable <ListItem> deletedDocs)
{
var identifiers = deletedDocs.Select(x => x.Key).ToList();
if (identifiers.Count == 0)
{
return(true);
}
var replicationStats = statistics.GetOrAdd(cfg.Name, name => new SqlReplicationStatistics(name));
using (var writer = new RelationalDatabaseWriter(Database, cfg, replicationStats))
{
foreach (var sqlReplicationTable in cfg.SqlReplicationTables)
{
writer.DeleteItems(sqlReplicationTable.TableName, sqlReplicationTable.DocumentKeyColumn, cfg.ParameterizeDeletesDisabled, identifiers);
}
writer.Commit();
if (Log.IsDebugEnabled)
{
Log.Debug("Replicated deletes of {0} for config {1}", string.Join(", ", identifiers), cfg.Name);
}
}
return(true);
}
public RelationalDatabaseWriter.TableQuerySummary[] SimulateSqlReplicationSQLQueries(string strDocumentId, SqlReplicationConfig sqlReplication, bool performRolledbackTransaction, out Alert alert)
{
alert = null;
RelationalDatabaseWriter.TableQuerySummary[] resutls = null;
try
{
var stats = new SqlReplicationStatistics(sqlReplication.Name);
var docs = new List <JsonDocument>()
{
Database.Documents.Get(strDocumentId, null)
};
var scriptResult = ApplyConversionScript(sqlReplication, docs);
var connectionsDoc = Database.Documents.Get(connectionsDocumentName, null);
var sqlReplicationConnections = connectionsDoc.DataAsJson.JsonDeserialization <SqlReplicationConnections>();
if (PrepareSqlReplicationConfig(sqlReplication, sqlReplication.Name, stats, sqlReplicationConnections, false, false))
{
if (performRolledbackTransaction)
{
using (var writer = new RelationalDatabaseWriter(Database, sqlReplication, stats))
{
resutls = writer.RolledBackExecute(scriptResult).ToArray();
}
}
else
{
var simulatedwriter = new RelationalDatabaseWriterSimulator(Database, sqlReplication, stats);
resutls = new List <RelationalDatabaseWriter.TableQuerySummary>()
{
new RelationalDatabaseWriter.TableQuerySummary()
{
Commands = simulatedwriter.SimulateExecuteCommandText(scriptResult)
.Select(x => new RelationalDatabaseWriter.TableQuerySummary.CommandData()
{
CommandText = x
}).ToArray()
}
}.ToArray();
}
}
alert = stats.LastAlert;
}
catch (Exception e)
{
alert = new Alert()
{
AlertLevel = AlertLevel.Error,
CreatedAt = SystemTime.UtcNow,
Message = "Last SQL replication operation for " + sqlReplication.Name + " was failed",
Title = "SQL replication error",
Exception = e.ToString(),
UniqueKey = "Sql Replication Error: " + sqlReplication.Name
};
}
return(resutls);
}
private bool ReplicateChangesToDestination(SqlReplicationConfig cfg, ICollection <ReplicatedDoc> docs, out int countOfReplicatedItems)
{
countOfReplicatedItems = 0;
var replicationStats = statistics.GetOrAdd(cfg.Name, name => new SqlReplicationStatistics(name));
var scriptResult = ApplyConversionScript(cfg, docs, replicationStats);
if (scriptResult.Ids.Count == 0)
{
return(true);
}
countOfReplicatedItems = scriptResult.Data.Sum(x => x.Value.Count);
try
{
using (var writer = new RelationalDatabaseWriter(Database, cfg, replicationStats))
{
if (writer.Execute(scriptResult))
{
Log.Debug("Replicated changes of {0} for replication {1}", string.Join(", ", docs.Select(d => d.Key)), cfg.Name);
replicationStats.CompleteSuccess(countOfReplicatedItems);
}
else
{
Log.Debug("Replicated changes (with some errors) of {0} for replication {1}", string.Join(", ", docs.Select(d => d.Key)), cfg.Name);
replicationStats.Success(countOfReplicatedItems);
}
}
return(true);
}
catch (Exception e)
{
Log.WarnException("Failure to replicate changes to relational database for: " + cfg.Name, e);
SqlReplicationStatistics replicationStatistics;
DateTime newTime;
if (statistics.TryGetValue(cfg.Name, out replicationStatistics) == false)
{
newTime = SystemTime.UtcNow.AddSeconds(5);
}
else
{
if (replicationStatistics.LastErrorTime == DateTime.MinValue)
{
newTime = SystemTime.UtcNow.AddSeconds(5);
}
else
{
// double the fallback time (but don't cross 15 minutes)
var totalSeconds = (SystemTime.UtcNow - replicationStatistics.LastErrorTime).TotalSeconds;
newTime = SystemTime.UtcNow.AddSeconds(Math.Min(60 * 15, Math.Max(5, totalSeconds * 2)));
}
}
replicationStats.RecordWriteError(e, Database, countOfReplicatedItems, newTime);
return(false);
}
}
private IEnumerable <string> GenerteInsertItemCommandText(string tableName, string pkName, List <ItemToReplicate> dataForTable)
{
foreach (var itemToReplicate in dataForTable)
{
var sb = new StringBuilder("INSERT INTO ")
.Append(GetTableNameString(tableName))
.Append(" (")
.Append(commandBuilder.QuoteIdentifier(pkName))
.Append(", ");
foreach (var column in itemToReplicate.Columns)
{
if (column.Key == pkName)
{
continue;
}
sb.Append(commandBuilder.QuoteIdentifier(column.Key)).Append(", ");
}
sb.Length = sb.Length - 2;
sb.Append(") VALUES (")
.Append(itemToReplicate.DocumentId)
.Append(", ");
foreach (var column in itemToReplicate.Columns)
{
if (column.Key == pkName)
{
continue;
}
DbParameter param = new OdbcParameter();
RelationalDatabaseWriter.SetParamValue(param, column.Value, null);
sb.Append("'").Append(param.Value).Append("'").Append(", ");
}
sb.Length = sb.Length - 2;
sb.Append(")");
if (IsSqlServerFactoryType && cfg.ForceSqlServerQueryRecompile)
{
sb.Append(" OPTION(RECOMPILE)");
}
sb.Append(";");
yield return(sb.ToString());
}
}
private bool ReplicateChangesToDesintation(SqlReplicationConfig cfg, IEnumerable <JsonDocument> docs)
{
var scriptResult = ApplyConversionScript(cfg, docs);
if (scriptResult.Data.Count == 0)
{
return(true);
}
var replicationStats = statistics.GetOrAdd(cfg.Name, name => new SqlReplicationStatistics(name));
var countOfItems = scriptResult.Data.Sum(x => x.Value.Count);
try
{
using (var writer = new RelationalDatabaseWriter(Database, cfg, replicationStats))
{
if (writer.Execute(scriptResult))
{
replicationStats.CompleteSuccess(countOfItems);
}
else
{
replicationStats.Success(countOfItems);
}
}
return(true);
}
catch (Exception e)
{
log.WarnException("Failure to replicate changes to relational database for: " + cfg.Name, e);
SqlReplicationStatistics replicationStatistics;
DateTime newTime;
if (statistics.TryGetValue(cfg.Name, out replicationStatistics) == false)
{
newTime = SystemTime.UtcNow.AddSeconds(5);
}
else
{
var totalSeconds = (SystemTime.UtcNow - replicationStatistics.LastErrorTime).TotalSeconds;
newTime = SystemTime.UtcNow.AddSeconds(Math.Max(60 * 15, Math.Min(5, totalSeconds + 5)));
}
replicationStats.RecordWriteError(e, Database, countOfItems, newTime);
return(false);
}
}
private bool ReplicateDeletionsToDestination(SqlReplicationConfig cfg, IEnumerable <ListItem> deletedDocs)
{
var identifiers = deletedDocs.Select(x => x.Key).ToList();
if (identifiers.Count == 0)
{
return(true);
}
var replicationStats = statistics.GetOrAdd(cfg.Name, name => new SqlReplicationStatistics(name));
using (var writer = new RelationalDatabaseWriter(Database, cfg, replicationStats))
{
foreach (var sqlReplicationTable in cfg.SqlReplicationTables)
{
writer.DeleteItems(sqlReplicationTable.TableName, sqlReplicationTable.DocumentKeyColumn, identifiers);
}
writer.Commit();
}
return(true);
}
private IEnumerable <string> GenerateDeleteItemsCommandText(string tableName, string pkName, bool doNotParameterize, List <string> identifiers)
{
const int maxParams = 1000;
database.WorkContext.CancellationToken.ThrowIfCancellationRequested();
for (int i = 0; i < identifiers.Count; i += maxParams)
{
var sb = new StringBuilder("DELETE FROM ")
.Append(GetTableNameString(tableName))
.Append(" WHERE ")
.Append(commandBuilder.QuoteIdentifier(pkName))
.Append(" IN (");
for (int j = i; j < Math.Min(i + maxParams, identifiers.Count); j++)
{
if (i != j)
{
sb.Append(", ");
}
if (doNotParameterize == false)
{
sb.Append(identifiers[j]);
}
else
{
sb.Append("'").Append(RelationalDatabaseWriter.SanitizeSqlValue(identifiers[j])).Append("'");
}
}
sb.Append(")");
if (IsSqlServerFactoryType && cfg.ForceSqlServerQueryRecompile)
{
sb.Append(" OPTION(RECOMPILE)");
}
sb.Append(";");
yield return(sb.ToString());
}
}
private bool ReplicateChangesToDesintation(SqlReplicationConfig cfg, IEnumerable<JsonDocument> docs)
{
var scriptResult = ApplyConversionScript(cfg, docs);
if (scriptResult.Data.Count == 0)
return true;
var replicationStats = statistics.GetOrAdd(cfg.Name, name => new SqlReplicationStatistics(name));
var countOfItems = scriptResult.Data.Sum(x => x.Value.Count);
try
{
using (var writer = new RelationalDatabaseWriter(Database, cfg, replicationStats))
{
if (writer.Execute(scriptResult))
replicationStats.CompleteSuccess(countOfItems);
else
replicationStats.Success(countOfItems);
}
return true;
}
catch (Exception e)
{
log.WarnException("Failure to replicate changes to relational database for: " + cfg.Name, e);
SqlReplicationStatistics replicationStatistics;
DateTime newTime;
if (statistics.TryGetValue(cfg.Name, out replicationStatistics) == false)
{
newTime = SystemTime.UtcNow.AddSeconds(5);
}
else
{
var totalSeconds = (SystemTime.UtcNow - replicationStatistics.LastErrorTime).TotalSeconds;
newTime = SystemTime.UtcNow.AddSeconds(Math.Max(60 * 15, Math.Min(5, totalSeconds + 5)));
}
replicationStats.RecordWriteError(e, Database, countOfItems, newTime);
return false;
}
}
private bool ReplicateDeletionsToDestination(SqlReplicationConfig cfg, IEnumerable<ListItem> deletedDocs)
{
var identifiers = deletedDocs.Select(x => x.Key).ToList();
if (identifiers.Count == 0)
return true;
var replicationStats = statistics.GetOrAdd(cfg.Name, name => new SqlReplicationStatistics(name));
using (var writer = new RelationalDatabaseWriter(Database, cfg, replicationStats))
{
foreach (var sqlReplicationTable in cfg.SqlReplicationTables)
{
writer.DeleteItems(sqlReplicationTable.TableName, sqlReplicationTable.DocumentKeyColumn, identifiers);
}
writer.Commit();
}
return true;
}
public RelationalDatabaseWriter.TableQuerySummary[] SimulateSqlReplicationSqlQueries(string strDocumentId, SqlReplicationConfig sqlReplication, bool performRolledbackTransaction, out Alert alert)
{
RelationalDatabaseWriter.TableQuerySummary[] resutls = null;
try
{
var stats = new SqlReplicationStatistics(sqlReplication.Name, false);
var jsonDocument = Database.Documents.Get(strDocumentId, null);
JsonDocument.EnsureIdInMetadata(jsonDocument);
var doc = jsonDocument.ToJson();
doc[Constants.DocumentIdFieldName] = jsonDocument.Key;
var docs = new List <ReplicatedDoc>
{
new ReplicatedDoc
{
Document = doc,
Etag = jsonDocument.Etag,
Key = jsonDocument.Key,
SerializedSizeOnDisk = jsonDocument.SerializedSizeOnDisk
}
};
var scriptResult = ApplyConversionScript(sqlReplication, docs, stats);
var sqlReplicationConnections = Database.ConfigurationRetriever.GetConfigurationDocument <SqlReplicationConnections <SqlReplicationConnections.PredefinedSqlConnectionWithConfigurationOrigin> >(Constants.SqlReplication.SqlReplicationConnectionsDocumentName);
if (PrepareSqlReplicationConfig(sqlReplication, sqlReplication.Name, stats, sqlReplicationConnections.MergedDocument, false, false))
{
if (performRolledbackTransaction)
{
using (var writer = new RelationalDatabaseWriter(Database, sqlReplication, stats))
{
resutls = writer.RolledBackExecute(scriptResult).ToArray();
}
}
else
{
var simulatedwriter = new RelationalDatabaseWriterSimulator(Database, sqlReplication, stats);
resutls = new List <RelationalDatabaseWriter.TableQuerySummary>
{
new RelationalDatabaseWriter.TableQuerySummary
{
Commands = simulatedwriter.SimulateExecuteCommandText(scriptResult)
.Select(x => new RelationalDatabaseWriter.TableQuerySummary.CommandData
{
CommandText = x
}).ToArray()
}
}.ToArray();
}
}
alert = stats.LastAlert;
}
catch (Exception e)
{
alert = new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = SystemTime.UtcNow,
Message = "Last SQL replication operation for " + sqlReplication.Name + " was failed",
Title = "SQL replication error",
Exception = e.ToString(),
UniqueKey = "Sql Replication Error: " + sqlReplication.Name
};
}
return(resutls);
}
public RelationalDatabaseWriter.TableQuerySummary[] SimulateSqlReplicationSqlQueries(string strDocumentId, SqlReplicationConfig sqlReplication, bool performRolledbackTransaction, out Alert alert)
{
RelationalDatabaseWriter.TableQuerySummary[] resutls = null;
try
{
var stats = new SqlReplicationStatistics(sqlReplication.Name, false);
var jsonDocument = Database.Documents.Get(strDocumentId, null);
JsonDocument.EnsureIdInMetadata(jsonDocument);
var doc = jsonDocument.ToJson();
doc[Constants.DocumentIdFieldName] = jsonDocument.Key;
var docs = new List<ReplicatedDoc>
{
new ReplicatedDoc
{
Document = doc,
Etag = jsonDocument.Etag,
Key = jsonDocument.Key,
SerializedSizeOnDisk = jsonDocument.SerializedSizeOnDisk
}
};
var scriptResult = ApplyConversionScript(sqlReplication, docs, stats);
var connectionsDoc = Database.Documents.Get(Constants.RavenSqlReplicationConnectionsDocumentName, null);
var sqlReplicationConnections = connectionsDoc != null ? connectionsDoc.DataAsJson.JsonDeserialization<SqlReplicationConnections>() : new SqlReplicationConnections();
if (PrepareSqlReplicationConfig(sqlReplication, sqlReplication.Name, stats, sqlReplicationConnections, false, false))
{
if (performRolledbackTransaction)
{
using (var writer = new RelationalDatabaseWriter(Database, sqlReplication, stats))
{
resutls = writer.RolledBackExecute(scriptResult).ToArray();
}
}
else
{
var simulatedwriter = new RelationalDatabaseWriterSimulator(Database, sqlReplication, stats);
resutls = new List<RelationalDatabaseWriter.TableQuerySummary>
{
new RelationalDatabaseWriter.TableQuerySummary
{
Commands = simulatedwriter.SimulateExecuteCommandText(scriptResult)
.Select(x => new RelationalDatabaseWriter.TableQuerySummary.CommandData
{
CommandText = x
}).ToArray()
}
}.ToArray();
}
}
alert = stats.LastAlert;
}
catch (Exception e)
{
alert = new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = SystemTime.UtcNow,
Message = "Last SQL replication operation for " + sqlReplication.Name + " was failed",
Title = "SQL replication error",
Exception = e.ToString(),
UniqueKey = "Sql Replication Error: " + sqlReplication.Name
};
}
return resutls;
}
private bool ReplicateChangesToDestination(SqlReplicationConfig cfg, ICollection<ReplicatedDoc> docs, out int countOfReplicatedItems)
{
countOfReplicatedItems = 0;
var replicationStats = statistics.GetOrAdd(cfg.Name, name => new SqlReplicationStatistics(name));
var scriptResult = ApplyConversionScript(cfg, docs, replicationStats);
if (scriptResult.Ids.Count == 0)
return true;
countOfReplicatedItems = scriptResult.Data.Sum(x => x.Value.Count);
try
{
using (var writer = new RelationalDatabaseWriter(Database, cfg, replicationStats))
{
if (writer.Execute(scriptResult))
{
Log.Debug("Replicated changes of {0} for replication {1}", string.Join(", ", docs.Select(d => d.Key)), cfg.Name);
replicationStats.CompleteSuccess(countOfReplicatedItems);
}
else
{
Log.Debug("Replicated changes (with some errors) of {0} for replication {1}", string.Join(", ", docs.Select(d => d.Key)), cfg.Name);
replicationStats.Success(countOfReplicatedItems);
}
}
return true;
}
catch (Exception e)
{
Log.WarnException("Failure to replicate changes to relational database for: " + cfg.Name, e);
SqlReplicationStatistics replicationStatistics;
DateTime newTime;
if (statistics.TryGetValue(cfg.Name, out replicationStatistics) == false)
{
newTime = SystemTime.UtcNow.AddSeconds(5);
}
else
{
if (replicationStatistics.LastErrorTime == DateTime.MinValue)
{
newTime = SystemTime.UtcNow.AddSeconds(5);
}
else
{
// double the fallback time (but don't cross 15 minutes)
var totalSeconds = (SystemTime.UtcNow - replicationStatistics.LastErrorTime).TotalSeconds;
newTime = SystemTime.UtcNow.AddSeconds(Math.Min(60 * 15, Math.Max(5, totalSeconds * 2)));
}
}
replicationStats.RecordWriteError(e, Database, countOfReplicatedItems, newTime);
return false;
}
}
public RelationalDatabaseWriter.TableQuerySummary[] SimulateSqlReplicationSQLQueries(string strDocumentId, SqlReplicationConfig sqlReplication, bool performRolledbackTransaction, out Alert alert)
{
alert = null;
RelationalDatabaseWriter.TableQuerySummary[] resutls = null;
try
{
var stats = new SqlReplicationStatistics(sqlReplication.Name);
var docs = new List<JsonDocument>() { Database.Documents.Get(strDocumentId, null) };
var scriptResult = ApplyConversionScript(sqlReplication, docs);
var connectionsDoc = Database.Documents.Get(connectionsDocumentName, null);
var sqlReplicationConnections = connectionsDoc.DataAsJson.JsonDeserialization<SqlReplicationConnections>();
if (PrepareSqlReplicationConfig( sqlReplication, sqlReplication.Name, stats, sqlReplicationConnections, false,false))
{
if (performRolledbackTransaction)
{
using (var writer = new RelationalDatabaseWriter(Database, sqlReplication, stats))
{
resutls = writer.RolledBackExecute(scriptResult).ToArray();
}
}
else
{
var simulatedwriter = new RelationalDatabaseWriterSimulator(Database, sqlReplication, stats);
resutls = new List<RelationalDatabaseWriter.TableQuerySummary>()
{
new RelationalDatabaseWriter.TableQuerySummary()
{
Commands = simulatedwriter.SimulateExecuteCommandText(scriptResult)
.Select(x => new RelationalDatabaseWriter.TableQuerySummary.CommandData()
{
CommandText = x
}).ToArray()
}
}.ToArray();
}
}
alert = stats.LastAlert;
}
catch (Exception e)
{
alert = new Alert()
{
AlertLevel = AlertLevel.Error,
CreatedAt = SystemTime.UtcNow,
Message = "Last SQL replication operation for " + sqlReplication.Name + " was failed",
Title = "SQL replication error",
Exception = e.ToString(),
UniqueKey = "Sql Replication Error: " + sqlReplication.Name
};
}
return resutls;
}
