If you need to do it on an ongoing basis, you could also try my UnVersion package which, for configured pages, can automatically clean up versions where you don't need them, just keepin the latest.
I know I'm writing this 11.5 years since the original post, but in case anyone else is experiencing the troubles I'm having trying to migrate 7.15.7 SQLCE database to Umbraco 8+ and got all excited when they saw this post.
FYI it doesn't work.
That is great to know Matt, I just wanted to help people avoid disappointment who are having intense and frustrating headache of trying to migrate legacy SQLCE data from 7.15+.
Absolute nightmare (before Christmas :D)
The problem will be for Unversion and scripts like this is that they work directly against the Umbraco database - and most people will be using SQL Server proper - and have written them with SQL Server syntax in mind, and it's subtle different for SQL CE :-(
I have an Unversioning command line thing I use, which calls this UnversioningService:
the syntax of this will undoubtedly be SQL Server too I'm afraid...
... but you can kind of see the order in which you delete things... and although there are 11 SQL statements, a lot of them are the same 'delete' format... so maybe they could be converted to SQL CE format...
As a last resort, I wonder if it would be any easier to initially convert the db from SQL CE to a regular SQL database using SQL EXPRESS? It could at least mitigate any SQL specific syntax issues that may be present. 🤔
:D thanks Marc. Yesterday I started adapting a bit of script that I borrowed from here https://our.umbraco.com/forum/core/general/74365-remove-audit-trail-and-version-history. needs reworking as CompactView scripts don't work quite the same as for SQL, it's like the caveman version much more rigid and less forgiving (for instance you can't create # tables so I have to find the required id's and hard codes them into the query)
But at least I do know what has been causing the issues now (thank you!), but by the way the cmsPropertyValues table you mentioned doesn't exist in .sdf I think it's the cmsPropertyData table dataNText field that needs to be changed. It's not as simple as going through the CMS and saving or republishing every single page - but just to be sure I did do that :) - the problem data had been delivered by a custom macro set - which have seen various upgrades and 'improvements' over the last few years!
Anyway, it's getting kind of hilarious now, I spent most of the day yesterday having to manually change html into Json, until I realised that unless I find a way of getting rid of previous versions it will take me until this time next year to complete as there are 1000's of rows that need amending and in CE using CompactView, these need to be tackled one at a time.
Thanks Matt, yes, I want to convert this to SQL but have to wait until after Christmas now to identify a server for it. In the meantime I want to do it in the SQLCE because I will still need to know specifically where and why the issues are happening (and prep some scripts). I only just realised yesterday that I really need to get rid of previous versions prior to cleaning up syntax.
Yes, Unversioning and emptying the recycle bins are a must first step before migrating (as you are finding out :-( )
sorry yes, cmsPropertyData stores the values, I was misremembering
I was wondering though if you have a ton of existing cmsPropertyData values to update, and the transformation from the HTML into JSON for each evolution of the macro follows some sort of pattern, but it's hard to match and update that pattern using a SQL Statement, then maybe a console app connected to the database would allow you to manipulate the values more easily in code?
eg you could create a Select statement to retrieve all the cmsPropertyData row that had the particular pattern, using PetaPoco, and then loop through each one - and transform the value, eg using regexes or HTML agility, and then console.writeline an Update Statement with the transformed value... you'd end up with a list of update statements that would correct the data, that could be run against the database but... which could also be rerun at a later date if you have to repeat the migration steps...
but it's a trade-off with how many actually need updating!
All good information Marc, great to have options. It will be easy enough to use a script to replace plain text and straight html with json but I wonder, do you know if when I have a field of data that starts with looks like: MACRO macroAlias="ContentBSButton" text="Business Plan" contentLink="" mediaLink="umb://media/58697eb9d00943108cdcfcb34324fc2e" urlLink="" imgClass="" gaLabel="" gaValue="" />
MACRO macroAlias="ContentBSButton" text="Business Plan - Presentation" contentLink="" mediaLink="umb://media/d3733bd8ce944568b2e253ebc4aa3789" urlLink="" imgClass="" gaLabel="" gaValue="" />
[etc text info
I can actually get rid of the macro declaration text (as this doesn't convert to json using a script) and just repopulate the actual html portion of the data as json.
If I can clear out the macro declarations first then it will be a lot easier to script the html and/ or plain text to json conversion but I've not been sure if it's necessary to keep that declaration in place yet. - Or indeed if I leave it in there, the migration process will continue to throw errors relating to unexpected data in grid - if you follow what I mean.
(EDITED: Sorry the actual declaration script (Open bracket+ question mark doesn't show here above)
Are these macros all in a rich text editor that has been converted into a grid property? and then content still exists that is in the old format and the migrator is expecting it to be in Grid format, but instead, it's the old rich text editor HTML and it's the conversion of the HTML into JSON format that is breaking?
gosh long question, not sure it made sense!
How many pages have data stored in this rich text editor format with Macros? and how many versions? eg you might find it's 1 page, but 100s of versions, and as you unversion data it might solve these problems anyway...
If there are tons, and you can't unversion them, and you need to change one html format into json grid blob, and there are tons, then if you were to use a console app, or temporary code in a SurfaceController... and work directly against the db using PetaPoco, pull back the values for each db entry that you need to transform, loop through them all and then I think you could use the MacroTagParser:
It's a really old part of Umbraco = but it's written to 'find Macros' in rich text area html... then with those references found, you could 'remove' them, and convert the remaining html into the json format?
then update the database, or instead write out an Update statement, so you can check the updates look ok before running them against the db.
All depends on how many you've got to transform I think
Happy New Year!
Apologies for the tardy reply (Christmas Hols), but yes that is exactly right! And there is a huge amount of history that I can't use the Umbraco clean up tools on a Compact to sort and this is a big problem.
So I've decided to ditch this for now and just convert to SQLDB then tackle the upgrade when I've got that all hooked up and working, this will help ameliorate the switch from dev to live as well, as the site will be in use until the last possible moment prior to upgrading, so it should make life so much easier! Now our systems guy is back from his hols I can get on with that, so am feeling much less insane now!
Thanks again for your help .... very much appreciated!
Thats right. So the way you have it setup, any documents of type RunwayTextpage and Section, when published, will auto clear out their version history. You can also use the rootXpath if you want to limit this to a certain area of the site. Any standard Umbraco XPath should work.
delete versions?!
Is there anyway that I can delete all the previous versions of a document and keep the last one ?!!
Any help much appreciated
Thank
Ali
Ali,
Take a look at the excellent client tools http://clienttools4umbraco.codeplex.com/
Regards
Ismaiol
If you need to do it on an ongoing basis, you could also try my UnVersion package which, for configured pages, can automatically clean up versions where you don't need them, just keepin the latest.
http://our.umbraco.org/projects/website-utilities/unversion
Matt
I know I'm writing this 11.5 years since the original post, but in case anyone else is experiencing the troubles I'm having trying to migrate 7.15.7 SQLCE database to Umbraco 8+ and got all excited when they saw this post. FYI it doesn't work.
Hey Karen,
For anyone wanting this functionality today (v9+) this has been baked into the core now with "History Cleanups"
Matt
That is great to know Matt, I just wanted to help people avoid disappointment who are having intense and frustrating headache of trying to migrate legacy SQLCE data from 7.15+. Absolute nightmare (before Christmas :D)
Hi Karen
Still feeling your pain!
The problem will be for Unversion and scripts like this is that they work directly against the Umbraco database - and most people will be using SQL Server proper - and have written them with SQL Server syntax in mind, and it's subtle different for SQL CE :-(
I have an Unversioning command line thing I use, which calls this UnversioningService:
https://gist.github.com/marcemarc/87c605dca4ae4d6ccb79d41bda102c68
the syntax of this will undoubtedly be SQL Server too I'm afraid...
... but you can kind of see the order in which you delete things... and although there are 11 SQL statements, a lot of them are the same 'delete' format... so maybe they could be converted to SQL CE format...
just sharing in hope for you really
regards
Marc
As a last resort, I wonder if it would be any easier to initially convert the db from SQL CE to a regular SQL database using SQL EXPRESS? It could at least mitigate any SQL specific syntax issues that may be present. 🤔
:D thanks Marc. Yesterday I started adapting a bit of script that I borrowed from here https://our.umbraco.com/forum/core/general/74365-remove-audit-trail-and-version-history. needs reworking as CompactView scripts don't work quite the same as for SQL, it's like the caveman version much more rigid and less forgiving (for instance you can't create # tables so I have to find the required id's and hard codes them into the query) But at least I do know what has been causing the issues now (thank you!), but by the way the cmsPropertyValues table you mentioned doesn't exist in .sdf I think it's the cmsPropertyData table dataNText field that needs to be changed. It's not as simple as going through the CMS and saving or republishing every single page - but just to be sure I did do that :) - the problem data had been delivered by a custom macro set - which have seen various upgrades and 'improvements' over the last few years! Anyway, it's getting kind of hilarious now, I spent most of the day yesterday having to manually change html into Json, until I realised that unless I find a way of getting rid of previous versions it will take me until this time next year to complete as there are 1000's of rows that need amending and in CE using CompactView, these need to be tackled one at a time.
Thanks Matt, yes, I want to convert this to SQL but have to wait until after Christmas now to identify a server for it. In the meantime I want to do it in the SQLCE because I will still need to know specifically where and why the issues are happening (and prep some scripts). I only just realised yesterday that I really need to get rid of previous versions prior to cleaning up syntax.
Hi Karen
Yes, Unversioning and emptying the recycle bins are a must first step before migrating (as you are finding out :-( )
sorry yes, cmsPropertyData stores the values, I was misremembering
I was wondering though if you have a ton of existing cmsPropertyData values to update, and the transformation from the HTML into JSON for each evolution of the macro follows some sort of pattern, but it's hard to match and update that pattern using a SQL Statement, then maybe a console app connected to the database would allow you to manipulate the values more easily in code?
eg you could create a Select statement to retrieve all the cmsPropertyData row that had the particular pattern, using PetaPoco, and then loop through each one - and transform the value, eg using regexes or HTML agility, and then console.writeline an Update Statement with the transformed value... you'd end up with a list of update statements that would correct the data, that could be run against the database but... which could also be rerun at a later date if you have to repeat the migration steps...
but it's a trade-off with how many actually need updating!
regards
marc
All good information Marc, great to have options. It will be easy enough to use a script to replace plain text and straight html with json but I wonder, do you know if when I have a field of data that starts with looks like: MACRO macroAlias="ContentBSButton" text="Business Plan" contentLink="" mediaLink="umb://media/58697eb9d00943108cdcfcb34324fc2e" urlLink="" imgClass="" gaLabel="" gaValue="" /> MACRO macroAlias="ContentBSButton" text="Business Plan - Presentation" contentLink="" mediaLink="umb://media/d3733bd8ce944568b2e253ebc4aa3789" urlLink="" imgClass="" gaLabel="" gaValue="" />
[etc text info
I can actually get rid of the macro declaration text (as this doesn't convert to json using a script) and just repopulate the actual html portion of the data as json.
If I can clear out the macro declarations first then it will be a lot easier to script the html and/ or plain text to json conversion but I've not been sure if it's necessary to keep that declaration in place yet. - Or indeed if I leave it in there, the migration process will continue to throw errors relating to unexpected data in grid - if you follow what I mean. (EDITED: Sorry the actual declaration script (Open bracket+ question mark doesn't show here above)
Hi Karen
Are these macros all in a rich text editor that has been converted into a grid property? and then content still exists that is in the old format and the migrator is expecting it to be in Grid format, but instead, it's the old rich text editor HTML and it's the conversion of the HTML into JSON format that is breaking?
gosh long question, not sure it made sense!
How many pages have data stored in this rich text editor format with Macros? and how many versions? eg you might find it's 1 page, but 100s of versions, and as you unversion data it might solve these problems anyway...
If there are tons, and you can't unversion them, and you need to change one html format into json grid blob, and there are tons, then if you were to use a console app, or temporary code in a SurfaceController... and work directly against the db using PetaPoco, pull back the values for each db entry that you need to transform, loop through them all and then I think you could use the MacroTagParser:
https://github.com/umbraco/Umbraco-CMS/blob/5bfab13dc5a268714aad2426a2b68ab5561a6407/src/Umbraco.Infrastructure/Macros/MacroTagParser.cs#L148
It's a really old part of Umbraco = but it's written to 'find Macros' in rich text area html... then with those references found, you could 'remove' them, and convert the remaining html into the json format?
then update the database, or instead write out an Update statement, so you can check the updates look ok before running them against the db.
All depends on how many you've got to transform I think
regards
marc
Hi Marc,
Happy New Year! Apologies for the tardy reply (Christmas Hols), but yes that is exactly right! And there is a huge amount of history that I can't use the Umbraco clean up tools on a Compact to sort and this is a big problem. So I've decided to ditch this for now and just convert to SQLDB then tackle the upgrade when I've got that all hooked up and working, this will help ameliorate the switch from dev to live as well, as the site will be in use until the last possible moment prior to upgrading, so it should make life so much easier! Now our systems guy is back from his hols I can get on with that, so am feeling much less insane now! Thanks again for your help .... very much appreciated!
I'll post an outcome at the appropriate time.
Karen
Hi Karen
It's all I've been thinking about over Christmas ... :-P
I think switching away from compact should make the task less herculean but probably still will involve some jeopardy!
good luck and do let us know so we can all get some closure...
regards
Marc
Hi Matt, Thanks for you quick reply.
I have installed your package. and change the unversion.config to this
<?xml version="1.0"?>
<unVersionConfig>
<add docTypeAlias="RunwayTextpage" />
<add docTypeAlias="Section" />
</unVersionConfig>
how can I trigger it ? It didnt delete the previous versions of Sections? !
Just found out, I need to publish the document that it will delete all the previous versions
Cheer Matt
Hey Ali,
Thats right. So the way you have it setup, any documents of type RunwayTextpage and Section, when published, will auto clear out their version history. You can also use the rootXpath if you want to limit this to a certain area of the site. Any standard Umbraco XPath should work.
Many thanks
Matt
Thanks mate,
Just for curiosity, is there any way that we can set it up that keeps only last 10 versions?
Ali
Hey Ali,
Not right now, but it's on my list, along with a couple more updates. As soon as I get the time, I'll give it a bit of an update.
Matt
Great
that is a wicked package mate.
Cheers
Ali
is working on a reply...