Page 1 of 1

providing sql options in the project

Posted: Wed Mar 25, 2009 1:54 pm
by koen.h
My project has the possibility to use sql storage though this is not necessary. I find it difficult to come up with a good way to design the classes for this.
First problem is the client code may or may not have an db connection already. And even if they have one I don't know if I can use it (eg the plain old mysql way with functions doesn't need to pass an object on which I can do something like '$connection->query()'). Should the SqlAdapter have a method to make a connection? Should the specific adapter use a __construct with a method to pass a connection of it can one?
Second, results differ from db implementation to db implementation. Eg PDO versus the plain old mysql again. So how to enforce a generic result object?

Don't know if this is clear enough though.

Re: providing sql options in the project

Posted: Wed Mar 25, 2009 2:53 pm
by Christopher
Generally the answer is yes to all your questions. I is pretty big subject though. Perhaps you should use PDO or and existing solution like Zend as a complete solution is a lot of work. There are lots of details, idiosyncrasies, and features that take a long time to accumulate/deal with.

Re: providing sql options in the project

Posted: Wed Mar 25, 2009 3:01 pm
by koen.h
Yes, using something like Zend_Db is an option. But I would loose the db connectivity I think most people still use (mysql functions). Same for using only PDO. That would be ideal of course, and easy. Unfortunately, I don't think that's a good idea for a lot of users.

Re: providing sql options in the project

Posted: Wed Mar 25, 2009 3:15 pm
by koen.h
Even then, something like Zend_Db would be overkill what those few queries that I need.

Re: providing sql options in the project

Posted: Wed Mar 25, 2009 7:54 pm
by Christopher
koen.h wrote:Yes, using something like Zend_Db is an option. But I would loose the db connectivity I think most people still use (mysql functions). Same for using only PDO. That would be ideal of course, and easy. Unfortunately, I don't think that's a good idea for a lot of users.
I think you just said that Zend_Db or PDO are not a good idea for a lot of users. That sounds kind of odd because Zend_Db and PDO were specifically designed for, and are currently used by, a lot of users. Perhaps I am misunderstanding you.
koen.h wrote:Even then, something like Zend_Db would be overkill what those few queries that I need.
First, how do you know that? And second, then why are you doing anything.

Re: providing sql options in the project

Posted: Thu Mar 26, 2009 12:44 am
by koen.h
A lot of users don't have access to the connection options Zend_Db provides since they're only option is the old mysql driver. Last time I checked Zend_Db couldn't be used with that connection option (for mysql only mysqli or PDO mysql).

I don't understand your second question. Why I am tring to abstract the connection and querying if I only need a few queries?

Re: providing sql options in the project

Posted: Thu Mar 26, 2009 1:08 pm
by koen.h
Maybe I need to be more clear. Let's take the following example:

Code: Select all

 
interface IPictureRetriever
{
    public function getPicture($name);
}
 
A class implementing this interface needs to be able to retrieve a picture from somewhere. One possibility is a class which retrieves images from a database (and no, this isn't what I'm doing).

We could create one like this:

Code: Select all

 
class MysqlPictureRetriever implements IPictureRetriever
{
    public function connect($connectionDetails);
    public function getPicture($name) {
        $result = mysql_select from db where picture is name etc;
    }
}
 
If I have two of those classes (eg a CommentSaver could store comments in a db) the connection method needs to go somewhere else. Also, when changing database types (eg from PDO to mysqli) we would need to create two new classes. Clearly these changes must be isolated: a single class responsible for connection and/or abstracting the querying would be needed. Not only that, it would need to make sure the result sets do not differ (eg a PDO result vs mysql result must be used by the client code without it being aware it's type).

A possible solution could be something like Zend_Db (=connection, querying and result set: solved). Although it doesn't support the mysql driver, it has one other problem. My classes are part of bigger program which may use Zend_Db, but it may use also something else like eZ Components. This means the following approach would fail:

Code: Select all

 
interface SqlPictureRetriever extends IPictureRetriever
{
    public function __connect(Zend_Db $db);
}
 
class MysqlPictureRetriever implements SqlPictureRetriever
{
    public function __connect(Zend_db $db) { $this->db = $db; }
    public function getPicture($name) { return $this->db->query('SELECT FROM etc'); }
}
 
note: don't know how to use Zend_Db, it's just an example.