Re: [sqlite] fsync on iOS

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 16/01/14 11:43, Ward Willats wrote:
> So it looks like fsync() is taking more than the 5 second timeout I've
> set.

This is not uncommon on mobile devices using flash based storage.  There
is a lot of volatility in read and write performance.

I don't know about the filesystem on iOS, but on Linux ext4 filesystem an
fsync turns into a sync of the whole filesystem.  This is because the
filesystem is journalled and it would be very difficult to get only the
necessary pieces written out as they could have interactions with other
files/directories/data in flight.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.14 (GNU/Linux)

iEYEARECAAYFAlLYT00ACgkQmOOfHg372QTw3QCfQNoOjJHrs9+pTSNmAx5BCoXk
essAoM0wmW454CL54bf0u2XTqV9vNxXP
=DY1e
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Saw something interesting in the debugger...

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/01/14 08:53, Ward Willats wrote:
> I found the UI thread and a worker thread, both in the DB, both in the
> default busy handler, both taking a 1 second sleep.
> 
> I expected to see a third thread in the DB doing some work while the
> other two waited -- but no such thing.

The default busy handler (see sqliteDefaultBusyCallback in source) sleeps
for these amount of milliseconds:

  { 1, 2, 5, 10, 15, 20, 25, 25,  25,  50,  50, 100 };

However on non-Windows if you do not have HAVE_USLEEP defined then it
sleeps with a granularity of one second.  The blocking thread could have
finished after 10ms, but you'll still be stuck in the busy handlers for
another 990ms.

Simply ensure HAVE_USLEEP is defined when building sqlite3.c.  Or add your
own busy handler that sleeps for sub-second amounts of time.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.14 (GNU/Linux)

iEYEARECAAYFAlLO5gYACgkQmOOfHg372QQffACferRzozDtbmZqn+R/fVwxMKtf
rkYAn0oQDoHSne95rciPUlfqPCeN3yCv
=fAg3
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is that same between rebinding every parameter and

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 30/12/13 06:18, Igor Tandetnik wrote:
> sqlite3_clear_binding is very rarely needed, in my experience. In fact,
> I have not yet encountered a reason to use it.

I use it in my Python wrapper (APSW).  The reason is because I have an
automatic statement cache.  The bindings are cleared before being putting
the statement into the cache to free up any memory (they could have large
blobs or strings) and to make the state of the statement being taken out
of the cache match that of the same one freshly prepared exactly.

I am of the opinion that SQLite should have a statement cache built in
rather than everyone having to implement their own:

  https://www.sqlite.org/src/tktview?name=ee4b2b48f5

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.14 (GNU/Linux)

iEYEARECAAYFAlLB3aYACgkQmOOfHg372QSsFgCgwLkmzGqOrVL9YRLy6gG1E/cC
W+4An1ZFo5njgI0V3i6IAVMrubdGMdQv
=Linf
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does not detect invalid column name when subexpression optimized away

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 19/12/13 15:36, RSmith wrote:
> With this query you essentially ask the RDBMS to evaluate and supply
> you with the result of (X and 0) - my guess is the optimiser pounces
> directly on the fact that (X and 0) will always be 0 no matter what X
> is so that it does not bother trying to evaluate X which means it never
> has the need to resolve the name and so never needs to raise the
> error.

Confirmed:

sqlite> select nonsense or 0;
SQLError: no such column: nonsense
sqlite> select 1 or nonsense;
SQLError: no such column: nonsense
sqlite> select nonsense and 0;
0

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.14 (GNU/Linux)

iEYEARECAAYFAlKzo/8ACgkQmOOfHg372QTyVACfbNlMOQR5nxfzgUg7tY5pF8il
d0sAnjIp9K/aAIFXZcbc+pZvDdK6tN0x
=Siia
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Last Modified" file attribute is not updated

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/12/13 10:30, Felipe Farinon wrote:
> I'm sorry to repost, but I just want to confirm that there is no
> interest in fixing this, so that I can handle this with a workaround in
> my application.

Note that the effect will be to make SQLite slower since it would need
extra system calls at various points to do metadata updates.  The most
accurate would be to update the timestamp on every write!

Looking at the last modified date is also not the most effective way of
detecting things.  You can use the filesystem archive flag to detect
changes since you last looked at the file.

You can use a trigger internally at the SQL level to track changes in a
meaningful way too.

Finally you can disable the use of memory mapping - have a look at the
Windows VFS.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.14 (GNU/Linux)

iEYEARECAAYFAlKmLooACgkQmOOfHg372QQ5YQCfSSIJHA7cWQQ9X1IpHGkgQnr+
K3AAn0w1Wl1OIfNhR5n0zDCMHDlIqyqD
=GUnl
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concrete example of corruption

[Roger Binns]

On 01/12/13 06:10, L. Wood wrote:
> D. Richard Hipp, are there any plans to make this more robust in the 
> future, so reading/writing a corrupt database (with no -journal file 
> available) will return an *error* instead of causing further damage?

There has been a ticket languishing for many years to at least make
detecting a mismatch between the database and a (possibly non-existent)
journal possible:

  https://www.sqlite.org/src/tktview?name=61d35ac210

Roger
___
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

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/11/13 19:47, James K. Lowden wrote:
> You might guess from my email domain name that I take an interest in 
> posts like yours.  And it's pretty good first cut, no pun intended.
> ;-)

It is also worthwhile looking at musicbrainz

  http://musicbrainz.org/doc/MusicBrainz_Database/Schema

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlJ8ZWQACgkQmOOfHg372QQN/wCg0EyCpIs8pl9MVOzerfshJKxc
JwAAoN+E6Wa8T3zWZ+r5ghLpeK1a8dem
=23xE
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Android - Occasional Error when closing a database

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 29/10/13 06:23, Matthew Dumbleton wrote:
> I have noticed that occasionally, after the data is entered and the
> transaction ended, the close method call produces an error 'unable to
> close due to unfinalised statements' and logCat shows the SQLITE_BUSY
> error code.

On Android I use the SQLiteDatabase class, and the insert method it
provides and haven't encountered this issue.

https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html

How are you inserting the data?

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlJwIwIACgkQmOOfHg372QSJ3wCfcuDegEoTwBnU20M/2kTmCRPB
UvYAn0O1y+qMYtN7O11GUxwxi7D4Orak
=gol4
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Write-ahead logging issue on Android

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/10/13 18:37, Sascha Sertel wrote:
> I have since tried to find out what the correct way is to point SQLite
> to the right place for creating temporary files in Android, with no 
> luck.

https://developer.android.com/reference/android/content/Context.html#getDir(java.lang.String,
int)

Pass in "sqlite_tmp" as the string.

Every app also has a cache dir.  Note that Android can clear this at any
arbitrary point if storage is getting low so it isn't the most robust
locaton for temp files.

https://developer.android.com/reference/android/content/Context.html#getCacheDir()

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlJTb3YACgkQmOOfHg372QTbUwCguSVoQ7AcJrGCk7qwwhzFQOqQ
z5YAoIFNQxhOTeP8/0Aq2lEpOD0Lc+GT
=VUgP
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Namespacing sqlite3

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 24/09/13 16:37, Neville Dastur wrote:
> That's a very interesting concept.

I've been doing it since 2004, and support all versions of Python from 2.3
onwards including 3.x.

> Are you using http://www.cython.org/ or something else to "create" the
> library wrapper

Nope.  I write good old fashioned C code against the CPython API.
https://code.google.com/p/apsw/source/browse/src

This where sqlite3.c gets #included followed by the other files a few
lines later.

https://code.google.com/p/apsw/source/browse/src/apsw.c#46

Cython didn't exist when I started, but SWIG did.  However it required a
lot of scaffolding and wasn't worth it.  My resulting code performs better
than Python's somewhat standard sqlite3 module, and has far better
testing, as well as complete coverage of the SQLite API.

  http://apidoc.apsw.googlecode.com/hg/pysqlite.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlJCZOcACgkQmOOfHg372QRaYwCgqoR4C9DmXg27SCbOXg2a4GNe
vyUAnRYFf8Uej21hrJTkhwXJWddkvYWi
=bKyX
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Namespacing sqlite3

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 24/09/13 12:56, Neville Dastur wrote:
> Searching around on Google it seems that namespacing in c / obj-c is
> not possible for the sqlite3 library.

There is another approach that I use.  I produce a Python C extension.
The final shared library with my extension contains only one exported
symbol as expected by Python, with all my symbols and SQLite private inside.

What I do is make everything one compilation unit.  SQLite is already
setup for this.  For example in single.c:

   #define SQLITE_API static
   #define SQLITE_EXTERN static
   #include "sqlite3.c"

   #include my other C files

All my methods are declared static too.  This approach has the advantage
that it works really well and has no expectations or dependencies on the
rest of the system.  It even works if another copy of SQLite is loaded
into the process (eg CoreData on Mac likes to do that).  As another bonus
it is also a bit faster too as the compiler ends up inlining SQLite code
into your methods that call it.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlJCBoIACgkQmOOfHg372QRRsQCePzIDcnfYiXf3c/RHyqhnlsdz
pZcAoNrIRsMoScmoR3c10py9mynosLmm
=KTqr
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compare Similar Cells

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 22/09/13 19:41, Kristopher Roy wrote:
> I have a table of songs, several have similar titles I can't find where
> to get started. I tried this but its not right. Select SongTitle,
> COUNT(SongTitle) AS LIKE_COUNT FROM Songs

I did work with a database that came from a company that sold music.
Their source data came from the various record companies and was a
complete mess.  (Yes record companies would make mistakes even for their
own artists!)  For our purposes the data had to be denormalised,
deduplicated and many items merged where the differences weren't important.

Fixing up the data required probabilistic matching, and can't be done in
simple SQL queries.  For example spelling mistakes had to be accounted
for, truncations, case differences, punctuation differences, numeric
differences (eg "Song One" vs "Song 1", "Album 3" vs "Album III", "Vol 4"
vs "Volume 4."), mixes (eg "Song One" vs "Song One (Radio Edit)"),
compositions of multiple artists or contributing to another artists songs
so "artist" becomes murky, etc.

This could only be achieved by processing all the data in advance.
Essentially every artist had to be scored against every other to see if
they were the same (but not similar), same for every album of that artist
against their other albums, and finally of all the songs within each
album.  It required a lot of inspecting the matches, finding anomalies,
doing google searches to find canonical information, adding heuristics,
making sure that heuristics changes did not break existing good matches,
and endless repeats until things are good enough.

If you are trying to do a good job, then you will need to do something
like that.

If you are trying to fix up your own collection, then first go in and fix
all the meta data.  Musicbrainz is a good source for authoritative
information and there are plenty of apps out there to help you edit and
update tags.

If you want a quick fix, then add another column to your SQLite database
that contains the normalised song title.  You will need to iterate over
all your existing data to calculate a normalised title.  For example
convert to all upper case, remove all punctuation, convert multiple spaces
to single, remove "digits -" as you gave in your example, truncating to 30
characters, and whatever else is relevant for your data.  You can now do
matching against the normalised title column for each title.

Roger


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlI/3EAACgkQmOOfHg372QSuHgCgla77zTSx5knJL036AMpU0Unx
JnEAoJ9Cx/kocO3ue4xafKFkM7BVEviE
=RDic
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hints for the query planner

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 12/09/13 05:03, Gabor Grothendieck wrote:
> Perhaps indexing the expression in question would be an alternative 
> that would keep the performance info separate from the select.

