public override void Up()
        {
            Delete.Table("umbracoAppTree");

            //NOTE: this is a hack since old umbraco versions might not have had their db's upgraded correctly so they are all quite inconsistent.
            // This is actually done in migration: RemoveUmbracoAppConstraints to target 4.9.0 but we've found with some db's that are currently at 4.9.1,
            // these upgrades did not run. So, now we not only have to check if these constraints exist, but we also have to check if the RemoveUmbracoAppConstraints
            // has executed since we cannot drop the same foreign key twice or we'll get errors.

            //Here we'll do a dirty check to see if RemoveUmbracoAppConstraints has executed
            if (Context.Expressions.Any(x =>
            {
                var b = x as MigrationExpressionBase;
                if (b == null)
                {
                    return(false);
                }
                return(b.Name == "FK_umbracoUser2app_umbracoApp");
            }) == false)
            {
                //These are the old aliases, before removing them, check they exist
                var constraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct().ToArray();
                if (constraints.Any(x => x.Item1.InvariantEquals("umbracoUser2app") && x.Item3.InvariantEquals("FK_umbracoUser2app_umbracoApp")))
                {
                    Delete.ForeignKey("FK_umbracoUser2app_umbracoApp").OnTable("umbracoUser2app");
                }
                if (constraints.Any(x => x.Item1.InvariantEquals("umbracoUser2app") && x.Item3.InvariantEquals("FK_umbracoUser2app_umbracoUser")))
                {
                    Delete.ForeignKey("FK_umbracoUser2app_umbracoUser").OnTable("umbracoUser2app");
                }
            }

            Delete.Table("umbracoApp");
        }
Exemplo n.º 2
0
        public override void Migrate()
        {
            var tables      = SqlSyntax.GetTablesInSchema(Context.Database).ToList();
            var constraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct().ToArray();
            var columns     = SqlSyntax.GetColumnsInSchema(Context.Database).ToArray();

            //In some very rare cases, there might alraedy be user group tables that we'll need to remove first
            //but of course we don't want to remove the tables we will be creating below if they already exist so
            //need to do some checks first since these old rare tables have a different schema
            RemoveOldTablesIfExist(tables, columns);

            if (AddNewTables(tables))
            {
                MigrateUserPermissions();
                MigrateUserTypesToGroups();
                DeleteOldTables(tables, constraints);
                SetDefaultIcons();
            }
            else
            {
                //if we aren't adding the tables, make sure that the umbracoUserGroup table has the correct FKs - these
                //were added after the beta release so we need to do some cleanup
                //if the FK doesn't exist
                if (constraints.Any(x => x.Item1.InvariantEquals("umbracoUserGroup") &&
                                    x.Item2.InvariantEquals("startContentId") &&
                                    x.Item3.InvariantEquals("FK_startContentId_umbracoNode_id")) == false)
                {
                    //before we add any foreign key we need to make sure there's no stale data in there which would  have happened in the beta
                    //release if a start node was assigned and then that start node was deleted.
                    Database.Execute(@"UPDATE umbracoUserGroup SET startContentId = NULL WHERE startContentId NOT IN (SELECT id FROM umbracoNode)");

                    Create.ForeignKey("FK_startContentId_umbracoNode_id")
                    .FromTable("umbracoUserGroup")
                    .ForeignColumn("startContentId")
                    .ToTable("umbracoNode")
                    .PrimaryColumn("id")
                    .OnDelete(Rule.None)
                    .OnUpdate(Rule.None)
                    .Do();
                }

                if (constraints.Any(x => x.Item1.InvariantEquals("umbracoUserGroup") &&
                                    x.Item2.InvariantEquals("startMediaId") &&
                                    x.Item3.InvariantEquals("FK_startMediaId_umbracoNode_id")) == false)
                {
                    //before we add any foreign key we need to make sure there's no stale data in there which would  have happened in the beta
                    //release if a start node was assigned and then that start node was deleted.
                    Database.Execute(@"UPDATE umbracoUserGroup SET startMediaId = NULL WHERE startMediaId NOT IN (SELECT id FROM umbracoNode)");

                    Create.ForeignKey("FK_startMediaId_umbracoNode_id")
                    .FromTable("umbracoUserGroup")
                    .ForeignColumn("startMediaId")
                    .ToTable("umbracoNode")
                    .PrimaryColumn("id")
                    .OnDelete(Rule.None)
                    .OnUpdate(Rule.None)
                    .Do();
                }
            }
        }
        public override void Up()
        {
            //This will work on mysql and should work on mssql however the old keys were not named consistently with how the keys are
            // structured now. So we need to do a check and manually remove them based on their old aliases.

            if (this.Context.CurrentDatabaseProvider == DatabaseProviders.MySql)
            {
                Delete.ForeignKey().FromTable("umbracoUser2app").ForeignColumn("app").ToTable("umbracoApp").PrimaryColumn("appAlias");
                Delete.ForeignKey().FromTable("umbracoAppTree").ForeignColumn("appAlias").ToTable("umbracoApp").PrimaryColumn("appAlias");
            }
            else
            {
                //These are the old aliases, before removing them, check they exist
                var constraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct().ToArray();

                if (constraints.Any(x => x.Item1.InvariantEquals("umbracoUser2app") && x.Item3.InvariantEquals("FK_umbracoUser2app_umbracoApp")))
                {
                    Delete.ForeignKey("FK_umbracoUser2app_umbracoApp").OnTable("umbracoUser2app");
                    //name this migration, this is a hack for DeleteAppTables to ensure it's not executed twice
                    ((MigrationExpressionBase)Context.Expressions.Last()).Name = "FK_umbracoUser2app_umbracoApp";
                }
                if (constraints.Any(x => x.Item1.InvariantEquals("umbracoUser2app") && x.Item3.InvariantEquals("FK_umbracoUser2app_umbracoUser")))
                {
                    Delete.ForeignKey("FK_umbracoUser2app_umbracoUser").OnTable("umbracoUser2app");
                }
            }
        }
