Re: DBM Compatibility layer

this is what looks like a different approach - it gets away from the build/execute and produces a more abstract methodoly. http://www.akbkhome.com/Projects/db_oo+-+an+object+based+Sql+builder+and+wrapper+for+peardb/db_oo.class.html On a base level some examples of usage /------------------------------ mulitiple fetch. SELECT *,DATE('YYYY-mm-dd',creation_date) as created FROM Group WHERE firstname='abc' and age > 12 limit 12; $group = new Group; $group->firstname = "abc"; $group->find() $group->limit(12); $group->condition_append('age > 12'); $group->select_add("DATE('YYYY-mm-dd',creation_date) as created"); while ($group->fetch()) { echo $group->id; } /------------------------------ single fetch (cached) SELECT * FROM group WHERE id=12; $group = db_oo::_static_get("Group",12); or $group = new Group; $group->get(12); or $group = new Group; $group->get("id",12); /------------------------------ deleting DELETE FROM Group WHERE id=12; $group = new Group; $group->id=12; $group->delete(); /------------------------------ updating UPDATE group SET firstname='freddy' WHERE id=12 $group = db_oo::_static_get("Group",12); $group->firstname = "freddy"; $group->update(); /------------------------------ inserting INSERT INTO group (firstname) VALUES ('freddy') $group = new group; $group->firstname = "freddy"; $group->insert(); There are quite a few more feature that need more documentation working on, but it the design of it is to help enforce putting all data manipulation / stored selects etc into the extended class, rather than the scripts - so that you would probably end up making Group::list_pear_developers(); // which which would store the builder for that select in the class - hence helping enforce code reuse, rather than splattering this around the application.... I also started looking at DBM compatiblity - but other than simple insert/updates - the complex selects where a bit much for it... regards alan Stig S. Bakken wrote:

I think a query builder is a great idea. If you want it to happen, it seems that you'd have to be The Querybuilder Man for PEAR. If you're up to it, write an RFC or flesh out your ideas below, or simply start coding. What the best package name would be really depends on what the featureset ends up like. - Stig On Wed, 2002-04-24 at 03:40, Brent Cook wrote:

On Fri, 19 Apr 2002, Lukas Smith wrote:

Yeah I doubt that an SQL parser is the way to go ... If you want to add another layer of abstraction I would rather suggest having the user not write SQL at all and rather build queries via a php object. Then you could do stuff like subselects and joins (or emulate the feature if it's missing). This would we be the easiest (although still very time consuming) to get true abstraction while still making use of all the advanced features that db's offer. That would do away with stuff like PEAR DB or Metabase (and of course the merger of the two that I am currently working on). That's why I don't think that a query builder should be build in top of a "traditional" db abstraction layer but more as a replacement (or while its being developed in parallel).

I didn't want to copy that entire thread, but the summary is that I had asked how different queries work between DB_ldap and DB, with regards to how we could create a DB-style interface to DBM-type databases. The solution that I heard was that someone needs to develop a way to programatically generate queries. As an aside toward creating such a mechanism, the Table abstraction layer to work on top of the DBM is getting further along. It currently implements selects and sorts very simply as methods of Table. The signatures of these methods is as follows: function select ($rawQuery, $rows=null) function sort ($rawQuery, $order = '<', $rows=null) $rawQuery field is a string that the methods parse to determine how to select or sort. They both return a set or rows in the form of an associative array like this: array (rowKey1 => array(field1=>value1, field2=>value2, ...)

      rowKey2 => ...
      ...)

This is an example of their usage on a table with the following schema: HATS:

 type     (enum) {fedora, stocking cap, top hat, bowler}

quantity (integer)

 brand    (varchar)

$query = '(quantity >= 50) or (type != fedora)'; $sortField = 'quantity'; $results = $table->sort($sortField, '>', $table->select($query)); When this is run, $results contains an array with the rows for which quantity >= 50 or that are not fedoras, sorted in descending order. These results are easily manipulated: echo "Query: $query\n"; echo "Sorting by: $sortField, descending order\n"; echo "Results:\n"; foreach ($results as $result)

    echo "brand = {$result['brand']}, quantity = {$result['quantity']}\n";

yields: Query: quantity >= 50 Sorting by: quantity, descending order Results: brand = Shilanda's Hats, quantity = 800 brand = Travis' Hats, quantity = 60 Does a variant of this simple chained query system seem reasonable for a query manager? I can see the need with remote databases to queue up parts of a query in order to execute them more efficiently, so calling one function at a time isn't the best idea. Perhaps a query manager could take this general form: class queryManager { var $queryStack; function reset () { $this->queryStack=array(); } function select ($rawQuery) { $this->queryStack[] = array ('type'=>'select', $query=>$rawQuery); } ... function execute () { foreach ($this->queryStack) {

     // build sql, make calls to methods on a DBM Table, etc.

} } What do you think? - Brent -- PEAR Development Mailing List (http://pear.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php