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 -r1.2 -r1.3 --- openacs-4/packages/categories/sql/postgresql/categories-init.sql 8 Feb 2004 17:29:03 -0000 1.2 +++ openacs-4/packages/categories/sql/postgresql/categories-init.sql 30 Mar 2013 22:50:44 -0000 1.3 @@ -52,22 +52,22 @@ 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 +CREATE OR REPLACE 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 + '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'', + 'f', null, null ); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0 (); drop function inline_0 (); Index: openacs-4/packages/categories/sql/postgresql/categories-relation.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/postgresql/categories-relation.sql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/categories/sql/postgresql/categories-relation.sql 30 Nov 2010 18:58:49 -0000 1.3 +++ openacs-4/packages/categories/sql/postgresql/categories-relation.sql 30 Mar 2013 22:50:44 -0000 1.4 @@ -6,54 +6,54 @@ -- @creation-date 2005-07-26 -- -create function inline_0 () -returns integer as ' -begin +CREATE OR REPLACE FUNCTION inline_0 () RETURNS integer AS $$ +BEGIN -- We create the roles to use them on the rel_type create - PERFORM acs_rel_type__create_role(''party'', ''Party'', ''Parties''); - PERFORM acs_rel_type__create_role(''category'', ''Category'', ''Categories''); - PERFORM acs_rel_type__create_role(''meta_category'', ''Meta Category'', ''Meta Categories''); + PERFORM acs_rel_type__create_role('party', 'Party', 'Parties'); + PERFORM acs_rel_type__create_role('category', 'Category', 'Categories'); + PERFORM acs_rel_type__create_role('meta_category', 'Meta Category', 'Meta Categories'); -- Creating two new rel_types PERFORM acs_rel_type__create_type ( - ''meta_category_rel'', -- rel_type - ''Meta Category Relation'', -- pretty_name - ''Meta Category Relation'', -- pretty_plural - ''relationship'', -- supertype + 'meta_category_rel', -- rel_type + 'Meta Category Relation', -- pretty_name + 'Meta Category Relation', -- pretty_plural + 'relationship', -- supertype null, -- table_name null, -- id_column null, -- package_name - ''category'', -- object_type_one - ''category'', -- role_one + 'category', -- object_type_one + 'category', -- role_one 1, -- min_n_rels_one 1, -- max_n_rels_one - ''category'', -- object_type_two - ''category'', -- role_two + 'category', -- object_type_two + 'category', -- role_two 1, -- min_n_rels_two 1 -- max_n_rels_two ); PERFORM acs_rel_type__create_type ( - ''user_meta_category_rel'', -- rel_type - ''User Meta Category Relation'', -- pretty_name - ''User Meta Category Relation'', -- pretty_plural - ''relationship'', -- supertype + 'user_meta_category_rel', -- rel_type + 'User Meta Category Relation', -- pretty_name + 'User Meta Category Relation', -- pretty_plural + 'relationship', -- supertype null, -- table_name null, -- id_column null, -- package_name - ''meta_category_rel'', -- object_type_one - ''meta_category'', -- role_one + 'meta_category_rel', -- object_type_one + 'meta_category', -- role_one 1, -- min_n_rels_one 1, -- max_n_rels_one - ''party'', -- object_type_two - ''party'', -- role_two + 'party', -- object_type_two + 'party', -- role_two 1, -- min_n_rels_two 1 -- max_n_rels_two ); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0 (); Index: openacs-4/packages/categories/sql/postgresql/category-link-package.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/postgresql/category-link-package.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/categories/sql/postgresql/category-link-package.sql 8 Feb 2004 17:30:45 -0000 1.1 +++ openacs-4/packages/categories/sql/postgresql/category-link-package.sql 30 Mar 2013 22:50:44 -0000 1.2 @@ -6,33 +6,48 @@ -- @creation-date 2004-02-04 -- -create or replace function category_link__new ( - integer, -- from_category_id - integer -- to_category_id -) returns integer as ' + + +-- added +select define_function_args('category_link__new','from_category_id,to_category_id'); + +-- +-- procedure category_link__new/2 +-- +CREATE OR REPLACE FUNCTION category_link__new( + p_from_category_id integer, + p_to_category_id integer +) RETURNS integer AS $$ -- function for adding category links -declare - p_from_category_id alias for $1; - p_to_category_id alias for $2; +DECLARE v_link_id integer; -begin - v_link_id := nextval (''category_links_id_seq''); +BEGIN + v_link_id := nextval ('category_links_id_seq'); insert into category_links (link_id, from_category_id, to_category_id) values (v_link_id, p_from_category_id, p_to_category_id); return v_link_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function category_link__del ( - integer -- link_id -) returns integer as ' + + +-- added +select define_function_args('category_link__del','link_id'); + +-- +-- procedure category_link__del/1 +-- +CREATE OR REPLACE FUNCTION category_link__del( + p_link_id integer +) RETURNS integer AS $$ -- function for deleting category links -declare - p_link_id alias for $1; -begin +DECLARE +BEGIN delete from category_links where link_id = p_link_id; return p_link_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; 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 -r1.7 -r1.8 --- openacs-4/packages/categories/sql/postgresql/category-package.sql 1 Oct 2007 09:46:57 -0000 1.7 +++ openacs-4/packages/categories/sql/postgresql/category-package.sql 30 Mar 2013 22:50:44 -0000 1.8 @@ -5,43 +5,40 @@ -- @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 with time zone, -- 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; + +-- added +select define_function_args('category__new','category_id,tree_id,locale,name,description,parent_id,deprecated_p,creation_date,creation_user,creation_ip'); + +-- +-- 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 char, + 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 +BEGIN v_category_id := acs_object__new ( p_category_id, -- object_id - ''category'', -- object_type + '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 + 't', -- security_inherit_p p_name, -- title null -- package_id ); @@ -87,28 +84,29 @@ (v_category_id, p_locale, p_name, p_description); return v_category_id; -end; -' language 'plpgsql'; +END; -create or replace function category__new_translation ( - integer, -- category_id - varchar, -- locale - varchar, -- name - varchar, -- description - timestamp with time zone, -- 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 +$$ LANGUAGE plpgsql; + + + +-- added +select define_function_args('category__new_translation','category_id,locale,name,description,modifying_date,modifying_user,modifying_ip'); + +-- +-- procedure category__new_translation/7 +-- +CREATE OR REPLACE FUNCTION category__new_translation( + p_category_id integer, + p_locale varchar, + p_name varchar, + p_description varchar, + p_modifying_date timestamp with time zone, + p_modifying_user integer, + p_modifying_ip varchar +) RETURNS integer AS $$ +DECLARE +BEGIN insert into category_translations (category_id, locale, name, description) values @@ -121,51 +119,72 @@ where object_id = p_category_id; return 0; -end; -' language 'plpgsql'; +END; -create or replace function category__phase_out ( - integer -- category_id -) -returns integer as ' -declare - p_category_id alias for $1; -begin +$$ LANGUAGE plpgsql; + + + +-- added +select define_function_args('category__phase_out','category_id'); + +-- +-- procedure category__phase_out/1 +-- +CREATE OR REPLACE FUNCTION category__phase_out( + p_category_id integer +) RETURNS integer AS $$ +DECLARE +BEGIN update categories - set deprecated_p = ''t'' + set deprecated_p = 't' where category_id = p_category_id; return 0; -end; -' language 'plpgsql'; +END; -create or replace function category__phase_in ( - integer -- category_id -) -returns integer as ' -declare - p_category_id alias for $1; -begin +$$ LANGUAGE plpgsql; + + + +-- added +select define_function_args('category__phase_in','category_id'); + +-- +-- procedure category__phase_in/1 +-- +CREATE OR REPLACE FUNCTION category__phase_in( + p_category_id integer +) RETURNS integer AS $$ +DECLARE +BEGIN update categories - set deprecated_p = ''f'' + set deprecated_p = 'f' where category_id = p_category_id; return 0; -end; -' language 'plpgsql'; +END; -create or replace function category__del ( - integer -- category_id -) -returns integer as ' -declare - p_category_id alias for $1; +$$ LANGUAGE plpgsql; + + +-- added +select define_function_args('category__del','category_id'); + +-- +-- procedure category__del/1 +-- +CREATE OR REPLACE FUNCTION category__del( + p_category_id integer +) RETURNS integer AS $$ +DECLARE + v_tree_id integer; v_left_ind integer; v_right_ind integer; node record; -begin +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; @@ -198,28 +217,29 @@ -- for debugging reasons perform category_tree__check_nested_ind(v_tree_id); return 0; -end; -' language 'plpgsql'; +END; -create or replace function category__edit ( - integer, -- category_id - varchar, -- locale - varchar, -- name - varchar, -- description - timestamp with time zone, -- 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 +$$ LANGUAGE plpgsql; + + + +-- added +select define_function_args('category__edit','category_id,locale,name,description,modifying_date,modifying_user,modifying_ip'); + +-- +-- procedure category__edit/7 +-- +CREATE OR REPLACE FUNCTION category__edit( + p_category_id integer, + p_locale varchar, + p_name varchar, + p_description varchar, + p_modifying_date timestamp with time zone, + p_modifying_user integer, + p_modifying_ip varchar +) RETURNS integer AS $$ +DECLARE +BEGIN -- change category name update category_translations set name = p_name, @@ -234,26 +254,31 @@ where object_id = p_category_id; return 0; -end; -' language 'plpgsql'; +END; -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_id alias for $3; +$$ LANGUAGE plpgsql; + + +-- added +select define_function_args('category__change_parent','category_id,tree_id,parent_id'); + +-- +-- procedure category__change_parent/3 +-- +CREATE OR REPLACE FUNCTION category__change_parent( + p_category_id integer, + p_tree_id integer, + p_parent_id integer +) RETURNS integer AS $$ +DECLARE + v_old_left_ind integer; v_old_right_ind integer; v_new_left_ind integer; v_new_right_ind integer; v_width integer; -begin +BEGIN update categories set parent_id = p_parent_id where category_id = p_category_id; @@ -323,23 +348,31 @@ perform category_tree__check_nested_ind(p_tree_id); return 0; -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function category__name ( - integer -- category_id -) -returns integer as ' -declare - p_category_id alias for $1; + + + +-- added +select define_function_args('category__name','category_id'); + +-- +-- procedure category__name/1 +-- +CREATE OR REPLACE FUNCTION category__name( + p_category_id integer +) RETURNS integer AS $$ +DECLARE v_name varchar; -begin +BEGIN select name into v_name from category_translations where category_id = p_category_id - and locale = ''en_US''; + and locale = 'en_US'; return 0; -end; -' language 'plpgsql'; +END; + +$$ LANGUAGE plpgsql; 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 -r1.2 -r1.3 --- openacs-4/packages/categories/sql/postgresql/category-synonym-package.sql 24 Feb 2005 13:33:05 -0000 1.2 +++ openacs-4/packages/categories/sql/postgresql/category-synonym-package.sql 30 Mar 2013 22:50:44 -0000 1.3 @@ -7,13 +7,21 @@ -- @creation-date 2004-01-08 -- -create or replace function category_synonym__convert_string (varchar(100)) -returns varchar(200) as ' + + +-- added +select define_function_args('category_synonym__convert_string','name'); + +-- +-- procedure category_synonym__convert_string/1 +-- +CREATE OR REPLACE FUNCTION category_synonym__convert_string( + p_name varchar(100) +) RETURNS varchar(200) AS $$ -- return string to build search index -declare - p_name alias for $1; +DECLARE v_index_string varchar(200); -begin +BEGIN -- convert string to uppercase and substitute special chars -- TODO: complete v_index_string := upper ( @@ -23,39 +31,57 @@ replace ( replace ( replace ( - replace (p_name, ''ä'', ''AE''), - ''Ä'', ''AE''), - ''ö'', ''OE''), - ''Ö'', ''OE''), - ''ü'', ''UE''), - ''Ü'', ''UE''), - ''ß'', ''SS'')); + replace (p_name, 'ä', 'AE'), + 'Ä', 'AE'), + 'ö', 'OE'), + 'Ö', 'OE'), + 'ü', 'UE'), + 'Ü', 'UE'), + 'ß', 'SS')); - return ('' '' || v_index_string || '' ''); -end;' language 'plpgsql'; + return (' ' || v_index_string || ' '); +END; +$$ LANGUAGE plpgsql; -create or replace function category_synonym__get_similarity (integer, integer, bigint) -returns integer as ' + + +-- added +select define_function_args('category_synonym__get_similarity','len1,len2,matches'); + +-- +-- procedure category_synonym__get_similarity/3 +-- +CREATE OR REPLACE FUNCTION category_synonym__get_similarity( + p_len1 integer, + p_len2 integer, + p_matches bigint +) RETURNS integer AS $$ -- calculates similarity of two strings -declare - p_len1 alias for $1; - p_len2 alias for $2; - p_matches alias for $3; -begin +DECLARE +BEGIN return (p_matches * 200 / (p_len1 + p_len2)); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function category_synonym__search (varchar(100), varchar(5)) -returns integer as ' + + +-- added +select define_function_args('category_synonym__search','search_text,locale'); + +-- +-- procedure category_synonym__search/2 +-- +CREATE OR REPLACE FUNCTION category_synonym__search( + p_search_text varchar(100), + p_locale varchar(5) +) RETURNS integer AS $$ -- return id for search string -declare - p_search_text alias for $1; - p_locale alias for $2; +DECLARE v_search_text varchar(200); v_query_id integer; v_len integer; v_i integer; -begin +BEGIN -- check if search text already exists select query_id into v_query_id from category_search @@ -66,20 +92,20 @@ if (v_query_id is not null) then update category_search set queried_count = queried_count + 1, - last_queried = date(''now'') + last_queried = date('now') where query_id = v_query_id; return (v_query_id); end if; -- get new search query id - v_query_id := nextval (''category_search_id_seq''); + v_query_id := nextval ('category_search_id_seq'); -- convert string to uppercase and substitute special chars v_search_text := category_synonym__convert_string (p_search_text); -- insert search data insert into category_search (query_id, search_text, locale, queried_count, last_queried) - values (v_query_id, p_search_text, p_locale, 1, date(''now'')); + values (v_query_id, p_search_text, p_locale, 1, date('now')); -- build search index v_len := length (v_search_text) - 2; @@ -104,19 +130,28 @@ group by s.synonym_id, s.name; return (v_query_id); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function category_synonym__reindex (integer, varchar(100), varchar(5)) -returns integer as ' + + +-- added +select define_function_args('category_synonym__reindex','synonym_id,name,locale'); + +-- +-- procedure category_synonym__reindex/3 +-- +CREATE OR REPLACE FUNCTION category_synonym__reindex( + p_synonym_id integer, + p_name varchar(100), + p_locale varchar(5) +) RETURNS integer AS $$ -- build search index for synonym -declare - p_synonym_id alias for $1; - p_name alias for $2; - p_locale alias for $3; +DECLARE v_name varchar(200); v_len integer; v_i integer; -begin +BEGIN -- delete old search results for this synonym delete from category_search_results where synonym_id = p_synonym_id; @@ -151,40 +186,58 @@ group by s.query_id, s.search_text; return (1); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function category_synonym__new (varchar(100), varchar(5), integer, integer) -returns integer as ' -declare - p_name alias for $1; - p_locale alias for $2; - p_category_id alias for $3; - p_synonym_id alias for $4; + + +-- added +select define_function_args('category_synonym__new','name,locale,category_id,synonym_id'); + +-- +-- procedure category_synonym__new/4 +-- +CREATE OR REPLACE FUNCTION category_synonym__new( + p_name varchar(100), + p_locale varchar(5), + p_category_id integer, + p_synonym_id integer +) RETURNS integer AS $$ +DECLARE v_synonym_id integer; -begin +BEGIN -- get new synonym_id if (p_synonym_id is null) then - v_synonym_id := nextval (''category_synonyms_id_seq''); + v_synonym_id := nextval ('category_synonyms_id_seq'); else v_synonym_id := p_synonym_id; end if; -- 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, 't'); -- insert in synonym index and search results PERFORM category_synonym__reindex (v_synonym_id, p_name, p_locale); return (v_synonym_id); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function category_synonym__del (integer) -returns integer as ' + + +-- added +select define_function_args('category_synonym__del','synonym_id'); + +-- +-- procedure category_synonym__del/1 +-- +CREATE OR REPLACE FUNCTION category_synonym__del( + p_synonym_id integer +) RETURNS integer AS $$ -- delete synonym -declare - p_synonym_id alias for $1; -begin +DECLARE +BEGIN -- delete search results delete from category_search_results where synonym_id = p_synonym_id; @@ -198,15 +251,24 @@ where synonym_id = p_synonym_id; return (1); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function category_synonym__edit (integer, varchar(100), varchar(5)) -returns integer as ' -declare - p_synonym_id alias for $1; - p_new_name alias for $2; - p_locale alias for $3; -begin + + +-- added +select define_function_args('category_synonym__edit','synonym_id,new_name,locale'); + +-- +-- procedure category_synonym__edit/3 +-- +CREATE OR REPLACE FUNCTION category_synonym__edit( + p_synonym_id integer, + p_new_name varchar(100), + p_locale varchar(5) +) RETURNS integer AS $$ +DECLARE +BEGIN -- update synonym data update category_synonyms set name = p_new_name, @@ -217,49 +279,64 @@ PERFORM category_synonym__reindex (p_synonym_id, p_new_name, p_locale); return (p_synonym_id); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; ----- -- triggers for category synonyms ----- -create or replace function category_synonym__new_cat_trans_trg () -returns trigger as ' + + +-- +-- procedure category_synonym__new_cat_trans_trg/0 +-- +CREATE OR REPLACE FUNCTION category_synonym__new_cat_trans_trg( + +) RETURNS trigger AS $$ -- trigger function for inserting category translation -declare +DECLARE v_synonym_id integer; -begin +BEGIN -- create synonym v_synonym_id := category_synonym__new (NEW.name, NEW.locale, NEW.category_id, null); -- mark synonym as not editable for users update category_synonyms - set synonym_p = ''f'' + set synonym_p = 'f' where synonym_id = v_synonym_id; return new; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function category_synonym__edit_cat_trans_trg () -returns trigger as ' + + +-- +-- procedure category_synonym__edit_cat_trans_trg/0 +-- +CREATE OR REPLACE FUNCTION category_synonym__edit_cat_trans_trg( + +) RETURNS trigger AS $$ -- trigger function for updating a category translation -declare +DECLARE v_synonym_id integer; -begin +BEGIN -- get synonym_id of updated category translation select synonym_id into v_synonym_id from category_synonyms where category_id = OLD.category_id and name = OLD.name and locale = OLD.locale - and synonym_p = ''f''; + and synonym_p = 'f'; -- update synonym PERFORM category_synonym__edit (v_synonym_id, NEW.name, NEW.locale); return new; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; create trigger category_synonym__insert_cat_trans_trg 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 -r1.13 -r1.14 --- openacs-4/packages/categories/sql/postgresql/category-tree-package.sql 1 Feb 2009 18:38:02 -0000 1.13 +++ openacs-4/packages/categories/sql/postgresql/category-tree-package.sql 30 Mar 2013 22:50:44 -0000 1.14 @@ -6,34 +6,32 @@ -- @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 with time zone, -- 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; + + +-- added +select define_function_args('category_tree__new','tree_id,locale,tree_name,description,site_wide_p,creation_date,creation_user,creation_ip,context_id'); + +-- +-- 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 char, + 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 +BEGIN v_tree_id := acs_object__new ( p_tree_id, -- object_id - ''category_tree'', -- object_type + 'category_tree', -- object_type p_creation_date, -- creation_date p_creation_user, -- creation_user p_creation_ip, -- creation_ip @@ -50,17 +48,17 @@ perform acs_permission__grant_permission ( v_tree_id, -- object_id p_creation_user, -- grantee_id - ''category_tree_read'' -- privilege + 'category_tree_read' -- privilege ); perform acs_permission__grant_permission ( v_tree_id, -- object_id p_creation_user, -- grantee_id - ''category_tree_write'' -- privilege + 'category_tree_write' -- privilege ); perform acs_permission__grant_permission ( v_tree_id, -- object_id p_creation_user, -- grantee_id - ''category_tree_grant_permissions'' -- privilege + 'category_tree_grant_permissions' -- privilege ); insert into category_tree_translations @@ -69,28 +67,29 @@ (v_tree_id, p_locale, p_tree_name, p_description); return v_tree_id; -end; -' language 'plpgsql'; +END; -create or replace function category_tree__new_translation ( - integer, -- tree_id - varchar, -- locale - varchar, -- tree_name - varchar, -- description - timestamp with time zone, -- 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 +$$ LANGUAGE plpgsql; + + + +-- added +select define_function_args('category_tree__new_translation','tree_id,locale,tree_name,description,modifying_date,modifying_user,modifying_ip'); + +-- +-- procedure category_tree__new_translation/7 +-- +CREATE OR REPLACE FUNCTION category_tree__new_translation( + p_tree_id integer, + p_locale varchar, + p_tree_name varchar, + p_description varchar, + p_modifying_date timestamp with time zone, + p_modifying_user integer, + p_modifying_ip varchar +) RETURNS integer AS $$ +DECLARE +BEGIN insert into category_tree_translations (tree_id, locale, name, description) values @@ -102,17 +101,24 @@ modifying_ip = p_modifying_ip where object_id = p_tree_id; return 0; -end; -' language 'plpgsql'; +END; -create or replace function category_tree__del ( - integer -- tree_id -) -returns integer as ' -declare - p_tree_id alias for $1; -begin +$$ LANGUAGE plpgsql; + + +-- added +select define_function_args('category_tree__del','tree_id'); + +-- +-- procedure category_tree__del/1 +-- +CREATE OR REPLACE FUNCTION category_tree__del( + p_tree_id integer +) RETURNS integer AS $$ +DECLARE +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); @@ -131,30 +137,30 @@ perform acs_object__delete(p_tree_id); return 0; -end; -' language 'plpgsql'; +END; -create or replace function category_tree__edit ( - integer, -- tree_id - varchar, -- locale - varchar, -- tree_name - varchar, -- description - char, -- site_wide_p - timestamp with time zone, -- 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 +$$ LANGUAGE plpgsql; + + + +-- added +select define_function_args('category_tree__edit','tree_id,locale,tree_name,description,site_wide_p,modifying_date,modifying_user,modifying_ip'); + +-- +-- 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 char, + 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; @@ -172,26 +178,30 @@ where object_id = p_tree_id; return 0; -end; -' language 'plpgsql'; +END; -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; +$$ LANGUAGE plpgsql; + + +-- added +select define_function_args('category_tree__copy','source_tree,dest_tree,creation_user,creation_ip'); + +-- +-- procedure category_tree__copy/4 +-- +CREATE OR REPLACE FUNCTION category_tree__copy( + p_source_tree integer, + p_dest_tree integer, + p_creation_user integer, + p_creation_ip varchar +) RETURNS integer AS $$ +DECLARE + v_new_left_ind integer; v_category_id integer; source record; -begin +BEGIN select coalesce(max(right_ind),0) into v_new_left_ind from categories where tree_id = p_dest_tree; @@ -200,7 +210,7 @@ v_category_id := acs_object__new ( null, - ''category'', -- object_type + 'category', -- object_type now(), -- creation_date p_creation_user, -- creation_user p_creation_ip, -- creation_ip @@ -236,28 +246,30 @@ perform category_tree__check_nested_ind(p_dest_tree); return 0; -end; -' language 'plpgsql'; +END; -create or replace function category_tree__map ( - integer, -- object_id - integer, -- tree_id - integer, -- subtree_category_id - char, -- assign_single_p - char, -- require_category_p - varchar -- widget -) -returns integer as ' -declare - p_object_id alias for $1; - p_tree_id alias for $2; - p_subtree_category_id alias for $3; - p_assign_single_p alias for $4; - p_require_category_p alias for $5; - p_widget alias for $6; +$$ LANGUAGE plpgsql; + + +-- added +select define_function_args('category_tree__map','object_id,tree_id,subtree_category_id,assign_single_p,require_category_p,widget'); + +-- +-- 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 char, + p_require_category_p char, + p_widget varchar +) RETURNS integer AS $$ +DECLARE + v_map_count integer; -begin +BEGIN select count(*) into v_map_count from category_tree_map @@ -272,65 +284,85 @@ p_assign_single_p, p_require_category_p, p_widget); end if; return 0; -end; -' language 'plpgsql'; +END; -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 +$$ LANGUAGE plpgsql; + + + +-- added +select define_function_args('category_tree__unmap','object_id,tree_id'); + +-- +-- procedure category_tree__unmap/2 +-- +CREATE OR REPLACE FUNCTION category_tree__unmap( + p_object_id integer, + p_tree_id integer +) RETURNS integer AS $$ +DECLARE +BEGIN delete from category_tree_map where object_id = p_object_id and tree_id = p_tree_id; return 0; -end; -' language 'plpgsql'; +END; -create or replace function category_tree__name ( - integer -- tree_id -) -returns varchar as ' -declare - p_tree_id alias for $1; +$$ LANGUAGE plpgsql; + + + +-- added +select define_function_args('category_tree__name','tree_id'); + +-- +-- procedure category_tree__name/1 +-- +CREATE OR REPLACE FUNCTION category_tree__name( + p_tree_id integer +) RETURNS varchar AS $$ +DECLARE v_name varchar; -begin +BEGIN select name into v_name from category_tree_translations where tree_id = p_tree_id - and locale = ''en_US''; + and locale = 'en_US'; return v_name; -end; -' language 'plpgsql'; +END; -create or replace function category_tree__check_nested_ind ( - integer -- tree_id -) -returns integer as ' -declare - p_tree_id alias for $1; +$$ LANGUAGE plpgsql; + + + +-- added +select define_function_args('category_tree__check_nested_ind','tree_id'); + +-- +-- procedure category_tree__check_nested_ind/1 +-- +CREATE OR REPLACE FUNCTION category_tree__check_nested_ind( + p_tree_id integer +) RETURNS integer AS $$ +DECLARE v_negative numeric; v_order numeric; v_parent numeric; -begin +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!''; + 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!''; + raise EXCEPTION '-20002: right index must be greater than left index!'; end if; select count(*) into v_parent @@ -341,9 +373,10 @@ and child.parent_id = parent.category_id; if v_parent > 0 then - raise EXCEPTION ''-20003: child index must be between parent index!''; + raise EXCEPTION '-20003: child index must be between parent index!'; end if; return 0; -end; -' language 'plpgsql'; +END; + +$$ LANGUAGE plpgsql; Index: openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.0d1-1.0d2.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.0d1-1.0d2.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.0d1-1.0d2.sql 1 Feb 2004 19:12:51 -0000 1.1 +++ openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.0d1-1.0d2.sql 30 Mar 2013 22:50:44 -0000 1.2 @@ -19,23 +19,25 @@ drop function category_tree__map (integer,integer,integer); -create or replace function category_tree__map ( - integer, -- object_id - integer, -- tree_id - integer, -- subtree_category_id - char, -- assign_single_p - char -- require_category_p -) -returns integer as ' -declare - p_object_id alias for $1; - p_tree_id alias for $2; - p_subtree_category_id alias for $3; - p_assign_single_p alias for $4; - p_require_category_p alias for $5; + +-- added +select define_function_args('category_tree__map','object_id,tree_id,subtree_category_id,assign_single_p,require_category_p'); + +-- +-- procedure category_tree__map/5 +-- +CREATE OR REPLACE FUNCTION category_tree__map( + p_object_id integer, + p_tree_id integer, + p_subtree_category_id integer, + p_assign_single_p char, + p_require_category_p char +) RETURNS integer AS $$ +DECLARE + v_map_count integer; -begin +BEGIN select count(*) into v_map_count from category_tree_map @@ -50,5 +52,6 @@ p_assign_single_p, p_require_category_p); end if; return 0; -end; -' language 'plpgsql'; +END; + +$$ LANGUAGE plpgsql; Index: openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.0d3-1.0d4.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.0d3-1.0d4.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.0d3-1.0d4.sql 11 Feb 2004 13:25:27 -0000 1.1 +++ openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.0d3-1.0d4.sql 30 Mar 2013 22:50:44 -0000 1.2 @@ -181,20 +181,27 @@ -- insert existing category translations as synonyms -- and build synonym index -create function inline_0 () -returns integer as ' -declare + + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( + +) RETURNS integer AS $$ +DECLARE rec_translations record; v_synonym_id integer; -begin +BEGIN for rec_translations in select category_id, name, locale from category_translations loop v_synonym_id := category_synonym__new (rec_translations.name, rec_translations.locale, rec_translations.category_id, null); - update category_synonyms set synonym_p = ''f'' where synonym_id = v_synonym_id; + update category_synonyms set synonym_p = 'f' where synonym_id = v_synonym_id; end loop; return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0 (); drop function inline_0 (); Index: openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.0d6-1.0d7.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.0d6-1.0d7.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.0d6-1.0d7.sql 24 Feb 2005 13:33:05 -0000 1.2 +++ openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.0d6-1.0d7.sql 30 Mar 2013 22:50:44 -0000 1.3 @@ -21,17 +21,23 @@ -- fix entries destroyed by old procs -- ---- -create function inline_0 () -returns integer as ' -declare + + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( + +) RETURNS integer AS $$ +DECLARE v_name category_translations.name%TYPE; v_synonym_cursor RECORD; -begin +BEGIN FOR v_synonym_cursor IN select category_id, locale from category_synonyms - where synonym_p = ''f'' + where synonym_p = 'f' LOOP select name into v_name from category_translations @@ -45,9 +51,10 @@ END LOOP; return 0; -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; + select inline_0 (); drop function inline_0 (); @@ -58,42 +65,56 @@ -- ----- -create or replace function category_synonym__new_cat_trans_trg () -returns trigger as ' + + +-- +-- procedure category_synonym__new_cat_trans_trg/0 +-- +CREATE OR REPLACE FUNCTION category_synonym__new_cat_trans_trg( + +) RETURNS trigger AS $$ -- trigger function for inserting category translation -declare +DECLARE v_synonym_id integer; -begin +BEGIN -- create synonym v_synonym_id := category_synonym__new (NEW.name, NEW.locale, NEW.category_id, null); -- mark synonym as not editable for users update category_synonyms - set synonym_p = ''f'' + set synonym_p = 'f' where synonym_id = v_synonym_id; return new; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function category_synonym__edit_cat_trans_trg () -returns trigger as ' + + +-- +-- procedure category_synonym__edit_cat_trans_trg/0 +-- +CREATE OR REPLACE FUNCTION category_synonym__edit_cat_trans_trg( + +) RETURNS trigger AS $$ -- trigger function for updating a category translation -declare +DECLARE v_synonym_id integer; -begin +BEGIN -- get synonym_id of updated category translation select synonym_id into v_synonym_id from category_synonyms where category_id = OLD.category_id and name = OLD.name and locale = OLD.locale - and synonym_p = ''f''; + and synonym_p = 'f'; -- update synonym PERFORM category_synonym__edit (v_synonym_id, NEW.name, NEW.locale); return new; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; ----- @@ -118,25 +139,25 @@ -- fix them to have spaces -- ----- -create or replace function category__edit ( - integer, -- category_id - varchar, -- locale - varchar, -- name - varchar, -- description - timestamp with time zone, -- 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 + + +-- added +select define_function_args('category__edit','category_id,locale,name,description,modifying_date,modifying_user,modifying_ip'); + +-- +-- procedure category__edit/7 +-- +CREATE OR REPLACE FUNCTION category__edit( + p_category_id integer, + p_locale varchar, + p_name varchar, + p_description varchar, + p_modifying_date timestamp with time zone, + p_modifying_user integer, + p_modifying_ip varchar +) RETURNS integer AS $$ +DECLARE +BEGIN -- change category name update category_translations set name = p_name, @@ -151,5 +172,6 @@ where object_id = p_category_id; return 0; -end; -' language 'plpgsql'; +END; + +$$ LANGUAGE plpgsql; Index: openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1.2d1-1.1.2d2.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1.2d1-1.1.2d2.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1.2d1-1.1.2d2.sql 16 Sep 2007 10:37:02 -0000 1.1 +++ openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1.2d1-1.1.2d2.sql 30 Mar 2013 22:50:44 -0000 1.2 @@ -2,25 +2,26 @@ drop function category_tree__map ( integer, integer, integer, char, char ); -create or replace function category_tree__map ( - integer, -- object_id - integer, -- tree_id - integer, -- subtree_category_id - char, -- assign_single_p - char, -- require_category_p - varchar -- widget -) -returns integer as ' -declare - p_object_id alias for $1; - p_tree_id alias for $2; - p_subtree_category_id alias for $3; - p_assign_single_p alias for $4; - p_require_category_p alias for $5; - p_widget alias for $6; + +-- added +select define_function_args('category_tree__map','object_id,tree_id,subtree_category_id,assign_single_p,require_category_p,widget'); + +-- +-- 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 char, + p_require_category_p char, + p_widget varchar +) RETURNS integer AS $$ +DECLARE + v_map_count integer; -begin +BEGIN select count(*) into v_map_count from category_tree_map @@ -35,5 +36,6 @@ p_assign_single_p, p_require_category_p, p_widget); end if; return 0; -end; -' language 'plpgsql'; +END; + +$$ LANGUAGE plpgsql; Index: openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1.2d2-1.1.2d3.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1.2d2-1.1.2d3.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1.2d2-1.1.2d3.sql 1 Oct 2007 10:15:43 -0000 1.1 +++ openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1.2d2-1.1.2d3.sql 30 Mar 2013 22:50:44 -0000 1.2 @@ -1,18 +1,25 @@ -- add missing alias for $1 -create or replace function category__name ( - integer -- category_id -) -returns integer as ' -declare - p_category_id alias for $1; + + +-- added +select define_function_args('category__name','category_id'); + +-- +-- procedure category__name/1 +-- +CREATE OR REPLACE FUNCTION category__name( + p_category_id integer +) RETURNS integer AS $$ +DECLARE v_name varchar; -begin +BEGIN select name into v_name from category_translations where category_id = p_category_id - and locale = ''en_US''; + and locale = 'en_US'; return 0; -end; -' language 'plpgsql'; \ No newline at end of file +END; + +$$ LANGUAGE plpgsql; \ No newline at end of file Index: openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1.2d3-1.1.2d4.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1.2d3-1.1.2d4.sql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1.2d3-1.1.2d4.sql 1 Feb 2009 18:38:02 -0000 1.3 +++ openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1.2d3-1.1.2d4.sql 30 Mar 2013 22:50:44 -0000 1.4 @@ -1,20 +1,21 @@ -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; +-- added +select define_function_args('category_tree__copy','source_tree,dest_tree,creation_user,creation_ip'); + +-- +-- procedure category_tree__copy/4 +-- +CREATE OR REPLACE FUNCTION category_tree__copy( + p_source_tree integer, + p_dest_tree integer, + p_creation_user integer, + p_creation_ip varchar +) RETURNS integer AS $$ +DECLARE v_new_left_ind integer; v_category_id integer; source record; -begin +BEGIN select coalesce(max(right_ind),0) into v_new_left_ind from categories where tree_id = p_dest_tree; @@ -23,7 +24,7 @@ v_category_id := acs_object__new ( null, - ''category'', -- object_type + 'category', -- object_type now(), -- creation_date p_creation_user, -- creation_user p_creation_ip, -- creation_ip @@ -59,5 +60,6 @@ perform category_tree__check_nested_ind(p_dest_tree); return 0; -end; -' language 'plpgsql'; +END; + +$$ LANGUAGE plpgsql; Index: openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1d1-1.1d2.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1d1-1.1d2.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1d1-1.1d2.sql 24 Jul 2004 15:14:24 -0000 1.1 +++ openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1d1-1.1d2.sql 30 Mar 2013 22:50:44 -0000 1.2 @@ -1,30 +1,33 @@ -- source was undeclared. -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; + +-- added +select define_function_args('category_tree__copy','source_tree,dest_tree,creation_user,creation_ip'); + +-- +-- procedure category_tree__copy/4 +-- +CREATE OR REPLACE FUNCTION category_tree__copy( + p_source_tree integer, + p_dest_tree integer, + p_creation_user integer, + p_creation_ip varchar +) RETURNS integer AS $$ +DECLARE + v_new_left_ind integer; v_category_id integer; source record; -begin +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 + 'category', -- object_type now(), -- creation_date p_creation_user, -- creation_user p_creation_ip, -- creation_ip @@ -60,6 +63,7 @@ perform category_tree__check_nested_ind(p_dest_tree); return 0; -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; + Index: openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1d2-1.1d3.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1d2-1.1d3.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1d2-1.1d3.sql 6 Feb 2005 18:42:19 -0000 1.1 +++ openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1d2-1.1d3.sql 30 Mar 2013 22:50:44 -0000 1.2 @@ -4,43 +4,40 @@ -- @author Jeff Davis -- @creation-date 2005-02-06 -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 with time zone, -- 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; + +-- added +select define_function_args('category__new','category_id,tree_id,locale,name,description,parent_id,deprecated_p,creation_date,creation_user,creation_ip'); + +-- +-- 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 char, + 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 +BEGIN v_category_id := acs_object__new ( p_category_id, -- object_id - ''category'', -- object_type + '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 + 't', -- security_inherit_p p_name, -- title null -- package_id ); @@ -86,38 +83,37 @@ (v_category_id, p_locale, p_name, p_description); return v_category_id; -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function category_tree__new ( - integer, -- tree_id - varchar, -- locale - varchar, -- tree_name - varchar, -- description - char, -- site_wide_p - timestamp with time zone, -- 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; + + + +-- added +select define_function_args('category_tree__new','tree_id,locale,tree_name,description,site_wide_p,creation_date,creation_user,creation_ip,context_id'); + +-- +-- 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 char, + 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 +BEGIN v_tree_id := acs_object__new ( p_tree_id, -- object_id - ''category_tree'', -- object_type + 'category_tree', -- object_type p_creation_date, -- creation_date p_creation_user, -- creation_user p_creation_ip, -- creation_ip @@ -134,17 +130,17 @@ perform acs_permission__grant_permission ( v_tree_id, -- object_id p_creation_user, -- grantee_id - ''category_tree_read'' -- privilege + 'category_tree_read' -- privilege ); perform acs_permission__grant_permission ( v_tree_id, -- object_id p_creation_user, -- grantee_id - ''category_tree_write'' -- privilege + 'category_tree_write' -- privilege ); perform acs_permission__grant_permission ( v_tree_id, -- object_id p_creation_user, -- grantee_id - ''category_tree_grant_permissions'' -- privilege + 'category_tree_grant_permissions' -- privilege ); insert into category_tree_translations @@ -153,6 +149,7 @@ (v_tree_id, p_locale, p_tree_name, p_description); return v_tree_id; -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; + Index: openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1d3-1.1d4.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1d3-1.1d4.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1d3-1.1d4.sql 31 Jul 2006 20:31:11 -0000 1.2 +++ openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.1d3-1.1d4.sql 30 Mar 2013 22:50:44 -0000 1.3 @@ -6,54 +6,54 @@ -- @creation-date 2005-07-26 -- -create function inline_0 () -returns integer as ' -begin +CREATE OR REPLACE FUNCTION inline_0 () RETURNS integer AS $$ +BEGIN -- We create two roles to use them on the rel_type create - PERFORM acs_rel_type__create_role(''party'', ''Party'', ''Parties''); - PERFORM acs_rel_type__create_role(''category'', ''Category'', ''Categories''); - PERFORM acs_rel_type__create_role(''meta_category'', ''Meta Category'', ''Meta Categories''); + PERFORM acs_rel_type__create_role('party', 'Party', 'Parties'); + PERFORM acs_rel_type__create_role('category', 'Category', 'Categories'); + PERFORM acs_rel_type__create_role('meta_category', 'Meta Category', 'Meta Categories'); -- Creating two new rel_types PERFORM acs_rel_type__create_type ( - ''meta_category_rel'', -- rel_type - ''Meta Category Relation'', -- pretty_name - ''Meta Category Relation'', -- pretty_plural - ''relationship'', -- supertype - ''meta_categories'', -- table_name - ''meta_category_id'', -- id_column + 'meta_category_rel', -- rel_type + 'Meta Category Relation', -- pretty_name + 'Meta Category Relation', -- pretty_plural + 'relationship', -- supertype + 'meta_categories', -- table_name + 'meta_category_id', -- id_column null, -- package_name - ''category'', -- object_type_one - ''category'', -- role_one + 'category', -- object_type_one + 'category', -- role_one 1, -- min_n_rels_one 1, -- max_n_rels_one - ''category'', -- object_type_two - ''category'', -- role_two + 'category', -- object_type_two + 'category', -- role_two 1, -- min_n_rels_two 1 -- max_n_rels_two ); PERFORM acs_rel_type__create_type ( - ''user_meta_category_rel'', -- rel_type - ''User Meta Category Relation'', -- pretty_name - ''User Meta Category Relation'', -- pretty_plural - ''relationship'', -- supertype - ''user_meta_categories'', -- table_name - ''user_meta_category_id'', -- id_column + 'user_meta_category_rel', -- rel_type + 'User Meta Category Relation', -- pretty_name + 'User Meta Category Relation', -- pretty_plural + 'relationship', -- supertype + 'user_meta_categories', -- table_name + 'user_meta_category_id', -- id_column null, -- package_name - ''meta_category_rel'', -- object_type_one - ''meta_category'', -- role_one + 'meta_category_rel', -- object_type_one + 'meta_category', -- role_one 1, -- min_n_rels_one 1, -- max_n_rels_one - ''party'', -- object_type_two - ''party'', -- role_two + 'party', -- object_type_two + 'party', -- role_two 1, -- min_n_rels_two 1 -- max_n_rels_two ); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0 (); Index: openacs-4/packages/file-storage/sql/postgresql/file-storage-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/file-storage-create.sql,v diff -u -r1.23 -r1.24 --- openacs-4/packages/file-storage/sql/postgresql/file-storage-create.sql 12 Oct 2009 22:46:16 -0000 1.23 +++ openacs-4/packages/file-storage/sql/postgresql/file-storage-create.sql 30 Mar 2013 22:50:45 -0000 1.24 @@ -66,35 +66,36 @@ 'file_storage__get_title' -- name_method ); -create or replace function inline_0 () -returns integer as' -declare +CREATE OR REPLACE FUNCTION inline_0 () +RETURNS integer AS $$ +DECLARE template_id integer; -begin +BEGIN -- Create the (default) file_storage_object content type template template_id := content_template__new( - ''file-storage-default'', -- name - '' + 'file-storage-default', -- name + ' @title;noquote@ @context;noquote@ @item_id;noquote@ -@text;noquote@'', -- text +@text;noquote@', -- text true -- is_live ); -- Register the template for the file_storage_object content type perform content_type__register_template( - ''file_storage_object'', -- content_type + 'file_storage_object', -- content_type template_id, -- template_id - ''public'', -- use_context - ''t'' -- is_default + 'public', -- use_context + 't' -- is_default ); return null; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0(); drop function inline_0(); Index: openacs-4/packages/file-storage/sql/postgresql/file-storage-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/file-storage-drop.sql,v diff -u -r1.13 -r1.14 --- openacs-4/packages/file-storage/sql/postgresql/file-storage-drop.sql 5 Dec 2009 02:08:14 -0000 1.13 +++ openacs-4/packages/file-storage/sql/postgresql/file-storage-drop.sql 30 Mar 2013 22:50:45 -0000 1.14 @@ -15,12 +15,18 @@ -- content repository is set up to cascade, so we should just have to -- delete the root folders -- -create function inline_0() -returns integer as ' -declare + + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( + +) RETURNS integer AS $$ +DECLARE rec_root_folder record; template_id integer; -begin +BEGIN for rec_root_folder in select package_id @@ -32,13 +38,14 @@ end loop; -- Unregister the content template - template_id := content_type__get_template(''file_storage_object'',''public''); + template_id := content_type__get_template('file_storage_object','public'); - perform content_type__unregister_template (''file_storage_object'', template_id, ''public''); + perform content_type__unregister_template ('file_storage_object', template_id, 'public'); perform content_template__del(template_id); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0(); drop function inline_0(); Index: openacs-4/packages/file-storage/sql/postgresql/file-storage-notifications-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/file-storage-notifications-create.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/file-storage/sql/postgresql/file-storage-notifications-create.sql 13 Jan 2005 13:58:11 -0000 1.2 +++ openacs-4/packages/file-storage/sql/postgresql/file-storage-notifications-create.sql 30 Mar 2013 22:50:45 -0000 1.3 @@ -1,43 +1,49 @@ -create function inline_0() returns integer as ' -declare + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( + +) RETURNS integer AS $$ +DECLARE impl_id integer; v_foo integer; -begin +BEGIN -- the notification type impl impl_id := acs_sc_impl__new ( - ''NotificationType'', - ''fs_fs_notif_type'', - ''file_storage'', - ''file_storage'' + 'NotificationType', + 'fs_fs_notif_type', + 'file_storage', + 'file_storage' ); v_foo := acs_sc_impl_alias__new ( - ''NotificationType'', -- impl_contract_name - ''fs_fs_notif_type'', -- impl_name - ''GetURL'', -- impl_operation_name - ''fs::notification::get_url'', -- impl_alias - ''TCL'' -- impl_pl + 'NotificationType', -- impl_contract_name + 'fs_fs_notif_type', -- impl_name + 'GetURL', -- impl_operation_name + 'fs::notification::get_url', -- impl_alias + 'TCL' -- impl_pl ); v_foo := acs_sc_impl_alias__new ( - ''NotificationType'', - ''fs_fs_notif_type'', - ''ProcessReply'', - '' fs::notification::process_reply'', - ''TCL'' + 'NotificationType', + 'fs_fs_notif_type', + 'ProcessReply', + ' fs::notification::process_reply', + 'TCL' ); PERFORM acs_sc_binding__new ( - ''NotificationType'', - ''fs_fs_notif_type'' + 'NotificationType', + 'fs_fs_notif_type' ); v_foo:= notification_type__new ( NULL, impl_id, - ''fs_fs_notif'', - ''File-Storage Notification'', - ''Notifications for File Storage'', + 'fs_fs_notif', + 'File-Storage Notification', + 'Notifications for File Storage', now(), NULL, NULL, @@ -48,17 +54,18 @@ insert into notification_types_intervals (type_id, interval_id) select v_foo, interval_id - from notification_intervals where name in (''instant'',''hourly'',''daily''); + from notification_intervals where name in ('instant','hourly','daily'); insert into notification_types_del_methods (type_id, delivery_method_id) select v_foo, delivery_method_id - from notification_delivery_methods where short_name in (''email''); + from notification_delivery_methods where short_name in ('email'); return (0); -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; + select inline_0(); drop function inline_0(); Index: openacs-4/packages/file-storage/sql/postgresql/file-storage-notifications-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/file-storage-notifications-drop.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/file-storage/sql/postgresql/file-storage-notifications-drop.sql 5 Dec 2009 02:08:14 -0000 1.2 +++ openacs-4/packages/file-storage/sql/postgresql/file-storage-notifications-drop.sql 30 Mar 2013 22:50:45 -0000 1.3 @@ -1,60 +1,68 @@ -create function inline_0 () -returns integer as ' -declare + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( +) RETURNS integer AS $$ +DECLARE row record; -begin +BEGIN for row in select type_id from notification_types - where short_name in (''fs_fs_notif'') + where short_name in ('fs_fs_notif') loop perform notification_type__delete(row.type_id); end loop; return null; +END; +$$ LANGUAGE plpgsql; -end; -' language 'plpgsql'; - select inline_0(); drop function inline_0(); -create function inline_0() returns integer as ' -declare + + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( +) RETURNS integer AS $$ +DECLARE impl_id integer; v_foo integer; -begin - +BEGIN -- the notification type impl impl_id := acs_sc_impl__get_id ( - ''NotificationType'', -- impl_contract_name - ''fs_fs_notif_type'' -- impl_name + 'NotificationType', -- impl_contract_name + 'fs_fs_notif_type' -- impl_name ); perform acs_sc_binding__delete ( - ''NotificationType'', - ''fs_fs_notif_type'' + 'NotificationType', + 'fs_fs_notif_type' ); v_foo := acs_sc_impl_alias__delete ( - ''NotificationType'', -- impl_contract_name - ''fs_fs_notif_type'', -- impl_name - ''GetURL'' -- impl_operation_name + 'NotificationType', -- impl_contract_name + 'fs_fs_notif_type', -- impl_name + 'GetURL' -- impl_operation_name ); v_foo := acs_sc_impl_alias__delete ( - ''NotificationType'', -- impl_contract_name - ''fs_fs_notif_type'', -- impl_name - ''ProcessReply'' -- impl_operation_name + 'NotificationType', -- impl_contract_name + 'fs_fs_notif_type', -- impl_name + 'ProcessReply' -- impl_operation_name ); perform acs_sc_impl__delete( - ''NotificationType'', -- impl_contract_name - ''fs_fs_notif_type'' -- impl_name + 'NotificationType', -- impl_contract_name + 'fs_fs_notif_type' -- impl_name ); select into v_foo type_id from notification_types where sc_impl_id = impl_id - and short_name = ''fs_fs_notif''; + and short_name = 'fs_fs_notif'; perform notification_type__delete (v_foo); @@ -63,20 +71,20 @@ and interval_id in ( select interval_id from notification_intervals - where name in (''instant'',''hourly'',''daily'') + where name in ('instant','hourly','daily') ); delete from notification_types_del_methods where type_id = v_foo and delivery_method_id in ( select delivery_method_id from notification_delivery_methods - where short_name in (''email'') + where short_name in ('email') ); return (0); -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0(); drop function inline_0(); Index: openacs-4/packages/file-storage/sql/postgresql/file-storage-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/file-storage-package-create.sql,v diff -u -r1.25 -r1.26 --- openacs-4/packages/file-storage/sql/postgresql/file-storage-package-create.sql 9 Jul 2007 07:43:06 -0000 1.25 +++ openacs-4/packages/file-storage/sql/postgresql/file-storage-package-create.sql 30 Mar 2013 22:50:45 -0000 1.26 @@ -6,34 +6,51 @@ -- @cvs-id $Id$ -- -create or replace function file_storage__get_root_folder ( - -- - -- Returns the root folder corresponding to a particular - -- package instance. - -- - integer -- apm_packages.package_id%TYPE -) -returns integer as ' -- fs_root_folders.folder_id%TYPE -declare - get_root_folder__package_id alias for $1; + + +-- added +select define_function_args('file_storage__get_root_folder','package_id'); + +-- +-- procedure file_storage__get_root_folder/1 +-- + -- + -- Returns the root folder corresponding to a particular + -- package instance. + -- +CREATE OR REPLACE FUNCTION file_storage__get_root_folder( + get_root_folder__package_id integer + +) RETURNS integer AS $$ +-- fs_root_folders.folder_id%TYPE +DECLARE v_folder_id fs_root_folders.folder_id%TYPE; -begin +BEGIN select folder_id into v_folder_id from fs_root_folders where package_id = get_root_folder__package_id; return v_folder_id; -end;' language 'plpgsql' stable; +END; +$$ LANGUAGE plpgsql stable; -create or replace function file_storage__get_package_id ( - integer -- cr_items.item_id%TYPE -) returns integer as ' -- fs_root_folders.package_id%TYPE -declare - get_package_id__item_id alias for $1; + + +-- added +select define_function_args('file_storage__get_package_id','item_id'); + +-- +-- procedure file_storage__get_package_id/1 +-- +CREATE OR REPLACE FUNCTION file_storage__get_package_id( + get_package_id__item_id integer +) RETURNS integer AS $$ +-- fs_root_folders.package_id%TYPE +DECLARE v_package_id fs_root_folders.package_id%TYPE; v_tree_sortkey cr_items.tree_sortkey%TYPE; -begin +BEGIN select fs_root_folders.package_id into v_package_id @@ -50,31 +67,35 @@ return v_package_id; end if; -end;' language 'plpgsql' stable; +END; +$$ LANGUAGE plpgsql stable; -create or replace function file_storage__new_root_folder ( - -- - -- Creates a new root folder - -- - -- - -- A hackish function to get around the fact that we can not run - -- code automatically when a new package instance is created. - -- - integer, -- apm_packages.package_id%TYPE - varchar, -- cr_folders.label%TYPE - varchar, -- cr_items.name%TYPE - varchar -) -returns integer as ' -- fs_root_folders.folder_id%TYPE -declare - new_root_folder__package_id alias for $1; - new_root_folder__folder_name alias for $2; - new_root_folder__url alias for $3; - new_root_folder__description alias for $4; - v_folder_id fs_root_folders.folder_id%TYPE; -begin +-- added +select define_function_args('file_storage__new_root_folder','package_id,folder_name,url,description'); + +-- +-- procedure file_storage__new_root_folder/4 +-- + -- + -- Creates a new root folder + -- + -- + -- A hackish function to get around the fact that we can not run + -- code automatically when a new package instance is created. + -- + +CREATE OR REPLACE FUNCTION file_storage__new_root_folder( + new_root_folder__package_id integer, + new_root_folder__folder_name varchar, + new_root_folder__url varchar, + new_root_folder__description varchar + +) RETURNS integer AS $$ +DECLARE + v_folder_id fs_root_folders.folder_id%TYPE; +BEGIN v_folder_id := content_folder__new ( new_root_folder__url, -- name new_root_folder__folder_name, -- label @@ -98,56 +119,59 @@ -- JS: true since we created a new subtype. PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_revision'', -- content_types - ''t'' -- include_subtypes + 'content_revision', -- content_types + 't' -- include_subtypes ); PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_folder'', -- content_types - ''t'' -- include_subtypes + 'content_folder', -- content_types + 't' -- include_subtypes ); PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_symlink'', -- content_types - ''t'' -- include_subtypes + 'content_symlink', -- content_types + 't' -- include_subtypes ); PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_extlink'', -- content_types - ''t'' -- include_subtypes + 'content_extlink', -- content_types + 't' -- include_subtypes ); return v_folder_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__new_file( - -- - -- Create a file in CR in preparation for actual storage - -- Wrapper for content_item__new - -- - -- DRB: I added this version to allow one to predefine item_id, among other things to - -- make it easier to use with ad_form - varchar, -- cr_items.name%TYPE, - integer, -- cr_items.parent_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar, -- acs_objects.creation_ip%TYPE, - boolean, -- store in db? - integer, -- cr_items.item_id%TYPE, - integer -- apm_packages.package_id%TYPE -) returns integer as ' -- cr_items.item_id%TYPE -declare - new_file__name alias for $1; - new_file__folder_id alias for $2; - new_file__user_id alias for $3; - new_file__creation_ip alias for $4; - new_file__indb_p alias for $5; - new_file__item_id alias for $6; - new_file__package_id alias for $7; - v_item_id integer; -begin + +-- added +select define_function_args('file_storage__new_file','name,folder_id,user_id,creation_ip,indb_p,item_id,package_id'); + +-- +-- procedure file_storage__new_file/7 +-- + -- + -- Create a file in CR in preparation for actual storage + -- Wrapper for content_item__new + -- + -- DRB: I added this version to allow one to predefine item_id, among other things to + -- make it easier to use with ad_form + +CREATE OR REPLACE FUNCTION file_storage__new_file( + new_file__name varchar, + new_file__folder_id integer, + new_file__user_id integer, + new_file__creation_ip varchar, + new_file__indb_p boolean, + new_file__item_id integer, + new_file__package_id integer + +) RETURNS integer AS $$ +DECLARE + v_item_id integer; +BEGIN if new_file__indb_p then v_item_id := content_item__new ( @@ -159,11 +183,11 @@ new_file__user_id, -- creation_user new_file__folder_id, -- context_id new_file__creation_ip, -- creation_ip - ''content_item'', -- item_subtype (default) - ''file_storage_object'', -- content_type (needed by site-wide search) + 'content_item', -- item_subtype (default) + 'file_storage_object', -- content_type (needed by site-wide search) null, -- title (default) null, -- description - ''text/plain'', -- mime_type (default) + 'text/plain', -- mime_type (default) null, -- nls_language (default) null, -- data (default) new_file__package_id -- package_id @@ -178,14 +202,14 @@ new_file__user_id, -- creation_user new_file__folder_id, -- context_id new_file__creation_ip, -- creation_ip - ''content_item'', -- item_subtype (default) - ''file_storage_object'', -- content_type (needed by site-wide search) + 'content_item', -- item_subtype (default) + 'file_storage_object', -- content_type (needed by site-wide search) null, -- title (default) null, -- description - ''text/plain'', -- mime_type (default) + 'text/plain', -- mime_type (default) null, -- nls_language (default) null, -- text (default) - ''file'', -- storage_type + 'file', -- storage_type new_file__package_id -- package_id ); @@ -195,25 +219,25 @@ return v_item_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__new_file( - varchar, -- cr_items.name%TYPE, - integer, -- cr_items.parent_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar, -- acs_objects.creation_ip%TYPE, - boolean, -- store in db? - integer -- apm_packages.package_id%TYPE -) returns integer as ' -- cr_items.item_id%TYPE -declare - new_file__name alias for $1; - new_file__folder_id alias for $2; - new_file__user_id alias for $3; - new_file__creation_ip alias for $4; - new_file__indb_p alias for $5; - new_file__package_id alias for $6; -begin + + +-- +-- procedure file_storage__new_file/6 +-- +CREATE OR REPLACE FUNCTION file_storage__new_file( + new_file__name varchar, + new_file__folder_id integer, + new_file__user_id integer, + new_file__creation_ip varchar, + new_file__indb_p boolean, + new_file__package_id integer +) RETURNS integer AS $$ +DECLARE +BEGIN return file_storage__new_file( new_file__name, -- name new_file__folder_id, -- parent_id @@ -224,61 +248,83 @@ new_file__package_id -- pacakge_id ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__delete_file ( - -- - -- Delete a file and all its version - -- Wrapper to content_item__delete - -- - integer -- cr_items.item_id%TYPE -) returns integer as ' -declare - delete_file__file_id alias for $1; -begin + +-- added +select define_function_args('file_storage__delete_file','file_id'); + +-- +-- procedure file_storage__delete_file/1 +-- + -- + -- Delete a file and all its version + -- Wrapper to content_item__delete + -- +CREATE OR REPLACE FUNCTION file_storage__delete_file( + delete_file__file_id integer + +) RETURNS integer AS $$ +DECLARE +BEGIN + return content_item__delete(delete_file__file_id); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__rename_file ( - -- - -- Rename a file and all - -- Wrapper to content_item__edit_name - -- - integer, -- cr_items.item_id%TYPE, - varchar -- cr_items.name%TYPE -) returns integer as ' -declare - rename_file__file_id alias for $1; - rename_file__name alias for $2; -begin +-- added +select define_function_args('file_storage__rename_file','file_id,name'); + +-- +-- procedure file_storage__rename_file/2 +-- + -- + -- Rename a file and all + -- Wrapper to content_item__edit_name + -- +CREATE OR REPLACE FUNCTION file_storage__rename_file( + rename_file__file_id integer, + rename_file__name varchar + +) RETURNS integer AS $$ +DECLARE +BEGIN + return content_item__edit_name( rename_file__file_id, -- item_id rename_file__name -- name ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__copy_file( - -- - -- Copy a file, but only copy the live_revision - -- - integer, -- cr_items.item_id%TYPE, - integer, -- cr_items.parent_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar -- acs_objects.creation_ip%TYPE -) returns integer as ' -- cr_revisions.revision_id%TYPE -declare - copy_file__file_id alias for $1; - copy_file__target_folder_id alias for $2; - copy_file__creation_user alias for $3; - copy_file__creation_ip alias for $4; - v_name cr_items.name%TYPE; + + +-- added +select define_function_args('file_storage__copy_file','file_id,target_folder_id,creation_user,creation_ip'); + +-- +-- procedure file_storage__copy_file/4 +-- + -- + -- Copy a file, but only copy the live_revision + -- +CREATE OR REPLACE FUNCTION file_storage__copy_file( + copy_file__file_id integer, + copy_file__target_folder_id integer, + copy_file__creation_user integer, + copy_file__creation_ip varchar + +) RETURNS integer AS $$ +DECLARE + v_name cr_items.name%TYPE; v_live_revision cr_items.live_revision%TYPE; v_filename cr_revisions.title%TYPE; v_description cr_revisions.description%TYPE; @@ -293,11 +339,11 @@ v_isurl boolean; v_content_type cr_items.content_type%TYPE; v_package_id apm_packages.package_id%TYPE; -begin +BEGIN v_isurl:= false; select content_type into v_content_type from cr_items where item_id = copy_file__file_id; - if v_content_type = ''content_extlink'' + if v_content_type = 'content_extlink' then v_isurl:= true; end if; @@ -306,7 +352,7 @@ if v_isurl = false then select i.name,i.live_revision,r.title,r.description,r.mime_type,r.content_length, - (case when i.storage_type = ''lob'' + (case when i.storage_type = 'lob' then true else false end) @@ -376,25 +422,33 @@ return 0; end if; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__move_file ( - -- - -- Move a file (ans all its versions) to a different folder. - -- Wrapper for content_item__move - -- - integer, -- cr_folders.folder_id%TYPE, - integer, -- cr_folders.folder_id%TYPE - integer, -- ceration_user - varchar -- creation_ip -) returns integer as ' -- 0 for success -declare - move_file__file_id alias for $1; - move_file__target_folder_id alias for $2; - move_file__creation_user alias for $3; - move_file__creation_ip alias for $4; -begin + +-- added +select define_function_args('file_storage__move_file','file_id,target_folder_id,creation_user,creation_ip'); + +-- +-- procedure file_storage__move_file/4 +-- + -- + -- Move a file (ans all its versions) to a different folder. + -- Wrapper for content_item__move + -- + +CREATE OR REPLACE FUNCTION file_storage__move_file( + move_file__file_id integer, + move_file__target_folder_id integer, + move_file__creation_user integer, + move_file__creation_ip varchar + +) RETURNS integer AS $$ +-- 0 for success +DECLARE +BEGIN + perform content_item__move( move_file__file_id, -- item_id move_file__target_folder_id -- target_folder_id @@ -403,29 +457,37 @@ perform acs_object__update_last_modified(move_file__target_folder_id,move_file__creation_user,move_file__creation_ip); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__get_title ( - -- - integer -- cr_items.item_id%TYPE -) returns varchar as ' -declare - get_title__item_id alias for $1; + + +-- added +select define_function_args('file_storage__get_title','item_id'); + +-- +-- procedure file_storage__get_title/1 +-- +CREATE OR REPLACE FUNCTION file_storage__get_title( + get_title__item_id integer + +) RETURNS varchar AS $$ +DECLARE v_title cr_revisions.title%TYPE; v_content_type cr_items.content_type%TYPE; -begin +BEGIN select content_type into v_content_type from cr_items where item_id = get_title__item_id; - if v_content_type = ''content_folder'' + if v_content_type = 'content_folder' then select label into v_title from cr_folders where folder_id = get_title__item_id; - else if v_content_type = ''content_symlink'' + else if v_content_type = 'content_symlink' then select label into v_title from cr_symlinks where symlink_id = get_title__item_id; @@ -438,14 +500,14 @@ end if; return v_title; +END; +$$ LANGUAGE plpgsql; -end;' language 'plpgsql'; -create or replace function file_storage__get_parent_id ( - integer -- item_id in cr_items.item_id%TYPE - ) returns integer as ' -- cr_items.item_id%TYPE +CREATE OR REPLACE FUNCTION file_storage__get_parent_id ( + get_parent_id__item_id integer +) RETURNS integer AS $$ declare - get_parent_id__item_id alias for $1; v_parent_id cr_items.item_id%TYPE; begin @@ -455,63 +517,81 @@ where item_id = get_parent_id__item_id; return v_parent_id; +END; +$$ LANGUAGE plpgsql; -end;'language 'plpgsql'; +-- added +select define_function_args('file_storage__get_content_type','file_id'); -create or replace function file_storage__get_content_type ( - -- - -- Wrapper for content_item__get_content_type - integer -- cr_items.item_id%TYPE -) returns varchar as ' -- cr_items.content_type%TYPE -declare - get_content_type__file_id alias for $1; -begin +-- +-- procedure file_storage__get_content_type/1 +-- + -- + -- Wrapper for content_item__get_content_type + -- + +CREATE OR REPLACE FUNCTION file_storage__get_content_type( + get_content_type__file_id integer +) RETURNS varchar AS $$ +DECLARE +BEGIN return content_item__get_content_type( get_content_type__file_id ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__get_folder_name ( - -- - -- Wrapper for content_folder__get_label - integer -- cr_folders.folder_id%TYPE -) returns varchar as ' -- cr_folders.label%TYPE -declare - get_folder_name__folder_id alias for $1; -begin + + +-- added +select define_function_args('file_storage__get_folder_name','folder_id'); + +-- +-- procedure file_storage__get_folder_name/1 +-- +CREATE OR REPLACE FUNCTION file_storage__get_folder_name( + get_folder_name__folder_id integer + +) RETURNS varchar AS $$ +DECLARE +BEGIN return content_folder__get_label( get_folder_name__folder_id ); +END; +$$ LANGUAGE plpgsql; -end;' language 'plpgsql'; -create or replace function file_storage__new_version ( - -- - -- Create a new version of a file - -- Wrapper for content_revision__new - -- - varchar, -- cr_revisions.title%TYPE, - varchar, -- cr_revisions.description%TYPE, - varchar, -- cr_revisions.mime_type%TYPE, - integer, -- cr_items.item_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar -- acs_objects.creation_ip%TYPE -) returns integer as ' -- cr_revisions.revision_id -declare - new_version__filename alias for $1; - new_version__description alias for $2; - new_version__mime_type alias for $3; - new_version__item_id alias for $4; - new_version__creation_user alias for $5; - new_version__creation_ip alias for $6; + +-- added +select define_function_args('file_storage__new_version','filename,description,mime_type,item_id,creation_user,creation_ip'); + +-- +-- procedure file_storage__new_version/6 +-- + -- + -- Create a new version of a file + -- Wrapper for content_revision__new + -- + +CREATE OR REPLACE FUNCTION file_storage__new_version( + new_version__filename varchar, + new_version__description varchar, + new_version__mime_type varchar, + new_version__item_id integer, + new_version__creation_user integer, + new_version__creation_ip varchar + +) RETURNS integer AS $$ +DECLARE v_revision_id cr_revisions.revision_id%TYPE; v_folder_id cr_items.parent_id%TYPE; -begin +BEGIN -- Create a revision v_revision_id := content_revision__new ( new_version__filename, -- title @@ -540,22 +620,31 @@ return v_revision_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__delete_version ( - -- - -- Delete a version of a file - -- - integer, -- cr_items.item_id%TYPE, - integer -- cr_revisions.revision_id%TYPE -) returns integer as ' -- cr_items.parent_id%TYPE -declare - delete_version__file_id alias for $1; - delete_version__version_id alias for $2; + + +-- added +select define_function_args('file_storage__delete_version','file_id,version_id'); + +-- +-- procedure file_storage__delete_version/2 +-- + -- + -- Delete a version of a file + -- + +CREATE OR REPLACE FUNCTION file_storage__delete_version( + delete_version__file_id integer, + delete_version__version_id integer + +) RETURNS integer AS $$ +DECLARE v_parent_id cr_items.parent_id%TYPE; v_deleted_last_version_p boolean; -begin +BEGIN if delete_version__version_id = content_item__get_live_revision(delete_version__file_id) then @@ -580,28 +669,30 @@ -- we cannot delete the content_item entry if there are no more revisions. return v_parent_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__new_folder( - -- - -- Create a folder - -- - varchar, -- cr_items.name%TYPE, - varchar, -- cr_folders.label%TYPE, - integer, -- cr_items.parent_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar -- acs_objects.creation_ip%TYPE -) returns integer as ' -- cr_folders.folder_id%TYPE -declare - new_folder__name alias for $1; - new_folder__folder_name alias for $2; - new_folder__parent_id alias for $3; - new_folder__creation_user alias for $4; - new_folder__creation_ip alias for $5; + + +-- added +select define_function_args('file_storage__new_folder','name,folder_name,parent_id,creation_user,creation_ip'); + +-- +-- procedure file_storage__new_folder/5 +-- +CREATE OR REPLACE FUNCTION file_storage__new_folder( + new_folder__name varchar, + new_folder__folder_name varchar, + new_folder__parent_id integer, + new_folder__creation_user integer, + new_folder__creation_ip varchar + +) RETURNS integer AS $$ +DECLARE v_folder_id cr_folders.folder_id%TYPE; v_package_id acs_objects.package_id%TYPE; -begin +BEGIN v_package_id := file_storage__get_package_id(new_folder__parent_id); -- Create a new folder @@ -623,82 +714,105 @@ -- JS: to true since we created a new subtype. PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_revision'', -- content_type - ''t''); -- include_subtypes (default) + 'content_revision', -- content_type + 't'); -- include_subtypes (default) PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_folder'', -- content_type - ''t'' -- include_subtypes (default) + 'content_folder', -- content_type + 't' -- include_subtypes (default) ); PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_extlink'', -- content_types - ''t'' -- include_subtypes + 'content_extlink', -- content_types + 't' -- include_subtypes ); PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_symlink'', -- content_types - ''t'' -- include_subtypes + 'content_symlink', -- content_types + 't' -- include_subtypes ); -- Give the creator admin privileges on the folder PERFORM acs_permission__grant_permission ( v_folder_id, -- object_id new_folder__creation_user, -- grantee_id - ''admin'' -- privilege + 'admin' -- privilege ); return v_folder_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__delete_folder( - -- - -- Delete a folder - -- - integer -- cr_folders.folder_id%TYPE -) returns integer as ' -- 0 for success -declare - delete_folder__folder_id alias for $1; -begin + +-- added + +-- +-- procedure file_storage__delete_folder/1 +-- + -- + -- Delete a folder + -- + +CREATE OR REPLACE FUNCTION file_storage__delete_folder( + delete_folder__folder_id integer + +) RETURNS integer AS $$ +DECLARE +BEGIN + return file_storage__delete_folder( delete_folder__folder_id, -- folder_id - ''f'' + 'f' ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__delete_folder( - -- - -- Delete a folder - -- - integer, -- cr_folders.folder_id%TYPE - boolean -) returns integer as ' -- 0 for success -declare - delete_folder__folder_id alias for $1; - delete_folder__cascade_p alias for $2; -begin + +-- added +select define_function_args('file_storage__delete_folder','folder_id,cascade_p'); + +-- +-- procedure file_storage__delete_folder/2 +-- + -- + -- Delete a folder + -- +CREATE OR REPLACE FUNCTION file_storage__delete_folder( + delete_folder__folder_id integer, + delete_folder__cascade_p boolean + +) RETURNS integer AS $$ +DECLARE +BEGIN return content_folder__delete( delete_folder__folder_id, -- folder_id delete_folder__cascade_p ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- JS: BEFORE DELETE TRIGGER to clean up CR entries (except root folder) -create or replace function fs_package_items_delete_trig () returns opaque as ' -declare + +-- +-- procedure fs_package_items_delete_trig/0 +-- +CREATE OR REPLACE FUNCTION fs_package_items_delete_trig ( +) RETURNS trigger AS $$ +DECLARE + v_rec record; -begin +BEGIN for v_rec in @@ -712,35 +826,35 @@ order by c1.tree_sortkey desc loop - -- DRB: Why can''t we just use object delete here? + -- DRB: Why can't we just use object delete here? -- We delete the item. On delete cascade should take care -- of deletion of revisions. - if v_rec.content_type = ''file_storage_object'' + if v_rec.content_type = 'file_storage_object' then - raise notice ''Deleting item_id = %'',v_rec.item_id; + raise notice 'Deleting item_id = %',v_rec.item_id; PERFORM content_item__delete(v_rec.item_id); end if; -- Instead of doing an if-else, we make sure we are deleting a folder. - if v_rec.content_type = ''content_folder'' + if v_rec.content_type = 'content_folder' then - raise notice ''Deleting folder_id = %'',v_rec.item_id; + raise notice 'Deleting folder_id = %',v_rec.item_id; PERFORM content_folder__delete(v_rec.item_id); end if; -- Instead of doing an if-else, we make sure we are deleting a folder. - if v_rec.content_type = ''content_symlink'' + if v_rec.content_type = 'content_symlink' then - raise notice ''Deleting symlink_id = %'',v_rec.item_id; + raise notice 'Deleting symlink_id = %',v_rec.item_id; PERFORM content_symlink__delete(v_rec.item_id); end if; -- Instead of doing an if-else, we make sure we are deleting a folder. - if v_rec.content_type = ''content_extlink'' + if v_rec.content_type = 'content_extlink' then - raise notice ''Deleting folder_id = %'',v_rec.item_id; + raise notice 'Deleting folder_id = %',v_rec.item_id; PERFORM content_extlink__delete(v_rec.item_id); end if; @@ -749,20 +863,22 @@ -- We need to return something for the trigger to be activated return old; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; create trigger fs_package_items_delete_trig before delete on fs_root_folders for each row execute procedure fs_package_items_delete_trig (); -- JS: AFTER DELETE TRIGGER to clean up last CR entry -create or replace function fs_root_folder_delete_trig () returns opaque as ' -begin +CREATE OR REPLACE FUNCTION fs_root_folder_delete_trig () RETURNS trigger AS $$ +BEGIN PERFORM content_folder__delete(old.folder_id); return null; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; create trigger fs_root_folder_delete_trig after delete on fs_root_folders for each row Index: openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-4.2-4.6.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-4.2-4.6.sql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-4.2-4.6.sql 13 Jan 2005 13:58:11 -0000 1.3 +++ openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-4.2-4.6.sql 30 Mar 2013 22:50:45 -0000 1.4 @@ -6,19 +6,23 @@ -- load the entire file, but use 'create or replace' to avoid -- stomping on old functions -create or replace function file_storage__get_root_folder ( - -- - -- Returns the root folder corresponding to a particular - -- package instance. - -- - integer -- apm_packages.package_id%TYPE -) -returns integer as ' -- fs_root_folders.folder_id%TYPE -declare - get_root_folder__package_id alias for $1; + + +-- added +select define_function_args('file_storage__get_root_folder','package_id'); + +-- +-- procedure file_storage__get_root_folder/1 +-- +CREATE OR REPLACE FUNCTION file_storage__get_root_folder( + get_root_folder__package_id integer + +) RETURNS integer AS $$ +-- fs_root_folders.folder_id%TYPE +DECLARE v_folder_id fs_root_folders.folder_id%TYPE; v_count integer; -begin +BEGIN select count(*) into v_count from fs_root_folders @@ -35,16 +39,25 @@ return v_folder_id; -end;' language 'plpgsql' with (iscachable); +END; +$$ LANGUAGE plpgsql with (iscachable); -create or replace function file_storage__get_package_id ( - integer -- cr_items.item_id%TYPE -) returns integer as ' -- fs_root_folders.package_id%TYPE -declare - get_package_id__item_id alias for $1; + + +-- added +select define_function_args('file_storage__get_package_id','item_id'); + +-- +-- procedure file_storage__get_package_id/1 +-- +CREATE OR REPLACE FUNCTION file_storage__get_package_id( + get_package_id__item_id integer +) RETURNS integer AS $$ +-- fs_root_folders.package_id%TYPE +DECLARE v_package_id fs_root_folders.package_id%TYPE; v_tree_sortkey cr_items.tree_sortkey%TYPE; -begin +BEGIN select fs_root_folders.package_id into v_package_id @@ -61,31 +74,31 @@ return v_package_id; end if; -end;' language 'plpgsql' with (iscachable); +END; +$$ LANGUAGE plpgsql with (iscachable); -create or replace function file_storage__new_root_folder ( - -- - -- Creates a new root folder - -- - -- - -- A hackish function to get around the fact that we can not run - -- code automatically when a new package instance is created. - -- - integer, -- apm_packages.package_id%TYPE - varchar, -- cr_folders.label%TYPE - varchar -- cr_folders.description%TYPE -) -returns integer as ' -- fs_root_folders.folder_id%TYPE -declare - new_root_folder__package_id alias for $1; - new_root_folder__folder_name alias for $2; - new_root_folder__description alias for $3; + + +-- added +select define_function_args('file_storage__new_root_folder','package_id,folder_name,description'); + +-- +-- procedure file_storage__new_root_folder/3 +-- +CREATE OR REPLACE FUNCTION file_storage__new_root_folder( + new_root_folder__package_id integer, + new_root_folder__folder_name varchar, + new_root_folder__description varchar + +) RETURNS integer AS $$ +-- fs_root_folders.folder_id%TYPE +DECLARE v_folder_id fs_root_folders.folder_id%TYPE; v_package_name apm_packages.instance_name%TYPE; v_package_key apm_packages.package_key%TYPE; v_folder_name cr_folders.label%TYPE; v_description cr_folders.description%TYPE; -begin +BEGIN select instance_name, package_key into v_package_name, v_package_key @@ -94,20 +107,20 @@ if new_root_folder__folder_name is null then - v_folder_name := v_package_name || '' Root Folder ''; + v_folder_name := v_package_name || ' Root Folder '; else v_folder_name := new_root_folder__folder_name; end if; if new_root_folder__description is null then - v_description := ''Root folder for the file-storage system. All other folders in file storage are subfolders of this one.''; + v_description := 'Root folder for the file-storage system. All other folders in file storage are subfolders of this one.'; else v_description := new_root_folder__description; end if; v_folder_id := content_folder__new ( - v_package_key || ''_'' || new_root_folder__package_id, -- name + v_package_key || '_' || new_root_folder__package_id, -- name v_folder_name, -- label v_description, -- description null -- parent_id (default) @@ -123,56 +136,55 @@ -- JS: true since we created a new subtype. PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_revision'', -- content_types - ''t'' -- include_subtypes + 'content_revision', -- content_types + 't' -- include_subtypes ); PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_folder'', -- content_types - ''t'' -- include_subtypes + 'content_folder', -- content_types + 't' -- include_subtypes ); -- set up default permissions PERFORM acs_permission__grant_permission ( v_folder_id, -- object_id - acs__magic_object_id(''the_public''), -- grantee_id - ''read'' -- privilege + acs__magic_object_id('the_public'), -- grantee_id + 'read' -- privilege ); PERFORM acs_permission__grant_permission ( v_folder_id, -- object_id - acs__magic_object_id(''registered_users''), -- grantee_id - ''write'' -- privilege + acs__magic_object_id('registered_users'), -- grantee_id + 'write' -- privilege ); return v_folder_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__new_file( - -- - -- Create a file in CR in preparation for actual storage - -- Wrapper for content_item__new - -- - -- DRB: I added this version to allow one to predefine item_id, among other things to - -- make it easier to use with ad_form - varchar, -- cr_items.name%TYPE, - integer, -- cr_items.parent_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar, -- acs_objects.creation_ip%TYPE, - boolean, -- store in db? - integer -- cr_items.item_id%TYPE, -) returns integer as ' -- cr_items.item_id%TYPE -declare - new_file__title alias for $1; - new_file__folder_id alias for $2; - new_file__user_id alias for $3; - new_file__creation_ip alias for $4; - new_file__indb_p alias for $5; - new_file__item_id alias for $6; + + +-- added +select define_function_args('file_storage__new_file','title,folder_id,user_id,creation_ip,indb_p,item_id'); + +-- +-- procedure file_storage__new_file/6 +-- +CREATE OR REPLACE FUNCTION file_storage__new_file( + new_file__title varchar, + new_file__folder_id integer, + new_file__user_id integer, + new_file__creation_ip varchar, + new_file__indb_p boolean, + new_file__item_id integer + +) RETURNS integer AS $$ +-- cr_items.item_id%TYPE +DECLARE v_item_id integer; -begin +BEGIN if new_file__indb_p then @@ -185,11 +197,11 @@ new_file__user_id, -- creation_user new_file__folder_id, -- context_id new_file__creation_ip, -- creation_ip - ''content_item'', -- item_subtype (default) - ''file_storage_object'', -- content_type (needed by site-wide search) + 'content_item', -- item_subtype (default) + 'file_storage_object', -- content_type (needed by site-wide search) null, -- title (default) null, -- description - ''text/plain'', -- mime_type (default) + 'text/plain', -- mime_type (default) null, -- nls_language (default) null -- data (default) ); @@ -203,14 +215,14 @@ new_file__user_id, -- creation_user new_file__folder_id, -- context_id new_file__creation_ip, -- creation_ip - ''content_item'', -- item_subtype (default) - ''file_storage_object'', -- content_type (needed by site-wide search) + 'content_item', -- item_subtype (default) + 'file_storage_object', -- content_type (needed by site-wide search) null, -- title (default) null, -- description - ''text/plain'', -- mime_type (default) + 'text/plain', -- mime_type (default) null, -- nls_language (default) null, -- text (default) - ''file'' -- storage_type + 'file' -- storage_type ); end if; @@ -219,24 +231,26 @@ return v_item_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__new_file( - varchar, -- cr_items.name%TYPE, - integer, -- cr_items.parent_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar, -- acs_objects.creation_ip%TYPE, - boolean -- store in db? -) returns integer as ' -- cr_items.item_id%TYPE -declare - new_file__title alias for $1; - new_file__folder_id alias for $2; - new_file__user_id alias for $3; - new_file__creation_ip alias for $4; - new_file__indb_p alias for $5; -begin + +-- +-- procedure file_storage__new_file/5 +-- +CREATE OR REPLACE FUNCTION file_storage__new_file( + new_file__title varchar, + new_file__folder_id integer, + new_file__user_id integer, + new_file__creation_ip varchar, + new_file__indb_p boolean +) RETURNS integer AS $$ +-- cr_items.item_id%TYPE +DECLARE +BEGIN + return file_storage__new_file( new_file__title, new_file__folder_id, @@ -246,61 +260,74 @@ null ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__delete_file ( - -- - -- Delete a file and all its version - -- Wrapper to content_item__delete - -- - integer -- cr_items.item_id%TYPE -) returns integer as ' -declare - delete_file__file_id alias for $1; -begin + +-- added +select define_function_args('file_storage__delete_file','file_id'); + +-- +-- procedure file_storage__delete_file/1 +-- +CREATE OR REPLACE FUNCTION file_storage__delete_file( + delete_file__file_id integer + +) RETURNS integer AS $$ +DECLARE +BEGIN + return content_item__delete(delete_file__file_id); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__rename_file ( - -- - -- Rename a file and all - -- Wrapper to content_item__rename - -- - integer, -- cr_items.item_id%TYPE, - varchar -- cr_items.name%TYPE -) returns integer as ' -declare - rename_file__file_id alias for $1; - rename_file__title alias for $2; -begin +-- added +select define_function_args('file_storage__rename_file','file_id,title'); + +-- +-- procedure file_storage__rename_file/2 +-- +CREATE OR REPLACE FUNCTION file_storage__rename_file( + rename_file__file_id integer, + rename_file__title varchar + +) RETURNS integer AS $$ +DECLARE + +BEGIN + return content_item__rename( rename_file__file_id, -- item_id rename_file__title -- name ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__copy_file( - -- - -- Copy a file, but only copy the live_revision - -- - integer, -- cr_items.item_id%TYPE, - integer, -- cr_items.parent_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar -- acs_objects.creation_ip%TYPE -) returns integer as ' -- cr_revisions.revision_id%TYPE -declare - copy_file__file_id alias for $1; - copy_file__target_folder_id alias for $2; - copy_file__creation_user alias for $3; - copy_file__creation_ip alias for $4; + + +-- added +select define_function_args('file_storage__copy_file','file_id,target_folder_id,creation_user,creation_ip'); + +-- +-- procedure file_storage__copy_file/4 +-- +CREATE OR REPLACE FUNCTION file_storage__copy_file( + copy_file__file_id integer, + copy_file__target_folder_id integer, + copy_file__creation_user integer, + copy_file__creation_ip varchar + +) RETURNS integer AS $$ +-- cr_revisions.revision_id%TYPE +DECLARE v_title cr_items.name%TYPE; v_live_revision cr_items.live_revision%TYPE; v_filename cr_revisions.title%TYPE; @@ -313,12 +340,12 @@ v_new_file_id cr_items.item_id%TYPE; v_new_version_id cr_revisions.revision_id%TYPE; v_indb_p boolean; -begin +BEGIN -- We copy only the title from the file being copied, and attributes of the -- live revision select i.name,i.live_revision,r.title,r.description,r.mime_type,r.content_length, - (case when i.storage_type = ''lob'' + (case when i.storage_type = 'lob' then true else false end) @@ -386,22 +413,27 @@ return v_new_version_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__move_file ( - -- - -- Move a file (ans all its versions) to a different folder. - -- Wrapper for content_item__move - -- - integer, -- cr_folders.folder_id%TYPE, - integer -- cr_folders.folder_id%TYPE -) returns integer as ' -- 0 for success -declare - move_file__file_id alias for $1; - move_file__target_folder_id alias for $2; -begin + +-- added +select define_function_args('file_storage__move_file','file_id,target_folder_id'); + +-- +-- procedure file_storage__move_file/2 +-- +CREATE OR REPLACE FUNCTION file_storage__move_file( + move_file__file_id integer, + move_file__target_folder_id integer + +) RETURNS integer AS $$ +-- 0 for success +DECLARE +BEGIN + perform content_item__move( move_file__file_id, -- item_id move_file__target_folder_id -- target_folder_id @@ -410,32 +442,37 @@ perform acs_object__update_last_modified(move_file__target_folder_id); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__get_title ( - -- - -- Unfortunately, title in the file-storage context refers - -- to the name attribute in cr_items, not the title attribute in - -- cr_revisions - integer -- cr_items.item_id%TYPE -) returns varchar as ' -declare - get_title__item_id alias for $1; + + +-- added +select define_function_args('file_storage__get_title','item_id'); + +-- +-- procedure file_storage__get_title/1 +-- +CREATE OR REPLACE FUNCTION file_storage__get_title( + get_title__item_id integer + +) RETURNS varchar AS $$ +DECLARE v_title cr_items.name%TYPE; v_content_type cr_items.content_type%TYPE; -begin +BEGIN select content_type into v_content_type from cr_items where item_id = get_title__item_id; - if v_content_type = ''content_folder'' + if v_content_type = 'content_folder' then select label into v_title from cr_folders where folder_id = get_title__item_id; - else if v_content_type = ''content_symlink'' + else if v_content_type = 'content_symlink' then select label into v_title from cr_symlinks where symlink_id = get_title__item_id; @@ -448,13 +485,13 @@ return v_title; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__get_parent_id ( - integer -- item_id in cr_items.item_id%TYPE - ) returns integer as ' -- cr_items.item_id%TYPE +CREATE OR REPLACE function file_storage__get_parent_id ( + get_parent_id__item_id integer +) RETURNS integer AS $$ declare - get_parent_id__item_id alias for $1; v_parent_id cr_items.item_id%TYPE; begin @@ -464,63 +501,74 @@ where item_id = get_parent_id__item_id; return v_parent_id; +end; +$$ language plpgsql; -end;'language 'plpgsql'; -create or replace function file_storage__get_content_type ( - -- - -- Wrapper for content_item__get_content_type - integer -- cr_items.item_id%TYPE -) returns varchar as ' -- cr_items.content_type%TYPE -declare - get_content_type__file_id alias for $1; -begin +-- added +select define_function_args('file_storage__get_content_type','file_id'); + +-- +-- procedure file_storage__get_content_type/1 +-- +CREATE OR REPLACE FUNCTION file_storage__get_content_type( + get_content_type__file_id integer +) RETURNS varchar AS $$ +DECLARE +BEGIN return content_item__get_content_type( get_content_type__file_id ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__get_folder_name ( - -- - -- Wrapper for content_folder__get_label - integer -- cr_folders.folder_id%TYPE -) returns varchar as ' -- cr_folders.label%TYPE -declare - get_folder_name__folder_id alias for $1; -begin + + +-- added +select define_function_args('file_storage__get_folder_name','folder_id'); + +-- +-- procedure file_storage__get_folder_name/1 +-- +CREATE OR REPLACE FUNCTION file_storage__get_folder_name( + get_folder_name__folder_id integer + +) RETURNS varchar AS $$ +DECLARE +BEGIN return content_folder__get_label( get_folder_name__folder_id ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__new_version ( - -- - -- Create a new version of a file - -- Wrapper for content_revision__new - -- - varchar, -- cr_revisions.title%TYPE, - varchar, -- cr_revisions.description%TYPE, - varchar, -- cr_revisions.mime_type%TYPE, - integer, -- cr_items.item_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar -- acs_objects.creation_ip%TYPE -) returns integer as ' -- cr_revisions.revision_id -declare - new_version__filename alias for $1; - new_version__description alias for $2; - new_version__mime_type alias for $3; - new_version__item_id alias for $4; - new_version__creation_user alias for $5; - new_version__creation_ip alias for $6; + + +-- added +select define_function_args('file_storage__new_version','filename,description,mime_type,item_id,creation_user,creation_ip'); + +-- +-- procedure file_storage__new_version/6 +-- +CREATE OR REPLACE FUNCTION file_storage__new_version( + new_version__filename varchar, + new_version__description varchar, + new_version__mime_type varchar, + new_version__item_id integer, + new_version__creation_user integer, + new_version__creation_ip varchar + +) RETURNS integer AS $$ +DECLARE v_revision_id cr_revisions.revision_id%TYPE; v_folder_id cr_items.parent_id%TYPE; -begin +BEGIN -- Create a revision v_revision_id := content_revision__new ( new_version__filename, -- title @@ -548,22 +596,27 @@ return v_revision_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__delete_version ( - -- - -- Delete a version of a file - -- - integer, -- cr_items.item_id%TYPE, - integer -- cr_revisions.revision_id%TYPE -) returns integer as ' -- cr_items.parent_id%TYPE -declare - delete_version__file_id alias for $1; - delete_version__version_id alias for $2; + + +-- added +select define_function_args('file_storage__delete_version','file_id,version_id'); + +-- +-- procedure file_storage__delete_version/2 +-- +CREATE OR REPLACE FUNCTION file_storage__delete_version( + delete_version__file_id integer, + delete_version__version_id integer + +) RETURNS integer AS $$ +DECLARE v_parent_id cr_items.parent_id%TYPE; v_deleted_last_version_p boolean; -begin +BEGIN if delete_version__version_id = content_item__get_live_revision(delete_version__file_id) then @@ -588,27 +641,29 @@ -- we cannot delete the content_item entry if there are no more revisions. return v_parent_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__new_folder( - -- - -- Create a folder - -- - varchar, -- cr_items.name%TYPE, - varchar, -- cr_folders.label%TYPE, - integer, -- cr_items.parent_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar -- acs_objects.creation_ip%TYPE -) returns integer as ' -- cr_folders.folder_id%TYPE -declare - new_folder__name alias for $1; - new_folder__folder_name alias for $2; - new_folder__parent_id alias for $3; - new_folder__creation_user alias for $4; - new_folder__creation_ip alias for $5; + + +-- added +select define_function_args('file_storage__new_folder','name,folder_name,parent_id,creation_user,creation_ip'); + +-- +-- procedure file_storage__new_folder/5 +-- +CREATE OR REPLACE FUNCTION file_storage__new_folder( + new_folder__name varchar, + new_folder__folder_name varchar, + new_folder__parent_id integer, + new_folder__creation_user integer, + new_folder__creation_ip varchar + +) RETURNS integer AS $$ +DECLARE v_folder_id cr_folders.folder_id%TYPE; -begin +BEGIN -- Create a new folder v_folder_id := content_folder__new ( @@ -628,52 +683,63 @@ -- JS: to true since we created a new subtype. PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_revision'', -- content_type - ''t''); -- include_subtypes (default) + 'content_revision', -- content_type + 't'); -- include_subtypes (default) PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_folder'', -- content_type - ''t'' -- include_subtypes (default) + 'content_folder', -- content_type + 't' -- include_subtypes (default) ); -- Give the creator admin privileges on the folder PERFORM acs_permission__grant_permission ( v_folder_id, -- object_id new_folder__creation_user, -- grantee_id - ''admin'' -- privilege + 'admin' -- privilege ); return v_folder_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__delete_folder( - -- - -- Delete a folder - -- - integer -- cr_folders.folder_id%TYPE -) returns integer as ' -- 0 for success -declare - delete_folder__folder_id alias for $1; -begin + +-- added + +-- +-- procedure file_storage__delete_folder/1 +-- +CREATE OR REPLACE FUNCTION file_storage__delete_folder( + delete_folder__folder_id integer + +) RETURNS integer AS $$ +DECLARE +BEGIN + return content_folder__delete( delete_folder__folder_id -- folder_id ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- JS: BEFORE DELETE TRIGGER to clean up CR entries (except root folder) drop function fs_package_items_delete_trig(); -create or replace function fs_package_items_delete_trig () returns opaque as ' -declare + +-- +-- procedure fs_package_items_delete_trig/0 +-- +CREATE OR REPLACE FUNCTION fs_package_items_delete_trig( +) RETURNS trigger AS $$ +DECLARE v_rec record; -begin +BEGIN for v_rec in @@ -690,16 +756,16 @@ -- We delete the item. On delete cascade should take care -- of deletion of revisions. - if v_rec.content_type = ''file_storage_object'' + if v_rec.content_type = 'file_storage_object' then - raise notice ''Deleting item_id = %'',v_rec.item_id; + raise notice 'Deleting item_id = %',v_rec.item_id; PERFORM content_item__delete(v_rec.item_id); end if; -- Instead of doing an if-else, we make sure we are deleting a folder. - if v_rec.content_type = ''content_folder'' + if v_rec.content_type = 'content_folder' then - raise notice ''Deleting folder_id = %'',v_rec.item_id; + raise notice 'Deleting folder_id = %',v_rec.item_id; PERFORM content_folder__delete(v_rec.item_id); end if; @@ -710,7 +776,8 @@ -- We need to return something for the trigger to be activated return old; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; create trigger fs_package_items_delete_trig before delete on fs_root_folders for each row Index: openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-4.6.1-4.6.2.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-4.6.1-4.6.2.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-4.6.1-4.6.2.sql 17 May 2003 10:38:49 -0000 1.2 +++ openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-4.6.1-4.6.2.sql 30 Mar 2013 22:50:45 -0000 1.3 @@ -1,13 +1,20 @@ -- Move old fs_simple_objects URLs to the content repository, where they -- belong. -create or replace function inline_0() returns integer as ' -declare + + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( + +) RETURNS integer AS $$ +DECLARE root record; folder record; fs_url record; new_url_id cr_extlinks.extlink_id%TYPE; -begin +BEGIN for root in select tree_sortkey from fs_root_folders, cr_items @@ -19,11 +26,11 @@ where cr_items.tree_sortkey between root.tree_sortkey and tree_right(root.tree_sortkey) and cr_folders.folder_id = cr_items.item_id loop - if not content_folder__is_registered(folder.folder_id, ''content_symlink'', ''t'') then - perform content_folder__register_content_type(folder.folder_id, ''content_symlink'', ''t''); + if not content_folder__is_registered(folder.folder_id, 'content_symlink', 't') then + perform content_folder__register_content_type(folder.folder_id, 'content_symlink', 't'); end if; - if not content_folder__is_registered(folder.folder_id, ''content_extlink'', ''t'') then - perform content_folder__register_content_type(folder.folder_id, ''content_extlink'', ''t''); + if not content_folder__is_registered(folder.folder_id, 'content_extlink', 't') then + perform content_folder__register_content_type(folder.folder_id, 'content_extlink', 't'); end if; end loop; @@ -60,7 +67,8 @@ return 0; -end' language 'plpgsql'; +end +$$ LANGUAGE plpgsql; begin; select inline_0(); @@ -123,11 +131,18 @@ -- JS: BEFORE DELETE TRIGGER to clean up CR entries (except root folder) drop function fs_package_items_delete_trig (); -create function fs_package_items_delete_trig () returns opaque as ' -declare + +-- +-- procedure fs_package_items_delete_trig/0 +-- +CREATE OR REPLACE FUNCTION fs_package_items_delete_trig( + +) RETURNS trigger AS $$ +DECLARE + v_rec record; -begin +BEGIN for v_rec in @@ -141,35 +156,35 @@ order by c1.tree_sortkey desc loop - -- DRB: Why can''t we just use object delete here? + -- DRB: Why can't we just use object delete here? -- We delete the item. On delete cascade should take care -- of deletion of revisions. - if v_rec.content_type = ''file_storage_object'' + if v_rec.content_type = 'file_storage_object' then - raise notice ''Deleting item_id = %'',v_rec.item_id; + raise notice 'Deleting item_id = %',v_rec.item_id; PERFORM content_item__delete(v_rec.item_id); end if; -- Instead of doing an if-else, we make sure we are deleting a folder. - if v_rec.content_type = ''content_folder'' + if v_rec.content_type = 'content_folder' then - raise notice ''Deleting folder_id = %'',v_rec.item_id; + raise notice 'Deleting folder_id = %',v_rec.item_id; PERFORM content_folder__delete(v_rec.item_id); end if; -- Instead of doing an if-else, we make sure we are deleting a folder. - if v_rec.content_type = ''content_symlink'' + if v_rec.content_type = 'content_symlink' then - raise notice ''Deleting symlink_id = %'',v_rec.item_id; + raise notice 'Deleting symlink_id = %',v_rec.item_id; PERFORM content_symlink__delete(v_rec.item_id); end if; -- Instead of doing an if-else, we make sure we are deleting a folder. - if v_rec.content_type = ''content_extlink'' + if v_rec.content_type = 'content_extlink' then - raise notice ''Deleting folder_id = %'',v_rec.item_id; + raise notice 'Deleting folder_id = %',v_rec.item_id; PERFORM content_extlink__delete(v_rec.item_id); end if; @@ -178,7 +193,8 @@ -- We need to return something for the trigger to be activated return old; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; drop trigger fs_package_items_delete_trig on fs_root_folders; create trigger fs_package_items_delete_trig before delete Index: openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-4.6.2-4.6.3.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-4.6.2-4.6.3.sql,v diff -u -r1.4 -r1.5 --- openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-4.6.2-4.6.3.sql 13 May 2008 11:09:45 -0000 1.4 +++ openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-4.6.2-4.6.3.sql 30 Mar 2013 22:50:45 -0000 1.5 @@ -1,29 +1,27 @@ -- Add user_id and IP to update_last_modified -- $Id -create or replace function file_storage__new_file( - -- - -- Create a file in CR in preparation for actual storage - -- Wrapper for content_item__new - -- - -- DRB: I added this version to allow one to predefine item_id, among other things to - -- make it easier to use with ad_form - varchar, -- cr_items.name%TYPE, - integer, -- cr_items.parent_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar, -- acs_objects.creation_ip%TYPE, - boolean, -- store in db? - integer -- cr_items.item_id%TYPE, -) returns integer as ' -- cr_items.item_id%TYPE -declare - new_file__title alias for $1; - new_file__folder_id alias for $2; - new_file__user_id alias for $3; - new_file__creation_ip alias for $4; - new_file__indb_p alias for $5; - new_file__item_id alias for $6; + + +-- added +select define_function_args('file_storage__new_file','title,folder_id,user_id,creation_ip,indb_p,item_id'); + +-- +-- procedure file_storage__new_file/6 +-- +CREATE OR REPLACE FUNCTION file_storage__new_file( + new_file__title -- varchar, + new_file__folder_id integer, + new_file__user_id integer, + new_file__creation_ip varchar, + new_file__indb_p boolean, + new_file__item_id integer + +) RETURNS integer AS $$ +-- cr_items.item_id%TYPE +DECLARE v_item_id integer; -begin +BEGIN if new_file__indb_p then @@ -36,11 +34,11 @@ new_file__user_id, -- creation_user new_file__folder_id, -- context_id new_file__creation_ip, -- creation_ip - ''content_item'', -- item_subtype (default) - ''file_storage_object'', -- content_type (needed by site-wide search) + 'content_item', -- item_subtype (default) + 'file_storage_object', -- content_type (needed by site-wide search) null, -- title (default) null, -- description - ''text/plain'', -- mime_type (default) + 'text/plain', -- mime_type (default) null, -- nls_language (default) null -- data (default) ); @@ -54,14 +52,14 @@ new_file__user_id, -- creation_user new_file__folder_id, -- context_id new_file__creation_ip, -- creation_ip - ''content_item'', -- item_subtype (default) - ''file_storage_object'', -- content_type (needed by site-wide search) + 'content_item', -- item_subtype (default) + 'file_storage_object', -- content_type (needed by site-wide search) null, -- title (default) null, -- description - ''text/plain'', -- mime_type (default) + 'text/plain', -- mime_type (default) null, -- nls_language (default) null, -- text (default) - ''file'' -- storage_type + 'file' -- storage_type ); end if; @@ -70,23 +68,28 @@ return v_item_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__copy_file( - -- - -- Copy a file, but only copy the live_revision - -- - integer, -- cr_items.item_id%TYPE, - integer, -- cr_items.parent_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar -- acs_objects.creation_ip%TYPE -) returns integer as ' -- cr_revisions.revision_id%TYPE -declare - copy_file__file_id alias for $1; - copy_file__target_folder_id alias for $2; - copy_file__creation_user alias for $3; - copy_file__creation_ip alias for $4; + + +-- added +select define_function_args('file_storage__copy_file','file_id,target_folder_id,creation_user,creation_ip'); + +-- +-- procedure file_storage__copy_file/4 +-- +CREATE OR REPLACE FUNCTION file_storage__copy_file( + copy_file__file_id -- -- + integer, + copy_file__target_folder_id integer, + copy_file__creation_user integer, + copy_file__creation_ip varchar + +) RETURNS integer AS $$ +-- cr_revisions.revision_id%TYPE +DECLARE v_title cr_items.name%TYPE; v_live_revision cr_items.live_revision%TYPE; v_filename cr_revisions.title%TYPE; @@ -99,12 +102,12 @@ v_new_file_id cr_items.item_id%TYPE; v_new_version_id cr_revisions.revision_id%TYPE; v_indb_p boolean; -begin +BEGIN -- We copy only the title from the file being copied, and attributes of the -- live revision select i.name,i.live_revision,r.title,r.description,r.mime_type,r.content_length, - (case when i.storage_type = ''lob'' + (case when i.storage_type = 'lob' then true else false end) @@ -172,26 +175,29 @@ return v_new_version_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__move_file ( - -- - -- Move a file (ans all its versions) to a different folder. - -- Wrapper for content_item__move - -- - integer, -- cr_folders.folder_id%TYPE, - integer, -- cr_folders.folder_id%TYPE - integer, - varchar -) returns integer as ' -- 0 for success -declare - move_file__file_id alias for $1; - move_file__target_folder_id alias for $2; - move_file__creation_user alias for $3; - move_file__creation_ip alias for $4; -begin + +-- added +select define_function_args('file_storage__move_file','file_id,target_folder_id,creation_user,creation_ip'); + +-- +-- procedure file_storage__move_file/4 +-- +CREATE OR REPLACE FUNCTION file_storage__move_file( + move_file__file_id -- integer, + move_file__target_folder_id integer, + move_file__creation_user integer, + move_file__creation_ip varchar + +) RETURNS integer AS $$ +-- 0 for success +DECLARE +BEGIN + perform content_item__move( move_file__file_id, -- item_id move_file__target_folder_id -- target_folder_id @@ -200,30 +206,32 @@ perform acs_object__update_last_modified(move_file__target_folder_id,move_file__creation_user,move_file__creation_ip); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__new_version ( - -- - -- Create a new version of a file - -- Wrapper for content_revision__new - -- - varchar, -- cr_revisions.title%TYPE, - varchar, -- cr_revisions.description%TYPE, - varchar, -- cr_revisions.mime_type%TYPE, - integer, -- cr_items.item_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar -- acs_objects.creation_ip%TYPE -) returns integer as ' -- cr_revisions.revision_id -declare - new_version__filename alias for $1; - new_version__description alias for $2; - new_version__mime_type alias for $3; - new_version__item_id alias for $4; - new_version__creation_user alias for $5; - new_version__creation_ip alias for $6; + + +-- added +select define_function_args('file_storage__new_version','filename,description,mime_type,item_id,creation_user,creation_ip'); + +-- +-- procedure file_storage__new_version/6 +-- +CREATE OR REPLACE FUNCTION file_storage__new_version( + new_version__filename -- -- + varchar, + new_version__description varchar, + new_version__mime_type varchar, + new_version__item_id integer, + new_version__creation_user integer, + new_version__creation_ip varchar + +) RETURNS integer AS $$ +-- cr_revisions.revision_id +DECLARE v_revision_id cr_revisions.revision_id%TYPE; v_folder_id cr_items.parent_id%TYPE; -begin +BEGIN -- Create a revision v_revision_id := content_revision__new ( new_version__filename, -- title @@ -251,5 +259,6 @@ return v_revision_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-4.6.3-5.0.0.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-4.6.3-5.0.0.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-4.6.3-5.0.0.sql 12 Mar 2004 13:45:37 -0000 1.2 +++ openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-4.6.3-5.0.0.sql 30 Mar 2013 22:50:45 -0000 1.3 @@ -12,51 +12,53 @@ -- Create the (default) file_storage_object content type template template_id := content_template__new( - ''file-storage-default'', -- name - '' + 'file-storage-default', -- name + ' @title;noquote@ @context;noquote@ -@text;noquote@'', -- text +@text;noquote@', -- text true -- is_live ); -- Register the template for the file_storage_object content type perform content_type__register_template( - ''file_storage_object'', -- content_type + 'file_storage_object', -- content_type template_id, -- template_id - ''public'', -- use_context - ''t'' -- is_default + 'public', -- use_context + 't' -- is_default ); return null; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0(); drop function inline_0(); -create or replace function file_storage__new_version ( - -- - -- Create a new version of a file - -- Wrapper for content_revision__new - -- - varchar, -- cr_revisions.title%TYPE, - varchar, -- cr_revisions.description%TYPE, - varchar, -- cr_revisions.mime_type%TYPE, - integer, -- cr_items.item_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar -- acs_objects.creation_ip%TYPE -) returns integer as ' -- cr_revisions.revision_id -declare - new_version__filename alias for $1; - new_version__description alias for $2; - new_version__mime_type alias for $3; - new_version__item_id alias for $4; - new_version__creation_user alias for $5; - new_version__creation_ip alias for $6; + + +-- added +select define_function_args('file_storage__new_version','filename,description,mime_type,item_id,creation_user,creation_ip'); + +-- +-- procedure file_storage__new_version/6 +-- +CREATE OR REPLACE FUNCTION file_storage__new_version( + new_version__filename -- -- + varchar, + new_version__description varchar, + new_version__mime_type varchar, + new_version__item_id integer, + new_version__creation_user integer, + new_version__creation_ip varchar + +) RETURNS integer AS $$ +-- cr_revisions.revision_id +DECLARE v_revision_id cr_revisions.revision_id%TYPE; v_folder_id cr_items.parent_id%TYPE; -begin +BEGIN -- Create a revision v_revision_id := content_revision__new ( new_version__filename, -- title @@ -85,4 +87,5 @@ return v_revision_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0a12-5.1.0a13.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0a12-5.1.0a13.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0a12-5.1.0a13.sql 13 Jan 2005 13:58:11 -0000 1.2 +++ openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0a12-5.1.0a13.sql 30 Mar 2013 22:50:45 -0000 1.3 @@ -5,13 +5,21 @@ -- Utility function lifted from one of the notification upgrade scripts... -- We might want to make this standard. -create or replace function safe_drop_constraint(name, name) -returns integer as ' -declare - p_table_name alias for $1; - p_constraint_name alias for $2; + + +-- added +select define_function_args('safe_drop_constraint','table_name,constraint_name'); + +-- +-- procedure safe_drop_constraint/2 +-- +CREATE OR REPLACE FUNCTION safe_drop_constraint( + p_table_name name, + p_constraint_name name +) RETURNS integer AS $$ +DECLARE v_constraint_p integer; -begin +BEGIN select count(*) into v_constraint_p from pg_constraint con, pg_class c @@ -20,11 +28,12 @@ and c.relname = p_table_name; if v_constraint_p > 0 then - execute ''alter table '' || p_table_name || '' drop constraint '' || p_constraint_name; + execute 'alter table ' || p_table_name || ' drop constraint ' || p_constraint_name; end if; return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- Now drop the old constraint if defined (it might not be). select safe_drop_constraint('fs_rss_subscrs', 'fs_rss_subscrs_fk'); Index: openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0a16-5.1.0a17.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0a16-5.1.0a17.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0a16-5.1.0a17.sql 30 Sep 2005 18:08:34 -0000 1.2 +++ openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0a16-5.1.0a17.sql 30 Mar 2013 22:50:45 -0000 1.3 @@ -8,27 +8,26 @@ -- add package_id to acs_objects for all objects in FS (see Tip 42) -create or replace function file_storage__new_root_folder ( - -- - -- Creates a new root folder - -- - -- - -- A hackish function to get around the fact that we can not run - -- code automatically when a new package instance is created. - -- - integer, -- apm_packages.package_id%TYPE - varchar, -- cr_folders.label%TYPE - varchar, -- cr_items.name%TYPE - varchar -) -returns integer as ' -- fs_root_folders.folder_id%TYPE -declare - new_root_folder__package_id alias for $1; - new_root_folder__folder_name alias for $2; - new_root_folder__url alias for $3; - new_root_folder__description alias for $4; + + +-- added +select define_function_args('file_storage__new_root_folder','package_id,folder_name,url,description'); + +-- +-- procedure file_storage__new_root_folder/4 +-- +CREATE OR REPLACE FUNCTION file_storage__new_root_folder( + new_root_folder__package_id -- -- -- + integer, + new_root_folder__folder_name varchar, + new_root_folder__url varchar, + new_root_folder__description varchar + +) RETURNS integer AS $$ +-- fs_root_folders.folder_id%TYPE +DECLARE v_folder_id fs_root_folders.folder_id%TYPE; -begin +BEGIN v_folder_id := content_folder__new ( @@ -54,54 +53,52 @@ -- JS: true since we created a new subtype. PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_revision'', -- content_types - ''t'' -- include_subtypes + 'content_revision', -- content_types + 't' -- include_subtypes ); PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_folder'', -- content_types - ''t'' -- include_subtypes + 'content_folder', -- content_types + 't' -- include_subtypes ); PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_symlink'', -- content_types - ''t'' -- include_subtypes + 'content_symlink', -- content_types + 't' -- include_subtypes ); PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_extlink'', -- content_types - ''t'' -- include_subtypes + 'content_extlink', -- content_types + 't' -- include_subtypes ); return v_folder_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__new_file( - -- - -- Create a file in CR in preparation for actual storage - -- Wrapper for content_item__new - -- - -- DRB: I added this version to allow one to predefine item_id, among other things to - -- make it easier to use with ad_form - varchar, -- cr_items.name%TYPE, - integer, -- cr_items.parent_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar, -- acs_objects.creation_ip%TYPE, - boolean, -- store in db? - integer, -- cr_items.item_id%TYPE, - integer -- apm_packages.package_id%TYPE -) returns integer as ' -- cr_items.item_id%TYPE -declare - new_file__name alias for $1; - new_file__folder_id alias for $2; - new_file__user_id alias for $3; - new_file__creation_ip alias for $4; - new_file__indb_p alias for $5; - new_file__item_id alias for $6; - new_file__package_id alias for $7; + + +-- added +select define_function_args('file_storage__new_file','name,folder_id,user_id,creation_ip,indb_p,item_id,package_id'); + +-- +-- procedure file_storage__new_file/7 +-- +CREATE OR REPLACE FUNCTION file_storage__new_file( + new_file__name -- varchar, + new_file__folder_id integer, + new_file__user_id integer, + new_file__creation_ip varchar, + new_file__indb_p boolean, + new_file__item_id integer, + new_file__package_id integer + +) RETURNS integer AS $$ +-- cr_items.item_id%TYPE +DECLARE v_item_id integer; -begin +BEGIN if new_file__indb_p then @@ -114,11 +111,11 @@ new_file__user_id, -- creation_user new_file__folder_id, -- context_id new_file__creation_ip, -- creation_ip - ''content_item'', -- item_subtype (default) - ''file_storage_object'', -- content_type (needed by site-wide search) + 'content_item', -- item_subtype (default) + 'file_storage_object', -- content_type (needed by site-wide search) null, -- title (default) null, -- description - ''text/plain'', -- mime_type (default) + 'text/plain', -- mime_type (default) null, -- nls_language (default) null, -- data (default) new_file__package_id -- package_id @@ -133,14 +130,14 @@ new_file__user_id, -- creation_user new_file__folder_id, -- context_id new_file__creation_ip, -- creation_ip - ''content_item'', -- item_subtype (default) - ''file_storage_object'', -- content_type (needed by site-wide search) + 'content_item', -- item_subtype (default) + 'file_storage_object', -- content_type (needed by site-wide search) null, -- title (default) null, -- description - ''text/plain'', -- mime_type (default) + 'text/plain', -- mime_type (default) null, -- nls_language (default) null, -- text (default) - ''file'', -- storage_type + 'file', -- storage_type new_file__package_id -- package_id ); @@ -150,25 +147,26 @@ return v_item_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__new_file( - varchar, -- cr_items.name%TYPE, - integer, -- cr_items.parent_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar, -- acs_objects.creation_ip%TYPE, - boolean, -- store in db? - integer -- apm_packages.package_id%TYPE -) returns integer as ' -- cr_items.item_id%TYPE -declare - new_file__name alias for $1; - new_file__folder_id alias for $2; - new_file__user_id alias for $3; - new_file__creation_ip alias for $4; - new_file__indb_p alias for $5; - new_file__package_id alias for $6; -begin + + +-- +-- procedure file_storage__new_file/6 +-- +CREATE OR REPLACE FUNCTION file_storage__new_file( + new_file__name varchar, + new_file__folder_id integer, + new_file__user_id integer, + new_file__creation_ip varchar, + new_file__indb_p boolean, + new_file__package_id integer +) RETURNS integer AS $$ +-- cr_items.item_id%TYPE +DECLARE +BEGIN return file_storage__new_file( new_file__name, -- name new_file__folder_id, -- parent_id @@ -179,22 +177,27 @@ new_file__package_id -- pacakge_id ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__copy_file( - -- - -- Copy a file, but only copy the live_revision - -- - integer, -- cr_items.item_id%TYPE, - integer, -- cr_items.parent_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar -- acs_objects.creation_ip%TYPE -) returns integer as ' -- cr_revisions.revision_id%TYPE -declare - copy_file__file_id alias for $1; - copy_file__target_folder_id alias for $2; - copy_file__creation_user alias for $3; - copy_file__creation_ip alias for $4; + + +-- added +select define_function_args('file_storage__copy_file','file_id,target_folder_id,creation_user,creation_ip'); + +-- +-- procedure file_storage__copy_file/4 +-- +CREATE OR REPLACE FUNCTION file_storage__copy_file( + copy_file__file_id -- -- + integer, + copy_file__target_folder_id integer, + copy_file__creation_user integer, + copy_file__creation_ip varchar + +) RETURNS integer AS $$ +-- cr_revisions.revision_id%TYPE +DECLARE v_name cr_items.name%TYPE; v_live_revision cr_items.live_revision%TYPE; v_filename cr_revisions.title%TYPE; @@ -208,12 +211,12 @@ v_new_version_id cr_revisions.revision_id%TYPE; v_indb_p boolean; v_package_id apm_packages.package_id%TYPE; -begin +BEGIN -- We copy only the title from the file being copied, and attributes of the -- live revision select i.name,i.live_revision,r.title,r.description,r.mime_type,r.content_length, - (case when i.storage_type = ''lob'' + (case when i.storage_type = 'lob' then true else false end) @@ -284,7 +287,8 @@ return v_new_version_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- upgrade existing data -- do in transaction since we're bashing acs_objects in a major way Index: openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0a2-5.1.0a3.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0a2-5.1.0a3.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0a2-5.1.0a3.sql 29 Jun 2004 10:18:24 -0000 1.2 +++ openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0a2-5.1.0a3.sql 30 Mar 2013 22:50:45 -0000 1.3 @@ -1,21 +1,24 @@ -create or replace function file_storage__rename_file ( - -- - -- Rename a file and all - -- Wrapper to content_item__edit_name - -- - integer, -- cr_items.item_id%TYPE, - varchar -- cr_items.name%TYPE -) returns integer as ' -declare - rename_file__file_id alias for $1; - rename_file__name alias for $2; -begin +-- added +select define_function_args('file_storage__rename_file','file_id,name'); +-- +-- procedure file_storage__rename_file/2 +-- +CREATE OR REPLACE FUNCTION file_storage__rename_file( + rename_file__file_id integer, + rename_file__name varchar + +) RETURNS integer AS $$ +DECLARE + +BEGIN + return content_item__edit_name( rename_file__file_id, -- item_id rename_file__name -- name ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0a3-5.1.0a4.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0a3-5.1.0a4.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0a3-5.1.0a4.sql 12 Jul 2004 14:49:58 -0000 1.2 +++ openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0a3-5.1.0a4.sql 30 Mar 2013 22:50:45 -0000 1.3 @@ -1,21 +1,23 @@ -create or replace function file_storage__move_file ( - -- - -- Move a file (ans all its versions) to a different folder. - -- Wrapper for content_item__move - -- - integer, -- cr_folders.folder_id%TYPE, - integer, -- cr_folders.folder_id%TYPE - integer, -- ceration_user - varchar -- creation_ip -) returns integer as ' -- 0 for success -declare - move_file__file_id alias for $1; - move_file__target_folder_id alias for $2; - move_file__creation_user alias for $3; - move_file__creation_ip alias for $4; -begin + +-- added +select define_function_args('file_storage__move_file','file_id,target_folder_id,creation_user,creation_ip'); + +-- +-- procedure file_storage__move_file/4 +-- +CREATE OR REPLACE FUNCTION file_storage__move_file( + move_file__file_id -- integer, + move_file__target_folder_id integer, + move_file__creation_user integer, + move_file__creation_ip varchar + +) RETURNS integer AS $$ +-- 0 for success +DECLARE +BEGIN + perform content_item__move( move_file__file_id, -- item_id move_file__target_folder_id -- target_folder_id @@ -24,4 +26,5 @@ perform acs_object__update_last_modified(move_file__target_folder_id,move_file__creation_user,move_file__creation_ip); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0a4-5.1.0a5.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0a4-5.1.0a5.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0a4-5.1.0a5.sql 24 Jul 2004 08:34:20 -0000 1.2 +++ openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0a4-5.1.0a5.sql 30 Mar 2013 22:50:45 -0000 1.3 @@ -1,42 +1,46 @@ -create function inline_0() returns integer as ' -declare +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( +) RETURNS integer AS $$ +DECLARE impl_id integer; v_foo integer; -begin +BEGIN -- the notification type impl impl_id := acs_sc_impl__new ( - ''NotificationType'', - ''fs_fs_notif_type'', - ''file_storage'' + 'NotificationType', + 'fs_fs_notif_type', + 'file_storage' ); v_foo := acs_sc_impl_alias__new ( - ''NotificationType'', -- impl_contract_name - ''fs_fs_notif_type'', -- impl_name - ''GetURL'', -- impl_operation_name - ''fs::notification::get_url'', -- impl_alias - ''TCL'' -- impl_pl + 'NotificationType', -- impl_contract_name + 'fs_fs_notif_type', -- impl_name + 'GetURL', -- impl_operation_name + 'fs::notification::get_url', -- impl_alias + 'TCL' -- impl_pl ); v_foo := acs_sc_impl_alias__new ( - ''NotificationType'', - ''fs_fs_notif_type'', - ''ProcessReply'', - '' fs::notification::process_reply'', - ''TCL'' + 'NotificationType', + 'fs_fs_notif_type', + 'ProcessReply', + ' fs::notification::process_reply', + 'TCL' ); PERFORM acs_sc_binding__new ( - ''NotificationType'', - ''fs_fs_notif_type'' + 'NotificationType', + 'fs_fs_notif_type' ); v_foo:= notification_type__new ( NULL, impl_id, - ''fs_fs_notif'', - ''File-Storage Notification'', - ''Notifications for File Storage'', + 'fs_fs_notif', + 'File-Storage Notification', + 'Notifications for File Storage', now(), NULL, NULL, @@ -47,17 +51,18 @@ insert into notification_types_intervals (type_id, interval_id) select v_foo, interval_id - from notification_intervals where name in (''instant'',''hourly'',''daily''); + from notification_intervals where name in ('instant','hourly','daily'); insert into notification_types_del_methods (type_id, delivery_method_id) select v_foo, delivery_method_id - from notification_delivery_methods where short_name in (''email''); + from notification_delivery_methods where short_name in ('email'); return (0); -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; + select inline_0(); drop function inline_0(); Index: openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0a6-5.1.0a7.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0a6-5.1.0a7.sql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0a6-5.1.0a7.sql 24 Feb 2005 13:33:18 -0000 1.3 +++ openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0a6-5.1.0a7.sql 30 Mar 2013 22:50:45 -0000 1.4 @@ -1,49 +1,55 @@ + --set serveroutput on size 200000 -create or replace function tmp_fs_name_duplicate ( - varchar, - integer -) returns varchar -as ' + +CREATE OR REPLACE FUNCTION tmp_fs_name_duplicate ( + v_name varchar, + v_count integer +) RETURNS varchar AS $$ declare - v_name alias for $1; --cr_items.name%TYPE - v_count alias for $2; v_insert_pos integer; begin - v_insert_pos := instr(v_name,''.'',-1)-1; + v_insert_pos := instr(v_name,'.',-1)-1; if v_insert_pos = -1 then - return v_name || ''.'' || v_count; + return v_name || '.' || v_count; else - return substr(v_name,1,v_insert_pos) || ''.'' || v_count || substr(v_name,v_insert_pos+1); + return substr(v_name,1,v_insert_pos) || '.' || v_count || substr(v_name,v_insert_pos+1); end if; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; --show errors -- This script assumes it will be run once on all files and not broken -- up into chunks. The order by clause plays a critical role in the -- script logics attempt to avoid name collisions. -create or replace function inline_0 () returns integer as ' -declare + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( +) RETURNS integer AS $$ +DECLARE + v_count integer; v_prev_parent_id integer; v_prev_title cr_items.name%TYPE; v_new_name cr_items.name%TYPE; v_item_row RECORD; -begin +BEGIN v_count := 1; v_prev_parent_id := 0; - v_prev_title := ''''; + v_prev_title := ''; for v_item_row in select r.item_id, r.revision_id, r.title, i.name, i.live_revision, i.parent_id from cr_items i, cr_revisions r where i.item_id=r.item_id and i.live_revision=r.revision_id - and i.content_type=''file_storage_object'' + and i.content_type='file_storage_object' order by parent_id, title, revision_id loop @@ -59,7 +65,7 @@ update cr_items set name = v_new_name where item_id=v_item_row.item_id; - raise notice ''%'',v_new_name; + raise notice '%',v_new_name; else @@ -74,7 +80,8 @@ end loop; return null; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0(); drop function inline_0(); Index: openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0d1-5.1.0d2.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0d1-5.1.0d2.sql,v diff -u -r1.6 -r1.7 --- openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0d1-5.1.0d2.sql 13 May 2008 11:09:45 -0000 1.6 +++ openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0d1-5.1.0d2.sql 30 Mar 2013 22:50:45 -0000 1.7 @@ -1,33 +1,46 @@ -- $Id -create or replace function file_storage__get_root_folder ( - -- - -- Returns the root folder corresponding to a particular - -- package instance. - -- - integer -- apm_packages.package_id%TYPE -) -returns integer as ' -- fs_root_folders.folder_id%TYPE -declare - get_root_folder__package_id alias for $1; + + +-- added +select define_function_args('file_storage__get_root_folder','package_id'); + +-- +-- procedure file_storage__get_root_folder/1 +-- +CREATE OR REPLACE FUNCTION file_storage__get_root_folder( + get_root_folder__package_id integer + +) RETURNS integer AS $$ +-- fs_root_folders.folder_id%TYPE +DECLARE v_folder_id fs_root_folders.folder_id%TYPE; -begin +BEGIN select folder_id into v_folder_id from fs_root_folders where package_id = get_root_folder__package_id; return v_folder_id; -end;' language 'plpgsql' with (iscachable); +END; +$$ LANGUAGE plpgsql with (iscachable); -create or replace function file_storage__get_package_id ( - integer -- cr_items.item_id%TYPE -) returns integer as ' -- fs_root_folders.package_id%TYPE -declare - get_package_id__item_id alias for $1; + + +-- added +select define_function_args('file_storage__get_package_id','item_id'); + +-- +-- procedure file_storage__get_package_id/1 +-- +CREATE OR REPLACE FUNCTION file_storage__get_package_id( + get_package_id__item_id integer +) RETURNS integer AS $$ +-- fs_root_folders.package_id%TYPE +DECLARE v_package_id fs_root_folders.package_id%TYPE; v_tree_sortkey cr_items.tree_sortkey%TYPE; -begin +BEGIN select fs_root_folders.package_id into v_package_id @@ -44,33 +57,32 @@ return v_package_id; end if; -end;' language 'plpgsql' with (iscachable); +END; +$$ LANGUAGE plpgsql with (iscachable); -- drop function file_storage__new_root_folder (integer); -create or replace function file_storage__new_file( - -- - -- Create a file in CR in preparation for actual storage - -- Wrapper for content_item__new - -- - -- DRB: I added this version to allow one to predefine item_id, among other things to - -- make it easier to use with ad_form - varchar, -- cr_items.name%TYPE, - integer, -- cr_items.parent_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar, -- acs_objects.creation_ip%TYPE, - boolean, -- store in db? - integer -- cr_items.item_id%TYPE, -) returns integer as ' -- cr_items.item_id%TYPE -declare - new_file__name alias for $1; - new_file__folder_id alias for $2; - new_file__user_id alias for $3; - new_file__creation_ip alias for $4; - new_file__indb_p alias for $5; - new_file__item_id alias for $6; + + +-- added +select define_function_args('file_storage__new_file','name,folder_id,user_id,creation_ip,indb_p,item_id'); + +-- +-- procedure file_storage__new_file/6 +-- +CREATE OR REPLACE FUNCTION file_storage__new_file( + new_file__name varchar, + new_file__folder_id integer, + new_file__user_id integer, + new_file__creation_ip varchar, + new_file__indb_p boolean, + new_file__item_id integer + +) RETURNS integer AS $$ +-- cr_items.item_id%TYPE +DECLARE v_item_id integer; -begin +BEGIN if new_file__indb_p then @@ -83,11 +95,11 @@ new_file__user_id, -- creation_user new_file__folder_id, -- context_id new_file__creation_ip, -- creation_ip - ''content_item'', -- item_subtype (default) - ''file_storage_object'', -- content_type (needed by site-wide search) + 'content_item', -- item_subtype (default) + 'file_storage_object', -- content_type (needed by site-wide search) null, -- title (default) null, -- description - ''text/plain'', -- mime_type (default) + 'text/plain', -- mime_type (default) null, -- nls_language (default) null -- data (default) ); @@ -101,14 +113,14 @@ new_file__user_id, -- creation_user new_file__folder_id, -- context_id new_file__creation_ip, -- creation_ip - ''content_item'', -- item_subtype (default) - ''file_storage_object'', -- content_type (needed by site-wide search) + 'content_item', -- item_subtype (default) + 'file_storage_object', -- content_type (needed by site-wide search) null, -- title (default) null, -- description - ''text/plain'', -- mime_type (default) + 'text/plain', -- mime_type (default) null, -- nls_language (default) null, -- text (default) - ''file'' -- storage_type + 'file' -- storage_type ); end if; @@ -117,24 +129,26 @@ return v_item_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__new_file( - varchar, -- cr_items.name%TYPE, - integer, -- cr_items.parent_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar, -- acs_objects.creation_ip%TYPE, - boolean -- store in db? -) returns integer as ' -- cr_items.item_id%TYPE -declare - new_file__name alias for $1; - new_file__folder_id alias for $2; - new_file__user_id alias for $3; - new_file__creation_ip alias for $4; - new_file__indb_p alias for $5; -begin + +-- +-- procedure file_storage__new_file/5 +-- +CREATE OR REPLACE FUNCTION file_storage__new_file( + new_file__name varchar, + new_file__folder_id integer, + new_file__user_id integer, + new_file__creation_ip varchar, + new_file__indb_p boolean +) RETURNS integer AS $$ +-- cr_items.item_id%TYPE +DECLARE +BEGIN + return file_storage__new_file( new_file__name, new_file__folder_id, @@ -144,61 +158,74 @@ null ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__delete_file ( - -- - -- Delete a file and all its version - -- Wrapper to content_item__delete - -- - integer -- cr_items.item_id%TYPE -) returns integer as ' -declare - delete_file__file_id alias for $1; -begin + +-- added +select define_function_args('file_storage__delete_file','file_id'); + +-- +-- procedure file_storage__delete_file/1 +-- +CREATE OR REPLACE FUNCTION file_storage__delete_file( + delete_file__file_id integer + +) RETURNS integer AS $$ +DECLARE +BEGIN + return content_item__delete(delete_file__file_id); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__rename_file ( - -- - -- Rename a file and all - -- Wrapper to content_item__rename - -- - integer, -- cr_items.item_id%TYPE, - varchar -- cr_items.name%TYPE -) returns integer as ' -declare - rename_file__file_id alias for $1; - rename_file__name alias for $2; -begin +-- added +select define_function_args('file_storage__rename_file','file_id,name'); + +-- +-- procedure file_storage__rename_file/2 +-- +CREATE OR REPLACE FUNCTION file_storage__rename_file( + rename_file__file_id integer, + rename_file__name varchar + +) RETURNS integer AS $$ +DECLARE + +BEGIN + return content_item__rename( rename_file__file_id, -- item_id rename_file__name -- name ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__copy_file( - -- - -- Copy a file, but only copy the live_revision - -- - integer, -- cr_items.item_id%TYPE, - integer, -- cr_items.parent_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar -- acs_objects.creation_ip%TYPE -) returns integer as ' -- cr_revisions.revision_id%TYPE -declare - copy_file__file_id alias for $1; - copy_file__target_folder_id alias for $2; - copy_file__creation_user alias for $3; - copy_file__creation_ip alias for $4; + + +-- added +select define_function_args('file_storage__copy_file','file_id,target_folder_id,creation_user,creation_ip'); + +-- +-- procedure file_storage__copy_file/4 +-- +CREATE OR REPLACE FUNCTION file_storage__copy_file( + copy_file__file_id integer, + copy_file__target_folder_id integer, + copy_file__creation_user integer, + copy_file__creation_ip varchar + +) RETURNS integer AS $$ +-- cr_revisions.revision_id%TYPE +DECLARE v_name cr_items.name%TYPE; v_live_revision cr_items.live_revision%TYPE; v_filename cr_revisions.title%TYPE; @@ -211,12 +238,12 @@ v_new_file_id cr_items.item_id%TYPE; v_new_version_id cr_revisions.revision_id%TYPE; v_indb_p boolean; -begin +BEGIN -- We copy only the title from the file being copied, and attributes of the -- live revision select i.name,i.live_revision,r.title,r.description,r.mime_type,r.content_length, - (case when i.storage_type = ''lob'' + (case when i.storage_type = 'lob' then true else false end) @@ -284,26 +311,29 @@ return v_new_version_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__move_file ( - -- - -- Move a file (ans all its versions) to a different folder. - -- Wrapper for content_item__move - -- - integer, -- cr_folders.folder_id%TYPE, - integer, -- cr_folders.folder_id%TYPE - integer, - varchar -) returns integer as ' -- 0 for success -declare - move_file__file_id alias for $1; - move_file__target_folder_id alias for $2; - move_file__creation_user alias for $3; - move_file__creation_ip alias for $4; -begin + +-- added +select define_function_args('file_storage__move_file','file_id,target_folder_id,creation_user,creation_ip'); + +-- +-- procedure file_storage__move_file/4 +-- +CREATE OR REPLACE FUNCTION file_storage__move_file( + move_file__file_id integer, + move_file__target_folder_id integer, + move_file__creation_user integer, + move_file__creation_ip varchar + +) RETURNS integer AS $$ +-- 0 for success +DECLARE +BEGIN + perform content_item__move( move_file__file_id, -- item_id move_file__target_folder_id -- target_folder_id @@ -312,29 +342,37 @@ perform acs_object__update_last_modified(move_file__target_folder_id,move_file__creation_user,move_file__creation_ip); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__get_title ( - -- - integer -- cr_items.item_id%TYPE -) returns varchar as ' -declare - get_title__item_id alias for $1; + + +-- added +select define_function_args('file_storage__get_title','item_id'); + +-- +-- procedure file_storage__get_title/1 +-- +CREATE OR REPLACE FUNCTION file_storage__get_title( + get_title__item_id integer + +) RETURNS varchar AS $$ +DECLARE v_title cr_revisions.title%TYPE; v_content_type cr_items.content_type%TYPE; -begin +BEGIN select content_type into v_content_type from cr_items where item_id = get_title__item_id; - if v_content_type = ''content_folder'' + if v_content_type = 'content_folder' then select label into v_title from cr_folders where folder_id = get_title__item_id; - else if v_content_type = ''content_symlink'' + else if v_content_type = 'content_symlink' then select label into v_title from cr_symlinks where symlink_id = get_title__item_id; @@ -348,13 +386,13 @@ return v_title; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__get_parent_id ( - integer -- item_id in cr_items.item_id%TYPE - ) returns integer as ' -- cr_items.item_id%TYPE +CREATE OR REPLACE FUNCTION file_storage__get_parent_id ( + get_parent_id__item_id integer +) returns integer as $$ declare - get_parent_id__item_id alias for $1; v_parent_id cr_items.item_id%TYPE; begin @@ -365,62 +403,76 @@ return v_parent_id; -end;'language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__get_content_type ( - -- - -- Wrapper for content_item__get_content_type - integer -- cr_items.item_id%TYPE -) returns varchar as ' -- cr_items.content_type%TYPE -declare - get_content_type__file_id alias for $1; -begin + +-- added +select define_function_args('file_storage__get_content_type','file_id'); + +-- +-- procedure file_storage__get_content_type/1 +-- +CREATE OR REPLACE FUNCTION file_storage__get_content_type( + get_content_type__file_id integer +) RETURNS varchar AS $$ +DECLARE +BEGIN return content_item__get_content_type( get_content_type__file_id ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__get_folder_name ( - -- - -- Wrapper for content_folder__get_label - integer -- cr_folders.folder_id%TYPE -) returns varchar as ' -- cr_folders.label%TYPE -declare - get_folder_name__folder_id alias for $1; -begin + + +-- added +select define_function_args('file_storage__get_folder_name','folder_id'); + +-- +-- procedure file_storage__get_folder_name/1 +-- +CREATE OR REPLACE FUNCTION file_storage__get_folder_name( + get_folder_name__folder_id integer + +) RETURNS varchar AS $$ +-- cr_folders.label%TYPE +DECLARE +BEGIN return content_folder__get_label( get_folder_name__folder_id ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__new_version ( - -- - -- Create a new version of a file - -- Wrapper for content_revision__new - -- - varchar, -- cr_revisions.title%TYPE, - varchar, -- cr_revisions.description%TYPE, - varchar, -- cr_revisions.mime_type%TYPE, - integer, -- cr_items.item_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar -- acs_objects.creation_ip%TYPE -) returns integer as ' -- cr_revisions.revision_id -declare - new_version__filename alias for $1; - new_version__description alias for $2; - new_version__mime_type alias for $3; - new_version__item_id alias for $4; - new_version__creation_user alias for $5; - new_version__creation_ip alias for $6; + + +-- added +select define_function_args('file_storage__new_version','filename,description,mime_type,item_id,creation_user,creation_ip'); + +-- +-- procedure file_storage__new_version/6 +-- +CREATE OR REPLACE FUNCTION file_storage__new_version( + new_version__filename varchar, + new_version__description varchar, + new_version__mime_type varchar, + new_version__item_id integer, + new_version__creation_user integer, + new_version__creation_ip varchar + +) RETURNS integer AS $$ +-- cr_revisions.revision_id +DECLARE v_revision_id cr_revisions.revision_id%TYPE; v_folder_id cr_items.parent_id%TYPE; -begin +BEGIN -- Create a revision v_revision_id := content_revision__new ( new_version__filename, -- title @@ -448,22 +500,28 @@ perform acs_object__update_last_modified(new_version__item_id,new_version__creation_user,new_version__creation_ip); return v_revision_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__delete_version ( - -- - -- Delete a version of a file - -- - integer, -- cr_items.item_id%TYPE, - integer -- cr_revisions.revision_id%TYPE -) returns integer as ' -- cr_items.parent_id%TYPE -declare - delete_version__file_id alias for $1; - delete_version__version_id alias for $2; + + +-- added +select define_function_args('file_storage__delete_version','file_id,version_id'); + +-- +-- procedure file_storage__delete_version/2 +-- +CREATE OR REPLACE FUNCTION file_storage__delete_version( + delete_version__file_id integer, + delete_version__version_id integer + +) RETURNS integer AS $$ +-- cr_items.parent_id%TYPE +DECLARE v_parent_id cr_items.parent_id%TYPE; v_deleted_last_version_p boolean; -begin +BEGIN if delete_version__version_id = content_item__get_live_revision(delete_version__file_id) then @@ -488,27 +546,30 @@ -- we cannot delete the content_item entry if there are no more revisions. return v_parent_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__new_folder( - -- - -- Create a folder - -- - varchar, -- cr_items.name%TYPE, - varchar, -- cr_folders.label%TYPE, - integer, -- cr_items.parent_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar -- acs_objects.creation_ip%TYPE -) returns integer as ' -- cr_folders.folder_id%TYPE -declare - new_folder__name alias for $1; - new_folder__folder_name alias for $2; - new_folder__parent_id alias for $3; - new_folder__creation_user alias for $4; - new_folder__creation_ip alias for $5; + + +-- added +select define_function_args('file_storage__new_folder','name,folder_name,parent_id,creation_user,creation_ip'); + +-- +-- procedure file_storage__new_folder/5 +-- +CREATE OR REPLACE FUNCTION file_storage__new_folder( + new_folder__name varchar, + new_folder__folder_name varchar, + new_folder__parent_id integer, + new_folder__creation_user integer, + new_folder__creation_ip varchar + +) RETURNS integer AS $$ +-- cr_folders.folder_id%TYPE +DECLARE v_folder_id cr_folders.folder_id%TYPE; -begin +BEGIN -- Create a new folder v_folder_id := content_folder__new ( @@ -528,75 +589,86 @@ -- JS: to true since we created a new subtype. PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_revision'', -- content_type - ''t''); -- include_subtypes (default) + 'content_revision', -- content_type + 't'); -- include_subtypes (default) PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_folder'', -- content_type - ''t'' -- include_subtypes (default) + 'content_folder', -- content_type + 't' -- include_subtypes (default) ); PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_extlink'', -- content_types - ''t'' -- include_subtypes + 'content_extlink', -- content_types + 't' -- include_subtypes ); PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_symlink'', -- content_types - ''t'' -- include_subtypes + 'content_symlink', -- content_types + 't' -- include_subtypes ); -- Give the creator admin privileges on the folder PERFORM acs_permission__grant_permission ( v_folder_id, -- object_id new_folder__creation_user, -- grantee_id - ''admin'' -- privilege + 'admin' -- privilege ); return v_folder_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__delete_folder( - -- - -- Delete a folder - -- - integer -- cr_folders.folder_id%TYPE -) returns integer as ' -- 0 for success -declare - delete_folder__folder_id alias for $1; -begin + +-- added + +-- +-- procedure file_storage__delete_folder/1 +-- +CREATE OR REPLACE FUNCTION file_storage__delete_folder( + delete_folder__folder_id integer + +) RETURNS integer AS $$ +-- 0 for success +DECLARE +BEGIN + return content_folder__delete( delete_folder__folder_id -- folder_id ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__get_title ( - -- - integer -- cr_items.item_id%TYPE -) returns varchar as ' -declare - get_title__item_id alias for $1; + + +-- +-- procedure file_storage__get_title/1 +-- +CREATE OR REPLACE FUNCTION file_storage__get_title( + get_title__item_id integer + +) RETURNS varchar AS $$ +DECLARE v_title cr_revisions.title%TYPE; v_content_type cr_items.content_type%TYPE; -begin +BEGIN select content_type into v_content_type from cr_items where item_id = get_title__item_id; - if v_content_type = ''content_folder'' + if v_content_type = 'content_folder' then select label into v_title from cr_folders where folder_id = get_title__item_id; - else if v_content_type = ''content_symlink'' + else if v_content_type = 'content_symlink' then select label into v_title from cr_symlinks where symlink_id = get_title__item_id; @@ -610,5 +682,6 @@ return v_title; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- name title switch script moved to a6-a7 upgrade Index: openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0d2-5.1.0d3.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0d2-5.1.0d3.sql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0d2-5.1.0d3.sql 26 May 2005 08:28:45 -0000 1.3 +++ openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0d2-5.1.0d3.sql 30 Mar 2013 22:50:45 -0000 1.4 @@ -5,38 +5,52 @@ -- @cvs-id $Id$ -- -create or replace function file_storage__delete_folder( - -- - -- Delete a folder - -- - integer -- cr_folders.folder_id%TYPE -) returns integer as ' -- 0 for success -declare - delete_folder__folder_id alias for $1; -begin + +-- added + +-- +-- procedure file_storage__delete_folder/1 +-- +CREATE OR REPLACE FUNCTION file_storage__delete_folder( + delete_folder__folder_id -- -- + integer + +) RETURNS integer AS $$ +-- 0 for success +DECLARE +BEGIN + return file_storage__delete_folder( delete_folder__folder_id, -- folder_id - ''f'' + 'f' ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__delete_folder( - -- - -- Delete a folder - -- - integer, -- cr_folders.folder_id%TYPE - boolean -) returns integer as ' -- 0 for success -declare - delete_folder__folder_id alias for $1; - delete_folder__cascade_p alias for $2; -begin + +-- added +select define_function_args('file_storage__delete_folder','folder_id,cascade_p'); + +-- +-- procedure file_storage__delete_folder/2 +-- +CREATE OR REPLACE FUNCTION file_storage__delete_folder( + delete_folder__folder_id -- -- + integer, + delete_folder__cascade_p boolean + +) RETURNS integer AS $$ +-- 0 for success +DECLARE +BEGIN + return content_folder__delete( delete_folder__folder_id, -- folder_id delete_folder__cascade_p ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0d3-5.1.0d4.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0d3-5.1.0d4.sql,v diff -u -r1.5 -r1.6 --- openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0d3-5.1.0d4.sql 26 May 2005 08:28:45 -0000 1.5 +++ openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.1.0d3-5.1.0d4.sql 30 Mar 2013 22:50:45 -0000 1.6 @@ -52,27 +52,26 @@ -- dropped old version in d1-d2 upgrade script. -create or replace function file_storage__new_root_folder ( - -- - -- Creates a new root folder - -- - -- - -- A hackish function to get around the fact that we can not run - -- code automatically when a new package instance is created. - -- - integer, -- apm_packages.package_id%TYPE - varchar, -- cr_folders.label%TYPE - varchar, -- cr_items.name%TYPE - varchar -) -returns integer as ' -- fs_root_folders.folder_id%TYPE -declare - new_root_folder__package_id alias for $1; - new_root_folder__folder_name alias for $2; - new_root_folder__url alias for $3; - new_root_folder__description alias for $4; + + +-- added +select define_function_args('file_storage__new_root_folder','package_id,folder_name,url,description'); + +-- +-- procedure file_storage__new_root_folder/4 +-- +CREATE OR REPLACE FUNCTION file_storage__new_root_folder( + new_root_folder__package_id -- -- -- + integer, + new_root_folder__folder_name varchar, + new_root_folder__url varchar, + new_root_folder__description varchar + +) RETURNS integer AS $$ +-- fs_root_folders.folder_id%TYPE +DECLARE v_folder_id fs_root_folders.folder_id%TYPE; -begin +BEGIN v_folder_id := content_folder__new ( @@ -97,25 +96,26 @@ -- JS: true since we created a new subtype. PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_revision'', -- content_types - ''t'' -- include_subtypes + 'content_revision', -- content_types + 't' -- include_subtypes ); PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_folder'', -- content_types - ''t'' -- include_subtypes + 'content_folder', -- content_types + 't' -- include_subtypes ); PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_symlink'', -- content_types - ''t'' -- include_subtypes + 'content_symlink', -- content_types + 't' -- include_subtypes ); PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_extlink'', -- content_types - ''t'' -- include_subtypes + 'content_extlink', -- content_types + 't' -- include_subtypes ); return v_folder_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.2.0d6-5.2.0d7.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.2.0d6-5.2.0d7.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.2.0d6-5.2.0d7.sql 8 Aug 2006 21:26:48 -0000 1.2 +++ openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.2.0d6-5.2.0d7.sql 30 Mar 2013 22:50:45 -0000 1.3 @@ -8,27 +8,26 @@ -- add package_id to acs_objects for all objects in FS (see Tip 42) -create or replace function file_storage__new_root_folder ( - -- - -- Creates a new root folder - -- - -- - -- A hackish function to get around the fact that we can not run - -- code automatically when a new package instance is created. - -- - integer, -- apm_packages.package_id%TYPE - varchar, -- cr_folders.label%TYPE - varchar, -- cr_items.name%TYPE - varchar -) -returns integer as ' -- fs_root_folders.folder_id%TYPE -declare - new_root_folder__package_id alias for $1; - new_root_folder__folder_name alias for $2; - new_root_folder__url alias for $3; - new_root_folder__description alias for $4; + + +-- added +select define_function_args('file_storage__new_root_folder','package_id,folder_name,url,description'); + +-- +-- procedure file_storage__new_root_folder/4 +-- +CREATE OR REPLACE FUNCTION file_storage__new_root_folder( + new_root_folder__package_id -- -- -- + integer, + new_root_folder__folder_name varchar, + new_root_folder__url varchar, + new_root_folder__description varchar + +) RETURNS integer AS $$ +-- fs_root_folders.folder_id%TYPE +DECLARE v_folder_id fs_root_folders.folder_id%TYPE; -begin +BEGIN v_folder_id := content_folder__new ( @@ -54,54 +53,52 @@ -- JS: true since we created a new subtype. PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_revision'', -- content_types - ''t'' -- include_subtypes + 'content_revision', -- content_types + 't' -- include_subtypes ); PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_folder'', -- content_types - ''t'' -- include_subtypes + 'content_folder', -- content_types + 't' -- include_subtypes ); PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_symlink'', -- content_types - ''t'' -- include_subtypes + 'content_symlink', -- content_types + 't' -- include_subtypes ); PERFORM content_folder__register_content_type( v_folder_id, -- folder_id - ''content_extlink'', -- content_types - ''t'' -- include_subtypes + 'content_extlink', -- content_types + 't' -- include_subtypes ); return v_folder_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__new_file( - -- - -- Create a file in CR in preparation for actual storage - -- Wrapper for content_item__new - -- - -- DRB: I added this version to allow one to predefine item_id, among other things to - -- make it easier to use with ad_form - varchar, -- cr_items.name%TYPE, - integer, -- cr_items.parent_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar, -- acs_objects.creation_ip%TYPE, - boolean, -- store in db? - integer, -- cr_items.item_id%TYPE, - integer -- apm_packages.package_id%TYPE -) returns integer as ' -- cr_items.item_id%TYPE -declare - new_file__name alias for $1; - new_file__folder_id alias for $2; - new_file__user_id alias for $3; - new_file__creation_ip alias for $4; - new_file__indb_p alias for $5; - new_file__item_id alias for $6; - new_file__package_id alias for $7; + + +-- added +select define_function_args('file_storage__new_file','name,folder_id,user_id,creation_ip,indb_p,item_id,package_id'); + +-- +-- procedure file_storage__new_file/7 +-- +CREATE OR REPLACE FUNCTION file_storage__new_file( + new_file__name -- varchar, + new_file__folder_id integer, + new_file__user_id integer, + new_file__creation_ip varchar, + new_file__indb_p boolean, + new_file__item_id integer, + new_file__package_id integer + +) RETURNS integer AS $$ +-- cr_items.item_id%TYPE +DECLARE v_item_id integer; -begin +BEGIN if new_file__indb_p then @@ -114,11 +111,11 @@ new_file__user_id, -- creation_user new_file__folder_id, -- context_id new_file__creation_ip, -- creation_ip - ''content_item'', -- item_subtype (default) - ''file_storage_object'', -- content_type (needed by site-wide search) + 'content_item', -- item_subtype (default) + 'file_storage_object', -- content_type (needed by site-wide search) null, -- title (default) null, -- description - ''text/plain'', -- mime_type (default) + 'text/plain', -- mime_type (default) null, -- nls_language (default) null, -- data (default) new_file__package_id -- package_id @@ -133,14 +130,14 @@ new_file__user_id, -- creation_user new_file__folder_id, -- context_id new_file__creation_ip, -- creation_ip - ''content_item'', -- item_subtype (default) - ''file_storage_object'', -- content_type (needed by site-wide search) + 'content_item', -- item_subtype (default) + 'file_storage_object', -- content_type (needed by site-wide search) null, -- title (default) null, -- description - ''text/plain'', -- mime_type (default) + 'text/plain', -- mime_type (default) null, -- nls_language (default) null, -- text (default) - ''file'', -- storage_type + 'file', -- storage_type new_file__package_id -- package_id ); @@ -150,25 +147,26 @@ return v_item_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__new_file( - varchar, -- cr_items.name%TYPE, - integer, -- cr_items.parent_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar, -- acs_objects.creation_ip%TYPE, - boolean, -- store in db? - integer -- apm_packages.package_id%TYPE -) returns integer as ' -- cr_items.item_id%TYPE -declare - new_file__name alias for $1; - new_file__folder_id alias for $2; - new_file__user_id alias for $3; - new_file__creation_ip alias for $4; - new_file__indb_p alias for $5; - new_file__package_id alias for $6; -begin + + +-- +-- procedure file_storage__new_file/6 +-- +CREATE OR REPLACE FUNCTION file_storage__new_file( + new_file__name varchar, + new_file__folder_id integer, + new_file__user_id integer, + new_file__creation_ip varchar, + new_file__indb_p boolean, + new_file__package_id integer +) RETURNS integer AS $$ +-- cr_items.item_id%TYPE +DECLARE +BEGIN return file_storage__new_file( new_file__name, -- name new_file__folder_id, -- parent_id @@ -179,7 +177,8 @@ new_file__package_id -- pacakge_id ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- upgrade existing data -- do in transaction since we're bashing acs_objects in a major way Index: openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.2.0d7-5.2.0d8.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.2.0d7-5.2.0d8.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.2.0d7-5.2.0d8.sql 8 Aug 2006 21:26:48 -0000 1.2 +++ openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.2.0d7-5.2.0d8.sql 30 Mar 2013 22:50:45 -0000 1.3 @@ -1,28 +1,20 @@ -create or replace function file_storage__new_file( - -- - -- Create a file in CR in preparation for actual storage - -- Wrapper for content_item__new - -- - -- DRB: I added this version to allow one to predefine item_id, among other things to - -- make it easier to use with ad_form - varchar, -- cr_items.name%TYPE, - integer, -- cr_items.parent_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar, -- acs_objects.creation_ip%TYPE, - boolean, -- store in db? - integer, -- cr_items.item_id%TYPE, - integer -- apm_packages.package_id%TYPE -) returns integer as ' -- cr_items.item_id%TYPE -declare - new_file__name alias for $1; - new_file__folder_id alias for $2; - new_file__user_id alias for $3; - new_file__creation_ip alias for $4; - new_file__indb_p alias for $5; - new_file__item_id alias for $6; - new_file__package_id alias for $7; +-- +-- procedure file_storage__new_file/7 +-- +CREATE OR REPLACE FUNCTION file_storage__new_file( + new_file__name varchar, + new_file__folder_id integer, + new_file__user_id integer, + new_file__creation_ip varchar, + new_file__indb_p boolean, + new_file__item_id integer, + new_file__package_id integer + +) RETURNS integer AS $$ +-- cr_items.item_id%TYPE +DECLARE v_item_id integer; -begin +BEGIN if new_file__indb_p then @@ -35,11 +27,11 @@ new_file__user_id, -- creation_user new_file__folder_id, -- context_id new_file__creation_ip, -- creation_ip - ''content_item'', -- item_subtype (default) - ''file_storage_object'', -- content_type (needed by site-wide search) + 'content_item', -- item_subtype (default) + 'file_storage_object', -- content_type (needed by site-wide search) null, -- title (default) null, -- description - ''text/plain'', -- mime_type (default) + 'text/plain', -- mime_type (default) null, -- nls_language (default) null, -- data (default) new_file__package_id -- package_id @@ -54,14 +46,14 @@ new_file__user_id, -- creation_user new_file__folder_id, -- context_id new_file__creation_ip, -- creation_ip - ''content_item'', -- item_subtype (default) - ''file_storage_object'', -- content_type (needed by site-wide search) + 'content_item', -- item_subtype (default) + 'file_storage_object', -- content_type (needed by site-wide search) null, -- title (default) null, -- description - ''text/plain'', -- mime_type (default) + 'text/plain', -- mime_type (default) null, -- nls_language (default) null, -- text (default) - ''file'', -- storage_type + 'file', -- storage_type new_file__package_id -- package_id ); @@ -71,25 +63,33 @@ return v_item_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function file_storage__new_file( - varchar, -- cr_items.name%TYPE, - integer, -- cr_items.parent_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar, -- acs_objects.creation_ip%TYPE, - boolean, -- store in db? - integer -- apm_packages.package_id%TYPE -) returns integer as ' -- cr_items.item_id%TYPE -declare - new_file__name alias for $1; - new_file__folder_id alias for $2; - new_file__user_id alias for $3; - new_file__creation_ip alias for $4; - new_file__indb_p alias for $5; - new_file__package_id alias for $6; -begin + + +-- added + +-- old define_function_args('file_storage__new_file','name,folder_id,user_id,creation_ip,indb_p,package_id') +-- new +select define_function_args('file_storage__new_file','name,folder_id,user_id,creation_ip,indb_p,item_id,package_id'); + + +-- +-- procedure file_storage__new_file/6 +-- +CREATE OR REPLACE FUNCTION file_storage__new_file( + new_file__name varchar, + new_file__folder_id integer, + new_file__user_id integer, + new_file__creation_ip varchar, + new_file__indb_p boolean, + new_file__package_id integer +) RETURNS integer AS $$ +-- cr_items.item_id%TYPE +DECLARE +BEGIN return file_storage__new_file( new_file__name, -- name new_file__folder_id, -- parent_id @@ -100,4 +100,5 @@ new_file__package_id -- pacakge_id ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.4.0d4-5.4.0d5.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.4.0d4-5.4.0d5.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.4.0d4-5.4.0d5.sql 9 Jul 2007 07:48:57 -0000 1.1 +++ openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-5.4.0d4-5.4.0d5.sql 30 Mar 2013 22:50:45 -0000 1.2 @@ -1,17 +1,19 @@ -create or replace function file_storage__copy_file( - -- - -- Copy a file, but only copy the live_revision - -- - integer, -- cr_items.item_id%TYPE, - integer, -- cr_items.parent_id%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar -- acs_objects.creation_ip%TYPE -) returns integer as ' -- cr_revisions.revision_id%TYPE -declare - copy_file__file_id alias for $1; - copy_file__target_folder_id alias for $2; - copy_file__creation_user alias for $3; - copy_file__creation_ip alias for $4; + +-- added +select define_function_args('file_storage__copy_file','file_id,target_folder_id,creation_user,creation_ip'); + +-- +-- procedure file_storage__copy_file/4 +-- +CREATE OR REPLACE FUNCTION file_storage__copy_file( + copy_file__file_id integer, + copy_file__target_folder_id integer, + copy_file__creation_user integer, + copy_file__creation_ip varchar + +) RETURNS integer AS $$ +-- cr_revisions.revision_id%TYPE +DECLARE v_name cr_items.name%TYPE; v_live_revision cr_items.live_revision%TYPE; v_filename cr_revisions.title%TYPE; @@ -27,11 +29,11 @@ v_isurl boolean; v_content_type cr_items.content_type%TYPE; v_package_id apm_packages.package_id%TYPE; -begin +BEGIN v_isurl:= false; select content_type into v_content_type from cr_items where item_id = copy_file__file_id; - if v_content_type = ''content_extlink'' + if v_content_type = 'content_extlink' then v_isurl:= true; end if; @@ -40,7 +42,7 @@ if v_isurl = false then select i.name,i.live_revision,r.title,r.description,r.mime_type,r.content_length, - (case when i.storage_type = ''lob'' + (case when i.storage_type = 'lob' then true else false end) @@ -110,5 +112,6 @@ return 0; end if; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/general-comments/sql/postgresql/general-comments-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/general-comments/sql/postgresql/general-comments-create.sql,v diff -u -r1.5 -r1.6 --- openacs-4/packages/general-comments/sql/postgresql/general-comments-create.sql 21 Mar 2005 12:01:21 -0000 1.5 +++ openacs-4/packages/general-comments/sql/postgresql/general-comments-create.sql 30 Mar 2013 22:50:45 -0000 1.6 @@ -34,18 +34,18 @@ -- create an index on foreign key constraint create index general_comments_object_id_idx on general_comments (object_id); -create function inline_0 () -returns integer as ' +CREATE OR REPLACE FUNCTION inline_0 () RETURNS integer AS $$ -- define and grant privileges -begin +BEGIN -- create privileges - PERFORM acs_privilege__create_privilege(''general_comments_create'', null, null); + PERFORM acs_privilege__create_privilege('general_comments_create', null, null); - PERFORM acs_privilege__add_child(''annotate'', ''general_comments_create''); + PERFORM acs_privilege__add_child('annotate', 'general_comments_create'); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0 (); @@ -55,27 +55,27 @@ -- NOTE: this is only temporary until we figure out how -- packages will register child types to an acs-message -create function inline_1 () -returns integer as ' -begin +CREATE OR REPLACE FUNCTION inline_1 () RETURNS integer AS $$ +BEGIN PERFORM content_type__register_child_type ( - /* parent_type => */ ''acs_message_revision'', - /* child_type => */ ''content_revision'', - ''generic'', 0, null + /* parent_type => */ 'acs_message_revision', + /* child_type => */ 'content_revision', + 'generic', 0, null ); PERFORM content_type__register_child_type ( - /* parent_type => */ ''acs_message_revision'', - /* child_type => */ ''image'', - ''generic'', 0, null + /* parent_type => */ 'acs_message_revision', + /* child_type => */ 'image', + 'generic', 0, null ); PERFORM content_type__register_child_type ( - /* parent_type => */ ''acs_message_revision'', - /* child_type => */ ''content_extlink'', - ''generic'', 0, null + /* parent_type => */ 'acs_message_revision', + /* child_type => */ 'content_extlink', + 'generic', 0, null ); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_1 (); Index: openacs-4/packages/general-comments/sql/postgresql/general-comments-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/general-comments/sql/postgresql/general-comments-drop.sql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/general-comments/sql/postgresql/general-comments-drop.sql 14 Jun 2001 19:52:22 -0000 1.3 +++ openacs-4/packages/general-comments/sql/postgresql/general-comments-drop.sql 30 Mar 2013 22:50:45 -0000 1.4 @@ -22,11 +22,16 @@ -- / -- remove all comments from the system -create function inline_0 () -returns integer as ' -declare + + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( +) RETURNS integer AS $$ +DECLARE comment_rec RECORD; -begin +BEGIN FOR comment_rec IN select comment_id from general_comments LOOP @@ -47,13 +52,11 @@ END LOOP; return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0 (); drop function inline_0 (); - --- / - drop table general_comments; Index: openacs-4/packages/rss-support/sql/postgresql/rss-generation-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/rss-support/sql/postgresql/rss-generation-create.sql,v diff -u -r1.13 -r1.14 --- openacs-4/packages/rss-support/sql/postgresql/rss-generation-create.sql 13 Feb 2008 22:40:05 -0000 1.13 +++ openacs-4/packages/rss-support/sql/postgresql/rss-generation-create.sql 30 Mar 2013 22:50:45 -0000 1.14 @@ -1,95 +1,96 @@ -create function inline_0 () -returns integer as ' -begin + +CREATE OR REPLACE FUNCTION inline_0 () RETURNS integer AS $$ +BEGIN PERFORM acs_object_type__create_type ( - ''rss_gen_subscr'', -- object_type - ''RSS Generation Subscription'', -- pretty_name - ''RSS Generation Subscriptions'', -- pretty_plural - ''acs_object'', -- supertype - ''rss_gen_subscrs'', -- table_name - ''subscr_id'', -- id_column + 'rss_gen_subscr', -- object_type + 'RSS Generation Subscription', -- pretty_name + 'RSS Generation Subscriptions', -- pretty_plural + 'acs_object', -- supertype + 'rss_gen_subscrs', -- table_name + 'subscr_id', -- id_column null, -- package_name - ''f'', -- abstract_p + 'f', -- abstract_p null, -- type_extension_table - ''rss_gen_subscr__name'' -- name_method + 'rss_gen_subscr__name' -- name_method ); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0 (); drop function inline_0 (); -create function inline_1 () -returns integer as ' -begin +CREATE OR REPLACE FUNCTION inline_1 () RETURNS integer AS $$ +BEGIN PERFORM acs_attribute__create_attribute ( - ''rss_gen_subscr'', -- object_type - ''IMPL_ID'', -- attribute_name - ''integer'', -- datatype - ''Implementation ID'', -- pretty_name - ''Implementation IDs'', -- pretty_plural + 'rss_gen_subscr', -- object_type + 'IMPL_ID', -- attribute_name + 'integer', -- datatype + 'Implementation ID', -- pretty_name + 'Implementation IDs', -- pretty_plural null, -- table_name null, -- column_name null, -- default_value 1, -- min_n_values 1, -- max_n_values null, -- sort_order - ''type_specific'', -- storage - ''f'' -- static_p + 'type_specific', -- storage + 'f' -- static_p ); PERFORM acs_attribute__create_attribute ( - ''rss_gen_subscr'', -- object_type - ''SUMMARY_CONTEXT_ID'', -- attribute_name - ''integer'', -- datatype - ''Context Identifier'', -- pretty_name - ''Context Identifiers'', -- pretty_plural + 'rss_gen_subscr', -- object_type + 'SUMMARY_CONTEXT_ID', -- attribute_name + 'integer', -- datatype + 'Context Identifier', -- pretty_name + 'Context Identifiers', -- pretty_plural null, -- table_name null, -- column_name null, -- default_value 1, -- min_n_values 1, -- max_n_values null, -- sort_order - ''type_specific'', -- storage - ''f'' -- static_p + 'type_specific', -- storage + 'f' -- static_p ); PERFORM acs_attribute__create_attribute ( - ''rss_gen_subscr'', -- object_type - ''TIMEOUT'', -- attribute_name - ''integer'', -- datatype - ''Timeout'', -- pretty_name - ''Timeouts'', -- pretty_plural + 'rss_gen_subscr', -- object_type + 'TIMEOUT', -- attribute_name + 'integer', -- datatype + 'Timeout', -- pretty_name + 'Timeouts', -- pretty_plural null, -- table_name null, -- column_name null, -- default_value 1, -- min_n_values 1, -- max_n_values null, -- sort_order - ''type_specific'', -- storage - ''f'' -- static_p + 'type_specific', -- storage + 'f' -- static_p ); PERFORM acs_attribute__create_attribute ( - ''rss_gen_subscr'', -- object_type - ''LASTBUILD'', -- attribute_name - ''integer'', -- datatype - ''Last Build'', -- pretty_name - ''Last Builds'', -- pretty_plural + 'rss_gen_subscr', -- object_type + 'LASTBUILD', -- attribute_name + 'integer', -- datatype + 'Last Build', -- pretty_name + 'Last Builds', -- pretty_plural null, -- table_name null, -- column_name null, -- default_value 1, -- min_n_values 1, -- max_n_values null, -- sort_order - ''type_specific'', -- storage - ''f'' -- static_p + 'type_specific', -- storage + 'f' -- static_p ); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_1 (); @@ -164,33 +165,33 @@ Used for display purposes. '; -select define_function_args ('rss_gen_subscr__new','p_subscr_id,p_impl_id,p_summary_context_id,p_timeout,p_lastbuild;now,p_object_type,p_creation_date;now,p_creation_user,p_creation_ip,p_context_id'); -create function rss_gen_subscr__new ( - integer, -- subscr_id - integer, -- impl_id - varchar, -- summary_context_id - integer, -- timeout - timestamptz, -- lastbuild - varchar, -- object_type - timestamptz, -- creation_date - integer, -- creation_user - varchar, -- creation_ip - integer -- context_id -) returns integer as ' -declare - p_subscr_id alias for $1; - p_impl_id alias for $2; - p_summary_context_id alias for $3; - p_timeout alias for $4; - p_lastbuild alias for $5; - p_object_type alias for $6; -- default ''rss_gen_subscr'' - p_creation_date alias for $7; -- default now() - p_creation_user alias for $8; -- default null - p_creation_ip alias for $9; -- default null - p_context_id alias for $10; -- default null + +-- old define_function_args ('rss_gen_subscr__new','p_subscr_id,p_impl_id,p_summary_context_id,p_timeout,p_lastbuild;now,p_object_type,p_creation_date;now,p_creation_user,p_creation_ip,p_context_id') +-- new +select define_function_args('rss_gen_subscr__new','p_subscr_id,p_impl_id,p_summary_context_id,p_timeout,p_lastbuild;now,p_object_type;rss_gen_subscr,p_creation_date;now,p_creation_user;null,p_creation_ip;null,p_context_id;null'); + + + +-- +-- procedure rss_gen_subscr__new/10 +-- +CREATE OR REPLACE FUNCTION rss_gen_subscr__new( + p_subscr_id integer, + p_impl_id integer, + p_summary_context_id varchar, + p_timeout integer, + p_lastbuild timestamptz, -- default 'now' + p_object_type varchar, -- default 'rss_gen_subscr' + p_creation_date timestamptz, -- default now() -- default 'now' + p_creation_user integer, -- default null + p_creation_ip varchar, -- default null + p_context_id integer -- default null + +) RETURNS integer AS $$ +DECLARE v_subscr_id rss_gen_subscrs.subscr_id%TYPE; v_summary_context_id rss_gen_subscrs.summary_context_id%TYPE; -begin +BEGIN v_subscr_id := acs_object__new ( p_subscr_id, p_object_type, @@ -213,39 +214,64 @@ return v_subscr_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function rss_gen_subscr__name (integer) -returns varchar as ' -declare - p_subscr_id alias for $1; -begin - return ''RSS Generation Subscription #'' || p_subscr_id; -end;' language 'plpgsql'; + +-- added +select define_function_args('rss_gen_subscr__name','subscr_id'); + +-- +-- procedure rss_gen_subscr__name/1 +-- +CREATE OR REPLACE FUNCTION rss_gen_subscr__name( + p_subscr_id integer +) RETURNS varchar AS $$ +DECLARE +BEGIN + return 'RSS Generation Subscription #' || p_subscr_id; +END; +$$ LANGUAGE plpgsql; + select define_function_args('rss_gen_subscr__del','subscr_id'); -create or replace function rss_gen_subscr__del (integer) -returns integer as ' -declare - p_subscr_id alias for $1; -begin + + +-- +-- procedure rss_gen_subscr__del/1 +-- +CREATE OR REPLACE FUNCTION rss_gen_subscr__del( + p_subscr_id integer +) RETURNS integer AS $$ +DECLARE +BEGIN delete from acs_permissions where object_id = p_subscr_id; delete from rss_gen_subscrs where subscr_id = p_subscr_id; - raise NOTICE ''Deleting subscription...''; + raise NOTICE 'Deleting subscription...'; PERFORM acs_object__delete(p_subscr_id); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function rss_gen_subscr__delete (integer) -returns integer as ' -declare - p_subscr_id alias for $1; -begin + + +-- added +select define_function_args('rss_gen_subscr__delete','subscr_id'); + +-- +-- procedure rss_gen_subscr__delete/1 +-- +CREATE OR REPLACE FUNCTION rss_gen_subscr__delete( + p_subscr_id integer +) RETURNS integer AS $$ +DECLARE +BEGIN return rss_gen_subscr__del (p_subscr_id); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/rss-support/sql/postgresql/rss-generation-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/rss-support/sql/postgresql/rss-generation-drop.sql,v diff -u -r1.5 -r1.6 --- openacs-4/packages/rss-support/sql/postgresql/rss-generation-drop.sql 24 Feb 2005 13:33:25 -0000 1.5 +++ openacs-4/packages/rss-support/sql/postgresql/rss-generation-drop.sql 30 Mar 2013 22:50:45 -0000 1.6 @@ -1,14 +1,19 @@ -create function inline_0 () -returns integer as ' -declare +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( + +) RETURNS integer AS $$ +DECLARE subscr record; v_result integer; -begin +BEGIN for subscr in select subscr_id from rss_gen_subscrs loop select rss_gen_subscr__delete(subscr.subscr_id) into v_result; end loop; return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0 (); drop function inline_0 (); Index: openacs-4/packages/rss-support/sql/postgresql/upgrade/upgrade-0.3d2-0.3d3.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/rss-support/sql/postgresql/upgrade/upgrade-0.3d2-0.3d3.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/rss-support/sql/postgresql/upgrade/upgrade-0.3d2-0.3d3.sql 13 Jan 2005 13:58:40 -0000 1.2 +++ openacs-4/packages/rss-support/sql/postgresql/upgrade/upgrade-0.3d2-0.3d3.sql 30 Mar 2013 22:50:45 -0000 1.3 @@ -16,32 +16,35 @@ integer -- context_id ); -create function rss_gen_subscr__new ( - integer, -- subscr_id - integer, -- impl_id - varchar, -- summary_context_id - integer, -- timeout - timestamptz, -- lastbuild - varchar, -- object_type - timestamptz, -- creation_date - integer, -- creation_user - varchar, -- creation_ip - integer -- context_id -) returns integer as ' -declare - p_subscr_id alias for $1; - p_impl_id alias for $2; - p_summary_context_id alias for $3; - p_timeout alias for $4; - p_lastbuild alias for $5; - p_object_type alias for $6; -- default ''rss_gen_subscr'' - p_creation_date alias for $7; -- default now() - p_creation_user alias for $8; -- default null - p_creation_ip alias for $9; -- default null - p_context_id alias for $10; -- default null + + +-- added + +-- old define_function_args('rss_gen_subscr__new','subscr_id,impl_id,summary_context_id,timeout,lastbuild,object_type;rss_gen_subscr,creation_date;now(),creation_user;null,creation_ip;null,context_id;null') +-- new +select define_function_args('rss_gen_subscr__new','p_subscr_id,p_impl_id,p_summary_context_id,p_timeout,p_lastbuild;now,p_object_type;rss_gen_subscr,p_creation_date;now,p_creation_user;null,p_creation_ip;null,p_context_id;null'); + + +-- +-- procedure rss_gen_subscr__new/10 +-- +CREATE OR REPLACE FUNCTION rss_gen_subscr__new( + p_subscr_id integer, + p_impl_id integer, + p_summary_context_id varchar, + p_timeout integer, + p_lastbuild timestamptz, + p_object_type varchar, -- default 'rss_gen_subscr' + p_creation_date timestamptz, -- default now() + p_creation_user integer, -- default null + p_creation_ip varchar, -- default null + p_context_id integer -- default null + +) RETURNS integer AS $$ +DECLARE v_subscr_id rss_gen_subscrs.subscr_id%TYPE; v_summary_context_id rss_gen_subscrs.summary_context_id%TYPE; -begin +BEGIN v_subscr_id := acs_object__new ( p_subscr_id, p_object_type, @@ -64,4 +67,5 @@ return v_subscr_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/rss-support/sql/postgresql/upgrade/upgrade-0.3d4-0.3d5.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/rss-support/sql/postgresql/upgrade/upgrade-0.3d4-0.3d5.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/rss-support/sql/postgresql/upgrade/upgrade-0.3d4-0.3d5.sql 24 Feb 2005 13:33:25 -0000 1.2 +++ openacs-4/packages/rss-support/sql/postgresql/upgrade/upgrade-0.3d4-0.3d5.sql 30 Mar 2013 22:50:45 -0000 1.3 @@ -6,31 +6,50 @@ -- @arch-tag: 2abf85db-45a1-4444-856d-683a01be7937 -- @cvs-id $Id$ -- -select define_function_args ('rss_gen_subscr__new','subscr_id,impl_id,summary_context_id,timeout,lastbuild,object_type,creation_date;now,creation_user,creation_ip,context_id'); +-- old define_function_args ('rss_gen_subscr__new','subscr_id,impl_id,summary_context_id,timeout,lastbuild,object_type,creation_date;now,creation_user,creation_ip,context_id') +-- new +select define_function_args('rss_gen_subscr__new','p_subscr_id,p_impl_id,p_summary_context_id,p_timeout,p_lastbuild;now,p_object_type;rss_gen_subscr,p_creation_date;now,p_creation_user;null,p_creation_ip;null,p_context_id;null'); + + select define_function_args('rss_gen_subscr__del','subscr_id'); -create or replace function rss_gen_subscr__del (integer) -returns integer as ' -declare - p_subscr_id alias for $1; -begin + + +-- +-- procedure rss_gen_subscr__del/1 +-- +CREATE OR REPLACE FUNCTION rss_gen_subscr__del( + p_subscr_id integer +) RETURNS integer AS $$ +DECLARE +BEGIN delete from acs_permissions where object_id = p_subscr_id; delete from rss_gen_subscrs where subscr_id = p_subscr_id; - raise NOTICE ''Deleting subscription...''; + raise NOTICE 'Deleting subscription...'; PERFORM acs_object__delete(p_subscr_id); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function rss_gen_subscr__delete (integer) -returns integer as ' -declare - p_subscr_id alias for $1; -begin + + +-- added +select define_function_args('rss_gen_subscr__delete','subscr_id'); + +-- +-- procedure rss_gen_subscr__delete/1 +-- +CREATE OR REPLACE FUNCTION rss_gen_subscr__delete( + p_subscr_id integer +) RETURNS integer AS $$ +DECLARE +BEGIN return rss_gen_subscr__del (p_subscr_id); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql;