date:20131107

On Thu, Nov 7, 2013 at 7:57 PM, Simon Slavin  wrote:

>
> > I will definitely look into the wal mode. Are there any test results for
> disk failure robustness of this mode compared to journal mode.
>
> This won't help.  As long as you're using a network file system which does
> not support fsync() properly you will get more corruption.
>

It does help, because the there are fewer times in WAL mode processing
where the order of write operations matter.  Hence, there are fewer
opportunities for a power failure or crash to cause problems.  But it is
not perfect, and corruption can still occur.  The only way to reliably
prevent corruption is to use a filesystem that never reorders writes across
an fsync() call.

The previous paragraph assumes that the cause of your corruption is a bug
in the filesystem that causes writes that happen before fsync() to actually
occur after one or more writes that were issued after the fsync().  If the
cause of corruption is something else, then WAL mode might not help as much.

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

Re: [sqlite] sqlite db getting corrupt on power outage scenarios

On 7 Nov 2013, at 11:50pm, Mayank Kumar (mayankum)  wrote:

> Are there known tools which tell us what data corrupted the db or some way of 
> removing only the corrupted section of the db ?

Download the SQLite shell tool for your platform.
Use .dump to dump the database to a SQL command file.
Then use the same shell tool to create a blank database and .read those 
commands to read that data back in.

We have no way of knowing how much, if any, of the data from the original 
database this will recover.  Take a look at the text file yourself and see if 
you can figure out how much went missing.

> I will definitely look into the wal mode. Are there any test results for disk 
> failure robustness of this mode compared to journal mode.

This won't help.  As long as you're using a network file system which does not 
support fsync() properly you will get more corruption.  Is this database on the 
hard disk in the computer running the SQLite application, or are you accessing 
it across a network ?  What OS and disk format are you using ?

>>> 1.   rc = sqlite3_open("test.db", );

change your app to look at this result code and check that it equals SQLITE_OK. 
 And do the same thing with the result codes returned by all your other 
sqlite3_ calls.  If any of them are not SQLITE_OK report the value and quit.

>>> 1.   when the db gets corrupt, I expect  that the next read/write 
>>> statement will look at the journal and recover from it .

Depends on the nature of the corruption.  Some corruption can't be detected.

>>> So I see the journal file does gets deleted after the power is restored and 
>>> the sqlite tries to write again, but the sqlite db is still corrupt. Is 
>>> there a way to verify the rollback using the journal file was successful or 
>>> not ?

Simply ignoring the journal file, or having had the journal file deleted should 
yield an uncorrupted database.  Possibly slightly out of date, but uncorrupted. 
 This is SQLite's fall-back situation.  Apparently it's not working in your 
case.

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

Re: [sqlite] sqlite db getting corrupt on power outage scenarios

On Thu, Nov 7, 2013 at 6:50 PM, Mayank Kumar (mayankum)
wrote:

>
> I also saw some references to corruption being caused when the same db is
> written to by 3.6 and then being written to by 3.7. Is that true, because
> we have similar scenario where the db was originally written by 3.4.0 and
> when we upgraded our software the db was written to by 3.7.7.1. Do you
> think this scenario could also cause a db corruption.
>
>
Unlikely.  That bug was only present in version 3.7.0 and was fixed in
3.7.0.1.

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

Re: [sqlite] sqlite db getting corrupt on power outage scenarios

2013-11-07 Thread Mayank Kumar (mayankum)

Thanks Richard.
Are there known tools which tell us what data corrupted the db or some way of 
removing only the corrupted section of the db ?
I will definitely look into the wal mode. Are there any test results for disk 
failure robustness of this mode compared to journal mode.

I also saw some references to corruption being caused when the same db is 
written to by 3.6 and then being written to by 3.7. Is that true, because we 
have similar scenario where the db was originally written by 3.4.0 and when we 
upgraded our software the db was written to by 3.7.7.1. Do you think this 
scenario could also cause a db corruption.

-Mayank

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Wednesday, November 06, 2013 11:18 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] sqlite db getting corrupt on power outage scenarios

On Wed, Nov 6, 2013 at 2:10 PM, Mayank Kumar (mayankum)
wrote:

> Hi Sqlite Users
> I am running into a situation where sometimes after the power is 
> restored my sqlite db running on linux is corrupted. Here is what I 
> see with
> integrity_check:-
>
> *** in database main ***
> Page 895: btreeInitPage() returns error code 11 On tree page 2 cell 
> 765: Child page depth differs On tree page 2 cell 766: Child page 
> depth differs
>

SQLite is suppose to recover automatically from a power loss, rolling back the 
last transaction, and NOT corrupting the database file.

However, in order to do this, SQLite relies on the filesystem behaving as 
advertised.  Corruption such as you describe can result from a broken
fsync() implementation or from consumer-grade disk drives that lie about having 
flushed their track buffers (saying that they have flushed their track buffers 
to oxide when in fact they have not).  Please read more at

http://www.sqlite.org/howtocorrupt.html

Please note that setting "PRAGMA journal_mode=WAL" makes SQLite more resistent 
to disk-drive lies.  Not immune, but more resistant.  You might want to set WAL 
mode if you have not done so already.

--
D. Richard Hipp
d...@sqlite.org
___
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] Row Level Locking as in InnoDB


On 7 Nov 2013, at 6:31pm, Raheel Gupta  wrote:

