I started working on a class to build SQL queries. The idea is to isolate my code from my database so all queries would go through this class. (The code at large would never use SQLSelect or prepared statements directly.) Before I get into this heavily, I wonder if such a thing already exists for public consumption?
So you mean you would have a class for each table for instance where you would be able to say myTableClass.GetAllRows or myTableClass.GetRowByID(2)
Oh I’m sure there is something somewhere but most, if not all classes, that I’ve ever looked at struggle with things like compound queries, multiple joins, complex conditionals (nested and or etc) and a host of other things that sql lets you do (like unions) etc.
I’ve always thought of simply building a set of classes modeled on those from EOF which handled all this very nicely but then once you have all these classes for compound predicates etc etc etc its actually almost quicker to just write the sql instead of creating instances or factories to put it all together.
And if you want those query builder classes to be cross DB the complexity tends to skyrocket as all db’s don’t tend to support all the same features so now the classes have to know how to rewrite a query for each db.
For example https://github.com/jkrasnay/sqlbuilder is “very simple” IMHO
EOF is huge see http://wocommunity.org/documents/javadoc/WebObjects/5.4.2/ and review EOAndQualifier & all related items for a query - but EO did way more than “build queries” as it also did middle tier object caching notification & a boat load more
Hibernate also does some of what EOF did and has its own model for this http://docs.jboss.org/hibernate/orm/3.5/javadocs/org/hibernate/SQLQuery.html
Mike: I will eventually have a class for each table if only to maintain constants for each table and field name.
Norman: Thanks, I’ll check those out.
Many moons ago we did a port of SmartSQL from VB6. I think it’s been 6 (+?) years since we last did anything with it but it’s at http://www.bkeeney.com/smartsql/. It was an object oriented way to create a query assuming you knew the relationship ahead of time. It’s ancient code so I make no promises.
And I guess you can always look at ActiveRecord http://www.bkeeney.com/rbinto/activerecord/ but I don’t think it’s exactly what you’re looking for. It rarely, if ever, is using straight SQL code and has the advantage of having the compiler and auto complete do some work for you.
Thanks Bob.
Years ago I wrote code that worked more like EOModeller where it generated classes & code for you that mapped a db into a bunch of tables with relationships - nothing done at runtime since this was for RB 2005 or so when introspection didn’t exist. (It was why I ever wrote code that could generate code)
The tool reverse engineered the DB and spit out classes based on the data model which is what I think ARGen does now
Then much like Bob’s Active record does now you could navigate through the db using dot notation etc & query different entities with qualifiers etc
ActiveRecord knows nothing about the relationships, though. One big drawback to it right now - it’s up to you to manage all that.