public string ScriptAddFilterColumn(DmColumn filterColumn)
{
var quotedColumnName = new ObjectNameParser(filterColumn.ColumnName.ToLowerInvariant(), "`", "`");
string str = string.Concat("Add new filter column, ", quotedColumnName.UnquotedString, ", to Tracking Table ", trackingName.QuotedString);
return(MySqlBuilder.WrapScriptTextWithComments(this.AddFilterColumnCommandText(filterColumn), str));
}
public string ScriptAddFilterColumn(DmColumn filterColumn)
{
var quotedColumnName = new ObjectNameParser(filterColumn.ColumnName, "[", "]");
string str = string.Concat("Add new filter column, ", quotedColumnName.FullUnquotedString, ", to Tracking Table ", trackingName.FullQuotedString);
return(SqliteBuilder.WrapScriptTextWithComments(this.AddFilterColumnCommandText(filterColumn), str));
}
internal static (ObjectNameParser tableName, ObjectNameParser trackingName) GetParsers(DmTable tableDescription)
{
string tableAndPrefixName = String.IsNullOrWhiteSpace(tableDescription.Prefix) ? tableDescription.TableName : $"{tableDescription.Prefix}.{tableDescription.TableName}";
var originalTableName = new ObjectNameParser(tableAndPrefixName, "[", "]");
var trackingTableName = new ObjectNameParser($"{tableAndPrefixName}_tracking", "[", "]");
return(originalTableName, trackingTableName);
}
internal static (ObjectNameParser tableName, ObjectNameParser trackingName) GetParsers(DmTable tableDescription)
{
string tableAndPrefixName = tableDescription.TableName;
var originalTableName = new ObjectNameParser(tableAndPrefixName.ToLowerInvariant(), "`", "`");
var trackingTableName = new ObjectNameParser($"{tableAndPrefixName.ToLowerInvariant()}_tracking", "`", "`");
return(originalTableName, trackingTableName);
}
public string ScriptAddFilterColumn(DmColumn filterColumn)
{
var quotedColumnName = new ObjectNameParser(filterColumn.ColumnName, "\"", "\"");
var str = string.Concat("Add new filter column, ", quotedColumnName.UnquotedString, ", to Tracking Table ",
_trackingName.QuotedString);
return(PostgreSqlBuilder.WrapScriptTextWithComments(AddFilterColumnCommandText(filterColumn), str));
}
private string AddFilterColumnCommandText(DmColumn col)
{
var quotedColumnName = new ObjectNameParser(col.ColumnName, "[", "]").QuotedString;
var quotedColumnType = new ObjectNameParser(col.GetSqlDbTypeString(), "[", "]").QuotedString;
quotedColumnType += col.GetSqlTypePrecisionString();
return(string.Concat("ALTER TABLE ", quotedColumnName, " ADD ", quotedColumnType));
}
public static string GetUnquotedSqlSchemaName(ObjectNameParser parser)
{
if (string.IsNullOrEmpty(parser.SchemaName))
{
return("dbo");
}
return(parser.SchemaName);
}
internal static MySqlParameter[] DeriveParameters(this MySqlConnection connection, MySqlCommand cmd, bool includeReturnValueParameter = false, MySqlTransaction transaction = null)
{
if (cmd == null)
{
throw new ArgumentNullException("SqlCommand");
}
var textParser = new ObjectNameParser(cmd.CommandText);
// Hack to check for schema name in the spName
string schemaName = "dbo";
string spName = textParser.UnquotedString;
int firstDot = spName.IndexOf('.');
if (firstDot > 0)
{
schemaName = cmd.CommandText.Substring(0, firstDot);
spName = spName.Substring(firstDot + 1);
}
var alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
connection.Open();
}
try
{
MySqlCommandBuilder.DeriveParameters(cmd);
}
finally
{
if (!alreadyOpened)
{
connection.Close();
}
}
if (!includeReturnValueParameter && cmd.Parameters.Count > 0)
{
cmd.Parameters.RemoveAt(0);
}
MySqlParameter[] discoveredParameters = new MySqlParameter[cmd.Parameters.Count];
cmd.Parameters.CopyTo(discoveredParameters, 0);
// Init the parameters with a DBNull value
foreach (MySqlParameter discoveredParameter in discoveredParameters)
{
discoveredParameter.Value = DBNull.Value;
}
return(discoveredParameters);
}
private string AddFilterColumnCommandText(DmColumn col)
{
var quotedColumnName = new ObjectNameParser(col.ColumnName, "`", "`").FullQuotedString;
var columnTypeString = this.mySqlDbMetadata.TryGetOwnerDbTypeString(col.OriginalDbType, col.DbType, false, false, col.MaxLength, this.tableDescription.OriginalProvider, MySqlSyncProvider.ProviderType);
var columnPrecisionString = this.mySqlDbMetadata.TryGetOwnerDbTypePrecision(col.OriginalDbType, col.DbType, false, false, col.MaxLength, col.Precision, col.Scale, this.tableDescription.OriginalProvider, MySqlSyncProvider.ProviderType);
var columnType = $"{columnTypeString} {columnPrecisionString}";
return(string.Concat("ALTER TABLE ", quotedColumnName, " ADD ", columnType));
}
//------------------------------------------------------------------
// Select Row command
//------------------------------------------------------------------
private MySqlCommand BuildSelectRowCommand()
{
MySqlCommand sqlCommand = new MySqlCommand();
this.AddPkColumnParametersToCommand(sqlCommand);
MySqlParameter sqlParameter = new MySqlParameter();
sqlParameter.ParameterName = "sync_scope_id";
sqlParameter.MySqlDbType = MySqlDbType.Guid;
sqlParameter.Size = 36;
sqlCommand.Parameters.Add(sqlParameter);
StringBuilder stringBuilder = new StringBuilder("SELECT ");
stringBuilder.AppendLine();
StringBuilder stringBuilder1 = new StringBuilder();
string empty = string.Empty;
foreach (var pkColumn in this.tableDescription.PrimaryKey.Columns)
{
ObjectNameParser pkColumnName = new ObjectNameParser(pkColumn.ColumnName, "`", "`");
stringBuilder.AppendLine($"\t`side`.{pkColumnName.FullQuotedString}, ");
stringBuilder1.Append($"{empty}`side`.{pkColumnName.FullQuotedString} = {MYSQL_PREFIX_PARAMETER}{pkColumnName.FullUnquotedString}");
empty = " AND ";
}
foreach (DmColumn mutableColumn in this.tableDescription.MutableColumns)
{
ObjectNameParser nonPkColumnName = new ObjectNameParser(mutableColumn.ColumnName, "`", "`");
stringBuilder.AppendLine($"\t`base`.{nonPkColumnName.FullQuotedString}, ");
}
stringBuilder.AppendLine("\t`side`.`sync_row_is_tombstone`,");
stringBuilder.AppendLine("\t`side`.`create_scope_id`,");
stringBuilder.AppendLine("\t`side`.`create_timestamp`,");
stringBuilder.AppendLine("\t`side`.`update_scope_id`,");
stringBuilder.AppendLine("\t`side`.`update_timestamp`");
stringBuilder.AppendLine($"FROM {tableName.FullQuotedString} `base`");
stringBuilder.AppendLine($"RIGHT JOIN {trackingName.FullQuotedString} `side` ON");
string str = string.Empty;
foreach (var pkColumn in this.tableDescription.PrimaryKey.Columns)
{
ObjectNameParser pkColumnName = new ObjectNameParser(pkColumn.ColumnName, "`", "`");
stringBuilder.Append($"{str}`base`.{pkColumnName.FullQuotedString} = `side`.{pkColumnName.FullQuotedString}");
str = " AND ";
}
stringBuilder.AppendLine();
stringBuilder.Append("WHERE ");
stringBuilder.Append(stringBuilder1.ToString());
stringBuilder.Append(";");
sqlCommand.CommandText = stringBuilder.ToString();
return(sqlCommand);
}
//------------------------------------------------------------------
// Update command
//------------------------------------------------------------------
private Tuple <NpgsqlCommand, string> BuildUpdateCommand()
{
var sqlCommand = new NpgsqlCommand();
var stringBuilder = new StringBuilder();
AddColumnParametersToCommand(sqlCommand);
var sqlParameter = new NpgsqlParameter();
sqlParameter.ParameterName = "sync_force_write";
sqlParameter.NpgsqlDbType = NpgsqlDbType.Integer;
sqlCommand.Parameters.Add(sqlParameter);
var sqlParameter1 = new NpgsqlParameter();
sqlParameter1.ParameterName = "sync_min_timestamp";
sqlParameter1.NpgsqlDbType = NpgsqlDbType.Bigint;
sqlCommand.Parameters.Add(sqlParameter1);
var whereArgs = new List <string>();
foreach (var pkColumn in _tableDescription.PrimaryKey.Columns)
{
var pk = new ObjectNameParser(pkColumn.ColumnName, "\"", "\"");
whereArgs.Add(
$"{pk.QuotedObjectName} = {PgsqlPrefixParameter}{pk.UnquotedString.ToLowerInvariant()}");
}
stringBuilder.AppendLine("ts := 0;");
stringBuilder.AppendLine(
$"SELECT \"timestamp\" FROM {_trackingName.QuotedObjectName} WHERE {string.Join(" AND ", whereArgs.Select(a => $"{_trackingName.QuotedObjectName}.{a}"))} LIMIT 1 INTO ts;");
stringBuilder.AppendLine($"UPDATE {_tableName.QuotedString}");
stringBuilder.Append(
$"SET {PostgreSqlManagementUtils.CommaSeparatedUpdateFromParameters(_tableDescription)}");
stringBuilder.Append($"WHERE {string.Join(" AND ", whereArgs)}");
stringBuilder.AppendLine($" AND (ts <= sync_min_timestamp OR sync_force_write = 1);");
stringBuilder.AppendLine("GET DIAGNOSTICS rows = ROW_COUNT;");
stringBuilder.AppendLine("RETURN rows;");
stringBuilder.AppendLine();
// Can't rely on rows count since MySql will return 0 if an update don't update any columns
//stringBuilder.AppendLine($"/* Since the update 'could' potentially returns 0 as row affected count when we make a double update with the same values, to be sure, make a fake update on metadatas time column */
//stringBuilder.AppendLine($"UPDATE {trackingName.QuotedObjectName} ");
//stringBuilder.AppendLine($"SET \"timestamp\" = {MySqlObjectNames.TimestampValue}");
//stringBuilder.AppendLine($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.PrimaryKey.Columns, "")} AND (ts <= sync_min_timestamp OR sync_force_write = 1);");
sqlCommand.CommandText = stringBuilder.ToString();
return(Tuple.Create(sqlCommand, "integer"));
}
//------------------------------------------------------------------
// Insert Metadata command
//------------------------------------------------------------------
private MySqlCommand BuildInsertMetadataCommand()
{
StringBuilder stringBuilderArguments = new StringBuilder();
StringBuilder stringBuilderParameters = new StringBuilder();
MySqlCommand sqlCommand = new MySqlCommand();
StringBuilder stringBuilder = new StringBuilder();
this.AddPkColumnParametersToCommand(sqlCommand);
MySqlParameter sqlParameter = new MySqlParameter("sync_scope_id", MySqlDbType.Guid);
sqlParameter.Size = 36;
sqlCommand.Parameters.Add(sqlParameter);
MySqlParameter sqlParameter1 = new MySqlParameter("sync_row_is_tombstone", MySqlDbType.Int32);
sqlCommand.Parameters.Add(sqlParameter1);
MySqlParameter sqlParameter3 = new MySqlParameter("create_timestamp", MySqlDbType.Int64);
sqlCommand.Parameters.Add(sqlParameter3);
MySqlParameter sqlParameter4 = new MySqlParameter("update_timestamp", MySqlDbType.Int64);
sqlCommand.Parameters.Add(sqlParameter4);
stringBuilder.AppendLine($"\tINSERT INTO {trackingName.QuotedString}");
string empty = string.Empty;
foreach (var pkColumn in this.tableDescription.PrimaryKey.Columns)
{
ObjectNameParser columnName = new ObjectNameParser(pkColumn.ColumnName, "`", "`");
stringBuilderArguments.Append(string.Concat(empty, columnName.QuotedString.ToLowerInvariant()));
stringBuilderParameters.Append(string.Concat(empty, $"in{columnName.UnquotedString.ToLowerInvariant()}"));
empty = ", ";
}
stringBuilder.Append($"\t({stringBuilderArguments.ToString()}, ");
stringBuilder.AppendLine($"\t`create_scope_id`, `create_timestamp`, `update_scope_id`, `update_timestamp`,");
stringBuilder.AppendLine($"\t`sync_row_is_tombstone`, `timestamp`, `last_change_datetime`)");
stringBuilder.Append($"\tVALUES ({stringBuilderParameters.ToString()}, ");
stringBuilder.AppendLine($"\tcreate_scope_id, create_timestamp, update_scope_id, update_timestamp, ");
stringBuilder.AppendLine($"\tsync_row_is_tombstone, {MySqlObjectNames.TimestampValue}, now())");
stringBuilder.AppendLine($"\tON DUPLICATE KEY UPDATE");
stringBuilder.AppendLine($"\t `create_scope_id` = create_scope_id, ");
stringBuilder.AppendLine($"\t `create_timestamp` = create_timestamp, ");
stringBuilder.AppendLine($"\t `update_scope_id` = update_scope_id, ");
stringBuilder.AppendLine($"\t `update_timestamp` = update_timestamp, ");
stringBuilder.AppendLine($"\t `sync_row_is_tombstone` = sync_row_is_tombstone, ");
stringBuilder.AppendLine($"\t `timestamp` = {MySqlObjectNames.TimestampValue}, ");
stringBuilder.AppendLine($"\t `last_change_datetime` = now(); ");
stringBuilder.AppendLine();
sqlCommand.CommandText = stringBuilder.ToString();
return(sqlCommand);
}
public static void DropTriggerIfExists(SqlConnection connection, SqlTransaction transaction, int commandTimeout, string quotedTriggerName)
{
ObjectNameParser objectNameParser = new ObjectNameParser(quotedTriggerName);
using (SqlCommand sqlCommand = new SqlCommand(string.Format(CultureInfo.InvariantCulture, "IF EXISTS (SELECT tr.name FROM sys.triggers tr JOIN sys.tables t ON tr.parent_id = t.object_id JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE tr.name = @triggerName and s.name = @schemaName) DROP TRIGGER {0}", quotedTriggerName), connection, transaction))
{
sqlCommand.CommandTimeout = commandTimeout;
sqlCommand.Parameters.AddWithValue("@triggerName", objectNameParser.ObjectName);
sqlCommand.Parameters.AddWithValue("@schemaName", SqlManagementUtils.GetUnquotedSqlSchemaName(objectNameParser));
sqlCommand.ExecuteNonQuery();
}
}
public static void DropProcedureIfExists(SqlConnection connection, SqlTransaction transaction, int commandTimout, string quotedProcedureName)
{
ObjectNameParser objectNameParser = new ObjectNameParser(quotedProcedureName);
using (SqlCommand sqlCommand = new SqlCommand(string.Format(CultureInfo.InvariantCulture, "IF EXISTS (SELECT * FROM sys.procedures p JOIN sys.schemas s ON s.schema_id = p.schema_id WHERE p.name = @procName AND s.name = @schemaName) DROP PROCEDURE {0}", quotedProcedureName), connection, transaction))
{
sqlCommand.CommandTimeout = commandTimout;
sqlCommand.Parameters.AddWithValue("@procName", objectNameParser.ObjectName);
sqlCommand.Parameters.AddWithValue("@schemaName", SqlManagementUtils.GetUnquotedSqlSchemaName(objectNameParser));
sqlCommand.ExecuteNonQuery();
}
}
private void SetInsertRowParameters(DbCommand command)
{
DbParameter p;
foreach (DmColumn column in this.TableDescription.Columns.Where(c => !c.IsReadOnly))
{
ObjectNameParser quotedColumn = new ObjectNameParser(column.ColumnName);
p = command.CreateParameter();
p.ParameterName = $"{MySqlBuilderProcedure.MYSQL_PREFIX_PARAMETER}{quotedColumn.ObjectNameNormalized}";
p.DbType = column.DbType;
p.SourceColumn = column.ColumnName;
command.Parameters.Add(p);
}
}
internal static string CommaSeparatedUpdateFromParameters(DmTable table, string fromPrefix = "")
{
StringBuilder stringBuilder = new StringBuilder();
string strFromPrefix = (string.IsNullOrEmpty(fromPrefix) ? string.Empty : string.Concat(fromPrefix, "."));
string strSeparator = "";
foreach (DmColumn mutableColumn in table.MutableColumns)
{
ObjectNameParser quotedColumn = new ObjectNameParser(mutableColumn.ColumnName, "`", "`");
stringBuilder.AppendLine($"{strSeparator} {strFromPrefix}{quotedColumn.FullQuotedString} = {MySqlBuilderProcedure.MYSQL_PREFIX_PARAMETER}{quotedColumn.FullUnquotedString}");
strSeparator = ", ";
}
return(stringBuilder.ToString());
}
private void SetInsertRowParameters(DbCommand command)
{
DbParameter p;
foreach (DmColumn column in this.TableDescription.Columns.Where(c => !c.IsReadOnly))
{
ObjectNameParser quotedColumn = new ObjectNameParser(column.ColumnName);
p = command.CreateParameter();
p.ParameterName = $"@{quotedColumn.ObjectNameNormalized}";
p.DbType = GetValidDbType(column.DbType);
p.SourceColumn = column.ColumnName;
command.Parameters.Add(p);
}
}
internal static object CommaSeparatedUpdateFromParametersStoredProcedure(DmTable table, string fromPrefix = "")
{
StringBuilder stringBuilder = new StringBuilder();
string strFromPrefix = (string.IsNullOrEmpty(fromPrefix) ? string.Empty : string.Concat(fromPrefix, "."));
string strSeparator = "";
foreach (DmColumn column in table.NonPkColumns.Where(c => !c.ReadOnly))
{
ObjectNameParser quotedColumn = new ObjectNameParser(column.ColumnName);
stringBuilder.AppendLine($"{strSeparator} {strFromPrefix}{quotedColumn.UnquotedString} = {quotedColumn.UnquotedString}0");
strSeparator = ", ";
}
return(stringBuilder.ToString());
}
private void SetInsertRowParameters(DbCommand command)
{
DbParameter p;
foreach (DmColumn column in this.TableDescription.Columns.Where(c => !c.ReadOnly))
{
ObjectNameParser quotedColumn = new ObjectNameParser(column.ColumnName);
p = command.CreateParameter();
p.ParameterName = $"@{quotedColumn.UnquotedStringWithUnderScore}";
p.DbType = column.DbType;
p.SourceColumn = column.ColumnName;
command.Parameters.Add(p);
}
}
internal static string CommaSeparatedUpdateFromParameters(DmTable table, string fromPrefix = "")
{
StringBuilder stringBuilder = new StringBuilder();
string strFromPrefix = (string.IsNullOrEmpty(fromPrefix) ? string.Empty : string.Concat(fromPrefix, "."));
string strSeparator = "";
foreach (DmColumn mutableColumn in table.MutableColumnsAndNotAutoInc)
{
ObjectNameParser quotedColumn = new ObjectNameParser(mutableColumn.ColumnName);
stringBuilder.AppendLine($"{strSeparator} {strFromPrefix}{quotedColumn.FullQuotedString} = @{quotedColumn.FullUnquotedString}");
strSeparator = ", ";
}
return(stringBuilder.ToString());
}
public static void DropTriggerIfExists(MySqlConnection connection, MySqlTransaction transaction, string quotedTriggerName)
{
ObjectNameParser objectNameParser = new ObjectNameParser(quotedTriggerName, "`", "`");
using (DbCommand dbCommand = connection.CreateCommand())
{
dbCommand.CommandText = $"drop trigger {objectNameParser.ObjectName}";
if (transaction != null)
{
dbCommand.Transaction = transaction;
}
dbCommand.ExecuteNonQuery();
}
}
public static void DropTableIfExists(SqliteConnection connection, SqliteTransaction transaction, string quotedTableName)
{
ObjectNameParser objectNameParser = new ObjectNameParser(quotedTableName);
using (DbCommand dbCommand = connection.CreateCommand())
{
dbCommand.CommandText = $"drop table if exist {objectNameParser.ObjectName}";
if (transaction != null)
{
dbCommand.Transaction = transaction;
}
dbCommand.ExecuteNonQuery();
}
}
internal static DmTable RelationsForTable(NpgsqlConnection connection, NpgsqlTransaction transaction, string tableName)
{
var commandRelations = @"select
con.constraint_name as ""foreignKey"",
att2.attname as ""columnName"",
cl.relname as ""referenceTableName"",
att.attname as ""referenceColumnName"",
con.child_table as ""tableName"",
con.child_schema
from
(select
unnest(con1.conkey) as ""parent"",
unnest(con1.confkey) as ""child"",
con1.conname as constraint_name,
con1.confrelid,
con1.conrelid,
cl.relname as child_table,
ns.nspname as child_schema
from
pg_class cl
join pg_namespace ns on cl.relnamespace = ns.oid
join pg_constraint con1 on con1.conrelid = cl.oid
where con1.contype = 'f'
) con
join pg_attribute att on
att.attrelid = con.confrelid and att.attnum = con.child
join pg_class cl on
cl.oid = con.confrelid
join pg_attribute att2 on
att2.attrelid = con.conrelid and att2.attnum = con.parent
WHERE con.child_table = @tableName AND con.child_schema = current_schema()";
ObjectNameParser tableNameParser = new ObjectNameParser(tableName, "\"", "\"");
DmTable dmTable = new DmTable(tableNameParser.UnquotedStringWithUnderScore);
using (var sqlCommand = new NpgsqlCommand(commandRelations, connection, transaction))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameParser.ObjectName);
using (var reader = sqlCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
}
return(dmTable);
}
public string CreateTableCommandText()
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"CREATE TABLE {trackingName.QuotedString} (");
// Adding the primary key
foreach (DmColumn pkColumn in TableDescription.PrimaryKey.Columns)
{
var quotedColumnName = new ObjectNameParser(pkColumn.ColumnName, "[", "]").QuotedString;
var quotedColumnType = new ObjectNameParser(pkColumn.GetSqlDbTypeString(), "[", "]").QuotedString;
quotedColumnType += pkColumn.GetSqlTypePrecisionString();
var nullableColumn = pkColumn.AllowDBNull ? "NULL" : "NOT NULL";
stringBuilder.AppendLine($"{quotedColumnName} {quotedColumnType} {nullableColumn}, ");
}
// adding the tracking columns
stringBuilder.AppendLine($"[create_scope_id] [uniqueidentifier] NULL, ");
stringBuilder.AppendLine($"[update_scope_id] [uniqueidentifier] NULL, ");
stringBuilder.AppendLine($"[create_timestamp] [bigint] NULL, ");
stringBuilder.AppendLine($"[update_timestamp] [bigint] NULL, ");
stringBuilder.AppendLine($"[timestamp] [timestamp] NULL, ");
stringBuilder.AppendLine($"[sync_row_is_tombstone] [bit] NOT NULL default(0), ");
stringBuilder.AppendLine($"[last_change_datetime] [datetime] NULL, ");
// adding the filter columns
if (this.FilterColumns != null)
{
foreach (DmColumn filterColumn in this.FilterColumns)
{
var isPk = TableDescription.PrimaryKey.Columns.Any(dm => TableDescription.IsEqual(dm.ColumnName, filterColumn.ColumnName));
if (isPk)
{
continue;
}
var quotedColumnName = new ObjectNameParser(filterColumn.ColumnName, "[", "]").QuotedString;
var quotedColumnType = new ObjectNameParser(filterColumn.GetSqlDbTypeString(), "[", "]").QuotedString;
quotedColumnType += filterColumn.GetSqlTypePrecisionString();
var nullableColumn = filterColumn.AllowDBNull ? "NULL" : "NOT NULL";
stringBuilder.AppendLine($"{quotedColumnName} {quotedColumnType} {nullableColumn}, ");
}
}
stringBuilder.Append(")");
return(stringBuilder.ToString());
}
private SqlCommand BuildForeignKeyConstraintsCommand(DmRelation foreignKey)
{
var sqlCommand = new SqlCommand();
string childTable = foreignKey.ChildTable.TableName;
string childSchema = foreignKey.ChildTable.Schema;
string childFullName = String.IsNullOrEmpty(childSchema) ? childTable : $"{childSchema}.{childTable}";
var childTableName = new ObjectNameParser(childFullName);
string parentTable = foreignKey.ParentTable.TableName;
string parentSchema = foreignKey.ParentTable.Schema;
string parentFullName = String.IsNullOrEmpty(parentSchema) ? parentTable : $"{parentSchema}.{parentTable}";
var parentTableName = new ObjectNameParser(parentFullName);
var relationName = NormalizeRelationName(foreignKey.RelationName);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("ALTER TABLE ");
stringBuilder.AppendLine(childTableName.FullQuotedString);
stringBuilder.Append("ADD CONSTRAINT ");
stringBuilder.AppendLine(relationName);
stringBuilder.Append("FOREIGN KEY (");
string empty = string.Empty;
foreach (var childColumn in foreignKey.ChildColumns)
{
var childColumnName = new ObjectNameParser(childColumn.ColumnName);
stringBuilder.Append($"{empty} {childColumnName.FullQuotedString}");
empty = ", ";
}
stringBuilder.AppendLine(" )");
stringBuilder.Append("REFERENCES ");
stringBuilder.Append(parentTableName.FullQuotedString).Append(" (");
empty = string.Empty;
foreach (var parentdColumn in foreignKey.ParentColumns)
{
var parentColumnName = new ObjectNameParser(parentdColumn.ColumnName);
stringBuilder.Append($"{empty} {parentColumnName.FullQuotedString}");
empty = ", ";
}
stringBuilder.Append(" ) ");
sqlCommand.CommandText = stringBuilder.ToString();
return(sqlCommand);
}
public static bool ProcedureExists(SqlConnection connection, SqlTransaction transaction, string quotedProcedureName)
{
bool flag;
ObjectNameParser objectNameParser = new ObjectNameParser(quotedProcedureName);
using (SqlCommand sqlCommand = new SqlCommand("IF EXISTS (SELECT * FROM sys.procedures p JOIN sys.schemas s ON s.schema_id = p.schema_id WHERE p.name = @procName AND s.name = @schemaName) SELECT 1 ELSE SELECT 0", connection))
{
sqlCommand.Parameters.AddWithValue("@procName", objectNameParser.ObjectName);
sqlCommand.Parameters.AddWithValue("@schemaName", SqlManagementUtils.GetUnquotedSqlSchemaName(objectNameParser));
if (transaction != null)
{
sqlCommand.Transaction = transaction;
}
flag = (int)sqlCommand.ExecuteScalar() != 0;
}
return(flag);
}
private NpgsqlCommand BuildForeignKeyConstraintsCommand(DmRelation foreignKey)
{
var sqlCommand = new NpgsqlCommand();
var childTable = foreignKey.ChildTable;
var childTableName = new ObjectNameParser(childTable.TableName.ToLowerInvariant(), "\"", "\"");
var parentTable = foreignKey.ParentTable;
var parentTableName = new ObjectNameParser(parentTable.TableName.ToLowerInvariant(), "\"", "\"");
var relationName = foreignKey.RelationName.Length > 50
? foreignKey.RelationName.Substring(0, 50)
: foreignKey.RelationName;
var stringBuilder = new StringBuilder();
stringBuilder.Append("ALTER TABLE ");
stringBuilder.AppendLine(parentTableName.QuotedString);
stringBuilder.Append("ADD CONSTRAINT ");
stringBuilder.AppendLine(relationName);
stringBuilder.Append("FOREIGN KEY (");
var empty = string.Empty;
foreach (var parentdColumn in foreignKey.ParentColumns)
{
var parentColumnName = new ObjectNameParser(parentdColumn.ColumnName.ToLowerInvariant(), "\"", "\"");
stringBuilder.Append($"{empty} {parentColumnName.QuotedString}");
empty = ", ";
}
stringBuilder.AppendLine(" )");
stringBuilder.Append("REFERENCES ");
stringBuilder.Append(childTableName.QuotedString).Append(" (");
empty = string.Empty;
foreach (var childColumn in foreignKey.ChildColumns)
{
var childColumnName = new ObjectNameParser(childColumn.ColumnName.ToLowerInvariant(), "\"", "\"");
stringBuilder.Append($"{empty} {childColumnName.QuotedString}");
}
stringBuilder.Append(" ) ");
sqlCommand.CommandText = stringBuilder.ToString();
return(sqlCommand);
}
private void SetInsertMetadataParameters(DbCommand command)
{
DbParameter p;
foreach (var column in TableDescription.PrimaryKey.Columns.Where(c => !c.ReadOnly))
{
var quotedColumn = new ObjectNameParser(column.ColumnName);
p = command.CreateParameter();
p.ParameterName = $"@in_{quotedColumn.UnquotedStringWithUnderScore.ToLowerInvariant()}";
p.DbType = column.DbType;
p.SourceColumn = column.ColumnName;
command.Parameters.Add(p);
}
/*
* p = command.CreateParameter();
* p.ParameterName = "@create_scope_id";
* p.DbType = DbType.Guid;
* command.Parameters.Add(p);
*
* p = command.CreateParameter();
* p.ParameterName = "@update_scope_id";
* p.DbType = DbType.Guid;
* 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.Int32;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@create_timestamp";
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@update_timestamp";
p.DbType = DbType.Int64;
command.Parameters.Add(p);
}
private MySqlCommand BuildForeignKeyConstraintsCommand(DmRelation foreignKey)
{
var sqlCommand = new MySqlCommand();
var childTable = foreignKey.ChildTable;
var childTableName = new ObjectNameParser(childTable.TableName, "`", "`");
var parentTable = foreignKey.ParentTable;
var parentTableName = new ObjectNameParser(parentTable.TableName, "`", "`");;
var relationName = NormalizeRelationName(foreignKey.RelationName);
DmColumn[] foreignKeyColumns = foreignKey.ChildColumns;
DmColumn[] referencesColumns = foreignKey.ParentColumns;
var stringBuilder = new StringBuilder();
stringBuilder.Append("ALTER TABLE ");
stringBuilder.AppendLine(childTableName.FullQuotedString);
stringBuilder.Append("ADD CONSTRAINT ");
stringBuilder.AppendLine($"`{relationName}`");
stringBuilder.Append("FOREIGN KEY (");
string empty = string.Empty;
foreach (var foreignKeyColumn in foreignKeyColumns)
{
var foreignKeyColumnName = new ObjectNameParser(foreignKeyColumn.ColumnName, "`", "`");
stringBuilder.Append($"{empty} {foreignKeyColumnName.FullQuotedString}");
empty = ", ";
}
stringBuilder.AppendLine(" )");
stringBuilder.Append("REFERENCES ");
stringBuilder.Append(parentTableName.FullQuotedString).Append(" (");
empty = string.Empty;
foreach (var referencesColumn in referencesColumns)
{
var referencesColumnName = new ObjectNameParser(referencesColumn.ColumnName, "`", "`");
stringBuilder.Append($"{empty} {referencesColumnName.FullQuotedString}");
empty = ", ";
}
stringBuilder.Append(" ) ");
sqlCommand.CommandText = stringBuilder.ToString();
return(sqlCommand);
}
public static void DropTableIfExists(MySqlConnection connection, MySqlTransaction transaction, string quotedTableName)
{
ObjectNameParser objectNameParser = new ObjectNameParser(quotedTableName, "`", "`");
using (MySqlCommand dbCommand = connection.CreateCommand())
{
dbCommand.CommandText = $"select * from information_schema.TABLES where table_schema = schema() and table_name = @tableName";
dbCommand.Parameters.AddWithValue("@tableName", objectNameParser.ObjectName);
if (transaction != null)
{
dbCommand.Transaction = transaction;
}
dbCommand.ExecuteNonQuery();
}
}
