Re: DB, MDB2, PDO - Future of DB-Abstraction in PEAR

Lukas Smith wrote:

Andreas Korthaus wrote:

But If I look at PDO, the only additional functionality I need is portable LIMIT support and portable sequences (like $db->nextId()).

Well limit is slightly more tricky, but atleast nextID() can best be handled inside the schema, this way you can get any popular database to produce auto increment like behaviour. This is exactly what the lastInsertId() method in PDO tries to facilitate. This leaves LIMIT support. MySQL, SQLite and PostGreSQL all support this natively (though PostGreSQL's implementation sucks AFAIK). For all others there are no beautiful solutions. However since PDO supports cursors you can get around alot of the network traffic which is the key aspect with LIMIT. Beyond that you can play around with single function that rewrites the SQL using the following recommendations: http://troels.arvin.dk/db/rdbms/#select-limit

If I think about it again, the problem could be reduced to high offset values if you use unbuffered results. And those are rarely used in real world applications I think. Who wants to browse through thousends of result-sets with a large amount of data using a pager... even search-engines aren't used with really high offsets to make network-traffic a real problem? So you only need a solution in very special cases. So the only problem with using PDO directly are the two feature requests mentioned earlier. I think I should use a very simple wrapper class or child class, to make it easy to add functionality, if I run into issues I did not think about now. Such a class could be extended by another, db specific class, if I have special problems with a specific database. Perhaps I will create a wrapper class for PDO which could be called statically using a singleton, I only have to figure out how to make it possible two use more than one instance (writing/reading with differen rights) without passing a $dsn or something like that in each call. Perhaps I have to use 2 classes (DBWrite, DBRead), DBWrite wraps PDO methodes necessary to manipulate data, DBRead needs to wrap methodes to read data. So I can call DBWrite::exec() and DBRead::query() everywhere in my applications. Or I use one DB class which could detect if it's a manipulating query or not by parsing the first few characters from SQL. What about something like: if (preg_match('/^\s*U|I/i', $sql)) { $writing = TRUE; } else { $writing = FALSE; } Of course the users need minimal permissions. best regards, Andreas