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 ! ::::
_______________________________________________
DB-SIG maillist - [email protected]
http://mail.python.org/mailman/listinfo/db-sig
