Forum OpenACS Development: Re: logging parameter value changes
I hope I've recovered well enough from a few dizzy spells from looking at kernel code to be coherent...
It seems that when ad_conn has info for ip_address and user, that parameter::set_value should pass the ip_address and user_id to apm__set_value, which would then perform acs_object__update_last_modified just prior to PERFORM apm__set_value(int4,int4,varchar)
..and then, have a trigger/table setup like this:
create table apm_parameter_values_audit ( value_id integer, package_id integer, parameter_id integer, -- -- This column holds the value for the instance parameter. -- attr_value_was text, last_modified timestamptz default current_timestamp not null, -- get following 2 values from acs_objects table modifying_user integer, modifying_ip varchar(50) ); create or replace function apm_parameter_values_audit_tr () returns opaque as ' DECLARE v_attr_value text; v_parameter_name varchar(100); v_last_modified timestamptz; v_creation_date timestamptz; v_last_modifying_user integer; v_modified_ip_address varchar(50); BEGIN select into v_last_modified last_modified from acs_objects where object_id = old.value_id; select into v_last_modifying_user modifying_user from acs_objects where object_id = old.value_id; select into v_modified_ip_address modifying_ip from acs_objects where object_id = old.value_id; select into v_parameter_name parameter_name from apm_parameters where parameter_id = old.parameter_id; v_attr_value := old.attr_value; IF (v_parameter_name = ''froogle_password'' ) THEN v_attr_value := ''value expunged''; END IF; -- check if modified from system instead of UI IF ( v_last_modifying_user IS NULL ) THEN IF (v_last_modified IS NULL ) THEN -- logging original parameter setting select into v_creation_date creation_date from acs_objects where object_id = old.value_id; insert into apm_parameter_values_audit ( value_id, package_id, parameter_id, attr_value_was, creation_date ) values ( old.value_id, old.package_id, old.parameter_id, v_attr_value, v_creation_date ); ELSE insert into apm_parameter_values_audit ( value_id, package_id, parameter_id, attr_value_was, last_modified ) values ( old.value_id, old.package_id, old.parameter_id, v_attr_value, v_last_modified ); END IF; ELSE insert into apm_parameter_values_audit ( value_id, package_id, parameter_id, attr_value_was, last_modified,modifying_user, modifying_ip ) values ( old.value_id, old.package_id, old.parameter_id, v_attr_value, v_last_modified, v_last_modifying_user, v_modified_ip_address ); END IF; return new; end;' language 'plpgsql'; create trigger apm_parameter_values_audit_tr before update or delete on apm_parameter_values for each row execute procedure apm_parameter_values_audit_tr ();
Is there a simpler way to log parameter changes to a table (assuming that this way will even work)?
Please let me know if I'm going in the right direction, as logging of parameter changes will be added to the kernel in one for or another soon.
Thanks in advance,
Torben
util::audit -object_id -modifing_user -creation_ip -attributes {list of attribute key/value pairs} -table_name -table_primary_key -table_primary_key_value
This will store the values in a general audit table. If we have an object_id, use the reference to acs_objects, for all other tables use tablename and primary_key_name and value:
create table audit (
audit_id primary key,
object_id references acs_objects,
table_name references pg_(whatever the view for user tables is),
table_primary_key,
table_primary_key_value,
last_modified timestamptz default current_timestamp not null default now(),
-- get following 2 values from acs_objects table
modifying_user integer,
modifying_ip varchar(50)
);
create table audit_value {
audit_id references audit,
key,
value
};
and then obviously something like
ad_proc util::audit::get_changes {
{-object_id ""}
{-modifing_user ""}
{-table_name ""}
{-table_primary_key ""}
{-table_primary_key_value ""}
} {
Retrieve audit log
@return list of list of changes matching the given combination of object_id, user or table definition with one row per change ordered by date.
@return the list will look like {$object_id $modifing_user $modifing_ip $table_name $table_primary_key $table_primary_key_value $modification_date {[list {key new_value old_value}]}}
}
What do you think? This should also take care of Torben's request.