crea_base.sql 11.3 KB
Newer Older
Florent Chehab's avatar
Florent Chehab committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303
DROP TABLE IF EXISTS Users CASCADE;
DROP TABLE IF EXISTS Groups CASCADE;
DROP TABLE IF EXISTS UserGroupAssocations CASCADE;
DROP TABLE IF EXISTS UserGroupAssocations CASCADE;
DROP TABLE IF EXISTS Forms CASCADE;
DROP TABLE IF EXISTS GroupFormRelations CASCADE;
DROP TABLE IF EXISTS FormAccess CASCADE;
DROP TABLE IF EXISTS FormSubmissions CASCADE;
DROP TABLE IF EXISTS FormSubmitted CASCADE;



CREATE TABLE Users(
	userId SERIAL PRIMARY KEY,
	login VARCHAR(255) UNIQUE NOT NULL, --CAS user login
	email VARCHAR(255), --Email of the user
	type VARCHAR(255), --type of the user given by the CAS system
	language VARCHAR(10)
);


CREATE TABLE Groups(
	groupId SERIAL PRIMARY KEY,
	title VARCHAR(255) NOT NULL,
	creator INTEGER REFERENCES Users(userId),
	active BOOLEAN,
	type VARCHAR(50) --user
);
INSERT INTO Groups(title,type) VALUES ('MASTER ADMINS','APP ESSENTIALS');
INSERT INTO Groups(title,type) VALUES ('Everyone','APP ESSENTIALS');


DROP RULE IF EXISTS preventDeleteMasterAdmins on Groups;
CREATE RULE preventDeleteMasterAdmins --PRevent dumb deletion...
	AS ON DELETE TO Groups
	WHERE (OLD.groupId = 1 or OLD.groupId=2)
	DO INSTEAD NOTHING;


CREATE TABLE UserGroupAssocations(
		assocId SERIAL PRIMARY KEY,
		userId INTEGER REFERENCES Users(userId) NOT NULL,
		groupId INTEGER REFERENCES Groups(groupId) NOT NULL,
		UNIQUE(userId,groupId)
);


CREATE TABLE Forms(
	formId SERIAL PRIMARY KEY,
	title VARCHAR(255) NOT NULL,
	infoJSON JSON,
	creator INTEGER REFERENCES Users(userId) NOT NULL,
	formJSON JSON,
	draft BOOLEAN NOT NULL,
	creationDate TIMESTAMP,
	lastModificationDate TIMESTAMP,
	closeDate TIMESTAMP,
	publicResult BOOLEAN NOT NULL,
	publicJSONsource BOOLEAN NOT NULL,
	anonymous SMALLINT NOT NULL,
	personnalInfo SMALLINT NOT NULL,
	finalResult TEXT, --csv actually
	tmp BOOLEAN NOT NULL,
	CHECK(anonymous=0 OR anonymous=1 OR anonymous=2)
);

DROP FUNCTION IF EXISTS updateFormTime() CASCADE;
CREATE FUNCTION updateFormTime()
  RETURNS trigger AS'
			BEGIN
				NEW.lastModificationDate=now();
			 RETURN NEW;
			END' LANGUAGE 'plpgsql';

DROP TRIGGER IF EXISTS formsUpdate on Forms;
CREATE TRIGGER formsUpdate --to make sure forms lastModificationDate is correct
		  BEFORE UPDATE
		  ON Forms
		  FOR EACH ROW
			WHEN (
	      OLD.infoJSON::text IS DISTINCT FROM NEW.infoJSON::text OR
				OLD.formJSON::text IS DISTINCT FROM NEW.formJSON::text OR
				OLD.title IS DISTINCT FROM NEW.title OR
				OLD.draft IS DISTINCT FROM NEW.draft OR
				OLD.closeDate IS DISTINCT FROM NEW.closeDate OR
				OLD.publicResult IS DISTINCT FROM NEW.publicResult OR
				OLD.publicJSONsource IS DISTINCT FROM NEW.publicJSONsource OR
				OLD.anonymous IS DISTINCT FROM NEW.anonymous OR
				OLD.finalResult IS DISTINCT FROM NEW.finalResult)
		  EXECUTE PROCEDURE updateFormTime();


CREATE TABLE GroupFormRelations(
	relId SERIAL PRIMARY KEY,
	groupId INTEGER REFERENCES Groups(groupId) NOT NULL,
	formId INTEGER REFERENCES Forms(formId) NOT NULL,
	canAdministrate BOOLEAN NOT NULL,
	canSeeResults BOOLEAN NOT NULL,
	UNIQUE(groupId,formId)
);


CREATE TABLE FormAccess(
	accessId SERIAL PRIMARY KEY,
	groupId INTEGER REFERENCES Groups(groupId) NOT NULL,
	formId INTEGER REFERENCES Forms(formId) NOT NULL,
	UNIQUE(groupId,formId)
);

