
The Design Of SQLite4 (work in progress) - dchest
http://www.sqlite.org/src4/doc/trunk/www/design.wiki
======
bane
Sqlite is really just one of those rare software projects that simply makes
the world a better place. No more thinking about file storage formats,
searching and indexing strategies, long term file readability (it's almost as
future proofed as ASCII), extensible, flexible, fast and all around awesome.

The number of projects where sqlite turned a difficult project from impossible
or very hard to doable is almost uncountable.

Here's a fun use case. Generate sqlite databases for each user, let them do
whatever they want in that database from whatever interface you are letting
them use. Store the sqlite file as a binary blob in a "real" database with
associated metadata for fast look up and retrieval, plus whatever other things
your database gives you (replication, redundancy etc.) And now you basically
get sand boxed work spaces for your users almost for free.

~~~
ahi
I am interested in more details/criticisms of the above use case. Better
approaches? or "Holy balls, that's idiocy!"

~~~
columbo
Hrm... an online application perhaps like autocad where you have
hundreds/thousands of bits of information for every user's "file"?

Instead of saving all that data in xml/json you could keep it in a database.
This would also allow you to release in versions of the main software by
keeping older users and their files on the previous version.

Even as I type this I start to see massive alarm bells but it was something
that came to mind.

Another option is an external application (like survey monkey) that publishes
a sqlite file that you could then use in your system to store data for that
external form without modifying your database... again much pain could be had
down this road.

~~~
bane
That's almost exactly the use case I've seen it used for. A custom modeling
software with collaboration features. Modellers built models in local client
software which stored the model as a SQlite database and u/l them to a central
MySQL database for distribution.

When changes were made to the model, a new one was simply uploaded and a flag
was set in the old one to take it off line (or was it only the newer versions
were available unless the user requested an older one...I can't remember to be
honest).

The metadata about the model gave details like the user who created it, dates
and times of upload, name of the model, various key words about the model etc.
"Replacing" an older version with a newer version just involved uploading the
new version into a new row in the database and copying over a dozen or so
meta-data fields.

Once the model user downloaded a model, it was as easy to work with on their
end with a variety of tools as new tooling simply had to be written to the
schema not to some bizarro binary format or parse some gargantuan text file
into a data structure. Plus elements in the model were already indexed in the
SQLite file so internal searches for model elements was trivial.

There simply wasn't a case where two users would be writing to the same SQLite
inner database, but dozens could read them without problem as that was all
handled by the outer database, and it preserved old versions of the models
without issue.

It also meant migrating to a better outer database meant only migrating a
fairly simple meta-data schema and extracting out the SQLite database then
just writing it all back into the new database. It took a few days to migrate
a few TB but a very short time to write all the migration scripts...an hour or
so.

The plus side is a modeler could take their model off-line with them and work
on them at their leisure, then upload them when they came back into work.

I was never personally involved in the project, but thought it was one of the
craziest MacGuyver-esque things I'd ever seen. But once I stopped thinking
about the SQLite inner databases as databases and more as just file blobs with
a convenient to work with standard format it all made sense. The outer
database was never responsible for searching/reading/writing the inner
databases, that all happened at the client end.

------
zacharyvoase
As long as SQLite4 preserves the level of testing we've seen in SQLite3, I’ll
be extremely happy: <http://www.sqlite.org/testing.html>

------
swah
Sidenote: this flat file organization
(<http://www.sqlite.org/src4/dir?name=src>) gives me a wonderful feel about
the design and size of the project, although it probably makes Java
programmers cringe.

------
gojomo
"The default built-in storage engine is a log-structured merge database. It is
very fast, faster than LevelDB, supports nested transactions, and stores all
content in a single disk file. Future versions of SQLite4 might also include a
built-in B-Tree storage engine. "

Faster than LevelDB, _and_ in a single file? Interesting.

~~~
swah
Yeah, also I thought that comparison was a little out of place...

~~~
Splenivore
In all fairness, the LevelDB guys made the comparison first:
<http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html>

Maybe outperforming LevelDB is a design goal for SQLite 4?

~~~
rozim
The leveldb page shows mixed results wrt sqlite3 - I would say that it is
generally faster that sqlite3 based on the results. There is no comparison to
sqlite4.

Most notably in large value operations, and also slightly in random reads,
sqlite3 wins. In in other benchmarks however, leveldb is faster.

The leveldb page notes that they perform poorly with large values due to the
increased I/O that is done. The relatively poor random read performance isn't
discussed but my guess is that it's due to having to fetch a compressed page
of data that contains values for other keys, thus leveldb is doing "more work"
than strictly necessary.

------
wvenable
The thing that really bugs me with SQLite3 is the inability to modify tables.
It's not much of a problem unless you have foreign key constraints and then
it's a nightmare. Ultimately I resorted to just recreating the entire database
from a script every time I had to make a change. If this is "fixed" in SQLite4
then I'm sold.

I'm surprised that there aren't any real binary integers stored anymore but I
can see how that would reduce complexity compared to how numbers were handled
in SQLite3.

~~~
rogerbinns
You can alter tables to add columns. Doing more complex modifications would
require SQLite internally to do pretty much what you do - recreating a new
database from existing content.

~~~
wvenable
I'd love if it just created a new table, moved the data over, and dropped the
original table _while maintaining the foreign key relationships_. That would
be perfect.

If you have foreign key relationships referencing the table, you can't drop
the table. You can't remove the foreign key relationships without modifying
the related table. But you can't modify that related table, you can only drop
it. But you can't drop that table because of other foreign key
relationships...

~~~
premchai21
But, last I checked, you could PRAGMA foreign_keys=off temporarily, which
would also inhibit the automatic handling of foreign key constraints in other
tables when the target table is renamed or dropped. Then you can create a new
table, populate it, drop the old one, rename the new one to the old name, and
turn foreign_keys back on, and the constraints will now target the new table.

~~~
wvenable
Does that actually work for dropping and renaming tables? I thought I tried
that.

~~~
rogerbinns
In any event you can get the foreign keys from sqlite_master, delete them
during the reorganisation and then put them back afterwards.

------
EzGraphs
SQLite3 is great. Had a few questions that were not directly addressed on the
linked page. 1) Will the SQL implementation be identical between SQL3 and
SQL4? 2) Does "I/O is direct to disk" indicate the same sort of locking that
exists in SQLite3 (the database is locked DML which makes its application as a
web server db somewhat limited).

