Page 1 of 2

OOP and SQL

Posted: Sun Mar 26, 2006 9:54 pm
by alex.barylski
For me personally OOP number one goal and priority has always been reusability...

Yes even over encapsulation data hiding and all the bells and whistles of OOP.

I use OOP in PHP, quite a bit actually...but I have a hell of a time using classes for anything database related...

I feel you loose reuse when you have SQL statements inside a member function, kind of like you loose reuse of a class with HTML inside member functions.

There are too many databases and abstraction layers available and in everyday use to force anyone user into using MySQL or AdoDB, etc...

As nice as OOP would be in working with SQL I can't seem to get myself going on on it...

I'll list my reasons:

1) Database systems are a plenty (MYSQL, MSSQL, SQLIte, etc...)
2) Adbstraction layers...AdoDB being popular but not standard...there are others
3) Database schemas change often and are also typically bound to one given project. There is no such thing as ONE single registration form...some people want date of birth, others want current age.

When I write anything OOP I want reusability, drop-in and go..no tinkering with implementation just plug and go.

I'm curious to hear how you deal with the above?

Do you favour encapsulation over reusability?

Cheers :)

Posted: Sun Mar 26, 2006 10:10 pm
by s.dot
I believe, and I may be wrong as im quite new to using OOP, that there are classes most programmers write that should be reusable from project to project, and classes they write for specific projects -- that won't be useable in other projects.

The project-specific classes could contain SQL, and database schema information.

Re: OOP and SQL

Posted: Sun Mar 26, 2006 11:00 pm
by Christopher
Hockey wrote:3) Database schemas change often and are also typically bound to one given project. There is no such thing as ONE single registration form...some people want date of birth, others want current age.
I can't really address the reusablity, because that depends on the same problem reoccuring. But it sounds like you are looking for some sort of mapping layer for your database access. ActiveRecord, DataMapper and ORM all come to mind as common solutions.

Posted: Mon Mar 27, 2006 1:35 am
by Gambler
but I have a hell of a time using classes for anything database related...
Same thing here. I blame SQL, relational data model and few other common database "features". I nearly had an argument on this topic in some other thread recently. After a bit of research I even found this: http://unixspace.com/context/index.html . It looks almost like DB I would write myself. Now, this would be object-oriented and resusable. Unfortunately, I don't even have time to try it right now.

Posted: Mon Mar 27, 2006 1:40 am
by Christopher
Gambler wrote:Same thing here. I blame SQL, relational data model and few other common database "features".
You might want to look at TableDataGateway. You can create simple model objects with finder and constraint methods that are pretty simple and clean for most uses.

Re: OOP and SQL

Posted: Mon Mar 27, 2006 5:24 am
by timvw
Hockey wrote: I feel you loose reuse when you have SQL statements inside a member function, kind of like you loose reuse of a class with HTML inside member functions.
Could you elaborate a bit? Imho there are enough examples of reusable SQL and HTML generators.
Hockey wrote: 1) Database systems are a plenty (MYSQL, MSSQL, SQLIte, etc...)
Admitted, they all talk their own dialect and this can be a serious pita.

But if you look at a project like Hibernate/EJB3.0 you see that it can easily talk all these dialects.. And switching from one to the other is a matter of changing a couple of connection parameters (and supplying the required jars)
Hockey wrote: Database schemas change often and are also typically bound to one given project. There is no such thing as ONE single registration form...some people want date of birth, others want current age.
Well, let's go back to hibernate. It's perfectly capable of generating/modifying your database schema so that it corresponds with your classes.
Hockey wrote: I'm curious to hear how you deal with the above?
In my experience a (relatively) simple SQL generator is usually more than enough.
Hockey wrote: Do you favour encapsulation over reusability?
I don't see how encapsulation is related to reusability.

Posted: Mon Mar 27, 2006 5:26 am
by timvw
Gambler wrote: Same thing here. I blame SQL, relational data model and few other common database "features". I nearly had an argument on this topic in some other thread recently.
So i'll ask it again: Why do you blame SQL, relational model for data and few other common database "features"?

