Forum OpenACS Q&A: Re: upgrading to PG 7.3 on old 3.x ACS
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';