> Any idea when will SQLite4 be released as stable ?

No.  It's not even feature-frozen yet, as far as we know.  And whenever it is, 
it's incredibly unlikely to have row level locking.

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

Re: [sqlite] Row Level Locking as in InnoDB

2013-11-07 Thread Raheel Gupta

>> SQLite4 still in development phase. It is not production ready.

Any idea when will SQLite4 be released as stable ?


On Tue, Nov 5, 2013 at 8:20 AM, Howard Chu  wrote:

> Aris Setyawan wrote:
>
>> Hi Howard,
>>
>>  I just looked, sophia is nothing special. See these microbench results.
>>> http://pastebin.com/cFK1JsCN
>>>
>>> LMDB's codebase is still smaller and faster. Nothing else touches LMDB's
>>> read
>>> speed.
>>>
>>
>> This is micro benchmark from sophia author compare with lmdb.
>> http://sphia.org/benchmarks.html
>>
>
> Quite off-topic for this list, but those results are garbage.
> https://github.com/pmwkaa/sophia_benchmark/issues/2#issuecomment-27740082
>
>
> --
>   -- Howard Chu
>   CTO, Symas Corp.   http://www.symas.com
>   Director, Highland Sun http://highlandsun.com/hyc/
>   Chief Architect, OpenLDAP  http://www.openldap.org/project/
> ___
> 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] Where does SQLite write out files?

On 7 Nov 2013, at 5:42pm, L. Wood  wrote:

