Android 6.0.1中SQLite触发器多操作触发语法错误求助

问题根源 #

Android 6.0.1内置的SQLite版本为3.8.10，不支持行值构造器语法（即SET (列名) = (值)这种带括号的写法），这是触发near "(": syntax error的直接原因。同时早期SQLite对触发器内多语句的语法解析更严格，行值语法的错误会连带导致后续语句被误解析，也就是Android Studio提示的END expected, got 'INSERT'。

高版本Android搭载的SQLite版本更高（比如Android 7.0+使用SQLite 3.19.0+），已经支持行值构造器语法，因此不会出现该问题。

修复方案 #

将所有触发器中的行值构造器语法替换为普通的UPDATE赋值语法，同时确保每个SQL语句用分号正确分隔：

修改后的关键触发器代码 #

1. 修正bulk_update_main触发器

CREATE TRIGGER bulk_update_main
BEFORE INSERT ON main
WHEN EXISTS (SELECT pubKey FROM main WHERE pubKey = NEW.pubKey AND age + 0 < NEW.age + 0)
BEGIN
  UPDATE main SET age = NEW.age
    WHERE pubKey = NEW.pubKey;
  INSERT INTO mainCount (age, count_) VALUES (NEW.age, 1);
  SELECT raise(IGNORE);
END;

2. 修正bulk_update_mainCount触发器

CREATE TRIGGER bulk_update_mainCount
BEFORE INSERT ON mainCount
WHEN EXISTS (SELECT * FROM mainCount WHERE age = NEW.age)
BEGIN
  UPDATE mainCount
    SET count_ = count_ + NEW.count_
    WHERE age = NEW.age;
  SELECT raise(IGNORE);
END;

完整的onCreate方法修改版 #

public void onCreate(SQLiteDatabase db) {
    Log.d(LOG_TAG, "--- onCreate database ---");
    //our pubKeys
    db.execSQL("create table users ("
            + "id integer primary key autoincrement,"
            + "pubKey BLOB,"
            + "privKey BLOB" + ");");
    db.execSQL("create table mainCount ("
            + "age integer primary key,"
            + "count_ Long);");
    db.execSQL("create table main ("
            + "pubKey blob primary key,"
            + "age integer);");
    db.execSQL("CREATE INDEX idx_date ON main (age);");
    db.execSQL("CREATE TRIGGER bulk_insert_main" +
            " BEFORE INSERT ON main" +
            " WHEN NOT EXISTS (SELECT pubKey FROM main WHERE pubKey = NEW.pubKey)" +
            " BEGIN" +
            "   INSERT INTO mainCount (age, count_) VALUES (NEW.age, 1);" +
            " END;");
    db.execSQL("CREATE TRIGGER bulk_update_main" +
            " BEFORE INSERT ON main" +
            " WHEN EXISTS (SELECT pubKey FROM main WHERE pubKey = NEW.pubKey AND age + 0 < NEW.age + 0)" +
            " BEGIN" +
            "   UPDATE main SET age = NEW.age" +
            "     WHERE pubKey = NEW.pubKey;" +
            "   INSERT INTO mainCount (age, count_) VALUES (NEW.age, 1);" +
            "   SELECT raise(IGNORE);" +
            " END;");
    db.execSQL("CREATE TRIGGER bulk_new_update_main" +
            " BEFORE UPDATE ON main" +
            " BEGIN" +
            "   INSERT INTO mainCount (age, count_) VALUES (OLD.age, -1);" +
            " END;");
    db.execSQL("CREATE TRIGGER bulk_ignore_main" +
            " BEFORE INSERT ON main" +
            " WHEN EXISTS (SELECT pubKey FROM main WHERE pubKey = NEW.pubKey AND age + 0 >= NEW.age + 0)" +
            " BEGIN" +
            "   SELECT raise(IGNORE);" +
            " END;");
    db.execSQL("CREATE TRIGGER bulk_update_mainCount" +
            " BEFORE INSERT ON mainCount" +
            " WHEN EXISTS (SELECT * FROM mainCount WHERE age = NEW.age)" +
            " BEGIN" +
            "   UPDATE mainCount" +
            "     SET count_ = count_ + NEW.count_" +
            "     WHERE age = NEW.age;" +
            "   SELECT raise(IGNORE);" +
            " END;");
}

验证说明 #

修改后，Android 6.0.1环境下的SQLite可正确解析触发器语法，多语句操作能正常执行；高版本Android环境的兼容性不受影响。

内容的提问来源于stack exchange，提问作者infoman