`KeyError` raised for table with multiple foreign primary keys

[kamakazikamikaze]

Using Emmett v2.5.13

The Issue

I have two tables defined as follows:

class Badge(Model):
    wmac = Field.string(length=12, validation={"len": {"range": (12, 13)}})
    bmac = Field.string(length=12, validation={"len": {"range": (12, 13)}})
    secret = Field.string(length=64, validation={"len": {"range": (64, 65)}})
    primary_keys = ["wmac", "bmac"]
    indexes = {"secret_value": {"fields": ["secret"]}}
    has_one({"activity": "Activity"})

class Activity(Model):
    tablename = "activity"
    updated_at = Field.datetime(default=datetime(2000, 1, 1))
    alive = Field.bool(default=False)
    primary_keys = [
        "badge_wmac",
        "badge_bmac",
    ]  # Column name is table, unless multiple keys used. Then it's TABLE.KEY1, TABLE.KEY2, ...
    belongs_to("badge")

Their definition is intended to enforce a 1:1 relationship so that an Activity entry cannot exist without first registering a Badge entry. I have noticed two different behaviors depending on the database used as the backend, however they both appear to be caused from Foreign Key definitions.

SQLite

When attempting to generate a migration or launch via emmett develop, a KeyError is immediately raised:

> Starting Emmett development server on app app
> Emmett application app running on http://127.0.0.1:8000 (press CTRL+C to quit)
> Restarting (stat mode)
> DEBUG in migrator [C:\...\Lib\site-packages\pydal\migrator.py:67]:
Error: 'Database' object has no attribute 'badges.wmac'
> DEBUG in migrator [C:\...\Lib\site-packages\pydal\migrator.py:67]:
Error: 'Database' object has no attribute 'badges.bmac'
Traceback (most recent call last):
  File "<frozen runpy>", line 198, in _run_module_as_main
  File "<frozen runpy>", line 88, in _run_code
  File "C:\...\Scripts\emmett.exe\__main__.py", line 7, in <module>
  File "C:\...\Lib\site-packages\emmett\cli.py", line 525, in main
    cli.main(prog_name="python -m emmett" if as_module else None)
  File "C:\...\Lib\site-packages\emmett\cli.py", line 236, in main
    return super().main(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\...\Lib\site-packages\click\core.py", line 1078, in main
    rv = self.invoke(ctx)
         ^^^^^^^^^^^^^^^^
  File "C:\...\Lib\site-packages\click\core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\...\Lib\site-packages\click\core.py", line 1434, in invoke
    return ctx.invoke(self.callback, **ctx.params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\...\Lib\site-packages\click\core.py", line 783, in invoke
    return __callback(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\...\Lib\site-packages\click\decorators.py", line 92, in new_func
    return ctx.invoke(f, obj, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\...\Lib\site-packages\click\core.py", line 783, in invoke
    return __callback(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\...\Lib\site-packages\emmett\cli.py", line 287, in develop_command
    runner(
  File "C:\...\Lib\site-packages\emmett\_reloader.py", line 172, in run_with_reloader
    locate_app(*app_target)
  File "C:\...\Lib\site-packages\emmett\_internal.py", line 283, in locate_app
    module = get_app_module(module_name, raise_on_failure=raise_on_failure)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\...\Lib\site-packages\emmett\_internal.py", line 247, in get_app_module
    __import__(module_name)
  File "C:\Users\acr\Documents\workspace\darkstarBadge\dev\app.py", line 40, in <module>
    db.define_models(Badge, Activity)
  File "C:\...\Lib\site-packages\emmett\orm\base.py", line 202, in define_models
    model.table = self.define_table(
                  ^^^^^^^^^^^^^^^^^^
  File "C:\...\Lib\site-packages\pydal\base.py", line 587, in define_table
    table = self.lazy_define_table(tablename, *fields, **args)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\...\Lib\site-packages\pydal\base.py", line 618, in lazy_define_table
    self._adapter.create_table(
  File "C:\...\Lib\site-packages\pydal\adapters\base.py", line 795, in create_table
    return self.migrator.create_table(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\...\Lib\site-packages\pydal\migrator.py", line 229, in create_table
    types['reference TFK'] % dict(
    ~~~~~^^^^^^^^^^^^^^^^^
KeyError: 'reference TFK'

Postgres

When Postgres is defined as the back-end, no errors are raised with migrations. In fact, I can create a Badge entry and then attempt to create an associated Activity entry but this gets raised:

  File "/app/server.py", line 586, in battlespace
    Activity.create(
  File "/venv/lib/python3.12/site-packages/emmett/orm/models.py", line 961, in create
    return cls.table.validate_and_insert(skip_callbacks=skip_callbacks, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.12/site-packages/emmett/orm/objects.py", line 164, in validate_and_insert
    response.id = self.insert(skip_callbacks=skip_callbacks, **new_fields)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.12/site-packages/emmett/orm/objects.py", line 143, in insert
    ret = self._db._adapter.insert(self, row.op_values())
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.12/site-packages/emmett/orm/adapters.py", line 62, in wrapped
    return f(adapter, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.12/site-packages/emmett/orm/adapters.py", line 149, in insert
    rid = typed_row_reference(id, table)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.12/site-packages/emmett/orm/helpers.py", line 555, in typed_row_reference
    return {
           ^
  File "/venv/lib/python3.12/site-packages/emmett/orm/helpers.py", line 138, in __new__
    tuple.__setattr__(rv, '_refmeta', RowReferenceMultiMeta(table))
                                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.12/site-packages/emmett/orm/helpers.py", line 57, in __init__
    self.casters = {pk: self._casters[table[pk].type] for pk in self.pks}
                        ~~~~~~~~~~~~~^^^^^^^^^^^^^^^^
KeyError: 'reference badges.wmac'

I can wrap this with a try-except statement and perform a db.commit() action anyways with the results being correctly recorded to the database. However, triggers (after_insert and after_update) do not seem to kick off, likely due to the exception.

Additional notes

• If I restrict Badge to a single primary key, this issue is not raised.

[gi0baro]

For the SQLite error, that's because the pyDAL dialect doesn't support the statement (not sure if it's just pyDAL or SQLite itself that doesn't support that kind of constraint).

For the postgreSQL one, as of today there's no support in Emmett ORM to define reference columns as primary keys. You will waste a column in Activity, but removing the primary_keys definition and adding an unique index on badge will ensure 1:1 relationship.

[kamakazikamikaze]

Understood. Had to add "unique": True when setting the index and now everything is working as designed. Appreciate it!