-- first experimental version of a db file system to store files in an 
-- ordered manner with keyword association, categories and versioning
-- very elementary right management for file access
-- aims to become a poor man's DMS one day...

--
-- file tree
--

CREATE SEQUENCE dbfs_files_id_seq INCREMENT BY 1 START WITH 1;

CREATE TABLE dbfs_files (
   ID           OID          DEFAULT nextval('dbfs_files_id_seq') PRIMARY KEY,
   ParentID     OID          NOT NULL,
   FileOwner    TEXT         DEFAULT current_user NOT NULL,
   FileName     TEXT         NOT NULL,
   FileType     CHAR(1)      NOT NULL,
   FileComment  TEXT         DEFAULT '' NOT NULL,

   FOREIGN KEY (ParentID) REFERENCES dbfs_files 
);

CREATE UNIQUE INDEX dbfs_files_unique_name_idx ON dbfs_files (FileName, ParentID);

INSERT INTO dbfs_files (id, parentid, filename, filetype) VALUES( 0, 0, 'ROOT', 'D');
INSERT INTO dbfs_files (    parentid, filename, filetype) VALUES(    0, '',     'D');

--
-- keywords
--

CREATE TABLE dbfs_keywords (
   FileID  OID  NOT NULL,
   Keyword TEXT NOT NULL
);

CREATE INDEX dbfs_keyword_idx ON dbfs_keywords (Keyword);

--
-- recursive path and id resolution
--

CREATE FUNCTION dbfs_path_cat(acc text, instr text) RETURNS text AS $$
  BEGIN
    IF acc IS NULL OR acc = '' THEN
      RETURN instr;
    ELSE
      RETURN instr || '/' || acc;
    END IF;
  END;
$$ LANGUAGE plpgsql;

CREATE AGGREGATE dbfs_path_agg(
  basetype    = text,
  sfunc       = dbfs_path_cat,
  stype       = text,
  initcond    = ''
);

CREATE FUNCTION dbfs_getid(text, integer) RETURNS OID AS $$
   WITH RECURSIVE get_path_id(id, parentid, level) AS (
           SELECT f.id, f.parentid, 1
           FROM dbfs_files f
           WHERE filename = split_part($1, '/', 1)
         UNION
           SELECT f.id, f.parentid, p.level + 1
           FROM dbfs_files f, get_path_id p
           WHERE f.filename = split_part($1, '/', p.level + 1) AND
                 f.parentid = p.id
   )
   SELECT id FROM get_path_id WHERE level = ($2 + 1)
$$ LANGUAGE SQL;

CREATE FUNCTION dbfs_getpath(OID) RETURNS text AS $$
   WITH RECURSIVE get_path(id, parentid, filename) AS (
        SELECT f.id, f.parentid, f.filename
        FROM dbfs_files f
        WHERE id = $1
      UNION
        SELECT f.id, f.parentid, f.filename
        FROM dbfs_files f, get_path p
        WHERE f.id = p.parentid
        AND f.id <> 0
   )
   SELECT dbfs_path_agg(filename) AS pathname FROM get_path;
$$ LANGUAGE SQL;

--
-- user and right management
--

CREATE SEQUENCE dbfs_users_id_seq INCREMENT BY 1 START WITH 1;

CREATE TABLE dbfs_users (
   ID           OID          DEFAULT nextval('dbfs_users_id_seq') PRIMARY KEY,
   Login        TEXT         UNIQUE NOT NULL,
   Name         TEXT         NOT NULL,
   Password     TEXT         NOT NULL
);

CREATE SEQUENCE dbfs_groups_id_seq INCREMENT BY 1 START WITH 1;

CREATE TABLE dbfs_groups (
   ID           OID          DEFAULT nextval('dbfs_groups_id_seq') PRIMARY KEY,
   Name         TEXT         UNIQUE NOT NULL
);

CREATE TABLE dbfs_memberships (
   UserID       OID          NOT NULL,
   GroupID      OID          NOT NULL,

   FOREIGN KEY (UserID)  REFERENCES dbfs_users, 
   FOREIGN KEY (GroupID) REFERENCES dbfs_groups 
);

CREATE INDEX dbfs_membership_user_idx ON dbfs_memberships (UserID);
CREATE INDEX dbfs_membership_group_idx ON dbfs_memberships (GroupID);

CREATE FUNCTION dbfs_userid(TEXT) RETURNS OID AS $$
   SELECT ID FROM dbfs_users WHERE Login = $1
$$ LANGUAGE SQL;

CREATE FUNCTION dbfs_groupid(TEXT) RETURNS OID AS $$
   SELECT ID FROM dbfs_groups WHERE Name = $1
$$ LANGUAGE SQL;

CREATE TABLE dbfs_permissions (
   GroupID      OID          NOT NULL,
   FileID       OID          NOT NULL,
   Access       INTEGER      DEFAULT 0 NOT NULL,

   FOREIGN KEY (GroupID)  REFERENCES dbfs_groups, 
   FOREIGN KEY (FileID)   REFERENCES dbfs_files 
);

