I'm looking for query building engine for PHP (not ORM!) which would satisfy some criteria specified below. Unfortunately, after looking into Doctrine, Propel, Adodb, Zend_Db, etc. I couldn't find any that really fits the profile - they are either too abstract (I don't need ORM-level abstraction) or don't support enough features. What I need is a library that would allow me to build the SQL query programmatically - I don't even need it to run it, though that would be OK too.
I really would like to avoid reinventing the wheel, so if you know something that fits the profile please bring it forward. If you think one of those named above fits (and I missed that), please tell me too.
MUST HAVE (if library doesn't have it, it's not useful for me):
- Support PHP 5.2 (namespaced libraries can't be used for now, unfortunately)
- Support programmatic query generation, including select expressions, order-by, limit, group-by, having, unions, outer joins, etc.
- Support Mysql, Oracle oci8, MSSQL, DB2, Postrges
- Support building named parametrized queries & prepared statements
- Support adding conditions/joins dynamically at any point
- Support for datatypes like datetime, etc. - e.g., properly formatting incoming/outgoing data, use proper comparison functions if needed, etc. (and of course proper quoting). We know types of all the fields in the code, so the library should allow us to tell it what each field is.
- Stand-alone (easy to use without tons of other support classes)
- Easy to extend and license allows extending on BSD-like terms
- Clean PHP 5 code (no PHP 4 object-by-ref cruft, etc.)
NICE TO HAVE (we could implement that on top of "must have"s but would be happier if it were already done)
- Support instantiating parametrized queries (full & partial) - i.e. after having built a query support giving it part of the params and generate new query with those params substituted
- Support merging two queries (i.e. adding conditions and tables from one query into another)
- Support query comments (including parametrizing them)
BONUS (this would really make us happy, but we could live without it for now)
- Support serialization
- Support caching
So, does anyone know such library?
CodeIgniter is a safe bet for what you're looking for: http://codeigniter.com/user_guide/database/active_record.html
Also, it can easily be extended from it's core to do what you need. It also has query caching (one of your bonus items).