-- $Id: schema-1.1.psql,v 1.1 2007-12-30 14:50:59 rafi Exp $
--
-- This is part of WebStorage
-- Copyright (c) 2005,2007 by Rafael Ostertag
--
--
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; either version 2 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
--
--
-- Use this PostgreSQL database schema when setting up WebStorage from
-- scratch. If you are upgrading from an earlier version of WebStorage
-- ajdust config.php to point to the database built from the old schema.
-- You will be redirected to a page which upgrades the existing database.
-- However, you are strongly encouraged to backup your data before performing
-- an upgrade of the database structure. 
--
--
-- Change those variables according your needs
--

\set database 'webstorage'

\set groupname 'webstorage'

--
-- Stop changing things from here
--

CREATE GROUP :groupname;

CREATE DATABASE :database WITH TEMPLATE = template0 ENCODING = 'LATIN1';


\connect :database

--
-- Tables
--

CREATE TABLE dircontent (
    directory_id int NOT NULL,
    content_id int NOT NULL,
    directory boolean NOT NULL
) WITHOUT OIDS;

CREATE TABLE directory (
    directory_id serial NOT NULL,
    name character(255) NOT NULL,
    modified timestamp with time zone NOT NULL,
    created timestamp with time zone NOT NULL
) WITHOUT OIDS;

CREATE TABLE file (
    file_id serial NOT NULL,
    name character(255) NOT NULL,
    "comment" character varying(1024),
    size integer NOT NULL,
    modified timestamp with time zone NOT NULL,
    created timestamp with time zone NOT NULL,
    "type" character(128) NOT NULL,
    file_oid oid
) WITHOUT OIDS;

CREATE TABLE activity (
    ts timestamp with time zone DEFAULT now() NOT NULL,
    who character(128) DEFAULT "current_user"() NOT NULL,
    activity character(64) NOT NULL,
    name character(255) NOT NULL
) WITHOUT OIDS;

--
-- Indices
--
-- We don''t need indices on the file_id and directory_id since
-- those are provided by the unique keys
CREATE INDEX directory_name_idx ON directory USING btree (name);
CREATE INDEX dircontent_content_id_idx ON dircontent USING btree (content_id);

--
-- Primary keys
--
ALTER TABLE directory
	ADD CONSTRAINT directory_directory_id_pkey PRIMARY KEY(directory_id);
ALTER TABLE file
	ADD CONSTRAINT file_file_id_pkey PRIMARY KEY(file_id);

--
-- Constraints
--
ALTER TABLE dircontent
    ADD CONSTRAINT dircontent_directory_id_content_id_directory_ukey UNIQUE (directory_id, content_id, directory);
ALTER TABLE directory
  ADD CONSTRAINT directory_directory_id_ukey UNIQUE(directory_id);
ALTER TABLE file
    ADD CONSTRAINT file_file_id_ukey UNIQUE (file_id);
ALTER TABLE dircontent
	ADD CONSTRAINT dircontent_directory_id_fkey
	FOREIGN KEY (directory_id) 
	REFERENCES directory (directory_id) 
	MATCH SIMPLE 
	ON DELETE RESTRICT 
	ON UPDATE NO ACTION;

--
-- Access permissions
--
REVOKE ALL ON TABLE file_file_id_seq FROM PUBLIC;
REVOKE ALL ON TABLE file FROM PUBLIC;
REVOKE ALL ON TABLE directory_directory_id_seq FROM PUBLIC;
REVOKE ALL ON TABLE directory FROM PUBLIC;
REVOKE ALL ON TABLE dircontent FROM PUBLIC;
REVOKE ALL ON TABLE activity FROM PUBLIC;
GRANT ALL ON TABLE file_file_id_seq TO GROUP :groupname;
GRANT ALL ON TABLE file TO GROUP :groupname;
GRANT ALL ON TABLE directory_directory_id_seq TO GROUP :groupname;
GRANT ALL ON TABLE directory TO GROUP :groupname;
GRANT ALL ON TABLE dircontent TO GROUP :groupname;
GRANT INSERT,SELECT ON TABLE activity TO GROUP :groupname;

