Re: [sqlite] WAL: no schema after close

[Roger Binns]

On 03/06/16 08:28, Максим Дементьев wrote:
> Thank you, I'll try this "yet another python SQLite wrapper" in the near 
> future, it looks interesting.

Disclosure: I am the author of APSW.

I recommend looking at the page showing the differences between APSW and
pysqlite:

  https://rogerbinns.github.io/apsw/pysqlite.html

  Note: I suggest using APSW when you want to directly use SQLite
  and its functionality or are using your own code to deal with
  database independence rather than DBAPI. Use pysqlite and DBAPI
  if your needs are simple, and you don’t want to use SQLite
  features.

Roger




signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] struct SrcList

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/06/2014 04:41 AM, Prakash Premkumar wrote:
> Can you please show me where in sqlite code the struct SrcList is
> filled up ?

You keep asking people to do lots of work on your behalf without
showing you have made any effort yourself.  You have the same tools
the rest of us do.

Why don't you open the source files and search for the code yourself?
 Do a google search for tools that help navigate source code?  For
example cscope is an old command line tool for doing just that.  Show
us why you couldn't work out the answers yourself first.

Avoid being a help vampire:

  http://www.skidmore.edu/~pdwyer/e/eoc/help_vampire.htm

Ask smart questions:

  http://catb.org/~esr/faqs/smart-questions.html

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlQyuc8ACgkQmOOfHg372QQSnQCeJ0smBuAjhWBpb6S3Tl9ouTcT
HhwAoLyIXKfhDkOJeQG6GLac137u/kyY
=SaMM
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory Usage

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/02/2014 03:12 PM, David Muchene wrote:
> I was wondering what options I can tune to make sqlite use more
> memory.

Have you tried using memory mapping (there is a pragma).

  https://sqlite.org/mmap.html

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlQyunsACgkQmOOfHg372QQtIgCbBEBxTOeztFZcDRi2f1mCtVI3
Z5AAn39YIdXxxsc6CA6DrwSx94r9Ck6D
=KBxA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Will someone be able to explain this weird outcome...

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/10/2014 01:18 PM, jose isaias cabrera wrote:
> I was able to figure out that comma's are more important than just
> a 1000 number delemeter, so I received the right answer by taking
> the commas out:

To help avoid this in the future, be aware that how developers deal
with numbers and how users experience them are very different.

People using your apps expect to see numbers in the normal way for
them.  For example thousands separators are useful, but note that some
locales group differently (eg around ten thousands).  Some use dots
not commas, and others the other way around.  Some use dots for the
decimal point and others use a comma.  Some don't use Arabic numerals
(0, 1, 2, 3 etc)

  https://en.wikipedia.org/wiki/Decimal_mark#Digit_grouping

Fortunately the operating system and programming environment provide
ways to output numbers (and dates, currency etc) in the most
appropriate way for the user.

Widespread programming languages wouldn't work very well if numbers
weren't consistently formatted (eg what happens if a developer in a
different locale runs the code).  Reflecting their origins, they
almost always only accept the anglo-centric integer notation of no
grouping and a dot as the decimal point.  SQLite uses SQL which does
the same.

For you that means separating out text that you are using with SQLite,
versus text that is shown/accepted from the user.  If you mix them
together you'll end up with unexpected behaviour, crashes, wrong
results etc.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlQ4dEsACgkQmOOfHg372QS17gCdGr31RcjBKe7ncvHbR8yAyoCW
dkAAoMZyiAzNIsVkirunvVWCh5ADspPq
=fCjG
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3.c in a library - api rename

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/28/2014 08:18 PM, Ward Willats wrote:
> I am using the amalgamation in a C++ library statically linked into
> other people's applications.
> 
> Is there a way to namespace and/or macro and/or let C++ do its
> name-mangling thing to all the identifiers (by running the CPP
> compiler and turning __cplusplus off) so only my library
> translation units can use this "secret" version? (Or, more like, so
> the host app doesn't accidentally use my version.)

I have another approach to this that works well.  All of my code that
interacts with SQLite is in one file.  At the top of the file I do this:

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

The rest of the file references the sqlite3 api as normal.  None of
the symbols leak, and it is a little faster as the compiler can inline
static methods.

Roger


-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlRRB0UACgkQmOOfHg372QSELQCgoWKxiyy2RHbmZr5VKAjal/jf
3iwAoK+B9xnnME9Jg+08XIt7PB+rYrl/
=Gwsy
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is sqlite thread-safety sufficient for use with "Go" language ?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/05/2014 02:05 PM, nicolas riesch wrote:
> This means that EACH SUCCESSIVE function in the sequence above can
> be processed on a DIFFERENT OS THREAD.

That works just fine with SQLite, with one caveat.  You should also
make sure the wrapper itself is threadsafe.  For example what does the
wrapper do if you call close/finalize in one thread and step in
another concurrently?

The caveat is an implementation decision in SQLite and its error apis.
 Usually the OS error model is to have the errors be per thread.
SQLite instead has them be attached to the connection.  This means the
error apis return the most recent error for a connection no matter
which thread it happened on.  (In your case that is possibly
desireable.)  An example of how this can give the wrong information is
if thread 1 does a step followed by looking at the error info, but
between those two calls thread 2 does a sqlite operation on the same
database connection.  The error info thread 1 looks at could be from
its earlier call or from the the thread 2 call.

The second error issue is the api that returns a pointer to the error
string (sqlite3_errmsg).  By the time the pointer is used it could be
pointing to garbage or even now unmapped memory because a SQLite call
elsewhere on the connection caused that pointer to be freed.  Unmapped
memory will cause a crash, and who knows what the garbage will result in.

You can tell if a wrapper got multithreading right if the code looks
like this around every SQLite API call:

  // acquire db mutex
  sqlite3_db_mutex(db)
// make sqlite call
sqlite3_step()
// copy error details if previous gave an error
if (error) {
// make a copy of the error message
saved=strdup(sqlite3_errmsg(db));
}
  // release mutex

Note this has to be done for every sqlite call that can set the error
message which is approximately all of them.  Here for example is the
macro I use in my Python wrapper to do this:

https://github.com/rogerbinns/apsw/blob/master/src/util.c#L36

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlRbsk0ACgkQmOOfHg372QQMNQCfTfLUpodmuqnqUhe3tlXRAUBf
N7EAoJeUlu4Ir2h5WCHY9k1Ey9U7icm/
=vmiH
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking errors on network

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/10/2014 09:41 AM, Mike McWhinney wrote:
> Please let know if there are any other solutions to this database
> locking problem as used on a network.

Yes.  Do not do it.  See the FAQ:

  https://www.sqlite.org/faq.html#q5

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlRg/6wACgkQmOOfHg372QQAfgCeLCZ7I4uC/3p+bNSuGQN0uTUB
6LEAoLjp4/yJzVJSWzGDq7cam8pezRma
=jie1
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking errors on network

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/10/2014 10:22 AM, Mike McWhinney wrote:
> So SQLite shouldn't be used at all on a network?  Aren't there any
> other provisions to handled the locking errors if/when they occur?

Network filesystems do not implement locking and other operations
*exactly* the same as for (most) local filesystems.  This is done due
to the protocols involved, race conditions, performance
considerations, latencies, and various other reasons.  You are seeing
the locking errors as a symptom of this.

If you use SQLite with a network then your data will eventually end up
corrupted.  Yes it is possible to sweep some stuff under the rug but
that does not mean corruption won't happen.  SQLite won't be able to
prevent it, and often may not detect it for a while.

This page describes how SQLite does locking as well as pointing to
some newer alternatives:

  https://www.sqlite.org/lockingv3.html

See also:

  https://www.sqlite.org/whentouse.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlRhBm8ACgkQmOOfHg372QTcLgCfblMaFauIRgE83WOcF9z2M6BV
BMYAnRSP1KwC+69vb5fUMsGeGbdImHU1
=1mbq
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 12/03/2014 11:44 PM, Baruch Burstein wrote:
> Is it possible to somehow search for/replace a string in all
> columns of all tables?

(Disclosure: I am the APSW author)

The APSW shell includes a .find command that does the searching bit.
You also get coloured output which is nice.  It was implemented to
help find things in a database where you are unfamiliar with the
schema and wondering where the heck things are referenced amongst lots
of tables and columns.

http://rogerbinns.github.io/apsw/shell.html

The code that implements it is here:

https://github.com/rogerbinns/apsw/blob/master/tools/shell.py#L1384

It essentially has to loop over all tables, and then uses an OR
statement to check for the value in each column.  From the code you
can see it does additional work based on the value so that it may do
string, integer and LIKE comparisons simultaneously as appropriate.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlSAsisACgkQmOOfHg372QQC+QCgt4YAbvrxt1luvsnhK/r2R/0Q
l4kAoI3PvnQRvmObQqqGMAGJC1cEvehf
=X77t
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 12/04/2014 11:59 AM, Petite Abeille wrote:
>> On Dec 4, 2014, at 8:44 AM, Baruch Burstein
>>  wrote:
>> 
>> Is it possible to somehow search for/replace a string in all
>> columns of all tables?
> 
> .dump | sed ’s/old/new/g' | .read ?

That will only work under the simplest of cases.  For example if "old"
occurs anywhere outside a value (eg table name, text of a trigger,
index, column) then the database won't be right.  Baruch also didn't
say if "old" should be an entire value match or a substring match -
the former won't work with sed reliably.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlSA0Y4ACgkQmOOfHg372QTCDgCfbsJR9uJ/tVlYVnnn0clU1Egr
x/YAoOUuleJXlh3XEADeAm9CO/DH47qZ
=/ItF
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 12/05/2014 01:24 AM, Max Vlasov wrote:
> I once implemented a virtual table "allvalues" that outputs all 
> database values with (hope self-explaining) fields
> 
> TableName, TableRowId, FieldName, Value

Could you expand on how you coped with the underlying database
changing, and how you mapped virtual table rowids to the actual
database records?

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlSB+fcACgkQmOOfHg372QRZ0QCdHbaDwwE0mrE8SaITJhn5lB7K
KugAoJaBjpLVj4zemq9kqS1UsCAyvjuc
=1Jet
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 12/07/2014 04:43 PM, David Barrett wrote:
> so I'm curious if you can think of a way using the API (or any
> other way) to essentially "nice" the process by inserting a short
> "sleep" into whatever loop runs inside the VACUUM command.

Using OS provided functionality will be the most reliable.  Other than
that, a simple way is to sleep in the progress callback, although that
will make I/O lumpy.  If you want finer grained control then you can
copy the pointers for the default VFS into your own VFS, and override
the read/write methods to rate limit themselves.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlSFvBsACgkQmOOfHg372QRv9wCfYrybsVowHx6QTpbw/WjMoSZh
AJIAoNc4HyP1pUU/AvTGkdjJeQm93I7Y
=IKzd
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table names length and content

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 12/08/2014 10:30 AM, jose isaias cabrera wrote:
>>> Hmmm... what I am looking for it is not there.  If the "string
>>> length" defined there is what defines the length of the name of
>>> a table, I am in business. :-)  However, there is nothing about
>>> problematic characters. ie. <>!@#$%^&*()_+=-{}\|[]'";:?/.,,
>>> etc., etc. in the table name.

SQLite supports all those, as well as zero length table names, column
types and names.


sqlite> create table " <>!@#$%^&*()_+=-{}\|[]'"";:?/.,, etc., etc. in
the table name"("" "");

sqlite> .header on

