[DB-SIG] Table Oriented Programming
Brad Clements
bkc at murkworks.com
Fri Jan 28 22:12:01 CET 2005
On 28 Jan 2005 at 12:35, Robert Brewer wrote:
> Why is it interesting to you? What benefits do you see over:
>
> john = employees.findfirst(name='john')
> print john.Department.name
>
If only my life was so easy, vs:
select
first 5000
s.shipmentid, s.ownerid, s.deliverydate, s.shipmentstate, s.shipdate, s.etadatetime,
s.billing, p.buyerspurchaseordernumber, p.salesordernumber, p.pieces,
p.publictrackingnumber, p.trackingnumber, ro.party, ro.customerrole, ato.company as
shiptocompany, ato.postal_code as shiptozip, ato.city as shiptocity, ato.state_province
as shiptostate, afrom.company as shipfromcompany, afrom.postal_code as
shipfromzip, afrom.city as shipfromcity, afrom.state_province as shipfromstate,
ce.documentid, ce.entryid, ce.shipmentid as entryshipmentid, dc.pagecount,
cs.carrierid, cs.servicetype
from
Shipment s inner join Package p on (s.shipmentid = p.shipmentid)
inner join PartyShipRelation pss on (s.id = pss.shipment_id)
inner join Roles ro on (pss.role_id = ro.id)
left join address ato on (s.shipto_id = ato.id)
left join address afrom on (s.shipfrom_id = afrom.id)
left join CustomsEntryShipLink cesl on (s.shipmentid = cesl.shipmentid)
left join CustomsEntries ce on (cesl.entryid = ce.entryid)
left join DocumentCatalog dc on (ce.documentid = dc.documentid)
inner join CarrierServices cs on (s.carrierserviceid = cs.serviceid)
where
( (upper(s.shipmentid) not like 'SFIEXP%') and (s.shipdate >= ?) and (s.shipdate
< ?) ) and
( (pss.party_id = ?) )
order by s.shipdate desc, s.shipmentid, p.trackingnumber
And that's a simple query.
I have 22 variables or so that can be used to control fieldlist, the where portion, and the
join.
also, the order of joins and where statements greatly impacts the performance of the
query. This is why I said in my first post that performance would be an issue, because
I'd need a way to specify which clauses are more important.
The code that creates the above query (or those like it) is getting pretty long. I'm
interested in more expressive mechanisms that still retain the ability to understand, vs
this:
def __generateDataSpecifier(self,dataSet,criteria=None, opts={}):
"""return a dataspecifier for use by rowFactory"""
if dataSet == 'Default':
dataSet = 'ShipmentList'
csvmode = False
if dataSet in ('ShipmentList','ShipmentListCSV', 'ShipmentCharges'):
if dataSet == 'ShipmentCharges':
shipcols = self.shipmentChargesColumns
packageColumns = ()
queryShipmentCharges = True
else:
queryShipmentCharges = False
shipcols = (self.dbtype == 'gvib' and self.shipmentListColumnsInterbase) or self.shipmentListColumns
packageColumns = ('buyerspurchaseordernumber','salesordernumber',
'pieces', 'publictrackingnumber', 'trackingnumber')
if dataSet == 'ShipmentListCSV' or opts.get('includeshipmentdetails'):
csvmode = True
shipcols += ((computedColumn('totalcharge',self.totalChargeSQL)),
(computedColumn('totalpieces',self.totalPiecesSQL)),
'carrierbilltoacctnumber',
'shiptoresidential',
'shipfromtelephone',
'billtotelephone',
'shiptotelephone',
'shiptoattention',
'billtoattention',
'shipfromattention',
)
if opts.get('includeactual'):
shipcols += (computedColumn('totalactual',self.totalActualSQL),)
if opts.get('includepublished'):
shipcols += (computedColumn('totalpublished',self.totalPublishedSQL),)
if opts.get('includeeta'):
shipcols += ('etadatetime', )
dsShipment = dataSpecifier(tablename=self.tableName,prefix="s",
columns=shipcols,criteria=criteria)
addressToCritera = opts.get('shiptoCriteria')
dsAddressTo = dataSpecifier(tablename='address', prefix='ato',
columns=csvmode and self.csv_addressToColumns or self.addressToColumns,
criteria=addressToCritera)
dsAddressFrom = dataSpecifier(tablename='address', prefix='afrom',
columns=csvmode and self.csv_addressFromColumns or self.addressFromColumns)
if csvmode:
dsAddressBillTo = dataSpecifier(tablename='address', prefix='abill',
columns=self.csv_addressBillToColumns)
else:
dsAddressBillTo = None
packageCriteria = opts.get('packageCriteria')
dsPackage = dataSpecifier(tablename='Package',prefix="p",
columns=packageColumns, criteria=packageCriteria)
dsCarrierServices = dataSpecifier(tablename='CarrierServices',prefix='cs',
columns=('carrierid','servicetype'))
partyCriteria = opts.get('partyCriteria')
dsPartyShipRelation = dataSpecifier(tablename='PartyShipRelation', prefix='pss',
columns=(),
criteria=partyCriteria,
)
roleCriteria = opts.get('partyRelationCriteria')
dsrole = dataSpecifier(tablename="Roles", prefix="ro",
columns=('party','customerrole'),
criteria=roleCriteria,
)
hotlistCriteria = opts.get('hotlistCriteria')
if hotlistCriteria:
dsShipmentHotlistLink = dataSpecifier(tablename='ShipmentHotlistLink',prefix="shl",
columns=(), criteria=hotlistCriteria)
else:
dsShipmentHotlistLink = None
entryCriteria = opts.get('entryCriteria')
if entryCriteria:
dsCustomsEntryShipLink = dataSpecifier(tablename='CustomsEntryShipLink',prefix="cesl",
columns=(), criteria=entryCriteria)
else:
dsCustomsEntryShipLink = dataSpecifier(tablename='CustomsEntryShipLink',prefix="cesl",
columns=() )
dsCustomsEntry = dataSpecifier(tablename='CustomsEntries',prefix="ce",
columns=('documentid', 'entryid',
computedColumn('entryshipmentid','ce.shipmentid as entryshipmentid'),
)
)
dsDocumentCatalog = dataSpecifier(tablename='DocumentCatalog',prefix="dc",
columns=('pagecount',) )
if self.dbtype == 'gvib':
if dsShipmentHotlistLink:
tables = [
RelatedTable(dsShipmentHotlistLink, 'shipment_id', dsShipment, 'id', whichMany=1),
RelatedTable(dsShipment,'shipmentid',dsPackage,'shipmentid'),
]
elif entryCriteria:
if queryShipmentCharges:
tables = [
RelatedTable(dsCustomsEntryShipLink, 'shipmentid', dsShipment, 'shipmentid' ,whichMany=1),
RelatedTable(dsShipment,'shipmentid',dsPackage,'shipmentid'),
]
else:
tables = [
RelatedTable(dsCustomsEntryShipLink, 'shipmentid', dsShipment, 'shipmentid' ,whichMany=1),
RelatedTable(dsCustomsEntryShipLink, 'entryid', dsCustomsEntry, 'entryid', whichMany=1),
RelatedTable(dsCustomsEntry, 'documentid', dsDocumentCatalog, 'documentid', whichMany=1),
RelatedTable(dsShipment,'shipmentid',dsPackage,'shipmentid'),
]
elif packageCriteria:
tables = [
RelatedTable(dsPackage,'shipmentid', dsShipment,'shipmentid', whichMany=1),
]
else:
tables = [
RelatedTable(dsShipment,'shipmentid',dsPackage,'shipmentid'),
]
if queryShipmentCharges:
dsCharges = dataSpecifier(tablename='Charges',prefix="c",
columns=self.chargeColumns)
dsChargeCodes = dataSpecifier(tablename='chargecodes',prefix='cc',
columns=('shortdescription', 'chargeclass'))
tables.extend(
[
RelatedTable(dsShipment, 'shipmentid', dsCharges, 'shipmentid'),
RelatedTable(dsCharges,'chargecodeid',dsChargeCodes,'id')
]
)
if partyCriteria or roleCriteria:
tables.extend(
[
RelatedTable(dsShipment, 'id', dsPartyShipRelation, 'shipment_id', whichMany=(not partyCriteria) and 1 or None),
]
)
if roleCriteria:
tables.append(
RelatedTable(dsPartyShipRelation, 'role_id', dsrole, 'id', whichMany=(not partyCriteria) and 1 or None),
)
if addressToCritera:
tables.append(
RelatedTable(dsShipment, 'shipto_id', dsAddressTo, 'id', whichMany=(not addressToCritera) and 1 or None),
)
else:
tables.extend(
[
RelatedTable(dsShipment, 'id', dsPartyShipRelation, 'shipment_id', whichMany=(not partyCriteria) and 1 or None),
RelatedTable(dsPartyShipRelation, 'role_id', dsrole, 'id', whichMany=(not partyCriteria) and 1 or None),
RelatedTable(dsShipment, 'shipto_id', dsAddressTo, 'id', whichMany=(not addressToCritera) and 1 or None),
RelatedTable(dsShipment, 'shipfrom_id', dsAddressFrom, 'id', whichMany=1),
]
)
if dsAddressBillTo:
tables.append(
RelatedTable(dsShipment, 'billto_id', dsAddressBillTo, 'id', whichMany=1)
)
if not queryShipmentCharges:
if not entryCriteria:
tables.extend(
[
RelatedTable(dsShipment, 'shipmentid', dsCustomsEntryShipLink, 'shipmentid', whichMany=1),
RelatedTable(dsCustomsEntryShipLink, 'entryid', dsCustomsEntry, 'entryid', whichMany=1),
RelatedTable(dsCustomsEntry, 'documentid', dsDocumentCatalog, 'documentid', whichMany=1),
]
)
tables.append(
RelatedTable(dsShipment,'carrierserviceid',dsCarrierServices,'serviceid')
)
ds = Join(*tables)
else:
raise RuntimeError("not supported")
tables = [RelatedTable(dsShipment,'shipmentid',dsPackage,'shipmentid'),
RelatedTable(dsShipment,'carrierservice_id',dsCarrierServices,'id')]
if dsCustomsEntryShipLink:
raise RuntimeError("can't handle customs entries link")
ds = Join(*tables)
else:
raise ValueError("Unsupported dataSet (%s)" % dataSet)
if queryShipmentCharges:
ds.distinct = True
else:
ds.limit = SHIPMENT_LIST_LIMIT
return ds
Note that criteria look like this:
requirements.append(NOTLIKE('shipmentid', 'SFIEXP'))
or
if packageCriteria:
requirements.append(EQ('shipmentid',const='p.trackingnumber'))
opts['packageCriteria'] = AND(*packageCriteria)
--
Brad Clements, bkc at murkworks.com (315)268-1000
http://www.murkworks.com (315)268-9812 Fax
http://www.wecanstopspam.org/ AOL-IM: BKClements
More information about the DB-SIG
mailing list