private static Dictionary <int, int?> GetPermissionLevels(DbConnection sqlConnection, int[] ids, int userId, int groupId, string entityTypeName, string parentEntityTypeName = "", int parentId = 0)
{
var result = new Dictionary <int, int?>();
var securitySql = Common.GetPermittedItemsAsQuery(sqlConnection, userId, groupId, PermissionLevel.Deny, PermissionLevel.FullAccess,
entityTypeName, parentEntityTypeName, parentId);
var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
var sql = $@" select i.id, pi.permission_level from {SqlQuerySyntaxHelper.IdList(dbType, "@ids", "i")}
left join ({securitySql}) as pi on pi.{entityTypeName}_id = i.id " ;
using (var cmd = DbCommandFactory.Create(sql, sqlConnection))
{
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", ids, dbType));
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
result[Convert.ToInt32(reader["id"])] = Converter.ToNullableInt32(reader["permission_level"]);
}
}
}
return(result);
}
public static void UpdateContentModification(DbConnection sqlConnection, List <int> liveIds, List <int> stageIds)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
var sql = new List <DbParameter>();
var sb = new StringBuilder();
;
if (liveIds.Any())
{
sb.AppendLine($@"update content_modification {WithRowLock(dbType)} set live_modified = {Now(dbType)} where content_id in (select id from {IdList(dbType, "@liveIds")});");
sql.Add(GetIdsDatatableParam("@liveIds", liveIds, dbType));
}
if (stageIds.Any())
{
sb.AppendLine($@"update content_modification {WithRowLock(dbType)} set stage_modified = {Now(dbType)} where content_id in (select id from {IdList(dbType, "@stageIds")});");
sql.Add(GetIdsDatatableParam("@stageIds", stageIds, dbType));
}
if (sql.Any())
{
using (var cmd = DbCommandFactory.Create(sb.ToString(), sqlConnection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddRange(sql.ToArray());
cmd.ExecuteNonQuery();
}
}
}
public static IEnumerable <DataRow> BatchInsert(DbConnection sqlConnection, DataTable articles, bool visible, int userId)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
using (var cmd = DbCommandFactory.Create(BatchInsertQuery, sqlConnection))
{
if (dbType == DatabaseType.SqlServer)
{
cmd.Parameters.Add(new SqlParameter("@values", SqlDbType.Structured)
{
TypeName = "Values", Value = articles
});
}
else
{
cmd.CommandText = $@"select * from qp_batch_insert(@values, @visible, @userId);";
var xml = GetValuesDoc(articles).ToString();
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@values", xml, dbType));
}
cmd.Parameters.AddWithValue("@visible", visible ? 1 : 0);
cmd.Parameters.AddWithValue("@userId", userId);
var dt = new DataTable();
DataAdapterFactory.Create(cmd).Fill(dt);
return(dt.AsEnumerable().ToArray());
}
}
public static Guid[] GetArticleGuidsByIds(DbConnection sqlConnection, int[] ids)
{
if (ids == null)
{
throw new ArgumentNullException(nameof(ids));
}
var databaseType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
var table = IdList(databaseType, "@ids", "ids");
var noLock = WithNoLock(databaseType);
var query = $@"select ci.unique_id, ids.id from {table} left join content_item ci {noLock} on ci.content_item_id = ids.id";
using (var cmd = DbCommandFactory.Create(query, sqlConnection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(GetIdsDatatableParam("@ids", ids, databaseType));
var result = new Dictionary <int, Guid>();
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
result.Add((int)dr.GetDecimal(1), dr.IsDBNull(0) ? Guid.Empty : dr.GetGuid(0));
}
}
return(ids.Select(n => result[n]).ToArray());
}
}
public static void PersistArticle(DbConnection currentDbConnection, string customerCode, string xml, out int id)
{
var databaseType = DatabaseTypeHelper.ResolveDatabaseType(currentDbConnection);
var ns = SqlQuerySyntaxHelper.DbSchemaName(databaseType);
var sql = $"select id, modified from {ns}.qp_persist_article(@xml)";
using (var cmd = DbCommandFactory.Create(sql, currentDbConnection))
{
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xml", xml, databaseType));
var dt = new DataTable();
try
{
DataAdapterFactory.Create(cmd).Fill(dt);
}
catch (PostgresException ex)
{
Logger.Error()
.Exception(ex)
.Message("Error while persisting article with xml: {xml}\n Query: {sql}", xml, sql)
.Property("customerCode", customerCode)
.Write();
throw;
}
id = (int)dt.Rows[0]["id"];
}
}
public static void ReplicateItems(DbConnection sqlConnection, int[] articleIds, int[] fieldIds)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
var sql = (dbType == DatabaseType.SqlServer) ? "qp_replicate_items" : "call qp_replicate_items(@ids, @attr_ids);";
using (var cmd = DbCommandFactory.Create(sql, sqlConnection))
{
if (dbType == DatabaseType.SqlServer)
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ids", SqlDbType.NVarChar, -1)
{
Value = string.Join(",", articleIds)
});
cmd.Parameters.Add(new SqlParameter("@attr_ids", SqlDbType.NVarChar, -1)
{
Value = string.Join(",", fieldIds)
});
}
else
{
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", articleIds, dbType));
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@attr_ids", fieldIds, dbType));
}
cmd.Parameters.AddWithValue("@modification_update_interval", -1);
cmd.ExecuteNonQuery();
}
}
public static Dictionary <int, bool> CheckLockedBy(DbConnection dbConnection, int[] ids, int currentUserId, bool forceUnlock)
{
string sql = $@"select locked_by, content_item_id from content_item ci with(nolock)
inner join @ids i on i.id = ci.content_item_id where locked_by is not null and locked_by <> @userId " ;
var result = ids.ToDictionary(kvp => kvp, kvp => true);
if (!forceUnlock)
{
using (var cmd = DbCommandFactory.Create(sql, dbConnection))
{
cmd.Parameters.Add(new SqlParameter("@ids", SqlDbType.Structured)
{
TypeName = "Ids",
Value = Common.IdsToDataTable(ids)
});
cmd.Parameters.AddWithValue("@userId", currentUserId);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var id = (int)(decimal)reader["content_item_id"];
result[id] = false;
}
}
}
}
return(result);
}
private static Dictionary <int, bool> CheckSecurity(DbConnection sqlConnection, int parentId, IEnumerable <int> testIds, int userId, int startLevel, string entityName, string parentEntityName, string columnName)
{
var granted = new Dictionary <int, bool>();
var securitySql = Common.GetPermittedItemsAsQuery(sqlConnection, userId, 0, startLevel, PermissionLevel.FullAccess,
entityName, parentEntityName, parentId);
var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
var trueValue = SqlQuerySyntaxHelper.ToBoolSql(dbType, true);
var falseValue = SqlQuerySyntaxHelper.ToBoolSql(dbType, false);
var sql = $@" select
i.id,
{SqlQuerySyntaxHelper.CastToBool(dbType, $"case when pi.{columnName} is null then {falseValue} else {trueValue} end")} as granted
from {SqlQuerySyntaxHelper.IdList(dbType, "@ids", "i")}
left join ({securitySql}) as pi on pi.{columnName} = i.id " ;
using (var cmd = DbCommandFactory.Create(sql, sqlConnection))
{
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", testIds, dbType));
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
granted[Convert.ToInt32(reader["id"])] = (bool)reader["granted"];
}
}
}
return(granted);
}
public static List <int> InsertArticleIds(DbConnection sqlConnection, string xml, bool withGuids)
{
var result = new List <int>();
var withGuidsStr = withGuids ? ", UNIQUE_ID" : "";
var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
string baseInsert = $@"INSERT INTO CONTENT_ITEM (VISIBLE, STATUS_TYPE_ID, CONTENT_ID, LAST_MODIFIED_BY {withGuidsStr}) ";
string baseSelect = $@"SELECT VISIBLE, STATUS_TYPE_ID, CONTENT_ID, LAST_MODIFIED_BY {withGuidsStr} ";
string sql = dbType == DatabaseType.SqlServer ? $@"
DECLARE @NewArticles TABLE (CONTENT_ID int, STATUS_TYPE_ID int, VISIBLE int, LAST_MODIFIED_BY int, UNIQUE_ID uniqueidentifier)
DECLARE @NewIds TABLE ([ID] INT);
INSERT INTO @NewArticles
SELECT
doc.col.value('./@contentId', 'int') CONTENT_ID
,doc.col.value('./@statusId', 'int') STATUS_TYPE_ID
,doc.col.value('./@visible', 'int') VISIBLE
,doc.col.value('./@userId', 'int') LAST_MODIFIED_BY
,doc.col.value('./@guid', 'uniqueidentifier') UNIQUE_ID
FROM @xml.nodes('ITEMS/ITEM') doc(col)
{baseInsert}
OUTPUT inserted.CONTENT_ITEM_ID INTO @NewIds
{baseSelect}
from @NewArticles
SELECT ID FROM @NewIds;
" : $@"
WITH inserted(id) AS
(
{baseInsert}
{baseSelect}
from XMLTABLE('ITEMS/ITEM' passing @xml COLUMNS
CONTENT_ID int PATH '@contentId',
STATUS_TYPE_ID int PATH '@statusId',
VISIBLE int PATH '@visible',
LAST_MODIFIED_BY int PATH '@userId',
UNIQUE_ID uuid PATH '@guid'
) x returning CONTENT_ITEM_ID
) select id from inserted;";
using (var cmd = DbCommandFactory.Create(sql, sqlConnection))
{
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xml", xml, dbType));
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int id;
if (int.TryParse(reader.GetValue(0).ToString(), out id))
{
result.Add(id);
}
}
}
return(result);
}
}
public static void ClearUserToken(DbConnection dbConnection, int userId, int sessionId)
{
var sql = $"delete from access_token where UserId = @userId and SessionId = @sessionId";
using (var cmd = DbCommandFactory.Create(sql, dbConnection))
{
cmd.Parameters.AddWithValue("@userId", userId);
cmd.Parameters.AddWithValue("@sessionId", sessionId);
cmd.ExecuteNonQuery();
}
}
public static void CreateArticleVersions(DbConnection connection, int userId, int[] ids)
{
var databaseType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var sql = SqlQuerySyntaxHelper.SpCall(databaseType, "qp_create_content_item_versions", "@ids, @last_modified_by");
using (var cmd = DbCommandFactory.Create(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(GetIdsDatatableParam("@ids", ids, databaseType));
cmd.Parameters.AddWithValue("@last_modified_by", userId);
cmd.ExecuteNonQuery();
}
}
public static void LockArticleForUpdate(DbConnection cnn, int id)
{
var databaseType = DatabaseTypeHelper.ResolveDatabaseType(cnn);
var withlock = databaseType == DatabaseType.SqlServer ? "with(rowlock, updlock)" : "";
var forUpdate = databaseType != DatabaseType.SqlServer ? "for update" : "";
using (var cmd = DbCommandFactory.Create($@"select content_item_id from content_item {withlock} where content_item_id = @id {forUpdate}", cnn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@id", id);
var dt = new DataTable();
DataAdapterFactory.Create(cmd).Fill(dt);
}
}
public static int RemovingActions_ClearO2MRelations(int contentId, DbConnection connection)
{
var dbType = GetDbType(connection);
if (dbType != DatabaseType.SqlServer)
{
return(0);
}
using (var cmd = DbCommandFactory.Create("qp_clear_relations", connection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@parent_id", contentId);
return(cmd.ExecuteNonQuery());
}
}
private static void FillColumn(DbConnection cnn, string tableName, FieldDAL newField)
{
var dbType = GetDbType(cnn);
var typeExpr = dbType == DatabaseType.Postgres ? PgColumnType(newField.Type.DatabaseType) : "nvarchar(max)";
var sql = $@"
update {tableName} set {Escape(dbType, newField.Name)} = cast(coalesce(cd.blob_data, cd.data) as {typeExpr})
from content_data cd
where cd.attribute_id = {newField.Id} and cd.content_item_id = {tableName}.content_item_id
";
using (var cmd = DbCommandFactory.Create(sql, cnn))
{
cmd.ExecuteNonQuery();
}
}
public static void UpdateNotForReplication(DbConnection sqlConnection, int[] ids, int userId)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
var setTrue = dbType == DatabaseType.SqlServer ? "1" : "true";
var sql = $@"
UPDATE CONTENT_ITEM SET NOT_FOR_REPLICATION = {setTrue}, MODIFIED = {SqlQuerySyntaxHelper.Now(dbType)}, LAST_MODIFIED_BY = @userId
WHERE {SqlQuerySyntaxHelper.IsFalse(dbType, "NOT_FOR_REPLICATION")} AND CONTENT_ITEM_ID IN (select id from {Common.IdList(dbType, "@ids")});
";
using (var cmd = DbCommandFactory.Create(sql, sqlConnection))
{
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", ids, dbType));
cmd.Parameters.AddWithValue("@userId", userId);
cmd.ExecuteNonQuery();
}
}
public static void InsertArticleValues(DbConnection sqlConnection, string xmlParameter)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
string sql = dbType == DatabaseType.SqlServer ? "qp_insertArticleValues" : PgTableValuesBlock + $@"
update content_data cd set data = coalesce(new.blob_data, new.data) from new
where cd.content_item_id = new.content_item_id and cd.attribute_id = new.attribute_id;
";
using (var cmd = DbCommandFactory.Create(sql, sqlConnection))
{
cmd.CommandType = dbType == DatabaseType.SqlServer ? CommandType.StoredProcedure : CommandType.Text;
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xmlParameter", xmlParameter, dbType));
var resultDt = new DataTable();
DataAdapterFactory.Create(cmd).Fill(resultDt);
}
}
public static int RemovingActions_RemoveContentItems(int contentId, int itemsToDelete, DbConnection connection)
{
var dbType = GetDbType(connection);
string disableTrigger = (dbType == DatabaseType.SqlServer) ? "select 1 as A into #disable_td_delete_item_o2m_nullify;" : "";
string query = $@"{disableTrigger}
delete FROM CONTENT_ITEM WHERE CONTENT_ITEM_ID in (
select {Top(dbType, itemsToDelete)} CONTENT_ITEM_ID from CONTENT_ITEM
where CONTENT_ID = @content_id order by CONTENT_ITEM_ID {Limit(dbType, itemsToDelete)}
);";
using (var cmd = DbCommandFactory.Create(query, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@content_id", contentId);
return(cmd.ExecuteNonQuery());
}
}
public static void DeleteArticles(DbConnection connection, List <int> ids, bool withAggregated)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var idParam = withAggregated ? $@"{DbSchemaName(dbType)}.qp_aggregated_and_self(@ids)" : "@ids";
var source = IdList(dbType, idParam);
if (ids != null && ids.Any())
{
var query = $"DELETE FROM content_item {WithRowLock(dbType)} where content_item_id in (select id from {source}) ";
using (var cmd = DbCommandFactory.Create(query, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(GetIdsDatatableParam("@ids", ids, dbType));
cmd.ExecuteNonQuery();
}
}
}
public static void UpdateArticleGuids(DbConnection sqlConnection, List <Tuple <int, Guid> > guidsByIdToUpdate)
{
if (guidsByIdToUpdate.Any())
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
var doc = new XDocument();
doc.Add(new XElement("ITEMS"));
foreach (var tuple in guidsByIdToUpdate)
{
var elem = new XElement("ITEM");
elem.Add(new XAttribute("id", tuple.Item1));
elem.Add(new XAttribute("guid", tuple.Item2));
doc.Root?.Add(elem);
}
string sql = dbType == DatabaseType.SqlServer ? $@"
DECLARE @NewIds TABLE (CONTENT_ITEM_ID int, UNIQUE_ID uniqueidentifier)
INSERT INTO @NewIds
SELECT
doc.col.value('./@id', 'int') CONTENT_ITEM_ID
,doc.col.value('./@guid', 'uniqueidentifier') UNIQUE_ID
FROM @xml.nodes('ITEMS/ITEM') doc(col)
UPDATE CONTENT_ITEM SET UNIQUE_ID = i.UNIQUE_ID FROM CONTENT_ITEM ci INNER JOIN @NewIds i
on ci.CONTENT_ITEM_ID = i.CONTENT_ITEM_ID
" : $@"
WITH new AS
(
select x.* from XMLTABLE('ITEMS/ITEM' passing @xml COLUMNS
content_item_id int PATH '@id',
unique_id uuid PATH '@guid'
) x
)
update content_item ci set unique_id = new.unique_id from new
where ci.content_item_id = new.content_item_id;";
using (var cmd = DbCommandFactory.Create(sql, sqlConnection))
{
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xml", doc.ToString(), dbType));
cmd.ExecuteNonQuery();
}
}
}
public static string GetConflictIds(DbConnection connection, int id, int contentId, string condition, List <FieldParameter> parameters)
{
var databaseType = DatabaseTypeHelper.ResolveDatabaseType(connection);
using (var cmd = DbCommandFactory.Create($"SELECT CONTENT_ITEM_ID FROM CONTENT_{contentId}_UNITED WHERE {condition} AND CONTENT_ITEM_ID <> @id", connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@id", id);
foreach (var parameter in parameters)
{
cmd.Parameters.Add(CreateDbParameter(databaseType, parameter));
}
var dt = new DataTable();
DataAdapterFactory.Create(cmd).Fill(dt);
return(IdCommaList(dt, FieldName.ContentItemId));
}
}
public static void SetArchiveFlag(DbConnection connection, IEnumerable <int> articleIds, int userId, bool flag, bool withAggregated)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var idParam = withAggregated ? $@"{DbSchemaName(dbType)}.qp_aggregated_and_self(@ids)" : "@ids";
var source = IdList(dbType, idParam);
var value = flag ? 1 : 0;
using (var cmd = DbCommandFactory.Create($@"
update content_item {WithRowLock(dbType)} set archive = {value}, modified = {Now(dbType)}, last_modified_by = @userId where content_item_id in (select id from {source});
update content_item {WithRowLock(dbType)} set locked_by = null, locked = null where content_item_id in (select id from {source});
", connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@userId", userId);
cmd.Parameters.Add(GetIdsDatatableParam("@ids", articleIds, dbType));
cmd.ExecuteNonQuery();
}
}
public static IEnumerable <DataRow> RemovingActions_GetContentsItemInfo(int?siteId, int?contentId, DbConnection connection)
{
var query = $@"select S.SITE_ID, S.SITE_NAME, C.CONTENT_ID, C.CONTENT_NAME, cast(COALESCE(I.ITEMS_COUNT, 0) as int) AS ITEMS_COUNT from
(select content_id, count(CONTENT_ITEM_ID) ITEMS_COUNT from content_item group by content_id) I
RIGHT JOIN CONTENT C ON C.CONTENT_ID = I.CONTENT_ID
JOIN SITE S ON S.SITE_ID = C.SITE_ID
where (c.content_id = @content_id OR @content_id is null)
and (s.site_id = @site_id OR @site_id is null)";
using (var cmd = DbCommandFactory.Create(query, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@content_id", (object)contentId ?? DBNull.Value);
cmd.Parameters.AddWithValue("@site_id", (object)siteId ?? DBNull.Value);
var dt = new DataTable();
DataAdapterFactory.Create(cmd).Fill(dt);
return(dt.AsEnumerable().ToArray());
}
}
public static void ValidateO2MValues(DbConnection sqlConnection, string xmlParameter, string message)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
var tableDefintionBlock = dbType == DatabaseType.SqlServer ? $@"
DECLARE @NewArticles TABLE (CONTENT_ITEM_ID int, ATTRIBUTE_ID int, DATA nvarchar(3500), BLOB_DATA nvarchar(max))
INSERT INTO @NewArticles
SELECT
doc.col.value('(CONTENT_ITEM_ID)[1]', 'int') CONTENT_ITEM_ID
,doc.col.value('(ATTRIBUTE_ID)[1]', 'int') ATTRIBUTE_ID
,doc.col.value('(DATA)[1]', 'nvarchar(3500)') DATA
,doc.col.value('(BLOB_DATA)[1]', 'nvarchar(max)') BLOB_DATA
FROM @xmlParameter.nodes('/PARAMETERS/FIELDVALUE') doc(col)
" : PgTableValuesBlock;
var tableUsing = dbType == DatabaseType.SqlServer ? "@NewArticles a" : "new a";
string sql = $@"{tableDefintionBlock}
select * from
(
select a.*, ca.ATTRIBUTE_NAME, rca.CONTENT_ID as RELATED_CONTENT_ID from {tableUsing}
inner join CONTENT_ATTRIBUTE ca on a.ATTRIBUTE_ID = ca.ATTRIBUTE_ID
inner join CONTENT_ATTRIBUTE rca on ca.RELATED_ATTRIBUTE_ID = rca.ATTRIBUTE_ID and ca.CONTENT_ID <> rca.CONTENT_ID
inner join CONTENT rc on rc.CONTENT_ID = rca.CONTENT_ID and rc.VIRTUAL_TYPE <> 3
where a.data != ''
) as a
left join CONTENT_ITEM ci on ci.CONTENT_ITEM_ID = cast(a.data as numeric)
where ci.CONTENT_ID is null or ci.CONTENT_ID <> a.RELATED_CONTENT_ID";
using (var cmd = DbCommandFactory.Create(sql, sqlConnection))
{
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xmlParameter", xmlParameter, dbType));
var resultDt = new DataTable();
DataAdapterFactory.Create(cmd).Fill(resultDt);
if (resultDt.AsEnumerable().Any())
{
var dr = resultDt.Rows[0];
var title = dr.Field <string>("ATTRIBUTE_NAME");
var data = dr.Field <string>("DATA");
var id = dr.Field <int>(FieldName.ContentItemId).ToString();
throw new ArgumentException(string.Format(message, id, title, data));
}
}
}
public static DataTable GetFieldTypes(DbConnection cnn, int[] ids)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(cnn);
var textType = dbType == DatabaseType.SqlServer ? "nvarchar(max)" : "text";
var text = $@"select attribute_id, BACK_RELATED_ATTRIBUTE_ID, attribute_type_id, link_id, is_classifier,
cast(case when coalesce(cast(enum_values as {textType}), '') <> '' then 1 else 0 end as bit) as is_string_enum
from content_attribute where attribute_id in (select id from {Common.IdList(dbType, "@ids")})";
using (var cmd = DbCommandFactory.Create(text, cnn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(Common.GetIdsDatatableParam("@ids", ids, dbType));
var dt = new DataTable();
DataAdapterFactory.Create(cmd).Fill(dt);
return(dt);
}
}
public static int CountChildArticles(DbConnection sqlConnection, int articleId, bool countArchived)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
var sql = "qp_count_child_articles";
if (dbType == DatabaseType.Postgres)
{
sql = $@"
select count(*)::int from content_data cd
inner join content_attribute ca on ca.attribute_id = cd.attribute_id
inner join content_item ci on ci.content_item_id = cd.content_item_id
where o2m_data = @article_id and not ca.aggregated and ci.archive = 0
";
}
using (var cmd = DbCommandFactory.Create(sql, sqlConnection))
{
string outParam;
cmd.Parameters.AddWithValue("@article_id", articleId);
if (dbType == DatabaseType.SqlServer)
{
outParam = "@count";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@count_archived", countArchived);
cmd.Parameters.Add(new SqlParameter(outParam, SqlDbType.Int)
{
Direction = ParameterDirection.Output
});
}
else
{
outParam = "count";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new NpgsqlParameter(outParam, NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Output
});
}
cmd.ExecuteNonQuery();
return((int)cmd.Parameters[outParam].Value);
}
}
public static void AdjustManyToMany(DbConnection connection, int id, int newId)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var sql = dbType == DatabaseType.SqlServer ? $@"
update item_to_item {WithRowLock(dbType)} set l_item_id = @newId where l_item_id = @id and r_item_id = @newId;
delete from item_to_item {WithRowLock(dbType)} where r_item_id = @id and l_item_id = @newId;
" : $@"
insert into item_to_item(link_id, l_item_id, r_item_id)
select link_id, @newId, @newId from item_to_item where l_item_id = @newId and r_item_id = @id;
delete from item_to_item where l_item_id = @newId and r_item_id = @id;
";
using (var cmd = DbCommandFactory.Create(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@newId", newId);
cmd.ExecuteNonQuery();
}
}
public static DataRow GetDefaultArticleRow(QPModelDataContext context, DbConnection connection, int contentId)
{
var databaseType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var sql = "qp_get_default_article";
if (databaseType == DatabaseType.Postgres)
{
var fields = context.FieldSet.Where(n => n.ContentId == contentId).OrderBy(n => n.Name).ToArray();
var fieldNames = fields.Select(n => n.Name.ToLower()).ToArray();
var fieldNameResults = String.Join(",", fieldNames.Select(n => $@"""{n}"" TEXT"));
var fieldSelects = String.Join(",", fields.Select(n => $@"""{n.Name.ToLower()}""::{PgSelectType((int)n.TypeId)}"));
sql = $@"
SELECT {fieldSelects} FROM crosstab('
select 0::numeric as content_item_id, lower(ca.attribute_name),
case when ca.attribute_type_id in (9, 10) then coalesce(ca.default_value, ca.default_blob_value)
else qp_correct_data(ca.default_value::text, ca.attribute_type_id, ca.attribute_size, ca.default_value)::text
end as value from content_attribute ca
inner join content c on ca.content_id = c.content_id
where c.content_id = {contentId}
order by 1,2
') AS final_result(content_item_id numeric, {fieldNameResults})" ;
}
using (var cmd = DbCommandFactory.Create(sql, connection))
{
if (databaseType == DatabaseType.Postgres)
{
cmd.CommandType = CommandType.Text;
}
else
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@content_id", contentId);
}
var ds = new DataSet();
DataAdapterFactory.Create(cmd).Fill(ds);
return(0 == ds.Tables.Count || 0 == ds.Tables[0].Rows.Count ? null : ds.Tables[0].Rows[0]);
}
}
public static int[] GetArticleIdsByGuids(DbConnection sqlConnection, Guid[] guids)
{
if (guids == null)
{
throw new ArgumentNullException(nameof(guids));
}
var databaseType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
var noLock = WithNoLock(databaseType);
var textCast = (databaseType == DatabaseType.Postgres) ? "::text" : "";
string xmlSource;
if (databaseType == DatabaseType.Postgres)
{
xmlSource = "XMLTABLE('/guids/guid' PASSING @xml COLUMNS unique_id uuid PATH '.')";
}
else
{
xmlSource = "(SELECT doc.col.value('.[1]', 'nvarchar(max)') UNIQUE_ID FROM @xml.nodes('/guids/guid') doc(col))";
}
var query = $"select coalesce(ci.content_item_id, 0), guids.unique_id{textCast} from {xmlSource} guids left join content_item ci {noLock} on ci.unique_id = guids.unique_id";
using (var cmd = DbCommandFactory.Create(query, sqlConnection))
{
var doc = new XDocument(new XElement("guids"));
doc.Root?.Add(guids.Select(n => new XElement("guid", n.ToString())));
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xml", doc.ToString(), databaseType));
var result = new Dictionary <Guid, int>();
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
result.Add(new Guid(dr.GetString(1)), (int)dr.GetDecimal(0));
}
}
return(guids.Select(n => result[n]).ToArray());
}
}
public static void GetContentModification(DbConnection sqlConnection, IEnumerable <int> articleIds, bool withAggregated, bool returnPublishedForLive, ref List <int> liveIds, ref List <int> stageIds)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
var idsParam = withAggregated ? $"{DbSchemaName(dbType)}.qp_aggregated_and_self(@ids)" : "@ids";
var source = IdList(dbType, idsParam);
var aggFunc = returnPublishedForLive ? "max" : "min";
var ids = articleIds as int[] ?? articleIds.ToArray();
if (!ids.Any())
{
return;
}
var sql = $@"
select cast(a.content_id as int) as content_id, cast({aggFunc}(a.is_published) as bit) as is_published from
(
select ci.content_item_id, ci.content_id,
case when st.status_type_name = 'Published' and {IsFalse(dbType, "ci.splitted")} then 1 else 0 end as is_published
from {source}
inner join content_item ci {WithNoLock(dbType)} on i.id = ci.content_item_id
inner join status_type st on ci.status_type_id = st.status_type_id
) a group by a.content_id
";
using (var cmd = DbCommandFactory.Create(sql, sqlConnection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(GetIdsDatatableParam("@ids", ids, dbType));
var dt = new DataTable();
DataAdapterFactory.Create(cmd).Fill(dt);
var rows = dt.AsEnumerable().ToArray();
stageIds = rows.Select(n => n.Field <int>("content_id")).ToList();
bool Predicate1(DataRow n) => n.Field <bool>("is_published");
bool Predicate2(DataRow n) => !n.Field <bool>("is_published");
liveIds = rows.Where(returnPublishedForLive ? (Func <DataRow, bool>)Predicate1 : Predicate2).Select(n => n.Field <int>("content_id")).ToList();
}
}
public static void BatchUpdate(DbConnection sqlConnection, DataTable articles, int userId)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
var sql = dbType == DatabaseType.SqlServer ? SqlBatchUpdateQuery : PgBatchUpdateQuery;
using (var cmd = DbCommandFactory.Create(sql, sqlConnection))
{
if (dbType == DatabaseType.SqlServer)
{
cmd.Parameters.Add(new SqlParameter("@values", SqlDbType.Structured)
{
TypeName = "Values", Value = articles
});
}
else
{
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@values", GetValuesDoc(articles).ToString(), dbType));
}
cmd.Parameters.AddWithValue("@userId", userId);
cmd.ExecuteNonQuery();
}
}