Exemplo n.º 4
0
        public override void Up()
        {
            var constraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct().ToArray();

            //if the FK doesn't exist
            if (constraints.Any(x => x.Item1.InvariantEquals("cmsContent") && x.Item2.InvariantEquals("contentType") && x.Item3.InvariantEquals("FK_cmsContent_cmsContentType_nodeId")) == false)
            {
                //Before we can add the foreign key there cannot be any orphaned content, media, members
                var orphanedIds = Context.Database.Fetch <int>("SELECT contentType FROM cmsContent WHERE NOT EXISTS (SELECT cmsContentType.nodeId FROM cmsContentType WHERE cmsContentType.nodeId = cmsContent.contentType)");
                foreach (var orphanedId in orphanedIds)
                {
                    Delete.FromTable("cmsTask").Row(new { nodeId = orphanedId });
                    Delete.FromTable("umbracoUser2NodeNotify").Row(new { nodeId = orphanedId });
                    Delete.FromTable("umbracoUser2NodePermission").Row(new { nodeId = orphanedId });
                    Delete.FromTable("umbracoRelation").Row(new { parentId = orphanedId });
                    Delete.FromTable("umbracoRelation").Row(new { childId = orphanedId });
                    Delete.FromTable("cmsTagRelationship").Row(new { nodeId = orphanedId });
                    Delete.FromTable("umbracoDomains").Row(new { domainRootStructureID = orphanedId });
                    Delete.FromTable("cmsDocument").Row(new { nodeId = orphanedId });
                    Delete.FromTable("cmsPropertyData").Row(new { contentNodeId = orphanedId });
                    Delete.FromTable("cmsMember2MemberGroup").Row(new { Member = orphanedId });
                    Delete.FromTable("cmsMember").Row(new { nodeId = orphanedId });
                    Delete.FromTable("cmsPreviewXml").Row(new { nodeId = orphanedId });
                    Delete.FromTable("cmsContentVersion").Row(new { ContentId = orphanedId });
                    Delete.FromTable("cmsContentXml").Row(new { nodeId = orphanedId });
                    Delete.FromTable("cmsContent").Row(new { nodeId = orphanedId });
                    Delete.FromTable("umbracoNode").Row(new { id = orphanedId });
                }

                //Some very old schemas don't have an index on the cmsContentType.nodeId column, I'm not actually sure when it was added but
                // it is absolutely required to exist in order to add other foreign keys and much better for perf, so we'll need to check it's existence
                // this came to light from this issue: http://issues.umbraco.org/issue/U4-4133
                var dbIndexes = SqlSyntaxContext.SqlSyntaxProvider.GetDefinedIndexes(Context.Database)
                                .Select(x => new DbIndexDefinition()
                {
                    TableName  = x.Item1,
                    IndexName  = x.Item2,
                    ColumnName = x.Item3,
                    IsUnique   = x.Item4
                }).ToArray();
                if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsContentType")) == false)
                {
                    Create.Index("IX_cmsContentType").OnTable("cmsContentType").OnColumn("nodeId").Ascending().WithOptions().Unique();
                }
                if (dbIndexes.Any(x => x.TableName.InvariantEquals("cmsContentType") && x.ColumnName.InvariantEquals("icon")) == false)
                {
                    Create.Index("IX_cmsContentType_icon").OnTable("cmsContentType").OnColumn("icon").Ascending().WithOptions().NonClustered();
                }

                Create.ForeignKey("FK_cmsContent_cmsContentType_nodeId")
                .FromTable("cmsContent")
                .ForeignColumn("contentType")
                .ToTable("cmsContentType")
                .PrimaryColumn("nodeId")
                .OnDelete(Rule.None)
                .OnUpdate(Rule.None);
            }
        }
        private void Initial()
        {
            var constraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct().ToArray();

            //create a new col which we will make a foreign key, but first needs to be populated with data.
            Alter.Table("cmsTagRelationship").AddColumn("propertyTypeId").AsInt32().Nullable();

            //drop the foreign key on umbracoNode.  Must drop foreign key first before primary key can be removed in MySql.
            if (Context.CurrentDatabaseProvider == DatabaseProviders.MySql)
            {
                Delete.ForeignKey().FromTable("cmsTagRelationship").ForeignColumn("nodeId").ToTable("umbracoNode").PrimaryColumn("id");
                //check for another strange really old one that might have existed
                if (constraints.Any(x => x.Item1 == "cmsTagRelationship" && x.Item2 == "tagId"))
                {
                    Delete.ForeignKey().FromTable("cmsTagRelationship").ForeignColumn("tagId").ToTable("cmsTags").PrimaryColumn("id");
                }
            }
            else
            {
                //Before we try to delete this constraint, we'll see if it exists first, some older schemas never had it and some older schema's had this named
                // differently than the default.

                var constraintMatches = constraints.Where(x => x.Item1 == "cmsTagRelationship" && x.Item2 == "nodeId" && x.Item3.InvariantStartsWith("PK_") == false);

                foreach (var constraint in constraintMatches)
                {
                    Delete.ForeignKey(constraint.Item3).OnTable("cmsTagRelationship");
                }
            }

            //we need to drop the primary key, this is sql specific since MySQL has never had primary keys on this table
            // at least since 6.0 and the new installation way but perhaps it had them way back in 4.x so we need to check
            // it exists before trying to drop it.
            if (Context.CurrentDatabaseProvider == DatabaseProviders.MySql)
            {
                //this will let us know if this pk exists on this table
                if (constraints.Count(x => x.Item1.InvariantEquals("cmsTagRelationship") && x.Item3.InvariantEquals("PRIMARY")) > 0)
                {
                    Delete.PrimaryKey("PK_cmsTagRelationship").FromTable("cmsTagRelationship");
                }
            }
            else
            {
                //lookup the PK by name
                var pkName = constraints.FirstOrDefault(x => x.Item1.InvariantEquals("cmsTagRelationship") && x.Item3.InvariantStartsWith("PK_"));
                if (pkName != null)
                {
                    Delete.PrimaryKey(pkName.Item3).FromTable("cmsTagRelationship");
                }
            }
        }
        public override void Migrate()
        {
            if (ColumnExists("cmsContentXml", "Rv") == false)
            {
                Alter.Table("cmsContentXml").AddColumn("Rv").AsInt64().NotNullable().WithDefaultValue(0).Do();
            }

            if (ColumnExists("cmsPreviewXml", "Rv") == false)
            {
                Alter.Table("cmsPreviewXml").AddColumn("Rv").AsInt64().NotNullable().WithDefaultValue(0).Do();
            }

            // remove the any PK_ and the FK_ to cmsContentVersion.VersionId
            var constraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct().ToArray();
            var dups        = new List <string>();

            foreach (var c in constraints.Where(x => x.Item1.InvariantEquals("cmsPreviewXml") && x.Item3.InvariantStartsWith("PK_")))
            {
                var keyName = c.Item3.ToLowerInvariant();
                if (dups.Contains(keyName))
                {
                    Logger.Warn <RefactorXmlColumns>("Duplicate constraint '{Constraint}'", c.Item3);
                    continue;
                }
                dups.Add(keyName);
                Delete.PrimaryKey(c.Item3).FromTable(c.Item1).Do();
            }
            foreach (var c in constraints.Where(x => x.Item1.InvariantEquals("cmsPreviewXml") && x.Item3.InvariantStartsWith("FK_cmsPreviewXml_cmsContentVersion")))
            {
                Delete.ForeignKey().FromTable("cmsPreviewXml").ForeignColumn("VersionId")
                .ToTable("cmsContentVersion").PrimaryColumn("VersionId")
                .Do();
            }

            if (ColumnExists("cmsPreviewXml", "Timestamp"))
            {
                Delete.Column("Timestamp").FromTable("cmsPreviewXml").Do();
            }

            if (ColumnExists("cmsPreviewXml", "VersionId"))
            {
                RemoveDuplicates();
                Delete.Column("VersionId").FromTable("cmsPreviewXml").Do();
            }

            // re-create the primary key
            Create.PrimaryKey("PK_cmsPreviewXml")
            .OnTable("cmsPreviewXml")
            .Columns(new[] { "nodeId" })
            .Do();
        }
        public override void Up()
        {
            if (Context.CurrentDatabaseProvider == DatabaseProviders.MySql)
            {
                var constraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct().ToArray();

                //This should be 3 because this table has 3 keys
                if (constraints.Count(x => x.Item1.InvariantEquals("cmsTagRelationship") && x.Item3.InvariantEquals("PRIMARY")) == 0)
                {
                    Create.PrimaryKey("PK_cmsTagRelationship")
                    .OnTable("cmsTagRelationship")
                    .Columns(new[] { "nodeId", "propertyTypeId", "tagId" });
                }
            }
        }
