Testing sqlite db to see if exists & ready

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
27 messages Options
12
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Testing sqlite db to see if exists & ready

dave boland
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
  [hidden email]

--
http://www.fastmail.com - A fast, anti-spam email service.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Testing sqlite db to see if exists & ready

Clemens Ladisch
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Testing sqlite db to see if exists & ready

Tim Streater-3
In reply to this post by dave boland
On 06 Apr 2017 at 11:28, Clemens Ladisch <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Testing sqlite db to see if exists & ready

Richard Hipp-3
In reply to this post by dave boland
On Thu, 06 Apr 2017 12:11 +0100, Tim Streater <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Testing sqlite db to see if exists & ready

Marco Bambini
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 <[hidden email]> wrote:
>
> On Thu, 06 Apr 2017 12:11 +0100, Tim Streater <[hidden email]> 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
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Testing sqlite db to see if exists & ready

Simon Slavin-3
In reply to this post by dave boland

On 6 Apr 2017, at 12:11pm, Tim Streater <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Testing sqlite db to see if exists & ready

Tim Streater-3
In reply to this post by dave boland
On 06 Apr 2017 at 13:19, Simon Slavin <[hidden email]> wrote:

> On 6 Apr 2017, at 12:11pm, Tim Streater <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Testing sqlite db to see if exists & ready

Simon Slavin-3
In reply to this post by dave boland

On 6 Apr 2017, at 2:44pm, Tim Streater <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Testing sqlite db to see if exists & ready

Tim Streater-3
In reply to this post by dave boland
On 06 Apr 2017 at 15:33, Simon Slavin <[hidden email]> wrote:

> On 6 Apr 2017, at 2:44pm, Tim Streater <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Testing sqlite db to see if exists & ready

Simon Slavin-3
In reply to this post by dave boland

On 6 Apr 2017, at 4:04pm, Tim Streater <[hidden email]> wrote:

> On 06 Apr 2017 at 15:33, Simon Slavin <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Testing sqlite db to see if exists & ready

James K. Lowden
In reply to this post by Simon Slavin-3
On Thu, 6 Apr 2017 13:19:38 +0100
Simon Slavin <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Testing sqlite db to see if exists & ready

Tim Streater-3
In reply to this post by dave boland
On 06 Apr 2017 at 16:10, Simon Slavin <[hidden email]> wrote:

> On 6 Apr 2017, at 4:04pm, Tim Streater <[hidden email]> wrote:
>
>> On 06 Apr 2017 at 15:33, Simon Slavin <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Testing sqlite db to see if exists & ready

dave boland
In reply to this post by Simon Slavin-3
"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">[hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users">sqlite-users
--
  dave boland
  [hidden email]

--
http://www.fastmail.com - A no graphics, no pop-ups email service

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Testing sqlite db to see if exists & ready

Jens Alfke-2
In reply to this post by Simon Slavin-3

> On Apr 6, 2017, at 5:19 AM, Simon Slavin <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Testing sqlite db to see if exists & ready

Simon Slavin-3
In reply to this post by dave boland

On 6 Apr 2017, at 7:38pm, dave boland <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Testing sqlite db to see if exists & ready

dave boland
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 <[hidden email]> 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">[hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users">sqlite-users
--
  dave boland
  [hidden email]

--
http://www.fastmail.com - Accessible with your email software
                          or over the web

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Testing sqlite db to see if exists & ready

David Raymond
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:[hidden email]] 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 <[hidden email]> 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">[hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users">sqlite-users
--
  dave boland
  [hidden email]

--
http://www.fastmail.com - Accessible with your email software
                          or over the web

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Testing sqlite db to see if exists & ready

Tim Streater-3
In reply to this post by dave boland
On 06 Apr 2017 at 19:54, Jens Alfke <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Testing sqlite db to see if exists & ready

Simon Slavin-3
In reply to this post by David Raymond

On 6 Apr 2017, at 10:07pm, David Raymond <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Testing sqlite db to see if exists & ready

Random Coder
In reply to this post by David Raymond
On Thu, Apr 6, 2017 at 2:07 PM, David Raymond <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12
Loading...