[sqlalchemy] Re: Problems/Bug in ordering_list (UNIQUE KEY violation)

[oberger]

Thank's for your answer.



On 27 Feb., 02:14, jason kirtland j...@discorporate.us wrote:
 Michael Bayer wrote:

  On Feb 19, 2009, at 4:33 PM, oberger wrote:

  Thank you Michael,

  but I am not able to bring this to work. Even with a flush and a
  commit after every Statement.
  I understand the problem with dependend UPDATES/DELETES.

  But how is theordering_listsuposed to work?
  When I delete on entry with: del short_trip.trip_stops[1]

  and then flush() and commit(). Theordering_listhas to do some
  work in the corresponding database table.

  im not sure, perhaps Jason can chime in on this

 For this constraint configuration you might try making the DB constraint
 initially deferred.  Given the ordering of statement execution in the
 unit of work, no other ideas are coming to mind.  Theordering_list
 itself is totally ignorant of the ORM.  It doesn't issue any flushes or
 deletions, though one could make an implementation that did embed that
 level of control over the unit of work.

 -j
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Problems/Bug in ordering_list (UNIQUE KEY violation)

[oberger]

Hi all,

I'm using Elixir  SQLAlchemy to run a routing Application.
The App will store people their trips and their corresponding
trip_stops.

I ran into a strange problem with the ordering_list.
I included the source of the Person table to be complete.

If I run this code:

oli = model.Person(login='oberger', email = 'my_mal',
password='secrt', last_name='Berger', first_name = 'Oliver')
short_trip = model.Trip(description='short trip')
oli.trips.append(short_trip)

ts = model.Trip_Stop(adress='Town1')
ts2 = model.Trip_Stop(adress='Town2')
ts3 = model.Trip_Stop(adress='Town3')
ts4 = model.Trip_Stop(adress='Town4')

short_trip.trip_stops.append(ts)
short_trip.trip_stops.append(ts2)
short_trip.trip_stops.append(ts3)
short_trip.trip_stops.insert(2, ts4)
model.Session.commit()

del short_trip.trip_stops[1]
model.Session.commit()

I'll get this error from the oracle database:

IntegrityError: (IntegrityError) ORA-1: Unique Constraint
(TRAV_OWNER.SYS_C007296) verletzt
 'UPDATE trip_stop SET waypoint_nr=:waypoint_nr WHERE
trip_stop.trip_sid = :trip_stop_trip_sid AND
 = :trip_stop_waypoint_nr' {'trip_stop_waypoint_nr': 2, 'waypoint_nr':
1, 'trip_stop_trip_sid': 1}


I am an oracle guy and know this database very well.
It seems that SQLAlchemy is updating the rows in table trip_stop in
the wrong order, so that a
UNIQUE KEY violation occours. If the updates are sent in the correct
order, no Error wil occour.
I think this is a bug.

Here is my model:
If anyone can help me, Thanks a lot

Oliver




class Person(Entity):
sid   = Field(Integer, Sequence('PERSON_SEQ'),
primary_key=True)
email = Field(String(60), required=True)
login = Field(String(20), required=True)
password  = Field(String(100) )
last_name = Field(String(50))
first_name= Field(String(40))
gender= Field(Boolean)
is_smoker = Field(Boolean)
zip_code  = Field(String(20))
city  = Field(String(80))
last_login= Field(DateTime, PassiveDefault(text
('sysdate')), required=True)
date_updated  = Field(DateTime, PassiveDefault(text
(sysdate)), required=True)
date_created  = Field(DateTime, PassiveDefault(text
(sysdate)), required=True)
locked_flag   = Field(Boolean, PassiveDefault('0'),
required=True)
verified  = Field(Boolean, PassiveDefault('0'),
required=True)
trips = OneToMany('Trip')

def __repr__(self):
return '%r %r, email: %r, last_name: %s, first_name: %s' \
   % (self.__class__.__name__.capitalize(), self.login,
self.email, self.last_name, self.first_name)

@events.before_insert
@events.before_update
def encrypt_password(self):
if self.password:
self.password = encrypt_value(self.password)
self.password_check = self.password



from sqlalchemy.ext.orderinglist import ordering_list
class Trip(Entity):
sid   = Field(Integer, Sequence('TRIP_SEQ'),
primary_key=True)
person= ManyToOne('Person',colname =
'person_sid', ondelete='cascade' , required=True)
description   = Field(String(200), required=True)
seat_count= Field(Integer, PassiveDefault('1'),
required=True)
smoking_allowed   = Field(Boolean, PassiveDefault('0'),
required=True)
passenger_gender  = Field(String(1), PassiveDefault('A'),
required=True)
expired   = Field(Boolean, PassiveDefault('0'),
required=True)
date_updated  = Field(DateTime, PassiveDefault(text
(SYSDATE)), required=True)
date_created  = Field(DateTime, PassiveDefault(text
(sysdate)), required=True)
type  = Field(String(1), PassiveDefault('S'),
required=True)
valid_from= Field(DateTime)
trip_stops= OneToMany('Trip_Stop',
collection_class=ordering_list('waypoint_nr'),
  order_by='waypoint_nr',
cascade=all, delete, delete-orphan)

def __repr__(self):
return '%r %r, desc: %r' \
   % (self.__class__.__name__.capitalize(), self.sid,
self.description)


from sqlalchemy.ext.orderinglist import ordering_list


class Trip_Stop(Entity):
trip  = ManyToOne('Trip',colname = 'trip_sid',
ondelete='cascade', primary_key=True)
waypoint_nr   = Field(Integer, primary_key=True)
adress= Field(String(200), required=True)
geo_x = Field(Float, default=0, required=True)
geo_y = Field(Float, default=0, required=True)
duration_in_minutes   = Field(Integer, default=7,
required=True)
price_in_euro = Field(Numeric(precision=22, scale=2,
asdecimal=True