[Zope] ZSQLMethod conditional insert

[Thomas Olsen]

Hi David

On Wednesday den 27. July 2005 23:50, David Pratt wrote:
> Hi Thomas. I would say postgres is better for this sort of thing.  In
> postgres, you can write a function in plpgsql or other function
> language (there is also a python function language) that you install in
> postgres. In any case once you write it and then call it - it is
> executed in a single transaction.  You are only left with selecting the
> function in your zsql from your script. 

I would prefer postgres too but it is decided to use MySQL in the project...

> It is hard to comment on the 
> method you have sketched out not knowing what arguments you are passing
> into the method. You have identified path but I see others that you
> haven't discussed so don't really know where you plan on getting them
> from.

Well "path" is actually the only argument passed to the method. "select 
@lastval:=num" is a (I think) MySQL specific way of assigning values to local 
variables. I could probably use a sub-select or something but my SQL is a bit 
rusty ;-)

> Regards,
> David
>
> On Wednesday, July 27, 2005, at 03:22 PM, Thomas Olsen wrote:
> > Hi
> >
> > This is probably an FAQ but I haven't been able to find and answer.
> >
> > I need a counter for a certain CMFType and want to store the hits in a
> > MySQL
> > database not to fill up the ZODB. I've checked to two suggestions
> > below but
> > they both use two ZSQLMethods first to check if the URL is already in
> > the
> > table, then to do the actual update or insert.
> >
> > http://www.zope.org/Members/element/Simple_SQL_Page_Counter
> > http://zopelabs.com/cookbook/991116439
> >
> > I'd like to be able to do that just in one ZSQLMethod for efficiency
> > but I
> > cant seem to figure out a way of doing it.
> >
> > The table is very simple:
> >
> > CREATE TABLE mostread (
> >   path varchar(255) NOT NULL default '',
> >   num bigint(20) NOT NULL default '0',
> >   dt datetime NOT NULL default '0000-00-00 00:00:00',
> >   PRIMARY KEY  (path)
> > )
> >
> > For now my ZSQLMethod get the argument "path" which is a relative URL
> > and it
> > looks like this:
> >
> >   select @lastval:=num from mostread where <dtml-sqltest path
> > type="string">
> >   <dtml-var sql_delimiter>
> >   update mostread set num=@lastval+1, dt=now() where <dtml-sqltest path
> > type="string">;
> >   </dtml-if>
> >
> > But that naturally only works if there is already a record containing
> > "path".
> > What I want to do is something like this (pseudo-code):
> >
> >   select @lastval:=num from mostread where <dtml-sqltest path
> > type="string">
> >   <dtml-var sql_delimiter>
> >   <dtml-if "sequence-length > 0">
> >     update mostread set num=@lastval+1, dt=now() where <dtml-sqltest
> > path
> > type="string">;
> >   <dtml-else>
> >     insert into mostread(path, num, dt)
> >       values(<dtml-sqlvar path type="string">, 1, now())
> >   </dtml-if>
> >
> > Is there a way of doing this or should I just create that extra
> > ZSQLMethod?
> >
> > --
> > Med venlig hilsen
> >
> > Thomas Olsen
> > http://www.headnet.dk
> > _______________________________________________
> > Zope maillist  -  Zope at zope.org
> > http://mail.zope.org/mailman/listinfo/zope
> > **   No cross posts or HTML encoding!  **
> > (Related lists -
> >  http://mail.zope.org/mailman/listinfo/zope-announce
> >  http://mail.zope.org/mailman/listinfo/zope-dev )

-- 
Med venlig hilsen

Thomas Olsen
http://www.headnet.dk