/* vkcdb views */


/* users of given group */
create view V_GROUPUSERS (GROUPID, GROUPNAME, USERID, USERNAME, FIRSTNAME, LASTNAME, CREATOR, CREATED)
         as select GROUPS.GROUPID, GROUPS.NAME, USERS.USERID, USERS.USERNAME, USERS.FIRSTNAME, USERS.LASTNAME, USERS2.USERNAME, USERGROUPS.CREATED
              from GROUPS, USERGROUPS, USERS, USERS USERS2
             where GROUPS.GROUPID = USERGROUPS.GROUPID
               and USERGROUPS.USERID = USERS.USERID
               and USERGROUPS.CREATORID = USERS2.USERID;

/* groups of given user */
create view V_USERGROUPS (USERID, USERNAME, GROUPID, GROUPNAME, CREATOR, CREATED)
         as select USERS.USERID, USERS.USERNAME, GROUPS.GROUPID, GROUPS.NAME, USERS2.USERNAME, USERGROUPS.CREATED 
              from USERS, USERGROUPS, GROUPS, USERS USERS2
             where GROUPS.GROUPID = USERGROUPS.GROUPID
               and USERGROUPS.USERID = USERS.USERID
               and USERGROUPS.CREATORID = USERS2.USERID;

/* show documents of given category, only original docs, no links, only created documents*/
create view V_CATEGORYDOCUMENTS (CATEGORYID, CATEGORYNAME, PARENTCATEGORYID, PARENTCATEGORYNAME, DOCUMENTID, DOCUMENTNAME, DOCVERSION, DOCAUTHOR, DOCPUBLISHED, DOCLASTCHANGER, DOCLASTCHANGED, DOCACCESSED)
         as select CATEGORIES.CATEGORYID, CATEGORIES.NAME, PARENTCATEGORIES.CATEGORYID, PARENTCATEGORIES.NAME, DOCUMENTS.DOCUMENTID, DOCUMENTS.VERSION_HIGH || '.' || DOCUMENTS.VERSION_LOW, DOCUMENTS.NAME, USERS.USERNAME, DOCUMENTS.PUBLISHED, USERS2.USERID, DOCUMENTS.LASTCHANGED, DOCUMENTS.ACCESSCOUNTER
              from CATEGORIES, CATEGORIES PARENTCATEGORIES, CATEGORYDOCUMENTS, DOCUMENTS, USERS, USERS USERS2
             where CATEGORIES.CATEGORYID = CATEGORYDOCUMENTS.CATEGORYID
               and CATEGORYDOCUMENTS.DOCUMENTID = DOCUMENTS.DOCUMENTID
               and CATEGORYDOCUMENTS.INITIALCATEGORY = 1
               and DOCUMENTS.FLAG = 0
               and CATEGORIES.PARENTCATEGORYID = PARENTCATEGORIES.CATEGORYID
               and DOCUMENTS.AUTHORID = USERS.USERID
               and DOCUMENTS.LASTCHANGERID = USERS2.USERID;

/* show linked documents of given category, only original docs, only created documents*/
create view V_CATEGORYDOCUMENTS (CATEGORYID, CATEGORYNAME, PARENTCATEGORYID, PARENTCATEGORYNAME, LINKCREATOR, LINKCREATED, DOCUMENTID, DOCUMENTNAME, DOCVERSION, DOCAUTHOR, DOCPUBLISHED, LASTCHANGER, LASTCHANGED, ACCESSED)
         as select CATEGORIES.CATEGORYID, CATEGORIES.NAME, PARENTCATEGORIES.CATEGORYID, PARENTCATEGORIES.NAME, USERS3.USERID, CATEGORYDOCUMENTS.CREATED, DOCUMENTS.DOCUMENTID, DOCUMENTS.NAME, DOCUMENTS.VERSION_HIGH || '.' || DOCUMENTS.VERSION_LOW, USERS.USERNAME, DOCUMENTS.PUBLISHED, USERS2.USERID, DOCUMENTS.LASTCHANGED, DOCUMENTS.ACCESSCOUNTER
              from CATEGORIES, CATEGORIES PARENTCATEGORIES, CATEGORYDOCUMENTS, DOCUMENTS, USERS, USERS USERS2, USERS USERS3
             where CATEGORIES.CATEGORYID = CATEGORYDOCUMENTS.CATEGORYID
               and CATEGORYDOCUMENTS.DOCUMENTID = DOCUMENTS.DOCUMENTID
               and CATEGORYDOCUMENTS.INITIALCATEGORY = 0
               and DOCUMENTS.FLAG = 0
               and CATEGORIES.PARENTCATEGORYID = PARENTCATEGORIES.CATEGORYID
               and DOCUMENTS.AUTHORID = USERS.USERID
               and DOCUMENTS.LASTCHANGERID = USERS2.USERID
               and CATEGORYDOCUMENTS.CREATORID = USERS3.USERID;

