5 ms·

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, n

by zzzeek 12y ago

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.

d4mi3n 12y ago
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)
- zzzeek 12y ago
  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/ http://dustycloud.org/blog/sqlite-alter-pain/ https://news.ycombinator.com/item?id=5886898 https://news.ycombinator.com/item?id=5886898 https://bitbucket.org/zzzeek/alembic/issue/21/column-renames-not-supported-on-sqlite https://bitbucket.org/zzzeek/alembic/issue/21/column-renames... plus https://bitbucket.org/zzzeek/alembic/issue/129/column-additions-uglify-sqlite-schemas https://bitbucket.org/zzzeek/alembic/issue/129/column-additi... in case sqlite devs care to look at that one...
  - d4mi3n 12y ago
    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.
    - keeperofdakeys 12y ago
      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.
      - nikbackm 12y ago
        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.
- daleharvey 12y ago
  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.
- frik 12y ago
  > 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.
- bobbyi_settv 12y ago
  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.
  - d4mi3n 12y ago
    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.
    - marcosdumay 12y ago
      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.
- panzi 12y ago
  > 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.
- mbell 12y ago
  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'.
  - dtech 12y ago
    > 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.
_wmd 12y ago
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.
- zzzeek 12y ago
  > I'm perplexed how you arrived at this conclusion from the linked document.. it is based on: https://news.ycombinator.com/item?id=5887053 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.
  - _wmd 12y ago
    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
nscanlon 12y ago
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.

by zzzeek 12y ago

d4mi3n 12y ago

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)

zzzeek 12y ago
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/ http://dustycloud.org/blog/sqlite-alter-pain/ https://news.ycombinator.com/item?id=5886898 https://news.ycombinator.com/item?id=5886898 https://bitbucket.org/zzzeek/alembic/issue/21/column-renames-not-supported-on-sqlite https://bitbucket.org/zzzeek/alembic/issue/21/column-renames... plus https://bitbucket.org/zzzeek/alembic/issue/129/column-additions-uglify-sqlite-schemas https://bitbucket.org/zzzeek/alembic/issue/129/column-additi... in case sqlite devs care to look at that one...
- d4mi3n 12y ago
  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.
  - keeperofdakeys 12y ago
    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.
    - nikbackm 12y ago
      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.
daleharvey 12y ago
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.
frik 12y ago
> 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.
bobbyi_settv 12y ago
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.
- d4mi3n 12y ago
  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.
  - marcosdumay 12y ago
    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.
panzi 12y ago
> 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.
mbell 12y ago
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'.
- dtech 12y ago
  > 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.

_wmd 12y ago

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.

zzzeek 12y ago
> I'm perplexed how you arrived at this conclusion from the linked document.. it is based on: https://news.ycombinator.com/item?id=5887053 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.
- _wmd 12y ago
  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

nscanlon 12y ago

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.