pears.DBCleanupRoutine

CREATE PROCEDURE "pears"."DBCleanupRoutine"()
BEGIN
  /* IXS tables */
  DELETE FROM "ixsstatus" WHERE "querytime" < CURRENT date-14;
  /* Jobrunner */
  DELETE FROM "iqxjobrunnerlog" WHERE "completedtime" < CURRENT date-180;
  /* TagValue */
  DELETE FROM "tagvalue" WHERE(("taglocation" = 'P') OR("taglocation" LIKE 'A%') OR("taglocation" = 'E'))
    AND NOT EXISTS(SELECT * FROM "person" WHERE "personid" = "tagvalue"."id");
  DELETE FROM "tagvalue" WHERE("taglocation" = 'C')
    AND NOT EXISTS(SELECT * FROM "company" WHERE "companyid" = "tagvalue"."id");
  DELETE FROM "tagvalue" WHERE("taglocation" LIKE 'V%')
    AND NOT EXISTS(SELECT * FROM "vacancy" WHERE "vacancyid" = "tagvalue"."id");
  DELETE FROM "tagvalue" WHERE("taglocation" LIKE 'L%')
    AND NOT EXISTS(SELECT * FROM "placement" WHERE "placementid" = "tagvalue"."id");
  DELETE FROM "tagvalue" WHERE("taglocation" = 'U')
    AND NOT EXISTS(SELECT * FROM "staff" WHERE "staffid" = "tagvalue"."id");
  DELETE FROM "tagvalue" WHERE("taglocation" = 'N')
    AND NOT EXISTS(SELECT * FROM "contactevent" WHERE "contacteventid" = "tagvalue"."id");
  /* OLEDocument */
  DELETE FROM "OLEDocument" WHERE("ownertype" = 'P')
    AND NOT EXISTS(SELECT * FROM "person" WHERE "personid" = "OLEDocument"."ownerid");
  DELETE FROM "OLEDocument" WHERE("ownertype" = 'C')
    AND NOT EXISTS(SELECT * FROM "company" WHERE "companyid" = "OLEDocument"."ownerid");
  DELETE FROM "OLEDocument" WHERE("ownertype" = 'V')
    AND NOT EXISTS(SELECT * FROM "vacancy" WHERE "vacancyid" = "OLEDocument"."ownerid");
  DELETE FROM "OLEDocument" WHERE("ownertype" = 'L')
    AND NOT EXISTS(SELECT * FROM "placement" WHERE "placementid" = "OLEDocument"."ownerid");
  /* Blobstore */
  DELETE FROM "Blobstore" WHERE(("class" = 'V') OR("class" = 'J'))
    AND NOT EXISTS(SELECT * FROM "person" WHERE "personid" = "Blobstore"."id");
  DELETE FROM "Blobstore" WHERE("class" = 'O')
    AND NOT EXISTS(SELECT * FROM "OLEDocument" WHERE "OLEDocumentid" = "Blobstore"."id");
  DELETE FROM "Blobstore" WHERE("class" = 'L')
    AND NOT EXISTS(SELECT * FROM "ContactEvent" WHERE "Contacteventid" = "Blobstore"."id");
  DELETE FROM "Blobstore" WHERE("class" = 'M')
    AND NOT EXISTS(SELECT * FROM "MailMerge" WHERE "MailMergeid" = "Blobstore"."id");
  DELETE FROM "Blobstore" WHERE("class" = 'T')
    AND NOT EXISTS(SELECT * FROM "TemplateStore" WHERE "Templateid" = "Blobstore"."id");
  /* RecentObjects */
  DELETE FROM "RecentObjects"
    WHERE "LastViewed" <= CURRENT date-60;
  DELETE FROM "RecentObjects"
    WHERE NOT EXISTS(SELECT * FROM "staff" WHERE "staffid" = "RecentObjects"."staffid");
  DELETE FROM "RecentObjects" WHERE("objecttype" = 'P')
    AND NOT EXISTS(SELECT * FROM "person" WHERE "personid" = "RecentObjects"."objectid");
  DELETE FROM "RecentObjects" WHERE("objecttype" = 'C')
    AND NOT EXISTS(SELECT * FROM "company" WHERE "companyid" = "RecentObjects"."objectid");
  DELETE FROM "RecentObjects" WHERE("objecttype" = 'V')
    AND NOT EXISTS(SELECT * FROM "vacancy" WHERE "vacancyid" = "RecentObjects"."objectid");
  DELETE FROM "RecentObjects" WHERE("objecttype" = 'L')
    AND NOT EXISTS(SELECT * FROM "placement" WHERE "placementid" = "RecentObjects"."objectid");
  DELETE FROM "RecentObjects" WHERE("objecttype" = 'N')
    AND NOT EXISTS(SELECT * FROM "contactevent" WHERE "contacteventid" = "RecentObjects"."objectid");
  DELETE FROM "RecentObjects" WHERE("objecttype" = 'R')
    AND NOT EXISTS(SELECT * FROM "progress" WHERE "progressid" = "RecentObjects"."objectid");
  DELETE FROM "RecentObjects" WHERE("objecttype" = 'E')
    AND NOT EXISTS(SELECT * FROM "employment" WHERE "employmentid" = "RecentObjects"."objectid");
  /* ignore type T */
  /* Phone */
  DELETE FROM "phone" WHERE("who" = 'P')
    AND NOT EXISTS(SELECT * FROM "person" WHERE "personid" = "phone"."whoid");
  DELETE FROM "phone" WHERE "who" = 'CP'
    AND NOT EXISTS(SELECT * FROM "employment" WHERE "employmentid" = "phone"."whoid");
  DELETE FROM "phone" WHERE("who" = 'C')
    AND NOT EXISTS(SELECT * FROM "company" WHERE "companyid" = "phone"."whoid")
END