Database Abstraction Pointless?

[KillAllDash9]

There are currently a lot of libraries available for PHP that provide 'database abstraction'. Some of them are good, some of them aren't, and they run the range from lightweight/efficient classes like the database classes in Eclipse, to feature-rich but bloated code like the classes in PEAR. What the majority of the classes promise is database independence. The idea is that if your code is originally written using database X as a backend, but two months down the line you discover you need to use database Y, you should be able to make the switch without having to edit every file in your application.

IMO, this is a very noble yet entirely useless endeavor in its present incarnation. The problem is that, although these libraries abstract the calls to the built-in PHP functions for databases, they do not abstract the SQL itself. Unfortunately, only the very basic SQL syntax is similar (not identical) across different database systems. So if I use database abstracion and write my application for MySQL and then later want to switch to PostgreSQL or Oracle, although I won't have to change most of the method calls in my scripts, I will still have to change almost all of the SQL queries! So you are basicly touching most of the files in your application anyway.

This isn't to say that creating classes to access your database is completely pointless--doing so still gives you the advantages of encapsulating all of the DB functionality into OO structure and makes your code easier to understand. However, I would like to describe an approach that works pretty well for me (although I'm still ironing it out).

One thing I realized is that if I have to rewrite my queries anyway, the code needed to achieve the so-called 'database independence' is just wasted overhead. So instead of writing a class that could switch functionality between several database sytems, I write my database classes to be specific to one system. I generally end up with two or three classes depending on the database being used: DBConnection, DBQuery, and sometimes DBLargeObject (useful with PostgreSQL, for instance).

DBConnection defines methods for things related to the connection itself as well as one-off commands such as INSERT, UPDATE, and DELETE that do not return a result set. It handles the opening and closing of the connection, and it also handles transaction support for a database that uses transactions.

DBQuery defines methods for executing a query and returning the result set, and also methods for doing common things with the result set such as returning each row as an array or object, etc.

DBLargeObject handles BLOBS for databases that support a seperate namespace for these datatypes. It lets you import and export files into a BLOB, return the results directly to a browser, etc.

Because these classes contain code only for a specific database system, they do not need to spend one microsecond of time abstracting. For commands that are similar between all SQL databases, the method names will be the same (so you can switch between databases without having to learn a whole new set of semantics for the new class), but the functionality may be quite different internally.

So now I have my database functions contained in a class. What does that gain me besides encapsulation at this point? Not much. I would still have to change every part of my code to switch databases at this point.

The solution is to make better use of OOP. In my scripts, instead of calling database methods all over the place, I use objects for everything that deals with persistant data.

For instance, lets say my program tracks information about user accounts. I would create two classes: User and UserCollection. User's methods and properties will deal entirely with the data stored for a specific user, and UserCollection's methods and properties deal with things such as returning a list of users and manipulating that list in some way.

If I ever need to access or manipulate information about a user in my program, I make sure that I never talk to the database directly. If I can't do what I want with the User and UserCollection classes (possibly in combination with other data object classes) then I will enhance those classes to provide the needed functionality.

What you gain through this approach in addition to better OOP structure is true database independence. If I switch from MySQL to PostgreSQL, I will not have to edit every file in my application. I would simply use DB* classes that are for PostgreSQL and then rewrite the methods in my data object classes (like User and UserCollection) so that they would work with the new database. Not only does this mean not editing queries throughout your application, but it also takes care of problems that come up due to changes in table structure that are needed due to differences in database systems.

Thoughts?

[csn]

Probably not a bad idea. I'm switching a lot of my scripts from MySQL to PostgreSQL. I thought about using a db abstraction like adodb or metabase, but thought it rather futile since PostgreSQL has many more features, and there are quite a few differences in function names and SQL syntax. And so often, multiple MySQL queries were condensed into one PostgreSQL view.

[okrogius]

That is another reason why not all of those db abstraction layers are so great as they seem.

I personally use ADOdb, php.weblogs.com/adodb. It handles this db dependant sql quite well. For example, there are so many different ways to write an sql query with limit depending on what db you are using...result?... there is a special function.

Instead of doing $db->Execute(SELECT ... LIMIT 0, 25); I use $db->SelectLimit("SELECT ...",25,0); etc. I think you get the idea.

But, your paorach also sounds quite good.

[Alarion]

I agree, KAD9. Datalib was my first PHP project, something I used to learn PHP with.

I have considered on how to abstract out the database-unique things like limits and the like. ADODB does a decent job of this, and is an approach I might take.

