Ah...missed that part. Well, the triggers are fired per-row I believe. So, if you don't use a transaction explicitly, and just let SQLite run without one, it will be slower, but the resource usage should be much less.
On May 29, 10:31 am, Mike Conley <mconl...@gmail.com> wrote: > Harish said it was an embedded system, probably all resources are pretty > severely restricted. > -- > Mike Conley > > On Fri, May 29, 2009 at 9:44 AM, Randy Syring <ra...@rcs-comp.com> wrote: > > > Mike, > > > Well...I am not sure. I thought SQLite held transaction details in > > a .journal file and not in memory. I thought that the memory use > > might actually be a Python problem and not a result of SQLite. If my > > thoughts are correct, using the FK approach should keep deleting the > > children in SQLite, which should use a journal file, which should > > reduce memory usage. > > > But I could be wrong. :) > > > On May 29, 9:28 am, Mike Conley <mconl...@gmail.com> wrote: > > > Randy, > > > > Interesting approach to foreign key management. Harish indicates he is > > > having a problem with restricted memory. Won't that still be true with > > > triggers? After all, if the problem is that the transaction is too big, > > it > > > will still be too big with all the pending deletes executed in a trigger. > > > > -- > > > Mike Conley > > > > On Fri, May 29, 2009 at 8:47 AM, Randy Syring <ra...@rcs-comp.com> > > wrote: > > > > > Another solution is to use triggers in SQLite to enforce FK > > > > relationships. > > > > >http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers > > > >http://code.google.com/p/sqlitefktg4sa/ > > > > > On May 29, 6:59 am, Mike Conley <mconl...@gmail.com> wrote: > > > > > One solution is to change the commit strategy; issue commits > > periodically > > > > > during the loop. > > > > > > parentlist = session.query(Parent).all() > > > > > count = 0 > > > > > for parent in parentlist: > > > > > session.delete(parent) > > > > > count += 1 > > > > > if count % 100 == 0 # use whatever frequency is needed > > > > > count = 0 > > > > > session.commit() > > > > > if count > 0: > > > > > session.commit() # this gets the last group of deletes > > > > > > The disadvantage of this approach is that you lose the ability to > > > > rollback > > > > > the entire delete process, and now must handle that problem with > > > > application > > > > > design. > > > > > > -- > > > > > Mike Conley > > > > > > On Fri, May 29, 2009 at 4:08 AM, Harish Vishwanath < > > > > harish.shas...@gmail.com > > > > > > > wrote: > > > > > > Hello, > > > > > > > I am running Sqlite/SQLA/Elixir on an embedded system. I have > > different > > > > > > classes with OneToMany relationships and I have configured cascade > > = > > > > "all, > > > > > > delete, delete-orphan" correctly on them. However, for this to > > work, I > > > > > > should do something like : > > > > > > > parentlist = session.query(Parent).all() > > > > > > for parent in parentlist: > > > > > > session.delete(parent) # > > > > > > session.commit() > > > > > > > The above chokes the system since it has limited memory. > > > > > > > The statement below : > > > > > > > session.query(Parent).delete(), issues DELETE FROM PARENT; > > > > > > > This is memory efficient, but it doesn't delete the child objects > > since > > > > > > Sqlite doesn't impose FK constraints. > > > > > > > Is there any way to solve this problem? > > > > > > > Regards, > > > > > > Harish --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
