Index: openacs-4/packages/user-preferences/user-preferences.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/user-preferences/user-preferences.info,v diff -u -r1.6 -r1.7 --- openacs-4/packages/user-preferences/user-preferences.info 11 Dec 2003 21:40:14 -0000 1.6 +++ openacs-4/packages/user-preferences/user-preferences.info 25 Feb 2004 17:27:28 -0000 1.7 @@ -7,9 +7,9 @@ f t - + OpenACS - 2003-11-10 + 2004-01-01 A skeleton for how to implement user-preference storage OpenACS Index: openacs-4/packages/user-preferences/sql/postgresql/user-preferences-core-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/user-preferences/sql/postgresql/user-preferences-core-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/user-preferences/sql/postgresql/user-preferences-core-create.sql 25 Feb 2004 17:27:29 -0000 1.1 @@ -0,0 +1,75 @@ + +-- +-- The User Preferences package +-- +-- Ported to PostgreSQL by: Gabriel Burca +-- Based on the Oracle version by: ben@openforce +-- +-- distributed under the GPL v2 +-- +-- Jan. 1 2004 +-- + + +create table user_preference_types ( + preference_type_id integer not null + constraint user_pref_type_id_pk primary key + constraint user_pref_type_id_fk references acs_objects(object_id), + package_key varchar(100) + constraint user_pref_type_pack_key_fk + references apm_package_types(package_key), + short_name varchar(100) not null, + constraint user_pref_un unique (package_key, short_name), + pretty_name varchar(250) not null, + datatype varchar(20) default 'text' not null + constraint user_pref_datatype_ch + check (datatype in ('text','number','enum')), + -- denormalized options if the datatype is choice + -- we really don't need a table of these. They are separated by "|" + options varchar(2000), + -- preference-type wide default value + default_value varchar(200) +); + + +-- These are the default values for package-instance specific stuff +create table user_preference_default_values ( + preference_type_id integer not null + constraint user_pref_def_val_type_id_fk + references user_preference_types(preference_type_id), + package_id integer not null + constraint user_pref_def_val_pack_id_fk + references apm_packages(package_id), + constraint user_pref_def_val_pk + primary key (preference_type_id, package_id), + default_value varchar(200) not null +); + + +-- These are the user preferences +create table user_preference_values ( + preference_type_id integer not null + constraint user_pref_val_type_id_fk + references user_preference_types(preference_type_id), + user_id integer not null + constraint user_pref_val_user_id_fk + references users(user_id), + package_id integer + constraint user_pref_val_pack_id_fk + references apm_packages(package_id), + constraint user_pref_val_pk + primary key (preference_type_id, user_id, package_id), + value varchar(200) +); + + + +-- +-- Object Types +-- + +select acs_object_type__create_type('user_pref_type', + 'User Preference Type', 'User Preference Types', + 'acs_object', 'user_preference_types', 'preference_type_id', + 'user_pref_type', 'f', null, null); + Index: openacs-4/packages/user-preferences/sql/postgresql/user-preferences-core-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/user-preferences/sql/postgresql/user-preferences-core-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/user-preferences/sql/postgresql/user-preferences-core-drop.sql 25 Feb 2004 17:27:29 -0000 1.1 @@ -0,0 +1,26 @@ + +-- +-- The User Preferences package +-- +-- Ported to PostgreSQL by: Gabriel Burca +-- Based on the Oracle version by: ben@openforce +-- +-- distributed under the GPL v2 +-- +-- Jan. 1 2004 +-- + +-- the drop scripts + +drop table user_preference_default_values; + +drop table user_preference_values; + +drop table user_preference_types; + + +-- +-- Object Types +-- + +select acs_object_type__drop_type ('user_pref_type'); Index: openacs-4/packages/user-preferences/sql/postgresql/user-preferences-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/user-preferences/sql/postgresql/user-preferences-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/user-preferences/sql/postgresql/user-preferences-create.sql 25 Feb 2004 17:27:29 -0000 1.1 @@ -0,0 +1,14 @@ + +-- +-- The User Preferences package +-- +-- Ported to PostgreSQL by: Gabriel Burca +-- Based on the Oracle version by: ben@openforce +-- +-- distributed under the GPL v2 +-- +-- Jan. 1 2004 +-- + +\i user-preferences-core-create.sql +\i user-preferences-package-create.sql Index: openacs-4/packages/user-preferences/sql/postgresql/user-preferences-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/user-preferences/sql/postgresql/user-preferences-package-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/user-preferences/sql/postgresql/user-preferences-package-create.sql 25 Feb 2004 17:27:29 -0000 1.1 @@ -0,0 +1,187 @@ + +-- +-- The User Preferences package +-- +-- Ported to PostgreSQL by: Gabriel Burca +-- Based on the Oracle version by: ben@openforce +-- +-- distributed under the GPL v2 +-- +-- Jan. 1 2004 +-- + +-- package +select define_function_args('user_pref_type__new', 'preference_type_id,object_type;user_pref_type,package_key,short_name,pretty_name,datatype;text,options,default_value,creation_date;now(),creation_user,creation_ip,context_id'); + +create or replace function user_pref_type__new (integer, varchar, varchar, varchar, varchar, + varchar, varchar, varchar, timestamptz, integer, varchar, integer) +returns int4 as ' +declare + p_preference_type_id alias for $1; -- default null + p_object_type alias for $2; -- default ''user_pref_type'' + p_package_key alias for $3; + p_short_name alias for $4; + p_pretty_name alias for $5; + p_datatype alias for $6; -- default ''text'' + p_options alias for $7; -- default null + p_default_value alias for $8; -- default null + p_creation_date alias for $9; -- default sysdate + p_creation_user alias for $10; + p_creation_ip alias for $11; + p_context_id alias for $12; + + v_pref_type_id user_preference_types.preference_type_id%TYPE; +begin + v_pref_type_id := acs_object__new ( + p_preference_type_id, + p_object_type, + p_creation_date, + p_creation_user, + p_creation_ip, + p_context_id + ); + + insert into user_preference_types + (preference_type_id, package_key, short_name, pretty_name, datatype, options, default_value) + values + (v_pref_type_id, p_package_key, p_short_name, p_pretty_name, p_datatype, p_options, p_default_value); + + return v_pref_type_id; + +end;' language 'plpgsql'; + +select define_function_args('user_pref_type__get_user_pref', 'preference_type,package_id,user_id'); + +create or replace function user_pref_type__get_user_pref (varchar, integer, integer) +returns varchar as ' +declare + p_preference_type alias for $1; + p_package_id alias for $2; + p_user_id alias for $3; + + v_type_id user_preference_types.preference_type_id%TYPE; + v_pref user_preference_values.value%TYPE; + begin + select preference_type_id into v_type_id + from user_preference_types where short_name = p_preference_type; + + -- if there is no such preference type + if NOT FOUND then return NULL; end if; + + -- check direct user pref for package_id not null + select value into v_pref from user_preference_values + where preference_type_id = v_type_id + and package_id = p_package_id + and user_id = p_user_id; + + if FOUND then return v_pref; end if; + + -- check user pref with package_id NULL + select value into v_pref from user_preference_values + where preference_type_id = v_type_id + and package_id is NULL + and user_id = p_user_id; + + if FOUND then return v_pref; end if; + + -- if not found, check package default + select default_value into v_pref from user_preference_default_values + where preference_type_id = v_type_id + and package_id = p_package_id; + + if FOUND then return v_pref; end if; + + -- if not found check default value for preference type + select default_value into v_pref from user_preference_types + where preference_type_id = v_type_id; + + return v_pref; + +end;' language 'plpgsql'; + + +select define_function_args('user_pref_type__set_package_default', 'preference_type_id,package_id,default_value'); + +create or replace function user_pref_type__set_package_default (integer, integer, varchar) +returns int4 as ' +declare + p_preference_type_id alias for $1; + p_package_id alias for $2; + p_default_value alias for $3; + + v_count integer; +begin + select count(*) into v_count from user_preference_default_values + where preference_type_id = p_preference_type_id + and package_id = p_package_id; + + if v_count > 0 then + + update user_preference_default_values + set default_value= p_default_value + where preference_type_id= p_preference_type_id + and package_id= p_package_id; + else + + insert into user_preference_default_values + (preference_type_id, package_id, default_value) values + (p_preference_type_id, p_package_id, p_default_value); + end if; + + return 0; + +end;' language 'plpgsql'; + + +select define_function_args('user_pref_type__set_user_pref', 'preference_type,package_id,user_id,value'); + +create or replace function user_pref_type__set_user_pref (varchar, integer, integer, varchar) +returns int4 as ' +declare + p_preference_type alias for $1; + p_package_id alias for $2; + p_user_id alias for $3; + p_value alias for $4; + + v_type_id user_preference_types.preference_type_id%TYPE; + v_count integer; +begin + select preference_type_id into v_type_id + from user_preference_types where short_name = p_preference_type; + + -- if there is no such preference type + if NOT FOUND then return NULL; end if; + + select count(*) into v_count from user_preference_values + where preference_type_id= v_type_id + and package_id= p_package_id + and user_id= p_user_id; + + if v_count > 0 then + update user_preference_values + set value= p_value + where preference_type_id= v_type_id + and package_id= p_package_id + and user_id= p_user_id; + else + insert into user_preference_values + (preference_type_id, package_id, user_id, value) values + (v_type_id, p_package_id, p_user_id, p_value); + end if; + + return 0; + +end;' language 'plpgsql'; + + +select define_function_args('user_pref_type__del', 'preference_type_id'); + +create or replace function user_pref_type__del (integer) +returns int4 as ' +declare + p_preference_type_id alias for $1; +begin + acs_object__del(p_preference_type_id); + return 0; +end;' language 'plpgsql'; + Index: openacs-4/packages/user-preferences/tcl/preference-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/user-preferences/tcl/preference-procs-postgresql.xql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/user-preferences/tcl/preference-procs-postgresql.xql 24 May 2002 20:43:27 -0000 1.1 +++ openacs-4/packages/user-preferences/tcl/preference-procs-postgresql.xql 25 Feb 2004 17:27:29 -0000 1.2 @@ -12,10 +12,10 @@ - + -select user_pref_type.set_user_pref( - :preference_type_id, +select user_pref_type__set_user_pref( + :preference_type, :package_id, :user_id, :value @@ -26,7 +26,7 @@ select user_pref_type__get_user_pref ( - :preference_type_id, + :preference_type, :package_id, :user_id ); Index: openacs-4/packages/user-preferences/tcl/preference-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/user-preferences/tcl/preference-procs.tcl,v diff -u -r1.1 -r1.2 --- openacs-4/packages/user-preferences/tcl/preference-procs.tcl 24 May 2002 20:43:27 -0000 1.1 +++ openacs-4/packages/user-preferences/tcl/preference-procs.tcl 25 Feb 2004 17:27:29 -0000 1.2 @@ -13,7 +13,9 @@ ad_proc -public get_preference_type_id { {-preference_type:required} } { - get the ID from the short name + Get the preference ID given the short name + + @param preference_type Corresponds to the short_name parameter to preference::type::new } { # This will eventually be cached return [db_string select_preference_type_id {} -default {}] @@ -24,7 +26,9 @@ {-package_id:required} {-default_value:required} } { - sets the default value for a package ID + Sets the default preference value for a package ID + + @param preference_type Corresponds to the short_name parameter to preference::type::new } { # get the preference type id set preference_type_id [get_preference_type_id -preference_type $preference_type] @@ -39,7 +43,9 @@ {-user_id:required} {-value:required} } { - set a user pref + Set a user preference. + + @param preference_type Corresponds to the short_name parameter to preference::type::new } { # get the preference type id set preference_type_id [get_preference_type_id -preference_type $preference_type] @@ -53,7 +59,31 @@ {-package_id:required} {-user_id:required} } { - get a user pref + Get a user, package, or default preference. The preference can be + obtained at four different levels: +

+ + + + + + +
package_id user_idReturns
package_id user_idUser preference for package_id (set with set_user_pref)
null user_idUser preference for all packages (set with set_user_pref)
package_id nullPackage default (set with set_package_default)
null nullSystem default (set with preference::type::new)
+ + Note: null == empty string (""), ... + + @param preference_type A short string that identifies the preference. + This corresponds to the short_name parameter passed to + preference::type::new. + + @param package_id This can be the real package id, or an empty string. + + @param user_id This can be the real user id, or an empty string. + + @see preference::type::new + @see preference::set_package_default + @see preference::set_user_pref + } { # get the preference type id set preference_type_id [get_preference_type_id -preference_type $preference_type] Index: openacs-4/packages/user-preferences/tcl/preference-types-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/user-preferences/tcl/preference-types-procs.tcl,v diff -u -r1.1 -r1.2 --- openacs-4/packages/user-preferences/tcl/preference-types-procs.tcl 24 May 2002 20:43:27 -0000 1.1 +++ openacs-4/packages/user-preferences/tcl/preference-types-procs.tcl 25 Feb 2004 17:27:29 -0000 1.2 @@ -19,22 +19,32 @@ {-options ""} {-default_value ""} } { - create a new preference type + Create a new preference type. The preference can be set at four + different levels. See get_user_pref for more details. + + @param short_name This is the name that will be used to set or retrieve + the preference using set_user_pref or get_user_pref. + + @see preference::set_user_pref + @see preference::get_user_pref + @see preference::set_package_default } { # Set up the vars set extra_vars [ns_set create] oacs_util::vars_to_ns_set -ns_set $extra_vars -var_list {preference_type_id package_key short_name pretty_name datatype options default_value} # Instantiate the pref - set preference_type_id [package_instantiate_object -extra_vars $extra_vars user_preference_type] + set preference_type_id [package_instantiate_object -extra_vars $extra_vars user_pref_type] return $preference_type_id } ad_proc -public delete { {-preference_type:required} } { - this deletes a preference type + This deletes a preference type. This is not yet implemented. + + @param preference_type Corresponds to the short_name parameter to preference::type::new } { # FIXME: implement }