News for hackers. Stuff that matters.
Re:The lack of proper “alter table” support in SQLite by revelation on Saturday June 15, 2013 @09:18PM (#5886965)
ALTER is in itself an odd command, useful only in development, never in production, and bringin with it deep architectural implications and an endless list of problems in basically all SQL systems today.

In SQLite, an embedded database, it is certainly out of place. Drop, rebuild, and move on with life.

Re:The lack of proper “alter table” support in SQLite by kalininalex on Saturday June 15, 2013 @11:09PM (#5887269)
"ALTER is in itself an odd command, useful only in development, never in production"

How would you then ever modify an existing database?

Re:The lack of proper “alter table” support in SQLite by kanzure on Saturday June 15, 2013 @11:32PM (#5887329)
> How would you then ever modify an existing database?

You would use techniques like:

https://github.com/soundcloud/large-hadron-migrator

"""

That is until your tables grow to millions of records. At this point, the locking nature of ALTER TABLE may take your site down for an hour or more while critical tables are migrated. In order to avoid this, developers begin to design around the problem by introducing join tables or moving the data into another layer. Development gets less and less agile as tables grow and grow. To make the problem worse, adding or changing indices to optimize data access becomes just as difficult.

Side effects may include black holes and universe implosion. There are few things that can be done at the server or engine level. It is possible to change default values in an ALTER TABLE without locking the table. The InnoDB Plugin provides facilities for online index creation, which is great if you are using this engine, but only solves half the problem.

At SoundCloud we started having migration pains quite a while ago, and after looking around for third party solutions, we decided to create our own. We called it Large Hadron Migrator, and it is a gem for online ActiveRecord and DataMapper migrations.

The basic idea is to perform the migration online while the system is live, without locking the table. In contrast to OAK and the facebook tool, we only use a copy table and triggers.

Lhm requires a monotonically increasing numeric Primary Key on the table, due to how the Chunker works.

"""

Re:The lack of proper “alter table” support in SQLite by yxhuvud on Sunday June 16, 2013 @05:31AM (#5887945)
.. or you use a real database engine that support schema changes in background transactions, like postgres.

Also, it is good to remember that not all production databases will grow large. Many stay small with a limited number of rows, but will still need to be updated as the product is updated.

Re:The lack of proper “alter table” support in SQLite by kalininalex on Sunday June 16, 2013 @12:06AM (#5887423)
OK, I guess the issue is with the "never in production". One size rarely fits all. I can have a lookup table with three rows in my database, for which Large Hadron Migrator is hardly an appropriate solution. ALTER will work just fine. For large tables - yes, a more complex migration solution is in order.
Re:The lack of proper “alter table” support in SQLite by gaadd33 on Saturday June 15, 2013 @10:00PM (#5887104)
Why is ALTER never useful in production?
Re:The lack of proper “alter table” support in SQLite by mateo411 on Saturday June 15, 2013 @10:54PM (#5887243)
I wouldn't say that ALTER TABLE is never useful in production, but you should try to avoid it, especially if you have large tables. Many implementations of ALTER TABLE, lock the table, which means that no process can write to the table, until the ALTER TABLE command completes. If the ALTER TABLE statement takes minutes, or a few hours to complete, then you will have degraded performance or downtime.
Re:The lack of proper “alter table” support in SQLite by gaadd33 on Saturday June 15, 2013 @11:18PM (#5887283)
Oh yeah that's very true, many SQL engines seem to lock tables whenever a DDL statement is run. I do like that Postgresql tends not to, it is a huge change from the hoops you have to jump through to add columns in a production MySQL scenario.
Re:The lack of proper “alter table” support in SQLite by genwin on Saturday June 15, 2013 @09:25PM (#5886994)
The ALTER doesn't have to be in the embedded code. It can be a separate utility.
Re:The lack of proper “alter table” support in SQLite by PommeDeTerre on Sunday June 16, 2013 @08:13AM (#5888212)
The migration libraries mentioned in the article basically are the "separate utility" that you describe. Yet apparently the developers of these libraries are unable to implement this functionality properly on their own, for whatever reason. So they want the SQLite developers to do so instead.
Re:The lack of proper “alter table” support in SQLite by spatz2013 on Saturday June 15, 2013 @10:26PM (#5887179)
tell that Drupal when they go from one version to the next.