PHP/MySQL/MDB2 - Implementing a Change Log

[cmayo]

Hi All,

One of my contract projects is a fairly involved office management system, and my client has decided he wants to add a change log to the system which would list each change made to each database table in the system.

There are dozens of scripts and hundreds of SQL queries in the existing system and while I could (and likely will) slog through each script and add a logging query to accompany each existing query, I'm wondering if anyone can suggest a better way, perhaps some method where either MySQL or the MDB2 abstraction layer could perform a logging action of some sort on every query executed?

Any suggestions are appreciated!

 

[DonQuichote]

I do assume you have some central database class (or one for each database type). To enable logging is to simply augment this class. If your code is written "blind panic" style, where database access can be done at any place, any time, it is now THE moment of writing such a database class.



+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[cmayo]

Thanks, Don. I'm not sure of the technical definition of "blind panic," but the only database class in use is the MDB2 abstraction layer, which is open source and extensible, and which I'd really hoped to avoid extending.

Anyone else have any ideas?

 

[DonQuichote]

Blind panic" is a term I made up myself:

LAlala, hey, I am sending some output to the browser...
Oh, I should print the username.
Help! Where do I get the username from? O yes! The database.
But where is the database? Ah, I must open a connection to it!
But the database doesn't speak PHP! So I must sent it some SQL!
So I must build up some SQL right now! Lessee...
Right! I have sent the query. But where are my results? Please server! My life depends on it (... or die ...)! I will have to dig them up!
And now I am digging up the results. Ah there it is. Pfiooh! Finally I can print a username...

Off course, this could be a bit more relaxed and better organized:
$application->Backend()->Users($userId)->Name
Where $application controls and holds all things that have to have application-wide scope, The backend object holds and controls all the persistent collections, of which the Users collection holds (surprise) the user objects, who have a Name property.

In fact, you cannot even see that the persistent collections use the database, because that is just an implementation detail. If they need a database, they should do so internally and keep quiet about it. Why? Because all the surrounding code does not care where the data comes from. That is an internal responsibility from the collections themselves. Off course, the surrounding code is then completely database independent.

My database class is just an abstraction for things I want to do with a database: run queries, tell me the last generated ID, escape a value properly, allow me to pass parameters to a query. It also holds some often use functions, like FirstRowOf, FirstFieldOf, AllRowsAsArray, etc. AND, off course, its holds the connection internally and closes it nicely upon destruction.

Now, most of these function map exactly to mysql_*, mysqli_* or whatever other library you use. But if you pass the appropriate database class, your code does not even have to know which type of database they are talking to, because that is an internal responsibility of that class only. In fact, I have database classes, that only log, send queries through a queue, and a splitter, that allows me to use all of them at once during testing. And the surrounding code will never even have a clue...

So this is why I said that now is the time to write your own database class now. It is the difference between your code getting things "from the database" or "if db2, lets's use these functions, if mysql, let's use those, if..." at all places where a database is used.

The other advantage, off course, is that you can make the logging part of the RunQuery method of the database classes. And again, the surrounding code does not need to know or can suffice by passing a boolean to the RunQuery method indicating that this query must be logged.

Well, this does force you to go through the entire code and change things, but you had to do that anyway. The advantage of a central database class is that you can solve any errors centrally and add extra features centrally (error handling, logging, etc.). I do not know what must go to the log, but for MySQL it is good to know that all queries can be logged by the server. It can produce giant log files on production servers, but is very useful during development.

Hope this helps.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)