CREATE FUNCTION dbfs_addgroup (TEXT) RETURNS VOID AS $$
   INSERT INTO dbfs_groups (Name) VALUES ($1);
   INSERT INTO dbfs_permissions (GroupID, FileID) (
      SELECT dbfs_groupid($1) AS GroupID, id AS FileID 
      FROM  dbfs_files 
      WHERE filetype = 'D' 
      ORDER BY id
   );
$$ LANGUAGE SQL;

CREATE FUNCTION dbfs_delgroup (TEXT) RETURNS VOID AS $$
   DELETE FROM dbfs_permissions WHERE GroupID = dbfs_groupid($1);
   DELETE FROM dbfs_groups WHERE NAME = $1;
$$ LANGUAGE SQL;

CREATE TABLE dbfs_session (
   UserID       OID          NOT NULL,
   GroupID      OID          NOT NULL,
   SessionID    INTEGER      DEFAULT pg_backend_pid() NOT NULL,
   LoginTime    TIMESTAMP    DEFAULT current_timestamp NOT NULL
);

CREATE FUNCTION dbfs_login (TEXT, TEXT) RETURNS INTEGER AS $$
   DELETE FROM dbfs_session 
    WHERE SessionID = pg_backend_pid()
       OR LoginTime < current_timestamp - interval '15 Minutes';

   INSERT INTO dbfs_session (UserID, GroupID) (
      SELECT u.ID AS UserID, g.ID AS GroupID
        FROM  dbfs_users u, dbfs_groups g, dbfs_memberships m
       WHERE u.ID = m.UserID 
         AND g.ID = m.GroupID
         AND u.Login = $1
         AND u.Password = md5($2)
   );

   SELECT CASE WHEN count(GroupID) = 0 THEN 0
               ELSE 1
          END
   FROM dbfs_session
   WHERE SessionID = pg_backend_pid();
$$ LANGUAGE SQL;

CREATE FUNCTION dbfs_logout (TEXT) RETURNS INTEGER AS $$
   DELETE FROM dbfs_session WHERE SessionID = pg_backend_pid();
   SELECT CASE WHEN count(GroupID) = 0 THEN 1
               ELSE 0
          END
   FROM dbfs_session
   WHERE SessionID = pg_backend_pid();
$$ LANGUAGE SQL;

CREATE FUNCTION dbfs_access(OID) RETURNS INTEGER AS $$
   SELECT max(r.access) AS permission 
   FROM dbfs_files f, dbfs_permissions r, dbfs_session s, dbfs_groups g 
   WHERE (
      f.id = $1
      AND f.filetype = 'D' 
      AND r.fileid = f.id
      AND s.groupid = g.id
      AND r.groupid = g.id
      AND s.sessionid = pg_backend_pid()
   )
   OR
   (
      f.id = $1
      AND f.filetype <> 'D'
      AND r.fileid = f.parentid
      AND s.groupid = g.id
      AND r.groupid = g.id
      AND s.sessionid = pg_backend_pid()
   );
$$ LANGUAGE SQL;

SELECT dbfs_addgroup ('admins');
SELECT dbfs_addgroup ('users');

--
-- versioning
--

CREATE TABLE dbfs_filedata (
   FileID       OID          NOT NULL,
   Version      INTEGER      NOT NULL,
   BlobID       OID          NOT NULL,

   FOREIGN KEY (FileID) REFERENCES dbfs_files 
);

CREATE UNIQUE INDEX dbfs_filedata_unique_id ON dbfs_filedata(FileID, Version);

--
-- categories
--

CREATE SEQUENCE dbfs_categories_id_seq INCREMENT BY 1 START WITH 1;

CREATE TABLE dbfs_categories (
   ID           OID          DEFAULT nextval('dbfs_categories_id_seq') PRIMARY KEY,
   Name         TEXT         UNIQUE NOT NULL
);

CREATE TABLE dbfs_belonging (
   FileID       OID          NOT NULL,
   CategoryID   OID          NOT NULL,

   FOREIGN KEY (FileID)     REFERENCES dbfs_files, 
   FOREIGN KEY (CategoryID) REFERENCES dbfs_categories 
);

--
-- functions
--

CREATE FUNCTION dbfs_copy_permissions(OID, OID) RETURNS OID AS $$
   INSERT INTO dbfs_permissions
      SELECT groupid, $2 AS fileid, access 
        FROM dbfs_permissions
       WHERE fileid = $1
   RETURNING $2;
$$ LANGUAGE SQL;

CREATE FUNCTION dbfs_mkdir (OID, TEXT) RETURNS OID AS $$
   INSERT INTO dbfs_files (parentid, filename, filetype, filedata)
     VALUES ($1, $2, 'D', 0)
   RETURNING dbfs_copy_permissions($1, id);
$$ LANGUAGE SQL;

CREATE FUNCTION dbfs_rmdir (OID) RETURNS VOID AS $$
   DELETE FROM dbfs_permissions
    WHERE fileid = $1;
   DELETE FROM dbfs_files 
	 WHERE id = $1;
$$ LANGUAGE SQL;