sqlite> pragma table_info(" <>!@#$%^&*()_+=-{}\|[]'"";:?/.,, etc.,
etc. in the table name");

cid|name|type|notnull|dflt_value|pk
0|||0||0

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlSF764ACgkQmOOfHg372QRorQCcDbpSsjwclDLcKAiRQlFOC73M
Sc8AnirtIkzx1v/5LWrAc1VYAGJ53MnS
=bj14
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 12/08/2014 01:35 PM, Max Vlasov wrote:
> I wonder whether I/O "sleeping" possible in the first place.

In this particular case the OP wants to vacuum while the machine is
doing other I/O activity unrelated to the vacuum.  Having more
sleeping during the vacuum will allow the other I/O a greater share.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlSGMTgACgkQmOOfHg372QRxMACgz3qZHBGcUrOyf4DkFR5Km1a4
jm4AoL49txXLfzPQefbjlnGg9UZ4GtcP
=9gAV
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 12/08/2014 05:36 PM, David Barrett wrote:
> *Re: "a simple way is to sleep in the progress callback"* -- Can
> you tell me more about this?  Are you referring to the callback
> provided to sqlite3_exec(), or something else?

https://sqlite.org/c3ref/progress_handler.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlSHIZ4ACgkQmOOfHg372QResgCg1AXMQWpW0LnhKVc9k02TXRfN
P0wAoLdmiexWvkkiZOojFb7BSwZXF07X
=97eR
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VACUUM requires 6.7 times space ?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/05/2015 10:25 AM, Jim Wilcoxson wrote:
> It's easy to do this in Python using iterdump(),

Be aware that the pysqlite dump implementation is incomplete in many
ways.  Some issues:

- - Doesn't dump in a transaction so will have irregularities if things
change during the dump

- - Deletes the entire sqlite_sequence table instead of the entry for
the table being dumped/restored

- - Runs ANALYZE on all tables during restore if sqlite_stat1 exists
(ignores later versions of stat), for every table restored

- - Doesn't handle virtual tables (certainly can't restore them -
probably chokes on backing them up)

- - Messes up if any table names or columns have a double quote in their
name (probably single quotes too)

- - Doesn't disable foreign key processing during restore which can
result in a huge mess

- - Who knows how many other issues

pysqlite's iterdump is 50 lines long.  The APSW dump code (also in
Python) is 230 lines, and the SQLite shell C code is about 200 lines.
 pysqlite is definitely missing many of the finer details.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlSrRo8ACgkQmOOfHg372QQnfACgz5idM01KvQEDcuXWKaU9M21R
OqkAoN/TDCCgOOD5jW2Iqi/obGt57dRY
=4OWi
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PHP: squelch warning and error messages

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/05/2015 04:53 PM, Lev wrote:
> I'm using the PHP bindings for SQLite3. How can I squelch error
> and warning messages? I do error checking, but the failing call
> emits the messages.
> 
> Messages like:
> 
> Warning: SQLite3::prepare(): Unable to prepare statement: 1, no
> such table:

I'm guessing the "1" is a the SQLite integer error code and "no such
table: " is the error string from SQLite.  It is mystifying why this
is a warning and not an error.  As far as SQLite is concerned whatever
text it was given is an error, referencing what appears to be a zero
length table name.  There is no way the query can actually execute
after getting that error.

You sure as heck do not want to squelch error messages.  SQLite is a
library for developers and only works when you give it correct SQL.

Or in short, you squelch the diagnostics by providing acceptable SQL.
 You need to log/trace queries to find out which ones are the problems.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlSrSKAACgkQmOOfHg372QRFcACaAwpOYnJwDRX3lwb3+uqSwTsT
BKsAoJ18lmnGUrNBKgPgHznYv7m0AlIW
=yR3X
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_exec and returned error

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/05/2015 11:43 PM, Valery Reznic wrote:
> I was planing to use this function like following:
> 
> ---
> 
> int res; char *errmsg;

I'd recommend you explicitly set that to NULL here.

> 
> res = sqlite3_exec( db,  zSql, NULL, NULL,  );
> 
> if ( res !=  SQLITE_OK) { fprintf( stderr, "%s", errmsg) 
> sqlite3_free( errmsg )
> 
> }

In your print block you can do this:

  fprintf(stderr, "%d: %s", res, errmsg ? errmsg : "Error");

You can also put this line outside the error block (sqlite3_free on
NULL is harmless):

  sqlite3_free( errmsg );

This way you will always free the message no matter what happened, and
will show error text if available.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlSsO4QACgkQmOOfHg372QROVACeO7Kh4+bz+JME+bdRdeQbvqtt
RKoAoJlkUoluQwvITqqTDak+xCQiBtKE
=40C3
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] damaged database recovery

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/15/2015 12:52 PM, Dave Dyer wrote:
> Of course that's possible, but .dump produced what superficially 
> appeared to be a perfectly consistent text file.

Note that .dump writes the output and then on encountering problems
attempts the table again, but instead starting from the "end" in
reverse order.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlS4Rk8ACgkQmOOfHg372QRlLACg2Qxbn/WFJYkIUq5g/k6hiOGT
XxEAnA5UV6S6OQRBpMrqS1y2f3Gzx8IZ
=dzLI
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 tool bug

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/16/2015 01:05 PM, Simon Slavin wrote:
> Why on earth would an operating system programmer bother to put any
> translation into piping

You have a system with a bunch of apps installed.  You then upgrade to
a new version of the operating system and a whole bunch of the apps
break.  Do you think people blame the apps or the operating system?
Do you think anyone takes the apps apart and blames them for using the
wrong apis despite documentation to the contrary?

Microsoft puts a phenomenal amount of effort into backwards
compatibility.  So the question is actually why Windows behaves the
way it does.

Pipes are done differently on Windows for historical reasons.  DOS
actually did them by writing to a file and not by running the commands
simultaneously.  Operating systems have always done something with
I/O.  C libraries (fopen etc) also do things.  Heck a good question
might be why does Unix not have a separate text type of file?  BTW
SQLite shell uses fopen but claiming binary mode.

Raymond Chen - a Microsoft employee who does a lot of work in this
area has many good articles:

http://blogs.msdn.com/b/oldnewthing/archive/2003/12/24/45779.aspx
http://blogs.msdn.com/b/oldnewthing/archive/2007/07/23/4003873.aspx
http://blogs.msdn.com/b/oldnewthing/archive/2015/01/07/10584656.aspx
http://blogs.msdn.com/b/oldnewthing/archive/2010/03/11/9976571.aspx

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlS5jEsACgkQmOOfHg372QQt8gCg4Lu0r7I5eg8B4vZUygPGczxt
SdwAniolznwWWxogG0NHFX3yHkfI3SoB
=VgJ4
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 tool bug

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/16/2015 02:35 PM, James K. Lowden wrote:
> I'm skeptical of the notion that cmd.exe is diddling with your data
> en route to the pipe.

Almost certainly the reason is that stdout and stdin are in character
mode.  It requires extra code to put them into binary mode:

  http://support.microsoft.com/kb/58427

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlS5lz4ACgkQmOOfHg372QR6mACg4MmvKwP3pvZ0AJwLyKl08sGm
Z/0An050kV+FhIUtHnyZzKYGYEiK836s
=aG9W
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 tool bug

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/17/2015 12:20 PM, Graham Holden wrote:
> I would echo this: it's good at mangling the command-line, but
> I've not been aware of it ever mangling data sent to a file/stream
> (other than the binary/text mode conversions).

In text mode (the default for stdin/out on Windows) control Z (ascii
26) is also considered end of file.

Microsoft don't document everything that is done to text streams, but
there is likely to also be some conversions for the current code page
and MBCS.  Here for example is what is done with stdio when using the
wide character methods:

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

The bug in the SQLite shell is that it tries to manage the encoding
itself, which is fine if the file is in binary mode.  But with
stdin/out in text mode doing so will lead to extra data mangling.  The
shell needs to change stdin/out to binary mode:

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

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlS68dsACgkQmOOfHg372QSWRQCfeBr7J/p0VhqsDwRAhDcSDq3d
MuYAoLG9R5Z3DiEHQgYTY/Ulpu7ilgIi
=/lr/
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fsync or fdatasync performs slowly

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/18/2015 06:49 PM, Wei, Catherine wrote:
> takes too much time when it executes fsync or fdatasync function.

Note that although the documentation says only the file handle is
synced, in practise many filesystems actually sync the whole
filesystem writing out all pending data for all files.

The reason is the underlying filesystem is written to carefully write
content (especially meta data) in a particular order, to ensure
robustness against power losses, make syncs easier to manage, and make
fsck easier.  Trying to sync just the one file handle is too
complicated, so they sync everything.

You need to watch out for other write activity on the system, not just
SQLite.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlS9ZfwACgkQmOOfHg372QTsbACgzB3nBOgN3PnHo0C15IPIAQzq
2gYAoLXA3UF6bA5kNSfI31AauNQUlwu5
=uWTV
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fsync or fdatasync performs slowly

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/20/2015 01:12 AM, Wei, Catherine wrote:
> What's strange is that when I remove the disk, the difference still
> exists. It takes long time for fsync working with no disks, which
> has confused me for several days.

What exactly do you mean by "no disks"?  You earlier mentioned a file
handle, which means at the very least that there are one or more
filesystems.  Even if you are only using ramdisks, memory pressure can
result in things being moved around and take some time.

Ultimately you and your colleagues are the ones who will have to
figure this out.  We don't have access to your systems, or a way to
reproduce, and SQLite doesn't have code to cause this.

A tool that may help is SystemTap.

Roger


-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlS+qcsACgkQmOOfHg372QTRWwCg2J2ppZfkikYm9lDIrQYTgFw5
plgAoOR2o3WSo0TFP+sdBGZWmErBf8a6
=XenX
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible to get table size (in bytes)?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/04/2015 10:26 AM, Rael Bauer wrote:
> Is it possible to get the information of how many bytes a table is 
> taking up in the database?

What is the underlying problem you are trying to solve with this
information?  There may be alternate approaches that work better.

How often do you want the size (eg a one off, checked every few minutes)?

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlTSsNoACgkQmOOfHg372QS+VgCguwbCzace81zpwYNueblRu0Qj
85AAnR0XRX4Mw8DBlgu2Tfq04+/ng5Nc
=7HiF
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Interrupting custom collation processing

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/07/2015 05:55 PM, Dan Ackroyd wrote:
> Due to the nature of PHP, it would be expected that the processing
> of the collation should stop immediately. However I can't see how
> to indicate to SQLite that an error has occurred, and so the
> function is called for the remaining rows that need to be ordered.

I'm the author of a python wrapper for SQLite and faced a similar
problem.  Python records the exception information separately.

At the place you want processing to abort, call sqlite3_interrupt.
The top level caller checks the exception information first and if
there signals a Python exception.  Only if that is None does it check
the sqlite api return code, so that error (SQLITE_ABORT in this case)
will not be signalled.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlTW1GoACgkQmOOfHg372QSxDQCfWouTfdBvBn1LnHYd0cQToQ4C
4gYAoI+kvxC5+ZxcvKEOUoo81h/jvB6U
=yi8Y
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] equivalent for JOIN LATERAL

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/08/2015 03:32 PM, Simon Slavin wrote:
> For those like me who hadn't heard of it, here's a reference:

Here is a presentation referenced "Modern SQL in PostgreSQL", with
title "Still using Windows 3.1?  So why stick to SQL-92?"  Lots of
nice exposition and diagrams.  SQLite is also mentioned.

   http://www.slideshare.net/MarkusWinand/modern-sql

