Die Flagge des Marasek

Dekostreifen

English

Aktuell Texte Der Comic Impressum Kalender Suche PHP-Klassen Container-Wizard main.s21

Kategorien

Buch
Computer
Computerspiele
Film
Geschichte
Gesellschaft
Idee
Kunst
Natur
Persönlich
Politik
Programmieren
Religion & Philosophie
Weblog
Weltpolitik
Weltsicht
{{login}}

paran: EPDO

Permalink
Vorheriger: paran: BasicNächster: Die Mittelschicht ist schuld!
Eingeordnet in: Programmieren

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"));
?>

Likewise, I wrote a function to fetch just one row:

<?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 = ?";
?>

Placeholders must contain only alphanumeric characters.

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.

Dieser Text ist Teil der Serie Paran in Action

paran: Autoloader
paran: Callback
paran: Basic
paran: EPDO

Kommentieren

Bitte beachten: Kommentare sind nicht sofort sichtbar, sondern werden erst nach einer kurzen Prüfung freigegeben, sofern keine rechtliche Beanstandung vorliegt.
Rechtlich bedenkliche Inhalte werden entweder entschärft oder nicht veröffentlicht.

* Titel  
* Nickname  
* Kommentar