What is the true object oriented way of writing database code?

Lord_Yggdrasill

Well I've heard it many times that you can easily write procedural code with objects. One example is this script below from php.net, it uses objects every line, but it still is purely procedural style:

$db = new PDO('mysql:host=localhost;dbname=<SOMEDB>', '<USERNAME>', 'PASSWORD');
$stmt = $db->prepare("select contenttype, imagedata from images where id=?");
$stmt->execute(array($_GET['id']));
$stmt->bindColumn(1, $type, PDO::PARAM_STR, 256);
$stmt->bindColumn(2, $lob, PDO::PARAM_LOB);
$stmt->fetch(PDO::FETCH_BOUND);

So what is the true object-oriented way of rewriting the above code? I did see a possible scenario from this blog, which suggests ORM with data mapper:
http://blog.ircmaxell.com/2012/07/oop-vs-procedural-code.html

$mapper = new PersonDataMapper(new MySQLi(...));
$people = $mapper->getAll();

What do you think?

traq

it's not "purely procedural": it's focused on the PDO / PDOStatement objects.

Mappers are generally "object-oriented," but that's not the problem they are intended solve. (They solve the problem of relational databases not being object-oriented.)

They do encapsulate everything, though, which I think is what you're looking to accomplish.

class myStmtPDO extends PDO{

# container for prepared statements
protected $_stmt = [];

// . . .

# just do:
# $DB = new myStmtPDO( $your_dsn ); $DB->selectImageData( $_GET['id'],$type,$lob );
public function selectImageData( $id,&$type,&$lob ){
    # statement already prepared?
    if( empty( $this->_stmt[__METHOD__] ) || ! ($this->_stmt[__METHOD__] instanceof PDOStatement) ){
        $sSQL = "SELECT `contenttype`,`imagedata` FROM `image` WHERE `id`=?";
        $this->_stmt[__METHOD__] = $this->prepare( $sSQL );
    }

    # get statement and execute
    $stmt = $this->_stmt[__METHOD__];
    $stmt->execute( [$id] );

    # bind params
    $stmt->bindColumn(1, $type, PDO::PARAM_STR, 256); 
    $stmt->bindColumn(2, $lob, PDO::PARAM_LOB); 

    # fetch
    $stmt->fetch(PDO::FETCH_BOUND);
}

// . . .

}

NogDog

Create stored procedures in the database itself so that the "real" SQL is encapsulated in the DB, then just invoke the stored procedures as needed via a simple one-line queries in the application code, essentially making the database an object and the procedure calls its methods. 🙂

Weedpacket

NogDog wrote:
essentially making the database an object and the procedure calls its methods.

And/or use an object-relational DBMS so that you can declare object types in your database. Then you get to have polymorphism and other object-oriented things there as well.

Lord_Yggdrasill

@ Traq:
Thanks for your example, it looks really good to me.

@ Nogdog:
Umm stored procedures in the database itself? Can you show me an example of that? Does it mean I will need an additional database field storing the database query string?

@ Weedpacket:
Thats a good idea, unfortunately my software design is limited to relational database so storing objects type may not be an option, unless you mean to serialize objects and store them as strings in database?

NogDog

Lord Yggdrasill;11026523 wrote:
@ Nogdog:
Umm stored procedures in the database itself? Can you show me an example of that? Does it mean I will need an additional database field storing the database query string?

I don't really have any first-hand experience with this, but essentially you create a stored procedure in the DBMS's language more or less as you would define a function, including defining any input parameters you need for it. Then you can invoke it from your app as a simple DB query that might look something like:

