private string PG_ReceiveMessagesScript(int messageCount)
{
DatabaseQueue queue = Settings.DatabaseSettings.DatabaseQueue;
string tableName = queue.TableName;
string field1 = queue.Fields.Where(f => f.Property == "МоментВремени").FirstOrDefault()?.Name;
string field2 = queue.Fields.Where(f => f.Property == "Идентификатор").FirstOrDefault()?.Name;
string field3 = queue.Fields.Where(f => f.Property == "ДатаВремя").FirstOrDefault()?.Name;
string field4 = queue.Fields.Where(f => f.Property == "Отправитель").FirstOrDefault()?.Name;
string field5 = queue.Fields.Where(f => f.Property == "Получатели").FirstOrDefault()?.Name;
string field6 = queue.Fields.Where(f => f.Property == "ТипОперации").FirstOrDefault()?.Name;
string field7 = queue.Fields.Where(f => f.Property == "ТипСообщения").FirstOrDefault()?.Name;
string field8 = queue.Fields.Where(f => f.Property == "ТелоСообщения").FirstOrDefault()?.Name;
//DELETE FROM _reference39 WHERE _idrref IN
//(SELECT _idrref FROM _reference39 ORDER BY _code ASC, _idrref ASC LIMIT 10)
//RETURNING
//_code as "Код", _idrref as "Ссылка", _version as "ВерсияДанных",
//_fld135 as "ДатаВремя", _fld136 as "Отправитель",
//_fld137 as "Получатели", _fld138 as "ТипОперации",
//_fld139 as "ТипСообщения", _fld140 as "ТелоСообщения";
StringBuilder script = new StringBuilder();
script.AppendLine("WITH cte AS");
script.AppendLine($"(SELECT {field1}, {field2} FROM {tableName} ORDER BY {field1} ASC, {field2} ASC LIMIT {messageCount})");
script.AppendLine($"DELETE FROM {tableName} t USING cte");
script.AppendLine($"WHERE t.{field1} = cte.{field1} AND t.{field2} = cte.{field2}");
script.AppendLine("RETURNING");
script.AppendLine($"t.{field1} AS \"МоментВремени\", t.{field2} AS \"Идентификатор\",");
script.AppendLine($"t.{field3} AS \"ДатаВремя\", CAST(t.{field4} AS varchar) AS \"Отправитель\",");
script.AppendLine($"CAST(t.{field5} AS varchar) AS \"Получатели\", CAST(t.{field6} AS varchar) AS \"ТипОперации\",");
script.AppendLine($"CAST(t.{field7} AS varchar) AS \"ТипСообщения\", CAST(t.{field8} AS text) AS \"ТелоСообщения\";");
return(script.ToString());
}
private string MS_ReceiveMessagesScript(int messageCount)
{
DatabaseQueue queue = Settings.DatabaseSettings.DatabaseQueue;
string tableName = queue.TableName;
string field1 = queue.Fields.Where(f => f.Property == "МоментВремени").FirstOrDefault()?.Name;
string field2 = queue.Fields.Where(f => f.Property == "Идентификатор").FirstOrDefault()?.Name;
string field3 = queue.Fields.Where(f => f.Property == "ДатаВремя").FirstOrDefault()?.Name;
string field4 = queue.Fields.Where(f => f.Property == "Отправитель").FirstOrDefault()?.Name;
string field5 = queue.Fields.Where(f => f.Property == "Получатели").FirstOrDefault()?.Name;
string field6 = queue.Fields.Where(f => f.Property == "ТипОперации").FirstOrDefault()?.Name;
string field7 = queue.Fields.Where(f => f.Property == "ТипСообщения").FirstOrDefault()?.Name;
string field8 = queue.Fields.Where(f => f.Property == "ТелоСообщения").FirstOrDefault()?.Name;
StringBuilder script = new StringBuilder();
script.AppendLine("WITH [CTE] AS");
script.AppendLine("(");
script.AppendLine($"SELECT TOP ({messageCount})");
script.AppendLine($"[{field1}] AS [МоментВремени],");
script.AppendLine($"[{field2}] AS [Идентификатор],");
script.AppendLine($"[{field3}] AS [ДатаВремя],");
script.AppendLine($"[{field4}] AS [Отправитель],");
script.AppendLine($"[{field5}] AS [Получатели],");
script.AppendLine($"[{field6}] AS [ТипОперации],");
script.AppendLine($"[{field7}] AS [ТипСообщения],");
script.AppendLine($"[{field8}] AS [ТелоСообщения]");
script.AppendLine("FROM");
script.AppendLine($"[{tableName}] WITH (ROWLOCK)");
script.AppendLine("ORDER BY");
script.AppendLine($"[{field1}] ASC, [{field2}] ASC");
script.AppendLine(")");
script.AppendLine("DELETE [CTE]");
script.AppendLine("OUTPUT deleted.[МоментВремени], deleted.[Идентификатор],");
script.AppendLine("deleted.[ДатаВремя], deleted.[Отправитель], deleted.[Получатели],");
script.AppendLine("deleted.[ТипОперации], deleted.[ТипСообщения], deleted.[ТелоСообщения];");
return(script.ToString());
}
