
SQLite Release 3.8.11.1 - billK
https://www.sqlite.org/releaselog/3_8_11_1.html
======
harperlee
SQLite should be the default way someone manages information on a binary data
file, as opposed to just inventing your format from scratch.

I remember on uni when we started defining our own formats, to later on learn
about what goes inside a DBMS, and thinking, "Why on earth would I ever do the
first thing again?" (not that there's no space for it, but it's not so big).

Also, when I've found a couple of times that a file that I thought had a
proprietary binary format was a SQLite file, it was bliss. So much easier to
navigate, inspect and work with that a huge XML file or a plain text one.

~~~
batbomb
I think this is somewhat incomplete information, and I say this as a SQLite
lover. It depends on your type of data, but both HDF5 and SQLite are more than
adequate formats for data, and you should be using one of them.

I think HDF5 is actually a better format for long term data management, but I
think SQLite is a easier to use in everyday life. There's also Parquet and
Avro for these as well.

High-Energy physics also has the "code is the documentation" clusterfuck known
as ROOT files.

~~~
chacham15
This again misses the point. The point is that you should use the right tool
for the job. If your application needs a particular format for whatever
reason, use that format. For example, could you imagine trying to encode a
video file in a sqlite database? Similarly, would you use a sqlite file for a
plaintext document (like c++) or a binary executable? To give a more custom
example: I was working on a project which tries to build a database of
solutions to games (like Connect 4). Connect 4 has such a large search state
that using a database is terrible because it doesnt meet the requirements of
really fast lookups (with great caching), small size, and fast parallel
writing that the solver needs.

------
shocks
Relevant change log entry:

> Miscellaneous micro-optimizations result in 22.3% more work for the same
> number of CPU cycles relative to the previous release. SQLite now runs twice
> as fast as version 3.8.0 and three times as fast as version 3.3.9. (Measured
> using cachegrind on the speedtest1.c workload on Ubuntu 14.04 x64 with gcc
> 4.8.2 and -Os. Your performance may vary.)

------
eblume
SQLite is amazing. It is a tech that no programmer should be unfamiliar with.
It takes at most a day or two of playing with it to learn all of its various
quirks and tricks, is easy to bake in to any project regardless of language,
and performs like mad.

~~~
daheza
Any particular resources you recommend to learn it?

~~~
asveikau
sqlite.org's documentation for the C api [1] and its explanation of what kind
of SQL it supports [2] are both very good.