My guess is yes to both...

------
cryptonector
I want all sorts of things in SQLite4, but let's start by saying that usign a
log-structured DB format is very welcomed news.

I'd also like the VFS to provide the page cache, so that we could use mmap(2)
windows instead of read(2) and pread(2), which should result in fewer data
copies and better performance.

Everything else I'd like to see in SQLite4 is icing on the cake.

------
spdegabrielle
I'm a big fan of SQLite3, and fossil-scm is my weapon of choice for solo
projects. '4 is looking good.

------
zvrba
> SQLite4 does all numeric computations using decimal arithmetic. SQLite4
> never uses C datatypes double or float (except in interface routines when
> converting between double and the internal decimal representation).

Is it only me who thinks this is a bad idea? Suddenly, what the DB computes
for you won't be bit-for-bit identical with what your C program would have
computed using IEEE arithmetic. Also, Goldberg has a number of slides showing
that (counterintuitively), extra precision and multiple roundings produce LESS
accurate results.

~~~
AnIrishDuck
Yeah, this section of the design document made me do a double-take. The
problems with this aren't just limited to the impedance mismatch; it also
seems that arithmetic will be a _ton_ slower due to lack of dedicated
hardware.

------
j_s
Nearly-on-topic warning: Android automatically deletes corrupt SQLite files
<http://stackoverflow.com/questions/7764943>

------
rogerbinns
Not enough of a change from the existing SQLite 3.

What I'd like to see most is trivial interoperability with JSON. Almost all
the code I write that goes near SQLite has a trip through JSON as well and it
gets tedious mapping between them. This also means being able to support
things like a value being a list which Postgres can do.

Other than that a way of doing offline mode and online synchronization
builtin. CouchDB tried to address this space, but wasn't practical to
integrate into most products.

~~~
gcp
This really sounds like the kind of thing that is perfectly doable outside the
SQLite 3 core.

~~~
rogerbinns
Which bit? Be able to do lists and a flexible schema would have to be part of
the core.

Being able to do synchronization can be done outside of the core, but is
fiddly and error prone. It is far better done as a fundamental part of the
database.

------
edwinyzh
Good news. Sqlite3 has already been great. It's also good to read that the API
will not be changed much, it's very important.

------
pdw
I wonder how this pluggable storage architecture relates to the
SQLite/BerkeleyDB hybrid that Oracle created a while back.

------
ArbitraryLimits
TL;DR All replacements for fopen() grow until they span multiple files.

~~~
dchest
Why multiple files?

 _The (default) on-disk database image is a single disk file with a well-
documented and stable file format, making the SQLite4 library suitable for use
as an application file format._

~~~
rogerbinns
Incidentally SQLite isn't actually a single file. The main database is.
Transactions in progress and the WAL are stored in other files. If you copied
just the main file at an inopportune time then it would be invalid. You have
to keep the main file and the journal/WAL together.

~~~
dchest
Journal is a temporary file created when writing into database, which you can
turn off.

<http://www.sqlite.org/pragma.html#pragma_journal_mode>

WAL mode is a different beast specifically not intended to be a replacement
for application format database, and it's not used by default.

So, no, SQLite is a single-file database. It provides more options than that,
but the statement is true.

~~~
rogerbinns
You can't turn off the journal unless you are happy to lose data in which case
don't bother with a database. See section 1.3 of
<http://www.sqlite.org/howtocorrupt.html>

~~~
dchest
There are many more reasons to use single-file databases other than safety of
file write operations.

TextEdit in OS X also creates a temporary file when it saves a document, and
then atomically renames it to the original filename; would you claim that RTF
document consists of two files in this case?

~~~
rogerbinns
Not even a close analogy as the text file is always valid.

More accurate would be changes being done by modifying blocks of the original
file as well as appends on the end so that it is not valid by itself, and
storing enough information to make it valid/undo in a second file.

