Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The Design Of SQLite4

sqlite.org

Post reply on HN
Print view View original

31–40 of 68 posts

Re: The Design Of SQLite4

#31

Post by skue »

sanxiyn wrote: post #26

"SQLite4 requires all elements of the PRIMARY KEY to be non-null. This is an SQL standard. Due to an oversight in early versions, SQLite3 does not enforce the NOT NULL constraint on PRIMARY KEY columns since by the time the omission was discovered SQLite3 was in such widespread use, activation of NOT NULL enforcement would have broken too many programs." I find this sad, and I wonder what those too many programs are.…

Hopefully there aren't many people designing data models with NULL primary keys.

However, if SQLite never enforced non-NULL behavior then it's certainly possible that along the way some applications could have written an errant record to a database with a NULL primary key (instead of treating it as an error case). And if a "fixed" version of SQLite3 ever shipped, then these previously-valid data files would suddenly become invalid.

Imagine being an app developer and having random customers whose data files suddenly break because the OS happened to upgrade the SQLite3 library to a version that included this fix. I completely understand why the the SQLite maintainers may have decided it wasn't worth the hassle to fix things prior to SQLite4.

Re: The Design Of SQLite4

#32

Post by asveikau »

sanxiyn wrote: post #26

"SQLite4 requires all elements of the PRIMARY KEY to be non-null. This is an SQL standard. Due to an oversight in early versions, SQLite3 does not enforce the NOT NULL constraint on PRIMARY KEY columns since by the time the omission was discovered SQLite3 was in such widespread use, activation of NOT NULL enforcement would have broken too many programs." I find this sad, and I wonder what those too many programs are.…

Thankfully from what I have seen sqlite is maintained with that old-school style of library development, the one you scarcely see these days, and don't hear much about on HN: the one where "thou shalt not break existing code" has some weight. I'd be nervous about depending on a library where the whim of the developer thinking "I find this sad" breaks my program.

sqlite4 OTOH seems to be the "let's break things" phase. Probably healthy to have both attitudes at certain stages.

Re: The Design Of SQLite4

#34

Post by furyofantares »

skue wrote: post #31
sanxiyn wrote: post #26

"SQLite4 requires all elements of the PRIMARY KEY to be non-null. This is an SQL standard. Due to an oversight in early versions, SQLite3 does not enforce the NOT NULL constraint on PRIMARY KEY columns since by the time the omission was discovered SQLite3 was in such widespread use, activation of NOT NULL enforcement would have broken too many programs." I find this sad, and I wonder what those too many programs are.…

Hopefully there aren't many people designing data models with NULL primary keys. However, if SQLite never enforced non-NULL behavior then it's certainly possible that along the way some applications could have written an errant record to a database with a NULL primary key (instead of treating it as an error case). And if a "fixed" version of SQLite3 ever shipped, then these previously-valid data files would suddenly…

> the OS happened to upgrade the SQLite3 library

I don't think this actually detracts from your point, but, are there cases where that can actually happen? Isn't SQLite always statically linked with the applicaton?

Re: The Design Of SQLite4

#35

Post by jasode »

SoftwareMaven wrote: post #17
jasode wrote: post #13

Reading the "Key Changes" section, it looks like the sqlite4_env environment will address the global mutex problem that was discussed in a recent HN thread. The pluggable db engine looks interesting but if I remember right, in MySQL the pluggable db engine initially attracted some niche providers but the marketplace of ideas eventually settled on ISAM/Innodb. What's still not clear from the Executive Summary is if th…

I don't think there is an answer to your question beyond "it's been battle tested extremely well", but there are strong reasons to move to sqlite4 (in particular, multiple linked instances in eg mobile apps and mobile OSes). It's not like Python where the weight of the existing community has drawn you towards python 2 (something that has changed a lot recently).

That's fair but I guess I was looking for a stronger statement from them because even for new minor point releases, the sqlite.org home page says clearly:

"Version 3.8.4.3 of SQLite is recommended for all new development."

Every time they change the "z" in v.x.y.z, they'll still state "is recommended for all new development."

So, it seems like they could just clearly say, "SQLite4 is recommended for all new development."

...unless there's some architecture tradeoff (unrelated to backward compatibility) that still makes SQLite3 desirable over SQLite4.

Re: The Design Of SQLite4

#36

Post by zzzeek »

MaxGabriel wrote: post #28
sanxiyn wrote: post #26

