DB Abstraction

[Marcus Nyeholt]

Disclaimer - I'm new to SS, please don't shoot me if this is already in under the covers somewhere :). I guess the overall question is something more like "How can I just let silverstripe take care of all the mapping between a filter I specify and the different underlying DB implementations of field/identifier quoting, pagination etc?".

Having read through the compatibility stuff between 2.3/2.4, and imagining the nuisance of in the future of doing something like switch (DB::get_type()) when performing a relatively simple filter, I'm curious as to the reasons for not using something like Zend_Db which already provides a lot of the abstractions that commonly cause issues such as identifier quoting, value quoting, prepared statements, sequences, limits, pagination etc.

So a query that ends up being like

"SELECT * FROM "MyObject" WHERE "MyObject".FieldA = 'value1\'s' AND "MyObject".FieldB = 'value2' OR "MyObject".FieldC > 10 ORDER BY FieldC LIMIT 10, 10"

becomes

DataObject::get("MyObject", "FieldA = ? AND FieldB = ? OR FieldC > ?", array("value1's", "value2", "10"))->orderBy('FieldC')->limit(1, 10);

or even

DataObject::get("MyObject", new DbFilter()->where("FieldA =" => "value1's", "FieldB =" => "value2")->orWhere("FieldC >" => "10"))->orderBy('FieldC')->limit(1, 10);


Obviously there are other queries that are just a pain and need separately written things, but I'd imagine they're the exception to the norm.

Marcus

[Sam Minnee]

Hi Marcus,

I guess the main reason is that the Database layer is fairly tightly
integrated with ORM. One of my half-done "yea I should really put
more time into that sometime" side-projects has been to refactor the
ORM to allow for methods like this:

DataObject::get("Group")->filter(array(
"Members.FirstName" => 'Sam'
))->Count();

Which would return number of groups that contain someone called Sam.
The key idea was to return DataList objects that didn't actually
execute the query until you iterated, or you called a single-value
method such as Count().

I guess the other reason is that the Zend framework didn't exist when
I started making SilverStripe. :-P

So the real question is "is it work switching to something like
Zend_Db", the answer to which I think is "No", mostly because I
suspect that it would be a very big job.

> under the covers somewhere :). I guess the overall question is something
> more like "How can I just let silverstripe take care of all the mapping
> between a filter I specify and the different underlying DB implementations
> of field/identifier quoting, pagination etc?".

To answer this specific question, the answer is currently "Use double-
quotes in 2.4". MySQL, MSSQL, PostgreSQL, and Oracle all support
this. For other databases, the long-term plan was the ORM refactoring
mentioned above.

Fun fact: We changed our MySQL connection to be ANSI compatible as
part of the DB-abstraction work in v2.4, which necessitated changesets
like this: http://open.silverstripe.org/changeset/66401

[Morven Lewis-Everley]

At the moment I don't have to many issues with the Silverstripe ORM, but in my mind, moving over to a third party library would make sense from a purely practical standpoint.

If the ORM was maintained by a third party (eg Zend), then it means that the Silverstripe devs can use the time they would spend developing that focusing on working on other parts of Silverstripe.

Using DataObject::get() like you demonstrated Sam would be pretty awesome. I prefer to use the minimum amount of SQL if I can :).

Cheers,

Mo

[Pete Bacon Darwin]

I raised this earlier and while I still think that it would be preferable to move to a third party provider, none of the standard ORMs provide the facilities that Sapphire requires: most importantly an effective inheritance mechanism (this is fundamental to the way the CMS works) and also aspect oriented techniques such as DataObjectDecorators.  In the long run my preference would be to choose a solid third party ORM and work with them to add these elements.

Pete

[dalesaurus]

I rather like SS's light ORM approach and smooth integration with the
templating system. It was one of the reasons I jumped in. Zend_Db/
Repose/Outlet/Doctrine/Propel/etc are all good systems but I have yet
to find something I can't do with SS's built in facilities. Plus the
idea of SS becoming a Frankenstien of libs that depend on libs make it
less nimble overall.