I'd rather just do 'ANALYZE query' and have SQLite go off and do whatever
it deems necessary to gather to make the query perform well in the future.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlIyQagACgkQmOOfHg372QT8jgCgtSROjcL1dyrHo+yP2leh1ffV
xBEAoKEOTIVqz3vlrVrlVeJ130Wru/Mg
=+8TU
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help needed for major SQLite problem

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/09/13 08:58, C M wrote:
> If you think APSW would provide more details about just what went
> wrong other than "SQLite logic error or missing database" (which, I
> feel, doesn't tell me much at all), then I could potentially try it.

I'm in the process of adding error log support and should have it in a few
days for you to try.  You are welcome to email me directly or use the
python-sqlite mailing list until the Python side of this is nailed down.

"SQLite logic error or missing database" is the string associated with
SQLITE_ERROR code which is used in a lot of places.

SQLite does have extended codes that give more detail.  pysqlite doesn't
provide them but APSW does:

  http://apidoc.apsw.googlecode.com/hg/exceptions.html

> Is setting up APSW and making the transition from pysqlite2's wrapper 
> fairly straightforward, or would I have to re-do all the database
> queries? (Even so, I do them all in one utilities library, so perhaps
> it would be not too much work).

The actual querying etc is the same.  (pysqlite borrowed how some of it is
done from APSW :-)  Note that pysqlite parses your SQL and tries to second
guess what is going with transactions, and starts them behind your back.
That is possibly causing problems for you too.

Roger


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlIk8J8ACgkQmOOfHg372QTYCQCgi79D27GRMc6Tgjc8a+lI0Gt2
7IUAoOHGHlTKTJDmle82l5d98nWozmJ8
=PA/r
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help needed for major SQLite problem

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

He needs to give the exception traceback which will show what is happening
at the time.

A common mistake with newish Python programmers is to catch all
exceptions, and then keep going which also hides the exception tracebacks.

On 01/09/13 15:12, Richard Hipp wrote:
> Does Python have an interface to the error and warning log mechanism
> of SQLite?  (http://www.sqlite.org/errlog.html)  Can you turn that on?
> It will probably give more details about what it happening.

pysqlite is the standard Python sqlite3 module and doesn't expose that
functionality.  (It also hasn't had a new release in over two years.)  It
tries to make SQLite look like the standard Python DBAPI and maps the
SQLite errors into those fewer DBAPI exceptions.

APSW (disclosure: I am the author) does provide the SQLite errors in a 1:1
mapping to exceptions.  The error log could be used in theory, but is
impractical because it has to be setup before any other SQLite call.
There doesn't appear to be any reason why it can't be changed after the
library is initialised.

I'll see if I can add something more useful to the next APSW release.

Note that the APSW API is similar to pysqlite, but not the same.  It
behaves the SQLite way rather than the DBAPI way.

  http://apidoc.apsw.googlecode.com/hg/pysqlite.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlIj0vYACgkQmOOfHg372QSYrwCcCuJyMqsUiS5SYVFpeYll72d+
Z0oAoJSoWZNanNAxoBMUzjXHMfyEFmj1
=eyCs
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help needed for major SQLite problem

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/09/13 14:34, C M wrote:
> Do you know how I can do that with Python?  For example, I tried this:
> 
> status = cursor.execute("some SQL statement here") print "The status
> is: ", status
> 
> But it prints the cursor object:
> 
>> The status is 

cursor.execute returns an iterator of the results which is the cursor itself.

What you need to provide is the traceback which will show what calls were
active.

The exception is generally caused by opening a file that isn't a database,
but can be caused by other rare sequences of api calls.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlIj0AIACgkQmOOfHg372QTU5QCfeGcle8r3m8nDs3dDgI7zb6BK
qWQAoLWkeKarpubX4S1SzcBxaT5LT1yr
=sjZY
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Path Length Limit on Windows

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 22/08/13 07:38, Markus Schaber wrote:
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg18707.html
> seems to indicate that SQLite is not using those APIs. On the other
> hand, that post is of 2006.

There has been an open ticket about this for several years.

  http://www.sqlite.org/src/tktview?name=c060923a54

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlIXpJkACgkQmOOfHg372QQDFQCgn56WYKUg3oxbiZ0Kp886flWa
H+sAoLMWH6Q462K1Lb5pTs7WwlZyxlnf
=Uj9e
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locking without locking database file

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 14/08/13 06:06, Ralf Ramsauer wrote:
> Neither cifs.

I worked on a CIFS server (visionfs)[1].  They are a convoluted
complicated mess.  During the OLE2 era, Microsoft's apps abused locking as
a means of inter-process communication.  It got very complicated, with
Excel especially bad/adventurous in this respect.

CIFS locks are also completely different in virtually every way from Unix
ones.  You cannot map between them sensibly.  Both VisionFS and Samba
ended up having a "database" of the lock information and essentially
ignored what Unix said.

What this means is you cannot use CIFS from a Unix box and get correct
semantics because the Unix API does not expose anything close to cifs reality.

Windows machines can in theory do the right thing.  It has been a while,
but back in those days there would be calls over the wire that I couldn't
find any API to request.  For well over a decade the protocol has roughly
consisted of NT kernel data structures being sent over the wire.  The
server side gets adjusted to detect and work around bugs in the various
client versions.

Because of the asynchronous nature of networked file systems, race
conditions, and the possibilities of running out of disk space, most apps
have done updates by writing to a new file and only when everything is
successful overwriting the original.

Updating a (possibly shared) file in-place which is what SQLite does is
fraught with peril, which is why it turns out to be the least exercised
part of protocols and where bugs/quirks lie.

[1] http://www.rogerbinns.com/visionfs.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlIMB3wACgkQmOOfHg372QTyGgCgxB/7XxUibw+p0T/aI0cNecE4
ZegAoMDoh7RKb6hZmXsWTbreWhXpSYWP
=0UbL
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 error string size!!!

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 12/08/13 01:45, techi eth wrote:
> What is the maximum size error string returned by sqlite3_errmsg()
> function

You need to make a copy of the string since it can be changed at any time,
including by other threads.

In practise it will usually be less than 100 bytes, and sometimes a bit
more.  In theory it could be up to 2GB (sizeof int sqlite3_malloc
parameter). Some strings are constructed from input including database,
table, constraint, and column names, and parts of queries.  Extensions,
user defined functions, virtual tables etc can also generate arbitrary
messages.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlIIrLsACgkQmOOfHg372QS2pACeK5gt9bOdk9dX3gM+kxhYt6xA
MxQAnRLb4yMHdNEV6RpV0MbFUQVteNJ/
=gJrr
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow Query on large database Help

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/08/13 11:54, Christopher W. Steenwyk wrote:
> I have been working on a large database and its queries now for
> several weeks and just can't figure out why my query is so slow. I've
> attached the schema, my query, and the results of EXPLAIN QUERY from
> sqliteman.

Attachments get stripped from the mailing list.  You can put them
somewhere like Dropbox.

Did you run analyze?

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlICmtMACgkQmOOfHg372QSRLwCgkb0vJAQ/cnH+nr85W2PUJJrY
U0kAoOPseXJlXtSqJw95tNgq1RUMHp37
=AvMF
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Guidance with Python and nested cursors

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 17/07/13 18:37, Keith Medcalf wrote:
> cr1 = cn.cursor() cr2 = cn.cursor()
> 
> cr1.execute('select ...') while True: row = cr1.fetchone() if not row: 
> break

While that is normal DBAPI, it is far more verbose and unpythonic than the
SQLite wrappers (disclosure: I'm the author of one of them).  Code can use
iterators and look something like this:

  for id,one,two in db.cursor().execute("select id, one, two from XXX"):
   three=one+two
   db.cursor().execute("insert into  ", (three,))

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlHnV8sACgkQmOOfHg372QTfbwCeIYJTKpLSn+RGlsDcMA/S18WI
4QcAoOHgcf3mcRwfWwR2IrB87DbS0oQY
=gtkt
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] function auxiliary data

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 17/07/13 04:03, Riccardo Vianello wrote:
> I'm not sure I can do the same with pysqlite),

pysqlite forces a minimum statement cache size of 5.

> but since sqlite3_reset doesn't clear the binding

It can't in general since you can immediately rerun the statement with the
same bindings.  However that isn't exposed in APSW so you can edit the
code to clear the bindings after each reset call.

It doesn't do that at the moment as a form of being lazy - only clearing
bindings when the statement is re-executed.

Roger


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlHnCn8ACgkQmOOfHg372QT2HwCeL02RdUT1/6rpKLIxuQ4HAehg
4asAnj29dAUc4ZHqVvY1UWZPa15VWOd9
=IO2u
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] function auxiliary data

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

[I'm the APSW author]

On 17/07/13 00:59, Riccardo Vianello wrote:
> In a test performed using APSW I disabled the statements cache and the 
> code behaved as expected also with parameters binding. This brought me 
> to think that the metadata associated to the parameters in a compiled 
> statement might be preserved until the statement is finalized, but I 
> will investigate things more in detail.

APSW does sqlite3_reset the statement in many circumstances, including
hitting the end of the results and on errors.

sqlite3_finalize is only called when the statement is being destroyed
which includes eviction from the statement cache, and on getting
SQLITE_SCHEMA error.

I do wish SQLite had a standard statement cache available.  Pretty much
all wrappers implement their own which is duplicate work and tricky to get
right.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlHmaBcACgkQmOOfHg372QS7nwCfYVAcvSQSD0/aNmY/OGwdG6KI
ZOQAmgLRuJaHxR+BJHSjtK9VU79yFlPR
=w0yh
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UnQLite

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/07/13 20:51, Nico Williams wrote:
> QBE is nice and simple, but lacks expressive power.

The Mongo query syntax with an example JSON object:

{ "a": 10, "b": {"c": 3}, "d": ["one", "two", "three"]}

To find all a==10:

  {"a" : 10 }

To find all a>=10:

  {"a": {"$gte": 10}}

To find c==3:

  { "b.c": 3}

To find where element two of d is "three":

  { "d.2": "three" }

To find where "a" is present but "b" is not:

  {"a": {"$exists": true}, "b": {"$exists": false} }

The various $operators allow expressing more deep and meaningful things:

  http://docs.mongodb.org/manual/reference/operator/

They are also used in updates.  eg to add 3 to "a" and 4 to "b.c"

  {"$inc": {"a": 3, "b.c": 4}}

The thing you can't do is reference objects (JOIN) in a different
collection (TABLE in SQL semantics).  SQL syntax is good at this. However
there is less of a need due to denormalized data, and other approaches
like map/reduce.  It is also fairly normal to store multiple "types" in
the same collection (eg songs, artists and albums) which would require
different tables in SQL.  I suspect multi-collection references could be
squeezed into the JSON like syntax but would be ugly.

It will be interesting to see who wins the query syntax mindshare.  There
does seem to be a desire to stay with SQL like syntax - for example
Cassandra also uses it.  Another thing I like about the Mongo syntax is
that it is trivial to build up queries programmatically.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlHaQ6gACgkQmOOfHg372QTnWwCgl3ktXQQYguEVCo1aBn9shcE9
/+8AniZ+X+3ndxPxuaXLO6CdZLRLfC0s
=qIII
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UnQLite

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/07/13 16:19, Simon Slavin wrote:
> 

What has generally become important to me is being able to supply
arbitrary JSON as a "record", be able to query it, and get the same
arbitrary JSON back out.

In addition to unqlite, some of the other "nosql" databases have the same
approach (eg MongoDB).  Even postgres is adding JSON storage via hstore.

What hasn't standardised is how you query which means you can't easily
change engines or write (mostly) portable code like you can with SQL.

MongoDB uses JSON shaped structures with $prefixed operators.  unqlite is
using a custom Jx9 interpreted language.  And postgres uses minor
extensions to SQL.  There was unql which DRH was involved in two years
ago, but appears unadopted.

My favourite is the MongoDB approach since the queries and data are
substantially similar - it is very similar to Query By Example.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlHaGJwACgkQmOOfHg372QTXaQCgkiy49SE5bHyvRr8gn+Oecgve
kSMAoLcq1ZXRg4Z5KXdU3HsfT9CkyqhE
=J/3c
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG REPORT: function instr() with chinese characters

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 06/07/13 01:20, ?? wrote:
> I am using the sqlite-shell-win32-x86-3071700, in WindowsXP SP3

I did the same queries on Linux and got very different answers that appear
to be correct.  I'd suggest using SQLite programmatically since the
Windows console isn't unicode.

SQLite version 3.7.15.2 (APSW 3.7.14.1-r1)
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table a (b int, c int);
sqlite> insert into a values(11,22);
sqlite> select instr('', '') as x from a;
1
sqlite> select instr('', '') as x from a;
3
sqlite> select instr('', '') as x from a;
5
sqlite> select instr('', '') as x from a;
7

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlHYfTQACgkQmOOfHg372QTYygCfXW3MQtF9tHCnG9/PIHTRwBjt
a8sAnRo2/4DkqjfDfanykDwk0GG9tcIw
=dEpl
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DLL Size differences and other info requested

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/07/13 16:38, Stephen Chrzanowski wrote:
> I just reduced my built file size to BELOW what comes "out of the box"
> from sqlite.org.  I've turned off all optimizations and ran a full
> build.  I'm now sitting at the 599k mark.

You probably want -Os

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

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlHUyqQACgkQmOOfHg372QTkQACgkKeNo1Qk5G4sgdkLJZPKV/Ai
0nkAn34ULSWYCsNk6I5WHvDViQOeTRTJ
=OMxe
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Another 2 questions about SQLite

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 30/06/13 20:17, Igor Korot wrote:
> Well I'm not familiar with SQLite internals, but one thing for sure: 
> why go thru the process if you can avoid it?

You are trying to do premature optimization.

Note the "Lite" in the SQLite.  It is already designed and implemented to
do the least amount of work - you haven't found a way to do less because
SQLite is already doing the least!

It only calculates the next (or first) result row when you ask for it, and
not before.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlHRALsACgkQmOOfHg372QSjJgCgv0zNhFZRdOaR4gzaq4b6Qw9B
6HQAoMZcfyORD9++LT0iTJCT0Crs+pSn
=xURz
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Large Database Windows vs Linux

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 28/06/13 13:17, RSmith wrote:
> Best guess is some other system is trying to also look into that file, 
> making the Windows file manager stutter quite possibly the Win7
> Preview pane, a 3rd party file indexer service, an anti-virus system or
> some other

Those are often called tag alongs since they tag along file i/o activity.

Also beware that if your database (or any other file) as a particular
extension then Windows' System Restore will keep making backup copies
whenever it changes.  Here is the list of monitored extensions:


http://msdn.microsoft.com/en-us/library/windows/desktop/aa378870(v=vs.85).aspx

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlHN+ugACgkQmOOfHg372QTTqgCeN3hNpHGON/CaoEx95y9605Qz
GMIAnAtmuJzgo3wwLrZdGOIbA2yWwuRP
=5SeP
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there available sample "featured" SQLite database file?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 28/06/13 01:12, Sqlite Dog wrote:
> I'm wondering is there available sample "feature-tight"
> "limit-striking" SQLite database (or SQL script)  for GUI manager
> testing purposes?

You can certainly measure coverage of sqlite3.c itself when running your
testing.  That will tell you what parts of functionality you haven't
exercised.

As far as I can tell the SQLite test suite does small pieces at a time
rather than a single huge file that does everything at once.  However you
may be able to capture all the pieces and join them together.

My own test suite (several times larger than all my other code combined)
also only does small pieces and concatenating them wouldn't work since
names etc are reused.

> - identifier names contains spaces,

Here is your starter schema (and perfectly valid):

  create table ""("" "");

If your code runs on a platform that is UTF-16 (or worse UCS-16) make sure
you test with code points above 65535.

For performance testing this could be helpful with the --dump-sql arg:

  https://code.google.com/p/apsw/source/browse/tools/speedtest.py

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlHNTSsACgkQmOOfHg372QQa5gCfQzrw5m8fFpuT9u29nTTkymZm
jhUAnRqEuC8opRWsjsni2TYJa2xLZaTh
=3+mD
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Should I do analyze?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 25/06/13 22:46, Navaneeth.K.N wrote:
> I am wondering should I do ANALYZE also as the last command? Will that 
> make the query planner happy and choose faster plans? Will that improve
> the runtime performance?

Since you know what your queries and data are, why don't you benchmark it
and find out?

In general ANALYZE should help the query planner make better informed
decisions, but those decisions can never be perfect.  It is possible but
not probable that some could turn out worse.  The SQLite authors try to
make SQLite very predictable so the query solutions chosen tend that way.
 An upcoming release will have a new query planner that should work better
when there are a lot of joins.

  http://www.sqlite.org/draft/queryplanner-ng.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlHKlmUACgkQmOOfHg372QQhNwCeIth6bQJMfZHFVM/FijyoY79N
82AAn1NVyCVQj7CDPt+8grjaT41ODDxM
=X88q
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] First Day of Week Inconsistency

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 19/06/13 09:20, Denis Burke wrote:
> Is it possible to modify %W to treat Sunday as the first day of the
> week?

Traditionally strftime %W uses Monday to start each week while %U uses
Sunday.  It looks like SQLite doesn't implement the latter.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlHB29kACgkQmOOfHg372QTxwQCfc3qGW5PtGljBm/rvgEOGVtA/
DzwAoI6cIiZ+SzMmX7NE2gcwEfDR0w2W
=y1z6
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Upgrade/Install sqlite3 on Ubuntu server

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 16/06/13 17:55, Simon Slavin wrote:
>> Here’s a nice Python wrapper:
>> 
>> 
> 
> Woops.  That’s Windows, you’re Ubuntu.  Don’t you have the sqlite3
> Python module

APSW is not Windows.  It is provided as source for all platforms, and also
binaries for Windows because Windows users typically do not have C
compilers available (and even if they do they have to match Python).

The recommended APSW build instructions are below.
(The Windows binaries were built that way too).

  http://apidoc.apsw.googlecode.com/hg/build.html#recommended

In particular note that it downloads and statically includes the SQLite
amalgamation completely ignoring any system SQLite.

pysqlite's setup.py script does have an option to do that, but I doubt it
works any more.  The pysqlite source has not had any changes in two years.

Also if you want to use SQLite and not pretend that it is the same as
other databases then this will be of interest:

  http://apidoc.apsw.googlecode.com/hg/pysqlite.html

(Disclosure: I am the author of APSW)

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlG+pO8ACgkQmOOfHg372QQLZQCgxhbB/EHUBdAZ3vVX78a4u3eY
9XkAoK3de4S0zjad3978iDdO7zRfrXCv
=eFbl
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Prepared statement invariants

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 14/06/13 10:31, Maxim Khitrov wrote:
> I'm writing SQLite bindings for Go [1].

I'm the author of SQLite bindings for Python - APSW.  One choice I made
was to mimic SQLite semantics into Python - essentially if it is a
function call/expensive operation in SQLite then that is also the case in
Python too.  I don't try to get anything in advance or for convenience, or
misrepresent SQLite semantics.

For example getting the result layout requires an explicit call:

  http://apidoc.apsw.googlecode.com/hg/cursor.html#apsw.Cursor.getdescription

What I do is expose "Connection" objects which correspond to a sqlite3*,
and "Cursor" objects which correspond to sqlite3_stmt*.  The whole
statement preparation thing is not exposed.  Behind the scenes there is a
cache of sqlite3_stmt* objects which are claimed and released as
necessary.  I believe that SQLite itself should provide the statement
caching because it has a notable performance benefit and is hard to get
exactly right.  Individual application developers will have even more trouble.

Query results are obtained via iteration on the Cursor as you can see in
the above doc link.  (I couldn't work out if Go supports an iteration
protocol in same way, especially getting the next member only as needed.)
 Here is example code for most of the functionality:

  http://apidoc.apsw.googlecode.com/hg/example.html

Another thing to look out for is that developers often want to trace the
SQL being executed, as well as how long it took, longest/popular queries
etc.  The dynamic nature of Python made it easy to create a supervisor
that can automatically do the tracing and reporting without code
modification, and with no cost when it isn't used:

  http://apidoc.apsw.googlecode.com/hg/execution.html#apsw-trace

There is a sqlite3_trace function but I don't use it because it expands
bindings which means you can't lookup the queries in your code.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlG8B1EACgkQmOOfHg372QTgbACgwJgzpFhszsB/1orPJ2lIVksS
gqsAoNnkjOjFSmTz1C9/3egmLs+ztqSs
=x87s
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit of attached databases

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/06/13 08:14, Eleytherios Stamatogiannakis wrote:
> We have tried with both views and VTs but SQLite does not create
> automatic indexes on them at all. So right now, to be able to have
> automatic indexes from SQLite's side we materialize all Virtual Tables
> into plain tables:

There is no reason that the virtual table implementation can't create
automatic indexes.  In response to the xBestIndex calls, the indices don't
actually have to exist.  You just need to ensure the estimated cost is
relatively useful.  If SQLite then decides to use the index you can then
create it on the fly.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlGuQQMACgkQmOOfHg372QRxxwCfV1QvfO6Fsky0x3krTLe08+Nm
EN4AniM6kkOHgcTm/mtREY3iD4QAF9o+
=KOXn
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Import skip first line

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/06/13 07:26, Bart Smissaert wrote:
> Is there a way to use the other features, but not this one, say if the
> table is already there?

The autoimport code is one function so you get all of it, or none of it.
The same is true of the import code which is a separate function, but it
mimics the official SQLite shell exactly.

If you are happy writing Python code then you can trivially add/change the
commands in the APSW shell, as well as adding new output formats, so you
make it do exactly what you want.

This is the autoimport code, the vast majority of which is detecting
column formats:

 https://code.google.com/p/apsw/source/browse/tools/shell.py#1641

You can scroll up to see the import code.

I just did a timing test of autoimport on a 53MB file with 111 columns and
185k rows.  It took 1m20s - two thirds of which was the detection phase.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlGqUEUACgkQmOOfHg372QRVXACdG1ZDSUzrL2TGMfaE1cQuSUgN
6vMAmQHwoWKQ+/zhAuZUNwM2U2Om4SAB
=P6JF
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Import skip first line

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 31/05/13 05:34, Bart Smissaert wrote:
> Importing a. csv file via the. import command of sqlite3.exe. As the
> first line holds the field names I want to skip that. There is no
> problem achieving this in code, but could I do this purely via sqlite3
> commands?

You can also use the enhanced shell in APSW (Python wrapper).  You don't
need to use or write any Python and can just execute it for the import.

Of particular use is that it has a .autoimport command which automatically
sets up the correct column names.  It also deduces the type in each column
so for example phone numbers won't be mangled to integers, and dates will
be "fixed" automatically determining if they are US or correct format.  It
also automatically works out separators (eg csv, tabs, pipes).

http://apidoc.apsw.googlecode.com/hg/shell.html

sqlite> .help autoimport

.autoimport FILENAME ?TABLE?  Imports filename creating a table and
  automatically working out separators
  and data types (alternative to .import
  command)

The import command requires that you precisely pre-setup the table
and schema, and set the data separators (eg commas or tabs).  In
many cases this information can be automatically deduced from the
file contents which is what this command does.  There must be at
least two columns and two rows.

If the table is not specified then the basename of the file will be
used.

Additionally the type of the contents of each column is also deduced
- - for example if it is a number or date.  Empty values are turned
into nulls.  Dates are normalized into -MM-DD format and
DateTime are normalized into ISO8601 format to allow easy sorting
and searching.  4 digit years must be used to detect dates.  US
(swapped day and month) versus rest of the world is also detected
providing there is at least one value that resolves the ambiguity.

Care is taken to ensure that columns looking like numbers are only
treated as numbers if they do not have unnecessary leading zeroes or
plus signs.  This is to avoid treating phone numbers and similar
number like strings as integers.

This command can take quite some time on large files as they are
effectively imported twice.  The first time is to determine the
format and the types for each column while the second pass actually
imports the data.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlGpIoEACgkQmOOfHg372QRSqwCg3WTRwifRKC+tK55BmTbomVyo
PHsAnR8v79nKkpVZ7WYWydeTYxsHbZoE
=c95H
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How people test db failures

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/05/13 22:31, Igor Korot wrote:
> Or the simplest one - the database is moved ...

That is sufficient to break the database.  A SQLite database is not just
the database file, it is also the journal and WAL.  If you don't move all
as a single unit then you break the database.  Often you'll get lucky ...

The only safe way to move a database is to use the backup api to make a
new copy with the new name.  If you do it outside of SQLite then great
care has to be taken, and errors won't necessarily be immediately apparent
or even show up under pragma check.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlGDT0QACgkQmOOfHg372QSkKwCgysgQWSsCWgSN1zMS0p9uOOWb
TL8An1MuVaYjysden5anmCnKk2G3+b5w
=8HDa
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How people test db failures

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/05/13 20:03, Igor Korot wrote:
> How do people prove to their customers that no matter what the
> software will either finish gracefully or will not break, crash or
> anything to that matter?

That is easy - use SQLite.  Put your work inside transactions.  You can
use savepoints to nest transactions.  A transaction either fully
completes, or has no effect.  Even if your app is forcefully killed this
will still hold.  (The database will be cleaned up the next time it is
opened removing information about the transaction that was under way.)

On the other hand your code interacting with SQLite could have bugs.  At a
simple level you could ignore the error code returned by functions and
assume they succeeded.

The only way to test that is to add in hooks to cause various pieces to
fail as needed, and use coverage analysis to help ensure you have tested a
reasonable amount of code.  An example of how thorough you can be is the
SQLite testing:

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

All that aside, there is still no protection from a hostile environment.
An overclocked/overheated cpu can make arithmetic errors.  Cosmic rays can
change bits in memory.  Filesystems without checksums can have bit flips
on the storage or in transmission.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlGDTf0ACgkQmOOfHg372QSuhgCfZaiQPow1ioBbZnez569/oVPt
3s0AoLdwcTPuEB8apk5Dv1VNpLS2bMXC
=VYvk
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How people test db failures

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/05/13 12:00, Igor Korot wrote:
> So, how people check whether db failure result in graceful program 
> termination/proper flow?

I use a macro that takes this form:

#define TESTPOINT(name, normal, failure)

I use it like this:

   TESTPOINT(StepNoMem, rc=sqlite3_step(stmt), rc=SQLITE_NOMEM);

The macro expands to something like this with debug builds:

  do {
 if(_point_should_fail(#name) { failure; }
 else { normal; }
  } while(0)

The _point_should_fail function returns true once and then false from then
on.  My test suite would then look like:

  def test2c():
 set_point_should_fail("StepNoMem")
 ... code that should handle the failure correctly ...

Of course reality is a little messier than this.  Here is some code that
sets a failure point:

  https://code.google.com/p/apsw/source/browse/src/apsw.c#217

Here is the corresponding test:

  https://code.google.com/p/apsw/source/browse/tests.py#7562

(I use 1/0 to ensure that there will always be an exception, and if it
turns out to be ZeroDivision then I know the previous line didn't cause
one and should have.)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlGBp+YACgkQmOOfHg372QQsrACgiDu/z2nn+NZdf/Q/Ep4JBrFu
U24An3J26qO54dT89vSVKo/Js60O7pwc
=mc1D
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Writing in a blob

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 22/04/13 04:39, Paolo Bolzoni wrote:
> But I noticed that sqlite3_blob_write cannot increase the size of the
> pointed open blob. So I ask, there is a way to treat a blob as a stream
> so I can write or read values in it with ease?

This is discussed in:

  http://www.sqlite.org/c3ref/blob_open.html

It mentions using zeroblob() so you can create the blob of the length you
want and then use blob_open/read/write/close to modify the contents.

Roger


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlF1gC0ACgkQmOOfHg372QRJRACcCBv5J2DS6Eor3R49CDw2bgEm
kIAAn0jyOaxhaAiFD1IemwFF84/kaR41
=jxCT
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.7.16.2

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 13/04/13 14:55, jose isaias cabrera wrote:
> Can this problem also from a DB file shared via Windows Shared folder

An effective way to corrupt your data is to use a networked filesystem.
They do not provide the exact required semantics as a local filesystem
provides.  Most of the time you'll get away with it, but every now and
then you'll experience data corruption.

Then like so many before you'll post to this mailing list, where you'll be
pointed at the following two links :-)

  http://www.sqlite.org/faq.html#q5
  http://www.sqlite.org/howtocorrupt.html  (section 2.1)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlFp78AACgkQmOOfHg372QRcWQCfcPTuzBjSGNUeYR6xwQSFyvDe
G8kAnim8px5ZTDow9KcbqebHBu3Nod/2
=llAx
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite in Google Summer of Code 2013

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 15/02/13 04:56, Dann Luciano wrote:
> ... or json results in sqlite.

Just formatting results as JSON is uninteresting and trivial.  It is a one
liner in Python for example.

I use MongoDB for the vast majority of my database work these days.  The
reason is because I can put arbitrary JSON objects in and get the same
arbitrary JSON objects out.  Queries have the same "shape" as the JSON
objects (unlike the defunct unql which tries to use SQL).

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlFos4AACgkQmOOfHg372QTmqwCfQOxLMP7JwM5WRk48mfCbwURa
bYwAoNfktyB7r+fGXosQ7ZKMoJfaKGIA
=CTqA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database modeling question

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/04/13 13:42, Alem Biscan wrote:
> For a job interview I am required among other things to create a phone
> book application.

It would be easy to spend a year on that!  You are probably better off
showing something short, simple and understandable first.  "Smart and gets
things done" is what many look for.

Then show how deep down the rabbit hole it could go depending on how
comprehensive a solution is needed.  Let's start with this amusing nonsense:

> Name   Surname

You fail pretty much all 40 of the issues listed here:

 http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/

I did a phone sync application a while back (BitPim) and here are some
relevant issues:

- - users have more than one computer/device/place they run the app

- - they can't all talk to each other at the same time

- - contradictory changes can be made

- - phone numbers need labels (eg "home", "work", custom values)

- - there can be more than one of each label

- - numbers need to be prioritised (eg home1, then work1 for one user,
cell1, home1 for another)

- - numbers can have punctuation, spacing and other symbols and they matter

- - normalizing numbers is hard and best done in the UI (eg converting 011
or 00 to +, 1 prefix in the US)

- - the same principles apply to most other fields (eg addresses, names)

- - you need to be able to recover any record and its values for any
(reasonable) point in the past.  This provides undo and removes the need
for explicit load/save commands

- - if you have to interoperate with anything else then they will have
mangled the data in unbelievable ways

- - if you have to interoperate with anything else then they will use
different and inconsistent labels, tags, field names etc

- - if you have to interoperate with anything else then you will need to use
a probabilistic matcher to link records between your system and theirs

- - if you have to interoperate with anything else then you'll want to store
some of their mangled data and ids to make future matching easier

- - almost every system like this ends up "intelligently" duplicating all
the user records at some point.  Think about prevention and how the user
recovers (undo from earlier is a great help)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlFl2XgACgkQmOOfHg372QRe1gCfaHfvyOp2o3mEZHZ1A+C26Eq6
K68AoJElmUjyyber54x4c0WNHsl/yFSm
=AHms
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Trivial doc issue for functions

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

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

The hex line is out of alphabetical order and should be two lines earlier.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlFXwh4ACgkQmOOfHg372QQ9IACePrhGGxVjQdDynxIJhP8ciSoS
Ox8AnjH+DSNVcNr+GD2JeEi/JaPOqyGo
=KLq4
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Timeline for full ALTER TABLE support?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 26/03/13 14:40, Tim Gustafson wrote:
> Is there a timeline or road map anywhere that could inform us as to
> when we might see that feature added?

In addition to what others have said, if SQLite did implement full ALTER
TABLE support it is unlikely to be implemented any different than what is
already in the FAQ. Note that there are pragmas to temporarily disable
various things like foreign keys and constraint checks.

  http://www.sqlite.org/faq.html#q11

The storage format for SQLite has each row written out as the values for
each column sequentially.  If you want to delete a column or re-order them
then every row has to be rewritten.

Appending a column was implemented by requiring a column default for the
new column, and if the row read had too few columns then obtain the
missing ones from the defaults.  This didn't require a rewrite of every row.

You can achieve what you want via various means, most recommended first:

- - update your code so that it only needs to append columns

- - change the underlying tables as in FAQ q11 above

- - create a view that presents the schema you want composed of the
underlying tables, with triggers for insert to redirect columns as appropriate

- - convince/pay someone to make a patch that implements what you need and
maintain your fork forwards

- - convince/pay the SQLite team to add it to the core, keeping in mind that
they care about library size and the 'Lite' bit

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlFTj5sACgkQmOOfHg372QQmEACfUehTgbHqtIHVvhcFOY36bUKn
g88AoNrlsPZX2vGDp6hqlanmNDM8yNFo
=iUmI
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimizer suboptimal planning with virtual tables

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 15/03/13 05:25, Clemens Ladisch wrote:
> If you had no xBestIndex implementation, SQLite would crash.

He is using APSW (a Python wrapper).  It provides a default implementation
if the developer does not.  Here is the documentation:

  http://apidoc.apsw.googlecode.com/hg/vtable.html#apsw.VTTable.BestIndex

There was some confusion over estimated cost.  APSW leaves it at the
SQLite supplied default which is 1e99 divided by 2.  This generally
encourages to SQLite to use anything else (eg real indices) unless the
developer using APSW knows better.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlFGzgAACgkQmOOfHg372QSzoQCfWxH+ACpo3FbeYFli7DEZVGpO
G+IAn2twe1Lva3sVE05jedQi/7VL3lpk
=CFQH
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.7.16 beta

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 12/03/13 17:28, Richard Hipp wrote:
> The next release is currently scheduled for Monday, 2013-03-18.
> 
> Please let us know as soon as possible if you see or suspect any issues
> in the current beta.  Thanks.

I can provide the opposite data.  My test suite extensively covers the
SQLite C API.  Everything looks good and I don't even remember the last
release there was a problem.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlE/2oUACgkQmOOfHg372QS3CwCfY/P+4IMyy6OCL3wrjkge3Z6k
MBUAoLtSOTTNjURgOAGH6HA77BDlHnH0
=/Evg
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Announcement of Copy - A cross platform syncing app, that uses SQLite as its backend

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/03/13 12:27, Jason Dictos wrote:
> Overall SQLite has been the perfect solution to our situation.

Thanks for the details.  What programming language do you use?  (Dropbox
uses Python.)

> Early on we figured out quite quickly not leverage the read/write
> locking models of SQLite

Do you use WAL mode?

I'm also curious roughly what you do with your schema.  It looks like the
Dropbox on Android schema has elements of implementing a tree with
everything in one big table and no triggers.

Do you use any of SQLite's extensibilty such as adding your own collations
or user defined functions.

> As it stands now, our customers periodically have random I/O errors
> ...

As you have large deployments, random stuff does happen.  We have a web
service with several requests per second from browsers all over the world
and sadly have to use HTTP (cough *IE* cough) rather than the SSL we
normally use.  Even though TCP/IP is checksummed there are sporadic
corruptions that come through (typically bit flips here and there).

8 years ago there were lots of CPU random errors:

  http://blogs.msdn.com/b/oldnewthing/archive/2005/04/12/407562.aspx

Do you run an integrity check at startup on the database?  I did so with
BitPim, but we didn't have analytics so there was no idea how often
corruption happened.

There has been an open feature request for a while to have data checksums
to deal with the case that what SQLite thinks it wrote is not what is
later returned:

  http://www.sqlite.org/src/tktview?name=72b01a982a

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlExZhYACgkQmOOfHg372QTLBgCgsbidy6oQfmAeS4OWq4OBSmFI
zxEAn04lneghgvr+ww76AQWzycZ3x+Q0
=eya6
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Announcement of Copy - A cross platform syncing app, that uses SQLite as its backend

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/03/13 09:56, Jason Dictos wrote:
> This app we wrote couldn't have happened without SQLite,

I would be interested in hearing about the experience with SQLite,
especially anything relevant to other developers.

One of your competitors is Dropbox who also use SQLite (I have no
connection to them other than as a customer).  On desktop computers you
can find the SQLite databases in a .dropbox subdirectory of your home
directory (or equivalent).  They were encrypted a while back because of
various issues.

The Dropbox Android client also uses SQLite with a non-encrypted database.
 It has several tables related to camera images, with the core schema
being these two tables:


CREATE TABLE dropbox (_id INTEGER PRIMARY KEY AUTOINCREMENT, _data TEXT,
modified TEXT, bytes INTEGER, revision TEXT, hash TEXT, icon TEXT, is_dir
INTEGER, path TEXT , canon_path TEXT, root TEXT, size TEXT, mime_type
TEXT, thumb_exists INTEGER, parent_path TEXT, canon_parent_path TEXT,
_display_name TEXT COLLATE NOCASE, is_favorite INTEGER, local_modified
INTEGER, local_bytes INTEGER, local_revision TEXT, local_hash TEXT,
accessed INTEGER, encoding TEXT, sync_status INTEGER, _natsort_name TEXT
COLLATE NOCASE);

CREATE TABLE pending_uploads (_id INTEGER PRIMARY KEY AUTOINCREMENT, class
TEXT, data TEXT);

dropbox._data is the path to a local copy of the file, not the file contents.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlEw8/IACgkQmOOfHg372QSl3gCgnJzdP1aqglyaUkL2Dp2+nPIk
7j8AoK7sorrrzzh0z+jrxzmGjWqjx4AL
=zgYl
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .dump - what isn't preserved?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/03/13 04:24, Dave McKee wrote:
> What else isn't losslessly maintained in a dump -> load cycle?

Various database settings and any strings with nuls in them.  Also the
sqlite shell expects to be loaded into a clean database.  For example it
doesn't delete existing tables of the same name, and it unilaterally
deletes the contents of sqlite_sequence.  These are mostly convenience
issues rather than data loss, but attention should be paid at restore time.

I made a dump of the same database using the SQLite shell and using the
APSW shell (I'm the author).  The only actual data loss is SQLite shell
ignoring strings after the first nul.

  https://gist.github.com/rogerbinns/5064697

You'll note the APSW shell puts a lot of extra information in there that
would be relevant if you were doing a restore.

Something else the SQLite shell does is deal with corrupt databases.  The
last time I looked at the code, if it encountered an error iterating over
a table, then it would try again iterating backwards.  I don't remember
what it did with respect to errors if only part of the data could be
iterated over.

TLDR: good question and there are a *lot* of issues under the surface
depending on how you intend to do the restore and what things matter to
your code

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlEwsFoACgkQmOOfHg372QSsTQCffwvaQFk1c/XAhZHJpPxCHflE
2PUAn1Rp0MVHtsUbNjbkEdYEBwf0omFc
=j1SZ
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Thread sync issue

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 24/02/13 00:14, Roger Binns wrote:
> On 23/02/13 21:23, Ashok Pitambar wrote:
>> Sqlite doc says it thread safe ...
> 
> http://www.sqlite.org/threadsafe.html
> 
> What makes you think the doc is wrong?

One big gotcha: errors - if you use one connection per thread then you are
fine.  If you use the same connection across threads then read on:

The one thing not mentioned is that retrieving SQLite errors is not
threadsafe.  The operating system does errors as thread specific (eg
errno, GetLastError).  SQLite does errors as connection wide (I really
wish this was fixed).

That means getting integer error codes is a race condition as a different
thread can change it before your thread has a chance to retrieve it.  If
you intend to retrieve the error string then your program *could crash*
since the pointer you get could end up with invalid memory access when you
treat it as a string.

Consequently the *only* safe way to make API calls where you intend to
look at the error code or string is to add yet another level of locking.
You can use sqlite3_db_mutex to get the mutex for a connection, and
sqlite3_mutex_enter/leave to keep it locked while you extract codes and
messages.  For example to safely call a single function (sqlite3_blobopen
in this example but it applies to every sqlite3 call that returns an error):

  int rc=SQLITE_OK;
  char *errmsg=NULL;
  sqlite3_mutex_enter(sqlite_db_mutex(db));
  rc=sqlite3_blobopen(.);
  if(rc!=SQLITE_OK) {
 rc=sqlite3_extended_errcode(db);
 errmsg=strdup(sqlite3_errmsg(db);
  }
  sqlite3_mutex_leave(sqlite_db_mutex(db));
  // now you can safely use rc and errmsg

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlEpztEACgkQmOOfHg372QTfLgCfVzKDib6ExfEhUO7StM5XYWT1
YSAAnRUWckgS95dF+7Rvq1RtU+DS8U7X
=LB1g
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Thread sync issue

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 23/02/13 21:23, Ashok Pitambar wrote:
> Sqlite doc says it thread safe ...

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

What makes you think the doc is wrong?

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlEpy+UACgkQmOOfHg372QRd+wCfVf2pjzJU6ewY+WyvUmWK4APE
OOsAoNOGXeEhO5QvxIfh5nTSnGpWxUF4
=Abjr
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do not read File change counter

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 21/02/13 06:51, Joost Voogt wrote:
> ... it would be nice, to minimize the amount of reads and gain
> performance, ...

Even if that happened, how much performance would you expect to be gained?
 If the bytes are frequently read they will end up in the operating system
cache and so perform at the speed of memory (no seeks, no disk).

  http://en.wikipedia.org/wiki/Amdahl's_law

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlEmTxAACgkQmOOfHg372QSq/QCgu6iT0Y+XBpLyczhXwpWlcHiC
2/AAoNFPPKDXi1KIpVk2fJW1Q0dqdkco
=30iy
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] import TXT file

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 21/02/13 02:34, Gert Van Assche wrote:
> Does anyone see something obvious I'm doing wrong?

There is no need to bash the SQLite shell into what you are trying to do.
 You can write your own code to read your file and insert each row into
the database.

Another option is to convert the data file into SQL commands.  Place
"BEGIN;" at the beginning of the file, and "END;" at the end of the file,
and then precede each line with "INSERT INTO Source VALUES('", double up
any single quotes in the line, and append "';".  (That is essentially what
the .import command is doing behind the scenes, but it tries a lot harder
to find separators, count/enforce number of columns, deal with end of line
etc.)

Roger


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlEl/9kACgkQmOOfHg372QSx2gCg49DGhrLuSIelDYMn0RAPe+GH
EHkAoJgzYZmyVEUGUcBNbWDxq90s8a5q
=koQD
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "default value of column [name] is not constant." error in table creation when using double quoted string literal in parenthesis

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/02/13 14:27, Simon Slavin wrote:
> Any chance of killing it in SQLite 4 ?

Continuing to beat a truly dead horse, it would be nice to help developers
fix their existing codebase via something like an additional "lint mode".

  http://www.sqlite.org/src/info/25e09aa2ab

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlEYS6EACgkQmOOfHg372QR+kgCePgJMHJ55coumXdEIsIOlOhJr
Ns8An1bmLOFeJ/5vYMBa8nQJSw31MmLC
=czW1
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] using the same sqlite parameter more than once causes premature memory deallocation

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/02/13 06:01, abbood wrote:
> i tried using the memory diagnostics tools in Xcode/Instruments --
> Zombies, GuardMalloc, and Malloc Stack Logging.. but they didn't tell
> me much.. i'm assuming valgrind is better?

They are fairly lightweight and only catch the more simple errors.
valgrind is perfect - it runs your application in such a way that every
single access (read or write) of any byte is tracked as well as the status
of every byte of memory.  This makes things run considerably slower, but
the resulting notifications are top notch.

> i took a sneak peak at valgrind.. and it looked really complicated.. is
> it?

Not in my opinion, and no matter what this is the only tool that will help
you so you'll just have to figure it out.  You just run your app prefixed
with valgrind.  eg if you did this:

  $ myapp arg1 arg2

Then you do this:

  $ valgrind myapp arg1 arg2

It is recommended you compile your app with debugging. Chances are highly
likely that you will then be shown your issue.  It is possible for some
issues to be missed.  One example is that after memory is freed it is kept
in a pool for a while, and then released for reuse making it valid again.
 I have a massive pool so memory is not reused with the option (5GB pool):

  --freelist-vol=500

I also care about memory leaks which valgrind will show on exit.  These
options cause it to show a lot of detail:

  --leak-check=full --leak-resolution=high --show-reachable=yes

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlEWueoACgkQmOOfHg372QQtaQCeNfCzwMp2UzNslMjoI538tjBf
0SEAoNEfzvG70jqtWeY7T2LcVfjcFYAM
=jmof
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 Python interface

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/02/13 10:00, James Vanns wrote:
> I can find no further Python DBI/sqlite3 API documentation to suggest
> either way. I tried it and it fails I guess that's it :(

A flag needs to be set in the open call which pysqlite doesn't do.  Heck
the most recent source release is from May 2011.  You may be able to edit
the source to add the flag in the sqlite3_open call and recompile.

> Has anyone got the URL format working with Python (regardless of
> version)?

Another alternative is to use APSW which has url opens by default
(disclosure I am the author).  The recommended build instructions embed
the SQLite amalgamation inside the extension so it doesn't affect anything
else.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlET+T8ACgkQmOOfHg372QQZwgCcCd/FBzxRcDl+QZrTayM8G073
SfwAnAqIKE3yavofxDxZrBu17aSSlLno
=evS/
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Load a Sqlite Database on Disk as an In-memory Database

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/02/13 23:33, Alex Cheng wrote:
> I want to write a program to load a sqlite database on disk as an
> in-memory database, for sake of reducing number of disk I/O operations.
> Do you know how to achieve it?

You may not need to actually do that.  Since your database is smaller than
memory it should be sufficient to just read the file contents sequentially
before opening in SQLite.  This will populate the operating system file
system cache so disk operations are served out of cache memory.

(Doesn't work on XP with default settings but does work on every other
modern operating system.)

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlEQxrAACgkQmOOfHg372QTlLgCeLI2dZP/v6Pw4VlgcgRGUKqHB
xcwAnjM1RAHNzGgvi9nnGnP4uoYG6vqV
=DCOk
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Determine if an index has been created

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/02/13 02:12, Paul Sanderson wrote:
> I will know the name of the index - I just need to check that it has
> been created.

Just use pragma index_info on the index name.  If it doesn't exist then
you get no rows returned and if it does exist then you a row per column in
the index.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlELmyUACgkQmOOfHg372QSkCwCgvIzw59DcPMytdRssRs37YPRB
IUMAoKmyDMkKhdubaMqdmp/H9WLrkYAW
=WCNM
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 Bug Report: The shell accepts some dot commands ending in semicolons while rejecting others.

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 28/01/13 21:11, Larry Brasfield wrote:
> If anybody truly cares enough to make this behave better, it is
> encapsulated in a function named "booleanValue(char *zArg)".  For
> myself, since it takes "0" and "1", which are easy to type, the present
> behavior is entirely unobjectionable.
> 
> As for whether an appended ';' (or any other junk not called for in
> the .help output) ought to produce a diagnostic instead of simply
> following the above logic, I would say that adherence to the GIGO
> principle is perfectly adequate.

Note the change the SQLite team have made:

  http://www.sqlite.org/src/info/b4d94947fc

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlEHZEMACgkQmOOfHg372QTUTgCeOLdm5gpZQDdE6WIWZHZMzJ1M
YwIAn3AuE9RW4STFDzSU9zw0loph+nxR
=l3sT
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] identify virtual tables?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 25/01/13 12:57, Petite Abeille wrote:
> and   lower( sql ) like '% virtual %'

It is worth pointing out that SQLite normalises the create table text for
a table in the sqlite_master table.  ie it always begins "CREATE VIRTUAL
TABLE" (uppercased) no matter how the table was created.  By contrast the
columns are left exactly as they were, whitepspace, comments and all.

DDL:

  create /* foo */ vIrTualtable foo using fts3(content /* ab */ TEXT);

Corresponding sqlite_master:

  CREATE VIRTUAL TABLE foo using fts3(content /* ab */ TEXT)

Of course this is an implementation detail and not specified anywhere, but
it is good enough.  Your like query has the problem that it will match
rows where virtual appears anywhere.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlEECw8ACgkQmOOfHg372QRNTwCgrYP5vGFvPHlvnauTv1u6e1Mu
xeIAn1t5fJLIvFna1Ndf+Jf42AHud0ev
=pwfQ
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] using the same sqlite parameter more than once causes premature memory deallocation

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 25/01/13 23:45, abbood wrote:
> i fixed it!! you were right! it's not to do with the guts of sql..
> rather it's to do with my incorrect sql statement..

Huh?  There is no SQL statement, valid or not, that can cause memory
errors.  It looks like SQLite is the victim of some other memory
mismanagement in your app and changing the SQL has just changed what code
will fall victim to it.

It is a very good idea to run valgrind before proceeding.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlEEA6AACgkQmOOfHg372QSswACgoIg1jidTzar4EVVfQmFZlDwb
ZuAAn1iIuUz84T4Gpzgv2Q58U1zYq9MR
=z+X5
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Substring Search Across Many Columns

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 25/01/13 12:59, Paul Vercellotti wrote:
> As I understand, it's tricky to get FTS to do substring matching, no?
> What's the best way to do that?

In what way is it tricky?  There are several examples of doing it in the
doc I pointed to.  Even when it does a full scan the list of all words
should be shorter than visiting each source row.

I recommend you actually go ahead and use FTS before deciding it doesn't
work.  You'll be able to get accurate performance information for your
data set.

  http://c2.com/cgi/wiki?PrematureOptimization

If you want to do substring matching using an index then you need to use
n-grams.  This involves taking fragments from the text.  For example if
your source text is "hi there" and you are doing n-grams between 2 and 4
letters then you would index these:

  'hi' 'hi ' 'hi t' 'i ' 'i t' 'i th' ' t' ' th' ' the' 'th'
  'the' 'ther' 'he' 'her' 'here' 'er' 'ere' 're'

You can possibly also use a FTS tokenizer that produces n-grams.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlEDFdwACgkQmOOfHg372QTShgCfXMmtiWFbWL9INRMF4TfTUTGb
5+IAn2LrTYKTm9mLcJ6mR6piRQ8LT6nw
=taL+
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Substring Search Across Many Columns

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 25/01/13 11:30, Paul Vercellotti wrote:
> I'm trying to match a substring (case-insensitive) across multiple
> columns and want it to go fast; it's very slow to do a query like '...
> WHERE name LIKE "%fish%" OR desc LIKE "%red%" OR title LIKE
> "%soup%"...'
> 
> Will creating a composite index ...

If you are concerned about performance then SQLite already has a solution
for you:

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

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlEC5IEACgkQmOOfHg372QSNawCfcstLdcz+03qyerOuCRD1apxg
OsIAoKmLokdeHugQARtxRmD3bxVwBu99
=MyPO
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] using the same sqlite parameter more than once causes premature memory deallocation

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 24/01/13 17:37, abbood wrote:
> Btw I'm curious how did you find out about this auto release thing? Can
> you add more detail about that?

