Index: openacs-4/packages/categories/sql/postgresql/categories-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/postgresql/categories-create.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/categories/sql/postgresql/categories-create.sql 19 May 2003 13:09:56 -0000 1.1
@@ -0,0 +1,240 @@
+--
+-- The Categories Package
+--
+-- @author Timo Hentschel (thentschel@sussdorff-roy.com)
+-- @creation-date 2003-04-16
+--
+
+ -- create the object types
+
+create function inline_0 ()
+returns integer as '
+begin
+
+ PERFORM acs_object_type__create_type (
+ ''category_tree'', -- object_type
+ ''Category Tree'', -- pretty_name
+ ''Category Trees'', -- pretty_plural
+ ''acs_object'', -- supertype
+ ''category_trees'', -- table_name
+ ''tree_id'', -- id_column
+ ''category_tree.name'', -- name_method
+ ''f'',
+ null,
+ null
+ );
+ PERFORM acs_object_type__create_type (
+ ''category'', -- object_type
+ ''Category'', -- pretty_name
+ ''Categories'', -- pretty_plural
+ ''acs_object'', -- supertype
+ ''categories'', -- table_name
+ ''category_id'', -- id_column
+ ''category.name'', -- name_method
+ ''f'',
+ null,
+ null
+ );
+
+ return 0;
+end;' language 'plpgsql';
+
+select inline_0 ();
+drop function inline_0 ();
+
+create table category_trees (
+ tree_id integer primary key
+ constraint cat_trees_tree_id_fk
+ references acs_objects on delete cascade,
+ site_wide_p char(1) default 't'
+ constraint cat_trees_site_wide_p_ck
+ check (site_wide_p in ('t','f'))
+);
+
+comment on table category_trees is '
+ This is general data for each category tree.
+';
+comment on column category_trees.tree_id is '
+ ID of a tree.
+';
+comment on column category_trees.site_wide_p is '
+ Declares if a tree is site-wide or local (only usable by users/groups
+ that have permissions).
+';
+
+create table category_tree_translations (
+ tree_id integer
+ constraint cat_tree_trans_tree_id_fk
+ references category_trees on delete cascade,
+ locale varchar(5) not null
+ constraint cat_tree_trans_locale_fk
+ references ad_locales,
+ name varchar(50) not null,
+ description varchar(1000),
+ primary key (tree_id, locale)
+);
+
+comment on table category_tree_translations is '
+ Translations for names and descriptions of trees in different languages.
+';
+comment on column category_tree_translations.tree_id is '
+ ID of a tree (see category_trees).
+';
+comment on column category_tree_translations.locale is '
+ ACS-Lang style locale if language ad country.
+';
+comment on column category_tree_translations.name is '
+ Name of the tree in the specified language.
+';
+comment on column category_tree_translations.description is '
+ Description of the tree in the specified language.
+';
+
+create table categories (
+ category_id integer
+ primary key constraint cat_category_id_fk
+ references acs_objects
+ on delete cascade,
+ tree_id integer
+ constraint cat_tree_id_fk
+ references category_trees
+ on delete cascade,
+ parent_id integer constraint cat_parent_id_fk
+ references categories,
+ deprecated_p char(1) default 'f'
+ constraint cat_deprecated_p_ck
+ check (deprecated_p in ('t','f')),
+ left_ind integer,
+ right_ind integer
+);
+
+create index categories_left_ix on categories(tree_id, left_ind);
+create index categories_parent_ix on categories(parent_id, category_id);
+
+-- create unique index categories_left_ix on categories(tree_id, left_ind);
+-- create unique index categories_parent_ix on categories(parent_id, category_id);
+-- analyze table categories compute statistics;
+
+comment on table categories is '
+ Information about the categories in the tree structure.
+';
+comment on column categories.category_id is '
+ ID of a category.
+';
+comment on column categories.tree_id is '
+ ID of a tree (see category_trees).
+';
+comment on column categories.parent_id is '
+ Points to a parent category in the tree or null (if topmost category).
+';
+comment on column categories.deprecated_p is '
+ Marks categories to be no longer supported.
+';
+comment on column categories.left_ind is '
+ Left index in nested set structure of a tree.
+';
+comment on column categories.right_ind is '
+ Right index in nested set structure of a tree.
+';
+
+create table category_translations (
+ category_id integer
+ constraint cat_trans_category_id_fk
+ references categories on delete cascade,
+ locale varchar(5) not null
+ constraint cat_trans_locale_fk
+ references ad_locales,
+ name varchar(200),
+ description varchar(4000),
+ primary key (category_id, locale)
+);
+
+comment on table category_translations is '
+ Translations for names and descriptions of categories in different languages.
+';
+comment on column category_translations.category_id is '
+ ID of a category (see categories).
+';
+comment on column category_translations.locale is '
+ ACS-Lang style locale if language ad country.
+';
+comment on column category_translations.name is '
+ Name of the category in the specified language.
+';
+comment on column category_translations.description is '
+ Description of the category in the specified language.
+';
+
+create table category_tree_map (
+ tree_id integer
+ constraint cat_tree_map_tree_id_fk
+ references category_trees on delete cascade,
+ object_id integer
+ constraint cat_tree_map_object_id_fk
+ references acs_objects on delete cascade,
+ subtree_category_id integer
+ default null
+ constraint cat_tree_map_subtree_id_fk
+ references categories,
+ primary key (object_id, tree_id)
+);
+
+create unique index cat_tree_map_ix on category_tree_map(tree_id, object_id);
+
+comment on table category_tree_map is '
+ Maps trees to objects (usually package instances) so that
+ other objects can be categorized.
+';
+comment on column category_tree_map.tree_id is '
+ ID of the mapped tree (see category_trees).
+';
+comment on column category_tree_map.object_id is '
+ ID of the mapped object (usually an apm_package if trees are to be used
+ in a whole package instance, i.e. file-storage).
+';
+comment on column category_tree_map.subtree_category_id is '
+ If a subtree is mapped, then this is the ID of the category on top
+ of the subtree, null otherwise.
+';
+
+create table category_object_map (
+ category_id integer
+ constraint cat_object_map_category_id_fk
+ references categories on delete cascade,
+ object_id integer
+ constraint cat_object_map_object_id_fk
+ references acs_objects on delete cascade,
+ primary key (category_id, object_id)
+);
+
+create unique index cat_object_map_ix on category_object_map(object_id, category_id);
+
+comment on table category_object_map is '
+ Maps categories to objects and thus categorizes and object.
+';
+comment on column category_object_map.category_id is '
+ ID of the mapped category (see categories).
+';
+comment on column category_object_map.object_id is '
+ ID of the mapped object.
+';
+
+-- create global temporary table category_temp (
+-- category_id integer
+-- ) on commit delete rows;
+
+create table category_temp (
+ category_id integer
+);
+
+comment on table category_temp is '
+ Used mainly for multi-dimensional browsing to use only bind vars
+ in queries
+';
+
+\i category-tree-package.sql
+\i category-package.sql
+
+\i categories-permissions.sql
+
+\i categories-init.sql
Index: openacs-4/packages/categories/sql/postgresql/categories-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/postgresql/categories-drop.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/categories/sql/postgresql/categories-drop.sql 19 May 2003 13:09:56 -0000 1.1
@@ -0,0 +1,87 @@
+--
+-- The Categories Package
+--
+-- @author Timo Hentschel (thentschel@sussdorff-roy.com)
+-- @creation-date 2003-04-16
+--
+
+drop table category_temp;
+
+drop table category_object_map;
+
+drop table category_tree_map;
+drop index cat_tree_map_ix;
+drop index cat_object_map_ix;
+
+drop table category_translations;
+
+drop table categories;
+drop index categories_left_ix;
+drop index categories_parent_ix;
+
+drop table category_tree_translations;
+
+drop table category_trees;
+
+delete from acs_permissions where object_id in
+ (select object_id from acs_objects where object_type = 'category_tree');
+delete from acs_objects where object_type='category';
+delete from acs_objects where object_type='category_tree';
+
+
+begin;
+ select acs_object_type__drop_type('category', 't');
+ select acs_object_type__drop_type('category_tree', 't');
+end;
+
+drop function category_tree__new (integer,varchar,varchar,varchar,
+ char,timestamp,integer,varchar,integer);
+drop function category_tree__new_translation (integer,varchar,varchar,
+ varchar,timestamp,integer,varchar);
+drop function category_tree__del (integer);
+drop function category_tree__edit (integer,varchar,varchar,varchar,
+ char,timestamp,integer,varchar);
+drop function category_tree__copy (integer,integer,integer,varchar);
+drop function category_tree__map (integer,integer,integer);
+drop function category_tree__unmap (integer,integer);
+drop function category_tree__check_nested_ind (integer);
+-- drop function category_tree__index_children (integer,integer);
+drop function category__new (integer,integer,varchar,varchar,varchar,
+ integer,char,timestamp,integer,varchar);
+drop function category__new_translation (integer,varchar,varchar,varchar,
+ timestamp,integer,varchar);
+drop function category__phase_out (integer);
+drop function category__phase_in (integer);
+drop function category__del (integer);
+drop function category__edit (integer,varchar,varchar,varchar,
+ timestamp,integer,varchar);
+drop function category__change_parent (integer,integer,integer);
+drop function category__name (integer);
+
+-- delete privileges;
+-- this shouldn't be necessary
+begin;
+delete from acs_privilege_descendant_map where privilege like 'category%';
+end;
+
+select acs_privilege__remove_child('category_admin','category_tree_read');
+select acs_privilege__remove_child('category_admin','category_tree_write');
+select acs_privilege__remove_child('category_admin','category_tree_grant_permissions');
+select acs_privilege__remove_child('admin','category_admin');
+
+select acs_privilege__drop_privilege('category_admin');
+select acs_privilege__drop_privilege('category_tree_write');
+select acs_privilege__drop_privilege('category_tree_read');
+select acs_privilege__drop_privilege('category_tree_grant_permissions');
+
+-- from categories-init
+drop table acs_named_objects;
+select acs_object_type__drop_type('acs_named_object', 't');
+select acs_sc_contract__delete(acs_sc_contract__get_id('AcsObject'));
+select acs_sc_msg_type__delete(acs_sc_msg_type__get_id('AcsObject.PageUrl.InputType'));
+select acs_sc_msg_type__delete(acs_sc_msg_type__get_id('AcsObject.PageUrl.OutputType'));
+select acs_sc_operation__delete(acs_sc_operation__get_id('AcsObject','PageUrl'));
+
+-- this should be being handled at the tcl callback level but isn't?
+select acs_sc_impl__delete('AcsObject','category_idhandler');
+select acs_sc_impl__delete('AcsObject','category_tree_idhandler');
Index: openacs-4/packages/categories/sql/postgresql/categories-init.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/postgresql/categories-init.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/categories/sql/postgresql/categories-init.sql 19 May 2003 13:09:56 -0000 1.1
@@ -0,0 +1,73 @@
+--
+-- The Categories Package
+--
+-- @author Timo Hentschel (thentschel@sussdorff-roy.com)
+-- @creation-date 2003-04-16
+--
+
+
+-- This should eventually be added to the acs-service-contract installation files
+
+begin;
+ select acs_sc_contract__new(
+ 'AcsObject', -- contract_name
+ 'Acs Object Id Handler' -- contract_desc
+ );
+ select acs_sc_msg_type__new(
+ 'AcsObject.PageUrl.InputType', -- msg_type_name
+ 'object_id:integer' -- msg_type_spec
+ );
+ select acs_sc_msg_type__new(
+ 'AcsObject.PageUrl.OutputType', -- msg_type_name
+ 'page_url:string' -- msg_type_spec
+ );
+ select acs_sc_operation__new(
+ 'AcsObject', -- contract_name
+ 'PageUrl', -- operation_name
+ 'Returns the package specific url to a page that displays an object', -- operation_desc
+ 'f', -- operation_iscachable_p
+ 1, -- operation_nargs
+ 'AcsObject.PageUrl.InputType', -- operation_inputtype
+ 'AcsObject.PageUrl.OutputType' -- operation_outputtype
+ );
+end;
+
+-- there should be an implementation of this contract
+-- for apm_package, user, group and other object types
+
+
+-- this should eventually be added to acs-kernel
+
+create table acs_named_objects (
+ object_id integer not null
+ constraint acs_named_objs_pk primary key
+ constraint acs_named_objs_object_id_fk
+ references acs_objects(object_id) on delete cascade,
+ object_name varchar(200),
+ package_id integer
+ constraint acs_named_objs_package_id_fk
+ references apm_packages(package_id) on delete cascade
+);
+
+create index acs_named_objs_name_ix on acs_named_objects(object_name);
+create index acs_named_objs_package_ix on acs_named_objects(package_id);
+
+create function inline_0 ()
+returns integer as '
+begin
+ PERFORM acs_object_type__create_type (
+ ''acs_named_object'', -- object_type
+ ''Named Object'', -- pretty_name
+ ''Named Objects'', -- pretty_plural
+ ''acs_object'', -- supertype
+ ''acs_named_objects'', -- table_name
+ ''object_id'', -- id_column
+ null, -- name_method
+ ''f'',
+ null,
+ null
+ );
+ return 0;
+end;' language 'plpgsql';
+select inline_0 ();
+drop function inline_0 ();
Index: openacs-4/packages/categories/sql/postgresql/categories-permissions.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/postgresql/categories-permissions.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/categories/sql/postgresql/categories-permissions.sql 19 May 2003 13:09:56 -0000 1.1
@@ -0,0 +1,19 @@
+--
+-- The Categories Package
+--
+-- @author Timo Hentschel (thentschel@sussdorff-roy.com)
+-- @creation-date 2003-04-16
+--
+
+begin;
+ -- create the privileges
+ select acs_privilege__create_privilege('category_tree_write', null, null);
+ select acs_privilege__create_privilege('category_tree_read', null, null);
+ select acs_privilege__create_privilege('category_tree_grant_permissions', null, null);
+
+ select acs_privilege__create_privilege('category_admin','Categories Administrator','Categories Administrators');
+ select acs_privilege__add_child('admin','category_admin');
+ select acs_privilege__add_child('category_admin','category_tree_read');
+ select acs_privilege__add_child('category_admin','category_tree_write');
+ select acs_privilege__add_child('category_admin','category_tree_grant_permissions');
+end;
Index: openacs-4/packages/categories/sql/postgresql/category-package.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/postgresql/category-package.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/categories/sql/postgresql/category-package.sql 19 May 2003 13:09:56 -0000 1.1
@@ -0,0 +1,341 @@
+--
+-- The Categories Package
+--
+-- @author Timo Hentschel (thentschel@sussdorff-roy.com)
+-- @creation-date 2003-04-16
+--
+
+create or replace function category__new (
+ integer, -- category_id
+ integer, -- tree_id
+ varchar, -- locale
+ varchar, -- name
+ varchar, -- description
+ integer, -- parent_id
+ char, -- deprecated_p
+ timestamp, -- creation_date
+ integer, -- creation_user
+ varchar -- creation_ip
+)
+returns integer as '
+declare
+ p_category_id alias for $1;
+ p_tree_id alias for $2;
+ p_locale alias for $3;
+ p_name alias for $4;
+ p_description alias for $5;
+ p_parent_id alias for $6;
+ p_deprecated_p alias for $7;
+ p_creation_date alias for $8;
+ p_creation_user alias for $9;
+ p_creation_ip alias for $10;
+
+ v_category_id integer;
+ v_left_ind integer;
+ v_right_ind integer;
+begin
+ v_category_id := acs_object__new (
+ p_category_id, -- object_id
+ ''category'', -- object_type
+ p_creation_date, -- creation_date
+ p_creation_user, -- creation_user
+ p_creation_ip, -- creation_ip
+ p_tree_id, -- context_id
+ ''t'' -- security_inherit_p
+ );
+
+ if (p_parent_id is null) then
+ select 1, coalesce(max(right_ind)+1,1) into v_left_ind, v_right_ind
+ from categories
+ where tree_id = p_tree_id;
+ else
+ select left_ind, right_ind into v_left_ind, v_right_ind
+ from categories
+ where category_id = p_parent_id;
+ end if;
+
+ insert into categories
+ (category_id, tree_id, deprecated_p, parent_id, left_ind, right_ind)
+ values
+ (v_category_id, p_tree_id, p_deprecated_p, p_parent_id, -1, -2);
+
+ -- move right subtrees to make room for new category
+ update categories
+ set left_ind = left_ind + 2,
+ right_ind = right_ind + 2
+ where tree_id = p_tree_id
+ and left_ind > v_right_ind;
+
+ -- expand upper nodes to make room for new category
+ update categories
+ set right_ind = right_ind + 2
+ where tree_id = p_tree_id
+ and left_ind <= v_left_ind
+ and right_ind >= v_right_ind;
+
+ -- insert new category
+ update categories
+ set left_ind = v_right_ind,
+ right_ind = v_right_ind + 1
+ where category_id = v_category_id;
+
+ insert into category_translations
+ (category_id, locale, name, description)
+ values
+ (v_category_id, p_locale, p_name, p_description);
+
+ return v_category_id;
+end;
+' language 'plpgsql';
+
+create or replace function category__new_translation (
+ integer, -- category_id
+ varchar, -- locale
+ varchar, -- name
+ varchar, -- description
+ timestamp, -- modifying_date
+ integer, -- modifying_user
+ varchar -- modifying_ip
+)
+returns integer as '
+declare
+ p_category_id alias for $1;
+ p_locale alias for $2;
+ p_name alias for $3;
+ p_description alias for $4;
+ p_modifying_date alias for $5;
+ p_modifying_user alias for $6;
+ p_modifying_ip alias for $7;
+begin
+ insert into category_translations
+ (category_id, locale, name, description)
+ values
+ (p_category_id, p_locale, p_name, p_description);
+
+ update acs_objects
+ set last_modified = p_modifying_date,
+ modifying_user = p_modifying_user,
+ modifying_ip = p_modifying_ip
+ where object_id = p_category_id;
+
+ return 0;
+end;
+' language 'plpgsql';
+
+create or replace function category__phase_out (
+ integer -- category_id
+)
+returns integer as '
+declare
+ p_category_id alias for $1;
+begin
+ update categories
+ set deprecated_p = ''t''
+ where category_id = p_category_id;
+
+ return 0;
+end;
+' language 'plpgsql';
+
+create or replace function category__phase_in (
+ integer -- category_id
+)
+returns integer as '
+declare
+ p_category_id alias for $1;
+begin
+ update categories
+ set deprecated_p = ''f''
+ where category_id = p_category_id;
+
+ return 0;
+end;
+' language 'plpgsql';
+
+create or replace function category__del (
+ integer -- category_id
+)
+returns integer as '
+declare
+ p_category_id alias for $1;
+
+ v_tree_id integer;
+ v_left_ind integer;
+ v_right_ind integer;
+ node record;
+begin
+ select tree_id, left_ind, right_ind
+ into v_tree_id, v_left_ind, v_right_ind
+ from categories where category_id = p_category_id;
+
+ for node in
+ select category_id
+ from categories
+ where tree_id = v_tree_id
+ and left_ind >= v_left_ind
+ and right_ind <= v_right_ind
+ loop
+ delete from category_object_map where category_id = node.category_id;
+ delete from category_translations where category_id = node.category_id;
+ delete from categories where category_id = node.category_id;
+ perform acs_object__delete(node.category_id);
+ end loop;
+
+ update categories
+ set right_ind = (right_ind - (1 + v_right_ind - v_left_ind))
+ where left_ind <= v_left_ind
+ and right_ind > v_left_ind
+ and tree_id = v_tree_id;
+
+ update categories
+ set right_ind = (right_ind - (1 + v_right_ind - v_left_ind)),
+ left_ind = (left_ind - (1 + v_right_ind - v_left_ind))
+ where left_ind > v_left_ind
+ and tree_id = v_tree_id;
+
+ -- for debugging reasons
+ perform category_tree__check_nested_ind(v_tree_id);
+ return 0;
+end;
+' language 'plpgsql';
+
+create or replace function category__edit (
+ integer, -- category_id
+ varchar, -- locale
+ varchar, -- name
+ varchar, -- description
+ timestamp, -- modifying_date
+ integer, -- modifying_user
+ varchar -- modifying_ip
+)
+returns integer as '
+declare
+ p_category_id alias for $1;
+ p_locale alias for $2;
+ p_name alias for $3;
+ p_description alias for $4;
+ p_modifying_date alias for $5;
+ p_modifying_user alias for $6;
+ p_modifying_ip alias for $7;
+begin
+ -- change category name
+ update category_translations
+ set name = p_name,
+ description = p_description
+ where category_id = p_category_id
+ and locale = p_locale;
+
+ update acs_objects
+ set last_modified = p_modifying_date,
+ modifying_user = p_modifying_user,
+ modifying_ip = p_modifying_ip
+ where object_id = p_category_id;
+
+ return 0;
+end;
+' language 'plpgsql';
+
+create or replace function category__change_parent (
+ integer, -- category_id
+ integer, -- tree_id
+ integer -- parent_id
+)
+returns integer as '
+declare
+ p_category_id alias for $1;
+ p_tree_id alias for $2;
+ p_parent alias for $3;
+
+ v_old_left_ind integer;
+ v_old_right_ind integer;
+ v_new_left_ind integer;
+ v_new_right_ind integer;
+ v_width integer;
+begin
+ update categories
+ set parent_id = p_parent_id
+ where category_id = p_category_id;
+
+ -- first save the subtree, then compact tree, then expand tree to make room
+ -- for subtree, then insert it
+
+ select left_ind, right_ind into v_old_left_ind, v_old_right_ind
+ from categories
+ where category_id = p_category_id;
+
+ v_width := v_old_right_ind - v_old_left_ind + 1;
+
+ -- cut out old subtree
+ update categories
+ set left_ind = -left_ind, right_ind = -right_ind
+ where tree_id = p_tree_id
+ and left_ind >= v_old_left_ind
+ and right_ind <= v_old_right_ind;
+
+ -- compact parent trees
+ update categories
+ set right_ind = right_ind - v_width
+ where tree_id = p_tree_id
+ and left_ind < v_old_left_ind
+ and right_ind > v_old_right_ind;
+
+ -- compact right tree portion
+ update categories
+ set left_ind = left_ind - v_width,
+ right_ind = right_ind - v_width
+ where tree_id = p_tree_id
+ and left_ind > v_old_left_ind;
+
+ if (p_parent_id is null) then
+ select 1, max(right_ind)+1 into v_new_left_ind, v_new_right_ind
+ from categories
+ where tree_id = p_tree_id;
+ else
+ select left_ind, right_ind into v_new_left_ind, v_new_right_ind
+ from categories
+ where category_id = p_parent_id;
+ end if;
+
+ -- move parent trees to make room
+ update categories
+ set right_ind = right_ind + v_width
+ where tree_id = p_tree_id
+ and left_ind <= v_new_left_ind
+ and right_ind >= v_new_right_ind;
+
+ -- move right tree portion to make room
+ update categories
+ set left_ind = left_ind + v_width,
+ right_ind = right_ind + v_width
+ where tree_id = p_tree_id
+ and left_ind > v_new_right_ind;
+
+ -- insert subtree at correct place
+ update categories
+ set left_ind = -left_ind + (v_new_right_ind - v_old_left_ind),
+ right_ind = -right_ind + (v_new_right_ind - v_old_left_ind)
+ where tree_id = p_tree_id
+ and left_ind < 0;
+
+ -- for debugging reasons
+ perform category_tree__check_nested_ind(p_tree_id);
+
+ return 0;
+end;
+' language 'plpgsql';
+
+create or replace function category__name (
+ integer -- category_id
+)
+returns integer as '
+declare
+ v_name varchar;
+begin
+ select name into v_name
+ from category_translations
+ where category_id = p_category_id
+ and locale = ''en_US'';
+
+ return 0;
+end;
+' language 'plpgsql';
Index: openacs-4/packages/categories/sql/postgresql/category-tree-package.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/postgresql/category-tree-package.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/categories/sql/postgresql/category-tree-package.sql 19 May 2003 13:09:56 -0000 1.1
@@ -0,0 +1,399 @@
+--
+-- The Categories Package
+--
+-- @author Timo Hentschel (thentschel@sussdorff-roy.com)
+-- @author Michael Steigman (michael@steigman.net)
+-- @creation-date 2003-04-16
+--
+
+create or replace function category_tree__new (
+ integer, -- tree_id
+ varchar, -- locale
+ varchar, -- tree_name
+ varchar, -- description
+ char, -- site_wide_p
+ timestamp, -- creation_date
+ integer, -- creation_user
+ varchar, -- creation_ip
+ integer -- context_id
+)
+returns integer as '
+declare
+ p_tree_id alias for $1;
+ p_locale alias for $2;
+ p_tree_name alias for $3;
+ p_description alias for $4;
+ p_site_wide_p alias for $5;
+ p_creation_date alias for $6;
+ p_creation_user alias for $7;
+ p_creation_ip alias for $8;
+ p_context_id alias for $9;
+
+ v_tree_id integer;
+begin
+ v_tree_id := acs_object__new (
+ p_tree_id, -- object_id
+ ''category_tree'', -- object_type
+ p_creation_date, -- creation_date
+ p_creation_user, -- creation_user
+ p_creation_ip, -- creation_ip
+ p_context_id -- context_id
+ );
+
+ insert into category_trees
+ (tree_id, site_wide_p)
+ values
+ (v_tree_id, p_site_wide_p);
+
+ perform acs_permission__grant_permission (
+ v_tree_id, -- object_id
+ p_creation_user, -- grantee_id
+ ''category_tree_read'' -- privilege
+ );
+ perform acs_permission__grant_permission (
+ v_tree_id, -- object_id
+ p_creation_user, -- grantee_id
+ ''category_tree_write'' -- privilege
+ );
+ perform acs_permission__grant_permission (
+ v_tree_id, -- object_id
+ p_creation_user, -- grantee_id
+ ''category_tree_grant_permissions'' -- privilege
+ );
+
+ insert into category_tree_translations
+ (tree_id, locale, name, description)
+ values
+ (v_tree_id, p_locale, p_tree_name, p_description);
+
+ return v_tree_id;
+end;
+' language 'plpgsql';
+
+create or replace function category_tree__new_translation (
+ integer, -- tree_id
+ varchar, -- locale
+ varchar, -- tree_name
+ varchar, -- description
+ timestamp, -- modifying_date
+ integer, -- modifying_user
+ varchar -- modifying_ip
+)
+returns integer as '
+declare
+ p_tree_id alias for $1;
+ p_locale alias for $2;
+ p_tree_name alias for $3;
+ p_description alias for $4;
+ p_modifying_date alias for $5;
+ p_modifying_user alias for $6;
+ p_modifying_ip alias for $7;
+begin
+ insert into category_tree_translations
+ (tree_id, locale, name, description)
+ values
+ (p_tree_id, p_locale, p_tree_name, p_description);
+
+ update acs_objects
+ set last_modified = p_modifying_date,
+ modifying_user = p_modifying_user,
+ modifying_ip = p_modifying_ip
+ where object_id = p_tree_id;
+ return 0;
+end;
+' language 'plpgsql';
+
+create or replace function category_tree__del (
+ integer -- tree_id
+)
+returns integer as '
+declare
+ p_tree_id alias for $1;
+begin
+
+ delete from category_tree_map where tree_id = p_tree_id;
+
+ delete from category_object_map where category_id in (select category_id from categories where tree_id = p_tree_id);
+
+ delete from category_translations where category_id in (select category_id from categories where tree_id = p_tree_id);
+
+ delete from categories where tree_id = p_tree_id;
+
+ delete from acs_objects where context_id = p_tree_id;
+
+ delete from acs_permissions where object_id = p_tree_id;
+
+ delete from category_tree_translations where tree_id = p_tree_id;
+ delete from category_trees where tree_id = p_tree_id;
+
+ perform acs_object__delete(p_tree_id);
+
+ return 0;
+end;
+' language 'plpgsql';
+
+create or replace function category_tree__edit (
+ integer, -- tree_id
+ varchar, -- locale
+ varchar, -- tree_name
+ varchar, -- description
+ char, -- site_wide_p
+ timestamp, -- modifying_date
+ integer, -- modifying_user
+ varchar -- modifying_ip
+)
+returns integer as '
+declare
+ p_tree_id alias for $1;
+ p_locale alias for $2;
+ p_tree_name alias for $3;
+ p_description alias for $4;
+ p_site_wide_p alias for $5;
+ p_modifying_date alias for $6;
+ p_modifying_user alias for $7;
+ p_modifying_ip alias for $8;
+begin
+ update category_trees
+ set site_wide_p = p_site_wide_p
+ where tree_id = p_tree_id;
+
+ update category_tree_translations
+ set name = p_tree_name,
+ description = p_description
+ where tree_id = p_tree_id
+ and locale = p_locale;
+
+ update acs_objects
+ set last_modified = p_modifying_date,
+ modifying_user = p_modifying_user,
+ modifying_ip = p_modifying_ip
+ where object_id = p_tree_id;
+
+ return 0;
+end;
+' language 'plpgsql';
+
+create or replace function category_tree__copy (
+ integer, -- source_tree
+ integer, -- dest_tree
+ integer, -- creation_user
+ varchar -- creation_ip
+)
+returns integer as '
+declare
+ p_source_tree alias for $1;
+ p_dest_tree alias for $2;
+ p_creation_user alias for $3;
+ p_creation_ip alias for $4;
+
+ v_new_left_ind integer;
+ v_category_id integer;
+begin
+ select coalesce(max(right_ind),0) into v_new_left_ind
+ from categories
+ where tree_id = p_dest_tree;
+
+ for source in (select category_id, parent_id, left_ind, right_ind from categories where tree_id = p_source_tree) loop
+
+ v_category_id := acs_object__new (
+ ''category'', -- object_type
+ now(), -- creation_date
+ p_creation_user, -- creation_user
+ p_creation_ip, -- creation_ip
+ p_dest_tree -- context_id
+ );
+
+ insert into categories
+ (category_id, tree_id, parent_id, left_ind, right_ind)
+ values
+ (v_category_id, p_dest_tree, source.parent_id, source.left_ind + v_new_left_ind, source.right_ind + v_new_left_ind);
+ end loop;
+
+ -- correct parent_ids
+ update categories c
+ set parent_id = (select t.category_id
+ from categories s, categories t
+ where s.category_id = c.parent_id
+ and t.tree_id = copy.dest_tree
+ and s.left_ind + v_new_left_ind = t.left_ind)
+ where tree_id = p_dest_tree;
+
+ -- copy all translations
+ insert into category_translations
+ (category_id, locale, name, description)
+ (select ct.category_id, t.locale, t.name, t.description
+ from category_translations t, categories cs, categories ct
+ where ct.tree_id = p_dest_tree
+ and cs.tree_id = p_source_tree
+ and cs.left_ind + v_new_left_ind = ct.left_ind
+ and t.category_id = cs.category_id);
+
+ -- for debugging reasons
+ perform check_nested_ind(p_dest_tree);
+
+ return 0;
+end;
+' language 'plpgsql';
+
+create or replace function category_tree__map (
+ integer, -- object_id
+ integer, -- tree_id
+ integer -- subtree_category_id
+)
+returns integer as '
+declare
+ p_object_id alias for $1;
+ p_tree_id alias for $2;
+ p_subtree_category_id alias for $3;
+
+ v_map_count integer;
+begin
+ select count(*)
+ into v_map_count
+ from category_tree_map
+ where object_id = p_object_id
+ and tree_id = p_tree_id;
+
+ if v_map_count = 0 then
+ insert into category_tree_map
+ (tree_id, subtree_category_id, object_id)
+ values (p_tree_id, p_subtree_category_id, p_object_id);
+ end if;
+ return 0;
+end;
+' language 'plpgsql';
+
+create or replace function category_tree__unmap (
+ integer, -- object_id
+ integer -- tree_id
+)
+returns integer as '
+declare
+ p_object_id alias for $1;
+ p_tree_id alias for $2;
+begin
+ delete from category_tree_map
+ where object_id = p_object_id
+ and tree_id = p_tree_id;
+ return 0;
+end;
+' language 'plpgsql';
+
+create or replace function category_tree__name (
+ integer -- tree_id
+)
+returns varchar as '
+declare
+ p_tree_id alias for $1;
+ v_name varchar;
+begin
+ select name into v_name
+ from category_tree_translations
+ where tree_id = p_tree_id
+ and locale = ''en_US'';
+
+ return v_name;
+end;
+' language 'plpgsql';
+
+create or replace function category_tree__check_nested_ind (
+ integer -- tree_id
+)
+returns integer as '
+declare
+ p_tree_id alias for $1;
+ v_negative numeric;
+ v_order numeric;
+ v_parent numeric;
+begin
+ select count(*) into v_negative from categories
+ where tree_id = p_tree_id and (left_ind < 1 or right_ind < 1);
+
+ if v_negative > 0 then
+ raise EXCEPTION ''-20001: negative index not allowed!'';
+ end if;
+
+ select count(*) into v_order from categories
+ where tree_id = p_tree_id
+ and left_ind >= right_ind;
+
+ if v_order > 0 then
+ raise EXCEPTION ''-20002: right index must be greater than left index!'';
+ end if;
+
+ select count(*) into v_parent
+ from categories parent, categories child
+ where parent.tree_id = check_nested_ind.tree_id
+ and child.tree_id = parent.tree_id
+ and (parent.left_ind >= child.left_ind or parent.right_ind <= child.right_ind)
+ and child.parent_id = parent.category_id;
+
+ if v_parent > 0 then
+ raise EXCEPTION ''-20003: child index must be between parent index!'';
+ end if;
+end;
+' language 'plpgsql';
+
+create or replace function category_tree__refresh_nested_ind (
+ integer -- tree_id
+)
+returns integer as '
+declare
+ p_tree_id alias for $1;
+ v_left_ind integer;
+ v_right_ind integer;
+begin
+ v_left_ind := 1;
+ for top_nodes in (select category_id
+ from categories
+ where tree_id = p_tree_id
+ and parent_id is null) loop
+ v_right_ind := index_children(top_nodes.category_id, v_left_ind);
+
+ update categories
+ set left_ind = v_left_ind,
+ right_ind = v_right_ind + 1
+ where category_id = top_nodes.category_id;
+
+ v_left_ind := v_right_ind + 2;
+ end loop;
+end;
+' language 'plpgsql';
+
+-- create or replace function category_tree__index_children (
+-- integer, -- tree_id
+-- integer -- ind
+-- )
+-- returns integer as '
+-- declare
+-- TYPE type_categories IS TABLE OF integer;
+-- nodes type_categories := type_categories();
+-- TYPE type_cursor IS REF CURSOR ;
+-- cc type_cursor;
+-- i integer := 0;
+-- v_category_id integer;
+-- v_ind integer := ind;
+-- v_left_ind integer;
+-- begin
+-- open cc for 'select category_id from categories where parent_id = :p_parent_id ' using p_parent_id;
+-- loop
+-- fetch cc into v_category_id;
+-- exit when cc%NOTFOUND;
+-- i := i + 1;
+-- nodes.extend;
+-- nodes(i) := v_category_id;
+-- end loop;
+-- close cc;
+-- if i > 0 then
+-- for j in 1..i loop
+-- v_ind := v_ind + 1;
+-- v_left_ind := v_ind;
+-- v_ind:= index_children(nodes(j), v_ind);
+-- v_ind := v_ind + 1;
+-- update categories set left_ind = v_left_ind, right_ind = v_ind where category_id = nodes(j);
+-- end loop;
+-- end if;
+-- return v_ind;
+-- end;
+-- ' language 'plpgsql';
+
Index: openacs-4/packages/categories/tcl/categories-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/categories/tcl/categories-procs-postgresql.xql,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/categories/tcl/categories-procs-postgresql.xql 27 Apr 2003 11:36:23 -0000 1.2
+++ openacs-4/packages/categories/tcl/categories-procs-postgresql.xql 19 May 2003 13:10:17 -0000 1.3
@@ -5,145 +5,102 @@
- FIX ME PLSQL
-FIX ME PLSQL
-
- begin
- :1 := category__new (
- category_id => :category_id,
- locale => :locale,
- name => :name,
- description => :description,
- tree_id => :tree_id,
- parent_id => :parent_id,
- creation_user => :user_id,
- creation_ip => :creation_ip
- );
- end;
-
+ select category__new (
+ :category_id,
+ :tree_id,
+ :locale,
+ :name,
+ :description,
+ :parent_id,
+ :deprecated_p,
+ current_timestamp,
+ :user_id,
+ :creation_ip
+ )
- FIX ME PLSQL
-FIX ME PLSQL
-
- begin
- category__new_translation (
- category_id => :category_id,
- locale => :default_locale,
- name => :name,
- description => :description,
- modifying_user => :user_id,
- modifying_ip => :creation_ip
- );
- end;
-
+ select category__new_translation (
+ :category_id,
+ :default_locale,
+ :name,
+ :description,
+ current_timestamp,
+ :user_id,
+ :creation_ip
+ )
- FIX ME PLSQL
-FIX ME PLSQL
-
- begin
- category__new_translation (
- category_id => :category_id,
- locale => :locale,
- name => :name,
- description => :description,
- modifying_user => :user_id,
- modifying_ip => :modifying_ip
- );
- end;
-
+ select category__new_translation (
+ :category_id,
+ :locale,
+ :name,
+ :description,
+ current_timestamp,
+ :user_id,
+ :modifying_ip
+ )
- FIX ME PLSQL
-FIX ME PLSQL
-
- begin
- category__edit (
- category_id => :category_id,
- locale => :locale,
- name => :name,
- description => :description,
- modifying_user => :user_id,
- modifying_ip => :modifying_ip
- );
- end;
-
+ select category__edit (
+ :category_id,
+ :locale,
+ :name,
+ :description,
+ current_timestamp,
+ :user_id,
+ :modifying_ip
+ )
- FIX ME PLSQL
-FIX ME PLSQL
-
- begin
- category__del ( :category_id );
- end;
-
+ select category__del ( :category_id )
- FIX ME PLSQL
-FIX ME PLSQL
-
- begin
- category__change_parent (
- category_id => :category_id,
- tree_id => :tree_id,
- parent_id => :parent_id
- );
- end;
-
+ select category__change_parent (
+ :category_id,
+ :tree_id,
+ :parent_id
+ )
- FIX ME PLSQL
-FIX ME PLSQL
-
- begin
- category__phase_in(:category_id);
- end;
-
+ select category__phase_in(:category_id)
- FIX ME PLSQL
-FIX ME PLSQL
-
- begin
- category__phase_out(:category_id);
- end;
-
+ select category__phase_out(:category_id)
- select acs_object__name(:object_id)
+ select acs_object__name(:object_id)
Index: openacs-4/packages/categories/tcl/categories-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/categories/tcl/categories-procs.tcl,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/categories/tcl/categories-procs.tcl 27 Apr 2003 11:36:23 -0000 1.2
+++ openacs-4/packages/categories/tcl/categories-procs.tcl 19 May 2003 13:10:17 -0000 1.3
@@ -17,6 +17,7 @@
-name:required
{-locale ""}
{-description ""}
+ {-deprecated_p "f"}
{-user_id ""}
{-creation_ip ""}
} {
@@ -27,6 +28,7 @@
@option locale locale of the language. [ad_conn locale] used by default.
@option name category name.
@option description description of the category.
+ @option deprecated_p is category deprecated?
@option tree_id tree_id of the category the category should be added.
@option parent_id id of the parent category. "" if top level category.
@option user_id user that adds the category. [ad_conn user_id] used by default.
Index: openacs-4/packages/categories/tcl/category-trees-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/categories/tcl/category-trees-procs-postgresql.xql,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/categories/tcl/category-trees-procs-postgresql.xql 27 Apr 2003 11:36:23 -0000 1.2
+++ openacs-4/packages/categories/tcl/category-trees-procs-postgresql.xql 19 May 2003 13:10:17 -0000 1.3
@@ -5,148 +5,99 @@
- FIX ME PLSQL
-FIX ME PLSQL
-
- begin
- category_tree__map(
- object_id => :object_id,
- subtree_category_id => :subtree_category_id,
- tree_id => :tree_id);
- end;
-
+ select category_tree__map(
+ :object_id,
+ :tree_id,
+ :subtree_category_id)
-
- FIX ME PLSQL
-FIX ME PLSQL
-
- begin
- category_tree__unmap(
- object_id => :object_id,
- tree_id => :tree_id);
- end;
-
+ select category_tree__unmap(:object_id,:tree_id)
- FIX ME PLSQL
-FIX ME PLSQL
-
- begin
- category_tree__copy(
- source_tree => :source_tree,
- dest_tree => :dest_tree
- );
- end;
-
+ select category_tree__copy(:source_tree,:dest_tree)
- FIX ME PLSQL
-FIX ME PLSQL
-
- begin
- :1 := category_tree__new (
- tree_id => :tree_id,
- tree_name => :name,
- description => :description,
- locale => :locale,
- creation_user => :user_id,
- creation_ip => :creation_ip,
- context_id => :context_id
- );
- end;
-
+ select category_tree__new (
+ :tree_id,
+ :locale,
+ :name,
+ :description,
+ :site_wide_p,
+ current_timestamp,
+ :user_id,
+ :creation_ip,
+ :context_id
+ )
- FIX ME PLSQL
-FIX ME PLSQL
-
- begin
- category_tree__new_translation (
- tree_id => :tree_id,
- tree_name => :name,
- description => :description,
- locale => :default_locale,
- modifying_user => :user_id,
- modifying_ip => :creation_ip
- );
- end;
-
+ select category_tree__new_translation (
+ :tree_id,
+ :default_locale,
+ :name,
+ :description,
+ current_timestamp,
+ :user_id,
+ :creation_ip
+ )
- FIX ME PLSQL
-FIX ME PLSQL
-
- begin
- category_tree__new_translation (
- tree_id => :tree_id,
- tree_name => :name,
- description => :description,
- locale => :locale,
- modifying_user => :user_id,
- modifying_ip => :modifying_ip
- );
- end;
-
+ select category_tree__new_translation (
+ :tree_id,
+ :locale,
+ :name,
+ :description,
+ current_timestamp,
+ :user_id,
+ :modifying_ip
+ )
- FIX ME PLSQL
-FIX ME PLSQL
-
- begin
- category_tree__edit (
- tree_id => :tree_id,
- tree_name => :name,
- description => :description,
- locale => :locale,
- modifying_user => :user_id,
- modifying_ip => :modifying_ip
- );
- end;
-
+ select category_tree__edit (
+ :tree_id,
+ :locale,
+ :name,
+ :description,
+ :site_wide_p,
+ current_timestamp,
+ :user_id,
+ :modifying_ip
+ )
- FIX ME PLSQL
-FIX ME PLSQL
-
- begin
- category_tree__del ( :tree_id );
- end;
-
+ select category_tree__del ( :tree_id )
-
select t.pretty_plural, n.object_id, n.object_name, p.package_id,
p.instance_name,
acs_permission__permission_p(n.object_id, :user_id, 'read') as read_p
@@ -156,7 +107,6 @@
and n.object_id = m.object_id
and p.package_id = n.package_id
and t.package_key = p.package_key
-
Index: openacs-4/packages/categories/tcl/category-trees-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/categories/tcl/category-trees-procs.tcl,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/categories/tcl/category-trees-procs.tcl 27 Apr 2003 11:36:23 -0000 1.2
+++ openacs-4/packages/categories/tcl/category-trees-procs.tcl 19 May 2003 13:10:17 -0000 1.3
@@ -76,6 +76,7 @@
{-tree_id ""}
-name:required
{-description ""}
+ {-site_wide_p "f"}
{-locale ""}
{-user_id ""}
{-creation_ip ""}
@@ -122,6 +123,7 @@
-tree_id:required
-name:required
{-description ""}
+ {-site_wide_p "f"}
{-locale ""}
{-user_id ""}
{-modifying_ip ""}
Index: openacs-4/packages/categories/www/categories-browse-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/categories/www/Attic/categories-browse-postgresql.xql,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/categories/www/categories-browse-postgresql.xql 7 May 2003 13:35:06 -0000 1.2
+++ openacs-4/packages/categories/www/categories-browse-postgresql.xql 19 May 2003 13:10:32 -0000 1.3
@@ -5,25 +5,23 @@
- select r.*
- from (select n.object_id, n.object_name as object_name, o.creation_date,
- t.pretty_name as package_type, n.package_id, p.instance_name,
- row_number() over ($order_by_clause) as row_number
- from acs_objects o, acs_named_objects n, apm_packages p, apm_package_types t,
- ($subtree_sql) s
- where n.object_id = s.object_id
- and o.object_id = n.object_id
- and p.package_id = n.package_id
- and t.package_key = p.package_key
- and exists (select 1
- from acs_object_party_privilege_map oppm
- where oppm.object_id = n.object_id
+ select n.object_id, n.object_name as object_name, o.creation_date,
+ t.pretty_name as package_type, n.package_id, p.instance_name
+ from acs_objects o, acs_named_objects n, apm_packages p, apm_package_types t,
+ ($subtree_sql) s
+ where n.object_id = s.object_id
+ and o.object_id = n.object_id
+ and p.package_id = n.package_id
+ and t.package_key = p.package_key
+ and exists (select 1
+ from acs_object_party_privilege_map oppm
+ where oppm.object_id = n.object_id
and oppm.party_id = :user_id
and oppm.privilege = 'read')
- $letter_sql
- $package_sql
- $order_by_clause) r
- where r.row_number between :first_row and :last_row
+ $letter_sql
+ $package_sql
+ $order_by_clause
+ limit $last_row offset $first_row
Index: openacs-4/packages/categories/www/categories-browse.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/categories/www/categories-browse.tcl,v
diff -u -r1.3 -r1.4
--- openacs-4/packages/categories/www/categories-browse.tcl 7 May 2003 13:35:06 -0000 1.3
+++ openacs-4/packages/categories/www/categories-browse.tcl 19 May 2003 13:10:32 -0000 1.4
@@ -46,6 +46,7 @@
}
set tree_ids [db_list check_permissions_on_trees ""]
}
+db_dml delete_tmp_category_trees ""
template::multirow create trees tree_id tree_name category_id category_name indent selected_p
template::util::list_to_lookup $category_ids category_selected
@@ -146,6 +147,7 @@
# execute query to get the objects on current page
set items [ad_table -Torderby $orderby get_categorized_objects "" $table_def]
}
+db_dml delete_tmp_category_trees ""
paginator get_display_info $p_name info $page
set group [paginator get_group $p_name $page]
Index: openacs-4/packages/categories/www/categories-browse.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/categories/www/categories-browse.xql,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/categories/www/categories-browse.xql 7 May 2003 13:35:06 -0000 1.2
+++ openacs-4/packages/categories/www/categories-browse.xql 19 May 2003 13:10:32 -0000 1.3
@@ -9,6 +9,12 @@
+
+
+ delete from category_temp
+
+
+
select t.tree_id
Index: openacs-4/packages/categories/www/index-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/categories/www/Attic/index-postgresql.xql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/categories/www/index-postgresql.xql 23 Apr 2003 12:29:27 -0000 1.1
+++ openacs-4/packages/categories/www/index-postgresql.xql 19 May 2003 13:10:32 -0000 1.2
@@ -5,10 +5,10 @@
-
+
select tree_id, site_wide_p,
- acs_permission__permission_p(tree_id, :user_id, 'category_tree_read') has_read_p
- from category_trees t
+ acs_permission__permission_p(tree_id, :user_id, 'category_tree_read') as has_read_p
+ from category_trees
Index: openacs-4/packages/categories/www/cadmin/category-usage-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/categories/www/cadmin/Attic/category-usage-postgresql.xql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/categories/www/cadmin/category-usage-postgresql.xql 27 Apr 2003 11:37:12 -0000 1.1
+++ openacs-4/packages/categories/www/cadmin/category-usage-postgresql.xql 19 May 2003 13:10:51 -0000 1.2
@@ -15,20 +15,18 @@
- select r.*
- from (select n.object_id, n.object_name as object_name, o.creation_date,
- t.pretty_name as package_type, n.package_id, p.instance_name,
- row_number() over ($order_by_clause) as row_number
- from acs_objects o, acs_named_objects n, apm_packages p, apm_package_types t,
- category_object_map m
- where n.object_id = m.object_id
- and o.object_id = n.object_id
- and p.package_id = n.package_id
- and t.package_key = p.package_key
- and m.category_id = :category_id
- and acs_permission__permission_p(m.object_id, :user_id, 'read') = 't'
- $order_by_clause) r
- where r.row_number between :first_row and :last_row
+ select n.object_id, n.object_name as object_name, o.creation_date,
+ t.pretty_name as package_type, n.package_id, p.instance_name
+ from acs_objects o, acs_named_objects n, apm_packages p, apm_package_types t,
+ category_object_map m
+ where n.object_id = m.object_id
+ and o.object_id = n.object_id
+ and p.package_id = n.package_id
+ and t.package_key = p.package_key
+ and m.category_id = :category_id
+ and acs_permission__permission_p(m.object_id, :user_id, 'read') = 't'
+ $order_by_clause
+ limit $last_row offset $first_row
Index: openacs-4/packages/categories/www/cadmin/index-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/categories/www/cadmin/Attic/index-postgresql.xql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/categories/www/cadmin/index-postgresql.xql 23 Apr 2003 12:29:27 -0000 1.1
+++ openacs-4/packages/categories/www/cadmin/index-postgresql.xql 19 May 2003 13:10:51 -0000 1.2
@@ -7,9 +7,9 @@
select tree_id, site_wide_p,
- acs_permission__permission_p(tree_id, :user_id, 'category_tree_write') has_write_p,
- acs_permission__permission_p(tree_id, :user_id, 'category_tree_read') has_read_p
- from category_trees t
+ acs_permission__permission_p(tree_id, :user_id, 'category_tree_write') as has_write_p,
+ acs_permission__permission_p(tree_id, :user_id, 'category_tree_read') as has_read_p
+ from category_trees
Index: openacs-4/packages/categories/www/cadmin/one-object-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/categories/www/cadmin/Attic/one-object-postgresql.xql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/categories/www/cadmin/one-object-postgresql.xql 23 Apr 2003 12:29:27 -0000 1.1
+++ openacs-4/packages/categories/www/cadmin/one-object-postgresql.xql 19 May 2003 13:10:51 -0000 1.2
@@ -7,7 +7,7 @@
select tree_id, site_wide_p,
- acs_permission__permission_p(tree_id, :user_id, 'category_tree_read') has_read_permission
+ acs_permission__permission_p(tree_id, :user_id, 'category_tree_read') as has_read_permission
from category_trees t
where not exists (select 1 from category_tree_map m
where m.object_id = :object_id
Index: openacs-4/packages/categories/www/cadmin/tree-update-2.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/categories/www/cadmin/Attic/tree-update-2.tcl,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/categories/www/cadmin/tree-update-2.tcl 27 Apr 2003 11:37:12 -0000 1.2
+++ openacs-4/packages/categories/www/cadmin/tree-update-2.tcl 19 May 2003 13:10:51 -0000 1.3
@@ -31,6 +31,7 @@
} on_error {
append list_of_errors " Node [category::get_name $category_id $locale] contains leaf (child) categories. If you really want to delete those leaf categories, plesae delete them first"
}
+db_dml delete_tmp_category_trees ""
if { [llength $list_of_errors] >0 } {
ad_return_complaint "Error Deleting Nodes" $list_of_errors
Index: openacs-4/packages/categories/www/cadmin/tree-update-2.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/categories/www/cadmin/Attic/tree-update-2.xql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/categories/www/cadmin/tree-update-2.xql 23 Apr 2003 12:29:27 -0000 1.1
+++ openacs-4/packages/categories/www/cadmin/tree-update-2.xql 19 May 2003 13:10:51 -0000 1.2
@@ -10,6 +10,12 @@
+
+
+ delete from category_temp
+
+
+