"SQLite4 requires all elements of the PRIMARY KEY to be non-null. This is an SQL standard. Due to an oversight in early versions, SQLite3 does not enforce the NOT NULL constraint on PRIMARY KEY columns since by the time the omission was discovered SQLite3 was in such widespread use, activation of NOT NULL enforcement would have broken too many programs." I find this sad, and I wonder what those too many programs are.…

It's not super unreasonable for a multi-element primary key, is it? For example, at work we have a MySQL table that stores advertising stats with a (Publisher ID, Country, Day) primary key[1]. All those fields are non-nullable, but I can imagine someone using NULL for the country[2] if we couldn't determine it (it's based on IP). [1] It's slightly more complicated, but it's basically similar. [2] "zz" might be approp…

it's unreasonable. NULL isn't a value, it means, "the value can't be known". If a primary key of a table has part of it that is unknown, you've designed your schema completely incorrectly, and you should brush up on normal form: http://en.wikipedia.org/wiki/Database_normalization#Normal_f...

Re: The Design Of SQLite4

#37

Post by panzi »

d4mi3n wrote: post #6
zzzeek wrote: post #2

Doubling down on the key/value model gives me the impression that a real and fully functional ALTER command is becoming even more unlikely than it already is, not to mention that this widely desired feature is not even mentioned here. A key advantage to the relational model is that we get to have schemas. But if you can't make reasonable changes to those schemas without explicitly rewriting all your data from scratch…

Please correct me if I'm wrong, but isn't a major use-case for SQLite a database for embedded systems? In that light, I can't picture too many situations off the top of my head where one would want to change the schema. That being said, I've personally seem SQLite used for more prototype-y stuff (default db for new instances of a Rails app, ad-hoc data stores for mobile phone apps, etc)

> Please correct me if I'm wrong, but isn't a major use-case for SQLite a database for embedded systems?

What makes you think that? It is used in many Desktop applications. E.g. most browsers (Firefox, Chrome, Safari), Skype and apparently even Apple Mail, iTunes and the Steam runtime use SQLite. Do you want to store complex indexed data in your desktop or smartphone application? Maybe something like a music library? Then you usually use SQLite. Even if you store relatively simple data you may use SQLite: no need to invent your own data file format. SQLite runs virtually everywhere and has bindings for virtually every programming language.

And as someone else already said: applications get updates that add features and need to update their DB accordingly.

Re: The Design Of SQLite4

#38

Post by panzi »

jqm wrote: post #19

I love sqlite and find it extremely useful. I just wish they could figure the multi-user issues out (maybe it's not practical...probably not, I don't know how it works internally). But, if they could do this it would be truly awesome!

Easy: Put a server process in front of it and route access to the DB through that. :P

Re: The Design Of SQLite4

#39

Post by panzi »

furyofantares wrote: post #34
skue wrote: post #31

Earlier quoted context omitted.

Hopefully there aren't many people designing data models with NULL primary keys. However, if SQLite never enforced non-NULL behavior then it's certainly possible that along the way some applications could have written an errant record to a database with a NULL primary key (instead of treating it as an error case). And if a "fixed" version of SQLite3 ever shipped, then these previously-valid data files would suddenly…

> the OS happened to upgrade the SQLite3 library I don't think this actually detracts from your point, but, are there cases where that can actually happen? Isn't SQLite always statically linked with the applicaton?

E.g. on iOS and Android you get access to the system's SQLite library and apparently can't even opt-out (because it is used by system APIs that you will use, if I understand correctly). See: https://news.ycombinator.com/item?id=7756332

Re: The Design Of SQLite4

#40

Post by DrJokepu »

_wmd wrote: post #16
zzzeek wrote: post #14

Earlier quoted context omitted.

> I'm perplexed how you arrived at this conclusion from the linked document.. it is based on: https://news.ycombinator.com/item?id=5887053 > I am very familiar with SQLite internals. The answer is already in there. SQLite stores each row as each column value encoded sequentially corresponding to the declared order of the columns. Changing column order or deletions/inserts require a rewrite of every row... > ...A SQLi…

SQLite 4 will have one storage engine record for each table row, just as it was in SQLite 3. Fundamentally, there is little difference in how SQLite stores data in the storage engine as compared to, say, Postgres, except that Postgres' SQL implementation is more tightly bound to its storage engine (e.g. index tuples are encoded using knowledge of the engine, whereas in SQLite they simply use the record's primary key)…

I don't think "lazy alter" is a particularly good idea in an RDBMS as it could lead to the operation failing after its transaction has been committed which is something that is generally avoided in relational databases.
Post reply on HN