public static object ExecuteScalar(IDbConnection conn, string sql, SqlSyntax syntax)
{
switch (syntax)
{
case SqlSyntax.MSSQL:
return SqlHelper.ExecuteScalar((SqlConnection)conn, CommandType.Text, sql, null);
case SqlSyntax.Oracle:
return OracleHelper.ExecuteScalar((OracleConnection)conn, CommandType.Text, sql, null);
case SqlSyntax.Mdx:
return MdxHelper.ExecuteScalar((AdomdConnection)conn, sql);
default: return null;
}
}
public static void ExecuteNonQuery(IDbConnection conn, string sql, SqlSyntax syntax)
{
switch (syntax)
{
case SqlSyntax.MSSQL:
SqlHelper.ExecuteNonQuery((SqlConnection)conn, CommandType.Text, sql, null);
break;
case SqlSyntax.Oracle:
break;
case SqlSyntax.Mdx:
MdxHelper.ExecuteNonQuery((AdomdConnection)conn, sql);
break;
}
}
public static DataTable ExecuteDataTable(IDbConnection conn, string sql, SqlSyntax syntax)
{
switch (syntax)
{
case SqlSyntax.MSSQL:
return SqlHelper.ExecuteDataTable((SqlConnection)conn, CommandType.Text, sql, null);
case SqlSyntax.Oracle:
return OracleHelper.ExecuteDataTable((OracleConnection)conn, CommandType.Text, sql, null);
case SqlSyntax.Mdx:
CellSet cellSet = MdxHelper.ExecuteCellSet((AdomdConnection)conn, sql);
if (cellSet != null)
{
return cellSet.ToDataTable();
}
return null;
default: return null;
}
}
protected override IRelation?PerformGet(int id)
{
Sql <ISqlContext> sql = GetBaseQuery(false);
sql.Where(GetBaseWhereClause(), new { id });
RelationDto?dto = Database.Fetch <RelationDto>(SqlSyntax.SelectTop(sql, 1)).FirstOrDefault();
if (dto == null)
{
return(null);
}
IRelationType?relationType = _relationTypeRepository.Get(dto.RelationType);
if (relationType == null)
{
throw new InvalidOperationException(string.Format("RelationType with Id: {0} doesn't exist", dto.RelationType));
}
return(DtoToEntity(dto, relationType));
}
public override string ToString()
{
if (IsExpressionSupported() == false)
{
return(string.Empty);
}
var sb = new StringBuilder();
foreach (string columnName in ColumnNames)
{
if (ColumnNames.First() != columnName)
{
sb.AppendLine(";");
}
sb.AppendFormat(SqlSyntax.DropColumn,
SqlSyntax.GetQuotedTableName(TableName),
SqlSyntax.GetQuotedColumnName(columnName));
}
return(sb.ToString());
}
/// <summary>
/// Adds the indexes to the merchProductVariant table.
/// </summary>
public override void Up()
{
var dbIndexes = SqlSyntax.GetDefinedIndexes(ApplicationContext.Current.DatabaseContext.Database)
.Select(x => new DbIndexDefinition
{
TableName = x.Item1,
IndexName = x.Item2,
ColumnName = x.Item3,
IsUnique = x.Item4
}).ToArray();
if (dbIndexes == null)
{
throw new NullReferenceException();
}
CreateIndex(dbIndexes, "IX_merchProductVariantName", "name");
CreateIndex(dbIndexes, "IX_merchProductVariantPrice", "price");
CreateIndex(dbIndexes, "IX_merchProductVariantSalePrice", "salePrice");
CreateIndex(dbIndexes, "IX_merchProductVariantBarcode", "barcode");
CreateIndex(dbIndexes, "IX_merchProductVariantManufacturer", "manufacturer");
}
public override void Up()
{
//Don't exeucte if the column is already there
var columns = SqlSyntax.GetColumnsInSchema(Context.Database).ToArray();
if (columns.Any(x => x.TableName.InvariantEquals("cmsPropertyType") && x.ColumnName.InvariantEquals("uniqueID")) == false)
{
Create.Column("uniqueID").OnTable("cmsPropertyType").AsGuid().NotNullable().WithDefault(SystemMethods.NewGuid);
// unique constraint on name + version
Create.Index("IX_cmsPropertyTypeUniqueID").OnTable("cmsPropertyType")
.OnColumn("uniqueID").Ascending()
.WithOptions()
.NonClustered()
.WithOptions()
.Unique();
// fill in the data in a way that is consistent over all environments
// (ie cannot use random guids, http://issues.umbraco.org/issue/U4-6942)
foreach (var data in Context.Database.Query <dynamic>(@"
SELECT cmsPropertyType.id ptId, cmsPropertyType.Alias ptAlias, cmsContentType.alias ctAlias, umbracoNode.nodeObjectType nObjType
FROM cmsPropertyType
INNER JOIN cmsContentType
ON cmsPropertyType.contentTypeId = cmsContentType.nodeId
INNER JOIN umbracoNode
ON cmsContentType.nodeId = umbracoNode.id"))
{
// create a consistent guid from
// property alias + content type alias + object type
string guidSource = data.ptAlias + data.ctAlias + data.nObjType;
var guid = guidSource.ToGuid();
// set the Unique Id to the one we've generated
Update.Table("cmsPropertyType").Set(new { uniqueID = guid }).Where(new { id = data.ptId });
}
}
}
public override void Up()
{
//Now we need to check if we can actually d6 this because we won't be able to if there's data in there that is too long
//http://issues.umbraco.org/issue/U4-9758
Execute.Code(database =>
{
var dbIndexes = SqlSyntax.GetDefinedIndexesDefinitions(database);
var colLen = (SqlSyntax is MySqlSyntaxProvider)
? database.ExecuteScalar <int?>("select max(LENGTH(LoginName)) from cmsMember")
: database.ExecuteScalar <int?>("select max(datalength(LoginName)) from cmsMember");
if (colLen < 900 == false)
{
return(null);
}
var localContext = new LocalMigrationContext(Context.CurrentDatabaseProvider, database, SqlSyntax, Logger);
//if an index exists on this table we need to drop it. Normally we'd check via index name but in some odd cases (i.e. Our)
//the index name is something odd (starts with "mi_"). In any case, the index cannot exist if we want to alter the column
//so we'll drop whatever index is there and add one with the correct name after.
var loginNameIndex = dbIndexes.FirstOrDefault(x => x.TableName.InvariantEquals("cmsMember") && x.ColumnName.InvariantEquals("LoginName"));
if (loginNameIndex != null)
{
localContext.Delete.Index(loginNameIndex.IndexName).OnTable("cmsMember");
}
//we can apply the col length change
localContext.Alter.Table("cmsMember")
.AlterColumn("LoginName")
.AsString(225)
.NotNullable();
return(localContext.GetSql());
});
}
/// <summary>
/// Updates the merchProductOption table adding the shared and detached content type key fields.
/// </summary>
public override void Up()
{
var database = ApplicationContext.Current.DatabaseContext.Database;
var columns = SqlSyntax.GetColumnsInSchema(database).ToArray();
// 'shared' column
if (columns.Any(
x => x.TableName.InvariantEquals("merchProductOption") && x.ColumnName.InvariantEquals("shared"))
== false)
{
Logger.Info(typeof(AddProductOptionColumns), "Adding shared column to merchProductOption table.");
//// Add the new 'shared' column
Create.Column("shared").OnTable("merchProductOption").AsBoolean().WithDefaultValue(false);
}
// 'detachedContentType' column
if (columns.Any(x => x.TableName.InvariantEquals("merchProductOption") && x.ColumnName.InvariantEquals("uiOption"))
== false)
{
Logger.Info(typeof(AddProductOptionColumns), "Adding uiOption column to merchProductOption table.");
//// Add the new 'shared' column
Create.Column("uiOption").OnTable("merchProductOption").AsString(50).Nullable();
}
// 'detachedContentType' column
if (columns.Any(x => x.TableName.InvariantEquals("merchProductOption") && x.ColumnName.InvariantEquals("detachedContentTypeKey"))
== false)
{
Logger.Info(typeof(AddProductOptionColumns), "Adding detachedContentType column to merchProductOption table.");
//// Add the new 'shared' column
Create.Column("detachedContentTypeKey").OnTable("merchProductOption").AsGuid().Nullable()
.ForeignKey("FK_merchProductOptionDetachedContent_merchProductOption", "merchDetachedContentType", "pk");
}
}
public IEnumerable <IUser> GetUsersAssignedToSection(string sectionAlias)
{
//Here we're building up a query that looks like this, a sub query is required because the resulting structure
// needs to still contain all of the section rows per user.
//SELECT *
//FROM [umbracoUser]
//LEFT JOIN [umbracoUser2app]
//ON [umbracoUser].[id] = [umbracoUser2app].[user]
//WHERE umbracoUser.id IN (SELECT umbracoUser.id
// FROM [umbracoUser]
// LEFT JOIN [umbracoUser2app]
// ON [umbracoUser].[id] = [umbracoUser2app].[user]
// WHERE umbracoUser2app.app = 'content')
var sql = GetBaseQuery(false);
var innerSql = GetBaseQuery("umbracoUser.id");
innerSql.Where("umbracoUser2app.app = " + SqlSyntax.GetQuotedValue(sectionAlias));
sql.Where(string.Format("umbracoUser.id IN ({0})", innerSql.SQL));
return(ConvertFromDtos(Database.Fetch <UserDto, User2AppDto, UserDto>(new UserSectionRelator().Map, sql)));
}
private IEnumerable <IUmbracoEntity> GetAxisDefinitions(params TemplateDto[] templates)
{
//look up the simple template definitions that have a master template assigned, this is used
// later to populate the template item's properties
var childIdsSql = new Sql()
.Select("nodeId,alias,parentID")
.From <TemplateDto>(SqlSyntax)
.InnerJoin <NodeDto>(SqlSyntax)
.On <TemplateDto, NodeDto>(SqlSyntax, dto => dto.NodeId, dto => dto.NodeId)
//lookup axis's
.Where("umbracoNode." + SqlSyntax.GetQuotedColumnName("id") + " IN (@parentIds) OR umbracoNode.parentID IN (@childIds)",
new { parentIds = templates.Select(x => x.NodeDto.ParentId), childIds = templates.Select(x => x.NodeId) });
var childIds = Database.Fetch <dynamic>(childIdsSql)
.Select(x => new UmbracoEntity
{
Id = x.nodeId,
ParentId = x.parentID,
Name = x.alias
});
return(childIds);
}
public IEnumerable <IUserGroup> GetGroupsAssignedToSection(string sectionAlias)
{
// Here we're building up a query that looks like this, a sub query is required because the resulting structure
// needs to still contain all of the section rows per user group.
// SELECT *
// FROM [umbracoUserGroup]
// LEFT JOIN [umbracoUserGroup2App]
// ON [umbracoUserGroup].[id] = [umbracoUserGroup2App].[user]
// WHERE umbracoUserGroup.id IN (SELECT umbracoUserGroup.id
// FROM [umbracoUserGroup]
// LEFT JOIN [umbracoUserGroup2App]
// ON [umbracoUserGroup].[id] = [umbracoUserGroup2App].[user]
// WHERE umbracoUserGroup2App.app = 'content')
Sql <ISqlContext> sql = GetBaseQuery(QueryType.Many);
Sql <ISqlContext> innerSql = GetBaseQuery(QueryType.Ids);
innerSql.Where("umbracoUserGroup2App.app = " + SqlSyntax.GetQuotedValue(sectionAlias));
sql.Where($"umbracoUserGroup.id IN ({innerSql.SQL})");
AppendGroupBy(sql);
return(Database.Fetch <UserGroupDto>(sql).Select(x => UserGroupFactory.BuildEntity(_shortStringHelper, x)));
}
public override void Migrate()
{
// allow null for the `data` field
if (DatabaseType.IsSqlCe())
{
// SQLCE does not support altering NTEXT, so we have to jump through some hoops to do it
// All column ordering must remain the same as what is defined in the DTO so we need to create a temp table,
// drop orig and then re-create/copy.
Create.Table <ContentNuDtoTemp>(withoutKeysAndIndexes: true).Do();
Execute.Sql($"INSERT INTO [{TempTableName}] SELECT nodeId, published, data, rv FROM [{Constants.DatabaseSchema.Tables.NodeData}]").Do();
Delete.Table(Constants.DatabaseSchema.Tables.NodeData).Do();
Create.Table <ContentNuDto>().Do();
Execute.Sql($"INSERT INTO [{Constants.DatabaseSchema.Tables.NodeData}] SELECT nodeId, published, data, rv, NULL FROM [{TempTableName}]").Do();
}
else
{
AlterColumn <ContentNuDto>(Constants.DatabaseSchema.Tables.NodeData, "data");
}
var columns = SqlSyntax.GetColumnsInSchema(Context.Database).ToList();
AddColumnIfNotExists <ContentNuDto>(columns, "dataRaw");
}
public override void Up()
{
//Don't exeucte if the table is already there
var tables = SqlSyntax.GetTablesInSchema(Context.Database).ToArray();
if (tables.InvariantContains("umbracoAccess"))
{
return;
}
Create.Table("umbracoAccess")
.WithColumn("id").AsGuid().NotNullable().PrimaryKey("PK_umbracoAccess")
.WithColumn("nodeId").AsInt32().NotNullable().ForeignKey("FK_umbracoAccess_umbracoNode_id", "umbracoNode", "id")
.WithColumn("loginNodeId").AsInt32().NotNullable().ForeignKey("FK_umbracoAccess_umbracoNode_id1", "umbracoNode", "id")
.WithColumn("noAccessNodeId").AsInt32().NotNullable().ForeignKey("FK_umbracoAccess_umbracoNode_id2", "umbracoNode", "id")
.WithColumn("createDate").AsDateTime().NotNullable().WithDefault(SystemMethods.CurrentDateTime)
.WithColumn("updateDate").AsDateTime().NotNullable().WithDefault(SystemMethods.CurrentDateTime);
//unique constraint on node id = 1:1
Create.Index("IX_umbracoAccess_nodeId").OnTable("umbracoAccess").OnColumn("nodeId").Unique();
Create.Table("umbracoAccessRule")
.WithColumn("id").AsGuid().NotNullable().PrimaryKey("PK_umbracoAccessRule")
.WithColumn("accessId").AsGuid().NotNullable().ForeignKey("FK_umbracoAccessRule_umbracoAccess_id", "umbracoAccess", "id")
.WithColumn("ruleValue").AsString().NotNullable()
.WithColumn("ruleType").AsString().NotNullable()
.WithColumn("createDate").AsDateTime().NotNullable().WithDefault(SystemMethods.CurrentDateTime)
.WithColumn("updateDate").AsDateTime().NotNullable().WithDefault(SystemMethods.CurrentDateTime);
//unique constraint on node + ruleValue + ruleType
Create.Index("IX_umbracoAccessRule").OnTable("umbracoAccessRule")
.OnColumn("accessId").Ascending()
.OnColumn("ruleValue").Ascending()
.OnColumn("ruleType").Ascending()
.WithOptions()
.Unique();
}
private void JoinToString(Join join, StringBuilder sb, bool modifySelf)
{
sb.Append(join.GetKeyword());
sb.Append(' ');
sb.Append(SqlSyntax.AutoBracketValid(join.Table));
// joinAlias belirtilmişse ekle
if (!string.IsNullOrEmpty(join.Name))
{
sb.Append(' ');
sb.Append(join.Name);
}
if (join.OnCriteria is object &&
!join.OnCriteria.IsEmpty)
{
sb.Append(" ON ");
if (!(join.OnCriteria is BinaryCriteria))
{
sb.Append('(');
}
if (modifySelf)
{
sb.Append(join.OnCriteria.ToString(this));
}
else
{
sb.Append(join.OnCriteria.ToStringIgnoreParams());
}
if (!(join.OnCriteria is BinaryCriteria))
{
sb.Append(')');
}
}
}
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 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())));
}
private string MigrationCode(Database database)
{
var localContext = new LocalMigrationContext(Context.CurrentDatabaseProvider, database, SqlSyntax, Logger);
//Clear all stylesheet data if the tables exist
var tables = SqlSyntax.GetTablesInSchema(Context.Database).ToArray();
if (tables.InvariantContains("cmsStylesheetProperty"))
{
localContext.Delete.FromTable("cmsStylesheetProperty").AllRows();
localContext.Delete.FromTable("umbracoNode").Row(new { nodeObjectType = new Guid(Constants.ObjectTypes.StylesheetProperty) });
localContext.Delete.Table("cmsStylesheetProperty");
}
if (tables.InvariantContains("cmsStylesheet"))
{
localContext.Delete.FromTable("cmsStylesheet").AllRows();
localContext.Delete.FromTable("umbracoNode").Row(new { nodeObjectType = new Guid(Constants.ObjectTypes.Stylesheet) });
localContext.Delete.Table("cmsStylesheet");
}
return(localContext.GetSql());
}
public override void Up()
{
var indexes = SqlSyntax.GetDefinedIndexes(Context.Database)
.Select(x => new DbIndexDefinition()
{
TableName = x.Item1,
IndexName = x.Item2,
ColumnName = x.Item3,
IsUnique = x.Item4
}).ToArray();
// drop the index if it exists
if (indexes.Any(x => x.IndexName.InvariantEquals("IX_umbracoNodeUniqueID")))
{
Delete.Index("IX_umbracoNodeUniqueID").OnTable("umbracoNode");
}
// set uniqueID to be non-nullable
// the index *must* be dropped else 'one or more objects access this column' exception
Alter.Table("umbracoNode").AlterColumn("uniqueID").AsGuid().NotNullable();
// create the index
Create.Index("IX_umbracoNode_uniqueID").OnTable("umbracoNode").OnColumn("uniqueID").Unique();
}
protected override IRelation PerformGet(int id)
{
var sql = GetBaseQuery(false);
sql.Where(GetBaseWhereClause(), new { id });
var dto = Database.Fetch <RelationDto>(SqlSyntax.SelectTop(sql, 1)).FirstOrDefault();
if (dto == null)
{
return(null);
}
var relationType = _relationTypeRepository.Get(dto.RelationType);
if (relationType == null)
{
throw new Exception(string.Format("RelationType with Id: {0} doesn't exist", dto.RelationType));
}
var factory = new RelationFactory(relationType);
return(DtoToEntity(dto, factory));
}
public IEnumerable <ITemplate> GetDescendants(int masterTemplateId)
{
var sql = GetBaseQuery(false);
if (masterTemplateId > 0)
{
//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>(
new Sql().Select(SqlSyntax.GetQuotedColumnName("path"))
.From <TemplateDto>(SqlSyntax)
.InnerJoin <NodeDto>(SqlSyntax)
.On <TemplateDto, NodeDto>(SqlSyntax, dto => dto.NodeId, dto => dto.NodeId)
.Where <NodeDto>(dto => dto.NodeId == masterTemplateId));
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)));
}
public override void Migrate()
{
// these have been obsoleted, need to copy the values here
var stylesheetPropertyObjectType = new Guid("5555da4f-a123-42b2-4488-dcdfb25e4111");
var stylesheetObjectType = new Guid("9F68DA4F-A3A8-44C2-8226-DCBD125E4840");
//Clear all stylesheet data if the tables exist
var tables = SqlSyntax.GetTablesInSchema(Context.Database).ToArray();
if (tables.InvariantContains("cmsStylesheetProperty"))
{
Delete.FromTable("cmsStylesheetProperty").AllRows().Do();
Delete.FromTable("umbracoNode").Row(new { nodeObjectType = stylesheetPropertyObjectType }).Do();
Delete.Table("cmsStylesheetProperty").Do();
}
if (tables.InvariantContains("cmsStylesheet"))
{
Delete.FromTable("cmsStylesheet").AllRows().Do();
Delete.FromTable("umbracoNode").Row(new { nodeObjectType = stylesheetObjectType }).Do();
Delete.Table("cmsStylesheet").Do();
}
}
public override IEnumerable <string> CreateCommands(SqlSyntax syntax)
{
string columnList = string.Join(", ", Columns.OrderBy(col => col.Position).Select(col => $"<{col.Name}> {((col.SortDirection == SortDirection.Ascending) ? "ASC" : "DESC")}"));
string clustered = (IsClustered) ? "CLUSTERED" : "NONCLUSTERED";
switch (Type)
{
case IndexType.UniqueIndex:
yield return($"CREATE {clustered} INDEX <{Name}> ON <{Table}> ({columnList})");
break;
case IndexType.UniqueConstraint:
yield return($"ALTER TABLE <{Table}> ADD CONSTRAINT <{Name}> UNIQUE {clustered} ({columnList})");
break;
case IndexType.PrimaryKey:
yield return($"ALTER TABLE <{Table}> ADD CONSTRAINT <{Name}> PRIMARY KEY {clustered} ({columnList})");
break;
}
}
protected override IEnumerable <EntityContainer> PerformGetAll(params int[] ids)
{
if (ids.Any())
{
//we need to batch these in groups of 2000 so we don't exceed the max 2100 limit
return(ids.InGroupsOf(2000).SelectMany(@group =>
{
var sql = GetBaseQuery(false)
.Where("nodeObjectType=@umbracoObjectTypeId", new { umbracoObjectTypeId = NodeObjectTypeId })
.Where(string.Format("{0} IN (@ids)", SqlSyntax.GetQuotedColumnName("id")), new { ids = @group });
sql.OrderBy <NodeDto>(x => x.Level, SqlSyntax);
return Database.Fetch <NodeDto>(sql).Select(CreateEntity);
}));
}
else
{
var sql = GetBaseQuery(false)
.Where("nodeObjectType=@umbracoObjectTypeId", new { umbracoObjectTypeId = NodeObjectTypeId });
sql.OrderBy <NodeDto>(x => x.Level, SqlSyntax);
return(Database.Fetch <NodeDto>(sql).Select(CreateEntity));
}
}
private void ApplyOrdering(ref Sql <ISqlContext> sql, Ordering ordering)
{
if (sql == null)
{
throw new ArgumentNullException(nameof(sql));
}
if (ordering == null)
{
throw new ArgumentNullException(nameof(ordering));
}
// TODO: although the default ordering string works for name, it wont work for others without a table or an alias of some sort
// As more things are attempted to be sorted we'll prob have to add more expressions here
string orderBy;
switch (ordering.OrderBy?.ToUpperInvariant())
{
case "PATH":
orderBy = SqlSyntax.GetQuotedColumn(NodeDto.TableName, "path");
break;
default:
orderBy = ordering.OrderBy ?? string.Empty;
break;
}
if (ordering.Direction == Direction.Ascending)
{
sql.OrderBy(orderBy);
}
else
{
sql.OrderByDescending(orderBy);
}
}
public override void Up()
{
Execute.Code(database =>
{
var dbIndexes = SqlSyntax.GetDefinedIndexesDefinitions(database);
//make sure it doesn't already exist
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsPropertyTypeAlias")) == false)
{
var localContext = new LocalMigrationContext(Context.CurrentDatabaseProvider, database, SqlSyntax, Logger);
//we can apply the index
localContext.Create.Index("IX_cmsPropertyTypeAlias").OnTable("cmsPropertyType")
.OnColumn("Alias")
.Ascending()
.WithOptions()
.NonClustered();
return(localContext.GetSql());
}
return(null);
});
}
public override void Up()
{
// don't execute if the column is not there anymore
var columns = SqlSyntax.GetColumnsInSchema(Context.Database).ToArray();
if (columns.Any(x => x.TableName.InvariantEquals("cmsPropertyTypeGroup") && x.ColumnName.InvariantEquals("parentGroupId")) == false)
{
return;
}
//This constraing can be based on old aliases, before removing them, check they exist
var constraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct().ToArray();
if (constraints.Any(x => x.Item1.InvariantEquals("cmsPropertyTypeGroup") && x.Item3.InvariantEquals("FK_cmsPropertyTypeGroup_cmsPropertyTypeGroup_id")))
{
Delete.ForeignKey("FK_cmsPropertyTypeGroup_cmsPropertyTypeGroup_id").OnTable("cmsPropertyTypeGroup");
}
if (constraints.Any(x => x.Item1.InvariantEquals("cmsPropertyTypeGroup") && x.Item3.InvariantEquals("FK_cmsPropertyTypeGroup_cmsPropertyTypeGroup")))
{
Delete.ForeignKey("FK_cmsPropertyTypeGroup_cmsPropertyTypeGroup").OnTable("cmsPropertyTypeGroup");
}
Delete.Column("parentGroupId").FromTable("cmsPropertyTypeGroup");
}
public override void Up()
{
Execute.Code(database =>
{
//Now we need to check if we can actually d6 this because we won't be able to if there's data in there that is too long
//http://issues.umbraco.org/issue/U4-9758
var colLen = (SqlSyntax is MySqlSyntaxProvider)
? database.ExecuteScalar <int?>("select max(LENGTH(LoginName)) from cmsMember")
: database.ExecuteScalar <int?>("select max(datalength(LoginName)) from cmsMember");
if (colLen < 900 == false && colLen != null)
{
return(null);
}
var dbIndexes = SqlSyntax.GetDefinedIndexesDefinitions(Context.Database);
//make sure it doesn't already exist
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsMember_LoginName")) == false)
{
var localContext = new LocalMigrationContext(Context.CurrentDatabaseProvider, database, SqlSyntax, Logger);
//we can apply the index
localContext.Create.Index("IX_cmsMember_LoginName").OnTable("cmsMember")
.OnColumn("LoginName")
.Ascending()
.WithOptions()
.NonClustered();
return(localContext.GetSql());
}
return(null);
});
}
public override void Up()
{
var dbIndexes = SqlSyntax.GetDefinedIndexes(Context.Database)
.Select(x => new DbIndexDefinition()
{
TableName = x.Item1,
IndexName = x.Item2,
ColumnName = x.Item3,
IsUnique = x.Item4
}).ToArray();
//do not create any indexes if they already exist in the database
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_umbracoNodeTrashed")) == false)
{
Create.Index("IX_umbracoNodeTrashed").OnTable("umbracoNode").OnColumn("trashed").Ascending().WithOptions().NonClustered();
}
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsContentVersion_ContentId")) == false)
{
Create.Index("IX_cmsContentVersion_ContentId").OnTable("cmsContentVersion").OnColumn("ContentId").Ascending().WithOptions().NonClustered();
}
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsDocument_published")) == false)
{
Create.Index("IX_cmsDocument_published").OnTable("cmsDocument").OnColumn("published").Ascending().WithOptions().NonClustered();
}
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsDocument_newest")) == false)
{
Create.Index("IX_cmsDocument_newest").OnTable("cmsDocument").OnColumn("newest").Ascending().WithOptions().NonClustered();
}
//We need to do this for SQL Azure V2 since it does not let you drop any clustered indexes
// Issue: http://issues.umbraco.org/issue/U4-5673
if (Context.CurrentDatabaseProvider == DatabaseProviders.SqlServer || Context.CurrentDatabaseProvider == DatabaseProviders.SqlAzure)
{
var version = Context.Database.ExecuteScalar <string>("SELECT @@@@VERSION");
if (version.Contains("Microsoft SQL Azure"))
{
var parts = version.Split(new[] { '-' }, StringSplitOptions.RemoveEmptyEntries).Select(x => x.Trim()).ToArray();
if (parts.Length > 1)
{
if (parts[1].StartsWith("11."))
{
//we want to drop the umbracoUserLogins_Index index since it is named incorrectly and then re-create it so
// it follows the standard naming convention
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("umbracoUserLogins_Index")))
{
//It's the old version that doesn't support dropping a clustered index on a table, so we need to do some manual work.
ExecuteSqlAzureSqlForChangingIndex();
}
return;
}
}
}
}
//we want to drop the umbracoUserLogins_Index index since it is named incorrectly and then re-create it so
// it follows the standard naming convention
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("umbracoUserLogins_Index")))
{
Delete.Index("umbracoUserLogins_Index").OnTable("umbracoUserLogins");
}
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_umbracoUserLogins_Index")) == false)
{
Create.Index("IX_umbracoUserLogins_Index").OnTable("umbracoUserLogins").OnColumn("contextID").Ascending().WithOptions().Clustered();
}
}
public override void Migrate()
{
//Now we need to check if we can actually do this because we won't be able to if there's data in there that is too long
var database = Database;
var dbIndexes = SqlSyntax.GetDefinedIndexesDefinitions(database);
var colLen = SqlSyntax is MySqlSyntaxProvider
? database.ExecuteScalar <int?>(string.Format("select max(LENGTH({0})) from cmsDictionary", SqlSyntax.GetQuotedColumnName("key")))
: database.ExecuteScalar <int?>(string.Format("select max(datalength({0})) from cmsDictionary", SqlSyntax.GetQuotedColumnName("key")));
if (colLen < 900 == false)
{
return;
}
//if it exists we need to drop it first
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsDictionary_key")))
{
Delete.Index("IX_cmsDictionary_key").OnTable("cmsDictionary").Do();
}
//we can apply the col length change
Alter.Table("cmsDictionary")
.AlterColumn("key")
.AsString(450)
.NotNullable()
.Do();
}
public override void Up()
{
if (Context.CurrentDatabaseProvider == DatabaseProviders.MySql)
{
var constraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct().ToArray();
//This should be 2 because this table has 2 keys
if (constraints.Count(x => x.Item1.InvariantEquals("cmsContentTypeAllowedContentType") && x.Item3.InvariantEquals("PRIMARY")) == 0)
{
Create.PrimaryKey("PK_cmsContentTypeAllowedContentType")
.OnTable("cmsContentTypeAllowedContentType")
.Columns(new[] { "Id", "AllowedId" });
}
//This should be 2 because this table has 2 keys
if (constraints.Count(x => x.Item1.InvariantEquals("cmsDocumentType") && x.Item3.InvariantEquals("PRIMARY")) == 0)
{
Create.PrimaryKey("PK_cmsDocumentType")
.OnTable("cmsDocumentType")
.Columns(new[] { "contentTypeNodeId", "templateNodeId" });
}
//This should be 2 because this table has 2 keys
if (constraints.Count(x => x.Item1.InvariantEquals("cmsMember2MemberGroup") && x.Item3.InvariantEquals("PRIMARY")) == 0)
{
Create.PrimaryKey("PK_cmsMember2MemberGroup")
.OnTable("cmsMember2MemberGroup")
.Columns(new[] { "Member", "MemberGroup" });
}
//This should be 2 because this table has 2 keys
if (constraints.Count(x => x.Item1.InvariantEquals("cmsPreviewXml") && x.Item3.InvariantEquals("PRIMARY")) == 0)
{
Create.PrimaryKey("PK_cmsContentPreviewXml")
.OnTable("cmsPreviewXml")
.Columns(new[] { "nodeId", "versionId" });
}
//This should be 2 because this table has 2 keys
if (constraints.Count(x => x.Item1.InvariantEquals("umbracoUser2app") && x.Item3.InvariantEquals("PRIMARY")) == 0)
{
Create.PrimaryKey("PK_user2app")
.OnTable("umbracoUser2app")
.Columns(new[] { "user", "app" });
}
//This should be 2 because this table has 3 keys
if (constraints.Count(x => x.Item1.InvariantEquals("umbracoUser2NodeNotify") && x.Item3.InvariantEquals("PRIMARY")) == 0)
{
Create.PrimaryKey("PK_umbracoUser2NodeNotify")
.OnTable("umbracoUser2NodeNotify")
.Columns(new[] { "userId", "nodeId", "action" });
}
//This should be 2 because this table has 3 keys
if (constraints.Count(x => x.Item1.InvariantEquals("umbracoUser2NodePermission") && x.Item3.InvariantEquals("PRIMARY")) == 0)
{
Create.PrimaryKey("PK_umbracoUser2NodePermission")
.OnTable("umbracoUser2NodePermission")
.Columns(new[] { "userId", "nodeId", "permission" });
}
}
}
protected override string GetSql()
{
return(SqlSyntax.Format(Index));
}
public IEnumerable <IRedirectUrl> SearchUrls(string searchTerm, long pageIndex, int pageSize, out long total)
{
var 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);
var result = Database.Page <RedirectUrlDto>(pageIndex + 1, pageSize, sql);
total = Convert.ToInt32(result.TotalItems);
var rules = result.Items.Select(Map);
return(rules);
}
public static IDbConnection GetConnection(SqlSyntax syntax, string connstr)
{
switch (syntax)
{
case SqlSyntax.MSSQL:
return SqlHelper.GetConnection(connstr);
case SqlSyntax.Oracle:
return OracleHelper.GetConnection(connstr);
case SqlSyntax.Mdx:
return MdxHelper.GetConnection(connstr);
default: return null;
}
}
