Database Abstraction

[RMcLeod]

Okay so I am writing an OOP database abstraction layer that can handle any type of database. (Don't get me started on why I'm not using one of the many perfectly good ones available).

 

So far I've basically got a Db class which has a single factory method for returning the correct type of adapter.

 

I'm now about to write the Abstract Adapter class and my question is this. From a users point of view which is best:

 

a) Seperate functions for different types of db query each returning something based on the type of query e.g.

<?php
$db->insert_row($sql); //returns last insert id.
$db->update_row($sql); //returns affected rows.
?>

 

b) One overall query function that stores results in member varaibles e.g.

<?php
$db->query(/* insert statement */); sets $db->insert_id
$db->query(/* update statement */); sets $db->affected_rows
?>

 

And another question should making strings safe for use in an sql query be the job of the coder using the API or the API itself?

[nloding]

Do what you like best.  I personally have a query() function that returns a result, and an execute() function used for INSERT and UPDATE commands.  The query() function stores an internal $_lastResult variable for ease of use, and execute() just executes the query.  The usual error handlers are in there.

[deadimp]

See how you yourself program and use this API. If you find yourself writing out repetitive insert / update / delete / whatever statements in 'raw' queries, you might want to make a wrapping function for it.

If you're going to make this adaptable for multiple database drivers / types / whatcha-call-it, keep in mind that there may be functional differences in how they operate, especially in query syntax.

[RichardRotterdam]

(Don't get me started on why I'm not using one of the many perfectly good ones available).

plz do i would love some nice links of a descent db class that manages postgress mysql  and sqlite. I know i could google it up

but just curious about everyones experience with db classes.

 

also i do agree that the select update insert are all just a sql query only thing that is probably different is to get a single row from a db instead of multiple ones

[RMcLeod]

please do i would love some nice links of a descent db class that manages postgress mysql  and sqlite. I know i could google it up

but just curious about everyones experience with db classes.

 

A really good one that is useable with mssql, mysql, oracle, postgre and sqlite comes with the Zend Framework.

 

I've also taken their approach of having different functions for differnet types of query. If you like I can give you access to my now finished API, I've only included adapters for MySQL and MSSQL so far, but it should be easy enough for you to see how to write new adapters if you're comfortable with the abstract factory pattern.

[nloding]

Please, if you could upload it somewhere, I'd love to read it.

[Highlander]

how about reading about PDO in PHP?

[RMcLeod]

Please, if you could upload it somewhere, I'd love to read it.

 

Here's a link to the files http://www.richardmcleod.co.uk/db_api.zip.

 

The Db class and Abstract class are documented, if you have any questions feel free to ask.

[deadonarrival]

It's always more fun to make your own

 

Personally I'd have a query() function which runs the query, and use insert() and select() functions to allow me to simplify the user process for these. Eg select($what,$table,$condition,$limit); and insert($table,$keys,$vals); just to simplify the users need for sql.

 

Then if the user chooses to use sql directly, they can do so, or they can just select items directly from the DB. I would also provide access to select a single field from a single row with select_row($table,$field,$condition);

 

It's just a case of deciding how much you would like to simplify the users experience, and how deep you want you functionality to be.

 

"And another question should making strings safe for use in an sql query be the job of the coder using the API or the API itself?"

I'd consider it partially the job of the API, although in my framework I seperate the cleaning of variables from the database entirely, if I was making a standalone db abstraction layer I'd include a basic cleanup.

 

For example

function clean($var)
{
    $var = stripslashes(mysql_real_escape_string($var));
    return $var;
}

Should be enough to make the database safe - let the user worry about whether there's html or javascript in there, your job abstracting the database is to worry about the database, nothing more.

[Daniel0]

$var = stripslashes(mysql_real_escape_string($var));

 

Those two functions will counteract each other

 

It accomplishes nothing except wasting resources.

[deadonarrival]

sorry, i meant to do it the other way around

mysql_real_escape_string(stripslashes($var));

 

The aim being to remove any escaping which the person using the script may have already done (we don't want to double escape and turn our ' into \\\'), and then add it in.

 

Yeah, doesn't make sense that way round

[aschk]

The days are gone where you would have rolled your own PHP database abstraction layer. This has since been superceded by PDO. Use it, there's no excuse not to.

[keeB]

With all due respect, aschk, I disagree.

 

Probably because I can, not because I should

 

I think when designing an adapter layer, you'd really be hard pressed to come up with something as feature rich as PDO. But, what if you don't need PDO's features for your app? In that case you're adding unnecessary bloat and using 10% (made up) which could easily be overcome with your own code, which you understand better, which allows you to grow and expand better.

 

There's always an argument for build vs. buy. Does the component area need to be highly customized or is it a generic piece that doesn't even focus on your core product roadmap? It's not an easy question to answer.

 

That being said, I congratulate RMcLeod on getting a concrete product complete If it were back to the designing phase, would you be doing anything different? Any limitations you have found with your design?

 

Personally, I am a fan of ORM's (Object-Relational Mapping) which persists model objects to the configured database. A common ORM is called Propel http://propel.phpdb.org/trac/

 

Of course, since I've written my own, I don't have all of the configuration fluff that's they have (good features, I call it fluff because I don't distribute it,) but the core concept is the same. It was enough of an argument when I needed an ORM that I could create my own in the time it would take me to fully leverage features provided by existing ORM like Propel.

 

