[Ils-source] r1294 - trunk/db/mssql

[scribe at crosswire.org]

Author: scribe
Date: 2015-01-13 14:48:36 -0700 (Tue, 13 Jan 2015)
New Revision: 1294

Added:
   trunk/db/mssql/upgrade179.2.sql
   trunk/db/mssql/upgrade179.3.sql
Modified:
   trunk/db/mssql/upgrade179.1.sql
Log:
split db script up into 3 parts due to create view directive not having escaped delimeters


Modified: trunk/db/mssql/upgrade179.1.sql
===================================================================
--- trunk/db/mssql/upgrade179.1.sql	2015-01-13 21:46:39 UTC (rev 1293)
+++ trunk/db/mssql/upgrade179.1.sql	2015-01-13 21:48:36 UTC (rev 1294)
@@ -1,7 +1,2 @@
 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)
-
-

Added: trunk/db/mssql/upgrade179.2.sql
===================================================================
--- trunk/db/mssql/upgrade179.2.sql	                        (rev 0)
+++ trunk/db/mssql/upgrade179.2.sql	2015-01-13 21:48:36 UTC (rev 1294)
@@ -0,0 +1,2 @@
+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;
+

Added: trunk/db/mssql/upgrade179.3.sql
===================================================================
--- trunk/db/mssql/upgrade179.3.sql	                        (rev 0)
+++ trunk/db/mssql/upgrade179.3.sql	2015-01-13 21:48:36 UTC (rev 1294)
@@ -0,0 +1,2 @@
+CREATE NONCLUSTERED INDEX CAEXPIREDONINDEX ON COURSEATTEMPT (COMPANYID,CAEXPIREDON) INCLUDE (USERPRID,COURSID,CACOMPLETEDATE,CASTATUSID)
+