public void Dates_Formatted_Properly()
{
var sql = new Sql();
sql.Select("*").From<DocumentDto>();
var dt = new DateTime(2013, 11, 21, 13, 25, 55);
var query = Query<IContent>.Builder.Where(x => x.ExpireDate <= dt);
var translator = new SqlTranslator<IContent>(sql, query);
var result = translator.Translate();
Assert.IsTrue(result.SQL.Contains("[expireDate] <= '2013-11-21 13:25:55'"));
}
public void Can_Build_ParentId_Query_For_IContent()
{
// Arrange
var sql = new Sql();
sql.Select("*");
sql.From("umbracoNode");
var query = Query<IContent>.Builder.Where(x => x.ParentId == -1);
// Act
var translator = new SqlTranslator<IContent>(sql, query);
var result = translator.Translate();
var strResult = result.SQL;
string expectedResult = "SELECT *\nFROM umbracoNode\nWHERE ([umbracoNode].[parentID] = -1)";
// Assert
Assert.That(strResult, Is.Not.Empty);
Assert.That(strResult, Is.EqualTo(expectedResult));
Console.WriteLine(strResult);
}
public void Can_Build_ContentTypeAlias_Query_For_IContentType()
{
// Arrange
var sql = new Sql();
sql.Select("*");
sql.From("umbracoNode");
var query = Query<IContentType>.Builder.Where(x => x.Alias == "umbTextpage");
// Act
var translator = new SqlTranslator<IContentType>(sql, query);
var result = translator.Translate();
var strResult = result.SQL;
string expectedResult = "SELECT *\nFROM umbracoNode\nWHERE ([cmsContentType].[alias] = 'umbTextpage')";
// Assert
Assert.That(strResult, Is.Not.Empty);
Assert.That(strResult, Is.EqualTo(expectedResult));
Console.WriteLine(strResult);
}
protected IEnumerable <int> PerformGetByQuery(IQuery <PropertyType> query)
{
// used by DataTypeService to remove properties
// from content types if they have a deleted data type - see
// notes in DataTypeService.Delete as it's a bit weird
var sqlClause = Sql()
.SelectAll()
.From <PropertyTypeGroupDto>()
.RightJoin <PropertyTypeDto>()
.On <PropertyTypeGroupDto, PropertyTypeDto>(left => left.Id, right => right.PropertyTypeGroupId)
.InnerJoin <DataTypeDto>()
.On <PropertyTypeDto, DataTypeDto>(left => left.DataTypeId, right => right.NodeId);
var translator = new SqlTranslator <PropertyType>(sqlClause, query);
var sql = translator.Translate()
.OrderBy <PropertyTypeDto>(x => x.PropertyTypeGroupId);
return(Database
.FetchOneToMany <PropertyTypeGroupDto>(x => x.PropertyTypeDtos, sql)
.Select(x => x.ContentTypeNodeId).Distinct());
}
public void QueryMember_WithSubQuery()
{
IScopeProvider provider = ScopeProvider;
using (provider.CreateScope())
{
IQuery <IMember> query = provider.CreateQuery <IMember>().Where(x =>
((Member)x).LongStringPropertyValue.Contains("1095") &&
((Member)x).PropertyTypeAlias == "headshot");
Sql <ISqlContext> sqlSubquery = GetSubquery();
var translator = new SqlTranslator <IMember>(sqlSubquery, query);
Sql <ISqlContext> subquery = translator.Translate();
Sql <ISqlContext> sql = GetBaseQuery(false)
.Append("WHERE umbracoNode.id IN (" + subquery.SQL + ")", subquery.Arguments)
.OrderByDescending <ContentVersionDto>(x => x.VersionDate)
.OrderBy <NodeDto>(x => x.SortOrder);
Debug.Print(sql.SQL);
Assert.That(sql.SQL, Is.Not.Empty);
}
}
protected IEnumerable <int> PerformGetByQuery(IQuery <PropertyType> query)
{
var sqlClause = new Sql();
sqlClause.Select("*")
.From <PropertyTypeGroupDto>()
.RightJoin <PropertyTypeDto>()
.On <PropertyTypeGroupDto, PropertyTypeDto>(left => left.Id, right => right.PropertyTypeGroupId)
.InnerJoin <DataTypeDto>()
.On <PropertyTypeDto, DataTypeDto>(left => left.DataTypeId, right => right.DataTypeId);
var translator = new SqlTranslator <PropertyType>(sqlClause, query);
var sql = translator.Translate()
.OrderBy <PropertyTypeDto>(x => x.PropertyTypeGroupId);
var dtos = Database.Fetch <PropertyTypeGroupDto, PropertyTypeDto, DataTypeDto, PropertyTypeGroupDto>(new GroupPropertyTypeRelator().Map, sql);
foreach (var dto in dtos.DistinctBy(x => x.ContentTypeNodeId))
{
yield return(dto.ContentTypeNodeId);
}
}
public IEnumerable <IRelation> GetPagedRelationsByQuery(IQuery <IRelation>?query, long pageIndex, int pageSize, out long totalRecords, Ordering?ordering)
{
Sql <ISqlContext> sql = GetBaseQuery(false);
if (ordering == null || ordering.IsEmpty)
{
ordering = Ordering.By(SqlSyntax.GetQuotedColumn(Constants.DatabaseSchema.Tables.Relation, "id"));
}
var translator = new SqlTranslator <IRelation>(sql, query);
sql = translator.Translate();
// apply ordering
ApplyOrdering(ref sql, ordering);
var pageIndexToFetch = pageIndex + 1;
Page <RelationDto>?page = Database.Page <RelationDto>(pageIndexToFetch, pageSize, sql);
List <RelationDto>?dtos = page.Items;
totalRecords = page.TotalItems;
var relTypes = _relationTypeRepository.GetMany(dtos.Select(x => x.RelationType).Distinct().ToArray())?
.ToDictionary(x => x.Id, x => x);
var result = dtos.Select(r =>
{
if (relTypes is null || !relTypes.TryGetValue(r.RelationType, out IRelationType? relType))
{
throw new InvalidOperationException(string.Format("RelationType with Id: {0} doesn't exist", r.RelationType));
}
return(DtoToEntity(r, relType));
}).WhereNotNull().ToList();
return(result);
}
public async Task CreateTableFromTypeMap(DbConnection con, string tableName, TypeMap typeMap)
{
var sqlMaps = SqlTranslator.SqlMapsFromTypeMaps(typeMap, this);
if (typeMap == null || typeMap.Count == 0)
{
throw new Exception("Type Map null or empty");
}
string sqlCreate = $"CREATE TABLE {tableName} (\n";
sqlCreate += "Id BIGINT AUTO_INCREMENT PRIMARY KEY,\n";
foreach (var map in sqlMaps)
{
sqlCreate += Column(map);
}
sqlCreate = sqlCreate.Substring(0, sqlCreate.Length - 2) + "\n)";
sqlCreate += $" ENGINE={engine}";
await con.RunCommandAsync(sqlCreate);
}
public void WhereBuilderValidity_Affect_QueryValidity()
{
var translator = new SqlTranslator();
translator.AddTable(typeof(Car));
translator.AddTable(typeof(CarMaker));
var whereBuilderFactory = new WhereBuilderFactory(() => new Comparator());
var whereIsValid = CountryCondition(whereBuilderFactory)
.TryBuild(translator, out _);
Assert.False(whereIsValid, "The where clause needs to be invalid");
var basicQuery = GetBuilder().DeleteFrom <Car>();
Assert.True(basicQuery.TryBuild(out _), "The basic query should be valid");
var isValid = basicQuery
.WhereFactory(CountryCondition) // Fail condition, country is not joined
.TryBuild(out var query);
Assert.False(isValid, "An invalid where should cause an otherwise valid query to be invalid");
}
protected override string FixDefaultValue(string defaultValue)
{
if (string.IsNullOrEmpty(defaultValue))
{
return(defaultValue);
}
//we only have getdate() function
var getDate = defaultValue.IndexOf("current_timestamp", StringComparison.OrdinalIgnoreCase);
if (getDate != -1)
{
defaultValue = defaultValue.Remove(getDate, "current_timestamp".Length).Insert(getDate, "getdate()");
}
var sysDateTime = defaultValue.IndexOf("sysdatetime()", StringComparison.OrdinalIgnoreCase);
if (sysDateTime != -1)
{
defaultValue = defaultValue.Remove(sysDateTime, "sysdatetime()".Length).Insert(sysDateTime, "getdate()");
}
//remove braces around numbers
defaultValue = SqlTranslator.RemoveParenthesis(defaultValue);
defaultValue = Regex.Replace(defaultValue, @"\((\d+)\)", "$1");
return(defaultValue);
}
// get a page of entities
public IEnumerable <IEntitySlim> GetPagedResultsByQuery(IQuery <IUmbracoEntity> query, Guid objectType, long pageIndex, int pageSize, out long totalRecords,
IQuery <IUmbracoEntity> filter, Ordering ordering)
{
var isContent = objectType == Constants.ObjectTypes.Document || objectType == Constants.ObjectTypes.DocumentBlueprint;
var isMedia = objectType == Constants.ObjectTypes.Media;
var sql = GetBaseWhere(isContent, isMedia, false, x =>
{
if (filter == null)
{
return;
}
foreach (var filterClause in filter.GetWhereClauses())
{
x.Where(filterClause.Item1, filterClause.Item2);
}
}, objectType);
ordering = ordering ?? Ordering.ByDefault();
var translator = new SqlTranslator <IUmbracoEntity>(sql, query);
sql = translator.Translate();
sql = AddGroupBy(isContent, isMedia, sql, ordering.IsEmpty);
if (!ordering.IsEmpty)
{
// apply ordering
ApplyOrdering(ref sql, ordering);
}
// TODO: we should be able to do sql = sql.OrderBy(x => Alias(x.NodeId, "NodeId")); but we can't because the OrderBy extension don't support Alias currently
//no matter what we always must have node id ordered at the end
sql = ordering.Direction == Direction.Ascending ? sql.OrderBy("NodeId") : sql.OrderByDescending("NodeId");
// for content we must query for ContentEntityDto entities to produce the correct culture variant entity names
var pageIndexToFetch = pageIndex + 1;
IEnumerable <BaseDto> dtos;
if (isContent)
{
var page = Database.Page <ContentEntityDto>(pageIndexToFetch, pageSize, sql);
dtos = page.Items;
totalRecords = page.TotalItems;
}
else if (isMedia)
{
var page = Database.Page <MediaEntityDto>(pageIndexToFetch, pageSize, sql);
dtos = page.Items;
totalRecords = page.TotalItems;
}
else
{
var page = Database.Page <BaseDto>(pageIndexToFetch, pageSize, sql);
dtos = page.Items;
totalRecords = page.TotalItems;
}
var entities = dtos.Select(x => BuildEntity(isContent, isMedia, x)).ToArray();
if (isContent)
{
BuildVariants(entities.Cast <DocumentEntitySlim>());
}
return(entities);
}
public void Can_Build_PublishedDescendants_Query_For_IContent()
{
// Arrange
var path = "-1,1046,1076,1089";
var id = 1046;
var nodeObjectTypeId = new Guid("C66BA18E-EAF3-4CFF-8A22-41B16D66A972");
var sql = new Sql();
sql.Select("*")
.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)
.Where<NodeDto>(x => x.NodeObjectType == nodeObjectTypeId);
var query = Query<IContent>.Builder.Where(x => x.Path.StartsWith(path) && x.Id != id && x.Published == true && x.Trashed == false);
// Act
var translator = new SqlTranslator<IContent>(sql, query);
var result = translator.Translate();
var strResult = result.SQL;
// Assert
Console.WriteLine(strResult);
}
static void Main(string[] args)
{
// -> example of how to implement a LINQ provider:
// https://blogs.msdn.microsoft.com/mattwar/2008/11/18/linq-building-an-iqueryable-provider-series/
// -> Image of Expression:
// http://ryanohs.com/wp-content/uploads/2016/04/ExpressionTree.png
var users = new List <User> {
new User {
Id = 1, AccessCount = 2, Birth = DateTime.Now.AddYears(-18), Name = "Toothless Dragon", Email = "*****@*****.**", Password = "******"
},
new User {
Id = 2, AccessCount = 666, Birth = DateTime.Now.AddYears(-50), Name = "The Joker", Email = "*****@*****.**", Password = "******"
},
new User {
Id = 3, AccessCount = 10, Birth = DateTime.Now.AddYears(-40), Name = "Luke Skywalker", Email = "*****@*****.**", Password = "******"
},
new User {
Id = 4, AccessCount = 2, Birth = DateTime.Now.AddYears(-40), Name = "Thanos", Email = "*****@*****.**", Password = "******"
},
new User {
Id = 5, AccessCount = 4, Birth = DateTime.Now.AddYears(-40), Name = "Tony Stark", Email = "*****@*****.**", Password = "******"
},
};
var usersQuery = users.AsQueryable();
var translator = new SqlTranslator();
var accessCount = 9;
var mostAccessQuery1 = usersQuery.Where(e => e.AccessCount > accessCount);
var mostAccessSQL1 = translator.BuildExpression(mostAccessQuery1);
Console.WriteLine("most access sql, sample #1:");
Console.WriteLine(mostAccessSQL1);
Console.WriteLine();
var mostAccessQuery2 = usersQuery.Where(e => e.AccessCount >= 10);
var mostAccessSQL2 = translator.BuildExpression(mostAccessQuery2);
Console.WriteLine("most access sql, sample #2:");
Console.WriteLine(mostAccessSQL2);
Console.WriteLine();
var mostAccessOrderedQuery = usersQuery.OrderBy(e => e.AccessCount);
var mostAccessOrderedSQL = translator.BuildExpression(mostAccessOrderedQuery);
Console.WriteLine("most access ordered sql:");
Console.WriteLine(mostAccessOrderedSQL);
Console.WriteLine();
var userByMailQuery = usersQuery.Where(e => e.Email == "*****@*****.**");
var userByMailSQL = translator.BuildExpression(userByMailQuery);
Console.WriteLine("user by mail sql:");
Console.WriteLine(userByMailSQL);
Console.WriteLine();
var loginQuery = usersQuery.Where(e => e.Email == "*****@*****.**" && e.Password == "halfofuniverse");
var loginSQL = translator.BuildExpression(loginQuery);
Console.WriteLine("login sql:");
Console.WriteLine(loginSQL);
Console.WriteLine();
var nameAndMailAreEqualQuery = usersQuery.Where(e => e.Email == e.Name);
var nameAndMailSQL = translator.BuildExpression(nameAndMailAreEqualQuery);
Console.WriteLine("name and mail are equal sql:");
Console.WriteLine(nameAndMailSQL);
Console.WriteLine();
}
public virtual IEnumerable<IUmbracoEntity> GetByQuery(IQuery<IUmbracoEntity> query, Guid objectTypeId)
{
bool isContent = objectTypeId == new Guid(Constants.ObjectTypes.Document);
bool isMedia = objectTypeId == new Guid(Constants.ObjectTypes.Media);
var wheres = string.Concat(" AND ", string.Join(" AND ", ((Query<IUmbracoEntity>)query).WhereClauses()));
var sqlClause = GetBaseWhere(GetBase, isContent, isMedia, wheres, objectTypeId);
var translator = new SqlTranslator<IUmbracoEntity>(sqlClause, query);
var sql = translator.Translate().Append(GetGroupBy(isContent, isMedia));
var factory = new UmbracoEntityFactory();
if (isMedia)
{
//treat media differently for now
//TODO: We should really use this methodology for Content/Members too!! since it includes properties and ALL of the dynamic db fields
var entities = _work.Database.Fetch<dynamic, UmbracoPropertyDto, UmbracoEntity>(
new UmbracoEntityRelator().Map, sql);
return entities;
}
else
{
//use dynamic so that we can get ALL properties from the SQL so we can chuck that data into our AdditionalData
var dtos = _work.Database.Fetch<dynamic>(sql);
return dtos.Select(factory.BuildEntityFromDynamic).Cast<IUmbracoEntity>().ToList();
}
}
public void Can_Create_Correct_Subquery()
{
var query = Query<IMember>.Builder.Where(x =>
((Member) x).LongStringPropertyValue.Contains("1095") &&
((Member) x).PropertyTypeAlias == "headshot");
var sqlSubquery = GetSubquery();
var translator = new SqlTranslator<IMember>(sqlSubquery, query);
var subquery = translator.Translate();
var sql = GetBaseQuery(false)
.Append(new Sql("WHERE umbracoNode.id IN (" + subquery.SQL + ")", subquery.Arguments))
.OrderByDescending<ContentVersionDto>(x => x.VersionDate)
.OrderBy<NodeDto>(x => x.SortOrder);
Console.WriteLine(sql.SQL);
Assert.That(sql.SQL, Is.Not.Empty);
}
protected override void AddTableConstraints(IList <string> columnList)
{
var formatter = SqlFormatProvider();
if (Table.PrimaryKey != null && Table.PrimaryKey.Columns.Count > 1)
{
columnList.Add("PRIMARY KEY (" + GetColumnList(Table.PrimaryKey.Columns) + ")");
}
foreach (var uniqueKey in Table.UniqueKeys)
{
columnList.Add("UNIQUE (" + GetColumnList(uniqueKey.Columns) + ")");
}
foreach (var checkConstraint in Table.CheckConstraints)
{
var expression = SqlTranslator.Fix(checkConstraint.Expression);
//nothing to write?
if (string.IsNullOrEmpty(expression))
{
continue;
}
//check if Access and reformat
if (checkConstraint.Name.Contains("]."))
{
//access format names [table].[column].ValidationRule
//access expression doesn't have column name so take it from constraint name
var columnName = checkConstraint.Name.Substring(0, checkConstraint.Name.LastIndexOf("].", System.StringComparison.Ordinal) + 1)
.Replace("[" + Table.Name + "].", "");
//must have braces
expression = "(" + columnName + " " + expression + ")";
}
columnList.Add("CHECK " + expression);
}
//http://www.sqlite.org/foreignkeys.html These aren't enabled by default.
foreach (var foreignKey in Table.ForeignKeys)
{
var referencedTable = foreignKey.ReferencedTable(Table.DatabaseSchema);
//can't find the table. Don't write the fk reference.
if (referencedTable == null)
{
continue;
}
string refColumnList;
if (referencedTable.PrimaryKey == null && referencedTable.PrimaryKeyColumn != null)
{
refColumnList = referencedTable.PrimaryKeyColumn.Name;
}
else if (referencedTable.PrimaryKey == null)
{
continue; //can't find the primary key
}
else
{
refColumnList = GetColumnList(referencedTable.PrimaryKey.Columns);
}
columnList.Add(string.Format(CultureInfo.InvariantCulture,
"FOREIGN KEY ({0}) REFERENCES {1} ({2})",
GetColumnList(foreignKey.Columns),
formatter.Escape(foreignKey.RefersToTable),
refColumnList));
}
}
/// <summary>
/// A helper method for inheritors to get the paged results by query in a way that minimizes queries
/// </summary>
/// <typeparam name="TDto">The type of the d.</typeparam>
/// <typeparam name="TContentBase">The 'true' entity type (i.e. Content, Member, etc...)</typeparam>
/// <param name="query">The query.</param>
/// <param name="pageIndex">Index of the page.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalRecords">The total records.</param>
/// <param name="nodeIdSelect">The tablename + column name for the SELECT statement fragment to return the node id from the query</param>
/// <param name="defaultFilter">A callback to create the default filter to be applied if there is one</param>
/// <param name="processQuery">A callback to process the query result</param>
/// <param name="orderBy">The order by column</param>
/// <param name="orderDirection">The order direction.</param>
/// <returns></returns>
/// <exception cref="System.ArgumentNullException">orderBy</exception>
protected IEnumerable <TEntity> GetPagedResultsByQuery <TDto, TContentBase>(IQuery <TEntity> query, long pageIndex, int pageSize, out long totalRecords,
Tuple <string, string> nodeIdSelect,
Func <Sql, IEnumerable <TEntity> > processQuery,
string orderBy,
Direction orderDirection,
Func <Tuple <string, object[]> > defaultFilter = null)
where TContentBase : class, IAggregateRoot, TEntity
{
if (orderBy == null)
{
throw new ArgumentNullException("orderBy");
}
// Get base query
var sqlBase = GetBaseQuery(false);
if (query == null)
{
query = new Query <TEntity>();
}
var translator = new SqlTranslator <TEntity>(sqlBase, query);
var sqlQuery = translator.Translate();
// Note we can't do multi-page for several DTOs like we can multi-fetch and are doing in PerformGetByQuery,
// but actually given we are doing a Get on each one (again as in PerformGetByQuery), we only need the node Id.
// So we'll modify the SQL.
var sqlNodeIds = new Sql(
sqlQuery.SQL.Replace("SELECT *", string.Format("SELECT {0}.{1}", nodeIdSelect.Item1, nodeIdSelect.Item2)),
sqlQuery.Arguments);
//get sorted and filtered sql
var sqlNodeIdsWithSort = GetSortedSqlForPagedResults(
GetFilteredSqlForPagedResults(sqlNodeIds, defaultFilter),
orderDirection, orderBy);
// Get page of results and total count
IEnumerable <TEntity> result;
var pagedResult = Database.Page <TDto>(pageIndex + 1, pageSize, sqlNodeIdsWithSort);
totalRecords = Convert.ToInt32(pagedResult.TotalItems);
//NOTE: We need to check the actual items returned, not the 'totalRecords', that is because if you request a page number
// that doesn't actually have any data on it, the totalRecords will still indicate there are records but there are none in
// the pageResult, then the GetAll will actually return ALL records in the db.
if (pagedResult.Items.Any())
{
//Crete the inner paged query that was used above to get the paged result, we'll use that as the inner sub query
var args = sqlNodeIdsWithSort.Arguments;
string sqlStringCount, sqlStringPage;
Database.BuildPageQueries <TDto>(pageIndex * pageSize, pageSize, sqlNodeIdsWithSort.SQL, ref args, out sqlStringCount, out sqlStringPage);
//if this is for sql server, the sqlPage will start with a SELECT * but we don't want that, we only want to return the nodeId
sqlStringPage = sqlStringPage
.Replace("SELECT *",
//This ensures we only take the field name of the node id select and not the table name - since the resulting select
// will ony work with the field name.
"SELECT " + nodeIdSelect.Item2);
//We need to make this an inner join on the paged query
var splitQuery = sqlQuery.SQL.Split(new[] { "WHERE " }, StringSplitOptions.None);
var withInnerJoinSql = new Sql(splitQuery[0])
.Append("INNER JOIN (")
//join the paged query with the paged query arguments
.Append(sqlStringPage, args)
.Append(") temp ")
.Append(string.Format("ON {0}.{1} = temp.{1}", nodeIdSelect.Item1, nodeIdSelect.Item2))
//add the original where clause back with the original arguments
.Where(splitQuery[1], sqlQuery.Arguments);
//get sorted and filtered sql
var fullQuery = GetSortedSqlForPagedResults(
GetFilteredSqlForPagedResults(withInnerJoinSql, defaultFilter),
orderDirection, orderBy);
var content = processQuery(fullQuery)
.Cast <TContentBase>()
.AsQueryable();
// Now we need to ensure this result is also ordered by the same order by clause
var orderByProperty = GetEntityPropertyNameForOrderBy(orderBy);
if (orderDirection == Direction.Ascending)
{
result = content.OrderBy(orderByProperty);
}
else
{
result = content.OrderByDescending(orderByProperty);
}
}
else
{
result = Enumerable.Empty <TEntity>();
}
return(result);
}
public virtual IEnumerable<IUmbracoEntity> GetByQuery(IQuery<IUmbracoEntity> query)
{
var wheres = string.Concat(" AND ", string.Join(" AND ", ((Query<IUmbracoEntity>) query).WhereClauses()));
var sqlClause = GetBase(false, false, wheres);
var translator = new SqlTranslator<IUmbracoEntity>(sqlClause, query);
var sql = translator.Translate().Append(GetGroupBy(false, false));
var dtos = _work.Database.Fetch<UmbracoEntityDto>(sql);
var factory = new UmbracoEntityFactory();
var list = dtos.Select(factory.BuildEntity).Cast<IUmbracoEntity>().ToList();
return list;
}
public IEnumerable <ObjectState> GetDesiredState(SchemaConnection connection, IOutput output)
{
var schemaDriver = connection.SchemaDriver;
var sqlTranslator = new SqlTranslator(schemaDriver, null);
HashSet <string> catalogs = new HashSet <string>(schemaDriver.DbDriver.DbStringComparer);
foreach (var nrdoTable in codeBase.AllTables)
{
if (nrdoTable.ExistingName != null)
{
if (nrdoTable.BeforeStatements.Any())
{
throw new ApplicationException("Table " + nrdoTable.Name + " is 'existing' but has before statements, which is no longer supported");
}
continue;
}
output.Verbose("Loading table " + nrdoTable.DatabaseName + " from " + nrdoTable.Type.Name);
var table = TableType.Create(schemaName + "." + nrdoTable.DatabaseName);
yield return(table);
if (preserveColumnOrder)
{
yield return(FieldOrderSensitivityType.Create(table.Identifier));
}
string sequencedPkeyFieldName = null;
string sequenceName = null;
if (nrdoTable.IsPkeySequenced)
{
sequencedPkeyFieldName = nrdoTable.PkeyGet.Fields.Single().Name;
var sequenceNameBase = getNameHashString(nrdoTable.Name) + "_" + sequencedPkeyFieldName;
if (schemaDriver.IsSequenceUsed)
{
sequenceName = "sq_" + sequenceNameBase;
yield return(SequenceType.Create(schemaName + "." + sequenceName));
}
if (schemaDriver.IsTriggerUsedForSequence)
{
var triggerName = "sqt_" + sequenceNameBase;
yield return(TriggerType.Create(table.Identifier, triggerName, schemaDriver.GetSequencedFieldTriggerTiming(), TriggerEvents.Insert,
schemaDriver.GetSequencedFieldTriggerBody(table.Name, sequencedPkeyFieldName, sequenceName)));
}
}
var fieldIndex = 0;
foreach (var nrdoField in nrdoTable.Fields)
{
if (nrdoField.Name == sequencedPkeyFieldName)
{
yield return(FieldType.CreateSequencedPkey(table.Identifier, nrdoField.Name, fieldIndex++, nrdoField.DbType, nrdoField.IsNullable, sequenceName));
}
else
{
yield return(FieldType.Create(table.Identifier, nrdoField.Name, fieldIndex++, nrdoField.DbType, nrdoField.IsNullable));
}
}
foreach (var nrdoIndex in nrdoTable.Indexes)
{
if (nrdoIndex.IsPrimary)
{
yield return(UniqueIndexType.CreatePrimaryKey(table.Identifier, nrdoIndex.Name,
from field in nrdoIndex.Fields select FieldType.Identifier(field), schemaDriver.DefaultPrimaryKeyCustomState));
}
else if (nrdoIndex.IsUnique)
{
yield return(UniqueIndexType.CreateUnique(table.Identifier, nrdoIndex.Name,
from field in nrdoIndex.Fields select FieldType.Identifier(field), schemaDriver.DefaultUniqueConstraintCustomState));
}
else
{
yield return(NonUniqueIndexType.Create(table.Identifier, nrdoIndex.Name,
from field in nrdoIndex.Fields select FieldType.Identifier(field), schemaDriver.DefaultIndexCustomState));
}
}
if (nrdoTable.FulltextFields.Any() && connection.SchemaDriver.IsFulltextSupported(connection))
{
var catalog = nrdoTable.FulltextCatalog ?? "NrdoFulltext";
if (!catalogs.Contains(catalog))
{
yield return(FulltextCatalogType.Create(catalog));
catalogs.Add(catalog);
}
var pkey = nrdoTable.Indexes.Where(index => index.IsPrimary).Single();
yield return(FulltextIndexType.Create(table.Identifier, FulltextCatalogType.Identifier(catalog), pkey.Name, nrdoTable.FulltextFields));
}
foreach (var nrdoFkey in nrdoTable.References)
{
if (nrdoFkey.IsFkey)
{
yield return(FkeyType.Create(table.Identifier, TableType.Identifier(schemaName + "." + nrdoFkey.TargetTable.DatabaseName),
nrdoFkey.FkeyName, nrdoFkey.IsCascadingFkey,
from fjoin in nrdoFkey.Joins select new FieldPair(fjoin.From.Name, fjoin.To.Name)));
}
}
var beforeIndex = 0;
foreach (var nrdoBefore in nrdoTable.BeforeStatements)
{
yield return(BeforeStatementType.Create(table.Identifier, "0" + nrdoTable.Name, beforeIndex++, nrdoBefore.Name, nrdoBefore.Step,
nrdoBefore.Initial, nrdoBefore.Upgrade, sqlTranslator.Translate(nrdoBefore.Statement)));
}
foreach (var oldName in nrdoTable.RenamedFrom)
{
var oldDbName = schemaName + "." + oldName.Replace(':', '_');
yield return(TableRenameType.Create(oldDbName, table.Name));
}
}
foreach (var nrdoQuery in codeBase.AllQueries)
{
output.Verbose("Loading query " + nrdoQuery.DatabaseName + " from " + nrdoQuery.Type.Name);
var queryName = schemaName + "." + nrdoQuery.DatabaseName;
var parameters = from param in nrdoQuery.Params select new ProcParam(param.Name, param.DbType);
if (nrdoQuery.IsStoredProc)
{
yield return(QueryType.CreateProc(queryName, parameters, sqlTranslator.Translate(nrdoQuery.Sql)));
if (nrdoQuery.IsPreUpgradeHook)
{
yield return(PreUpgradeHookType.Create(queryName));
}
}
else if (nrdoQuery.IsStoredFunction)
{
var returnType = nrdoQuery.Results.Single().DbType;
yield return(QueryType.CreateFunction(queryName, parameters, returnType, sqlTranslator.Translate(nrdoQuery.Sql)));
}
else
{
yield return(QueryType.CreateUnstored(queryName));
}
var beforeIndex = 0;
foreach (var nrdoBefore in nrdoQuery.BeforeStatements)
{
yield return(BeforeStatementType.Create(QueryType.Identifier(queryName), "1" + nrdoQuery.Name, beforeIndex++, nrdoBefore.Name, nrdoBefore.Step,
nrdoBefore.Initial, nrdoBefore.Upgrade, sqlTranslator.Translate(nrdoBefore.Statement)));
}
}
}
/// <summary>
/// Return the audit items as paged result
/// </summary>
/// <param name="query">
/// The query coming from the service
/// </param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalRecords"></param>
/// <param name="orderDirection"></param>
/// <param name="auditTypeFilter">
/// Since we currently do not have enum support with our expression parser, we cannot query on AuditType in the query or the custom filter
/// so we need to do that here
/// </param>
/// <param name="customFilter">
/// A user supplied custom filter
/// </param>
/// <returns></returns>
public IEnumerable <IAuditItem> GetPagedResultsByQuery(IQuery <IAuditItem> query, long pageIndex, int pageSize,
out long totalRecords, Direction orderDirection,
AuditType[] auditTypeFilter,
IQuery <IAuditItem> customFilter)
{
if (auditTypeFilter == null)
{
auditTypeFilter = new AuditType[0];
}
var sql = GetBaseQuery(false);
if (query == null)
{
query = new Query <IAuditItem>();
}
var queryHasWhereClause = query.GetWhereClauses().Any();
var translatorIds = new SqlTranslator <IAuditItem>(sql, query);
var translatedQuery = translatorIds.Translate();
var customFilterWheres = customFilter?.GetWhereClauses().ToArray();
var hasCustomFilter = customFilterWheres != null && customFilterWheres.Length > 0;
if (hasCustomFilter)
{
var filterSql = new Sql();
foreach (var filterClause in customFilterWheres)
{
filterSql.Append($"AND ({filterClause.Item1})", filterClause.Item2);
}
translatedQuery = GetFilteredSqlForPagedResults(translatedQuery, filterSql, queryHasWhereClause);
}
if (auditTypeFilter.Length > 0)
{
var filterSql = new Sql();
foreach (var filterClause in auditTypeFilter)
{
filterSql.Append("AND (logHeader = @logHeader)", new { logHeader = filterClause.ToString() });
}
translatedQuery = GetFilteredSqlForPagedResults(translatedQuery, filterSql, queryHasWhereClause || hasCustomFilter);
}
if (orderDirection == Direction.Descending)
{
translatedQuery.OrderByDescending("Datestamp");
}
else
{
translatedQuery.OrderBy("Datestamp");
}
// Get page of results and total count
var pagedResult = Database.Page <LogDto>(pageIndex + 1, pageSize, translatedQuery);
totalRecords = pagedResult.TotalItems;
var pages = pagedResult.Items.Select(
dto => new AuditItem(dto.Id, dto.Comment, Enum <AuditType> .ParseOrNull(dto.Header) ?? AuditType.Custom, dto.UserId)).ToArray();
//Mapping the DateStamp
for (var i = 0; i < pages.Length; i++)
{
pages[i].CreateDate = pagedResult.Items[i].Datestamp;
}
return(pages);
}
public async Task <bool> HashesMatch(DbConnection con, string tableName, TypeMap typeMap)
{
const string HASH_COLUMN = "Hash";
string getHashRecord = $"select {HASH_COLUMN} from AnalyzedModelHashes where TableName='{tableName}'";
string existingHash = await con.RunCommandGetString(getHashRecord, HASH_COLUMN);
string newHash = typeMap.EncodeToBase64();
if (existingHash == newHash)
{
return(true);
}
if (existingHash == string.Empty)
{
await CreateTableFromTypeMap(con, tableName, typeMap);
return(false);
}
TypeMap existing = TypeMap.DecodeFromBase64(existingHash);
TypeMapDifferences differences = existing.GetDifferences(typeMap);
foreach (TypeMapDifference difference in differences)
{
if (difference.DifferenceType == DifferenceType.DataTypeChange)
{
throw new Exception($"Difference types {nameof(DifferenceType.DataTypeChange)}, " +
$" not supported. \n" +
$"Column: {difference.ColumnKey}");
}
if (difference.DifferenceType == DifferenceType.ChangedColumnOrder)
{
//TODO: Better handling.
//ignore for now
}
if (difference.DifferenceType == DifferenceType.AddedColumn)
{
NodeProperties node = typeMap[difference.ColumnKey];
string alterState = $"ALTER TABLE {typeMap.Name} ADD COLUMN {difference.ColumnKey} {SqlTranslator.SqlTypeFromInternalType(node.InternalType, this).SqlType};";
await con.RunCommandAsync(alterState);
}
}
return(false);
}
public IReadOnlyCollection <T> Find(BooleanExpression condition = null)
{
var filter = SqlTranslator.Build(condition, typeof(T));
return(Orm.FindUnversionedContent <T>(filter).Result.ToList());
}
public virtual IEnumerable<IUmbracoEntity> GetByQuery(IQuery<IUmbracoEntity> query, Guid objectTypeId)
{
bool isContent = objectTypeId == new Guid(Constants.ObjectTypes.Document);
bool isMedia = objectTypeId == new Guid(Constants.ObjectTypes.Media);
var wheres = string.Concat(" AND ", string.Join(" AND ", ((Query<IUmbracoEntity>)query).WhereClauses()));
var sqlClause = GetBaseWhere(GetBase, isContent, isMedia, wheres, objectTypeId);
var translator = new SqlTranslator<IUmbracoEntity>(sqlClause, query);
var sql = translator.Translate().Append(GetGroupBy(isContent, isMedia));
var dtos = isMedia
? _work.Database.Fetch<UmbracoEntityDto, UmbracoPropertyDto, UmbracoEntityDto>(
new UmbracoEntityRelator().Map, sql)
: _work.Database.Fetch<UmbracoEntityDto>(sql);
var factory = new UmbracoEntityFactory();
var list = dtos.Select(factory.BuildEntity).Cast<IUmbracoEntity>().ToList();
return list;
}
private IEnumerable <IUser> GetPagedResultsByQuery(IQuery <IUser> query, long pageIndex, int pageSize, out long totalRecords, string orderBy, Direction orderDirection,
string[] includeUserGroups = null,
string[] excludeUserGroups = null,
UserState[] userState = null,
IQuery <IUser> customFilter = null)
{
if (string.IsNullOrWhiteSpace(orderBy))
{
throw new ArgumentException("Value cannot be null or whitespace.", "orderBy");
}
Sql filterSql = null;
var customFilterWheres = customFilter != null?customFilter.GetWhereClauses().ToArray() : null;
var hasCustomFilter = customFilterWheres != null && customFilterWheres.Length > 0;
if (hasCustomFilter ||
(includeUserGroups != null && includeUserGroups.Length > 0) || (excludeUserGroups != null && excludeUserGroups.Length > 0) ||
(userState != null && userState.Length > 0 && userState.Contains(UserState.All) == false))
{
filterSql = new Sql();
}
if (hasCustomFilter)
{
foreach (var filterClause in customFilterWheres)
{
filterSql.Append($"AND ({filterClause.Item1})", filterClause.Item2);
}
}
if (includeUserGroups != null && includeUserGroups.Length > 0)
{
const string subQuery = @"AND (umbracoUser.id IN (SELECT DISTINCT umbracoUser.id
FROM umbracoUser
INNER JOIN umbracoUser2UserGroup ON umbracoUser2UserGroup.userId = umbracoUser.id
INNER JOIN umbracoUserGroup ON umbracoUserGroup.id = umbracoUser2UserGroup.userGroupId
WHERE umbracoUserGroup.userGroupAlias IN (@userGroups)))" ;
filterSql.Append(subQuery, new { userGroups = includeUserGroups });
}
if (excludeUserGroups != null && excludeUserGroups.Length > 0)
{
const string subQuery = @"AND (umbracoUser.id NOT IN (SELECT DISTINCT umbracoUser.id
FROM umbracoUser
INNER JOIN umbracoUser2UserGroup ON umbracoUser2UserGroup.userId = umbracoUser.id
INNER JOIN umbracoUserGroup ON umbracoUserGroup.id = umbracoUser2UserGroup.userGroupId
WHERE umbracoUserGroup.userGroupAlias IN (@userGroups)))" ;
filterSql.Append(subQuery, new { userGroups = excludeUserGroups });
}
if (userState != null && userState.Length > 0)
{
//the "ALL" state doesn't require any filtering so we ignore that, if it exists in the list we don't do any filtering
if (userState.Contains(UserState.All) == false)
{
var sb = new StringBuilder("(");
var appended = false;
if (userState.Contains(UserState.Active))
{
sb.Append("(userDisabled = 0 AND userNoConsole = 0 AND lastLoginDate IS NOT NULL)");
appended = true;
}
if (userState.Contains(UserState.Inactive))
{
if (appended)
{
sb.Append(" OR ");
}
sb.Append("(userDisabled = 0 AND userNoConsole = 0 AND lastLoginDate IS NULL)");
appended = true;
}
if (userState.Contains(UserState.Disabled))
{
if (appended)
{
sb.Append(" OR ");
}
sb.Append("(userDisabled = 1)");
appended = true;
}
if (userState.Contains(UserState.LockedOut))
{
if (appended)
{
sb.Append(" OR ");
}
sb.Append("(userNoConsole = 1)");
appended = true;
}
if (userState.Contains(UserState.Invited))
{
if (appended)
{
sb.Append(" OR ");
}
sb.Append("(lastLoginDate IS NULL AND userDisabled = 1 AND invitedDate IS NOT NULL)");
appended = true;
}
sb.Append(")");
filterSql.Append("AND " + sb);
}
}
// Get base query for returning IDs
var sqlBaseIds = GetBaseQuery("id");
if (query == null)
{
query = new Query <IUser>();
}
var queryHasWhereClause = query.GetWhereClauses().Any();
var translatorIds = new SqlTranslator <IUser>(sqlBaseIds, query);
var sqlQueryIds = translatorIds.Translate();
var sqlBaseFull = GetBaseQuery("umbracoUser.*, umbracoUserGroup.*, umbracoUserGroup2App.*, umbracoUserStartNode.*");
var translatorFull = new SqlTranslator <IUser>(sqlBaseFull, query);
//get sorted and filtered sql
var sqlNodeIdsWithSort = GetSortedSqlForPagedResults(
GetFilteredSqlForPagedResults(sqlQueryIds, filterSql, queryHasWhereClause),
orderDirection, orderBy);
// Get page of results and total count
var pagedResult = Database.Page <UserDto>(pageIndex + 1, pageSize, sqlNodeIdsWithSort);
totalRecords = Convert.ToInt32(pagedResult.TotalItems);
//NOTE: We need to check the actual items returned, not the 'totalRecords', that is because if you request a page number
// that doesn't actually have any data on it, the totalRecords will still indicate there are records but there are none in
// the pageResult.
if (pagedResult.Items.Any())
{
//Create the inner paged query that was used above to get the paged result, we'll use that as the inner sub query
var args = sqlNodeIdsWithSort.Arguments;
string sqlStringCount, sqlStringPage;
Database.BuildPageQueries <UserDto>(pageIndex * pageSize, pageSize, sqlNodeIdsWithSort.SQL, ref args, out sqlStringCount, out sqlStringPage);
var sqlQueryFull = translatorFull.Translate();
//We need to make this FULL query an inner join on the paged ID query
var splitQuery = sqlQueryFull.SQL.Split(new[] { "WHERE " }, StringSplitOptions.None);
var fullQueryWithPagedInnerJoin = new Sql(splitQuery[0])
.Append("INNER JOIN (")
//join the paged query with the paged query arguments
.Append(sqlStringPage, args)
.Append(") temp ")
.Append("ON umbracoUser.id = temp.id");
AddGroupLeftJoin(fullQueryWithPagedInnerJoin);
if (splitQuery.Length > 1)
{
//add the original where clause back with the original arguments
fullQueryWithPagedInnerJoin.Where(splitQuery[1], sqlQueryIds.Arguments);
}
//get sorted and filtered sql
var fullQuery = GetSortedSqlForPagedResults(
GetFilteredSqlForPagedResults(fullQueryWithPagedInnerJoin, filterSql, queryHasWhereClause),
orderDirection, orderBy);
var users = ConvertFromDtos(Database.Fetch <UserDto, UserGroupDto, UserGroup2AppDto, UserStartNodeDto, UserDto>(new UserGroupRelator().Map, fullQuery))
.ToArray(); // important so we don't iterate twice, if we don't do this we can end up with null values in cache if we were caching.
return(users);
}
return(Enumerable.Empty <IUser>());
}
public virtual IEnumerable<IUmbracoEntity> GetByQuery(IQuery<IUmbracoEntity> query, Guid objectTypeId)
{
bool isContent = objectTypeId == new Guid(Constants.ObjectTypes.Document);
bool isMedia = objectTypeId == new Guid(Constants.ObjectTypes.Media);
var wheres = string.Concat(" AND ", string.Join(" AND ", ((Query<IUmbracoEntity>)query).WhereClauses()));
var sqlClause = GetBaseWhere(GetBase, isContent, isMedia, wheres, objectTypeId);
var translator = new SqlTranslator<IUmbracoEntity>(sqlClause, query);
var sql = translator.Translate().Append(GetGroupBy(isContent, isMedia));
var factory = new UmbracoEntityFactory();
if (isMedia)
{
//treat media differently for now
var dtos = _work.Database.Fetch<UmbracoEntityDto, UmbracoPropertyDto, UmbracoEntityDto>(
new UmbracoEntityRelator().Map, sql);
return dtos.Select(factory.BuildEntity).Cast<IUmbracoEntity>().ToArray();
}
else
{
//use dynamic so that we can get ALL properties from the SQL
//we'll have to stitch stuff together manually but we can get our
//additional data to put in the dictionary.
var dtos = _work.Database.Fetch<dynamic>(sql);
var entityProps = TypeHelper.GetPublicProperties(typeof (IUmbracoEntity)).Select(x => x.Name).ToArray();
var result = new List<IUmbracoEntity>();
foreach (var d in dtos)
{
//build the initial entity
IUmbracoEntity entity = factory.BuildEntityFromDynamic(d);
//convert the dynamic row to dictionary
var asDictionary = (IDictionary<string, object>) d;
//figure out what extra properties we have that are not on the IUmbracoEntity and add them to additional data
foreach (var k in asDictionary.Keys
.Select(x => new {orig = x, title = x.ConvertCase(StringAliasCaseType.PascalCase)})
.Where(x => entityProps.InvariantContains(x.title) == false))
{
entity.AdditionalData[k.title] = asDictionary[k.orig];
}
result.Add(entity);
}
return result;
}
}
// Constructors
private Compressor(SqlTranslator translator)
{
newLineEnd = translator.NewLine[translator.NewLine.Length - 1];
last = newLineEnd;
}
/// <summary>
/// A helper method for inheritors to get the paged results by query in a way that minimizes queries
/// </summary>
/// <typeparam name="TDto">The type of the d.</typeparam>
/// <param name="query">The query.</param>
/// <param name="pageIndex">Index of the page.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalRecords">The total records.</param>
/// <param name="nodeIdSelect">The tablename + column name for the SELECT statement fragment to return the node id from the query</param>
/// <param name="defaultFilter">A callback to create the default filter to be applied if there is one</param>
/// <param name="processQuery">A callback to process the query result</param>
/// <param name="orderBy">The order by column</param>
/// <param name="orderDirection">The order direction.</param>
/// <param name="orderBySystemField">Flag to indicate when ordering by system field</param>
/// <returns></returns>
/// <exception cref="System.ArgumentNullException">orderBy</exception>
protected IEnumerable <TEntity> GetPagedResultsByQuery <TDto>(IQuery <TEntity> query, long pageIndex, int pageSize, out long totalRecords,
Tuple <string, string> nodeIdSelect,
Func <Sql, PagingSqlQuery <TDto>, IEnumerable <TEntity> > processQuery,
string orderBy,
Direction orderDirection,
bool orderBySystemField,
Func <Tuple <string, object[]> > defaultFilter = null)
{
if (orderBy == null)
{
throw new ArgumentNullException("orderBy");
}
// Get base query for returning IDs
var sqlBaseIds = GetBaseQuery(BaseQueryType.Ids);
// Get base query for returning all data
var sqlBaseFull = GetBaseQuery(BaseQueryType.FullMultiple);
if (query == null)
{
query = new Query <TEntity>();
}
var translatorIds = new SqlTranslator <TEntity>(sqlBaseIds, query);
var sqlQueryIds = translatorIds.Translate();
var translatorFull = new SqlTranslator <TEntity>(sqlBaseFull, query);
var sqlQueryFull = translatorFull.Translate();
//get sorted and filtered sql
var sqlNodeIdsWithSort = GetSortedSqlForPagedResults(
GetFilteredSqlForPagedResults(sqlQueryIds, defaultFilter),
orderDirection, orderBy, orderBySystemField, nodeIdSelect);
// Get page of results and total count
IEnumerable <TEntity> result;
var pagedResult = Database.Page <TDto>(pageIndex + 1, pageSize, sqlNodeIdsWithSort);
totalRecords = Convert.ToInt32(pagedResult.TotalItems);
//NOTE: We need to check the actual items returned, not the 'totalRecords', that is because if you request a page number
// that doesn't actually have any data on it, the totalRecords will still indicate there are records but there are none in
// the pageResult, then the GetAll will actually return ALL records in the db.
if (pagedResult.Items.Any())
{
//Create the inner paged query that was used above to get the paged result, we'll use that as the inner sub query
var args = sqlNodeIdsWithSort.Arguments;
string sqlStringCount, sqlStringPage;
Database.BuildPageQueries <TDto>(pageIndex * pageSize, pageSize, sqlNodeIdsWithSort.SQL, ref args, out sqlStringCount, out sqlStringPage);
//We need to make this FULL query an inner join on the paged ID query
var splitQuery = sqlQueryFull.SQL.Split(new[] { "WHERE " }, StringSplitOptions.None);
var fullQueryWithPagedInnerJoin = new Sql(splitQuery[0])
.Append("INNER JOIN (")
//join the paged query with the paged query arguments
.Append(sqlStringPage, args)
.Append(") temp ")
.Append(string.Format("ON {0}.{1} = temp.{1}", nodeIdSelect.Item1, nodeIdSelect.Item2))
//add the original where clause back with the original arguments
.Where(splitQuery[1], sqlQueryIds.Arguments);
//get sorted and filtered sql
var fullQuery = GetSortedSqlForPagedResults(
GetFilteredSqlForPagedResults(fullQueryWithPagedInnerJoin, defaultFilter),
orderDirection, orderBy, orderBySystemField, nodeIdSelect);
return(processQuery(fullQuery, new PagingSqlQuery <TDto>(Database, sqlNodeIdsWithSort, pageIndex, pageSize)));
}
else
{
result = Enumerable.Empty <TEntity>();
}
return(result);
}
public IEnumerable <IUmbracoEntity> GetPagedResultsByQuery(IQuery <IUmbracoEntity> query, Guid objectTypeId, long pageIndex, int pageSize, out long totalRecords,
string orderBy, Direction orderDirection, IQuery <IUmbracoEntity> filter = null)
{
bool isContent = objectTypeId == Constants.ObjectTypes.DocumentGuid || objectTypeId == Constants.ObjectTypes.DocumentBlueprintGuid;
bool isMedia = objectTypeId == Constants.ObjectTypes.MediaGuid;
var factory = new UmbracoEntityFactory();
var sqlClause = GetBaseWhere(GetBase, isContent, isMedia, sql =>
{
if (filter != null)
{
foreach (var filterClause in filter.GetWhereClauses())
{
sql.Where(filterClause.Item1, filterClause.Item2);
}
}
}, objectTypeId);
var translator = new SqlTranslator <IUmbracoEntity>(sqlClause, query);
var entitySql = translator.Translate();
var pagedSql = entitySql.Append(GetGroupBy(isContent, isMedia, false)).OrderBy("umbracoNode.id");
IEnumerable <IUmbracoEntity> result;
if (isMedia)
{
//Treat media differently for now, as an Entity it will be returned with ALL of it's properties in the AdditionalData bag!
var pagedResult = _work.Database.Page <dynamic>(pageIndex + 1, pageSize, pagedSql);
var ids = pagedResult.Items.Select(x => (int)x.id).InGroupsOf(2000);
var entities = pagedResult.Items.Select(factory.BuildEntityFromDynamic).Cast <IUmbracoEntity>().ToList();
//Now we need to merge in the property data since we need paging and we can't do this the way that the big media query was working before
foreach (var idGroup in ids)
{
var propSql = GetPropertySql(Constants.ObjectTypes.Media)
.Where("contentNodeId IN (@ids)", new { ids = idGroup })
.OrderBy("contentNodeId");
//This does NOT fetch all data into memory in a list, this will read
// over the records as a data reader, this is much better for performance and memory,
// but it means that during the reading of this data set, nothing else can be read
// from SQL server otherwise we'll get an exception.
var allPropertyData = _work.Database.Query <dynamic>(propSql);
//keep track of the current property data item being enumerated
var propertyDataSetEnumerator = allPropertyData.GetEnumerator();
var hasCurrent = false; // initially there is no enumerator.Current
try
{
//This must be sorted by node id (which is done by SQL) because this is how we are sorting the query to lookup property types above,
// which allows us to more efficiently iterate over the large data set of property values.
foreach (var entity in entities)
{
// assemble the dtos for this def
// use the available enumerator.Current if any else move to next
while (hasCurrent || propertyDataSetEnumerator.MoveNext())
{
if (propertyDataSetEnumerator.Current.contentNodeId == entity.Id)
{
hasCurrent = false; // enumerator.Current is not available
//the property data goes into the additional data
entity.AdditionalData[propertyDataSetEnumerator.Current.propertyTypeAlias] = new UmbracoEntity.EntityProperty
{
PropertyEditorAlias = propertyDataSetEnumerator.Current.propertyEditorAlias,
Value = StringExtensions.IsNullOrWhiteSpace(propertyDataSetEnumerator.Current.dataNtext)
? propertyDataSetEnumerator.Current.dataNvarchar
: StringExtensions.ConvertToJsonIfPossible(propertyDataSetEnumerator.Current.dataNtext)
};
}
else
{
hasCurrent = true; // enumerator.Current is available for another def
break; // no more propertyDataDto for this def
}
}
}
}
finally
{
propertyDataSetEnumerator.Dispose();
}
}
result = entities;
}
else
{
var pagedResult = _work.Database.Page <dynamic>(pageIndex + 1, pageSize, pagedSql);
result = pagedResult.Items.Select(factory.BuildEntityFromDynamic).Cast <IUmbracoEntity>().ToList();
}
//The total items from the PetaPoco page query will be wrong due to the Outer join used on parent, depending on the search this will
//return duplicate results when the COUNT is used in conjuction with it, so we need to get the total on our own.
//generate a query that does not contain the LEFT Join for parent, this would cause
//the COUNT(*) query to return the wrong
var sqlCountClause = GetBaseWhere(
(isC, isM, f) => GetBase(isC, isM, f, true), //true == is a count query
isContent, isMedia, sql =>
{
if (filter != null)
{
foreach (var filterClause in filter.GetWhereClauses())
{
sql.Where(filterClause.Item1, filterClause.Item2);
}
}
}, objectTypeId);
var translatorCount = new SqlTranslator <IUmbracoEntity>(sqlCountClause, query);
var countSql = translatorCount.Translate();
totalRecords = _work.Database.ExecuteScalar <int>(countSql);
return(result);
}
private IEnumerable <IUser> GetPagedResultsByQuery(IQuery <IUser> query, long pageIndex, int pageSize, out long totalRecords,
string orderBy, Direction orderDirection = Direction.Ascending,
string[] includeUserGroups = null, string[] excludeUserGroups = null, UserState[] userState = null, IQuery <IUser> filter = null)
{
if (string.IsNullOrWhiteSpace(orderBy))
{
throw new ArgumentException("Value cannot be null or whitespace.", nameof(orderBy));
}
Sql <ISqlContext> filterSql = null;
var customFilterWheres = filter?.GetWhereClauses().ToArray();
var hasCustomFilter = customFilterWheres != null && customFilterWheres.Length > 0;
if (hasCustomFilter ||
includeUserGroups != null && includeUserGroups.Length > 0 ||
excludeUserGroups != null && excludeUserGroups.Length > 0 ||
userState != null && userState.Length > 0 && userState.Contains(UserState.All) == false)
{
filterSql = SqlContext.Sql();
}
if (hasCustomFilter)
{
foreach (var clause in customFilterWheres)
{
filterSql.Append($"AND ({clause.Item1})", clause.Item2);
}
}
if (includeUserGroups != null && includeUserGroups.Length > 0)
{
const string subQuery = @"AND (umbracoUser.id IN (SELECT DISTINCT umbracoUser.id
FROM umbracoUser
INNER JOIN umbracoUser2UserGroup ON umbracoUser2UserGroup.userId = umbracoUser.id
INNER JOIN umbracoUserGroup ON umbracoUserGroup.id = umbracoUser2UserGroup.userGroupId
WHERE umbracoUserGroup.userGroupAlias IN (@userGroups)))";
filterSql.Append(subQuery, new { userGroups = includeUserGroups });
}
if (excludeUserGroups != null && excludeUserGroups.Length > 0)
{
const string subQuery = @"AND (umbracoUser.id NOT IN (SELECT DISTINCT umbracoUser.id
FROM umbracoUser
INNER JOIN umbracoUser2UserGroup ON umbracoUser2UserGroup.userId = umbracoUser.id
INNER JOIN umbracoUserGroup ON umbracoUserGroup.id = umbracoUser2UserGroup.userGroupId
WHERE umbracoUserGroup.userGroupAlias IN (@userGroups)))";
filterSql.Append(subQuery, new { userGroups = excludeUserGroups });
}
if (userState != null && userState.Length > 0)
{
//the "ALL" state doesn't require any filtering so we ignore that, if it exists in the list we don't do any filtering
if (userState.Contains(UserState.All) == false)
{
var sb = new StringBuilder("(");
var appended = false;
if (userState.Contains(UserState.Active))
{
sb.Append("(userDisabled = 0 AND userNoConsole = 0 AND lastLoginDate IS NOT NULL)");
appended = true;
}
if (userState.Contains(UserState.Inactive))
{
if (appended)
{
sb.Append(" OR ");
}
sb.Append("(userDisabled = 0 AND userNoConsole = 0 AND lastLoginDate IS NULL)");
appended = true;
}
if (userState.Contains(UserState.Disabled))
{
if (appended)
{
sb.Append(" OR ");
}
sb.Append("(userDisabled = 1)");
appended = true;
}
if (userState.Contains(UserState.LockedOut))
{
if (appended)
{
sb.Append(" OR ");
}
sb.Append("(userNoConsole = 1)");
appended = true;
}
if (userState.Contains(UserState.Invited))
{
if (appended)
{
sb.Append(" OR ");
}
sb.Append("(lastLoginDate IS NULL AND userDisabled = 1 AND invitedDate IS NOT NULL)");
appended = true;
}
sb.Append(")");
filterSql.Append("AND " + sb);
}
}
// create base query
var sql = SqlContext.Sql()
.Select <UserDto>()
.From <UserDto>();
// apply query
if (query != null)
{
sql = new SqlTranslator <IUser>(sql, query).Translate();
}
// get sorted and filtered sql
var sqlNodeIdsWithSort = ApplySort(ApplyFilter(sql, filterSql, query != null), orderDirection, orderBy);
// get a page of results and total count
var pagedResult = Database.Page <UserDto>(pageIndex + 1, pageSize, sqlNodeIdsWithSort);
totalRecords = Convert.ToInt32(pagedResult.TotalItems);
// map references
PerformGetReferencedDtos(pagedResult.Items);
return(pagedResult.Items.Select(UserFactory.BuildEntity));
}
private static Sql ToSql <T>(this Expression <Func <T, bool> > query, IDatabase db)
{
var translator = new SqlTranslator(db);
return(translator.Translate(query));
}
