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.5 -r1.6 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql 29 Mar 2001 05:38:45 -0000 1.5 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql 30 Mar 2001 03:03:08 -0000 1.6 @@ -717,9 +717,104 @@ constraint cr_keywords_name_nil not null, description text default '' not null, - has_children boolean + has_children boolean, + tree_sortkey varchar(4000) ); + +create function cr_keywords_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_keywords + where parent_id is null; + + v_parent_sk := ''''; + else + select max(tree_sortkey) into max_key + from cr_keywords + where parent_id = new.parent_id; + + select coalesce(max(tree_sortkey),'''') into v_parent_sk + from cr_keywords + where keyword_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_keywords_insert_tr before insert +on cr_keywords for each row +execute procedure cr_keywords_insert_tr (); + +create function cr_keywords_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.keyword_id = old.keyword_id and + new.parent_id = old.parent_id then + + return new; + + end if; + + for v_rec in select keyword_id + from cr_keywords + where tree_sortkey like new.tree_sortkey || ''%'' + order by tree_sortkey + LOOP + if clr_keys_p then + update cr_keywords 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_keywords + where keyword_id = v_rec.keyword_id; + + if p_id is null then + select max(tree_sortkey) into max_key + from cr_keywords + where parent_id is null; + + v_parent_sk := ''''; + else + select max(tree_sortkey) into max_key + from cr_keywords + where parent_id = p_id; + + select coalesce(max(tree_sortkey),'''') into v_parent_sk + from cr_keywords + where keyword_id = p_id; + end if; + + update cr_keywords + set tree_sortkey = v_parent_sk || ''/'' || tree_next_key(max_key) + where keyword_id = v_rec.keyword_id; + + end LOOP; + + return new; + +end;' language 'plpgsql'; + +create trigger cr_keywords_update_tr after update +on cr_keywords +for each row +execute procedure cr_keywords_update_tr (); + comment on table cr_keywords is ' Stores a subject taxonomy for classifying content items, analogous to the system used by a library. 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.5 -r1.6 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-folder.sql 29 Mar 2001 05:38:45 -0000 1.5 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-folder.sql 30 Mar 2001 03:03:08 -0000 1.6 @@ -406,6 +406,7 @@ i2.tree_sortkey <= i1.tree_sortkey and i1.tree_sortkey like (i2.tree_sortkey || ''%'') + order by i2.tree_sortkey desc LOOP v_parent_id := v_rec.parent_id; exit when v_parent_id = is_sub_folder__folder_id; @@ -575,18 +576,30 @@ content_type = is_registered__content_type; else +-- select +-- object_type +-- from +-- acs_object_types +-- where +-- object_type <> ''acs_object'' +-- connect by +-- prior object_type = supertype +-- start with +-- object_type = is_registered.content_type v_is_registered := 1; for v_subtype_val in select object_type from acs_object_types where - object_type <> 'acs_object' - connect by - prior object_type = supertype - start with - object_type = is_registered.content_type + object_type <> ''acs_object'' + and + tree_sortkey + like (select tree_sortkey || ''%'' + from acs_object_types + where object_type = is_registered__content_type) + order by tree_sortkey LOOP if content_folder__is_registered(is_registered__folder_id, v_subtype_val.object_type, ''f'') = ''f'' then 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.3 -r1.4 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-keyword.sql 29 Mar 2001 01:21:03 -0000 1.3 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-keyword.sql 30 Mar 2001 03:03:08 -0000 1.4 @@ -222,10 +222,21 @@ -- Look from specific to general if is_assigned__recurse = ''up'' then +-- select 1 from dual where exists (select 1 from +-- (select keyword_id from cr_keywords +-- connect by parent_id = prior keyword_id +-- start with keyword_id = is_assigned__keyword_id +-- ) t, cr_item_keyword_map m +-- where +-- t.keyword_id = m.keyword_id +-- and +-- m.item_id = is_assigned__item_id); + select 1 from dual where exists (select 1 from (select keyword_id from cr_keywords - connect by parent_id = prior keyword_id - start with keyword_id = is_assigned__keyword_id + where tree_sortkey like (select tree_sortkey || ''%'' + from cr_keywords + where keyword_id = is_assigned__keyword_id) ) t, cr_item_keyword_map m where t.keyword_id = m.keyword_id @@ -240,11 +251,28 @@ end if; if is_assigned__recurse = ''down'' then +-- select 1 from dual where exists ( select 1 from +-- (select keyword_id from cr_keywords +-- connect by prior parent_id = keyword_id +-- start with keyword_id = is_assigned__keyword_id +-- ) t, cr_item_keyword_map m +-- where +-- t.keyword_id = m.keyword_id +-- and +-- m.item_id = is_assigned__item_id); + select 1 from dual where exists ( select 1 from - (select keyword_id from cr_keywords - connect by prior parent_id = keyword_id - start with keyword_id = is_assigned__keyword_id - ) t, cr_item_keyword_map m + (select + k2.keyword_id + from + cr_keywords k1, cr_keywords k2 + where + k1.keyword_id = is_assigned__keyword_id + and + k2.tree_sortkey <= k1.tree_sortkey + and + k1.tree_sortkey like (k2.tree_sortkey || ''%'')) t, + cr_item_keyword_map m where t.keyword_id = m.keyword_id and @@ -268,21 +296,36 @@ create function content_keyword__get_path (integer) returns text as ' declare - keyword_id alias for $1; - v_path text default ''''; - v_is_found boolean default ''f''; - v_heading cr_keywords.heading%TYPE; - v_rec record; + get_path_keyword_id alias for $1; + v_path text default ''''; + v_is_found boolean default ''f''; + v_heading cr_keywords.heading%TYPE; + v_rec record; begin +-- select +-- heading +-- from ( +-- select +-- heading, level as tree_level +-- from cr_keywords +-- connect by prior parent_id = keyword_id +-- start with keyword_id = get_path.keyword_id) k +-- order by +-- tree_level desc for v_rec in select heading from ( select - heading, level as tree_level - from cr_keywords - connect by prior parent_id = keyword_id - start with keyword_id = get_path.keyword_id) k + k2.heading, tree_level(k2.tree_sortkey) as tree_level + from + cr_keywords k1, cr_keywords k2 + where + k1.keyword_id = get_path__keyword_id + and + k2.tree_sortkey <= k1.tree_sortkey + and + k1.tree_sortkey like (k2.tree_sortkey || ''%'')) k order by tree_level desc LOOP 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.3 -r1.4 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-perms.sql 29 Mar 2001 01:21:03 -0000 1.3 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-perms.sql 30 Mar 2001 03:03:08 -0000 1.4 @@ -149,13 +149,34 @@ has_revoke_authority__privilege alias for $3; has_revoke_authority__revokee_id alias for $4; begin +-- select 1 from +-- (select object_id from acs_objects +-- connect by prior context_id = object_id +-- start with object_id = has_revoke_authority__object_id) t, +-- (select privilege, child_privilege from acs_privilege_hierarchy +-- connect by prior privilege = child_privilege +-- start with child_privilege = ''cm_perm'') h +-- where +-- content_permission__permission_p( +-- t.object_id, has_revoke_authority__holder_id, h.child_privilege +-- ) = ''t'' +-- and +-- content_permission__permission_p( +-- t.object_id, has_revoke_authority__revokee_id, h.privilege +-- ) = ''f''; + select 1 from - (select object_id from acs_objects - connect by prior context_id = object_id - start with object_id = has_revoke_authority__object_id) t, - (select privilege, child_privilege from acs_privilege_hierarchy - connect by prior privilege = child_privilege - start with child_privilege = ''cm_perm'') h + (select o2.object_id + from acs_objects o1, acs_objects o2 + where o1.object_id = has_revoke_authority__object_id + and o2.tree_sortkey <= o1.tree_sortkey + and o1.tree_sortkey like (o2.tree_sortkey || ''%'')) t + (select i2.privilege, i2.child_privilege + from acs_privilege_hierarchy_index i1, + acs_privilege_hierarchy_index i2 + where i1.child_privilege = ''cm_perm'' + and i2.tree_sortkey <= i1.tree_sortkey + and i1.tree_sortkey like (i2.tree_sortkey || ''%'')) h where content_permission__permission_p( t.object_id, has_revoke_authority__holder_id, h.child_privilege @@ -231,13 +252,28 @@ grant_permission__object_type alias for $6; v_object_id acs_objects.object_id%TYPE; begin +-- select +-- o.object_id +-- from +-- (select object_id, object_type from acs_objects +-- connect by context_id = prior object_id +-- start with object_id = grant_permission__object_id) o +-- where +-- content_permission__has_grant_authority ( +-- o.object_id, holder_id, grant_permission__privilege +-- ) = ''t'' +-- and +-- content_item__is_subclass (o.object_type, grant_permission__object_type) = ''t'' for v_rec in select o.object_id from - (select object_id, object_type from acs_objects - connect by context_id = prior object_id - start with object_id = grant_permission__object_id) o + (select object_id, object_type + from acs_objects + where tree_sortkey + like (select tree_sortkey || ''%'' + from acs_objects + where object_id = grant_permission__object_id)) o where content_permission__has_grant_authority ( o.object_id, holder_id, grant_permission__privilege @@ -303,13 +339,19 @@ revoke_permission__object_type alias for $6; v_rec record; begin +-- select object_id, object_type from acs_objects +-- connect by context_id = prior object_id +-- start with object_id = revoke_permission__object_id for v_rec in select o.object_id from - (select object_id, object_type from acs_objects - connect by context_id = prior object_id - start with object_id = revoke_permission__object_id) o + (select object_id, object_type + from acs_objects + where tree_sortkey + like (select tree_sortkey || ''%'' + from acs_objects + where object_id = revoke_permission__object_id)) o where content_permission__has_revoke_authority (o.object_id, revoke_permission__holder_id, revoke_permission__privilege, revoke_permission__revokee_id) = ''t'' and 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.4 -r1.5 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-revision.sql 29 Mar 2001 05:38:45 -0000 1.4 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-revision.sql 30 Mar 2001 03:03:08 -0000 1.5 @@ -584,7 +584,7 @@ declare get_content__revision_id alias for $1; v_storage_type varchar; - v_lob_id + v_lob_id integer; v_data text; begin select storage_type, lob, 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.6 -r1.7 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql 29 Mar 2001 05:38:45 -0000 1.6 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql 30 Mar 2001 03:03:08 -0000 1.7 @@ -606,9 +606,9 @@ -- from -- acs_object_types -- where --- object_type <> 'acs_object' +-- object_type <> ''acs_object'' -- and --- object_type <> 'content_revision' +-- object_type <> ''content_revision'' -- start with -- object_type = refresh_view__content_type -- connect by @@ -981,10 +981,11 @@ for type_rec in select ot.object_type from acs_object_types ot - where tree_sortkey + where ot.tree_sortkey like (select tree_sortkey || ''%'' from acs_object_types where object_type = ''content_revision'') + order by ot.tree_sortkey LOOP PERFORM content_type__refresh_view (type_rec.object_type); end LOOP; Index: openacs-4/packages/acs-content-repository/sql/postgresql/types-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/types-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/acs-content-repository/sql/postgresql/types-create.sql 27 Mar 2001 02:02:31 -0000 1.1 +++ openacs-4/packages/acs-content-repository/sql/postgresql/types-create.sql 30 Mar 2001 03:03:09 -0000 1.2 @@ -546,9 +546,16 @@ type_rec record; begin +-- select object_type from acs_object_types +-- connect by supertype = prior object_type +-- start with object_type = ''content_revision'' + for type_rec in select object_type from acs_object_types - connect by supertype = prior object_type - start with object_type = ''content_revision'' + 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;