I am searching for query building engine for PHP (not ORM!) which may satisfy some criteria specified below. Regrettably, after considering Doctrine, Propel, Adodb, Zend_Db, etc. I could not find any that actually fits the profile - they're either too abstract (I do not need ORM-level abstraction) or don't support enough features. Things I require is a library that will let me build the SQL query programmatically - I do not even require it to operate it, though that might be Perfectly acceptable.

I truly want to avoid reinventing the wheel, if you have some understanding that matches the profile please take it forward. If you feel among individuals named above fits (and that i skipped that), please let me know too.

Should Have (if library does not get it, it isn't helpful for me personally):

  • Support PHP 5.2 (namespaced libraries can not be used for the time being, regrettably)
  • Support programmatic query generation, including choose expressions, order-by, limit, group-by, getting, unions, outer joins, etc.
  • Support Mysql, Oracle oci8, MSSQL, DB2, Postrges
  • Support building named parametrized queries &lifier prepared claims
  • Support adding conditions/joins dynamically at any time
  • Support for datatypes like datetime, etc. - e.g., correctly formatting incoming/outgoing data, use proper comparison functions as needed, etc. (not to mention proper estimating). We all know types of all of the fields within the code, therefore the library should let us tell it what each area is.
  • Stand-alone (simple to use without a lot of other support classes)
  • Simple to extend and license enables stretching on BSD-like terms
  • Clean PHP 5 code (no PHP 4 object-by-ref cruft, etc.)

NICE To Possess (we're able to implement that on the top of "should haveInchutes but could be more happy whether it were already done)

  • Support instantiating parametrized queries (full &lifier partial) - i.e. after getting built a question support passing on area of the params and generate new query with individuals params replaced
  • Support merging two queries (i.e. adding conditions and tables in one query into another)
  • Support query comments (including parametrizing them)

BONUS (this could really make us happy, but we're able to do without it for the time being)

  • Support serialization
  • Support caching

So, does anybody know such library?

CodeIgniter is really a safe wager for which you are searching for: http://codeigniter.com/user_guide/database/active_record.html

Also, it may be easily extended from it's core to complete the thing you need. Additionally, it has query caching (your bonus products).