OO DataBase Abstraction Layer, what for?

[bilibytes]

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:

public function getUserBm()
    { 
        $sql =<<<_SQL
        SELECT * FROM bm LEFT JOIN bm_sections
        ON bm.section_id = bm_sections.id
        WHERE user_id = '$this->_userId'
_SQL;
        $result = $this->_db->query($sql);
 
        if($this->_db->numRows($result) > 0){    
            while($row = $this->_db->fetchArray($result)){
                foreach($row as $key => $val){
                    $dataArray[][$key] = $val;
                }
            }
            return $dataArray;
        }
    }

-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

bilibytes

[Dormilich]

@bilibytes
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)

@bilibytes
probably

@bilibytes
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.

@bilibytes
I find it a very good practice

@bilibytes
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:

(void) public static function connect()
(bool) public static function prepare((string) $index, (string) $sql)
(object) public static function execute((string) $index, (array) $params)

SQL handling class

// prepare all non-dynamic SQL statements
(bool) protected function load()
// do stuff like triggering statement execution or processing the results
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, …)
)

[Markus]

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:

$val = "some 'value";
$val2 = "ano'ther value";
$this->db->from('tbl1');
$this->db->where('col1', $val);
$this->db->where('col2', $val2);
$this->db->limit(1);
$this->db->order_by('col3', 'desc');
 
// Would produce (depending on the database driver you have specified)
SELECT * FROM `tbl1` WHERE `col1` = 'some \'value' AND `col2` = 'ano\'ther value' ORDER BY `col3` DESC LIMIT 1
// Notice all values are escaped and the how the SQL is arranged properly.
 

Another cool thing for the CI library is chaining (PHP5 only).

$this->db
     ->from('table')
     ->where('x', 1)
     ->limit(1);