I used a debugger to set a breakpoint in malloc_error_debug as the message
says.  That gave a stack trace of an autorelease pool being drained.

Since most frees happen in the autorelease pool drain it doesn't
particularly help with telling you which item is the problem.  I asked on
the valgrind list and got a way of keeping the allocation stack trace
instead of the free one.  (The next valgrind release will allow you to
print both.)

  http://article.gmane.org/gmane.comp.debugging.valgrind/12755

The cause of that original issue in my code was NSInvocation and dealing
with returned objects.  The cause of the most recent issue was because
NSData was owning a buffer passed to it that I didn't want it to.

> Ie I put a break point in malloc_error_break.. But then I just jump to
> the internals of sqlite.. And basically within the salite internals
> it's freeing an operation that doesn't exist..

Using valgrind will narrow down the problem.  What you are seeing is the
consequence of earlier memory errors.

> But im not sure if it's a good idea to modify the guts of sqlite.. Is
> it?

SQLite is *extremely* unlikely to have a bug.  Some other piece of code
has the bug, and SQLite is the victim.  Remember that virtually every web
browser on virtually every platform is using SQLite - an error would show
up for someone.

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

> And then I made the second part use in line variables as opposed to
> bindings.. Ie NSString stringwithformat..

Do remember sqlite3_mprintf for that sort of thing, especially if strings
are involved to avoid sql injection attacks/bugs:

  http://www.sqlite.org/c3ref/mprintf.html

