如何使用数据迁移更新sqlite3模式

Question

我可能有一个特殊的数据迁移问题：

1. 我有一个现有的和填充的SQLite3数据库。
2. 我确实收到了一个(希望兼容的)数据库的新模式。

结果应该是一个新的数据库，根据新模式构建，尽可能多地包含旧的数据库内容。

考虑到SQLite3 ALTER语句和我们的工作流中的限制，可以安全地假设：

• 通常情况下，新列将被添加到表的末尾。
• 添加的列(字段)要么是默认的，要么是空的。
• 很少会添加一些表。
• 很少会有表格或列被删除。
• 不会发生表/列重命名。
• 不会发生栏目重组。

注意到：如果新模式与旧模式不兼容(即:上述任何假设都不成立)，那么它就会严重失败。

我尝试了这个脚本(旧数据库是data.sql3，新模式是data.schema)：

mkdir tmp
cd tmp

#compute old DB schema
sqlite3 ../data.sql3 .schema >old_s

#purge new schema for any initialization...
grep -v ^INSERT ../data.schema >data.schema
#... create a dew, empty DB...
sqlite3 new.sql3 <data.schema
#... and compute a standard schema
#(this is done to avoid typing differences)
sqlite3 new.sql3 .schema >new_s

#iff the schemas are different
if ! diff -q old_s new_s
then
  #save old DB
  mv ../data.sql3 .
  #dump contents
  sqlite3 data.sql3 .dump >old_d
  #expunge all statements needed to recreate DB/Tables
  #new_d contains only INSERT statements
  grep -v -f old_s old_d >new_d
  #add old DB content to new DB
  sqlite3 new.sql3 <new_d
  #move new DB in place
  mv new.sql3 ../data.sql3
fi
cd ..

这可以检测更改，但无法重新填充新数据库，因为.dump不包含列名，因此插入失败(缺少值)。

我想要寻找的是某种方法来强制sqlite3 DB .dump输出包含所有字段名的INSERT语句(通常依赖于位置)，或者(这是不可能的)某种方式告诉sqlite3 DB <new_d将任何未定义的字段考虑为null或默认值(没有失败)。

实现同样结果的任何其他方式(不需要知道究竟修改了什么)也同样受到欢迎。

Answer 1

为了能够用较少的列插入/导入转储到表中，您可以为新的附加列提供默认值，也可以简单地将它们设置为NULL。约束子句对于CREATE TABLE和ALTER TABLE是相同的。

http://www.sqlite.org/syntax/column-constraint.html

-- newColumn is set to a default value if not provided with INSERT
alter table myTable
add column newColumn INTEGER NOT NULL default 0;

-- newColumn may be NULL, which is the default if not provided with INSERT
alter table myTable
add column newColumn INTEGER;

-- It is also valid to combine NULL and DEFAULT constraints
alter table myTable
add column newColumn INTEGER default 0;

注意，为了使INSERT语句与新列一起工作，它必须提供列名。