Grdb.swift: Question: is possible to update a virtual table?

[StriderHND]

Hello

I'm looking at the docs trying to find if its posible to update a virtualTable with the alter method
this is my current virtualTable.

try db.create(virtualTable: "clinBuisness_ft", using: FTS4()) { t in // or FTS5()
            t.synchronize(withTable: "clinBuisness")
            t.tokenizer = .unicode61()
            t.tokenizer = .unicode61(removeDiacritics: true)
            t.column("buisnessName")
            t.column("buisnessDescription")
        }

I wanna add a new column to this table

try db.alter(table: "clinBuisness_ft"){ t in
            //rest of code
           t.add(column: "buisnessCity")
        }

is this valid or there is other way to do this?

Environment

GRDB flavor(s): GRDB
GRDB version: 2.3.1
Installation method: CocoaPods
Xcode version: 9.0 (9A235)
Swift version: 4.0
Platform(s) running GRDB: iOS
macOS version running Xcode:

[groue]

Hello @StriderHND,

No, it is not possible.

According to SQLite documentation:

As with all virtual table types, it is not possible [...] to use the ALTER TABLE command to add extra columns to FTS tables (although it is possible to use ALTER TABLE to rename an FTS table).

You will thus have to drop and recreate the full-text table. Since you use the synchronize(withTable:)method, the full-text index will be recreated automatically from the contents of the clinBuisness table.

[groue]

BTW, thanks @StriderHND: your question has revealed that it was impossible to add an untyped column to a regular (non virtual) table with t.add(column:"name"). This has been fixed in #280.

[StriderHND]

Thanks for the help @groue !

[StriderHND]

Hello @groue

Im trying what you suggested but im getting some errors about the trigger of the table already exist.

Any help ?

migrator.registerMigrationWithDeferredForeignKeyCheck("newfulltextTable") { (db) in
        try db.create(virtualTable: "new_clinBuisness_ft", using: FTS4()) { t in // or FTS5()
            t.synchronize(withTable: "clinBuisness")
            t.tokenizer = .unicode61()
            t.tokenizer = .unicode61(removeDiacritics: true)
            t.column("buisnessName")
            t.column("buisnessDescription")
            t.column("buisnessTagList")
        }
        try db.execute("INSERT INTO new_clinBuisness_ft SELECT * FROM clinBuisness_ft")
        try db.drop(table: "clinBuisness_ft")
        try db.rename(table: "new_clinBuisness_ft", to: "clinBuisness_ft")
    }

2017-11-30 09:13:13.384526-0600 Clin[6190:3233105] [logging] trigger "__clinBuisness_bu" already exists
fatal error: 'try!' expression unexpectedly raised an error: SQLite error 1 with statement `CREATE TRIGGER "__clinBuisness_bu" BEFORE UPDATE ON "clinBuisness" BEGIN
    DELETE FROM "new_clinBuisness_ft" WHERE docid=old."id";
END;
CREATE TRIGGER "__clinBuisness_bd" BEFORE DELETE ON "clinBuisness" BEGIN
    DELETE FROM "new_clinBuisness_ft" WHERE docid=old."id";
END;
CREATE TRIGGER "__clinBuisness_au" AFTER UPDATE ON "clinBuisness" BEGIN
    INSERT INTO "new_clinBuisness_ft"("docid", "buisnessName", "buisnessDescription", "buisnessTagList") VALUES(new."id", new."buisnessName", new."buisnessDescription", new."buisnessTagList");
END;
CREATE TRIGGER "__clinBuisness_ai" AFTER INSERT ON "clinBuisness" BEGIN
    INSERT INTO "new_clinBuisness_ft"("docid", "buisnessName", "buisnessDescription", "buisnessTagList") VALUES(new."id", new."buisnessName", new."buisnessDescription", new."buisnessTagList");
END;`: trigger "__clinBuisness_bu" already exists: file /Library/Caches/com.apple.xbs/Sources/swiftlang/swiftlang-900.0.65/src/swift/stdlib/public/core/ErrorType.swift, line 181
2017-11-30 09:13:13.387138-0600 Clin[6190:3233105] fatal error: 'try!' expression unexpectedly raised an error: SQLite error 1 with statement `CREATE TRIGGER "__clinBuisness_bu" BEFORE UPDATE ON "clinBuisness" BEGIN
    DELETE FROM "new_clinBuisness_ft" WHERE docid=old."id";
END;
CREATE TRIGGER "__clinBuisness_bd" BEFORE DELETE ON "clinBuisness" BEGIN
    DELETE FROM "new_clinBuisness_ft" WHERE docid=old."id";
END;
CREATE TRIGGER "__clinBuisness_au" AFTER UPDATE ON "clinBuisness" BEGIN
    INSERT INTO "new_clinBuisness_ft"("docid", "buisnessName", "buisnessDescription", "buisnessTagList") VALUES(new."id", new."buisnessName", new."buisnessDescription", new."buisnessTagList");
END;
CREATE TRIGGER "__clinBuisness_ai" AFTER INSERT ON "clinBuisness" BEGIN
    INSERT INTO "new_clinBuisness_ft"("docid", "buisnessName", "buisnessDescription", "buisnessTagList") VALUES(new."id", new."buisnessName", new."buisnessDescription", new."buisnessTagList");
END;`: trigger "__clinBuisness_bu" already exists: file /Library/Caches/com.apple.xbs/Sources/swiftlang/swiftlang-900.0.65/src/swift/stdlib/public/core/ErrorType.swift, line 181

