I've recently moved several of my applications to a PhraseBook pattern for SQL queries and HTML snippet storage/isolation. My PhraseBook implementation allows various descriptive comments to be tied to phrases and then HTML-ified for documentation.
I'ld like to develop some sort of system for collecting stats/profiling the SQL queries and storing the results back into the Phrase file.
Typically speaking database queries are handled by a Database class which returns a QueryResult. Typically the caller will query the PhraseBook for query along with the variable bindings. The Phrasebook returns the built query which is then passed to the Database->query() method. Often the calling function is a member function of Database, but this is not a requirement.
The data I would like to collect:
The number of times a given PhraseBook query is requested. (Easy)
The time the query took to execute (Hard*)
The average and stddev for the above (Easy, given above)
The query plan (Easy given the hard one...)
Easy, Medium, Hard are all relative.... Most are trivial, but a clean design is harder....
The challenges are:
1. This should be off by default, possibly except for the simple counting requests data.
2. I don't want to have to propagate debuging information through the code when its not always used...
The 1st data element can be done when phrase is requested
The 2nd-4th items aren't know until the query is executed. The DB class and the PhraseBook class are de-coupled, so there is no apparent way for the DB class to know from which Phrase the query came. (or if it even came from a phrase). Extending the query() function to include a reference to $phrase_handle, is not a clean option.
One solution: a singleton "QueryProfiler" class could be created along with a "ProfileSQLPhraseBook" subclass of PhraseBook. Anytime a Phrase is requested from the ProfileSQLPhraseBook, it adds the query, handle, and source book to the QueryProfiler. The DB class can check if the query exists in the QueryProfiler, then conducts the needed measurements, passing the results back to the Query Profiler. This can also offload the task of updating the comment fields of the PhraseBook to the QueryProfiler.
This does at a little bit of overhead to the non-debuging case (checking for a key in the hash), but I can live with that as the function calls aren't messed with.
Does anyone have any comments or suggestions?
Combining a PhraseBook pattern with profiling....
Moderator: General Moderators