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.2 -r1.3 --- openacs-4/packages/views/sql/postgresql/views-datamodel.sql 23 Jul 2007 05:25:09 -0000 1.2 +++ openacs-4/packages/views/sql/postgresql/views-datamodel.sql 1 Aug 2007 08:59:56 -0000 1.3 @@ -12,7 +12,7 @@ -- License. Full text of the license is available from the GNU Project: -- http://www.fsf.org/copyleft/gpl.html -create table views ( +create table views_views ( object_id integer constraint views_object_id_fk references acs_objects(object_id) on delete cascade @@ -23,15 +23,15 @@ references parties(party_id) on delete cascade constraint views_viewer_id_nn not null, - views integer default 1, + views_count integer default 1, last_viewed timestamptz default now(), - constraint views_pk + constraint views_views_pk primary key (object_id, viewer_id) ); -create unique index views_viewer_idx on views(viewer_id, object_id); +create unique index views_views_viewer_idx on views_views(viewer_id, object_id); -comment on table views is ' +comment on table views_views is ' a simple count of how many times an object is viewed. '; @@ -43,7 +43,7 @@ not null constraint view_aggregatess_pk primary key, - views integer default 1, + views_count integer default 1, unique_views integer default 1, last_viewed timestamptz default now() ); @@ -64,31 +64,31 @@ 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, + view_type varchar(100) not null, + views_count integer default 1, last_viewed timestamptz default now(), constraint views_by_type_pk - primary key (object_id, viewer_id, type) + primary key (object_id, viewer_id, view_type) ); -create unique index views_by_type_viewer_idx on views_by_type(viewer_id, object_id, type); +create unique index views_by_type_viewer_idx on views_by_type(viewer_id, object_id, view_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 + constraint view_agg_b_type_ob_id_fk references acs_objects(object_id) on delete cascade - constraint view_aggregates_by_type_object_id_nn + constraint view_agg_b_type_ob_id_nn not null, - type varchar(100) not null, - views integer default 1, + view_type varchar(100) not null, + views_count integer default 1, unique_views integer default 1, last_viewed timestamptz default now(), constraint view_aggregates_by_type_pk - primary key (object_id, type) + primary key (object_id, view_type) ); comment on table view_aggregates_by_type is ' 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 -r1.1 -r1.2 --- openacs-4/packages/views/sql/postgresql/views-drop.sql 20 May 2004 13:01:30 -0000 1.1 +++ openacs-4/packages/views/sql/postgresql/views-drop.sql 1 Aug 2007 08:59:56 -0000 1.2 @@ -11,8 +11,11 @@ -- http://www.fsf.org/copyleft/gpl.html select drop_package('views'); +drop table view_aggregates_by_type; +drop table views_by_type; drop table view_aggregates; -drop table views; -drop function views_upd_tr(); -drop function views_ins_tr(); - +drop table views_views; +drop function views_views_upd_tr() cascade; +drop function views_views_ins_tr() cascade; +drop function views_by_type_ins_tr() cascade; +drop function views_by_type_upd_tr() cascade; 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.2 -r1.3 --- openacs-4/packages/views/sql/postgresql/views-procs.sql 23 Jul 2007 05:25:09 -0000 1.2 +++ openacs-4/packages/views/sql/postgresql/views-procs.sql 1 Aug 2007 08:59:56 -0000 1.3 @@ -16,17 +16,17 @@ declare p_object_id alias for $1; p_viewer_id alias for $2; - v_views views.views%TYPE; + v_views views_views.views_count%TYPE; begin - select views into v_views from views where object_id = p_object_id and viewer_id = p_viewer_id; + select views_count into v_views from views_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) + INSERT into views_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() + UPDATE views_views + SET views_count = views_count + 1, last_viewed = now() WHERE object_id = p_object_id and viewer_id = p_viewer_id; end if; @@ -42,26 +42,26 @@ declare p_object_id alias for $1; p_viewer_id alias for $2; - p_type alias for $3; - v_views views.views%TYPE; + p_view_type alias for $3; + v_views views_views.views_count%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; + select views_count into v_views from views_by_type where object_id = p_object_id and viewer_id = p_viewer_id and view_type = p_view_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); + INSERT into views_by_type(object_id,viewer_id,view_type) + VALUES (p_object_id, p_viewer_id,p_view_type); v_views := 0; else UPDATE views_by_type - SET views = views + 1, last_viewed = now(), type = p_type + SET views_count = views_count + 1, last_viewed = now(), view_type = p_view_type WHERE object_id = p_object_id and viewer_id = p_viewer_id - and type = p_type; + and view_type = p_view_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'); +select define_function_args('views_by_type__record_view','object_id,viewer_id,view_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.2 -r1.3 --- openacs-4/packages/views/sql/postgresql/views-triggers.sql 23 Jul 2007 05:25:09 -0000 1.2 +++ openacs-4/packages/views/sql/postgresql/views-triggers.sql 1 Aug 2007 08:59:56 -0000 1.3 @@ -12,51 +12,51 @@ -- 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 ' +create function views_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) + INSERT INTO view_aggregates (object_id,views_count,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() + SET views_count = views_count + 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 +create trigger views_views_ins_tr +after insert on views_views for each row -execute procedure views_ins_tr(); +execute procedure views_views_ins_tr(); -create function views_upd_tr () returns opaque as ' +create function views_views_upd_tr () returns opaque as ' begin UPDATE view_aggregates - SET views = views + 1, last_viewed = now() + SET views_count = views_count + 1, last_viewed = now() WHERE object_id = new.object_id; return new; end;' language 'plpgsql'; -create trigger views_upd_tr -after update on views +create trigger views_views_upd_tr +after update on views_views for each row -execute procedure views_upd_tr(); +execute procedure views_views_upd_tr(); -- XXXJCD: Should there be a delete trigger? -create function views_by_type_ins_tr () returns opaque as ' +create or replace 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()); + if not exists (select 1 from view_aggregates_by_type where object_id = new.object_id and view_type = new.view_type) then + INSERT INTO view_aggregates_by_type (object_id,view_type,views_count,unique_views,last_viewed) + VALUES (new.object_id,new.view_type,1,1,now()); else UPDATE view_aggregates_by_type - SET views = views + 1, unique_views = unique_views + 1, last_viewed = now() + SET views_count = views_count + 1, unique_views = unique_views + 1, last_viewed = now() WHERE object_id = new.object_id - AND type = new.type; + AND view_type = new.view_type; end if; return new; @@ -67,12 +67,12 @@ for each row execute procedure views_by_type_ins_tr(); -create function views_by_type_upd_tr () returns opaque as ' +create or replace function views_by_type_upd_tr () returns opaque as ' begin UPDATE view_aggregates_by_type - SET views = views + 1, last_viewed = now() + SET views_count = views_count + 1, last_viewed = now() WHERE object_id = new.object_id - AND type = new.type; + AND view_type = new.view_type; return new; end;' language 'plpgsql'; Index: openacs-4/packages/views/sql/postgresql/upgrade/upgrade-0.1d2-0.1d3.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/views/sql/postgresql/upgrade/upgrade-0.1d2-0.1d3.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/views/sql/postgresql/upgrade/upgrade-0.1d2-0.1d3.sql 1 Aug 2007 08:59:57 -0000 1.1 @@ -0,0 +1,156 @@ +-- packages/views/sql/postgresql/upgrade/upgrade-0.1d2-0.1d3.sql +-- +-- Upgrade tables names and column to Oracle compatibility +-- +-- Copyright (C) 2006 Innova - UNED +-- @author Mario Aguado +-- @creation-date 20/07/2006 +-- +-- @cvs-id $Id: upgrade-0.1d2-0.1d3.sql,v 1.1 2007/08/01 08:59:57 marioa 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 + +--Drop function triggers + +drop function views_upd_tr() cascade; +drop function views_ins_tr() cascade; +drop function views_by_type_ins_tr() cascade; +drop function views_by_type_upd_tr() cascade; + +--Rename table and column name + +alter table views rename column views to views_count; +alter table view_aggregates rename column views to views_count; +alter table views rename to views_views; +alter index views_viewer_idx rename to views_views_viewer_idx; + +alter table views_by_type rename column views to views_count; +alter table view_aggregates_by_type rename column views to views_count; + +alter table views_by_type rename column type to view_type; +alter table view_aggregates_by_type rename column type to view_type; + +--Modify function with new table and column names. + +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.views_count%TYPE; +begin + select views_count into v_views from views_views where object_id = p_object_id and viewer_id = p_viewer_id; + + if v_views is null then + INSERT into views_views(object_id,viewer_id) + VALUES (p_object_id, p_viewer_id); + v_views := 0; + else + UPDATE views_views + SET views_count = views_count + 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'; + +--Create new triggers with new names + +create or replace function views_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_count,unique_views,last_viewed) + VALUES (new.object_id,1,1,now()); + else + UPDATE view_aggregates + SET views_count = views_count + 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_views_ins_tr +after insert on views_views +for each row +execute procedure views_views_ins_tr(); + +create or replace function views_views_upd_tr () returns opaque as ' +begin + UPDATE view_aggregates + SET views_count = views_count + 1, last_viewed = now() + WHERE object_id = new.object_id; + + return new; +end;' language 'plpgsql'; + +create trigger views_views_upd_tr +after update on views_views +for each row +execute procedure views_views_upd_tr(); + + +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_view_type alias for $3; + v_views views_views.views_count%TYPE; +begin + select views_count into v_views from views_by_type where object_id = p_object_id and viewer_id = p_viewer_id and view_type = p_view_type; + + if v_views is null then + INSERT into views_by_type(object_id,viewer_id,view_type) + VALUES (p_object_id, p_viewer_id,p_view_type); + v_views := 0; + else + UPDATE views_by_type + SET views_count = views_count + 1, last_viewed = now(), view_type = p_view_type + WHERE object_id = p_object_id + and viewer_id = p_viewer_id + and view_type = p_view_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,view_type'); + +create or replace 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 view_type = new.view_type) then + INSERT INTO view_aggregates_by_type (object_id,view_type,views_count,unique_views,last_viewed) + VALUES (new.object_id,new.view_type,1,1,now()); + else + UPDATE view_aggregates_by_type + SET views_count = views_count + 1, unique_views = unique_views + 1, last_viewed = now() + WHERE object_id = new.object_id + AND view_type = new.view_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 or replace function views_by_type_upd_tr () returns opaque as ' +begin + UPDATE view_aggregates_by_type + SET views_count = views_count + 1, last_viewed = now() + WHERE object_id = new.object_id + AND view_type = new.view_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();