The fact this fits in a few pages and is so approachable is a testament to its simplicity. I think I'd find it a lot harder to grok the file format of, for example, a Word doc/docx file.
SQLite's File Format
71–80 of 86 posts
Re: SQLite's File Format
#72The fact this fits in a few pages and is so approachable is a testament to its simplicity. I think I'd find it a lot harder to grok the file format of, for example, a Word doc/docx file.
I wouldn't put .doc and .docx next to one another, as they're only tangentially related. I'd bet getting the
hello, world
of .docx would be some silliness, but would not be hard to grok. I couldn't readily find a browsable copy of ECMA 376 4th Ed online but https://github.com/PumasAI/WriteDocx.jl/blob/v1.2.0/docs/src... was in the ballpark of what I expected to find in some section of the actual specRe: SQLite's File Format
#73Earlier quoted context omitted.
It was designed to be a DB for Tcl at a time when that language didn't have typed objects. Its SQL implementation reflects that. Where are the grand Python, or Perl, or JS DBs?
It actually does have typed values, it is just the schema didn’t constrain the value types stored in each column, until relatively recently the column type was mostly just documentation. However, now it has STRICT tables which do constrain the value types of columns. And for a lot longer you’ve been able to implement the same thing manually using check constraints-which is a bit verbose if you are writing the schema…
>> It was designed to be a DB for Tcl at a time when that language didn't have typed objects. Its SQL implementation reflects that.
> It actually does have typed values
Now. As the article points out, they were not part of the initial design, because of the Tcl heritage.
Re: SQLite's File Format
#74Earlier quoted context omitted.
That's just 10 30TB HDDs. Throw in two more for redundancy and mount them in a single zfs raidz2 (a fancy RAID6). At about $600 per drive that's just $7200. Half that if you go with 28TB refurbished drives (throw in another drive to make up for lost capacity). That is in the realm of lots of people's hobby projects (mostly people who end up on /r/datahoarder). If you aren't into home-built NAS hardware you can even d…
Last week I threw together a 840TB system to do a data migration. $1500 used 36-bay 4U, 36 refurbished Exos X28 drives, 3x12 RAIDz2. $15000 all in.
Where did you source the drives?
Re: SQLite's File Format
#75Earlier quoted context omitted.
This seems like an unnecessarily negative comment. I've been a user of SQLite for over 20 years now (time flies!), what you're calling lack of polish, I would chalk up to Dr. Hipp has been consciousness about maintaining compatibility over the long term. So much so, that the Library of Congress recommends it for long-term preservation of data. Long term compatibility (i.e. prioritizing the needs of users vs chasing i…
> I would chalk up to Dr. Hipp has been consciousness about maintaining compatibility over the long term. I agree. I am not suggesting that the SQLite team doesn't know how to make the technology better. Just that they aren't/haven't. Backwards compatibility is a good reason not to. My original comment was contrasting craftsmanship and utility, since both are somewhat prized on HN, but they aren't the same thing at a…
> contrasting craftsmanship and utility, since both are somewhat prized on HN
I'd say they're prized everywhere, though "craftsmanship" is really subjective. and the HN I usually [edit/add: see] seems to have more a meta of "criticize anything someone tries to build, and rave about IQ" tbh ;)
SQLite works and I don't have to think about it why it works (too much). That is IMO a true hallmark of solid engineering.
Re: SQLite's File Format
#76Any recommendations from HN for a write-once (literally once), data storage format that's suitable for network storage? sqlite docs recommend avoiding using it on network storage, though from what I can gather, it's less of an issue if you're truly only doing reads (meaning I could create it locally and then copy it to network storage). Apache Parquet seems promising, and it seems to support indexing now which is an…
Ordinary files inside squashfs?
https://www.kernel.org/doc/html/latest/filesystems/squashfs....
Re: SQLite's File Format
#77Earlier quoted context omitted.
I think it's one of the reasons DuckDB has seen the popularity that it has.
thats a strange argument DuckDB is for OLAP and SQLite is for OLTP
Yeah, but most applications are small. So, at the scale of most applications you can drop in DuckDB with zero change in actual performance. It still has indexes to support highly selective queries because it needs to have functional primary keys.
Re: SQLite's File Format
#78Earlier quoted context omitted.
Dr. Hipp has said several times that nobody expected a weakly-typed database to achieve the pervasiveness that is observed with SQLite. At the same time, strict tables address some of the concern of those coming from conventional databases. Dates and times are a core problem to SQLite not seen elsewhere as far as I know, but this does evade UTC and constantly shifting regional time. My OS gets timezone updates every…
SQLite probably doesn't do anything with times and dates except punting some functions to the limited libc facilities because including any proper date-time facilities would basically double the footprint of SQLite. Same for encodings and collations.
Same for encodings and collations.
Re: SQLite's File Format
#79Earlier quoted context omitted.
It actually does have typed values, it is just the schema didn’t constrain the value types stored in each column, until relatively recently the column type was mostly just documentation. However, now it has STRICT tables which do constrain the value types of columns. And for a lot longer you’ve been able to implement the same thing manually using check constraints-which is a bit verbose if you are writing the schema…
>> It was designed to be a DB for Tcl at a time when that language didn't have typed objects. Its SQL implementation reflects that. > It actually does have typed values Now. As the article points out, they were not part of the initial design, because of the Tcl heritage.
AFAIK it has always had typed values. Don’t confuse column types (which constrain a column to containing only values of a specified type) with value types (which enable it to treat the string “12” and the integer 12 and the floating point 12.0 as three distinct values)
Tcl has value types. Tcl 7.x and earlier only had one data type, the string-so adding two integers required two string-to-int conversions followed by an int-to-string conversion. In 1997, Tcl 8.x was released, which internally has distinct values types (int, string, etc), although it retains the outward appearance of “everything-is-a-string” for backward compatibility. So SQLite’s Tcl heritage included distinguishing different types of values, as is done in post-1997 Tcl.
Re: SQLite's File Format
#80SQLite is a great example of a single factor mattering more than everything else combined. A database contained in a single file is such a good idea that it outweighs a poorly designed storage layer, poorly designed column formats, and a terrible SQL implementation. If craftsmanship is measured by the long tail of good choices that give something a polished and pristine feel, then SQLite was built with none of it. An…
I think they do a good job with test coverage, compatibility, and sustainable support. Can't say that about most every other hype database made by a fortune 500 and shut down 3 years later.