/Raven.Database/Bundles/SqlReplication/RelationalDatabaseWriter.cs
C# | 370 lines | 327 code | 41 blank | 2 comment | 27 complexity | 7f0fa28856ac2215df7f7fc52797abc9 MD5 | raw file
Possible License(s): MPL-2.0-no-copyleft-exception, BSD-3-Clause, CC-BY-SA-3.0
- using System;
- using System.Collections.Generic;
- using System.Data.Common;
- using System.Diagnostics;
- using System.Globalization;
- using System.Reflection;
- using System.Text;
- using Raven.Abstractions;
- using Raven.Abstractions.Data;
- using Raven.Abstractions.Logging;
- using Raven.Database.Extensions;
- using Raven.Database.Indexing;
- using Raven.Imports.Newtonsoft.Json.Linq;
- using Raven.Json.Linq;
- using System.Linq;
-
- namespace Raven.Database.Bundles.SqlReplication
- {
- public class RelationalDatabaseWriter : IDisposable
- {
- private readonly DocumentDatabase database;
- private readonly SqlReplicationConfig cfg;
- private readonly DbProviderFactory providerFactory;
- private readonly SqlReplicationStatistics replicationStatistics;
- private readonly DbCommandBuilder commandBuilder;
- private readonly DbConnection connection;
- private readonly DbTransaction tx;
- private readonly List<Func<DbParameter, String, Boolean>> stringParserList;
-
- private static readonly ILog log = LogManager.GetCurrentClassLogger();
-
- bool hadErrors;
-
- public RelationalDatabaseWriter( DocumentDatabase database, SqlReplicationConfig cfg, SqlReplicationStatistics replicationStatistics)
- {
- this.database = database;
- this.cfg = cfg;
- this.replicationStatistics = replicationStatistics;
-
- providerFactory = GetDbProviderFactory(cfg);
-
- commandBuilder = providerFactory.CreateCommandBuilder();
- connection = providerFactory.CreateConnection();
-
- Debug.Assert(connection != null);
- Debug.Assert(commandBuilder != null);
-
- connection.ConnectionString = cfg.ConnectionString;
-
- try
- {
- connection.Open();
- }
- catch (Exception e)
- {
- database.AddAlert(new Alert
- {
- AlertLevel = AlertLevel.Error,
- CreatedAt = SystemTime.UtcNow,
- Exception = e.ToString(),
- Title = "Sql Replication could not open connection",
- Message = "Sql Replication could not open connection to " + connection.ConnectionString,
- UniqueKey = "Sql Replication Connection Error: " + connection.ConnectionString
- });
- throw;
- }
-
- tx = connection.BeginTransaction();
-
- stringParserList = new List<Func<DbParameter, string, bool>> {
- (colParam, value) => {
- if( char.IsDigit( value[ 0 ] ) ) {
- DateTime dateTime;
- if (DateTime.TryParseExact(value, Default.OnlyDateTimeFormat, CultureInfo.InvariantCulture,
- DateTimeStyles.RoundtripKind, out dateTime))
- {
- switch( providerFactory.GetType( ).Name ) {
- case "MySqlClientFactory":
- colParam.Value = dateTime.ToString( "yyyy-MM-dd HH:mm:ss.ffffff" );
- break;
- default:
- colParam.Value = dateTime;
- break;
- }
- return true;
- }
- }
- return false;
- },
- (colParam, value) => {
- if( char.IsDigit( value[ 0 ] ) ) {
- DateTimeOffset dateTimeOffset;
- if( DateTimeOffset.TryParseExact( value, Default.DateTimeFormatsToRead, CultureInfo.InvariantCulture,
- DateTimeStyles.RoundtripKind, out dateTimeOffset ) ) {
- switch( providerFactory.GetType( ).Name ) {
- case "MySqlClientFactory":
- colParam.Value = dateTimeOffset.ToUniversalTime().ToString( "yyyy-MM-dd HH:mm:ss.ffffff" );
- break;
- default:
- colParam.Value = dateTimeOffset;
- break;
- }
- return true;
- }
- }
- return false;
- }
- };
- }
-
- public bool Execute(ConversionScriptResult scriptResult)
- {
- var identifiers = scriptResult.Data.SelectMany(x => x.Value).Select(x => x.DocumentId).Distinct().ToList();
- foreach (var sqlReplicationTable in cfg.SqlReplicationTables)
- {
- // first, delete all the rows that might already exist there
- DeleteItems(sqlReplicationTable.TableName, sqlReplicationTable.DocumentKeyColumn, cfg.ParameterizeDeletesDisabled,
- identifiers);
- }
-
- foreach (var sqlReplicationTable in cfg.SqlReplicationTables)
- {
- List<ItemToReplicate> dataForTable;
- if (scriptResult.Data.TryGetValue(sqlReplicationTable.TableName, out dataForTable) == false)
- continue;
-
- InsertItems(sqlReplicationTable.TableName, sqlReplicationTable.DocumentKeyColumn, dataForTable);
- }
-
- Commit();
-
- return hadErrors == false;
- }
-
- public bool Commit()
- {
- tx.Commit();
- return true;
- }
-
- private void InsertItems(string tableName, string pkName, List<ItemToReplicate> dataForTable)
- {
- foreach (var itemToReplicate in dataForTable)
- {
- using (var cmd = connection.CreateCommand())
- {
- cmd.Transaction = tx;
-
- database.WorkContext.CancellationToken.ThrowIfCancellationRequested();
-
- var sb = new StringBuilder("INSERT INTO ")
- .Append(commandBuilder.QuoteIdentifier(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;
-
- var pkParam = cmd.CreateParameter();
- pkParam.ParameterName = GetParameterName(providerFactory, commandBuilder, pkName);
- pkParam.Value = itemToReplicate.DocumentId;
- cmd.Parameters.Add(pkParam);
-
- sb.Append(") \r\nVALUES (")
- .Append(GetParameterName(providerFactory, commandBuilder, pkName))
- .Append(", ");
-
- foreach (var column in itemToReplicate.Columns)
- {
- if (column.Key == pkName)
- continue;
- var colParam = cmd.CreateParameter();
- colParam.ParameterName = column.Key;
- SetParamValue( colParam, column.Value, stringParserList );
- cmd.Parameters.Add(colParam);
- sb.Append(GetParameterName(providerFactory, commandBuilder, column.Key)).Append(", ");
- }
- sb.Length = sb.Length - 2;
- sb.Append(")");
- cmd.CommandText = sb.ToString();
- try
- {
- cmd.ExecuteNonQuery();
- }
- catch (Exception e)
- {
- log.WarnException(
- "Failure to replicate changes to relational database for: " + cfg.Name + " (doc: "+ itemToReplicate.DocumentId +" ), will continue trying." +
- Environment.NewLine + cmd.CommandText, e);
- replicationStatistics.RecordWriteError(e, database);
- hadErrors = true;
- }
- }
- }
- }
-
- public void DeleteItems(string tableName, string pkName, bool doNotParameterize, List<string> identifiers)
- {
- const int maxParams = 1000;
- using (var cmd = connection.CreateCommand())
- {
- cmd.Transaction = tx;
- database.WorkContext.CancellationToken.ThrowIfCancellationRequested();
- for (int i = 0; i < identifiers.Count; i += maxParams)
- {
- cmd.Parameters.Clear();
- var sb = new StringBuilder("DELETE FROM ")
- .Append(commandBuilder.QuoteIdentifier(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)
- {
- var dbParameter = cmd.CreateParameter();
- dbParameter.ParameterName = GetParameterName(providerFactory, commandBuilder, "p" + j);
- dbParameter.Value = identifiers[j];
- cmd.Parameters.Add(dbParameter);
- sb.Append(dbParameter.ParameterName);
- }
- else
- {
- sb.Append("'").Append(SanitizeSqlValue(identifiers[j])).Append("'");
- }
-
- }
- sb.Append(")");
-
- cmd.CommandText = sb.ToString();
-
- try
- {
- cmd.ExecuteNonQuery();
- }
- catch (Exception e)
- {
- log.WarnException(
- "Failure to replicate changes to relational database for: " + cfg.Name + ", will continue trying." +
- Environment.NewLine + cmd.CommandText, e);
- replicationStatistics.RecordWriteError(e, database);
- hadErrors = true;
- }
- }
- }
- }
-
- public string SanitizeSqlValue(string sqlValue)
- {
- return sqlValue.Replace("'", "''");
- }
-
- private static string GetParameterName(DbProviderFactory providerFactory, DbCommandBuilder commandBuilder, string paramName)
- {
- switch (providerFactory.GetType().Name)
- {
- case "SqlClientFactory":
- case "MySqlClientFactory":
- return "@" + paramName;
-
- case "OracleClientFactory":
- case "NpgsqlFactory":
- return ":" + paramName;
-
- default:
- // If we don't know, try to get it from the CommandBuilder.
- return getParameterNameFromBuilder(commandBuilder, paramName);
- }
- }
-
- private static readonly Func<DbCommandBuilder, string, string> getParameterNameFromBuilder =
- (Func<DbCommandBuilder, string, string>)
- Delegate.CreateDelegate(typeof(Func<DbCommandBuilder, string, string>),
- typeof(DbCommandBuilder).GetMethod("GetParameterName",
- BindingFlags.Instance | BindingFlags.NonPublic, Type.DefaultBinder,
- new[] { typeof(string) }, null));
-
-
- private static void SetParamValue(DbParameter colParam, RavenJToken val, List<Func<DbParameter, String, Boolean>> stringParsers)
- {
- if (val == null)
- colParam.Value = DBNull.Value;
- else
- {
- switch (val.Type)
- {
- case JTokenType.None:
- case JTokenType.Object:
- case JTokenType.Uri:
- case JTokenType.Raw:
- case JTokenType.Array:
- colParam.Value = val.Value<string>();
- return;
- case JTokenType.String:
- var value = val.Value<string>();
- if( value.Length > 0 && stringParsers != null ) {
- foreach( var parser in stringParsers ) {
- if( parser( colParam, value ) ) {
- return;
- }
- }
- }
- colParam.Value = value;
- return;
- case JTokenType.Integer:
- case JTokenType.Date:
- case JTokenType.Bytes:
- case JTokenType.Guid:
- case JTokenType.Boolean:
- case JTokenType.TimeSpan:
- case JTokenType.Float:
- colParam.Value = val.Value<object>();
- return;
- case JTokenType.Null:
- case JTokenType.Undefined:
- colParam.Value = DBNull.Value;
- return;
- default:
- throw new InvalidOperationException("Cannot understand how to save " + val.Type + " for " + colParam.ParameterName);
- }
- }
- }
-
- private DbProviderFactory GetDbProviderFactory(SqlReplicationConfig cfg)
- {
- DbProviderFactory providerFactory;
- try
- {
- providerFactory = DbProviderFactories.GetFactory(cfg.FactoryName);
- }
- catch (Exception e)
- {
- log.WarnException(
- string.Format("Could not find provider factory {0} to replicate to sql for {1}, ignoring", cfg.FactoryName,
- cfg.Name), e);
-
- database.AddAlert(new Alert
- {
- AlertLevel = AlertLevel.Error,
- CreatedAt = SystemTime.UtcNow,
- Exception = e.ToString(),
- Title = "Sql Replication could not find factory provider",
- Message = string.Format("Could not find factory provider {0} to replicate to sql for {1}, ignoring", cfg.FactoryName,
- cfg.Name),
- UniqueKey = string.Format("Sql Replication Provider Not Found: {0}, {1}", cfg.Name, cfg.FactoryName)
- });
-
- throw;
- }
- return providerFactory;
- }
-
-
-
- public void Dispose()
- {
- tx.Dispose();
- commandBuilder.Dispose();
- connection.Dispose();
- }
- }
- }