Exemplo n.º 8
0
        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("cmsContentType2ContentType") && x.Item3.InvariantEquals("PRIMARY")) == 0)
                {
                    Create.PrimaryKey("PK_cmsContentType2ContentType")
                    .OnTable("cmsContentType2ContentType")
                    .Columns(new[] { "parentContentTypeId", "childContentTypeId" });
                }
            }
        }
        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()
        {
            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" });
                }
            }
        }
        public override void Up()
        {
            var constraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct().ToArray();

            //if the FK doesn't exist
            if (constraints.Any(x => x.Item1.InvariantEquals("cmsLanguageText") && x.Item2.InvariantEquals("languageId") && x.Item3.InvariantEquals("FK_cmsLanguageText_umbracoLanguage_id")) == false)
            {
                //Somehow, a language text item might end up with a language Id of zero or one that no longer exists
                //before we add the foreign key
                foreach (var pk in Context.Database.Query <int>(
                             "SELECT cmsLanguageText.pk FROM cmsLanguageText WHERE cmsLanguageText.languageId NOT IN (SELECT umbracoLanguage.id FROM umbracoLanguage)"))
                {
                    Delete.FromTable("cmsLanguageText").Row(new { pk = pk });
                }

                var columns = SqlSyntax.GetColumnsInSchema(Context.Database).Distinct().ToArray();

                if (columns.Any(x => x.ColumnName.InvariantEquals("id") &&
                                x.TableName.InvariantEquals("umbracoLanguage") &&
                                x.DataType.InvariantEquals("smallint")))
                {
                    //Ensure that the umbracoLanguage PK is INT and not SmallInt (which it  might be in older db versions)
                    // In order to 'change' this to an INT, we have to run a full migration script which is super annoying
                    Create.Table("umbracoLanguage_TEMP")
                    .WithColumn("id").AsInt32().NotNullable().Identity()
                    .WithColumn("languageISOCode").AsString(10).Nullable()
                    .WithColumn("languageCultureName").AsString(50).Nullable();

                    var currentData = this.Context.Database.Fetch <LanguageDto>(new Sql().Select("*").From <LanguageDto>(SqlSyntax));
                    foreach (var languageDto in currentData)
                    {
                        Insert.IntoTable("umbracoLanguage_TEMP")
                        .EnableIdentityInsert()
                        .Row(new { id = languageDto.Id, languageISOCode = languageDto.IsoCode, languageCultureName = languageDto.CultureName });
                    }

                    //ok, all data has been copied over, drop the old table, rename the temp table and re-add constraints.
                    Delete.Table("umbracoLanguage");
                    Rename.Table("umbracoLanguage_TEMP").To("umbracoLanguage");

                    //add the pk
                    Create.PrimaryKey("PK_language").OnTable("umbracoLanguage").Column("id");
                }

                var dbIndexes = SqlSyntax.GetDefinedIndexes(Context.Database)
                                .Select(x => new DbIndexDefinition
                {
                    TableName  = x.Item1,
                    IndexName  = x.Item2,
                    ColumnName = x.Item3,
                    IsUnique   = x.Item4
                }).ToArray();

                //make sure it doesn't already exist
                if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsDictionary_id")) == false)
                {
                    Create.Index("IX_cmsDictionary_id").OnTable("cmsDictionary")
                    .OnColumn("id").Ascending()
                    .WithOptions().NonClustered()
                    .WithOptions().Unique();
                }

                //now we need to create a foreign key
                Create.ForeignKey("FK_cmsLanguageText_umbracoLanguage_id").FromTable("cmsLanguageText").ForeignColumn("languageId")
                .ToTable("umbracoLanguage").PrimaryColumn("id").OnDeleteOrUpdate(Rule.None);

                Alter.Table("cmsDictionary").AlterColumn("parent").AsGuid().Nullable();

                //set the parent to null if it equals the default dictionary item root id
                foreach (var pk in Context.Database.Query <int>("SELECT pk FROM cmsDictionary WHERE parent NOT IN (SELECT id FROM cmsDictionary)"))
                {
                    Update.Table("cmsDictionary").Set(new { parent = (Guid?)null }).Where(new { pk = pk });
                }

                Create.ForeignKey("FK_cmsDictionary_cmsDictionary_id").FromTable("cmsDictionary").ForeignColumn("parent")
                .ToTable("cmsDictionary").PrimaryColumn("id").OnDeleteOrUpdate(Rule.None);
            }
        }
