7 ms·

The Design Of SQLite4 (work in progress)

169 points by dchest 14y ago

spdegabrielle 14y ago
I'm a big fan of SQLite3, and fossil-scm is my weapon of choice for solo projects. '4 is looking good.
EzGraphs 14y ago
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...
gojomo 14y ago
"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 14y ago
  Yeah, also I thought that comparison was a little out of place...
  - Splenivore 14y ago
    In all fairness, the LevelDB guys made the comparison first: http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html Maybe outperforming LevelDB is a design goal for SQLite 4?
    - rozim 14y ago
      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 14y ago
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 14y ago
  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 14y ago
    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 14y ago
      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 14y ago
        Does that actually work for dropping and renaming tables? I thought I tried that.
bane 14y ago
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 14y ago
  I am interested in more details/criticisms of the above use case. Better approaches? or "Holy balls, that's idiocy!"
  - simcop2387 14y ago
    I would hope it would be simpler to manage via some kind of clustered filesystem instead nested databases.
  - mark_story 14y ago
    I really hope you never have to do a schema migration. That's going to be hell with your matryoshka database.
  - columbo 14y ago
    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.
rogerbinns 14y ago
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 14y ago
  This really sounds like the kind of thing that is perfectly doable outside the SQLite 3 core.
  - rogerbinns 14y ago
    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.
ArbitraryLimits 14y ago
TL;DR All replacements for fopen() grow until they span multiple files.
- dchest 14y ago
  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 14y ago
    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 14y ago
      Journal is a temporary file created when writing into database, which you can turn off. http://www.sqlite.org/pragma.html#pragma_journal_mode 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 14y ago
        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 http://www.sqlite.org/howtocorrupt.html
        dchest 14y ago
        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?
zacharyvoase 14y ago
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 http://www.sqlite.org/testing.html
pdw 14y ago
I wonder how this pluggable storage architecture relates to the SQLite/BerkeleyDB hybrid that Oracle created a while back.
swah 14y ago
Sidenote: this flat file organization (http://www.sqlite.org/src4/dir?name=src 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.
edwinyzh 14y ago
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.
zvrba 14y ago
> 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 14y ago
  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 14y ago
Nearly-on-topic warning: Android automatically deletes corrupt SQLite files http://stackoverflow.com/questions/7764943 http://stackoverflow.com/questions/7764943
cryptonector 14y ago
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.

169 points by dchest 14y ago

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

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

gojomo 14y ago

"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 14y ago
Yeah, also I thought that comparison was a little out of place...
- Splenivore 14y ago
  In all fairness, the LevelDB guys made the comparison first: http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html Maybe outperforming LevelDB is a design goal for SQLite 4?
  - rozim 14y ago
    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 14y ago

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 14y ago
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 14y ago
  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 14y ago
    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 14y ago
      Does that actually work for dropping and renaming tables? I thought I tried that.

bane 14y ago

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 14y ago
I am interested in more details/criticisms of the above use case. Better approaches? or "Holy balls, that's idiocy!"
- simcop2387 14y ago
  I would hope it would be simpler to manage via some kind of clustered filesystem instead nested databases.
- mark_story 14y ago
  I really hope you never have to do a schema migration. That's going to be hell with your matryoshka database.
- columbo 14y ago
  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.

rogerbinns 14y ago

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 14y ago
This really sounds like the kind of thing that is perfectly doable outside the SQLite 3 core.
- rogerbinns 14y ago
  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.

ArbitraryLimits 14y ago

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

dchest 14y ago
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 14y ago
  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 14y ago
    Journal is a temporary file created when writing into database, which you can turn off. http://www.sqlite.org/pragma.html#pragma_journal_mode 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 14y ago
      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 http://www.sqlite.org/howtocorrupt.html
      - dchest 14y ago
        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?

zacharyvoase 14y ago

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 http://www.sqlite.org/testing.html

pdw 14y ago

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

swah 14y ago

Sidenote: this flat file organization (http://www.sqlite.org/src4/dir?name=src 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.

edwinyzh 14y ago

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.

zvrba 14y ago

> 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 14y ago
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 14y ago

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

cryptonector 14y ago

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.