Page 1 of 1

Database comparisons

Posted: Sat Apr 01, 2006 4:06 pm
by alex.barylski
So I've looked into the code behind PEAR MDB, AdoDB and a few other PHP database abstraction layers in order to get a glimpse of what each one does and how each is better or worse than the others...

I have to say, most I've reviewed are quite poorly written in comparison to MDB or AdoDB.

There are actually more caveat's than I originally thought there would be when taking on the project of writing a DB abstraction layer :)

MDB and Ado have shown me this...

So now I'm looking for any quality articles you database experts may know of which depict the differences between popuplar databases?

For example, I had no idea (only ever using MySQL) that primary keys don't exist in many other databases, they use a technique called sequencing???

Also, MySQL doesn't handle "affected rows" for DELETE statements so MDG uses a tricky little hack to circumvent this issue in the MySQL driver :)

So I ask, anyone know of some good articles (hopefully brief - outlining differences) I should be aware of so I can accelerate my writing an abstraction layer???

Cheers :)

Posted: Sat Apr 01, 2006 4:38 pm
by John Cartwright
There are actually more caveat's than I originally thought there would be when taking on the project of writing a DB abstraction layer Smile

MDB and Ado have shown me this...
Why reinvent the wheel? You said it youself, there are already excelletent db abstraction layers that has addresse many issues.

Re: Database comparisons

Posted: Sat Apr 01, 2006 4:52 pm
by timvw
Hockey wrote: For example, I had no idea (only ever using MySQL) that primary keys don't exist in many other databases, they use a technique called sequencing???
Actually, primary keys exist in all dbms that i know.

MySQL uses an AUTO_INCREMENT attribute that isn't standard, with MS-SQL the same feature is called "Identity", ...
Hockey wrote: So I ask, anyone know of some good articles (hopefully brief - outlining differences) I should be aware of so I can accelerate my writing an abstraction layer???
Sorry, can't help you with that. As you've already noticed, each dbms talks its own dialect and there isn't a real standard in sql worl. Time for something better :)

Re: Database comparisons

Posted: Sat Apr 01, 2006 5:09 pm
by AKA Panama Jack
Hockey wrote:I have to say, most I've reviewed are quite poorly written in comparison to MDB or AdoDB.
You might want to check out my ADOdb Lite Abstraction Layer. The link is in my signature. It supports most of what is in ADOdb with more features constantly being added.

The great thing about it is how it was written. It is a modular based layer where only the code modules you need are ever loaded. It uses 250-300% fewer resources (memory and CPU load) while being a lot faster Benchmark.

It has become a very, very popular replacement for ADOdb using the same command syntax. Still not as many downloads as ADOdb but 13,000+ in the short time it has been available is respectable. :)

Re: Database comparisons

Posted: Sat Apr 01, 2006 5:10 pm
by alex.barylski
timvw wrote:
Hockey wrote: For example, I had no idea (only ever using MySQL) that primary keys don't exist in many other databases, they use a technique called sequencing???
Actually, primary keys exist in all dbms that i know.

MySQL uses an AUTO_INCREMENT attribute that isn't standard, with MS-SQL the same feature is called "Identity", ...
Hockey wrote: So I ask, anyone know of some good articles (hopefully brief - outlining differences) I should be aware of so I can accelerate my writing an abstraction layer???
Sorry, can't help you with that. As you've already noticed, each dbms talks its own dialect and there isn't a real standard in sql worl. Time for something better :)
I just realized my mistake...I meant AUTO_INCREMENT...not primary keys :P

Unfortunately it appears that way :)

Thankfully (not being a DBA) every application I have ever written has likely relied on fairly basic SQL, enough so that it would port over to other systems without hassle, except maybe, by the sounds of things data types.

The more I read the more I realize there is quite a bit of difference between RDBMS...

Hopefully after I read the few articles I just found on the differences between popular systems...I can again review PEAR DB and AdoDB source and find improvments, so I'm not exactly re-inventing the wheel...

Cheers :)

Posted: Sat Apr 01, 2006 5:21 pm
by alex.barylski
Jcart wrote:
There are actually more caveat's than I originally thought there would be when taking on the project of writing a DB abstraction layer Smile

MDB and Ado have shown me this...
Why reinvent the wheel? You said it youself, there are already excelletent db abstraction layers that has addresse many issues.
PEAR and AdoDB are huuuuuge...although I found a benchmark and it didn't appear to add much of ahit in performance compared to native function calls, the codebase is still massive for all that it does...

Tons of error checking, etc...

I don't need that stuff for my purposes...I'm in need a fine tuned abstraction layer, because I'm using it in yet a further abstraction. I'm not one to typically complain about performance, in this case, because the advantage of abstraction is worth the investment in more powerful hardware.

I have my reasons for re-inventing the wheel.
1) I needed a good reason to read up on RDBMS
2) Smaller foot print
3) I like using code I copyright as my own in my commercial projects - I do however use some LGPL projects, I still have plans on re-writting them.
4) Where is the harm in options? Yet another DB abstraction layer can't be bad...

When and if I release my version, I'll be sure to do my homework and release docs explaining differences, design decisions and comparison between mine and others, even the crappy ones - so people can make educated choices as to why they might use mine... :)

I don't know why, but I dislike PEAR...although again I have used it (or some of it)...I dislike it...

Mostly, its because when I put my name on anything I like things to be done exactly as I do it...not nessecarily implementation, but interface for sure, variable naming, file naming & structure, commenting, etc... :)

I'm just anal about things like that :)

Plus I've already written a fairly large library of code which I use in projects and I don't yet have an abstraction layer, so heres my chance :)

Posted: Sun Apr 02, 2006 2:25 pm
by alvinphp
I don't know any good articles off the top of my head, but I would recommend playing with PostgreSQL. It is a full feature database. I haven't worked with MySQL in a while, but when I did it was severely lacking in functionality. It was something between MS Access and Oracle. PostgreSQL is great in that it does everything most major databases do and it is free.