/// <summary>
/// Формирует Sql для удаления данных
/// </summary>
private string GetDeleteSql(string[] tempTablePath, string[] tablePath, IList <ColumnMap> map)
{
return($"DELETE FROM {PostgreNamingHelper.Escape(tablePath)} AS rl " +
$"USING {PostgreNamingHelper.Escape(tempTablePath)} AS tmp " +
$"WHERE tmp.{PostgreNamingHelper.Escape(cRowStateColumnName)} IN ('D') " +
$"AND {string.Join(" AND ", map.Where(x => x.IsKey).Select(x => $"rl.{x.EscapedRealColumn} = tmp.{x.EscapedTempColumn}"))} ");
}
/// <summary>
/// Формирует Sql для команды получения индекса первичного ключа таблицы
/// </summary>
private string GetTableIndexInfoSql(string[] tablePath)
{
return($"SELECT indkey, indoption as k " +
$"FROM pg_index i " +
$"WHERE i.indrelid = \'{PostgreNamingHelper.EscapeString(PostgreNamingHelper.Escape(tablePath))}\'::regclass " +
$"AND i.indisprimary = true ");
}
/// <summary>
/// Формирует Sql для команды импорта данных во временную таблицу
/// </summary>
private string GetImportDataSql(string[] tempTablePath, DataTable schema)
{
var columns = new List <string> {
cRowStateColumnName
};
foreach (var row in schema.Rows.OfType <DataRow>())
{
columns.Add($"col{(int)row["ColumnOrdinal"]}");
columns.Add($"col{(int)row["ColumnOrdinal"]}Specified");
if (!(bool)row["IsKey"])
{
continue;
}
columns.Add($"col{(int)row["ColumnOrdinal"]}new");
columns.Add($"col{(int)row["ColumnOrdinal"]}newSpecified");
}
return
($"COPY {PostgreNamingHelper.Escape(tempTablePath)}" +
$"({string.Join(", ", columns.Select(x=>PostgreNamingHelper.Escape(x)))}) " +
$"FROM STDIN BINARY;");
}
/// <summary>
/// Проверяет наличие первичного ключа в схеме
/// </summary>
private void AssertHasPrimaryKey(DataTable schema, string[] tablePath)
{
if (schema.Rows.OfType <DataRow>().All(x => !(bool)x["IsKey"]))
{
throw new InvalidOperationException($"Table {PostgreNamingHelper.Escape(tablePath)} does not have primary key.");
}
}
/// <summary>
/// Формирует Sql для вставки данных
/// </summary>
private string GetInsertSql(string[] tempTablePath, string[] tablePath, IList <ColumnMap> map)
{
return($"INSERT INTO {PostgreNamingHelper.Escape(tablePath)} " +
$"({string.Join(", ", map.Select(x => x.EscapedRealColumn))}) " +
$"SELECT " +
$"{string.Join(", ", map.Select(x => x.EscapedTempColumn))} " +
$"FROM {PostgreNamingHelper.Escape(tempTablePath)} AS tmp " +
$"WHERE tmp.{PostgreNamingHelper.Escape(cRowStateColumnName)} IN ('I') ");
}
/// <summary>
/// Формирует Sql для обновления данных
/// </summary>
private string GetUpdateSql(string[] tempTablePath, string[] tablePath, IList <ColumnMap> map)
{
var setBlock = string.Join(", ", map.Select(x =>
$"{x.EscapedRealColumn} = CASE " +
$"WHEN tmp.{(x.IsKey ? x.EscapedTempUpdatedKeySpecifiedColumn : x.EscapedTempSpecifiedColumn)} " +
$"THEN tmp.{(x.IsKey ? x.EscapedTempUpdatedKeyColumn : x.EscapedTempColumn)} " +
$"ELSE rl.{x.EscapedRealColumn} " +
$"END"));
return($"UPDATE {PostgreNamingHelper.Escape(tablePath)} AS rl " +
$"SET {setBlock} " +
$"FROM {PostgreNamingHelper.Escape(tempTablePath)} AS tmp " +
$"WHERE tmp.{PostgreNamingHelper.Escape(cRowStateColumnName)} IN ('U') " +
$"AND {string.Join(" AND ", map.Where(x => x.IsKey).Select(x => $"rl.{x.EscapedRealColumn} = tmp.{x.EscapedTempColumn}"))} ");
}
/// <summary>
/// Формирует Sql для команды создания временной таблицы
/// </summary>
private string GetCreateTempTableSql(DataTable schema, string tempTableName)
{
var sb = new StringBuilder();
//Создание таблицы
sb.Append($"CREATE TEMP TABLE {PostgreNamingHelper.Escape(tempTableName)} (");
sb.Append($"{PostgreNamingHelper.Escape(cRowStateColumnName)} {cRowStateColumnType}");
foreach (var columnInfo in schema.Rows.OfType <DataRow>())
{
var columnName = $"col{(int) columnInfo["ColumnOrdinal"]}";
sb.Append($", {PostgreNamingHelper.Escape(columnName)} " +
$"{(string)columnInfo["DataTypeName"]}");
sb.Append($", {PostgreNamingHelper.Escape($"{columnName}Specified")} bool ");
if (!(bool)columnInfo["IsKey"])
{
continue;
}
columnName = $"{columnName}new";
sb.Append($", {PostgreNamingHelper.Escape(columnName)} " +
$"{(string)columnInfo["DataTypeName"]}");
sb.Append($", {PostgreNamingHelper.Escape($"{columnName}Specified")} bool ");
}
sb.Append("); ");
//Создание индекса
sb.Append($"CREATE INDEX {PostgreNamingHelper.Escape($"IX_{tempTableName}")} ");
sb.Append($"ON {PostgreNamingHelper.Escape(tempTableName)} (");
var indexInfos = new List <string> {
$"{PostgreNamingHelper.Escape(cRowStateColumnName)} ASC NULLS LAST"
};
indexInfos.AddRange(
schema.Rows.OfType <DataRow>()
.Where(x => (bool)x["IsKey"])
.OrderBy(x => (int)x["PrimaryKeyIndexOrdinal"])
.Select(x => $"{PostgreNamingHelper.Escape($"col{(int) x["ColumnOrdinal"]}")} " +
$"{(string) x["PrimaryKeyIndexOrderBy"]} " +
$"{(string) x["PrimaryKeyIndexNulls"]}"));
sb.Append(string.Join(", ", indexInfos));
sb.Append("); ");
return(sb.ToString());
}
public ColumnMap(string tempColumn, string realColumn, bool isKey)
{
TempColumn = tempColumn;
TempSpecifiedColumn = $"{tempColumn}Specified";
RealColumn = realColumn;
EscapedTempColumn = PostgreNamingHelper.Escape(TempColumn);
EscapedTempSpecifiedColumn = PostgreNamingHelper.Escape(TempSpecifiedColumn);
EscapedRealColumn = PostgreNamingHelper.Escape(RealColumn);
IsKey = isKey;
if (isKey)
{
TempUpdatedKeyColumn = $"{tempColumn}new";
TempUpdatedKeySpecifiedColumn = $"{tempColumn}newSpecified";
EscapedTempUpdatedKeyColumn = PostgreNamingHelper.Escape(TempUpdatedKeyColumn);
EscapedTempUpdatedKeySpecifiedColumn = PostgreNamingHelper.Escape(TempUpdatedKeySpecifiedColumn);
}
}
/// <summary>
/// Формирует Sql для команды удаления временной таблицы
/// </summary>
private string GetDropTableSql(string[] tempTablePath)
{
return($"DROP TABLE IF EXISTS {PostgreNamingHelper.Escape(tempTablePath)}");
}
/// <summary>
/// Формирует Sql для команды получения схемы таблицы
/// </summary>
private string GetSchemaTableSql(string[] tablePath)
{
return($"SELECT * FROM {PostgreNamingHelper.Escape(tablePath)} LIMIT 0");
}