6 ms·

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 th

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. The one special case that is allowed is adding a column on the end of the schema providing it has a default value. 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. For plain tables this is no big deal, but for more complicated ones there are a lot of issues such as foreign key references, constraints, indices. The majority of the code would be dealing with all these conditions and interactions. It also wouldn't be any more efficient than code the developer writes - ie there are no shortcuts available to the SQLite developers that aren't available to developers using SQLite. The net effect would be a larger library (they limit to a 250kb library size so something else would need to be chopped), some complex documentation and a heck of a lot of testing code. For something that is relatively rarely needed (see requests on the mailing lists). The chance of something like this ending up in the SQLite core is extremely slim, so you could never depend on it being there anyway. SQLite does have several things to help. There is a user_version pragma you can use to keep track of the schema version and use for upgrading. You can temporarily disable foreign key and constraint enforcing. There are numerous pragmas to get table metadata. The table definitions are stored as SQL strings in a sqlite master table, and a pragma allows you to make that writeable.

codeka 13y ago
I agree with you. This kind of functionality is much better implemented in a third-party tool like sqlalchemy-migrate or alembic that OP mentions in the article. I much rather a smaller sqlite library than one that is burdened with functionality you just don't use 99% of the time. The benefit of making an "official" migration library for SQLite is probably outweighed by the opportunity cost of having it developed, tested and kept up-to-date.
- paroneayea 13y ago
  The problem though is that Alembic doesn't want to engineer itself around implementing the tooling because it's so painful (and the author of that tool is himself asking for this kind of alter table support) and sqlalchemy-migrate breaks all the time. And sqlalchemy-migrate breaks all the time, and when I used South and django-migrations (granted, that was years ago), those also broke all the time too. The external migrations path isn't working.
  - PommeDeTerre 13y ago
    So write and apply such modification scripts manually. It's not difficult, and you can tailor it to your table's unique or unusual properties, as needed. I've seen too many people waste far too much time and effort with automated migration systems, for what amounts to very little saving even in the cases where it does work well.
- stormbrew 13y ago
  This makes no sense. Who's more capable of keeping a solution for this developed, tested, and up-to-date, the core developers of the database or a third party that's probably hoping to achieve something other than a migration tool? It's worth noting that it's not like rewriting the whole table is a solution to this that's unique to SQLite. MySQL's two main table types also do this in order to alter a table in certain (very common) ways. But they don't make you do it yourself, because it's deceptively difficult to do it right.
  - rogerbinns 13y ago
    Look at the issue a different way. What functionality needs to be removed from SQLite in order to add more comprehensive alter table? SQLite is defined by being "lite" and that involves a lot of saying "no". You can use other database solutions. SQLite is also public domain. You can cause this code to be written and have complete freedom to redistribute/keep private as you see fit. So what is actually being demanded here is that the SQLite core developers spend their time on the feature. SQLite used to have no table altering functionality at all. The ability to append a column (which had few technical risks or ambiguity) was paid for by AOL.
paroneayea 13y ago
Thanks for that helpful reply! Interesting to see that what I wrote out isn't far from what reality would be. I appreciate your informed input here. But still, as I described in the post, all these bits of tooling that other developers write seem to break all the time anyway. If you look at the quote from the Alembic developer, you can read the level of frustration and spent time that's been put into this. If this is how it's going to work, why not just do it in one location, as I said? All this tooling that's trying to add workarounds seems to be constantly breaking. I think that sqlite could do a better job implementing that logic itself. At the moment, with MediaGoblin, we're trapped and our developers and users are fighting these problems on a very regular basis. I believe that the constant reimplementation of this in migration frameworks is indication enough that it really is desired and would be useful. Again, I encourage: if there's an experienced developer of sqlite, try testing my theory by running a crowdfunding campaign to add the feature. I bet you'll have proof enough through donations that this is something people want.
- rogerbinns 13y ago
  I don't see what the actual difficulty is. Being able to do arbitrary schema migrations is considerably harder than making it work for your own data. In my own setup code I look at the schema version and know how to go from version 3 to 4 to 5. It is usually a handful of lines of code for each. I've reread the content again, and something unstated appears to be the use of an ORM (there are several good posts about why they are evil). ORMs already place several layers of indirection and abstraction between code and the database. It is hardly surprising that the contortions make it harder for them to do things. However I couldn't find an explanation as to why they think the copy and adjust approach would be so hard. Perhaps they are unaware of SQLite functionality like getting the table as a schema or a column by column basis. I've been on the sqlite-users mailing list for a decade, and use the frequency of requests there to gauge the mood. Alter table is very infrequently mentioned. It should be noted that just having some code that implements alter table is insufficient. If it doesn't become a standard part of the SQLite core then you can't depend on it being there. And even if it did, you would have to wait years before it is present on the SQLite versions used by stable/enterprise distributions. If you want to crowdfund this, contact the SQLite developers asking for a quote and use that. http://www.sqlite.org/support.html http://www.sqlite.org/support.html You'll also need to establish what you want to happen - eg if a column affinity changes what happens to existing data? What happens to constraints and foreign keys? What should it do if a transaction is already active?