CREATE TABLE FormSubmissions(
	submissionId SERIAL PRIMARY KEY,
	formId INTEGER REFERENCES Forms(formId) NOT NULL,
	uId VARCHAR(255) NOT NULL,
	answers JSON NOT NULL,
	results JSON NOT NULL,
	userInfo JSON NOT NULL,
	draft BOOLEAN NOT NULL,
	UNIQUE (formId,uId)
);

DROP FUNCTION IF EXISTS updateFormSubmissionStatus() CASCADE;
CREATE FUNCTION updateFormSubmissionStatus()
  RETURNS trigger AS'
			BEGIN
				UPDATE Forms SET tmp=false WHERE formId=OLD.formId;
			 RETURN NEW;
			END' LANGUAGE 'plpgsql';

DROP TRIGGER IF EXISTS FormSubmissionsUpdate on FormSubmissions;
CREATE TRIGGER FormSubmissionsUpdate --to make sure forms lastModificationDate is correct
		  AFTER UPDATE
		  ON FormSubmissions
		  FOR EACH ROW
			WHEN (OLD.answers::text IS DISTINCT FROM NEW.answers::text OR
	      OLD.draft IS DISTINCT FROM NEW.draft)
		  EXECUTE PROCEDURE updateFormSubmissionStatus();



CREATE TABLE FormSubmitted(
	Id SERIAL PRIMARY KEY,
	userId INTEGER REFERENCES Users(userId) NOT NULL,
	formId INTEGER REFERENCES Forms(formId) NOT NULL,
	submissionId INTEGER REFERENCES FormSubmissions(submissionId), -- can be NULL if anonymous
	UNIQUE (userId,formId)
);
CREATE UNIQUE INDEX simpleCheck ON FormSubmitted (submissionId)
WHERE submissionId IS NOT NULL;




-- TRIGGERS
DROP FUNCTION IF EXISTS checkGroupExist(varchar) CASCADE;
CREATE FUNCTION checkGroupExist(varchar) RETURNS bigint
    AS 'select count(groupId) from groups where groups.title = $1 AND groups.creator is Null;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

DROP FUNCTION IF EXISTS findGroupId(varchar) CASCADE;
CREATE FUNCTION findGroupId(varchar) RETURNS integer
		AS 'select groupId from groups where groups.title = $1 LIMIT 1'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;


DROP FUNCTION IF EXISTS createGroupOnUserInsert() CASCADE;
CREATE FUNCTION createGroupOnUserInsert()
  RETURNS trigger AS
	$BODY$
			BEGIN
			 INSERT INTO UserGroupAssocations(userId,groupId) VALUES(NEW.userId,2);
			 IF NEW.type IS NULL OR findGroupId(NEW.type)=1 THEN
			 ELSIF checkGroupExist(NEW.type)=0 THEN
			 INSERT INTO Groups(title,type) VALUES(NEW.type,'CAS');
			 INSERT INTO UserGroupAssocations(userId,groupId) VALUES(NEW.userId,findGroupId(NEW.type));
			 END IF;
			 RETURN NEW;
			END;
			$BODY$
			 LANGUAGE 'plpgsql';



DROP TRIGGER IF EXISTS userInsert on Users;
CREATE TRIGGER userInsert
		  AFTER INSERT
		  ON Users
		  FOR EACH ROW
		  EXECUTE PROCEDURE createGroupOnUserInsert();


DROP FUNCTION IF EXISTS updateGroupOnUserUpdate() CASCADE;
CREATE FUNCTION updateGroupOnUserUpdate()
  RETURNS trigger AS
			$BODY$
			BEGIN
			IF OLD.type IS NULL THEN
				ELSIF findGroupId(OLD.type)!=1 THEN
				 DELETE FROM UserGroupAssocations WHERE userId=OLD.userId AND groupId=findGroupId(OLD.type);
			END IF;

			IF NEW.type IS NULL OR findGroupId(NEW.type)=1 THEN
			ELSIF checkGroupExist(NEW.type)=0 THEN
				INSERT INTO Groups(title,type) VALUES(NEW.type,'CAS');
				INSERT INTO UserGroupAssocations(userId,groupId) VALUES(NEW.userId,findGroupId(NEW.type));
			ELSE INSERT INTO UserGroupAssocations(userId,groupId) VALUES(NEW.userId,findGroupId(NEW.type));
			END IF;
			 RETURN NEW;
			END;
			$BODY$
			 LANGUAGE 'plpgsql';

DROP TRIGGER IF EXISTS userUpdate on Users;
CREATE TRIGGER userUpdate --to make sure groups are  up to date
		  BEFORE UPDATE
		  ON Users
		  FOR EACH ROW
		  EXECUTE PROCEDURE updateGroupOnUserUpdate();


--------------------------------------------------
--------------------------------------------------
-- views for easy managment
DROP VIEW IF EXISTS draftForms;
Create view draftForms AS
select formId, title, creator from forms where draft=true;


DROP VIEW IF EXISTS getInspiredByEveryone;
Create view getInspiredByEveryone AS
select forms.creator, users.login, forms.title, forms.formid from forms, users where users.userid = forms.creator AND forms.draft=false AND publicJSONsource=true ORDER BY forms.title ASC;