/* show keywords belonging to a category */
create view V_CATEGORYKEYWORDS (CATEGORYID, CATEGORYNAME, KEYWORDID, KEYWORD)
         as select CATEGORIES.CATEGORYID, CATEGORIES.NAME, KEYWORDS.KEYWORDID, KEYWORDS.KEYWORD
              from CATEGORIES, CATEGORYKEYWORDS, KEYWORDS
             where CATEGORIES.CATEGORYID = CATEGORYKEYWORDS.CATEGORYID
               and CATEGORYKEYWORDS.KEYWORDID = KEYWORD.KEYWORDID;

/* show user acls of given category */
/* evt last changed .. */
create view V_CATEGORYUSERACLS (CATEGORYID, CATEGORYNAME, ACLTYPE, USERID, USERNAME, PERMISSIONID, TOKEN, PRINTSTRING)
         as select CATEGORIES.CATEGORYID, CATEGORIES.NAME, ACLTYPES.NAME, USERS.USERID, USERS.USERNAME, PERMISSIONS.PERMISSIONID, PERMISSIONS.TOKEN, PERMISSIONS.PRINTSTRING
              from CATEGORIES, CATEGORYACLS, PERMISSIONS, USERS, ACLTYPES
             where CATEGORIES.ACLID = CATEGORYACLS.ACLID
               and CATEGORYACLS.ACLTYPEID = 1
               and CATEGORYACLS.ACLTYPEID = ACLTYPES.ACLTYPEID
               and CATEGORYACLS.ACCESSORID = USERS.USERID
               and CATEGORYACLS.PERMISSIONID = PERMISSIONS.PERMISSIONID;

/* show group acls of given category */
create view V_CATEGORYGROUPACLS (CATEGORYID, CATEGORYNAME, ACLTYPE, GROUPID, GROUPNAME, PERMISSIONID, TOKEN, PRINTSTRING)
         as select CATEGORIES.CATEGORYID, CATEGORIES.NAME, ACLTYPES.NAME, GROUPS.GROUPID, GROUPS.NAME, PERMISSIONS.PERMISSIONID, PERMISSIONS.TOKEN, PERMISSIONS.PRINTSTRING
              from CATEGORIES, CATEGORYACLS, PERMISSIONS, GROUPS, ACLTYPES
             where CATEGORIES.ACLID = CATEGORYACLS.ACLID
               and CATEGORYACLS.ACLTYPEID = 2
               and CATEGORYACLS.ACLTYPEID = ACLTYPES.ACLTYPEID
               and CATEGORYACLS.ACCESSORID = GROUPS.USERID
               and CATEGORYACLS.PERMISSIONID = PERMISSIONS.PERMISSIONID;

/* show anyother acls of given category */
create view V_CATEGORYANYACLS (CATEGORYID, CATEGORYNAME, ACLTYPE, GROUPID, GROUPNAME, PERMISSIONID, TOKEN, PRINTSTRING)
         as select CATEGORIES.CATEGORYID, CATEGORIES.NAME, ACLTYPES.NAME, GROUPS.GROUPID, GROUPS.NAME, PERMISSIONS.PERMISSIONID, PERMISSIONS.TOKEN, PERMISSIONS.PRINTSTRING
              from CATEGORIES, CATEGORYACLS, PERMISSIONS, ACLTYPES
             where CATEGORIES.ACLID = CATEGORYACLS.ACLID
               and CATEGORYACLS.ACLTYPEID = 3
               and CATEGORYACLS.ACLTYPEID = ACLTYPES.ACLTYPEID
               and CATEGORYACLS.PERMISSIONID = PERMISSIONS.PERMISSIONID;

