Conditional relationships (?)

[zarch]

Dear all,

I would like to write a gateway that read a signal from a sensor using a supported protocol and send the read value using other different protocols. I tried three different approaches:

• (Not working) a dynamically ForeignKey conditional relation;
• (working) a ForeignKey for each input and output protocols
• (working) a ForeignKey using a JSON column to mask the different protocol attributes

Do you see better options? Based on your experience which one it seems better to you?

Dynamically ForeignKey conditional relation

Lets see the code:

from enum import Enum
from typing import Union
from piccolo.table import Table
from piccolo.columns import UUID, ForeignKey, Varchar


class Modbus(Table):
    id = UUID(primary_key=True)
    # ...Modbus relatated columns

class MQTT(Table):
    id = UUID(primary_key=True)
    # ...MQTT relatated columns

class OPCUA(Table):
    id = UUID(primary_key=True)
    # ...OPCUA relatated columns

class CANbus(Table):
    id = UUID(primary_key=True)
    # ...CAN bus relatated columns

class BACnet(Table):
    id = UUID(primary_key=True)
    # ...BACnet relatated columns
    
class REST(Table):
    id = UUID(primary_key=True)
    # ...REST relatated columns

class ProtocolType(Enum):
    Modbus
    MQTT
    OPCUA
    CANbus
    BACnet
    REST

Protocol = Union[Modbus, MQTT, OPCUA, CANbus, BACnet, REST]


class Signal(Table):
    id = UUID(primary_key=True)
    name = Varchar(length=128)
    
    # input protocol
    itype = Varchar(
        length=16, 
        choices=ProtocolType,
    )
    iprot = ForeignKey(itype)
    
    # output protocol
    otype = Varchar(
        length=16, 
        choices=ProtocolType,
    )
    oprot = ForeignKey(otype)

Of course, this raise an error: ValueError: Error - ``references`` must be a ``Table`` subclass, or a ``LazyTableReference`` instance.

ForeignKey for each input and output protocols

An alternative could be to define a input and output ForeignKey for every protocol, something like:

class SignalFK(Table):
    id = UUID(primary_key=True)
    name = Varchar(length=128)
    
    # define input protocol to be used (only one should be set)
    i_modbus = ForeignKey(Modbus, null=True)
    i_mqtt = ForeignKey(MQTT, null=True)
    i_opcua = ForeignKey(OPCUA, null=True)
    i_canbus = ForeignKey(CANbus, null=True)
    i_bacnet = ForeignKey(BACnet, null=True)
    i_rest = ForeignKey(REST, null=True)
    # eventually add a constrain to check that one and only one is set
    
    # define output protocols to be used (more than one are possible)
    o_modbus = ForeignKey(Modbus, null=True)
    o_mqtt = ForeignKey(MQTT, null=True)
    o_opcua = ForeignKey(OPCUA, null=True)
    o_canbus = ForeignKey(CANbus, null=True)
    o_bacnet = ForeignKey(BACnet, null=True)
    o_rest = ForeignKey(REST, null=True)

However, this solution force me to change the table adding a couple of attribute for each protocol.

ForeignKey using a JSON column

Third solution that came to my mind is:

from piccolo.columns import UUID, ForeignKey, Varchar, JSON, LazyTableReference
from piccolo.columns.m2m import M2M

class Protocol(Table):
    id = UUID(primary_key=True)
    ptype = Varchar(
        length=16, 
        choices=ProtocolType,
    )
    options = JSON()


class SignalJSN(Table):
    id = UUID(primary_key=True)
    name = Varchar(length=128)
    
    iprot = ForeignKey(Protocol)
    oprot = M2M(LazyTableReference("Signal2Protocol", module_path=__name__))
    
    
class Signal2Protocol(Table):
    signal = ForeignKey(SignalJSN)
    protocol = ForeignKey(Protocol)

For this last option, it would be possible to define a set of pydantic class to be used for the serailization. In this way I could define a pydantic class for each protocol and then based on Protocol.ptype attribute serialize/deserialize.

[dantownsend]

I think your suggested solutions make sense.

To me, the options which I like the most are probably:

Nullable foreign keys

Having a bunch of nullable foreign keys, and you assign the relevant one. The downside is you end up with lots of foreign keys if you have a lot of protocols, but not the end of the world.

You have a single protocol table, with JSON data

Just as you suggested in your last approach. It means you can a single foreign key, and store the information about the particular protocol in a JSON field.

A small hint - use JSONB instead of JSON if possible, it's better for querying that plain JSON.