--
-- file-storage.sql
--
-- created June 1999 by aure@arsdigita.com and dh@arsdigita.com
-- 

-- modified January 2000 by randyg@arsdigita.com
-- All permissions are now taken care of by the general-permissions module
-- (general-permissions.sql).  Permissions are per version.

create sequence fs_file_id_seq;

create table fs_files (
	file_id			integer primary key,
	file_title		varchar(500) not null,
	-- sort_key and depth help us with displaying contents quickly
        sort_key		integer not null,
        depth                   integer not null,
	folder_p		char(1) default 'f' check (folder_p in ('t','f')),
	-- the group_id and public_p are used solely for display purposes
        -- if there is a group_id then we display this file under the group folder
	group_id                integer references user_groups(group_id),
	-- if public_p is 't' we show the file in the public folder
	public_p                char(1) default 'f' check (public_p in ('t','f')),
	-- if group_id is null and public_p <> 't'
	-- the folder or document is in the users' tree
	owner_id		integer not null references users(user_id),
	deleted_p		char(1) default 'f' check (deleted_p in ('t','f')),
	-- parent_id is null for top level items
	parent_id		integer references fs_files(file_id)
);

create function fs_files_tr() returns opaque as '
begin
         if new.parent_id = '''' then
            new.parent_id := null;
         end if;
         if new.group_id = '''' then
            new.group_id = null;
         end if;
  return new;
end;
' language 'plpgsql';

create trigger fs_files_tr
before insert or update on fs_files
for each row execute procedure fs_files_tr();

-- need two indices to support CONNECT BY 

create index fs_files_idx1 on fs_files(file_id, parent_id);
create index fs_files_idx2 on fs_files(parent_id, file_id);


-- folders are also stored in fs_versions so that general_permissions can be
-- wrapped around the folders as well.  This way, is someone ever wants to 
-- put permissions on folders the functionality will already be in place.

create sequence fs_version_id_seq;

create table fs_versions (
	version_id		integer primary key,
	-- this is a version of the file key defined by file_key
	file_id			integer not null references fs_files,
	-- this is where the actual content is stored
	-- POSTGRES: not sure what to do here!
	-- version_content		blob,
        lob                     integer references lobs,
	-- description can be keywords, version notes, etc.
	version_description 	varchar(500),
	creation_date		datetime not null,
	author_id		integer not null references users(user_id),
	-- file name including extension but not path
	client_file_name	varchar(500),
	file_type		varchar(100),	-- this is a MIME type (e.g., image/jpeg)
	file_extension		varchar(50), 	-- e.g., "jpg"
	-- this value is null for the most recent version or equal to the id 
        -- of the version that supersedes this one
	superseded_by_id	integer references fs_versions(version_id),
	-- can be useful when deciding whether to present all of something
	n_bytes			integer,
	-- added so we can store URLs
	url			varchar(200)
);

create trigger fs_versions_trigger before delete or update or insert
on fs_versions for each row execute procedure on_lob_ref();


-- we'll often be asking "show me all the versions of file #4"
create index fs_versions_by_file on fs_versions(file_id);

create view fs_versions_latest
as
select * from fs_versions fs where fs.superseded_by_id is null;


create function fs_node_is_child(integer,integer) returns char as '
declare
        start   alias for $1;
        id      alias for $2;
        pid     integer;
begin
        if id = start then return ''t'';
        end if;

        select into pid parent_id from fs_files where file_id = id;

        if pid is null then
           return ''f'';
        else if id = start then 
           return ''t'';
        else 
           return fs_node_is_child(start,pid);
        end if;
        end if;
end;
' language 'plpgsql';

-- drop function fs_gen_key(integer);
create function fs_gen_key(integer) returns varchar as '
declare
        id      alias for $1;
        str     varchar;
        len     integer;
begin
        str := ''0000000000'' || text(id);
        len := char_length(str);
        return substr(str,len - 9);
end;
' language 'plpgsql';

-- drop function fs_connect_by(integer);
create function fs_connect_by(integer) returns text as '
declare
	id              alias for $1;
        pid             integer;
BEGIN
        select into pid parent_id from fs_files where id = file_id;
	IF pid is null
	THEN 
		return fs_gen_key(id);
	ELSE
		return fs_connect_by(pid) || ''/'' || fs_gen_key(id);
	END IF;	
END;
' language 'plpgsql';

-- drop function fs_level_gen(integer,integer);
create function fs_level_gen(integer,integer) returns integer as '
declare
	id              alias for $1;
        cnt             alias for $2;
	pid             integer;
BEGIN
        select into pid parent_id from fs_files where file_id = id;

	IF pid is null
	THEN 
		return cnt;
	ELSE
		return fs_level_gen(pid,cnt+1);
	END IF;	
END;
' language 'plpgsql';

-- lets create an easy way to walk the tree so that we can join the connect by
-- with the permissions tables
--DRB: PG doesn't have connect by
-- create view fs_files_tree
-- as
-- select
--       file_id,	
--       file_title,
--       sort_key,
--       depth,   
--       folder_p,
--       owner_id,
--       deleted_p,
--       group_id,
--       public_p,
--       parent_id,
--       fs_level_gen(file_id,1) as the_level
--  from fs_files
-- order by fs_connect_by(file_id);

-- connect by prior fs_files.file_id = parent_id
-- start with parent_id is null;


-- if you have Intermedia installed (Oracle 8i only + additional
-- sysadmin/dbadmin nightmares)

-- create index fs_versions_content_idx 
-- on fs_versions (version_content)
-- indextype is ctxsys.context;

-- Seed the general_permission_types table with data for
-- administering permissions on this module (markc@arsdigita.com)
--
insert into general_permission_types (
    table_name,
    permission_type
) values (
    'FS_VERSIONS',
    'read'
);

insert into general_permission_types (
    table_name,
    permission_type
) values (
    'FS_VERSIONS',
    'write'
);

insert into general_permission_types (
    table_name,
    permission_type
) values (
    'FS_VERSIONS',
    'comment'
);

insert into general_permission_types (
    table_name,
    permission_type
) values (
    'FS_VERSIONS',
    'owner'
);