chacham15 13y ago
Side question: how much in demand are recursive CTE's? I, particularly, hack recursive functions around all SQL View queries, but it kludges the code up quite a bit. Am I the only one with this problem?
- rogerbinns 13y ago
  I don't remember anyone asking on the mailing list, but you can search the archives. SQLite does let you do select operations on selects: SELECT ... FROM (SELECT ... FROM ..) The other important thing SQLite has available is user defined functions. Because SQLite is running in the same process as your query code, you can add your own scalar and aggregate functions that do anything you want. It also has a virtual table mechanism so you can provide tables of any shape and contents you want based on other data.
  - saurik 13y ago
    A sub-select is not a recursive CTE; think more along the lines of "find me all children of this Hacker News post #" where the scheme is simply "id | parent-id | poster | text": to do this requires recursion. I could totally be misunderstanding the reason you bring them up, however.
    - rogerbinns 13y ago
      I brought up subselects because they are a solution some of the time to some of the problems.
mst 13y ago
I believe that in the vast majority of cases MySQL's ALTER is 'rename, copy-with-changes, drop' - memory sayeth that LiveJournal used to only ever add columns during normal migrations, and then go back and clean up the 'dead' ones every so often during a maintenance window. Having the relevant rename, copy-with-changes, drop logic implemented -once- within SQLite would seem to me to be a nice thing to have; it's probably the thing that annoys me most about SQLite (honestly I'd rather have got support for this than foreign keys, which I think speaks to the "you'd have to drop something else" argument). Perhaps a workable compromise would be to write a C extension that handles it once, and then try and convince vendors to add that extension to the 'recommends' list for the SQLite package - and then, if that code proves widely used, have the argument about implementing it in core at that point.

codeka 13y ago

I agree with you. This kind of functionality is much better implemented in a third-party tool like sqlalchemy-migrate or alembic that OP mentions in the article. I much rather a smaller sqlite library than one that is burdened with functionality you just don't use 99% of the time. The benefit of making an "official" migration library for SQLite is probably outweighed by the opportunity cost of having it developed, tested and kept up-to-date.

paroneayea 13y ago
The problem though is that Alembic doesn't want to engineer itself around implementing the tooling because it's so painful (and the author of that tool is himself asking for this kind of alter table support) and sqlalchemy-migrate breaks all the time. And sqlalchemy-migrate breaks all the time, and when I used South and django-migrations (granted, that was years ago), those also broke all the time too. The external migrations path isn't working.
- PommeDeTerre 13y ago
  So write and apply such modification scripts manually. It's not difficult, and you can tailor it to your table's unique or unusual properties, as needed. I've seen too many people waste far too much time and effort with automated migration systems, for what amounts to very little saving even in the cases where it does work well.
stormbrew 13y ago
This makes no sense. Who's more capable of keeping a solution for this developed, tested, and up-to-date, the core developers of the database or a third party that's probably hoping to achieve something other than a migration tool? It's worth noting that it's not like rewriting the whole table is a solution to this that's unique to SQLite. MySQL's two main table types also do this in order to alter a table in certain (very common) ways. But they don't make you do it yourself, because it's deceptively difficult to do it right.
- rogerbinns 13y ago
  Look at the issue a different way. What functionality needs to be removed from SQLite in order to add more comprehensive alter table? SQLite is defined by being "lite" and that involves a lot of saying "no". You can use other database solutions. SQLite is also public domain. You can cause this code to be written and have complete freedom to redistribute/keep private as you see fit. So what is actually being demanded here is that the SQLite core developers spend their time on the feature. SQLite used to have no table altering functionality at all. The ability to append a column (which had few technical risks or ambiguity) was paid for by AOL.

paroneayea 13y ago

Thanks for that helpful reply! Interesting to see that what I wrote out isn't far from what reality would be. I appreciate your informed input here. But still, as I described in the post, all these bits of tooling that other developers write seem to break all the time anyway. If you look at the quote from the Alembic developer, you can read the level of frustration and spent time that's been put into this. If this is how it's going to work, why not just do it in one location, as I said? All this tooling that's trying to add workarounds seems to be constantly breaking. I think that sqlite could do a better job implementing that logic itself. At the moment, with MediaGoblin, we're trapped and our developers and users are fighting these problems on a very regular basis. I believe that the constant reimplementation of this in migration frameworks is indication enough that it really is desired and would be useful. Again, I encourage: if there's an experienced developer of sqlite, try testing my theory by running a crowdfunding campaign to add the feature. I bet you'll have proof enough through donations that this is something people want.

rogerbinns 13y ago
I don't see what the actual difficulty is. Being able to do arbitrary schema migrations is considerably harder than making it work for your own data. In my own setup code I look at the schema version and know how to go from version 3 to 4 to 5. It is usually a handful of lines of code for each. I've reread the content again, and something unstated appears to be the use of an ORM (there are several good posts about why they are evil). ORMs already place several layers of indirection and abstraction between code and the database. It is hardly surprising that the contortions make it harder for them to do things. However I couldn't find an explanation as to why they think the copy and adjust approach would be so hard. Perhaps they are unaware of SQLite functionality like getting the table as a schema or a column by column basis. I've been on the sqlite-users mailing list for a decade, and use the frequency of requests there to gauge the mood. Alter table is very infrequently mentioned. It should be noted that just having some code that implements alter table is insufficient. If it doesn't become a standard part of the SQLite core then you can't depend on it being there. And even if it did, you would have to wait years before it is present on the SQLite versions used by stable/enterprise distributions. If you want to crowdfund this, contact the SQLite developers asking for a quote and use that. http://www.sqlite.org/support.html http://www.sqlite.org/support.html You'll also need to establish what you want to happen - eg if a column affinity changes what happens to existing data? What happens to constraints and foreign keys? What should it do if a transaction is already active?

chacham15 13y ago

Side question: how much in demand are recursive CTE's? I, particularly, hack recursive functions around all SQL View queries, but it kludges the code up quite a bit. Am I the only one with this problem?

rogerbinns 13y ago
I don't remember anyone asking on the mailing list, but you can search the archives. SQLite does let you do select operations on selects: SELECT ... FROM (SELECT ... FROM ..) The other important thing SQLite has available is user defined functions. Because SQLite is running in the same process as your query code, you can add your own scalar and aggregate functions that do anything you want. It also has a virtual table mechanism so you can provide tables of any shape and contents you want based on other data.
- saurik 13y ago
  A sub-select is not a recursive CTE; think more along the lines of "find me all children of this Hacker News post #" where the scheme is simply "id | parent-id | poster | text": to do this requires recursion. I could totally be misunderstanding the reason you bring them up, however.
  - rogerbinns 13y ago
    I brought up subselects because they are a solution some of the time to some of the problems.

mst 13y ago

I believe that in the vast majority of cases MySQL's ALTER is 'rename, copy-with-changes, drop' - memory sayeth that LiveJournal used to only ever add columns during normal migrations, and then go back and clean up the 'dead' ones every so often during a maintenance window. Having the relevant rename, copy-with-changes, drop logic implemented -once- within SQLite would seem to me to be a nice thing to have; it's probably the thing that annoys me most about SQLite (honestly I'd rather have got support for this than foreign keys, which I think speaks to the "you'd have to drop something else" argument). Perhaps a workable compromise would be to write a C extension that handles it once, and then try and convince vendors to add that extension to the 'recommends' list for the SQLite package - and then, if that code proves widely used, have the argument about implementing it in core at that point.