Re: [sqlite] Running App state in db?
Thanks to everyone answering in this thread! On Wed, Mar 01, 2006 at 05:44:45PM -0800, Nathaniel Smith wrote: [...] > I don't know enough about your app to know if this would work, but > if you only have the one app instance managing locks, and locks expire > whenever that app dies... perhaps you can just have that app, on > startup, unconditionally erase all locks in the db? I'll do that probably anyway. (I'm considering killing the file and recreating the schema on "boot".) My app is a server and forks off some childs for the clients. So if the child crashes, those lock records should go away. Elrond
Re: [sqlite] Running App state in db?
On Thu, Mar 02, 2006 at 12:18:21AM +0100, Elrond wrote: > I intended to only put the locks as such in the db. > When it comes to a new lock, I'll select all relevant old > locks, that might conflict, handle the conflict check in > app logic and finally insert a new lock record. (all inside > a proper table lock/transaction). > > I just hoped for some cool way to let the db cleanup all > those records, when the session dies. I don't know enough about your app to know if this would work, but if you only have the one app instance managing locks, and locks expire whenever that app dies... perhaps you can just have that app, on startup, unconditionally erase all locks in the db? -- Nathaniel -- Details are all that matters; God dwells there, and you never get to see Him if you don't struggle to get them right. -- Stephen Jay Gould
Re: [sqlite] Running App state in db?
On Wed, Mar 01, 2006 at 12:44:47PM -0600, Jim C. Nasby wrote: [...] > Depending on your needs, you might be able to just lock a row for > updates and hold that lock. IE, open a seperate connection to the > database and do: > > BEGIN; > UPDATE process SET start_time = now() WHERE process_id = ?; > > And then 'sit' on that connection until you're done. When you're > finished, just issue a COMMIT. Note that some databases won't like you > leaving that transaction open a real long time, so it depends on what > you're doing if this will work. I also don't know if SQLite cares about > such things. [...] sqlite mostly cares about such things, but it goes the "lock the whole db" way. I'd love row level locks, right! I already considered adding some "this_row_locked" column to a table, just to emulate row level locking. Elrond
Re: [sqlite] Running App state in db?
On Wed, Mar 01, 2006 at 04:00:53PM -0600, Jim C. Nasby wrote: > On Wed, Mar 01, 2006 at 01:53:45PM -0800, w b wrote: > > Well, my locking data isn't as simple as "locked, not > > locked". The resource has ranges that can be locked, and it > > can be locked for reading (shared) and writing (exclusive). > > It's not really fun. > > Sounds to me like the best bet is to put the 'resources' into a database > and let it handle the locking... Letting the db handle it, isn't really an option: For starters, I can't put any possible range of the resource into the db. I intended to only put the locks as such in the db. When it comes to a new lock, I'll select all relevant old locks, that might conflict, handle the conflict check in app logic and finally insert a new lock record. (all inside a proper table lock/transaction). I just hoped for some cool way to let the db cleanup all those records, when the session dies. (not, that I have that now ;) ) Elrond
Re: [sqlite] Running App state in db?
On Wed, Mar 01, 2006 at 01:53:45PM -0800, w b wrote: > Well, my locking data isn't as simple as "locked, not > locked". The resource has ranges that can be locked, and it > can be locked for reading (shared) and writing (exclusive). > It's not really fun. Sounds to me like the best bet is to put the 'resources' into a database and let it handle the locking... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] Running App state in db?
Elrond. If you dont have any luck with the database way check out ACE Adaptive Communications Environment. That has wrapped all of the code for the likes of mutexes etc. So could save you a bunch of time if you need to go to option 1 especially across multiple OS's http://www.cs.wustl.edu/~schmidt/ACE-overview.html Elrond <[EMAIL PROTECTED]> wrote: On Wed, Mar 01, 2006 at 01:32:31PM -0600, Jim C. Nasby wrote: > BTW, if you're running everything on a single machine there's lots of > other ways you can do locking that don't involve the database. [...] Well, my locking data isn't as simple as "locked, not locked". The resource has ranges that can be locked, and it can be locked for reading (shared) and writing (exclusive). It's not really fun. That said, I want the whole fun to work on Unix and Windows. So my options are: 1) Write native api code for the job, using shared memory/mutexs, or whatnot for the relevant OS. 2) Find a suitable storage for the structured data. I'm currently trying (2). Elrond
Re: [sqlite] Running App state in db?
On Wed, Mar 01, 2006 at 01:32:31PM -0600, Jim C. Nasby wrote: > BTW, if you're running everything on a single machine there's lots of > other ways you can do locking that don't involve the database. [...] Well, my locking data isn't as simple as "locked, not locked". The resource has ranges that can be locked, and it can be locked for reading (shared) and writing (exclusive). It's not really fun. That said, I want the whole fun to work on Unix and Windows. So my options are: 1) Write native api code for the job, using shared memory/mutexs, or whatnot for the relevant OS. 2) Find a suitable storage for the structured data. I'm currently trying (2). Elrond
Re: [sqlite] Running App state in db?
BTW, if you're running everything on a single machine there's lots of other ways you can do locking that don't involve the database. On Wed, Mar 01, 2006 at 11:20:01AM -0800, w b wrote: > Unfortunately I think that this would lock the whole database within SQLITE > as there is no row level locking, so probably not the best way to go > forward, unless all of the other applications are only performing reads ? > > > Some othe ideas that might help. > > Have a field in one of your tables (May be a process table as Jim > descriobed) that stores the last update time when your main app performed a > refresh of the data. Your other applications could then infer that if that > value is greater than some threshold that the data within is old and should > not be trusted. So your other applications could infer from that that your > app has crashed. In this case you might not need to clean the DB as the data > is effectively implied as being bad given that the last_refresh time is > outside of your accepted aging window. This assumes that you are > periodically refreshing the data in there which sounds like that is the case > > On recovery (restart ) of your application I think the only thing you > probably dont want to do is go thru the recreation of the tables as that > would invalidate any prepares that your other applications have done. So may > be delete the old data and refresh it (or simply overwrite it). In doing so > your other applications would then see a new time stamp within the accepted > threshold range and so could now trust that data again. > > Wayne > > > "Jim C. Nasby" <[EMAIL PROTECTED]> wrote: On Wed, Mar 01, 2006 at 07:38:58PM > +0100, Elrond wrote: > > > > Hi, > > > > I'm considering to put the state of a running app into an > > sqlite db. I want it in a db, so external tools can query > > it and know, what the app is doing currently. > > > > Any hints on how to clean up the db, when the app crashes? > > > > (I have external resources, that I need to "lock", so the > > idea is to put the locks in the db, so more than one > > instance of the app can run and they don't kill the > > external resource.) > > > > Any hints? > > Depending on your needs, you might be able to just lock a row for > updates and hold that lock. IE, open a seperate connection to the > database and do: > > BEGIN; > UPDATE process SET start_time = now() WHERE process_id = ?; > > And then 'sit' on that connection until you're done. When you're > finished, just issue a COMMIT. Note that some databases won't like you > leaving that transaction open a real long time, so it depends on what > you're doing if this will work. I also don't know if SQLite cares about > such things. > -- > Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] > Pervasive Software http://pervasive.comwork: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] Running App state in db?
Unfortunately I think that this would lock the whole database within SQLITE as there is no row level locking, so probably not the best way to go forward, unless all of the other applications are only performing reads ? Some othe ideas that might help. Have a field in one of your tables (May be a process table as Jim descriobed) that stores the last update time when your main app performed a refresh of the data. Your other applications could then infer that if that value is greater than some threshold that the data within is old and should not be trusted. So your other applications could infer from that that your app has crashed. In this case you might not need to clean the DB as the data is effectively implied as being bad given that the last_refresh time is outside of your accepted aging window. This assumes that you are periodically refreshing the data in there which sounds like that is the case On recovery (restart ) of your application I think the only thing you probably dont want to do is go thru the recreation of the tables as that would invalidate any prepares that your other applications have done. So may be delete the old data and refresh it (or simply overwrite it). In doing so your other applications would then see a new time stamp within the accepted threshold range and so could now trust that data again. Wayne "Jim C. Nasby" <[EMAIL PROTECTED]> wrote: On Wed, Mar 01, 2006 at 07:38:58PM +0100, Elrond wrote: > > Hi, > > I'm considering to put the state of a running app into an > sqlite db. I want it in a db, so external tools can query > it and know, what the app is doing currently. > > Any hints on how to clean up the db, when the app crashes? > > (I have external resources, that I need to "lock", so the > idea is to put the locks in the db, so more than one > instance of the app can run and they don't kill the > external resource.) > > Any hints? Depending on your needs, you might be able to just lock a row for updates and hold that lock. IE, open a seperate connection to the database and do: BEGIN; UPDATE process SET start_time = now() WHERE process_id = ?; And then 'sit' on that connection until you're done. When you're finished, just issue a COMMIT. Note that some databases won't like you leaving that transaction open a real long time, so it depends on what you're doing if this will work. I also don't know if SQLite cares about such things. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] Running App state in db?
On Wed, Mar 01, 2006 at 07:38:58PM +0100, Elrond wrote: > > Hi, > > I'm considering to put the state of a running app into an > sqlite db. I want it in a db, so external tools can query > it and know, what the app is doing currently. > > Any hints on how to clean up the db, when the app crashes? > > (I have external resources, that I need to "lock", so the > idea is to put the locks in the db, so more than one > instance of the app can run and they don't kill the > external resource.) > > Any hints? Depending on your needs, you might be able to just lock a row for updates and hold that lock. IE, open a seperate connection to the database and do: BEGIN; UPDATE process SET start_time = now() WHERE process_id = ?; And then 'sit' on that connection until you're done. When you're finished, just issue a COMMIT. Note that some databases won't like you leaving that transaction open a real long time, so it depends on what you're doing if this will work. I also don't know if SQLite cares about such things. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
[sqlite] Running App state in db?
Hi, I'm considering to put the state of a running app into an sqlite db. I want it in a db, so external tools can query it and know, what the app is doing currently. Any hints on how to clean up the db, when the app crashes? (I have external resources, that I need to "lock", so the idea is to put the locks in the db, so more than one instance of the app can run and they don't kill the external resource.) Any hints? Elrond
