[Ils-source] r1360 - trunk/sql

[scribe at crosswire.org]

Author: scribe
Date: 2015-12-03 18:26:15 -0700 (Thu, 03 Dec 2015)
New Revision: 1360

Added:
   trunk/sql/clearrecurrenttraining.properties
Modified:
   trunk/sql/createForumTables.properties
Log:
added new script to clear recurrent training data
updated forum table creation script to use sql server syntax instead of mysql syntax


Added: trunk/sql/clearrecurrenttraining.properties
===================================================================
--- trunk/sql/clearrecurrenttraining.properties	                        (rev 0)
+++ trunk/sql/clearrecurrenttraining.properties	2015-12-04 01:26:15 UTC (rev 1360)
@@ -0,0 +1,11 @@
+Category=Student Records
+Name=Clear Recurrent Training
+Description=This statement clears recurrent training for a user
+#Requires=CompanyID
+
+Heading=Select a profile for which to clear all recurrent training
+#SQL=SELECT * FROM USERPROFILE WHERE COMPANYID={CompanyID}
+SQL=SELECT * FROM USERPROFILE WHERE COMPANYID=1
+LinkColumn=USERPRID
+#SQL2=update COURSEATTEMPT set CAEXPIREDON=NULL WHERE USERPRID={0} AND COMPANYID={CompanyID} and CAEXPIREDON is not NULL
+SQL2=update COURSEATTEMPT set CAEXPIREDON=NULL, CAWARNEDON1=NULL, CAWARNEDON2=NULL, CAWARNEDON3=NULL, CAWARNEDON4=NULL WHERE USERPRID={0} AND COMPANYID=1 and CAEXPIREDON is not NULL

Modified: trunk/sql/createForumTables.properties
===================================================================
--- trunk/sql/createForumTables.properties	2015-12-04 01:23:38 UTC (rev 1359)
+++ trunk/sql/createForumTables.properties	2015-12-04 01:26:15 UTC (rev 1360)
@@ -3,24 +3,24 @@
 Description=Create all Forum Tables
 
 SQL=CREATE TABLE FORUMMESSAGE (		\
-FORUMMESSAGEID INTEGER NOT NULL AUTO_INCREMENT,	\
+FORUMMESSAGEID INTEGER NOT NULL IDENTITY(1,1),	\
 FORUMTOPICID INTEGER NOT NULL,	\
 SITEID VARCHAR(128),	\
-CREATEDATE TIMESTAMP DEFAULT NOW(),		\
+CREATEDATE DATETIME DEFAULT CURRENT_TIMESTAMP,		\
 FROMUSERID VARCHAR(256),			\
 SUBJECT VARCHAR(256),			\
 LIKES INTEGER NOT NULL DEFAULT 0,	\
 VIEWS INTEGER NOT NULL DEFAULT 0,	\
-APPROVED BOOLEAN DEFAULT FALSE,	\
-BODY TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci,	\
+APPROVED BIT DEFAULT 0,	\
+BODY TEXT,	\
 CONSTRAINT FORUMMESSAGEPK1 PRIMARY KEY (FORUMMESSAGEID))
 
 SQL2=CREATE TABLE FORUMTOPIC (		\
-FORUMTOPICID INTEGER NOT NULL AUTO_INCREMENT,	\
+FORUMTOPICID INTEGER NOT NULL IDENTITY(1,1),	\
 FORUMTOPICNAME VARCHAR(256),			\
 COURSEID INTEGER NOT NULL,	\
 COURSENAME VARCHAR(256),	\
-REQUIREAPPROVAL BOOLEAN,	\
+REQUIREAPPROVAL BIT,	\
 CONSTRAINT FORUMTOPICPK1 PRIMARY KEY (FORUMTOPICID))
 
 SQL3=CREATE INDEX FORUMTOPICCOURSEID ON FORUMTOPIC (COURSEID, FORUMTOPICID)