...

View Full Version : Database Abstraction



RMcLeod
05-16-2008, 01:50 PM
Okay so I am writing an OOP database abstraction layer that can handle any type of database. (Don't get me started on why I'm not using one of the many perfectly good ones available).

So far I've basically got a Db class which has a single factory method for returning the correct type of adapter.

I'm now about to write the Abstract Adapter class and my question is this. From a users point of view which is best:

a) Seperate functions for different types of db query each returning something based on the type of query e.g.


<?php
$db->insert_row($sql); //returns last insert id.
$db->update_row($sql); //returns affected rows.
?>


b) One overall query function that stores results in member varaibles e.g.


<?php
$db->query(/* insert statement */); sets $this->insert_id
$db->query(/* update statement */); sets $this->affected_rows
?>


EDIT: And another question should making strings safe for use in an sql query be the job of the coder using the API or the API itself?

tomws
05-16-2008, 03:24 PM
If I was using it, I'd prefer option (b).

aedrin
05-16-2008, 04:43 PM
I think method A is a cleaner abstraction (though I would name them insertQuery and updateQuery, or executeInsert and executeUpdate).

Though remember that not all databases have an automatically incrementing row (such as Oracle) so you want be able to provide the same service. Which might be confusing.

The problem with B is that you cannot predict the output of the member variables. (You'd have to parse the $query, which is not a clean abstraction)

Let the user tell you what they are doing, it is much cleaner.

chaosprime
05-16-2008, 05:02 PM
I would prefer that the main database object have a generic query function, with objects representing individual rows having specific-purpose methods for saving (ideally encompassing insert and update as appropriate), deleting, whatever.

The API should make strings safe if the way you interact with it is suitable for that. If I'm doing:


$row = $table->find(27);
$row->name = 'Bob';
$row->save();

then the string 'Bob' should be sanitized without me having to worry about it. If I'm bypassing all of that kind of functionality by rolling my own SQL string and sending it to the database's generic-query function, then I'm responsible for quoting at that point.

Just to randomly engage in some holy war, I greatly disagree about the insertQuery(), etc. That case pattern convention is ever so fashionable because of Java, but it's obnoxious, for this reason:


class foo {
var $someVar;
function getSomeVar() {
return $someVar;
}
}

The case pattern of the attribute should not be changing around because of a fashion statement. SomeVar, some_var, SOME_VAR, whatever, take your pick, but use something that stays consistent.

aedrin
05-16-2008, 05:18 PM
Since my main development is done in Java, I end up using what I'm used to. To make it worse, I actually do it like this:



class Something {
private $_name;
public function getName() { return _name; }
}


;)


SomeVar, some_var, SOME_VAR, whatever, take your pick, but use something that stays consistent.

I agree with about 80% of that. It is true that staying consistent matters. But it does not mean that I can't do whatever I feel is right. As long as I apply my naming convention consistently, it is no problem.

And the changing case is not a problem in my opinion. It doesn't increase my rate of errors, or decrease readability. Which is what conventions are all about.

Fou-Lu
05-17-2008, 06:09 AM
Yes, its a nice day.

From a developer point of view: your option b is most suitable. We should know how to write queries.
But, from a user friendly point of view, I prefer to break down no matter how painful it is. I'm so brutal on myself that I don't even allow my methods to use Result sets as a return >.<. Just in case its from a file system (which is another huge advantage)
One huge advantage to this method is you can control how the parameters come in. This can make things like prepared statements easier, and my favorite - table prefixes can be automagically appended :)

Oh, lemme show you what I mean from brutal, this is from a java abstraction layer I created awhile back:


package interfaces;

import java.util.Map;

import exceptions.MyDBException;

/**
* Contract a Database or storage object to implement these methods.
* Figured that ResultSet may not always be used, so we are forcing
* HashMaps
*/
public interface IMDB
{
/**
* Force this object to open
* @throws Exceptions.MyDBException
*/
public void open()
throws MyDBException;

/**
* Force this object to close
* @throws Exceptions.MyDBException
*/
public void close()
throws MyDBException;

/**
* Select from storage given the fields and from location
* @param fields
* @param from
* @return
* @throws Exceptions.MyDBException
*/
public Map<Integer, Map<String, String>> select(String fields, String from)
throws MyDBException;

/**
* Select from storage given the fields, from and condition
* @param fields
* @param from
* @param condition
* @return
* @throws Exceptions.MyDBException
*/
public Map<Integer, Map<String, String>> select(String fields, String from,
String condition)
throws MyDBException;

/**
* Select from storage given the fields, from, condition, and order
* @param fields
* @param from
* @param condition
* @param order
* @return
* @throws Exceptions.MyDBException
*/
public Map<Integer, Map<String, String>> select(String fields, String from,
String condition,
String order)
throws MyDBException;

/**
* Select from storage given the fields, from, condition, order, and other
* @param fields
* @param from
* @param condition
* @param order
* @param other
* @return
* @throws Exceptions.MyDBException
*/
public Map<Integer, Map<String, String>> select(String fields, String from,
String condition,
String order, String other)
throws MyDBException;

/**
* Select from a RAW sql input
* @param sql
* @return
* @throws Exceptions.MyDBException
*/
public Map<Integer, Map<String, String>> selectRaw(String sql)
throws MyDBException;

/**
* Get the last inserted ID
* @return
*/
public int lastInsertID();

/**
* Insert a record into storage given the items to insert and location
* @param fieldVals
* @param to
* @throws Exceptions.MyDBException
*/
public void insert(Map<String, Object> fieldVals, String to)
throws MyDBException;

/**
* Update in storage given the new values and where to write them to
* @param fieldVals
* @param to
* @return
* @throws Exceptions.MyDBException
*/
public int update(Map<String, Object> fieldVals, String to)
throws MyDBException;

/**
* Update in storage given the new values, where to write, and a condition
* @param fieldVals
* @param to
* @param condition
* @return
* @throws Exceptions.MyDBException
*/
public int update(Map<String, Object> fieldVals, String to,
String condition)
throws MyDBException;

/**
* Delete from a given storage location
* @param from
* @return
* @throws Exceptions.MyDBException
*/
public boolean delete(String from)
throws MyDBException;

/**
* Delete from a given storage location given a condition
* @param from
* @param condition
* @return
* @throws Exceptions.MyDBException
*/
public boolean delete(String from, String condition)
throws MyDBException;
}


yummy.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum