Database abstraction layer

Published 17 February, 2007 in Programming - 0 Comments
These classes is part of PLib

I can’t remember the last time I made a web site that wasn’t hooked up to a database in one way or another! There’s nothing wrong with using the database functions that PHP provide directly but if you work with different types of databases – MySQL, Postgres, Oracle, SQL Server, SQLite and so on – you need to remeber how the functions for these databases work, in what order to pass arguments and what not. And maybe you write an application that teoretically could use an abritrary database and not MySQL that you developed the application for, and then it would be nice to use the same function calls no matter what database is being used.

This is where the database abstraction layer comes in handy. There already exist many of them so, again and as always, why bother writing another one? Well, it’s fun and you always learn new stuff and, for good and bad, third party APIs tend to be packed with features that you never use so it get quite tedious learning the API.

So I wrote my own database abstraction layer that only contain the functionality I mostly use. If I need more functionaly it’s easy to implement. When I wrote the abstraction layer I tried to evolve my OOP (object orientated programming) skills (which isn’t too great) so that in it self was I goal.

The abstraction layer contains of two interfaces and two master classes: A connection interface and class and a result interface and class. To create a driver for a new type of database you inherit the master classes and implement the interfaces. This is how the skelleton looks like:

  • interface IDB
    The connection interface
  • interface IDBResult
    The query result interface
  • abstract class DB
    The connection master class
  • abstract class DBResult
    The query result master class

So a database driver should have two classes: One that extends the DB class and implements the IDB interface and one class that extends the DBResult class and implements the IDBResult interface.

Example

Here’s a simple example of usage:

41 lines of PHP
  1. <?php
  2. require_once ‘Database.php’;
  3. //! MySQL example
  4. try {
  5. $category = $_GET[‘category’];
  6. $db = DB::Create(‘MySQL’, ‘localhost’, ‘uname’, ‘pw0rd’, ‘mydatabase’);
  7. $db>Connect();
  8. $res = $db>Query(“SELECT * FROM table WHERE cat_id = ‘%d'”, $category);
  9. if ($res>NumRows() > 0) {
  10. while ($row = $res>Fetch()) {
  11. echo “Title: “ . $row>title . “<br/>”;
  12. }
  13. }
  14. }
  15. //! Could be DBDriverNotFound, DBConnectionFail, DBNotFound, DBQueryFail
  16. catch (Exception $e) {
  17. die(“Error: “ . $e>getMessage();
  18. }
  19. //! SQLite example
  20. try {
  21. $category = $_GET[‘category’];
  22. $db = DB::Create(‘SQLite’, ‘mysqlitedb’);
  23. $db>Connect();
  24. $res = $db>Query(“SELECT * FROM table WHERE cat_id = ‘%d'”, $category);
  25. if ($res>NumRows() > 0) {
  26. while ($row = $res>Fetch()) {
  27. echo “Title: “ . $row>title . “<br/>”;
  28. }
  29. }
  30. }
  31. //! Could be DBDriverNotFound, DBConnectionFail, DBNotFound, DBQueryFail
  32. catch (Exception $e) {
  33. die(“Error: “ . $e>getMessage();
  34. }
  35. ?>

As you can see here we’re using exactly the same code, except for the instantiation of the database object, wether we’re using MySQL or SQLite, and that’s the meaning of the database abstraction layer.

Sources

I currently have two implementations for MySQL and SQLite. The MySQL implementation is what I’m using for this blog and the SQLite implementation is not very well tested yet.

The classes are pretty well documented and the documentation is bundled in the package below.

Database abstraction layer 17:31, Sat 17 October 2009 :: 42.1 kB