Hadanite Marasek: paran: EPDO

One of my oldest classes within paran is EPDO. I think that the whole PDO stuff was a reason to write the third generation of my class library, as I was glad that finally an official DB wrapper had appeared and I was then ready to abandon my own abstraction class. However cool as PDO was, it lacked a few functions which I had come to like.

A few helpers

First of all, I found the whole Statement/fetch-game a little bit tedious. mysql_result() was practical when you had a query which would return only one value, but with PDO, this is boring business. Therefore, I wrote EPDO::result.


<?php

$stmt = $pdo->prepare("select count(*) from employees where gender = ?");

$stmt->execute(array("f"));

$result = $stmt->fetchColumn();

$stmt->closeCursor();

//becomes:

$result = $epdo->result("select count(*) from employees where gender = ?", array("f"));

?>


<?php

$stmt = $pdo->prepare("select name, surname from user where login = ? and password = ?");

$stmt->execute(array("hm", "supersecret"));

$array = $stmt->fetch();

$stmt->closeCursor();

//becomes

$array = $epdo->row("select name, surname from user where login = ? and password = ?", array("hm", "supersecret"));

?>

And before someone objects: yes, I know that passwords should not be stored as clear text.

A few more helpers

Once I saw a bug within a bug tracking system: problems when saving values which contain single quotes. I inquired and looked into the source code and found the answer: these top level programmers with Diploma had not only written every single query by hand, but neglected to quote special characters. Which reminded me of the saying that a good programmer is a lazy programmer - if he is lazy on the right spot. And certainly I'm lazy, that is why I wrote helpers for insert, update and delete statements, which make for the majority of all queries. Why should anyone even be tempted to fail to properly do an INSERT?


<?php

$create["login"] = "hm";

$create["password"] = "supersecret";

$create["surname"] = "Marasek";

$create["name"] = "Hadanita";

//create returns the AUTO_INCREMENT/serial value of the newly created key

$id = $epdo->create("user", $create);



//update user with login "hm" to have password "cat friend" and name "Hadanite"

$update["password"] = "cat friend";

$update["name"] = "Hadanite";

$condition["login"] = "hm";

$epdo->update("user", $update, $cond);



// delete user with login hm.

$epdo->delete("user", $cond);

?>

EPDO::write nearly does the same as EPDO::create, save for the difference that it does not care about serials. Use it when writing to tables that do not have an auto incrementing primary key.

Nested Transaction


<?php

try {

    $epdo->transBegin();

    $balance = $epdo->result("select balance from account where customer_id = ?", array(77191));

    if($balance<$withdraw) {

        throw new Exception("balance is not sufficient");

    }

    $balance = $epdo->update("account", array("balance"=>$balance-$withdraw), array("customer_id"=>77191));

    $epdo->commit();

} catch (Exception $e) {

    // do whatever you want here.

}

?>

There are numerous occasions on which money can be withdrawn off my account, so it would be reasonable to write a function "withdrawMoney" or such. However, when money is withdrawn, this will happen within a greater transaction (or should). The transaction within withdrawMoney would collide with that outer transaction, but as I never want to have the risk that money is withdrawn with no transaction at all, I want to have a nested transaction - one that inherits an existing transaction or starts one of its own:


<?php

function withdrawMoney($withdraw) {

    try {

        //transInherit inherits an existing transaction or starts a new one.

        $epdo->transInherit();

        $balance = $epdo->result("select balance from account where customer_id = ?", array(77191));

        if($balance<$withdraw) {

            throw new Exception("balance is not sufficient");

        }

        $balance = $epdo->update("account", array("balance"=>$balance-$withdraw), array("customer_id"=>77191));

        // localCommit commits if the previous transInherit actually opened a new transaction;

        // if it inherited a transaction, it will do nothing.

        $epdo->localCommit();

    } catch (Exception $e) {

        // do whatever you want here.

    }

}

?>

withdrawMoney is actually a bad example since you never want to withdraw money into empty space. However, I had functions before which had a right to exist standalone, but might have been parts of a greater transaction as well.

generic queries

Generic queries were a somewhat quick hack I came up with when writing a Nested Set class. I wanted to keep it flexible, so it would work with table layouts different from mine. Therefore, the Table class featured a few functions where you have to map generic terms (such as id) to a specific layout (such as tree_id). But to use these mappers became a mess of concatenated strings within the class itself. As Nested Set was complicated in itself, I decided to use placeholders with queries:


<?php

$query = "select <id>, <name>, <surname>, <login> from <user> where <login> = ?";

$array = array("id"=>"duId", "name"=>"duName", "surname"=>"duSurname", "login"=>"duLogin", "user"=>"dUser");

$parsed = $epdo->parseGenericQuery($query, $array);

//$parsed will now be "select duId, duName, duSurname, duLogin from dUser where duLogin = ?";

?>

Meta Information

What I missed most was a way to get meta information about tables, at least column names. EPDO::getColumns will deliver columns as an array for PostgreSQL and MySQL databases.

Comments

Please note: comments posted won't be visible immediately, as they will be checked for harmful content.