Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Verify Migration fails regarding foreign key #3115

Closed
vanniktech opened this issue Apr 18, 2022 · 2 comments · Fixed by #3203
Closed

Verify Migration fails regarding foreign key #3115

vanniktech opened this issue Apr 18, 2022 · 2 comments · Fixed by #3203
Labels

Comments

@vanniktech
Copy link
Contributor

SQLDelight Version

2.0.0-alpha02

Operating System

Mac

Gradle Version

7.4.2

Kotlin Version

1.6.20

AGP Version

7.1.3

Describe the Bug

I chose to delete my duplicated database files and only use 1.db now when I execute verifySqlDelightMigration and it fails:

> There was a failure while executing work items
   > A failure occurred while executing app.cash.sqldelight.gradle.VerifyMigrationTask$VerifyMigrationAction
      > Error migrating from 1.db, fresh database looks different from migration database:
        /tables[flashCategory]/foreignKeys[]/foreignKeyTable/definition - CHANGED
          BEFORE:
            CREATE TABLE flashDeck (
              id TEXT NOT NULL PRIMARY KEY,
              name TEXT NOT NULL,
              description TEXT,
              color INTEGER NOT NULL,
              created INTEGER NOT NULL,
              updated INTEGER NOT NULL,
              category TEXT,
              dailyReminder INTEGER NOT NULL DEFAULT 1,
              FOREIGN KEY (category) REFERENCES flashCategory(id)
            )
          AFTER:
            CREATE TABLE flashDeck (
              id TEXT NOT NULL PRIMARY KEY,
              name TEXT NOT NULL,
              description TEXT,
              color INTEGER NOT NULL,
              created INTEGER NOT NULL,
              updated INTEGER NOT NULL
            , category TEXT REFERENCES flashCategory(id), dailyReminder INTEGER NOT NULL DEFAULT 1)
        /tables[flashDeck]/definition - CHANGED
          BEFORE:
            CREATE TABLE flashDeck (
              id TEXT NOT NULL PRIMARY KEY,
              name TEXT NOT NULL,
              description TEXT,
              color INTEGER NOT NULL,
              created INTEGER NOT NULL,
              updated INTEGER NOT NULL,
              category TEXT,
              dailyReminder INTEGER NOT NULL DEFAULT 1,
              FOREIGN KEY (category) REFERENCES flashCategory(id)
            )
          AFTER:
            CREATE TABLE flashDeck (
              id TEXT NOT NULL PRIMARY KEY,
              name TEXT NOT NULL,
              description TEXT,
              color INTEGER NOT NULL,
              created INTEGER NOT NULL,
              updated INTEGER NOT NULL
            , category TEXT REFERENCES flashCategory(id), dailyReminder INTEGER NOT NULL DEFAULT 1)
        /tables[flashCard]/foreignKeys[]/primaryKeyTable/definition - CHANGED
          BEFORE:
            CREATE TABLE flashDeck (
              id TEXT NOT NULL PRIMARY KEY,
              name TEXT NOT NULL,
              description TEXT,
              color INTEGER NOT NULL,
              created INTEGER NOT NULL,
              updated INTEGER NOT NULL,
              category TEXT,
              dailyReminder INTEGER NOT NULL DEFAULT 1,
              FOREIGN KEY (category) REFERENCES flashCategory(id)
            )
          AFTER:
            CREATE TABLE flashDeck (
              id TEXT NOT NULL PRIMARY KEY,
              name TEXT NOT NULL,
              description TEXT,
              color INTEGER NOT NULL,
              created INTEGER NOT NULL,
              updated INTEGER NOT NULL
            , category TEXT REFERENCES flashCategory(id), dailyReminder INTEGER NOT NULL DEFAULT 1)
        /tables[flashCard]/tableConstraints[]/primaryKeyTable/definition - CHANGED
          BEFORE:
            CREATE TABLE flashDeck (
              id TEXT NOT NULL PRIMARY KEY,
              name TEXT NOT NULL,
              description TEXT,
              color INTEGER NOT NULL,
              created INTEGER NOT NULL,
              updated INTEGER NOT NULL,
              category TEXT,
              dailyReminder INTEGER NOT NULL DEFAULT 1,
              FOREIGN KEY (category) REFERENCES flashCategory(id)
            )
          AFTER:
            CREATE TABLE flashDeck (
              id TEXT NOT NULL PRIMARY KEY,
              name TEXT NOT NULL,
              description TEXT,
              color INTEGER NOT NULL,
              created INTEGER NOT NULL,
              updated INTEGER NOT NULL
            , category TEXT REFERENCES flashCategory(id), dailyReminder INTEGER NOT NULL DEFAULT 1)

I've tried reproducing in a sample project but I just can't. I've also stopped Gradle and did everything cleanly, still same result.

However looking at the text input while they aren't the same, unless I'm missing something, they seem to be correct and the same.

It's just that the definition for the foreign key is textually different:

-category TEXT,
-FOREIGN KEY (category) REFERENCES flashCategory(id)
+category TEXT REFERENCES flashCategory(id)

I also don't know why this exact text output is listed 4 times:

  • /tables[flashCategory]/foreignKeys[]/foreignKeyTable/definition - CHANGED
  • /tables[flashDeck]/definition - CHANGED
  • /tables[flashCard]/foreignKeys[]/primaryKeyTable/definition - CHANGED
  • /tables[flashCard]/tableConstraints[]/primaryKeyTable/definition - CHANGED

String replacing also seems to fail in the after text:

            , category TEXT REFERENCES flashCategory(id), dailyReminder INTEGER NOT NULL DEFAULT 1)

Stacktrace

No response

Gradle Build Script

No response

@vanniktech vanniktech added the bug label Apr 18, 2022
@vanniktech vanniktech changed the title Migration Verify Migration fails regarding foreign key Apr 18, 2022
@AlecKazakova
Copy link
Collaborator

if you update your .sq version of the schema to match the BEFORE does it work fine? I don't think we'll actually be able to fix this since we're doing string matching instead of anything more intense.

Alternatively, since this check is new we can provide an option to turn off definition matching so it preserves the old behaviour.

@vanniktech
Copy link
Contributor Author

Yup if I change it in my .sq file it works:

-  category TEXT,
-  dailyReminder INTEGER AS Boolean NOT NULL DEFAULT 1,
-  FOREIGN KEY (category) REFERENCES flashCategory(id)
+  category TEXT REFERENCES flashCategory(id),
+  dailyReminder INTEGER AS Boolean NOT NULL DEFAULT 1

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Development

Successfully merging a pull request may close this issue.

2 participants