private string CreateUpdateUntrackedRowsCommand()
{
var stringBuilder = new StringBuilder();
var str1 = new StringBuilder();
var str2 = new StringBuilder();
var str3 = new StringBuilder();
var str4 = SqlManagementUtils.JoinTwoTablesOnClause(this.TableDescription.PrimaryKeys, "[side]", "[base]");
stringBuilder.AppendLine($"INSERT INTO {trackingName.Schema().Quoted().ToString()} (");
var comma = "";
foreach (var pkeyColumn in TableDescription.GetPrimaryKeysColumns())
{
var pkeyColumnName = ParserName.Parse(pkeyColumn).Quoted().ToString();
str1.Append($"{comma}{pkeyColumnName}");
str2.Append($"{comma}[base].{pkeyColumnName}");
str3.Append($"{comma}[side].{pkeyColumnName}");
comma = ", ";
}
stringBuilder.Append(str1.ToString());
stringBuilder.AppendLine($", [update_scope_id], [sync_row_is_tombstone], [last_change_datetime]");
stringBuilder.AppendLine($")");
stringBuilder.Append($"SELECT ");
stringBuilder.Append(str2.ToString());
stringBuilder.AppendLine($", NULL, 0, GetUtcDate()");
stringBuilder.AppendLine($"FROM {tableName.Schema().Quoted().ToString()} as [base] WHERE NOT EXISTS");
stringBuilder.Append($"(SELECT ");
stringBuilder.Append(str3.ToString());
stringBuilder.AppendLine($" FROM {trackingName.Schema().Quoted().ToString()} as [side] ");
stringBuilder.AppendLine($"WHERE {str4})");
var r = stringBuilder.ToString();
return(r);
}
internal MySqlParameter GetMySqlParameter(SyncColumn column)
{
var mySqlDbMetadata = new MySqlDbMetadata();
var parameterName = ParserName.Parse(column).Unquoted().Normalized().ToString();
var sqlParameter = new MySqlParameter
{
ParameterName = $"{MySqlBuilderProcedure.MYSQL_PREFIX_PARAMETER}{parameterName}",
DbType = column.GetDbType(),
IsNullable = column.AllowDBNull
};
(byte precision, byte scale) = mySqlDbMetadata.TryGetOwnerPrecisionAndScale(column.OriginalDbType, column.GetDbType(), false, false, column.MaxLength, column.Precision, column.Scale, this.tableDescription.OriginalProvider, MySqlSyncProvider.ProviderType);
if ((sqlParameter.DbType == DbType.Decimal || sqlParameter.DbType == DbType.Double ||
sqlParameter.DbType == DbType.Single || sqlParameter.DbType == DbType.VarNumeric) && precision > 0)
{
sqlParameter.Precision = precision;
if (scale > 0)
{
sqlParameter.Scale = scale;
}
}
else if (column.MaxLength > 0)
{
sqlParameter.Size = (int)column.MaxLength;
}
else if (sqlParameter.DbType == DbType.Guid)
{
sqlParameter.Size = 36;
}
else
{
sqlParameter.Size = -1;
}
return(sqlParameter);
}
private SqlCommand BuildPkCommand()
{
var stringBuilder = new StringBuilder();
var tableNameString = tableName.Schema().Quoted().ToString();
var primaryKeyNameString = tableName.Schema().Unquoted().Normalized().ToString();
stringBuilder.AppendLine($"ALTER TABLE {tableNameString} ADD CONSTRAINT [PK_{primaryKeyNameString}] PRIMARY KEY(");
for (int i = 0; i < this.tableDescription.PrimaryKeys.Count; i++)
{
var pkColumn = this.tableDescription.PrimaryKeys[i];
var quotedColumnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.Append(quotedColumnName);
if (i < this.tableDescription.PrimaryKeys.Count - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.Append(")");
return(new SqlCommand(stringBuilder.ToString()));
}
private NpgsqlCommand BuildPkCommand(DbConnection connection, DbTransaction transaction)
{
var stringBuilder = new StringBuilder();
var tableNameString = tableName.Schema().Quoted().ToString();
var primaryKeyNameString = tableName.Schema().Unquoted().Normalized().ToString();
stringBuilder.AppendLine($"ALTER TABLE {tableNameString} ADD CONSTRAINT \"PK_{primaryKeyNameString}\" PRIMARY KEY(");
for (int i = 0; i < this.tableDescription.PrimaryKeys.Count; i++)
{
var pkColumn = this.tableDescription.PrimaryKeys[i];
var quotedColumnName = ParserName.Parse(pkColumn, "\"").Quoted().ToString();
stringBuilder.Append(quotedColumnName);
if (i < this.tableDescription.PrimaryKeys.Count - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.Append(")");
return(new NpgsqlCommand(stringBuilder.ToString(), (NpgsqlConnection)connection, (NpgsqlTransaction)transaction));
}
private string CreateUpdateUntrackedRowsCommand()
{
var stringBuilder = new StringBuilder();
var str1 = new StringBuilder();
var str2 = new StringBuilder();
var str3 = new StringBuilder();
var str4 = MySqlManagementUtils.JoinTwoTablesOnClause(this.TableDescription.GetPrimaryKeysColumns(), "`side`", "`base`");
stringBuilder.AppendLine($"INSERT INTO {trackingName.Schema().Quoted().ToString()} (");
var comma = "";
foreach (var pkeyColumn in TableDescription.GetPrimaryKeysColumns())
{
var pkeyColumnName = ParserName.Parse(pkeyColumn, "`").Quoted().ToString();
str1.Append($"{comma}{pkeyColumnName}");
str2.Append($"{comma}`base`.{pkeyColumnName}");
str3.Append($"{comma}`side`.{pkeyColumnName}");
comma = ", ";
}
stringBuilder.Append(str1.ToString());
stringBuilder.AppendLine($", `update_scope_id`, `sync_row_is_tombstone`, `timestamp`, `last_change_datetime`");
stringBuilder.AppendLine($")");
stringBuilder.Append($"SELECT ");
stringBuilder.Append(str2.ToString());
stringBuilder.AppendLine($", NULL, 0, {MySqlObjectNames.TimestampValue}, now()");
stringBuilder.AppendLine($"FROM {tableName.Schema().Quoted().ToString()} as `base` WHERE NOT EXISTS");
stringBuilder.Append($"(SELECT ");
stringBuilder.Append(str3.ToString());
stringBuilder.AppendLine($" FROM {trackingName.Schema().Quoted().ToString()} as `side` ");
stringBuilder.AppendLine($"WHERE {str4})");
var r = stringBuilder.ToString();
return(r);
}
public static async Task DropTriggerIfExistsAsync(MySqlConnection connection, MySqlTransaction transaction, string quotedTriggerName)
{
var triggerName = ParserName.Parse(quotedTriggerName, "`");
using DbCommand dbCommand = connection.CreateCommand();
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
dbCommand.CommandText = $"drop trigger {triggerName.Unquoted().ToString()}";
dbCommand.Transaction = transaction;
await dbCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
if (!alreadyOpened)
{
connection.Close();
}
}
private string DeleteTriggerBodyText()
{
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
stringBuilder.AppendLine("UPDATE [side] ");
stringBuilder.AppendLine("SET \t[sync_row_is_tombstone] = 1");
stringBuilder.AppendLine("\t,[update_scope_id] = NULL -- since the update if from local, it's a NULL");
stringBuilder.AppendLine("\t,[update_timestamp] = @@DBTS+1");
stringBuilder.AppendLine("\t,[last_change_datetime] = GetUtcDate()");
// Filter columns
if (this.Filters != null && Filters.Count > 0)
{
foreach (var filter in this.Filters)
{
var columnFilter = this.tableDescription.Columns[filter.ColumnName];
if (columnFilter == null)
{
throw new InvalidExpressionException($"Column {filter.ColumnName} does not exist in Table {this.tableDescription.TableName}");
}
if (this.tableDescription.PrimaryKey.Columns.Any(c => c.ColumnName == columnFilter.ColumnName))
{
continue;
}
var columnName = ParserName.Parse(columnFilter).Quoted().ToString();
stringBuilder.AppendLine($"\t,{columnName} = [d].{columnName}");
}
stringBuilder.AppendLine();
}
stringBuilder.AppendLine($"FROM {trackingName.Schema().Quoted().ToString()} [side]");
stringBuilder.Append($"JOIN DELETED AS [d] ON ");
stringBuilder.AppendLine(SqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKey.Columns, "[side]", "[d]"));
return(stringBuilder.ToString());
}
private string CreateIndexCommandText()
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"CREATE NONCLUSTERED INDEX [{trackingName.Schema().Unquoted().Normalized().ToString()}_timestamp_index] ON {trackingName.Schema().Quoted().ToString()} (");
stringBuilder.AppendLine($"\t[update_timestamp] ASC");
stringBuilder.AppendLine($"\t,[update_scope_id] ASC");
stringBuilder.AppendLine($"\t,[sync_row_is_tombstone] ASC");
// Filter columns
if (this.Filters != null && this.Filters.Count > 0)
{
foreach (var filterColumn in this.Filters)
{
// check if the filter column is already a primary key.
// in this case, we will add it as an index in the next foreach
if (this.tableDescription.PrimaryKey.Columns.Any(c => c.ColumnName.ToLowerInvariant() == filterColumn.ColumnName.ToLowerInvariant()))
{
continue;
}
if (!this.tableDescription.Columns.Any(c => c.ColumnName.ToLowerInvariant() == filterColumn.ColumnName.ToLowerInvariant()))
{
continue;
}
var columnName = ParserName.Parse(filterColumn.ColumnName).Quoted().ToString();
stringBuilder.AppendLine($"\t,{columnName} ASC");
}
}
foreach (var pkColumn in this.tableDescription.PrimaryKey.Columns)
{
var columnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.AppendLine($"\t,{columnName} ASC");
}
stringBuilder.Append(")");
return(stringBuilder.ToString());
}
private SqlCommand BuildForeignKeyConstraintsCommand(SyncRelation constraint)
{
var sqlCommand = new SqlCommand();
var tableName = ParserName.Parse(constraint.GetTable()).Quoted().Schema().ToString();
var parentTableName = ParserName.Parse(constraint.GetParentTable()).Quoted().Schema().ToString();
var relationName = NormalizeRelationName(constraint.RelationName);
var stringBuilder = new StringBuilder();
stringBuilder.Append("ALTER TABLE ");
stringBuilder.Append(tableName);
stringBuilder.AppendLine(" WITH NOCHECK");
stringBuilder.Append("ADD CONSTRAINT ");
stringBuilder.AppendLine($"[{relationName}]");
stringBuilder.Append("FOREIGN KEY (");
string empty = string.Empty;
foreach (var column in constraint.Keys)
{
var childColumnName = ParserName.Parse(column.ColumnName).Quoted().ToString();
stringBuilder.Append($"{empty} {childColumnName}");
empty = ", ";
}
stringBuilder.AppendLine(" )");
stringBuilder.Append("REFERENCES ");
stringBuilder.Append(parentTableName).Append(" (");
empty = string.Empty;
foreach (var parentdColumn in constraint.ParentKeys)
{
var parentColumnName = ParserName.Parse(parentdColumn.ColumnName).Quoted().ToString();
stringBuilder.Append($"{empty} {parentColumnName}");
empty = ", ";
}
stringBuilder.Append(" ) ");
sqlCommand.CommandText = stringBuilder.ToString();
return(sqlCommand);
}
public static async Task <SyncTable> GetTableAsync(SqliteConnection connection, SqliteTransaction transaction, string tableName)
{
string command = "select * from sqlite_master where name = @tableName limit 1";
var tableNameNormalized = ParserName.Parse(tableName).Unquoted().Normalized().ToString();
var tableNameString = ParserName.Parse(tableName).ToString();
var syncTable = new SyncTable(tableNameNormalized);
using (var sqlCommand = new SqliteCommand(command, connection))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameString);
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
if (transaction != null)
{
sqlCommand.Transaction = transaction;
}
using (var reader = await sqlCommand.ExecuteReaderAsync().ConfigureAwait(false))
{
syncTable.Load(reader);
}
if (!alreadyOpened)
{
connection.Close();
}
}
return(syncTable);
}
public Task <DbCommand> GetAddColumnCommandAsync(string columnName, DbConnection connection, DbTransaction transaction)
{
var command = connection.CreateCommand();
command.Connection = connection;
command.Transaction = transaction;
var stringBuilder = new StringBuilder($"ALTER TABLE {this.tableName.Quoted().ToString()} ");
var column = this.tableDescription.Columns[columnName];
var columnNameString = ParserName.Parse(columnName, "`").Quoted().ToString();
var columnType = this.dbMetadata.GetCompatibleColumnTypeDeclarationString(column, this.tableDescription.OriginalProvider);
var identity = string.Empty;
if (column.IsAutoIncrement)
{
var s = column.GetAutoIncrementSeedAndStep();
if (s.Seed > 1 || s.Step > 1)
{
throw new NotSupportedException("can't establish a seed / step in MySql autoinc value");
}
identity = $"AUTO_INCREMENT";
}
var nullString = column.AllowDBNull ? "NULL" : "NOT NULL";
// if we have a readonly column, we may have a computed one, so we need to allow null
if (column.IsReadOnly)
{
nullString = "NULL";
}
stringBuilder.AppendLine($"ADD {columnNameString} {columnType} {identity} {nullString};");
command.CommandText = stringBuilder.ToString();
return(Task.FromResult(command));
}
private SqliteCommand BuildForeignKeyConstraintsCommand(DmRelation foreignKey)
{
SqliteCommand sqlCommand = new SqliteCommand();
var childTable = foreignKey.ChildTable;
var childTableName = ParserName.Parse(childTable.TableName).Quoted().ToString();
var parentTable = foreignKey.ParentTable;
var parentTableName = ParserName.Parse(parentTable.TableName).Quoted().ToString();
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("ALTER TABLE ");
stringBuilder.AppendLine(childTableName);
stringBuilder.Append("ADD CONSTRAINT ");
stringBuilder.AppendLine(foreignKey.RelationName);
stringBuilder.Append("FOREIGN KEY (");
string empty = string.Empty;
foreach (var childColumn in foreignKey.ChildColumns)
{
var childColumnName = ParserName.Parse(childColumn).Quoted().ToString();
stringBuilder.Append($"{empty} {childColumnName}");
}
stringBuilder.AppendLine(" )");
stringBuilder.Append("REFERENCES ");
stringBuilder.Append(parentTableName).Append(" (");
empty = string.Empty;
foreach (var parentdColumn in foreignKey.ParentColumns)
{
var parentColumnName = ParserName.Parse(parentdColumn).Quoted().ToString();
stringBuilder.Append($"{empty} {parentColumnName}");
empty = ", ";
}
stringBuilder.Append(" ) ");
sqlCommand.CommandText = stringBuilder.ToString();
return(sqlCommand);
}
private void SetUpdateRowParameters(DbCommand command)
{
DbParameter p;
var prefix_parameter = MySqlBuilderProcedure.MYSQL_PREFIX_PARAMETER;
foreach (var column in this.TableDescription.Columns.Where(c => !c.IsReadOnly))
{
var columnName = ParserName.Parse(column, "`").Unquoted().Normalized().ToString();
p = command.CreateParameter();
p.ParameterName = $"{prefix_parameter}{columnName}";
p.DbType = column.GetDbType();
p.SourceColumn = column.ColumnName;
command.Parameters.Add(p);
}
p = command.CreateParameter();
p.ParameterName = "sync_scope_id";
p.DbType = DbType.Guid;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "sync_force_write";
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "sync_min_timestamp";
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "sync_row_count";
p.DbType = DbType.Int32;
p.Direction = ParameterDirection.Output;
command.Parameters.Add(p);
}
private NpgsqlCommand BuildForeignKeyConstraintsCommand(SyncRelation constraint, DbConnection connection, DbTransaction transaction)
{
var tableName = ParserName.Parse(constraint.GetTable(), "\"").Quoted().Schema().ToString();
var parentTableName = ParserName.Parse(constraint.GetParentTable(), "\"").Quoted().Schema().ToString();
var relationName = NormalizeRelationName(constraint.RelationName);
var stringBuilder = new StringBuilder();
stringBuilder.Append("ALTER TABLE ");
stringBuilder.Append(tableName);
stringBuilder.Append("ADD CONSTRAINT ");
stringBuilder.AppendLine(relationName);
stringBuilder.Append("FOREIGN KEY (");
string empty = string.Empty;
foreach (var column in constraint.Keys)
{
var childColumnName = ParserName.Parse(column.ColumnName, "\"").Quoted().ToString();
stringBuilder.Append($"{empty} {childColumnName}");
empty = ", ";
}
stringBuilder.AppendLine(" )");
stringBuilder.Append("REFERENCES ");
stringBuilder.Append(parentTableName).Append(" (");
empty = string.Empty;
foreach (var parentdColumn in constraint.ParentKeys)
{
var parentColumnName = ParserName.Parse(parentdColumn.ColumnName, "\"").Quoted().ToString();
stringBuilder.Append($"{empty} {parentColumnName}");
empty = ", ";
}
stringBuilder.Append(" ) ");
var sqlCommand = new NpgsqlCommand(stringBuilder.ToString(), (NpgsqlConnection)connection, (NpgsqlTransaction)transaction);
return(sqlCommand);
}
internal static string JoinTwoTablesOnClause(IEnumerable <string> columns, string leftName, string rightName)
{
var stringBuilder = new StringBuilder();
string strRightName = (string.IsNullOrEmpty(rightName) ? string.Empty : string.Concat(rightName, "."));
string strLeftName = (string.IsNullOrEmpty(leftName) ? string.Empty : string.Concat(leftName, "."));
string str = "";
foreach (var column in columns)
{
var quotedColumn = ParserName.Parse(column).Quoted().ToString();
stringBuilder.Append(str);
stringBuilder.Append(strLeftName);
stringBuilder.Append(quotedColumn);
stringBuilder.Append(" = ");
stringBuilder.Append(strRightName);
stringBuilder.Append(quotedColumn);
str = " AND ";
}
return(stringBuilder.ToString());
}
internal static bool ProcedureExists(MySqlConnection connection, MySqlTransaction transaction, string commandName)
{
bool procExist;
var commandNameString = ParserName.Parse(commandName, "`");
using (MySqlCommand dbCommand = connection.CreateCommand())
{
dbCommand.CommandText = @"select count(*) from information_schema.ROUTINES
where ROUTINE_TYPE = 'PROCEDURE'
and ROUTINE_SCHEMA = schema()
and ROUTINE_NAME = @procName";
dbCommand.Parameters.AddWithValue("@procName", commandNameString.Unquoted().ToString());
if (transaction != null)
{
dbCommand.Transaction = transaction;
}
procExist = (long)dbCommand.ExecuteScalar() != 0L;
}
return(procExist);
}
/// <summary>
/// Get a table by its name
/// </summary>
public SetupTable this[string tableName]
{
get
{
if (string.IsNullOrEmpty(tableName))
throw new ArgumentNullException("tableName");
var parser = ParserName.Parse(tableName);
var tblName = parser.ObjectName;
var schemaName = parser.SchemaName;
schemaName = schemaName == null ? string.Empty : schemaName;
var sc = SyncGlobalization.DataSourceStringComparison;
var table = InnerCollection.FirstOrDefault(innerTable =>
{
var innerTableSchemaName = String.IsNullOrEmpty(innerTable.SchemaName) ? string.Empty : innerTable.SchemaName;
return string.Equals(innerTable.TableName, tblName, sc) && string.Equals(innerTableSchemaName, schemaName);
});
return table;
}
}
internal static string JoinOneTablesOnParametersValues(IEnumerable <string> columns, string leftName)
{
var stringBuilder = new StringBuilder();
string strLeftName = (string.IsNullOrEmpty(leftName) ? string.Empty : string.Concat(leftName, "."));
string str = "";
foreach (var column in columns)
{
var quotedColumn = ParserName.Parse(column).Quoted().ToString();
var unquotedColumn = ParserName.Parse(column).Unquoted().Normalized().ToString();
stringBuilder.Append(str);
stringBuilder.Append(strLeftName);
stringBuilder.Append(quotedColumn);
stringBuilder.Append(" = ");
stringBuilder.Append($"@{unquotedColumn}");
str = " AND ";
}
return(stringBuilder.ToString());
}
public string CreatePkCommandText()
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append($"ALTER TABLE {trackingName.Schema().Quoted().ToString()} ADD CONSTRAINT \"PK_{trackingName.Schema().Unquoted().Normalized().ToString()}\" PRIMARY KEY (");
var primaryKeysColumns = this.tableDescription.GetPrimaryKeysColumns().ToList();
for (int i = 0; i < primaryKeysColumns.Count; i++)
{
var pkColumn = primaryKeysColumns[i];
var quotedColumnName = ParserName.Parse(pkColumn, "\"").Quoted().ToString();
stringBuilder.Append(quotedColumnName);
if (i < primaryKeysColumns.Count - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.Append(")");
return(stringBuilder.ToString());
}
/// <summary>
/// Specify a table to add to the sync process
/// If you don't specify any columns, all columns in the data source will be imported
/// </summary>
public SetupTable(string tableName, string schemaName = null)
{
// Potentially user can pass something like [SalesLT].[Product]
// or SalesLT.Product or Product. ParserName will handle it
var parserTableName = ParserName.Parse(tableName);
tableName = parserTableName.ObjectName;
// Check Schema
if (string.IsNullOrEmpty(schemaName))
{
schemaName = string.IsNullOrEmpty(parserTableName.SchemaName) ? null : parserTableName.SchemaName;
}
else
{
var parserSchemaName = ParserName.Parse(schemaName);
schemaName = parserSchemaName.ObjectName;
}
this.TableName = tableName;
this.SchemaName = schemaName;
this.Columns = new SetupColumns();
}
public static SyncTable Table(MySqlConnection connection, MySqlTransaction transaction, string tableName)
{
string commandColumn = "select * from information_schema.TABLES where table_schema = schema() and table_name = @tableName limit 1;";
var tableNameParser = ParserName.Parse(tableName, "`");
var syncTable = new SyncTable(tableNameParser.Unquoted().ToString());
using (var sqlCommand = new MySqlCommand(commandColumn, connection))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameParser.Unquoted().ToString());
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
connection.Open();
}
if (transaction != null)
{
sqlCommand.Transaction = transaction;
}
using (var reader = sqlCommand.ExecuteReader())
{
syncTable.Load(reader);
}
if (!alreadyOpened)
{
connection.Close();
}
}
return(syncTable);
}
/// <summary>
/// Set the default stored procedures names
/// </summary>
private void SetDefaultNames()
{
var pref = this.Setup.StoredProceduresPrefix;
var suf = this.Setup.StoredProceduresSuffix;
var tpref = this.Setup.TriggersPrefix;
var tsuf = this.Setup.TriggersSuffix;
var tableName = ParserName.Parse(TableDescription);
var schema = string.IsNullOrEmpty(tableName.SchemaName) ? "dbo" : tableName.SchemaName;
this.AddStoredProcedureName(DbStoredProcedureType.SelectChanges, string.Format(selectChangesProcName, schema, $"{pref}{tableName.Unquoted().Normalized().ToString()}{suf}"));
this.AddStoredProcedureName(DbStoredProcedureType.SelectChangesWithFilters, string.Format(selectChangesProcNameWithFilters, schema, $"{pref}{tableName.Unquoted().Normalized().ToString()}{suf}", "{0}"));
this.AddStoredProcedureName(DbStoredProcedureType.SelectInitializedChanges, string.Format(initializeChangesProcName, schema, $"{pref}{tableName.Unquoted().Normalized().ToString()}{suf}"));
this.AddStoredProcedureName(DbStoredProcedureType.SelectInitializedChangesWithFilters, string.Format(initializeChangesProcNameWithFilters, schema, $"{pref}{tableName.Unquoted().Normalized().ToString()}{suf}", "{0}"));
this.AddStoredProcedureName(DbStoredProcedureType.SelectRow, string.Format(selectRowProcName, schema, $"{pref}{tableName.Unquoted().Normalized().ToString()}{suf}"));
this.AddStoredProcedureName(DbStoredProcedureType.UpdateRow, string.Format(updateProcName, schema, $"{pref}{tableName.Unquoted().Normalized().ToString()}{suf}"));
this.AddStoredProcedureName(DbStoredProcedureType.DeleteRow, string.Format(deleteProcName, schema, $"{pref}{tableName.Unquoted().Normalized().ToString()}{suf}"));
this.AddStoredProcedureName(DbStoredProcedureType.DeleteMetadata, string.Format(deleteMetadataProcName, schema, $"{pref}{tableName.Unquoted().Normalized().ToString()}{suf}"));
this.AddStoredProcedureName(DbStoredProcedureType.Reset, string.Format(resetMetadataProcName, schema, $"{pref}{tableName.Unquoted().Normalized().ToString()}{suf}"));
this.AddTriggerName(DbTriggerType.Insert, string.Format(insertTriggerName, schema, $"{tpref}{tableName.Unquoted().Normalized().ToString()}{tsuf}"));
this.AddTriggerName(DbTriggerType.Update, string.Format(updateTriggerName, schema, $"{tpref}{tableName.Unquoted().Normalized().ToString()}{tsuf}"));
this.AddTriggerName(DbTriggerType.Delete, string.Format(deleteTriggerName, schema, $"{tpref}{tableName.Unquoted().Normalized().ToString()}{tsuf}"));
this.AddStoredProcedureName(DbStoredProcedureType.BulkTableType, string.Format(bulkTableTypeName, schema, $"{pref}{tableName.Unquoted().Normalized().ToString()}{suf}"));
this.AddStoredProcedureName(DbStoredProcedureType.BulkUpdateRows, string.Format(bulkUpdateProcName, schema, $"{pref}{tableName.Unquoted().Normalized().ToString()}{suf}"));
this.AddStoredProcedureName(DbStoredProcedureType.BulkDeleteRows, string.Format(bulkDeleteProcName, schema, $"{pref}{tableName.Unquoted().Normalized().ToString()}{suf}"));
this.AddCommandName(DbCommandType.DisableConstraints, string.Format(disableConstraintsText, ParserName.Parse(TableDescription).Schema().Quoted().ToString()));
this.AddCommandName(DbCommandType.EnableConstraints, string.Format(enableConstraintsText, ParserName.Parse(TableDescription).Schema().Quoted().ToString()));
this.AddCommandName(DbCommandType.UpdateUntrackedRows, CreateUpdateUntrackedRowsCommand());
this.AddCommandName(DbCommandType.UpdateMetadata, CreateUpdateMetadataCommand());
}
internal static async Task <bool> ProcedureExistsAsync(MySqlConnection connection, MySqlTransaction transaction, string commandName)
{
bool procExist;
var commandNameString = ParserName.Parse(commandName, "`");
using (var dbCommand = connection.CreateCommand())
{
dbCommand.CommandText = @"select count(*) from information_schema.ROUTINES
where ROUTINE_TYPE = 'PROCEDURE'
and ROUTINE_SCHEMA = schema()
and ROUTINE_NAME = @procName";
dbCommand.Parameters.AddWithValue("@procName", commandNameString.Unquoted().ToString());
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
if (transaction != null)
{
dbCommand.Transaction = transaction;
}
procExist = ((long)await dbCommand.ExecuteScalarAsync().ConfigureAwait(false)) != 0L;
if (!alreadyOpened)
{
connection.Close();
}
}
return(procExist);
}
public virtual async Task CreateInsertTriggerAsync(DbConnection connection, DbTransaction transaction)
{
var insTriggerName = this.sqlObjectNames.GetCommandName(DbCommandType.InsertTrigger).name;
var insTriggerParseName = ParserName.Parse(insTriggerName, "\"");
var trigger = new StringBuilder();
trigger.AppendLine($"CREATE OR REPLACE FUNCTION {insTriggerName}()");
trigger.AppendLine($"RETURNS TRIGGER AS");
trigger.AppendLine($"$BODY$");
trigger.AppendLine($"BEGIN");
trigger.AppendLine(this.InsertTriggerBodyText());
trigger.AppendLine($"RETURN NULL;");
trigger.AppendLine($"END;");
trigger.AppendLine($"$BODY$");
trigger.AppendLine($"lANGUAGE 'plpgsql';");
trigger.AppendLine($"DROP TRIGGER IF EXISTS {insTriggerParseName.Quoted().ToString()} on {tableName.Schema().Quoted().ToString()};");
trigger.AppendLine($"CREATE TRIGGER {insTriggerParseName.Quoted().ToString()} AFTER INSERT ON {tableName.Schema().Quoted().ToString()}");
trigger.AppendLine($"FOR EACH ROW EXECUTE FUNCTION {insTriggerName}();");
trigger.AppendLine($"");
using (var command = new NpgsqlCommand(trigger.ToString(), (NpgsqlConnection)connection, (NpgsqlTransaction)transaction))
{
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
private SqlCommand BuildTableCommand()
{
StringBuilder stringBuilder = new StringBuilder($"CREATE TABLE {tableName.Schema().Quoted().ToString()} (");
string empty = string.Empty;
stringBuilder.AppendLine();
foreach (var column in this.tableDescription.Columns)
{
var columnName = ParserName.Parse(column).Quoted().ToString();
var columnTypeString = this.sqlDbMetadata.TryGetOwnerDbTypeString(column.OriginalDbType, column.DbType, false, false, column.MaxLength, this.tableDescription.OriginalProvider, SqlSyncProvider.ProviderType);
var columnPrecisionString = this.sqlDbMetadata.TryGetOwnerDbTypePrecision(column.OriginalDbType, column.DbType, false, false, column.MaxLength, column.Precision, column.Scale, this.tableDescription.OriginalProvider, SqlSyncProvider.ProviderType);
var columnType = $"{columnTypeString} {columnPrecisionString}";
var identity = string.Empty;
if (column.IsAutoIncrement)
{
var s = column.GetAutoIncrementSeedAndStep();
identity = $"IDENTITY({s.Step},{s.Seed})";
}
var nullString = column.AllowDBNull ? "NULL" : "NOT NULL";
// if we have a computed column, we should allow null
if (column.IsReadOnly)
{
nullString = "NULL";
}
stringBuilder.AppendLine($"\t{empty}{columnName} {columnType} {identity} {nullString}");
empty = ",";
}
stringBuilder.Append(")");
string createTableCommandString = stringBuilder.ToString();
return(new SqlCommand(createTableCommandString));
}
private void SetDeleteRowParameters(DbCommand command)
{
DbParameter p;
foreach (var column in this.TableDescription.PrimaryKey.Columns.Where(c => !c.IsReadOnly))
{
var unquotedColumn = ParserName.Parse(column).Normalized().Unquoted().ToString();
p = command.CreateParameter();
p.ParameterName = $"@{unquotedColumn}";
p.DbType = GetValidDbType(column.DbType);
p.SourceColumn = column.ColumnName;
command.Parameters.Add(p);
}
p = command.CreateParameter();
p.ParameterName = "@sync_force_write";
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_min_timestamp";
p.DbType = DbType.Int64;
command.Parameters.Add(p);
}
internal static async Task <SyncTable> GetPrimaryKeysForTableAsync(MySqlConnection connection, MySqlTransaction transaction, string tableName)
{
var commandColumn = @"select * from information_schema.COLUMNS where table_schema = schema() and table_name = @tableName and column_key='PRI'";
var tableNameParser = ParserName.Parse(tableName, "`");
var syncTable = new SyncTable(tableNameParser.Unquoted().ToString());
using (var sqlCommand = new MySqlCommand(commandColumn, connection))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameParser.Unquoted().ToString());
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
if (transaction != null)
{
sqlCommand.Transaction = transaction;
}
using (var reader = await sqlCommand.ExecuteReaderAsync().ConfigureAwait(false))
{
syncTable.Load(reader);
}
if (!alreadyOpened)
{
connection.Close();
}
}
return(syncTable);
}
private void SetUpdateMetadataParameters(DbCommand command)
{
DbParameter p;
foreach (var column in this.TableDescription.GetPrimaryKeysColumns().Where(c => !c.IsReadOnly))
{
var unquotedColumn = ParserName.Parse(column).Normalized().Unquoted().ToString();
p = command.CreateParameter();
p.ParameterName = $"@{unquotedColumn}";
p.DbType = GetValidDbType(column.GetDbType());
p.SourceColumn = column.ColumnName;
command.Parameters.Add(p);
}
p = command.CreateParameter();
p.ParameterName = "@sync_scope_id";
p.DbType = DbType.Guid;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_row_is_tombstone";
p.DbType = DbType.Boolean;
command.Parameters.Add(p);
}
public static async Task <SyncTable> GetColumnsForTableAsync(SqliteConnection connection, SqliteTransaction transaction, string tableName)
{
var tableNameParser = ParserName.Parse(tableName, "`");
var tableNameString = tableNameParser.Unquoted().ToString();
string commandColumn = $"SELECT * FROM pragma_table_info('{tableName}');";
var syncTable = new SyncTable(tableNameString);
using (var sqlCommand = new SqliteCommand(commandColumn, connection))
{
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
if (transaction != null)
{
sqlCommand.Transaction = transaction;
}
using (var reader = await sqlCommand.ExecuteReaderAsync().ConfigureAwait(false))
{
syncTable.Load(reader);
}
if (!alreadyOpened)
{
connection.Close();
}
}
return(syncTable);
}
internal static DmTable RelationsForTable(MySqlConnection connection, MySqlTransaction transaction, string tableName)
{
var commandRelations = @"
SELECT
ke.CONSTRAINT_NAME as ForeignKey,
ke.POSITION_IN_UNIQUE_CONSTRAINT as ForeignKeyOrder,
ke.referenced_table_name as TableName,
ke.REFERENCED_COLUMN_NAME as ColumnName,
ke.table_name ReferenceTableName,
ke.COLUMN_NAME ReferenceColumnName
FROM
information_schema.KEY_COLUMN_USAGE ke
WHERE
ke.referenced_table_name IS NOT NULL
and ke.REFERENCED_TABLE_SCHEMA = schema()
AND ke.REFERENCED_TABLE_NAME = @tableName
ORDER BY
ke.referenced_table_name;";
var tableNameParser = ParserName.Parse(tableName, "`");
var dmTable = new DmTable(tableNameParser.Unquoted().ToString());
using (MySqlCommand sqlCommand = new MySqlCommand(commandRelations, connection, transaction))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameParser.Unquoted().ToString());
using (var reader = sqlCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
}
return(dmTable);
}
