Cleaning all unnecessary versions via SQL

After my first small paternity leave from about two weeks I got a direct tweed asking for a sql script which is cleaning all unnecessary versions from all nodes at once. Unlike to the SQL Script posted before which deletes all versions from a given nodeId (usefull if you have a massive growing database with thousand of versions for one node) this script will delete all unnecessary versions from all nodes at once. As usual this is a works on my machine post. Please make a backup from your database and report misbehaviour.

How it works

At first the script will create a temporary table and inserts all nodeId's from nodes which are documents (remember, not only documents have versions, also members and medias can have versions) and which are published and not in the recycle bin.

-- Create a temporary table for all documents which are published and not in the recycle bin
CREATE TABLE #Nodes (id int)
-- Delete all rows if the table exists before
TRUNCATE TABLE #Nodes

-- Insert all nodeIds from all documents which are published and not in the recycle bin
INSERT INTO #Nodes
    SELECT N.id
    FROM umbracoNode N
        INNER JOIN cmsDocument D ON N.ID = D.NodeId
    WHERE nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972'
        AND [path] NOT LIKE '%-20%'
        AND D.Published = 1

After that it creates another temporary table and inserts all versions from the nodeIds in the first temporary table which not are published an which not are the newest ones.

-- Create a temporary table for all versionId's to delete
CREATE TABLE #Versions (id UniqueIdentifier)
-- Delete all rows if it exists before
TRUNCATE TABLE #Versions

-- Insert all versionId's from all nodeIds in the #Nodes table
-- and where published is set to false and newest is set to false
INSERT INTO #Versions
    SELECT versionId
    FROM cmsDocument
    WHERE nodeId IN (SELECT id FROM #Nodes)
        AND published = 0 AND newest = 0

Now it will delete all rows from the cmsPropertyData, cmsContentVersion and cmsDocument where the versionId's are in the second temporary table.

-- DELETE all versions from cmsPropertyData, cmsContentVersion, cmsDocument
-- from the nodes which are published and which are not in the recycle bin
-- and which are not published and which are not the newest
DELETE FROM cmsPropertyData WHERE VersionId IN (SELECT id FROM #Versions)
DELETE FROM cmsContentVersion WHERE VersionId IN (SELECT id FROM #Versions)
DELETE FROM cmsDocument WHERE VersionId IN (SELECT id FROM #Versions)

Unlike the script in the post Cleaning versions in the umbraco db for given nodes via SQL this script doesn't delete the indexes. If the script runs to long you are free to add these step to this script. On the other hand this script can be used as a regularly cleanup.

Download the complete script here and give me your feedback.

WLW Plugin v1 Beta 2 with Media Links

As I wrote the last blog post where I introduced the WLW Plugin Insert Umbraco Links I noticed that inserting links to umbraco media would also be a good idea. Thought and did it. I extended the existing plugin to also load media items from umbraco. Every media which has a generic property called umbracoFile  can be inserted now. In the standard installation there are two media types which have this generic property build in: the File and the Image.

You can download the new bits here.

Give me your feedback.

WLW Plugin Insert Umbraco Links

For my blog(s) I am using Windows Live Writer and some Plugins like the Pre Code Snippet. Until now inserting links to other entries in my blog was circular. I had to open the umbraco backend, get the id of the node, goto WLW, add a link, insert {localLink:id}. Save.

With the API from WLW it is possible to implement Plugins for WLW. So I did and created my first WLW Plugin. After some small problems with the Windows Live Writer API and the binding context in a Winform I finished it. Now it is much more comfortable to insert Lokal Links.

Installation

First you have to download the files from my blog. (btw, another plugin idea: inserting media links from umbraco...)

In the downloaded zip-file there are two files included:

• thoehler.com.wlw.InsertLinkFromUmbraco.dll - which is the dll you have to put in Plugins folder from your WLW installation.
• th.InsertLinks_v1_Beta_1.zip - which is the package that installs the webservice in your umbraco installation.

Usage

After starting the WLW you get a new entry in the insert menu and in the insert box of your sidebar.

Clicking on the new entry will launch the plugin dialogue. On the first usage the dialogue is empty and you have to edit the settings by clicking on the Settings button (you also can launch the settings dialogue via Extras/Options/Plugins). Here in this dialogue you can add new umbraco instances where you have installed the webservice. You can add several installations which you can easily switch in the Insert Link dialogue. Don't forget to save your settings. The settings are saved from WLW and retrieved back on start.

Now you can go back to the Insert Link dialogue where you select the umbraco instance from which you want to add a link and click down in the tree to your node you want to add. For performance issues I am only loading nodes from one level at once. So if you click on a node it automatically loads the given childnodes (if the node has childnodes). Now click insert Link, done...

Security

The user is is authenticated with the given username and password. The username is mapped to the umbraco user and the nodes that will be retrieved are related to the Start Content setting for the Umbraco User.

Please test this plugin and give me feedback.

Umbraco.de launched

Yesterday started umbraco.de. Congratulations to Thorsten Hoffmann who made the site. It looks like umbraco.org, it feels like umbraco.org, but it is umbraco.de.Let all Germans start using umbraco.de and forum.umbraco.de to give umbraco a better platform in Germany.

Umbraco v4 released

On Friday Umbraco released his version 4. The biggest step forward in the history of umbraco. As usual I installed v4 release on this blog and it is running smooth. Guys: good job!