Index: openacs-4/packages/views/sql/oracle/views-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/views/sql/oracle/views-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/views/sql/oracle/views-create.sql 1 Aug 2007 08:59:09 -0000 1.2 @@ -0,0 +1,21 @@ +-- Views +-- +-- Tracking and aggregating object views... +-- +-- Copyright (C) 2007 Mario +-- @author Mario Aguado +-- @creation-date 2007-07-30 +-- +-- @cvs-id $Id: views-create.sql,v 1.2 2007/08/01 08:59:09 marioa Exp $ +-- Adapted to Oracle from Jeff Davis postresql's implementation +-- 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 + +@@ views-datamodel.sql +@@ views-procs.sql +@@ views-triggers.sql + + + + Index: openacs-4/packages/views/sql/oracle/views-datamodel.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/views/sql/oracle/views-datamodel.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/views/sql/oracle/views-datamodel.sql 1 Aug 2007 08:59:09 -0000 1.2 @@ -0,0 +1,99 @@ +-- Views +-- +-- Tracking and aggregating object views... +-- +-- Copyright (C) 2007 Mario +-- @author Mario Aguado +-- @creation-date 2007-07-30 +-- +-- @cvs-id $Id: views-datamodel.sql,v 1.2 2007/08/01 08:59:09 marioa Exp $ +-- Adapted to Oracle from Jeff Davis postresql's implementation +-- 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_VIEWS ( + OBJECT_ID INTEGER + CONSTRAINT VIEWS_VIEWS_OBJECT_ID_FK + REFERENCES ACS_OBJECTS(OBJECT_ID) ON DELETE CASCADE + CONSTRAINT VIEWS_VIEWS_OBJECT_ID_NN + NOT NULL, + VIEWER_ID INTEGER + CONSTRAINT VIEWS_VIEWS_OWNER_ID_FK + REFERENCES PARTIES(PARTY_ID) ON DELETE CASCADE + CONSTRAINT VIEWS_VIEWS_VIEWER_ID_NN + NOT NULL, + VIEWS_COUNT INTEGER DEFAULT 1, + LAST_VIEWED DATE DEFAULT SYSDATE, + CONSTRAINT VIEWS_VIEWS_PK + PRIMARY KEY (OBJECT_ID, VIEWER_ID) +); + +CREATE UNIQUE INDEX VIEWS_VIEWS_VIEWER_IDX ON VIEWS_VIEWS(VIEWER_ID, OBJECT_ID); + +COMMENT ON TABLE VIEWS_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_COUNT INTEGER DEFAULT 1, + UNIQUE_VIEWS INTEGER DEFAULT 1, + LAST_VIEWED DATE DEFAULT SYSDATE +); + +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. +'; + + +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, + VIEW_TYPE VARCHAR(100) NOT NULL, + VIEWS_COUNT INTEGER DEFAULT 1, + LAST_VIEWED DATE DEFAULT SYSDATE, + CONSTRAINT VIEWS_BY_TYPE_PK + PRIMARY KEY (OBJECT_ID, VIEWER_ID, VIEW_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_AGG_B_TYPE_OB_ID_FK + REFERENCES ACS_OBJECTS(OBJECT_ID) ON DELETE CASCADE + CONSTRAINT VIEW_AGG_B_TYPE_OB_ID_NN + NOT NULL, + VIEW_TYPE VARCHAR(100) NOT NULL, + VIEWS_COUNT INTEGER DEFAULT 1, + UNIQUE_VIEWS INTEGER DEFAULT 1, + LAST_VIEWED DATE DEFAULT SYSDATE, + CONSTRAINT VIEW_AGGREGATES_BY_TYPE_PK + PRIMARY KEY (OBJECT_ID, VIEW_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/oracle/views-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/views/sql/oracle/views-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/views/sql/oracle/views-drop.sql 1 Aug 2007 08:59:09 -0000 1.2 @@ -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.2 2007/08/01 08:59:09 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 PACKAGE VIEWS_VIEW; +DROP PACKAGE VIEWS_VIEW_BY_TYPE; +DROP TABLE VIEW_AGGREGATES_BY_TYPE; +DROP TABLE VIEWS_BY_TYPE; +DROP TABLE VIEW_AGGREGATES; +DROP TABLE VIEWS_VIEWS; \ No newline at end of file Index: openacs-4/packages/views/sql/oracle/views-procs.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/views/sql/oracle/views-procs.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/views/sql/oracle/views-procs.sql 1 Aug 2007 08:59:09 -0000 1.1 @@ -0,0 +1,90 @@ +-- Views +-- +-- Tracking and aggregating object views -- procedures. +-- +-- Copyright (C) 2003 Jeff Davis +-- @author Jeff Davis +-- +-- Adapted for Oracle by Mario Aguado +-- @author Mario Aguado +-- @creation-date 28/06/2006 +-- +-- @cvs-id $Id: views-procs.sql,v 1.1 2007/08/01 08:59:09 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 +CREATE OR REPLACE PACKAGE VIEWS_VIEW AS + + FUNCTION RECORD_VIEW ( + P_OBJECT_ID IN ACS_OBJECTS.OBJECT_ID%TYPE, + P_VIEWER_ID IN VIEWS_VIEWS.VIEWER_ID%TYPE) RETURN INTEGER; + --update the view count of object_id for viewer viewer_id, returns view count +END VIEWS_VIEW; +/ +CREATE OR REPLACE PACKAGE BODY VIEWS_VIEW AS + + FUNCTION RECORD_VIEW ( + P_OBJECT_ID IN ACS_OBJECTS.OBJECT_ID%TYPE, + P_VIEWER_ID IN VIEWS_VIEWS.VIEWER_ID%TYPE) RETURN INTEGER IS + V_COUNT INTEGER; + VIEWS_COUNT VIEWS_VIEWS.VIEWS_COUNT%TYPE; + BEGIN + SELECT COUNT(*) INTO V_COUNT FROM VIEWS_VIEWS WHERE OBJECT_ID = P_OBJECT_ID AND VIEWER_ID = P_VIEWER_ID; + + IF V_COUNT = 0 THEN + INSERT INTO VIEWS_VIEWS(OBJECT_ID,VIEWER_ID) + VALUES (P_OBJECT_ID, P_VIEWER_ID); + VIEWS_COUNT := 0; + ELSE + SELECT VIEWS_COUNT INTO VIEWS_COUNT FROM VIEWS_VIEWS WHERE OBJECT_ID = P_OBJECT_ID AND VIEWER_ID = P_VIEWER_ID; + UPDATE VIEWS_VIEWS + SET VIEWS_COUNT = VIEWS_COUNT + 1, LAST_VIEWED = SYSDATE + WHERE OBJECT_ID = P_OBJECT_ID + AND VIEWER_ID = P_VIEWER_ID; + END IF; + RETURN VIEWS_COUNT + 1; + END RECORD_VIEW; + +END VIEWS_VIEW; +/ +SHOW ERRORS; + +CREATE OR REPLACE PACKAGE VIEWS_VIEW_BY_TYPE AS + FUNCTION RECORD_VIEW ( + P_OBJECT_ID IN ACS_OBJECTS.OBJECT_ID%TYPE, + P_VIEWER_ID IN VIEWS_BY_TYPE.VIEWER_ID%TYPE, + P_VIEW_TYPE IN VIEWS_BY_TYPE.VIEW_TYPE%TYPE) RETURN VIEWS_VIEWS.VIEWS_COUNT%TYPE; +END VIEWS_VIEW_BY_TYPE; +/ +SHOW ERRORS; + +CREATE OR REPLACE PACKAGE BODY VIEWS_VIEW_BY_TYPE AS + FUNCTION RECORD_VIEW ( + P_OBJECT_ID IN ACS_OBJECTS.OBJECT_ID%TYPE, + P_VIEWER_ID IN VIEWS_BY_TYPE.VIEWER_ID%TYPE, + P_VIEW_TYPE IN VIEWS_BY_TYPE.VIEW_TYPE%TYPE) RETURN VIEWS_VIEWS.VIEWS_COUNT%TYPE IS + 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 = sysdate, 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 RECORD_VIEW; +END VIEWS_VIEW_BY_TYPE; +/ +SHOW ERRORS; + + + Index: openacs-4/packages/views/sql/oracle/views-triggers.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/views/sql/oracle/views-triggers.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/views/sql/oracle/views-triggers.sql 1 Aug 2007 08:59:09 -0000 1.2 @@ -0,0 +1,69 @@ +-- Views +-- +-- Tracking and aggregating object views - triggers for denormalization +-- +-- Copyright (C) 2003 Jeff Davis +-- @author Jeff Davis +-- Adapted for Oracle by Mario Aguado +-- @author Mario Aguado +-- @creation-date 28/06/2006 +-- +-- @cvs-id $Id: views-triggers.sql,v 1.2 2007/08/01 08:59:09 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 + + +CREATE OR REPLACE TRIGGER VIEWS_VIEWS_INS_TR AFTER INSERT ON VIEWS_VIEWS +FOR EACH ROW +DECLARE + V_COUNT INTEGER; +BEGIN + SELECT COUNT(*) INTO V_COUNT FROM VIEW_AGGREGATES WHERE OBJECT_ID = :NEW.OBJECT_ID; + IF V_COUNT < 1 THEN + INSERT INTO VIEW_AGGREGATES (OBJECT_ID,VIEWS_COUNT,UNIQUE_VIEWS,LAST_VIEWED) + VALUES (:NEW.OBJECT_ID,1,1,SYSDATE); + ELSE + UPDATE VIEW_AGGREGATES + SET VIEWS_COUNT = VIEWS_COUNT + 1, UNIQUE_VIEWS = UNIQUE_VIEWS + 1, LAST_VIEWED = SYSDATE + WHERE OBJECT_ID = :NEW.OBJECT_ID; + END IF; +END; +/ +CREATE OR REPLACE TRIGGER VIEWS_VIEWS_UPD_TR AFTER UPDATE ON VIEWS_VIEWS +FOR EACH ROW +BEGIN + UPDATE VIEW_AGGREGATES + SET VIEWS_COUNT = VIEWS_COUNT + 1, LAST_VIEWED = SYSDATE + WHERE OBJECT_ID = :NEW.OBJECT_ID; +END; +/ + +CREATE OR REPLACE TRIGGER VIEWS_BY_TYPE_INS_TR AFTER INSERT ON VIEWS_BY_TYPE +FOR EACH ROW +DECLARE + V_COUNT INTEGER; +BEGIN + SELECT COUNT(*) INTO V_COUNT FROM VIEW_AGGREGATES_BY_TYPE WHERE OBJECT_ID = :NEW.OBJECT_ID AND VIEW_TYPE = :NEW.VIEW_TYPE; + IF V_COUNT < 1 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,SYSDATE); + ELSE + UPDATE VIEW_AGGREGATES_BY_TYPE + SET VIEWS_COUNT = VIEWS_COUNT + 1, UNIQUE_VIEWS = UNIQUE_VIEWS + 1, LAST_VIEWED = SYSDATE + WHERE OBJECT_ID = :NEW.OBJECT_ID + AND VIEW_TYPE = :NEW.VIEW_TYPE; + END IF; +END; +/ + +CREATE OR REPLACE TRIGGER VIEWS_BY_TYPE_UPD_TR AFTER UPDATE ON VIEWS_BY_TYPE +FOR EACH ROW +BEGIN + UPDATE VIEW_AGGREGATES_BY_TYPE + SET VIEWS_COUNT = VIEWS_COUNT + 1, LAST_VIEWED = SYSDATE + WHERE OBJECT_ID = :NEW.OBJECT_ID + AND VIEW_TYPE = :NEW.VIEW_TYPE; +END; +/ Index: openacs-4/packages/views/sql/oracle/upgrade/upgrade-0.1d2-0.1d3.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/views/sql/oracle/upgrade/upgrade-0.1d2-0.1d3.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/views/sql/oracle/upgrade/upgrade-0.1d2-0.1d3.sql 1 Aug 2007 08:59:09 -0000 1.1 @@ -0,0 +1,111 @@ +UPDATE APM_PACKAGE_VERSIONS SET VERSION_NAME = '0.1d2' WHERE VERSION_NAME = '0.2d2' AND PACKAGE_KEY = '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, + VIEW_TYPE VARCHAR(100) NOT NULL, + VIEWS_COUNT INTEGER DEFAULT 1, + LAST_VIEWED DATE DEFAULT SYSDATE, + CONSTRAINT VIEWS_BY_TYPE_PK + PRIMARY KEY (OBJECT_ID, VIEWER_ID, VIEW_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_AGG_B_TYPE_OB_ID_FK + REFERENCES ACS_OBJECTS(OBJECT_ID) ON DELETE CASCADE + CONSTRAINT VIEW_AGG_B_TYPE_OB_ID_NN + NOT NULL, + VIEW_TYPE VARCHAR(100) NOT NULL, + VIEWS_COUNT INTEGER DEFAULT 1, + UNIQUE_VIEWS INTEGER DEFAULT 1, + LAST_VIEWED DATE DEFAULT SYSDATE, + CONSTRAINT VIEW_AGGREGATES_BY_TYPE_PK + PRIMARY KEY (OBJECT_ID, VIEW_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 PACKAGE VIEWS_VIEW_BY_TYPE AS + FUNCTION RECORD_VIEW ( + P_OBJECT_ID IN ACS_OBJECTS.OBJECT_ID%TYPE, + P_VIEWER_ID IN VIEWS_BY_TYPE.VIEWER_ID%TYPE, + P_VIEW_TYPE IN VIEWS_BY_TYPE.VIEW_TYPE%TYPE) RETURN VIEWS_VIEWS.VIEWS_COUNT%TYPE; +END VIEWS_VIEW_BY_TYPE; +/ +SHOW ERRORS; + +CREATE OR REPLACE PACKAGE BODY VIEWS_VIEW_BY_TYPE AS + FUNCTION RECORD_VIEW ( + P_OBJECT_ID IN ACS_OBJECTS.OBJECT_ID%TYPE, + P_VIEWER_ID IN VIEWS_BY_TYPE.VIEWER_ID%TYPE, + P_VIEW_TYPE IN VIEWS_BY_TYPE.VIEW_TYPE%TYPE) RETURN VIEWS_VIEWS.VIEWS_COUNT%TYPE IS + 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 = sysdate, 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 RECORD_VIEW; +END VIEWS_VIEW_BY_TYPE; +/ +SHOW ERRORS; + +CREATE OR REPLACE TRIGGER VIEWS_BY_TYPE_INS_TR +AFTER INSERT ON VIEWS_BY_TYPE +FOR EACH ROW +DECLARE + V_COUNT INTEGER; +BEGIN + SELECT COUNT(*) INTO V_COUNT FROM VIEW_AGGREGATES_BY_TYPE WHERE OBJECT_ID = :NEW.OBJECT_ID AND VIEW_TYPE = :NEW.VIEW_TYPE; + IF V_COUNT < 1 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,SYSDATE); + ELSE + UPDATE VIEW_AGGREGATES_BY_TYPE + SET VIEWS_COUNT = VIEWS_COUNT + 1, UNIQUE_VIEWS = UNIQUE_VIEWS + 1, LAST_VIEWED = SYSDATE + WHERE OBJECT_ID = :NEW.OBJECT_ID + AND VIEW_TYPE = :NEW.VIEW_TYPE; + END IF; +END; +/ + +CREATE OR REPLACE TRIGGER VIEWS_BY_TYPE_UPD_TR +AFTER UPDATE ON VIEWS_BY_TYPE +FOR EACH ROW +BEGIN + UPDATE VIEW_AGGREGATES_BY_TYPE + SET VIEWS_COUNT = VIEWS_COUNT + 1, LAST_VIEWED = SYSDATE + WHERE OBJECT_ID = :NEW.OBJECT_ID + AND VIEW_TYPE = :NEW.VIEW_TYPE; +END; +/