Re: [sqlite] Testing sqlite db to see if exists & ready

[Clemens Ladisch]

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

[Simon Slavin]

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

[Clemens Ladisch]

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

[David Raymond]

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 <david.raym...@tomtom.com> 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

[Simon Slavin]

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

[Simon Slavin]

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

[Tim Streater]

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

[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: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 <dbola...@fastmail.fm> 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

[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  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

[Simon Slavin]

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

[Jens Alfke]

> 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

[dave boland]

"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

[Tim Streater]

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

[James K. Lowden]

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

[Simon Slavin]

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

[Tim Streater]

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

[Simon Slavin]

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

[Tim Streater]

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

[Simon Slavin]

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

[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  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

[Richard Hipp]

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

[Tim Streater]

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

[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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Testing SQLite in C++

[Arbol One]

Excellent!

Thanks dude!

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
Sent: Thursday, October 20, 2011 11:06 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Testing SQLite in C++

Do not include sqlite3ext.h. Applications need only sqlite3.h.

Pavel


On Thu, Oct 20, 2011 at 11:02 AM, Arbol One <arbol...@gmail.com> wrote:
> Hey kids, I am testing SQLite in the hope that I can use it in my program,
> but I get this confusing error msg, Can anybody help?
>
>
>
> Error message
>
> 
>
> D:\dev\sqlitetest\main.cpp:14:10: error: 'sqlite3_api' was not declared in
> this scope
>
>
>
> Code
>
> ~
>
> #include 
>
> #include "sqlite/sqlite3.h"
>
> #include "sqlite/sqlite3ext.h"
>
> #include 
>
> int main() {
>
>    sqlite3 *db;
>
>    Glib::ustring dbName("sqliteTest");
>
>    int rc;
>
>
>
>    rc = sqlite3_open(dbName.c_str(), ); // ç Error Message comes as
soon
> as I add this line
>
>
>
>    std::cout << "Hello world!" << std::endl;
>
>    return 0;
>
> }
>
>
>
> Thanks in advance!!
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Testing SQLite in C++

[Pavel Ivanov]

Do not include sqlite3ext.h. Applications need only sqlite3.h.

Pavel


On Thu, Oct 20, 2011 at 11:02 AM, Arbol One  wrote:
> Hey kids, I am testing SQLite in the hope that I can use it in my program,
> but I get this confusing error msg, Can anybody help?
>
>
>
> Error message
>
> 
>
> D:\dev\sqlitetest\main.cpp:14:10: error: 'sqlite3_api' was not declared in
> this scope
>
>
>
> Code
>
> ~
>
> #include 
>
> #include "sqlite/sqlite3.h"
>
> #include "sqlite/sqlite3ext.h"
>
> #include 
>
> int main() {
>
>    sqlite3 *db;
>
>    Glib::ustring dbName("sqliteTest");
>
>    int rc;
>
>
>
>    rc = sqlite3_open(dbName.c_str(), ); // ç Error Message comes as soon
> as I add this line
>
>
>
>    std::cout << "Hello world!" << std::endl;
>
>    return 0;
>
> }
>
>
>
> Thanks in advance!!
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Testing SQLite on embedded platform with no Tcl

[D. Richard Hipp]

On Jan 12, 2009, at 10:53 AM, John Efstathiades wrote:

> Hello,
>
> I am looking into porting the latest SQLite to an embedded platform  
> running
> a commercial real-time operating system. I'd like to use as much of  
> the
> existing regression test code as possible to ensure the port is  
> correct but
> unfortunately the target environment does not have Tcl.
>
> The host environment is Windows and we will have a host-target link  
> (via
> serial or telnet) to a CLI (a port of sqlite3) on the target. At  
> present I
> am thinking about writing scripts that send SQL over the serial link  
> and
> capture the response.
>
> Can anyone suggest a testing approach that would allow us to make  
> use of the
> existing regression test code without having Tcl on the target system?
>
> Should I give serious consideration to porting Tcl just to run the
> regression tests?
>

One approach taken by many companies is to trust that the workstation- 
based TCL tests correctly validate SQLite and don't worry so much  
about doing complete testing on the target.  Just write a few simple  
sanity checks to make sure that it was compiled correctly and make due  
with that.

Another approach is to port TCL to your target platform.  Depending on  
your platform, this might be a small or a huge task.

The third approach is the TH3 test suite for SQLite described at 
http://www.sqlite.org/testing.html 
.  This third way is, alas, not a free option, but it is available to  
you if are using SQLite in an embedded mission-critical application  
and are uncomfortable  with either of the other two options above.

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Testing SQLite on embedded platform with no Tcl

[Michael Schlenker]

John Efstathiades schrieb:
> Hello,
> 
> I am looking into porting the latest SQLite to an embedded platform running
> a commercial real-time operating system. I'd like to use as much of the
> existing regression test code as possible to ensure the port is correct but
> unfortunately the target environment does not have Tcl. 
Thats rare. Tcl gets ported to really weird places. Unless its really tiny
there should be some Tcl port. Which OS are you using?

If its vxworks have a look at:
http://wiki.tcl.tk/21062
> 
> Should I give serious consideration to porting Tcl just to run the
> regression tests?  
Might be easier than the work around. Usually Tcl is pretty easy to port as
it has very few external dependencies.

Michael

-- 
Michael Schlenker
Software Engineer

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Straße 1-3   Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/  E-Mail: m...@contact.de

Sitz der Gesellschaft: Bremen
Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Testing sqlite

[Shane Harrelson]

If you have TCL installed, you can build the 'testfixture' which is used to
run the tests.

;# Unpack the source tree into "sqlite"
mkdir sqlite
cd sqlite
tar xzf sqlite.tar.gz

;# Build will occur in a sibling directory
cd ..
mkdir bld

;# Change to the build directory
cd bld

;# Run the configure script (this generates makefile in bld directory)
../sqlite/configure

;# Run the makefile.
make

;# Make the testfixture and run fulltest suite
make fulltest


You can run individual tests by running the testfixture directly and
specifying the test you want to run:

testfixture ../sqlite/test/alter.test



On 4/30/08, Lloyd <[EMAIL PROTECTED]> wrote:
>
> Hi,
> In the test folder of sqlite3 source distribution I saw so many test
> scripts. How can I run these tests? I am curious to know the various
> testing methods used in sqlite.
>
> Thanks,
> Lloyd
>
>
> __
> Scanned and protected by Email scanner
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

RE: [sqlite] Testing SQLite

[Brown, Daniel]

Ah, I just figured out why I thought I've not got the tests.  I've been
using the pre-processed C code version of SQLite (as I'm working in
Visual Studio on Windows), I guess I'll need to get the full SQLite
package building with make then to get the test functionality instead of
using the pre-processed source?

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: 13 December 2007 10:14
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Testing SQLite

"Brown, Daniel" <[EMAIL PROTECTED]> wrote:
> Morning List,
> 
> I've just started experimenting with SQLite to see if I can replace 
> our current custom embedded database solution with it and trying to 
> port SQLite to some of our embedded platforms. Are there are any 
> testing frameworks (unit tests would be great) for SQLite?  I'd like 
> to be able to automatically verify I've not broken anything in my 
> porting or tinkering,
> 
> I've had a look round the website but I've not managed to find 
> anything there.
> 

Something like 65% of the SQLite source code in the standard download is
devoted exclusively to testing.  Only 35% of the code actually becomes
part of a production build.

The test scripts are written in TCL.  You'll need to have a TCL
implementation available for your target in order to run the tests.
Assuming you have TCL installed, just do:

 make fulltest

and a bunch of tests will run that given on the order of 98% test
coverage.

--
D. Richard Hipp <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Testing SQLite

[drh]

"Brown, Daniel" <[EMAIL PROTECTED]> wrote:
> Morning List,
> 
> I've just started experimenting with SQLite to see if I can replace our
> current custom embedded database solution with it and trying to port
> SQLite to some of our embedded platforms. Are there are any testing
> frameworks (unit tests would be great) for SQLite?  I'd like to be able
> to automatically verify I've not broken anything in my porting or
> tinkering, 
> 
> I've had a look round the website but I've not managed to find anything
> there.
> 

Something like 65% of the SQLite source code in the standard
download is devoted exclusively to testing.  Only 35% of the
code actually becomes part of a production build.

The test scripts are written in TCL.  You'll need to have a
TCL implementation available for your target in order to run
the tests.  Assuming you have TCL installed, just do:

 make fulltest

and a bunch of tests will run that given on the order of 98%
test coverage.

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-