The SQLite Database File Format

https://www.sqlite.org/fileformat2.html

by s16h • 12 years ago

144 42 12 years ago

rckclmbr• 12 years ago

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

ushi• 12 years 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

dr_zoidberg• 12 years 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.

ygra• 12 years ago

Also worth a read: http://www.sqlite.org/testing.html

RivieraKid• 12 years ago

Yeah, these no-context submissions are annoying but common here.

thibauts• 12 years 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.

rubiquity• 12 years 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• 12 years ago

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

petilon• 12 years 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/... http://developer.android.com/reference/android/database/sqli...

dangoor• 12 years ago

Firefox and Chrome both use SQLite for some of their data. It's a great package.

rplnt• 12 years ago

Opera did too, also the new one does.

qewrffewqwfqew• 12 years ago

In case you've been living under a rock .. https://www.sqlite.org/mostdeployed.html

erikb3• 12 years ago

Your choice of languages leaves room for improvement.

Zecc• 12 years ago

That text needs an update. There's no mention of Android and Symbian is all but dead.

cwt137• 12 years 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

rectangletangle• 12 years ago

It comes bundled with most Python implementations (import sqlite3).

mathnode• 12 years ago

http://www.sqlite.org/famous.html

petercooper• 12 years ago

People have provided some good links, but I suspect more Rails apps run on it than you might suspect. I run a few of mine on it. It performs more than well enough until you have tens of people attempting simultaneous writes (or doing crazy stuff like logging or message queuing through SQLite), which perhaps 90%+ of webapps don't need to do anyway. More time is spent rendering the view than grabbing the data in most cases.

rakoo2• 12 years ago

Fossil [0] is a scm that does a lot of really useful things that revolve around managing code:

- web interface, native or CGI

- wiki

- bugtracker

Fossil is implemented using SQLite as a storage, which is rather unsurprising given that both are written by the same guy.

[0] http://www.fossil-scm.org/index.html/doc/tip/www/index.wiki

72deluxe2• 12 years ago

That looks really interesting, and lightweight! Have you used it? Any comments on it? I only ask because I have a rather old CVS tree to migrate; I have used SVN but by the looks of things this is even more lightweight. Comments on it would be welcome.

e40• 12 years ago

Adobe Lightroom.

KC8ZKF• 12 years ago

iTunes

brucehart• 12 years ago

SQLite is also used in many embedded systems. If you plug in a USB drive to my TV, it creates a SQLite database to keep track of the current position of the video files it plays.

jpswade2• 12 years 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.

erikb3• 12 years 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?

personZ2• 12 years 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.

billpg• 12 years ago

An RFC might be useful to define the file format as a general purpose data-set format.

"MIME type application/my-example is defined as a resource conforming to RFCxxxx with the following tables..."

The basic data-set RFC could leave out the internal locking and transaction support, leaving "must be zero" placeholders.

kabdib• 12 years ago

Hmmm. This presses one of my buttons.

An RFC that justs explains a data format is essentially useless to me. Well, the format of a file is an important part, but it's less than half the story behind what's there, because you're missing a lot of semantics (that are not easily described in english).

There are many "campfire story" class RFCs that raise my blood pressure because they take a complex subject and describe its syntax and formatting and layout pretty well, but leave the hairy algorithmic stuff in the domain of hand-wavy and loose English.

For some reason we leave critical infrastructure open to interpretation. We don't have standards that actually help implementers make sure that what they've written is correct, instead we have documents that try to describe correct behavior and maybe provide a little guidance. For some reason it's gauche to ship working code in an RFC [I've been reading standards for 35 years and I used to work for NIST, I think I know the issues with code in an RFC -- they seem easily surmountable].

There are very important RFCs that don't have reference implementations in their (HTTP, I'm looking at you. If you've ever written an HTTP proxy then you know the particular hell I'm talking about, and don't get me started on PKI or -- oh Lord -- CSS).

On thing that caught my eye here was the section on the B-tree in the sqlite file format. I've written these, they are hard to get right. But the section here is an english description of a very complex data structure, and if this appeared in an RFC and you asked ten different developers to reimplement it then you'd wind up with wildy different interpretations of the text, all non-interoperable until the world finally decided to have some kind of "interop fest".

So I'm happy that SQLite already has a publically available b-tree implementation. Because you won't have a "standard" without that code, and QED.

Complex standards need reference implementations. Just calling something an RFC doesn't make it more useful. Also, forcing ivory tower types to write working code is the best anodyne I know against unimplementable and overly complex standards.

tscherno• 12 years ago

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

sauere• 12 years ago

http://howfuckedismydatabase.com/sqlite/

lazyant• 12 years ago

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

jevinskie• 12 years ago

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.

The SQLite Database File Format

Related Stories