Seen on HackerNews:

   https://news.ycombinator.com/item?id=9018129

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlTYB58ACgkQmOOfHg372QSipwCfTJa/M8UPBqQ1UwRmoxgTVSwU
wBoAn0I9HpwyQswDjLjCKjoY6IdTh4lu
=EOku
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLCipher - Full Database Encryption for SQLite

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/09/2015 02:54 AM, Dominique Devienne wrote:
> Adding PRAGMAs is not possible to a true SQLite "extension" I
> thought,

It is however possible to add functions.  eg encryption_mode could be
added and called like this:

  select encryption_mode('aes-512', 3);

> My question then for Dr. Hipp is why such DB-encryption cannot not
> be implemented as a true "extension"? And the changes necessary to
> allow it? (like allowing user-defined PRAGMAs for example?).

The biggest problem with encryption (or compression) is that you want
to store extra information per page.  For example encryption will want
per page IV or similar key info.  Compression may result in the data
being larger than the page (eg if it is uncompressible you need need
at least one more bit to indicate that).  Storing the information
separate from the page results in all sorts of data sync issues.

The SQLite file format allows specifying how many bytes are reserved
per page which can then be used for storing this extra information.
However trying to do this as a generic extension is not possible
because APIs aren't exposed that way, and would be awkward if they were.

You can do all this by defining SQLITE_HAS_CODEC and then providing
various functions, but you'll want to compile SQLite and the
encryption code at the same time.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlTZHdEACgkQmOOfHg372QSmWACgibDcuPzVsyfFiFcdPZSNMN6B
CnkAoMQuNGoNqr2uhuNnWOkh/UiuKQ42
=qvhW
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 mailing list broken

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/12/2015 05:15 AM, Cal Leeming wrote:
> I've been having a problem where iterdump() is exporting in a
> format which is unsuitable for MySQL,

iterdump is part of pysqlite, and has no code from the official SQLite
project.  iterdump itself is just an inadequate 50 lines of python code:

 https://github.com/ghaering/pysqlite/blob/master/lib/dump.py

BTW there is a python sqlite group which will suit your python <->
sqlite needs better:

  https://groups.google.com/forum/#!forum/python-sqlite

> It seems the backup features of sqlite3 are not exposed in higher 
> level APIs such as Python,

SQLite exposes a C API.  It is then up to the wrappers to expose that
in their wrapped form.  pysqlite does expose it but seems to leave it
out of the documentation.

  https://github.com/ghaering/pysqlite/blob/master/src/backup.c

> and getting data out of a sqlite3 in-memory DB in python into a
> file is proving to be a difficult task, something which is arguable
> a common requirement.

It is pretty simple.  You can use the backup API, you can use textual
dumps, and you attach databases and copy that way.  I suggest posting
to the python sqlite list with what it is you are trying to do.

Disclosure:  I'm the author of an alternate Python SQLite wrapper:

  http://rogerbinns.github.io/apsw/

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlTc3AQACgkQmOOfHg372QSejACg5etNEUcBnNq0rKQV2cABkPo6
bjwAn0FagmG7B8jUxTCSEWDzLbMhOXwo
=4h4h
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Porting SQLite to another operating system (not supported out of the box)

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/10/2015 07:03 PM, Jay Kreibich wrote:
> ... VFS is unlikely to make the cut. ... similar things about the
> xBestIndex() and xFilter() functions

I haven't read the book, but one thing that may help is not using C
for these.  I think it is easier to understand VFS & BestIndex etc
using a higher level language, and use those to prototype, and then
drop down to C once you understand the problem.

As an example, my APSW wrapper (Python) lets you write a VFS
"inheriting" from an existing one.  You only need to override the
methods you care about, rather than having to implement everything
from scratch.  This is how hard it is to do a vfs that xors the data
read and written:

  http://rogerbinns.github.io/apsw/example.html#example-vfs

This is a vtable with blank BestIndex implementation:

  http://rogerbinns.github.io/apsw/example.html#example-vtable

I believe the wrappers for other languages are similar.  Of course for
a book you'd have to pick a language/wrapper and hope it is acceptable
for the audience.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlTdBHMACgkQmOOfHg372QRvYgCgg0Y1/Scvo+SGwOvPLkq3zBCF
nb0Anj2XQydblsSTFd0szBZ1afuLto8q
=mvfo
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] insert in sqlite (returning value for inserted row)

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/01/2015 10:10 AM, Kumar Suraj wrote:
> Hi Richard.. this wont work for me due to following reason.

Yes it will.  This is how I do it:

  BEGIN TRANSACTION;
INSERT INTO table .;
SELECT last_insert_rowid();
  COMMIT;

That will always give the right answer no matter how concurrent your
usage is.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlUcTPwACgkQmOOfHg372QRmcwCgzSMaabaWvdQGIIa2cSH2UywX
kHIAoN/ovfhvl54Qc/SbJ5NCDc/xb9CT
=4iBF
-END PGP SIGNATURE-

[sqlite] Prevent database file from being overwritten by other processes

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/08/2015 07:18 AM, Fabian Pr?bstl wrote:
> Is there an easy way of configuring / changing this so that
> SHARED_WRITE is not acquired? Will SQLite even function? Is it just
> easier to create a hidden copy and work on that?

For something this critical, an alternate approach is to restructure
it so you have a service which does the database and similar work, and
a separate gui that talks to the service.  (Note I mean service in the
Windows sense, much like a daemon on Unix.)

You can run the service as a different user, which means the gui
application user can't even access the file directly, nor cause mayhem.

That separate structuring also has other benefits such as allowing for
a separate command line client, makes testing easier, and you can put
the service and gui on different machines.  Multiple instances of the
gui can run too which may be very useful.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlUlVvkACgkQmOOfHg372QTvbgCg2cy3bsDgRj8TiOLbsvWbr8cQ
1VoAn2ZAajXnlQwlsd9mzkf3R7k/racZ
=kyDP
-END PGP SIGNATURE-

[sqlite] Request: Metadata about C API constants and functions

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

I am the author of a Python wrapper for SQLite.  As part of my
documentation build process I link function names to the relevant page
in the SQLite doc.  I also have to wrap all constants - eg making sure
that all SQLITE_LIMIT_ ones are wrapped and pointing to their doc.  (I
point to most C functions and wrap virtually all constants.)

In order to point to the correct doc pages, and verify that I pick up
all constants including new ones with each release, I have code that
parses the SQLite website building up lists of functions and constants
and which page is the correct one for them.  That means regular
expressions like r"""(sqlite3_.+?)<"""

This is a little brittle as it breaks every time there are formatting
changes.  Would it be possible to have some data file on the website
instead that I could use?  Perhaps other wrapper authors have the same
needs.

These are the details of what I need:

For each function, its name, the doc page and fragment, and ideally a
few word description of it (the title on the doc page is fine).

For each family of constants (eg result codes, extended result codes,
run time limits), the doc page, and a few word description (of the family)

A JSON or XML file with that would make my life a lot easier.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlUnF1UACgkQmOOfHg372QT6wACggLHovwyZvwyYQJgD2UPmhH+N
g7YAn08hjxOP5Qol/jy0QZekxPgWMy4v
=261V
-END PGP SIGNATURE-

[sqlite] Request: Metadata about C API constants and functions

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/09/2015 05:42 PM, Richard Hipp wrote:
> On 4/9/15, Roger Binns  wrote:
>> A JSON or XML file with that would make my life a lot easier.
> 
> JSON/XML?  Seriously?  We eat our own dogfood here, thank you!
> When you get the data it will be in an SQLite database file!
> JSON/XML Good grief...

So the URL for this SQLite database is?  :-)

> As it happens, the SQLite documentation build process already
> parses out most of this and puts it into a database already.

Any chance you can put a copy of the database on the website that gets
updated along with documentation updates?

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlUnNLwACgkQmOOfHg372QSr3gCfcR77qmpDg0ZOntG0BHXFtHY8
EbwAnA3DjcRx1aCvJj31DbNpyV/XVgSe
=Puuy
-END PGP SIGNATURE-

[sqlite] Request: Metadata about C API constants and functions

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/09/2015 06:43 PM, Simon Slavin wrote:
> Hmm.  We could add an output mode to the shell tool which outputs
> in JSON format.  And by "We" I mean someone who can write good C,
> which isn't me.

Get yourself a time machine, go back 6 years, and enjoy the shell
which has had that all along:

  http://rogerbinns.github.io/apsw/shell.html

The shell is in Python, but you don't need to know any to use it.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlUnNdQACgkQmOOfHg372QRiPQCgp3OWyyxIhnKvUfOSYoQG8Mnz
RvkAnRFOXOukgmWyB8TRnEHVtpTYNzIG
=gLhP
-END PGP SIGNATURE-

[sqlite] Request: Metadata about C API constants and functions

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/10/2015 12:33 AM, Dominique Devienne wrote:
> But that's build time. There is no way (i.e. pragma) to list
> registered SQL functions at runtime.

There is a ticket for this:

  https://www.sqlite.org/src/tktview/5896edbe463b6a1f0f14

It was rejected without explanation a year ago, after being open for 8
years.  Sadly there is no workaround as the information is just not
exposed in any useful way.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlUn0SsACgkQmOOfHg372QS9HgCeJS+65yV4iagsRqCpSXs8+em/
hawAoNPy4Oqw4t2iEbJzuSSOrFq3IqWa
=zlHT
-END PGP SIGNATURE-

[sqlite] JSON expressions for records and synchoronisation

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/10/2015 01:51 PM, Simon Slavin wrote:
> With a cursory glance I do see important incompatibilities with
> SQLite.

I use JSON as the data format for $work stuff (startups) for years,
and these JSON schemas etc miss why some of us use JSON.  If you are
using a statically typed language like Java or C++, then arbitrary
data is very difficult to deal with.  XML is very popular in that
world because DTDs mean you can make it statically typed.  The mindset
is that if everything can be specified and constrained in advance,
then successfully compiling the code means it is mostly correct.

JSON like several other languages (eg Python, Ruby) is dynamically
typed.  You can stick anything you want in variables/fields, since the
type is associated with the value, not the variable name.  This allows
for far greater flexibility, and especially means you do not have to
decide in advance exactly what you will do with data.  Dealing with
XML is a huge pain!  Code is trivial to deal with whatever you get.
Importing code does not mean it is correct - running it is how you
figure that out.

These schema and similar efforts seem to be an exercise in getting the
worst of all worlds, combining rigid languages with flexible data by
trying to make the data inflexible.  Use the right tool for the job.
It is also why this and efforts like unsql don't take off - they are
bandaids for a mindset being applied in the wrong place.

/rant

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlUoPX8ACgkQmOOfHg372QSywwCfbHzpZxiOz+/D3wO2UgbbNuMS
YGYAniyVNFM9I9JIX2H6Mi9yuiIYV6Np
=1ZX9
-END PGP SIGNATURE-

[sqlite] sqlite3 (or sqlite4) performance on NFS

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/11/2015 06:30 AM, Peng Yu wrote:
> I am wondering for my limited usage scenario, is it possible to
> make sqlite3 on NFS as fast as sqlite3 on local storage (such as
> disable file locking).

The latency is what is getting you.  SQLite uses synchronous/blocking
I/O.  What version of NFS are you using?  NFSv4 has features that
significantly reduce the effects of latency.

If you want to maximize performance then I recommend writing your own
VFS, which is very easy for read only.  That will let you do read
ahead and caching.  Heck you could just copy the database locally and
use that.

