4 ms·

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 th

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

by d4mi3n 12y ago

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.