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 [EMAIL PROTECTED], 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 [EMAIL PROTECTED], 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@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 _______________________________________________ Zope maillist - Zope@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 )