One technique I used in the past (for WAN optimisers) is to record the
access patterns for a newly opened file, and then on subsequent opens
pre-read that same data.  This was very effective across many file
formats, but SQLite was not tested/relevant.  It also meant not having
to understand the actual file formats.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlUpwCQACgkQmOOfHg372QSt1gCgzORbl2o18gKfkzMx8qph557L
AQUAn0Rcog3QZbwWeAck8tFlira84xZU
=dgFW
-END PGP SIGNATURE-

[sqlite] JSON expressions for records and synchoronisation

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/13/2015 03:15 PM, James K. Lowden wrote:
> I beg to differ, Roger.

Note that I deliberately said "some" and not "all".  Everyone has
their reasons, and not everyone has the same reason.

> The programming language of choice doesn't affect the correctness
> of a function.

I didn't mention compilers, nor are they relevant.  I am generalising
to a mindset, where those that use a static type system as implemented
in various popular languages, tend to lean more heavily on that type
system and its rigidity, hence expecting the same thing from their
data, hence being concerned about schemas etc.  And those using
dynamic type systems as implemented in various popular languages can
favour that same dynamism in their data, where these schema proposals
aren't particularly helpful.  Dynamic data is harder to deal with in
static type systems.

> I think you know better.  When you write a program, you *have* 
> decided "in advance" (of running it) "exactly what you will do"
> with the data.

I mean things like you can just put a single value in that you may
look at later, and then later figure out if a single value or a list
of values is better.  Or make it an object (in the JSON sense) or a
list of them. Dynamic type systems make it far easier to deal with
this, to change your mind later, and to deal with mixtures of
different types in the data.

Roger


-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlUsYzsACgkQmOOfHg372QT0dgCgkSSIlby2WGzFlDw+fxSy8LAe
EwwAnjTNpX5PQ65HHweaSAH0Pv+vBUGV
=6lK3
-END PGP SIGNATURE-

[sqlite] Request: Metadata about C API constants and functions

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/10/2015 07:11 AM, Richard Hipp wrote:
> On 4/9/15, Roger Binns  wrote:
>> 
>> So the URL for this SQLite database is?  :-)
>> 
> 
> https://www.sqlite.org/toc.db

Thank you for this.  I've now updated my doc build tools, and even
added some more features.  All the regular expressions are gone.  My
doc tool has ~15% less code.  It is a very productive time when you
get to delete code :-)

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlUu9N0ACgkQmOOfHg372QSvmgCePLoTdlfDYPxoBqPeF1k9oW9t
f+0AoMwRfmOe5WUJ1y3XEI+0jUqEh0WK
=+NlH
-END PGP SIGNATURE-

[sqlite] MMAP performance with databases over 2GB

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

In my testing I am finding that using SQLite's mmap support with
larger databases (final db size is ~10GB) to create the database (WAL
mode) is considerably (about three times) slower than no mmap.

The default max mmap limit is 2GB (sadly).  Has anyone else tested
mmap with >2GB databases and have results?  I don't know if this is a
peculiarity of my data set, or because of how SQLite is implemented.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlXDdQMACgkQmOOfHg372QRt3wCeKQpP9g2OhWS2yJg+iU7Gxvxo
TPUAn3ikDarecOaKLPIsnS3Xv+IltreU
=yUxV
-END PGP SIGNATURE-

[sqlite] MMAP performance with databases over 2GB

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/06/2015 09:27 AM, Dan Kennedy wrote:
>> Is it using more CPU cycles in mmap mode or just taking longer?
>> If the former, does [time] attribute them to "user" or "system"?

It is taking longer.  I have 3 XML dumps which I turn into JSON
(incrementally) and then denormalise and insert into SQLite across
several tables.  While all this work is going on, the code prints out
statistics about how long it is running and about how many records per
second are being processed.  The final database size after commit and
vacuum is ~8GB.  There are a lot of foreign keys too, although all
simple (referring to an INTEGER PRIMARY KEY column).

I gave mmap a try - ie the *only* change was to add an extra pragma
before the beginning of the transaction:

   "pragma mmap_size="+str(2*1024*1024*1024)

In hard numbers, without that line I was doing ~1,118 records per
second and with it it does ~300.  A normal run takes about 1h20m but
the mmap one was still running 3 hours later when I aborted it.

(BTW this is all on a tmpfs filesystem on 64 bit Linux with swap
spread across two ssds, and 32GB of ram.  ie the actual storage
hardware isn't a factor.  Also single threaded because XML.)

>> How large are you letting the wal file grow between checkpoints?

Pretty much the entire database size.  For the tests I was starting
with a deleted database directory (ie no pre-existing files), and then
doing these pragmas:

  "pragma page_size=4096",
# "pragma mmap_size="+str(2*1024*1024*1024),
  "pragma journal_mode=wal",
  "pragma wal_autocheckpoint=1",
  "pragma foreign_keys=on",

Then I start a transaction, and do the importing within that
transaction.  The database file is 4kb during that process, the wal
file gets to be about 10GB.

If I use gdb to periodically break into the running process in the
mmap case, then it was always in sqlite3WalFindFrame.

I don't need any help fixing my importing process (eg don't need a
journal on an empty database anyway).  But it is frustrating that mmap
only goes up to a few kb shy of 2GB even for 64 bit, and I have one
example (ie anecdote not data) showing that mmap hurts for inserts on
> 2GB databases.  Perhaps it is worth others testing to see if this
> is
a systemic problem, or just bad luck for me :-)

It may also be relevant that tables add columns over time.  I
dynamically add them after encountering previously unseen fields in
the JSON.  However I'd expect the schema to be final a few thousand
records in.  Most tables have 3 to 6 million records.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlXDmukACgkQmOOfHg372QSVtgCbBihGgIuZqS3Yy2JARXZ1+Q59
GmwAoMG53XxuLNhcMIw1PV46fD/Z/5tT
=luXx
-END PGP SIGNATURE-

[sqlite] Determine query type

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/06/2015 09:46 AM, Ben Newberg wrote:
> Is there a good way to determine if a sql query is either
> attempting to modify a database, or simply querying it?

You most like want to use the authoriser.  It tells you exactly what
the query is doing, and you can block/render harmless/approve the
various actions making it up:

  https://sqlite.org/c3ref/set_authorizer.html

A very brief example of what you get called back with is here:

  http://rogerbinns.github.io/apsw/example.html#authorizer-example

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlXDm8oACgkQmOOfHg372QTuBACcCD99VOOBscT6YOHsE4jux3Zc
lSYAoMksAQcHqieUHsdWniNqXnOFYJ4v
=IG84
-END PGP SIGNATURE-

[sqlite] MMAP performance with databases over 2GB

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/07/2015 02:55 AM, Dan Kennedy wrote:
>> http://www.sqlite.org/src/info/3a82c8e6cb7227fe
> 
>> Does that improve performance any in your case?

I tested 3a82c8e6 (fix above) against e596a6b6 (previous changeset),
in both cases starting with an empty database, on tmpfs and with WAL.
 Three kinds of data are imported into the database, but it also means
the first kind fits mostly within 2GB.  I stopped the third kind
import at 48 minutes in both cases.

3a82c8e6 (with mmap change)
- ---

2m52s  22,821 per second
2m56s   4,823 per second
47m56s  1,157 per second 3.3 million records imported of this kind


e596a6b6 (without mmap change)
- --

2m51s  22,855 per second
3m43s   3,800 per second
47m54s462 per second 1.3 million records imported of this kind


Your change definitely helped once the database got above 2GB (I don't
track exactly where that change happens in the import process - looks
like in the second kind.)

It would also be really nice if there wasn't a 2GB mmap limit on 64
bit machines.  The database would fit in my RAM around 4 times, and in
the address space more times than there are grains of sand!  Yea I
know this isn't very Lite ...

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlXFLlkACgkQmOOfHg372QQStwCfXXQhxJsyfJWUq/hOUm2KYdbs
aPoAoLOHYbBn7CItwbmASG5igPeeeXpl
=f1gz
-END PGP SIGNATURE-

[sqlite] how to detect when a table is modified ?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/07/2015 04:23 PM, Nicolas J?ger wrote:
> I'm writing a program using sqlite3 and gtk to screen a database,
> there is a way to get a signal from sqlite3 when something is
> modified inside a database (values, add/delete row...), in the way
> to update what the program has to screen ?

Is the database being modified from within the same process, or from
outside?

The update hook will tell you (most of the time) about changes within
the same connection (ie if you are making the changes):

  https://sqlite.org/c3ref/update_hook.html

The data_version pragma can tell you if something has changed, but
you'll need to poll:

  https://www.sqlite.org/pragma.html#pragma_data_version

You can use change notification of the file system to know when the
database file is changing and then do the pragma polling to find out
when the change is complete.  For example on Windows the API starts
with FindFirstChangeNotification and on Linux you'll find i/dnotify.
Do make sure to monitor the main database file, as well as any other
related filenames such as wal, journal and shm.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlXFTvcACgkQmOOfHg372QSjZwCfZkGNxETrd5MSdx41Wks9/wGh
670AnA7I7jQxlKMe2rMkVTraR/m7PYag
=Uu+0
-END PGP SIGNATURE-

[sqlite] Site unavailable.

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/11/2015 04:18 PM, ??? ??? wrote:
> In Russia when I'm trying to visit sqlite.org site, I'm getting
> conection timeout.

Use down for everyone or just me to check sites:

  http://www.downforeveryoneorjustme.com/sqlite.org

In this case it is you.

> Also, sqlite.org doesn't pinged.

$ ping sqlite.org
PING sqlite.org (67.18.92.124) 56(84) bytes of data.
64 bytes from sqlite.org (67.18.92.124): icmp_seq=1 ttl=48 time=41.4 ms
64 bytes from sqlite.org (67.18.92.124): icmp_seq=2 ttl=48 time=40.6 ms
64 bytes from sqlite.org (67.18.92.124): icmp_seq=3 ttl=48 time=42.1 ms
^C

You likely either have a transient issue, or there is some sort of
filtering going on at some level.  It has been known for various
content based firewalls to incorrectly decide sites like sqlite are
inappropriate.  However they should generally tell you they blocked
the access.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlXKioQACgkQmOOfHg372QQsUQCg156vPDGVqz4iqUNz31s+QNY8
9ScAniH0soJC+LiKEC+c3k8Xiw9jq6JI
=EYWs
-END PGP SIGNATURE-

[sqlite] ESC_BAD_ACCESS when using a background thread

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/23/2015 03:31 AM, Jeff M wrote:
> sqlite3_step();   // occasionally crashes here (showing
> ESC_BAD_ACCESS on main thread)

That has three very likely causes.  The first is that your internal
state gets messed up, and the statement has actually been
finalized/freed.  ie it is junk memory.  You can add assertions that
check it is still a known statement pointer by checking this returns it:

  http://sqlite.org/c3ref/next_stmt.html

The second is that the memory where you stored the statement pointer
is what is trashed.

The final cause is that some other code has memory bugs, causing
damage to SQLite's data structures.

> It's not a zombie object issue (tested with NSZombieEnabled).

Sadly that only checks Objective C objects, and not all memory.

> Any ideas on how to debug this?

I used valgrind running the app in the simulator.  (I also configure
valgrind to never actually reuse memory.)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlXaXpEACgkQmOOfHg372QRKZACfWyT6pEyNQ9sEKPbhFQ4pI/5G
Nh0AniO5ESx9CIbB484/gYqjtfCsGUrM
=Op+8
-END PGP SIGNATURE-