> How would you adapt SQLite to this environment?
> 
> Here are my own thoughts so far (based on the webpage 
> http://sqlite.org/tempfiles.html):
> 
> * I'm aware of the files that SQLite can write to the *same* directory as 
> that of the actual database file, and I have ways to deal with that (by 
> putting the database file into a so-called package, so all the files will be 
> together as a unit).

Sandboxed apps cannot change their own bundles.  This is to stop an app from 
downloading new code and rewriting itself, turning itself into malware.  You 
should use the app's container directory as described here:

Be careful about how you're using "cStringUsingEncoding:" or "UTF8String" and 
it'll all work fine.

> * If I set the global variable sqlite3_temp_directory 
> (http://sqlite.org/c3ref/temp_directory.html) to the value returned by 
> Apple's NSTemporaryDirectory(), I'm hoping to be fine. Will I?

You should find that the default temp directory should already be set to a 
place that's compatible with app sandboxing.  I don't remember whether it 
actually was NSTemporaryDirectory() but it was somewhere that's safe and 
worked.  Or at least it was when I tested.  Of course, this may have been 
changed in later releases of OS X, so if you find you do get errors please tell 
us.

On the other hand, if you feel happier explicitly setting it to 
NSTemporaryDirectory then that should work too.

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

Re: [sqlite] Handling move/rename of an SQLite database file

On 7 Nov 2013, at 6:03pm, L. Wood  wrote:

> I did a simple experiment and got an error from sqlite3_step() after renaming 
> the file. This is what I did:
> 
> After the call to sqlite3_open(), I halt/sleep my program for 10 seconds to 
> give me ample time to rename the file. I rename the file. The subsequent 
> sqlite3_prepare_v2() works without errors. However, the sqlite3_step() *does* 
> give an error: Error code 10, "disk I/O error". This does not happen if I do 
> not rename the file.
> 
> Am I missing something? 

SQLite's _open() function doesn't actually open the file.  It sets things up so 
that the first proper operation on the file can open it, when it needs to.

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

Re: [sqlite] Handling move/rename of an SQLite database file

2013-11-07 Thread L. Wood

> D. Richard Hipp wrote:
> No errors are returned. SQLite never notices that the database file has
> been renamed.

I did a simple experiment and got an error from sqlite3_step() after renaming 
the file. This is what I did:

After the call to sqlite3_open(), I halt/sleep my program for 10 seconds to 
give me ample time to rename the file. I rename the file. The subsequent 
sqlite3_prepare_v2() works without errors. However, the sqlite3_step() *does* 
give an error: Error code 10, "disk I/O error". This does not happen if I do 
not rename the file.

Am I missing something?   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling move/rename of an SQLite database file


On 7 Nov 2013, at 5:38pm, Richard Hipp  wrote:

> Rollback journal files might be closed and reopened.

Okay.  That gives us more of the answer to the original question.

Wood, this is the only file that SQLite would care about if moved.  And 
technically you could use NSNotification to tell you if someone moved a journal 
file while SQLite was using it.  But in practise, under both OS X and iOS, it 
doesn't matter.  Losing that file between transactions isn't going to corrupt 
your database and if someone does move or delete it at that point the best 
thing your app could do is crash, so the user knows not to do that.

So yes, OS X does give you a mechanism for keeping track of files being moved.  
But there's not much point in using it with a SQLite database.

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

[sqlite] Where does SQLite write out files?

2013-11-07 Thread L. Wood

What directories can SQLite possibly write files to?

Modern Mac OS X programs run in "sandbox mode". This is a requirement to 
publish apps on Apple's Mac App Store. "Sandboxing" means that it is impossible 
for an app to write files into locations outside a given authorized "sandbox".

For instance, a proper way to get a safe temporary directory is Apple's own C 
API, NSTemporaryDirectory().

How would you adapt SQLite to this environment?


Here are my own thoughts so far (based on the webpage 
http://sqlite.org/tempfiles.html):

* I'm aware of the files that SQLite can write to the *same* directory as that 
of the actual database file, and I have ways to deal with that (by putting the 
database file into a so-called package, so all the files will be together as a 
unit).

* If I set the global variable sqlite3_temp_directory 
(http://sqlite.org/c3ref/temp_directory.html) to the value returned by Apple's 
NSTemporaryDirectory(), I'm hoping to be fine. Will I?

Unfortunately, the webpage also says:

"The manner in which SQLite uses temporary files is not considered part of the 
contract that SQLite makes with applications. The information in this document 
is a correct description of how SQLite operates at the time that this document 
was written or last updated. But there is no guarantee that future versions of 
SQLite will use temporary files in the same way. New kinds of temporary files 
might be employed and some of the current temporary file uses might be 
discontinued in future releases of SQLite."

This renders any efforts in this regard ill-defined. Are there any solid ways 
out? I'm using SQLite 3.8.1, and are the rules on what webpage even true for 
this version? That is nowhere mentioned.   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling move/rename of an SQLite database file

On Thu, Nov 7, 2013 at 12:04 PM, Simon Slavin  wrote:

>
> Assuming that the user is using OS X, use of standard VFS, access on a
> local disk, no ATTACH, no Jedi mind tricks ...
>
> Does SQLite ever close and reopen database or journal files between
> _open() and _close() ?
>

Rollback journal files might be closed and reopened.  But the main database
file is opened once and held open until sqlite3_close() (or DETACH if the
file was originally opened using ATTACH).

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

Re: [sqlite] Handling move/rename of an SQLite database file

On 7 Nov 2013, at 3:58pm, Richard Hipp  wrote:

> On Thu, Nov 7, 2013 at 10:31 AM, L. Wood  wrote:
> 
>> QUESTION: What error codes can each of the following functions possibly
>> return while the database file is renamed/moved during the execution of the
>> function?
> 
> No errors are returned.  SQLite never notices that the database file has
> been renamed.
> 
> In Unix, once a file is opened, the process only deals with the file
> descriptor.  The underlying filename can change or even deleted (unlinked)
> and the application will never know.

Richard (or any of the team),

Assuming that the user is using OS X, use of standard VFS, access on a local 
disk, no ATTACH, no Jedi mind tricks ...

Does SQLite ever close and reopen database or journal files between _open() and 
_close() ?  An example of a time I might expect this would be during a VACUUM, 
or as a transaction finishes while journal_mode = DELETE.

My guess is that if SQLite closes one of these files it creates a new one with 
the name and path it already knows, but I wonder if it might close and reopen 
one of these files.  Of course, if it closes a file you've moved, then tries to 
delete it, it won't be able to delete it.

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

Re: [sqlite] PRAGMA cache_size when copying data from two DBs

2013-11-07 Thread jose isaias cabrera



"Richard Hipp" asked...


On Thu, Nov 7, 2013 at 10:13 AM, jose isaias cabrera
wrote:



Greetings!

When copying data from two DBs, do the cache_size of PRAGMA needs to
match
both connection?  What type of response, issues or problem would it cause
when the two connection's PRAGMA cache_size are different?



It shouldn't make any difference.  Are you having problems?


No, Dr. Hipp, but I have a sharedDB that I would like to change the cache to 
get faster push and release, but with 20 shared users, and before they get a 
new version of the PM program with the new cache change, I wanted to know if 
it would cause damage to the DB or "something".  I will let you know. :-)


Thanks.

josé

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

Re: [sqlite] PRAGMA cache_size when copying data from two DBs

On Thu, Nov 7, 2013 at 10:13 AM, jose isaias cabrera
wrote:

>
> Greetings!
>
> When copying data from two DBs, do the cache_size of PRAGMA needs to match
> both connection?  What type of response, issues or problem would it cause
> when the two connection's PRAGMA cache_size are different?
>

It shouldn't make any difference.  Are you having problems?

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

Re: [sqlite] Handling move/rename of an SQLite database file

2013-11-07 Thread Zsbán Ambrus

On Thu, Nov 7, 2013 at 4:31 PM, L. Wood  wrote:
> Users could rename/move a database file while my (Mac OS X) program has made 
> an SQLite connection to the file. I want to handle this properly.

One thing you have to be particularly careful about is the extra files
sqlite creates next to the database file, such as the rollback journal
files.  The page http://sqlite.org/tempfiles.html documents how sqlite
currently uses these.  In particular, if sqlite finds the database
file but does not file the associated journal file, your database can
go corrupted.  I don't know how you could assure that this does not
happen.

Instead of trying to handle errors from the frontend of sqlite, you
might get a more robust solution if you wrote a custom VFS backend for
sqlite, possibly by modifying the existing uniq VSF backend.  The VFS
is documented on http://sqlite.org/vfs.html .

Sqlite performs all operating system dependent functions through the
replacable VFS backend.  These operations include opening a file,
reading, writing, file locking.  This way if, for example, if you
detect that a user have moved the database file and sqlite wants to
open the corresponding rollback journal file, you can make sure that
it opens the correct rollback journal.

Besides ensuring that there the extra files are found correctly, you
will also have to make sure that file locking works correctly accross
moves.  All this gets quite complicated (maybe even impossible),
especially if multiple programs want to open the same sqlite database
concurrently.

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

Re: [sqlite] Handling move/rename of an SQLite database file

On Thu, Nov 7, 2013 at 10:31 AM, L. Wood  wrote:

>
> QUESTION: What error codes can each of the following functions possibly
> return while the database file is renamed/moved during the execution of the
> function?
>

No errors are returned.  SQLite never notices that the database file has
been renamed.

In Unix, once a file is opened, the process only deals with the file
descriptor.  The underlying filename can change or even deleted (unlinked)
and the application will never know.

Note that renaming or unlinking a database file while it is being written,
then killing the writer process or turning the power off, can corrupt the
database file since it leave the associated journal file with a different
name.  See http://www.sqlite.org/howtocorrupt.html#unlink for additional
information.

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

[sqlite] Handling move/rename of an SQLite database file

2013-11-07 Thread L. Wood

Users could rename/move a database file while my (Mac OS X) program has made an 
SQLite connection to the file. I want to handle this properly.


Mac OS X (Unix) has ways to track a file that is renamed/moved, so if I can get 
enough information about how SQLite works in this regard, handling this should 
be possible.


Below is a question that I believe I need answered to be able handle this 
properly, but feel free to add more thoughts of your own if you think I need 
more information.


QUESTION: What error codes can each of the following functions possibly return 
while the database file is renamed/moved during the execution of the function?


* sqlite3_open()
* sqlite3_prepare_v2()
* sqlite3_step()
* The various "sqlite3_column()" functions - could they return something 
unexpected?
* sqlite3_finalize()
* sqlite3_close()



My thinking is that if I knew all the possible error codes, I could - upon such 
an error - check if the file was renamed/moved. If it was, I could close and 
reopen the database connection on the new path and continue/restart execution 
as required. If it was not renamed/moved, it would be a genuine error to be 
handled like any other error. (And of course, I would perform the renamed/moved 
check in between the SQLite function calls, but that's not related to SQLite 
per se.)


Again, you're welcome to add your own thoughts to the mix. Thank you.


I'm using SQLite 3.8.1.   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] PRAGMA cache_size when copying data from two DBs

2013-11-07 Thread jose isaias cabrera



Greetings!

When copying data from two DBs, do the cache_size of PRAGMA needs to match 
both connection?  What type of response, issues or problem would it cause 
when the two connection's PRAGMA cache_size are different?


thanks,

josé 


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

Re: [sqlite] Disparity between query time in SQLite Administrator and Flash Pro

2013-11-07 Thread SongbookDB

Thanks Stephen - had a quick play with SQLite Expert just now before sleep
- found a 3.7.4 dll and dropped it in and it loaded up fine. I'll see if
the queries take the same time as in Flash tomorrow.


On Thu, Nov 7, 2013 at 10:53 PM, Stephen Chrzanowski wrote:

> SQLite Expert allows you to use any SQLite DLL.  Its free for
> non-commercial use, but even that said, its an awesome package to buy, life
> time upgrades, and seemingly always being updated.
>
> http://www.sqliteexpert.com/
>
>
>
> On Wed, Nov 6, 2013 at 7:30 PM, SongbookDB  >wrote:
>
> > Just got a reply re the version Flash uses - it's 3.7.4 RC4, which is
> later
> > than what SQLite Administrator uses (3.5.1).
> >
> > Is there a program like SQLite Administrator that will let me load a
> > particular version of SQLite, so my testing will match the version in
> Flash
> > AIR?
> >
> > FYI - he used this to get the version from AIR - SELECT
> sqlite_source_id()
> > - and then looked the hash code returned up on the SQLite timeline at
> > http://www.sqlite.org/cgi/src/info/a586a4deeb253300
> >
> >
> > On Thu, Nov 7, 2013 at 11:21 AM, Simon Slavin 
> > wrote:
> >
> > >
> > > On 7 Nov 2013, at 12:09am, SongbookDB 
> > > wrote:
> > >
> > > > The database seems to write fine. I have it loading into a datagrid
> in
> > my
> > > > Flash AIR desktop app, from where I read it and make edits to it.
> > >
> > > I don't understand how any SQLite app can return the error you gave:
> > >
> > > >> Flash gives the error - no such function: 'sqlite_version'
> > >
> > > All APIs that allow you to execute SQLite commands should let you
> execute
> > >
> > > SELECT sqlite_version()
> > >
> > > so perhaps you have better ask the Flash Pro people about this problem.
> > >
> > > Simon.
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> > --
> > Shaun Thomson
> > Owner - SongbookDB
> > ___
> > 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
>



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

Re: [sqlite] tools list from sqlite.org

On 7 Nov 2013, at 1:48pm, dd  wrote:

>  I used to include sqlite3.h/c files (from amalgamation) in
> application. I would like to know about tools from sqlite.org.
> 
>  What are all the tools available from sqlite.org? Where can I find
> the list of tools/utilities? any document?

There's a download page on that web site.  All the common downloads are listed 
on that page.

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

[sqlite] tools list from sqlite.org

Hi,

  I used to include sqlite3.h/c files (from amalgamation) in
application. I would like to know about tools from sqlite.org.

  What are all the tools available from sqlite.org? Where can I find
the list of tools/utilities? any document?

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

Re: [sqlite] Need suggestion for database scheama

It has to work with multiple devices in future. This is valid point
for me. Thanks.

On Thu, Nov 7, 2013 at 3:42 PM, Stephan Beal  wrote:
> On Thu, Nov 7, 2013 at 12:15 PM, dd  wrote:
>
>> Thanks for pointing multimedia id, Stephan Beal. I missed it. I will
>> ad this to my schema.
>>
>
> If you're only storing the list for local use on one machine, adding the
> media ID is almost certainly overkill, but i found it useful for my
> lots-of-external-drives collection, and it works regardless of the type of
> media. e.g. i also had self-burned DVDs in the collection. Instead of
> writing down what was on each DVD, i simply wrote its label/ID on the front
> of it. Back in the 80's and early 90's applications for indexing floppy
> disk collections using a similar approach was relatively common.
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> "Since tyranny's the only guaranteed byproduct of those who insist on a
> perfect world, freedom will have to do." -- Bigby Wolf
> ___
> 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] Registering Virtual Tables on the spot (when needed)

2013-11-07 Thread Eric Grange

Hi,

I would like to use SQLite in a scripting context where there are thousands
of "potential" virtual tables, but of these, only a very very small subset
(a couple or a handful at most) would be actually used in any given script,
so it wouldn't make sense to expose them all, all the time.

I'm looking for a way to leverage the existing SQLite SQL parser to figure
out which of those tables are actually used, and expose only those.

Basically, some approach like
- pass the SQL without exposing anything, which could result in an error
- if the error correspond to "unknown table", expose the table
- repeat until there are no more errors (or a "real" error is encountered)

I could use a custom SQL parser, but that would be complicated, would have
to be kept compatible with SQLite evolutions, and the parsing would be
completely wasted when the tables have already been exposed.

Parsing the error message is simple and works, but it's also fragile if the
message text is changed... Is there a more reliable way? Something like an
sqlite3_errentity() that would return the unknown table name when the
sqlite3_errcode() says the error is an unknown table, that would return the
unknown column name when the errcode is an unknown field etc.

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

Re: [sqlite] Query / schema optimization procedure?

2013-11-07 Thread Daniel Polski

Thanks alot,
I will read through your email in detail later and think it through, but
want to send a quick reply right now.

About complexity: I see that it looks overly complex, especially when
using table names like 't1' instead of names which makes sense in the
real world. I understand that it's hard to give any good advice here
without seeing the real usage. I do think that the design is fairly
"good" though, basically 3nf.

About triggers: They're only used in this example to fill the database
with some test data and is removed after they've been used.

About t11, t12: You're correct that they're doing something related, but
is separated on purpose to let the user link data different ways

Thanks alot!
/Daniel

Simon Slavin skrev 2013-11-07 14:08:

On 7 Nov 2013, at 10:52am, Daniel Polski wrote:

I would be happy to get advice on how to think / what to look for when trying
to optimize a query, views, adding indexes, optimizing schema design and so on.

So far I've figured out that I could add appropriate indexes since the 'explain
query plan' indicates the query is using many automatic indices, but I haven't
added any extra yet since I would like to provide you with the original schema.

Not about to go through the whole thing, but a few things jump out at me.
Mostly that this is too big and too complicated and looks like you're trying to
keep your entire business logic in a database. While technically your final
query_view might work, the fact that it is so complicated suggests that your
database design originated back in the mainframe days. Anyone trying to
understand your schema or any software that relies on it is going to be
completely lost. As someone who has had to work on code written 20 years ago
by people long since retired, that setup concerns me.

In short, the reason it's not obvious to you what indexes are going to be
useful is that your design is too complicated and relies on too much inside
knowledge.

* Tables t11 and t12 look like they should be the same thing, or related in
some way.

* SQLite does not have a DATETIME type.

* Don't use single quotes (or even double quotes) to delimit constraint names.
Single quotes are for specifying strings.

* Your date/times in t13 should probably be encoded as a startDateTime and an
endDateTime. Having to search/match both on date and time makes stuff
unnecessarily complicated.

* Special use for some id values in t1 and t3 and t5. Looks weird. If these
are two different kinds of things they should be marked differently (have
different values in a column) or exist in different tables or something, not
rely on some special internally-known business logic that knows that 16, 32,
1000 and 2000 are magic numbers. Think through why those rows are so special
and figure out a way to use tables and columns to do it. Or keep that logic
entirely outside your database definitions.

* TRIGGERs t1 --> t1 --> t2 --> t4, t3 --> t3, t5 --> t5, t7 --> t7, t16 -->
t16. Anything that complicated is bound, sooner or later, to collapse. If you intend to keep level
of complexity you need to write some database-checking code which checks the integrity of your
database cross-references and makes sure none of those trigger got violated. Generally speaking any
code which uses both FOREIGN KEYs and TRIGGERs shows a partially-completed conversion from one
schema to another.

* If your demo code needs to turn recursive triggers off, you're probably doing
something wrong somewhere. If those recursive triggers reflected how things
work in the real world, you would never need to turn them off.

* don't rely on the results of anything like "strftime('%s', datetime('now'),
'localtime') - strftime('%s', temp_table.starttime) AS timer". Definitions of 'now'
within SQL are shaky at best (does it change during a SELECT that takes 10 minutes to
execute ?). Things like that should be done in your software, not in your database.

Dan, I'm sorry to be so complaining of your setup but it so strongly reminds me
of COBOL and other things I haven't seen since mainframe days. I suspect that
working to remove some of your internal business logic (magic numbers
especially) will force you to simplify your tables to the point that your final
SELECT will so simple you can figure out indexes by yourself.

Simon.
___
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] Query / schema optimization procedure?

