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

[Josef Meile]

Hi Charlie,

>>>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.
Here I meant that I haven't done any test to messure 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.
So, I will try 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.
Actually, I when using Zope, I use a DateTime, but the database calls
it 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.
That's quite similar to my "show reservations on a week" query. On my
case, I show the user a table with all the days of the week and the
status of the resource on each period of time. So, here I will have to
do the check only once and not twice. However, with or without check, 
conflicts can still happen, so, I will have to catch them with an
external method.

Thanks,
Josef