Re: [sqlite] Atomically creating a database and bootstrapping its tables
Ron Stevens wrote: That solves the problem of two processes bootstrapping the database, but is it possible for the second process to open the database while the first is in the process of creating it (creating the header pages, the system tables, etc) and view the database as corrupt since it isn't fully created yet? I imagine there is at least a small window between when the file is first created and when the file lock is acquired on it, but I'm wondering how big that window is (or does it not exist?). Ron, There is no window of opportunity for corruption. Before it can read a process must acquire a read lock on the database file. This will only be granted if no other process holds a write lock. A second process can open the database connection while the first is initializing the database, but because the first process is doing the initialization inside an exclusive transaction it will hold the write lock, and therefore the second process will get a BUSY error if it tries to read (or write) the database. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Atomically creating a database and bootstrapping its tables
Why not create it under a random name, and then rename after creation? Noah Hart -Original Message- From: Ron Stevens [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 13, 2007 10:59 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Atomically creating a database and bootstrapping its tables > No, you have to do that in your application. > > In pseudo code your create function could do this: > > open the database file > begin an exclusive transaction (since you may need to write to > initialize the tables) > read the user_version > if the user version is not zero >create the tables (read SQL script into a string and pass the > string to sqlite3_exec) >set the user version to a non zero value > endif > commit the transaction > > Only one process will succeed in getting the write lock while opening > the transaction. That process will create the tables and set the > user_version so no other process will try to reinitialize the tables latter. That solves the problem of two processes bootstrapping the database, but is it possible for the second process to open the database while the first is in the process of creating it (creating the header pages, the system tables, etc) and view the database as corrupt since it isn't fully created yet? I imagine there is at least a small window between when the file is first created and when the file lock is acquired on it, but I'm wondering how big that window is (or does it not exist?). - To unsubscribe, send email to [EMAIL PROTECTED] - CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Atomically creating a database and bootstrapping its tables
No, you have to do that in your application. In pseudo code your create function could do this: open the database file begin an exclusive transaction (since you may need to write to initialize the tables) read the user_version if the user version is not zero create the tables (read SQL script into a string and pass the string to sqlite3_exec) set the user version to a non zero value endif commit the transaction Only one process will succeed in getting the write lock while opening the transaction. That process will create the tables and set the user_version so no other process will try to reinitialize the tables latter. That solves the problem of two processes bootstrapping the database, but is it possible for the second process to open the database while the first is in the process of creating it (creating the header pages, the system tables, etc) and view the database as corrupt since it isn't fully created yet? I imagine there is at least a small window between when the file is first created and when the file lock is acquired on it, but I'm wondering how big that window is (or does it not exist?). - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Atomically creating a database and bootstrapping its tables
Ron Stevens wrote: In general, does SQLite protect against the database being opened while it's being created so other processes don't open it assuming everything is in a good state? Ron, No, you have to do that in your application. In pseudo code your create function could do this: open the database file begin an exclusive transaction (since you may need to write to initialize the tables) read the user_version if the user version is not zero create the tables (read SQL script into a string and pass the string to sqlite3_exec) set the user version to a non zero value endif commit the transaction Only one process will succeed in getting the write lock while opening the transaction. That process will create the tables and set the user_version so no other process will try to reinitialize the tables latter. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Atomically creating a database and bootstrapping its tables
To bootstrap my db's I create a database template. Then make a physical copy of that. Locking and access is done via flock. So the first process to gain the lock wins and is respoonsible for making the copy, the other just waits until the lock is released and then connects. I make lots of databases and found that creating a template then copying is much faster than using the sqlite api to create the db, then to create the individual tables. Ron Stevens <[EMAIL PROTECTED]> wrote: I have two processes trying to access a database for the first time at roughly the same time. I'm wondering if it's possible to atomically create a database and bootstrap it with some tables from one process so that the other process doesn't open the database either before SQLite has finished writing the system tables or the first process has finished the application specific bootstrapping. In general, does SQLite protect against the database being opened while it's being created so other processes don't open it assuming everything is in a good state? - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Atomically creating a database and bootstrapping its tables
I have two processes trying to access a database for the first time at roughly the same time. I'm wondering if it's possible to atomically create a database and bootstrap it with some tables from one process so that the other process doesn't open the database either before SQLite has finished writing the system tables or the first process has finished the application specific bootstrapping. In general, does SQLite protect against the database being opened while it's being created so other processes don't open it assuming everything is in a good state? - To unsubscribe, send email to [EMAIL PROTECTED] -
