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

21–30 of 68 posts

Re: The Design Of SQLite4

#21

Post by seanalltogether »

ccgus wrote: post #18
seanalltogether wrote: post #15

I'm sure this sounds arbitrary, but I'd love it if the sqlite file header allowed for more room to add program specific metadata. The fact that more and more apps are using sqlite files to store user facing files, it would be nice if space in the header was given to identify the expected usage of the sql file.

You're aware of the application_id pragma? That's what this guy was created for: http://www.sqlite.org/pragma.html#pragma_application_id

Ah, no I wasn't. The file format page simply marks that region as reserved. http://www.sqlite.org/fileformat.html

Re: The Design Of SQLite4

#23

Post by peatmoss »

I wonder if this will have any implications for spatialite? If spatialite were a little better able to make automatic use of spatial indexes, I could easily see it becoming the default foundation for many open source GIS.

Re: The Design Of SQLite4

#25

Post by wolf550e »

_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)…

To be able to defer the re-encoding of rows (e.g. when ALTER changes column order) the row header must store the version/identifier of the format used to encode it. You can also do it block-at-a-time instead of row-at-a-time, so only each block would store a version/identifier of the format. But any way, deferrable re-encoding means some storage overhead and inconsistent UPDATE performance after ALTER until all rows (or all blocks) have been re-encoded.

Re: The Design Of SQLite4

#26

Post by sanxiyn »

"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. Why would one put NULL on PRIMARY KEY?

Re: The Design Of SQLite4

#27

Post by eridius »

ccgus wrote: post #18
seanalltogether wrote: post #15

I'm sure this sounds arbitrary, but I'd love it if the sqlite file header allowed for more room to add program specific metadata. The fact that more and more apps are using sqlite files to store user facing files, it would be nice if space in the header was given to identify the expected usage of the sql file.

You're aware of the application_id pragma? That's what this guy was created for: http://www.sqlite.org/pragma.html#pragma_application_id

Hah, I read that and thought "Hmmm, I wonder if Gus knows about this? He should use it in Acorn". Then I read your username :)

Re: The Design Of SQLite4

#28

Post by MaxGabriel »

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 appropriate to represent an unknown country, but other domains might not have a good default value

Edit: double asterisks messing up my formatting

Re: The Design Of SQLite4

#29

Post by d4mi3n »

zzzeek wrote: post #7
d4mi3n wrote: post #6

Earlier quoted context omitted.

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)

I am asked constantly for ALTER support in SQLite, including by the maintainer of GNU Mailman, which is neither embedded nor a prototype one-off. SQLite is extremely flexible and used in an enormous variety of situations. References: http://dustycloud.org/blog/sqlite-alter-pain/ https://news.ycombinator.com/item?id=5886898 https://bitbucket.org/zzzeek/alembic/issue/21/column-renames... plus https://bitbucket.org/zzze…

Very interesting! Thanks for the references.

Having read some of these, though, it's pretty clear that ALTER support is a major pain point. Would you have any insight as to why the SQLite maintainers would not choose to prioritize that issue? From what I know of SQLite, it's a very well built piece of software. It seems odd to me that such a smart bunch of guys would disregard community feedback without a good reason.

Re: The Design Of SQLite4

#30

Post by d4mi3n »

bobbyi_settv wrote: post #11
d4mi3n wrote: post #6

Earlier quoted context omitted.

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)

It's needed, for example, when upgrading applications that use sqlite for storage. Like in the mobile phone case, if a new version of the app comes out that has a new feature that requires a new column in one of the tables, when you upgrade the app on your phone, the app probably wants to migrate the sqlite db to the new schema rather than throwing away all your saved data.

This is something I hadn't considered. Being able to do firmware updates to a device with an embedded instance of SQLite seems huge.

Seeing as ALTER support is currently lacking, what kinds of approaches are commonly taken to work around this issue? I'm particularly interested in hearing about how people manage workaround to this problem for things like upgrading embedded devices. I imagine the scarcity of resources (RAM, CPU) available to most embedded devices would make this a difficult problem to solve on the hardware at hand.

Post reply on HN