Exemplo n.º 12
0
        public override void Up()
        {
            //Don't execute anything if there is no 'master' column - this might occur if the db is already upgraded
            var cols = SqlSyntax.GetColumnsInSchema(Context.Database).ToArray();

            if (cols.Any(x => x.ColumnName.InvariantEquals("master") && x.TableName.InvariantEquals("cmsTemplate")) == false)
            {
                return;
            }

            var constraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct().ToArray();

            //update the parentId column for all templates to be correct so it matches the current 'master' template

            //In some old corrupted databases, the information will not be correct in the master column so we need to fix that
            //first by nulling out the master column where the id doesn't actually exist
            Execute.Sql(@"UPDATE cmsTemplate SET master = NULL WHERE " +
                        SqlSyntax.GetQuotedColumnName("master") + @" IS NOT NULL AND " +
                        SqlSyntax.GetQuotedColumnName("master") + @" NOT IN (" +
                        //Stupid MySQL... needs this stupid syntax because it can do an update with a sub query of itself,
                        // yet it can do one with a sub sub query
                        // ... this will work in all dbs too
                        @"SELECT nodeId FROM (SELECT * FROM cmsTemplate a) b)");

            //Now we can bulk update the parentId column

            //NOTE: This single statement should be used but stupid SQLCE doesn't support Update with a FROM !!
            // so now we have to do this by individual rows :(
            //Execute.Sql(@"UPDATE umbracoNode
            //SET parentID = COALESCE(t2." + SqlSyntax.GetQuotedColumnName("master")  +  @", -1)
            //FROM umbracoNode t1
            //INNER JOIN cmsTemplate t2
            //ON t1.id = t2.nodeId");
            Execute.Code(database =>
            {
                var templateData = database.Fetch <dynamic>("SELECT * FROM cmsTemplate");

                foreach (var template in templateData)
                {
                    var sql = "SET parentID=@parentId WHERE id=@nodeId";

                    LogHelper.Info <MigrateAndRemoveTemplateMasterColumn>("Executing sql statement: UPDATE umbracoNode " + sql);

                    database.Update <NodeDto>(sql,
                                              new { parentId = template.master ?? -1, nodeId = template.nodeId });
                }

                return(string.Empty);
            });

            //Now we can update the path, but this needs to be done in a delegate callback so that the query runs after the updates just completed
            Execute.Code(database =>
            {
                //NOTE: we are using dynamic because we need to get the data in a column that no longer exists in the schema
                var templates = database.Fetch <dynamic>(new Sql().Select("*").From <TemplateDto>());
                foreach (var template in templates)
                {
                    var sql = string.Format(SqlSyntax.UpdateData,
                                            SqlSyntax.GetQuotedTableName("umbracoNode"),
                                            "path=@buildPath",
                                            "id=@nodeId");

                    LogHelper.Info <MigrateAndRemoveTemplateMasterColumn>("Executing sql statement: " + sql);

                    //now build the correct path for the template
                    database.Execute(sql, new
                    {
                        buildPath = BuildPath(template, templates),
                        nodeId    = template.nodeId
                    });
                }

                return(string.Empty);
            });



            //now remove the master column and key
            if (this.Context.CurrentDatabaseProvider == DatabaseProviders.MySql)
            {
                //Because MySQL doesn't name keys with what you want, we need to query for the one that is associated
                // this is required for this specific case because there are 2 foreign keys on the cmsTemplate table
                var fkName = constraints.FirstOrDefault(x => x.Item1.InvariantEquals("cmsTemplate") && x.Item2.InvariantEquals("master"));
                if (fkName != null)
                {
                    Delete.ForeignKey(fkName.Item3).OnTable("cmsTemplate");
                }
            }
            else
            {
                if (constraints.Any(x => x.Item1.InvariantEquals("cmsTemplate") && x.Item3.InvariantEquals("FK_cmsTemplate_cmsTemplate")))
                {
                    Delete.ForeignKey("FK_cmsTemplate_cmsTemplate").OnTable("cmsTemplate");
                }

                //TODO: Hopefully it's not named something else silly in some crazy old versions
            }


            var dbIndexes = SqlSyntax.GetDefinedIndexes(Context.Database)
                            .Select(x => new DbIndexDefinition()
            {
                TableName  = x.Item1,
                IndexName  = x.Item2,
                ColumnName = x.Item3,
                IsUnique   = x.Item4
            }).ToArray();

            //in some databases there's an index (IX_Master) on the master column which needs to be dropped first
            var foundIndex = dbIndexes.FirstOrDefault(x => x.TableName.InvariantEquals("cmsTemplate") && x.ColumnName.InvariantEquals("master"));

            if (foundIndex != null)
            {
                Delete.Index(foundIndex.IndexName).OnTable("cmsTemplate");
            }

            if (cols.Any(x => x.ColumnName.InvariantEquals("master") && x.TableName.InvariantEquals("cmsTemplate")))
            {
                Delete.Column("master").FromTable("cmsTemplate");
            }
        }