On 7 Nov 2013, at 10:52am, Daniel Polski  wrote:

> I would be happy to get advice on how to think / what to look for when trying 
> to optimize a query, views, adding indexes, optimizing schema design and so 
> on.
> 
> So far I've figured out that I could add appropriate indexes since the 
> 'explain query plan' indicates the query is using many automatic indices, but 
> I haven't added any extra yet since I would like to provide you with the 
> original schema.

Not about to go through the whole thing, but a few things jump out at me.  
Mostly that this is too big and too complicated and looks like you're trying to 
keep your entire business logic in a database.  While technically your final 
query_view might work, the fact that it is so complicated suggests that your 
database design originated back in the mainframe days.  Anyone trying to 
understand your schema or any software that relies on it is going to be 
completely lost.  As someone who has had to work on code written 20 years ago 
by people long since retired, that setup concerns me.

In short, the reason it's not obvious to you what indexes are going to be 
useful is that your design is too complicated and relies on too much inside 
knowledge.

* Tables t11 and t12 look like they should be the same thing, or related in 
some way.

* SQLite does not have a DATETIME type.

* Don't use single quotes (or even double quotes) to delimit constraint names.  
Single quotes are for specifying strings.

* Your date/times in t13 should probably be encoded as a startDateTime and an 
endDateTime.  Having to search/match both on date and time makes stuff 
unnecessarily complicated.

