Forum OpenACS Q&A: upgrading to PG 7.3 on old 3.x ACS
I've recently upgraded my server to Postgres 7.3 and my (ancient) OACS 3.x some of the code seems to be breaking. So, I'd like to druge up some old code and see if you can help me :).
There are problems I figured out (for example ::datetime needs to be ::timestamptz). But there are ones I can't figure out. Especially stuff in the homepage procedures. For example, hp_get_filesystem_root_node works when changed to t he following:
create function hp_get_filesystem_root_node(integer) returns integer as '
declare
u_id alias for $1;
root_id integer;
BEGIN
select file_id into root_id from users_files
where filename = u_id::varchar
and parent_id is null
and owner_id = u_id;
return root_id;
END;
' language 'plpgsql';
But I tried doing the same fix on hp_true_filename (knocking the additional OR constraing on the "parent_id is null" line) and get this error:
denali=# select hp_true_filename(10) from dual; WARNING: plpgsql: ERROR during compile of hp_true_filename near line 3 ERROR: parser: parse error at or near ")" at character 15The function definition is
create function hp_true_filename(integer) returns varchar as '
declare
filesystem_node alias for $1;
fullname varchar;
parentid integer;
BEGIN
select into parentid parent_id from users_files
where file_id = filesystem_node;
select into fullname filename from users_files
where file_id = filesystem_node;
IF parentid is null or parentid = ''''
THEN
return fullname;
ELSE
return hp_true_filename(parentid) || ''/'' || fullname;
END IF;
END;
' language 'plpgsql';
Any ideas? Pointers to stuff that will help me out, specific help, or whatever else you think might be helpful, would be really appreciated!
Posted by
Vinod Kurup
on 09/29/03 04:33 AM
Hi Justin,
Not sure if this is the issue for you, but I think psql has become very intolerant of tabs in PG 7.3. See my post about this. Try converting all the tabs in hp_true_filename to spaces and then reloading it into psql.
Posted by
Justin Palmer
on 09/29/03 04:42 AM
Thanks, Vinod. It turns out that the issue wasn't tabs. The problem was that it didn't like some of the boolean operations. Below are functions that work. psql -f them in and it's set.
-- returned value is a filename that does not begin with a slash
create or replace function hp_true_filename(integer) returns varchar as '
declare
filesystem_node alias for $1;
fullnamevarchar;
parentidinteger;
BEGIN
select into parentid parent_id from users_files
where file_id = filesystem_node;
select into fullname filename from users_files
where file_id = filesystem_node;
IF parentid
THEN
return fullname;
ELSE
return hp_true_filename(parentid) || ''/'' || fullname;
END IF;
END;
' language 'plpgsql';
-- drop function hp_true_dirname(integer);
create or replace function hp_true_dirname(integer) returns varchar as '
declare
filesystem_node alias for $1;
fullnamevarchar;
parentidinteger;
dirp char(1);
BEGIN
select into parentid parent_id from users_files
where file_id = filesystem_node;
select into fullname filename from users_files
where file_id = filesystem_node;
select into dirp directory_p from users_files
where file_id = filesystem_node;
IF dirp = ''f'' then
fullname := '''';
END IF;
IF parentid
THEN
return fullname;
ELSE
return hp_true_dirname(parentid) || ''/'' || fullname;
END IF;
END;
' language 'plpgsql';
-- returned value is a varchar2 which is the sort key
-- Uses the fact that the full filename of each file has
-- to be unique.
-- drop function hp_filesystem_node_sortkey_gen(integer,integer);
create or replace function hp_filesystem_node_sortkey_gen(integer,integer)
returns varchar as '
declare
filesystem_node alias for $1;
start alias for $2;
fullnamevarchar;
parentidinteger;
dir_pvarchar(1);
plsql_valrecord;
discriminatorvarchar(5); -- useful for discriminating between files and directories
BEGIN
select into plsql_val
filename,
(case when directory_p = ''t'' then ''0'' else ''1'' end) as dp,
parent_id
from users_files
where file_id = filesystem_node;
dir_p := plsql_val.dp;
fullname := plsql_val.filename;
parentid := plsql_val.parent_id;
IF parentid = start or parentid
THEN
return dir_p || fullname;
ELSE
return hp_filesystem_node_sortkey_gen(parentid,start) || ''/'' || dir_p || fullname;
END IF;
END;
' language 'plpgsql';
-- select *,hp_filesystem_node_sortkey_gen(file_id) as generated_sort_key,filename
-- from users_files order by generated_sort_key asc;
-- drop function hp_filesystem_node_level_gen(integer,integer,integer);
create or replace function hp_filesystem_node_level_gen(integer,integer,integer)
returns integer as '
declare
filesystem_node alias for $1;
count alias for $2;
start alias for $3;
parentidinteger;
BEGIN
select into parentid parent_id
from users_files
where file_id = filesystem_node;
IF parentid = start or parentid
THEN
return count;
ELSE
return hp_filesystem_node_level_gen(parentid,count+1,start);
END IF;
END;
' language 'plpgsql';
-- drop function hp_id_is_subnode(integer,integer);
create or replace function hp_id_is_subnode(integer,integer)
returns varchar as '
declare
node alias for $1;
filesystem_node alias for $2;
parentidinteger;
BEGIN
select into parentid parent_id
from users_files
where file_id = node;
IF parentid is null THEN
return ''f'';
ELSE IF parentid = filesystem_node THEN
return ''t'';
ELSE
return hp_id_is_subnode(parentid,filesystem_node);
END IF;
END IF;
END;
' language 'plpgsql';
-- select *,hp_filesystem_node_sortkey_gen(file_id) as generated_sort_key,hp_filesystem_node_level_gen(file_id,0) as level
-- from users_files order by generated_sort_key asc;
-- returns a filename beginning with a slash, unless the file is user's root
-- drop function hp_user_relative_filename(integer);
create or replace function hp_user_relative_filename(integer) returns varchar as '
declare
filesystem_node alias for $1;
fullnamevarchar;
parentidinteger;
BEGIN
select into fullname filename from users_files
where file_id = filesystem_node;
select into parentid parent_id from users_files
where file_id = filesystem_node;
IF parentid
THEN
return '''';
ELSE
return hp_user_relative_filename(parentid) || ''/'' || fullname;
END IF;
END;
' language 'plpgsql';
-- drop function hp_get_filesystem_root_node(integer);
create or replace function hp_get_filesystem_root_node(integer) returns integer as '
declare
u_id alias for $1;
root_id integer;
BEGIN
select into root_id file_id from users_files
where filename = u_id::varchar
and parent_id is null
and owner_id = u_id;
return root_id;
END;
' language 'plpgsql';
select hp_get_filesystem_root_node(1);
create or replace function hp_get_filesystem_node_owner(integer) returns integer as '
declare
fsid alias for $1;
owner_idinteger;
BEGIN
select into owner_id owner_id from users_files
where file_id = fsid;
return owner_id;
END;
' language 'plpgsql';
create or replace function hp_get_filesystem_child_count(integer) returns integer as '
declare
fsid alias for $1;
counter integer;
BEGIN
select into counter count(*) from users_files
where parent_id = fsid;
return counter;
END;
' language 'plpgsql';
-- drop function hp_access_denied_p(integer,integer);
create or replace function hp_access_denied_p(integer,integer) returns integer as '
declare
fsid alias for $1;
u_id alias for $2;
o_id integer;
BEGIN
select into o_id owner_id from users_files
where file_id = fsid;
IF o_id = u_id
THEN
return 0;
ELSE
return 1;
END IF;
END;
' language 'plpgsql';
-- select hp_access_denied_p(2,1);
-- drop function hp_fs_node_from_rel_name(integer,varchar);
create or replace function hp_fs_node_from_rel_name(integer,varchar) returns integer as '
declare
rootid alias for $1;
rel_name alias for $2;
id integer;
rname varchar;
slash_location integer;
nodeid integer;
BEGIN
rname := rel_name;
id := rootid;
LOOP
IF rname = '''' THEN
return id;
END IF;
slash_location := position(''/'' in rname);
IF slash_location = 0 THEN
select into nodeid file_id
from users_files
where parent_id = id
and filename = rname;
return nodeid;
ELSE IF slash_location = 1 THEN
rname := substr(rname,2);
ELSE
select into nodeid file_id
from users_files
where parent_id = id
and filename = SUBSTR(rname,1,slash_location - 1);
id := nodeid;
rname := substr(rname,slash_location);
END IF;
END IF;
END LOOP;
END;
' language 'plpgsql';
------------------------------------------
-- ENDOF fileSystemManagement codeBlock --
------------------------------------------
---------------------------------------------
-- BEGINNINGOF contentManagement codeBlock --
---------------------------------------------
create or replace function hp_top_level_content_title(integer) returns varchar as '
declare
filesystem_node alias for $1;
managedp varchar(1);
fullnamevarchar;
parentidinteger;
parent_managedpvarchar(1);
BEGIN
select into fullname file_pretty_name from users_files
where file_id = filesystem_node;
select into parentid parent_id from users_files
where file_id = filesystem_node;
select into managedp managed_p from users_files
where file_id = filesystem_node;
IF parentid
THEN
return fullname;
END IF;
IF managedp = ''t''
THEN
select into parent_managedp managed_p
from users_files
where file_id = parentid;
IF parent_managedp = ''f''
THEN
return fullname;
ELSE
return hp_top_level_content_title(parentid);
END IF;
ELSE
return fullname;
END IF;
END;
' language 'plpgsql';
create or replace function hp_top_level_content_node(integer) returns varchar as '
declare
filesystem_node alias for $1;
managedp varchar(1);
parentidinteger;
parent_managedpvarchar(1);
BEGIN
select into parentid parent_id from users_files
where file_id = filesystem_node;
select into managedp managed_p from users_files
where file_id = filesystem_node;
IF parentid
THEN
return filesystem_node;
END IF;
IF managedp = ''t''
THEN
select managed_p into parent_managedp
from users_files
where file_id = parentid;
IF parent_managedp = ''f''
THEN
return filesystem_node;
ELSE
return hp_top_level_content_node(parentid);
END IF;
ELSE
return filesystem_node;
END IF;
END;
' language 'plpgsql';
create or replace function hp_onelevelup_content_title(integer) returns varchar as '
declare
filesystem_node alias for $1;
managedp varchar(1);
dirpvarchar(1);
parentidinteger;
fullnamevarchar;
BEGIN
select into fullname file_pretty_name from users_files
where file_id = filesystem_node;
select into parentid parent_id from users_files
where file_id = filesystem_node;
select into managedp managed_p from users_files
where file_id = filesystem_node;
select into dirp directory_p from users_files
where file_id = filesystem_node;
IF parentid
THEN
return fullname;
END IF;
IF managedp = ''t''
THEN
IF dirp = ''t''
THEN
return fullname;
ELSE
return hp_onelevelup_content_title(parentid);
END IF;
ELSE
return fullname;
END IF;
END;
' language 'plpgsql';
create or replace function hp_onelevelup_content_node(integer) returns varchar as '
declare
filesystem_node alias for $1;
managedp varchar(1);
dirpvarchar(1);
parentidinteger;
BEGIN
select into parentid parent_id from users_files
where file_id = filesystem_node;
select into managedp managed_p from users_files
where file_id = filesystem_node;
select into dirp directory_p from users_files
where file_id = filesystem_node;
IF parentid
THEN
return filesystem_node;
END IF;
IF managedp = ''t''
THEN
IF dirp = ''t''
THEN
return filesystem_node;
ELSE
return hp_onelevelup_content_node(parentid);
END IF;
ELSE
return filesystem_node;
END IF;
END;
' language 'plpgsql';
---------------------------------------
-- ENDOF contentManagement codeBlock --
---------------------------------------
---------------------------------------------------
-- BEGINNINGOF neighbourhoodManagement codeBlock --
---------------------------------------------------
create or replace function hp_true_neighborhood_name(integer) returns varchar as '
declare
neighborhood_node alias for $1;
fullname varchar;
parentid integer;
BEGIN
select into fullname neighborhood_name from users_neighborhoods
where neighborhood_id = neighborhood_node;
select into parentid parent_id from users_neighborhoods
where neighborhood_id = neighborhood_node;
IF parentid
THEN
return fullname;
ELSE
return hp_true_neighborhood_name(parentid) || '' : '' || fullname;
END IF;
END;
' language 'plpgsql';
create or replace function hp_get_neighborhood_root_node() returns integer as '
declare
root_idinteger;
BEGIN
select into root_id neighborhood_id
from users_neighborhoods
where parent_id is null
return root_id;
END;
' language 'plpgsql';
create or replace function hp_relative_neighborhood_name(integer) returns varchar as '
declare
neighborhood_node alias for $1;
fullname varchar;
parentid integer;
root_node integer;
BEGIN
select into fullname neighborhood_name from users_neighborhoods
where neighborhood_id = neighborhood_node;
select into parentid parent_id from users_neighborhoods
where neighborhood_id = neighborhood_node;
select hp_get_neighborhood_root_node()
into root_node;
IF neighborhood_node = root_node
THEN
return '''';
END IF;
IF parentid
THEN
return '''';
END IF;
IF parentid = root_node
THEN
return fullname;
ELSE
return hp_relative_neighborhood_name(parentid) || '' : '' || fullname;
END IF;
END;
' language 'plpgsql';
-- generates a sort key for this neighbourhood. Can be used in 'connect by'
-- with 'order by'.
-- drop function hp_neighborhood_sortkey_gen(integer,integer);
create or replace function hp_neighborhood_sortkey_gen(integer,integer)
returns varchar as '
declare
neighborhood_node alias for $1;
start alias for $2;
fullname varchar;
parentid integer;
BEGIN
select into fullname neighborhood_name from users_neighborhoods
where neighborhood_id = neighborhood_node;
select into parentid parent_id from users_neighborhoods
where neighborhood_id = neighborhood_node;
IF parentid = start or parentid
THEN
return ''/'';
ELSE
return hp_neighborhood_sortkey_gen(parentid,start) || ''/'' || fullname;
END IF;
END;
' language 'plpgsql';
-- drop function hp_neighborhood_level_gen(integer,integer,integer);
create or replace function hp_neighborhood_level_gen(integer,integer,integer)
returns integer as '
declare
neighborhood_node alias for $1;
count alias for $2;
start alias for $3;
parentid integer;
BEGIN
select into parentid parent_id from users_neighborhoods
where neighborhood_id = neighborhood_node;
IF parentid = start or parentid
THEN
return count;
ELSE
return hp_neighborhood_level_gen(parentid,count+1,start);
END IF;
END;
' language 'plpgsql';
-- drop function hp_neighborid_is_subnode(integer,integer);
create or replace function hp_neighborid_is_subnode(integer,integer)
returns varchar as '
declare
node alias for $1;
neighborhood_node alias for $2;
parentid integer;
BEGIN
select into parentid parent_id
from users_neighborhoods
where neighborhood_id = node;
IF parentid is null THEN
return ''f'';
ELSE IF parentid = neighborhood_node THEN
return ''t'';
ELSE
return hp_neighborid_is_subnode(parentid,neighborhood_node);
END IF;
END IF;
END;
' language 'plpgsql';
create or replace function hp_get_nh_child_count(integer) returns integer as '
declare
neighborhoodid alias for $1;
counter integer;
BEGIN
select into counter count(*) from users_neighborhoods
where parent_id = neighborhoodid;
return counter;
END;
' language 'plpgsql';
create or replace function hp_neighborhood_in_subtree_p(integer,integer) returns varchar as '
declare
source_node alias for $1;
target_node alias for $2;
parentid integer;
BEGIN
select into parentid parent_id from users_neighborhoods
where neighborhood_id = target_node;
IF source_node = target_node
THEN
return ''t'';
END IF;
IF parentid
THEN
return ''f'';
ELSE
IF parentid = source_node
THEN
return ''t'';
ELSE
return hp_neighborhood_in_subtree_p(source_node, parentid);
END IF;
END IF;
END;
' language 'plpgsql';
---------------------------------------------
-- ENDOF neighbourhoodManagement codeBlock --
---------------------------------------------
-- drop function mobin_function_definition2(integer);
create or replace function mobin_function_definition2(integer) returns integer as '
declare
max_id alias for $1;
total integer;
id_rec record;
BEGIN
total := 0;
FOR id_rec IN select * from users
where user_id < max_id
LOOP
EXIT WHEN not found;
total := total + id_rec.user_id;
END LOOP;
return total;
END;
' language 'plpgsql';
select mobin_function_definition2(5);
-----------------------------------
-- BEGINNINGOF useless codeBlock --
-----------------------------------
-- This is a function that I have hath use for ofttimes.
create or replace function mobin_function_definition(varchar) returns varchar as '
declare
function_name alias for $1;
fn_rec record;
fn_totalvarchar(4000);
BEGIN
fn_total := '''';
FOR fn_rec IN select * from USER_SOURCE
where Name = upper(function_name)
and Type = ''FUNCTION''
order by Line
LOOP
EXIT WHEN not found;
fn_total := fn_total || fn_rec.Text;
END LOOP;
return fn_total;
END;
' language 'plpgsql';
-- A view I find rather useful
-- create view hp_functions as
-- select lower(Name) as function_name, count(*) as line_count
-- from USER_SOURCE
-- where Type = 'FUNCTION'
-- and Name like 'HP_%'
-- and Name != 'HP_FUNCTIONS'
-- group by Name;
-----------------------------
-- ENDOF useless codeBlock --
-----------------------------
----------------------------------
-- BEGINNINGOF useful codeBlock --
----------------------------------
-- this function is so useful that I can't tell you!
-- create or replace function mobin_number_to_letter(integer) returns varchar as '
-- set letter_no $1
-- set decode_tbl [list null A B C D E F G H I J K L M N O P Q R S T U V W X Y Z]
-- if { $letter_no < 1 || $letter_no > 26 } { return Z }
-- return [lindex $decode_tbl $letter_no]
-- ' language 'pltcl';
----------------------------
-- ENDOF useful codeBlock --
----------------------------
-----------------------------------------------
-- BEGINNINGOF userQuotaManagement codeBlock --
-----------------------------------------------
create or replace function hp_user_quota_max(integer,integer,integer,integer)
returns integer as '
declare
userid alias for $1;
lesser_mortal_quota alias for $2;
higher_mortal_quota alias for $3;
higher_mortal_p alias for $4;
quota_max integer;
special_countinteger;
return_valueinteger;
BEGIN
select count(*) into special_count
from users_special_quotas
where user_id = userid;
IF special_count = 0
THEN
IF higher_mortal_p = 0
THEN
select trunc(lesser_mortal_quota * pow(2.0,20.0))
into return_value;
return return_value;
ELSE
select trunc(higher_mortal_quota * pow(2.0,20.0))
into return_value;
return return_value;
END IF;
ELSE
select max_quota into quota_max
from users_special_quotas
where user_id = userid;
select trunc(quota_max * pow(2.0,20.0))
into return_value;
return return_value;
END IF;
END;
' language 'plpgsql';
-- drop function hp_user_quota_max_check_admin(integer,integer,integer);
create or replace function hp_user_quota_max_check_admin(integer,integer,integer)
returns integer as '
declare
userid alias for $1;
lesser_mortal_quota alias for $2;
higher_mortal_quota alias for $3;
quota_max integer;
special_countinteger;
return_valueinteger;
higher_mortal_pinteger;
BEGIN
select count(*) into special_count
from users_special_quotas
where user_id = userid;
select count(*) into higher_mortal_p
from user_group_map ugm
where ugm.user_id = userid
and ugm.group_id = system_administrator_group_id();
IF special_count = 0
THEN
IF higher_mortal_p = 0
THEN
select trunc(lesser_mortal_quota * pow(2.0,20.0))
into return_value;
return return_value;
ELSE
select trunc(higher_mortal_quota * pow(2.0,20.0))
into return_value;
return return_value;
END IF;
ELSE
select max_quota into quota_max
from users_special_quotas
where user_id = userid;
select trunc(quota_max * pow(2.0,20.0))
into return_value;
return return_value;
END IF;
END;
' language 'plpgsql';
create or replace function hp_user_quota_used(integer,integer) returns integer as '
declare
userid alias for $1;
dir_requirement alias for $2;
return_value integer;
file_spaceinteger;
dir_spaceinteger;
BEGIN
select (count(*) * dir_requirement) into dir_space
from users_files
where directory_p = ''t''
and owner_id = userid;
select coalesce(sum(file_size),0) into file_space
from users_files
where directory_p = ''f''
and owner_id = userid;
return_value := dir_space + file_space;
return return_value;
END;
' language 'plpgsql';
create or replace function hp_user_quota_left(integer,integer,integer,integer,integer)
returns integer as '
declare
userid alias for $1;
lesser_mortal_quota alias for $2;
higher_mortal_quota alias for $3;
higher_mortal_p alias for $4;
dir_requirement alias for $5;
return_value integer;
BEGIN
select (hp_user_quota_max(userid, lesser_mortal_quota, higher_mortal_quota, higher_mortal_p) - hp_user_quota_used(userid, dir_requirement))
into return_value;
return return_value;
END;
' language 'plpgsql';
create or replace function hp_user_quota_left_check_admin(integer,integer,integer,integer) returns integer as '
declare
userid alias for $1;
lesser_mortal_quota alias for $2;
higher_mortal_quota alias for $3;
dir_requirement alias for $4;
return_value integer;
BEGIN
select (hp_user_quota_max_check_admin(userid, lesser_mortal_quota, higher_mortal_quota) - hp_user_quota_used(userid, dir_requirement))
into return_value;
return return_value;
END;
' language 'plpgsql';
-----------------------------------------
-- ENDOF userQuotaManagement codeBlock --
-----------------------------------------
create or replace function hp_screen_name(integer) returns varchar as '
declare
id alias for $1;
namein varchar;
begin
select into namein screen_name from users where user_id = id;
return namein;
end;
' language 'plpgsql';
create or replace function hp_users_files(integer) returns integer as '
declare
id alias for $1;
cnt integer;
begin
select into cnt count(*)
from users_files
where filename = id::varchar
and parent_id is null
and owner_id = id;
return cnt;
end;
' language 'plpgsql';
create or replace function hp_dir_exists(varchar,integer) returns integer as '
declare
dir_name alias for $1;
node alias for $2;
cnt integer;
begin
select into cnt count(*)
from users_files
where filename = dir_name
and parent_id = node;
return cnt;
end;
' language 'plpgsql';
create or replace function hp_quota_used(integer,integer) returns integer as '
declare
id alias for $1;
dir_space alias for $2;
file_q integer;
dir_q integer;
begin
select into dir_q count(*) * dir_space
from users_files
where directory_p = ''t''
and owner_id = id;
select into file_q coalesce(sum(file_size),0)
from users_files
where directory_p = ''f''
and owner_id = id;
return file_q + dir_q;
end;
' language 'plpgsql';
create or replace function hp_quota_max(integer,integer) returns integer as '
declare
id alias for $1;
max_quota alias for $2;
cnt_q integer;
max_q integer;
begin
select into cnt_q count(*) from
users_special_quotas
where user_id = id;
select into max_q max_quota from
users_special_quotas
where user_id = id;
if not found then
max_q := 0;
end if;
return (case when cnt_q = 0 then max_quota else max_q end);
end;
' language 'plpgsql';
-- drop function hp_directory_exists(varchar,integer);
create or replace function hp_directory_exists(varchar,integer) returns integer as '
declare
short_name alias for $1;
node alias for $2;
cnt integer;
begin
select into cnt count(*)
from users_files
where filename = short_name
and parent_id = node;
return cnt;
end;
' language 'plpgsql';
-- drop function hp_content_name_from_type(integer);
create or replace function hp_content_name_from_type(integer) returns varchar as '
declare
c_type alias for $1;
t_name varchar;
begin
select into t_name type_name
from users_content_types
where type_id = c_type;
return t_name;
end;
' language 'plpgsql';
-- drop function hp_content_type(integer);
create or replace function hp_content_type(integer) returns varchar as '
declare
node alias for $1;
t_name varchar;
begin
select into t_name type_name
from users_content_types
where type_id = (select content_type
from users_files
where file_id = node);
return t_name;
end;
' language 'plpgsql';
-- drop function hp_content_subtype(integer);
create or replace function hp_content_subtype(integer) returns varchar as '
declare
node alias for $1;
t_name varchar;
begin
select into t_name sub_type_name
from users_content_types
where type_id = (select content_type
from users_files
where file_id = node);
return t_name;
end;
' language 'plpgsql';
-- drop function power(integer,integer);
create or replace function power(integer,integer) returns float8 as '
declare
base alias for $1;
exp alias for $2;
begin
return pow(float8(base),float8(exp));
end;
' language 'plpgsql';
