Index: openacs-4/packages/logger/logger.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/logger/logger.info,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/logger/logger.info 8 Apr 2003 12:11:56 -0000 1.1 @@ -0,0 +1,28 @@ + + + + + Logger + Loggers + f + f + + + + oracle + postgresql + + Peter Marklund + Lars Pind + Supports time and expenses reporting. Full specification is here: http://www.collaboraid.biz/developer/logger-spec + 2003-04-02 + Collaboraid + + + + + + + + + Index: openacs-4/packages/logger/sql/postgresql/logger-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/logger/sql/postgresql/logger-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/logger/sql/postgresql/logger-create.sql 8 Apr 2003 12:12:40 -0000 1.1 @@ -0,0 +1,10 @@ +-- +-- Logger Oracle data model +-- +-- @author Lars Pind (lars@collaboraid.biz) +-- @author Peter Marklund (peter@collaboraid.biz) +-- @creation-date 2003-03-28 +-- @auther Postgres Port by Dirk Gomez (openacs@dirkgomez.de) + +\i logger-tables-create.sql +\i logger-packages-create.sql Index: openacs-4/packages/logger/sql/postgresql/logger-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/logger/sql/postgresql/logger-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/logger/sql/postgresql/logger-drop.sql 8 Apr 2003 12:12:40 -0000 1.1 @@ -0,0 +1,10 @@ +-- +-- Drop script for the Logger Oracle data model +-- +-- @author Lars Pind (lars@collaboraid.biz) +-- @author Peter Marklund (peter@collaboraid.biz) +-- @creation-date 2003-03-31 + -- @auther Postgres Port by Dirk Gomez (openacs@dirkgomez.de) + +\i logger-tables-drop.sql +\i logger-packages-drop.sql Index: openacs-4/packages/logger/sql/postgresql/logger-packages-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/logger/sql/postgresql/logger-packages-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/logger/sql/postgresql/logger-packages-create.sql 8 Apr 2003 12:12:40 -0000 1.1 @@ -0,0 +1,138 @@ +-- +-- Postgres packages for the Logger application +-- +-- @author Lars Pind (lars@collaboraid.biz) +-- @author Peter Marklund (peter@collaboraid.biz) +-- @creation-date 2003-04-03 +-- @auther Postgres Port by Dirk Gomez (openacs@dirkgomez.de) + +create function logger_project__new (integer, varchar, varchar, integer, integer, varchar, integer) +returns integer as ' +declare + project_id alias for $1; -- default null + name alias for $2; + description alias for $3; -- default null + project_lead alias for $4; + creation_user alias for $5; + creation_ip alias for $6; -- default null + package_id alias for $7; + + v_project_id integer; +begin + v_project_id := acs_object__new( + project_id, + ''logger_project'', + package_id, + creation_ip, + creation_user + ); + + insert into logger_projects (project_id, name, description, project_lead) + values (v_project_id, name, description, project_lead); + + insert into logger_project_pkg_map (project_id, package_id) + values (v_project_id, logger_project.new.package_id); + + return v_project_id; +end; ' language 'plpgsql'; + +create function logger_project__delete (integer) +returns integer as ' +declare + project_id alias for $1; +begin + -- Delete all measurements in the project + for rec in (select measurement_id + from logger_measurements + where project_id = logger_project.delete.project_id + ) + loop + logger_measurement.delete(rec.measurement_id); + end loop; + + -- Delete all variables only mapped to this project. + for rec in (select variable_id + from logger_variables + where exists (select 1 + from logger_project_pkg_map + where project_id = logger_project.delete.project_id + ) + and not exists (select 1 + from logger_project_pkg_map + where project_id <> logger_project.delete.project_id + ) + ) + loop + delete from logger_variables where variable_id = rec.variable_id; + end loop; + + -- Delete the project acs object. This will cascade the row in the logger_projects table + -- as well as all projections in the project + acs_object.delete(project_id); +end; ' language 'plpgsql'; + +create function logger_project__name (integer) +returns varchar as ' +declare + project_id alias for $1; + + v_name logger_projects.name%TYPE; +begin + select name + into v_name + from logger_projects + where project_id = name.project_id; + + return v_name; +end; ' language 'plpgsql'; + +create function logger_measurement__new (integer, integer, integer, integer, date, varchar, integer, varchar, integer) +returns integer as ' +declare + measurement_id alias for $1; -- default null + project_id alias for $2; + variable_id alias for $3; + value alias for $4; + time_stamp alias for $5; + description alias for $6; -- default null + creation_user alias for $7; + creation_ip alias for $8; -- default null + + v_measurement_id integer; +begin + v_measurement_id := acs_object__new( + measurement_id, + ''logger_measurement'', + project_id, + creation_ip, + creation_user + ); + + insert into logger_measurements (measurement_id, project_id, variable_id, value, + time_stamp, description) + values (v_measurement_id, project_id, variable_id, value, time_stamp, description); + + return v_measurement_id; +end; ' language 'plpgsql'; + +create function logger_measurement__delete (integer) +returns integer as ' +declare + measurement_id alias for $1; -- default null +begin + -- The row in the measurements table will cascade + acs_object.delete(measurement_id); +end; ' language 'plpgsql'; + +create function logger_measurement__name (integer) +returns varchar as ' +declare + v_name logger_projects.name%TYPE; + begin + -- TODO: Should we only return the say 20 first characters here? + select description into v_name + from logger_measurements + where measurement_id = logger_measurement.name.measurement_id; + + return v_name; +end; ' language 'plpgsql'; Index: openacs-4/packages/logger/sql/postgresql/logger-packages-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/logger/sql/postgresql/logger-packages-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/logger/sql/postgresql/logger-packages-drop.sql 8 Apr 2003 12:12:40 -0000 1.1 @@ -0,0 +1,13 @@ +-- +-- Drop script for Oracle PL/SQL packages in the Logger application +-- +-- @author Lars Pind (lars@collaboraid.biz) +-- @author Peter Marklund (peter@collaboraid.biz) +-- @creation-date 2003-03-28 + +drop function logger_project__new (integer, varchar, varchar, integer, integer, varchar, integer) ; +drop function logger_project__delete (integer) ; +drop function logger_project__name (integer) ; +drop function logger_measurement__new (integer, integer, integer, integer, date, varchar, integer, varchar, integer) ; +drop function logger_measurement__delete (integer) ; +drop function logger_measurement__name (integer) ; Index: openacs-4/packages/logger/sql/postgresql/logger-tables-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/logger/sql/postgresql/logger-tables-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/logger/sql/postgresql/logger-tables-create.sql 8 Apr 2003 12:12:40 -0000 1.1 @@ -0,0 +1,227 @@ +-- Oracle tables for the Logger application +-- +-- @author Lars Pind (lars@collaboraid.biz) +-- @author Peter Marklund (peter@collaboraid.biz) +-- @creation-date 3:d of April 2003 + +create table logger_projects ( + project_id integer + constraint logger_projects_pk + primary key + constraint logger_projects_pid_fk + references acs_objects(object_id) + on delete cascade, + name varchar(1000), + description varchar(4000), + active_p char(1) + default 't' + constraint logger_projects_ap_nn + not null + constraint logger_projects_ap_ck + check (active_p in ('t', 'f')), + project_lead integer + constraint logger_projects_pl_nn + not null + constraint logger_projects_pl_fk + references users(user_id) +); + +comment on table logger_projects is ' + Log entries are grouped by projects. Once we have a dedicated + project management package for OpenACS this table will be superseeded by + tables in that package. In order to make such a change easier in the future + we are not referencing the logger_projects table directly in the logger datamodel + but instead reference acs_objects. +'; + +-- By making projects acs objects we can use permissioning and also by +-- referencing acs objects we make any future transition to project objects in a +-- project management package easier. +create function inline_0 () +returns integer as ' +begin +PERFORM acs_object_type__create_type ( + ''logger_project'', -- object_type + ''Logger project'', -- pretty_name + ''Logger projects'', -- pretty_plural + ''acs_object'', -- supertype + ''logger_projects'', -- table_name + ''project_id'', -- id_column + null, -- package_name + ''f'', -- abstract_p + null, -- type_extension_table + ''logger_project.name'' -- name_method +); + return 0; +end;' language 'plpgsql'; +select inline_0 (); +drop function inline_0 (); + +-- +create table logger_project_pkg_map ( + project_id integer + constraint logger_project_pkg_map_pr_fk + references acs_objects(object_id) + on delete cascade, + package_id integer + constraint logger_project_pkg_map_pa_fk + references apm_packages(package_id) + on delete cascade, + constraint logger_project_pkg_map_un + unique (project_id, package_id) +); + +comment on table logger_project_pkg_map is ' + Each project can be mounted in multiple package instances. +'; + +create table logger_variables ( + variable_id integer + constraint logger_variables_pk + primary key, + name varchar(200), + unit varchar(200), + type varchar(50) + default 'additive' + constraint logger_variables_type_nn + not null + constraint logger_variables_type_ck + check (type in ('additive', 'non-additive')) +); + +comment on column logger_variables.type is ' + Indicates if measurements of this variable should be added together or not. + Examples of additive variables are time and money spent at different times during + a project. A non-additive variable would be the amount of money in a bank account. +'; + +create sequence logger_variables_seq; + +create table logger_project_var_map ( + project_id integer + constraint logger_project_var_map_pid_fk + references acs_objects(object_id) + on delete cascade + constraint logger_project_var_map_pid_nn + not null, + variable_id integer + constraint logger_project_var_map_vid_fk + references logger_variables(variable_id) + on delete cascade + constraint logger_project_var_map_vid_nn + not null, + primary_p char(1) + default 't' + constraint logger_project_var_map_pp_nn + not null + constraint logger_project_var_map_pp_ck + check (primary_p in ('t', 'f')), + constraint logger_project_var_map_un + unique(project_id, variable_id) +); + +comment on column logger_project_var_map.primary_p is ' + Every project must have a primary variable which represents what we are + most interested in logging and reporting in that project + - typically time spent on various tasks. +'; + +create table logger_projections ( + projection_id integer + constraint logger_projections_pk + primary key, + project_id integer + constraint logger_projections_pid_nn + not null + constraint logger_projections_pid_fk + references acs_objects(object_id) + on delete cascade, + variable_id integer + constraint logger_projections_vid_nn + not null + constraint logger_projections_vid_fk + references logger_variables(variable_id) + on delete cascade, + start_time date + constraint logger_projections_st_nn + not null, + end_time date + constraint logger_projections_et_nn + not null, + value integer + constraint logger_projections_value_nn + not null +); + +comment on table logger_projections is ' + This table allows a project admin to specify expected or targeted logging values + over a particular time period. +'; + +comment on column logger_projections.value is ' + For additive variables the projection value will represent the expected or targeted + sum of measurements during the time range and for non-additive variables it will + represent an average. +'; + +create sequence logger_projections_seq; + +create table logger_measurements ( + measurement_id integer + constraint logger_measurements_pk + primary key + constraint logger_measurements_mid_fk + references acs_objects(object_id) + on delete cascade, + project_id integer + constraint logger_measurements_pid_fk + references acs_objects(object_id) + on delete cascade, + variable_id integer + constraint logger_measurements_v_id_fk + references logger_variables(variable_id) + on delete cascade, + value integer + constraint logger_measurements_value_nn + not null, + time_stamp date + default now () + constraint logger_measurements_ts_nn + not null, + description varchar(4000) +); + +comment on table logger_measurements is ' + This is the center piece of the logger datamodel that holds the actually reported + data - namely numbers bound to points in time. Given the HR-XML + Time and Reporting standard (see http://www.hr-xml.org) we considered allowing + for explicit start and end times. However, in the interest of simplicity + for the initial release of the package we opted against this. The HR-XML spec talks + about three categories of reports - time events, time intervals, and + expenses incurred. Of those we are initially only supporting the latter two + and for time intervals we don''t support any explicit start and end time (only a timestamp + and a value). Support for those remaining HR-XML use cases can be added on later without + much difficulty. +'; + +-- Measurements need to be acs objects if we are to categorize the with the categories +-- package +create function inline_0 () +returns integer as ' +begin +PERFORM acs_object_type__create_type ( + ''logger_measurement'', -- object_type + ''Logger measurement'', -- pretty_name + ''Logger measurements'', -- pretty_plural + ''acs_object'', -- supertype + ''logger_measurements'', -- table_name + ''measurement_id'', -- id_column + null, -- package_name + ''f'', -- abstract_p + null, -- type_extension_table + ''logger_measurement.name'' -- name_method +); + return 0; +end;' language 'plpgsql'; +select inline_0 (); +drop function inline_0 (); Index: openacs-4/packages/logger/sql/postgresql/logger-tables-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/logger/sql/postgresql/logger-tables-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/logger/sql/postgresql/logger-tables-drop.sql 8 Apr 2003 12:12:40 -0000 1.1 @@ -0,0 +1,50 @@ +-- Drop non-procedural data model of the Logger application. +-- NOTE: In general it is not a good idea to source sql drop scripts +-- from the command line since such scripts may assume that any data in package instances +-- has already been dropped by the APM through the before-uninstantiate callback. +-- Use the /acs-admin/apm UI instead to delete packages. +-- +-- @author Lars Pind (lars@collaboraid.biz) +-- @author Peter Marklund (peter@collaboraid.biz) +-- @creation-date 3:d of April 2003 + +drop table logger_measurements; + +create function inline_0 () +returns integer as ' +begin + perform acs_object_type__drop_type ( + ''logger_measurement'', ''f'' + ); + + return null; +end;' language 'plpgsql'; +select inline_0(); +drop function inline_0 (); + +drop table logger_projections; + +drop sequence logger_projections_seq; + +drop table logger_project_var_map; + +drop table logger_variables; + +drop sequence logger_variables_seq; + +drop table logger_project_pkg_map; + +drop table logger_projects; + +create function inline_0 () +returns integer as ' +begin + perform acs_object_type__drop_type ( + ''logger_project'', ''f'' + ); + + return null; +end;' language 'plpgsql'; +select inline_0(); +drop function inline_0 (); + Index: openacs-4/packages/logger/tcl/test/logger-test-init.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/logger/tcl/test/logger-test-init.tcl,v diff -u -r1.3 -r1.4 --- openacs-4/packages/logger/tcl/test/logger-test-init.tcl 6 Apr 2003 09:27:14 -0000 1.3 +++ openacs-4/packages/logger/tcl/test/logger-test-init.tcl 8 Apr 2003 12:13:46 -0000 1.4 @@ -133,13 +133,19 @@ aa_equals "Value of measurements is retrievable" $hour_measurement_1_retr(value) \ $hour_measurement_1(value) + # Add a test category tree + # TODO when the categories package is stabilized + } -teardown_code { ##### # # Teardown # ##### + # Delete the categories + # TODO... + # Delete the package - deletes all logger data related to the package through the before-uninstantiate callback apm_package_instance_delete $package_id