[1] [http://sqlite.org/cintro.html](http://sqlite.org/cintro.html)

[2] [http://sqlite.org/lang.html](http://sqlite.org/lang.html)

~~~
agumonkey
If I may, python sqlite interface is pretty great too.

[https://docs.python.org/3.4/library/sqlite3.html#sqlite-
and-...](https://docs.python.org/3.4/library/sqlite3.html#sqlite-and-python-
types)

~~~
keyneus
The sqlite3 module that comes with python is terrible when it comes to errors.
For example, if OperationalError is raised, it could be any of the following
SQLite errors:

SQLITE_ERROR, SQLITE_PERM, SQLITE_ABORT, SQLITE_BUSY, SQLITE_LOCKED,
SQLITE_READONLY, SQLITE_INTERRUPT, SQLITE_IOERR, SQLITE_FULL, SQLITE_CANTOPEN,
SQLITE_PROTOCOL, SQLITE_EMPTY, SQLITE_SCHEMA

How do you know which? The only thing you can do is convert the exception to a
string and compare error messages. That's absurd.

How about IntegrityError? That's either SQLITE_CONSTRAINT or SQLITE_MISMATCH.
Which one? Compare strings again.

If you need to use SQLite with python, apsw actually converts errors to
discrete exceptions.

~~~
rogerbinns
(I am the APSW author). The reason why the sqlite3 module (aka pysqlite)
behaves the way it does is because of DBAPI (Python standard database API).
That tries to make all databases look and behave the same. pysqlite could do
more - eg the SQLite error code could be an attribute on the exception, but
the code base is very rarely updated.

Wanting a Python wrapper for SQLite that celebrated SQLite's features,
functionality and semantics rather than paper them over is why I started APSW
a decade ago. It also has considerably better testing and diagnostics, and is
thread safe.

[http://rogerbinns.github.io/apsw/pysqlite.html](http://rogerbinns.github.io/apsw/pysqlite.html)

~~~
coleifer
Thank you for your work on APSW, it's a fantastic library. I'm the author of
the peewee ORM[1] and I've included an APSW backend[2] for folks who want to
use peewee with APSW. Being able to write extension modules in Python is such
a cool feature! I also made a demo that exposes Redis as a SQLite table[3].

[1] [http://docs.peewee-orm.com/](http://docs.peewee-orm.com/)

[2] [http://docs.peewee-
orm.com/en/latest/peewee/playhouse.html#a...](http://docs.peewee-
orm.com/en/latest/peewee/playhouse.html#apsw)

[3]
[https://github.com/coleifer/peewee/blob/master/examples/redi...](https://github.com/coleifer/peewee/blob/master/examples/redis_vtable/redis_vtab.py)

------
gtrubetskoy
When I was tinkering with [http://thredis.org](http://thredis.org) I spent a
lot of time looking into SQLite internals, and it is by far the most polished,
comment-rich code base that I have ever seen, it reads like a fun book.
Especially if you're interested in low-lever relational db stuff - how SQL
statements are processed, data is stored, B-Trees, etc. It's better than any
book out there (and such books tend to be very expensive).

------
coleifer
I wrote a post a while back singing the praises of SQLite. Some of you may be
interested:

[http://charlesleifer.com/blog/sqlite-small-fast-reliable-
cho...](http://charlesleifer.com/blog/sqlite-small-fast-reliable-choose-any-
three-/)

~~~
joshbaptiste
Great, write up convinced me to use Sqlite instead of Mysql for a simple in-
house webpage.

------
applecore
I'd like to see the same speed measurements done for mobile. A 64-bit Linux
desktop isn't representative of the #1 use case for SQLite as a mobile
database replacement for Core Data.

~~~
thomaslutz
Why should it not be similar faster on mobile?

~~~
em3rgent0rdr
No only different instruction set, but probably drastically different cache
hierarchy and branch predictors. The tool cachegrind which they are using is
simulating a program's effect on the cache and branch predictor, which will
likely both be dramatically smaller in embedded devices vs modern server CPUs,
so the results may be very misleading to real world applications for sqite.

~~~
pcwalton
Also multicore memory hierarchies are very different on ARM -- e.g. atomics
are much more expensive there in my experience.

------
PythonicAlpha
SQLite is one of the rare examples of very elaborate implemented software that
is very good maintained, too.

One of the best pieces of free software around.

Where else do you find software, that was very good implemented from the
beginning and is so carefully enhanced and optimized? (And not only for new
"features") -- I remember no official available example.

~~~
arandomdude
And SQLite is also one of the most tested pieces of software in the world:
[https://www.sqlite.org/testing.html](https://www.sqlite.org/testing.html)

~~~
huu
SQLite is the ultimate blackbox. Reliability beyond compare

------
wxs
We use SQLite extensively in Minuum and I couldn't be happier. It's in all our
critical real-time code paths that bring up candidates while you're typing.
Because we're on SQLite our predictions keep getting faster for free!

Faster predictions mean we can handle more types of errors (more missed
characters, more complex typos) in the same time budget. Thanks SQLite, you
guys are awesome.

------
billK
The change in title hides the main point (~2x speed increase) and makes it
appear as a simple bugfix release.

------
jmnicolas
You would think with such a performances boost they would have put it first in
the change-log, but no it's hidden at the end.

One thing for sure, they never worked for AMD or Nvidia marketing department
;-)

------
NelsonMinar
One of the great things about SQLite is its incredibly comprehensive automated
test suite. That allows aggressive optimization without fear you're breaking
something.

------
shredwheat
I still remember when Firefox switched to using SQLite for most of its
internal data. It felt like it took 3 or 4 releases to work out the
concurrency and blocking issues. I remember back in the day it had lots to do
with fsync.

Has most of that been worked out between SQLite and newer Linux kernels, or is
that a pit waiting for other applications that go the same route?

------
coleifer
I wrote up a quick blog post describing how to build the new FTS5 search
extension, which was included in the 3.8.11.1 release:

[http://charlesleifer.com/blog/building-the-sqlite-
fts5-searc...](http://charlesleifer.com/blog/building-the-sqlite-fts5-search-
extension/)

------
fiveoak
A little off topic, but is there any benchmarking software to compare embedded
databases? For instance SQLite vs H2.

------
coleifer
Anybody heard anything about SQLite4?