$stmt = $pdo->prepare("CALL the_procedure(:arg1, :arg2)");
$stmt->execute(array(':arg1' => $value1, ':arg2' => $value2);

Weedpacket

Lord Yggdrasill wrote:
Thats a good idea, unfortunately my software design is limited to relational database so storing objects type may not be an option, unless you mean to serialize objects and store them as strings in database?

Well, it depends on your choice of RDBMS, but any that supports the SQL standard would allow you to define your own types, define the domains over which those types are defined, and provide for building composite types from existing ones (PostgreSQL automatically creates for each table a composite type to represent a single row in that table, with property names/types matching the those of the table's columns). Then one can define operators and casting rules and functions over those types.

You will of course need to write a mapping between the database types and the PHP types if you want such a mapping to exist - one that would operate at the level provided by the database driver. Of course, since there are two different type systems, there would have to be a translation layer between them anyway.

Then of course there are the OODBMSs, which are built from the start to handle objects (unlike ORDBMSs, which are RDBMSs with added support for objects)...

Lord_Yggdrasill

NogDog;11026531 wrote:
I don't really have any first-hand experience with this, but essentially you create a stored procedure in the DBMS's language more or less as you would define a function, including defining any input parameters you need for it. Then you can invoke it from your app as a simple DB query that might look something like:
$stmt = $pdo->prepare("CALL the_procedure(:arg1, :arg2)");
$stmt->execute(array(':arg1' => $value1, ':arg2' => $value2);

Umm so you were just referring to prepared statements? If so I think I know how to do it, and in fact my database class already is using prepared statements. Guess I am just a bit confused of the terminology here then? XD

@:
I see, so you are saying its doable even with relational databases. It sounds like what Doctrine is trying to achieve by defining their PHP types.

Weedpacket

Lord Yggdrasill wrote:
It sounds like what Doctrine is trying to achieve by defining their PHP types.

Well, yes: ORM stands for "Object-Relational Mapping"; i.e., managing the mapping between objects and relations.

But what I was referring to was the DBMS's type system, not PHP's. Assuming the DBMS supports the necessary SQL, an ORM could be written to exploit it.

(Conversely, if your ORM doesn't emulate functionality missing from whatever DBMS platform(s) it supports, then it and therefore you lose access to that functionality in platforms that do have it.)

The relational database model has some problems with supporting object orientation. Some of them are mitigated by an ORM (if it can build the necessary schema for you), but in general relational database theory is a bit short on the mathematical structure that maintaining an object graph typically would typically demand - not without a lot of translation. And even then there's the impedance mismatch that comes with mixing completely different programming paradigms; the only way to avoid bogging down in that is often to pick one to do all the work there and just use bits of the other only when necessary.

traq

Lord Yggdrasill;11026539 wrote:
Umm so you were just referring to prepared statements? If so I think I know how to do it, and in fact my database class already is using prepared statements. Guess I am just a bit confused of the terminology here then? XD

That example uses a prepared statement to CALL the stored procedure, but no, the two are not the same thing.

A stored procedure is a set of SQL statements that is stored in the DB and is run when CALLed. It can contain regular SELECT (etc.) statements, but it can also have its own business logic (IF / THEN, loops, etc.) and process data before returning it, or do just about anything else you can do to the DB.

You can read more here.

NogDog

traq;11026543 wrote:
That example uses a prepared statement to CALL the stored procedure, but no, the two are not the same thing.

A stored procedure is a set of SQL statements that is stored in the DB and is run when CALLed. It can contain regular SELECT (etc.) statements, but it can also have its own business logic (IF / THEN, loops, etc.) and process data before returning it, or do just about anything else you can do to the DB.

You can read more here.

Yep. In my example, the "the_procedure" stored procedure might be anything from a very simple insert query to a 1000-line procedure performing multiple queries, depending on various conditions and such -- all transparent the the code that it invokes it (just like when calling a public method from a PHP object).

Lord_Yggdrasill

@ WeedPacket:
I see, so it does require the database to support object types. Thanks for your comments/

@ Traq:
Interesting, thanks for the clarification. So storing a statement in database, is it a serialized statement object like PDOstatement?

@ NogDog:
I appreciate your example, thanks for your advices.

traq

Lord Yggdrasill;11026565 wrote:
So storing a statement in database, is it a serialized statement object like PDOstatement?

No, it's a native MySQL language construct. PHP has nothing to do with it. Did you look at the link in my post above?

Lord_Yggdrasill

traq;11026573 wrote:
No, it's a native MySQL language construct. PHP has nothing to do with it. Did you look at the link in my post above?

I see, thanks a lot. I did look at your link, but did not seem to understand whats actually going on. If these are mysql syntax, it will be possible to create query object that plays with the generation of such mysql query strings to interact with database. It looks quite useful and interesting.

traq

Yes, it's possible to generate them (e.g., in PHP) and send them to the database, but the whole point is that you can move that logic out of PHP and let MySQL deal with it itself.

Lord_Yggdrasill

traq;11026603 wrote:
Yes, it's possible to generate them (e.g., in PHP) and send them to the database, but the whole point is that you can move that logic out of PHP and let MySQL deal with it itself.

I see what you mean, thanks for the clarification.