[groue]

Your code looks legit at first sight: I have to investigate why it fails.

Yet you have a simpler option: don't rename the clinBuisness_ft table. Instead, drop it, then create it again with the new column.

[groue]

Your code looks legit at first sight: I have to investigate why it fails.

No it isn't: The INSERT INTO new_clinBuisness_ft SELECT * FROM clinBuisness_ft query directly conflicts with the synchronize(withTable:) method. The documentation says:

The eventual content already present in the regular table is indexed [...]

Maybe this is not clear enough. But this means that virtual tables created with the synchronize(withTable:) method are automatically populated, and synchronized, with the content of the target table clinBuisness. You should thus never insert values directly in the full-text table.

[StriderHND]

No it isn't: The INSERT INTO new_clinBuisness_ft SELECT * FROM clinBuisness_ft query directly conflicts with the synchronize(withTable:) method.

I expected that would happened because I've already tried without the insert line and still getting the same errors.

Maybe this is not clear enough. But this means that virtual tables created with the synchronize(withTable:) method are automatically populated, and synchronized, with the content of the target table clinBuisness. You should thus never insert values directly in the full-text table.

I realize reading the docs, after a made another test, and still have the problem. even just trying to create a new vitualTable and Sync with clinBuisness I get the same errors

migrator.registerMigrationWithDeferredForeignKeyCheck("newfulltextTable") { (db) in
        try db.drop(table: "clinBuisness_ft")

        try db.create(virtualTable: "new_clinBuisness_ft", using: FTS4()) { t in // or FTS5()
            t.synchronize(withTable: "clinBuisness")
            t.tokenizer = .unicode61()
            t.tokenizer = .unicode61(removeDiacritics: true)
            t.column("buisnessName")
            t.column("buisnessDescription")
            t.column("buisnessTagList")
        }
    }

Even with just creating a new vitualTable and synchronize that table to clinBuisness tries to create a new trigger that already exist. even if I drop the virtualTable first I still have the same problem, it seems when creating a VirtualTable using Synchronize and then drop that table, the triggers created are not deleted / dropped. it should create new triggers because is a different virtualTable.

[groue]

All right, @StriderHND, you've found a bug: it's currently impossible to recreate a virtual table, because dropping the table does not drop the triggers formerly created by synchronize(withTable:).

I'll publish a fix shortly.

Meanwhile, you can drop the triggers manually, with DROP TRIGGER __clinBuisness_bu etc. statement. (drop all four triggers).

[StriderHND]

Thanks @groue for the help!

[groue]

@StriderHND, the pull request #281 addresses your concern, and will be merged shortly in a new GRDB version.

Can you please have a look at the new Deleting Synchronized Full-Text Tables documentation chapter, and tell me if everything is crystal clear to you? Don't miss the warning about GRDB <= v2.3.1, because it surely applies to your application.

[groue]

@StriderHND, please upgrade GRDB to v2.4.0. Your migration should now look like:

migrator.registerMigration("newfulltextTable") { db in
    // Add the buisnessCity column to the full-text table clinBuisness_ft.
    // As SQLite does not support altering full-text tables, we need to
    // drop clinBuisness_ft before creating it again.
    try db.drop(table: "clinBuisness_ft")
    try db.dropFTS4SynchronizationTriggers(forTable: "clinBuisness_ft")
    try db.dropFTS4SynchronizationTriggers(forTable: "clinBuisness") // Support for GRDB <= 2.3.1

    try db.create(virtualTable: "clinBuisness_ft", using: FTS4()) { t in
        t.synchronize(withTable: "clinBuisness")
        t.tokenizer = .unicode61(removeDiacritics: true)
        t.column("buisnessName")
        t.column("buisnessDescription")
        t.column("buisnessTagList")
        t.column("buisnessCity")
    }
}

[StriderHND]

@groue, Thanks I just checked the documentation and everything is clear!.

Thanks for the help