Index: openacs-4/packages/ratings/ratings.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ratings/ratings.info,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ratings/ratings.info 19 May 2004 19:00:02 -0000 1.1 @@ -0,0 +1,29 @@ + + + + + Ratings + Ratings + f + t + ratings + + + Jeff Davis + Multidimensional ratings on arbitrary acs_objects. + Xarg + Support for rating objects, with trigger maintained summary data. + GPL + http://www.gnu.org/copyleft/gpl.html + 0 + + + + + + + + + + + Index: openacs-4/packages/ratings/sql/postgresql/ratings-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ratings/sql/postgresql/ratings-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ratings/sql/postgresql/ratings-create.sql 19 May 2004 19:00:03 -0000 1.1 @@ -0,0 +1,18 @@ +-- Ratings +-- +-- Simple multidimensional rating package. +-- +-- Copyright (C) 2003 Jeff Davis +-- @author Jeff Davis +-- @creation-date 10/22/2003 +-- +-- @cvs-id $Id: ratings-create.sql,v 1.1 2004/05/19 19:00:03 jeffd Exp $ +-- +-- This is free software distributed under the terms of the GNU Public +-- License. Full text of the license is available from the GNU Project: +-- http://www.fsf.org/copyleft/gpl.html + +\i ratings-datamodel.sql +\i ratings-procs.sql +\i ratings-triggers.sql +\i ratings-data.sql Index: openacs-4/packages/ratings/sql/postgresql/ratings-data.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ratings/sql/postgresql/ratings-data.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ratings/sql/postgresql/ratings-data.sql 19 May 2004 19:00:03 -0000 1.1 @@ -0,0 +1,13 @@ +-- Simple multidimensional rating package -- backing data. +-- +-- Copyright (C) 2003 Jeff Davis +-- @author Jeff Davis +-- @creation-date 10/22/2003 +-- +-- @cvs-id $Id: ratings-data.sql,v 1.1 2004/05/19 19:00:03 jeffd Exp $ +-- +-- This is free software distributed under the terms of the GNU Public +-- License. Full text of the license is available from the GNU Project: +-- http://www.fsf.org/copyleft/gpl.html + +select rating_dimension__new ('Quality','quality','Overall rating of an item.',1,5,'','best'); Index: openacs-4/packages/ratings/sql/postgresql/ratings-datamodel.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ratings/sql/postgresql/ratings-datamodel.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ratings/sql/postgresql/ratings-datamodel.sql 19 May 2004 19:00:03 -0000 1.1 @@ -0,0 +1,121 @@ +-- Ratings +-- +-- Simple multidimensional rating package. +-- +-- Copyright (C) 2003 Jeff Davis +-- @author Jeff Davis +-- @creation-date 10/22/2003 +-- +-- @cvs-id $Id: ratings-datamodel.sql,v 1.1 2004/05/19 19:00:03 jeffd Exp $ +-- +-- This is free software distributed under the terms of the GNU Public +-- License. Full text of the license is available from the GNU Project: +-- http://www.fsf.org/copyleft/gpl.html + +create table rating_dimensions ( + dimension_id integer + constraint rating_dims_object_id_fk + references acs_objects(object_id) + constraint rating_dimensions_pk + primary key, + dimension_key varchar(100) + constraint rating_dims_dim_key_nn + not null, + description text, + range_low integer default 1, + range_high integer default 5, + label_low text default 'worst', + label_high text default 'best' +); + +comment on table rating_dimensions is ' + The definition for a rating dimension. +'; + + +create table ratings ( + rating_id integer + constraint ratings_rating_id_fk + references acs_objects(object_id) + constraint ratings_rating_id_pk + primary key, + dimension_id integer + constraint ratings_dimension_id_fk + references rating_dimensions(dimension_id) on delete cascade, + object_id integer + constraint ratings_object_id_fk + references acs_objects(object_id) on delete cascade + constraint ratings_object_id_nn + not null, + rating integer + constraint ratings_rating_nn + not null, + owner_id integer + constraint ratings_owner_id_fk + references parties(party_id) on delete cascade + constraint ratings_owner_id_nn + not null, + constraint ratings_un + unique (object_id, owner_id, dimension_id) +); + +comment on table ratings is ' + An object_id is related along dimension dimension_id by user user_id. Triggers maintain + rating_aggregates which hold the average rating along a dimension. +'; + +comment on column ratings.rating is ' + An integer rating object_id along dimension. Typically 1-5 although no constraint imposed + although maybe there should be a before insert trigger to verify the rating is between high and low + for dimension_id. +'; + + +create table rating_aggregates ( + dimension_id integer + constraint ratings_dimension_id_fk + references rating_dimensions(dimension_id) on delete cascade, + object_id integer + constraint ratings_object_id_fk + references acs_objects(object_id) on delete cascade + constraint ratings_object_id_nn + not null, + ratings integer, + rating_sum integer, + rating_ave float, + rated_on timestamptz, + constraint rating_aggregates_pk + primary key (object_id, dimension_id) +); + +comment on table rating_aggregates is ' + contains denormalized aggregates for the ratings table. trigger maintained by inserts and updates on ratings. +'; + + +select acs_object_type__create_type( + 'rating_dimension', + 'Rating Dimension', + 'Rating Dimensions', + 'acs_object', + 'rating_dimensions', + 'dimension_id', + 'rating_dimension', + 'f', + null, + 'rating_dimension__title' +); + +select acs_object_type__create_type( + 'rating', + 'Rating', + 'Ratings', + 'acs_object', + 'ratings', + 'rating_id', + 'rating', + 'f', + null, + 'rating__title' +); + Index: openacs-4/packages/ratings/sql/postgresql/ratings-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ratings/sql/postgresql/ratings-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ratings/sql/postgresql/ratings-drop.sql 19 May 2004 19:00:03 -0000 1.1 @@ -0,0 +1,53 @@ +-- drop the ratings stuff +-- +-- Copyright (C) 2003 Jeff Davis +-- @author Jeff Davis davis@xarg.net +-- @creation-date 10/22/2003 +-- +-- @cvs-id $Id: ratings-drop.sql,v 1.1 2004/05/19 19:00:03 jeffd Exp $ +-- +-- This is free software distributed under the terms of the GNU Public +-- License. Full text of the license is available from the GNU Project: +-- http://www.fsf.org/copyleft/gpl.html + + +create or replace function tmp_ratings_drop () +returns integer as ' +declare + coll_rec RECORD; +begin + for coll_rec in select object_id + from acs_objects + where object_type = ''rating_dimension'' + loop + PERFORM acs_object__delete (coll_rec.object_id); + end loop; + + + + for coll_rec in select object_id + from acs_objects + where object_type = ''rating'' + loop + PERFORM acs_object__delete (coll_rec.object_id); + end loop; + + return 1; +end; ' language 'plpgsql'; + +select tmp_ratings_drop (); +drop function tmp_ratings_drop (); + +select acs_object_type__drop_type('rating_dimension', 'f'); +select acs_object_type__drop_type('rating', 'f'); + +drop table rating_aggregates; +drop table ratings; +drop table rating_dimensions; + +select drop_package('rating_dimension'); +select drop_package('rating'); + +drop function ratings_upd_tr(); +drop function ratings_ins_tr(); +drop function ratings_del_tr(); Index: openacs-4/packages/ratings/sql/postgresql/ratings-procs.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ratings/sql/postgresql/ratings-procs.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ratings/sql/postgresql/ratings-procs.sql 19 May 2004 19:00:03 -0000 1.1 @@ -0,0 +1,217 @@ +-- Ratings +-- +-- PL/pgSQL procs for the simple multidimensional ratings package. +-- +-- Copyright (C) 2003 Jeff Davis +-- @author Jeff Davis +-- @creation-date 10/22/2003 +-- +-- @cvs-id $Id: ratings-procs.sql,v 1.1 2004/05/19 19:00:03 jeffd Exp $ +-- +-- This is free software distributed under the terms of the GNU Public +-- License. Full text of the license is available from the GNU Project: +-- http://www.fsf.org/copyleft/gpl.html + +create or replace function rating_dimension__new (integer,varchar,varchar,varchar,integer,integer,varchar,varchar,integer,timestamptz,integer,varchar,integer) +returns integer as ' +declare + p_dimension_id alias for $1; -- default null + p_title alias for $2; + p_dimension_key alias for $3; + p_description alias for $4; + p_range_low alias for $5; + p_range_high alias for $6; + p_label_low alias for $7; + p_label_high alias for $8; + + p_package_id alias for $9; + p_creation_date alias for $10; -- default now() + p_creation_user alias for $11; -- default null + p_creation_ip alias for $12; -- default null + p_context_id alias for $13; -- default null + + v_dimension_id rating_dimensions.dimension_id%TYPE; +begin + v_dimension_id := acs_object__new ( + p_dimension_id, + ''rating_dimension'', + p_creation_date, + p_creation_user, + p_creation_ip, + p_context_id, + ''t'', + p_title, + p_package_id); + + insert into rating_dimensions (dimension_id,dimension_key,description,range_low,range_high,label_low,label_high) + values (v_dimension_id,p_dimension_key,p_description,p_range_low,p_range_high,p_label_low,p_label_high); + + return v_dimension_id; + +end;' language 'plpgsql'; + +select define_function_args('rating_dimension__new','dimension_id,title,dimension_key,description,range_low,range_high,label_low,label_high,package_id,creation_date,creation_user,creation_ip,context_id'); + +-- simple api for initial populate. +create or replace function rating_dimension__new (varchar,varchar,varchar,integer,integer,varchar,varchar) +returns integer as ' +declare + p_title alias for $1; + p_dimension_key alias for $2; + p_description alias for $3; + p_range_low alias for $4; + p_range_high alias for $5; + p_label_low alias for $6; + p_label_high alias for $7; +begin + return rating_dimension__new(null,p_title,p_dimension_key,p_description,p_range_low,p_range_high,p_label_low,p_label_high,null,now(),null,null,null); +end;' language 'plpgsql'; + + +create or replace function rating_dimension__delete (integer) +returns integer as ' +declare + p_dimension_id alias for $1; +begin + if exists (select 1 from acs_objects where object_id = p_dimension_id and object_type = ''rating_dimension'') then + delete from acs_permissions + where object_id = p_dimension_id; + + PERFORM acs_object__delete(p_dimension_id); + + return 0; + else + raise NOTICE ''rating_dimension__delete object_id % does not exist or is not a rating_dimension'',p_dimension_id; + return 0; + end if; +end;' language 'plpgsql'; + + +select define_function_args('rating_dimension__delete','dimension_id'); + +create or replace function rating_dimension__title (integer) +returns varchar as ' +declare + p_dimension_id alias for $1; + v_title varchar; +begin + SELECT title into v_title + FROM acs_objects + WHERE object_id = p_dimension_id + and object_type = ''rating_dimension''; + + return v_title; +end; +' language 'plpgsql'; + +select define_function_args('rating_dimension__title','dimension_id'); + + +create or replace function rating__new (integer,integer,integer,integer,varchar,integer,timestamptz,integer,varchar,integer) +returns integer as ' +declare + p_rating_id alias for $1; + p_dimension_id alias for $2; + p_object_id alias for $3; + p_rating alias for $4; + p_title alias for $5; + p_package_id alias for $6; + p_creation_date alias for $7; + p_creation_user alias for $8; + p_creation_ip alias for $9; + p_context_id alias for $10; + v_rating_id ratings.rating_id%TYPE; +begin + v_rating_id := acs_object__new ( + p_rating_id, + ''rating'', + p_creation_date, + p_creation_user, + p_creation_ip, + p_context_id, + ''t'', + p_title, + p_package_id); + + INSERT INTO ratings (rating_id,dimension_id,object_id,rating,owner_id) + VALUES (v_rating_id,p_dimension_id,p_object_id,p_rating,p_creation_user); + + return v_rating_id; +end;' language 'plpgsql'; + +select define_function_args('rating__new','rating_id,dimension_id,object_id,rating,title,package_id,creation_date,creation_user,creation_ip,context_id'); + +create or replace function rating__delete (integer) +returns integer as ' +declare + p_rating_id alias for $1; +begin + if exists (select 1 from acs_objects where object_id = p_rating_id and object_type = ''rating'') then + delete from acs_permissions + where object_id = p_rating_id; + + -- we can just rely on the cascade in ratings, and the triggers for updating averages. + PERFORM acs_object__delete(p_rating_id); + + return 0; + else + raise NOTICE ''rating__delete object_id % does not exist or is not a rating'',p_rating_id; + return -1; + end if; +end;' language 'plpgsql'; + +select define_function_args('rating__delete','rating_id'); + +create or replace function rating__title (integer) +returns varchar as ' +declare + p_rating_id alias for $1; + v_title varchar; +begin + SELECT title into v_title + FROM acs_objects + WHERE object_id = p_rating_id + and object_type = ''rating''; + + return v_title; +end;' language 'plpgsql'; + +select define_function_args('rating__title','rating_id'); + +create or replace function rating__rate(integer,integer,integer,varchar,integer,timestamptz,integer,varchar,integer) +returns integer as ' +declare + p_dimension_id alias for $1; + p_object_id alias for $2; + p_rating alias for $3; + p_title alias for $4; + p_package_id alias for $5; + p_date alias for $6; + p_user alias for $7; + p_ip alias for $8; + p_context_id alias for $9; + v_rating_id ratings.rating_id%TYPE; +begin + SELECT rating_id into v_rating_id + FROM ratings + WHERE dimension_id = p_dimension_id + and object_id = p_object_id + and owner_id = p_user; + + if v_rating_id is null then + PERFORM rating__new(null, p_dimension_id, p_object_id, p_rating, p_title, p_package_id, p_date, p_user, p_ip, p_context_id); + else + UPDATE ratings + SET rating = p_rating + WHERE rating_id = v_rating_id; + + + UPDATE acs_objects + SET last_modified = coalesce(p_date,now()), modifying_user = p_user, modifying_ip = p_ip + WHERE object_id = v_rating_id; + end if; + + return v_rating_id; +end;' language 'plpgsql'; + +select define_function_args('rating__rate','dimension_id, object_id, rating, title, package_id, rated_on;now(), user_id, ip, context_id'); Index: openacs-4/packages/ratings/sql/postgresql/ratings-triggers.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ratings/sql/postgresql/ratings-triggers.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ratings/sql/postgresql/ratings-triggers.sql 19 May 2004 19:00:03 -0000 1.1 @@ -0,0 +1,78 @@ +-- Ratings +-- +-- Triggers for maintaining denormalized rating aggregates +-- +-- Copyright (C) 2003 Jeff Davis +-- @author Jeff Davis +-- @creation-date 1/12/2003 +-- +-- @cvs-id $Id: ratings-triggers.sql,v 1.1 2004/05/19 19:00:03 jeffd Exp $ +-- +-- This is free software distributed under the terms of the GNU Public +-- License. Full text of the license is available from the GNU Project: +-- http://www.fsf.org/copyleft/gpl.html + +create function ratings_ins_tr () returns opaque as ' +begin + if not exists ( + SELECT 1 + FROM rating_aggregates + WHERE dimension_id = new.dimension_id + and object_id = new.object_id ) then + + INSERT INTO rating_aggregates (dimension_id, object_id, ratings, rating_sum, rating_ave, rated_on) + VALUES (new.dimension_id, new.object_id, 1, new.rating, new.rating, now()); + + else + + UPDATE rating_aggregates + SET ratings = ratings + 1, rating_sum = rating_sum + new.rating, + rating_ave = (rating_sum + new.rating)/(ratings + 1), rated_on = now() + WHERE dimension_id = new.dimension_id + and object_id = new.object_id; + + end if; + + return new; +end;' language 'plpgsql'; + +create trigger ratings_ins_tr +after insert on ratings +for each row +execute procedure ratings_ins_tr(); + +create function ratings_upd_tr () returns opaque as ' +begin + UPDATE rating_aggregates + SET rating_sum = rating_sum - coalesce(old.rating,1) + coalesce(new.rating,1), + rating_ave = (rating_sum - coalesce(old.rating,1) + coalesce(new.rating,1))/ratings, + rated_on = now() + WHERE dimension_id = new.dimension_id + and object_id = new.object_id; + + return new; + +end;' language 'plpgsql'; + +create trigger ratings_upd_tr +after update on ratings +for each row +execute procedure ratings_upd_tr(); + +-- drop function ratings_del_tr() cascade; +create function ratings_del_tr () returns opaque as ' +begin + UPDATE rating_aggregates + SET ratings = (case when ratings > 0 then ratings - 1 else 0 end), + rating_sum = (case when rating_sum - coalesce(old.rating,1) > 0 then rating_sum - coalesce(old.rating,1) else 0 end), + rating_ave = (rating_sum - coalesce(old.rating,1))/(case when ratings > 1 then ratings - 1 else 1 end) + WHERE dimension_id = old.dimension_id + and object_id = old.object_id; + + return old; +end;' language 'plpgsql'; + +create trigger ratings_del_tr +after delete on ratings +for each row +execute procedure ratings_del_tr(); Index: openacs-4/packages/ratings/tcl/rating-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ratings/tcl/rating-procs.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ratings/tcl/rating-procs.tcl 19 May 2004 19:00:03 -0000 1.1 @@ -0,0 +1,175 @@ +# /packages/cop-base/tcl/ratings-procs.tcl +ad_library { + TCL library for multidimensional ratings + + @author Jeff Davis + + @creation-date 10/23/2003 + @cvs-id $Id: rating-procs.tcl,v 1.1 2004/05/19 19:00:03 jeffd Exp $ +} + +namespace eval ratings {} +namespace eval ratings::aggregates {} +namespace eval ratings::dimensions {} + +ad_proc -public ratings::aggregates::get { + {-dimension_key "quality"} + -object_id +} { + returns array set ratings info for ratings on the given + object_id along dimension dimension_key + + @param dimension_key + @param object_id + + @return array set info on ratings for object $object_id + + @author Jeff Davis davis@xarg.net + @creation-date 2004-01-30 +} { + array set dim [ratings::dimensions::get -dimension_key $dimension_key] + set dimension_id $dim(dimension_id) + + if { [db_0or1row get_ratings { + SELECT rating_ave, rating_sum, ratings + FROM rating_aggregates + WHERE dimension_id = :dimension_id + and object_id = :object_id } -column_array ret] } { + return [array get ret] + } else { + return {} + } +} + +ad_proc -public ratings::get { + {-dimension_key "quality"} + -object_id + -user_id +} { + Returns the rating given by the passed in user. + + @return the rating or empty string if unrated. + + @author Jeff Davis davis@xarg.net + @creation-date 2004-01-30 +} { + if {![empty_string_p $object_id]} { + return [db_string get_rating { + SELECT rating + FROM ratings + WHERE dimension_id = (select dimension_id from rating_dimensions where dimension_key = :dimension_key) + and object_id = :object_id + and owner_id = :user_id} -default {} ] + } else { + error "ratings::get: no object_id provided" + } +} + + +ad_proc -public ratings::dimensions::get { + -dimension_key +} { + Retrieve the dimension data for the given dimension_key + + @author Jeff Davis davis@xarg.net + @creation-date 2004-01 +} { + db_1row get {select * from rating_dimensions where dimension_key = :dimension_key} -column_array ret + + return [array get ret] +} + + + +ad_proc -public ratings::form { + {-dimension_key "quality"} + {-user_id {}} + {-return_url {}} + -object_id +} { + Create a rating widget; returns an html fragment with the form defined. + should be an include. + + @author Jeff Davis davis@xarg.net + @creation-date 2004-01-30 +} { + array set dim [ratings::dimensions::get -dimension_key $dimension_key] + + if {![info exists dim(dimension_key)]} { + error "ratings::widget: invalid dimension_key $dimension_key" + } + + if {![empty_string_p $object_id] + && [empty_string_p $user_id]} { + set user_id [ad_conn user_id] + } + + if {![empty_string_p $object_id]} { + set rating [ratings::get \ + -dimension_key $dimension_key \ + -object_id $object_id \ + -user_id $user_id ] + } else { + set rating {} + } + + if {[empty_string_p $return_url]} { + set return_url [ad_return_url] + } + + set out "
\n[export_vars -form {object_id dimension_key return_url}]\n
\n" + } else { + append out "\n\n\n" + } +} + + +ad_proc -public ratings::rate { + {-dimension_key "quality"} + -object_id + -user_id + -rating +} { + Sets the rating for object_id for user user_id. + + @author Jeff Davis davis@xarg.net + @creation-date 2004-01-30 +} { + array set dim [ratings::dimensions::get -dimension_key $dimension_key] + set dimension_id $dim(dimension_id) + + set vars [list \ + [list dimension_id $dimension_id] \ + [list object_id $object_id] \ + [list rating $rating] \ + [list title "Rating $dimension_key by $user_id on $object_id"] \ + [list package_id [ad_conn package_id]] \ + [list user_id $user_id] \ + [list ip [ad_conn peeraddr]] \ + ] + + package_exec_plsql -var_list $vars rating rate +} + +ad_proc -public ratings::icon_base {} { + return the base url for the rating icons +} { + return /resources/ratings/big/ +} Index: openacs-4/packages/ratings/www/rate.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ratings/www/rate.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ratings/www/rate.tcl 19 May 2004 19:00:03 -0000 1.1 @@ -0,0 +1,27 @@ +# /packages/ratings/www/rate +ad_page_contract { + Save a rating provided by a user. + + @author Jeff Davis davis@xarg.net + + @creation-date 10/30/2003 + @cvs-id $Id: rate.tcl,v 1.1 2004/05/19 19:00:03 jeffd Exp $ +} { + object_id:integer,notnull + dimension_key:notnull + rating:integer,notnull + {return_url:trim {}} +} + +set user_id [auth::require_login] + +ratings::rate -dimension_key $dimension_key \ + -object_id $object_id \ + -user_id $user_id \ + -rating $rating + +if {![empty_string_p $return_url]} { + ad_returnredirect -message "Your rating is now $rating for this item." $return_url +} else { + ad_returnredirect -message "Your rating is now $rating for this item" [get_referrer] +}