[Ils-source] r1419 - trunk/db/mssql
scribe at crosswire.org
scribe at crosswire.org
Sun Apr 3 17:35:19 MST 2016
Author: scribe
Date: 2016-04-03 17:35:18 -0700 (Sun, 03 Apr 2016)
New Revision: 1419
Added:
trunk/db/mssql/upgrade1.80.5-2.sql
Modified:
trunk/db/mssql/ilsdb.sql
Log:
Added *WITHGROUPS VIEW which was missing from original 1.80.5 delivery
Modified: trunk/db/mssql/ilsdb.sql
===================================================================
--- trunk/db/mssql/ilsdb.sql 2016-04-03 23:25:42 UTC (rev 1418)
+++ trunk/db/mssql/ilsdb.sql 2016-04-04 00:35:18 UTC (rev 1419)
@@ -1,46 +1,50 @@
-CREATE TABLE ILSGROUP (COMPANYID INTEGER NOT NULL, ILSGROUPID INTEGER IDENTITY(1,1) NOT NULL, GROUPNAME VARCHAR(75) NOT NULL, GROUPDESC VARCHAR(255), GROUPTYPEID INTEGER, USERDATA VARCHAR(80), CONSTRAINT ILSGROUPPK1 PRIMARY KEY (COMPANYID, ILSGROUPID))
-CREATE TABLE COURSE (COMPANYID INTEGER NOT NULL, COURSID INTEGER IDENTITY(1,1) NOT NULL, COURSNUM VARCHAR(20) NOT NULL, COURSNAME VARCHAR(100), COURSDESC VARCHAR(500), COURSTYPEID INTEGER, COURSPASS INTEGER, COURSPASSEXPDAYS INTEGER, COURSREASBEFDAYS INTEGER, COURSURL VARCHAR(255), COURSCKLISTURL VARCHAR(255), COURSCOST FLOAT, COURSSEATMINS INTEGER, COURSISCURRICULUM CHAR(1), COURSISQUICKVIEW CHAR(1), COURSPOSTDATE DATETIME, COURSISACTIVE CHAR(1), COURSISIPRESTRICTED char(1), COURSREQMGRAPPRVL CHAR(1), USERDATA VARCHAR(80), COURSISCURRICCERT char(1) DEFAULT 'F', COURSCERTGEN VARCHAR(60) DEFAULT null, CONSTRAINT COURSEPK1 PRIMARY KEY (COMPANYID, COURSID))
-
-CREATE TABLE USERPROFILE (COMPANYID INTEGER NOT NULL, USERPRID INTEGER IDENTITY(1,1) NOT NULL, USERPRNUM VARCHAR(50) NOT NULL, USERPRPASSWD VARCHAR(20), USERPRMNAME VARCHAR(40), USERPRSNAME VARCHAR(40), USERPRFNAME VARCHAR(40), USERPRLNAME VARCHAR(40), USERPRSTATUSID INTEGER, USERPRADDR1 VARCHAR(60),USERPRADDR2 VARCHAR(60),USERPRCITY VARCHAR(30),USERPRSTATE VARCHAR(2),USERPRZIP VARCHAR(12),USERPRCOUNTRY VARCHAR(50),USERPREMAIL VARCHAR(50), USERPRPHONE VARCHAR(50), USERPREMPTITLEID INTEGER, USERPRLASTLOGIN TIMESTAMP, USERPRACCESSLEVEL INTEGER, USERPRACCESSMOD INTEGER, USERPRINACTDATE DATETIME, USERPRHIREDATE DATETIME, USERPRTERMDATE DATETIME, USERPRIPRESTRICTOVERRIDE char(1), USERDATA VARCHAR(80), USERPRSECONDLOGINOVERRIDE char(1) DEFAULT 'F', USERPMGRRECUREMAILSOVERRIDE char(1) DEFAULT 'F', CONSTRAINT USERPROFILEPK1 PRIMARY KEY (COMPANYID, USERPRID))
-CREATE TABLE ANNOUNCEMENT (COMPANYID INTEGER NOT NULL, ANNOUNCID INTEGER IDENTITY(1,1) NOT NULL, ANNOUNCTITLE VARCHAR(100), ANNOUNCMESSAGE VARCHAR(255), ANNOUNCEXP DATETIME, ANNOUNCURL VARCHAR(255), ANNOUNCPOSTDATE DATETIME, USERDATA VARCHAR(80), CONSTRAINT ANNOUNCEMENTPK1 PRIMARY KEY (COMPANYID, ANNOUNCID))
-CREATE TABLE RESOURCE (COMPANYID INTEGER NOT NULL, RESOURCID INTEGER IDENTITY(1,1) NOT NULL, RESOURCTITLE VARCHAR(100), RESOURCMESSAGE VARCHAR(255), RESOURCEXP DATETIME, RESOURCURL VARCHAR(255), RESOURCPOSTDATE DATETIME, RESOURCTYPE INTEGER, USERDATA VARCHAR(80), CONSTRAINT RESOURCEPK1 PRIMARY KEY (COMPANYID, RESOURCID))
-CREATE TABLE USERGROUP (COMPANYID INTEGER NOT NULL, USERPRID INTEGER NOT NULL, ILSGROUPID INTEGER NOT NULL, CONSTRAINT USERGROUPPK1 PRIMARY KEY (COMPANYID, USERPRID, ILSGROUPID))
-CREATE TABLE COURSEATTEMPT (COMPANYID INTEGER NOT NULL, COURSATTID INTEGER IDENTITY(1,1) NOT NULL, USERPRID INTEGER NOT NULL, COURSID INTEGER NOT NULL, CASTARTDATE DATETIME, CACOMPLETEDATE DATETIME, CASCORE INTEGER, CASTATUSID INTEGER, CASTAGE TEXT, CAISMGRAPPRVL CHAR(1), CAWARNEDON1 DATETIME, CAWARNEDON2 DATETIME, CAWARNEDON3 DATETIME, CAWARNEDON4 DATETIME, CAEXPIREDON DATETIME, CAEXPREASON NVARCHAR(80), CAFORCEPASSON DATETIME, CAFORCEPASSREASON NVARCHAR(80), CACALLBACKSTATUS VARCHAR(50), CONSTRAINT COURSEATTEMPTPK1 PRIMARY KEY (COMPANYID, COURSATTID))
-CREATE TABLE COMPLIANCE (COMPANYID INTEGER NOT NULL, COMPLCID INTEGER IDENTITY(1,1) NOT NULL, COMPLCNAME VARCHAR(40), CONSTRAINT COMPLIANCEPK1 PRIMARY KEY (COMPANYID, COMPLCID))
-CREATE TABLE COURSECOMPLC (COMPANYID INTEGER NOT NULL, COURSID INTEGER NOT NULL, COMPLCID INTEGER NOT NULL, CONSTRAINT COURSECOMPLCPK1 PRIMARY KEY (COMPANYID, COURSID, COMPLCID))
-CREATE TABLE COURSEGROUP (COMPANYID INTEGER NOT NULL, COURSID INTEGER NOT NULL, ILSGROUPID INTEGER NOT NULL, SORTORDER INTEGER, CONSTRAINT COURSEGROUPPK1 PRIMARY KEY (COMPANYID, COURSID, ILSGROUPID))
-CREATE TABLE MANAGERGROUP (COMPANYID INTEGER NOT NULL, USERPRID INTEGER NOT NULL, ILSGROUPID INTEGER NOT NULL, CONSTRAINT MANAGERGROUPPK1 PRIMARY KEY (COMPANYID, USERPRID, ILSGROUPID))
-CREATE TABLE COURSEATTSTATUS (COMPANYID INTEGER NOT NULL, CASTATUSID INTEGER NOT NULL, CASTATUSDESC VARCHAR(40), CONSTRAINT COURSATTSTATUSPK1 PRIMARY KEY (COMPANYID, CASTATUSID))
-CREATE TABLE COMPANY (COMPANYID INTEGER IDENTITY(1,1) NOT NULL, CONAME VARCHAR(40), COFUNDSAVAIL FLOAT, COPERSEATCOST FLOAT, COADMINCONTACTNM VARCHAR(127), COADMINCONTACTEM VARCHAR(127), COADMINCONTACTPH VARCHAR(127), COLOGOURL VARCHAR(127), COCOPYRIGHT VARCHAR(253), CONSTRAINT COMPANYPK1 PRIMARY KEY (COMPANYID))
-CREATE TABLE SYSCONF (COMPANYID INTEGER NOT NULL, SYSCFCOLLABURL VARCHAR(253), SYSCFEMAILSRV VARCHAR(126), CONSTRAINT SYSCONFPK1 PRIMARY KEY (COMPANYID))
-CREATE TABLE CURRICULUMCOURSE (COMPANYID INTEGER NOT NULL, CURRICULUMID INTEGER NOT NULL, COURSID INTEGER NOT NULL, SORTORDER INTEGER, CONSTRAINT CURRICULUMCOURSEPK1 PRIMARY KEY (COMPANYID, CURRICULUMID, COURSID))
-CREATE TABLE EMPTITLE (COMPANYID INTEGER NOT NULL, EMPTITLEID INTEGER IDENTITY(1,1) NOT NULL, EMPTITLENAME VARCHAR(40), EMPTITLEDESC VARCHAR(80), USERDATA VARCHAR(80), CONSTRAINT EMPTITLEPK1 PRIMARY KEY (COMPANYID, EMPTITLEID))
-CREATE TABLE GROUPTYPE (COMPANYID INTEGER NOT NULL, GROUPTYPEID INTEGER NOT NULL, GROUPTYPENAME VARCHAR(40), GROUPTYPEDESC VARCHAR(80), CONSTRAINT GROUPTYPEPK1 PRIMARY KEY (COMPANYID, GROUPTYPEID))
-CREATE TABLE COURSETYPE (COMPANYID INTEGER NOT NULL, COURSETYPEID INTEGER NOT NULL, COURSETYPENAME VARCHAR(40), COURSETYPEDESC VARCHAR(80), CONSTRAINT COURSETYPEPK1 PRIMARY KEY (COMPANYID, COURSETYPEID))
-CREATE TABLE SECURITYMODE (COMPANYID INTEGER NOT NULL, SECMODDESC VARCHAR(75) NOT NULL, SECMODVAL INTEGER NOT NULL, CONSTRAINT SECURITYMODEPK1 PRIMARY KEY (COMPANYID, SECMODDESC))
-INSERT INTO COMPANY (CONAME, COFUNDSAVAIL, COPERSEATCOST, COLOGOURL, COCOPYRIGHT) VALUES ('ILS', 0.00, 0.00, 'images/logo.gif', 'Copyright (C) 2006 ILS')
-INSERT INTO SYSCONF (COMPANYID, SYSCFCOLLABURL, SYSCFEMAILSRV) VALUES (1, 'http://crosswire.org/forums', 'mail.crosswire.org')
-INSERT INTO COURSEATTSTATUS (COMPANYID, CASTATUSID, CASTATUSDESC) VALUES (1, 0, 'NEW')
-INSERT INTO COURSEATTSTATUS (COMPANYID, CASTATUSID, CASTATUSDESC) VALUES (1, 1, 'PASSED')
-INSERT INTO COURSEATTSTATUS (COMPANYID, CASTATUSID, CASTATUSDESC) VALUES (1, 2, 'FAILED')
-INSERT INTO COURSEATTSTATUS (COMPANYID, CASTATUSID, CASTATUSDESC) VALUES (1, 3, 'IN PROGRESS')
-INSERT INTO GROUPTYPE (COMPANYID, GROUPTYPEID, GROUPTYPENAME, GROUPTYPEDESC) VALUES (1, 1, 'Role', 'Groups of Roles')
-INSERT INTO GROUPTYPE (COMPANYID, GROUPTYPEID, GROUPTYPENAME, GROUPTYPEDESC) VALUES (1, 2, 'Location', 'Groups of Locations')
-INSERT INTO COURSETYPE (COMPANYID, COURSETYPEID, COURSETYPENAME, COURSETYPEDESC) VALUES (1, 1, 'AICC / HACP', 'AICC / HACP')
-INSERT INTO COURSETYPE (COMPANYID, COURSETYPEID, COURSETYPENAME, COURSETYPEDESC) VALUES (1, 2, 'eXpress course', 'eXpress course')
-INSERT INTO USERPROFILE (COMPANYID, USERPRNUM, USERPRPASSWD, USERPRFNAME, USERPRLNAME, USERPRSTATUSID, USERPRACCESSLEVEL) VALUES (1, 'admin', 'Administrator1', 'Admin', 'Admin', 1, 3)
-INSERT INTO SECURITYMODE (COMPANYID, SECMODDESC, SECMODVAL) VALUES (1, 'Default Manager', 8)
-INSERT INTO SECURITYMODE (COMPANYID, SECMODDESC, SECMODVAL) VALUES (1, 'Badging Officer', 520)
-INSERT INTO SECURITYMODE (COMPANYID, SECMODDESC, SECMODVAL) VALUES (1, 'Course Developer', 9)
-
-
-
--- run separately
-
-CREATE VIEW USERCOURSEASSIGNMENTS WITH SCHEMABINDING AS SELECT DISTINCT T2.COMPANYID, T2.USERPRID, COALESCE (T9.COURSID, T1.COURSID) AS COURSID FROM dbo.USERGROUP AS T2 INNER JOIN dbo.COURSEGROUP AS T3 ON T3.COMPANYID = T2.COMPANYID AND T3.ILSGROUPID = T2.ILSGROUPID INNER JOIN dbo.COURSE AS T1 ON T1.COMPANYID = T3.COMPANYID AND T1.COURSID = T3.COURSID LEFT OUTER JOIN dbo.CURRICULUMCOURSE AS T5 ON T5.COMPANYID = T1.COMPANYID AND T5.CURRICULUMID = T1.COURSID AND T1.COURSISCURRICULUM = 'T' LEFT OUTER JOIN dbo.COURSE AS T9 ON T5.COMPANYID IS NOT NULL AND T9.COMPANYID = T5.COMPANYID AND T9.COURSID = T5.COURSID WHERE (T9.COURSID IS NULL) AND (T1.COURSISCURRICULUM IS NULL) AND (T9.COURSID IS NULL) AND (T1.COURSISQUICKVIEW IS NULL OR T1.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NULL) AND (T1.COURSISCURRICULUM IS NULL) AND (T9.COURSID IS NOT NULL) AND (T9.COURSISQUICKVIEW IS NULL OR T9.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NULL) AND (T1.COURSISCURRICULUM <> 'T') AND (T1.COURSISQUICKVIEW IS NULL OR T1.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NOT NULL) AND (T1.COURSISCURRICULUM <> 'T') AND (T9.COURSISQUICKVIEW IS NULL OR T9.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NULL) AND (T1.COURSISQUICKVIEW IS NULL OR T1.COURSISQUICKVIEW <> 'T') AND (T1.COURSID <> T9.COURSID) OR (T9.COURSID IS NOT NULL) AND (T9.COURSISQUICKVIEW IS NULL OR T9.COURSISQUICKVIEW <> 'T') AND (T1.COURSID <> T9.COURSID);
-
-
-CREATE VIEW COURSEATTEMPTSTATUS WITH SCHEMABINDING AS SELECT CA.COMPANYID, CA.USERPRID, CA.COURSID, CA.COURSATTID, CA.CACOMPLETEDATE, CA.CAEXPIREDON, COALESCE(CA.CASTATUSID, 0) AS CASTATUSID, CASE COALESCE(CA.CASTATUSID, 0) WHEN 3 THEN 1 WHEN 0 THEN 1 ELSE 0 END AS CRS_OPEN, CASE COALESCE(CA.CASTATUSID, 0) WHEN 1 THEN 1 ELSE 0 END AS CRS_COMPLETED, CASE COALESCE(CA.CASTATUSID, 0) WHEN 2 THEN 1 ELSE 0 END AS CRS_FAILED FROM DBO.COURSEATTEMPT CA;
-
-
-CREATE NONCLUSTERED INDEX CAEXPIREDONINDEX ON COURSEATTEMPT (COMPANYID,CAEXPIREDON) INCLUDE (USERPRID,COURSID,CACOMPLETEDATE,CASTATUSID);
+CREATE TABLE ILSGROUP (COMPANYID INTEGER NOT NULL, ILSGROUPID INTEGER IDENTITY(1,1) NOT NULL, GROUPNAME VARCHAR(75) NOT NULL, GROUPDESC VARCHAR(255), GROUPTYPEID INTEGER, USERDATA VARCHAR(80), CONSTRAINT ILSGROUPPK1 PRIMARY KEY (COMPANYID, ILSGROUPID))
+CREATE TABLE COURSE (COMPANYID INTEGER NOT NULL, COURSID INTEGER IDENTITY(1,1) NOT NULL, COURSNUM VARCHAR(20) NOT NULL, COURSNAME VARCHAR(100), COURSDESC VARCHAR(500), COURSTYPEID INTEGER, COURSPASS INTEGER, COURSPASSEXPDAYS INTEGER, COURSREASBEFDAYS INTEGER, COURSURL VARCHAR(255), COURSCKLISTURL VARCHAR(255), COURSCOST FLOAT, COURSSEATMINS INTEGER, COURSISCURRICULUM CHAR(1), COURSISQUICKVIEW CHAR(1), COURSPOSTDATE DATETIME, COURSISACTIVE CHAR(1), COURSISIPRESTRICTED char(1), COURSREQMGRAPPRVL CHAR(1), USERDATA VARCHAR(80), COURSISCURRICCERT char(1) DEFAULT 'F', COURSCERTGEN VARCHAR(60) DEFAULT null, CONSTRAINT COURSEPK1 PRIMARY KEY (COMPANYID, COURSID))
+
+CREATE TABLE USERPROFILE (COMPANYID INTEGER NOT NULL, USERPRID INTEGER IDENTITY(1,1) NOT NULL, USERPRNUM VARCHAR(50) NOT NULL, USERPRPASSWD VARCHAR(20), USERPRMNAME VARCHAR(40), USERPRSNAME VARCHAR(40), USERPRFNAME VARCHAR(40), USERPRLNAME VARCHAR(40), USERPRSTATUSID INTEGER, USERPRADDR1 VARCHAR(60),USERPRADDR2 VARCHAR(60),USERPRCITY VARCHAR(30),USERPRSTATE VARCHAR(2),USERPRZIP VARCHAR(12),USERPRCOUNTRY VARCHAR(50),USERPREMAIL VARCHAR(50), USERPRPHONE VARCHAR(50), USERPREMPTITLEID INTEGER, USERPRLASTLOGIN TIMESTAMP, USERPRACCESSLEVEL INTEGER, USERPRACCESSMOD INTEGER, USERPRINACTDATE DATETIME, USERPRHIREDATE DATETIME, USERPRTERMDATE DATETIME, USERPRIPRESTRICTOVERRIDE char(1), USERDATA VARCHAR(80), USERPRSECONDLOGINOVERRIDE char(1) DEFAULT 'F', USERPMGRRECUREMAILSOVERRIDE char(1) DEFAULT 'F', CONSTRAINT USERPROFILEPK1 PRIMARY KEY (COMPANYID, USERPRID))
+CREATE TABLE ANNOUNCEMENT (COMPANYID INTEGER NOT NULL, ANNOUNCID INTEGER IDENTITY(1,1) NOT NULL, ANNOUNCTITLE VARCHAR(100), ANNOUNCMESSAGE VARCHAR(255), ANNOUNCEXP DATETIME, ANNOUNCURL VARCHAR(255), ANNOUNCPOSTDATE DATETIME, USERDATA VARCHAR(80), CONSTRAINT ANNOUNCEMENTPK1 PRIMARY KEY (COMPANYID, ANNOUNCID))
+CREATE TABLE RESOURCE (COMPANYID INTEGER NOT NULL, RESOURCID INTEGER IDENTITY(1,1) NOT NULL, RESOURCTITLE VARCHAR(100), RESOURCMESSAGE VARCHAR(255), RESOURCEXP DATETIME, RESOURCURL VARCHAR(255), RESOURCPOSTDATE DATETIME, RESOURCTYPE INTEGER, USERDATA VARCHAR(80), CONSTRAINT RESOURCEPK1 PRIMARY KEY (COMPANYID, RESOURCID))
+CREATE TABLE USERGROUP (COMPANYID INTEGER NOT NULL, USERPRID INTEGER NOT NULL, ILSGROUPID INTEGER NOT NULL, CONSTRAINT USERGROUPPK1 PRIMARY KEY (COMPANYID, USERPRID, ILSGROUPID))
+CREATE TABLE COURSEATTEMPT (COMPANYID INTEGER NOT NULL, COURSATTID INTEGER IDENTITY(1,1) NOT NULL, USERPRID INTEGER NOT NULL, COURSID INTEGER NOT NULL, CASTARTDATE DATETIME, CACOMPLETEDATE DATETIME, CASCORE INTEGER, CASTATUSID INTEGER, CASTAGE TEXT, CAISMGRAPPRVL CHAR(1), CAWARNEDON1 DATETIME, CAWARNEDON2 DATETIME, CAWARNEDON3 DATETIME, CAWARNEDON4 DATETIME, CAEXPIREDON DATETIME, CAEXPREASON NVARCHAR(80), CAFORCEPASSON DATETIME, CAFORCEPASSREASON NVARCHAR(80), CACALLBACKSTATUS VARCHAR(50), CONSTRAINT COURSEATTEMPTPK1 PRIMARY KEY (COMPANYID, COURSATTID))
+CREATE TABLE COMPLIANCE (COMPANYID INTEGER NOT NULL, COMPLCID INTEGER IDENTITY(1,1) NOT NULL, COMPLCNAME VARCHAR(40), CONSTRAINT COMPLIANCEPK1 PRIMARY KEY (COMPANYID, COMPLCID))
+CREATE TABLE COURSECOMPLC (COMPANYID INTEGER NOT NULL, COURSID INTEGER NOT NULL, COMPLCID INTEGER NOT NULL, CONSTRAINT COURSECOMPLCPK1 PRIMARY KEY (COMPANYID, COURSID, COMPLCID))
+CREATE TABLE COURSEGROUP (COMPANYID INTEGER NOT NULL, COURSID INTEGER NOT NULL, ILSGROUPID INTEGER NOT NULL, SORTORDER INTEGER, CONSTRAINT COURSEGROUPPK1 PRIMARY KEY (COMPANYID, COURSID, ILSGROUPID))
+CREATE TABLE MANAGERGROUP (COMPANYID INTEGER NOT NULL, USERPRID INTEGER NOT NULL, ILSGROUPID INTEGER NOT NULL, CONSTRAINT MANAGERGROUPPK1 PRIMARY KEY (COMPANYID, USERPRID, ILSGROUPID))
+CREATE TABLE COURSEATTSTATUS (COMPANYID INTEGER NOT NULL, CASTATUSID INTEGER NOT NULL, CASTATUSDESC VARCHAR(40), CONSTRAINT COURSATTSTATUSPK1 PRIMARY KEY (COMPANYID, CASTATUSID))
+CREATE TABLE COMPANY (COMPANYID INTEGER IDENTITY(1,1) NOT NULL, CONAME VARCHAR(40), COFUNDSAVAIL FLOAT, COPERSEATCOST FLOAT, COADMINCONTACTNM VARCHAR(127), COADMINCONTACTEM VARCHAR(127), COADMINCONTACTPH VARCHAR(127), COLOGOURL VARCHAR(127), COCOPYRIGHT VARCHAR(253), CONSTRAINT COMPANYPK1 PRIMARY KEY (COMPANYID))
+CREATE TABLE SYSCONF (COMPANYID INTEGER NOT NULL, SYSCFCOLLABURL VARCHAR(253), SYSCFEMAILSRV VARCHAR(126), CONSTRAINT SYSCONFPK1 PRIMARY KEY (COMPANYID))
+CREATE TABLE CURRICULUMCOURSE (COMPANYID INTEGER NOT NULL, CURRICULUMID INTEGER NOT NULL, COURSID INTEGER NOT NULL, SORTORDER INTEGER, CONSTRAINT CURRICULUMCOURSEPK1 PRIMARY KEY (COMPANYID, CURRICULUMID, COURSID))
+CREATE TABLE EMPTITLE (COMPANYID INTEGER NOT NULL, EMPTITLEID INTEGER IDENTITY(1,1) NOT NULL, EMPTITLENAME VARCHAR(40), EMPTITLEDESC VARCHAR(80), USERDATA VARCHAR(80), CONSTRAINT EMPTITLEPK1 PRIMARY KEY (COMPANYID, EMPTITLEID))
+CREATE TABLE GROUPTYPE (COMPANYID INTEGER NOT NULL, GROUPTYPEID INTEGER NOT NULL, GROUPTYPENAME VARCHAR(40), GROUPTYPEDESC VARCHAR(80), CONSTRAINT GROUPTYPEPK1 PRIMARY KEY (COMPANYID, GROUPTYPEID))
+CREATE TABLE COURSETYPE (COMPANYID INTEGER NOT NULL, COURSETYPEID INTEGER NOT NULL, COURSETYPENAME VARCHAR(40), COURSETYPEDESC VARCHAR(80), CONSTRAINT COURSETYPEPK1 PRIMARY KEY (COMPANYID, COURSETYPEID))
+CREATE TABLE SECURITYMODE (COMPANYID INTEGER NOT NULL, SECMODDESC VARCHAR(75) NOT NULL, SECMODVAL INTEGER NOT NULL, CONSTRAINT SECURITYMODEPK1 PRIMARY KEY (COMPANYID, SECMODDESC))
+
+-- Add a company (1)
+
+INSERT INTO COMPANY (CONAME, COFUNDSAVAIL, COPERSEATCOST, COLOGOURL, COCOPYRIGHT) VALUES ('ILS', 0.00, 0.00, 'images/logo.gif', 'Copyright (C) 2006 ILS')
+INSERT INTO SYSCONF (COMPANYID, SYSCFCOLLABURL, SYSCFEMAILSRV) VALUES (1, 'http://crosswire.org/forums', 'mail.crosswire.org')
+INSERT INTO COURSEATTSTATUS (COMPANYID, CASTATUSID, CASTATUSDESC) VALUES (1, 0, 'NEW')
+INSERT INTO COURSEATTSTATUS (COMPANYID, CASTATUSID, CASTATUSDESC) VALUES (1, 1, 'PASSED')
+INSERT INTO COURSEATTSTATUS (COMPANYID, CASTATUSID, CASTATUSDESC) VALUES (1, 2, 'FAILED')
+INSERT INTO COURSEATTSTATUS (COMPANYID, CASTATUSID, CASTATUSDESC) VALUES (1, 3, 'IN PROGRESS')
+INSERT INTO GROUPTYPE (COMPANYID, GROUPTYPEID, GROUPTYPENAME, GROUPTYPEDESC) VALUES (1, 1, 'Role', 'Groups of Roles')
+INSERT INTO GROUPTYPE (COMPANYID, GROUPTYPEID, GROUPTYPENAME, GROUPTYPEDESC) VALUES (1, 2, 'Location', 'Groups of Locations')
+INSERT INTO COURSETYPE (COMPANYID, COURSETYPEID, COURSETYPENAME, COURSETYPEDESC) VALUES (1, 1, 'AICC / HACP', 'AICC / HACP')
+INSERT INTO COURSETYPE (COMPANYID, COURSETYPEID, COURSETYPENAME, COURSETYPEDESC) VALUES (1, 2, 'eXpress course', 'eXpress course')
+INSERT INTO USERPROFILE (COMPANYID, USERPRNUM, USERPRPASSWD, USERPRFNAME, USERPRLNAME, USERPRSTATUSID, USERPRACCESSLEVEL) VALUES (1, 'admin', 'Administrator1', 'Admin', 'Admin', 1, 3)
+INSERT INTO SECURITYMODE (COMPANYID, SECMODDESC, SECMODVAL) VALUES (1, 'Default Manager', 8)
+INSERT INTO SECURITYMODE (COMPANYID, SECMODDESC, SECMODVAL) VALUES (1, 'Badging Officer', 520)
+INSERT INTO SECURITYMODE (COMPANYID, SECMODDESC, SECMODVAL) VALUES (1, 'Course Developer', 9)
+
+
+
+-- run separately
+
+CREATE VIEW USERCOURSEASSIGNMENTS WITH SCHEMABINDING AS SELECT DISTINCT T2.COMPANYID, T2.USERPRID, COALESCE (T9.COURSID, T1.COURSID) AS COURSID FROM dbo.USERGROUP AS T2 INNER JOIN dbo.COURSEGROUP AS T3 ON T3.COMPANYID = T2.COMPANYID AND T3.ILSGROUPID = T2.ILSGROUPID INNER JOIN dbo.COURSE AS T1 ON T1.COMPANYID = T3.COMPANYID AND T1.COURSID = T3.COURSID LEFT OUTER JOIN dbo.CURRICULUMCOURSE AS T5 ON T5.COMPANYID = T1.COMPANYID AND T5.CURRICULUMID = T1.COURSID AND T1.COURSISCURRICULUM = 'T' LEFT OUTER JOIN dbo.COURSE AS T9 ON T5.COMPANYID IS NOT NULL AND T9.COMPANYID = T5.COMPANYID AND T9.COURSID = T5.COURSID WHERE (T9.COURSID IS NULL) AND (T1.COURSISCURRICULUM IS NULL) AND (T9.COURSID IS NULL) AND (T1.COURSISQUICKVIEW IS NULL OR T1.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NULL) AND (T1.COURSISCURRICULUM IS NULL) AND (T9.COURSID IS NOT NULL) AND (T9.COURSISQUICKVIEW IS NULL OR T9.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NULL) AND (T1.COURSISCURRICULUM <> 'T') AND (T1.COURSISQUICKVIEW IS NULL OR T1.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NOT NULL) AND (T1.COURSISCURRICULUM <> 'T') AND (T9.COURSISQUICKVIEW IS NULL OR T9.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NULL) AND (T1.COURSISQUICKVIEW IS NULL OR T1.COURSISQUICKVIEW <> 'T') AND (T1.COURSID <> T9.COURSID) OR (T9.COURSID IS NOT NULL) AND (T9.COURSISQUICKVIEW IS NULL OR T9.COURSISQUICKVIEW <> 'T') AND (T1.COURSID <> T9.COURSID);
+
+CREATE VIEW USERCOURSEASSIGNMENTSWITHGROUPS WITH SCHEMABINDING AS SELECT DISTINCT T2.COMPANYID, T2.USERPRID, COALESCE (T9.COURSID, T1.COURSID) AS COURSID, G.ILSGROUPID, G.GROUPTYPEID FROM dbo.USERGROUP AS T2 INNER JOIN dbo.COURSEGROUP AS T3 ON T3.COMPANYID = T2.COMPANYID AND T3.ILSGROUPID = T2.ILSGROUPID INNER JOIN dbo.COURSE AS T1 ON T1.COMPANYID = T3.COMPANYID AND T1.COURSID = T3.COURSID LEFT OUTER JOIN dbo.CURRICULUMCOURSE AS T5 ON T5.COMPANYID = T1.COMPANYID AND T5.CURRICULUMID = T1.COURSID AND T1.COURSISCURRICULUM = 'T' LEFT OUTER JOIN dbo.COURSE AS T9 ON T5.COMPANYID IS NOT NULL AND T9.COMPANYID = T5.COMPANYID AND T9.COURSID = T5.COURSID LEFT OUTER JOIN dbo.ILSGROUP G ON G.ILSGROUPID=T2.ILSGROUPID WHERE (T9.COURSID IS NULL) AND (T1.COURSISCURRICULUM IS NULL) AND (T9.COURSID IS NULL) AND (T1.COURSISQUICKVIEW IS NULL OR T1.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NULL) AND (T1.COURSISCURRICULUM IS NULL) AND (T9.COURSID IS NOT NULL) AND (T9.COURSISQUICKVIEW IS NULL OR T9.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NULL) AND (T1.COURSISCURRICULUM <> 'T') AND (T1.COURSISQUICKVIEW IS NULL OR T1.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NOT NULL) AND (T1.COURSISCURRICULUM <> 'T') AND (T9.COURSISQUICKVIEW IS NULL OR T9.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NULL) AND (T1.COURSISQUICKVIEW IS NULL OR T1.COURSISQUICKVIEW <> 'T') AND (T1.COURSID <> T9.COURSID) OR (T9.COURSID IS NOT NULL) AND (T9.COURSISQUICKVIEW IS NULL OR T9.COURSISQUICKVIEW <> 'T') AND (T1.COURSID <> T9.COURSID);
+
+CREATE VIEW COURSEATTEMPTSTATUS WITH SCHEMABINDING AS SELECT CA.COMPANYID, CA.USERPRID, CA.COURSID, CA.COURSATTID, CA.CACOMPLETEDATE, CA.CAEXPIREDON, COALESCE(CA.CASTATUSID, 0) AS CASTATUSID, CASE COALESCE(CA.CASTATUSID, 0) WHEN 3 THEN 1 WHEN 0 THEN 1 ELSE 0 END AS CRS_OPEN, CASE COALESCE(CA.CASTATUSID, 0) WHEN 1 THEN 1 ELSE 0 END AS CRS_COMPLETED, CASE COALESCE(CA.CASTATUSID, 0) WHEN 2 THEN 1 ELSE 0 END AS CRS_FAILED FROM DBO.COURSEATTEMPT CA;
+
+
+CREATE NONCLUSTERED INDEX CAEXPIREDONINDEX ON COURSEATTEMPT (COMPANYID,CAEXPIREDON) INCLUDE (USERPRID,COURSID,CACOMPLETEDATE,CASTATUSID);
Added: trunk/db/mssql/upgrade1.80.5-2.sql
===================================================================
--- trunk/db/mssql/upgrade1.80.5-2.sql (rev 0)
+++ trunk/db/mssql/upgrade1.80.5-2.sql 2016-04-04 00:35:18 UTC (rev 1419)
@@ -0,0 +1,2 @@
+CREATE VIEW USERCOURSEASSIGNMENTSWITHGROUPS WITH SCHEMABINDING AS SELECT DISTINCT T2.COMPANYID, T2.USERPRID, COALESCE (T9.COURSID, T1.COURSID) AS COURSID, G.ILSGROUPID, G.GROUPTYPEID FROM dbo.USERGROUP AS T2 INNER JOIN dbo.COURSEGROUP AS T3 ON T3.COMPANYID = T2.COMPANYID AND T3.ILSGROUPID = T2.ILSGROUPID INNER JOIN dbo.COURSE AS T1 ON T1.COMPANYID = T3.COMPANYID AND T1.COURSID = T3.COURSID LEFT OUTER JOIN dbo.CURRICULUMCOURSE AS T5 ON T5.COMPANYID = T1.COMPANYID AND T5.CURRICULUMID = T1.COURSID AND T1.COURSISCURRICULUM = 'T' LEFT OUTER JOIN dbo.COURSE AS T9 ON T5.COMPANYID IS NOT NULL AND T9.COMPANYID = T5.COMPANYID AND T9.COURSID = T5.COURSID LEFT OUTER JOIN dbo.ILSGROUP G ON G.ILSGROUPID=T2.ILSGROUPID WHERE (T9.COURSID IS NULL) AND (T1.COURSISCURRICULUM IS NULL) AND (T9.COURSID IS NULL) AND (T1.COURSISQUICKVIEW IS NULL OR T1.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NULL) AND (T1.COURSISCURRICULUM IS NULL) AND (T9.COURSID IS NOT NULL) AND (T9.COURSISQUICKVIEW IS NULL OR T9.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NULL) AND (T1.COURSISCURRICULUM <> 'T') AND (T1.COURSISQUICKVIEW IS NULL OR T1.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NOT NULL) AND (T1.COURSISCURRICULUM <> 'T') AND (T9.COURSISQUICKVIEW IS NULL OR T9.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NULL) AND (T1.COURSISQUICKVIEW IS NULL OR T1.COURSISQUICKVIEW <> 'T') AND (T1.COURSID <> T9.COURSID) OR (T9.COURSID IS NOT NULL) AND (T9.COURSISQUICKVIEW IS NULL OR T9.COURSISQUICKVIEW <> 'T') AND (T1.COURSID <> T9.COURSID);
+
More information about the Ils-source
mailing list