android - SQLITEASSETHELPER 正确的升级脚本

Question

我不理解文档，我担心如果我做错了，会不小心将我的表丢到我的数据库中并损坏原始文件。

https://github.com/jgilfelt/android-sqlite-asset-helper

关于添加具有值的列。以下代码是通过创建者 git-hub 示例提供的

    -- add a FullNames column to Employees
ALTER TABLE "Employees" RENAME TO 'Employees_ME_TMP';

CREATE TABLE "Employees" 
(
    "EmployeeID" int NOT NULL,
    "LastName" varchar(20) NOT NULL,
    "FirstName" varchar(10) NOT NULL,
    "FullName" varchar(150),
    PRIMARY KEY ("EmployeeID")
);

INSERT INTO "Employees"  
(
    "EmployeeID", 
    "LastName", 
    "FirstName", 
    "FullName"
) 
SELECT 
    "EmployeeID", 
    "LastName", 
    "FirstName", 
    "FirstName" || ' ' || "LastName" 
FROM 
    "Employees_ME_TMP";

DROP TABLE "Employees_ME_TMP";

如果我需要添加两列，然后将一堆值插入到这些列中，这是正确的语法吗？

    -- add a FullNames column and NickName to Employees
ALTER TABLE "Employees" RENAME TO 'Employees_ME_TMP';

CREATE TABLE "Employees" 
(
    "EmployeeID" int NOT NULL,
    "LastName" varchar(20) NOT NULL,
    "FirstName" varchar(10) NOT NULL,
    "FullName" varchar(150),
    "NickName" varchar(150),
    PRIMARY KEY ("EmployeeID")
);

    INSERT INTO Employees (FullName, NickName)
    VALUES ('Tyler Durden','Jack')
    WHERE rowid = 18;

    INSERT INTO Employees (FullName, NickName)
    VALUES ('Donnie Darko','Jake')
    WHERE rowid = 19;

DROP TABLE "Employees_ME_TMP";

Answer 1

documentation说:

The INSERT statement … creates one or more new rows in an existing table.

您的新表是空的，因此您实际上想要创建行，但这些行中的数据必须来自旧表。所以你必须使用 SELECT 形式的 INSERT 语句:

INSERT INTO Employees(EmployeeID, LastName, FirstName)
SELECT EmployeeID, LastName, FirstName FROM Employees_ME_TMP;

INSERT 中未提及的其他列将使用其默认值(在本例中为 NULL)填充。然后您必须将新值放入这些列中。使用 UPDATE statement 完成更改行:

UPDATE Employees
SET FullName = 'Tyler Durden',
    NickName = 'Jack'
WHERE EmployeeID = 18;

UPDATE Employees
SET FullName = 'Donnie Darko',
    NickName = 'Jake'
WHERE EmployeeID = 19;