DROP VIEW PROFILED;

DROP TABLE EDIT_RIGHTS;
DROP TABLE DOC_RIGHTS;

DROP TABLE UD_EDIT;
DROP TABLE PROFILED_BY;
DROP TABLE UD_PROFILE;

DROP TABLE UD;

DROP TABLE MEMBER;
DROP TABLE USER;

CREATE TABLE USER (userid VARCHAR PRIMARY KEY, password VARCHAR NULL, name VARCHAR NULL, type CHAR(1), contact VARCHAR NULL);
CREATE TABLE MEMBER (userid VARCHAR PRIMARY KEY, grpid VARCHAR NULL);

CREATE TABLE UD(udid INT IDENTITY, userid VARCHAR NOT NULL, docid VARCHAR NOT NULL, FOREIGN KEY (userid) REFERENCES USER (userid));

CREATE TABLE DOC_RIGHTS (udid INT PRIMARY KEY, rights CHAR(1) DEFAULT '-' NOT NULL, FOREIGN KEY (udid) REFERENCES UD (udid));
CREATE TABLE EDIT_RIGHTS (userid INT, authorid INT, rights CHAR(1) DEFAULT '-' NOT NULL, FOREIGN KEY (userid) REFERENCES USER (userid), FOREIGN KEY (authorid) REFERENCES USER (userid));

CREATE TABLE UD_PROFILE (profileid INT IDENTITY, udid INT, FOREIGN KEY (udid) REFERENCES UD (udid));

CREATE TABLE PROFILED_BY(profileid INT, userid VARCHAR NOT NULL, FOREIGN KEY (profileid) REFERENCES UD_PROFILE (profileid), FOREIGN KEY (userid) REFERENCES USER (userid));

CREATE TABLE UD_EDIT(editid INT IDENTITY, udid INT, created TIMESTAMP DEFAULT 'now', editdocid VARCHAR NOT NULL, comment VARCHAR NULL, FOREIGN KEY (udid) REFERENCES UD (udid));

CREATE VIEW PROFILED AS
SELECT UD.userid userid, UD.docid docid, PROFILED_BY.userid profuserid
FROM UD, UD_PROFILE, PROFILED_BY
WHERE UD.udid=UD_PROFILE.udid 
   AND UD_PROFILE.profileid=PROFILED_BY.profileid;
   
CREATE VIEW UD_RIGHTS AS
SELECT UD.userid userid, UD.docid docid, DOC_RIGHTS.rights rights
FROM UD JOIN DOC_RIGHTS ON WHERE UD.udid=DOC_RIGHTS.udid;
   