When you get to the core of OOP, being able to persist an entire object and having your relational database schema match your object model really takes out the confusion of understanding and maintenance.

 

Good luck!

[Xeoncross]

This has since been superceded by PDO. Use it, there's no excuse not to.

 

PDO wastes so much computer resources it isn't funny. It is as bad as the blog system wordpress...

[448191]

This has since been superceded by PDO. Use it, there's no excuse not to.

 

PDO wastes so much computer resources it isn't funny. It is as bad as the blog system wordpress...

 

I'd be interested to know where you gained this wisdom. Do you even know what PDO is?

 

@KeeB: What's so feature rich about PDO? It's just your basic common interface. It's not even a 'real' DBAL.

 

 

[corbin]

I'm personally not a fan of PDO's layout....

 

I also hate how it does lowerUpper() functions.  Camel case can die in a fire.

 

And I must agree with 448191.  PDO doesn't emulate anything but transactions.... Zend_Db is more feature rich than PDO.  (Actually, one of the Adapters Zend_Db can use is overlayed on PDO).  (Zend_Db was just a random example)

 

Also, I don't like how PDO is an extension, especially that it's not enabled by default.  If I coded something around PDO then my host didn't have it enabled (would be dumb of me not to check) I would be pissed.

 

Anyway, my $.02.

 

I say go ahead and make your own DB API.  Worst case, if it sucks, you'll be able to see why it sucks, and you'll gain knowledge ;p.

[Daniel0]

Also, I don't like how PDO is an extension, especially that it's not enabled by default. If I coded something around PDO then my host didn't have it enabled (would be dumb of me not to check) I would be pissed.

 

In PHP 5.1+ PDO is included by default and on my VPS (Debian Etch), PDO was installed by default when I installed the package using APT. Furthermore, all functions that deal with database connectivity are located in extensions.

[corbin]

Hrmmm touché.  But is PDO enabled by default?  (Then again, none of the DB exts are enabled by default if I remember correctly.)

 

I didn't stop and think about all of the DB functions being in extensions.... x.x

[Xeoncross]

I'd be interested to know where you gained this wisdom. Do you even know what PDO is?

 

Yes, and unlike many people before I start any project I run benchmarks (like ini vs yaml) to see what I can expect from something coded by another person. PDO is just too slow for the "features" that it offers (which are irrelevant to most people running small php systems).

 

Run your own benchmarks and just try to prove me wrong. 

[448191]

Run your own benchmarks and just try to prove me wrong. 

 

Ha. Well that's turning the world upside down, isn't it. Because...

 

1) You make a claim, you back it up. Provide us with the test script you use so we can see if you are right, or full of it.

2) You say PDO is slow/expensive, but compared to what? Compared to vendor specific extensions like mysqli (that I can believe), or compared to a DBAL written in PHP (that I have a hard time believing).

3) When you directly compare a whole application (Wordpress) with a PHP extension, people are going to think you are full of shit.

 

Bring it on.

 

---

 

About PDO and the difference with a DBAL, you need look no further than the manual for this info:

 

PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data. PDO does not provide a database  abstraction; it doesn't rewrite SQL or emulate missing features. You should use a full-blown abstraction layer if you need that facility.

 

 

 

 

 

 

[Xeoncross]

1) You make a claim, you back it up. Provide us with the test script you use so we can see if you are right, or full of it.

 

Fair enough. I will search for the script I used a while ago to determine this.

 

2) You say PDO is slow/expensive, but compared to what? Compared to vendor specific extensions like mysqli (that I can believe), or compared to a DBAL written in PHP (that I have a hard time believing).

 

Compared to other extensions and a DBAL written in PHP actually 

 

3) When you directly compare a whole application (Wordpress) with a PHP extension, people are going to think you are full of shit.

 

Wordpress is almost the most bloated piece of software I can think of. It uses 10Mb of ram just to show the index (1 post) on a vanilla install.

Therefore, whenever I need compare another piece of software to something slow - I just use wordpress as so many people understand it's bloated.

 

Of course Wordpress has nothing to do with DBAL's.

 

Bring it on.

 

Feisty 

 

 

[corbin]

Wow....  I just downloaded/installed WP because I figured there was no way it uses 10MB of memory with just a bare install....  It use s 7.6 x.x

[Xeoncross]

Wow....  I just downloaded/installed WP because I figured there was no way it uses 10MB of memory with just a bare install....  It use s 7.6 x.x

Did you use the PHP core to test memory or did you rely on memory_get_usage()?

 

I used Xdebug as it records the WHOLE process worth of memory.

I think that using the function it only showed like 8.5MB.

 

But that is still a little better, maybe wordpress is starting to look at performance 

[corbin]

I just used memory_get_usage(true).  I didn't care much about the memory usage of the PHP core.

[Xeoncross]

I didn't care much about the memory usage of the PHP core.

 

Well you might be surprised if you make use of different lib's during your scripts course. 

Since each system out there uses different ways of processing and handling data the core lib's should count as part of the scripts resources.