[Ils-source] r1497 - trunk/db/mssql
scribe at crosswire.org
scribe at crosswire.org
Thu Nov 3 16:37:50 MST 2016
Author: scribe
Date: 2016-11-03 16:37:50 -0700 (Thu, 03 Nov 2016)
New Revision: 1497
Added:
trunk/db/mssql/upgrade1.81.0-1.sql
Modified:
trunk/db/mssql/ilsdb.sql
Log:
added db changed for 1.81
Modified: trunk/db/mssql/ilsdb.sql
===================================================================
--- trunk/db/mssql/ilsdb.sql 2016-11-03 23:35:44 UTC (rev 1496)
+++ trunk/db/mssql/ilsdb.sql 2016-11-03 23:37:50 UTC (rev 1497)
@@ -1,5 +1,5 @@
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', COURSISALLOWCERTPARTRETAKE char(1) DEFAULT 'F', COURSCERTGEN VARCHAR(60) DEFAULT null, CONSTRAINT COURSEPK1 PRIMARY KEY (COMPANYID, COURSID))
+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', COURSISALLOWCERTPARTRETAKE char(1) DEFAULT 'F', COURSCERTGEN VARCHAR(60) DEFAULT null, COURSISCERTSTORE char(1) DEFAULT 'F', 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(50), 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))
@@ -37,12 +37,15 @@
INSERT INTO SECURITYMODE (COMPANYID, SECMODDESC, SECMODVAL) VALUES (1, 'Course Developer', 9)
+GO
-- 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, CASE WHEN (T9.COURSID IS NULL) THEN NULL ELSE T1.COURSID END as FROMCURRICULUM
+GO
+
+CREATE VIEW USERCOURSEASSIGNMENTSWITHGROUPS WITH SCHEMABINDING AS SELECT DISTINCT T2.COMPANYID, T2.USERPRID, COALESCE (T9.COURSID, T1.COURSID) AS COURSID, G.ILSGROUPID, G.GROUPTYPEID, CASE WHEN (T9.COURSID IS NULL OR T9.COURSISCURRICULUM='T') THEN NULL ELSE T1.COURSID END as FROMCURRICULUM
FROM dbo.USERGROUP AS T2
-- which courses and curriculum are assigned by user group
INNER JOIN dbo.COURSEGROUP AS T3 ON T3.COMPANYID = T2.COMPANYID AND T3.ILSGROUPID = T2.ILSGROUPID
@@ -54,15 +57,20 @@
LEFT OUTER JOIN dbo.COURSE AS T9 ON T5.COMPANYID IS NOT NULL AND T9.COMPANYID = T5.COMPANYID AND T9.COURSID = T5.COURSID
-- group details
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 (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);
+--
+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 (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);
+GO
+
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;
+GO
CREATE NONCLUSTERED INDEX CAEXPIREDONINDEX ON COURSEATTEMPT (COMPANYID,CAEXPIREDON) INCLUDE (USERPRID,COURSID,CACOMPLETEDATE,CASTATUSID);
+
+GO
Added: trunk/db/mssql/upgrade1.81.0-1.sql
===================================================================
--- trunk/db/mssql/upgrade1.81.0-1.sql (rev 0)
+++ trunk/db/mssql/upgrade1.81.0-1.sql 2016-11-03 23:37:50 UTC (rev 1497)
@@ -0,0 +1,30 @@
+alter table COURSE add COURSCERTGEN VARCHAR(60) DEFAULT null;
+alter table COURSE add COURSISCERTSTORE char(1) DEFAULT 'F';
+alter table COURSE add COURSISALLOWCURRPARTRETAKE char(1) default 'F';
+
+DROP VIEW USERCOURSEASSIGNMENTSWITHGROUPS;
+GO
+
+CREATE VIEW USERCOURSEASSIGNMENTSWITHGROUPS WITH SCHEMABINDING AS SELECT DISTINCT T2.COMPANYID, T2.USERPRID, COALESCE (T9.COURSID, T1.COURSID) AS COURSID, G.ILSGROUPID, G.GROUPTYPEID, CASE WHEN (T9.COURSID IS NULL OR T9.COURSISCURRICULUM='T') THEN NULL ELSE T1.COURSID END as FROMCURRICULUM
+FROM dbo.USERGROUP AS T2
+-- which courses and curriculum are assigned by user group
+INNER JOIN dbo.COURSEGROUP AS T3 ON T3.COMPANYID = T2.COMPANYID AND T3.ILSGROUPID = T2.ILSGROUPID
+-- course details for courses curriculum assigned by user group
+INNER JOIN dbo.COURSE AS T1 ON T1.COMPANYID = T3.COMPANYID AND T1.COURSID = T3.COURSID
+-- for each curriculum, pull in all courses. T5.COURSID is a course assigned via a curriculum
+LEFT OUTER JOIN dbo.CURRICULUMCOURSE AS T5 ON T5.COMPANYID = T1.COMPANYID AND T5.CURRICULUMID = T1.COURSID AND T1.COURSISCURRICULUM = 'T'
+-- course details for courses assigned via a curriculum
+LEFT OUTER JOIN dbo.COURSE AS T9 ON T5.COMPANYID IS NOT NULL AND T9.COMPANYID = T5.COMPANYID AND T9.COURSID = T5.COURSID
+-- group details
+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 (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);
+
+
+GO
+
More information about the Ils-source
mailing list