* Special use for some id values in t1 and t3 and t5.  Looks weird.  If these 
are two different kinds of things they should be marked differently (have 
different values in a column) or exist in different tables or something, not 
rely on some special internally-known business logic that knows that 16, 32, 
1000 and 2000 are magic numbers.  Think through why those rows are so special 
and figure out a way to use tables and columns to do it.  Or keep that logic 
entirely outside your database definitions.

* TRIGGERs t1 --> t1 --> t2 --> t4, t3 --> t3, t5 --> t5, t7 --> t7, t16 --> 
t16.  Anything that complicated is bound, sooner or later, to collapse.  If you 
intend to keep level of complexity you need to write some database-checking 
code which checks the integrity of your database cross-references and makes 
sure none of those trigger got violated.  Generally speaking any code which 
uses both FOREIGN KEYs and TRIGGERs shows a partially-completed conversion from 
one schema to another.

* If your demo code needs to turn recursive triggers off, you're probably doing 
something wrong somewhere.  If those recursive triggers reflected how things 
work in the real world, you would never need to turn them off.

* don't rely on the results of anything like  "strftime('%s', datetime('now'), 
'localtime') - strftime('%s', temp_table.starttime) AS timer".  Definitions of 
'now' within SQL are shaky at best (does it change during a SELECT that takes 
10 minutes to execute ?).  Things like that should be done in your software, 
not in your database.

