OOP MySQL Query Builder

OK, so now that I'm actually on the list... I work for the Office of Research and Sponsored programs at the University of Mississippi building web based database driven applications. In the course of my work I have built a project I think may be of use to the world at large. Building object oriented php applications, I wanted an easier way to handle building queries for my various stored classes. I adopted a "reader writer" methodology and produced MySQLQueryWriters for each of the classes that needed them, and found that for many classes their queries were virtually identical to say their parent class. In an effort to reuse these queries, I implemented a colleciton of classes that represent the various components of MySQL queries. For instance the MySQLSelect class, which defines a SELECT query, is built up of a MySQLSelectClause, a MySQLFromClause, etc. Those classes in turn are made of MySQLFields, MySQLTables, etc. Basically I have written a library which allows you to define the entry SQL query as an object hierarchy... I don't really have time to smooth out the project and expand it for all the extraneous queries (things like DO and TRUNCATE) but would hate to see this project languish while I move on to other things I need to get done. Accordingly, I'd like to make it available to the community to do with as they please (hopefully expand, clean and polish it ). As to the notes on the PECL website I'd like to introduce the project via the list before I post it. I don't have a name for it yet, but it is targeted to the idiosyncrasies of the MySQL database and as such I've been referring to it as MOOQS ("mooks"). Here is a break down of the class files (as they are now, I'm not totally finished) and a couple examples of their use. Please note that much of this may have to change to meet the coding guidelines and naming conventions endorsed by PECL. For instance, for "managed classes" I generally have a model and a controller and various views which leads to the "/ClassName/Model.php" naming of these various classes even though they don't have controllers or views. I use an include directory for my applications and a folder hierarchy within them that matches the naming convention of the classes. These things have developed over time and are my own convention, but are fairly straight forward. For instance, the class MySQLField would be included in source like so: include('MySQL/Field/Model.php'). I may rename the top level directory to MOOQS (or whatever the project name becomes) rather than MySQL. Class Hierarchy / Directory Structure MySQL: Boolean: Model.php Conditional: Model.php Group: Model.php Conjunction: Model.php Connection: Model.php Database: Model.php Expression: Model.php Field: Model.php Value: Model.php From: Clause: Model.php Insert: Model.php Select: Set: Limit: Clause: Literal: Model.php Null: Model.php Operator: Model.php Order: Model.php Clause: Model.php Ordinal: Model.php Select: Model.php Clause: Model.php String: Model.php Table: Model.php Join: Model.php Update: Model.php Where: Clause: Model.php Example of Usage Here is where the power of this method shines. As an example I took some code from my PersonMySQLQueryWriter which is a class that takes person objects and writes queries for use by the PersonController (and other classes). In other words, I take the approach that SQL queries are "views" of a model. Please note that I've removed a lot of code and comments to make this brief. Please also note that I "kindasorta" use the Cocoa programming conventions for naming methods, hence the getter methods don't have the "get" prefix and the method names are long, descriptive, and full of prepositions :-). Example 1: This builds a select query using the primary key on the table. It adds the fields we want to select, and the where clause. Note that I have a special MySQLDatabaseConnection class that provides the functions to look up tables and fields from the database and return them as their appropriate object. FWIW, I do some caching in this class so that I don't have to perform a DESCRIBE or SHOW TABLES query each time I call one of those functions. public static function selectForPersonId($personId) { // create a new select query $query = new MySQLSelect(); // build the select clause $query->selectClause()->addField($database->fieldForNameWithTable('PERSON_ID', 'persons')); $query->selectClause()->addField($database->fieldForNameWithTable('name_first', 'persons')); $query->selectClause()->addField($database->fieldForNameWithTable('name_last', 'persons')); // build the from clause $query->fromClause()->addTable($database->tableForName('persons')); // build the where clause $query->whereClause()->conditions()->addCondition ( new MySQLConditional ( $database->fieldForNameWithTable('PERSON_ID', 'persons'), new MySQLOperator('='), new MySQLLiteral($personId) ) ); // return this new query return $query; } Example 2: This uses the product of the prior function as a template for a query that searches based on values of the current object. public static function selectForPerson(DataGeneralPerson $person) { // grab the generic select $query = self::selectForPersonId($person->personId()); // determine if we have an id if ($person->personId() > 0) { // return the query unmodified } else { // remove the person id and search by the attributes of this person // build a replacement where clause $query->setWhereClause(new MySQLWhereClause()); $query->whereClause()->conditions()->addCondition(new MySQLConditional ( $database->fieldForNameWithTable('name_first', 'persons'), new MySQLOperator('LIKE'), new MySQLString($person->firstName()) )); $query->whereClause()->conditions()->addCondition(new MySQLConditional ( $database->fieldForNameWithTable('name_last', 'persons'), new MySQLOperator('LIKE'), new MySQLString($person->lastName()) )); $query->whereClause()->conditions()->addCondition(new MySQLConditional ( $database->fieldForNameWithTable('name_middle', 'persons'), new MySQLOperator('LIKE'), new MySQLString($person->middleName()) )); } // return the finished query return $query; } Example 3: This uses the product of Example 1 as a starting point and simply adds a couple new fields to select and a couple new tables via left join. public static function selectWithContactInfoForPersonId($personId) { // start with the generic person select $query = self::selectForPersonId($personId); // add the additional fields $query->selectClause()->addField($database->fieldForNameWithTable('email_address', 'person_emails')); $query->selectClause()->addField($database->fieldForNameWithTable('email_label', 'person_emails')); $query->selectClause()->addField($database->fieldForNameWithTable('phone_number', 'person_phones')); $query->selectClause()->addField($database->fieldForNameWithTable('phone_label', 'person_phones')); // add the table joins ( new MySQLTableJoin ( null, 'LEFT', $database->tableForName('person_emails'), new MySQLConditionalGroup ( array ( new MySQLConditional ( $database->fieldForNameWithTable('PERSON_ID', 'persons'), new MySQLOperator('='), $database->fieldForNameWithTable('PERSON_ID', 'person_emails') ), new MySQLConditional ( $database->fieldForNameWithTable('email_label', 'person_emails'), new MySQLOperator('='), new MySQLString('Primary') ) ) ) ) ); $query->fromClause()->addTableJoin ( new MySQLTableJoin ( null, 'LEFT', $database->tableForName('person_phones'), new MySQLConditionalGroup ( array ( new MySQLConditional ( $database->fieldForNameWithTable('PERSON_ID', 'persons'), new MySQLOperator('='), $database->fieldForNameWithTable('PERSON_ID', 'person_phones') ), new MySQLConditional ( $database->fieldForNameWithTable('phone_label', 'person_phones'), new MySQLOperator('='), new MySQLString('Primary') ) ) ) ) ); // return this new query return $query; } I apologize for the length of this message. Please comment/question and let me know what you think. Thank you, Errol Sayre Web and Database Application Developer Office of Research and Sponsored Programs The University of Mississippi