Add true 'prepare' functionality to Queries

[lonnieezell]

Queries should have the ability to be a true prepared statement that can be reused. There are performance reasons NOT to make every query a prepared statement, and the query build/connection already takes care of escaping parameters in the same way that a prepared statement would. However, the user should still be able to use true prepared statements because sometimes it does make sense.

Enhancement Checklist:

• Component(s) modified
• Unit testing updated
• User guide updated
• Securely signed commits
• Conforms to style guide

[Portaflex]

Perhaps we could use bindings to parametrize our query ($1, $2) and pass, say pg_prepare, to the DB. If we mark our query as prepared, we could tell CI4 to ask the DB if it already has our named query. If not, send pg_prepare and execute the query. If yes, just execute the prepared query. We need also a persistent connection.

In this way we would have just one definition for the query, specifying if it should be prepared or not.

[lonnieezell]

I haven't firmly got this one fixed in my head, but the user will need to be able to hold on to the Query object itself so they can re-use it with new data. And will likely need a new method or two to run that query. That's why I think it's going to need a new class (extends the Query class?) to work.

I picture the db connection having a prepare() method which builds the prepared query object, and actually prepares it with the database.

[Portaflex]

I've figured out a way to do prepared queries using Postgres. It works 100% of the times without "where" clauses, and 50% of the times with them.

I added one class to Database\Postgres\Prepare.php. And one more method to Database\BaseConnection.php to instantiate the new class.

It is ugly, changes methods from BaseBuilder.php, but works (more or less). Just an idea.

[lonnieezell]

Sorry it took me so long to get back to you. Thanks for taking a stab at that! I don't know that it's ideal, though. I would love to find a way to not have to override anything on the Builder class, so that we didn't mess with anything there. Since it's a query and just has additional capability to send that query multiple times, I think it might work to do something like:

$pQuery = $this->db->prepare(function($db) { 
    return $db->table('foo')->where('ax', 'y')->get();
});

$set1 = $pQuery->run($data1);
$set2 = $pQuery->run($data2);

The trickiest portion of that is that 'get' and similar methods are going to want to actually hit that database, but we might be able to put a flag in there, or something.

Hmm. Will think about it for a few more days but this interface seems promising.

[lonnieezell]

Have started work on this: #200

[lonnieezell]

Now implemented in 099cc8f