UnversioningService

UnversioningService.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using log4net;

namespace Moriyama.Unversion.App
{
    public class UnversioningService
    {
        private static readonly ILog Logger = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        private readonly string _connectionString;

        public UnversioningService(string connectionString)
        {
            _connectionString = connectionString;
        }

        private void ProcessItem(int a, String connectionString, int versionsToKeep)
        {
            var connection = new SqlConnection(connectionString);
            connection.Open();

            var cmdText2 = string.Format(@"SELECT			cv.VersionId,
	                                                cv.VersionDate,
	                                                d.published,
	                                                d.newest
                                FROM				cmsContentVersion cv
                                LEFT OUTER JOIN		cmsDocument d ON d.versionId = cv.VersionId
                                WHERE				cv.ContentId = {0}
                                ORDER BY            cv.VersionDate DESC", a);


            Logger.Info("Executing: " + cmdText2);
            var cmd2 = new SqlCommand(cmdText2, connection);
            var reader2 = cmd2.ExecuteReader();
            Logger.Info("Done executing");

            var versions2 = new List<string>();
            var readerIndex = 0;

            while (reader2.Read())
            {
                var versionid = Convert.ToString(reader2["VersionId"]);

                var published = false;
                try
                {
                    published = (bool)reader2["published"];
                }
                catch (Exception ex)
                {
                    Logger.Warn(ex.Message, ex);
                }

                var newest = false;

                try
                {
                    newest = (bool)reader2["newest"];
                }
                catch (Exception ex)
                {
                    Logger.Warn(ex.Message, ex);
                }

                readerIndex++;

                if (published || newest || readerIndex <= versionsToKeep)
                    versions2.Add(versionid);

            }

            reader2.Close();
            if (versions2.Count == 0)
                return;

            var versionsToKeepString = String.Join("','", versions2.ToArray());

            var cmdText3 = string.Format(@"
                                DELETE
                                FROM	cmsPreviewXml
                                WHERE	nodeId = {0} AND versionId NOT IN ('{1}')", a, versionsToKeepString);

            var cmd3 = new SqlCommand(cmdText3, connection);

            Logger.Info("Executing: " + cmdText3);
            cmd3.ExecuteNonQuery();
            Logger.Info("Done executing");


            var cmdTxt4 = string.Format(@"
                                DELETE
                                FROM	cmsPropertyData
                                WHERE	contentNodeId = {0} AND versionId  NOT IN ('{1}')", a, versionsToKeepString);

            Logger.Info("Executing: " + cmdTxt4);
            var cmd4 = new SqlCommand(cmdTxt4, connection);
            cmd4.ExecuteNonQuery();
            Logger.Info("Done executing");


            var cmdText5 = string.Format(@"
                                DELETE
                                FROM	cmsContentVersion
                                WHERE	contentId = {0} AND versionId  NOT IN ('{1}')", a, versionsToKeepString);

            Logger.Info("Executing: " + cmdText5);
            var cmd5 = new SqlCommand(cmdText5, connection);
            cmd5.ExecuteNonQuery();
            Logger.Info("Done executing");

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

            var cmd6 = new SqlCommand(cmdText6, connection);
            Logger.Info("Executing: " + cmdText6);
            cmd6.ExecuteNonQuery();
            Logger.Info("Done executing");

            connection.Close();
            connection.Dispose();
        }
        
        public void Unversion(int versionsToKeep)
        {
            Logger.Info("Unversioning to " + versionsToKeep + " versions using " + _connectionString);

            var connection = new SqlConnection(_connectionString);

            connection.Open();
            const string cmdTxt = "SELECT ContentId, COUNT(ContentId) AS VersionCount FROM cmsContentVersion GROUP BY ContentId ORDER BY COUNT(ContentId) DESC;";

            Logger.Info("Executing: " + cmdTxt);

            var cmd = new SqlCommand(cmdTxt, connection);
            var reader = cmd.ExecuteReader();

            Logger.Info("Done executing");

            var items = new List<int>();

            while (reader.Read())
            {
                var count = (int)reader["VersionCount"];
                var id = (int)reader["ContentId"];

                if (count > 1)
                {
                    items.Add(id);
                }
            }

            reader.Close();

            int processed = 0;
            int failed = 0;

            connection.Close();
            connection.Dispose();

            foreach (var a in items)
            {
                Logger.Info("Working on -> " + a);

                try
                {
                    processed++;
                    ProcessItem(a, _connectionString, versionsToKeep);
                }
                catch (Exception ex)
                {
                    Logger.Warn(ex);
                    failed++;
                }
            }

            Logger.Info("Processed " + processed + " items. " + failed + " failed.");
        }
        public void UnversionBefore(int noOfMonths)
        {
            var versionCutOffDate = DateTime.Now.AddMonths(-noOfMonths);
            Logger.Info("Unversioning Before " + versionCutOffDate.ToString("{0:d}") + " versions using " + _connectionString);

            var connection = new SqlConnection(_connectionString);

            connection.Open();
            // Select all versions before the cut off date that are not published and are not the 'newest'
           var cmdTxt = string.Format(@"SELECT ContentId, COUNT(ContentId) AS VersionCount 
                                    FROM cmsContentVersion cv 
                                    LEFT OUTER JOIN	cmsDocument d ON d.versionId = cv.VersionId Where VersionDate < '{0}' 
                                    AND published=0 AND newest=0 
                                    GROUP BY ContentId 
                                    ORDER BY COUNT(ContentId) DESC", versionCutOffDate.ToString("yyyy-MM-dd"));


            Logger.Info("Executing: " + cmdTxt);

            var cmd = new SqlCommand(cmdTxt, connection);
            var reader = cmd.ExecuteReader();

            Logger.Info("Done executing");

            var items = new List<int>();

            while (reader.Read())
            {
                var id = (int)reader["ContentId"];
                items.Add(id);
            }

            reader.Close();

            int processed = 0;
            int failed = 0;

            connection.Close();
            connection.Dispose();

            foreach (var a in items)
            {
                Logger.Info("Working on -> " + a);

                try
                {
                    processed++;
                    ProcessItemBefore(a, _connectionString, versionCutOffDate);
                }
                catch (Exception ex)
                {
                    Logger.Warn(ex);
                    failed++;
                }
            }

            Logger.Info("Processed " + processed + " items. " + failed + " failed.");
        }
        private void ProcessItemBefore(int a, String connectionString, DateTime versionCutOffDate)
        {
            var connection = new SqlConnection(connectionString);
            connection.Open();

            var cmdText2 = string.Format(@"SELECT cv.VersionId,
	                                                cv.VersionDate,
	                                                d.published,
	                                                d.newest
                                FROM				cmsContentVersion cv
                                LEFT OUTER JOIN		cmsDocument d ON d.versionId = cv.VersionId
                                WHERE				cv.ContentId = {0}
                                ORDER BY            cv.VersionDate DESC", a);


            Logger.Info("Executing: " + cmdText2);
            var cmd2 = new SqlCommand(cmdText2, connection);
            var reader2 = cmd2.ExecuteReader();
            Logger.Info("Done executing");

            var versions2 = new List<string>();
            var readerIndex = 0;

            while (reader2.Read())
            {
                var versionid = Convert.ToString(reader2["VersionId"]);
                var versionDate = Convert.ToDateTime(reader2["VersionDate"]);
                var published = false;
                try
                {
                    published = (bool)reader2["published"];
                }
                catch (Exception ex)
                {
                    Logger.Warn(ex.Message, ex);
                }

                var newest = false;

                try
                {
                    newest = (bool)reader2["newest"];
                }
                catch (Exception ex)
                {
                    Logger.Warn(ex.Message, ex);
                }

                readerIndex++;

                if (published || newest || versionDate > versionCutOffDate)
                    versions2.Add(versionid);

            }

            reader2.Close();
            if (versions2.Count == 0)
                return;

            var versionsToKeepString = String.Join("','", versions2.ToArray());

            var cmdText3 = string.Format(@"
                                DELETE
                                FROM	cmsPreviewXml
                                WHERE	nodeId = {0} AND versionId NOT IN ('{1}')", a, versionsToKeepString);

            var cmd3 = new SqlCommand(cmdText3, connection);

            Logger.Info("Executing: " + cmdText3);
            cmd3.ExecuteNonQuery();
            Logger.Info("Done executing");


            var cmdTxt4 = string.Format(@"
                                DELETE
                                FROM	cmsPropertyData
                                WHERE	contentNodeId = {0} AND versionId  NOT IN ('{1}')", a, versionsToKeepString);

            Logger.Info("Executing: " + cmdTxt4);
            var cmd4 = new SqlCommand(cmdTxt4, connection);
            cmd4.ExecuteNonQuery();
            Logger.Info("Done executing");


            var cmdText5 = string.Format(@"
                                DELETE
                                FROM	cmsContentVersion
                                WHERE	contentId = {0} AND versionId  NOT IN ('{1}')", a, versionsToKeepString);

            Logger.Info("Executing: " + cmdText5);
            var cmd5 = new SqlCommand(cmdText5, connection);
            cmd5.ExecuteNonQuery();
            Logger.Info("Done executing");

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

            var cmd6 = new SqlCommand(cmdText6, connection);
            Logger.Info("Executing: " + cmdText6);
            cmd6.ExecuteNonQuery();
            Logger.Info("Done executing");

            connection.Close();
            connection.Dispose();
        }
    }
}