I have not thought about this too much beyond my gut-instinct problem about where it is being done (in web2py), and the suggested alternative (in sqlite, with triggers)... so - is another collaborating system (lets say a django app) sets up triggers for on-cascade in sqlite, and you (web2py) commit a transaction that begins with the deletion of a record, sqlite then processes it's triggers (which someone else setup) to delete associated references, and then your code to delete the associated references (which are now no longer there, due to the triggers) - what will happen to the transaction?
This is at the heart of my concern - not sure if it will really be a problem, but it _seems_ problematic to me. On Sat, Sep 12, 2009 at 9:16 AM, mdipierro <[email protected]> wrote: > > I still do not see why this may a problem since the operation is done > in a transaction but, I will look into it. > > On Sep 12, 2:03 am, Yarko Tymciurak <[email protected]> wrote: > > On Fri, Sep 11, 2009 at 11:24 AM, mdipierro <[email protected]> > wrote: > > > > > The new mechanism only kicks in if you have SQLITE and the references > > > are set as ondelete='CASCADE' (the default). > > > If you try to access a non sqlite database nothing changes > > > If you try to access an existing sqlite database, then set > > > oncascade='' and migrate=False. > > > I cannot think of a situation where this can create conflict. > > > > If you have web2py doing "web2py cascade" on sqlite db named "a"; > > AND > > you have (something else) creating sqlite records, and does "cascade > > triggers" in sqlite db named "a" (co-accessed by web2py &, say, django) > then > > you have trouble. > > > > I suggest this be re-implemented to do cascade the way sqlite suggests. > > (seehttp://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers) > > > > > > > > > On Sep 11, 11:18 am, Yarko Tymciurak <[email protected]> wrote: > > > > On Fri, Sep 11, 2009 at 1:42 AM, mdipierro <[email protected]> > > > wrote: > > > > > > > On Sep 11, 1:19 am, Richard <[email protected]> wrote: > > > > > > > on delete cascade in SQLite > > > > > > > > Fantastic! My feature request for this can be closed: > > > > >http://code.google.com/p/web2py/issues/detail?id=50 > > > > > > > Can you do it or should I do it? > > > > > > > > Did the solution involve sqlite triggers? > > > > > > > No. The solution is quite dumb. recursively delete all referencing > > > > > records. I guess it could be improved using triggers. > > > > > > Yes - I was going to mention this too when I saw it.... There is a > > > pattern > > > > on SQLite pages somewhere (I pointed someone to them once). > > > > > > My immediate concern is about web2py only activity taking care of an > > > > external db - and what is another app is cooperating on the DB (like > we > > > are > > > > doing for PyCon - either web2py or django...)... > > > > > > What is the db exists, and already has triggers to handle cascade? > What > > > > happens then? > > > > What is another application assumes / depends on the DB to have the > > > tirggers > > > > (so one - web2py - does the cascade action, but the other doesn't). > > > > > > Make sense? I think this > > > > needs more thought. > > > > > > - Yarko > > > > > > > > > row = db(db.mytable.id>0).select().first() > > > > > > > > very convenient > > > > > > > > Richard > > > > > > > > On Sep 11, 3:12 pm, mdipierro <[email protected]> wrote: > > > > > > > > > I have made some changes to the code in trunk: > > > > > > > > > 1) Since tonight I attended a presentation about how SQLAlchemy > can > > > do > > > > > > > on delete cascade in SQLite even if SQLite does not support it, > I > > > > > > > implemented this feature in web2py too. There is nothing you > have > > > to > > > > > > > do it now there by default. For example: > > > > > > > > > db=DAL('sqlite://test.db') > > > > > > > db.define_table('a',Field('name')) > > > > > db.define_table('b',Field('a',db.a,ondelete='CASCADE'),Field('name')) > > > > > > > db.a.insert(name='xxx1') > > > > > > > db.a.insert(name='xxx2') > > > > > > > db.a.insert(name='xxx3') > > > > > > > db.a.insert(name='xxx4') > > > > > > > db.b.insert(a=2,name='yyy1') #(1) > > > > > > > db.b.insert(a=2,name='yyy2') #(1) > > > > > > > db.b.insert(a=3,name='yyy3') > > > > > > > db(db.a.id==2).delete() > > > > > > > for row in db(db.b.a==2).select(): print row # prints nothing > as it > > > > > > > should > > > > > > > > > Notice ondelete='CASCADE' is default. > > > > > > > > > 2) you can now do: > > > > > > > > > row = db(db.mytable.id>0).select().first() > > > > > > > row = db(db.mytable.id>0).select().last() > > > > > > > > > and row is None if no records are selected. > > > > > > > > > 3) I fixed the problem with the memory leak reported by > zahariash > > > > > > > > > 4) I refactored some code in main.py, compileapp.py and > globals.py. > > > > > > > This should make the code cleaner. Hopefully I did not break > it. > > > > > > > Please give it a try. > > > > > > > > > Massimo > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "web2py-users" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/web2py?hl=en -~----------~----~----~----~------~----~------~--~---