However integrating another ORM or writing yet-another-one would open
up SS to the other major DBs out there, among other things. Even
though you don't often find PHP lying around without MySQL nearby.
Either approach will take a serious amount of work. Then again the
work that the folks in the projects listed above have put in to their
ORMs is pretty sizable.

I would still like to see SS's codebase not depend on other
frameworks, especially after having worked with Zend to resolve bugs
before.

Sam, nice tr/`/\\"/ commit!

On Nov 17, 3:38 am, Morven Lewis-Everley

<m.lewis.ever...@googlemail.com> wrote:
> At the moment I don't have to many issues with the Silverstripe ORM, but in
> my mind, moving over to a third party library would make sense from a purely
> practical standpoint.
>
> If the ORM was maintained by a third party (eg Zend), then it means that the
> Silverstripe devs can use the time they would spend developing that focusing
> on working on other parts of Silverstripe.
>
> Using DataObject::get() like you demonstrated Sam would be pretty awesome. I
> prefer to use the minimum amount of SQL if I can :).
>
> Cheers,
>
> Mo
>

[Marcus Nyeholt]

It's probably important to note the distinction between the ORM functionality (mapping of objects and their relationships to DB tables) and the actual code that deals with querying/updating the database. I don't think the ORM side of SS needs to change at all, I was more interested in how the underlying DB layer deals with the actual mapping of ORM functionality, and whether it could benefit from a db interaction/abstraction layer.

I'll see how long it takes me before I get annoyed with forgetting a quote here or double quote there and write a wrapper around calls to DataObject::get* methods :p

Marcus

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "SilverStripe Development" group.
To post to this group, send email to silverst...@googlegroups.com
To unsubscribe from this group, send email to silverstripe-d...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/silverstripe-dev?hl=en
-~----------~----~----~----~------~----~------~--~---

[Sam Minnee]

> However integrating another ORM or writing yet-another-one would open
> up SS to the other major DBs out there, among other things.

SilverStripe 2.4 has support for PostgreSQL and MSSQL. The connectors
for these databases are packaged as separate modules.

[dalesaurus]

I was not aware, that is great! Retract anything nice I said about
integrating other ORMs into SilverStripe and roll it into the hype for
2.4.

And Marcus, isn't half the fun of programming making sure you don't
forget [insert particular language nuances here] when pounding out
code? :)

[Sam Minnee]

> It's probably important to note the distinction between the ORM
> functionality (mapping of objects and their relationships to DB tables) and
> the actual code that deals with querying/updating the database. I don't
> think the ORM side of SS needs to change at all, I was more interested in
> how the underlying DB layer deals with the actual mapping of ORM
> functionality, and whether it could benefit from a db
> interaction/abstraction layer.

I guess the issue right now is that the ORM and Database layers are a
little too meshed together.

Loosely, the Database class and its subclasses handle the querying and
low-level query generation, using either SQLQuery objects or special
arrays passed to Database::manipulate() as the intermediate
representation.

The problem is that filters, sort expressions, and sometimes joins are
still represented as raw SQL fragments,

So, the solution that I'm advocating is introducing a better
representation of those items. Which is what the Members.FirstName
bit in the preceeding example is about.

DataObject::get("Group")->filter(array(
"Members.FirstName" => 'Sam'
))->Count();

Because "Members" in the preceding example is part of the ORM (a many-
many relationship from Group objects to Member objects), we can't
really rely on a database layer to do this kind of transformation.
This querying style has already been implemented in the SearchContext
and SearchFilter system; in my mind, it makes more sense to push it
into the ORM-proper.

Most of the code in the Database layer is targeted at schema
generation and schema migration. I'm not sure if Zend_Db, or any
other systems, provide this functionality to the level that we need.