I don't know if bypassing the Abstraction library altogether and incorporating DatabaseXYZ in your main application classes is necessarily beneficial. I mean, it really has to be determined on a case-by-case basis. I *do* like your approach, when taken at face value.

However, after re-reading your post it almost sounds like you are basically writing your own abstraction, BUT instead of letting PHP include the correct class files or create the correct objects, you just manually replace the DB classes with ones for DatabaseXYZ.

Wouldn't that be the same as creating an Abstraction Library but telling the user to "include_once 'abstract.mysql.php'" to use MySQL or "include_once 'abstract.postgres.php'" to use Postgres?

Am I following you here? You still have to edit your classes (as you mentioned) to correct certain SQL nuances between the systems though...

[Technosailor]

Or, having done this for awhile, he has collected a set of classes for MySQL, a set of classes for Postgres etc and he simply includes those classes based on the project. Makes more sense than editing the classes everytime you switch databases on projects.

Aaron

[KillAllDash9]

I do have DB* classes prebuilt for MySQL and PostgreSQL that I reuse between different projects.

Yes, If I was switching databases on a specific project, I would have to edit the data object classes such as the User and UserCollection classes I described in my original post, but my point is that you would likely have to do this anyway.

Yes, ADODB does provide functions for abstracting things like the difference in SELECT statements for limiting and offsetting results, but when you get into really optimizing your code, what does it do to abstract the difference in table structures, etc. Can it automaticly translate between a PostgreSQL database where I may use inheritance to relate tables and a MySQL database where I would have to have the shared data inserted in one table and the specialized data inserted in one or more different tables? Can it translate a stored view in PostgreSQL into the need to create a temporary table in MySQL and then run queries on that table? These are the kinds of things that are going to force you to rewrite much of your database code even if you abstract the functions with something like ADODB. With the approach I am using, you are at least limiting the number of places where this type of code appears to a very specific class library.

[KillAllDash9]

I should also note that I'm not really

incorporating DatabaseXYZ in your main application classes

You can really think of the data access objects as a database abstraction layer, its just that they are not an all-purpose generic abstraction layer. They only abstract a specific sub-set of data for the application.

These data access objects then become the 'database' that the application uses. Whenever the application needs to retrieve or modify the stored data, it uses these classes which then take care of getting the right data to the right place.

[KillAllDash9]

OK, sorry for the three-in-a-row here, but I wanted to make one more comment on this:

Wouldn't that be the same as creating an Abstraction Library but telling the user to "include_once 'abstract.mysql.php'" to use MySQL or "include_once 'abstract.postgres.php'" to use Postgres?

I don't really tell the 'user' to do these includes, because I rarely develop (and am not really reffering to) applications that are meant to be distributed to a wide variety of users and installed by them. I primarily develop specialized applications that have little 'distributable' value.

However, I think my method would still work for something that was packaged for distribution assuming that a) there was an installation script that would tell the program which database system was to be used so that the 'user' would not have to edit code to include any files, and b) the developer does the work to create a seperate database creation script, DB* classes, and data access objects with identical methods but different internal workings for each database that is supported by the application.

If you do the work required, your application can then not only support several database back-ends, but it can actually be optimized for each of those databases as opposed to trying to make one generic database schema fit all of the supported database systems by limiting the database to features supported by _all_ of those databases (regardless of syntactical nuances that something like ADODB could iron out).

[Alarion]

ok, combing what you said, and what Sketch mentioned, then you could then have:

A Data Abstraction Layer (by this, I mean a very basic one, which I have tried to make DataLib be. In that, you can still use DB specific SQL, etc.)

The you have your objects (the User and User Collection you gave as an example). But you would have something like say:

user.mysql.php
user_collection.mysql.php

user.postgres.php
user_collecytion.postgres.php

etc...

then, possibly you could actually include files in your program like "user.class.php and user_collection.class.php". You would just copy the "db specific" classes to the "classname.class.php" files.

Might be interesting if you know ahead of time that you be prototyping (for example) on Postgres and then deploying on Oracle. You would just copy the correct class files over when you go to deploy and wa-la.

I guess it really depends on *what* you are doing. A system like we are developing at work (Ammunition/Inventory tracking for the Navy) would be something that might benefit from your approach, as it is something that really needs to have tuned SQL(due to the millions and millions of rows), which might be hard to do with a general abstraction library.

But something like.. a guestbook or blog script might be "easier" to support with using a abstraction library and using general SQL instead of trying to optimize everything.