> So I deleted that row from the dbase and it works fine.. I couldn't
> find anything different on that row..

Memory allocators typically have buckets for different sized allocations
often in powers of two.  It could be that row had a string needing 33
bytes while others needed less which then caused memory to come out of a
different bucket which then changes where the victim of the actual bug
shows up.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlEC2o8ACgkQmOOfHg372QQQgACg2u97/wfBys3ryf/EZphv0R43
hjUAoLh2/anUjqGWa+GxC+7GO5tt3D0L
=X0kB
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] using the same sqlite parameter more than once causes premature memory deallocation

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 24/01/13 00:24, abbood wrote:
> then it crashes with the following error: malloc: *** error for object 
> 0x9b6350: pointer being freed was not allocated *** set a breakpoint
> in malloc_error_break to debug

Coincidentally enough I am debugging this exact same issue right now.  Are
you sure the error has anything to do with your binds?  In my case I did
as it said and have found that ARC has added something to the autorelease
pool.  This has nothing directly to do with SQLite and everything to do
with the boundary between Objective C/ARC and C.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlEBu6sACgkQmOOfHg372QSR5gCfU+FAhhp8QuzAB6q1sP8xq2T4
j3wAoMUx3i4dFzzIldgyGJghNtXmsJR6
=+wFc
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE starvation.

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 16/01/13 07:59, Shuki Sasson wrote:
> When we run the test we see that two threads are trying hundreds of
> time to get through the begin unsuccessfully.

