Database transactions involving prepared statements

I was thinking whether it is possible to avoid exposing PDO and PDOStatement objects to the users of my database library (mainly just me). While I was working on my project I sort of notice that there is a almost fixed pattern whenever I work with the database. With this in mind, I added in some new functions to the library, and decided to make a quick release for this.

Most of the time, issuing a database statement in PHP involves 3 steps. Firstly, one would want to prepare a statement. Then the statement is to be executed. If there’s no problem with the execution one would proceed with fetching the result. Writing these is fairly easy, and I am sure most people are familiar enough with the following statements,


$statement = $pdo->prepare('SELECT :foo');
$statement->execute(array(':foo' => 'bar'));
statement->fetch(); // or fetchAll(), fetchColumn() etc.


Then I started thinking whether I can abuse the power of closure to this. After some hacking, I have made the 3-step process into the following code,


use \coolsilon\database as db;
// step 0: make database object
$database = db\init($pdo);
// step 1: create the statement
$statement = call_user_func($database, 'SELECT :foo');
// step 2: execute the query
$result = call_user_func($statement, array(':foo' => 'bar'));
// step 3: fetching row(s) from the result
call_user_func($result); // which is the same as call_user_func($result, db\FETCH);
call_user_func($result, db\FETCH_ALL);
call_user_func($result, db\FETCH_OBJECT);
call_user_func($result, db\FETCH_COLUMN);
// I don't like this form, but should be perfectly OK
$result();
$result(db\FETCH_ALL);
$result(db\FETCH_OBJECT);
$result(db\FETCH_COLUMN);


Nothing much has changed at the relation-based query side though. Except when creating a relation generator, instead of passing in a PDO object to relation_generator function, a database closure is passed in instead. The function statement_execute is obviously dropped, in favor of this new form of interacting with database.


use \coolsilon\database as db;
// instead of this
$generator = db\relation_generator($pdo, json_reader());
// a generator is now
$generator = db\relation_generator(db\init($pdo), json_reader());
// getting a relation
$relation = call_user_func($generator, 'relation.json');
// getting a statement
$statement = call_user_func($relation, $statement_key);
// executing the statement
$result = call_user_func($statement, $data);
// getting row(s) from the result
call_user_func($result); // same as call_user_func($result, db\FETCH);
call_user_func($result, db\FETCH_COLUMN);
call_user_func($result, db\FETCH_OBJECT);
call_user_func($result, db\FETCH_ALL);


Basically this is all for now. An added bonus gained by simplifying function calls involving database is that I can further isolate the form library from the database library. While there is still a long way to go in making the

There is also some small changes made to the form library, where new widgets are introduced. To get an idea on what new widgets are being introduced, you may want to check the following schema.


"field": {
"favorite-number": {
"widget": {
"type": "text"
},
"validation": {
"integer": []
}
},
"email": {
"widget": {
"type": "text"
},
"validation": {
"email": []
}
},
"url": {
"widget": {
"type": "text"
},
"validation": {
"url": []
}
},
"photo": {
"widget": {
"type": "upload"
},
"validation": {
"type": ["image/jpg", "image/jpeg", "image/png", "image/gif"]
}
}
}


Basically there are a couple of new validation functions are added to handle integer data, URL and email. A new file upload widget is also added, and a list of allowed file types can be added. Considering I have poured in enough code, I guess tagging it as 0.2 is appropriate. Kindly check the code if you’re interested.