Re: OOP and SQL

Posted: Mon Mar 27, 2006 6:49 pm
by alex.barylski
arborint wrote:
Hockey wrote:3) Database schemas change often and are also typically bound to one given project. There is no such thing as ONE single registration form...some people want date of birth, others want current age.
I can't really address the reusablity, because that depends on the same problem reoccuring. But it sounds like you are looking for some sort of mapping layer for your database access. ActiveRecord, DataMapper and ORM all come to mind as common solutions.
I've heard a bit about ActiveRecord, but know very little of what it does...

I suppose it's time to familiarize myself with how it works...

Thanks for the heads up :)

Re: OOP and SQL

Posted: Mon Mar 27, 2006 8:37 pm
by alex.barylski
timvw wrote:
Hockey wrote: I feel you loose reuse when you have SQL statements inside a member function, kind of like you loose reuse of a class with HTML inside member functions.
Could you elaborate a bit? Imho there are enough examples of reusable SQL and HTML generators.
Hockey wrote: 1) Database systems are a plenty (MYSQL, MSSQL, SQLIte, etc...)
Admitted, they all talk their own dialect and this can be a serious pita.

But if you look at a project like Hibernate/EJB3.0 you see that it can easily talk all these dialects.. And switching from one to the other is a matter of changing a couple of connection parameters (and supplying the required jars)
Hockey wrote: Database schemas change often and are also typically bound to one given project. There is no such thing as ONE single registration form...some people want date of birth, others want current age.
Well, let's go back to hibernate. It's perfectly capable of generating/modifying your database schema so that it corresponds with your classes.
Hockey wrote: I'm curious to hear how you deal with the above?
In my experience a (relatively) simple SQL generator is usually more than enough.
Hockey wrote: Do you favour encapsulation over reusability?
I don't see how encapsulation is related to reusability.
1) HTML does not belong inside a class...thats the worst example of OOP...

If I want to use your class, who says I want the header your class outputs? HTML is better left for template engines...

2) And thats cool, but again it forces me into using it's database abstraction layer doesn't it? What if I want to use AdoDB or my own???

3) Apart from being fundamental principles of OOP I don't think they are related... :P

I wanted to know, on a personal note, which the community prefered...

For instance, I typically write store sql inside functions with a namespace:

Code: Select all

function codename_insert_sql($fname, $lname, $etc='');
For me, reusability is "everything" when writting an class...if I can't reuse the class exactly as is in a later project I might as well save myself the time of writing extra class syntax (I'm that lazy yes) and proceed to writing fast efficient SQL functions instead of member functions.

I know the performance hit for using OOP is dismal, but I need more reason that encapsulation/data hiding...

If you have CPerson class which maps to your very own database structure, it's very likely both the schema and your object properties, methods, etc will change in your next project...if you add an age field to your schema, you need to update your class...which is NOT reusable in the pure OOP sense of the word.

I have much more to add to this comment, but I"m soooooo tired dude, I'll have to continue some other time :P

Cheers and thanks for listening and your feedback :)

Re: OOP and SQL

Posted: Mon Mar 27, 2006 8:46 pm
by Christopher
Hockey wrote:I've heard a bit about ActiveRecord, but know very little of what it does...

I suppose it's time to familiarize myself with how it works...
Given your comments like "OOP number one goal and priority has always been reusability" I would recommend that you head straight for Mappers. ActiveRecord is just a gateway that also contains business logic. It's a pretty handy shortcut is some cases, but it is a shortcut. When you hear ActiveRecord these days (as with RoR) it is really the mapping and relations that are being touted.

Re: OOP and SQL

