The Design Of SQLite4 (work in progress)
sqlite.org
sqlite.org
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.
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.
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.
Basically, unless you can (and willing to) write your own SQLite VFS that is designed to handle this safely, I wouldn't do it.
SQLite has one very limited write scenario: one writer at a time. Your real database isn't going to understand that. The real database will happily hand out the inner SQLite database to multiple connections. At best, you'll be converting SQLite's locking into a bunch of transaction failures for conflicting updates; at worst, you'll have a race condition where only the last of several edits gets persisted.
On top of that you have the disk churn associated with rewriting the database on every change. Suppose your SQLite database is 20 MB—every write to that means 20 MB from the real database to your application and back, which means 20 MB of disk thrashing too. Guess how many users need to do this before your database is spending all day rewriting essentially unchanged bytes. Depending on your storage backend, you can't even necessarily expect that the old ones are overwritten—with MVCC, the database may wind up just allocating new blocks, in which case you're creating a lot of garbage collection work and wasting a lot of space.
Every real database has a facility to let you isolate schemas from each other. In MySQL, you can create separate databases for each user; in PostgreSQL you can do that or just create separate schemas. There are better solutions to this problem that don't have the drawbacks identified above. It's a cute idea, but in practice I don't think it will work out at all well.
Postgres 9.2 will have JSON support as well, but lacking accompanying functions it will be of limited utility.
But it can often be tolerably handled with a row-level flag in the outer-database to handle "locking" the database if you are operating in a scanrio where that makes sense.
There are other scenarios where users simply aren't writing anything to the inner SQLite databases, but you are simply using it as a storage medium. Imagine getting a SQLite database of publicly accessible government data instead of a csv dump or some unparsable XML file. Some organizations have already caught on to the idea, the NTSB for example gives out MS-Access databases of preliminary crash investigations.
I just think it's a crazy fun hacker-style idea that I've seen from time-to-time as a replacement for even harder to work with custom binary file formats stuffed into a relational database (don't ask, there are oddball projects where that kind of thing gets specified -- like certain high-end modeling systems where the user might check out a model to work on in isolation of the parent system, the data might be stored as SQLite databases instead of some proprietary bit bucket, and then uploaded back to the server in total where the model data is yanked out for server side processing).
The upside is that you can replace two sets of tools (relational tools and custom binary tooling) for working with data with one set of tools (just the relational) which ends up clarifying things quite a bit.
The above scenario, where isolating the user's datapool so that they can take it offline, do work, then check it back in to the system is almost absurdly simple.
But honestly, there's a very very limited number of use-cases where this makes sense and doing something more sane makes much more sense -- but when it works, it's ends up being one of those "so stupid it's brilliant" things.
You do that and you're just trading one race condition for another.
> There are other scenarios where users simply aren't writing anything to the inner SQLite databases, but you are simply using it as a storage medium.
It's a pretty labor-intensive method. Many databases have other ways of introducing unstructured data that aren't going to murder performance. PostgreSQL's hstore, for example.
> the user might check out a model to work on in isolation of the parent system, the data might be stored as SQLite databases instead of some proprietary bit bucket, and then uploaded back to the server in total where the model data is yanked out for server side processing
I have a friend who has used SQLite as an interchange format. It worked reasonably well, but he also didn't have to worry much about schema versions. Still, you can do that without making your backend pay the penalty.
> The above scenario, where isolating the user's datapool so that they can take it offline, do work, then check it back in to the system is almost absurdly simple.
It's still more work than using a file system for no advantage. Just having it in the database doesn't necessarily constitute a win—most DBAs would suggest you weigh the filesystem alternative against dumping files into BLOBs in the database before deciding which way to go.
There will always be exceptions to any hard and fast rule, but I don't think the ones you've described above warrant this design.
SELECT pk, sqlite, version FROM foo;
-- later
UPDATE foo SET sqlite='myblob', version=43 WHERE pk = whatever and version = 42;
-- if zero row update, there's a conflict
Terrible design though.
Purely a thought experiment but I'm wondering if a Postgres SQLite data type (similar to the JSON data type) could be workable.
The advantage to having a SQLite datatype would be that you could do the kinds of things you can do with the XML data type (extracting information in the outer layer for querying, indexing or simply ensuring validity) but you're still going to thrash the disk with every little change. And once you've committed to Postgres, you may as well use its own solution to this problem, schemas.
No easier format than just reusing SQLite.
Not to say that SQLite wouldn't offer lots of advantages, but I'm not sure I'd be in a huge rush to start packing all my game assets into a db unless I had some pretty convincing wins. I'd be much more likely to stick w/ the 380k in development and then pack into contiguous level binfiles before shipping. ( I'm totally armchair coding though, I admit :) )
1. One per user - in home directory. Check.
2. Real database with metadata - filesystem. Check.
3. Replication - filesystem (block replication). Check.
4. Redundancy - filesystem (RAID). Check.
So technically it already does that without any need for further abstraction. You can use UNIX perms to set it read only for all and read-write for user, then you can share it :)
For reference, I've built a HUGE amount of stuff with sqlite3 - you are right it does make the world a better place! We use it for:
1. unit test back end for NHibernate as it's fast and in memory.
2. issue/tickets/wiki (trac)
3. data processing.
4. archival datasets.
5. general swiss army knife when you can't be bothered to do it in SSIS.
Faster than LevelDB, and in a single file? Interesting.
Maybe outperforming LevelDB is a design goal for SQLite 4?
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.
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.
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...
My guess is yes to both...
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.
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.
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.
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.
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.
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.
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?
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.