[PEAK] Status of peak.query.algebra

[Phillip J. Eby]

There's now prototype "SQL from relational algebra" code in CVS.  The API 
is still subject to significant flux, error handling ranges from OK to 
poor, and certain capabilities are still missing, but here's an example or two:


db = PhysicalDB(
     tables = Items(
         Branch = (
             'headempnr','branchnr','cityname','statecode','country'
         ),
         Employee = (
             'empnr','empname','branchnr','salary','cityname','statecode',
             'country', 'supervisor_empnr', 'mainphone', 'otherphone'
         ),
         Speaks = ('empnr','languagename'),
         Drives = ('empnr','carregnr'),
         Car = ('carregnr','carmodelname','color'),
         LangUse = ('languagename','country'),
     )
)

Branch = db['Branch']
print Branch(where=Branch['branchnr'].eq(42)).simpleSQL()

# above prints:
#   SELECT B1.* FROM Branch AS B1 WHERE B1.branchnr=42


Employee = db['Employee']
MAX = aggregate('MAX')

print Employee(
     groupBy=['branchnr'],  # group-by columns
     keep=['branchnr'],     # select only these columns
     calc=Items(            # add computed columns
         maxSalary=MAX(Employee['salary'])
     )
).simpleSQL()

# above prints:
#   SELECT E1.branchnr, MAX(E1.salary) AS maxSalary
#     FROM Employee AS E1
#    GROUP BY E1.branchnr

There are even more interesting examples (e.g. HAVING, SELECT ... FROM 
(SELECT ...)) in the test suite.


At this point the primary missing relational capabilities are:

* Subqueries in conditions (e.g. "WHERE EXISTS (SELECT ...)")
* Outer joins
* Parameters
* Driver-specific SQL generation
* Type metadata and conversions

And the missing general capabilities are:

* query compilation/execution
* conceptual query translation to relational algebra


If you'd like to play around with what's there so far, and let me know if 
you find any oddities, I'd appreciate it.  Especially if you can formulate 
your question or problem in the form of a test case.  :)  Note also that 
the current interface docs don't necessarily reflect the actual API at the 
moment.  So, when in doubt, consult the test cases for how things are 
supposed to work.