7 ms·

SQLite: The art of keeping it simple

114 points by yarapavan 10y ago

yarapavan 10y ago
http://sqlite.org/src4/doc/trunk/www/design.wiki http://sqlite.org/src4/doc/trunk/www/design.wiki offers a good overview of SQLite deisgn, for those who are interested.
- sargun 10y ago
  I thought SQLite4 was dead in the water? There have been no contributions since late 2015. Is there still a plan to maintain it?
  - coleifer 10y ago
    I like to think of it as a thought experiment with some working code attached.
- ronjouch 10y ago
  > "SQLite4 is an alternative, not a replacement, for SQLite3. SQLite3 is not going away. SQLite3 and SQLite4 will be supported in parallel. The SQLite3 legacy will not be abandoned. SQLite3 will continue to be maintained and improved. But designers of new systems will now have the option to select SQLite4 instead of SQLite3 if desired." In addition to commitment, that's what I call clarity by repetition / reformulation :) . Good to see a writer handling worries about such backward-compatibility-breaking changes in the absolutelytotallymostest explicit way.
  - jordanlev 10y ago
    I listened to a podcast interview with Richard Hipp (the main SQLite developer) recently: http://5by5.tv/changelog/201 http://5by5.tv/changelog/201 It was really good. One of the things he mentioned is how they're planning to support SQLite until 2050 because of an agreement with Airbus (apparently that's the life of one of their recent plane models, and SQLite is used for some aspect of the software). Crazy!
  - dfox 10y ago
    I remember seeing mostly same paragraph 10 years ago, only with 3->2 and 4->3 :)
  - vic20forever 10y ago
    She knows her audience ;)
- coleifer 10y ago
  sqlite4 is completely different from sqlite3. The design is interesting in its own right but it's for a completely different product.
projectramo 10y ago
The link is broken at the moment. I really want to read it to learn the answer to the following: why isn't sqlite able to handle full scale loads? Why do I have to swap it out? Can't someone change the implementation details so it can work at scale and leave the interface the same? Where is sqlheavy?
- batbomb 10y ago
  SQLite has a basic optimizer. That's basically it IMO. You can already do some things like use BDB backend if you want better transaction performance.
- veli_joza 10y ago
  Here's the cached link, but there are no such answers within this text. This just measures some arbitrary metrics on sqlite codebase. http://webcache.googleusercontent.com/search?q=cache:21EcxYD03cUJ:www.codergears.com/Blog/%3Fp%3D2392+ http://webcache.googleusercontent.com/search?q=cache:21EcxYD... Some of advice within should be taken with a grain of salt. Exiting function as early as possible might be OK for you, but it goes against some coding standards that dictate only single return statement per function.
- sirtaj 10y ago
  sqlheavy is available, it's called postgresql. But you could go halfway and use something like libmysqld.
- acqq 10y ago
  > why isn't sqlite able to handle full scale loads? The goal of sqlite is to be easily embeddable: that means not too much code, not too much resources used. It serves that purpose well: it's widely used in a lot of programs where you never even thought that they could have it. https://www.sqlite.org/mostdeployed.html https://www.sqlite.org/mostdeployed.html So the goal was never to suit your "full scale loads" the way you probably define them. But I don't know what you actually understand under the "full scale loads" and once you state what you would exactly expect to achieve it can be discussed more. Until then, simply stated, there is a reason why the big server software is big and resource hungry, unlike sqlite.
IshKebab 10y ago
Is this a joke? > No extra comments in the body. Yeay no comments! > The function body is well indented. It clearly isn't. There's no whitespace to separate the lines making it hard to read, and the if bodies are on the same line as the if statements which is also hard to read.
- marlag 10y ago
  I love the indentation. Much easier to read than a bunch of nested ifs and whiles and good to see a lack of outdated comments inside the method body. So a line-by-line read of that code seemed quite enjoyable to me.
