Index: openacs-4/packages/categories/categories.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/categories/categories.info,v
diff -u -N -r1.19.2.3 -r1.19.2.4
--- openacs-4/packages/categories/categories.info 1 Dec 2015 13:38:53 -0000 1.19.2.3
+++ openacs-4/packages/categories/categories.info 14 May 2016 18:54:15 -0000 1.19.2.4
@@ -8,7 +8,7 @@
f
categories
-
+
timo@timohentschel.de
Manage categories in category trees and let users map objects to categories.
2015-10-04
@@ -17,7 +17,7 @@
GPL
1
-
+
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 -N -r1.11 -r1.11.6.1
--- openacs-4/packages/categories/sql/postgresql/categories-create.sql 22 Apr 2008 16:04:38 -0000 1.11
+++ openacs-4/packages/categories/sql/postgresql/categories-create.sql 14 May 2016 18:54:16 -0000 1.11.6.1
@@ -36,9 +36,7 @@
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'))
+ site_wide_p boolean default true
);
comment on table category_trees is '
@@ -91,9 +89,7 @@
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')),
+ deprecated_p boolean default false,
left_ind integer,
right_ind integer
);
@@ -166,12 +162,8 @@
default null
constraint cat_tree_map_subtree_id_fk
references categories,
- assign_single_p char(1) default 'f'
- constraint cat_tree_map_single_p_ck
- check (assign_single_p in ('t','f')),
- require_category_p char(1) default 'f'
- constraint cat_tree_map_categ_p_ck
- check (require_category_p in ('t','f')),
+ assign_single_p boolean default false,
+ require_category_p boolean default false,
widget varchar(20),
primary key (object_id, tree_id)
);
@@ -300,9 +292,7 @@
constraint category_synonyms_locale_fk
references ad_locales on delete cascade,
name varchar(100) not null,
- synonym_p char(1) default 't'
- constraint category_synonyms_synonym_p_ck
- check (synonym_p in ('t','f'))
+ synonym_p boolean default true
);
-- to get all synonyms in given locale
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 -N -r1.8 -r1.8.4.1
--- openacs-4/packages/categories/sql/postgresql/category-package.sql 30 Mar 2013 22:50:44 -0000 1.8
+++ openacs-4/packages/categories/sql/postgresql/category-package.sql 14 May 2016 18:54:16 -0000 1.8.4.1
@@ -20,7 +20,7 @@
p_name varchar,
p_description varchar,
p_parent_id integer,
- p_deprecated_p char,
+ p_deprecated_p boolean,
p_creation_date timestamp with time zone,
p_creation_user integer,
p_creation_ip varchar
@@ -137,7 +137,7 @@
DECLARE
BEGIN
update categories
- set deprecated_p = 't'
+ set deprecated_p = true
where category_id = p_category_id;
return 0;
@@ -159,7 +159,7 @@
DECLARE
BEGIN
update categories
- set deprecated_p = 'f'
+ set deprecated_p = false
where category_id = p_category_id;
return 0;
Index: openacs-4/packages/categories/sql/postgresql/category-synonym-package.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/postgresql/category-synonym-package.sql,v
diff -u -N -r1.3 -r1.3.4.1
--- openacs-4/packages/categories/sql/postgresql/category-synonym-package.sql 30 Mar 2013 22:50:44 -0000 1.3
+++ openacs-4/packages/categories/sql/postgresql/category-synonym-package.sql 14 May 2016 18:54:16 -0000 1.3.4.1
@@ -215,7 +215,7 @@
-- insert synonym data
insert into category_synonyms (synonym_id, category_id, locale, name, synonym_p)
- values (v_synonym_id, p_category_id, p_locale, p_name, 't');
+ values (v_synonym_id, p_category_id, p_locale, p_name, true);
-- insert in synonym index and search results
PERFORM category_synonym__reindex (v_synonym_id, p_name, p_locale);
@@ -304,7 +304,7 @@
-- mark synonym as not editable for users
update category_synonyms
- set synonym_p = 'f'
+ set synonym_p = false
where synonym_id = v_synonym_id;
return new;
@@ -329,7 +329,7 @@
where category_id = OLD.category_id
and name = OLD.name
and locale = OLD.locale
- and synonym_p = 'f';
+ and synonym_p = false;
-- update synonym
PERFORM category_synonym__edit (v_synonym_id, NEW.name, NEW.locale);
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 -N -r1.14 -r1.14.4.1
--- openacs-4/packages/categories/sql/postgresql/category-tree-package.sql 30 Mar 2013 22:50:44 -0000 1.14
+++ openacs-4/packages/categories/sql/postgresql/category-tree-package.sql 14 May 2016 18:54:16 -0000 1.14.4.1
@@ -262,8 +262,8 @@
p_object_id integer,
p_tree_id integer,
p_subtree_category_id integer,
- p_assign_single_p char,
- p_require_category_p char,
+ p_assign_single_p boolean,
+ p_require_category_p boolean,
p_widget varchar
) RETURNS integer AS $$
DECLARE
Index: openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1.3d4-1.1.3d5.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1.3d4-1.1.3d5.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1.3d4-1.1.3d5.sql 14 May 2016 18:54:16 -0000 1.1.2.1
@@ -0,0 +1,246 @@
+--
+-- Alter caveman style booleans (type character(1)) to real SQL boolean types.
+--
+
+ALTER TABLE categories
+ DROP constraint IF EXISTS cat_deprecated_p_ck,
+ ALTER COLUMN deprecated_p DROP DEFAULT,
+ ALTER COLUMN deprecated_p TYPE boolean
+ USING deprecated_p::boolean,
+ ALTER COLUMN deprecated_p SET DEFAULT false;
+
+ALTER TABLE category_synonyms
+ DROP constraint IF EXISTS category_synonyms_synonym_p_ck,
+ ALTER COLUMN synonym_p DROP DEFAULT,
+ ALTER COLUMN synonym_p TYPE boolean
+ USING synonym_p::boolean,
+ ALTER COLUMN synonym_p SET DEFAULT true;
+
+ALTER TABLE category_tree_map
+ DROP constraint IF EXISTS cat_tree_map_single_p_ck,
+ ALTER COLUMN assign_single_p DROP DEFAULT,
+ ALTER COLUMN assign_single_p TYPE boolean
+ USING assign_single_p::boolean,
+ ALTER COLUMN assign_single_p SET DEFAULT false;
+
+ALTER TABLE category_tree_map
+ DROP constraint IF EXISTS cat_tree_map_categ_p_ck,
+ ALTER COLUMN require_category_p DROP DEFAULT,
+ ALTER COLUMN require_category_p TYPE boolean
+ USING require_category_p::boolean,
+ ALTER COLUMN require_category_p SET DEFAULT false;
+
+ALTER TABLE category_trees
+ DROP constraint IF EXISTS cat_trees_site_wide_p_ck,
+ ALTER COLUMN site_wide_p DROP DEFAULT,
+ ALTER COLUMN site_wide_p TYPE boolean
+ USING site_wide_p::boolean,
+ ALTER COLUMN site_wide_p SET DEFAULT true;
+
+
+-- procedure category__new/10
+--
+CREATE OR REPLACE FUNCTION category__new(
+ p_category_id integer,
+ p_tree_id integer,
+ p_locale varchar,
+ p_name varchar,
+ p_description varchar,
+ p_parent_id integer,
+ p_deprecated_p boolean,
+ p_creation_date timestamp with time zone,
+ p_creation_user integer,
+ p_creation_ip varchar
+) RETURNS integer AS $$
+DECLARE
+
+ 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
+ p_name, -- title
+ null -- package_id
+ );
+
+ 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;
+
+--
+-- procedure category_tree__map/6
+--
+CREATE OR REPLACE FUNCTION category_tree__map(
+ p_object_id integer,
+ p_tree_id integer,
+ p_subtree_category_id integer,
+ p_assign_single_p boolean,
+ p_require_category_p boolean,
+ p_widget varchar
+) RETURNS integer AS $$
+DECLARE
+
+ 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,
+ assign_single_p, require_category_p, widget)
+ values (p_tree_id, p_subtree_category_id, p_object_id,
+ p_assign_single_p, p_require_category_p, p_widget);
+ end if;
+ return 0;
+END;
+
+$$ LANGUAGE plpgsql;
+
+--
+-- procedure category_tree__new/9
+--
+CREATE OR REPLACE FUNCTION category_tree__new(
+ p_tree_id integer,
+ p_locale varchar,
+ p_tree_name varchar,
+ p_description varchar,
+ p_site_wide_p boolean,
+ p_creation_date timestamp with time zone,
+ p_creation_user integer,
+ p_creation_ip varchar,
+ p_context_id integer
+) RETURNS integer AS $$
+DECLARE
+
+ 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
+ p_tree_name, -- title
+ null -- package_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;
+
+
+--
+-- procedure category_tree__edit/8
+--
+CREATE OR REPLACE FUNCTION category_tree__edit(
+ p_tree_id integer,
+ p_locale varchar,
+ p_tree_name varchar,
+ p_description varchar,
+ p_site_wide_p boolean,
+ p_modifying_date timestamp with time zone,
+ p_modifying_user integer,
+ p_modifying_ip varchar
+) RETURNS integer AS $$
+DECLARE
+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;