Dan, I'm sorry to be so complaining of your setup but it so strongly reminds me 
of COBOL and other things I haven't seen since mainframe days.  I suspect that 
working to remove some of your internal business logic (magic numbers 
especially) will force you to simplify your tables to the point that your final 
SELECT will so simple you can figure out indexes by yourself.

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

Re: [sqlite] Disparity between query time in SQLite Administrator and Flash Pro

2013-11-07 Thread Stephen Chrzanowski

SQLite Expert allows you to use any SQLite DLL.  Its free for
non-commercial use, but even that said, its an awesome package to buy, life
time upgrades, and seemingly always being updated.

http://www.sqliteexpert.com/



On Wed, Nov 6, 2013 at 7:30 PM, SongbookDB wrote:

> Just got a reply re the version Flash uses - it's 3.7.4 RC4, which is later
> than what SQLite Administrator uses (3.5.1).
>
> Is there a program like SQLite Administrator that will let me load a
> particular version of SQLite, so my testing will match the version in Flash
> AIR?
>
> FYI - he used this to get the version from AIR - SELECT sqlite_source_id()
> - and then looked the hash code returned up on the SQLite timeline at
> http://www.sqlite.org/cgi/src/info/a586a4deeb253300
>
>
> On Thu, Nov 7, 2013 at 11:21 AM, Simon Slavin 
> wrote:
>
> >
> > On 7 Nov 2013, at 12:09am, SongbookDB 
> > wrote:
> >
> > > The database seems to write fine. I have it loading into a datagrid in
> my
> > > Flash AIR desktop app, from where I read it and make edits to it.
> >
> > I don't understand how any SQLite app can return the error you gave:
> >
> > >> Flash gives the error - no such function: 'sqlite_version'
> >
> > All APIs that allow you to execute SQLite commands should let you execute
> >
> > SELECT sqlite_version()
> >
> > so perhaps you have better ask the Flash Pro people about this problem.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Shaun Thomson
> Owner - SongbookDB
> ___
> 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] Need suggestion for database scheama

2013-11-07 Thread Stephan Beal

On Thu, Nov 7, 2013 at 12:15 PM, dd  wrote:

> Thanks for pointing multimedia id, Stephan Beal. I missed it. I will
> ad this to my schema.
>

