search

IBM / dse-decision-optimization-utilities

A Python package with various utilities that speed-up development of Decision Optimization projects in Watson Studio. Implements common tasks and provides best-practice guidelines to architect DO projects.
Apache License 2.0
4 stars 7 forks source link

ScenarioDbManager using DB constraint naming_convention #54

Closed victorterpstra closed 1 year ago

victorterpstra commented 1 year ago

The sqlalchemy.MetaData(schema=schema, naming_convention=convention) has the option to provide a naming convention for constraints. See https://docs.sqlalchemy.org/en/20/core/constraints.html#constraint-naming-conventions

The advantage is that when getting constraint violations, the error message is much easier to decode. By default, the DB will create names based on unique integer IDs, which are meaningless. You would have to use tools like DBeaver to find the constraint and check what tables and fields are involved.

Problem: The SQLAlchemy docs suggest a convention. However, this fails for our supply-chain use-case. In particular with a table where we have 2 FK constraints to the same table, e.g. the ShippingLane table has originLocationName and destinationLocationName pointing to the same Location table. The naming convention duplicate names.

TODO: find a naming convention that supports all use-cases of FK constraints

victorterpstra commented 1 year ago

Implemented in 0.5.4.6b1 Added flag use_custom_naming_convention in ScenarioDbManager.init() to enable the custom naming. If True, will call the method get_custom_naming_convention get get a Dict with the naming. Can be overridden by subclass to customize. Default implementation on ScenarioDbManager class uses variation to allow 2 or more FKs to same table.

Warning: default customization is not guaranteed to have unique names. Therefore, the ability to disable.