7 ms·

The SQLite Database File Format

144 points by s16h 12y ago

rckclmbr 12y ago
This is an odd, random thing to be on the front page. Did any event in particular happen to make this come up?
- RivieraKid 12y ago
  Yeah, these no-context submissions are annoying but common here.
- thibauts 12y ago
  Doesn't seem that odd to me. I love stumbling on things like that. Makes me learn a bit of something I wouldn't have learnt otherwise. I think this kind of knowledge is of common interest to so called "hackers" or people that like to know how things work.
- ushi 12y ago
  There was this submission[0] yesterday. Probably someone wanted to learn more about it and stumbled upon this amazing document. [0] https://news.ycombinator.com/item?id=8378894 https://news.ycombinator.com/item?id=8378894
  - dr_zoidberg 12y ago
    The format is amazing, however the document is not well written. You have to read about half of it to begin to understand how the format works. I had to read it whole for a project and it was really time consuming, though I don't think I could write it any better than it is right now. There were some blog posts that were far clearer to understand the file format, but unfortunately I lost track of them.
    - dunham 12y ago
      As a pedagogical exercise, to help me understand the design, I wrote some Go code to read a sqlite database and dump table contents. I remember having to peek at the source code to figure out a few things that weren't explained well in that document. I'd previously written code to read the lucene database format (python code that time), which was also a good learning experience. I ended up leveraging some of that knowledge for a work project. And I've also played around with reading/writing git archives.
      - SQLite 12y ago
        I'd like to improve the document so that reference to the SQLite source code is not required. Can you explain (perhaps via private email) what the file format document did not describe sufficiently for you to implement a reader in Go?
        dunham 12y ago
        It was over a year ago, so my memory is a little fuzzy. I do remember that near the end of the process I looked at the source code to answer a question or two. I'll skim over the docs again and see if I can remember what I was confused about.
    - ph_N 12y ago
      I've grown to like the SQLite documentation to the brink of adoration. It reads verbose but has a certain mathematical succinctness and completeness to it. Took me some time to get used to, and yes, you always have to read the entire paragraph. In the unlikely case that this is intentional it's genius: it doesn't let you get away with "what do I need to know to make this code compile/kinda-sorta-run", but almost forces you to at least notice the things you should know when using it (protecting you from dangerous half-knowledge). I'm not sure if this is perfect for file format documentation, but I would guess that a more "traditional" presentation would answer certain questions faster - such as "oh! this looks easy" / "this will take me weeks to work through", or "no, they don't seem to use RFC455692673 index optimization". However, for actually uisng the format for something productive, the time invested would be about the same. All in all, SQLite is a pleasure to work with, and that's something outstanding on its own.
  - ygra 12y ago
    Also worth a read: http://www.sqlite.org/testing.html http://www.sqlite.org/testing.html
- rubiquity 12y ago
  Welcome to HackerNews. The front page doesn't have to be plagued with flat/material design boostrap themes, Wired.com garbage and TechCrunch articles about startups that are written by the startup's founders. Databases are a ton of fun to read and talk about. If I ever have an event in my life that means I won't have to work anymore I'll spend the majority of my time studying databases. The next time you're cussing at Microsoft Word for being slow while you type out a paragraph, think about SQLite and how it manages to do sub-millisecond queries to something that is just a file on your file system. Wow!
whitten 12y ago
Is SQLite used as the internal database for some popular systems?
- petilon 12y ago
  Both iOS and Android use SQLite. That means billions of devices have SQLite on them. That makes SQLite the most popular implementation of SQL. See: https://developer.apple.com/library/mac/documentation/cocoa/conceptual/CoreData/Articles/cdPersistentStores.html https://developer.apple.com/library/mac/documentation/cocoa/... http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html http://developer.android.com/reference/android/database/sqli...
- dangoor 12y ago
  Firefox and Chrome both use SQLite for some of their data. It's a great package.
  - rplnt 12y ago
    Opera did too, also the new one does.
- qewrffewqwfqew 12y ago
  In case you've been living under a rock .. https://www.sqlite.org/mostdeployed.html https://www.sqlite.org/mostdeployed.html
  - erikb 12y ago
    Your choice of languages leaves room for improvement.
  - Zecc 12y ago
    That text needs an update. There's no mention of Android and Symbian is all but dead.
