Custom secondary

[jdkandersson]

As a developer I want to define additional keys for my secondary table so that I can customise what is stored in the database.

• Take secondary table generation out of the model factory and add it as a schema helper
• Add looking for table name that is the same as secondary and verify that all required columns are present
• If not found, construct as normal otherwise amend the existing

A model can act as an association table, as illustrated by the last example on this page: https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html#association-object.

It is not clear what the constraints are. The following is the assumption that is made based on the best knowledge and understanding:

1. Any extra columns are not primary keys. This will be enforced.
2. It is advised that the columns must somehow be automatically generated (e.g. through default, that they are nullable or through a server_default). server_default is currently not supported and would have to be configured separately, e.g. through an alembic migration. Otherwise the SQLAlchemy relationship functionality won't work since additional information for the association would need to be passed. For generality, this will not be enforced but noted in the documentation.
3. There are some limitations with SQLAlchemy if separate relationships to members of the many-to-many relationship are defined from the association model. These will be noted in the documentation but not enforced.

Schema Helper

Needs to create a schema that is added to the schemas with the appropriate table name and columns. Should be similar to how the secondary table is constructed today but defined as a schema.

Currently the type and format of the primary key column are captured. The table name and column name is also noted for the purpose of constructing the foreign key. The following schema should be sufficient:

<association schema name>:
  type: object
  x-tablename: <x-secondary>
  properties:
    <parent tablename>_<parent property name>:
      type: <parent property type>
      x-primary-key: true
      # if the parent property defines a format
      format: <parent property format>
      # if the parent property defines maxLength
      maxLength: <parent property maxLength>
      x-foreign-key: <parent tablename>.<parent property name>
    <child tablename>_<child property name>:
      type: <child property type>
      x-primary-key: true
      # if the child property defines a format
      format: <child property format>
      # if the child property defines maxLength
      maxLength: <child property maxLength>
      x-foreign-key: <child tablename>.<child property name>
  required:
    - <parent tablename>_<parent property name>
    - <child tablename>_<child property name>

Need to note in the changelog that association table columns will now be constructed as primary keys on the table which is a change from how it was done before. There is a need to check whether this is a breaking change.

The schema name will be constructed as the PascalCase of the of the relationship. There needs to be a check whether that clashes with any existing schema name. If so, Assoc will be pre-pended as many times as necessary to resolve any clashes.

Processing Schemas

The following steps are required for processing the schemas:

1. Iterate over all schemas and their properties retaining the parent schema and the schemas in the context and staying within the model properties only
2. Filter for properties that (1) are relationships and (2) are many-to-many relationships
3. Convert the property schema to an association schema
4. Add them to the schemas

Additional Validation

The following additional validation is required:

• Check that all secondary values are unique. This is because there cannot be any overlap between the generated association tables.
• Check that all table names are unique. This is done by retrieving the local x-tablename and checking whether it has been seen before. Ignore single table inheritance schemas.
• Check that if an association table is defined (indicated by that there is a schema with a table name that is equal to a x-secondary value), it is correctly defined. The rules are:
  1. must be of type object,
  2. must have at most 2 primary key properties with the following constraints:
     1. the foreign key references one of the tables in the many to many relationship,
     2. the type, format (if defined) and maxLength (if defined) match the respective value of the property targeted by the foreign key and
     3. the primary key columns appear in the required array.

Validating Pre-Defined Association

Validation

Iterate over all schemas and look for a schema that has the same table name based on the defined artefacts. Careful with implementation, need to use some sort of lookup to avoid O(n^2) time complexity.

For construction of the expected schema, need access to the schemas, the parent schema and the property schema. Need to build a dictionary that is keyed with the secondary value and those items. Then need to iterate over all schemas and filter for those that are in that map.

Need function that takes a schema, schemas and the parent and property schema for a many-to-many schema. This is used to perform validation.

• If it is found, loop through the properties. If more than 2 primary keys are found return a validation error
• loop through the properties and if a primary key is found without a foreign key return a validation error
• loop through the properties and if with a foreign key that is not one of the expected foreign keys return a validation error.
• Loop through the properties looking for duplicate primary key and foreign key combinations. If one is found, return a validation error.
• Loop through the properties and look for primary key properties with one of the expected foreign keys. Check that the type, format and maxLength are as expected. If not, return a validation error.

Amending pre-defined association schema

The association schema construction needs to be changed to accommodate pre-defined association schemas. At this point it is assumed that the validation rules pass.

• If the association schema is already defined and has all expected foreign keys defined as primary key columns, don't do anything.
• If the association schema is pre-defined but does not define all required properties, wrap it into an allOf with the following modifications from when it has not been defined:
  1. skip any properties where a primary key property with the same foreign key has already been defined and
  2. do not include the required array.
• If the association schema is not pre-defined, add it into the schemas

Need to know which foreign keys have already been defined for a given association schema and the name of the root schema (not an inherited schema).

1. Get a set of table names of the associations
2. Iterate over all schemas, filter for the ones that are in the table names of associations and build a map of table name against a list of schema names
3. Convert each value of that map to have the name of the schema and the foreign keys that are defined
4. Go over the associations and convert the schemas based on the foreign keys that are already defined