[Ils-source] r1306 - trunk/sql

[scribe at crosswire.org]

Author: scribe
Date: 2015-04-14 13:50:56 -0700 (Tue, 14 Apr 2015)
New Revision: 1306

Added:
   trunk/sql/cleandeletedgroups.properties
Log:
added script to clean up assignments left over when a group is deleted.
Newer versions of the ils clean this up programmatically.


Added: trunk/sql/cleandeletedgroups.properties
===================================================================
--- trunk/sql/cleandeletedgroups.properties	                        (rev 0)
+++ trunk/sql/cleandeletedgroups.properties	2015-04-14 20:50:56 UTC (rev 1306)
@@ -0,0 +1,8 @@
+Category=Database Maintenance
+Name=Clean Deleted Groups
+Description=This script removes all assignments left hanging from deleted groups
+Requires=CompanyID
+
+SQL=delete from usergroup where ilsgroupid in (select t1.ilsgroupid from USERGROUP t1 left join ILSGROUP t2 on t1.ILSGROUPID=t2.ILSGROUPID where t2.ILSGROUPID is null)
+SQL2=delete from coursegroup where ilsgroupid in (select t1.ilsgroupid from COURSEGROUP t1 left join ILSGROUP t2 on t1.ILSGROUPID=t2.ILSGROUPID where t2.ILSGROUPID is null)
+SQL3=delete from managergroup where ilsgroupid in (select t1.ilsgroupid from MANAGERGROUP t1 left join ILSGROUP t2 on t1.ILSGROUPID=t2.ILSGROUPID where t2.ILSGROUPID is null)