Hi Guillaume,
Guillaume ORIOL wrote:
I would like to implement a query tracker in Zend_Db for some tables.
The idea is to store in db an history of all SQL queries that were
executed on these tables.
First of all, never use a timestamp as a primary key. You can't be sure
a timestamp is unique; the occasion might occur two actions are
performed on the same time (even in one request, assuming a query is
performed faster than one second, which is actually quite probable).
Second, if you want to log insert / update and delete statements, you
might want to log actions rather than logging the queries.
create table recordlog(
table varchar(100) not null,
record_id bigint not null, /* if you would want to log compound
primary keys, consider extra columns, or using varchar */
action enum('delete', 'insert', 'update') not null,
... more data you're interested in
);
If you are going to implement a history (as your proposed table name
suggests), you'd probably benefit more from a versioning system, where
you keep versions of all records (even deleted ones), and you copy
original data to before updating or deleting a record. The versioning
table has the same layout as the original table, plus some sort of
version identifier and maybe some action meta data, like logged in user,
date and time, etc.
That way rolling back to previous versions is easy (joined UPDATE on
rolling back to a previous version, or INSERT INTO.. SELECT rolling back
a delete), and searching the table for specific data changes is much
easier.
HTH
Gerard
