News for hackers. Stuff that matters.
Re:The Design Of SQLite4 by zzzeek on Sunday May 25, 2014 @02:42PM (#7797114)
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, that works pretty hard against those advantages. Note that an ALTER command that rewrites all the data automatically, while it would perform like crap, would be better than none at all. Because that's what everyone is doing anyway.

Re:The Design Of SQLite4 by _wmd on Sunday May 25, 2014 @03:09PM (#7797220)
I'm perplexed how you arrived at this conclusion from the linked document.. every major DBMS with a "fully functional ALTER" has a key/value store underneath it, as did SQLite 3, as does PostgreSQL and MySQL, as does..

The only difference with the new design is that the SQL engine and storage engine have been explicitly modularized and exported as a public API, potentially allowing simultaneous k/v and SQL use within a single transaction, or flexible use of SQL with an existing store.

Re:The Design Of SQLite4 by zzzeek on Sunday May 25, 2014 @03:21PM (#7797260)
> 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 SQLite provided ALTER TABLE implementation would do exactly what was stated - start a transaction, rename the existing table to a temporary name, create a new one with the desired schema, and copy data across mangling as appropriate before deleting the old table and finishing the transaction.

the document here appears to suggest that the internal structure idea is being largely maintained as is, except that it will be organized in one giant blob, rather than blob-per-table. If the structure were being changed such that ALTER were suddenly much more feasible, I'd assume that would be one of the giant headlines of this story. But it's not.

Of course we'd always welcome hearing from actual SQLite developers someday on this issue.

Re:The Design Of SQLite4 by _wmd on Sunday May 25, 2014 @03:46PM (#7797326)
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)

Postgres and suchlike don't have some magical data structure that makes ALTER TABLE possible, all row oriented stores have the same choice: either implement the alter immediately (involves a scan and rewrite) or lazily (on next record update).

Not sure if there are any that take the latter approach, but the method is commonplace elsewhere

Re:The Design Of SQLite4 by wolf550e on Sunday May 25, 2014 @05:09PM (#7797569)
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 by DrJokepu on Sunday May 25, 2014 @08:27PM (#7798160)
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.
Re:The Design Of SQLite4 by thesz on Monday May 26, 2014 @05:32AM (#7799176)
1) That would be true for any row-oriented DBMS.

2) LSM tree is blazingly fast in inserts (my own experiments shows two orders of magnitude difference for BerkeleyDB and my own LSM tree implementation in C# for bulk random inserts).

So I think SQLite4 is safe here.

Re:The Design Of SQLite4 by zzzeek on Monday May 26, 2014 @10:47AM (#7799906)
Great! So they can add a full blown ALTER. But so far, the only indication we can get is, "never".
Re:The Design Of SQLite4 by d4mi3n on Sunday May 25, 2014 @02:51PM (#7797144)
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)

Re:The Design Of SQLite4 by zzzeek on Sunday May 25, 2014 @02:54PM (#7797159)
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/zzzeek/alembic/issue/129/column-additi... in case sqlite devs care to look at that one...

Re:The Design Of SQLite4 by d4mi3n on Sunday May 25, 2014 @06:14PM (#7797789)
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 by keeperofdakeys on Sunday May 25, 2014 @09:29PM (#7798296)
The problem is that it's hard to support for a database like sqlite. Things have a very specific on-disk representation, so you can't just add data to the middle of a file; rewriting the file also means others can't read it at the same time, so you can't do long operations.

When you look at this operation on a normal database, it isn't too bad. The database can change the on-disk representation while still serving requests, and it can use whatever storage it wants, since it's the only one that operates on the data.

Re:The Design Of SQLite4 by nikbackm on Monday May 26, 2014 @03:14AM (#7798985)
Wouldn't a database generally be offline when you ALTER it? At least one that is used in scenarios where SQLITE is appropriate.

In that case it should not hurt too much if the operation takes some time.

Re:The Design Of SQLite4 by frik on Sunday May 25, 2014 @03:05PM (#7797201)
> Please correct me if I'm wrong, but isn't a major use-case for SQLite a database for embedded systems?

SQLite is used in OSX (spotlight metadata, Core Data framework, iTunes, Mail), iOS, Android, WebSQL, etc.

I am pretty sure every computer gadget you own runs some instance of SQLite (be it an anti-virus app, Firefox, Thunderbird, Oracle products, Skype, Adobe software, PHP (SQLite2 & SQLite3), QT and even many Win Phone 8 apps incorporate SQLite3 library.

Re:The Design Of SQLite4 by bobbyi_settv on Sunday May 25, 2014 @03:05PM (#7797203)
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.
Re:The Design Of SQLite4 by d4mi3n on Sunday May 25, 2014 @06:17PM (#7797800)
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.

Re:The Design Of SQLite4 by marcosdumay on Sunday May 25, 2014 @09:37PM (#7798309)
The usual way to deal with that (and recommended by the SQLite docs) is creating a new table, copying the data, and dropping the old table.

Needless to say that this is VERY error prone, and limited resources make it even more so.

Re:The Design Of SQLite4 by daleharvey on Sunday May 25, 2014 @03:03PM (#7797196)
People want to change schema in embedded systems, programs get updated. Having an explicit mechanism to deal with that is one thing IndexedDB and webSQL got right.

We use ALTER tables in pouchdb on webSQL schema upgrade, I am not familiar with the missing functionality, we dont do a lot of complicated things though.

Re:The Design Of SQLite4 by mbell on Sunday May 25, 2014 @09:05PM (#7798246)
I think at least part of the confusion here is the definition of 'embedded'. To some people 'embedded' means 'within the same process or thread as the core application', to others it means 'firmware running on small hardware devices'.
Re:The Design Of SQLite4 by dtech on Monday May 26, 2014 @02:32AM (#7798920)
> To some people 'embedded' means 'within the same process or thread as the core application'

Only that definition is silly here, since SQLite is by its serverless nature always embedded inside another program.

Re:The Design Of SQLite4 by panzi on Sunday May 25, 2014 @07:58PM (#7798077)
> 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 by nscanlon on Monday May 26, 2014 @08:19AM (#7799437)
I agree that adding this, even if it is horribly inefficient, would be better than everyone reimplementing it at the application layer. I just don't see how you've drawn this conclusion from the linked summary. This sounds to me like a pet gripe of yours and you are just looking for a forum to air it.