--
-- Function handlers
--
CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
    AS '$libdir/plpgsql', 'plpgsql_call_handler'
    LANGUAGE c;
CREATE FUNCTION plpgsql_validator(oid) RETURNS void
    AS '$libdir/plpgsql', 'plpgsql_validator'
    LANGUAGE c;
CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler VALIDATOR plpgsql_validator;

--
-- Trigger functions
--

-- Logs directory activities 
CREATE OR REPLACE FUNCTION directory_activity() RETURNS "trigger"
    AS $$BEGIN
	IF TG_OP='INSERT'
	THEN
		INSERT INTO activity (activity,name) VALUES ('Directory Addition',NEW.name);
	END IF;

	IF TG_OP='DELETE'
	THEN
		INSERT INTO activity (activity,name) VALUES ('Directory Deletion',OLD.name);
	END IF;

	IF TG_OP='UPDATE'
	THEN
		IF OLD.name != NEW.name
		THEN
			INSERT INTO activity (activity,name) VALUES ('Directory Rename',OLD.name || ' to ' || NEW.name);
		END IF;
	END IF;
	
	RETURN NULL;
END;$$
    LANGUAGE plpgsql;
    
-- Logs file activities
CREATE OR REPLACE FUNCTION file_activity() RETURNS "trigger"
    AS $$BEGIN
	IF TG_OP='INSERT'
	THEN
		INSERT INTO activity (activity,name) VALUES ('File Addition',NEW.name);
	END IF;

	IF TG_OP='DELETE'
	THEN
		INSERT INTO activity (activity,name) VALUES ('File Deletion',OLD.name);
	END IF;

	IF TG_OP='UPDATE'
	THEN
		IF OLD.name <> NEW.name
		THEN
			INSERT INTO activity (activity,name) VALUES ('File Rename', OLD.name || ' to ' || NEW.name);
		ELSE
			INSERT INTO activity (activity,name) VALUES ('File Update',OLD.name);
		END IF;
	END IF;
	
	RETURN NULL;
END;$$
    LANGUAGE plpgsql;

-- Updates the modified date of file entries
CREATE OR REPLACE FUNCTION file_modified() RETURNS "trigger"
	AS $$BEGIN
		IF TG_OP='INSERT'
		THEN
			IF NEW.modified IS NOT NULL	
			THEN
				RAISE EXCEPTION 'You must not set the modified date manually.';
			END IF;
			IF NEW.created IS NOT NULL
			THEN
				RAISE EXCEPTION 'You must not set the created date manually.';
			END IF;
		
			NEW.created = now();
		END IF;
		
		-- Try to prevent the user tampering with the created date.
		IF TG_OP='UPDATE'
		THEN
			NEW.created := OLD.created;
		END IF;
		
		NEW.modified := current_timestamp;
		RETURN NEW;
END;$$
	LANGUAGE plpgsql;
	
-- Updates the modified date of directory entries.
CREATE OR REPLACE FUNCTION directory_modified() RETURNS "trigger"
	AS $$BEGIN
		IF TG_OP='INSERT'
		THEN
			IF NEW.modified IS NOT NULL	
			THEN
				RAISE EXCEPTION 'You must not set the modified date manually.';
			END IF;
			IF NEW.created IS NOT NULL
			THEN
				RAISE EXCEPTION 'You must not set the created date manually.';
			END IF;
		
			NEW.created = now();
		END IF;
		
		-- Try to prevent the user tampering with the created date.
		IF TG_OP='UPDATE'
		THEN
			NEW.created := OLD.created;
		END IF;
		
		NEW.modified := current_timestamp;
		RETURN NEW;
END;$$
	LANGUAGE plpgsql;