If you're only storing the list for local use on one machine, adding the
media ID is almost certainly overkill, but i found it useful for my
lots-of-external-drives collection, and it works regardless of the type of
media. e.g. i also had self-burned DVDs in the collection. Instead of
writing down what was on each DVD, i simply wrote its label/ID on the front
of it. Back in the 80's and early 90's applications for indexing floppy
disk collections using a similar approach was relatively common.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Since tyranny's the only guaranteed byproduct of those who insist on a
perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need suggestion for database scheama

Thanks for pointing multimedia id, Stephan Beal. I missed it. I will
ad this to my schema.

On Thu, Nov 7, 2013 at 3:07 PM, Stephan Beal  wrote:
> On Thu, Nov 7, 2013 at 11:50 AM, dd  wrote:
>
>> CREATE TABLE if not exists AllFilesTbl (Id INTEGER PRIMARY KEY
>> AUTOINCREMENT, file_path TEXT NOT NULL, file_type INTEGER NOT NULL,
>> UNIQUE(file_path));
>>
>
> Some years ago i worked on something similar to keep track of my multimedia
> collection and i added one layer of abstraction to this table: instead of
> storing just a file path, i stored a media ID and a path, with the media ID
> effectively defining the root path. e.g. let's say i've got 5 external hard
> drives, each of them with a unique label. Mine were called
> MM--, e.g. MM-500-00, MM-500-01, etc.
> (MM==MultiMedia). The file path is then relative to wherever that media
> (device) is mounted. In my case i stored the media id as a simple string,
> but arguably more correct would be to store it as an integer and then have
> a separate lookup table mapping id==>name. A view could then easily
> translate that into a more human-readable form.
>
> Was it worth the effort? In my case it was because my media was scattered
> around many external drives and this allowed me to quickly determine which
> drive. But for simple use cases it very possibly isn't worth the extra
> effort. Unfortunately, i have long since lost all that code (it was
> implemented in JS, using a SpiderMonkey binding for sqlite), so i don't
> have it to share with you, but the idea is simple enough that it doesn't
> really need a demonstration.
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> "Since tyranny's the only guaranteed byproduct of those who insist on a
> perfect world, freedom will have to do." -- Bigby Wolf
> ___
> 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] Need suggestion for database scheama

2013-11-07 Thread Stephan Beal

On Thu, Nov 7, 2013 at 11:50 AM, dd  wrote:

> CREATE TABLE if not exists AllFilesTbl (Id INTEGER PRIMARY KEY
> AUTOINCREMENT, file_path TEXT NOT NULL, file_type INTEGER NOT NULL,
> UNIQUE(file_path));
>

Some years ago i worked on something similar to keep track of my multimedia
collection and i added one layer of abstraction to this table: instead of
storing just a file path, i stored a media ID and a path, with the media ID
effectively defining the root path. e.g. let's say i've got 5 external hard
drives, each of them with a unique label. Mine were called
MM--, e.g. MM-500-00, MM-500-01, etc.
(MM==MultiMedia). The file path is then relative to wherever that media
(device) is mounted. In my case i stored the media id as a simple string,
but arguably more correct would be to store it as an integer and then have
a separate lookup table mapping id==>name. A view could then easily
translate that into a more human-readable form.

Was it worth the effort? In my case it was because my media was scattered
around many external drives and this allowed me to quickly determine which
drive. But for simple use cases it very possibly isn't worth the extra
effort. Unfortunately, i have long since lost all that code (it was
implemented in JS, using a SpiderMonkey binding for sqlite), so i don't
have it to share with you, but the idea is simple enough that it doesn't
really need a demonstration.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Since tyranny's the only guaranteed byproduct of those who insist on a
perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query / schema optimization procedure?

2013-11-07 Thread Daniel Polski


Hello,
I have an SQL query which fetches the requested data from the database, 
but is using too much resources when doing so.


I would be happy to get advice on how to think / what to look for when 
trying to optimize a query, views, adding indexes, optimizing schema 
design and so on.


So far I've figured out that I could add appropriate indexes since the 
'explain query plan' indicates the query is using many automatic 
indices, but I haven't added any extra yet since I would like to provide 
you with the original schema.


I'm not allowed to send the real database, but I've created a way to 
recreate the schema layout & add needed test data below. I'm sorry it's 
quite long, but since I'm not sure what optimizations might be done I 
rather describe too much than too little (but I've stripped some tables 
of columns I find irrelevant for the query in question).


I've also created an extra view 'query_view' which contains the query 
I'm trying to optimize.
In the real application the 'v4.t1_searchvalue = 16' in the query_view 
is changing from time to time and not a static value.


SELECT v2_id FROM query_view;
Should result in v2_id's in order: 1, 4, 6, 8, 11, 3

I'm using SQLite 3.8.1

I would be very happy if you help me with the optimization, but even 
happier if you explain how you think to reach the goal.


It's also possible to tweak the database/view layout, but it depends on 
how big impact it would make to the processing speed.


Best regards,
Daniel



PRAGMA FOREIGN_KEYS = 0;

DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
DROP TABLE IF EXISTS t4;
DROP TABLE IF EXISTS t5;
DROP TABLE IF EXISTS t6;

