By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,322 Members | 1,904 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,322 IT Pros & Developers. It's quick & easy.

OO DataBase Abstraction Layer, what for?

P: 128
Hi there,

I am wondering what are the advantages of having a database abstraction layer like the one explained Here (in Devshed)

Don't you get more efficient queries writing them down in SQL?

As far as i can understand, i just see an advantage whenever the underlying database were to change (and even then, SQL isn't the same on each RDBMS?)

I actually wrap my queries into methods like this:
Expand|Select|Wrap|Line Numbers
  1. public function getUserBm()
  2.     { 
  3.         $sql =<<<_SQL
  4.         SELECT * FROM bm LEFT JOIN bm_sections
  5.         ON bm.section_id =
  6.         WHERE user_id = '$this->_userId'
  7. _SQL;
  8.         $result = $this->_db->query($sql);
  10.         if($this->_db->numRows($result) > 0){    
  11.             while($row = $this->_db->fetchArray($result)){
  12.                 foreach($row as $key => $val){
  13.                     $dataArray[][$key] = $val;
  14.                 }
  15.             }
  16.             return $dataArray;
  17.         }
  18.     }

-I make a class to hide the RDBMS specific functions

-then i instantiate that class into a property of another class that will use the abstracted functions. (here: $this->_db)

-i try to create a layer that keeps SQL code separate from the objects that use the information fetched from db. (the code above is from one of the classes that are used for separating SQL from rest)

is this a very bad practice?

If someone can bring light on this topic, i would be very greatful.
Thank you very much

Apr 19 '09 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 5K+
P: 8,639
only did a quick reading, but I think it's to leave the SQL composition and DB handling to the class (i.e. no manual SQL writing)


that's why DALs were done in the first place (I assume). the script just fetches all fields to work in every case (although that may not be the best choice in some cases)

…and the user has still to take care of userland data validation himself.

I find it a very good practice

there are certainly more ways to use the DAL, it mostly depends on how you like to do it.

(currently my favorite is:
abstract DB handler class:
Expand|Select|Wrap|Line Numbers
  1. (void) public static function connect()
  2. (bool) public static function prepare((string) $index, (string) $sql)
  3. (object) public static function execute((string) $index, (array) $params)
SQL handling class
Expand|Select|Wrap|Line Numbers
  1. // prepare all non-dynamic SQL statements
  2. (bool) protected function load()
  3. // do stuff like triggering statement execution or processing the results
  4. protected/public function doSomething()
output class [extends SQL handling class]
(loads the methods from parent according to needs, fetches user input, formats DB results for output, …)
Apr 19 '09 #2

Expert 5K+
P: 6,050
I think the main reason is to (obvious) abstract away any programmer errors that may arise from having to manually rewrite data access scripts when a database is changed from, say MySQL to one with different syntax. Using a (good) DAL will know which syntax to use based on your configuration of the DAL.

CodeIgniter (and most of the big frameworks, I can assume) uses an awesome DAL.

For someone who isn't very knowledgeable with SQL, it's easier to read the query when it's written like this:

Expand|Select|Wrap|Line Numbers
  1. $val = "some 'value";
  2. $val2 = "ano'ther value";
  3. $this->db->from('tbl1');
  4. $this->db->where('col1', $val);
  5. $this->db->where('col2', $val2);
  6. $this->db->limit(1);
  7. $this->db->order_by('col3', 'desc');
  9. // Would produce (depending on the database driver you have specified)
  10. SELECT * FROM `tbl1` WHERE `col1` = 'some \'value' AND `col2` = 'ano\'ther value' ORDER BY `col3` DESC LIMIT 1
  11. // Notice all values are escaped and the how the SQL is arranged properly.
Another cool thing for the CI library is chaining (PHP5 only).
Expand|Select|Wrap|Line Numbers
  1. $this->db
  2.      ->from('table')
  3.      ->where('x', 1)
  4.      ->limit(1);
Apr 20 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.