Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql 28 Mar 2001 12:57:00 -0000 1.2 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql 29 Mar 2001 01:21:03 -0000 1.3 @@ -289,18 +289,24 @@ not null constraint cr_revisions_item_id_fk references cr_items on delete cascade, - title varchar(1000), - description varchar(4000), + title varchar(1000) default '' not null, + description text, publish_date timestamp, mime_type varchar(200) default 'text/plain' constraint cr_revisions_mime_type_ref references cr_mime_types, nls_language varchar(50), - -- blob id - content integer - -- content blob + -- use Don's postgresql lob hack for now. + storage_type varchar(10) default 'lob' + constraint cr_revisions_storage_type + check (storage_type in ('lob','text')), + lob integer, + content text default '' not null, + content_length integer ); +create trigger cr_revisions_lob_trig before delete or update or insert +on cr_revisions for each row execute procedure on_lob_ref(); create index cr_revisions_by_mime_type on cr_revisions(mime_type); create index cr_revisions_title_idx on cr_revisions(title); @@ -336,8 +342,6 @@ An XML document representing the compiled attributes for a revision '; --- what's up with this? We probably need a trigger or something to emulate --- this behavior. -- create global temporary table cr_content_text ( -- revision_id integer primary key, @@ -427,16 +431,10 @@ cr_items on delete cascade constraint cr_folders_pk primary key, - label varchar(1000), - description varchar(4000), - has_child_folders char(1) - default 'f' - constraint cr_folder_child_chk - check (has_child_folders in ('t','f')), - has_child_symlinks char(1) - default 'f' - constraint cr_folder_symlink_chk - check (has_child_symlinks in ('t', 'f')) + label varchar(1000) default '' not null, + description text default '' not null, + has_child_folders boolean default 'f', + has_child_symlinks boolean default 'f' ); comment on table cr_folders is ' @@ -511,10 +509,7 @@ not null constraint cr_type_template_map_ctx_fk references cr_template_use_contexts, - is_default char(1) - default 'f' - constraint cr_type_template_map_def_ck - check (is_default in ('t','f')), + is_default boolean default 'f', constraint cr_type_template_map_pk primary key (content_type, template_id, use_context) ); @@ -604,7 +599,7 @@ label varchar(1000) constraint cr_extlink_label_nil not null, - description varchar(4000) + description text default '' not null ); comment on table cr_extlinks is ' @@ -626,10 +621,8 @@ heading varchar(600) constraint cr_keywords_name_nil not null, - description varchar(4000), - has_children char(1) - constraint cr_keywords_child_chk - check (has_children in ('t', 'f')) + description text, + has_children boolean ); comment on table cr_keywords is ' @@ -670,7 +663,7 @@ -------------------------------------------------------------- create table cr_text ( - text varchar(4000) + text text default '' not null ); comment on table cr_text is ' Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-folder.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-folder.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-folder.sql 28 Mar 2001 02:15:10 -0000 1.2 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-folder.sql 29 Mar 2001 01:21:03 -0000 1.3 @@ -513,8 +513,8 @@ -- function is_registered -create function content_folder__is_registered (integer,varchar,varchar) -returns integer as ' +create function content_folder__is_registered (integer,varchar,boolean) +returns boolean as ' declare is_registered__folder_id alias for $1; is_registered__content_type alias for $2; Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql 28 Mar 2001 02:15:10 -0000 1.2 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql 29 Mar 2001 01:21:03 -0000 1.3 @@ -250,7 +250,7 @@ -- function is_published create function content_item__is_published (integer) -returns char as ' +returns boolean as ' declare is_published__item_id alias for $1; begin @@ -394,7 +394,7 @@ declare is_valid_child__item_id alias for $1; is_valid_child__content_type alias for $2; - v_is_valid_child char(1); + v_is_valid_child boolean; v_max_children cr_type_children.max_n%TYPE; v_n_children integer; begin @@ -612,7 +612,7 @@ -- function get_id -create function content_item__get_id (varchar,integer,char) +create function content_item__get_id (varchar,integer,boolean) returns integer as ' declare get_id__item_path alias for $1; @@ -831,10 +831,13 @@ declare item_id alias for $1; root_path alias for $2; - blob_loc cr_revisions.content%TYPE; - v_revision cr_items.live_revision%TYPE; + -- blob_loc cr_revisions.content%TYPE; + -- v_revision cr_items.live_revision%TYPE; begin + -- FIXME: + raise NOTICE ''not implemented for postgresql''; +/* v_revision := content_item__get_live_revision(item_id); select content into blob_loc from cr_revisions @@ -845,7 +848,7 @@ end if; PERFORM blob_to_file(root_path || content_item__get_path(item_id), blob_loc); - +*/ return 0; end;' language 'plpgsql'; @@ -1236,7 +1239,7 @@ v_locale cr_items.locale%TYPE; v_item_id cr_items.item_id%TYPE; v_revision_id cr_revisions.revision_id%TYPE; - v_is_registered char(1); + v_is_registered boolean; v_old_revision_id cr_revisions.revision_id%TYPE; v_new_revision_id cr_revisions.revision_id%TYPE; begin Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-keyword.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-keyword.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-keyword.sql 28 Mar 2001 02:15:10 -0000 1.2 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-keyword.sql 29 Mar 2001 01:21:03 -0000 1.3 @@ -77,7 +77,7 @@ -- function is_leaf create function content_keyword__is_leaf (integer) -returns varchar as ' +returns boolean as ' declare is_leaf__keyword_id alias for $1; begin Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-perms.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/Attic/content-perms.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-perms.sql 28 Mar 2001 02:15:10 -0000 1.2 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-perms.sql 29 Mar 2001 01:21:03 -0000 1.3 @@ -142,7 +142,7 @@ -- function has_revoke_authority create function content_permission__has_revoke_authority (integer,integer,varchar,integer) -returns varchar as ' +returns boolean as ' declare has_revoke_authority__object_id alias for $1; has_revoke_authority__holder_id alias for $2; @@ -220,7 +220,7 @@ -- procedure grant_permission -create function content_permission__grant_permission (integer,integer,varchar,integer,varchar,varchar) +create function content_permission__grant_permission (integer,integer,varchar,integer,boolean,varchar) returns integer as ' declare grant_permission__object_id alias for $1; @@ -255,7 +255,7 @@ exit when grant_permission__is_recursive = ''f''; end loop; - return 0; + return 0; end;' language 'plpgsql'; @@ -292,7 +292,7 @@ -- procedure revoke_permission -create function content_permission__revoke_permission (integer,integer,varchar,integer,varchar,varchar) +create function content_permission__revoke_permission (integer,integer,varchar,integer,boolean,varchar) returns integer as ' declare revoke_permission__object_id alias for $1; Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-revision.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-revision.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-revision.sql 28 Mar 2001 02:15:10 -0000 1.2 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-revision.sql 29 Mar 2001 01:21:03 -0000 1.3 @@ -42,12 +42,16 @@ new__item_id ); + -- binary data is stored in cr_revisions using Don's lob hack. + -- This routine only inserts the lob id. It would need to be followed by + -- ns_pg blob_dml from within a tcl script to actually insert the lob data. + insert into cr_revisions ( revision_id, title, description, mime_type, publish_date, - nls_language, content, item_id + nls_language, lob, item_id, storage_type ) values ( v_revision_id, new__title, new__description, new__mime_type, - new__publish_date, new__nls_language, new__data, new__item_id + new__publish_date, new__nls_language, new__data, new__item_id, ''lob'' ); return v_revision_id; @@ -59,46 +63,42 @@ create function content_revision__new (varchar,varchar,timestamp,varchar,varchar,text,integer,integer,timestamp,integer,varchar) returns integer as ' declare - title alias for $1; - description alias for $2; - publish_date alias for $3; - mime_type alias for $4; - nls_language alias for $5; - text alias for $6; - item_id alias for $7; - revision_id alias for $8; - creation_date alias for $9; - creation_user alias for $10; - creation_ip alias for $11; - v_revision_id integer; - blob_loc cr_revisions.content%TYPE; + new__title alias for $1; + new__description alias for $2; + new__publish_date alias for $3; + new__mime_type alias for $4; + new__nls_language alias for $5; + new__text alias for $6; + new__item_id alias for $7; + new__revision_id alias for $8; + new__creation_date alias for $9; + new__creation_user alias for $10; + new__creation_ip alias for $11; + v_revision_id integer; + v_content_type acs_object_types.object_type%TYPE; begin - blob_loc := empty_blob(); + v_content_type := content_item__get_content_type(new__item_id); - v_revision_id := content_revision__new( - title, - description, - publish_date, - mime_type, - nls_language, - blob_loc, - item_id, - revision_id, - creation_date, - creation_user, - creation_ip + v_revision_id := acs_object__new( + new__revision_id, + v_content_type, + new__creation_date, + new__creation_user, + new__creation_ip, + new__item_id ); - select - content into blob_loc - from - cr_revisions - where - revision_id = v_revision_id - for update; + -- text data is stored directly in cr_revisions using text datatype. - PERFORM string_to_blob(text, blob_loc); + insert into cr_revisions ( + revision_id, title, description, mime_type, publish_date, + nls_language, content, item_id, storage_type, content_length + ) values ( + v_revision_id, new__title, new__description, new__mime_type, + new__publish_date, new__nls_language, new__text, new__item_id, ''text'', + length(new__text) + ); return v_revision_id; @@ -369,15 +369,15 @@ returns integer as ' declare revision_id alias for $1; - clob_loc clob; + clob_loc text; v_doc_id cr_xml_docs.doc_id%TYPE; v_revision_id cr_revisions.revision_id%TYPE; begin v_doc_id := cr_xml_doc_seq.nextval; insert into cr_xml_docs (doc_id, doc) - values (v_doc_id, empty_clob()); + values (v_doc_id, ''''); -- FIXME: need a way to deal with this xml call. v_revision_id := write_xml(revision_id, clob_loc); @@ -436,10 +436,9 @@ -- function is_latest create function content_revision__is_latest (integer) -returns varchar as ' +returns boolean as ' declare is_latest__revision_id alias for $1; - v_ret varchar(1); begin select 1 from cr_items @@ -459,10 +458,15 @@ returns integer as ' declare to_temporary_clob__revision_id alias for $1; - b blob; - c text; + -- b blob; + -- c text; begin - -- FIXME + -- FIXME: I can't find an instance in the 4.2 beta code where this + -- is used so I'm not worrying about porting it for now. + -- DCW - 2001-03-28. + + raise EXCEPTION ''not implemented content_revision.to_temporary_clob''; +/* insert into cr_content_text ( revision_id, content ) values ( @@ -473,7 +477,7 @@ where revision_id = to_temporary_clob__revision_id; PERFORM blob_to_clob(b, c); - +*/ return 0; end;' language 'plpgsql'; @@ -487,12 +491,16 @@ lobs blob; lobd blob; v_item_id cr_items.item_id%TYPE; - v_content_length integer; + v_content_length cr_revisions.content_length%TYPE; v_revision_id_dest cr_revisions.revision_id%TYPE; + v_content cr_revisions.content%TYPE; + v_lob cr_revisions.lob%TYPE; + v_new_lob cr_revisions.lob%TYPE; + v_content_type cr_revisions.content_type%TYPE; begin -- FIXME select - dbms_lob.getlength( content ), item_id + content_length, item_id into v_content_length, v_item_id from @@ -521,17 +529,63 @@ When a BLOB, CLOB, or NCLOB is copied from one row to another row in the same table or in a different table, the actual LOB value is copied, not just the LOB locator. */ - update cr_revisions - set content = ( select content from cr_revisions - where revision_id = content_copy__revision_id ) - where revision_id = v_revision_id_dest; + + select content, content_length, lob, storage_type + into v_content, v_content_length, v_lob, v_storage_type + from cr_revisions where revision_id = content_copy__revision_id; + + if v_storage_type = ''lob'' then + v_new_lob := empty_lob(); + PERFORM lob_copy(v_lob, v_new_lob); + + update cr_revisions + set content = v_content, + content_length = v_content_length, + storage_type = ''lob'' + lob = v_new_lob + where revision_id = v_revision_id_dest; + else + update cr_revisions + set content = v_content, + content_length = v_content_length, + storage_type = ''text'', + lob = null + where revision_id = v_revision_id_dest; + end if; + end if; return 0; end;' language 'plpgsql'; +-- procedure content_copy +create function content_revision__get_content (integer) +returns text as ' +declare + get_content__revision_id alias for $1; + v_storage_type varchar; + v_lob_id + v_data text; +begin + select storage_type, lob, + into v_storage_type, v_lob_id + from cr_revisions + where revision_id = get_content__revision_id; + + if v_storage_type = ''lob'' then + return lob_get_data (v_lob_id); + else + select content into v_data + from cr_revisions + where revision_id = get_content_revision_id; + return v_data; + end if; + +end;' language 'plpgsql'; + + -- show errors -- Trigger to maintain latest_revision in cr_items Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql 28 Mar 2001 02:20:11 -0000 1.3 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql 29 Mar 2001 01:21:03 -0000 1.4 @@ -610,7 +610,7 @@ execute ''create view '' || v_table_name || ''i as select acs_objects.*, cr.revision_id, cr.title, cr.item_id, - cr.content as data, cr_text.text, + content_revision__get_content(cr.revision_id) as data, cr_text.text, cr.description, cr.publish_date, cr.mime_type, cr.nls_language'' || cols || '' from acs_objects, cr_revisions cr, cr_text'' || tabs || '' where Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-update.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-update.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-update.sql 28 Mar 2001 02:15:10 -0000 1.2 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-update.sql 29 Mar 2001 01:21:03 -0000 1.3 @@ -83,10 +83,8 @@ on i.item_id = s.symlink_id''; execute ''alter table cr_folders add - has_child_folders char(1) - default \\\'\\\'f\\\'\\\' - constraint cr_folder_child_chk - check (has_child_folders in (\\\'\\\'t\\\'\\\',\\\'\\\'f\\\'\\\'))''; + has_child_folders boolean + default \\\'\\\'f\\\'\\\'''; execute ''update cr_folders f set has_child_folders = coalesce((select \\\'\\\'t\\\'\\\' from dual where exists @@ -116,7 +114,7 @@ raise NOTICE ''Creating CR_TEXT table for incoming text submissions...''; - execute ''create table cr_text ( text varchar(4000) )''; + execute ''create table cr_text ( text text default \\\'\\\' not null )''; -- For some reason a simple insert statement throws an error but this works execute ''insert into cr_text values (NULL)''; Index: openacs-4/packages/acs-content-repository/sql/postgresql/lob.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/Attic/lob.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-content-repository/sql/postgresql/lob.sql 29 Mar 2001 01:21:03 -0000 1.1 @@ -0,0 +1,110 @@ +-- SQL support for fake lobs for ACS/Postgres. +-- Don Baccus February 2000 + +-- for each user table my_table in which you want to stuff large +-- amounts of data: + +-- define a column "lob integer references lobs" +-- do "create trigger my_table_lob_trig before delete or update or insert +-- on my_table for each row execute procedure on_lob_ref()" + +-- to initialize a row's lob column, use empty_lob(): + +-- insert into my_table (lob) values(empty_lob()); + +-- deletes and updates on my_table use reference count information +-- to delete data from lobs and lob_data when appropriate. + + +create sequence lob_sequence; + +create table lobs ( + lob_id integer not null primary key, + refcount integer not null default 0 +); + +create function on_lobs_delete() returns opaque as ' +begin + delete from lob_data where lob_id = old.lob_id; + return old; +end;' language 'plpgsql'; + +create trigger lobs_delete_trig before delete on lobs +for each row execute procedure on_lobs_delete(); + +create table lob_data ( + lob_id integer not null references lobs, + segment integer not null, + byte_len integer not null, + data bytea not null, + primary key (lob_id, segment) +); + +create index lob_data_index on lob_data(lob_id); + +-- Note - race conditions might cause problems here, but I +-- couldn't get locking to work consistently between PG 6.5 +-- and PG 7.0. The ACS doesn't share LOBs between tables +-- or rows within a table anyway, I don't think/hope. + +create function on_lob_ref() returns opaque as ' +begin + if TG_OP = ''UPDATE'' then + if new.lob = old.lob then + return new; + end if; + end if; + + if TG_OP = ''INSERT'' or TG_OP = ''UPDATE'' then + if new.lob is not null then + insert into lobs select new.lob, 0 + where 0 = (select count(*) from lobs where lob_id = new.lob); + update lobs set refcount = refcount + 1 where lob_id = new.lob; + end if; + end if; + + if TG_OP <> ''INSERT'' then + if old.lob is not null then + update lobs set refcount = refcount - 1 where lob_id = old.lob; + delete from lobs where lob_id = old.lob and refcount = 0; + end if; + end if; + + if TG_OP = ''INSERT'' or TG_OP = ''UPDATE'' then return new; + else return old; + end if; + +end;' language 'plpgsql'; + +create function empty_lob() returns integer as ' +begin + return nextval(''lob_sequence''); +end;' language 'plpgsql'; + +create function lob_get_data(integer) returns text as ' +declare + lob_id integer; + v_rec record; + v_data text default ''''; +begin + for v_rec in select data, segment from lob_data order by segment; + v_data := v_data || v_rec.data; + end if; + + return v_data; + +end;' language 'plpgsql'; + +create function lob_copy(integer, integer) returns integer as ' +declare + from_id alias for $1; + to_id alias for $2; +begin + insert into lob_data + select to_id as lob_id, segment, byte_len, data + from lob_data + where lob_id = from_id; + + return null; + +end;' language 'plpgsql'; Index: openacs-4/packages/acs-kernel/sql/postgresql/acs-kernel-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/acs-kernel-create.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-kernel-create.sql 18 Mar 2001 05:22:27 -0000 1.2 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-kernel-create.sql 29 Mar 2001 01:21:03 -0000 1.3 @@ -11,6 +11,7 @@ -- set feedback off \i postgresql.sql +\i lob.sql \i acs-logs-create.sql \i acs-metadata-create.sql \i acs-objects-create.sql