Look at the code for the default busy handler (sqliteDefaultBusyCallback).
 It keeps retrying with increasing backoff.  If you do not like these
intervals then write your own busy handler that uses a better algorithm
for your use case.

These are the default intervals used in milliseconds (providing you have
sleep granularity better than seconds which is the case on Windows and
with usleep).

   { 1, 2, 5, 10, 15, 20, 25, 25,  25,  50,  50, 100 }

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlD4erkACgkQmOOfHg372QQWPwCfZlMikT1DnuiflZDa58pIdnD9
vwYAoKRI+Z5UQWl6NP6rm8K1eyrRxYht
=vuw2
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite version of android 4.0.3

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/01/13 16:23, sqlitekyounoii wrote:
> I want to know sqlite version of android 4.0.3

My 4.0 device is 4.0.4 but I doubt they changed it.  The version number is
3.7.4 and the source id is 2011-02-23 14:33:31
8609a15dfad23a7c5311b52617d5c4818c0b8d1e.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlDuIA0ACgkQmOOfHg372QS9yACfZFFwvjkqUhcDB0zXRiRzv8NK
LTsAn2nTH/sVr8BAHHogRixfXUfeQkVQ
=+1Ku
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using sqlite in multi fork() without threads

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/01/13 00:22, Eduardo Morras wrote:
> opened before the fork, and after it the childs use the database
connection. I don't want to corrupt the database,