- cwt137 12y ago
  This podcast talks with the creator about some systems that have SQLite in it and the open source project. http://twit.tv/show/floss-weekly/26 http://twit.tv/show/floss-weekly/26
jpswade 12y ago
The problem with SQLite is that there's no official RFC, it's a pseudo database. When you're weighing up what to implement, it certainly helps when there is an agreed upon technical specification.
- erikb 12y ago
  This seems to be a valid comment, but I'm not sure I understand it. Can you show some examples from other databases? How are they doing it right?
  - personZ 12y ago
    I assume that they're referring to the great browser wars, where Web SQL was abandoned in favor of IndexedDB because the former was essentially a call out to use SQLite as it is. Many argued against a single implementation because it didn't have an underlying high level specification that would allow for other implementations.
    - jpswade 12y ago
      https://hacks.mozilla.org/2010/06/beyond-html5-database-apis-and-the-road-to-indexeddb/ https://hacks.mozilla.org/2010/06/beyond-html5-database-apis...
    - erikb 12y ago
      Why are you assuming I am referring to anything? I simply said I don't completely understand the parent comment and would like some examples. Maybe you intended to respond to another comment?
      - personZ 12y ago
        And I was obviously trying to address your question, speculating what jpswade was talking about. If you fail to get the basic context of a thread, maybe the internet isn't for you.
- billpg 12y ago
tscherno 12y ago
Also very interesting: http://www.sqlite.org/testing.html http://www.sqlite.org/testing.html
sauere 12y ago
http://howfuckedismydatabase.com/sqlite/ http://howfuckedismydatabase.com/sqlite/
- lazyant 12y ago
  by "concurrent users" the site means "concurrent users for writes" because for reads concurrent users is fine in sqlite.
jevinskie 12y ago
http://permalink.gmane.org/gmane.comp.db.sqlite.general/90549 http://permalink.gmane.org/gmane.comp.db.sqlite.general/9054... Apparently the latest and greatest SQLite is sometimes 50% faster than 3.7.17 released in July of last year. Impressive! I also love using SQLite for testing compiler optimization passes. Their TCL test suite coverage is quite good. Unfortunately, I think you have to splurge tens of thousands of dollars to get the C-based test suite. I believe you can only test full branch coverage with the C test suite.

144 points by s16h 12y ago

rckclmbr 12y ago

This is an odd, random thing to be on the front page. Did any event in particular happen to make this come up?

RivieraKid 12y ago
Yeah, these no-context submissions are annoying but common here.
thibauts 12y ago
Doesn't seem that odd to me. I love stumbling on things like that. Makes me learn a bit of something I wouldn't have learnt otherwise. I think this kind of knowledge is of common interest to so called "hackers" or people that like to know how things work.
ushi 12y ago
There was this submission[0] yesterday. Probably someone wanted to learn more about it and stumbled upon this amazing document. [0] https://news.ycombinator.com/item?id=8378894 https://news.ycombinator.com/item?id=8378894
- dr_zoidberg 12y ago
  The format is amazing, however the document is not well written. You have to read about half of it to begin to understand how the format works. I had to read it whole for a project and it was really time consuming, though I don't think I could write it any better than it is right now. There were some blog posts that were far clearer to understand the file format, but unfortunately I lost track of them.
  - dunham 12y ago
    As a pedagogical exercise, to help me understand the design, I wrote some Go code to read a sqlite database and dump table contents. I remember having to peek at the source code to figure out a few things that weren't explained well in that document. I'd previously written code to read the lucene database format (python code that time), which was also a good learning experience. I ended up leveraging some of that knowledge for a work project. And I've also played around with reading/writing git archives.
    - SQLite 12y ago
      I'd like to improve the document so that reference to the SQLite source code is not required. Can you explain (perhaps via private email) what the file format document did not describe sufficiently for you to implement a reader in Go?
      - dunham 12y ago
        It was over a year ago, so my memory is a little fuzzy. I do remember that near the end of the process I looked at the source code to answer a question or two. I'll skim over the docs again and see if I can remember what I was confused about.
  - ph_N 12y ago
    I've grown to like the SQLite documentation to the brink of adoration. It reads verbose but has a certain mathematical succinctness and completeness to it. Took me some time to get used to, and yes, you always have to read the entire paragraph. In the unlikely case that this is intentional it's genius: it doesn't let you get away with "what do I need to know to make this code compile/kinda-sorta-run", but almost forces you to at least notice the things you should know when using it (protecting you from dangerous half-knowledge). I'm not sure if this is perfect for file format documentation, but I would guess that a more "traditional" presentation would answer certain questions faster - such as "oh! this looks easy" / "this will take me weeks to work through", or "no, they don't seem to use RFC455692673 index optimization". However, for actually uisng the format for something productive, the time invested would be about the same. All in all, SQLite is a pleasure to work with, and that's something outstanding on its own.
