/// <summary>
/// The BuildCountQuery
/// </summary>
/// <param name="table">The table<see cref="ISqlTableIdentifier"/></param>
/// <param name="filter">The filter<see cref="ISqlConditionExpression"/></param>
/// <returns>The <see cref="IParameterizedQuery"/></returns>
public IParameterizedQuery BuildCountQuery(ISqlTableIdentifier table, ISqlConditionExpression filter)
{
string conditions;
IEnumerable <ISqlParameter> parameters;
if (filter != null)
{
IUncheckedSqlCondition uncheckedCondition = ConditionStringBuilder.Build(filter);
conditions = uncheckedCondition.SqlConditionString;
parameters = uncheckedCondition.Parameters;
foreach (string column in uncheckedCondition.UncheckedIdentifiers)
{
Logger.LogTrace($"Checking existence of {table.Table}.{column}");
if (!Connection.CheckIfColumnExists(table, column))
{
throw new InvalidOperationException($"Column {table.Table}.{column} does not exist or is not accessible.");
}
}
}
else
{
conditions = "1 = 1";
parameters = Enumerable.Empty <ISqlParameter>();
}
string tableStr = DbCommandBuilder.QuoteIdentifier(table.Table);
string sql = $"SELECT COUNT(*) FROM {tableStr} WHERE ({conditions})";
return(new DbQuery(sql, parameters));
}
/// <param name="schema">The schema of the table. May be <code>null</code> as some providers (e.g. SQLite, MySQL) do not support schemata.</param>
/// <param name="tableName">The name of the table.</param>
/// <param name="commandBuilder">The <see cref="DbCommandBuilder"/> of the provider, may be <code>null</code>.</param>
/// <returns>A select statement used to retrieve all column names in a database.</returns>
private static string SelectStatement(string schema, string tableName, DbCommandBuilder commandBuilder)
{
var hasSchema = !string.IsNullOrEmpty(schema);
var quotedSchema = hasSchema ? commandBuilder?.QuoteIdentifier(schema) ?? schema : null;
var quotedTableName = commandBuilder?.QuoteIdentifier(tableName) ?? tableName;
var schemaSeparator = commandBuilder?.SchemaSeparator ?? ".";
var tableDescription = hasSchema ? quotedSchema + schemaSeparator + quotedTableName : quotedTableName;
return($"SELECT * FROM {tableDescription} WHERE 1=0");
}
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());
}
}
public override string QuoteIdentifier(string ident)
{
if (m_builder == null)
{
return(ident);
}
try
{
return(m_builder.QuoteIdentifier(ident));
}
catch (Exception)
{
return(ident);
}
}
internal virtual string QuoteCompoundIdentifier(string s)
{
if (s == null)
{
throw ALinq.Error.ArgumentNull("s");
}
if (s.StartsWith(ParameterPrefix, StringComparison.Ordinal))
{
return(s);
}
if (IsQuoted(s))
{
return(s);
}
if (!s.StartsWith(QuotePrefix, StringComparison.Ordinal) && s.EndsWith(QuoteSuffix, StringComparison.Ordinal))
{
int length = s.IndexOf('.');
if (length < 0)
{
return(builder.QuoteIdentifier(s));
}
string str = s.Substring(0, length);
string str2 = s.Substring(length + 1, (s.Length - length) - 1);
if (!IsQuoted(str2))
{
str2 = builder.QuoteIdentifier(str2);
}
return(QuoteCompoundIdentifier(str) + ('.' + str2));
}
if (s.StartsWith(QuotePrefix, StringComparison.Ordinal) && !s.EndsWith(QuoteSuffix, StringComparison.Ordinal))
{
int num2 = s.LastIndexOf('.');
if (num2 < 0)
{
return(builder.QuoteIdentifier(s));
}
string str3 = s.Substring(0, num2);
if (!IsQuoted(str3))
{
str3 = builder.QuoteIdentifier(str3);
}
string str4 = s.Substring(num2 + 1, (s.Length - num2) - 1);
return(str3 + ('.') + QuoteCompoundIdentifier(str4));
}
int index = s.IndexOf('.');
if (index < 0)
{
return(builder.QuoteIdentifier(s));
}
string str5 = s.Substring(0, index);
string str6 = s.Substring(index + 1, (s.Length - index) - 1);
return(QuoteCompoundIdentifier(str5) + ('.') + QuoteCompoundIdentifier(str6));
}
internal static string EscapeObjectName(DesignerDataConnection connection, string objectName)
{
string str = "[";
string str2 = "]";
try
{
DbProviderFactory dbProviderFactory = SqlDataSourceDesigner.GetDbProviderFactory(connection.ProviderName);
DbCommandBuilder builder = dbProviderFactory.CreateCommandBuilder();
if (dbProviderFactory == OracleClientFactory.Instance)
{
str = str2 = "\"";
}
builder.QuotePrefix = str;
builder.QuoteSuffix = str2;
return(builder.QuoteIdentifier(objectName));
}
catch (Exception)
{
return(str + objectName + str2);
}
}
public string QuoteIdentifier(string identifier)
{
return(_builder.QuoteIdentifier(identifier));
}
/// <summary>
///
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public override string Quote(string name)
{
return(cb.QuoteIdentifier(name));
}
private void InsertItems(string tableName, string pkName, List <ItemToReplicate> dataForTable, Action <DbCommand> commandCallback = null)
{
var sqlReplicationTableMetrics = sqlReplicationMetrics.GetTableMetrics(tableName);
var replicationInsertActionsMetrics = sqlReplicationTableMetrics.SqlReplicationInsertActionsMeter;
var replicationInsertActionsHistogram = sqlReplicationTableMetrics.SqlReplicationInsertActionsHistogram;
var replicationInsertDurationHistogram = sqlReplicationTableMetrics.SqlReplicationInsertActionsDurationHistogram;
var sp = new Stopwatch();
foreach (var itemToReplicate in dataForTable)
{
sp.Restart();
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = tx;
database.WorkContext.CancellationToken.ThrowIfCancellationRequested();
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;
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(")");
if (IsSqlServerFactoryType && cfg.ForceSqlServerQueryRecompile)
{
sb.Append(" OPTION(RECOMPILE)");
}
var stmt = sb.ToString();
cmd.CommandText = stmt;
if (commandCallback != null)
{
commandCallback(cmd);
}
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;
}
finally
{
sp.Stop();
var elapsedMiliseconds = sp.ElapsedMilliseconds;
if (log.IsDebugEnabled)
{
log.Debug(string.Format("Insert took: {0}ms, statement: {1}", elapsedMiliseconds, stmt));
}
var elapsedMicroseconds = (long)(sp.ElapsedTicks * SystemTime.MicroSecPerTick);
replicationInsertDurationHistogram.Update(elapsedMicroseconds);
replicationInsertActionsMetrics.Mark(1);
replicationInsertActionsHistogram.Update(1);
if (elapsedMiliseconds > LongStatementWarnThresholdInMiliseconds)
{
HandleSlowSql(elapsedMiliseconds, stmt);
}
}
}
}
}
public override void OnIndexEntryCreated(string entryKey, Document document)
{
var cmd = _providerFactory.CreateCommand();
var pkParam = cmd.CreateParameter();
pkParam.ParameterName = GetParameterName("entryKey");
pkParam.Value = entryKey;
cmd.Parameters.Add(pkParam);
var sb = new StringBuilder("INSERT INTO ")
.Append(_commandBuilder.QuoteIdentifier(destination.TableName))
.Append(" (")
.Append(_commandBuilder.QuoteIdentifier(destination.PrimaryKeyColumnName))
.Append(", ");
foreach (var mapping in destination.ColumnsMapping)
{
sb.Append(mapping.Value).Append(", ");
}
sb.Length = sb.Length - 2;
sb.Append(") \r\nVALUES (")
.Append(pkParam.ParameterName)
.Append(", ");
foreach (var mapping in destination.ColumnsMapping)
{
var parameter = cmd.CreateParameter();
parameter.ParameterName = GetParameterName(mapping.Key);
var field = document.GetFieldable(mapping.Key);
var numericfield = document.GetFieldable(String.Concat(mapping.Key, "_Range"));
if (numericfield != null)
{
field = numericfield;
}
if (field == null || field.StringValue == Constants.NullValue)
{
parameter.Value = DBNull.Value;
}
else if (field.StringValue == Constants.EmptyString)
{
parameter.Value = "";
}
else if (field.StringValue.Equals("False", StringComparison.InvariantCultureIgnoreCase))
{
parameter.Value = false;
}
else if (field.StringValue.Equals("True", StringComparison.InvariantCultureIgnoreCase))
{
parameter.Value = true;
}
else if (field is NumericField)
{
var numField = (NumericField)field;
parameter.Value = numField.NumericValue;
}
else
{
var stringValue = field.StringValue;
if (datePattern.IsMatch(stringValue))
{
try
{
parameter.Value = DateTools.StringToDate(stringValue);
}
catch
{
parameter.Value = stringValue;
}
}
else
{
DateTime time;
if (DateTime.TryParseExact(stringValue, Default.DateTimeFormatsToRead, CultureInfo.InvariantCulture,
DateTimeStyles.None, out time))
{
parameter.Value = time;
}
else
{
parameter.Value = stringValue;
}
}
}
cmd.Parameters.Add(parameter);
sb.Append(parameter.ParameterName).Append(", ");
}
sb.Length = sb.Length - 2;
sb.Append(")");
cmd.CommandText = sb.ToString();
commands.Enqueue(cmd);
}
private void InsertItems(string tableName, string pkName, List <ItemToReplicate> dataForTable)
{
foreach (var itemToReplicate in dataForTable)
{
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = tx;
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);
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;
}
}
}
}
private void ConfigureAdapter(DbConnection cnn, string tableName)
{
DbDataAdapter adapter;
if (!_adapters.TryGetValue(tableName, out adapter))
{
DbCommandBuilder builder = _factory.CreateCommandBuilder();
adapter = _factory.CreateDataAdapter();
adapter.SelectCommand = GetCommand(cnn, string.Format("SELECT * FROM {0}", builder.QuoteIdentifier(tableName)));
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
builder.DataAdapter = adapter;
// save default builder to quote identifiers
if (_builder == null)
{
_builder = builder;
}
_adapters.Add(tableName, adapter);
}
adapter.Fill(_dataSet, tableName);
}
public string EncodeTable(string table)
{
return(CommandBuilder.QuoteIdentifier(table));
}
/// <summary>
/// Quotes the <paramref name="unquotedIdentifier"/>.
/// </summary>
/// <param name="unquotedIdentifier">The original unquoted identifier.</param>
/// <returns><see cref="string"/></returns>
protected virtual string QuoteIdentifier(string unquotedIdentifier)
{
return(_commandBuilder.QuoteIdentifier(unquotedIdentifier));
}
private IEnumerable <DbCommand> InsertItems(string tableName, string pkName, List <ItemToReplicate> dataForTable, bool exportCommands = false)
{
foreach (var itemToReplicate in dataForTable)
{
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = tx;
database.WorkContext.CancellationToken.ThrowIfCancellationRequested();
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;
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(")");
if (IsSqlServerFactoryType && cfg.ForceSqlServerQueryRecompile)
{
sb.Append(" OPTION(RECOMPILE)");
}
cmd.CommandText = sb.ToString();
if (exportCommands)
{
yield return(cmd);
}
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;
}
}
}
}
private string Quote(string identifier)
{
return(builder.QuoteIdentifier(identifier));
}
protected override void ProcessRecord()
{
try
{
if (InputObject == null)
{
return;
}
if (_fieldSet == null)
{
_qualifiedTableName = GetQualifiedTableName();
CreateTable();
_useNamedParameters = TestNamedParameterSupport();
_insertStmt = "insert into " + _qualifiedTableName + " (";
_transaction = _connection.BeginTransaction();
}
using (var cmd = _connection.CreateCommand())
{
cmd.CommandText = _insertStmt;
cmd.Transaction = _transaction;
var buffer = new StringBuilder(_insertStmt);
var placeholders = new StringBuilder();
string pa = "?";
var count = 0;
if (InputObject is PSObject)
{
var obj = (PSObject)InputObject;
foreach (var p in obj.Properties)
{
if (!p.IsGettable || !p.IsInstance)
{
continue;
}
if (_fieldSet.Contains(p.Name))
{
if (count > 0)
{
buffer.Append(',');
placeholders.Append(',');
}
++count;
var qualified = _builder.QuoteIdentifier(p.Name);
buffer.Append(qualified);
var param = cmd.CreateParameter();
if (_useNamedParameters)
{
pa = "@" + count.ToString();
param.ParameterName = pa;
}
var value = p.Value;
if (value != null)
{
if (value is PSObject)
{
value = (value as PSObject).BaseObject;
}
param.Value = value;
}
cmd.Parameters.Add(param);
placeholders.Append(pa);
}
}
}
else
{
foreach (var p in InputObject.GetType().GetProperties())
{
if (!p.CanRead)
{
continue;
}
if (_fieldSet.Contains(p.Name))
{
if (count > 0)
{
buffer.Append(',');
placeholders.Append(',');
}
++count;
var qualified = _builder.QuoteIdentifier(p.Name);
buffer.Append(qualified);
var param = cmd.CreateParameter();
if (_useNamedParameters)
{
pa = "@" + count.ToString();
param.ParameterName = pa;
}
var value = p.GetValue(InputObject);
if (value != null)
{
if (value is PSObject)
{
value = (value as PSObject).BaseObject;
}
param.Value = value;
}
cmd.Parameters.Add(param);
placeholders.Append(pa);
}
}
}
// If there are no columns to be inserted, just skip.
if (placeholders.Length == 0)
{
return;
}
buffer.Append(") values (");
buffer.Append(placeholders);
buffer.Append(')');
var stmt = buffer.ToString();
WriteVerbose(stmt);
cmd.CommandText = stmt;
var rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected != 1)
{
throw new RuntimeException("Insertion failed");
}
}
}
catch (Exception e)
{
if (_transaction != null)
{
_transaction.Rollback();
}
DisposeResources();
WriteError(new ErrorRecord(e, "", ErrorCategory.NotSpecified, null));
throw new PipelineStoppedException();
}
}
