Cannot create foreign keys to columns in tables with . in their name

[simonw]

Caused this 500 error:

No such column: graph.henryn

In Sentry:

[simonw]

See also:

• 61

My hunch is that this plugin doesn't work well with all sorts of cases where tables have unconventional characters in their names.

[simonw]

To create a weird.db test database:

echo '{"id": 1, "name": "Sirocco", "species": 582}' | sqlite-utils insert weird.db 'animal.name/with/slashes' - --pk id
echo '{"id": 582, "name": "Kākāpō"}' | sqlite-utils insert weird.db 'table.name/with/slashes.categories' - --pk id

[simonw]

I started with this but it's not quite doing it, need to rethink a bit:

diff --git a/datasette_edit_schema/__init__.py b/datasette_edit_schema/__init__.py
index b71726e..d178c63 100644
--- a/datasette_edit_schema/__init__.py
+++ b/datasette_edit_schema/__init__.py
@@ -548,15 +548,15 @@ async def edit_schema_table(request, datasette):
                         info["foreign_key"].other_column,
                     ),
                     "value": "{}.{}".format(
-                        info["foreign_key"].other_table,
-                        info["foreign_key"].other_column,
+                        tilde_encode(info["foreign_key"].other_table),
+                        tilde_encode(info["foreign_key"].other_column),
                     ),
                     "selected": True,
                 }
             )
             seen.add(
                 "{}:{}".format(
-                    info["foreign_key"].other_table, info["foreign_key"].other_column
+                    tilde_encode(info["foreign_key"].other_table), tilde_encode(info["foreign_key"].other_column)
                 )
             )
         # Now add suggestions
@@ -574,15 +574,15 @@ async def edit_schema_table(request, datasette):
                         "selected": False,
                     }
                 )
-                seen.add("{}:{}".format(suggested_table, suggested_column))
+                seen.add("{}:{}".format(tilde_encode(suggested_table), tilde_encode(suggested_column)))
                 info["suggested"] = "{}.{}".format(suggested_table, suggested_column)
         # And the rest
         for rest_table, rest_column in info["options"]:
-            if "{}:{}".format(rest_table, rest_column) not in seen:
+            if "{}:{}".format(tilde_encode(rest_table), tilde_encode(rest_column)) not in seen:
                 options.append(
                     {
                         "name": "{}.{}".format(rest_table, rest_column),
-                        "value": "{}.{}".format(rest_table, rest_column),
+                        "value": "{}.{}".format(tilde_encode(rest_table), tilde_encode(rest_column)),
                         "selected": False,
                     }
                 )

[simonw]

Relevant code

The code that displays the update foreign keys form: https://github.com/simonw/datasette-edit-schema/blob/5bc7680f5aeba3587df188b26dd893c219fef974/datasette_edit_schema/templates/edit_schema_table.html#L159-L176

The code that populates that template context: https://github.com/simonw/datasette-edit-schema/blob/5bc7680f5aeba3587df188b26dd893c219fef974/datasette_edit_schema/__init__.py#L487-L501

And: https://github.com/simonw/datasette-edit-schema/blob/5bc7680f5aeba3587df188b26dd893c219fef974/datasette_edit_schema/__init__.py#L537-L588

The code that processes that form submission: https://github.com/simonw/datasette-edit-schema/blob/5bc7680f5aeba3587df188b26dd893c219fef974/datasette_edit_schema/__init__.py#L763-L809

[simonw]

The current design for this has option values with table.column_name - which breaks if the table itself includes . characters.

I can use tild_encode() to fix this:

>>> from datasette.utils import tilde_encode
>>> tilde_encode('foo.bar')
'foo~2Ebar'

I'm going to update all of those values to use tilde_encode(table) + '.' + tilde_encode(column).

[simonw]

I also manually tested this against the table where I originally found the bug and it fixed it.