CommandNotFound database foreign key constraint fail

[Libor Martinek]

```
$ lsb_release -rd
Description: Ubuntu 20.04.3 LTS
Release: 20.04
```

I have compiled sqlite3 with enabled foreign keys support - https://sqlite.org/foreignkeys.html and replaced the system provided sqlite3 shared libraries with my own (maybe unwise, but that's for another discussion :))

When I wanted to fetch latest `apt` changes, I got the following error:

$ sudo apt update
Hit:1 https://aquasecurity.github.io/trivy-repo/deb focal InRelease
...
Traceback (most recent call last):
  File "/usr/lib/cnf-update-db", line 26, in <module>
    col.create(db)
  File "/usr/lib/python3/dist-packages/CommandNotFound/db/creator.py", line 104, in create
    raise e
  File "/usr/lib/python3/dist-packages/CommandNotFound/db/creator.py", line 94, in create
    self._fill_commands(con)
  File "/usr/lib/python3/dist-packages/CommandNotFound/db/creator.py", line 138, in _fill_commands
    self._parse_single_commands_file(con, fp)
  File "/usr/lib/python3/dist-packages/CommandNotFound/db/creator.py", line 225, in _parse_single_commands_file
    self._insert_command(con, command, pkg_id)
  File "/usr/lib/python3/dist-packages/CommandNotFound/db/creator.py", line 167, in _insert_command
    con.execute("""
sqlite3.OperationalError: no such table: main.pkgs
```

which is clearly an incorrect naming in the `create_table_sql` in `CommandNotFound/db/creator.py`,
but since sqlite3 does not enforce foreign keys, you can get away with all sorts of funky stuff:

```
sqlite> PRAGMA foreign_keys=OFF;
sqlite> CREATE TABLE tbl1(a, b NOT NULL, FOREIGN KEY (b) REFERENCES "nonexistent" (b));
sqlite>
sqlite> INSERT INTO tbl1 VALUES (1, 2); -- No problem!
```

The first patch attempted was therefore:

```
$ git diff
diff --git a/CommandNotFound/db/creator.py b/CommandNotFound/db/creator.py
index d887c9d..a52be56 100755
--- a/CommandNotFound/db/creator.py
+++ b/CommandNotFound/db/creator.py
@@ -34,7 +34,7 @@ create_db_sql="""
             [cmdID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
             [pkgID] INTEGER NOT NULL,
             [command] TEXT,
- FOREIGN KEY ([pkgID]) REFERENCES "pkgs" ([pkgID])
+ FOREIGN KEY ([pkgID]) REFERENCES "packages" ([pkgID])
            );
            CREATE TABLE IF NOT EXISTS "packages"
            (
```

which resulted in another error in `$ sudo apt update`:

```
Traceback (most recent call last):
  File "/usr/lib/cnf-update-db", line 26, in <module>
    col.create(db)
  File "/usr/lib/python3/dist-packages/CommandNotFound/db/creator.py", line 94, in create
    self._fill_commands(con)
  File "/usr/lib/python3/dist-packages/CommandNotFound/db/creator.py", line 138, in _fill_commands
    self._parse_single_commands_file(con, fp)
  File "/usr/lib/python3/dist-packages/CommandNotFound/db/creator.py", line 210, in _parse_single_commands_file
    self._delete_pkgid(con, already_in_db[0])
  File "/usr/lib/python3/dist-packages/CommandNotFound/db/creator.py", line 152, in _delete_pkgid
    con.execute("DELETE FROM packages WHERE pkgID=?", (pkgid,) )
sqlite3.IntegrityError: FOREIGN KEY constraint failed
```

which is understandable, since default as per sqlite docs for `FOREIGN KEY` is `NO ACTION`. Final patch is thus adding `ON UPDATE CASCADE ON UPDATE DELETE`:

```
$ git diff
diff --git a/CommandNotFound/db/creator.py b/CommandNotFound/db/creator.py
index d887c9d..a52be56 100755
--- a/CommandNotFound/db/creator.py
+++ b/CommandNotFound/db/creator.py
@@ -34,7 +34,7 @@ create_db_sql="""
             [cmdID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
             [pkgID] INTEGER NOT NULL,
             [command] TEXT,
- FOREIGN KEY ([pkgID]) REFERENCES "pkgs" ([pkgID])
+ FOREIGN KEY ([pkgID]) REFERENCES "packages" ([pkgID]) ON UPDATE CASCADE ON DELETE CASCADE
            );
            CREATE TABLE IF NOT EXISTS "packages"
            (
```

Now, as I would expect, `apt update` works as usual:

```
$ sudo apt update
...
Reading package lists... Done
Building dependency tree
Reading state information... Done
17 packages can be upgraded. Run 'apt list --upgradable' to see them.
```

Best regards,
Libor
$ sudo apt update
...
Reading package lists... Done
Building dependency tree
Reading state information... Done
17 packages can be upgraded. Run 'apt list --upgradable' to see them.
```

Best regards,
Libor

[Ubuntu Foundations Team Bug Bot (crichton)]

The attachment "db_create_sql_bug.patch" seems to be a patch. If it isn't, please remove the "patch" flag from the attachment, remove the "patch" tag, and if you are a member of the ~ubuntu-reviewers, unsubscribe the team.

[This is an automated message performed by a Launchpad user owned by ~brian-murray, for any issues please contact him.]