[Zope3-dev] Re: RDB support

[Stuart Bishop]

On Friday, April 12, 2002, at 01:40  AM, Matthew T. Kromer wrote:

> Stephan Richter wrote:
>
>>> You probably want some kind of querybuilder object which keeps track 
>>> of its own state, and either knows about all kinds of DBAPI 2.0 
>>> parameter binding styles, or leverages a DA-specific binding routine.
>>
>>
>> But shouldn't this be on a higher level? What if I do not want this 
>> service? Or do you mean more you want to replace what is currently 
>> done with DTML in SQL Methods?
>>
>
> The idea is to enable easier replacement of the DTML/SQL method code. 
> Also, you want something that forces a singular parameter binding 
> function onto the DA, rather than having the higher level code be more 
> versatile (and understand the 5 or so different types of parameter 
> binding.)  The DA can then convert to the underlying adapter.

But taking this route may limit the usability of ZSQL methods (or 
perhaps that should
be abusibility...). If we replace DTML, it needs to be replaced with 
something as
powerful.

One option would be to rely on the DA. We would call the DA, passing it 
a callable
object and arbitrary arguments. The callable object would return the
SQL-with-placeholders and our parameters to bind. This would allow me to 
stop
using disgusting DTML to generate complex queries and instead use 
slightly less
disgusting Python. ZSQL Methods as per Zope 2 would then be trivial, and 
could be
dumped altogether unless we want a simple way of binding DTML to a DA.

class DataBaseAdaptor:
	def __call__(self,callable,*list,**kw):
		'generate an sql query and execute it'
		# Cache check goes here
		sql,params = callable(*list,**kw)
		sql,params = self._munge(sql,params)
		return self.cursor.execute(sql,params) # Or a more intelligent 
resultset
	def _munge(self,sql,params):
		'mangle our sql and parameters into the format used by our 
adaptor as per DBAPI2.0'
		return (sql,params)


Gratuitous example of a real life ZSQL Method:

<dtml-comment>
     Execute an 'and' search on metadata.
     eq_args and like_args is a list of (metadata_id,value) tuples.
</dtml-comment>

<dtml-in eq_args prefix=s>
     <dtml-unless s_start>
         INTERSECT
     </dtml-unless>

     SELECT user_id_fk AS user_id
     FROM cg_contact_data
     WHERE metadata_id_fk = <dtml-sqlvar s_key type=int>
         AND value = <dtml-sqlvar s_item type=nb>
</dtml-in>

<dtml-if "like_args and eq_args">
     INTERSECT
</dtml-if>

<dtml-in like_args prefix=s>
     <dtml-unless s_start>
         INTERSECT
     </dtml-unless>

     SELECT user_id_fk AS user_id
     FROM cg_contact_data
     WHERE metadata_id_fk = <dtml-sqlvar s_key type=int>
         AND value = <dtml-sqlvar s_item type=nb>
</dtml-in>

<dtml-if "like_args and eq_args">
     INTERSECT
</dtml-if>

<dtml-in like_args prefix=s>
     <dtml-unless s_start>
         INTERSECT
     </dtml-unless>

     SELECT user_id_fk AS user_id
     FROM cg_contact_data
     WHERE metadata_id_fk = <dtml-sqlvar s_key type=int>
         AND lower(value) LIKE '%' || lower(<dtml-sqlvar s_item 
type=nb>) || '%'
</dtml-in>


--
Stuart Bishop <zen@shangri-la.dropbear.id.au>
http://shangri-la.dropbear.id.au/