Tests fail in yesod-sqlite due to changes in new version of sqlite3

[frt]

Looks like the behavior of PRAGMA foreign_keys=OFF have changed after sqlite-3.9, I am having this issue using sqlite-3.11.0.

[paul-rouse]

The problem is not the sqlite3 version, but rather the version of persistent-sqlite - version 2.6.2 enforced foreign key checks and broke wipeDB in the tests. The tests as distributed don't populate the tables with any foreign keys, so this bug is not apparent immediately after creating a new scaffolding. However, simply creating an entry in the email table whenever a user is created is sufficient to reproduce the issue.

What happens is this. wipeDB uses a low-level "raw" connection to set PRAGMA foreign_keys = OFF and then wraps that same connection into a form suitable for persistent, using wrapConnection, in the hope that the foreign_keys setting still applies. Unfortunately, wrapConnection sets its own idea of how foreign keys should be handled, which is ON.

Instead, in persistent-sqlite>=2.6.2 you need to use wrapConnectionInfo with an SqliteConnectionInfo which sets the foreign keys checking OFF.

For a new scaffolded site, you can change the code to something like:

import Database.Persist.Sqlite (wrapConnectionInfo, mkSqliteConnectionInfo, fkEnabled)
import Lens.Micro              (set)
...
wipeDB app = do
    ...
    let infoNoFK = set fkEnabled False $ mkSqliteConnectionInfo ""
        wrapper = wrapConnectionInfo infoNoFK sqliteConn
    pool <- runLoggingT (createSqlPool wrapper 1) logFunc

I'll do a PR, but it looks as if it will be a bit of a conditional-compilation mess, since we need to support earlier versions of persistent as well.

[paul-rouse]

Actually, do we need to support earlier versions? What's the policy for the scaffolding @snoyberg ?

[snoyberg]

I'm fine with dropping support, as long as the latest LTS is supported.

…

On Tue, Jul 18, 2017, 9:10 PM Paul Rouse ***@***.***> wrote: Actually, do we need to support earlier versions? What's the policy for the scaffolding @snoyberg <https://github.com/snoyberg> ? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#155 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AADBB4zucySlQwLDSVFUR4piNG02tAetks5sPPUIgaJpZM4Obb8y> .

[paul-rouse]

OK - cleaned up code in PR #156, which requires persistent-sqlite >= 2.6.2

[MaxGabriel]

Closing as this looks fixed