/// <summary>
/// Returns SQL for updating the <paramref name="table"/> to redact the capture groups in <see cref="IsIdentifiableRule.IfPattern"/>. If no capture groups are represented in the <paramref name="usingRule"/> then this class falls back on <see cref="ProblemValuesUpdateStrategy"/>
/// </summary>
/// <param name="table"></param>
/// <param name="primaryKeys"></param>
/// <param name="failure"></param>
/// <param name="usingRule"></param>
/// <returns></returns>
public override IEnumerable <string> GetUpdateSql(DiscoveredTable table, Dictionary <DiscoveredTable, DiscoveredColumn> primaryKeys, Failure failure, IsIdentifiableRule usingRule)
{
if (usingRule == null || string.IsNullOrWhiteSpace(usingRule.IfPattern))
{
return(_fallback.GetUpdateSql(table, primaryKeys, failure, usingRule));
}
try
{
Regex r = new Regex(usingRule.IfPattern);
var match = r.Match(failure.ProblemValue);
//Group 1 (index 0) is always the full match, we want selective updates
if (match.Success && match.Groups.Count > 1)
{
var syntax = table.GetQuerySyntaxHelper();
//update the capture groups of the Regex
return(match.Groups.Cast <Group>().Skip(1).Select(m => GetUpdateWordSql(table, primaryKeys, syntax, failure, m.Value)));
}
//The Regex did not have capture groups or did not match the failure
return(_fallback.GetUpdateSql(table, primaryKeys, failure, usingRule));
}
catch (Exception)
{
//The Regex pattern was bad or something else bad went wrong
return(_fallback.GetUpdateSql(table, primaryKeys, failure, usingRule));
}
}
public override void MakeDistinct(DatabaseOperationArgs args, DiscoveredTable discoveredTable)
{
var syntax = discoveredTable.GetQuerySyntaxHelper();
string sql =
@"DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY {0} ORDER BY {0}) AS RowNum
FROM {1}
) as f
where RowNum > 1";
string columnList = string.Join(",",
discoveredTable.DiscoverColumns().Select(c => syntax.EnsureWrapped(c.GetRuntimeName())));
string sqlToExecute = string.Format(sql, columnList, discoveredTable.GetFullyQualifiedName());
var server = discoveredTable.Database.Server;
using (var con = args.GetManagedConnection(server))
{
using (var cmd = server.GetCommand(sqlToExecute, con))
args.ExecuteNonQuery(cmd);
}
}
/// <inheritdoc cref="TriggerImplementer(DiscoveredTable,bool)"/>
public PostgreSqlTriggerImplementer(DiscoveredTable table, bool createDataLoadRunIDAlso) : base(table, createDataLoadRunIDAlso)
{
_schema = string.IsNullOrWhiteSpace(_table.Schema) ? table.GetQuerySyntaxHelper().GetDefaultSchemaIfAny():_table.Schema;
_triggerRuntimeName = _table.GetRuntimeName() + "_OnUpdate";
_procedureRuntimeName = _table.GetRuntimeName() + "_OnUpdateProc";
_procedureNameFullyQualified = _schema + ".\"" + _procedureRuntimeName + "\"";
}
public string GetSql()
{
var response = _table.GetQuerySyntaxHelper().HowDoWeAchieveTopX(100);
switch (response.Location)
{
case QueryComponent.SELECT:
return("Select " + response.SQL + " * from " + _table.GetFullyQualifiedName());
case QueryComponent.WHERE:
return("Select * from " + _table.GetFullyQualifiedName() + " WHERE " + response.SQL);
case QueryComponent.Postfix:
return("Select * from " + _table.GetFullyQualifiedName() + " " + response.SQL);
default:
throw new ArgumentOutOfRangeException();
}
}
/// <summary>
/// Generates 1 UPDATE statement per <see cref="Failure.Parts"/> for redacting the current <paramref name="failure"/>
/// </summary>
/// <param name="table"></param>
/// <param name="primaryKeys"></param>
/// <param name="failure"></param>
/// <param name="usingRule"></param>
/// <returns></returns>
public override IEnumerable <string> GetUpdateSql(DiscoveredTable table,
Dictionary <DiscoveredTable, DiscoveredColumn> primaryKeys, Failure failure, IsIdentifiableRule usingRule)
{
var syntax = table.GetQuerySyntaxHelper();
foreach (var part in failure.Parts)
{
yield return(GetUpdateWordSql(table, primaryKeys, syntax, failure, part.Word));
}
}
/// <summary>
/// Returns the table name suitable for being passed into OBJECT_ID including schema if any
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
private string GetObjectName(DiscoveredTable table)
{
var syntax = table.GetQuerySyntaxHelper();
var objectName = syntax.EnsureWrapped(table.GetRuntimeName());
if (table.Schema != null)
{
return(syntax.EnsureWrapped(table.Schema) + "." + objectName);
}
return(objectName);
}
protected override string GetRenameTableSql(DiscoveredTable discoveredTable, string newName)
{
string oldName = discoveredTable.GetWrappedName();
var syntax = discoveredTable.GetQuerySyntaxHelper();
if (!string.IsNullOrWhiteSpace(discoveredTable.Schema))
{
oldName = syntax.EnsureWrapped(discoveredTable.Schema) + "." + oldName;
}
return(string.Format("exec sp_rename '{0}', '{1}'", syntax.Escape(oldName), syntax.Escape(newName)));
}
protected override string GetRenameTableSql(DiscoveredTable discoveredTable, string newName)
{
var syntax = discoveredTable.GetQuerySyntaxHelper();
return(string.Format("RENAME TABLE {0} TO {1};", discoveredTable.GetWrappedName(), syntax.EnsureWrapped(newName)));
}