- ygra 12y ago
  Also worth a read: http://www.sqlite.org/testing.html http://www.sqlite.org/testing.html
rubiquity 12y ago
Welcome to HackerNews. The front page doesn't have to be plagued with flat/material design boostrap themes, Wired.com garbage and TechCrunch articles about startups that are written by the startup's founders. Databases are a ton of fun to read and talk about. If I ever have an event in my life that means I won't have to work anymore I'll spend the majority of my time studying databases. The next time you're cussing at Microsoft Word for being slow while you type out a paragraph, think about SQLite and how it manages to do sub-millisecond queries to something that is just a file on your file system. Wow!

whitten 12y ago

Is SQLite used as the internal database for some popular systems?

petilon 12y ago
Both iOS and Android use SQLite. That means billions of devices have SQLite on them. That makes SQLite the most popular implementation of SQL. See: https://developer.apple.com/library/mac/documentation/cocoa/conceptual/CoreData/Articles/cdPersistentStores.html https://developer.apple.com/library/mac/documentation/cocoa/... http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html http://developer.android.com/reference/android/database/sqli...
dangoor 12y ago
Firefox and Chrome both use SQLite for some of their data. It's a great package.
- rplnt 12y ago
  Opera did too, also the new one does.
qewrffewqwfqew 12y ago
In case you've been living under a rock .. https://www.sqlite.org/mostdeployed.html https://www.sqlite.org/mostdeployed.html
- erikb 12y ago
  Your choice of languages leaves room for improvement.
- Zecc 12y ago
  That text needs an update. There's no mention of Android and Symbian is all but dead.
cwt137 12y ago
This podcast talks with the creator about some systems that have SQLite in it and the open source project. http://twit.tv/show/floss-weekly/26 http://twit.tv/show/floss-weekly/26

jpswade 12y ago

The problem with SQLite is that there's no official RFC, it's a pseudo database. When you're weighing up what to implement, it certainly helps when there is an agreed upon technical specification.

erikb 12y ago
This seems to be a valid comment, but I'm not sure I understand it. Can you show some examples from other databases? How are they doing it right?
- personZ 12y ago
  I assume that they're referring to the great browser wars, where Web SQL was abandoned in favor of IndexedDB because the former was essentially a call out to use SQLite as it is. Many argued against a single implementation because it didn't have an underlying high level specification that would allow for other implementations.
  - jpswade 12y ago
    https://hacks.mozilla.org/2010/06/beyond-html5-database-apis-and-the-road-to-indexeddb/ https://hacks.mozilla.org/2010/06/beyond-html5-database-apis...
  - erikb 12y ago
    Why are you assuming I am referring to anything? I simply said I don't completely understand the parent comment and would like some examples. Maybe you intended to respond to another comment?
    - personZ 12y ago
      And I was obviously trying to address your question, speculating what jpswade was talking about. If you fail to get the basic context of a thread, maybe the internet isn't for you.
billpg 12y ago

tscherno 12y ago

Also very interesting: http://www.sqlite.org/testing.html http://www.sqlite.org/testing.html

sauere 12y ago

http://howfuckedismydatabase.com/sqlite/ http://howfuckedismydatabase.com/sqlite/

lazyant 12y ago
by "concurrent users" the site means "concurrent users for writes" because for reads concurrent users is fine in sqlite.

jevinskie 12y ago

http://permalink.gmane.org/gmane.comp.db.sqlite.general/90549 http://permalink.gmane.org/gmane.comp.db.sqlite.general/9054... Apparently the latest and greatest SQLite is sometimes 50% faster than 3.7.17 released in July of last year. Impressive! I also love using SQLite for testing compiler optimization passes. Their TCL test suite coverage is quite good. Unfortunately, I think you have to splurge tens of thousands of dollars to get the C-based test suite. I believe you can only test full branch coverage with the C test suite.