[Zope] Re: ZSQLMethod conditional insert

[Christian Scholz]

Hi!

Might something like this help?

INSERT INTO table (a,b,c) VALUES (1,2,3)
       ON DUPLICATE KEY UPDATE c=c+1;

So this will catch errors and instead do the update.
Available from version 4.1.0 on as the docs say.

See also the docs:

http://dev.mysql.com/doc/mysql/en/insert.html

-- christian

PS: I might have misunderstood something ;-)


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?
>