[DB-SIG] Table Oriented Programming
M.-A. Lemburg
mal at egenix.com
Fri Jan 28 22:15:45 CET 2005
Brad Clements wrote:
> 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.
I don't think that writing the query using some algebra form
will make things easier to comprehend.
The problem with your query generator is not the complexity of
the generated SQL, it's the many conditions and options that
make things hard to read.
> 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:
I'd try to break down this monster into functional chunks that
work on a query object (adding or moving things around as necessary)
rather than trying to do everything in one function. Mapping
options to methods is also worth a try.
> 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)
>
>
>
--
Marc-Andre Lemburg
eGenix.com
Professional Python Services directly from the Source (#1, Jan 28 2005)
>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________
::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::
More information about the DB-SIG
mailing list