orf 10y ago
Cached: https://webcache.googleusercontent.com/search?q=cache:http://www.jarchitect.com/Blog/?p=2392 https://webcache.googleusercontent.com/search?q=cache:http:/... The post says the first code snippet's variable naming is easy to understand. Really? To me it's unreadable despite ticking all the best practice boxes. I mean there are no comments and all the variable names seem to be as short as possible, it's hard to follow exactly what and why it's doing things to me at least.
- Mahn 10y ago
  * No comments * No line jumps * Cryptic variable names * Magic numbers * Multiple return points in the middle of the function Yeah, this code might be doing good things, but I wouldn't be thrilled to inherit it in a project. I mean it's not bad code per se, we all have written things like this; it's just not something to point at for exemplary code either.
  - tenken 10y ago
    The only numbers I see are 1 and 0. Surely, you don't mean we should redefine 1 and 0!? :P Those numbers are hardly magic. The function comment header describes the purpose of the major variables used in the function. While the variables names may seem cryptic, they appear of a consistant format (within the function) so I bet the larger codebase is equally consistent in their usage -- and adapting to the codebases convention would be made easier.
    - e12e 10y ago
      It's a little compact for my taste (eg: the mulitple early returns on error, I think I'd like a blank line after a return, to make it stand out a bit more -- but I'm not sure. I don't write C on a daily basis). The only other thing that strikes me is the convoluted calculation of nBytes, from (sizeof(char * ) + sizeof(i16) + 1) multiplied by the n-argument for resize. Granted nBytes is just used for the custom malloc-call, but I get a little scared with that and the naked mix'n'match of sizeof's in there. It seems like that (and maybe a few of the other things) could've been DEFINE-d in a header, and or factored out to easily in-line-able functions -- that might have provided an (even) clearer picture of what's going on.
bntyhntr 10y ago
I recently hacked at sqlite to support some features I wanted (being able to retrieve same named columns from different tables in jdbc result sets and implementing some mysql functions) and it was not fun. I guess maybe it says something that as someone with very limited c exposure I was able to get the column hack working in a night, but honestly following the code around was a chore. Lots of conditionals in a single function meant jumping back and forth a lot. Also, as a primarily java programmer, I find what c programmers think is a descriptive variable name is a lot different than what I think is a descriptive variable name, but that's a personal issue.
swah 10y ago
Since no one mentioned yet, the (lack of) license: "The source code files for other SQL database engines typically begin with a comment describing your license rights to view and copy that file. The SQLite source code contains no license since it is not governed by copyright. Instead of a license, the SQLite source code offers a blessing: May you do good and not evil May you find forgiveness for yourself and forgive others May you share freely, never taking more than you give."
- tlrobinson 10y ago
  I wonder if they've ever run into the problem Douglas Crockford had with IBM requesting he clarify the "The Software shall be used for Good, not Evil" clause he added to the MIT license for JSLint.... https://www.youtube.com/watch?v=-C-JoyNuQJs&feature=player_detailpage#t=2480s https://www.youtube.com/watch?v=-C-JoyNuQJs&feature=player_d...
  - mayoff 10y ago
    You can buy a SQLite license if you really want one: https://www.sqlite.org/copyright.html https://www.sqlite.org/copyright.html at which point you are free to ignore the blessing.
- coleifer 10y ago
  It's public domain, so no license necessary. The blessing is awesome though.
  - creshal 10y ago
    As discussed below, public domain isn't a concept in many legislations (copyright vs. authors' right e.g. – you can relinquish your exclusive right to copy, but not your authorship), so minimal licenses like CC0 are needed to really put your works under public-domain-or-equivalent worldwide.
sargun 10y ago
I think a testament to the quality of SQLite is its flexibility. I was given the task of building a distributed, HA databases that ran without human intervention. I was able to adapt SQLite to run on a different storage backend in the area of days as opposed to the weeks or more it would take with another system. In addition to this, I was even able to port SQLite to the Linux kernel: https://github.com/sargun/ksqlite https://github.com/sargun/ksqlite -- and IMHO, it's really neat that in <1000 lines of code I could adapt this database to run in kernel space.
- Something1234 10y ago
  Why would I want sqlite to run in the kernel? I'm asking out of genuine curiosity.
  - sargun 10y ago
    For fun. More seriously I was interested for two things: 1) Policy framework - every policy framework has its own wacky way of specifying matches, and actions. SQL turns out to be pretty good at this. 2) For some other projects I needed an in-kernel virtual machine. I needed something written in C, that had a no libc requirement. My choices were nekovm, and SQLite at the end. I decided to take out two birds with one stone.
pmarreck 10y ago
He's not as skilled as he thinks he is. And I can't even comment on the post, it returns a permissions error. Here was my comment: 1) Where's the unit test of this function? :) 2) The function could be purer. For example, sqlite3DbMallocZero could be passed in as an anonymous function. That way you could actually unit-test the out-of-memory condition that returns SQLITE_NOMEM_BKPT ... without actually being out-of-memory. ;)

