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.4 -r1.5 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql 29 Mar 2001 02:46:30 -0000 1.4 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql 29 Mar 2001 05:38:45 -0000 1.5 @@ -190,7 +190,8 @@ ), content_type varchar(100) constraint cr_items_rev_type_fk - references acs_object_types + references acs_object_types, + tree_sortkey varchar(4000) ); create index cr_items_by_locale on cr_items(locale); @@ -201,6 +202,100 @@ create unique index cr_items_unique_id on cr_items(parent_id, item_id); create index cr_items_by_parent_id on cr_items(parent_id); + +create function cr_items_insert_tr () returns opaque as ' +declare + v_parent_sk varchar; + max_key varchar; +begin + if new.parent_id is null then + select max(tree_sortkey) into max_key + from cr_items + where parent_id is null; + + v_parent_sk := ''''; + else + select max(tree_sortkey) into max_key + from cr_items + where parent_id = new.parent_id; + + select coalesce(max(tree_sortkey),'''') into v_parent_sk + from cr_items + where item_id = new.parent_id; + end if; + + + new.tree_sortkey := v_parent_sk || ''/'' || tree_next_key(max_key); + + return new; + +end;' language 'plpgsql'; + +create trigger cr_items_insert_tr before insert +on cr_items for each row +execute procedure cr_items_insert_tr (); + +create function cr_items_update_tr () returns opaque as ' +declare + v_parent_sk varchar; + max_key varchar; + p_id integer; + v_rec record; + clr_keys_p boolean default ''t''; +begin + if new.item_id = old.item_id and + new.parent_id = old.parent_id then + + return new; + + end if; + + for v_rec in select item_id + from cr_items + where tree_sortkey like new.tree_sortkey || ''%'' + order by tree_sortkey + LOOP + if clr_keys_p then + update cr_items set tree_sortkey = null + where tree_sortkey like new.tree_sortkey || ''%''; + clr_keys_p := ''f''; + end if; + + select parent_id into p_id + from cr_items + where item_id = v_rec.item_id; + + if p_id is null then + select max(tree_sortkey) into max_key + from cr_items + where parent_id is null; + + v_parent_sk := ''''; + else + select max(tree_sortkey) into max_key + from cr_items + where parent_id = p_id; + + select coalesce(max(tree_sortkey),'''') into v_parent_sk + from cr_items + where item_id = p_id; + end if; + + update cr_items + set tree_sortkey = v_parent_sk || ''/'' || tree_next_key(max_key) + where item_id = v_rec.item_id; + + end LOOP; + + return new; + +end;' language 'plpgsql'; + +create trigger cr_items_update_tr after update +on cr_items +for each row +execute procedure cr_items_update_tr (); + comment on table cr_items is ' Each content item has a row in this table, as well as a row in the acs_objects table. The parent_id is used to place an 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.4 -r1.5 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-folder.sql 29 Mar 2001 02:46:30 -0000 1.4 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-folder.sql 29 Mar 2001 05:38:45 -0000 1.5 @@ -387,14 +387,25 @@ v_sub_folder_p := ''t''; end if; +-- select +-- parent_id +-- from +-- cr_items +-- connect by +-- prior parent_id = item_id +-- start with +-- item_id = is_sub_folder__target_folder_id + for v_rec in select - parent_id - from - cr_items - connect by - prior parent_id = is_sub_folder__item_id - start with - item_id = is_sub_folder__target_folder_id + i2.parent_id + from + cr_items i1, cr_items i2 + where + i1.item_id = is_sub_folder__target_folder_id + and + i2.tree_sortkey <= i1.tree_sortkey + and + i1.tree_sortkey like (i2.tree_sortkey || ''%'') LOOP v_parent_id := v_rec.parent_id; exit when v_parent_id = is_sub_folder__folder_id; @@ -459,6 +470,23 @@ end if; else + +-- insert into cr_folder_type_map +-- select +-- register_content_type__folder_id as folder_id, +-- object_type as content_type +-- from +-- acs_object_types +-- where +-- object_type <> ''acs_object'' +-- and +-- not exists (select 1 from cr_folder_type_map +-- where folder_id = register_content_type__folder_id +-- and content_type = acs_object_types.object_type) +-- connect by +-- prior object_type = supertype +-- start with +-- object_type = register_content_type__content_type; insert into cr_folder_type_map select @@ -472,11 +500,11 @@ not exists (select 1 from cr_folder_type_map where folder_id = register_content_type__folder_id and content_type = acs_object_types.object_type) - connect by - prior object_type = supertype - start with - object_type = register_content_type__content_type; - + and + tree_sortkey + like (select tree_sortkey || ''%'' + from acs_object_types + where object_type = register_content_type__content_type); end if; return 0; @@ -497,14 +525,25 @@ where folder_id = unregister_content_type__folder_id and content_type = unregister_content_type__content_type; else + +-- delete from cr_folder_type_map +-- where folder_id = unregister_content_type__folder_id +-- and content_type in (select object_type +-- from acs_object_types +-- where object_type <> ''acs_object'' +-- connect by prior object_type = supertype +-- start with +-- object_type = unregister_content_type__content_type); + delete from cr_folder_type_map where folder_id = unregister_content_type__folder_id and content_type in (select object_type - from acs_object_types - where object_type <> ''acs_object'' - connect by prior object_type = supertype - start with - object_type = unregister_content_type__content_type); + from acs_object_types + where object_type <> ''acs_object'' + and tree_sortkey + like (select tree_sortkey || ''%'' + from acs_object_types + where object_type = unregister_content_type__content_type) end if; 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.3 -r1.4 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql 29 Mar 2001 01:21:03 -0000 1.3 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql 29 Mar 2001 05:38:45 -0000 1.4 @@ -27,16 +27,30 @@ v_folder_id := content_item_globals.c_root_folder_id; else + +-- select +-- item_id into v_folder_id +-- from +-- cr_items +-- where +-- parent_id = 0 +-- connect by +-- prior parent_id = item_id +-- start with +-- item_id = get_root_folder__item_id; + select - item_id into v_folder_id + i2.item_id into v_folder_id from - cr_items + cr_items i1, cr_items i2 where - parent_id = 0 - connect by - prior parent_id = item_id - start with - item_id = get_root_folder__item_id; + i2.parent_id = 0 + and + i1.item_id = get_root_folder__item_id + and + i2.tree_sortkey <= i1.tree_sortkey + and + i1.tree_sortkey like (i2.tree_sortkey || ''%''); if NOT FOUND then raise EXCEPTION '' -20000: Could not find a root folder for item ID %. Either the item does not exist or its parent value is corrupted.'', get_root_folder__item_id; @@ -1494,14 +1508,25 @@ v_subclass_p := ''f''; +-- select +-- object_type +-- from +-- acs_object_types +-- connect by +-- prior object_type = supertype +-- start with +-- object_type = is_subclass__supertype + for v_inherit_val in select object_type from acs_object_types - connect by - prior object_type = supertype - start with - object_type = is_subclass__supertype + where + tree_sortkey + like (select object_type || ''%'' + from acs_object_types + where object_type = is_subclass__supertype) + order by tree_sortkey LOOP if v_inherit_val.object_type = is_subclass__object_type then v_subclass_p := ''t''; 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.3 -r1.4 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-revision.sql 29 Mar 2001 01:21:03 -0000 1.3 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-revision.sql 29 Mar 2001 05:38:45 -0000 1.4 @@ -199,23 +199,42 @@ where revision_id = copy__revision_id; +-- select +-- object_type +-- from +-- acs_object_types +-- where +-- object_type <> ''acs_object'' +-- and +-- object_type <> ''content_revision'' +-- connect by +-- prior supertype = object_type +-- start with +---- object_type = (select object_type +-- from acs_objects +-- where object_id = copy__revision_id) +-- order by +-- level desc + -- iterate over the ancestor types and copy attributes for type_rec in select - object_type + ot2.object_type, tree_level(ot2.tree_sortkey) as level from - acs_object_types + acs_object_types ot1, acs_object_types ot2 where - object_type <> ''acs_object'' + ot2.object_type <> ''acs_object'' and - object_type <> ''content_revision'' - connect by - prior supertype = object_type - start with - object_type = (select object_type - from acs_objects - where object_id = copy__revision_id) + ot2.object_type <> ''content_revision'' + and + ot1.object_type = (select object_type + from acs_objects + where object_id = copy__revision_id) + and + ot2.object_type <= ot1.object_type + and + ot1.object_type like (ot2.object_type || ''%'') order by - level desc + level desc LOOP PERFORM content_revision__copy_attributes(type_rec.object_type, copy__revision_id, v_copy_id); 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.5 -r1.6 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql 29 Mar 2001 02:46:30 -0000 1.5 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql 29 Mar 2001 05:38:45 -0000 1.6 @@ -499,6 +499,7 @@ create function '' || v_table_name || ''t() returns opaque as \\\' declare new_revision_id integer; + begin if new.item_id is null then @@ -539,20 +540,37 @@ end if;''; +-- select +-- object_type +-- from +-- acs_object_types +-- where +-- object_type <> ''acs_object'' +-- and +-- object_type <> ''content_revision'' +-- connect by +-- prior supertype = object_type +-- start with +-- object_type = refresh_trigger.content_type +-- order by +-- level desc + -- add an insert statement for each subtype in the hierarchy for this type for type_rec in select - object_type + ot2.object_type, tree_level(ot2.tree_sortkey) as level from - acs_object_types + acs_object_types ot1, acs_object_types ot2 where - object_type <> ''acs_object'' + ot2.object_type <> ''acs_object'' and - object_type <> ''content_revision'' - connect by - prior supertype = object_type - start with - object_type = refresh_trigger.content_type + ot2.object_type <> ''content_revision'' + and + ot1.object_type = refresh_trigger__content_type + and + ot2.object_type <= ot1.object_type + and + ot1.object_type like (ot2.object_type || ''%'') order by level desc LOOP @@ -583,19 +601,37 @@ v_table_name varchar; join_rec record; begin +-- select +-- table_name, id_column, level +-- from +-- acs_object_types +-- where +-- object_type <> 'acs_object' +-- and +-- object_type <> 'content_revision' +-- start with +-- object_type = refresh_view__content_type +-- connect by +-- object_type = prior supertype for join_rec in select - table_name, id_column, level - from - acs_object_types - where - object_type <> 'acs_object' - and - object_type <> 'content_revision' - start with - object_type = refresh_view__content_type - connect by - object_type = prior supertype + ot2.table_name, + ot2.id_column, + tree_level(ot2.tree_sortkey) as level + from + acs_object_types ot1, acs_object_types ot2 + where + ot2.object_type <> ''acs_object'' + and + ot2.object_type <> ''content_revision'' + and + ot1.object_type = refresh_view__content_type + and + ot2.object_type <= ot1.object_type + and + ot1.object_type like (ot2.object_type || ''%'') + order by + ot2.tree_sortkey desc LOOP cols := cols || '', '' || join_rec.table_name || ''.*''; tabs := tabs || '', '' || join_rec.table_name; @@ -938,10 +974,17 @@ type_rec record; begin - for type_rec in select object_type - from acs_object_types - connect by supertype = prior object_type - start with object_type = ''content_revision'' +-- select object_type +-- from acs_object_types +-- connect by supertype = prior object_type +-- start with object_type = ''content_revision'' + + for type_rec in select ot.object_type + from acs_object_types ot + where tree_sortkey + like (select tree_sortkey || ''%'' + from acs_object_types + where object_type = ''content_revision'') LOOP PERFORM content_type__refresh_view (type_rec.object_type); end LOOP;