[sqlite] ATTACH DATABASE statement speed

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/19/2015 05:56 PM, Paolo Bolzoni wrote:
> I left running the pragma quick check during the night and finished
> in 2 hours and 46 minutes, so it is about 8 times slower than in
> ext4. Zfs is an advanced filesystem plenty of features, but this
> speed difference is too much I think.

I use btrfs which like zfs is also a copy on write filesystem.  It is
possible for the files to get very fragmented which can result in
dismal performance, even on an SSD even for reads.  Random small
writes especially aggravate this.  btrfs has an autodefrag option that
addresses this in the background, and SQLite is specifically mentioned
as all the browsers use it behind the scenes as do many email clients.

  https://btrfs.wiki.kernel.org/index.php/Gotchas  (2nd last section)

The filefrag command may be helpful if implemented for zfs and will
tell you if fragmentation is a problem.

Virtual machine images are another problematic file type with similar
read/write patterns to SQLite.

Copy on write filesystems don't modify existing (meta)data, but rather
write new versions that point to the existing data for bits that
aren't changed.  Repeat this many times and the chains of pointers get
very long, which is the fragmentation.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlXadDsACgkQmOOfHg372QRdpACfRUmHD4hXfAx6+il0q/7lINxd
9lwAoNAyYV1oa5cYcn1O00JxW4nsI/Sp
=VrTq
-END PGP SIGNATURE-

[sqlite] ATTACH DATABASE statement speed

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/23/2015 06:48 PM, Simon Slavin wrote:
> Paolo is using Linux which does not do read-ahead optimization like
> some versions of Windows.  Therefore if he really is using an SSD
> then fragmentation is not an issue.

You are confusing things.  The data structures used in a copy on write
filesystem (as both btrfs and zfs are) are what gets fragmented.  This
is not like a traditional filesystem that will update existing data
structures.  It is more analoguous to garbage collection.

I promise you that it really does become an issue, even with an SSD.
When a file is across ten thousand fragments, latency and throughput
suffer.

> I have seen fragmentation make something take twice as long.

You are talking about traditional fragmentation, not a degenerate
state for copy on write based filesystems.

> My suspicion here is that there's a design fault in ZFS.

Unless zfs does automatic defragging, it will have exactly the same
problems as btrfs.  This is inherent in how the data structures are
laid out, and that nothing is modified in place.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlXakBgACgkQmOOfHg372QTepACffpEZ/tozxJKv0bKgZQ0D0wIL
HqUAn3ES+b+xr/c8h7I/lqJs1zhQRVrg
=+S02
-END PGP SIGNATURE-

[sqlite] ESC_BAD_ACCESS when using a background thread

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/24/2015 03:08 AM, Jeff M wrote:
> I've checked all of your suggestions and nothing is amiss.

You ran valgrind and it said everything is fine?  That would be shocking.

> I don't understand how the main thread can run before the
> background task has completed -- and specifically while sqlite_step
> is still executing.

If the database is busy/locked then sqlite_step can relinquish the
mutex, call the busy handler and try again.

> Apparently, sqlite_step() can relinquish control to the main
> thread.

SQLite does not contain a thread scheduler (we'll ignore pragma threads).

You've either got 100% correct code in which case the underlying
issues are to do with concurrency (need to check memory barriers,
marking as volatile etc), or there is a bug in the code related to
object/pointer lifetimes or similar.  With the latter changing things
changes where collateral damage happens, but doesn't actually fix the
issue.

It is almost certainly the second case.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlXbSzwACgkQmOOfHg372QR/XACfV5Iw0umJ1smYygE/Komcemx0
46gAnjumLvNw1/fj2uFpIEdTwYnmhlOd
=HcCt
-END PGP SIGNATURE-

[sqlite] ESC_BAD_ACCESS when using a background thread

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/25/2015 02:38 AM, Jeff M wrote:
> I tried to install valgrind (on Mac OS Yosemite), but I can't get
> it to compile.  I don't have the fortitude to work out the install
> issues.

Generally you would be better off using something like homebrew:

  http://brew.sh/

It will take care of dependencies, updates etc and knows how to
compile things.

> Looking at Xcode's memory report (and using Instruments),

Instruments isn't too bad, but IIRC does not do the same thing as
valgrind.  Instruments is a lighter less thorough functionality.
Simon gave lots of links.

> ... this app is pre-ARC

Ouch.

> Among other things, I see many persistent 4K memory blocks
> resulting
from sqlite3MemRealloc

Many of those are likely to be the SQLite cache.  Only closing the
connection will free them.  (SQLite does have a boatload of custom
memory management options, but your underlying issue is very unlikely
in SQLite itself.)

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlXcpHcACgkQmOOfHg372QTuEgCgjYogcxmCcINsHDj06EUKE8zj
j9oAn1PHfGnz93q9BBXm8LxIoP8I0o2S
=FIdc
-END PGP SIGNATURE-

[sqlite] Appropriate Uses For SQLite

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/18/2015 11:43 AM, Richard Hipp wrote:
> but I think the truth is we are probably stuck with SQL for a while
> yet.

In theory there could be an intermediate representation form (like
compilers do) that is publicly available, with the (now optional) SQL
part producing IR, as well as any other query language
implementations.  The LLVM project is an example of doing a design
like this.

In practise the result wouldn't be very Lite, and would constrain
future development.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlTlAGsACgkQmOOfHg372QRP+ACgqoP3Ss8ZgvO95M8IVHhLRDbo
itEAoMhJKWIKiiYjsAqNUGl/cpv/e+fp
=Z+np
-END PGP SIGNATURE-

[sqlite] Appropriate Uses For SQLite

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/18/2015 01:28 PM, Jay Kreibich wrote:
> SQLite kind of already does this, if you consider VDBE instructions
> to be an IR.

There is a lot that would have to be done with it:

- - make the IR stable across releases

- - add more general instructions beyond only what is needed for SQL

- - expose an API that takes the IR

- - possibly redesign it to make static verification possible, like Java
bytecode did.  Currently VDBE can assume there is no hostile intent,
but general provided IR would need to be checked.  For example a
static check that it doesn't goto outside the bounds of the IR.

- - lots of documentation and provision for testing

All that adds up to a lot of work, and possibly quite a lot of redesign!

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlTl/20ACgkQmOOfHg372QRx0ACgzY6vhU+84LMLMcpeZCAiLtwl
RE8AoJFv/1LQTMhsFvHxgODh/zxnKKQF
=Egzn
-END PGP SIGNATURE-

[sqlite] Sqlite3 tutorial and reference manuals?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/22/2015 10:10 AM, russ lyttle wrote:
> The "natural" language for the Raspberry PI is Python. For Arduino
> it is C++. I've confirmed that both PERL and Python drop too many
> messages when using my naif DB with sqlite3, R-PI model A, and Digi
> xbee s2 radios.

You didn't say how you structured things, and some details really matter.

Any kind of database that has durability will have to sync to storage
at various points, and that storage will be relatively very slow.  For
SQLite the default is for each statement to be its own transaction,
unless you explicitly manage transactions.  Consequently each insert
will block waiting on storage fsync.  There is tweaking you can do to
mitigate things, but we'd need to know more about what you are doing
and have done to provide advice.  Not doing anything about this will
provide dismal performance even on desktops.

The second part is about dropping messages.  Since you aren't using
real time operating systems, there will be variability in latency in
dealing with messages.  The usual solution is to put some sort of
queue/buffer between message ingestion and message processing to
smooth over the timing bumps.  It is generally a good idea to also
split that across multiple threads or processes so that priorities can
be managed.  Python's GIL is also very relevant.  For the RPi 1 with a
single core, it isn't going to matter too much, but the RPi 2 gets you
quad core.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlTqKNAACgkQmOOfHg372QSPHQCdGj+SLu9ignj5BoI5S8L1WAjL
xQoAnjDXyKdRm38Tm9H5CQxQvPoB7Osg
=7/nt
-END PGP SIGNATURE-

[sqlite] SIGSEGV rolling back in-memory DB‏‎

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/07/2015 09:47 AM, Richard Hipp wrote:
> Can you send instructions on how to recreate the problem?  Please
> bare in mind that none of the core SQLite developers do much with
> python. Alternatively, you might send emails to Roger Binns and
> badger him into trouble-shooting the problem, since he is an expert
> at both python and SQLite and is likely to get to the bottom of the
> matter faster than any of the core SQLite devs.

No need to badger, I am happy to help :-)

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlWcEUcACgkQmOOfHg372QQXMACfdSKACGIZOwsAzOnpf2fwl1iO
UWgAn1n4OuC1/Cr9y6TfwK6QovmmgTEe
=j1D4
-END PGP SIGNATURE-

[sqlite] SIGSEGV rolling back in-memory DB‏‎

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/07/2015 02:06 PM, Brian Soby wrote:
> I don't believe it's a python specific issue since the core problem
> occurs in a rollback command internal to SQLlite.

Unless you configure it otherwise, pysqlite likes to parse your SQL
and do extra operations behind your back (eg automatically starting
and committing transactions).  pysqlite also doesn't do
multi-threading correctly - how do you do threading?

> I don't have an easy way to recreate the problem ...

That will make it very tricky.  Note that these kind of issues are
extremely rare in SQLite, and almost always caused by other bits of
code corrupting memory, and taking out SQLite as a side effect.  Try
disabling as many non-SQLite extensions as possible (that are
implemented in C).  Also use valgrind and similar tools as memory
checkers.

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

Sadly pysqlite doesn't have an easy way of tracing SQL execution
without you modifying all call sites and updating its code to catch
the behind your back operations.

If you have the time and can update your code, you can try APSW as an
alternative to pysqlite (disclosure: I am the APSW author).  APSW
includes a tool that lets you see all queries and data returned, as
well as profiling information:

  http://rogerbinns.github.io/apsw/execution.html#apsw-trace

You may also find these two links relevant:

  http://rogerbinns.github.io/apsw/pysqlite.html
  https://pythonhosted.org/sqmediumlite/src/apswdbapi2.py.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlWcVZwACgkQmOOfHg372QSOjACgoSXRAijHJncTHYY6VU4dfB6Q
gHIAoMT6zvydHZCvhNAFzX1azvFmMeZO
=mfkI
-END PGP SIGNATURE-

[sqlite] SIGSEGV rolling back in-memory DB‏‎

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/08/2015 09:55 AM, Brian Soby wrote:
> It's single threaded.

Good - that eliminates a whole bunch of complications.

> I have a log of all of the queries run through the database up
> until the crash. It crashes on a rollback command.

What happens when you run those queries as a script for the SQLite
shell?  If that has problems then everything needed to reproduce the
issue is present.

However if that doesn't reproduce the issue then it is something else
in the process that is causing problems.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlWdW5UACgkQmOOfHg372QTvJwCgrgS9gKjopjfppv6nPqTKSg1S
rR4AoLwvAXooKJn/1kNwVipyEDwEQ0KZ
=9y8e
-END PGP SIGNATURE-

[sqlite] SIGSEGV rolling back in-memory DB‏‎

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/08/2015 10:33 AM, Brian Soby wrote:
> Nothing appears to happen but it's not running very faithfully.
> Specifically, I get a bunch of problems on the rollbacks about the
> savepoints not existing even though it's replaying the original
> SAVEPOINT "x" command before the rollbacks. This is probably more
> due to me not knowing what's going on than anything else.

Do you have pysqlite configured with isolation level of None?  If you
use the defaults then it starts doing stuff on your behalf that you
wouldn't have captured.


