Index: openacs-4/contrib/obsolete-packages/bboard/sql/postgresql/bboard-packages.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/bboard/sql/postgresql/bboard-packages.sql,v
diff -u -N -r1.4 -r1.5
--- openacs-4/contrib/obsolete-packages/bboard/sql/postgresql/bboard-packages.sql 1 Dec 2001 22:25:53 -0000 1.4
+++ openacs-4/contrib/obsolete-packages/bboard/sql/postgresql/bboard-packages.sql 9 Dec 2001 04:21:58 -0000 1.5
@@ -500,9 +500,9 @@
child_val record;
message_val record;
image_p integer;
- v_search_key varchar;
+ v_search_key varbit;
begin
- select into v_search_key
+ select tree_sortkey into v_search_key
from acs_messages
where message_id = thread_id;
Index: openacs-4/contrib/obsolete-packages/bboard/tcl/bboard-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/bboard/tcl/bboard-procs-postgresql.xql,v
diff -u -N -r1.9 -r1.10
--- openacs-4/contrib/obsolete-packages/bboard/tcl/bboard-procs-postgresql.xql 8 Dec 2001 01:17:59 -0000 1.9
+++ openacs-4/contrib/obsolete-packages/bboard/tcl/bboard-procs-postgresql.xql 9 Dec 2001 04:21:58 -0000 1.10
@@ -411,10 +411,10 @@
select s.subscriber_id as recipient_id, s.thread_id as grouping_id,
now() as wait_until
- from bboard_thread_subscribers s, acs_messages m, acs_messages m2
+ from bboard_thread_subscribers s, acs_messages m,
+ (select tree_ancestor_keys(acs_message_get_tree_sortkey(:message_id)) as tree_sortkey) parents
where s.thread_id = m.message_id
- and m2.message_id = :message_id
- and m.tree_sortkey between tree_root_key(m2.tree_sortkey) and tree_right(tree_root_key(m2.tree_sortkey))
+ and m.tree_sortkey = parents.tree_sortkey
and exists (select 1
from all_object_party_privilege_map map
where map.object_id = :message_id and
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 -N -r1.33 -r1.34
--- openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql 8 Dec 2001 01:17:59 -0000 1.33
+++ openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql 9 Dec 2001 04:21:58 -0000 1.34
@@ -236,6 +236,13 @@
foreign key (item_id) references cr_items(item_id);
+create function cr_items_get_tree_sortkey(integer) returns varbit as '
+declare
+ p_item_id alias for $1;
+begin
+ return tree_sortkey from cr_items where item_id = p_item_id;
+end;' language 'plpgsql';
+
create function cr_items_tree_insert_tr () returns opaque as '
declare
v_parent_sk varbit default null;
@@ -955,6 +962,13 @@
);
+create function cr_keywords_get_tree_sortkey(integer) returns varbit as '
+declare
+ p_keyword_id alias for $1;
+begin
+ return tree_sortkey from cr_keywords where keyword_id = p_keyword_id;
+end;' language 'plpgsql';
+
create function cr_keywords_tree_insert_tr () returns opaque as '
declare
v_parent_sk varbit default null;
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 -N -r1.35 -r1.36
--- openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql 8 Dec 2001 01:17:59 -0000 1.35
+++ openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql 9 Dec 2001 04:21:58 -0000 1.36
@@ -13,8 +13,6 @@
create view content_item_globals as
select -100 as c_root_folder_id;
--- create or replace package body content_item
--- function get_root_folder
create function content_item__get_root_folder (integer)
returns integer as '
declare
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 -N -r1.9 -r1.10
--- openacs-4/packages/acs-content-repository/sql/postgresql/content-keyword.sql 8 Dec 2001 01:17:59 -0000 1.9
+++ openacs-4/packages/acs-content-repository/sql/postgresql/content-keyword.sql 9 Dec 2001 04:21:58 -0000 1.10
@@ -8,8 +8,6 @@
-- License. Full text of the license is available from the GNU Project:
-- http://www.fsf.org/copyleft/gpl.html
--- create or replace package body content_keyword
--- function get_heading
create function content_keyword__get_heading (integer)
returns text as '
declare
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 -N -r1.7 -r1.8
--- openacs-4/packages/acs-content-repository/sql/postgresql/content-perms.sql 8 Dec 2001 01:17:59 -0000 1.7
+++ openacs-4/packages/acs-content-repository/sql/postgresql/content-perms.sql 9 Dec 2001 04:21:58 -0000 1.8
@@ -151,40 +151,33 @@
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'';
- return count(*) > 0 from
+ -- DRB: Note that the privilege selection doesn't use the slick tree_ancestor_keys
+ -- trick. There are two reasons for this. The first is that we might have a set of
+ -- tree_sortkeys returned from the acs_privilege_hierarchy_index when child_privilege
+ -- is ''cm_perm''. The second is that this table is relatively small anyway and the
+ -- old style's probably just as efficient as the first as an index scan is only preferred
+ -- by the Postgres optimizer when it will significantly reduce the number of rows scanned.
+
+ return exists (select 1 from
(select o2.object_id
- from acs_objects o1, acs_objects o2
- where o1.object_id = has_revoke_authority__object_id
- and o1.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey)) t
+ from (select tree_ancestor_keys(acs_object__get_tree_sortkey(has_revoke_authority__object_id)) as tree_sortkey) parents,
+ acs_objects o2
+ where o2.tree_sortkey = parents.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 i1.tree_sortkey between i2.tree_sortkey and tree_right(i2.tree_sortkey)) h
+ and i1.tree_sortkey between i2.tree_sortkey and tree_right(i2.tree_sortkey)
+ and tree_ancestor_p(i2.tree_sortkey, i1.tree_sortkey)) h
where
content_permission__permission_p(
t.object_id, has_revoke_authority__holder_id, h.child_privilege
)
and not
content_permission__permission_p(
t.object_id, has_revoke_authority__revokee_id, h.privilege
- );
+ ));
end;' language 'plpgsql';
Index: openacs-4/packages/acs-kernel/sql/postgresql/acs-metadata-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/acs-metadata-create.sql,v
diff -u -N -r1.12 -r1.13
--- openacs-4/packages/acs-kernel/sql/postgresql/acs-metadata-create.sql 8 Dec 2001 01:17:59 -0000 1.12
+++ openacs-4/packages/acs-kernel/sql/postgresql/acs-metadata-create.sql 9 Dec 2001 04:21:58 -0000 1.13
@@ -57,6 +57,13 @@
-- support for tree queries on acs_object_types
+create function acs_object_type_get_tree_sortkey(varchar) returns varbit as '
+declare
+ p_object_type alias for $1;
+begin
+ return tree_sortkey from acs_object_types where object_type = p_object_type;
+end;' language 'plpgsql';
+
create function acs_object_type_insert_tr () returns opaque as '
declare
v_parent_sk varbit default null;
@@ -499,93 +506,6 @@
-- METADATA PACKAGES --
-----------------------
--- create or replace package acs_object_type
--- is
--- -- define an object type
--- procedure create_type (
--- object_type in acs_object_types.object_type%TYPE,
--- pretty_name in acs_object_types.pretty_name%TYPE,
--- pretty_plural in acs_object_types.pretty_plural%TYPE,
--- supertype in acs_object_types.supertype%TYPE
--- default 'acs_object',
--- table_name in acs_object_types.table_name%TYPE,
--- id_column in acs_object_types.id_column%TYPE default 'XXX',
--- package_name in acs_object_types.package_name%TYPE default null,
--- abstract_p in acs_object_types.abstract_p%TYPE default 'f',
--- type_extension_table in acs_object_types.type_extension_table%TYPE
--- default null,
--- name_method in acs_object_types.name_method%TYPE default null
--- );
---
--- -- delete an object type definition
--- procedure drop_type (
--- object_type in acs_object_types.object_type%TYPE,
--- cascade_p in char default 'f'
--- );
---
--- -- look up an object type's pretty_name
--- function pretty_name (
--- object_type in acs_object_types.object_type%TYPE
--- ) return acs_object_types.pretty_name%TYPE;
---
--- -- Returns 't' if object_type_2 is a subtype of object_type_1. Note
--- -- that this function will return 'f' if object_type_1 =
--- -- object_type_2
--- function is_subtype_p (
--- object_type_1 in acs_object_types.object_type%TYPE,
--- object_type_2 in acs_object_types.object_type%TYPE
--- ) return char;
---
--- end acs_object_type;
-
--- show errors
-
-
--- create or replace package acs_attribute
--- is
---
--- -- define an object attribute
--- function create_attribute (
--- object_type in acs_attributes.object_type%TYPE,
--- attribute_name in acs_attributes.attribute_name%TYPE,
--- datatype in acs_attributes.datatype%TYPE,
--- pretty_name in acs_attributes.pretty_name%TYPE,
--- pretty_plural in acs_attributes.pretty_plural%TYPE default null,
--- table_name in acs_attributes.table_name%TYPE default null,
--- column_name in acs_attributes.column_name%TYPE default null,
--- default_value in acs_attributes.default_value%TYPE default null,
--- min_n_values in acs_attributes.min_n_values%TYPE default 1,
--- max_n_values in acs_attributes.max_n_values%TYPE default 1,
--- sort_order in acs_attributes.sort_order%TYPE default null,
--- storage in acs_attributes.storage%TYPE default 'type_specific',
--- static_p in acs_attributes.static_p%TYPE default 'f'
--- ) return acs_attributes.attribute_id%TYPE;
---
--- procedure drop_attribute (
--- object_type in varchar2,
--- attribute_name in varchar2
--- );
---
--- procedure add_description (
--- object_type in acs_attribute_descriptions.object_type%TYPE,
--- attribute_name in acs_attribute_descriptions.attribute_name%TYPE,
--- description_key in acs_attribute_descriptions.description_key%TYPE,
--- description in acs_attribute_descriptions.description%TYPE
--- );
---
--- procedure drop_description (
--- object_type in acs_attribute_descriptions.object_type%TYPE,
--- attribute_name in acs_attribute_descriptions.attribute_name%TYPE,
--- description_key in acs_attribute_descriptions.description_key%TYPE
--- );
---
--- end acs_attribute;
-
--- show errors
-
-
--- create or replace package body acs_object_type
--- procedure create_type
create function acs_object_type__create_type (varchar,varchar,varchar,varchar,varchar,varchar,varchar,boolean,varchar,varchar)
returns integer as '
declare
Index: openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql,v
diff -u -N -r1.27 -r1.28
--- openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql 8 Dec 2001 01:17:59 -0000 1.27
+++ openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql 9 Dec 2001 04:21:58 -0000 1.28
@@ -228,6 +228,15 @@
create trigger acs_objects_last_mod_update_tr before update on acs_objects
for each row execute procedure acs_objects_last_mod_update_tr ();
+-- tree query support for acs_objects
+
+create function acs_objects_get_tree_sortkey(integer) returns varbit as '
+declare
+ p_object_id alias for $1;
+begin
+ return tree_sortkey from acs_objects where object_id = p_object_id;
+end;' language 'plpgsql';
+
create function acs_objects_insert_tr () returns opaque as '
declare
v_parent_sk varbit default null;
@@ -534,69 +543,6 @@
-- ACS_OBJECT PACKAGE --
------------------------
--- create or replace package acs_object
--- as
---
--- function new (
--- object_id in acs_objects.object_id%TYPE default null,
--- object_type in acs_objects.object_type%TYPE
--- default 'acs_object',
--- creation_date in acs_objects.creation_date%TYPE
--- default sysdate,
--- creation_user in acs_objects.creation_user%TYPE
--- default null,
--- creation_ip in acs_objects.creation_ip%TYPE default null,
--- context_id in acs_objects.context_id%TYPE default null
--- ) return acs_objects.object_id%TYPE;
---
--- procedure delete (
--- object_id in acs_objects.object_id%TYPE
--- );
---
--- function name (
--- object_id in acs_objects.object_id%TYPE
--- ) return varchar2;
---
--- -- The acs_object_types.name_method for "acs_object"
--- --
--- function default_name (
--- object_id in acs_objects.object_id%TYPE
--- ) return varchar2;
---
--- -- Determine where the attribute is stored and what sql needs to be
--- -- in the where clause to retreive it
--- -- Used in get_attribute and set_attribute
--- procedure get_attribute_storage (
--- object_id_in in acs_objects.object_id%TYPE,
--- attribute_name_in in acs_attributes.attribute_name%TYPE,
--- v_column out varchar2,
--- v_table_name out varchar2,
--- v_key_sql out varchar2
--- );
---
--- -- Get/set the value of an object attribute, as long as
--- -- the type can be cast to varchar2
--- function get_attribute (
--- object_id_in in acs_objects.object_id%TYPE,
--- attribute_name_in in acs_attributes.attribute_name%TYPE
--- ) return varchar2;
---
--- procedure set_attribute (
--- object_id_in in acs_objects.object_id%TYPE,
--- attribute_name_in in acs_attributes.attribute_name%TYPE,
--- value_in in varchar2
--- );
---
--- function check_representation (
--- object_id in acs_objects.object_id%TYPE
--- ) return char;
---
--- end acs_object;
-
--- show errors
-
--- create or replace package body acs_object
--- procedure initialize_attributes
create function acs_object__initialize_attributes (integer)
returns integer as '
declare
Index: openacs-4/packages/acs-kernel/sql/postgresql/acs-relationships-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/acs-relationships-create.sql,v
diff -u -N -r1.9 -r1.10
--- openacs-4/packages/acs-kernel/sql/postgresql/acs-relationships-create.sql 1 Dec 2001 17:55:16 -0000 1.9
+++ openacs-4/packages/acs-kernel/sql/postgresql/acs-relationships-create.sql 9 Dec 2001 04:21:58 -0000 1.10
@@ -362,36 +362,28 @@
actual_object_type_one acs_object_types.object_type%TYPE;
actual_object_type_two acs_object_types.object_type%TYPE;
begin
- select 1 into dummy
- from acs_rel_types rt,
- acs_objects o1,
- acs_objects o2
- where exists (select 1
- from acs_object_types t, acs_object_types o
- where t.object_type = o1.object_type
- and o.object_type = rt.object_type_one
- and t.tree_sortkey between o.tree_sortkey and tree_right(o.tree_sortkey))
- and exists (select 1
- from acs_object_types t, acs_object_types o
- where t.object_type = o2.object_type
- and o.object_type = rt.object_type_two
- and t.tree_sortkey between o.tree_sortkey and tree_right(o.tree_sortkey))
- and rt.rel_type = new.rel_type
+
+ -- DRB: The obvious rewrite using exists kills Postgres!!! Argh!!! This is faster, so there ...
+ -- Get all the object type
+ select rt.object_type_one, rt.object_type_two,
+ o1.object_type, o2.object_type
+ into target_object_type_one, target_object_type_two,
+ actual_object_type_one, actual_object_type_two
+ from acs_rel_types rt, acs_objects o1, acs_objects o2
+ where rt.rel_type = new.rel_type
and o1.object_id = new.object_id_one
and o2.object_id = new.object_id_two;
+ select count(*) into dummy
+ from (select tree_ancestor_keys(acs_object_type_get_tree_sortkey(actual_object_type_one)) as tree_sortkey) parents1,
+ (select tree_ancestor_keys(acs_object_type_get_tree_sortkey(actual_object_type_two)) as tree_sortkey) parents2,
+ (select tree_sortkey from acs_object_types where object_type = target_object_type_one) root1,
+ (select tree_sortkey from acs_object_types where object_type = target_object_type_two) root2
+ where tree_ancestor_p(root1.tree_sortkey, parents1.tree_sortkey)
+ and tree_ancestor_p(root2.tree_sortkey, parents2.tree_sortkey);
+
if NOT FOUND then
- -- At least one of the object types must have been wrong.
- -- Get all the object type information and print it out.
- select rt.object_type_one, rt.object_type_two,
- o1.object_type, o2.object_type
- into target_object_type_one, target_object_type_two,
- actual_object_type_one, actual_object_type_two
- from acs_rel_types rt, acs_objects o1, acs_objects o2
- where rt.rel_type = new.rel_type
- and o1.object_id = new.object_id_one
- and o2.object_id = new.object_id_two;
raise EXCEPTION ''-20001: % violation: Invalid object types. Object % (%) must be of type % Object % (%) must be of type %'', new.rel_type,
new.object_id_one,
Index: openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql,v
diff -u -N -r1.17 -r1.18
--- openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql 8 Dec 2001 01:17:59 -0000 1.17
+++ openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql 9 Dec 2001 04:21:58 -0000 1.18
@@ -359,10 +359,39 @@
end;' language 'plpgsql' with (isstrict, iscachable);
+create function tree_ancestor_key(varbit, integer) returns varbit as '
+
+-- Returns a key for the ancestor at the given level. The root is level
+-- one.
+
+declare
+ p_tree_key alias for $1;
+ p_level alias for $2;
+ v_level integer default 0;
+ v_pos integer default 1;
+begin
+
+ if tree_level(p_tree_key) < p_level then
+ raise exception ''tree_ancestor_key: key is at a level less than %'', p_level;
+ end if;
+
+ while v_level < p_level loop
+ v_level := v_level + 1;
+ if substring(p_tree_key, v_pos, 1) = ''1'' then
+ v_pos := v_pos + 16;
+ else
+ v_pos := v_pos + 8;
+ end if;
+ end loop;
+
+ return substring(p_tree_key, 1, v_pos - 1);
+
+end;' language 'plpgsql' with (isstrict, iscachable);
+
create function tree_root_key(varbit) returns varbit as '
-- Return the tree_sortkey for the root node of the node with the
--- given tree_sortkey.
+-- given tree_sortkey.
declare
p_tree_key alias for $1;
@@ -501,7 +530,104 @@
return position(p_potential_ancestor in p_potential_child) = 1;
end;' language 'plpgsql';
+create function create_tree_ancestor_keys() returns boolean as '
+-- PG 7.1 does not allow recursive SQL functions, but David Walker figured out how to
+-- get around this with a truly inspired hack he posted to the OpenACS 4 Design Forum.
+
+-- His solution involves a general "create and replace function" function written in
+-- Tcl.
+
+-- Rather than use the general solution I have just hacked up a PL/pgSQL function to
+-- create the one recursive function we need: tree_ancestor_keys(varbit, integer).
+
+-- This function should probably only be called from its overloaded cousin
+-- tree_ancestor_keys(varbit).
+
+begin
+
+ -- create tree_ancestor_keys with a dummy body
+
+ execute ''create function tree_ancestor_keys(varbit, integer) returns setof varbit as ''''
+ select $1
+ '''' language ''''sql'''' '';
+
+ -- create another function with the body we want
+
+ execute ''create function __tree_ancestor_keys(varbit, integer) returns setof varbit as ''''
+ select tree_ancestor_key($1, $2)
+ union
+ select tree_ancestor_keys($1, $2 + 1)
+ where $2 < tree_level($1)
+ '''' language ''''sql'''' with (isstrict) '';
+
+ -- replace the body for tree_ancestor_keys with the body we want. Slick, eh?
+
+ update pg_proc
+ set prosrc = hack.prosrc, probin = hack.probin
+ from (select prosrc, probin
+ from pg_proc
+ where proname = ''__tree_ancestor_keys'') hack
+ where proname = ''tree_ancestor_keys'';
+
+ execute ''drop function __tree_ancestor_keys(varbit, integer)'';
+
+ return true;
+end;' language 'plpgsql';
+
+select create_tree_ancestor_keys();
+
+create function tree_ancestor_keys(varbit) returns setof varbit as '
+
+-- tree_ancestor_keys returns the set of ancestor keys starting at the level passed
+-- in as the second parameter down to the key passed in as the first parameter. Normally
+-- this will be one - its intended use is to drive the recursive building of the set of
+-- ancestor keys.
+
+-- Here is an example on acs_objects:
+
+-- select o.*
+-- from acs_objects o,
+-- (select tree_ancestor_keys(acs_objects_get_tree_sortkey(:object_id), 1) as tree_sortkey) parents
+-- where o.tree_sortkey = parents.tree_sortkey;
+
+-- This query will use the index on tree_sortkey to scan acs_objects. The function to grab
+-- the tree_sortkey for the node is necessary (and must be defined for each table that uses
+-- our hierarchical query scheme) to avoid restrictions on the use of SQL functions that
+-- return sets.
+
+-- if you only want the ancestors for a node within a given subtree, do something like this and
+-- cross your fingers that Postgres will figure out whether the join on parent or the root is
+-- more restrictive and do the right one first:
+
+-- select o.*
+-- from acs_objects o,
+-- (select tree_sortkey from acs_objects where object_id = :root_id) as root
+-- (select tree_ancestor_keys(acs_objects_get_tree_sortkey(:object_id), 1) as tree_sortkey) parents
+-- where o.tree_sortkey = parents.tree_sortkey
+-- and o.tree_sortkey >= root.tree_sortkey;
+
+-- DO NOT BE TEMPTED TO REWRITE THE ABOVE QUERIES LIKE THIS:
+
+-- select *
+-- from acs_objects
+-- where object_id in (select tree_ancestor_keys(object_id)
+-- from acs_objects
+-- where object_id = :object_id);
+
+-- This is more readable and is certainly cleaner BUT WILL NOT USE THE INDEX ON TREE_SORTKEY
+-- when scanning the acs_objects instance referred to by the left operand of the "in" operator. Given
+-- that acs_objects will become HUGE on real systems the resulting sequential scan would cripple
+-- performance.
+
+-- WARNING: subselects in where clauses that call this function and join on an outer table appear
+-- to reliably kill PG 7.1.2. Not tested for PG 7.2. If it fails there a bug report will be
+-- filed.
+
+ select tree_ancestor_keys($1, 1)
+
+' language 'sql' with (isstrict);
+
----------------------------------------------------------------------------
-- PG substitute for Oracle user_tab_columns view
Index: openacs-4/packages/acs-kernel/sql/postgresql/site-nodes-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/site-nodes-create.sql,v
diff -u -N -r1.13 -r1.14
--- openacs-4/packages/acs-kernel/sql/postgresql/site-nodes-create.sql 8 Dec 2001 01:17:59 -0000 1.13
+++ openacs-4/packages/acs-kernel/sql/postgresql/site-nodes-create.sql 9 Dec 2001 04:21:58 -0000 1.14
@@ -61,6 +61,13 @@
create index site_nodes_object_id_idx on site_nodes (object_id);
create index site_nodes_tree_skey_idx on site_nodes (tree_sortkey);
+create function site_node_get_tree_sortkey(integer) returns varbit as '
+declare
+ p_node_id alias for $1;
+begin
+ return tree_sortkey from site_nodes where node_id = p_node_id;
+end;' language 'plpgsql';
+
create function site_node_insert_tr () returns opaque as '
declare
v_parent_sk varbit default null;
@@ -195,8 +202,6 @@
-- show errors
--- create or replace package body site_node
--- function new
create function site_node__new (integer,integer,varchar,integer,boolean,boolean,integer,varchar)
returns integer as '
declare
Index: openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-create.sql,v
diff -u -N -r1.6 -r1.7
--- openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-create.sql 8 Dec 2001 01:17:59 -0000 1.6
+++ openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-create.sql 9 Dec 2001 04:21:58 -0000 1.7
@@ -93,6 +93,13 @@
-- support for tree queries on acs_messages
+create function acs_message_get_tree_sortkey(integer) returns varbit as '
+declare
+ p_message_id alias for $1;
+begin
+ return tree_sortkey from acs_messages where message_id = p_message_id;
+end;' language 'plpgsql';
+
create function acs_message_insert_tr () returns opaque as '
declare
v_parent_sk varbit default null;
Index: openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-packages.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-packages.sql,v
diff -u -N -r1.9 -r1.10
--- openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-packages.sql 8 Dec 2001 01:17:59 -0000 1.9
+++ openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-packages.sql 9 Dec 2001 04:21:58 -0000 1.10
@@ -239,7 +239,7 @@
v_message_id acs_messages.message_id%TYPE;
v_ancestor_sk varbit;
begin
- select tree_root_key(tree_sortkey) into v_ancestor_sk
+ select tree_ancestor_key(tree_sortkey, 1) into v_ancestor_sk
from acs_messages
where message_id = p_message_id;
Index: openacs-4/packages/acs-subsite/www/admin/site-map/index-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/site-map/index-postgresql.xql,v
diff -u -N -r1.4 -r1.5
--- openacs-4/packages/acs-subsite/www/admin/site-map/index-postgresql.xql 4 Dec 2001 00:20:47 -0000 1.4
+++ openacs-4/packages/acs-subsite/www/admin/site-map/index-postgresql.xql 9 Dec 2001 04:21:58 -0000 1.5
@@ -9,9 +9,9 @@
select s2.node_id, s2.name, s2.directory_p, tree_level(s2.tree_sortkey) as level,
acs_object__name(s2.object_id) as obj_name,
acs_permission__permission_p(s2.object_id, :user_id, 'admin') as admin_p
- from site_nodes s1, site_nodes s2
- where s1.node_id = :root_id
- and s1.tree_sortkey between s2.tree_sortkey and tree_right(s2.tree_sortkey)
+ from (select tree_ancestor_keys(site_node_get_tree_sortkey(:root_id)) as tree_sortkey) parents,
+ site_nodes s2
+ where s2.tree_sortkey = parents.tree_sortkey
order by level
Index: openacs-4/packages/acs-tcl/tcl/navigation-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-tcl/tcl/Attic/navigation-procs-postgresql.xql,v
diff -u -N -r1.6 -r1.7
--- openacs-4/packages/acs-tcl/tcl/navigation-procs-postgresql.xql 8 Dec 2001 01:17:59 -0000 1.6
+++ openacs-4/packages/acs-tcl/tcl/navigation-procs-postgresql.xql 9 Dec 2001 04:21:58 -0000 1.7
@@ -7,10 +7,9 @@
select site_node__url(n2.node_id) as url, n2.object_id,
acs_object__name(n2.object_id) as object_name,
tree_level(n2.tree_sortkey) as level
- from (select * from site_nodes where node_id = :node_id) n1,
+ from (select tree_ancestor_keys(site_node_get_tree_sortkey(:node_id)) as tree_sortkey) parents,
site_nodes n2
- where n1.tree_sortkey between n2.tree_sortkey and tree_right(n2.tree_sortkey)
- and tree_ancestor_p(n2.tree_sortkey, n1.tree_sortkey)
+ where n2.tree_sortkey = parents.tree_sortkey
order by level asc
Index: openacs-4/packages/bookmarks/sql/postgresql/bookmarks-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/bookmarks/sql/postgresql/bookmarks-create.sql,v
diff -u -N -r1.17 -r1.18
--- openacs-4/packages/bookmarks/sql/postgresql/bookmarks-create.sql 8 Dec 2001 01:17:59 -0000 1.17
+++ openacs-4/packages/bookmarks/sql/postgresql/bookmarks-create.sql 9 Dec 2001 04:21:58 -0000 1.18
@@ -107,6 +107,13 @@
-- With index on tree_sortkey
create index bm_bookmarks_idx1 on bm_bookmarks(tree_sortkey);
+create function bm_bookmarks_get_tree_sortkey(integer) returns varbit as '
+declare
+ p_bookmark_id alias for $1;
+begin
+ return tree_sortkey from bm_bookmarks where bookmark_id = p_bookmark_id;
+end;' language 'plpgsql';
+
create function bm_bookmarks_insert_tr () returns opaque as '
declare
v_parent_sk varbit default null;
@@ -721,10 +728,9 @@
SELECT CASE WHEN count(*)=0 THEN ''f'' ELSE ''t'' END INTO v_private_p
FROM acs_objects,
(
- SELECT bm2.bookmark_id FROM bm_bookmarks bm, bm_bookmarks bm2
- WHERE bm.bookmark_id = p_bookmark_id
- and bm.tree_sortkey between bm2.tree_sortkey and tree_right(bm2.tree_sortkey)
- and tree_ancestor_p(bm2.tree_sortkey, bm.tree_sortkey)
+ SELECT bm.bookmark_id FROM bm_bookmarks bm,
+ (SELECT tree_ancestor_keys(bm_bookmarks_get_tree_sortkey(p_bookmark_id)) as tree_sortkey) parents
+ WHERE bm.tree_sortkey = parents.tree_sortkey
) b
WHERE b.bookmark_id = acs_objects.object_id
AND acs_objects.security_inherit_p = ''f'';
Index: openacs-4/packages/cms/www/modules/permissions/index-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/cms/www/modules/permissions/index-postgresql.xql,v
diff -u -N -r1.3 -r1.4
--- openacs-4/packages/cms/www/modules/permissions/index-postgresql.xql 8 Dec 2001 01:18:00 -0000 1.3
+++ openacs-4/packages/cms/www/modules/permissions/index-postgresql.xql 9 Dec 2001 04:21:58 -0000 1.4
@@ -16,24 +16,21 @@
from
acs_permissions per, acs_privileges p, parties u,
persons n,
- (select o2.object_id
- from (select * from acs_objects where object_id = :object_id) o1,
- acs_objects o2
- where o1.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey)
- and tree_ancestor_p(o2.tree_sortkey, o1.tree_sortkey)
- and tree_level(o2.tree_sortkey) >= (select
- case when max(tree_level(ob2.tree_sortkey)) is null
+ (select o.object_id
+ from (select tree_ancestor_keys(acs_objects_get_tree_sortkey(:object_id)) as tree_sortkey) parents,
+ acs_objects o
+ where o.tree_sortkey = parents.tree_sortkey
+ and tree_level(o.tree_sortkey) >= (select
+ case when max(tree_level(ob.tree_sortkey)) is null
then 0
- else max(tree_level(ob2.tree_sortkey))
+ else max(tree_level(ob.tree_sortkey))
end
from
- (select *
- from acs_objects
- where object_id = :object_id) ob1,
- acs_objects ob2
- where ob1.tree_sortkey between ob2.tree_sortkey and tree_right(ob2.tree_sortkey)
- and tree_ancestor_p(ob2.tree_sortkey, ob1.tree_sortkey)
- and ob2.security_inherit_p = 'f')) o
+ (select tree_ancestor_keys(acs_objects_get_tree_sortkey(:object_id))
+ as tree_sortkey) parents,
+ acs_objects ob
+ where ob.tree_sortkey = parents.tree_sortkey
+ and ob.security_inherit_p = 'f')) o
where
per.privilege = p.privilege
and
Index: openacs-4/packages/file-storage/tcl/file-storage-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/tcl/file-storage-procs-postgresql.xql,v
diff -u -N -r1.7 -r1.8
--- openacs-4/packages/file-storage/tcl/file-storage-procs-postgresql.xql 8 Dec 2001 01:18:00 -0000 1.7
+++ openacs-4/packages/file-storage/tcl/file-storage-procs-postgresql.xql 9 Dec 2001 04:21:58 -0000 1.8
@@ -71,16 +71,16 @@
- select (case when file_storage__get_content_type(j.item_id) = 'content_folder'
+ select (case when file_storage__get_content_type(i.item_id) = 'content_folder'
then 'index?folder_id='
else 'file?file_id='
- end) || j.item_id,
- file_storage__get_title(j.item_id)
- from cr_items i, cr_items j, cr_items k
- where i.item_id = :start_id
- and k.item_id = file_storage__get_root_folder([ad_conn package_id])
- and j.tree_sortkey between tree_left(k.tree_sortkey) and i.tree_sortkey
- and tree_ancestor_p(j.tree_sortkey, i.tree_sortkey)
+ end) || i.item_id,
+ file_storage__get_title(i.item_id)
+ from (select tree_ancestor_keys(cr_items_get_tree_sortkey(:start_id)) as tree_sortkey) parents,
+ (select tree_sortkey from cr_items where item_id = file_storage__get_root_folder([ad_conn package_id])) root,
+ cr_items i
+ where i.tree_sortkey = parents.tree_sortkey
+ and i.tree_sortkey > root.tree_sortkey
order by j.tree_sortkey asc
Index: openacs-4/packages/static-pages/sql/postgresql/static-pages-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/static-pages/sql/postgresql/static-pages-create.sql,v
diff -u -N -r1.17 -r1.18
--- openacs-4/packages/static-pages/sql/postgresql/static-pages-create.sql 8 Dec 2001 01:18:00 -0000 1.17
+++ openacs-4/packages/static-pages/sql/postgresql/static-pages-create.sql 9 Dec 2001 04:21:58 -0000 1.18
@@ -54,6 +54,13 @@
--tree_sortkey index DaveB
create index sp_folders_tree_skey_idx on sp_folders (tree_sortkey);
+create function sp_folders_get_tree_sortkey(integer) returns varbit as '
+declare
+ p_folder_id alias for $1;
+begin
+ return tree_sortkey from sp_folders where folder_id = p_folder_id;
+end;' language 'plpgsql';
+
-- tree_sortkey triggers DaveB
create function sp_folders_insert_tr () returns opaque as '