https://sqlite.org/faq.html#q6

You can't use the database across forks.  Parent and child would both
consider themselves owners of open databases and silently corrupt each
other's work and interfere with each other's locks.  You will corrupt the
database. You need to close all databases and then do the fork, and then
open databases in the children.

In APSW I provide a fork checker.  This is done by providing an
alternative mutex implementation that records the process id a mutex was
created in and then checking the mutex is only ever used in the same
process id.  (Each database connection has its own mutex.)

The code that does this:

  https://code.google.com/p/apsw/source/browse/src/apsw.c#704

In my benchmark tests I measured a slowdown of 1%.  ie if your code did
nothing but SQLite calls then you can expect it to be about 1% slower.

I strongly recommend you do something like this to ensure that no
developer accidentally has databases used across forks.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlDq0REACgkQmOOfHg372QQC6QCfX7wa7eIq4okyJRT74LN8X4xI
xlgAoOSwC5dDzyvfb4S30nivW/rLEYIk
=tlN5
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 1.1GB database - 7.8 million records

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 31/12/12 12:33, Michael Black wrote:
> Journaling off might work if you don't have any indexes.

You should create the indexes after inserting the data.  If you don't you
end up with index data and row data intermingled in the database which
makes things slower.

Journaling off definitely worked for me when I benchmarked it.  I was
working with a 15GB dump from postgres on Linux.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlDiABEACgkQmOOfHg372QQesQCgjTJbDb3Yt2iyC/7vUEJAFuTq
1T0AoIPDu/fpdtOoEEnmkNn4vr/lGTpe
=bgfW
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 1.1GB database - 7.8 million records

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 31/12/12 10:35, Michael Black wrote:
> One transaction like you did is best.
> 
> I recently ran a test which ran pretty well with a commit every 1M
> records. Doing every 100,000 records slowed things down dramatically.

If you are creating the initial database then you can turn journalling etc
off until the database, indices etc are fully created.  This will get you
a little more speed too.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlDh7mkACgkQmOOfHg372QR/dwCfVhcMaYJIr6pTFKsL1LbaFiVJ
xk8An3lyoOv/LLmi9lWh8ZFEFJdCGfZO
=ie9C
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to verify referential integrity of SQLite database

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 12/12/12 12:36, Richard Hipp wrote:
> Does anybody know of any reason why we should not do this?

I would love if this was combined with an optional per page checksum that
detects corruption early.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlDI7+UACgkQmOOfHg372QR8fACbBFIKWxbuWTz51Tt1k8Hwnykq
lkQAnA7gD2x5AZOAZ6w7KOJPo49i37Fc
=RRCX
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] table backup

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/12/12 03:46, dd wrote:
> But in customers place, I am getting corruptions.

Chances are that whatever corrupts your databases would also corrupt the
backups.  Here is something the Windows team found:

  http://blogs.msdn.com/b/oldnewthing/archive/2005/04/12/407562.aspx

> But, critical data is there with corrupted database. How can I recover
> it without backup.

You can't without a backup unless the corruption happened in duplicate
information (eg an index).

> Any sample application for virtual table option.

You'll probably find that quite hard.  A simpler alternative is to
temporarily attach another database and copy the table into it.

  attach 'backup.db' as backup;
  drop table if exists backup.important;
  create table backup.important as select * from important;
  detach backup;

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlDHdLIACgkQmOOfHg372QTEjgCdGcoBgu9u5d55NuSoIsl57WWr
pqYAoMWjFW0UhM0i8tsRCqRznV8Irh5u
=03Xw
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unql

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/12/12 19:01, dcharno wrote:
> What ever happened to Unql and is there any chance it will be revived?
> It seemed like it would have been incredible useful.

Warning: My opinion only

Unql seemed to be especially pointless.  Being somewhat compatible with
SQL syntax made no sense - SQL syntax and all the tools using it assume
the relational model and simple table like data layouts.  NoSQL databases
vary, but generally they are non-relational and schemaless, and JSON
interoperable ones have a structure completely different than SQL tables
(eg item nesting).

Every NoSQL database already has its own query mechanism so Unql would
always be a second class citizen if any of them ever adopted Unql.

NoSQL databases also tend to have looser consistency, have multiple
servers available and various other semantics very different than SQL
servers.  The native NoSQL interfaces expose and work with those semantics.

Cassandra did actually end up with something SQL inspired:

  http://cassandra.apache.org/doc/cql/CQL.html

Riak uses something like Google advanced searches (field:value):

  http://docs.basho.com/riak/latest/cookbooks/Riak-Search---Querying/

MongoDB uses JSON with extra magic operators:

  http://docs.mongodb.org/manual/applications/read/

Personally I like MongoDB approach where queries strongly resemble the
underlying stored data which means very little translation between the two.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlDGqEAACgkQmOOfHg372QQ8OACgxbOBcp1F5rADh9Uw5+0efsEe
5RQAn3sim96zcz6x2lKMXF+B7Sp20P1A
=OHFc
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] table backup

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/12/12 21:44, dd wrote:
> Sometimes, sqlite databse corrupts.

That is the problem you need to fix.  If you have a system that is
unreliable then it will also corrupt your backups.

http://www.sqlite.org/lockingv3.html#how_to_corrupt
http://www.sqlite.org/howtocorrupt.html

> So, I want to take online backup of specific table. Not entire
> database.

Do you need to take a backup on every change, as part of the change or is
it acceptable to make backups periodically and possibly lose intermediate
versions of the data?

For a periodic backup you can iterate over the table contents and output
them in a convenient format for you, such as CSV or SQL statements.

For saving all data you can use triggers to save historical values in a
second table and then do a periodic backup.

If it must be immediate then the only choice available is to use a virtual
table and do the backup during writes/sync.

This is all considerably more work than figuring out why you are getting
corruption in the first place.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlDGF2IACgkQmOOfHg372QTo9gCfSQQwreSvsa9lrV/wj0YC2Fvj
LT0AmwdZSaNvVJJuic3gLYmQfn9YX6x3
=Gx1r
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] table backup

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/12/12 10:35, dd wrote:
> i mean, for every write operation in a table.