https://github.com/ghaering/pysqlite/blob/51c1a18eac3d1d14a052b09e3546dd0c4ce70638/src/cursor.c#L607

Ultimately since you understand your code base best, you'll need to
either provide pure SQL that can reproduce the issue, or you'll need
to prove that there is no other component in the process that is
corrupting memory that happens to be used by SQLite.

To better catch issues with the SQLite shell, I recommend compiling it
with -DSQLITE_DEBUG -UNDEBUG and running under valgrind.  This will
improve the chances of catching memory issues and verify SQLite
internal assertion checking (there is a lot of it).

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlWet/wACgkQmOOfHg372QT+ggCgwXiWdIhegZlUdDINi9OhN4G6
5gwAn22bk2Qgt2ljVHb5c7GBSYFkALAK
=5mrB
-END PGP SIGNATURE-

[sqlite] Schema-less JSON SQLite DB?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/13/2015 05:43 PM, Hayden Livingston wrote:
> Is there a concept of a schema-less JSON SQLite DB?

What exactly is it you want?  A schema-less database?  A JSON
database? Using SQLite to store JSON?  (It is unclear if you mean
something like SQLite, or SQLite itself).  Something small and light?
 A library not a server?

Be aware that you can dynamically update the SQLite schema, and that
it takes no time to do so.  Another database I won't name locks the
database and rewrites each record.  This can take hours or even days.
 SQLite just updates the schema table, and fills in defaults for
records written before the schema change, on reads.  I wrote some code
the other day that takes JSON and does the schema manipulation,
although it is annoying.  (It would be really great if SQLite
supported arrays like Postgres does.)

You can also store arbitrary JSON in a column.  You won't be able to
query it effectively, but you can duplicate values into columns you
can do queries and indexes on.

> My reason is simple: versioning. We have lot of business metrics
> that get updated let's say once a month, and we need to be agile to
> get them. Right now, we just put the version in the SQLite file,
> and then make sure no queries cross the boundaries.

You can also use multiple databases, and attach them.  For example if
you operate on a month by month basis, then you can put each month's
data in a separate SQLite file, then attach last month's as
'lastmonth', and use in queries (eg SELECT blah blah WHERE bar.foo >
lastmonth.bar.foo)

> Do others have experiences and requirements similar to this?

All the time.  I use SQLite when I don't need networked access, a
running database server, and need less things that can go wrong.
Mapping JSON into this is painful but possible.

When I can use a database server, I prefer Mongodb as it is very good
at arbitrary JSON in, the same arbitrary JSON back out.  It is
especially pleasant that the query syntax has the same shape as the
underlying JSON data.  Also JSON maps trivially to Python which I use
the most.  (Note however that Mongodb does have some issues, but so
does much other software out there.  Production use does require
effort & planning as does other software.)

In the longer term Postgres is getting increasingly better JSON
support.  Hopefully it will give Mongodb a run for its money soon.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlWkbfsACgkQmOOfHg372QTFiwCgzzf5fEzfJdWn84bk0e1fusGE
qeAAnAhR+sHb39Gsha2Owq3SXsdZVRex
=9jcT
-END PGP SIGNATURE-

[sqlite] Schema-less JSON SQLite DB?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/13/2015 08:00 PM, Hayden Livingston wrote:
> Does your code also map object hierarchies in json?

Yes, but thankfully I don't have much of them.  Essentially the top
level of the object has a unique id (SQLite allocated), and then other
tables are used to join zero or more child objects to the top level.

> What general format do you use?

Ultimately I use Python dictionaries which are supersets of JSON
objects.  Some (ugly) code can convert both ways

> Each object type gets stored in a separate table?

In my case yes but that is because the underlying data represents
known entities and was actually originally in Postgres and then
exported to hideous inconsistent XML which I then convert/denormalise
back into JSON.

Do remember that SQLite does not require you to specify a type for
each column, nor does it care about the values in a column being
different types between rows.  That means I don't have to worry about
types, only the big picture top level of something being an object, a
list, or a scalar.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlWkg0MACgkQmOOfHg372QRTbQCfRobbh4uU1Eqf0SD9LJxABFYj
iv8AoKUvXNQEgGVvmPiZ/tQgHyU7Q0yL
=S7AM
-END PGP SIGNATURE-

[sqlite] Fwd: Usenix paper: Korean researchers invent sqlite WALDIO mode to circumvent redundant journaling by EXT4 on eMMC

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/14/2015 06:53 AM, Mikael wrote:
> Does SQLite do the same, what about SQLite do the same, patch?

The SQLite authors rejected checksumming SQLite database pages.  The
existing integrity check will only catch issues that happen in
sufficiently important metadata, but in general won't catch corruption.

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

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlWlKfgACgkQmOOfHg372QTW0gCgn5PVs7z9G6FEu5dG31hbRNy1
jAIAniXv0ebDjsCuroOrkwI7D4Wszwno
=sV74
-END PGP SIGNATURE-

[sqlite] Schema-less JSON SQLite DB?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/15/2015 08:22 AM, Sergej Jure?ko wrote:
> What do you guys think? Is it stupid, could it be improved?

I recommend looking at Mongodb  and Postgres first to see how they do
queries.  It would be better to be compatible with them where
practical, rather than being gratuitously different.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlWmkmwACgkQmOOfHg372QTHIACgi64Sm5nAmc4FyWrF6mimT5Ng
15sAoJJoY51QW/3QM/exRLJYHNtFKBse
=EgGt
-END PGP SIGNATURE-

[sqlite] why does SQLite support VARCHAR(X) when the doc says it ignores it?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/16/2015 02:46 PM, Hayden Livingston wrote:
> It seems that
> 
> CREATE TABLE A( something varchar(255) )
> 
> it just
> 
> CREATE TABLE A( something text )
> 
> Why have it at all?

Because other code and other humans look at the database too.
Consider it documentation of intent.  If it has no value to you, then
leave out the type.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlWoJyIACgkQmOOfHg372QTu+wCghdSY6eDO5zXezGV+/xRXHHDr
rWUAn1XuuG65S/48qjyfx9TP/lzXYS0g
=Fzkn
-END PGP SIGNATURE-

[sqlite] CSV excel import

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/30/2015 10:58 AM, Sylvain Pointeau wrote:
> is it possible? in a lot of cases, I cannot use sqlite (executable)
> because of the lack of a good CSV import. It would really great if
> this could be addressed.

Use the APSW shell:

  http://rogerbinns.github.io/apsw/shell.html

Behind the scenes it uses the Python CSV libraries which support a
number of dialects including excel (default) and excel-tab.

The APSW shell also supports an .autoimport command.  It automatically
works out dialects, separators and data types.  Here is the extended help:

 8< 
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.
 8< 

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlW6dDEACgkQmOOfHg372QRG5ACgt/OpLOPVZ40YQ4B2EWBf7yk0
HCUAniZLLUkPs7ac7cvCLb7Bn3zyNguC
=YpCt
-END PGP SIGNATURE-

[sqlite] Does length() use strlen() on blobs?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/01/2015 10:40 AM, David Barrett wrote:
> Hi!  If I define a column as BLOB type, will length() run strlen()
> on the data, or will it consult some kind of internal value to
> determine the length?

sqlite> select cast(x'424446' as text);
BDF
sqlite> select length(cast(x'4244460048' as text));
3
sqlite> select length(cast(x'4244460048' as blob));
5

Length of blob is number of bytes.  Length of text is number of
characters until first null byte.  I'll leave it up to you to figure
out what happens with multibyte characters.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlTzeyYACgkQmOOfHg372QSp5QCdEwDFmFfCcwEgR0J7D5uUzlAq
J1gAniGY88fNty/pv7qq2zUZ5RWxZKZs
=qUxe
-END PGP SIGNATURE-

[sqlite] Read Blob Returning bytes till NULL Terminated String

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/09/2015 10:31 AM, reddy ykk wrote:
> I have a requirement of reading an image data (BLOB) from a
> database. I could successfully read the length of the blob using 
> sqlite3_column_bytes() but when i try to read the entire blob
> using sqlite3_column_blob(), i am getting undesired results.

You will need to give more detailed information, so we know what you
are actually doing and what undesired results you are actually getting.

  http://catb.org/~esr/faqs/smart-questions.html

> I am getting the bytes till NULL terminated string is present. In
> my case, only 3 bytes of data are being returned by the method.

That indicates a misunderstanding.  You can only deal with blobs by
having a pointer and a length.  sqlite3_column_bytes() returns that
length, and sqlite3_column_blob() returns a pointer where the length
bytes are the data originally supplied.

> Is there any way that if we specify the length, the whole blob data
> is retrieved irrespective of the NULL bytes .

sqlite3_column_bytes/blob do fundamentally work, and do inspection of
the data since all bytes are valid in blobs.  If they didn't a heck of
a lot of people would have noticed by now.

Repeatedly mentioning NULL is a strong indication that whatever
non-SQLite routines you are using are the problem.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlT96gwACgkQmOOfHg372QQw+wCfU3la7vcCoezrsELJXmcQ7OjZ
/Z8Ani3+roNc0s5d+jKQAm46xLw/i1Q7
=2RtC
-END PGP SIGNATURE-

[sqlite] C++ ORM

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/09/2015 02:38 PM, Scott Robison wrote:
> Note: He's okay using SQLite more or less directly if he needs to,
> as he recognizes the lack of reflection in C++ might lead to a less
> manageable ORM. Still, never hurts to ask.

Don't use an ORM unless the ongoing benefits outweigh the drawbacks.

http://blogs.tedneward.com/2006/06/26/The%2BVietnam%2BOf%2BComputer%2BScience.aspx

http://stackoverflow.com/questions/404083/is-orm-still-the-vietnam-of-computer-science

A nice quote from the latter:

  Object-oriented is still object-oriented and Relational is
  still Set-oriented.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlT+NmIACgkQmOOfHg372QQ7CQCgqZcwZ0xQK0fJoG39t6OKDHBF
oIEAoInFomPfmw77XSMbNWWZndb9IKQc
=u2/6
-END PGP SIGNATURE-

[sqlite] Problem: you can delete a virtual table with open statements and then SEGV

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

This was originally reported as a problem in APSW (Python SQLite wrapper):

  https://github.com/rogerbinns/apsw/issues/184

The important bits are that a cursor (sqlite3_stmt) is created, and
step called once, but *not* reset or finalised:

  cursor.execute('SELECT * FROM TEMP.vt')

Then the the virtual table is dropped (different sqlite3_stmt):

  db.cursor().execute('DROP TABLE TEMP.vt')

Finally sqlite3_step is called on the sqlite3_stmt from earlier which
causes a segfault:

  rc = pModule->xNext(pCur->pVtabCursor);

This is because pModule is now null.

I can't think of any way I can realistically prevent this from
happening.  SQLite really should prevent it from happening by
disallowing the deletion of virtual tables with open statements.

I did the same steps using a real (non-virtual table) and on trying to
drop the table with open statements get:

  LockedError: database table is locked

Virtual tables should behave the same way.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlUQoswACgkQmOOfHg372QSlPACgsgh/75SnQWAzhwE+HXrM5If8
SxwAoI0UTwCYJS/yTzIm3/BD45+lCPG6
=TWTA
-END PGP SIGNATURE-

[sqlite] Problem: you can delete a virtual table with open statements and then SEGV

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/23/2015 04:33 PM, Roger Binns wrote:
> SQLite really should prevent it from happening by disallowing the
> deletion of virtual tables with open statements.

