DB::BeginSelect('authorid', 'body', 'posttime');
DB::From('comments');
DB::BeginWhere();
DB::FieldComp('id', $some_comment_id, DB_EQUALS);
DB::And();
DB::FieldComp('posttime', DB::NOW(), DB_GREATERTHAN);
DB::EndWhere();
DB::EndSelect();
$result = DB::Query();
[web] DB Abstraction Layer [PHP 4]
Author
I've been looking for a good database abstraction layer for PHP 4. I know about PEAR::DB and ADOdb but these systems (as well as the others I've found) seem to only abstract away the function names to perform queries while leaving you to deal with the differences in SQL dialects. If I have to deal with differences in SQL dialects, why even bother abstracting away the function names?
I'm looking for something that would allow me to do something along the lines of
If something like that doesnt exist then... I guess I'll have to go write my own... [bawling]
I'm not aware of such a system. What I do is stick to ANSI SQL and use using a switch() if there's a DB-specific way that's greatly faster than the ANSI way would be. For me on average that's on average every one out of 10-20 queries or so. The only thing that's really DB specific is the set of SQL queries that creates all initialises all the tables.
Author
Do you think that such a system would even be useful, then? Or would it just be too much work?
I use a DB-specific Class, and a "defenition" file.
I have one Class per DB type (mySQL 4 and 5 for example), and each class has its own file: cDB_mysql4.php cDB_mysql5.php
Then, on the defenition file, I define a keyword that points to the DB type I'm using.
Then, all I have to do is a:
require(K_DB_File);
And the apropriate DB Class will be called and used.
It is pretty obvious that all classes need to have the same methods, so the methods used are pretty generalistic.
I have one Class per DB type (mySQL 4 and 5 for example), and each class has its own file: cDB_mysql4.php cDB_mysql5.php
Then, on the defenition file, I define a keyword that points to the DB type I'm using.
Then, all I have to do is a:
require(K_DB_File);
And the apropriate DB Class will be called and used.
It is pretty obvious that all classes need to have the same methods, so the methods used are pretty generalistic.
I would never use such a system (as the OP suggested) because I never have to target multiple databases and if I did I (basically) write portable SQL so such a system would be overkill.
There are a few MySQL things I use a lot, such as LIMIT. That could easily be abstracted in the query:
$db->setLimit($start, $length);
$db->query("SELECT * FROM foo");
If I came across something else that was very useful but non-standard, I'd try my best to write a similar function. It could even be something generic like:
$db->query($db->normalize("SELECT #SOME TOKEN#(foo) FROM bar"));
Where "DB::normalize" would be some function that would search/replace tokens to the vendor specific call. Obviously you wouldn't call that function unless you knew there was something special in the query.
Things like PEAR::DB are handy to me because I can do $db->getRow(), $db->getCol(), $db->getOne(), $db->getAll(), etc. I use a DB wrapper for that reason, not to target multiple databases.
There are a few MySQL things I use a lot, such as LIMIT. That could easily be abstracted in the query:
$db->setLimit($start, $length);
$db->query("SELECT * FROM foo");
If I came across something else that was very useful but non-standard, I'd try my best to write a similar function. It could even be something generic like:
$db->query($db->normalize("SELECT #SOME TOKEN#(foo) FROM bar"));
Where "DB::normalize" would be some function that would search/replace tokens to the vendor specific call. Obviously you wouldn't call that function unless you knew there was something special in the query.
Things like PEAR::DB are handy to me because I can do $db->getRow(), $db->getCol(), $db->getOne(), $db->getAll(), etc. I use a DB wrapper for that reason, not to target multiple databases.
I agree with the general sentements here. Write in ANSI SQL and you won't go wrong. If your particular SQL vendor doesn't support a call, tell them about it, and write in a switch() statement to deal with it. MySQL is notorious for fudge factors on certain queries, but other than that, you should be fine; SQL was designed to be database vendor agnostic.
As for using PEAR::DB or ADODB, I don't really like either, but if it fits you, go ahead and use them. PHP5 also has an even better way of dealing with this problem; PHP Data Objects (PDO). I would recommend them to anyone who can support PHP5.1 as the new standard. A lot of people don't like it, but then again a lot of people didn't like C++ when it came out, and even more people didn't like Object C (which is now the language used for Cocoa by Apple). Sometimes upgrading is a good thing ;).
As for using PEAR::DB or ADODB, I don't really like either, but if it fits you, go ahead and use them. PHP5 also has an even better way of dealing with this problem; PHP Data Objects (PDO). I would recommend them to anyone who can support PHP5.1 as the new standard. A lot of people don't like it, but then again a lot of people didn't like C++ when it came out, and even more people didn't like Object C (which is now the language used for Cocoa by Apple). Sometimes upgrading is a good thing ;).
Quote:Original post by Colin Jeanne
Do you think that such a system would even be useful, then? Or would it just be too much work?
I don't know about actually creating such a system (looks like loads of work) but I would never even use it because of the work involved. Writing a syntax like your original suggestion for each and every SQL query seems like far more work than the occasional special casing of regular SQL queries.
The only thing that's handled like that in my DB system is the MySQL limit statement (As KonForce already mentioned). E.g.
$sql = ' ... ';
DB::Query($sql);
DB::QueryLimit($sql, 0, 10);
Author
Prozak, that is exactly the method I am using currently. The database and result classes I am using are (with some improvement) here.
Author
Hmmm... I guess I'll continue to use the database class I've been using all along.
Thanks for the comments everyone!
Thanks for the comments everyone!
Using the DAO pattern can help with some of this as well. Create a DAO for logical data entity, then break the sql queries out into a seperate properties file.
Your property file would look something like this:
view_book_by_id=select * from books where id ='?'
list_of_books=select * from books
... etc etc
When all the sql is away from the code, it's easy to just search and replace as needed.
Your property file would look something like this:
view_book_by_id=select * from books where id ='?'
list_of_books=select * from books
... etc etc
When all the sql is away from the code, it's easy to just search and replace as needed.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
