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...
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).