Page 1 of 1

OO SQL Generators versus Phrasebook

Posted: Mon Jun 30, 2008 1:02 am
by alex.barylski
Up until now I've used a phrasebook approach to storing SQL queries in a centralize location and just passing a name like FIND_EMAIL to the phrasebook object which then returns the SQL string formatted for use in PDO (ie: placeholders).

Advantages:

1) Centralized storage of all SQL.
2) Nice and concise when used in the model. There are no complex SQL statements making the code harder to read.

Disadvantages:

1) Changing the schema requires updates in three places. DB, Model/PDO object, SQL phrasebook database.
2) Dynamic SQL queries, such as those used in advanced filtering are not possible using the static phrasebook approach (without hacking in search and replace on custom placeholders -- which is messy if done at great length).

For the above reasons, I almost always try and stay away from advanced filtering but now my application requires it.

I'm thinking about using a OO SQL generator (like that found in Skeleton or Doctrine) or writing my own (hopefully not). I like the idea of the object taking care of returning proper SQL based on RDBMS requirement (mysql, mssql, etc). I also like the fluent interface and object feel to writing SQL that way.

I assume that OO SQL generator would yield all the flexibility of a normal SQL query but take care of things like LIMIT differences, etc?

What other advantages might using a OO SQL generator give me? This is something I have wanted to do for a while but I just need something light weight (no ORM solution, etc). Just a few light classes which generate SQL which I can then pass to my PDO object which would take care of data access layer issues and SQLi, etc.

That being said, I don't really need or want my OO SQL generator to handle escaping data as that would be redundant if used in PDO.

This is why I am considering using Skeleton as it's probably most light weight.

On second thought, if the OO SQL generator did handle escaping I wouldn't need to pass prepared statements to PDO saving me some lines of additional boring code... :P I could just use PDO as the data access layer...

Zing!!! I like that idea...

Arborint, if you read this (and I'm sure you will) can you go over some examples for me (publically of course) so I get a feel as to how I would start integrating SQL generator into my models and database object (PDO wrapper).

Re: OO SQL Generators versus Phrasebook

Posted: Mon Jun 30, 2008 1:19 am
by alex.barylski
Looking at the Skeleton framework...

I see a data mapper class. This might be interesting. As I understand it, it maps my class properties to physical tables so any updates I make to the object (not class -- not expecting schema changes) when I call save() those changes are persisted.

This could come in handy when writing CRUD routines, saving me from having to slug through all the SQL.

I have some poking aorund to do, but incase someone feels like adding to this topic, how would the data mapper obtain access to the database conneciton (which is a PDO wrapper) would I have to use Skeleton's DB connection class?

Re: OO SQL Generators versus Phrasebook

Posted: Mon Jun 30, 2008 3:02 am
by Christopher
The Skeleton OO SQL classes are general, except for escaping and LIMIT. Those are database specific so you register a connection object that can do those two things if you need that functionality. Or you can escape yourself and do the LIMIT manually. The classes just return SQL strings so they will work with any DB library.

The connection classes are currently undergoing a refactor. PDO is in the works, but they are just simple Adapter wrapper classes. We are also adding a set of "wrapper" classes that include higher level insert(), delete(), update(), select(), prepared statements, etc. functionality. There are also very simple ActiveRecord (no JOINs yet) and TableDataGateway classes that make creating classes for CRUD very simple.

The current DataMapper is very low level, less powerful, and much, much simpler that the other systems out there. You map class/properties to table/fields and the DataMapper manages the load and save (with dirty checks). And it is unobtrusive, meaning that your classes don't need to inherit a base class to be persisted. But it is all manual. The plan is to have a standard DESCRIBE object that you can get from a connection for table information. Combine that with introspection and the relations can be automated. After that some build-time tools are probably needed.

If you want more info, feel free to contact me...

Re: OO SQL Generators versus Phrasebook

Posted: Mon Jun 30, 2008 3:09 am
by onion2k
Hockey wrote:Up until now I've used a phrasebook approach to storing SQL queries in a centralize location and just passing a name like FIND_EMAIL to the phrasebook object which then returns the SQL string formatted for use in PDO (ie: placeholders).
Sounds a bit like stored procedures but with the procedure in PHP instead of the database.

Re: OO SQL Generators versus Phrasebook

Posted: Mon Jun 30, 2008 6:10 am
by alex.barylski
Sounds a bit like stored procedures but with the procedure in PHP instead of the database.
I have never used stored procedures but I am familiar with them...my implementation of phrasebook was quite simple.

Code: Select all

[GROUPNAME]
 
CREATE = "INSERT INTO table VALUES (0, :name, :address)"
Basically just static SQL statements stored in a single INI file.