Thanks for fixing this.  I can confirm that the latest SQLite dev code
now gives the expected database table is locked error with the prior
test code.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlUR/w4ACgkQmOOfHg372QTFegCff/6Vl7NrJPRrFuJNgB8SLWk4
ol0AoJ9qYTMuP7xhEMyKkEYt4euC/CHj
=awey
-END PGP SIGNATURE-

[sqlite] Bug: sqlite3_memory_used/highwater truncate to 32 bits

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

The sqlite3_memory_used and highwater interfaces are defined to return
a 64 bit value.  They carefully call the 32 bit limited sqlite3_status
method and then cast to 64 bit.  Instead they should call
sqlite3_status64.

Reported indirectly via https://github.com/rogerbinns/apsw/issues/191

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlVOsbwACgkQmOOfHg372QSkLgCgkqMS2hWqLFU0246u8aqx+JtN
NkcAn3iDFhK5/+pq5v0n6vTprxZflaXZ
=4QMd
-END PGP SIGNATURE-

[sqlite] Issue: Inconsistency of sqlite3_dbstat_register declaration

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

There is a forward declaration of sqlite3_dbstat_register (currently
line 130871 in the 3.8.10.1 amalgamation):

  int sqlite3_dbstat_register(sqlite3*);

Later is the actual implementation (currently line 155838 in the
3.8.10.1 amalgamation):

  SQLITE_API int SQLITE_STDCALL sqlite3_dbstat_register(sqlite3 *db){

If SQLITE_API or SQLITE_STDCALL are set to anything then the
inconsistency can cause a compiler error.  In my case SQLITE_API is
defined as static.

Compilation then fails with gcc on Linux:

/space/apsw/sqlite3/sqlite3.c:155838:12: error: static declaration of
?sqlite3_dbstat_register? follows non-static declaration

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlVPrCoACgkQmOOfHg372QQEuwCgkCmhV/1ceig99me3QduAw7WL
qxUAniB4ZEF6KN52A/kAV/HZxVJ1kL9G
=686t
-END PGP SIGNATURE-

[sqlite] Issue: Inconsistency of sqlite3_dbstat_register declaration

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/11/2015 01:34 AM, Jan Nijtmans wrote:
> 2) If sqlite is not compiled with -DSQLITE_ENABLE_DBSTAT_VTAB=1,
> or the function is renamed (as it should) it wouldn't even work.

That was my scenario.  I just -DSQLITE_ENABLE_DBSTAT_VTAB and the code
didn't compile because of the inconsistency.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlVQvbgACgkQmOOfHg372QQ2eACdHWNbxVjmDGbzFtktgwADzwnH
rckAnRn30JWtYfBHzY0j5qG8tV8Kb8YO
=LfCW
-END PGP SIGNATURE-

[sqlite] Suggestion for .import improvement

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/11/2015 12:24 PM, tonyp at acm.org wrote:
> It?s a real nuisance to have to edit out the headers every time,
> especially when the files are very large and they do not load fast
> (or at all) into a text editor.  Plus, it?s semi-automatic.

I suggest you use a simpler way.  Create a temporary table and do the
.import into that.  You can then copy that table into your desired one:

  BEGIN
  CREATE TEMPORARY TABLE temptable .;
  .import log.csv temptable
  INSERT INTO desired SELECT * from temptable;
  COMMIT

You can explicitly delete the temp table, or it will automatically go
away when you close the connection.

The advantage of this approach is you can do processing on the
temporary table (eg convert nulls to empty string or vice versa, drop
header row), add a column (eg import date), have different columns
and/or names between the two (adjust the INSERT/SELECT statement) etc.
 This will all work from the shell, requires no programming, and will
import just as fast.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlVRVTYACgkQmOOfHg372QREYwCePcN1Z5RauOs7lo4jvtLNyx+t
018AnRJpES+JhBR5kM3+7m4Fa2p1I9t6
=oss6
-END PGP SIGNATURE-

[sqlite] xBestIndex() implementation question

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/13/2015 08:06 PM, Jilong Kuang wrote:
> Sure, that is one option. But I'm just wondering if inside the
> xBestIndex() we can get the value field, we can do a much better
> job to give the cardinality info.

Note that the value could be something other than a constant (eg an
expression like price > delivery_charge*0.9)

You are just helping SQLite pick the best index out of all the
possibilities, so as long as they compare sensibly it is fine.
SQLite's analyze command builds statistical information that is useful
(eg how many different values there are, how (un)evenly they are
distributed).  You could do something similar.

Or just follow Eric's suggestion :-)

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlVUGasACgkQmOOfHg372QSPcACgpmvvhwEEmj9+ccVgI+qTaw0B
y/YAn2WtrppfLn0PvDpa+n1loqFYgzQW
=vskS
-END PGP SIGNATURE-

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] 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] 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] "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] 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] 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] 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] 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] Solving the Sudoku with SQlite 3.8.3 trunk

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/02/14 23:15, big stone wrote:
> APSW looks indeed great for specialised installations.

The intention behind APSW is a Python wrapper for SQLite3.  It does
everything the SQLite way where applicable.  It advances with SQLite
meaning new versions of APSW do not support older releases of SQLite.

The one shipped with Python (sqlite3 aka pysqlite) tries to comply with
DBAPI and hence tries to be indistinguishable from the wrappers for the
other databases (postgres, mysql etc).  This includes faking functionality
that SQLite doesn't actually have.  It also supports very old versions of
SQLite - I believe it will work correctly with 3.0.0!

> If we wish to have SQLite + Python combination

You'll find the python sqlite community at
https://groups.google.com/forum/#!forum/python-sqlite

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

iEYEARECAAYFAlLz5AYACgkQmOOfHg372QRi7gCeNhVPXQ2W7BGA1k2bU9q7vcNq
KL0Anjw2vcScfVG6pIUHJFFaB8SFFgNk
=CYrk
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 28/02/14 16:54, Ashleigh wrote:
> I'm trying to view files from my iphone backup I'm not sure which
> program it is it says sqlite it is a black box like the windows command
> If any one knows a better way to read and understand the files I would
> greatly appreciate it I think the file ext. is a plist. Live, love &
> laugh.

In addition to those, read this:

  http://catb.org/~esr/faqs/smart-questions.html

Roger

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

iEYEARECAAYFAlMRNzgACgkQmOOfHg372QTAWACglwFrY79O3Z8U0Hz7xCv3B8VM
Xv8Anjm//0wqI5eBrJ08EIFB4/OdixcU
=DOm0
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiling SQLite on VxWorks 6.3 (DKM)

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 28/02/14 06:37, deltuo wrote:
> i compile sqlite 3.8.3 to vxworks 6.9,  i  first compile sqlite in dkm
> and get xx.a lib file, and then test it in vip project, but meet disk
> i/o error, can you help me ? thank you , my email is del...@126.com

  http://catb.org/~esr/faqs/smart-questions.html

Roger

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

iEUEARECAAYFAlMSPxoACgkQmOOfHg372QQXIQCfT7Sa9kempGXWTYs+L6aot98I
Rw8AmJ2dp9jR0bN0FThp98ab/ZygeD0=
=T0I3
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why would batched write operations NOT be faster than individual ones

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/03/14 03:00, Simon Slavin wrote:
> What the heck ?  Is this a particular implementation of RAID ...

The technical term is "write hole" and can occur at many RAID levels:

  http://www.raid-recovery-guide.com/raid5-write-hole.aspx

You can mitigate it by having a setup that doesn't have failures such as
using battery backup.

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

iEYEARECAAYFAlMUxBMACgkQmOOfHg372QTxewCgjuqKWh4m+pz2JRtQWznPA83o
YEcAnjDuMMULpMX14VVlLsQ4NmJbD6PA
=Dp0Y
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_MAX_PAGE_COUNT

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/03/14 10:59, Raheel Gupta wrote:
> If you point out to me the changes required I will do it and have it 
> reviewed.

The changes required are to update the test suites (there are several) to
hit/cross the current limit, to modify all relevant code including any
code that calls that code, to audit for overflows, to provide a new api
and tests for that.

And to ensure that stays maintained for the future lifetime of SQLite 3.

Roger

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

iEYEARECAAYFAlMXuWsACgkQmOOfHg372QRWcgCgmLmuv+d7Mxu5sfn9XFxAwx8N
xagAoIpbTOEPWtPqXRsQSBuIjgV4NYhl
=RMyF
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How good is pragma integrity_check

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 20/03/14 18:06, Simon Slavin wrote:
> All useful as far as SQLite itself goes, and better than nothing.
> Unfortunately, failing hard disks do weird things in weird orders.  And
> the interaction between the physical hard disk and the on-board cache
> makes it impossible to find out what's really on the disk.  There's
> only one way to check whether the whole file is readable from a disk:
> read the whole file from the disk.  Then do an integrity check on the
> copy you just made.

It also doesn't check the data, just the structure of the data.  There was
a feature request ticket for several years for checksums to at least catch
unexpected changes to the data itself:

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

Sadly it was rejected a few weeks ago without explanation.

Roger

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

iEYEARECAAYFAlMsmbQACgkQmOOfHg372QQy5gCfVa599WN9XWUB1Q8ABKYPJmQ6
QBwAnjuHzmK2oGfnhHDqdjlhD/5CoYe/
=1Y2C
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How good is pragma integrity_check

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 21/03/14 15:24, Simon Slavin wrote:
> Checksums stored with the page index lists,

SQLite already has the ability to carve out data on each page for other
uses.  For example the encryption extension uses this.

> Nevertheless, the basic SQLite engine is so efficient that any
> slow-down would be noticed and might cause complaints.

It couldn't be on by default for backwards compatibility reasons.  (WAL is
another example of that.)

So you don't have to use it.  Those of us who would rather proactively
know about data corruption (to the best of SQLite's ability to detect it)
are happy to take whatever hit there would be.  After all, fast queries on
corrupted data are pointless.

And as we see in messages on this list, finally spotting corruption long
after it first happened is very hard to recover from and hard to nail down
the cause for.

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

iEYEARECAAYFAlMvwWMACgkQmOOfHg372QSnOQCdEpBWBvcNsntkZ6WPvDs0yAju
fc0AoJzagj56DyoYrhmeE73rwHhe+D2f
=ZAfw
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT several rows

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/04/14 14:37, David King wrote:
> ... because of the author's opinions of the standard Python ways to
> require packages

As said author, the problem is that pip etc authors chose to make it
impossible to provide arguments to parts of the install process[1].  APSW
uses those arguments to control things like fetching SQLite source, which
extensions to enable and things affecting SQLite like if extension loading
is supported.

I could pick some defaults but they will always be wrong for some subset
of people.  It is also fairly hairy to make this all work from a build
perspective.  I'll likely end up hacking some defaults at some point.

Also for the record I haven't seen a patch from you to fix the issue in a
way you deem acceptable :-)

When I distributed Python applications in the past (eg BitPim) I bundled
everything up so Python being used was not visible (nor relevant) to the
user.  This approach worked fine on Windows, Linux and Mac.

[1] distutils - a standard part of python - is used under the hood which
has multiple subcommands each of which can be given flags.  pip etc
ultimately call into that.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlM93bkACgkQmOOfHg372QTx4wCgss8x7+Vymm7pZZDQ4X9+pLH5
D6IAoKF39KgDU95UesVtiFESluNGHv9s
=+r3t
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite dump makes wrong "CREATE VIEW" order

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 17/04/14 00:43, Tyumentsev Alexander wrote:
> Is it the user responsibility to follow all dependencies and recreate 
> "VIEW" tree ?

