Index: openacs-4/packages/views/views.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/views/views.info,v diff -u -r1.1 -r1.2 --- openacs-4/packages/views/views.info 20 May 2004 13:01:30 -0000 1.1 +++ openacs-4/packages/views/views.info 23 Jul 2007 05:25:09 -0000 1.2 @@ -8,7 +8,7 @@ t views - + Jeff Davis Track views of objects. Xarg @@ -17,7 +17,7 @@ http://www.gnu.org/copyleft/gpl.html 0 - + Index: openacs-4/packages/views/sql/postgresql/views-datamodel.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/views/sql/postgresql/views-datamodel.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/views/sql/postgresql/views-datamodel.sql 20 May 2004 13:01:30 -0000 1.1 +++ openacs-4/packages/views/sql/postgresql/views-datamodel.sql 23 Jul 2007 05:25:09 -0000 1.2 @@ -53,11 +53,46 @@ trigger maintained by updates on views. '; +create table views_by_type ( + object_id integer + constraint views_by_type_object_id_fk + references acs_objects(object_id) on delete cascade + constraint views_by_type_object_id_nn + not null, + viewer_id integer + constraint views_by_type_owner_id_fk + references parties(party_id) on delete cascade + constraint views_by_type_viewer_id_nn + not null, + type varchar(100) not null, + views integer default 1, + last_viewed timestamptz default now(), + constraint views_by_type_pk + primary key (object_id, viewer_id, type) +); +create unique index views_by_type_viewer_idx on views_by_type(viewer_id, object_id, type); +comment on table views_by_type is ' + a simple count of how many times an object is viewed for each type. +'; +create table view_aggregates_by_type ( + object_id integer + constraint view_aggregates_by_type_object_id_fk + references acs_objects(object_id) on delete cascade + constraint view_aggregates_by_type_object_id_nn + not null, + type varchar(100) not null, + views integer default 1, + unique_views integer default 1, + last_viewed timestamptz default now(), + constraint view_aggregates_by_type_pk + primary key (object_id, type) +); +comment on table view_aggregates_by_type is ' + a simple count of how many times an object is viewed for each type, + multiple visits trigger maintained by updates on views_by_type. +'; - - - Index: openacs-4/packages/views/sql/postgresql/views-procs.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/views/sql/postgresql/views-procs.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/views/sql/postgresql/views-procs.sql 20 May 2004 13:01:30 -0000 1.1 +++ openacs-4/packages/views/sql/postgresql/views-procs.sql 23 Jul 2007 05:25:09 -0000 1.2 @@ -38,3 +38,30 @@ select define_function_args('views__record_view','object_id,viewer_id'); +create or replace function views_by_type__record_view (integer, integer, varchar) returns integer as ' +declare + p_object_id alias for $1; + p_viewer_id alias for $2; + p_type alias for $3; + v_views views.views%TYPE; +begin + select views into v_views from views_by_type where object_id = p_object_id and viewer_id = p_viewer_id and type = p_type; + + if v_views is null then + INSERT into views_by_type(object_id,viewer_id,type) + VALUES (p_object_id, p_viewer_id,p_type); + v_views := 0; + else + UPDATE views_by_type + SET views = views + 1, last_viewed = now(), type = p_type + WHERE object_id = p_object_id + and viewer_id = p_viewer_id + and type = p_type; + end if; + + return v_views + 1; +end;' language 'plpgsql'; + +comment on function views_by_type__record_view(integer, integer, varchar) is 'update the view by type count of object_id for viewer viewer_id, returns view count'; + +select define_function_args('views_by_type__record_view','object_id,viewer_id,type'); Index: openacs-4/packages/views/sql/postgresql/views-triggers.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/views/sql/postgresql/views-triggers.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/views/sql/postgresql/views-triggers.sql 20 May 2004 13:01:30 -0000 1.1 +++ openacs-4/packages/views/sql/postgresql/views-triggers.sql 23 Jul 2007 05:25:09 -0000 1.2 @@ -46,3 +46,39 @@ execute procedure views_upd_tr(); -- XXXJCD: Should there be a delete trigger? + +create function views_by_type_ins_tr () returns opaque as ' +begin + if not exists (select 1 from view_aggregates_by_type where object_id = new.object_id and type = new.type) then + INSERT INTO view_aggregates_by_type (object_id,type,views,unique_views,last_viewed) + VALUES (new.object_id,new.type,1,1,now()); + else + UPDATE view_aggregates_by_type + SET views = views + 1, unique_views = unique_views + 1, last_viewed = now() + WHERE object_id = new.object_id + AND type = new.type; + end if; + + return new; +end;' language 'plpgsql'; + +create trigger views_by_type_ins_tr +after insert on views_by_type +for each row +execute procedure views_by_type_ins_tr(); + +create function views_by_type_upd_tr () returns opaque as ' +begin + UPDATE view_aggregates_by_type + SET views = views + 1, last_viewed = now() + WHERE object_id = new.object_id + AND type = new.type; + + return new; +end;' language 'plpgsql'; + +create trigger views_by_type_upd_tr +after update on views_by_type +for each row +execute procedure views_by_type_upd_tr(); + Index: openacs-4/packages/views/sql/postgresql/upgrade/upgrade-0.1d-0.1d2.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/views/sql/postgresql/upgrade/upgrade-0.1d-0.1d2.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/views/sql/postgresql/upgrade/upgrade-0.1d-0.1d2.sql 23 Jul 2007 05:25:10 -0000 1.1 @@ -0,0 +1,109 @@ +-- upgrade-0.1d-0.1d2.sql + +create table views_by_type ( + object_id integer + constraint views_by_type_object_id_fk + references acs_objects(object_id) on delete cascade + constraint views_by_type_object_id_nn + not null, + viewer_id integer + constraint views_by_type_owner_id_fk + references parties(party_id) on delete cascade + constraint views_by_type_viewer_id_nn + not null, + type varchar(100) not null, + views integer default 1, + last_viewed timestamptz default now(), + constraint views_by_type_pk + primary key (object_id, viewer_id, type) +); + +create unique index views_by_type_viewer_idx on views_by_type(viewer_id, object_id, type); + +comment on table views_by_type is ' + a simple count of how many times an object is viewed for each type. +'; + +create table view_aggregates_by_type ( + object_id integer + constraint view_aggregates_by_type_object_id_fk + references acs_objects(object_id) on delete cascade + constraint view_aggregates_by_type_object_id_nn + not null, + type varchar(100) not null, + views integer default 1, + unique_views integer default 1, + last_viewed timestamptz default now(), + constraint view_aggregates_by_type_pk + primary key (object_id, type) +); + +comment on table view_aggregates_by_type is ' + a simple count of how many times an object is viewed for each type, + multiple visits trigger maintained by updates on views_by_type. +'; + + +create or replace function views_by_type__record_view (integer, integer, varchar) returns integer as ' +declare + p_object_id alias for $1; + p_viewer_id alias for $2; + p_type alias for $3; + v_views views.views%TYPE; +begin + select views into v_views from views_by_type where object_id = p_object_id and viewer_id = p_viewer_id and type = p_type; + + if v_views is null then + INSERT into views_by_type(object_id,viewer_id,type) + VALUES (p_object_id, p_viewer_id,p_type); + v_views := 0; + else + UPDATE views_by_type + SET views = views + 1, last_viewed = now(), type = p_type + WHERE object_id = p_object_id + and viewer_id = p_viewer_id + and type = p_type; + end if; + + return v_views + 1; +end;' language 'plpgsql'; + +comment on function views_by_type__record_view(integer, integer, varchar) is 'update the view by type count of object_id for viewer viewer_id, returns view count'; + +select define_function_args('views_by_type__record_view','object_id,viewer_id,type'); + +create function views_by_type_ins_tr () returns opaque as ' +begin + if not exists (select 1 from view_aggregates_by_type where object_id = new.object_id and type = new.type) then + INSERT INTO view_aggregates_by_type (object_id,type,views,unique_views,last_viewed) + VALUES (new.object_id,new.type,1,1,now()); + else + UPDATE view_aggregates_by_type + SET views = views + 1, unique_views = unique_views + 1, last_viewed = now() + WHERE object_id = new.object_id + AND type = new.type; + end if; + + return new; +end;' language 'plpgsql'; + +create trigger views_by_type_ins_tr +after insert on views_by_type +for each row +execute procedure views_by_type_ins_tr(); + +create function views_by_type_upd_tr () returns opaque as ' +begin + UPDATE view_aggregates_by_type + SET views = views + 1, last_viewed = now() + WHERE object_id = new.object_id + AND type = new.type; + + return new; +end;' language 'plpgsql'; + +create trigger views_by_type_upd_tr +after update on views_by_type +for each row +execute procedure views_by_type_upd_tr(); + Index: openacs-4/packages/views/tcl/views-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/views/tcl/views-procs.tcl,v diff -u -r1.4 -r1.5 --- openacs-4/packages/views/tcl/views-procs.tcl 4 May 2007 11:54:05 -0000 1.4 +++ openacs-4/packages/views/tcl/views-procs.tcl 23 Jul 2007 05:25:10 -0000 1.5 @@ -13,6 +13,7 @@ ad_proc -public views::record_view { -object_id -viewer_id + {-type ""} } { Record an object view by viewer_id @@ -24,6 +25,14 @@ @author Jeff Davis davis@xarg.net @creation-date 2004-01-30 } { + if { $type ne "" } { + if { [lsearch [list views unique_views last_viewed] $type] >= 0 } { + # if the type is on of the list it will conflict on the views::get procedure + error "views::record_view type cannot be views, unique_views or last_viewed" + } + set views_by_type [db_string record_view_by_type "select views_by_type__record_view(:object_id, :viewer_id, :type)" -default 1] + } + if {[catch {db_string record_view "select views__record_view(:object_id, :viewer_id)" -default 1} views]} { set views 0 } @@ -48,23 +57,42 @@ FROM view_aggregates WHERE object_id = :object_id } -column_array ret] } { + db_foreach select_views_by_type { + select type, views + from view_aggregates_by_type + where object_id = :object_id + } { + set ret($type) $views + } return [array get ret] } - return {views {} unique_views {} last_viewed {}} } + ad_proc -public views::viewed_p { -object_id {-user_id 0} + {-type ""} } { if {!$user_id} { set user_id [ad_conn user_id] } - return [db_string get_viewed_p { - select count(*) - from views - where object_id = :object_id - and viewer_id = :user_id - } -default 0] + if { $type ne "" } { + return [db_string get_viewed_by_type_p { + select count(*) + from views_by_type + where object_id = :object_id + and viewer_id = :user_id + and type = :type + } -default 0] + } else { + return [db_string get_viewed_p { + select count(*) + from views + where object_id = :object_id + and viewer_id = :user_id + } -default 0] + } + }