DROP VIEW IF EXISTS getInspiredByYourself;
Create view getInspiredByYourself AS
select forms.creator, forms.title, forms.formid from forms where forms.draft=false ORDER BY forms.title ASC;


DROP VIEW IF EXISTS mainGroups;
Create view mainGroups AS
select groups.groupId, groups.title from Groups where ((groups.type='CAS' OR groups.type = 'APP ESSENTIALS') AND groups.groupId != 1);

DROP VIEW IF EXISTS otherAdministrativeGroups;
Create view otherAdministrativeGroups AS
select groups.groupId, groups.title, UserGroupAssocations.userid from Groups, UserGroupAssocations where ((groups.type='SUB-CAS' OR groups.type = 'UVS') AND groups.groupId != 1 AND UserGroupAssocations.groupId = Groups.groupId) ;

DROP VIEW IF EXISTS activeGroups;
Create view activeGroups AS
select * from Groups where groups.active = true AND groups.title != '';

DROP VIEW IF EXISTS unActiveGroups;
Create view unActiveGroups AS
select * from Groups where groups.active = false;

DROP VIEW IF EXISTS yourGroups;
Create view yourGroups AS
select activeGroups.groupId, activeGroups.title, activeGroups.creator, UserGroupAssocations.userid, users.login from activeGroups, UserGroupAssocations, Users where (activeGroups.type='' AND UserGroupAssocations.groupId = activeGroups.groupId AND UserGroupAssocations.userid = Users.userId);



DROP VIEW IF EXISTS groupsAndParticipantsLogin;
Create view groupsAndParticipantsLogin AS
select groups.groupId, users.login, Groups.creator, groups.active, users.userid, groups.title from Groups, UserGroupAssocations, Users where (UserGroupAssocations.groupId = Groups.groupId AND UserGroupAssocations.userid = Users.userId AND Groups.type = '' AND Groups.title !='');



DROP VIEW IF EXISTS groupCreator;
Create view groupCreator AS
select formId, creator from forms;


DROP VIEW IF EXISTS groupCanAdministrate;
Create view groupCanAdministrate AS
select GroupFormRelations.formId, UserGroupAssocations.userid, Groups.groupId, Groups.title AS groupTitle, forms.creator, forms.title AS formTitle from Forms, GroupFormRelations,UserGroupAssocations, Groups WHERE Forms.formid = GroupFormRelations.formid AND GroupFormRelations.canAdministrate=true AND GroupFormRelations.groupId = groups.groupid AND UserGroupAssocations.groupId = groups.groupId;


DROP VIEW IF EXISTS groupCanSeeResults;
Create view groupCanSeeResults AS
select GroupFormRelations.formId, UserGroupAssocations.userid, Groups.groupId, Groups.title AS groupTitle, forms.creator, forms.title AS formTitle from Forms, GroupFormRelations,UserGroupAssocations, Groups WHERE Forms.formid = GroupFormRelations.formid AND (GroupFormRelations.canSeeResults = true OR GroupFormRelations.canAdministrate=true)  AND GroupFormRelations.groupId = groups.groupid AND UserGroupAssocations.groupId = groups.groupId;

DROP VIEW IF EXISTS groupCanRights CASCADE;
Create view groupCanRights AS
select GroupFormRelations.formId, UserGroupAssocations.userid, sum(UserGroupAssocations.userid)<0 as creator, sum(GroupFormRelations.canAdministrate::int)>0 AS canAdministrate, sum( (GroupFormRelations.canAdministrate OR GroupFormRelations.canSeeResults)::int)>0 AS canSeeResults from Forms, GroupFormRelations,UserGroupAssocations, Groups WHERE Forms.formid = GroupFormRelations.formid AND (GroupFormRelations.canSeeResults = true OR GroupFormRelations.canAdministrate=true)  AND GroupFormRelations.groupId = groups.groupid AND UserGroupAssocations.groupId = groups.groupId GROUP BY GroupFormRelations.formId, UserGroupAssocations.userid;

DROP VIEW IF EXISTS dumbCreatorVue CASCADE;
Create view dumbCreatorVue AS
select Forms.formId, Forms.creator as userid,  1>0 as creator, 1>0 as canAdministrate, 1>0 as canSeeResults from Forms;

DROP View IF EXISTS unionOfRights CASCADE;
Create view unionOfRights AS
select * from groupCanRights UNION select * from dumbCreatorVue;

DROP VIEW IF EXISTS userFormRightsRecap CASCADE;
Create view userFormRightsRecap AS
SELECT userid, formid, sum(creator::int)>0  AS isCreator, sum(canAdministrate::int)>0 AS canAdministrate, sum(canSeeResults::int)>0 AS canSeeResults FROM unionOfRights GROUP BY userid, formid;


DROP VIEW IF EXISTS groupCanAnswerForm;
Create view groupCanAnswerForm AS
select FormAccess.formId, UserGroupAssocations.userid, Groups.groupId, Groups.title from FormAccess, UserGroupAssocations, Groups WHERE FormAccess.groupid = Groups.groupid AND UserGroupAssocations.groupId = groups.groupId;