Database Abstraction Layer

Coding Critique is the place to post source code for peer review by other members of DevNetwork. Any kind of code can be posted. Code posted does not have to be limited to PHP. All members are invited to contribute constructive criticism with the goal of improving the code. Posted code should include some background information about it and what areas you specifically would like help with.

Popular code excerpts may be moved to "Code Snippets" by the moderators.

Moderator: General Moderators

Post Reply
User avatar
VirtuosiMedia
Forum Contributor
Posts: 133
Joined: Thu Jun 12, 2008 6:16 pm

Database Abstraction Layer

Post by VirtuosiMedia »

I had started a thread about this before here, but I'd actually like to get the code itself critiqued if anyone would be so kind.

This is a very, very early pre-release of a database abstraction layer. It has some similarities to a few different patterns, but doesn't follow any of them exactly as far as I know. The best way to describe it may be to call it an SQL generator. Right now it only works for MySQL and it doesn't currently support views or transactions, but it does support joins and subqueries and a few other basic things. There is another class (not included in this package) that handles data definition language (DDL) statements that will also eventually accompany the package, as will support for other DBMS's like PostgreSQL, Oracle, MS SQL, DB2, etc. More about it can be read in the readme.txt file, in the comments in the code, and in the thread above.

For a test script, I included a limited user management file using the abstraction layer.

Any comments, critiques, suggestions, or questions would be greatly appreciated. Thank you in advance.
Attachments
VMDB.zip
(8.88 KiB) Downloaded 166 times
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Database Abstraction Layer

Post by JAB Creations »

I did a little searching about database abstraction layers and it seems DAL isn't popular with many experienced programmers because it adds an additional layer of complexity that isn't really needed by those with more programming experience. I'm not trying to put your work down but I am trying to understand what the goal is, why people would want to use it? Does it potentially make inexperienced programmers (such as myself) who start using it without direct language-->database experience dependent on the DAL?
User avatar
VirtuosiMedia
Forum Contributor
Posts: 133
Joined: Thu Jun 12, 2008 6:16 pm

Re: Database Abstraction Layer

Post by VirtuosiMedia »

Thanks for the reply. I guess the best way to answer your questions is to explain the situation for which I need it. I wanted to write an application that could be portable across different database types. I also wanted to allow third party developers to write extensions for it. However, I didn't want to have a different version of the core application for each DBMS and I didn't want to force developers to have to write multiple versions of their extensions either.

What you see here is a sort of first draft of my attempt to solve those problems. It definitely isn't complete. I'm working on a PostgreSQL layer right now and eventually I'll write for Oracle, MS SQL, and DB2 as well...and SQLite might be a possibility, but I'm really not too keen on its security. There is also another class that wasn't included in the download that handles table creation, etc. The hope is that once I have completed classes for each of those databases, I'll be able to write my application code once and have it portable across all of those DBMS with just a change of the config file.

Obviously, there are some drawbacks to this approach. Like you mentioned, it adds another layer to the application. However, I've noticed quite a few applications that have some sort of DAL wrapper and if you look at Zend_DB, this is smaller than that. I still haven't tested for added overhead, so we'll see if there are any serious performance issues and if it can scale. Another drawback is that the classes will be limited to some basic functionality that is shared or can be emulated across the DBMS above. This might be a little frustrating for programmers who want to do much beyond CRUD, but the classes can be extended if they need to do so.

I'd like to think that there are a few advantages to using it, though, too. There is the portability factor. It's not completely injection safe, but it is a little more secure than just using straight up queries on the inserts, at least (if anyone has specific suggestions for making it completely injection safe for all queries, please let me know...it may be something I examine a lot more). I also personally like the syntax, though that's a completely biased.

I don't know if this is the absolutely best approach to this situation, but it's what I came up with after a lot of research and a very long discussion on another forum (which you can read here if you're interested). Like I said, it's not finished yet and isn't really even an abstraction layer yet because I only have the MySQL layer done, but I wanted to get some feedback from some experienced developers before I got too far.

Will it be a solution for everyone when it's finished? No, not everyone will need or want to write portable code and optimizing for a specific database will be much easier without a DAL. I honestly hadn't given thought to whether or not newer programmers would become dependent on it rather than learning the database specific functions. I might be wrong, but from what I've seen of beginning PHP programmers, this wouldn't really solve a problem they are facing and so they probably wouldn't need it or use it. However, it solves a problem for me that I couldn't find the answer to anywhere else.

If you got a chance to look at it, did you have any suggestions, comments, or questions on the code itself?


Last bumped by VirtuosiMedia on Tue Jul 29, 2008 6:58 am.
Post Reply