public override string ToString()
{
if (IsExpressionSupported() == false)
return string.Empty;
var updateItems = new List<string>();
var whereClauses = new List<string>();
foreach (var item in Set)
{
updateItems.Add(string.Format("{0} = {1}",
SqlSyntax.GetQuotedColumnName(item.Key),
GetQuotedValue(item.Value)));
}
if (IsAllRows)
{
whereClauses.Add("1 = 1");
}
else
{
foreach (var item in Where)
{
whereClauses.Add(string.Format("{0} {1} {2}",
SqlSyntax.GetQuotedColumnName(item.Key),
item.Value == null ? "IS" : "=",
GetQuotedValue(item.Value)));
}
}
return string.Format(SqlSyntax.UpdateData,
SqlSyntax.GetQuotedTableName(TableName),
string.Join(", ", updateItems.ToArray()),
string.Join(" AND ", whereClauses.ToArray()));
}
protected new void ReplaceColumn <T>(string tableName, string currentName, string newName)
{
if (DatabaseType.IsSqlCe())
{
AddColumn <T>(tableName, newName, out var sqls);
Execute.Sql($"UPDATE {SqlSyntax.GetQuotedTableName(tableName)} SET {SqlSyntax.GetQuotedColumnName(newName)}={SqlSyntax.GetQuotedColumnName(currentName)}").Do();
foreach (var sql in sqls)
{
Execute.Sql(sql).Do();
}
Delete.Column(currentName).FromTable(tableName).Do();
}
else
{
// TODO: Adding column just to get the SQL to execute after the rename
AddColumn <T>(tableName, newName, out var sqls);
Delete.Column(currentName).FromTable(tableName).Do();
Execute.Sql(SqlSyntax.FormatColumnRename(tableName, currentName, newName)).Do();
foreach (var sql in sqls)
{
Execute.Sql(sql).Do();
}
}
}
protected override string GetSql()
{
var constraintType = Constraint.IsPrimaryKeyConstraint ? "PRIMARY KEY" : "UNIQUE";
if (Constraint.IsPrimaryKeyConstraint && SqlSyntax.SupportsClustered())
{
constraintType += Constraint.IsPrimaryKeyClustered ? " CLUSTERED" : " NONCLUSTERED";
}
if (Constraint.IsNonUniqueConstraint)
{
constraintType = string.Empty;
}
var columns = new string[Constraint.Columns.Count];
for (var i = 0; i < Constraint.Columns.Count; i++)
{
columns[i] = SqlSyntax.GetQuotedColumnName(Constraint.Columns.ElementAt(i));
}
return(string.Format(
SqlSyntax.CreateConstraint,
SqlSyntax.GetQuotedTableName(Constraint.TableName),
SqlSyntax.GetQuotedName(Constraint.ConstraintName),
constraintType,
string.Join(", ", columns)));
}
public IEnumerable <ITemplate> GetDescendants(string alias)
{
var sql = GetBaseQuery(false);
if (alias.IsNullOrWhiteSpace() == false)
{
//unfortunately SQLCE doesn't support scalar subqueries in the where clause, otherwise we could have done this
// in a single query, now we have to lookup the path to acheive the same thing
var path = Database.ExecuteScalar <string>(
"SELECT umbracoNode.path FROM cmsTemplate INNER JOIN umbracoNode ON cmsTemplate.nodeId = umbracoNode.id WHERE cmsTemplate.alias = @alias", new { alias = alias });
if (path.IsNullOrWhiteSpace())
{
return(Enumerable.Empty <ITemplate>());
}
sql.Where(@"(umbracoNode." + SqlSyntax.GetQuotedColumnName("path") + @" LIKE @query)", new { query = path + ",%" });
}
sql.OrderBy("umbracoNode." + SqlSyntax.GetQuotedColumnName("level"));
var dtos = Database.Fetch <TemplateDto, NodeDto>(sql).ToArray();
if (dtos.Length == 0)
{
return(Enumerable.Empty <ITemplate>());
}
var axisDefos = GetAxisDefinitions(dtos).ToArray();
return(dtos.Select(x => MapFromDto(x, axisDefos)));
}
protected override Sql GetBaseQuery(bool isCount)
{
var sqlx = string.Format("LEFT OUTER JOIN {0} {1} ON ({1}.{2}={0}.{2} AND {1}.{3}=1)",
SqlSyntax.GetQuotedTableName("cmsDocument"),
SqlSyntax.GetQuotedTableName("cmsDocument2"),
SqlSyntax.GetQuotedColumnName("nodeId"),
SqlSyntax.GetQuotedColumnName("published"));
var sql = new Sql();
sql.Select(isCount ? "COUNT(*)" : "*")
.From <DocumentDto>()
.InnerJoin <ContentVersionDto>()
.On <DocumentDto, ContentVersionDto>(left => left.VersionId, right => right.VersionId)
.InnerJoin <ContentDto>()
.On <ContentVersionDto, ContentDto>(left => left.NodeId, right => right.NodeId)
.InnerJoin <NodeDto>()
.On <ContentDto, NodeDto>(left => left.NodeId, right => right.NodeId)
// cannot do this because PetaPoco does not know how to alias the table
//.LeftOuterJoin<DocumentPublishedReadOnlyDto>()
//.On<DocumentDto, DocumentPublishedReadOnlyDto>(left => left.NodeId, right => right.NodeId)
// so have to rely on writing our own SQL
.Append(sqlx /*, new { @published = true }*/)
.Where <NodeDto>(x => x.NodeObjectType == NodeObjectTypeId);
return(sql);
}
/// <summary>
/// Gets paged document descendants as XML by path
/// </summary>
/// <param name="path">Path starts with</param>
/// <param name="pageIndex">Page number</param>
/// <param name="pageSize">Page size</param>
/// <param name="orderBy"></param>
/// <param name="totalRecords">Total records the query would return without paging</param>
/// <returns>A paged enumerable of XML entries of content items</returns>
public virtual IEnumerable <XElement> GetPagedXmlEntriesByPath(string path, long pageIndex, int pageSize, string[] orderBy, out long totalRecords)
{
var query = new Sql().Select(string.Format("umbracoNode.id, cmsContentXml.{0}", SqlSyntax.GetQuotedColumnName("xml")))
.From("umbracoNode")
.InnerJoin("cmsContentXml").On("cmsContentXml.nodeId = umbracoNode.id");
if (path == "-1")
{
query.Where("umbracoNode.nodeObjectType = @type", new { type = NodeObjectTypeId });
}
else
{
query.Where(string.Format("umbracoNode.{0} LIKE (@0)", SqlSyntax.GetQuotedColumnName("path")), path.EnsureEndsWith(",%"));
}
//each order by param needs to be in a bracket! see: https://github.com/toptensoftware/PetaPoco/issues/177
query.OrderBy(orderBy == null
? "(umbracoNode.id)"
: string.Join(",", orderBy.Select(x => string.Format("({0})", SqlSyntax.GetQuotedColumnName(x)))));
var pagedResult = Database.Page <ContentXmlDto>(pageIndex + 1, pageSize, query);
totalRecords = pagedResult.TotalItems;
return(pagedResult.Items.Select(dto => XElement.Parse(dto.Xml)));
}
public override string ToString()
{
var deleteItems = new List <string>();
if (IsAllRows)
{
deleteItems.Add(string.Format(SqlSyntax.DeleteData, SqlSyntax.GetQuotedTableName(TableName), "1 = 1"));
}
else
{
foreach (var row in Rows)
{
var whereClauses = new List <string>();
foreach (KeyValuePair <string, object> item in row)
{
whereClauses.Add(string.Format("{0} {1} {2}",
SqlSyntax.GetQuotedColumnName(item.Key),
item.Value == null ? "IS" : "=",
GetQuotedValue(item.Value)));
}
deleteItems.Add(string.Format(SqlSyntax.DeleteData,
SqlSyntax.GetQuotedTableName(TableName),
String.Join(" AND ", whereClauses.ToArray())));
}
}
return(String.Join("; ", deleteItems.ToArray()));
}
public Dictionary <string, Guid> GetDictionaryItemKeyMap()
{
var columns = new[] { "key", "id" }.Select(x => (object)SqlSyntax.GetQuotedColumnName(x)).ToArray();
var sql = Sql().Select(columns).From <DictionaryDto>();
return(Database.Fetch <DictionaryItemKeyIdDto>(sql).ToDictionary(x => x.Key, x => x.Id));
}
public IEnumerable <IDictionaryItem> GetDictionaryItemDescendants(Guid?parentId)
{
//This methods will look up children at each level, since we do not store a path for dictionary (ATM), we need to do a recursive
// lookup to get descendants. Currently this is the most efficient way to do it
Func <Guid[], IEnumerable <IEnumerable <IDictionaryItem> > > getItemsFromParents = guids =>
{
//needs to be in groups of 2000 because we are doing an IN clause and there's a max parameter count that can be used.
return(guids.InGroupsOf(2000)
.Select(@group =>
{
var sqlClause = GetBaseQuery(false)
.Where <DictionaryDto>(x => x.Parent != null)
.Where($"{SqlSyntax.GetQuotedColumnName("parent")} IN (@parentIds)", new { parentIds = @group });
var translator = new SqlTranslator <IDictionaryItem>(sqlClause, Query <IDictionaryItem>());
var sql = translator.Translate();
sql.OrderBy <DictionaryDto>(x => x.UniqueId);
return Database
.FetchOneToMany <DictionaryDto>(x => x.LanguageTextDtos, sql)
.Select(ConvertFromDto);
}));
};
var childItems = parentId.HasValue == false
? new[] { GetRootDictionaryItems() }
: getItemsFromParents(new[] { parentId.Value });
return(childItems.SelectRecursive(items => getItemsFromParents(items.Select(x => x.Key).ToArray())).SelectMany(items => items));
}
protected virtual string GetFieldName(Database.PocoData pocoData, string name)
{
var column = pocoData.Columns.FirstOrDefault(x => x.Value.PropertyInfo.Name == name);
return(string.Format("{0}.{1}",
SqlSyntax.GetQuotedTableName(pocoData.TableInfo.TableName),
SqlSyntax.GetQuotedColumnName(column.Value.ColumnName)));
}
protected override string GetSql() =>
HasDefaultConstraint
? string.Format(
SqlSyntax.DeleteDefaultConstraint,
SqlSyntax.GetQuotedTableName(TableName),
SqlSyntax.GetQuotedColumnName(ColumnName),
SqlSyntax.GetQuotedName(ConstraintName))
: string.Empty;
protected virtual string GetFieldName(PocoData pocoData, string name, string alias)
{
var column = pocoData.Columns.FirstOrDefault(x => x.Value.MemberInfoData.Name == name);
var tableName = SqlSyntax.GetQuotedTableName(alias ?? pocoData.TableInfo.TableName);
var columnName = SqlSyntax.GetQuotedColumnName(column.Value.ColumnName);
return(tableName + "." + columnName);
}
public override string ToString()
{
if (IsExpressionSupported() == false)
{
return(string.Empty);
}
var sb = new StringBuilder();
if (EnabledIdentityInsert && SqlSyntax.SupportsIdentityInsert())
{
sb.AppendLine(string.Format("SET IDENTITY_INSERT {0} ON;", SqlSyntax.GetQuotedTableName(TableName)));
if (CurrentDatabaseProvider == DatabaseProviders.SqlServer || CurrentDatabaseProvider == DatabaseProviders.SqlServerCE)
{
sb.AppendLine("GO");
}
}
try
{
foreach (var item in Rows)
{
var cols = "";
var vals = "";
foreach (var keyVal in item)
{
cols += SqlSyntax.GetQuotedColumnName(keyVal.Key) + ",";
vals += GetQuotedValue(keyVal.Value) + ",";
}
cols = cols.TrimEnd(',');
vals = vals.TrimEnd(',');
var sql = string.Format(SqlSyntax.InsertData,
SqlSyntax.GetQuotedTableName(TableName),
cols, vals);
sb.AppendLine(string.Format("{0};", sql));
if (CurrentDatabaseProvider == DatabaseProviders.SqlServer || CurrentDatabaseProvider == DatabaseProviders.SqlServerCE)
{
sb.AppendLine("GO");
}
}
}
finally
{
if (EnabledIdentityInsert && SqlSyntax.SupportsIdentityInsert())
{
sb.AppendLine(string.Format("SET IDENTITY_INSERT {0} OFF;", SqlSyntax.GetQuotedTableName(TableName)));
if (CurrentDatabaseProvider == DatabaseProviders.SqlServer || CurrentDatabaseProvider == DatabaseProviders.SqlServerCE)
{
sb.AppendLine("GO");
}
}
}
return(sb.ToString());
}
/// <inheritdoc />
public void ClearCurrent(string source, string context, string action)
{
var sql = new Sql($@"UPDATE {SqlSyntax.GetQuotedTableName(ConsentDto.TableName)}
SET {SqlSyntax.GetQuotedColumnName("current")} = @0
WHERE {SqlSyntax.GetQuotedColumnName("source")} = @1 AND {SqlSyntax.GetQuotedColumnName("context")} = @2 AND {SqlSyntax.GetQuotedColumnName("action")} = @3
AND {SqlSyntax.GetQuotedColumnName("current")} <> @0 ", false, source, context, action);
Database.Execute(sql);
}
protected void ReplaceColumn <T>(string tableName, string currentName, string newName)
{
AddColumn <T>(tableName, newName, out var sqls);
Execute.Sql($"UPDATE {SqlSyntax.GetQuotedTableName(tableName)} SET {SqlSyntax.GetQuotedColumnName(newName)}={SqlSyntax.GetQuotedColumnName(currentName)}").Do();
foreach (var sql in sqls)
{
Execute.Sql(sql).Do();
}
Delete.Column(currentName).FromTable(tableName).Do();
}
private void DissociateRolesInternal(int[] memberIds, string[] roleNames)
{
var existingSql = Sql()
.SelectAll()
.From <NodeDto>()
.Where <NodeDto>(dto => dto.NodeObjectType == NodeObjectTypeId)
.Where("umbracoNode." + SqlSyntax.GetQuotedColumnName("text") + " in (@names)", new { names = roleNames });
var existingRolesIds = Database.Fetch <NodeDto>(existingSql).Select(x => x.NodeId).ToArray();
Database.Execute("DELETE FROM cmsMember2MemberGroup WHERE Member IN (@memberIds) AND MemberGroup IN (@memberGroups)",
new { /*memberIds =*/ memberIds, memberGroups = existingRolesIds });
}
/// <inheritdoc />
// only invoked from tests
public void Remove(int contentId, int propertyTypeId, IEnumerable <ITag> tags)
{
var tagSetSql = GetTagSet(tags);
var group = SqlSyntax.GetQuotedColumnName("group");
var deleteSql = $@"DELETE FROM cmsTagRelationship WHERE nodeId = {contentId} AND propertyTypeId = {propertyTypeId} AND tagId IN (
SELECT id FROM cmsTags INNER JOIN {tagSetSql} ON (
tagSet.tag = cmsTags.tag AND tagSet.{group} = cmsTags.{group} AND COALESCE(tagSet.languageId, -1) = COALESCE(cmsTags.languageId, -1)
)
)";
Database.Execute(deleteSql);
}
// this is a clever way to produce an SQL statement like this:
//
// (
// SELECT 'Spacesdd' AS Tag, 'default' AS [group]
// UNION
// SELECT 'Cool' AS tag, 'default' AS [group]
// ) AS tagSet
//
// which we can then use to reduce queries
//
private string GetTagSet(IEnumerable <ITag> tags)
{
var sql = new StringBuilder();
var group = SqlSyntax.GetQuotedColumnName("group");
var first = true;
sql.Append("(");
foreach (ITag tag in tags)
{
if (first)
{
first = false;
}
else
{
sql.Append(" UNION ");
}
// HACK: SQLite (or rather SQL server setup was a hack)
if (SqlContext.DatabaseType.IsSqlServer())
{
sql.Append("SELECT N'");
}
else
{
sql.Append("SELECT '");
}
sql.Append(SqlSyntax.EscapeString(tag.Text));
sql.Append("' AS tag, '");
sql.Append(SqlSyntax.EscapeString(tag.Group));
sql.Append("' AS ");
sql.Append(group);
sql.Append(" , ");
if (tag.LanguageId.HasValue)
{
sql.Append(tag.LanguageId);
}
else
{
sql.Append("NULL");
}
sql.Append(" AS languageId");
}
sql.Append(") AS tagSet");
return(sql.ToString());
}
protected override void PersistNewItem(IDataTypeDefinition entity)
{
((DataTypeDefinition)entity).AddingEntity();
//ensure a datatype has a unique name before creating it
entity.Name = EnsureUniqueNodeName(entity.Name);
//TODO: should the below be removed?
//Cannot add a duplicate data type
var exists = Database.ExecuteScalar <int>(@"SELECT COUNT(*) FROM cmsDataType
INNER JOIN umbracoNode ON cmsDataType.nodeId = umbracoNode.id
WHERE umbracoNode." + SqlSyntax.GetQuotedColumnName("text") + "= @name", new { name = entity.Name });
if (exists > 0)
{
throw new DuplicateNameException("A data type with the name " + entity.Name + " already exists");
}
var factory = new DataTypeDefinitionFactory(NodeObjectTypeId);
var dto = factory.BuildDto(entity);
//Logic for setting Path, Level and SortOrder
var parent = Database.First <NodeDto>("WHERE id = @ParentId", new { ParentId = entity.ParentId });
int level = parent.Level + 1;
int sortOrder =
Database.ExecuteScalar <int>("SELECT COUNT(*) FROM umbracoNode WHERE parentID = @ParentId AND nodeObjectType = @NodeObjectType",
new { ParentId = entity.ParentId, NodeObjectType = NodeObjectTypeId });
//Create the (base) node data - umbracoNode
var nodeDto = dto.NodeDto;
nodeDto.Path = parent.Path;
nodeDto.Level = short.Parse(level.ToString(CultureInfo.InvariantCulture));
nodeDto.SortOrder = sortOrder;
var o = Database.IsNew(nodeDto) ? Convert.ToInt32(Database.Insert(nodeDto)) : Database.Update(nodeDto);
//Update with new correct path
nodeDto.Path = string.Concat(parent.Path, ",", nodeDto.NodeId);
Database.Update(nodeDto);
//Update entity with correct values
entity.Id = nodeDto.NodeId; //Set Id on entity to ensure an Id is set
entity.Path = nodeDto.Path;
entity.SortOrder = sortOrder;
entity.Level = level;
dto.DataTypeId = nodeDto.NodeId;
Database.Insert(dto);
entity.ResetDirtyProperties();
}
protected override void PersistUpdatedItem(IDataTypeDefinition entity)
{
entity.Name = EnsureUniqueNodeName(entity.Name, entity.Id);
//Cannot change to a duplicate alias
var exists = Database.ExecuteScalar <int>(@"SELECT COUNT(*) FROM cmsDataType
INNER JOIN umbracoNode ON cmsDataType.nodeId = umbracoNode.id
WHERE umbracoNode." + SqlSyntax.GetQuotedColumnName("text") + @"= @name
AND umbracoNode.id <> @id",
new { id = entity.Id, name = entity.Name });
if (exists > 0)
{
throw new DuplicateNameException("A data type with the name " + entity.Name + " already exists");
}
//Updates Modified date
((DataTypeDefinition)entity).UpdatingEntity();
//Look up parent to get and set the correct Path if ParentId has changed
if (entity.IsPropertyDirty("ParentId"))
{
var parent = Database.First <NodeDto>("WHERE id = @ParentId", new { ParentId = entity.ParentId });
entity.Path = string.Concat(parent.Path, ",", entity.Id);
entity.Level = parent.Level + 1;
var maxSortOrder =
Database.ExecuteScalar <int>(
"SELECT coalesce(max(sortOrder),0) FROM umbracoNode WHERE parentid = @ParentId AND nodeObjectType = @NodeObjectType",
new { ParentId = entity.ParentId, NodeObjectType = NodeObjectTypeId });
entity.SortOrder = maxSortOrder + 1;
}
var factory = new DataTypeDefinitionFactory(NodeObjectTypeId);
//Look up DataTypeDefinition entry to get Primary for updating the DTO
var dataTypeDto = Database.SingleOrDefault <DataTypeDto>("WHERE nodeId = @Id", new { Id = entity.Id });
factory.SetPrimaryKey(dataTypeDto.PrimaryKey);
var dto = factory.BuildDto(entity);
//Updates the (base) node data - umbracoNode
var nodeDto = dto.NodeDto;
Database.Update(nodeDto);
Database.Update(dto);
//NOTE: This is a special case, we need to clear the custom cache for pre-values here so they are not stale if devs
// are querying for them in the Saved event (before the distributed call cache is clearing it)
IsolatedCache.ClearCacheItem(GetPrefixedCacheKey(entity.Id));
entity.ResetDirtyProperties();
}
/// <summary>
/// This is a clever way to produce an SQL statement like this:
///
/// (select 'Spacesdd' as Tag, 'default' as [Group]
/// union
/// select 'Cool' as Tag, 'default' as [Group]
/// ) as TagSet
///
/// This allows us to use the tags to be inserted as a temporary in memory table.
/// </summary>
/// <param name="tagsToInsert"></param>
/// <returns></returns>
private string GetTagSet(IEnumerable <ITag> tagsToInsert)
{
//TODO: Fix this query, since this is going to be basically a unique query each time, this will cause some mem usage in peta poco,
// and surely there's a nicer way!
//TODO: When we fix, be sure to remove the @ symbol escape
var array = tagsToInsert
.Select(tag =>
string.Format("select '{0}' as Tag, '{1}' as " + SqlSyntax.GetQuotedColumnName("group") + @"",
PetaPocoExtensions.EscapeAtSymbols(tag.Text.Replace("'", "''")), tag.Group))
.ToArray();
return("(" + string.Join(" union ", array).Replace(" ", " ") + ") as TagSet");
}
protected override string GetSql()
{
var stmts = new StringBuilder();
if (EnabledIdentityInsert && SqlSyntax.SupportsIdentityInsert())
{
stmts.AppendLine($"SET IDENTITY_INSERT {SqlSyntax.GetQuotedTableName(TableName)} ON");
AppendStatementSeparator(stmts);
}
try
{
foreach (var item in Rows)
{
var cols = new StringBuilder();
var vals = new StringBuilder();
var first = true;
foreach (var keyVal in item)
{
if (first)
{
first = false;
}
else
{
cols.Append(",");
vals.Append(",");
}
cols.Append(SqlSyntax.GetQuotedColumnName(keyVal.Key));
vals.Append(GetQuotedValue(keyVal.Value));
}
var sql = string.Format(SqlSyntax.InsertData, SqlSyntax.GetQuotedTableName(TableName), cols, vals);
stmts.Append(sql);
AppendStatementSeparator(stmts);
}
}
finally
{
if (EnabledIdentityInsert && SqlSyntax.SupportsIdentityInsert())
{
stmts.AppendLine($"SET IDENTITY_INSERT {SqlSyntax.GetQuotedTableName(TableName)} OFF");
AppendStatementSeparator(stmts);
}
}
return(stmts.ToString());
}
public IEnumerable <IRedirectUrl> GetAllUrls(int rootContentId, long pageIndex, int pageSize, out long total)
{
Sql <ISqlContext> sql = GetBaseQuery(false)
.Where(
string.Format("{0}.{1} LIKE @path", SqlSyntax.GetQuotedTableName("umbracoNode"),
SqlSyntax.GetQuotedColumnName("path")), new { path = "%," + rootContentId + ",%" })
.OrderByDescending <RedirectUrlDto>(x => x.CreateDateUtc);
Page <RedirectUrlDto> result = Database.Page <RedirectUrlDto>(pageIndex + 1, pageSize, sql);
total = Convert.ToInt32(result.TotalItems);
IEnumerable <IRedirectUrl> rules = result.Items.Select(Map);
return(rules);
}
private Sql GetTagsQuerySelect(bool withGrouping = false)
{
var sql = new Sql();
if (withGrouping)
{
sql = sql.Select("cmsTags.id, cmsTags.tag, cmsTags." + SqlSyntax.GetQuotedColumnName("group") + @", Count(*) NodeCount");
}
else
{
sql = sql.Select("DISTINCT cmsTags.*");
}
return(sql);
}
protected override IEnumerable <string> GetDeleteClauses()
{
var list = new List <string>
{
"DELETE FROM cmsTask WHERE userId = @Id",
"DELETE FROM cmsTask WHERE parentUserId = @Id",
"DELETE FROM umbracoUser2NodePermission WHERE userId = @Id",
"DELETE FROM umbracoUser2NodeNotify WHERE userId = @Id",
"DELETE FROM umbracoUser2app WHERE " + SqlSyntax.GetQuotedColumnName("user") + "=@Id",
"DELETE FROM umbracoUser WHERE id = @Id",
"DELETE FROM umbracoExternalLogin WHERE id = @Id"
};
return(list);
}
/// <inheritdoc />
// only invoked from ContentRepositoryBase with all cultures + replaceTags being true
public void Assign(int contentId, int propertyTypeId, IEnumerable <ITag> tags, bool replaceTags = true)
{
// to no-duplicates array
ITag[] tagsA = tags.Distinct(new TagComparer()).ToArray();
// replacing = clear all
if (replaceTags)
{
Sql <ISqlContext> sql0 = Sql().Delete <TagRelationshipDto>()
.Where <TagRelationshipDto>(x => x.NodeId == contentId && x.PropertyTypeId == propertyTypeId);
Database.Execute(sql0);
}
// no tags? nothing else to do
if (tagsA.Length == 0)
{
return;
}
// tags
// using some clever logic (?) to insert tags that don't exist in 1 query
// must coalesce languageId because equality of NULLs does not exist
var tagSetSql = GetTagSet(tagsA);
var group = SqlSyntax.GetQuotedColumnName("group");
// insert tags
var sql1 = $@"INSERT INTO cmsTags (tag, {group}, languageId)
SELECT tagSet.tag, tagSet.{group}, tagSet.languageId
FROM {tagSetSql}
LEFT OUTER JOIN cmsTags ON (tagSet.tag = cmsTags.tag AND tagSet.{group} = cmsTags.{group} AND COALESCE(tagSet.languageId, -1) = COALESCE(cmsTags.languageId, -1))
WHERE cmsTags.id IS NULL";
Database.Execute(sql1);
// insert relations
var sql2 = $@"INSERT INTO cmsTagRelationship (nodeId, propertyTypeId, tagId)
SELECT {contentId}, {propertyTypeId}, tagSet2.Id
FROM (
SELECT t.Id
FROM {tagSetSql}
INNER JOIN cmsTags as t ON (tagSet.tag = t.tag AND tagSet.{group} = t.{group} AND COALESCE(tagSet.languageId, -1) = COALESCE(t.languageId, -1))
) AS tagSet2
LEFT OUTER JOIN cmsTagRelationship r ON (tagSet2.id = r.tagId AND r.nodeId = {contentId} AND r.propertyTypeID = {propertyTypeId})
WHERE r.tagId IS NULL";
Database.Execute(sql2);
}
protected override string GetSql()
{
var updateItems = Set?.Select(x => $"{SqlSyntax.GetQuotedColumnName(x.Key)} = {GetQuotedValue(x.Value)}");
var whereClauses = IsAllRows
? null
: Where?.Select(x => $"{SqlSyntax.GetQuotedColumnName(x.Key)} {(x.Value == null ? "IS" : "=")} {GetQuotedValue(x.Value)}");
var whereClause = whereClauses == null
? "(1=1)"
: string.Join(" AND ", whereClauses.ToArray());
return(string.Format(SqlSyntax.UpdateData,
SqlSyntax.GetQuotedTableName(TableName),
string.Join(", ", updateItems ?? Array.Empty <string>()),
whereClause));
}
public IEnumerable <IRedirectUrl> SearchUrls(string searchTerm, long pageIndex, int pageSize, out long total)
{
Sql <ISqlContext> sql = GetBaseQuery(false)
.Where(
string.Format("{0}.{1} LIKE @url", SqlSyntax.GetQuotedTableName("umbracoRedirectUrl"),
SqlSyntax.GetQuotedColumnName("Url")),
new { url = "%" + searchTerm.Trim().ToLowerInvariant() + "%" })
.OrderByDescending <RedirectUrlDto>(x => x.CreateDateUtc);
Page <RedirectUrlDto> result = Database.Page <RedirectUrlDto>(pageIndex + 1, pageSize, sql);
total = Convert.ToInt32(result.TotalItems);
IEnumerable <IRedirectUrl> rules = result.Items.Select(Map);
return(rules);
}
protected void ReplaceColumn <T>(string tableName, string currentName, string newName)
{
if (DatabaseType.IsSqlCe())
{
AddColumn <T>(tableName, newName, out var sqls);
Execute.Sql($"UPDATE {SqlSyntax.GetQuotedTableName(tableName)} SET {SqlSyntax.GetQuotedColumnName(newName)}={SqlSyntax.GetQuotedColumnName(currentName)}").Do();
foreach (var sql in sqls)
{
Execute.Sql(sql).Do();
}
Delete.Column(currentName).FromTable(tableName).Do();
}
else
{
Execute.Sql(SqlSyntax.FormatColumnRename(tableName, currentName, newName)).Do();
AlterColumn <T>(tableName, newName);
}
}
/// <summary>
/// Gets paged media results
/// </summary>
/// <param name="query">Query to excute</param>
/// <param name="pageIndex">Page number</param>
/// <param name="pageSize">Page size</param>
/// <param name="totalRecords">Total records query would return without paging</param>
/// <param name="orderBy">Field to order by</param>
/// <param name="orderDirection">Direction to order by</param>
/// <param name="filter">Search text filter</param>
/// <returns>An Enumerable list of <see cref="IMedia"/> objects</returns>
public IEnumerable <IMedia> GetPagedResultsByQuery(IQuery <IMedia> query, long pageIndex, int pageSize, out long totalRecords,
string orderBy, Direction orderDirection, string filter = "")
{
var args = new List <object>();
var sbWhere = new StringBuilder();
Func <Tuple <string, object[]> > filterCallback = null;
if (filter.IsNullOrWhiteSpace() == false)
{
sbWhere.Append("AND (umbracoNode." + SqlSyntax.GetQuotedColumnName("text") + " LIKE @" + args.Count + ")");
args.Add("%" + filter + "%");
filterCallback = () => new Tuple <string, object[]>(sbWhere.ToString().Trim(), args.ToArray());
}
return(GetPagedResultsByQuery <ContentVersionDto, Models.Media>(query, pageIndex, pageSize, out totalRecords,
new Tuple <string, string>("cmsContentVersion", "contentId"),
ProcessQuery, orderBy, orderDirection,
filterCallback));
}