Posted: Mon Mar 27, 2006 8:56 pm
by Christopher
Hockey wrote:1) HTML does not belong inside a class...thats the worst example of OOP...
If I want to use your class, who says I want the header your class outputs? HTML is better left for template engines...
I don't see the problem with classes that generate HTML or XML or whatever. I regularly use a form field generator class for example.
Hockey wrote:I know the performance hit for using OOP is dismal, but I need more reason that encapsulation/data hiding...
I wouldn't say "dismal" -- some constructs are a little slower.
Hockey wrote:If you have CPerson class which maps to your very own database structure, it's very likely both the schema and your object properties, methods, etc will change in your next project...if you add an age field to your schema, you need to update your class...which is NOT reusable in the pure OOP sense of the word.
Again, I don't see the problem with coding application specific model objects. I will sacrafice reusablity for clean design.

Re: OOP and SQL

Posted: Mon Mar 27, 2006 9:52 pm
by alex.barylski
arborint wrote:
Hockey wrote:1) HTML does not belong inside a class...thats the worst example of OOP...
If I want to use your class, who says I want the header your class outputs? HTML is better left for template engines...
I don't see the problem with classes that generate HTML or XML or whatever. I regularly use a form field generator class for example.
Hockey wrote:I know the performance hit for using OOP is dismal, but I need more reason that encapsulation/data hiding...
I wouldn't say "dismal" -- some constructs are a little slower.
Hockey wrote:If you have CPerson class which maps to your very own database structure, it's very likely both the schema and your object properties, methods, etc will change in your next project...if you add an age field to your schema, you need to update your class...which is NOT reusable in the pure OOP sense of the word.
Again, I don't see the problem with coding application specific model objects. I will sacrafice reusablity for clean design.
1) It's not reusable like a class should be. I use a Smarty style template engine and like all my forms done by a designer who makes them look awesome.

Ideally (Although current frameworks, template engines, etc don't offer 100% satisfaction) you should always strive for HTML/CONTENT seperation as well as PHP/HTML seperation. Not sure what Forms generator you use, but the one in PEAR I'm pretty sure generates the HTML for you:

Code: Select all

$frm->addFieldText('First Name');
$frm->addFieldType(TEXT_BOX, 'fname', 'Default text');
Or something to the above affect anyways right?

Which would output something like:

Code: Select all

<table>
  <tr>
    <td>First Name</td>
    <td><input type="text" name="fname" value="Default Text" /></td>
  </tr>
</table>
Which I suppose could be injected into a Smarty template as in:

Code: Select all

<?
  $frm->addFieldText('First Name');
  $frm->addFieldType(TEXT_BOX, 'fname', 'Default text');

  $tmp_buff = $frm->getFormHtml(); // Or something like this 

  // Assign form generator output to smarty template
  $smarty->assign('form_area', $tmp_buff);
?>
Problem is, what if I want more control over the output of my FORM html? Even if FORM generators had an API for manipulating every last visiual aspect of a FORM output, the amount of code it would require in PHP would be more than what it's worth - it would be easier to use plain HTML.

Besides HTML does what it does and PHP does what it does...hence the introduction of template engines.

2) I didn't mean dismal in the sense that there is no performance hit, but just wrapping a couple functions into a classes namespace doesn't cause "much" of a performance hit...when you start deriving classes, etc and using composition like mad which are also objects, then classes become a potetial performance problem. But I dought that just simple wrappers - which I imagine MOST database classes are incur much of a performance hit.

3) Nothing wrong with it, just not really needed, especially in a performance-centric or should I say performance-concerned environment like a shared host or web server of any kind. I disagree with procedural programming making for a "less clean design". Thats not the purpose of OOP IMHO anyways. OOP helps prevent namespace collisions, so in that sense it's cleaner I guess - if thats what you meant. But it's just as easy to prefix a codename or namespace to a global function and likely avoid namespace collisions as well. :)

As for multiple globals variables messing up a design...ok I can agree with that, but how many globals are there when dealing with a database?

Code: Select all

class CPerson{
  function insert(){}
  function update(){}
  function remove(){}
  function removeExpired(){}