In any case, it's an interesting approach

[Technosailor]

KillAll,

I would really like to play with your DB classes if you don't mind....particularly the postgresql and mysql ones. Do you mind?

Aaron

[randem]

kill all -9,

I agree 100% with what you've said, and with your approach.

The classes you end up with are practical business objects, which follow good coding guidelines and good application design, and are essentially not abstraction at all.

It's very much the same as the approach used by application developers - and which I've used many times myself - where you put all the data access classes into a library (DLL on windows, SO on linux) and dynamically use the library that works with the database you're using.

For an example app, lets say you've got a contact manager which stores information about people, allows sending mass mailings, and stores a record of what's mailed to whom. In this case, you'd have objects to represent each contact (person), and each mailing, and those objects would contain all methods required for persisting data, completely removing that task from the actual application. Now, if you decide to switch from MySQL to Postgress, you just need a couple of new business objects. And what's more is it doesn't stop there... you can change to XML, flat-files, or even an obscure socket-based network protocol if you really want to. The only change is in the core library of business objects.

In the application world, this is a simple case of swapping DLLs or SOs... most likely dynamically based on an INI file or registry setting. To do something like this in PHP, you would have to check that setting once for every page you load, which is a little repetetive, but still far more performance-friendly than using an overbearing database abstraction class.

Excellent thread.

[KillAllDash9]

And you really don't have to do the check on every page, either. As long as the file names are the same for each library, just copy the library you will be using into the proper directory. If you decide to switch at some point, just copy the alternative library into this directory overwriting the other files.

[KillAllDash9]

I think this method also adds some interesting possibilities when you add certain 'data objects' to the session. For instance, if you are performing an operation on one of the contacts and that operation requires multiple 'pages' to complete, you could add that object to a special session variable. It would then be serialized at the end of each request, and you could defer your database update to the end of the operation without the need to muck around with hidden form fields and such.

[randem]

Another thing which I do (and it appears you do as well) which could be explained in more detail is to create an abstract base class for these business objects.

For instance, going back to the contact manager (a completely hypothetical example, by the way) if I were creating business objects for the Contacts and Mailings, I would first create a base class to handle the tedious connection stuff. eg:

class BusinessObjectBase
{
    function connect(){
        //...
    }
    function disconnect(){
        //...
    }
    function retrieve(){
        //...
    }
    function store(){
        //...
    }
    function get_next_id(){
        //...
    }
    //... and so on...
}

and then extend that class through inheritance for each of the business objects. This keeps the interface standard accross the board, as well as keeping the code to a minimum -- which further facilitates the ease of making the necessary edits to work with another database.

Extending objects may add a performance hit on sites with heavy traffic, but it's worth it for those sites that aren't as performance-dependant.

[r0kawa]

I've try many db abstraction class, phplib, pear, adodb and lastly, I've been reading the DAO (Data Access Objects) from http://www.exteremephp.org . You can extend that class to write specific function to specific table.

For example, if you want to make some insert function to specific table, just extend that class, and create the insert function for that table. Although all of you think it is simply wasting of time but it's the best method that i could think of to support every database in the world :P .

You can read their manual about DAO in here http://www.extremephp.org/tutorials/tut1/tutorial1.pdf

[Captain Proton]

What about the following situations:

- A data object in one table has a reference to a data object in another table

- One data object has a collection of other data objects

Is that possible with those data access objects?

[r0kawa]

Yes it's possible.

- A data object in one table has a reference to a data object in another table

For Example , you have table A and B.
if you have 2 classes for handlin A and B.
If Class B need object from A, just instance Class A and get the object from that instance.


