Re: [sqlite] Can I simulate a COMMIT?
--- Scott Hess <[EMAIL PROTECTED]> wrote: > - for newly-created files, create them during the transaction. If > the transaction rolls back, delete the file. If the transaction > commits, leave them alone. > > - for deleted files, leave them in place. If the transaction > commits, delete the files. If the transaction rolls back, leave them > alone. There's a small window a failure between the successful commit and deleting the file. If you lose power or crash, the file will be processed again upon restarting the process. > Handling updates could be a challange, as could handling multiple > inserts and deletes of the same entity within a single transaction. > As you might guess, this can be an annoying thing to get right! > > If you have full control over the app, the best solution might be to > periodically run a "BEGIN EXCLUSIVE", then review the filesystem and > database state and delete any excess files, then run "ROLLBACK". This > still doesn't handle conflicts in case someone wants to put a file in > the same place as an existing deleted file. Got a little couch potato? Check out fun summer activities for kids. http://search.yahoo.com/search?fr=oni_on_mail=summer+activities+for+kids=bz - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Can I simulate a COMMIT?
--- Yves Goergen <[EMAIL PROTECTED]> wrote: > On 08.09.2007 21:06 CE(S)T, Joe Wilson wrote: > > Are you able to store this file in the database itself as a BLOB? > > No, its an input file that I'm processing and saving the results in the > database. When processing and database import are successful, the file > must be deleted so that it won't be imported again in a later run. Just rename the file with a ".pending" suffix before the transaction. If the transaction succeeds, delete the .pending file. If the transaction fails, rename the .pending file back to its original name. If you want to be thorough in the face of crashing, make a table of all processed file names. Update that table with each processed file name within the transaction. Upon program startup, consult the file name table to see if you have already processed that pending file, if so - delete the pending file, if not - repeat the transaction on the pending file. Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games. http://get.games.yahoo.com/proddesc?gamekey=monopolyherenow - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Can I simulate a COMMIT?
On 9/7/07, Yves Goergen <[EMAIL PROTECTED]> wrote: > I'm currently using a transaction for this on the database side and > rolling it back if the file cannot be deleted. But what if the file is > gone and then SQLite says it doesn't accept my records? Since we're > inside a transaction, integrity checks should be deferred until a > COMMIT. Is there a way to tell whether the COMMIT will succeed under the > current conditions so that I can safely delete the file? What integrity checks? You could also take the approach of maintaining a "delete journal" inside the database itself, but controlled by the application. E.g. write a record to a table that says you're deleting file foo as part of the transaction, and when the COMMIT succeeds, your application can delete the file. If something happens between the commit and delete, your application can consult the journal to find out what file was supposed to be deleted, and pick up where it left off. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Can I simulate a COMMIT?
In general, you have to do something like: - for newly-created files, create them during the transaction. If the transaction rolls back, delete the file. If the transaction commits, leave them alone. - for deleted files, leave them in place. If the transaction commits, delete the files. If the transaction rolls back, leave them alone. Handling updates could be a challange, as could handling multiple inserts and deletes of the same entity within a single transaction. As you might guess, this can be an annoying thing to get right! If you have full control over the app, the best solution might be to periodically run a "BEGIN EXCLUSIVE", then review the filesystem and database state and delete any excess files, then run "ROLLBACK". This still doesn't handle conflicts in case someone wants to put a file in the same place as an existing deleted file. -scott On 9/8/07, Yves Goergen <[EMAIL PROTECTED]> wrote: > On 08.09.2007 21:06 CE(S)T, Joe Wilson wrote: > > Are you able to store this file in the database itself as a BLOB? > > No, its an input file that I'm processing and saving the results in the > database. When processing and database import are successful, the file > must be deleted so that it won't be imported again in a later run. > > On 08.09.2007 22:16 CE(S)T, Dwight Ingersoll wrote: > > Assuming the data files you are working with are of a manageable size, > > either read the file into a variable in your code and then process it if the > > file delete succeeds, or import the data file into a work table in SQLite, > > and then attempt the file delete. > > And when deleting the file works fine, but processing does not? Then the > file is gone and I need to restore it or something. My application must > not break in this time and I need to be able to recreate that file... > Storing it in a db table sound like the data is safe at any time, but > it's still not the original format. > > Well, maybe deleting to the recycle bin would be an option I could live > with. Or if all else fails, giving the database thing a priority and > only inform the user if the file could not be deleted. After all, it's > not usual that the file can't be deleted or that a database error would > occur in my particular case, but it's an interesting question in > general, I believe. > > -- > Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> > Visit my web laboratory at http://beta.unclassified.de > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Can I simulate a COMMIT?
On 08.09.2007 21:06 CE(S)T, Joe Wilson wrote: > Are you able to store this file in the database itself as a BLOB? No, its an input file that I'm processing and saving the results in the database. When processing and database import are successful, the file must be deleted so that it won't be imported again in a later run. On 08.09.2007 22:16 CE(S)T, Dwight Ingersoll wrote: > Assuming the data files you are working with are of a manageable size, > either read the file into a variable in your code and then process it if the > file delete succeeds, or import the data file into a work table in SQLite, > and then attempt the file delete. And when deleting the file works fine, but processing does not? Then the file is gone and I need to restore it or something. My application must not break in this time and I need to be able to recreate that file... Storing it in a db table sound like the data is safe at any time, but it's still not the original format. Well, maybe deleting to the recycle bin would be an option I could live with. Or if all else fails, giving the database thing a priority and only inform the user if the file could not be deleted. After all, it's not usual that the file can't be deleted or that a database error would occur in my particular case, but it's an interesting question in general, I believe. -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Can I simulate a COMMIT?
On 9/7/07, Yves Goergen <[EMAIL PROTECTED]> wrote: > > Hi, > > in a scenario when multiple operations need to be transactionally > synchronised, I have a file that must be deleted when the database > records are added successfully, but the database operations must be > rolled back, if the file cannot be deleted. Assuming the data files you are working with are of a manageable size, either read the file into a variable in your code and then process it if the file delete succeeds, or import the data file into a work table in SQLite, and then attempt the file delete.
Re: [sqlite] Can I simulate a COMMIT?
--- Yves Goergen <[EMAIL PROTECTED]> wrote: > in a scenario when multiple operations need to be transactionally > synchronised, I have a file that must be deleted when the database > records are added successfully, but the database operations must be > rolled back, if the file cannot be deleted. > > I'm currently using a transaction for this on the database side and > rolling it back if the file cannot be deleted. But what if the file is > gone and then SQLite says it doesn't accept my records? Since we're > inside a transaction, integrity checks should be deferred until a > COMMIT. Is there a way to tell whether the COMMIT will succeed under the > current conditions so that I can safely delete the file? > > Would that work with nested transactions or are integrity checks also > deferred to the most outer transaction? I never used nested transactions > so I have no experience with it. There's no nested transaction support at this time, although it's on the to do list: http://www.sqlite.org/cvstrac/wiki?p=ToDo But you will always have trouble with external synchronization with resources that do not support 2PC anyway. http://en.wikipedia.org/wiki/Two-phase_commit Are you able to store this file in the database itself as a BLOB? You might be able to use Libsqlfs, a POSIX style file system on top of an SQLite database. (I've never tried to use it). http://www.nongnu.org/libsqlfs/ Assuming this library works, your users can always be guaranteed the files are valid, and are erased in concert with your database transactions. Park yourself in front of a world of choices in alternative vehicles. Visit the Yahoo! Auto Green Center. http://autos.yahoo.com/green_center/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Can I simulate a COMMIT?
On 9/7/07, Yves Goergen <[EMAIL PROTECTED]> wrote: > Hi, > > in a scenario when multiple operations need to be transactionally > synchronised, I have a file that must be deleted when the database > records are added successfully, but the database operations must be > rolled back, if the file cannot be deleted. > > I'm currently using a transaction for this on the database side and > rolling it back if the file cannot be deleted. But what if the file is > gone and then SQLite says it doesn't accept my records? Since we're > inside a transaction, integrity checks should be deferred until a > COMMIT. Is there a way to tell whether the COMMIT will succeed under the > current conditions so that I can safely delete the file? My understanding is that if your first insert succeeds you hold a write lock on the table and barring any exceptional errors a commit should always succeed. -- Cory Nelson - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Can I simulate a COMMIT?
Hi, in a scenario when multiple operations need to be transactionally synchronised, I have a file that must be deleted when the database records are added successfully, but the database operations must be rolled back, if the file cannot be deleted. I'm currently using a transaction for this on the database side and rolling it back if the file cannot be deleted. But what if the file is gone and then SQLite says it doesn't accept my records? Since we're inside a transaction, integrity checks should be deferred until a COMMIT. Is there a way to tell whether the COMMIT will succeed under the current conditions so that I can safely delete the file? Would that work with nested transactions or are integrity checks also deferred to the most outer transaction? I never used nested transactions so I have no experience with it. -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