  function isMale(){}
}
Depending on how you implement your classes I guess...if you pull all your data from a record and store it in a object then serialize that changed data back to the database...yes there could potentially multiple variables...

Personally I'd rather have each function execute SQL directly:

Code: Select all

function getAge(){ return mysql_query('SELECT age FROM persons WHERE pkid = 2'); }
Not syntactically correct, but you get my drift :)

This way you can optimize SQL queries individually as required, as opposed to having a generic Load() or Save() method serialize your entire object. If you did use this approach however, I suppose classes would make more sense in the cleaner design sense, yes I agree :)

Cheers :)

Posted: Mon Mar 27, 2006 10:06 pm
by Gambler
timvw wrote:So i'll ask it again: Why do you blame SQL, relational model for data and few other common database "features"?
Imagine yourself writing script to display titles for all today's articles.

Code: Select all

create articles(
id int,
preface text,
content text,
primary key(id)
);

create users(
id int,
name varchar(30),
primary key(id),
unique (name )
);

SELECT * FROM articles WHERE date = CURDATE();
Now, you need to write another script that displays all titles for today, plus prefaces, plus authors' names from another table. Can you use previous query and programmatically "add" new functionality on to it? No. You need to copy and paste.

With SQL, you can't easily break the problem into smaller parts and keep reasonable efficiency at the same time. Complex SQL is hard to generate and queries take too much time to be issued by hundreds inside a loop. That's why it's hard to reuse and fit into hardcore OOP applications. Plus, there are other ways of dealing with data, which are simply better for things I code daily.

As for the relational data model, I don't like many things about it. One, it encourages query paradigm. Two, it is not as flexible as data storage could be. Three, it's too abstract and complex. During our last conversation you dismissed a couple of my points as “mere implementation details”. However, I work with implementations, so those details matter, a lot. There are things like deployment and maintenance. Relational data model complicates both, since you need to to create schema in the beginning and change it afterwards.
arborint wrote:You might want to look at TableDataGateway. You can create simple model objects with finder and constraint methods that are pretty simple and clean for most uses.
I looked up that design pattern, but I'm not sure how it would fit into real-life scenarios. Say we're dealing with entities from previous example. Assuming you have TDGArticle and TDGUser, how would you deal with the task of getting author name for each article?

Posted: Mon Mar 27, 2006 10:13 pm
by Christopher
Hockey wrote:I disagree with procedural programming making for a "less clean design". Thats not the purpose of OOP IMHO anyways. OOP helps prevent namespace collisions, so in that sense it's cleaner I guess - if thats what you meant.
A lot to respond to, but the the above sentence jumped out at me -- and I could't agree with it more.

If you are just using the class construct for namespacing then you are not really using OO. The power of OO is more about how it effects your design than your coding.

Posted: Mon Mar 27, 2006 10:33 pm
by Christopher
Gambler wrote:
arborint wrote:You might want to look at TableDataGateway. You can create simple model objects with finder and constraint methods that are pretty simple and clean for most uses.
I looked up that design pattern, but I'm not sure how it would fit into real-life scenarios. Say we're dealing with entities from previous example. Assuming you have TDGArticle and TDGUser, how would you deal with the task of getting author name for each article?
Here is a very rough PHP4 pseudo-code example, but hopefully you get the idea that all the database access is encapsulate within the Gateway. It keeps the dependencies very clear which is the most important thing -- avoiding Transaction Scripts whenever possible.

Code: Select all

classs Article {
 var $id;
 var $preface;
 var $content;
}

class ArticleGateway {
  var $db;

  function ArticleGateway(&$db) {
    $this->db = &$db;
  }

  function find ($id) {
    return $this->db->fetchObjects('SELECT id,preface,content FROM articles WHERE id=' . (int)$id);
  }

  function findTodaysArticles () {
    while ($article = $this->db->fetchObjects('SELECT id,preface,content FROM articles WHERE date=CURDATE()') {
      $articles[] = $article;
    }
    return $articles;
  }

}