[Zope-DB] Re: Trying to catching ZPsycopg exceptions aborts the transaction

[Charlie Clark]

On 2004-11-30 at 23:54:07 [+0100], Josef Meile <jmeile at hotmail.com> wrote:
> Hi,
> 
> > Every time I see FASTER in connection with databases I get sceptical. 
> > FASTER
> > in what sense? Execution speed is in my experience not generally an issue
> > (of course, there are lots of cases where it is important), FASTER
> > maintenance, is.
> Perhaps you are right. I haven't done a real test, so, I can assure
> this.

?

>  > I think you need to rethink your application. CONSTRAINTS is to
>  > enforce data integrity and should not be part of an application. There
>  > are times for wanting to go outside Zope's transaction machinery but
>  > this is easy enough using an external method and well-documented in
>  > these archives. But that isn't the issue, I think.
> Isn't the same if you do this from a python product, which is what I'm
> actually doing?

Yes, it is. If you really want to go outside Zope's transaction management 
then check the archives for how to do this.
 
> > This seems unnecessary complex and, dare I say it, redundant and thus 
> > likely
> > to cause problems.
> Yes, I know it is redundant, but it was a way to find overlapping
> ranges. I'm not happy with it either.

You may need to create a "booking" type which you can query sensibly. 
Personally I would always use a datetime type rather than a timestamp.

SELECT resourceId Reservation
WHERE resourceId = <dtml-sqlvar resourcId type="int">
AND startTime > <dtml-sqlvar endTime type="string">
AND endTime < <dtml-sqlvar startTime type="string">

If this returns a value, then you can make a reservation. If the result set 
is empty then you can query for appropriate times and pass them back to the 
user.

> > Why will it take longer? How much longer will it take? Does it really 
> > matter
> > here? What about if these requests are running concurrently?
> > 
> > You need to consider your model carefully and check for the existance of
> > others - there must be because this is a standard situation.
> > 
> > As Dieter suggests explicit implentation of your logic: checking for
> > availability before booking makes more sense. You can then return
> > appropriate information to the user to act upon.
> > "RESOURCE XYZ is not available for the requested time.
> > It is available from AA:BB hours to CC:DD hours"
> > 
> > Actually you might want to consider the more general issue and whether a
> > rules engine might not be helpful. After all: how do hotels deal with this
> > problem?
> Yes, I'm also wondering how do they deal with the race conditions. You
> said that one could return the appropiate information to the user, but
> as you know html is static and if the user takes, let's say, 5 seconds
> to do the reservation, the information may be outdated. Anyway, I guess
> after all one has to catch the exception some how.

In the scenario you are describing it is unlikely that this will be the case 
but whatever the situation, assuming the database is correctly configured, it 
will handle any possible conflicts correctly. Generally you present the user 
with the information you know and then provide the opportunity to confirm the 
booking. As long as you inform the user that the system will have to check 
for final availability there shouldn't be any problems. I think there 
situations where you would want to keep the transaction open until the user 
has decided what to do but I don't see the need for that here.

Charlie
-- 
Charlie Clark
eGenix.com

Professional Python Services directly from the Source
 >>> 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 ! ::::