[Solved]Best way for query logging

[eluser]rogierb[/eluser]
Hi Guys 'n Gals,

I need to log all insert and update queries(there are no deletes) but am strugling to find the best way.
In one of our projects I've *hacked* the mysql_driver by including a custom method.
This is very poor programming as 4 o'clock friday afternoon decissions can be.

I'm looking for a beter solution and have come up with a few:
- post-controller hook
- extending the output library
- A custom copy of the db driver(ugh..)

maybe there are more solution, I'd very much like to hear them.
Also, my concern is speed. Is using a hook significantly slower? Is it slower then a extension of the output class? What is the fastest way?

So basically what I'm looking for is the ulimate solution for logging queries, not to much to ask for, right? :-)

Thanx a bundle

Rogier

[eluser]n0xie[/eluser]
I would offer you a different solution: you could just let MySQL log everything it 'does'.

Take a look at the logging capabilities of MySQL ( I'm assuming you are using MySQL). If it's just for profiling, you might be better off with the mysql slow query logger which is excellent for finding bottleneck queries.

[eluser]rogierb[/eluser]
Hey n0xie,

Not really an option since I want to include userdetails to the logging.
I want to know who did what and when they did it, from what IP they come from, and yes unfortunately it is necessary.

I already implemented the slow query option in the cnf. A beautifull option:-)

But thanx for the suggestion.

[eluser]n0xie[/eluser]
In your case, from an architect point of view, I'd probably try to implement the Observer Pattern, basically triggering an action on every 'UPDATE', although I have never implemented something like that in CI, so I can't tell you how effective 'hacking' this into the database adapter would be. Searching on the observer pattern on the forum did find KhEvent, which might be useful for your case, but I have no first hand experience with it although I must say it looks interesting.

[eluser]rogierb[/eluser]
KhEvent indeed looks very interesting. Implementing it in a existing system might not be the best idea. I don't want to go trough a couple of hundred controllers and model.

I am sure going to try it in one of our new systems though. This is perfect for package distribution and SQL updates.

Since I need every update and insert, creating triggers on every insert or update doesn't seems helpfull.
Or am I missing something?

[eluser]n0xie[/eluser]
I just thought of another solution. The profiler class has access to a list of all the queries executed. You could try to modify this by extending the Profiler Class (which would be cleaner then hacking the DB_Driver) and trapping all INSERT/UPDATE queries and write them to a log. If you are using PHP5 you have access to these functions since they are pseudo (PHP4) 'private' (underscore before the function).

I just tested this and it works:

Code:

// your normal page
[...]
        $this->load->library('profiler');
        $test = $this->profiler->_compile_queries();
        echo(strip_tags($test));

Now you could add this to a post_controller hook and get a nice list of queries that were executed.

[eluser]rogierb[/eluser]
So it is going to be a post_controller hook then.

Im not going to use the profiler since the queries are available trough $this->db->queries anyway.
Now al I need is an include an exclude array...

But is using hooks going to slow the application down?

[eluser]n0xie[/eluser]
Doh, I forgot all about $this->db->queries :ohh:

The addition of Hooks would add a small overhead (memory mostly), but would be minimal if you add the hook as a post_system hook. This way the output is already send to the client, so an user wouldn't notice the difference.

Since I liked the idea of storing all executed queries I made a POC:

Code:

/* hooks.php */
/* Location: ./system/application/config/hooks.php */
$hook['post_system'][] = array(
                                'class'    => 'DBlog_Hook',
                                'function' => 'log_all_query',
                                'filename' => 'DBlog_Hook.php',
                                'filepath' => 'hooks'
                                );

/* DBlog_Hook.php */
/* Location: ./system/application/hooks/DBlog_Hook.php */
class DBlog_Hook {

   var $path    = 'system/logs/';
    
    function DBlog_Hook()
    {
        $this->CI =& get_instance();
    }
    
    function log_all_query()
    {
        $dbs    = array();
        $output = NULL;
        
        $queries = $this->CI->db->queries;

        if (count($queries) == 0)
        {
            $output .= "no queries\n";
        }
        else
        {
            foreach ($queries as $query)
            {
                $output .= $query . "\n";
            }
            $output .= "===\n";
        }

        $this->CI->load->helper('file');
        if ( ! write_file($this->path . 'queries.txt', $output, 'a+'))
        {
             log_message('debug','Unable to write the file');
        }
    }
}

[eluser]rogierb[/eluser]
Hey n0xie, thanx!

Saves me time;-)

I'm going to extend this so each day a new logifile is written. Since this application is company based it will be something like '115_company_name_query_log-2009-10-18.php'
Right now we have files > 10MB per day per company.
Since I don't want anyone to be able to read the file, the first line is "<?php if (!defined('BASEPATH')) exit('No direct script access allowed'); ?>"

Also the line written wil be something like:
856 | 14:20 | 127.0.0.1 | INSERT INTO some_table (some_id, another_id, sort, type, status, int_value) VALUES ('1234', '116', '1', '0', '2', '4');

This way we can track who f-ed up.

[eluser]jpi[/eluser]
Interesting post here, especially the post_system hook by n0xie.

I, too, wanted to log all queries + their execution time. This execution time is automatically measured by CI and available in the $CI->db->query_times array. I have slightly rewritten n0xie's hook :

Replace

Code:

foreach ($queries as $query)
            {
                $output .= $query . "\n";
            }

By :

Code:

foreach ($queries as $key => $val)
                {                    
                    $time = $this->CI->db->query_times[$key];

                    $output .= $val . "-" . $time . "\n";                    
                }