DROP TABLE IF EXISTS t7;
DROP TABLE IF EXISTS t8;
DROP TABLE IF EXISTS t9;
DROP TABLE IF EXISTS t10;
DROP TABLE IF EXISTS t11;
DROP TABLE IF EXISTS t12;
DROP TABLE IF EXISTS t13;
DROP TABLE IF EXISTS t14;
DROP TABLE IF EXISTS t15;
DROP TABLE IF EXISTS t16;

DROP VIEW IF EXISTS v1;
DROP VIEW IF EXISTS v2;
DROP VIEW IF EXISTS v3;
DROP VIEW IF EXISTS v4;

PRAGMA FOREIGN_KEYS = 1;

CREATE TABLE t1 (
id INTEGER PRIMARY KEY,
t1_searchvalue INT
);

CREATE TABLE t2 (
id INTEGER PRIMARY KEY,
t2_searchvalue INTEGER NOT NULL,
t1_id INT NOT NULL,
CONSTRAINT "FK_t2" FOREIGN KEY( t1_id ) REFERENCES t1 ( id ) ON 
DELETE CASCADE

);

CREATE TABLE t3 (
id INT NOT NULL,
t3_value_1 varchar( 10 ) NOT NULL,
disabled BOOLEAN,
t3_value_2 BOOLEAN,
CONSTRAINT "PK_t3" PRIMARY KEY(id)
);

CREATE TABLE t4 (
id INTEGER PRIMARY KEY,
t3_id INT NOT NULL,
t4_value_1 VARCHAR( 10 ) NOT NULL,
searchvalue INT NOT NULL,
t2_id INT NOT NULL,

CONSTRAINT "FK_t4_002" FOREIGN KEY( t3_id ) REFERENCES t3 ( id ),
CONSTRAINT "FK_t4_003" FOREIGN KEY( t2_id ) REFERENCES t2 ( id ) ON 
DELETE CASCADE

);

CREATE TABLE t5 (
id INTEGER PRIMARY KEY,
name VARCHAR(20) NOT NULL
);

CREATE TABLE t6 (
id INTEGER PRIMARY KEY,
starttime DATETIME NOT NULL,
t5_id INT NOT NULL,
t6_value_1 BOOLEAN NOT NULL,
t6_value_2 BOOLEAN NOT NULL DEFAULT 0,

CONSTRAINT 'FK_t6_001' FOREIGN KEY( t5_id ) REFERENCES t5 ( id )
);

CREATE TABLE t7 (
key_part_1 INT NOT NULL,
key_part_2 INT NOT NULL,
last_seen DATETIME NOT NULL,
t1_searchvalue INT NOT NULL,
t2_searchvalue INT NOT NULL,
t4_searchvalue INT NOT NULL,
CONSTRAINT "PK_t7" PRIMARY KEY(key_part_1, key_part_2)
);

CREATE TABLE t8 (
t6_id INT NOT NULL,
t7_key_part_1 INT NOT NULL,
t7_key_part_2 INT NOT NULL,
CONSTRAINT "FK_t8_001" FOREIGN KEY( t6_id ) REFERENCES t6 ( id ) ON 
DELETE CASCADE,
CONSTRAINT "FK_t8_002" FOREIGN KEY(t7_key_part_1, t7_key_part_2) 
REFERENCES t7 ( key_part_1, key_part_2)

);

CREATE TABLE t9 (
id INTEGER PRIMARY KEY,
name VARCHAR(30),
enabled BOOLEAN NOT NULL
);

CREATE TABLE t10 (
t4_id INT NOT NULL,
t9_id INT NOT NULL,
CONSTRAINT 'PK_t10' PRIMARY KEY( t4_id, t9_id),
CONSTRAINT 'FK_t10_001' FOREIGN KEY( t4_id ) REFERENCES t4 ( id ) 
ON DELETE CASCADE,
CONSTRAINT 'FK_t10_002' FOREIGN KEY( t9_id ) REFERENCES t9 ( id ) 
ON DELETE CASCADE

);

CREATE TABLE t11 (
t3_id INT NOT NULL,
t9_id INT NOT NULL,
CONSTRAINT 'PK_t11' PRIMARY KEY( t3_id, t9_id),
CONSTRAINT 'FK_t11_001' FOREIGN KEY( t3_id ) REFERENCES t3 ( id ) 
ON DELETE CASCADE,
CONSTRAINT 'FK_t11_002' FOREIGN KEY( t9_id ) REFERENCES t9 ( id ) 
ON DELETE CASCADE

);

CREATE TABLE t12 (
t9_id INT NOT NULL,
t3_id INT NOT NULL,
CONSTRAINT 'PK_t12' PRIMARY KEY( t9_id, t3_id),
CONSTRAINT 'FK_t12_001' FOREIGN KEY( t9_id ) REFERENCES t9 ( id ) 
ON DELETE CASCADE,
CONSTRAINT 'FK_t12_002' FOREIGN KEY( t3_id ) REFERENCES t3 ( id ) 
ON DELETE CASCADE

);

CREATE TABLE t13 (
t9_id INT NOT NULL,
day INT NOT NULL,
starttime TIME NOT NULL,
endtime TIME NOT NULL,
CONSTRAINT 'PK_t13' PRIMARY KEY( t9_id, starttime, day),
CONSTRAINT 'FK_t13' FOREIGN KEY( t9_id ) REFERENCES t9 ( id ) ON 
DELETE

[sqlite] Need suggestion for database scheama