-- -- homepage.sql -- -- created by mobin@mit.edu at Mon Jan 10 21:52:32 EST 2000 4221'N 7104'W -- Usman Y. Mobin -- -- supports the Homepage system for giving users the ability to publish -- personal web content within a larger community. -- the public content actually appears in /users/ -- should some of the maintenance pages appear at /homepage/ ? -- the site-wide admin pages are at /admin/homepage/ -- no group admin pages -- be explicit about -- 1) does more than one user ever get to maintain a particular set of -- content? e.g., can they collaborate with another user? Not really. -- One user takes responsibility for all of this and maintains it him -- or herself. However, a user can authorize one or more other users -- to be helpers. These people can be "HTML programmers" that are -- friends of the user but their email address will never appear as -- signatory. This is different from group-maintained content where a -- group of users is authorized to maintain but the group signs and -- takes collective responsiblity. -- -- 2) what if a site gets really large and the primary purpose is giving -- members personal homepages (e.g., if an adopter of ACS decides to -- become "The GeoCities of Brazil")? How do we support this? First, -- users could decide to join user groups. Then the /users/ index -- page would show a summary of user groups whose members have -- personal pages. This requires no new data in Oracle. This is -- enabled with SubdivisionByGroupP=1 in the .ini file. Users with -- homepages and no group affiliation show up in "unaffiliated" (fun -- with OUTER JOIN). When SubdivisionByNeighborhoodP=1, we either -- keep a denormalized neighborhood_sortkey in the homepages table -- and flag the "homepages" that are actually neighborhood folders or -- have some separate tables holding categorization. -- create sequence users_neighborhood_id_seq_t start 2; create view users_neighborhood_id_seq as select nextval('users_neighborhood_id_seq_t') as nextval; create table users_neighborhoods ( neighborhood_id integer primary key, neighborhood_name varchar(500) not null, description varchar(4000), parent_id integer references users_neighborhoods on delete cascade ); -- the system is smart enough to adjust if the root neighborhood -- has a different neighborhood_id. insert into users_neighborhoods (neighborhood_id, neighborhood_name, description, parent_id) values (1, 'Neighborhoods', 'Neighborhood RootNode', null); create table users_homepages ( user_id integer, -- the background colour settings for user's public pages bgcolor varchar(40), -- the text colour settings for user's public pages textcolor varchar(40), -- the colour settings for unvisitied links in user's public pages unvisited_link varchar(40), -- the colour settings for visitied links in user's public pages visited_link varchar(40), -- the settings to determine whether the links are underlined or -- not in user's public pages link_text_decoration varchar(40), -- the settings to determine whether the links are bold or -- not in user's public pages. I have added this because I have -- strong preference for bold links when they are not underlined. link_font_weight varchar(40), -- font for user's public generated pages font_type varchar(40), -- the background colour settings for user's maintenance pages maint_bgcolor varchar(40), maint_textcolor varchar(40), maint_unvisited_link varchar(40), maint_visited_link varchar(40), maint_link_text_decoration varchar(40), maint_link_font_weight varchar(40), maint_font_type varchar(40), neighborhood_id integer -- feature_level varchar(30), -- constraint hp_feature_lvl_ck check(feature_level 'platinum', 'gold', 'silver') -- keywords varchar(4000) ); -- users have their quotas specified by [ad_parameter PrivelegedUserMaxQuota -- users] or [ad_parameter NormalUserMaxQuota users] depending on whether -- they are site wide administrators or not. However, some users might have -- special quotas which can be granted by site wide administrators. These -- quotas are recorded in the users_special_quotas table. If a user has an -- entry in this table then the above mentioned parameter values are ignored -- and instead max_quota is used as his/her quota space. create table users_special_quotas ( user_id integer primary key references users, max_quota float8 not null, modification_date datetime default sysdate() not null ); create sequence users_type_id_seq_t start 2; create view users_type_id_seq as select nextval('users_type_id_seq_t') as nextval; create table users_content_types ( type_id integer primary key, type_name varchar(200) not null, sub_type_name varchar(200) not null, owner_id integer references users, sub_type integer references users_content_types, super_type integer references users_content_types ); -- We use this sequence to assign values to file_id. The -- reason for starting from 2 is that no file is special -- enough to have file_id=1, or is there a file that is? create sequence users_file_id_seq_t start 2; create view users_file_id_seq as select nextval('users_file_id_seq_t') as nextval; create table users_files ( file_id integer primary key, -- the maximum filesize in unix is 255 characters (starting from 1) filename varchar(255) not null, directory_p char(1) default 'f', constraint users_dir_ck check(directory_p in ('t','f')), file_pretty_name varchar(500) not null, -- this is the number of bytes the files takes up on the file -- system. We will use these values to determine quota usage -- except where directory_p is true. In that case, we'll use -- [ad_parameter DirectorySpaceRequirement users] to see the -- amount of quota space consumed by a directory. Thus, if we -- magically manage to change the file system, we dont have -- to update file_size for directories here because it is -- irrelevent. managed_p char(1) default 'f' check(managed_p in ('t','f')), -- this column is used for files created by the content -- publishing system which the user cannot rename or move modifyable_p char(1) default 't' check(modifyable_p in ('t','f')), file_size float8 not null, content_type integer references users_content_types, -- points to the user_id of the user who owns this file. owner_id integer not null references users, -- points to the file_id of the directory which contains -- this file. Useful for supporting hierarchical content -- structure. parent_id integer references users_files ); create index users_files_idx1 on users_files(file_id, parent_id); create index users_files_idx2 on users_files(parent_id, file_id); create index users_files_idx3 on users_files(owner_id); create sequence users_access_id_sequence_t start 2; create view users_access_id_sequence as select nextval('users_access_id_sequence_t') as nextval; create table users_files_access_log ( access_id integer primary key, file_id integer references users_files on delete set null, relative_filename varchar(500) not null, owner_id integer references users on delete set null, access_date datetime not null, ip_address varchar(50) not null ); ------------------------------------------------ -- BEGINNINGOF fileSystemManagement codeBlock -- ------------------------------------------------ -- returned value is a filename that does not begin with a slash 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'; -- drop function hp_true_dirname(integer); create function hp_true_dirname(integer) returns varchar as ' declare filesystem_node alias for $1; fullname varchar; parentid integer; 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 is null or 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 function hp_filesystem_node_sortkey_gen(integer,integer) returns varchar as ' declare filesystem_node alias for $1; start alias for $2; fullname varchar; parentid integer; dir_p varchar(1); plsql_val record; discriminator varchar(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 is null 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 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; parentid integer; BEGIN select into parentid parent_id from users_files where file_id = filesystem_node; IF parentid = start or parentid is null 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 function hp_id_is_subnode(integer,integer) returns varchar as ' declare node alias for $1; filesystem_node alias for $2; parentid integer; 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 function hp_user_relative_filename(integer) returns varchar as ' declare filesystem_node alias for $1; fullname varchar; parentid integer; 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 is null or 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 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 or parent_id = ''''::int4 and owner_id = u_id; return root_id; END; ' language 'plpgsql'; select hp_get_filesystem_root_node(1); create function hp_get_filesystem_node_owner(integer) returns integer as ' declare fsid alias for $1; owner_id integer; BEGIN select into owner_id owner_id from users_files where file_id = fsid; return owner_id; END; ' language 'plpgsql'; create 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 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 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 function hp_top_level_content_title(integer) returns varchar as ' declare filesystem_node alias for $1; managedp varchar(1); fullname varchar; parentid integer; parent_managedp varchar(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 is null or 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 function hp_top_level_content_node(integer) returns varchar as ' declare filesystem_node alias for $1; managedp varchar(1); parentid integer; parent_managedp varchar(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 is null or 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 function hp_onelevelup_content_title(integer) returns varchar as ' declare filesystem_node alias for $1; managedp varchar(1); dirp varchar(1); parentid integer; fullname varchar; 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 is null or 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 function hp_onelevelup_content_node(integer) returns varchar as ' declare filesystem_node alias for $1; managedp varchar(1); dirp varchar(1); parentid integer; 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 is null or 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 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 is null or parentid = '''' THEN return fullname; ELSE return hp_true_neighborhood_name(parentid) || '' : '' || fullname; END IF; END; ' language 'plpgsql'; create function hp_get_neighborhood_root_node() returns integer as ' declare root_id integer; BEGIN select into root_id neighborhood_id from users_neighborhoods where parent_id is null or parent_id = ''''; return root_id; END; ' language 'plpgsql'; create 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 is null or 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 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 is null 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 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 is null 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 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 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 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 is null or 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 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 function mobin_function_definition(varchar) returns varchar as ' declare function_name alias for $1; fn_rec record; fn_total varchar(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 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 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_count integer; return_value integer; 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 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_count integer; return_value integer; higher_mortal_p integer; 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 function hp_user_quota_used(integer,integer) returns integer as ' declare userid alias for $1; dir_requirement alias for $2; return_value integer; file_space integer; dir_space integer; 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 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 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 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 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 or parent_id = '''' and owner_id = id; return cnt; end; ' language 'plpgsql'; create 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 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 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 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 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 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 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 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';