-- Intended to be called when the dircontent table is modified (insert, update, delete).
-- It updates the directory holding the content.
CREATE OR REPLACE FUNCTION dircontent_update_directory_modfied() RETURNS "trigger"
	AS $$DECLARE
		did directory.directory_id%TYPE;
	
	BEGIN
		IF TG_OP='INSERT'
		THEN
			did := NEW.directory_id;
		ELSE
			did := OLD.directory_id;
		END IF;
		UPDATE directory 
			SET modified=current_timestamp 
			WHERE directory_id=did;
		RETURN NULL;
END;$$
	LANGUAGE plpgsql;
	
-- Protect the root directory from tampering, e.g. update and deletion
CREATE OR REPLACE FUNCTION protect_root_dir() RETURNS "trigger"
	AS $$BEGIN
		IF TG_OP='DELETE'
		THEN
			IF OLD.name='<root>'
			THEN
				RAISE EXCEPTION 'You must not delete the root directory.';
			END IF;
			RETURN OLD;
		END IF;
		
		IF TG_OP='UPDATE'
		THEN
			IF NEW.name!='<root>' AND OLD.name='<root>'
			THEN
				RAISE EXCEPTION 'You must not rename the root directory.';
			END IF;
		END IF;
		
		RETURN NEW;
END;$$
	LANGUAGE plpgsql;
	
-- Make sure the dircontent reference to this file is deleted first
-- I''m unable to implement it as foreign key in the dircontent table,
-- since this check also relies on the directory column of dircontent.
CREATE OR REPLACE FUNCTION file_delete() RETURNS "trigger"
	AS $$BEGIN
		PERFORM * FROM dircontent WHERE content_id = OLD.file_id AND directory = 'f';
		IF FOUND THEN
			RAISE EXCEPTION 'You may not delete files still referred to by dircontent.';
		END IF;
		RETURN OLD;
END;$$
	LANGUAGE plpgsql;
	
-- Make sure the dircontent reference to this directory is deleted first
-- I''m unable to implement it as foreign key in the dircontent table,
-- since this check also relies on the directory column of dircontent.
CREATE OR REPLACE FUNCTION directory_delete() RETURNS "trigger"
	AS $$BEGIN
		PERFORM * FROM dircontent WHERE content_id = OLD.directory_id AND directory = 't';
		IF FOUND THEN
			RAISE EXCEPTION 'You may not delete directories still referred to by dircontent.';
		END IF;
		RETURN OLD;
END;$$
	LANGUAGE plpgsql;
 
--
-- Trigers
--
CREATE TRIGGER trg_directory_activity
    AFTER INSERT OR DELETE OR UPDATE ON directory
    FOR EACH ROW
    EXECUTE PROCEDURE directory_activity();
   
CREATE TRIGGER trg_directory_modification
	BEFORE INSERT OR UPDATE ON directory
	FOR EACH ROW
	EXECUTE PROCEDURE directory_modified();
	
CREATE TRIGGER trg_directory_deletion
	BEFORE DELETE ON directory
	FOR EACH ROW
	EXECUTE PROCEDURE directory_delete();
	
CREATE TRIGGER trg_protect_root
	BEFORE DELETE OR UPDATE ON directory
	FOR EACH ROW
	EXECUTE PROCEDURE protect_root_dir();
    
CREATE TRIGGER trg_file_activity
    AFTER INSERT OR DELETE OR UPDATE ON file
    FOR EACH ROW
    EXECUTE PROCEDURE file_activity();
    
CREATE TRIGGER trg_file_modification
	BEFORE INSERT OR UPDATE ON file
	FOR EACH ROW
	EXECUTE PROCEDURE file_modified();
	
CREATE TRIGGER trg_file_deletion
	BEFORE DELETE ON file
	FOR EACH ROW
	EXECUTE PROCEDURE file_delete();
	
CREATE TRIGGER trg_dircontent_modification
	AFTER INSERT OR DELETE OR UPDATE ON dircontent
	FOR EACH ROW
	EXECUTE PROCEDURE dircontent_update_directory_modfied();
    
--
-- Comments
--
COMMENT ON DATABASE :database IS 'Version 1.1';

--
-- Create root directory
--
INSERT INTO directory (name) VALUES ('<root>');