4 ms·

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 ex

by 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.

by zzzeek 12y ago

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.