You should specify some of your other constraints and exactly what it is
you are trying to achieve (*not* how you are trying to achieve it).

The solutions range from implementing undo by using triggers to save old
values, through using a virtual table that backs up data somewhere else.

The SQLite backup API only works on the whole database.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlDE5poACgkQmOOfHg372QQI8gCeP6GfezJUWQ7M3pdfvvgc2FCp
J1QAoMSM/tbpxlicrrDpTbgjHAsWln1d
=mPMA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug: Segmentation fault in libsqlite3.so.0.8.6[b69a4000+ac000]

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/12/12 14:43, Paul Menzel wrote:
> The following code caused the segmentation fault.

By far the most likely cause is some other library or the app itself
stomping on SQLite's memory.  SQLite is on every Android device, every iOS
device, virtually every browser (including Firefox and Chrome), used as a
component in a lot of programs etc.  All of that provides an immense
amount of real world testing.  Then add in the testing SQLite itself
undergoes which is *far* in advance of most other software short of some
safety critical systems.

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

All of the above is why it requires strong evidence in order to point the
finger at SQLite.

If you do want to trace this further then I recommend you try the valgrind
program.  However it does require some understanding of how memory is used
and managed in a C based process.  Also if glib/evolution/libcamel play
silly games with memory (eg using their own allocators and pools) then it
will completely obscure what is going on from valgrind.

You'll most likely get better insight in a group devoted to evolution.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlC+j8YACgkQmOOfHg372QTexgCgldd0k9006XRiaetrEOcidSJ1
UMgAnjXoiTHUDa/oTxvXTkoPCg37DUmQ
=uLKA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] list: Please allow signed messages.

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/12/12 13:47, Paul Menzel wrote:
> please allow signed messages to be sent to the list.
> 
> I got: »The message's content type was not explicitly allowed«

This works.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with undefined - http://www.enigmail.net/

iEYEARECAAYFAlC8AKEACgkQmOOfHg372QSeGwCgw0OLLPEpiU4MzXi8LsVxWWhP
Gh8AoJph2+hEbXtoDFc4xbgmaAHkbsXB
=Fngf
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Deprecation of asyncvfs

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

The documentation also needs to say asyncvfs is out of favour, in
particular on this page:

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

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlC7pawACgkQmOOfHg372QR6xwCdFbYD1GUte+wz1E4sjGH+u9Lg
p8cAoLtTPyG059v8n2tHyjdFvdYsC4Xi
=boYS
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 30/11/12 13:31, David de Regt wrote:
> Only possible agent is MSE,

MSE is the best behaved.  Norton and similar are especially bad.

> ... and process monitor doesn't show it eating IO

Sadly that rules out easy fixes :-)

> I tried changing block size to the native block size and it only sped
> up by ~5%.

Although that helps, I was talking about alignment.  This can happen at a
minor level - eg a small block size could be 4kb but the partition starts
at 63kb.  That means each filesystem block maps onto parts of two
different SSD/HDD blocks.  For SSDs there are also major block sizes
(erase block) which typically tended to be 128kb.  Again a misalignment
could cause a lot of extra work to be done.

Depending on how Windows got partitioned - the older the partitioning the
more likely this is to happen.  It won't shouldn't using a current Windows
7/8 on a fresh machine today.

Run msinfo32 and then Components > Storage > Disks to find the relevant
partition and its starting offset/alignment.

(This is unlikely to be your problem, but if present does result in the
kind of performance degradation you are seeing.)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlC5LIYACgkQmOOfHg372QQTsgCg1kUbwbwBnJOcenMHnFULGZe5
PqcAn30XBAT6extxig8Md7MI6XEtoHbi
=xYNE
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 30/11/12 09:41, David de Regt wrote:
> Is there something ridiculous about the windows file system performance
> that hoses sqlite's open/read/write/close transaction cycle?

There are multiple possible confounding factors.  One is that you could
have tag alongs running - virus scanners, backup agents, content indexers
etc.  I strongly recommend running Sysinternal's Process Monitor which
will show file activity and point fingers.

Another is that Windows monitors files with certain extensions as part of
system restore.  If you happened to pick one of the extensions for your
database you'll find it a lot slower:

  http://msdn.microsoft.com/en-us/library/aa378870.aspx

There are some other tradeoffs in the NTFS implementation, such as how all
directory information is stored in a single large "file" (MFT).

It is also possible that the blocks of the filesystem don't align with the
blocks of the SSD which will cause the SSD performance to be a lot slower.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlC5IaoACgkQmOOfHg372QQjRgCdG4HCUkm9K/fRqIESJDfusiKG
WGQAni80PNqPHynWWYZxil1QRZmUEdZE
=nGIY
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "natrual" join (not "natural")

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/11/12 18:25, Mark Brand wrote:
> You mentioned that this extra AS might help avoid ambiguities and
> errors. The only example of this you mentioned was where aliases names
> with spaces are not quoted. Do you have cases of this in mind that
> wouldn't be syntax errors anyway? In other words, I'm looking for
> evidence that "missing AS" really is in the same category of "trailing
> semicolons, etc".

Table originally has a column named "price".  For various reasons they add
a new column named "price new".  Since you can't use bindings in queries
they will either have been written out by hand or composed (eg sprintf).
If the latter code doesn't quote the names then the query includes "price
new" which selects the wrong column and "overwrites" the value returned
for "new".  There is a probability of it being detected which depends on
other names in the query/tables and what the consuming code uses.  But a
lint that warns about an implicit AS would have a 100% chance of catching
this problem, if is a relevant problem for that code base.

BTW I also put trailing slashes on directory names where possible - again
it is unnecessary, but again it makes the intentions crystal clear, and in
some cases does catch errors.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlCTJCIACgkQmOOfHg372QQGygCfYzALXoJmUX+xcNJRcXzf1bw/
tKMAnjugQ1YzQyBMqSK6Pt1TUHMHZ/Q3
=P2IF
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "natrual" join (not "natural")

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/11/12 15:46, Mark Brand wrote:
> A warning for "missing" AS would create noise and serve no purpose.

Then ignore/don't use it for your perfect code!

Other developers do use their own code (that could have been written years
ago), other code and other libraries.  Sometimes they have bugs like for
example not quoting names that have spaces in them.  A missing AS warning
would catch that.

> The misspelling of NATURAL would be caught by a warning for JOIN
> without ON constraint.

True.  And other possible errors could be caught by missing AS.

> In my opinion, it's actually a good idea to leave out the AS in table 
> aliases since SQL-92 and many or most implementations do not require
> the AS, and at least one prominent implementation does not allow it.

You can leave out trailing semicolons in Javascript statements, or not put
squiggly brackets around one statement blocks in C, or not close many HTML
tags, or depend on accidental ordering in SQL etc.

You can do whatever you want for your code.  But many people appreciate
tools that proactively help find problems.  It is often very common to put
in "unnecessary" extra syntax to make it very clear what the intentions
are (eg extra parentheses even though precedence doesn't require them or
things in paragraph above).  Using AS to make your intentions very clear
is up to you and others (I always do it).

Currently there aren't any practical tools for auditing your running code
when using SQLite - something would be very helpful.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlCTEJQACgkQmOOfHg372QRZsQCeLUi3VS892qXYa2WXM5KFyydv
hfUAoI+sknCBiRVVJv/ESuQS9ALwjMAV
=qnqA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "natrual" join (not "natural")

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/11/12 14:32, Mark Brand wrote:
> My point is that the proposed lint mode should worry more about 2) than
> 1).

I didn't think they are mutually exclusive.  The class of probable errors
detected overlaps.

> ... as Oracle at least doesn't allow it.

All lint mode stuff would be warnings not errors, so as a developer you
can choose which ones to ignore, black/whitelist or pay attention to and
cause a fault in your test suite.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlCS8dAACgkQmOOfHg372QStgwCgwIUMjZ0XMHqy1GtMSyN+YC8e
LwkAn30MqqJYyGI8QOYzO3XNOA9sY/oQ
=aBAi
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "natrual" join (not "natural")

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/11/12 12:33, Mark Brand wrote:
> The optionality of "AS" when aliasing column names is not strange.

You are missing the point :-)  I am not arguing for syntax changes or
anything else in the core of SQLite or SQL.

Instead in an additional "lint" mode, it should point out when you
neglected to use AS.  The purpose of lint mode is to reduce the
probability of bugs or unintended consequences in your code.  Omitting AS
is one way to increase bug probability as this thread shows.  (Another
example would be if query strings are generated and column names
containing spaces are not quoted.)

There are an ever expanding list of things a lint mode would help with as
you gave.  I'm just hoping the SQLite team can figure out a way of
providing such a mode given constraints, while still keeping SQLite "lite".

Roger


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlCS2LoACgkQmOOfHg372QSOQgCfUaBHQrRpHpM6PF/BnCUrn5+G
oeQAn1+bcF+A/D/hdKz7iLVsVVJca5MO
=fP/G
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "natrual" join (not "natural")

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 31/10/12 07:27, Jonas Malaco Filho wrote:
> Maybe there could be a strict switch.

There is a ticket for a "lint" mode that would help catch common issues
where there are possibly problems.  This is another example of something
appropriate for it to catch (always an explicit AS).  Earlier message and
link:

   http://goo.gl/2ycor


Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlCR/vsACgkQmOOfHg372QRGvwCfZ9HV8bIMQEbfF1jxQuH07TRt
onkAn1dHV+cNUOZd3D0kPb9ODhxuvkUw
=bN2b
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Decimal point and localisation

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 27/10/12 12:43, Steinar Midtskogen wrote:
> But why can't sqlite observe LC_NUMERIC for the output - no ambiguity 
> would then arise?

Because the SQLite library does not know where the output is going.  If
you ask for a number to be provided as a string there is no way it can
even guess.

If you are referring to the shell then it has a similar issue.  Is the
output going to be read by a program (where being deterministic is good)
or by a human (where local conventions matter more)?

The rule of thumb is very easy - the people directly using SQLite are
developers.  Those developers should then take whatever formatting and
similar actions are necessary for their end users as appropriate.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlCMTd0ACgkQmOOfHg372QQqxQCfTA1Tw+TRYoeh9aLkzjnPTvcU
5GkAmwU8DagckuXxsrkRY6CthYR/FTFJ
=zZ1u
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database Memory Stream

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/10/12 05:47, Chris Sonon wrote:
> I would like to house my database in a ZIP file and extract the
> database to a memory stream and then open it directly from the memory
> stream? Is this possible? If so, how?

You can get a zipvfs from the SQLite team:

  http://www.hwaci.com/sw/sqlite/zipvfs.html

You can do it yourself with the C api - I don't know if the C# wrapper is
fully equivalent.  It is also most likely the the zipvfs from the SQLite
team will be cheaper unless your time is worth nothing.

If you want to do it yourself, there are cheap hacks like extracting to a
temporary file and opening that.  You should also be able to use the
backup API to copy a temporary file to memory.  If temporary files are not
acceptable then you can write a VFS that exposes the zip file.  Again the
backup API can be used to copy the database, or you could just use your
zipvfs directly.

The VFS operates at the level of the database file - for example there are
methods for reading and writing blocks of the files.  SQLite also has a
virtual table mechanism which operates at a higher level - rows and
columns.  You can use SQL while supplying the underlying data to operate
on.  This approach may be more applicable if the data in the zip file is a
higher level.

And lastly you can store SQL in the zip file and just run it against a
memory database.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlBvw1QACgkQmOOfHg372QThPgCfSkQoIF2I9jgQ74hXcs5TasGM
ILYAn0BAEDaiF6PGg49VeXaHf/L7Jfwo
=79RU
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problems in VFS file read

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 30/09/12 19:16, Roger Binns wrote:
> Looking through the code for both VFS I don't see any attempt to check
> the amount parameter is positive, and in both cases it silently gets
> passed to a routine expecting unsigned (size_t or DWORD).

http://www.sqlite.org/src/info/daebe3bd2d9bd7b6f876a8110cf5045eb3fee078

