Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • wtct 85 posts 128 karma points
    Dec 10, 2010 @ 17:58
    wtct
    0

    Removing old version by SQL query

    Hi!

    When the plugin is installed  there can be exetuted a special query which deletes all old versions. So we don't publish node for removing old versions. Of course we can define how many versions for one document should stay.

    DELETE FROM cmsPropertyData
    WHERE versionId IN (SELECT D.versionId
                        FROM cmsDocument D
                        WHERE updateDate < (SELECT updateDate
                                            FROM (SELECT updateDate, RANK() OVER(ORDER BY updateDate DESC) number
                                                  FROM cmsDocument
                                                  WHERE nodeId = D.nodeID) AS tmp
                                            WHERE tmp.number = 3)
                        )   

    DELETE FROM cmsContentVersion
    WHERE versionId IN (SELECT D.versionId
                        FROM cmsDocument D
                        WHERE updateDate < (SELECT updateDate
                                            FROM (SELECT updateDate, RANK() OVER(ORDER BY updateDate DESC) number
                                                  FROM cmsDocument
                                                  WHERE nodeId = D.nodeID) AS tmp
                                            WHERE tmp.number = 3)
                        )
                                           
    DELETE FROM cmsDocument
    WHERE versionId IN (SELECT D.versionId
                        FROM cmsDocument D
                        WHERE updateDate < (SELECT updateDate
                                            FROM (SELECT updateDate, RANK() OVER(ORDER BY updateDate DESC) number
                                                  FROM cmsDocument
                                                  WHERE nodeId = D.nodeID) AS tmp
                                            WHERE tmp.number = 3)
                        )

    The tmp.number condition determines the number of version to stay in db.

  • Matt Brailsford 2807 posts 14331 karma points MVP 7x c-trib
    Dec 10, 2010 @ 19:49
    Matt Brailsford
    0

    Hi Wctc,

    I'm not entirely sure what you asking / suggesting? Would you mind clarifying?

    If you are just showing how to do it via SQL statement, this is already freely available in the sourcecode which you can find here:

    http://unversion.codeplex.com/SourceControl/changeset/view/3e7948bad71b#ApplicationBase.cs

    Many thanks

    Matt

  • wtct 85 posts 128 karma points
    Dec 10, 2010 @ 19:56
    wtct
    0

    Hi Matt,

    I would like to show how to remove all old content version without publishing a node.

  • Matt Brailsford 2807 posts 14331 karma points MVP 7x c-trib
    Dec 10, 2010 @ 20:05
    Matt Brailsford
    0

    Hi Wtct,

    Ahh, ok, that's cool. You may want to look at adding it to the wiki though, as this forum is mainly for bugs, feedback and suggestions for the UnVersion package itself, so may end up getting a bit lost.

    http://our.umbraco.org/wiki

    I'd also suggest taking a look at the source of UnVersion as there seems to be a couple of items you've missed out that I would recommend adding, mainly cleaning up the cmsPreviewXml table, and ensuring the Published / Newest versions are not deleted (deleting these will cause problems).

    The reason this package was created in the first place was really to ensure all loose ends are cleaned up, and making sure the data structure remains stable, so would hate for somebody to accidentaly break their install.

    Many thanks

    Matt

  • wtct 85 posts 128 karma points
    Dec 10, 2010 @ 20:12
    wtct
    0

    Thanks for this:

    "mainly cleaning up the cmsPreviewXml table, and ensuring the Published / Newest versions are not deleted (deleting these will cause problems)"

    Best regards!

    wtct

  • wtct 85 posts 128 karma points
    Dec 10, 2010 @ 21:46
    wtct
    0

    Hi!

    I prepared new SQL query version :)

    I hope it is good version :) Yes I remember about cmsPreviewXm but it is similary.

    DELETE FROM cmsDocument
    WHERE versionID NOT IN
    (
    SELECT D.versionId
    FROM cmsDocument D
    WHERE D.versionId IN (SELECT versionId
    FROM (SELECT CV.versionId, published, newest, RANK() OVER(ORDER BY CV.versionDate DESC) RowNum
    FROM cmsContentVersion CV JOIN cmsDocument DD ON CV.versionId = DD.versionId
    WHERE DD.nodeId = D.nodeId) AS tmp
    WHERE tmp.RowNum <= 3 OR tmp.published = 1 OR tmp.newest = 1)
    )

    GO

    DELETE FROM cmsContentVersion
    WHERE VersionId IN (SELECT CV.versionId FROM cmsContentVersion CV LEFT JOIN cmsDocument D ON D.versionId = Cv.versionID WHERE D.versionID IS NULL)

    GO

    DELETE FROM cmsPropertyData
    WHERE VersionId IN (SELECT DISTINCT PD.versionId FROM cmsPropertyData PD LEFT JOIN cmsDocument D ON D.versionId = PD.versionID WHERE D.versionID IS NULL)

  • Laurent Lequenne 120 posts 245 karma points
    Sep 13, 2011 @ 10:11
    Laurent Lequenne
    0

    You wish ! Before posting stupid SQL commands test locally !

  • wtct 85 posts 128 karma points
    Sep 13, 2011 @ 10:29
    wtct
    0

    So what is wrong with the second posted query?

  • Laurent Lequenne 120 posts 245 karma points
    Sep 13, 2011 @ 10:33
    Laurent Lequenne
    0

    It delete all media properties.... And had to restore 3 databases as I saw this lately !

  • Rich Green 2246 posts 3981 karma points
    Sep 13, 2011 @ 10:37
    Rich Green
    0

    If you're just looking to remove old versions periodically then F.A.L.M housekeeping can do this amongst other features (clearing and viewing logs etc.)

    http://our.umbraco.org/projects/backoffice-extensions/falm-housekeeping

    Rich

  • Laurent Lequenne 120 posts 245 karma points
    Sep 13, 2011 @ 10:52
    Laurent Lequenne
    0

    FALM Housekeeping (Error message with can something like can not find 0000000000000000 node id) ... Not Working ! Unversion not working... on 4.7 ! And tryed on 3 different implementations... with databases that are growing fast, I have a 2 month old site (40 subsites) where the DB is up to 1 GB 2, and could be reduced to +/- 250 MB

     

     

     

  • wtct 85 posts 128 karma points
    Sep 13, 2011 @ 19:08
    wtct
    0

    I think that you can fix my query because it is very simple.

  • Laurent Lequenne 120 posts 245 karma points
    Sep 20, 2011 @ 11:28
    Laurent Lequenne
    0
    USE [UmbracoDev]
    GO
    /****** Object:  StoredProcedure [dbo].[DeleteHistory]    Script Date: 09/20/2011 11:20:53 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[DeleteHistory]
    AS
    BEGIN
    
    Select VersionID into #tmp FROM cmsDocument
    WHERE versionID NOT IN  (SELECT D.versionId FROM cmsDocument D 
        WHERE D.versionId IN (SELECT versionId FROM (SELECT CV.versionId, published, newest, RANK() OVER(ORDER BY CV.versionDate DESC) RowNum   
                                                           FROM cmsContentVersion CV JOIN cmsDocument DD ON CV.versionId = DD.versionId    
                                                                                                               WHERE DD.nodeId = D.nodeId) AS tmp     
                                       WHERE tmp.RowNum <= 3 OR tmp.published = 1 OR tmp.newest = 1)  )
                                       
    
    
    DELETE FROM cmsContentVersion WHERE VersionId IN (select #tmp.VersionId from #tmp)
    DELETE FROM cmsPropertyData WHERE VersionId IN (select #tmp.VersionId from #tmp)
    DELETE FROM cmsDocument WHERE VersionId IN (select #tmp.VersionId from #tmp)
    END
    

    Voila... Lazy boy... Here some good SQL practice !

    So we are using your first query to save the VersionID's to delete into a temporary table.

    And then we delete from each table the VersionIDs found in the #tmp table.

  • Laurent Lequenne 120 posts 245 karma points
    Sep 20, 2011 @ 11:29
    Laurent Lequenne
    0
    USE [UmbracoDev]
    GO
    /****** Object:  StoredProcedure [dbo].[DeleteHistory]    Script Date: 09/20/2011 11:20:53 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[DeleteHistory]
    AS
    BEGIN
    
    Select VersionID into #tmp FROM cmsDocument
    WHERE versionID NOT IN  (SELECT D.versionId FROM cmsDocument D 
        WHERE D.versionId IN (SELECT versionId FROM (SELECT CV.versionId, published, newest, RANK() OVER(ORDER BY CV.versionDate DESC) RowNum   
                                                           FROM cmsContentVersion CV JOIN cmsDocument DD ON CV.versionId = DD.versionId    
                                                                                                               WHERE DD.nodeId = D.nodeId) AS tmp     
                                       WHERE tmp.RowNum <= 3 OR tmp.published = 1 OR tmp.newest = 1)  )
                                       
    
    
    DELETE FROM cmsContentVersion WHERE VersionId IN (select #tmp.VersionId from #tmp)
    DELETE FROM cmsPropertyData WHERE VersionId IN (select #tmp.VersionId from #tmp)
    DELETE FROM cmsDocument WHERE VersionId IN (select #tmp.VersionId from #tmp)
    END
    
  • Markus Johansson 1661 posts 4735 karma points c-trib
    Mar 06, 2018 @ 15:02
    Markus Johansson
    0

    After running this query I got an error since there where constrains with the "cmsPreviewXml"-table, adding this first solved my issue.

    I've uploaded this as a gist here:

    https://gist.github.com/enkelmedia/b322025eab775344e6c3762a776590a1

    Hope this can help anyone that tries to run the query.

    Cheers!

  • Connie DeCinko 931 posts 1159 karma points
    Nov 05, 2013 @ 18:43
    Connie DeCinko
    1

    I agree, FALM Housekeeping seemed like a great idea but as time passes, it has caused more problems than it fixes.  It times out and then leaves a trail of orphan records in its path!  And it does not appear to support Umbraco 6.x and above.

     

Please Sign in or register to post replies

Write your reply to:

Draft