"DROP VIEW IF EXISTS" not working

[devpolant]

I try to get a list of all views in my database:

var config = Configuration()
            config.trace = { print($0) }

            let queue = try DatabaseQueue(path: downloadedDatabaseURL.path, configuration: config)
            try queue.inDatabase { db in
                let rows = try Row.fetchAll(db, """
                    SELECT name, type, sql FROM sqlite_master
                    WHERE type != 'index' AND name NOT IN ('sqlite_sequence') AND name NOT LIKE '%old%'
                    ORDER BY type, name
                    """
                )
                var views = [Row]()
                for row in rows {
                    let type: String = row["type"]
                    switch type {
                    case "view":
                        views.append(row)
                    default:
                        break
                    }
                }
                for view in views {
                    try updateView(view, in: db)
                }

But I can't drop the view:

private func updateView(_ viewInfo: Row, in db: Database) throws {
        let name: String = viewInfo["name"]
        try db.execute("DROP VIEW IF EXISTS \(name);")
       // ...
}

In trace I see the following SQL:
DROP VIEW IF EXISTS EMAIL_TABLE;

The error did not throw, but the view actually did not delete from the database file on the disk.

But then when I try to create a view with this name (EMAIL_TABLE), I receive an error:
[logging] table EMAIL_TABLE already exists

Maybe I missing something?
GRDB version 2.1.0
Tested on simulator and iPhone 5 (iOS 10.3.3)

Thanks!

[groue]

Hello @polant,

No, you are not missing anything, and you have found a bug. It will be fixed shortly: stay tuned.

You may wonder how it is possible that DROP VIEW statements could fail. I would raise an eyebrow as well...

Well, in order to notify transaction observers of deleted rows, GRDB has to prevent the SQLite truncate optimization. This requires a funny little dance with SQLite Compile-Time Authorization Callbacks. When misused, SQLite doesn't drop table, and views.

The missing tests have been added in a0e3d4a: there won't be any regression once the fix has shipped.

Thanks for the report!

[devpolant]

Thanks!

[groue]

The bug is fixed. Until a new version is shipped, you can use the development branch.

[groue]

Version 2.2.0 has shipped with the fix. Happy GRDB!