The dumping is happening in the order that the views were created.  This
approach generally works, but fails in your example where you created
another view of the same name.  (There are some other edge cases where you
could create circular links between views.)

I can't see any way of resolving your issue since there is no general SQL
parser which is what would have to be run to work out dependency order.

Roger



-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlNP58cACgkQmOOfHg372QT0RwCg5gXqYYmQ0Ws1LqJaFBhdpvwJ
dLAAn2+8hvf+nl9jTR45Z2TYQsT6mAlB
=APvL
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Most efficient storage for arrays

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 22/04/14 05:24, Neville Dastur wrote:
> I am looking for some advice on storing and searching data that comes
> from an external JSON source and needs to be stored on device in a
> Sqlite3 database.

Your data is from MongoDB :)  Note they do have an extended JSON to deal
with types like ObjectId, binary and dates:

http://docs.mongodb.org/manual/reference/mongodb-extended-json/#bson-data-types-and-associated-representations

> On device I need to be able to retrieve the entire “document” and
> display all the array values,

How much data will there be overall and how performant does all this have
to be?

> The options i see are to store the array fields as pipe separated
> values and then use LIKE “|%|” to search items.

That still requires scanning every record as you aren't doing a match from
the start of the value.

If you need performance then you need to normalize the data, or use a
database that supports lists like postgres.

If you need something for not too much data then just be simple - have a
single column which is the JSON text of the whole document.  You can still
use LIKE to find records of interest, deserialise back to JSON and do the
final filtering/querying/sorting there.

If you need a "proper" and convenient solution with SQLite then user
defined functions and virtual tables are a great way of hiding what you do
under the hood wrt to how the data is actually stored.  You'll have to
write them to understand your data shape and queries.

There was a now defunct project UnQL that was mixing together JSON like
data, SQL like queries and bit of SQLite.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlNW19UACgkQmOOfHg372QTNvgCgltU2vNzzVuRfTuPdc2+59VXS
h9sAnjya0jcj32aC3nYDA4Myv5x4tyxi
=fiQU
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Most efficient storage for arrays

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 22/04/14 15:00, Neville Dastur wrote:
> On 22 Apr 2014, at 21:58, Roger Binns <rog...@rogerbinns.com> wrote:
>> Your data is from MongoDB :)  Note they do have an extended JSON to
>> deal with types like ObjectId, binary and dates:
> Yes, it is. But I control the document structure and it does not have
> any types that can’t be converted easily after a pull over the net.

Does that mean you can make it individual fields instead of a list?  As
Petite Abeille pointed out you need to do SQL things the SQL way or you
will end up in a world of hurt.

>> How much data will there be overall and how performant does all this
>> have to be?
> 10,000s or records

Doesn't sound like much.

>> That still requires scanning every record as you aren't doing a match
>> from the start of the value.
> By this, you mean searching like this is “expensive”

You are searching within a column so an index can't be used - (eg looking
for || anywhere within that column).  That means every record has to
be examined.  Hence my queries about data size and performance requirements.

You will need to normalize if you want SQLite's performance and use
indexes so that columns contain exactly one value.

>> If you need something for not too much data then just be simple -
>> have a single column which is the JSON text of the whole document.
>> You can still use LIKE to find records of interest, deserialise back
>> to JSON and do the final filtering/querying/sorting there.
> 
> Not sure how this helps find say  in array2 alone. Note I have
> changed example so that there is a field value that is the same in
> array1 and 2 { "_id" : ObjectId(“xx"), "name" :
> “Description”, “array1" : [ “”,”” ], “array2" : [
> “”,””,”” ], "tags" :
> [“searchword1”,”searchword2”,”searchword3"] }

You have a SQL table containing one column  which is that entire document
as a single JSON string.  In SQL you do SELECT json FROM table WHERE json
LIKE '%%'.  This will match the above as well as any record where 
is in the _id, description etc.

Then in your app code you deserialise the json and check the object for
your exact query.  ie you use SQL to make a first pass looking for
candidates, and use your own app specific code to narrow that down.

>> If you need a "proper" and convenient solution with SQLite then user 
>> defined functions and virtual tables are a great way of hiding what
>> you do under the hood wrt to how the data is actually stored.  You'll
>> have to write them to understand your data shape and queries.
> Not really an option as I don’t have that control over the sqlite lib
> compiled into the mobile app.

Really?  It is trivial on iOS, and not too hard on Android (need to use
the NDK).

There is no easy way out of this.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlNXEIoACgkQmOOfHg372QQWxgCbBREj/ZCFTmdbFf5fLQLAqkAt
NTUAn0TfMt3T9faOVbfgfGpzIoeg9ndY
=rkUB
-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 I know the python functions registered on SQLite ?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/05/14 09:34, big stone wrote:
> Is there a way to get the list of all the 'external' functions created
> in a  SQLite connexion ?

This is only known internally within SQLite and there is no way to get at
the information from the public API.

SQLite could provide the information as a virtual table or similar.  A
ticket was created 7 years ago asking for it, and closed 2 months ago by
the team with resolution "Rejected":

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

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlNr7TEACgkQmOOfHg372QSMMACgghhBC+ZEzgAInw3ZeFpCbMXh
ehoAnjfri69yzIO9V2DL4SZf7WQUPUZk
=cs4S
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pre-preparing querys

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 17/05/14 15:27, Richard Hipp wrote:
> The TCL interface for SQLite caches the N most recent prepared
> statements (where N defaults to 10 but is configurable) and reuses
> those prepared statements if the same queries are run again.  That
> approach seems to work well in practice.

It seems like most language wrappers for SQLite include some sort of
statement cache because it is generally useful.  It seems like the sort of
thing that would be helpful within the core of SQLite itself, or as an
officially supported extra extension.

There is a ticket including pointers to previous mailing list discussion:

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

Roger


-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlN6dfEACgkQmOOfHg372QQmcACfX46ZvXwKt/Y9DnBL0BRxH9QM
lFsAoLRh/hjZai4SC5ie/DCpd2+D3NA2
=1Ve9
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pre-preparing querys

[Roger Binns]

On 05/19/2014 02:26 PM, Simon Slavin wrote:

Are there any advantages to this other than increased speed ?


Nope.  However I've yet to see anyone complaining that SQLite is too 
fast and shouldn't be faster :-)


I believe that on average an app using SQLite will have a distribution 
where a few queries are used repeatedly and others are used rarely.  A 
statement cache helps the former and has no real effect on the latter.


To me it is telling that all these SQLite wrappers added statement 
caches.  There must have been a performance incentive for so many to do 
the extra work, including DRH himself for his TCL wrapper.


It is very difficult to get statement caches right.  They have to be 
thread safe, and they have to handle multiple statements correctly (eg 
"select 1; select 2").  They need to have bounded size.  They interact 
with authorizers.  They also have to have a copy of the SQL statement. 
SQLite already has to deal with all these issues, including keeping a 
copy of the statement so SQLite implementing the cache would save memory.


Roger

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

Re: [sqlite] Pre-preparing querys

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 19/05/14 21:09, James K. Lowden wrote:
> I took "statement cache" to mean that execution plans would persist 
> either past sqlite3_exec() or that many plan would be kept, in case 
> later useful, when sqlite3_step() recompiles according to passed 
> parameters.  I think that's unlikely to help much, and might hurt.

Statement cache means something front-ending sqlite3_prepare.  A simple
implementation would be updating sqlite3_finalize to stash the statement
in a (bounded) hash table keyed by the SQL.  Then in sqlite3_prepare look
in the hash table for the SQL and return the stashed statement on match.
(There are a few more subtleties.)

> You're talking about using one prepared statement repeatedly, which,
> if nothing else, is convenient.

Statement cache sizes seem to be between 10 and 100 entries in various
implementations I looked at.

> And the application can always maintain its "cache" of
> prepared-statement handles.

Ah, for the olden days :-)  Where exactly would this cache live?  What if
you are using several different libraries and modules that don't know
about each other?  What about various ORMs?  The olden days where the app
developer is responsible for every line of code in the app and they all
work together well, including cooperating over a SQLite statement cache
are a rarity now.

Ask these questions:

- - Should app/library developers using SQLite have to implement their own
caching mechanism?

- - Why did the majority of developers writing wrappers for SQLite spend the
extra effort to also implement caches?

BTW my answer for the second one is because statement preparation showed
up in profiling, both outside and inside SQLite.  The outside bit was
because of having to do UTF8 conversion from native string representation,
and inside because statement preparation takes a while - it involves
parsing, many memory allocations and lots of other fiddly stuff.

Roger


-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlN7rRMACgkQmOOfHg372QRVoQCgtNdWJ/LiD67W2O7sdVSGinbV
mvQAniz4mbJr3+8pzYj0siG5v+jjj+Ko
=SB5Q
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] warning for partial indexes with "X AND Y"?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 23/05/14 05:26, Török Edwin wrote:
> Would it be possible to show a message when someone creates a useless
> index

There was a ticket from two years requesting a "lint" mode to catch
various issues that keep cropping up over the years:

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

The SQLite team rejected it in March.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlN/2XsACgkQmOOfHg372QRtnACeOCNixsgztnpZSqk/4T4fWqZE
fN4An0t4mC2VQOXwXeCnV3qYrUTULHRx
=qk0x
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reading compressed database files

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 28/05/14 00:25, Hadashi, Rinat wrote:
> My databases are very big (almost 100 GB). I am looking for a
> compression solution.
> 
> Did anyone have an experience with reading a compressed database?

It would be helpful if you characterise your data and queries.

For example if the size is due to blobs, then careful ordering of columns,
or moving them to separate tables will likely be very useful.

You can get compression external to SQLite by using a compressing
filesystem like NTFS or btrfs (make sure to pick an appropriate page
size), or by internal compression with cerod:

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

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlOFpvsACgkQmOOfHg372QRSRACfcDqTprcD//n9yYXcGPl9yQfo
sTIAoLkIaQHR4JAwk1LbuRzCyQsx/5aN
=tYeT
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reading compressed database files

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 28/05/14 02:26, Hadashi, Rinat wrote:
> I have 13 tables, of which 2 are huge, 2 are medium and the rest are
> very small. My huge tables have 3 columns: numeric, numeric and varchar
> with millions of rows. I keep an index on the numeric columns.
> 
> Does that bring up any column ordering suggestion?

Showing the schema and slowest query will help.  Also what is the average
size of the varchar values?  What operating system and filesystem are you
using?

SQLite stores each row as each column sequentially encoded.  If for
example your varchar was around 32kb then to read two rows would require
seeking/reading about every 32kb, which is way less efficient than if it
was 10 bytes in size in which case multiple rows come back with each read.

You haven't mentioned what you tried already.  Good starters are running
vacuum after populating the database and determining the optimal page
size.  The latter will depend on your queries - eg a 64kb page size will
result in 64kb of i/o even if only one byte is needed from a page.  If you
use NTFS compression then it operates on units of 64kb so using a 64kb
page size would be optimal.

I recommend you have a deterministic repeatable representative set of data
and queries.  That way you try different settings like page size, file
system compression and operating system tuning (if applicable).

There isn't some secret magic wand that will suddenly make things faster -
instead you need to measure and tweak multiple places.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlOGMfgACgkQmOOfHg372QT9IACfVvhc1LWG4X2IFBC0rKKNnrdw
UFIAoNhtFdh1EZKEo3fx7Kj9bkdKJRW4
=02fs
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users