Abstracting a database class

[Full Decent]

Hey all!

I've been using PHP for a while and I'm hearing that I should look into
using it OO. I'm looking into making changes to the program Camera Life
(http://fdcl.sf.net). I want to abstract the connection to the database
which is currently MYSQL and maybe implement another database, or just
leave it open for addition. If I get lucky and get the hang of this,
I'd also like to abstract calls to GD.

So firstly, is there a good solution to this problem? I've seen many
examples, but many of them are incomplete or simply academic. Also,
will I lose any functionality by doing it this way? I am currently
using a complicated SELECT statement with a CONCAT. Will this be
possible with the abstracted class?

Thanks for your consideration,
FD

[Balazs Wellisch]

Have a look at this: http://adodb.sourceforge.net/ The abstraction is
already done for you. Or, you can look on pear.php.net for their DB class
too.

Balazs
"Full Decent" <fu********@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Hey all!

I've been using PHP for a while and I'm hearing that I should look into
using it OO. I'm looking into making changes to the program Camera Life
(http://fdcl.sf.net). I want to abstract the connection to the database
which is currently MYSQL and maybe implement another database, or just
leave it open for addition. If I get lucky and get the hang of this,
I'd also like to abstract calls to GD.

So firstly, is there a good solution to this problem? I've seen many
examples, but many of them are incomplete or simply academic. Also,
will I lose any functionality by doing it this way? I am currently
using a complicated SELECT statement with a CONCAT. Will this be
possible with the abstracted class?

Thanks for your consideration,
FD

[Janwillem Borleffs]

Full Decent wrote:

I've been using PHP for a while and I'm hearing that I should look
into using it OO. I'm looking into making changes to the program
Camera Life (http://fdcl.sf.net). I want to abstract the connection
to the database which is currently MYSQL and maybe implement another
database, or just leave it open for addition. If I get lucky and get
the hang of this, I'd also like to abstract calls to GD.

You might want to take a peek at the following:

http://www.php.net/dbx/

Or for PHP5:

http://www.php.net/pdo/
JW

[David Haynes]

Full Decent wrote:

Hey all!

I've been using PHP for a while and I'm hearing that I should look into
using it OO. I'm looking into making changes to the program Camera Life
(http://fdcl.sf.net). I want to abstract the connection to the database
which is currently MYSQL and maybe implement another database, or just
leave it open for addition. If I get lucky and get the hang of this,
I'd also like to abstract calls to GD.

So firstly, is there a good solution to this problem? I've seen many
examples, but many of them are incomplete or simply academic. Also,
will I lose any functionality by doing it this way? I am currently
using a complicated SELECT statement with a CONCAT. Will this be
possible with the abstracted class?

Thanks for your consideration,
FD

I usually implement this with a double abstraction.

The first is a database factory to provide concepts such as SQL query
support, database connection sharing, error handling, etc.

The second is a database interface implementation that isolates all the
SQL in one class. This implementation is the only class that
instantiates the factory and uses it.

This way, I can change database factories (to connect to different
databases) with ease and, since the interface implementation contains
all the SQL, there is only one file I need to scan to adjust for the
variances in the SQL languages. (e.g. limit 1 in MySQL vs. count = 1 in
Oracle)

Is abstracting the database communications (with a factory) and
abstracting the interfaces a good idea? I certainly think so. In fact,
abstracting all communications is a good idea in my book. I use it for
all my SOAP work too.

-david-

[Lüpher Cypher]

Full Decent wrote:

Hey all!

I've been using PHP for a while and I'm hearing that I should look into
using it OO. I'm looking into making changes to the program Camera Life
(http://fdcl.sf.net). I want to abstract the connection to the database
which is currently MYSQL and maybe implement another database, or just
leave it open for addition. If I get lucky and get the hang of this,
I'd also like to abstract calls to GD.

So firstly, is there a good solution to this problem? I've seen many
examples, but many of them are incomplete or simply academic. Also,
will I lose any functionality by doing it this way? I am currently
using a complicated SELECT statement with a CONCAT. Will this be
possible with the abstracted class?

Thanks for your consideration,
FD

I would definetely consider it :) For instance, if you use mysql and
then need to move the site to another server with a different dbms, I
bet you'd hate to change all those mysql_* calls :)
So, off the top of the head, I'd go with something like

class DB {
function DB($host,$db,$user,$pwd) {
$this->host = $host;
$this->db = $db;
$this->user = $user;
$this->pwd = $pwd;
}

function connect() {
// abstract
}

...

function query($query) {
// abstract
}

...
}

class DBMySql extends DB {
function connect() {
mysql_connect($this-host,$this->user,$this->pwd);
mysql_select_db($this->db);
}
...
}

class DBPostgreSql extends DB {
function connect() {
pg_connect("host=$this->host dbname=$this->db user=$this->user
password=$this->pwd");
}
...
}

and so on. Then in any place you want to use database, assume you are
using class DB:

....
/**
* @var DB
* @access private
*/
var $db;
....

Then instantiate it to whatever you use:

....
$this->db = new DBMySql("localhost","test","user","pwd");
....
$this->connect(); // works for any database now
....

Then, if you move to another database, simply change which class you
instantiate.

As for the SQL statments, I'd say there could be a problem if the syntax
differs in different dbms. For instance, in Oracle to get current date
one would call sysdate(), and in ms-sql - getdate(). In this case you
may want to consider aliases for function names which are replaced when
query() method is called.
luph

[Balazs Wellisch]

This way, I can change database factories (to connect to different
databases) with ease and, since the interface implementation contains all
the SQL, there is only one file I need to scan to adjust for the variances
in the SQL languages. (e.g. limit 1 in MySQL vs. count = 1 in Oracle)

Hmmm, ADODB already takes care of that for you. It also deals with date
formatting and other issues. Plus it provides useful features like caching
and SQL logging. Why reinvent the wheel?

Balazs

[Chung Leong]

Full Decent wrote:

Hey all!

I've been using PHP for a while and I'm hearing that I should look into
using it OO. I'm looking into making changes to the program Camera Life
(http://fdcl.sf.net). I want to abstract the connection to the database
which is currently MYSQL and maybe implement another database, or just
leave it open for addition. If I get lucky and get the hang of this,
I'd also like to abstract calls to GD.

That's not a good approach. There is no point in abstracting calls to
the database, as the bulk of the differences between databases lies in
the sematics of the queries. What you really want to do is abstract the
entire data retrieval process. You application code doesn't need to
know that it's getting its data from a database. The data just needs to
be coming from somewhere: a MySQL database, a MS-SQL database, an
XML-file, or even a SOAP-compatible web-service. If your application is
agnostic in regards to data source, then it's automatically database
independent.

[David Haynes]

Balazs Wellisch wrote:

This way, I can change database factories (to connect to different
databases) with ease and, since the interface implementation contains all
the SQL, there is only one file I need to scan to adjust for the variances
in the SQL languages. (e.g. limit 1 in MySQL vs. count = 1 in Oracle)

Hmmm, ADODB already takes care of that for you. It also deals with date
formatting and other issues. Plus it provides useful features like caching
and SQL logging. Why reinvent the wheel?

Balazs

ADOdb is an example of an abstract database factory, as is PEAR DB. It
was not my intention to imply that there was a need to write the
factory, just that it was a good idea to isolate the concept of the
factory from the concept of the SQL code driving the factory, and, in
turn, to isolate the SQL code from the rest of the application space.
Sometimes, due to the databases I am talking to and the in-house
standards in place, it is necessary to write my own factory. This does
not diminish the advantages of the two abstraction layer model.

In other OO languages, Java for example, it is common to have a caching
database factory and a database bean - which is an interface
implementation - that contains the SQL code for the application.

-david-

[Full Decent]

I like this idea and the possibility of running "DB queries" against a
real DB, the filesystem, or the network. Do you know of existing
classes that provide this level of abstraction beyond ADODB?

[Full Decent]

I am going to start by porting to ADODB. Later I will see if I can
abstract further by separating the accesses as Leong suggested.

Thanks!

[Chung Leong]

Full Decent wrote:

I like this idea and the possibility of running "DB queries" against a
real DB, the filesystem, or the network. Do you know of existing
classes that provide this level of abstraction beyond ADODB?

It's just a matter of separating the concerns of your code. Doesn't
really require special logic. For example, say you need to retrieve a
article from the database, instead of running the query in the
application code itself, call a function to do it:

$message = GetMessageById($id);

or

$message = GetMessageByTitle($title);

or

$messages = GetMessagesByDate($start, $end);

If you want to get fancy, use a class factory

$messages = $message_factory->Load($criteria, $offset, $length);

There are many ways to do this. The bottomline is to isolate the data
save/retrieval code, so that you can swap in a different mechanism by
simply including a different file.

[Manuel Lemos]

Hello,

on 01/03/2006 07:38 PM Full Decent said the following:

I like this idea and the possibility of running "DB queries" against a
real DB, the filesystem, or the network. Do you know of existing
classes that provide this level of abstraction beyond ADODB?

You may want to take a look at Metastorage. This is a tool that
generates code for classes of objects that perform what is known as
object-relational mapping. In simple words, you can abstract table rows
and treat them as objects.

Besides the abstraction level that Metastorage generated code provides,
it also makes you much more productive. You just need to describe the
classes, variables, validation rules, relationships between classes and
functions that you need manipulate your objects. Then Metastorage
generates all the code of the classes that you describe in a few seconds.

Metastorage can also generate classes to install your database schema,
report data extraction classes and classes to generate and process
themed Web forms to perform common types of object manipulation operations.

A similar effort to write, test and debug equivalent code manually would
take days, weeks of months to be ready. The more complex your project
becomes, the greater are the productivity gains that Metastorage provides.

Here you may find more information about Metastorage:

http://www.metastorage.net/

Here are some screenshots of Metastorage generator Web interface and
generated application forms using different themes
http://www.meta-language.net/screenshots.html

Here is a tutorial:
http://www.meta-language.net/metastorage-tutorial.html
--

Regards,
Manuel Lemos

Metastorage - Data object relational mapping layer generator
http://www.metastorage.net/

PHP Classes - Free ready to use OOP components written in PHP
http://www.phpclasses.org/