Exemplo n.º 13
0
        public override void Up()
        {
            //now that the controlId column is renamed and now a string we need to convert
            if (Context == null || Context.Database == null)
            {
                return;
            }

            //var cpt = SqlSyntaxContext.SqlSyntaxProvider.GetConstraintsPerTable(Context.Database);
            //var di = SqlSyntaxContext.SqlSyntaxProvider.GetDefinedIndexes(Context.Database);

            if (Context.CurrentDatabaseProvider != DatabaseProviders.SqlServer)
            {
                Delete.DefaultConstraint().OnTable("cmsMacroProperty").OnColumn("macroPropertyHidden");
            }
            else
            {
                //If we are on SQLServer, we need to delete default constraints by name, older versions of umbraco did not name these default constraints
                // consistently so we need to look up the constraint name to delete, this only pertains to SQL Server and this issue:
                // http://issues.umbraco.org/issue/U4-4133
                var sqlServerSyntaxProvider = new SqlServerSyntaxProvider();
                var defaultConstraints      = sqlServerSyntaxProvider.GetDefaultConstraintsPerColumn(Context.Database).Distinct();

                //lookup the constraint we want to delete, normally would be called "DF_cmsMacroProperty_macroPropertyHidden" but
                // we cannot be sure with really old versions
                var constraint = defaultConstraints
                                 .SingleOrDefault(x => x.Item1 == "cmsMacroProperty" && x.Item2 == "macroPropertyHidden");
                if (constraint != null)
                {
                    Execute.Sql(string.Format("ALTER TABLE [{0}] DROP CONSTRAINT [{1}]", "cmsMacroProperty", constraint.Item3));
                }
            }

            Delete.Column("macroPropertyHidden").FromTable("cmsMacroProperty");

            if (Context.CurrentDatabaseProvider == DatabaseProviders.MySql)
            {
                Delete.ForeignKey().FromTable("cmsMacroProperty").ForeignColumn("macroPropertyType").ToTable("cmsMacroPropertyType").PrimaryColumn("id");
            }
            else
            {
                //Before we try to delete this constraint, we'll see if it exists first, some older schemas never had it and some older schema's had this named
                // differently than the default.

                var keyConstraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct();
                var constraint     = keyConstraints
                                     .SingleOrDefault(x => x.Item1 == "cmsMacroProperty" && x.Item2 == "macroPropertyType" && x.Item3.InvariantStartsWith("PK_") == false);
                if (constraint != null)
                {
                    Delete.ForeignKey(constraint.Item3).OnTable("cmsMacroProperty");
                }
            }

            Alter.Table("cmsMacroProperty").AddColumn("editorAlias").AsString(255).NotNullable().WithDefaultValue("");

            //we need to get the data and create the migration scripts before we change the actual schema bits below!
            var list = Context.Database.Fetch <dynamic>("SELECT * FROM cmsMacroPropertyType");

            foreach (var item in list)
            {
                var alias = item.macroPropertyTypeAlias;
                //check if there's a map created
                var newAlias = (string)LegacyParameterEditorAliasConverter.GetNewAliasFromLegacyAlias(alias);
                if (newAlias.IsNullOrWhiteSpace() == false)
                {
                    alias = newAlias;
                }

                //update the table with the alias, the current macroPropertyType will contain the original id
                Update.Table("cmsMacroProperty").Set(new { editorAlias = alias }).Where(new { macroPropertyType = item.id });
            }

            //drop the column now
            Delete.Column("macroPropertyType").FromTable("cmsMacroProperty");

            //drop the default constraint
            Delete.DefaultConstraint().OnTable("cmsMacroProperty").OnColumn("editorAlias");
        }
