Index: openacs-4/packages/views/views.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/views/views.info,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/views/views.info 20 May 2004 13:01:30 -0000 1.1 @@ -0,0 +1,29 @@ + + + + + Views + Views + f + t + views + + + Jeff Davis + Track views of objects. + Xarg + Records views of objects, tracks summary count per object and can be used for displaying which objects are "new" to a given user. + GPL + http://www.gnu.org/copyleft/gpl.html + 0 + + + + + + + + + + + Index: openacs-4/packages/views/sql/postgresql/views-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/views/sql/postgresql/views-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/views/sql/postgresql/views-create.sql 20 May 2004 13:01:30 -0000 1.1 @@ -0,0 +1,21 @@ +-- Views +-- +-- Tracking and aggregating object views... +-- +-- Copyright (C) 2003 Jeff Davis +-- @author Jeff Davis +-- @creation-date 1/12/2003 +-- +-- @cvs-id $Id: views-create.sql,v 1.1 2004/05/20 13:01:30 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 views-datamodel.sql +\i views-procs.sql +\i views-triggers.sql + + + + 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 --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/views/sql/postgresql/views-datamodel.sql 20 May 2004 13:01:30 -0000 1.1 @@ -0,0 +1,63 @@ +-- Views +-- +-- Tracking and aggregating object views... +-- +-- Copyright (C) 2003 Jeff Davis +-- @author Jeff Davis +-- @creation-date 1/12/2003 +-- +-- @cvs-id $Id: views-datamodel.sql,v 1.1 2004/05/20 13:01:30 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 views ( + object_id integer + constraint views_object_id_fk + references acs_objects(object_id) on delete cascade + constraint views_object_id_nn + not null, + viewer_id integer + constraint views_owner_id_fk + references parties(party_id) on delete cascade + constraint views_viewer_id_nn + not null, + views integer default 1, + last_viewed timestamptz default now(), + constraint views_pk + primary key (object_id, viewer_id) +); + +create unique index views_viewer_idx on views(viewer_id, object_id); + +comment on table views is ' + a simple count of how many times an object is viewed. +'; + +create table view_aggregates ( + object_id integer + constraint view_aggs_object_id_fk + references acs_objects(object_id) on delete cascade + constraint view_aggs_object_id_nn + not null + constraint view_aggregatess_pk + primary key, + views integer default 1, + unique_views integer default 1, + last_viewed timestamptz default now() +); + +comment on table view_aggregates is ' + a simple count of how many times an object is viewed, multiple visits + trigger maintained by updates on views. +'; + + + + + + + + + Index: openacs-4/packages/views/sql/postgresql/views-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/views/sql/postgresql/views-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/views/sql/postgresql/views-drop.sql 20 May 2004 13:01:30 -0000 1.1 @@ -0,0 +1,18 @@ +-- drop the tracking and aggregating object views... +-- +-- Copyright (C) 2003 Jeff Davis +-- @author Jeff Davis davis@xarg.net +-- @creation-date 10/22/2003 +-- +-- @cvs-id $Id: views-drop.sql,v 1.1 2004/05/20 13:01:30 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 drop_package('views'); +drop table view_aggregates; +drop table views; +drop function views_upd_tr(); +drop function views_ins_tr(); + 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 --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/views/sql/postgresql/views-procs.sql 20 May 2004 13:01:30 -0000 1.1 @@ -0,0 +1,40 @@ +-- Views +-- +-- Tracking and aggregating object views -- procedures. +-- +-- Copyright (C) 2003 Jeff Davis +-- @author Jeff Davis +-- @creation-date 1/12/2003 +-- +-- @cvs-id $Id: views-procs.sql,v 1.1 2004/05/20 13:01:30 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 views__record_view (integer, integer) returns integer as ' +declare + p_object_id alias for $1; + p_viewer_id alias for $2; + v_views views.views%TYPE; +begin + select views into v_views from views where object_id = p_object_id and viewer_id = p_viewer_id; + + if v_views is null then + INSERT into views(object_id,viewer_id) + VALUES (p_object_id, p_viewer_id); + v_views := 0; + else + UPDATE views + SET views = views + 1, last_viewed = now() + WHERE object_id = p_object_id + and viewer_id = p_viewer_id; + end if; + + return v_views + 1; +end;' language 'plpgsql'; + +comment on function views__record_view(integer, integer) is 'update the view count of object_id for viewer viewer_id, returns view count'; + +select define_function_args('views__record_view','object_id,viewer_id'); + 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 --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/views/sql/postgresql/views-triggers.sql 20 May 2004 13:01:30 -0000 1.1 @@ -0,0 +1,48 @@ +-- Views +-- +-- Tracking and aggregating object views - triggers for denormalization +-- +-- Copyright (C) 2003 Jeff Davis +-- @author Jeff Davis +-- @creation-date 1/12/2003 +-- +-- @cvs-id $Id: views-triggers.sql,v 1.1 2004/05/20 13:01:30 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 views_ins_tr () returns opaque as ' +begin + if not exists (select 1 from view_aggregates where object_id = new.object_id) then + INSERT INTO view_aggregates (object_id,views,unique_views,last_viewed) + VALUES (new.object_id,1,1,now()); + else + UPDATE view_aggregates + SET views = views + 1, unique_views = unique_views + 1, last_viewed = now() + WHERE object_id = new.object_id; + end if; + + return new; +end;' language 'plpgsql'; + +create trigger views_ins_tr +after insert on views +for each row +execute procedure views_ins_tr(); + +create function views_upd_tr () returns opaque as ' +begin + UPDATE view_aggregates + SET views = views + 1, last_viewed = now() + WHERE object_id = new.object_id; + + return new; +end;' language 'plpgsql'; + +create trigger views_upd_tr +after update on views +for each row +execute procedure views_upd_tr(); + +-- XXXJCD: Should there be a delete trigger? 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 --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/views/tcl/views-procs.tcl 20 May 2004 13:01:30 -0000 1.1 @@ -0,0 +1,44 @@ +# /packages/cop-base/tcl/ratings-procs.tcl +ad_library { + TCL library for recoding views + + @author Jeff Davis + + @creation-date 2004-05-20 + @cvs-id $Id: views-procs.tcl,v 1.1 2004/05/20 13:01:30 jeffd Exp $ +} + +namespace eval views {} + +ad_proc -public views::record_view { + -object_id + -viewer_id +} { + Record an object view by viewer_id + + @param dimension_key + @param object_id + + @return the total view count for the user + + @author Jeff Davis davis@xarg.net + @creation-date 2004-01-30 +} { + set views [db_string record_view "select views__record_view(:object_id, :viewer_id)" -default 1] + + return $views +} + +ad_proc -public views::get { + -object_id +} { + if {[db_0or1row views { + SELECT views, unique_views, to_char(last_viewed,'YYYY-MM-DD HH24:MI:SS') as last_viewed + FROM view_aggregates + WHERE object_id = :object_id + } -column_array ret] } { + return [array get ret] + } + + return {views {} unique_views {} last_viewed {}} +}