114 points by yarapavan 10y ago

http://sqlite.org/src4/doc/trunk/www/design.wiki http://sqlite.org/src4/doc/trunk/www/design.wiki offers a good overview of SQLite deisgn, for those who are interested.

sargun 10y ago
I thought SQLite4 was dead in the water? There have been no contributions since late 2015. Is there still a plan to maintain it?
- coleifer 10y ago
  I like to think of it as a thought experiment with some working code attached.
ronjouch 10y ago
> "SQLite4 is an alternative, not a replacement, for SQLite3. SQLite3 is not going away. SQLite3 and SQLite4 will be supported in parallel. The SQLite3 legacy will not be abandoned. SQLite3 will continue to be maintained and improved. But designers of new systems will now have the option to select SQLite4 instead of SQLite3 if desired." In addition to commitment, that's what I call clarity by repetition / reformulation :) . Good to see a writer handling worries about such backward-compatibility-breaking changes in the absolutelytotallymostest explicit way.
- jordanlev 10y ago
  I listened to a podcast interview with Richard Hipp (the main SQLite developer) recently: http://5by5.tv/changelog/201 http://5by5.tv/changelog/201 It was really good. One of the things he mentioned is how they're planning to support SQLite until 2050 because of an agreement with Airbus (apparently that's the life of one of their recent plane models, and SQLite is used for some aspect of the software). Crazy!
- dfox 10y ago
  I remember seeing mostly same paragraph 10 years ago, only with 3->2 and 4->3 :)
- vic20forever 10y ago
  She knows her audience ;)
coleifer 10y ago
sqlite4 is completely different from sqlite3. The design is interesting in its own right but it's for a completely different product.

projectramo 10y ago

The link is broken at the moment. I really want to read it to learn the answer to the following: why isn't sqlite able to handle full scale loads? Why do I have to swap it out? Can't someone change the implementation details so it can work at scale and leave the interface the same? Where is sqlheavy?

batbomb 10y ago
SQLite has a basic optimizer. That's basically it IMO. You can already do some things like use BDB backend if you want better transaction performance.
veli_joza 10y ago
Here's the cached link, but there are no such answers within this text. This just measures some arbitrary metrics on sqlite codebase. http://webcache.googleusercontent.com/search?q=cache:21EcxYD03cUJ:www.codergears.com/Blog/%3Fp%3D2392+ http://webcache.googleusercontent.com/search?q=cache:21EcxYD... Some of advice within should be taken with a grain of salt. Exiting function as early as possible might be OK for you, but it goes against some coding standards that dictate only single return statement per function.
sirtaj 10y ago
sqlheavy is available, it's called postgresql. But you could go halfway and use something like libmysqld.
acqq 10y ago
> why isn't sqlite able to handle full scale loads? The goal of sqlite is to be easily embeddable: that means not too much code, not too much resources used. It serves that purpose well: it's widely used in a lot of programs where you never even thought that they could have it. https://www.sqlite.org/mostdeployed.html https://www.sqlite.org/mostdeployed.html So the goal was never to suit your "full scale loads" the way you probably define them. But I don't know what you actually understand under the "full scale loads" and once you state what you would exactly expect to achieve it can be discussed more. Until then, simply stated, there is a reason why the big server software is big and resource hungry, unlike sqlite.

IshKebab 10y ago

Is this a joke? > No extra comments in the body. Yeay no comments! > The function body is well indented. It clearly isn't. There's no whitespace to separate the lines making it hard to read, and the if bodies are on the same line as the if statements which is also hard to read.

marlag 10y ago
I love the indentation. Much easier to read than a bunch of nested ifs and whiles and good to see a lack of outdated comments inside the method body. So a line-by-line read of that code seemed quite enjoyable to me.

orf 10y ago

Cached: https://webcache.googleusercontent.com/search?q=cache:http://www.jarchitect.com/Blog/?p=2392 https://webcache.googleusercontent.com/search?q=cache:http:/... The post says the first code snippet's variable naming is easy to understand. Really? To me it's unreadable despite ticking all the best practice boxes. I mean there are no comments and all the variable names seem to be as short as possible, it's hard to follow exactly what and why it's doing things to me at least.

