gistfile1.txt

BEGIN

      DECLARE n INT;
      DECLARE loop_end INT DEFAULT 0;
      DECLARE nodeids CURSOR FOR (select distinct(nodeid)  from versions order by nodeid);
      declare continue handler for sqlstate '02000' set loop_end = 1;

      CREATE TEMPORARY TABLE versions (rank int, versionid varchar(40), nodeid int, updatedate timestamp, newest tinyint, published tinyint);
      CREATE TEMPORARY TABLE versionids (rank int, versionid varchar(40), nodeid int);

      SET @rank = 0;

      insert into versions select @rank:=@rank+1 AS rank, versionid, nodeid, updatedate, newest, published from CMSDocument CD1
        where versionid
        not in (SELECT versionid FROM cmsDocument CD2  Where (published = 1 or newest = 1) and CD1.nodeid = CD2.nodeid)
        ORDER BY nodeid ASC;

      SET @rank = 0;
      SET @count = 1;


      OPEN nodeids;


      repeat

        FETCH nodeids INTO n;

        if not loop_end then

          insert into versionids select @rank:=@rank+1 AS rank, versionid, nodeid from versions V where nodeid = n order by updatedate DESC LIMIT 10,18446744073709551615;
          DELETE FROM cmsPropertyData where contentnodeid = n and versionid in (select versionid from versionids where nodeid = n);

          DELETE FROM cmsContentVersion where contentid = n and versionid in (select versionid from versionids where nodeid = n);
          delete from cmsdocument where nodeid = n and versionid in (select versionid from versionids where nodeid = n);
          delete from versionids;
        end if;

      until loop_end end repeat;

      CLOSE nodeids;


      DROP TEMPORARY TABLE versions;
      DROP TEMPORARY TABLE versionids;
    END