Exemplo n.º 14
0
        public override void Up()
        {
            //Some very old schemas don't have an index on the cmsContent.nodeId column, I'm not actually sure when it was added but
            // it is absolutely required to exist in order to have it as a foreign key reference, so we'll need to check it's existence
            // this came to light from this issue: http://issues.umbraco.org/issue/U4-4133
            var dbIndexes = SqlSyntax.GetDefinedIndexes(Context.Database)
                            .Select(x => new DbIndexDefinition(x)).ToArray();

            if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsContent")) == false)
            {
                Create.Index("IX_cmsContent").OnTable("cmsContent").OnColumn("nodeId").Ascending().WithOptions().Unique();
            }

            if (Context.CurrentDatabaseProvider == DatabaseProviders.SqlServer ||
                Context.CurrentDatabaseProvider == DatabaseProviders.SqlServerCE)
            {
                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("cmsPreviewXml") && x.Item3.InvariantStartsWith("PK_")) == 0)
                {
                    Create.PrimaryKey("PK_cmsContentPreviewXml")
                    .OnTable("cmsPreviewXml")
                    .Columns(new[] { "nodeId", "versionId" });
                }

                if (constraints.Count(x => x.Item1.InvariantEquals("cmsTags") && x.Item3.InvariantStartsWith("PK_")) == 0)
                {
                    Create.PrimaryKey("PK_cmsTags")
                    .OnTable("cmsTags")
                    .Columns(new[] { "id" });
                }

                if (constraints.Count(x => x.Item1.InvariantEquals("cmsStylesheetProperty") && x.Item3.InvariantStartsWith("PK_")) == 0)
                {
                    Create.PrimaryKey("PK_cmsStylesheetProperty")
                    .OnTable("cmsStylesheetProperty")
                    .Columns(new[] { "nodeId" });
                }

                if (constraints.Count(x => x.Item1.InvariantEquals("cmsStylesheet") && x.Item3.InvariantStartsWith("PK_")) == 0)
                {
                    Create.PrimaryKey("PK_cmsStylesheet")
                    .OnTable("cmsStylesheet")
                    .Columns(new[] { "nodeId" });

                    Create.ForeignKey("FK_cmsStylesheet_umbracoNode_id").FromTable("cmsStylesheet").ForeignColumn("nodeId")
                    .ToTable("umbracoNode").PrimaryColumn("id").OnDeleteOrUpdate(Rule.None);
                }

                if (constraints.Count(x => x.Item1.InvariantEquals("cmsMember") && x.Item3.InvariantStartsWith("PK_")) == 0)
                {
                    Create.PrimaryKey("PK_cmsMember")
                    .OnTable("cmsMember")
                    .Columns(new[] { "nodeId" });

                    Create.ForeignKey("FK_cmsMember_umbracoNode_id").FromTable("cmsMember").ForeignColumn("nodeId")
                    .ToTable("umbracoNode").PrimaryColumn("id").OnDeleteOrUpdate(Rule.None);

                    Create.ForeignKey("FK_cmsMember_cmsContent_nodeId").FromTable("cmsMember").ForeignColumn("nodeId")
                    .ToTable("cmsContent").PrimaryColumn("nodeId").OnDeleteOrUpdate(Rule.None);
                }
            }
        }