public bool NeedToCreateForeignKeyConstraints(DmRelation foreignKey)
{
var parentTable = foreignKey.ParentTable.TableName;
var parentSchema = foreignKey.ParentTable.Schema;
var parentFullName = string.IsNullOrEmpty(parentSchema) ? parentTable : $"{parentSchema}.{parentTable}";
var alreadyOpened = _connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
_connection.Open();
}
var dmTable = PostgreSqlManagementUtils.RelationsForTable(_connection, _transaction, parentFullName);
var foreignKeyExist = dmTable.Rows.Any(r =>
dmTable.IsEqual(r["ForeignKey"].ToString(), foreignKey.RelationName));
return(!foreignKeyExist);
}
catch (Exception ex)
{
Debug.WriteLine($"Error during checking foreign keys: {ex}");
throw;
}
finally
{
if (!alreadyOpened && _connection.State != ConnectionState.Closed)
{
_connection.Close();
}
}
}
//------------------------------------------------------------------
// Delete Metadata command
//------------------------------------------------------------------
private Tuple <NpgsqlCommand, string> BuildDeleteMetadataCommand()
{
var sqlCommand = new NpgsqlCommand();
AddPkColumnParametersToCommand(sqlCommand);
var sqlParameter = new NpgsqlParameter();
sqlParameter.ParameterName = "sync_check_concurrency";
sqlParameter.NpgsqlDbType = NpgsqlDbType.Integer;
sqlCommand.Parameters.Add(sqlParameter);
var sqlParameter1 = new NpgsqlParameter();
sqlParameter1.ParameterName = "sync_row_timestamp";
sqlParameter1.NpgsqlDbType = NpgsqlDbType.Bigint;
sqlCommand.Parameters.Add(sqlParameter1);
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
stringBuilder.AppendLine($"DELETE FROM {_trackingName.QuotedString} ");
stringBuilder.Append($"WHERE ");
stringBuilder.AppendLine(
PostgreSqlManagementUtils.ColumnsAndParameters(_tableDescription.PrimaryKey.Columns, ""));
stringBuilder.Append(";");
sqlCommand.CommandText = stringBuilder.ToString();
return(Tuple.Create(sqlCommand, "void"));
}
public bool NeedToCreateTrigger(DbTriggerType type)
{
var updTriggerName = string.Format(_postgreSqlObjectNames.GetCommandName(DbCommandType.UpdateTrigger),
_tableName.UnquotedStringWithUnderScore);
var delTriggerName = string.Format(_postgreSqlObjectNames.GetCommandName(DbCommandType.DeleteTrigger),
_tableName.UnquotedStringWithUnderScore);
var insTriggerName = string.Format(_postgreSqlObjectNames.GetCommandName(DbCommandType.InsertTrigger),
_tableName.UnquotedStringWithUnderScore);
var triggerName = string.Empty;
switch (type)
{
case DbTriggerType.Insert:
{
triggerName = insTriggerName;
break;
}
case DbTriggerType.Update:
{
triggerName = updTriggerName;
break;
}
case DbTriggerType.Delete:
{
triggerName = delTriggerName;
break;
}
}
return(!PostgreSqlManagementUtils.TriggerExists(_connection, _transaction, triggerName));
}
//------------------------------------------------------------------
// Delete command
//------------------------------------------------------------------
private Tuple <NpgsqlCommand, string> BuildDeleteCommand()
{
var sqlCommand = new NpgsqlCommand();
AddPkColumnParametersToCommand(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 stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
stringBuilder.AppendLine("ts := 0;");
stringBuilder.AppendLine(
$"SELECT \"timestamp\" FROM {_trackingName.QuotedObjectName} WHERE {PostgreSqlManagementUtils.WhereColumnAndParameters(_tableDescription.PrimaryKey.Columns, _trackingName.QuotedObjectName)} LIMIT 1 INTO ts;");
stringBuilder.AppendLine($"DELETE FROM {_tableName.QuotedString} WHERE");
stringBuilder.AppendLine(
PostgreSqlManagementUtils.WhereColumnAndParameters(_tableDescription.PrimaryKey.Columns, ""));
stringBuilder.AppendLine("AND (ts <= sync_min_timestamp OR sync_force_write = 1);");
stringBuilder.AppendLine("GET DIAGNOSTICS rows = ROW_COUNT;");
stringBuilder.AppendLine("RETURN rows;");
sqlCommand.CommandText = stringBuilder.ToString();
return(Tuple.Create(sqlCommand, "integer"));
}
//------------------------------------------------------------------
// 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"));
}
/// <summary>
/// Check if we need to create the stored procedure
/// </summary>
public bool NeedToCreateProcedure(DbCommandType commandType)
{
if (_connection.State != ConnectionState.Open)
{
throw new ArgumentException("Here, we need an opened connection please");
}
var commandName = _sqlObjectNames.GetCommandName(commandType);
return(!PostgreSqlManagementUtils.ProcedureExists(_connection, _transaction, commandName));
}
public List <string> GetTablePrimaryKeys()
{
// Get PrimaryKey
var dmTableKeys = PostgreSqlManagementUtils.PrimaryKeysForTable(_sqlConnection, _sqlTransaction, TableName);
if (dmTableKeys == null || dmTableKeys.Rows.Count == 0)
{
throw new Exception("No Primary Keys in this table, it' can't happen :) ");
}
var lstKeys = new List <String>();
foreach (var dmKey in dmTableKeys.Rows)
{
lstKeys.Add((string)dmKey["COLUMN_NAME"]);
}
return(lstKeys);
}
private string DeleteTriggerBodyText()
{
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
stringBuilder.AppendLine("BEGIN");
stringBuilder.AppendLine($"UPDATE {_trackingName.QuotedString} ");
stringBuilder.AppendLine("SET \"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\" = {PostgreSqlObjectNames.TimestampValue}");
stringBuilder.AppendLine($"\t,\"timestamp\" = {PostgreSqlObjectNames.TimestampValue}");
stringBuilder.AppendLine("\t,\"last_change_datetime\" = now()");
// Filter columns
if (Filters != null)
{
for (var i = 0; i < Filters.Count; i++)
{
var filterColumn = Filters[i];
if (_tableDescription.PrimaryKey.Columns.Any(c =>
c.ColumnName.ToLowerInvariant() == filterColumn.ColumnName.ToLowerInvariant()))
{
continue;
}
var columnName = new ObjectNameParser(filterColumn.ColumnName.ToLowerInvariant(), "\"", "\"");
stringBuilder.AppendLine($"\t,{columnName.QuotedString} = \"d\".{columnName.QuotedString}");
}
stringBuilder.AppendLine();
}
stringBuilder.Append($"WHERE ");
stringBuilder.Append(PostgreSqlManagementUtils.JoinTwoTablesOnClause(_tableDescription.PrimaryKey.Columns,
_trackingName.QuotedString, "old"));
stringBuilder.AppendLine(";");
stringBuilder.AppendLine("RETURN OLD;");
stringBuilder.AppendLine("END;");
return(stringBuilder.ToString());
}
private string UpdateTriggerBodyText()
{
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
stringBuilder.AppendLine($"Begin ");
stringBuilder.AppendLine($"\tUPDATE {_trackingName.QuotedString} ");
stringBuilder.AppendLine("\tSET \"update_scope_id\" = NULL -- since the update if from local, it's a NULL");
stringBuilder.AppendLine($"\t\t,\"update_timestamp\" = {PostgreSqlObjectNames.TimestampValue}");
stringBuilder.AppendLine($"\t\t,\"timestamp\" = {PostgreSqlObjectNames.TimestampValue}");
stringBuilder.AppendLine("\t\t,\"last_change_datetime\" = now()");
if (Filters != null && Filters.Count > 0)
{
for (var i = 0; i < Filters.Count; i++)
{
var filterColumn = Filters[i];
if (_tableDescription.PrimaryKey.Columns.Any(c =>
c.ColumnName.ToLowerInvariant() == filterColumn.ColumnName.ToLowerInvariant()))
{
continue;
}
var columnName = new ObjectNameParser(filterColumn.ColumnName.ToLowerInvariant(), "\"", "\"");
stringBuilder.AppendLine($"\t,{columnName.QuotedString} = \"i\".{columnName.QuotedString}");
}
stringBuilder.AppendLine();
}
stringBuilder.Append($"\tWhere ");
stringBuilder.Append(PostgreSqlManagementUtils.JoinTwoTablesOnClause(_tableDescription.PrimaryKey.Columns,
_trackingName.QuotedString, "new"));
stringBuilder.AppendLine($"; ");
stringBuilder.AppendLine("RETURN NEW;");
stringBuilder.AppendLine($"End; ");
return(stringBuilder.ToString());
}
//------------------------------------------------------------------
// Insert command
//------------------------------------------------------------------
private Tuple <NpgsqlCommand, string> BuildInsertCommand()
{
var sqlCommand = new NpgsqlCommand();
var stringBuilder = new StringBuilder();
var stringBuilderArguments = new StringBuilder();
var stringBuilderParameters = new StringBuilder();
AddColumnParametersToCommand(sqlCommand);
stringBuilder.Append(string.Concat("IF ((SELECT COUNT(*) FROM ", _trackingName.QuotedString, " WHERE "));
stringBuilder.Append(
PostgreSqlManagementUtils.ColumnsAndParameters(_tableDescription.PrimaryKey.Columns, string.Empty));
stringBuilder.AppendLine(") <= 0) THEN");
var empty = string.Empty;
foreach (var mutableColumn in _tableDescription.Columns.Where(c => !c.ReadOnly))
{
var columnName = new ObjectNameParser(mutableColumn.ColumnName, "\"", "\"");
stringBuilderArguments.Append(string.Concat(empty, columnName.QuotedString));
stringBuilderParameters.Append(string.Concat(empty,
$"{PgsqlPrefixParameter}{columnName.UnquotedString}"));
empty = ", ";
}
stringBuilder.AppendLine($"\tINSERT INTO {_tableName.QuotedString}");
stringBuilder.AppendLine($"\t({stringBuilderArguments})");
stringBuilder.AppendLine($"\tVALUES ({stringBuilderParameters});");
stringBuilder.AppendLine();
stringBuilder.AppendLine("GET DIAGNOSTICS rows = ROW_COUNT;");
stringBuilder.AppendLine("RETURN rows;");
stringBuilder.AppendLine("END IF;");
stringBuilder.AppendLine("RETURN 0;");
sqlCommand.CommandText = stringBuilder.ToString();
return(Tuple.Create(sqlCommand, "INT"));
}
public List <DmColumn> GetTableDefinition()
{
List <DmColumn> columns = new List <DmColumn>();
// Get the columns definition
var dmColumnsList = PostgreSqlManagementUtils.ColumnsForTable(_sqlConnection, _sqlTransaction, TableName);
var postgreSqlDbMetadata = new PostgreSqlDbMetadata();
foreach (var c in dmColumnsList.Rows.OrderBy(r => (int)r["ordinal_position"]))
{
var typeName = c["data_type"].ToString();
var name = c["column_name"].ToString();
// Gets the datastore owner dbType
var datastoreDbType = (NpgsqlDbType)postgreSqlDbMetadata.ValidateOwnerDbType(typeName, false, false);
// once we have the datastore type, we can have the managed type
Type columnType = postgreSqlDbMetadata.ValidateType(datastoreDbType);
var dbColumn = DmColumn.CreateColumn(name, columnType);
dbColumn.OriginalTypeName = typeName;
dbColumn.SetOrdinal(Convert.ToInt32(c["ordinal_position"]));
var maxLengthLong = c["character_octet_length"] != DBNull.Value ? Convert.ToInt64(c["character_octet_length"]) : 0;
dbColumn.MaxLength = maxLengthLong > Int32.MaxValue ? Int32.MaxValue : (Int32)maxLengthLong;
dbColumn.Precision = c["numeric_precision"] != DBNull.Value ? Convert.ToByte(c["numeric_precision"]) : (byte)0;
dbColumn.Scale = c["numeric_scale"] != DBNull.Value ? Convert.ToByte(c["numeric_scale"]) : (byte)0;
dbColumn.AllowDBNull = (String)c["is_nullable"] != "NO";
dbColumn.AutoIncrement = typeName.Contains("serial");
dbColumn.IsUnsigned = false;
columns.Add(dbColumn);
}
return(columns);
}
/// <summary>
/// Check if we need to create the table in the current database
/// </summary>
public bool NeedToCreateTable()
{
return(!PostgreSqlManagementUtils.TableExists(_connection, _transaction, _tableName.UnquotedString));
}
public DmTable GetTableRelations()
{
return(PostgreSqlManagementUtils.RelationsForTable(_sqlConnection, _sqlTransaction, TableName));
}
//------------------------------------------------------------------
// Update Metadata command
//------------------------------------------------------------------
private Tuple <NpgsqlCommand, string> BuildUpdateMetadataCommand()
{
var sqlCommand = new NpgsqlCommand();
var stringBuilder = new StringBuilder();
AddPkColumnParametersToCommand(sqlCommand);
var sqlParameter = new NpgsqlParameter();
sqlParameter.ParameterName = "sync_scope_id";
sqlParameter.NpgsqlDbType = NpgsqlDbType.Uuid;
sqlCommand.Parameters.Add(sqlParameter);
var sqlParameter1 = new NpgsqlParameter();
sqlParameter1.ParameterName = "sync_row_is_tombstone";
sqlParameter1.NpgsqlDbType = NpgsqlDbType.Integer;
sqlCommand.Parameters.Add(sqlParameter1);
var sqlParameter3 = new NpgsqlParameter();
sqlParameter3.ParameterName = "create_timestamp";
sqlParameter3.NpgsqlDbType = NpgsqlDbType.Bigint;
sqlCommand.Parameters.Add(sqlParameter3);
var sqlParameter5 = new NpgsqlParameter();
sqlParameter5.ParameterName = "update_timestamp";
sqlParameter5.NpgsqlDbType = NpgsqlDbType.Bigint;
sqlCommand.Parameters.Add(sqlParameter5);
//stringBuilder.AppendLine("DO $$");
//stringBuilder.AppendLine($"DECLARE was_tombstone int;");
//stringBuilder.AppendLine("BEGIN");
stringBuilder.AppendLine($"ts := 1;"); // was_tombstone
stringBuilder.AppendLine($"SELECT {_trackingName.QuotedObjectName}.\"sync_row_is_tombstone\" " +
$"FROM {_trackingName.QuotedObjectName} " +
$"WHERE {PostgreSqlManagementUtils.WhereColumnAndParameters(_tableDescription.PrimaryKey.Columns, _trackingName.QuotedObjectName)} " +
$"LIMIT 1 INTO ts;");
stringBuilder.AppendLine($"IF (ts is not null and ts = 1 and sync_row_is_tombstone = 0) THEN");
stringBuilder.AppendLine($"UPDATE {_trackingName.QuotedString}");
stringBuilder.AppendLine($"SET \"create_scope_id\" = sync_scope_id, ");
stringBuilder.AppendLine($"\t \"update_scope_id\" = sync_scope_id, ");
stringBuilder.AppendLine($"\t \"create_timestamp\" = $4, ");
stringBuilder.AppendLine($"\t \"update_timestamp\" = $5, ");
stringBuilder.AppendLine($"\t \"sync_row_is_tombstone\" = $3, ");
stringBuilder.AppendLine($"\t \"timestamp\" = {PostgreSqlObjectNames.TimestampValue}, ");
stringBuilder.AppendLine($"\t \"last_change_datetime\" = now() ");
stringBuilder.AppendLine(
$"WHERE {PostgreSqlManagementUtils.WhereColumnAndParameters(_tableDescription.PrimaryKey.Columns, "")};");
stringBuilder.AppendLine($"ELSE");
stringBuilder.AppendLine($"UPDATE {_trackingName.QuotedString}");
stringBuilder.AppendLine($"SET \"update_scope_id\" = sync_scope_id, ");
stringBuilder.AppendLine($"\t \"update_timestamp\" = $5, ");
stringBuilder.AppendLine($"\t \"sync_row_is_tombstone\" = $3, ");
stringBuilder.AppendLine($"\t \"timestamp\" = {PostgreSqlObjectNames.TimestampValue}, ");
stringBuilder.AppendLine($"\t \"last_change_datetime\" = now() ");
stringBuilder.AppendLine(
$"WHERE {PostgreSqlManagementUtils.WhereColumnAndParameters(_tableDescription.PrimaryKey.Columns, "")};");
stringBuilder.AppendLine("END IF;");
stringBuilder.AppendLine("GET DIAGNOSTICS rows = ROW_COUNT;");
stringBuilder.AppendLine("RETURN rows;");
sqlCommand.CommandText = stringBuilder.ToString();
return(Tuple.Create(sqlCommand, "integer"));
}