The best way to check if a file is a sqlite3 DB file?

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

The best way to check if a file is a sqlite3 DB file?

Peng Yu
Hi,

I use `file` to check if a file is a sqlite3 DB file. But I have to
parse the result. Is there a better way to just return an exit status
of 0 for a sqlite3 DB file and 1 otherwise? Thanks.

$ file /tmp/tmp.erZ5aS6PUX.sqa
/tmp/tmp.erZ5aS6PUX.sqa: SQLite 3.x database, last written using
SQLite version 3022000

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

Re: The best way to check if a file is a sqlite3 DB file?

Clemens Ladisch
Peng Yu wrote:
> Is there a better way to just return an exit status of 0 for
> a sqlite3 DB file and 1 otherwise?

Extract the magic header string from a known DB file:

  dd bs=16 count=1 < some.db > sqlite3-signature

Then you can compare it against the beginning of the file:

  cmp --bytes=16 sqlite3-signature /tmp/tmp.erZ5aS6PUX.sqa > /dev/null
  [ $? = 0 ] && echo SQLite DB


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
|

Re: The best way to check if a file is a sqlite3 DB file?

Dominique Devienne
On Wed, Sep 18, 2019 at 6:43 PM Clemens Ladisch <[hidden email]> wrote:

> Peng Yu wrote:
> > Is there a better way to just return an exit status of 0 for
> > a sqlite3 DB file and 1 otherwise?
>
> Extract the magic header string from a known DB file:
>
>   dd bs=16 count=1 < some.db > sqlite3-signature
>
> Then you can compare it against the beginning of the file:
>
>   cmp --bytes=16 sqlite3-signature /tmp/tmp.erZ5aS6PUX.sqa > /dev/null
>   [ $? = 0 ] && echo SQLite DB


I'm actually surprised sqlite3[.exe] itself doesn't have a more to do that.
I tried using it to open a non-DB file, and it opens in interactive mode,
with
no error or warning, wether I use -bail or not. I was expecting a hard
error.

You'd think sqlite3[.exe] is the best suited to figure out if a file is a
valie SQLite database or not,
and exit with a non-zero status if it's not (possibly using a given CLI arg
for that behavior). That
would be a useful addition, if it's not already possible in a way I can't
find. Parsing the 100 bytes
header would do and be super fast. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: The best way to check if a file is a sqlite3 DB file?

Rowan Worth-2
On Thu, 19 Sep 2019 at 16:03, Dominique Devienne <[hidden email]>
wrote:

> On Wed, Sep 18, 2019 at 6:43 PM Clemens Ladisch <[hidden email]>
> wrote:
>
> > Peng Yu wrote:
> > > Is there a better way to just return an exit status of 0 for
> > > a sqlite3 DB file and 1 otherwise?
> >
> > Extract the magic header string from a known DB file:
> >
> >   dd bs=16 count=1 < some.db > sqlite3-signature
> >
> > Then you can compare it against the beginning of the file:
> >
> >   cmp --bytes=16 sqlite3-signature /tmp/tmp.erZ5aS6PUX.sqa > /dev/null
> >   [ $? = 0 ] && echo SQLite DB
>
>
> I'm actually surprised sqlite3[.exe] itself doesn't have a more to do that.
> I tried using it to open a non-DB file, and it opens in interactive mode,
> with
> no error or warning, wether I use -bail or not. I was expecting a hard
> error.
>

As usual, sqlite doesn't touch the DB file until it is asked to. Try
"sqlite3 FILENAME 'pragma schema_version'" on some random file and you'll
get "Error: file is encrypted or is not a database". But note that trying
the same on a non-existent file will succeed, and additionally create an
empty file.

Technically from sqlite's perspective a non-existent or empty file is a
perfectly well-formed database, just one which happens to contain no data.


> You'd think sqlite3[.exe] is the best suited to figure out if a file is a
> valie SQLite database or not,
>

It still is: sqlite3 FILENAME 'pragma integrity_check'

Parsing the header doesn't tell you whether the DB is valid, but if that's
all you want to do I suggest the ubiquitous file(1) command which reports
"SQLite 3.x database" for a [non-empty] sqlite db file.

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

Re: The best way to check if a file is a sqlite3 DB file?

Dominique Devienne
On Thu, Sep 19, 2019 at 10:20 AM Rowan Worth <[hidden email]> wrote:

> On Thu, 19 Sep 2019 at 16:03, Dominique Devienne <[hidden email]>
> > On Wed, Sep 18, 2019 at 6:43 PM Clemens Ladisch <[hidden email]>
> > > Peng Yu wrote:
> > > > Is there a better way to just return an exit status of 0 for
> > > > a sqlite3 DB file and 1 otherwise?
>
> > >   dd bs=16 count=1 < some.db > sqlite3-signature
> > >   cmp --bytes=16 sqlite3-signature /tmp/tmp.erZ5aS6PUX.sqa > /dev/null
> > >   [ $? = 0 ] && echo SQLite DB
> >
> >
> > I'm actually surprised sqlite3[.exe] itself doesn't have a more to do
> that.
>
> As usual, sqlite doesn't touch the DB file until it is asked to. Try
> "sqlite3 FILENAME 'pragma schema_version'" on some random file and you'll
> get "Error: file is encrypted or is not a database". But note that trying
> the same on a non-existent file will succeed, and additionally create an
> empty file.
>

Thanks. Good tip. Need double not single quotes on Windows. And error code
of 26 if a bit weird,
but this works well otherwise:

D:\>sqlite3 TypedEntity.h 'pragma schema_version'
Error: unrecognized token: "'pragma"

D:\>sqlite3 TypedEntity.h "pragma schema_version"
Error: file is not a database

D:\>echo %ERRORLEVEL%
26

D:\>sqlite3 SOME.db "pragma schema_version"
58

D:\>echo %ERRORLEVEL%
0


> > You'd think sqlite3[.exe] is the best suited to figure out if a file is a
> > valie SQLite database or not,
>
> It still is: sqlite3 FILENAME 'pragma integrity_check'
>
> Parsing the header doesn't tell you whether the DB is valid, but if that's
> all you want to do I suggest the ubiquitous file(1) command which reports
> "SQLite 3.x database" for a [non-empty] sqlite db file.
>

I'm well aware of that. We were discussing an alternative to the *nix file
command,
and integrity_check goes way beyond (and is way slower) than checkinga file
type.
You might as well through FK checks with foreign_key_check if you are going
there :).

pragma quick_check would be more appropriate, if one wants to go the extra
mile, w/o slowing things down too much.

But again, thanks for this tip. Good one. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users