Feature request: manual dashboard execution

[jamiehowarth0]

I've got a very old site I'm upgrading with content dating back to 2011. The DB is well in excess of 4GB, with over 12m rows in cmsPropertyData & over 8,000 content nodes. Running publish on the root node with children results in a SQL Server deadlock.

I'd like to be able to run Unversion from a single dashboard screen with the same options, instead of relying on the Publish event, in order to trim the database down as a single operation.

I've forked & will submit a PR in due course.

[leekelleher]

Hi @benjaminhowarth1. Take a look at the F.A.L.M. Housekeeping package - it has a "Versions Manager" feature that may be of use to you? https://our.umbraco.com/packages/backoffice-extensions/falm-housekeeping/

[jamiehowarth0]

Hey @leekelleher, sadly all that the FALM package offers is deleting all versions of a single node.

I've got over 8,000 nodes to do this on, hence why unVersion seems to be a better way of approaching this - I only want the last 5 versions of every node in the site, I don't need anything prior to that.

[leekelleher]

Ah, doh, shame.

[jamiehowarth0]

OK, so I'm getting a dashboard together, but I'm also looking at some refactors, specifically, SQL command re-use, especially when running large un-version operations.

Firstly, the four SQL commands for removing historical versions can be bundled into a single operation, rather than multiple operations. Secondly, the way in which the connections are created doesn't use Umbraco's built-in database factories, which would enable wider support than just SQL Server.

Replacing this:

var sqlStrings = new List<string> {
                        string.Format(@"
                                    DELETE
                                    FROM    cmsPreviewXml
                                    WHERE   nodeId = {0} AND versionId NOT IN ({1})",
                        content.Id,
                        versionsToKeepString),
                        string.Format(@"
                                    DELETE
                                    FROM    cmsPropertyData
                                    WHERE   contentNodeId = {0} AND versionId  NOT IN ({1})",
                        content.Id,
                        versionsToKeepString),
                        string.Format(@"
                                    DELETE
                                    FROM    cmsContentVersion
                                    WHERE   contentId = {0} AND versionId  NOT IN ({1})",
                        content.Id,
                        versionsToKeepString),
                        string.Format(@"
                                    DELETE
                                    FROM    cmsDocument 
                                    WHERE   nodeId = {0} AND versionId  NOT IN ({1})",
                        content.Id,
                        versionsToKeepString)
                    };

                    foreach (var sqlString in sqlStrings)
                    {
                        ExecuteSql(sqlString, conn);
                    }

With this:

var sqlString = string.Format(
                    string.Concat(
                        @"DELETE FROM cmsPreviewXml WHERE nodeId = {0} AND versionId NOT IN ({1});", Environment.NewLine,
                        @"DELETE FROM cmsPropertyData WHERE contentNodeId = {0} AND versionId NOT IN ({1});", Environment.NewLine,
                        @"DELETE FROM cmsContentVersion WHERE contentId = {0} AND versionId NOT IN ({1});", Environment.NewLine,
                        @"DELETE FROM cmsDocument WHERE nodeId = {0} AND versionId NOT IN ({1});"),
                    content.Id,
                    versionsToKeepString
                );

Would turn each un-version operation from four SQL commands into one, thus optimising command execution, as a start.

I'll put together a couple of separate PRs for this.

[mattbrailsford]

Just be aware, I believe SQL CE doesn't support multiple statements in a single query, which I think is why they are all separate for maximum compatibility. Guess this leads to the question of what should this support?

Matt

On Wed, 29 Jan 2020 at 18:52, Benjamin Howarth notifications@github.com wrote:

OK, so I'm getting a dashboard together, but I'm also looking at some refactors, specifically, SQL command re-use, especially when running large un-version operations.

Firstly, the four SQL commands for removing historical versions can be bundled into a single operation, rather than multiple operations. Secondly, the way in which the connections are created doesn't use Umbraco's built-in database factories, which would enable wider support than just SQL Server.

Replacing this: `var sqlStrings = new List { string.Format(@" DELETE FROM cmsPreviewXml WHERE nodeId = {0} AND versionId NOT IN ({1})", content.Id, versionsToKeepString),

                string.Format(@"
                            DELETE
                            FROM  cmsPropertyData
                            WHERE contentNodeId = {0} AND versionId  NOT IN ({1})",
                content.Id,
                versionsToKeepString),

                string.Format(@"
                            DELETE
                            FROM  cmsContentVersion
                            WHERE contentId = {0} AND versionId  NOT IN ({1})",
                content.Id,
                versionsToKeepString),

                string.Format(@"
                            DELETE
                            FROM  cmsDocument
                            WHERE nodeId = {0} AND versionId  NOT IN ({1})",
                content.Id,
                versionsToKeepString)
            };

            foreach (var sqlString in sqlStrings)
            {
                ExecuteSql(sqlString, conn);
            }`

With this:

var sqlString = string.Format( string.Concat( @"DELETE FROM cmsPreviewXml WHERE nodeId = {0} AND versionId NOT IN ({1});", Environment.NewLine, @"DELETE FROM cmsPropertyData WHERE contentNodeId = {0} AND versionId NOT IN ({1});", Environment.NewLine, @"DELETE FROM cmsContentVersion WHERE contentId = {0} AND versionId NOT IN ({1});", Environment.NewLine, @"DELETE FROM cmsDocument WHERE nodeId = {0} AND versionId NOT IN ({1});"), content.Id, versionsToKeepString );

Would turn each un-version operation from four SQL commands into one, thus optimising command execution, as a start.

I'll put together a couple of separate PRs for this.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/umco/umbraco-unversion/issues/22?email_source=notifications&email_token=AAEAXSM362QB6MO32RVKAEDRAHF7LA5CNFSM4KIUH272YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEKIKJKY#issuecomment-579904683, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEAXSN7VPD72RFKUKCEKGLRAHF7LANCNFSM4KIUH27Q .

[jamiehowarth0]

@mattbrailsford apparently the trick is to split the lines with GO and it'll run each one separately. https://stackoverflow.com/questions/7703256/sql-server-compact-4-0-chokes-on-insert-statements

[jamiehowarth0]

@mattbrailsford I've updated my PR to use PetaPoco instead of manual SqlCommand. This also has the added advantage of DatabaseContext having an enum stating what DB type is in use - so I've taken the commands and run them one by one for SQL Compact (you were right about the multiple statements not being supported, there's conflicting advice about this as I've found), or batched them up as a single command for any other DB type. This makes things a little neater from a performance perspective.

[skttl]

This is a great idea, and something I would love to see.