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 -r1.1 -r1.2 --- openacs-4/packages/ratings/sql/postgresql/ratings-datamodel.sql 19 May 2004 19:00:03 -0000 1.1 +++ openacs-4/packages/ratings/sql/postgresql/ratings-datamodel.sql 21 May 2004 22:53:02 -0000 1.2 @@ -54,9 +54,7 @@ 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) + not null ); comment on table ratings is ' @@ -80,9 +78,15 @@ references acs_objects(object_id) on delete cascade constraint ratings_object_id_nn not null, - ratings integer, - rating_sum integer, - rating_ave float, + all_ratings integer, + all_rating_sum integer, + all_rating_ave float, + anon_ratings integer, + anon_rating_sum integer, + anon_rating_ave float, + reg_ratings integer, + reg_rating_sum integer, + reg_rating_ave float, rated_on timestamptz, constraint rating_aggregates_pk primary key (object_id, dimension_id) 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 -r1.2 -r1.3 --- openacs-4/packages/ratings/sql/postgresql/ratings-procs.sql 21 May 2004 07:48:04 -0000 1.2 +++ openacs-4/packages/ratings/sql/postgresql/ratings-procs.sql 21 May 2004 22:53:02 -0000 1.3 @@ -39,9 +39,7 @@ p_creation_user, p_creation_ip, p_context_id, - ''t'', - p_title, - p_package_id); + ''t''); 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); @@ -129,9 +127,7 @@ p_creation_user, p_creation_ip, p_context_id, - ''t'', - p_title, - p_package_id); + ''t''); 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); @@ -192,11 +188,15 @@ 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 p_user = 0 then + v_rating_id := null; + else + 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; + end if; if v_rating_id is null then v_rating_id := 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); @@ -205,7 +205,6 @@ 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; @@ -214,4 +213,4 @@ 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'); +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 -r1.1 -r1.2 --- openacs-4/packages/ratings/sql/postgresql/ratings-triggers.sql 19 May 2004 19:00:03 -0000 1.1 +++ openacs-4/packages/ratings/sql/postgresql/ratings-triggers.sql 21 May 2004 22:53:02 -0000 1.2 @@ -20,17 +20,39 @@ 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()); + if new.owner_id = 0 then + INSERT INTO rating_aggregates (dimension_id, object_id, all_ratings, all_rating_sum, all_rating_ave, + anon_ratings, anon_rating_sum, anon_rating_ave, reg_ratings, reg_rating_sum, reg_rating_ave, rated_on) + VALUES (new.dimension_id, new.object_id, 1, new.rating, new.rating, + 0, 0, 0, 1, new.rating, new.rating, now()); + else + INSERT INTO rating_aggregates (dimension_id, object_id, all_ratings, all_rating_sum, all_rating_ave, + anon_ratings, anon_rating_sum, anon_rating_ave, reg_ratings, reg_rating_sum, reg_rating_ave, rated_on) + VALUES (new.dimension_id, new.object_id, 1, new.rating, new.rating, + 1, new.rating, new.rating, 0, 0, 0, now()); + end if; 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; - + if new.owner_id = 0 then + UPDATE rating_aggregates + SET all_ratings = all_ratings + 1, all_rating_sum = all_rating_sum + new.rating, + all_rating_ave = 1.0*(all_rating_sum + new.rating)/(all_ratings + 1), + anon_ratings = anon_ratings + 1, anon_rating_sum = anon_rating_sum + new.rating, + anon_rating_ave = 1.0*(anon_rating_sum + new.rating)/(anon_ratings + 1), + rated_on = now() + WHERE dimension_id = new.dimension_id + and object_id = new.object_id; + else + UPDATE rating_aggregates + SET all_ratings = all_ratings + 1, all_rating_sum = all_rating_sum + new.rating, + all_rating_ave = 1.0*(all_rating_sum + new.rating)/(all_ratings + 1), + reg_ratings = reg_ratings + 1, reg_rating_sum = reg_rating_sum + new.rating, + reg_rating_ave = 1.0*(reg_rating_sum + new.rating)/(reg_ratings + 1), + rated_on = now() + WHERE dimension_id = new.dimension_id + and object_id = new.object_id; + end if; end if; return new; @@ -43,13 +65,53 @@ 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; + -- We first subtract the old, then add the new, in case owner_id, dimension_id or object_id was changed. + if old.owner_id = 0 then + UPDATE rating_aggregates + SET all_ratings = (case when all_ratings > 0 then all_ratings - 1 else 0 end), + all_rating_sum = (case when all_rating_sum - coalesce(old.rating,1) > 0 then all_rating_sum - coalesce(old.rating,1) else 0 end), + all_rating_ave = 1.0*(all_rating_sum - coalesce(old.rating,1))/(case when all_ratings > 1 then all_ratings - 1 else 1 end), + anon_ratings = (case when anon_ratings > 0 then anon_ratings - 1 else 0 end), + anon_rating_sum = (case when anon_rating_sum - coalesce(old.rating,1) > 0 then anon_rating_sum - coalesce(old.rating,1) else 0 end), + anon_rating_ave = 1.0*(anon_rating_sum - coalesce(old.rating,1))/(case when anon_ratings > 1 then anon_ratings - 1 else 1 end) + WHERE dimension_id = old.dimension_id + and object_id = old.object_id; + else + UPDATE rating_aggregates + SET all_ratings = (case when all_ratings > 0 then all_ratings - 1 else 0 end), + all_rating_sum = (case when all_rating_sum - coalesce(old.rating,1) > 0 then all_rating_sum - coalesce(old.rating,1) else 0 end), + all_rating_ave = 1.0*(all_rating_sum - coalesce(old.rating,1))/(case when all_ratings > 1 then all_ratings - 1 else 1 end), + reg_ratings = (case when reg_ratings > 0 then reg_ratings - 1 else 0 end), + reg_rating_sum = (case when reg_rating_sum - coalesce(old.rating,1) > 0 then reg_rating_sum - coalesce(old.rating,1) else 0 end), + reg_rating_ave = 1.0*(reg_rating_sum - coalesce(old.rating,1))/(case when reg_ratings > 1 then reg_ratings - 1 else 1 end) + WHERE dimension_id = old.dimension_id + and object_id = old.object_id; + end if; + + if new.owner_id = 0 then + UPDATE rating_aggregates + SET all_ratings = all_ratings + 1, + all_rating_sum = all_rating_sum + coalesce(new.rating,1), + all_rating_ave = 1.0*(all_rating_sum + coalesce(new.rating,1))/(all_ratings + 1), + anon_ratings = anon_ratings + 1, + anon_rating_sum = anon_rating_sum + coalesce(new.rating,1), + anon_rating_ave = 1.0*(anon_rating_sum + coalesce(new.rating,1))/(anon_ratings + 1), + rated_on = now() + WHERE dimension_id = new.dimension_id + and object_id = new.object_id; + else + UPDATE rating_aggregates + SET all_ratings = all_ratings + 1, + all_rating_sum = all_rating_sum + coalesce(new.rating,1), + all_rating_ave = 1.0*(all_rating_sum + coalesce(new.rating,1))/(all_ratings + 1), + reg_ratings = reg_ratings + 1, + reg_rating_sum = reg_rating_sum + coalesce(new.rating,1), + reg_rating_ave = 1.0*(reg_rating_sum + coalesce(new.rating,1))/(reg_ratings + 1), + rated_on = now() + WHERE dimension_id = new.dimension_id + and object_id = new.object_id; + end if; return new; end;' language 'plpgsql'; @@ -62,12 +124,27 @@ -- 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; + if old.owner_id = 0 then + UPDATE rating_aggregates + SET all_ratings = (case when all_ratings > 0 then all_ratings - 1 else 0 end), + all_rating_sum = (case when all_rating_sum - coalesce(old.rating,1) > 0 then all_rating_sum - coalesce(old.rating,1) else 0 end), + all_rating_ave = 1.0*(all_rating_sum - coalesce(old.rating,1))/(case when all_ratings > 1 then all_ratings - 1 else 1 end), + anon_ratings = (case when anon_ratings > 0 then anon_ratings - 1 else 0 end), + anon_rating_sum = (case when anon_rating_sum - coalesce(old.rating,1) > 0 then anon_rating_sum - coalesce(old.rating,1) else 0 end), + anon_rating_ave = 1.0*(anon_rating_sum - coalesce(old.rating,1))/(case when anon_ratings > 1 then anon_ratings - 1 else 1 end) + WHERE dimension_id = old.dimension_id + and object_id = old.object_id; + else + UPDATE rating_aggregates + SET all_ratings = (case when all_ratings > 0 then all_ratings - 1 else 0 end), + all_rating_sum = (case when all_rating_sum - coalesce(old.rating,1) > 0 then all_rating_sum - coalesce(old.rating,1) else 0 end), + all_rating_ave = 1.0*(all_rating_sum - coalesce(old.rating,1))/(case when all_ratings > 1 then all_ratings - 1 else 1 end), + reg_ratings = (case when reg_ratings > 0 then reg_ratings - 1 else 0 end), + reg_rating_sum = (case when reg_rating_sum - coalesce(old.rating,1) > 0 then reg_rating_sum - coalesce(old.rating,1) else 0 end), + reg_rating_ave = 1.0*(reg_rating_sum - coalesce(old.rating,1))/(case when reg_ratings > 1 then reg_ratings - 1 else 1 end) + WHERE dimension_id = old.dimension_id + and object_id = old.object_id; + end if; return old; end;' language 'plpgsql';