/* show mask acls of given category */
create view V_CATEGORYMASKACLS (CATEGORYID, CATEGORYNAME, ACLTYPE, GROUPID, GROUPNAME, PERMISSIONID, TOKEN, PRINTSTRING)
         as select CATEGORIES.CATEGORYID, CATEGORIES.NAME, ACLTYPES.NAME, GROUPS.GROUPID, GROUPS.NAME, PERMISSIONS.PERMISSIONID, PERMISSIONS.TOKEN, PERMISSIONS.PRINTSTRING
              from CATEGORIES, CATEGORYACLS, PERMISSIONS, ACLTYPES
             where CATEGORIES.ACLID = CATEGORYACLS.ACLID
               and CATEGORYACLS.ACLTYPEID = 4
               and CATEGORYACLS.ACLTYPEID = ACLTYPES.ACLTYPEID
               and CATEGORYACLS.PERMISSIONID = PERMISSIONS.PERMISSIONID;

/* show user acls for a given group */
create view V_GROUPACLS (GROUPID, GROUPNAME, ACLTYPE, USERID, USERNAME, PERMISSIONID, TOKEN, PRINTSTRING)
         as select GROUPS.GROUPID, GROUPS.NAME, ACLTYPES.NAME, USERS.USERID, USERS.USERNAME, PERMISSIONS.PERMISSIONID, PERMISSIONS.TOKEN, PERMISSIONS.PRINTSTRING
              from GROUPS, GROUPACLS, PERMISSIONS, USERS, ACLTYPES
             where GROUPS.ACLID = GROUPACLS.ACLID
               and GROUPACLS.ACLTYPEID = 1
               and GROUPACLS.ACLTYPEID = ACLTYPES.ACLTYPEID
               and GROUPACLS.ACCESSORID = USERS.USERID
               and GROUPACLS.PERMISSIONID = PERMISSIONS.PERMISSIONID;

/* show all keywords for which a user has registered */
create view V_USERREGISTEREDKEYWORDS (USERID, USERNAME, KEYOWRDID, KEYWORD)
         as select USERS.USERID, USERS.USERNAME, KEYWORDS.KEYWORDID, KEYWORDS.KEYWORD
              from USERS, USERREGISTEREDKEYWORDS, KEYWORDS
             where USERS.USERID = USERREGISTEREDKEYWORDS.USERID
               and USERREGISTEREDKEYWORDS.USERID = KEYWORDS.KEYWORDID;

/* show all categories for which a user has registered */
create view V_USERREGISTEREDCATEGORIES (USERID, USERNAME, CATEGORYID, CATEGORYNAME)
         as select USERS.USERID, USERS.USERNAME, CATEGORIES.CATEGORYID, CATEGORIEKEYWORDS.KEYWORD
              from USERS, USERREGISTEREDCATEGORIES, CATEGORIES
             where USERS.USERID = USERREGISTEREDCATEGORIES.USERID
               and USERREGISTEREDCATEGORIES.CATEGORYID = CATEGORIES.CATEGORYID;

/* show all users which have been registered for a categories */
create view V_USERREGISTEREDCATEGORIES (CATEGORYID, CATEGORYNAME, USERID, USERNAME)
         as select CATEGORIES.CATEGORYID, CATEGORIEKEYWORDS.KEYWORD, USERS.USERID, USERS.USERNAME
              from USERS, USERREGISTEREDCATEGORIES, CATEGORIES
             where USERS.USERID = USERREGISTEREDCATEGORIES.USERID
               and USERREGISTEREDCATEGORIES.CATEGORYID = CATEGORIES.CATEGORYID;