Can I suggest just returning IOError if the value is outside of reasonable
bounds?  Silently truncating it as that patch does still leaves the
possibility of buffer overflow - for example a 1kb buffer could be passed
in with a negative 1kb length and would result in a buffer overflow.

As to how a negative number could end being passed in, someone could have
their own VFS that calls through to the underlying SQLite provided ones.
(That is considerably less work than doing your own entire VFS.)  Or some
combination of values could unintentionally cause it such as code trying
to return a negative number as an error code but it being taken as is (an
example is sectorsize where again signed and unsigned are mixed).

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlBp5BgACgkQmOOfHg372QSNBQCeIOQ0lYodqgbHmOWgd3R96ZTc
eqkAoMxiHoRBbnlZgFjMvUeAK3dIg1OU
=rlUn
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problems in VFS file read

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

One of the parameters to read is the amount of data to read.  Since SQLite
avoids standard types like size_t, a signed int is used instead.  I
observed a difference in behaviour between the Unix VFS and the Windows
VFS - the former returns an error while the latter doesn't.

Looking through the code for both VFS I don't see any attempt to check the
amount parameter is positive, and in both cases it silently gets passed to
a routine expecting unsigned (size_t or DWORD).

Obviously under normal circumstances a negative number would not be passed
in, but I don't think we can be certain that there are absolutely no bugs
that could lead to it happening.  The consequences could be very serious,
ranging from a simple buffer overflow to an exploit.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlBo/OsACgkQmOOfHg372QRPnwCfZlQB1+gzJ5opIRdSWIdMUVFN
wbgAnRewU/Wdw3d4eF9C5YfocrfnZ997
=WoMo
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of WAL file and cache

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 26/09/12 01:15, Sebastian Krysmanski wrote:
> 1. I understand that the WAL file is stored on the disk.

During a transaction the old pages and the new pages will both be present
at some point no matter what mode is used.  Consequently you should expect
maximum usage to be around the size of your existing data plus twice the
size of the changes you are undertaking while usage outside of a
transaction will be roughly existing data plus changes.

Worst case would be something like changing a value on every single page.
 Best case would be adding new data with no indices.

> It says in the documentation: 1000 pages. How big is a page?

1kb default.

  http://www.sqlite.org/pragma.html#pragma_page_size

> Also, does WAL has any (significant) memory consumption (per
> connection/thead, or dependent on the WAL file size)?

There is some but not really significant.

> 2. I'm guessing a SQLite cache (connection private 
> [SQLITE_OPEN_PRIVATECACHE] or shared [SQLITE_OPEN_SHAREDCACHE]) is
> stored in memory only (i.e. not on the disk), right? What's its size in
> memory? Or, on what factors does this size depend on?

SharedCache is about sharing the cache memory between connections in the
same process.  It is unlikely you'll ever want to do this, or even benefit
from it if you did use it.  The default cache size is 2MB from sqlite.org
- - I haven't checked to see what value Android changed it to.

Many of the system apps, and a lot of others apps are using SQLite behind
the scenes.  You are not the first developer by a long shot.  Remember
that the first Android phones allocated 16MB per process for everything.

I recommend you just go ahead and use SQLite as the Android system intends
you to.  Actually I recommend you expose things as a ContentProvider and
use SQLite behind the scenes.

It is trivial to use SQLite on Android.  Note that the library code does
some things behind your back such as dealing with collation registrations
to make sorting consistent.  (If you ever .dump a database that was
created on Android you can see that.)

Start with this class that helps manage the schema on your database, as
well as creating the database in the right place:


http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html

Roger


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlBjbWgACgkQmOOfHg372QQUmACfTE4NyVslAFHDkNfiwPvgBP6w
uO0AoJ/YFlZ1a2y0ne3Lk0GCJy781WqG
=48fo
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] dynamic bulk import

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 22/09/12 13:41, Keith Medcalf wrote:
> Take a look at the apsw (Another Python SQLite Wrapper) shell which has
> an .autoimport command that may do what you want.

Thanks for pointing out APSW (I'm the author).  It is only necessary to
use the APSW shell to do the autoimport - after that you can use the
database anywhere. Here is the detailed help information for the
autoimport command:

=== 8< ===

The import command requires that you precisely pre-setup the table and
schema, and set the data separators (eg commas or tabs).  In many cases
this information can be automatically deduced from the file contents
which is what this command does.  There must be at least two columns and
two rows.

If the table is not specified then the basename of the file will be
used.

Additionally the type of the contents of each column is also deduced -
for example if it is a number or date.  Empty values are turned into
nulls.  Dates are normalized into -MM-DD format and DateTime are
normalized into ISO8601 format to allow easy sorting and searching.  4
digit years must be used to detect dates.  US (swapped day and month)
versus rest of the world is also detected providing there is at least
one value that resolves the ambiguity.

Care is taken to ensure that columns looking like numbers are only
treated as numbers if they do not have unnecessary leading zeroes or
plus signs.  This is to avoid treating phone numbers and similar number
like strings as integers.

This command can take quite some time on large files as they are
effectively imported twice.  The first time is to determine the format
and the types for each column while the second pass actually imports the
data.

=== 8< ===

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlBeMjkACgkQmOOfHg372QQ7hwCeJgqwYFzG7ZK1fA+a/FWJVGo0
IiMAnRzavbeacm+aAkYvDfZHQiNY+NjD
=Xybr
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual tables are driving me insane!

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 21/09/12 14:44, Jörgen Hägglund wrote:
> At this point I get an access violation in SQLite3.dll (reading of
> address 0008)

You are reading the third member of a structure that is NULL.

You should use a debugger that catches this sort of thing and lets you
examine what is going on.  It is also a good idea to use memory debugger
tools.  (The Linux tools are very good at both of those, Windows is
considerably less productive.)

Virtual tables can be very simple to write once you have the glue done
correctly.  For example here is one in Python that provides access to the
files in a bunch of directories where most of the columns correspond to
struct stat members:

 http://apidoc.apsw.googlecode.com/hg/example.html#example-vtable

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlBc5ggACgkQmOOfHg372QSInQCfQjVvH/A0Ob8stVDhQwOU1uWi
if0An21SYYi22k9RB9DTiTtnxCImP63b
=PKA0
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Covering index scan optimization

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 15/09/12 18:23, Simon Slavin wrote:
> PRAGMA lint_mode=ON

I'm sure there is some ideal to preserve the "Lite" part of the name and a
lint mode would be quite intrusive.  I can imagine a separate compilation
mode (eg another #define), or some mechanism by which you could load a
second "hook" library into SQLite that takes over various bits of
functionality (mostly parsing).

> It might help in the testing suite too.

I doubt it.  Test suites have a lot of code running at boundary conditions
and need to provoke certain configurations and circumstances.  Providing
randomly ordered results back doesn't particularly help anything.  (You'll
note just how much test code had to be altered to keep tests functional
after adding the covering index code.)

Roger



-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlBVUI0ACgkQmOOfHg372QTHbACfTP/8dfoa0zNbgStAeiMfFfMu
o/sAoIwX+dwIiLk+jmbOgBOQtsX7Eipb
=fNrn
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Covering index scan optimization

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 15/09/12 11:57, Richard Hipp wrote:
> ... and that this change merely exposes their brokenness.  I won't
> dispute that.  Nevertheless, with this changes, those applications will
> stop working.

There is the lint mode request:

  http://www.sqlite.org/src/tktview?name=25e09aa2ab

It would be *really* helpful if there was some way to put SQLite into a
mode by which developers using it can test and improve their own apps.  If
their app worked in that lint/test mode they would know that the chances
of changes like this one breaking their app would be tiny.

For the ordering case, and implicit "ORDER BY random()" could be added to
every query that doesn't specify an order.

(Of course how to deliver such a mode is tricky ...)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlBVKHMACgkQmOOfHg372QQRuwCghe+HEV0pKybWOam2TkDRfxax
LJMAn3afFd0YB9uEWVii2xTTRgFmkPfz
=+tho
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Covering index scan optimization

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

>> Is there a chance that the change will go into SQLite mainline?
> 
> Not without a copyright release.

And it may require more especially if you are an employee.  See the bottom
section of http://www.sqlite.org/copyright.html

And of course it is more than the few lines of changes - all the test
suites have to be updated to ensure no breakage and 100% coverage.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlBToHQACgkQmOOfHg372QR1XgCfZvoXr2uKaVFFDo46sEQZiML6
X3UAn0qWun5ldvSJdGj4SEN/n7dVBd7V
=GQzU
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to determine the effective cache size

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 28/08/12 12:48, Foster, Kristina (CIV) wrote:
> Thank you for the clarification.  I was wondering if I missed something
> with the command line or Python interface.  I will stay tuned for the
> sqlite3_db_status method in future releases.

A new APSW version has been released including sqlite3_db_status.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlBJN7AACgkQmOOfHg372QQuwACgwSOcRPZKFnrGDozxCIHZIq6v
nmsAoJ+JtTTSdcx812Gz22a2FP6/e1S2
=Rmx6
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Error code inconsistency

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

The functions sqlite3_status and sqlite3_db_status do substantially the
same thing (in different scopes) and have exactly the same signature and
semantics.

However the error code they return is gratuitously different when provided
with a (currently unknown) code.  In particular sqlite3_status returns
SQLITE_MISUSE while sqlite3_db_status returns SQLITE_ERROR.

It would be appropriate to make them return the same error code and for it
to be SQLITE_NOTFOUND (just like file control uses).

The doc doesn't currently specify which codes would be returned, but the
current behaviour is very ambiguous.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlBFQyEACgkQmOOfHg372QTddwCdFiZCPjTvq6wZCRcX7f6A3Hri
1x0An2FmAFatiJ+m7dEGa1+8mdzZlrTw
=P9kg
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do you load a ".csv" and skip the first line?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/09/12 12:19, joe.fis...@tanguaylab.com wrote:
> Is there some way to load a ".csv" file into a SQLite database table
> when the first row (record) of the file contains headers?

You may want to consider using the APSW shell.  It has a .autoimport
command that automatically figures out separators, what fields are
text/date/numeric etc.  You can then use the resulting database with
whatever you use for SQLite.

   http://apidoc.apsw.googlecode.com/hg/shell.html

Example usage (a few .dump lines trimmed) against your test csv:

  $ python -c "import apsw;apsw.main()" test.db
  SQLite version 3.7.13 (APSW 3.7.13-r1)
  Enter ".help" for instructions
  Enter SQL statements terminated with a ";"
  sqlite> .autoimport test.csv
  Detected Format excel  Columns 4  Rows 4
  Auto-import into table "test" complete
  sqlite> .dump test
  CREATE TABLE test(id, barcode_id, light, bpm);
  INSERT INTO test VALUES(1,'02455',1,180.2);
  INSERT INTO test VALUES(2,'02455',1,168.3);
  INSERT INTO test VALUES(3,'02455',1,189.4);

You'll note that it figured out barcode field was a string despite
consisting of only digits.  This is the .help for autoimport:

.autoimport FILENAME ?TABLE?  Imports filename creating a table and
  automatically working out separators and data
  types (alternative to .import command)

The import command requires that you precisely pre-setup the table and
schema, and set the data separators (eg commas or tabs).  In many cases
this information can be automatically deduced from the file contents which
is what this command does.  There must be at least two columns and two rows.

If the table is not specified then the basename of the file will be used.

Additionally the type of the contents of each column is also deduced - for
example if it is a number or date.  Empty values are turned into nulls.
Dates are normalized into -MM-DD format and DateTime are normalized
into ISO8601 format to allow easy sorting and searching.  4 digit years
must be used to detect dates.  US (swapped day and month) versus rest of
the world is also detected providing there is at least one value that
resolves the ambiguity.

Care is taken to ensure that columns looking like numbers are only treated
as numbers if they do not have unnecessary leading zeroes or plus signs.
This is to avoid treating phone numbers and similar number like strings as
integers.

This command can take quite some time on large files as they are effectively
imported twice.  The first time is to determine the format and the types for
each column while the second pass actually imports the data.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlBCclIACgkQmOOfHg372QQV2QCg3s6VpgCbvGG9xQqvQ1fSvBpj
OjkAn1afh25GTDpjPUYuHoJASmHdQ/3Z
=2S51
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users