Mahn 10y ago
* No comments * No line jumps * Cryptic variable names * Magic numbers * Multiple return points in the middle of the function Yeah, this code might be doing good things, but I wouldn't be thrilled to inherit it in a project. I mean it's not bad code per se, we all have written things like this; it's just not something to point at for exemplary code either.
- tenken 10y ago
  The only numbers I see are 1 and 0. Surely, you don't mean we should redefine 1 and 0!? :P Those numbers are hardly magic. The function comment header describes the purpose of the major variables used in the function. While the variables names may seem cryptic, they appear of a consistant format (within the function) so I bet the larger codebase is equally consistent in their usage -- and adapting to the codebases convention would be made easier.
  - e12e 10y ago
    It's a little compact for my taste (eg: the mulitple early returns on error, I think I'd like a blank line after a return, to make it stand out a bit more -- but I'm not sure. I don't write C on a daily basis). The only other thing that strikes me is the convoluted calculation of nBytes, from (sizeof(char * ) + sizeof(i16) + 1) multiplied by the n-argument for resize. Granted nBytes is just used for the custom malloc-call, but I get a little scared with that and the naked mix'n'match of sizeof's in there. It seems like that (and maybe a few of the other things) could've been DEFINE-d in a header, and or factored out to easily in-line-able functions -- that might have provided an (even) clearer picture of what's going on.

bntyhntr 10y ago

I recently hacked at sqlite to support some features I wanted (being able to retrieve same named columns from different tables in jdbc result sets and implementing some mysql functions) and it was not fun. I guess maybe it says something that as someone with very limited c exposure I was able to get the column hack working in a night, but honestly following the code around was a chore. Lots of conditionals in a single function meant jumping back and forth a lot. Also, as a primarily java programmer, I find what c programmers think is a descriptive variable name is a lot different than what I think is a descriptive variable name, but that's a personal issue.

swah 10y ago

Since no one mentioned yet, the (lack of) license: "The source code files for other SQL database engines typically begin with a comment describing your license rights to view and copy that file. The SQLite source code contains no license since it is not governed by copyright. Instead of a license, the SQLite source code offers a blessing: May you do good and not evil May you find forgiveness for yourself and forgive others May you share freely, never taking more than you give."

tlrobinson 10y ago
I wonder if they've ever run into the problem Douglas Crockford had with IBM requesting he clarify the "The Software shall be used for Good, not Evil" clause he added to the MIT license for JSLint.... https://www.youtube.com/watch?v=-C-JoyNuQJs&feature=player_detailpage#t=2480s https://www.youtube.com/watch?v=-C-JoyNuQJs&feature=player_d...
- mayoff 10y ago
  You can buy a SQLite license if you really want one: https://www.sqlite.org/copyright.html https://www.sqlite.org/copyright.html at which point you are free to ignore the blessing.
coleifer 10y ago
It's public domain, so no license necessary. The blessing is awesome though.
- creshal 10y ago
  As discussed below, public domain isn't a concept in many legislations (copyright vs. authors' right e.g. – you can relinquish your exclusive right to copy, but not your authorship), so minimal licenses like CC0 are needed to really put your works under public-domain-or-equivalent worldwide.

sargun 10y ago

I think a testament to the quality of SQLite is its flexibility. I was given the task of building a distributed, HA databases that ran without human intervention. I was able to adapt SQLite to run on a different storage backend in the area of days as opposed to the weeks or more it would take with another system. In addition to this, I was even able to port SQLite to the Linux kernel: https://github.com/sargun/ksqlite https://github.com/sargun/ksqlite -- and IMHO, it's really neat that in <1000 lines of code I could adapt this database to run in kernel space.

Something1234 10y ago
Why would I want sqlite to run in the kernel? I'm asking out of genuine curiosity.
- sargun 10y ago
  For fun. More seriously I was interested for two things: 1) Policy framework - every policy framework has its own wacky way of specifying matches, and actions. SQL turns out to be pretty good at this. 2) For some other projects I needed an in-kernel virtual machine. I needed something written in C, that had a no libc requirement. My choices were nekovm, and SQLite at the end. I decided to take out two birds with one stone.

pmarreck 10y ago

He's not as skilled as he thinks he is. And I can't even comment on the post, it returns a permissions error. Here was my comment: 1) Where's the unit test of this function? :) 2) The function could be purer. For example, sqlite3DbMallocZero could be passed in as an anonymous function. That way you could actually unit-test the out-of-memory condition that returns SQLITE_NOMEM_BKPT ... without actually being out-of-memory. ;)