Re: [sqlite] Testing sqlite db to see if exists & ready
Simon Slavin wrote:
> On 7 Apr 2017, at 6:58am, Clemens Ladisch wrote:
>> David Raymond:
>>> https://docs.python.org/2/reference/datamodel.html#with-statement-context-managers
>>>
>>> For the sqlite3 module connection object: on a bad exit from an
>>> exception it will do a rollback, on a clean exit it will do a commit,
>>> and run .close() either way.
>>
>> It does not run .close().
>
> Doesn’t seem to run .close() on __exit_ or __del_. But in another
> thread DRH has posted that failing to close a connection won’t do any
> serious harm. Still troubles me as a programmer, though, to see an
> 'open' without a 'close'.
There is no separate transaction object, so the only way to handle
transactions with a context manager is the connection object itself.
Apparently, handling transactions was thought to be more important
than cleaning up the connection itself.
Anyway, automatically closing the connection is still possible:
with contextlib.closing(sqlite3.connect('...')):
...
Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
On 7 Apr 2017, at 6:58am, Clemens Ladisch wrote: > David Raymond: >> https://docs.python.org/2/reference/datamodel.html#with-statement-context-managers >> >> For the sqlite3 module connection object: on a bad exit from an >> exception it will do a rollback, on a clean exit it will do a commit, >> and run .close() either way. > > It does not run .close(). Doesn’t seem to run .close() on __exit_ or __del_. But in another thread DRH has posted that failing to close a connection won’t do any serious harm. Still troubles me as a programmer, though, to see an 'open' without a 'close'. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
David Raymond: >https://docs.python.org/2/reference/datamodel.html#with-statement-context-managers > >For the sqlite3 module connection object: on a bad exit from an >exception it will do a rollback, on a clean exit it will do a commit, >and run .close() either way. It does not run .close(). Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
Yeah, with the Look Before You Leap style there's that chance, but since the default behavior is to create the file if it's not there, then connecting to some file name is never gonna fail (unless the absolute path directory doesn't exist) With the CLI for example I know I've created tons of blank files by hitting the wrong character, or having shift held down too long when I'm attaching things. attach database 'January.sqlite' as jan; attach database 'FEbruary.sqlite' as feb; Oops, just created new file with a capital E, and won't notice it until I run a query and see "Error: no such table: feb.theTableYouWereLookingFor" -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Random Coder Sent: Thursday, April 06, 2017 6:07 PM To: SQLite mailing list Subject: Re: [sqlite] Testing sqlite db to see if exists & ready On Thu, Apr 6, 2017 at 2:07 PM, David Raymond wrote: > Before opening the connection you could do something along the lines of > > if not os.path.isfile(fi) or not os.access(fi, os.W_OK): > print "File isn't there or isn't writable" > return 1 > with open(fi, "r") as f: > if f.read(16) != "SQLite format 3\x00": > print "Magic header isn't correct" > return 1 If you do something like this, be prepared for another process to have created the database for you after your check suggested it doesn't exist. Might not matter in your exact use case, but if there's a chance for two instances of your script to be running at once, you'll need to handle this race condition. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
On 6 Apr 2017, at 11:55pm, David Raymond wrote: > For the sqlite3 module connection object: on a bad exit from an exception it > will do a rollback, on a clean exit it will do a commit, and run .close() > either way. Thanks for the answer about open() scope. Your addition is interesting. Someone who learned to use SQLite (or even normal file handling) from Python might not realise how useful their language was being for them. If they transferred to a different language they might not realise they had to close their files. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
Yes. "with" runs the "__exit__()" method of whatever you're creating there, passing in the exception info if it's ending because of an exception. For standard files the __exit__ behavior is to close down the file either way. https://docs.python.org/2/reference/datamodel.html#with-statement-context-managers For the sqlite3 module connection object: on a bad exit from an exception it will do a rollback, on a clean exit it will do a commit, and run .close() either way. with sqlite3.connect(dbFile) as conn: blah blah #no need to call .close here https://docs.python.org/2/library/sqlite3.html#using-the-connection-as-a-context-manager -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Thursday, April 06, 2017 5:27 PM To: SQLite mailing list Subject: Re: [sqlite] Testing sqlite db to see if exists & ready On 6 Apr 2017, at 10:07pm, David Raymond wrote: > with open(fi, "r") as f: In Python, once you fall outside the scope of "with open()" does it automatically close the file for you ? If so, that’s pretty neat. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
On April 6, 2017 11:26:11 AM EDT, "James K. Lowden" wrote: >On Thu, 6 Apr 2017 13:19:38 +0100 >Simon Slavin wrote: > >> Instead use PHP functions to check that the file exists using PHP >> function "file_exists()" and then using fread() to read the first 16 >> bytes from it. Those 16 bytes should be "SQLite format 3" followed >> by a 0x00 byte for a string terminator. > >Wouldn't it better to use a URI filename for sqlite3_open_v2 with >mode=rw? > >Suggestion to developers: it might be nice to have a version of >sqlite3_open that takes a file descriptor as a parameter instead of >a filename. The caller could then set the open mode (and, for a new >file, permissions) by conventional means, and then open the database. > >--jkl > >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users URI filenames are disabled in the default configuration of SQLite (which a typical build of PHP would use). -- J. King ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
On Thu, Apr 6, 2017 at 2:07 PM, David Raymond wrote: > Before opening the connection you could do something along the lines of > > if not os.path.isfile(fi) or not os.access(fi, os.W_OK): > print "File isn't there or isn't writable" > return 1 > with open(fi, "r") as f: > if f.read(16) != "SQLite format 3\x00": > print "Magic header isn't correct" > return 1 If you do something like this, be prepared for another process to have created the database for you after your check suggested it doesn't exist. Might not matter in your exact use case, but if there's a chance for two instances of your script to be running at once, you'll need to handle this race condition. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
On 6 Apr 2017, at 10:07pm, David Raymond wrote: > with open(fi, "r") as f: In Python, once you fall outside the scope of "with open()" does it automatically close the file for you ? If so, that’s pretty neat. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
On 06 Apr 2017 at 19:54, Jens Alfke wrote: > PS: Tim, for some reason your mail client (iLetter) is sending replies without > an In-Reply-To header, which breaks up the threading (at least in my mail > client) making it very hard to follow. There’s probably not a way for you to > change that, but maybe you could send them a bug report? I am actually the author of iLetter (the databases I was referring to upthread were users' mailboxes). I've never actually (ever) paid attention to the In-Reply-To: header but I can look at adding that once I find out what it is. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
Before opening the connection you could do something along the lines of if not os.path.isfile(fi) or not os.access(fi, os.W_OK): print "File isn't there or isn't writable" return 1 with open(fi, "r") as f: if f.read(16) != "SQLite format 3\x00": print "Magic header isn't correct" return 1 -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of dave boland Sent: Thursday, April 06, 2017 3:58 PM To: Simon Slavin; SQLite mailing list Subject: Re: [sqlite] Testing sqlite db to see if exists & ready I assume this will work in a similar fashion for Python? On Thu, Apr 6, 2017, at 03:24 PM, Simon Slavin wrote: > > On 6 Apr 2017, at 7:38pm, dave boland wrote: > > > "unconfigured means no tables, no fields, no nothing. With SQLite, it > > is possible to have an empty file, a database with a table but no > > fields, etc. The reason this concerns me is that I want to know what I > > have before connecting to a file and creating a new database when I did > > not intend to do that. So, what (and why) are the steps to test the > > database file to see what state it is in? > > Okay. If that’s the definition of 'unconfigured' you want, do what I > recommended in a previous post: > > First, use the PHP function "file_exists()" to check that the file > exists. > > If the file does exist use PHP to check it’s an actual database: > > fopen(path, 'rb') > fread(, 16) > fclose(). > > Then check those 16 bytes. They should be 'SQLite format 3\0'. The last > character is a 0x00 byte for a string terminator. If there are less then > 16 bytes, or if they don’t match that string then it’s not a "configured" > (by your definition) SQLite database. > > Simon. > ___ > sqlite-users">sqlite-users mailing list > sqlite-users">sqlite-users@mailinglists">sqlite-users">sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users";>sqlite-users -- dave boland dbola...@fastmail.fm -- http://www.fastmail.com - Accessible with your email software or over the web ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
I assume this will work in a similar fashion for Python? On Thu, Apr 6, 2017, at 03:24 PM, Simon Slavin wrote: > > On 6 Apr 2017, at 7:38pm, dave boland wrote: > > > "unconfigured means no tables, no fields, no nothing. With SQLite, it > > is possible to have an empty file, a database with a table but no > > fields, etc. The reason this concerns me is that I want to know what I > > have before connecting to a file and creating a new database when I did > > not intend to do that. So, what (and why) are the steps to test the > > database file to see what state it is in? > > Okay. If that’s the definition of 'unconfigured' you want, do what I > recommended in a previous post: > > First, use the PHP function "file_exists()" to check that the file > exists. > > If the file does exist use PHP to check it’s an actual database: > > fopen(path, 'rb') > fread(, 16) > fclose(). > > Then check those 16 bytes. They should be 'SQLite format 3\0'. The last > character is a 0x00 byte for a string terminator. If there are less then > 16 bytes, or if they don’t match that string then it’s not a "configured" > (by your definition) SQLite database. > > Simon. > ___ > sqlite-users">sqlite-users mailing list > sqlite-users">sqlite-users@mailinglists">sqlite-users">sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users";>sqlite-users -- dave boland dbola...@fastmail.fm -- http://www.fastmail.com - Accessible with your email software or over the web ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
On 6 Apr 2017, at 7:38pm, dave boland wrote: > "unconfigured means no tables, no fields, no nothing. With SQLite, it > is possible to have an empty file, a database with a table but no > fields, etc. The reason this concerns me is that I want to know what I > have before connecting to a file and creating a new database when I did > not intend to do that. So, what (and why) are the steps to test the > database file to see what state it is in? Okay. If that’s the definition of 'unconfigured' you want, do what I recommended in a previous post: First, use the PHP function "file_exists()" to check that the file exists. If the file does exist use PHP to check it’s an actual database: fopen(path, 'rb') fread(, 16) fclose(). Then check those 16 bytes. They should be 'SQLite format 3\0'. The last character is a 0x00 byte for a string terminator. If there are less then 16 bytes, or if they don’t match that string then it’s not a "configured" (by your definition) SQLite database. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
> On Apr 6, 2017, at 5:19 AM, Simon Slavin wrote: > > Don’t do this. Because if the file isn’t there, or if the file is there but > has zero length, SQLite will turn it into a SQLite file and then return > results which don’t let you tell whether the file was already there or just > created. And you probably don't want this. You could open the file read-only... —Jens PS: Tim, for some reason your mail client (iLetter) is sending replies without an In-Reply-To header, which breaks up the threading (at least in my mail client) making it very hard to follow. There’s probably not a way for you to change that, but maybe you could send them a bug report? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
"unconfigured means no tables, no fields, no nothing. With SQLite, it is possible to have an empty file, a database with a table but no fields, etc. The reason this concerns me is that I want to know what I have before connecting to a file and creating a new database when I did not intend to do that. So, what (and why) are the steps to test the database file to see what state it is in? Thanks, Dave > > I’m not sure what you mean by "unconfigured" so I’ll let other people > write about that, or you can post to clarify. > > Simon. > ___ > sqlite-users">sqlite-users mailing list > sqlite-users">sqlite-users@mailinglists">sqlite-users">sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users";>sqlite-users -- dave boland dbola...@fastmail.fm -- http://www.fastmail.com - A no graphics, no pop-ups email service ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
On 06 Apr 2017 at 16:10, Simon Slavin wrote: > On 6 Apr 2017, at 4:04pm, Tim Streater wrote: > >> On 06 Apr 2017 at 15:33, Simon Slavin wrote: >> >>> After touching, try opening the file and issuing a CREATE TABLE command. >>> See whether it works or gives an error. >> >> The command works and the file goes from 0 to 8k bytes. > > Right. So you can’t tell if a file is a SQLite database or not just by > opening it using the SQLite API. It might be a blank file that another > program is intending to use for something else. > > If you’re trying to find out whether a file with a certain name exists and > is a SQLite database, don’t open the file using the SQLite API. I can tell if it's an sqlite database with the characteristics I am looking for, and the tests I do don't interfere with the file. That's all I care about. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
On Thu, 6 Apr 2017 13:19:38 +0100 Simon Slavin wrote: > Instead use PHP functions to check that the file exists using PHP > function "file_exists()" and then using fread() to read the first 16 > bytes from it. Those 16 bytes should be "SQLite format 3" followed > by a 0x00 byte for a string terminator. Wouldn't it better to use a URI filename for sqlite3_open_v2 with mode=rw? Suggestion to developers: it might be nice to have a version of sqlite3_open that takes a file descriptor as a parameter instead of a filename. The caller could then set the open mode (and, for a new file, permissions) by conventional means, and then open the database. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
On 6 Apr 2017, at 4:04pm, Tim Streater wrote: > On 06 Apr 2017 at 15:33, Simon Slavin wrote: > >> After touching, try opening the file and issuing a CREATE TABLE command. See >> whether it works or gives an error. > > The command works and the file goes from 0 to 8k bytes. Right. So you can’t tell if a file is a SQLite database or not just by opening it using the SQLite API. It might be a blank file that another program is intending to use for something else. If you’re trying to find out whether a file with a certain name exists and is a SQLite database, don’t open the file using the SQLite API. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
On 06 Apr 2017 at 15:33, Simon Slavin wrote: > On 6 Apr 2017, at 2:44pm, Tim Streater wrote: > >> That would appear not to be the case. Under OS X 10.9.5, I touched a >> non-existent file and then using sqlite3.app did: >> >> .schema<--- gave nothing >> select version from globals; <--- gave "Error: no such table" >> >> My file stayed at zero bytes long. > > After touching, try opening the file and issuing a CREATE TABLE command. See > whether it works or gives an error. The command works and the file goes from 0 to 8k bytes. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
On 6 Apr 2017, at 2:44pm, Tim Streater wrote: > That would appear not to be the case. Under OS X 10.9.5, I touched a > non-existent file and then using sqlite3.app did: > > .schema<--- gave nothing > select version from globals; <--- gave "Error: no such table" > > My file stayed at zero bytes long. After touching, try opening the file and issuing a CREATE TABLE command. See whether it works or gives an error. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
On 06 Apr 2017 at 13:19, Simon Slavin wrote: > On 6 Apr 2017, at 12:11pm, Tim Streater wrote: > >> When my app starts, I check that the file in question actually *is* a >> database by doing some simple steps like open, selects from important tables, >> and a read/write to a globals table in the database that contains, for >> instance, the version number. > > Don’t do this. Because if the file isn’t there, or if the file is there > but has zero length, SQLite will turn it into a SQLite file and then return > results which don’t let you tell whether the file was already there or just > created. And you probably don't want this. That would appear not to be the case. Under OS X 10.9.5, I touched a non-existent file and then using sqlite3.app did: .schema<--- gave nothing select version from globals; <--- gave "Error: no such table" My file stayed at zero bytes long. I then added a bit of text to the file and repeating the exercise: .schema<--- gave nothing select version from globals; <--- gave "Error: file is encrypted or is not a database" The file was not modified by these actions. > Instead use PHP functions to check that the file exists using PHP function > "file_exists()" and then using fread() to read the first 16 bytes from it. > Those 16 bytes should be "SQLite format 3" followed by a 0x00 byte for a > string terminator. Actually I am scanning the directory and examining all the files there. The sequence of tests is: open, read from the two expected tables, write back to one of them. This confirms that the file is a database, it's one I'm looking for, it has the expected tables, and is writeable too. I keep a database of such files which is then re-populated with successful entries. This sequence was subject to careful testing when I first wrote it. Each step is under try/catch and I only proceed to the next step if previous ones succeeded. > I’m not sure what you mean by "unconfigured" so I’ll let other people > write about that, or you can post to clarify. I'm not sure either, better ask the OP. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
On 6 Apr 2017, at 12:11pm, Tim Streater wrote: > When my app starts, I check that the file in question actually *is* a > database by doing some simple steps like open, selects from important tables, > and a read/write to a globals table in the database that contains, for > instance, the version number. Don’t do this. Because if the file isn’t there, or if the file is there but has zero length, SQLite will turn it into a SQLite file and then return results which don’t let you tell whether the file was already there or just created. And you probably don't want this. Instead use PHP functions to check that the file exists using PHP function "file_exists()" and then using fread() to read the first 16 bytes from it. Those 16 bytes should be "SQLite format 3" followed by a 0x00 byte for a string terminator. I’m not sure what you mean by "unconfigured" so I’ll let other people write about that, or you can post to clarify. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
Seems like a good reason to introduce a way to query the existence of a
particular pragma command, something like:
PRAGMA exists('user_version');
or
PRAGMA exists='user_version';
--
Marco Bambini
http://www.sqlabs.com
http://twitter.com/sqlabs
http://instagram.com/sqlabs
> On 6 Apr 2017, at 13:42, Richard Hipp wrote:
>
> On Thu, 06 Apr 2017 12:11 +0100, Tim Streater wrote:
>>
>> I keep reading that the continued
>> existence of any particular PRAGMA is completely un-guaranteed.
>>
>
> We say that. But in practice, if we were to remove a pragma it would
> break thousands, perhaps millions, of applications, so they are all
> there for the long haul. Especially "PRAGMA user_version" you can
> count on being there.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
On Thu, 06 Apr 2017 12:11 +0100, Tim Streater wrote: > > I keep reading that the continued > existence of any particular PRAGMA is completely un-guaranteed. > We say that. But in practice, if we were to remove a pragma it would break thousands, perhaps millions, of applications, so they are all there for the long haul. Especially "PRAGMA user_version" you can count on being there. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
On 06 Apr 2017 at 11:28, Clemens Ladisch wrote: > dave boland wrote: >> Being a little paranoid, I like to insure that the db file exists > > SQLite automatically creates an empty DB if you try to open > a nonexistent file, so you do not actually need to do anything. > >> and what state it is in (unconfigured, so needs to be made >> ready; or ready to accept data (or be read)). How do I do that? > > Store a DB version number somewhere. (You can do it like Android and use > PRAGMA user_version, or use an entry in some table.) If the version > number is not high enough, you have to create or update the database. > Do everything in a transaction to prevent a partially-created/updated > database: When my app starts, I check that the file in question actually *is* a database by doing some simple steps like open, selects from important tables, and a read/write to a globals table in the database that contains, for instance, the version number. I'm using PHP for this and it is a good use for try/catch at each step. If the step succeeds, I pass to the next. Since the database (one of many, in fact) belongs to the user, I can't hide it away somewhere. It also allows the user to add possibly older versions of the database file there, or completely other files such as READMEs. Files such as the latter will fail at an early step (usually with: file is not an SQLITE database) but I don't care why; they either pass or fail, and if they fail, the app then ignores them. Older versions of the db can be spotted by the version number in the globals table (as Clemens say above); they can then be silently upgraded to the current version. This will happen if I have, for instance, added a column or two to support some new feature. I don't user PRAGMA user_version because I keep reading that the continued existence of any particular PRAGMA is completely un-guaranteed. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
dave boland wrote:
> Being a little paranoid, I like to insure that the db file exists
SQLite automatically creates an empty DB if you try to open
a nonexistent file, so you do not actually need to do anything.
> and what state it is in (unconfigured, so needs to be made
> ready; or ready to accept data (or be read)). How do I do that?
Store a DB version number somewhere. (You can do it like Android and use
PRAGMA user_version, or use an entry in some table.) If the version
number is not high enough, you have to create or update the database.
Do everything in a transaction to prevent a partially-created/updated
database:
conn.isolation_level = None # Python sucks
conn.execute('begin')
with conn:# automatically commits or rolls back the
transaction
version = conn.execute('PRAGMA user_version').fetchone()[0]
if version < 1:
conn.execute('CREATE TABLE foo(bar)')
# ...
conn.execute('PRAGMA user_version = 1')
# optional: updates
if version < 2:
conn.execute('ALTER TABLE foo ADD COLUMN baz')
# ...
conn.execute('PRAGMA user_version = 2')
Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Testing sqlite db to see if exists & ready
Being a little paranoid, I like to insure that the db file exists, which is easy, and what state it is in (unconfigured, so needs to be made ready; or ready to accept data (or be read)). How do I do that? Using Python, but would like a generalized approach. Feel free to point me to documentation that I may have missed. Thanks, Dave -- dave boland dbola...@fastmail.fm -- http://www.fastmail.com - A fast, anti-spam email service. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
