pears.DBCleanupRoutine [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.DBCleanupRoutine ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
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
</code>