[PEAK] Status of peak.query.algebra
Phillip J. Eby
pje at telecommunity.com
Fri Oct 24 16:58:41 EDT 2003
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.
More information about the PEAK
mailing list