- One data object has a collection of other data objects
It's the same with the first concept, but u need to instance the class needed or just create a static variable
Class B {
//this is the constructor
function B()
{
//u can use any object from class A
$this->allObjectA = & new A();
//or u can use any function that u want to return object
//using static variable
$this->A = & classA::method();

}

and it will be available all over class B

Please correct me if i'm wrong.

tq
r0kawa

[KillAllDash9]

I will preface this by saying that I did not read through all of the documentation on DAO, and unfortunately that is the only information that their site really had about the library at all, but here's what I don't like about it. Each Data Access Object seems to be just a representation of a 'row' from the table. That would seem to limit the functionality a little bit. What if my 'object' doesn't correspond neatly to a single row in the database?

I think the idea of a business object is cleaner, as you have more freedom with the methods that you create to manipulate that object. It requires the programmer to sever himself, for a while, from the idea that this data is stored in tables in a database. Instead, you simply think of the objects your application is manipulating in more 'spacial' terms. Afterall, the way the data is stored shouldn't be important to the business logic layer of the application at all.

By creating these business objects as the interface between the business logic and the storage layers of your application, you provide a looser coupling of the components of your system.

[aivarannamaa]

Martin Fowler's "Patterns of Enterprise Application Architecture", which is mentioned several times in this forum, covers this topic very well.

Three main approaches for organizing an enterprise application (that is applications that need to implement business logic wich relies on persistent data) presented in the book:

1. Transaction script -- this is of structural programming style, you just have a function or method for a particular task (like updating employees personal information), you go and code the implementation, executing database querying functions along the way when needed, using optionally some helper functions, ie. sql and business logic is quite mixed. Of course, many OO developers would despise this way, but Fowler admits it can be suitable for small problems ("not more than two dozen tables"!) when developers are not very good in OO. You can make it more OO using Active Records, objects that correspond to database rows and have all needed methods for dealing with that row. SQL and business logic are mixed in those objects and Transaction script manipulates them.

2. Table module -- More OO but not very much. Business logic is built in set of classes that correspond to respective database tables. Class model follows database structure. Those classes have methods for setting and getting rows in the table and for any other task that deals with that table:

$employee->getAllEmployeesInDept ($dept)
or
$employee->updateEmployee ($id, $name, $address, $dept)
or
$employee->raiseSalary ($id)

only one instance of each such class is created (actually one could use class methods -- Employee::raiseSalary($id), because these classes can be made stateless). SQL can be encapsulated in separate Table Data Gateway class which is dealing only with database and contains no other logic (and which can be substituted when data source is changed), but I think this can be sometimes an overkill.

3. Domain model -- OO style. Business (or domain) logic implementation need not to worry about data source structure, you make classes that suit best for solving business problems and leave matching this with database as another separate task. Relationships between objects are implemented using the way programmer might like (inner classes, references, objects as properties, sets, lists and so on). You code as if business objects would be persisted automagically. This is good way to go if domain logic is very complicated, when every bit of complexity avoided in domain logic implementation (like dealing with database) is important. This is good part. Bad part is that code dealing with database still must be written and it also must be matched with structure of domain objects. Complexity saved in domain layer goes (doubled?) to persistence layer. Here come questions like:
* when to load a row from database?
* when to save it back, how do you know when business object is done with that data?
* what if two business objects are created that are related to the same database row
* one-many relationships, do I need to load all rows of the many end when collection is created?
* and so on, read the chapter about concurrency if you want to see more issues.
Some more common options implementing persistence are DataMapper and Row Data Gateway.


It may seem that I'm biased towards Transacrion script and Table module. That's true but it's because I have had quite small problems to solve and also, I admit that Domain model is just too tough for me as I'm quite beginner in OO. But I believe that when you got used with it, then you want to solve even simple problems with Domain model. But if you don't feel so confident and don't have very complicated problem, then consider Transaction Script or Table Module. Fowler warns that a Domain Module with DataMapper is very hard to design right.


Maybe I got Martin Fowler ideas wrong and then this post made a false impression of these things, but you need to read it yourself anyway. Unfortunately, as the book is printed now, the web version is taken down (of course, you are lucky if you happened to make a copy before that

Aivar

[Captain Proton]

Yes it's possible.

- A data object in one table has a reference to a data object in another table

Well, this would be an inefficient solution. You have to execute two queries, one for each individual object, when instead you could execute one query with a JOIN or something.

The reason I asked is because I'm building a Persistence Layer (or O/R Mapping system as its also called) myself, specifically for PHP. For each class in your application you create a mapping file that looks something like this:

<class class="News">
  <key name="id" column="news_id"/>
  <reference name="user" class="User"/>
    <key column="user_id"/>
  </reference>

  <field name="date" type="date"/>

  <collection name="related_news" table="related_news"/>
    <key column="news1_id"/>
    
    <reference class="News"/>
       <key column="news2_id"/>
    </reference>
  </collection>
</class>

The system takes care of finding, loading, saving and deleting objects. The major advantage of a system like this is that it saves you a lot of time executing database queries. You simply do something like this for saving objects:

$news = new News();
$news->setTitle('Isnt this a great piece of software?');
$news->setUser(
  User::find($userId)
);
$news->save(); 

The News and User classes in this example are business objects with get() and set() methods for their properties, such as getTitle and getUser(). As developer you only have to worry about creating your business objects and don't have to worry about loading and saving them to a database.

Combine this with some powerful classes for supporting database specific SQL / queries and you have a true Database Abstraction Layer. 'Abstraction' in the sense that you don't need to worry about interaction between your code and the database or what type of database you are using. The persistence layer takes care of this.

I'd like to know if there would be interest in such a system, cause Im thinking about releasing it as an open source product (when finished).

[HarryF]

Much as I was impressed by the extremephp approach, for most of the things I want to do, it feels like overkill.

Personally think the adodb approach is better, but could be combined with a DAO like approach in which you build a query.

Comparing MySQL and Oracle for example, to select a limited result set;

MySQL;

SELECT col1, col2 FROM table ORDER BY col2 LIMIT 50, 10

Oracle;

SELECT * FROM (
  SELECT ROW_NUMBER() OVER(ORDER BY col2) RN, col1, col2 FROM table ORDER BY col2)
WHERE RN BETWEEN 50 AND 60

It's also worth bearing in mind that other technologies (such as XML and XQuery / XPath) are also becoming important data sources.

For me a query should be something like;

$fields=array ('username','password','email');
$q=& new QueryBuilder();
$q->select($fields); // Adds a SELECT to the query buffer with the column names
$q->order('username'); // Add an ORDER BY
$q->limit(50,60); // Limits the result set
$result=$q->fetchResult(); 

So this would be something to add to existing database abstraction.

Captain Proton - sounds real interesting. You planning on bringing XML schema (e.g. primitive / user defined types) into that?

[Captain Proton]

Yeah, the system I'm building supports lots more things than what I mentioned in my previous post. It supports custom types as well, that is, objects as types (called Composite Objects in the system).

Here's a more complete mapping file example, showing some more possible uses. You can also map subclasses of a parent class to the same table (Single Table Inheritance):

<!-- Discriminator Column is a column that indicates the subclass of an object -->
<class class="NewsItem" table="news" discriminatorColumn="type"/>
  <!-- Property(ies) of an object that uniquely identify it -->
  <key name="id" column="userID"/>

  <!-- Other primitive properties of an object -->
  <field name="title" type="string"/>
  <field type="int"/>
  <field type="bool" trueValue="1" falseValue="0"/>
  <!-- An object of class Date, built-in support -->
  <field type="date"/>

  <!-- Reference to another object -->
  <reference name="user" class="User"/>
    <!-- Foreign Keys -->
    <key column="userID"/>
  </reference>

  <!-- A collection of 'elements': regular values or references to other objects -->
  <collection name="relatedArticles" table="related_news">
   ...
  </collection>

  <!-- A property of an object that is itself an object, saved to the same table as its owner -->
  <object name="summary" class="NewsSummary"/>
    <field name="text" column="summaryText"/>
    <field name="image" column="summaryImage"/>
  </object>

  <!-- Properties for subclasses, which can have properties, references, and collections themselves too  -->
  <subclass class="StickyNews" discriminatorValue="sticky"/>
    <field name="stickyDate"/>
  </subclass>
</class>

[trickie]

$news = new News();
$news->setTitle('Isnt this a great piece of software?');
$news->setUser(
  User::find($userId)
);
$news->save(); 

I've been thinking of doing something similar, but for a community publishing system...

What i was thinking is similar to this (changed to suit this context):

$news = new News();

//this tells the news object how it's state should be persist
$news->setPersistenceModel('xml');

//params could be $_POSt, $_GET, events whatever...
$news->setTitle($_POST['title']); 
$news->setPoster($_SESSION['user']);
$news->setContent($_POST['content']);

/**
this object is similar to the plug-in library KillAll-9 was talking about.
This library would have have a consistant interface so that you can 
save to a DB or a textfile, or in this case an XML doc.
*/
if($persistenceObj = new PersistanceObj($this->persistanceModel))
{
   $news->save($persistenceObj);
}
else
{
   echo "Wrong Library";
}


//this is sort of what $news->save would look like
function save(&$persistanceObj)
{
   $persistanceObj->persist($this);
} 

Of course, none of this is thought out to any great extent, just thought i'd throw in my 2 cents.

[KillAllDash9]

The only thing I don't like about that is that you are setting the method of persistence through a mehtod call. The point of using the business data objects is that the rest of your code shouldn't have to know anything about how the data is persisted.

[trickie]

so maybe pass the method of persistance when i create the persistance object.:

$persistanceObject = new Persistance('xml'); 

then you can just call :

$$whatever->save($persistanceObject); 

Is that what you mean?