Overriding default query manager

I am building an application which needs to have full auditing. I have already created a ModelBase, ControllerBase and FormBase which allow me to interact with the database to save audit information when child classes are accessed. The problem I am facing now is with PHQL. If I use something like MyModel::find() then methods that MyModel inherits from ModelBase can be called and used and so I get my audit information. But if I do something like

$query = $this->modelsManager->createQuery("SELECT * from MyModelOne inner join MyModelTwo");
$cars = $query->execute();

Now $cars is a generic query result without any of my auditing functions built in. Is there some way during dependency injection that I can override all default query objects so that those also include code for auditing?



83.0k

You can add a listener to your database connection so every SQL generated can be stored/audited:

https://github.com/phalcon/forum/blob/master/app/config/services.php#L114-L131



15.9k

Not good enough. I need to know what the results are that were returned. If I am looking at SQL 3 months hence I haven't a clue what the results were at the time that they were run. I need a way to track: "As a result of the following SQL, here are the values from tables A,B, and C that the user actually viewed in the cursor returned."



83.0k

You need to check the results at database level or ORM level?



15.9k

This is a system which will store HIPAA protected data. I need to be able to provide a complete audit trail of every piece of data inserted, deleted, updated, or viewed by every user in any given sign-in session, and retain that information for 7 years.

So far I have been able to handle insertions, updates, and deletes cleanly by making parent classes for Forms and Models that all of my form and model objects inherit from. I've handled views by overriding render() in Form and implementing a Model method getValue($column_name) which is what I use to pull data from models. The issue is that when I do a query that isn't just a simple call to a single model, my getValue() method isn't implemented because the object returned doesn't inherit from my ModelBase class.



83.0k

You can override the default models manager to intercept when query instances are created:

class MyModelsManager extends \Phalcon\Mvc\Model\Manager
{
     public function executeQuery($phql, $placeholders=null) { }
     public function createQuery ($phql) { }
}

And register this ModelsManager instead:

$di->set('modelsManager', new MyModelsManager, true);


15.9k

This is exactly the sort of solution that I was thinking of. The issue is how can I get result metadata from those methods. In other words, conceptually, I need to take the sql passed, get from it the names of each table being viewed and then the name of each column from each table. Then when I execute the query, I need to make sure that the primary key from each table is in the select list (how?) so that I can store this in my audit logs.

What I have so far. In services.php:

 $di->set('modelsManager', function() {
     return new MyModelManager();
 });

Then in MyModelManager.php:

public function executeQuery($phql, $placeholders=false){
    $query = parent::createQuery($phql);    

    $parsed = $query->parse();
    $columns = $parsed['columns'];
    $results = $query->execute();
    ...

I just don't see how I can a) guarantee that the primary key field for every table is in the select list, and b) save the data values viewed without walking the entire cursor twice - once to save the audit data and then a second time in the view to display the results. It seems like there is also some limitations on what is returned in parse() like if I use table aliases then parse() returns the alias and not the actual Model name.