Skip to content

# From SQL Developer LDM to `bird_data_model.py`

Jump to bottom
RBirdwatcher edited this page Aug 2, 2026 · 1 revision

From SQL Developer LDM to bird_data_model.py

How the BIRD Logical Data Model, exported from Oracle SQL Developer Data Modeler as a set of CSV files, becomes an executable Django model - and from there a relational database.

1. The short version

flowchart LR
  csv["SQL_Developer_CSV_export"]
  blueprint["Model_blueprint<br/>in_memory_object_graph"]
  models["results/.../models.py"]
  bird["pybirdai/models/bird_data_model.py"]
  db[("SQLite_or_other_RDBMS")]

  csv -->|"stage_1_build"| blueprint
  blueprint -->|"stage_2_emit"| models
  models -->|"copied_into_the_app"| bird
  bird -->|"makemigrations_and_migrate"| db
Loading

Two stages, one file, then the ORM does the rest.

Stage Question it answers Code
1. Build What does this model mean? import_sqldev_ldm_to_blueprint.py
2. Emit What should the Python look like? emit_django_from_blueprint.py

2. Why do it this way?

Why keep an object-oriented model at all?

BIRD's subject matter is naturally taxonomic. An instrument may be a financial asset instrument, which may be a debt security, which may be a covered bond. Regulatory rules are written at whatever level of that taxonomy they apply to: "all instruments must report a reference date", but "only covered bonds report a cover pool identifier".

If the first thing we did was flatten that taxonomy into wide tables, we would throw away exactly the information the rules are written against. Every later step - transformations, filters, joins, test-data generation - would have to rediscover "is this row a covered bond?" from column values.

So the pipeline keeps the hierarchy: inheritance in, inheritance out.

Why then map it to a relational database?

Because the data itself is relational, the reporting tooling is relational, and banks' source systems are relational. We want both: an object model to reason with and a relational schema to store and query.

That is exactly the problem an ORM exists to solve, so we let Django's ORM own the mapping instead of hand-writing DDL:

  • a class becomes a table,
  • a subclass becomes its own table plus an automatic link to its parent (Django's multi-table inheritance),
  • a ForeignKey becomes a foreign key column and an index,
  • a choices dictionary documents the permitted coded values,
  • makemigrations / migrate generate and apply the schema.

One generated Python file is simultaneously the object model and the definition of the relational schema. There is no second artefact to keep in sync.

Why generate a file instead of building classes in memory?

It is tempting to build real models.Model subclasses on the fly with type(). Don't. Django models register themselves with the app registry the moment they are defined, related_name values must be unique across the whole app, and the migration workflow expects models to be readable from source. A half-built model would be visible to Django before it made sense.

Blueprints deliberately look like Django without being Django. The graph can be incomplete, contradictory or unordered while it is being built, and none of that leaks anywhere. Only when it is finished do we write it out as text.

Why two stages rather than one pass?

The CSV export is flat and unordered. DM_Relations.csv can reference an entity whose row appears later in DM_Entities.csv; a subtype can be listed before its supertype; an arc can name a target that turns out to have been skipped as reference data.

Python has no forward declarations - class LEAF(ROOT) requires ROOT to already exist. So the build stage is allowed to be forgiving and out of order, and the emit stage is responsible for producing something valid and correctly ordered. Separating the two is what keeps either from becoming unmanageable.

3. The input: a SQL Developer CSV export

The exported files live in birds_nest/resources/ldm/.

File What the import takes from it
DM_Entities.csv One row per entity: name, preferred abbreviation, supertype, classification type
DM_Attributes.csv One row per attribute: owning entity, domain or logical type, PK/FK flags, sequence
DM_Domains.csv One row per domain (an enumerated value set), with its synonym
DM_Domain_AVT.csv The members of each domain: the stored code and its description
DM_Relations.csv Relationships: source, target, cardinality, optionality, identifying flag
DM_Classification_Types.csv Names for the classification types used to tag entities
DM_Logical_To_Native.csv Logical type names, used when an attribute has no domain
arcs.csv Disjoint subtyping - see section 5

Two naming conventions matter throughout:

  • Preferred abbreviation becomes the Python class or field name (INSTRMNT_CLLTRL_ASSGNMNT). BIRD uses vowel-dropped abbreviations to stay within database identifier limits.
  • Entity name is the readable long name (Instrument Collateral assignment). It is kept, with spaces turned into underscores, as the Django Meta.verbose_name (Instrument_Collateral_assignment) and is what later stages match logical entities on.

Everything is passed through Utils.make_valid_id(), which strips punctuation and accents, and truncates names over 93 characters with a short hash suffix. The limit is not arbitrary: Django builds permission codenames by prefixing model names, and those have a 100-character ceiling.

Reference data is skipped. Entities classified as Reference data (country lists, currency lists and similar) are not turned into classes - they are already available as domains. This is why the import tolerates relationships and arcs pointing at entities that do not exist.

4. Stage 1: build the model blueprint

The blueprint is a small graph of plain Python objects, defined in pybirdai/model_blueprint/. It is shaped like Django, not like the CSVs and not like an ECore metamodel.

classDiagram
  class ModelPackage {
    name
    classifiers
    annotation_directives
  }
  class ModelClass {
    name
    is_abstract
    superclasses
    members
    entity_metadata
    key_metadata
  }
  class Field {
    name
    data_type
    is_identifier
  }
  class Relationship {
    name
    target
    upper_bound
    opposite
    is_delegate
  }
  class Enumeration {
    name
    values
  }
  class EnumerationValue {
    code
    label
  }
  class DataType {
    name
  }
  class Annotation {
    source
    details
  }

  ModelPackage o-- ModelClass
  ModelPackage o-- Enumeration
  ModelClass o-- Field
  ModelClass o-- Relationship
  ModelClass --> ModelClass : superclass
  Relationship --> ModelClass : target
  Field --> DataType : data_type
  Enumeration --|> DataType
  Enumeration o-- EnumerationValue
  ModelClass o-- Annotation
Loading

The mapping is intentionally boring:

SQL Developer Blueprint Django
Entity ModelClass model class / table
Attribute Field model field / column
Relation Relationship ForeignKey
Domain Enumeration choices dictionary
Domain member EnumerationValue one {code: label} entry
Subtype superclasses Python base class
Arc arc ModelClass + _delegate Relationship extra table + ForeignKey

Build order

SQLDevLDMImport.do_import() runs these steps in order. The order matters: each one relies on what the previous ones put in place.

flowchart TD
  A["import_classification_types"] --> B["add_ldm_classes_to_package"]
  B --> C["load_ldm_relation_metadata"]
  C --> D["add_ldm_forward_engineering_annotations"]
  D --> E["import_disjoint_subtyping_information<br/>arcs_and_delegates"]
  E --> F["set_ldm_super_classes"]
  F --> G["add_ldm_enums_to_package"]
  G --> H["add_ldm_literals_to_enums"]
  H --> I["create_ldm_types_map"]
  I --> J["add_ldm_attributes_to_classes"]
  J --> K["remove_enums_not_used_by_attributes"]
  K --> L["add_ldm_relationships_between_classes"]
  L --> M["remove_duplicate_attributes_in_subclasses"]
  M --> N["mark_root_class_as_entity_group_annotation"]
Loading

Three of these deserve a note:

  • Arcs before ordinary supertypes. set_ldm_super_classes only assigns a supertype to a class that does not already have one. Arc membership is therefore claimed first, and a class's plain SQL Developer supertype is used only if no arc got there first.
  • remove_duplicate_attributes_in_subclasses. SQL Developer's export repeats inherited attributes on every subtype. Python inherits them already, and redeclaring a field on a Django subclass is an error, so any attribute that exists on an ancestor is dropped from the child.
  • remove_enums_not_used_by_attributes. BIRD defines far more domains than any attribute uses. Unused ones would emit thousands of dead choices dictionaries, so they are discarded.

Keys

Every root class gets a generated surrogate key:

INSTRMNT_CLLTRL_ASSGNMNT_uniqueID = models.CharField(..., primary_key=True)

Subclasses get none - Django's multi-table inheritance gives them a primary key that is also the link to their parent. The business key (the composite of reference date, reporting agent, identifier and so on) is not made the database primary key; it is recorded in the annotations instead, where the forward-engineering step can use it. Composite primary keys are awkward in Django and would make every relationship a multi-column join.

5. Arcs and delegates: the interesting part

This is the one genuinely tricky idea in the pipeline, and it is worth understanding because its naming leaks into the generated model, the joins and the ETL.

The problem

An entity can be specialised along several independent axes at once. A balance-sheet-recognised financial asset instrument is specialised:

  • by instrument type,
  • by fair value type,
  • by whether it was taken into possession.

These are orthogonal. A single instrument has a value on each axis simultaneously. SQL Developer models each axis as an arc: a named group of mutually exclusive subtype relationships.

Python and Django only give us single inheritance, which can express exactly one axis. So one axis becomes real inheritance and the others have to be modelled some other way.

The solution

For every arc on an entity that has more than one, the import creates:

  1. an arc class named after the arc,
  2. a {arc}_delegate foreign key on the source class, pointing at the arc class,
  3. arc membership: each entity in the arc gets the arc class as its superclass.

Choosing a subtype along that axis then means pointing the delegate at the right arc member.

flowchart TD
  subgraph axis_by_inheritance["Axis 1 - Python inheritance"]
    FAIT["Financial_asset_instrument_type"]
    BSR["BLNC_SHT_RCGNSD_FNNCL_ASST_INSTRMNT"]
    FAIT --- BSR
  end

  subgraph axis_by_delegate["Axis 2 - arc plus delegate"]
    ARC["Balance_sheet_recognised_financial_asset_instrument_type<br/>arc_class"]
    IFRS["BLNC_SHT_RCGNSD_FNNCL_ASST_INSTRMNT_IFRS"]
    NGAAP["BLNC_SHT_RCGNSD_FNNCL_ASST_INSTRMNT_NGAAP"]
    ARC --- IFRS
    ARC --- NGAAP
  end

  BSR -->|"..._type_delegate"| ARC
Loading

In the generated model this is:

class BLNC_SHT_RCGNSD_FNNCL_ASST_INSTRMNT(Financial_asset_instrument_type):
    Balance_sheet_recognised_financial_asset_instrument_type_delegate = models.ForeignKey(
        "Balance_sheet_recognised_financial_asset_instrument_type", models.SET_NULL, ...)
    Balance_sheet_recognised_financial_asset_instrument_by_fair_value_type_delegate = models.ForeignKey(...)
    Balance_sheet_recognised_financial_asset_instrument_taken_into_possession_type_delegate = models.ForeignKey(...)

class BLNC_SHT_RCGNSD_FNNCL_ASST_INSTRMNT_IFRS(Balance_sheet_recognised_financial_asset_instrument_type):
    ...

One axis via inheritance, three via delegates.

Points worth knowing

  • A single arc is not turned into a delegate. If an entity has only one arc, that arc is the inheritance axis and ordinary subclassing is enough. Only entities with two or more arcs need the delegate machinery.
  • Arc classes are marked abstract in the blueprint but are emitted as normal Django models. The is_abstract flag steers ETL traversal - it tells the subtype exploder not to generate test data for the arc holder itself - but the arc class still gets its own table, because the delegate foreign key has to point at something.
  • The _delegate suffix is a contract. Forward engineering, join generation and the ETL filters all recognise a field by that suffix. Renaming it would break downstream code.

6. Stage 2: emit the Django source

BlueprintToDjango walks the finished blueprint and writes text. It makes no decisions about meaning - all of those were made in stage 1.

Ordering

write_class_and_superclasses_in_correct_order() is a depth-first walk: before writing a class it writes that class's superclass, recursively. This is what turns an unordered graph into a valid Python module.

Type mapping

An attribute's Django field type is decided by the name of its domain, which is built as <domain synonym>_domain - so the domain with synonym MNTRY_NN_NGTV_2D becomes the enumeration MNTRY_NN_NGTV_2D_domain. BIRD uses the synonym prefix as a type convention, and the import follows it:

Enumeration name pattern Blueprint type Django field
String, String_*, STRNG_* String CharField(max_length=255)
Number, RL*, Real_* double FloatField
Monetary*, MNTRY_*, INTGR*, YR*, Non_negative_integers* int BigIntegerField
All_possible_dates*, DT_FLL* Date DateTimeField
BLN* boolean BooleanField
anything else the Enumeration itself CharField with choices

The last row is the common case: most BIRD attributes are coded values, and they become a CharField with a choices dictionary emitted immediately above the field.

What a class looks like

class INSTRMNT_CLLTRL_ASSGNMNT(models.Model):
    test_id = models.CharField("test_id", max_length=255, default=None, blank=True, null=True)
    __bird_annotations__ = {'ldm': {...}}
    INSTRMNT_CLLTRL_ASSGNMNT_uniqueID = models.CharField(..., primary_key=True)
    INSTRMNT_CLLTRL_ASSGNMNT_TYP_domain = {"1": "Loan_and_advance_Collateral_received_assignment", ...}
    INSTRMNT_CLLTRL_ASSGNMNT_TYP = models.CharField(..., choices=INSTRMNT_CLLTRL_ASSGNMNT_TYP_domain, ...)
    PRTCTN_ALLCTD_VL = models.BigIntegerField(...)

    class Meta:
        verbose_name = 'Instrument_Collateral_assignment'
        verbose_name_plural = 'Instrument_Collateral_assignments'
Element Where it comes from Why
test_id added to every root class tags which test fixture a row belongs to, so test data can be grouped and cleaned up
__bird_annotations__ entity_metadata + key_metadata LDM facts with no structural home - see below
*_uniqueID generated for root classes surrogate primary key
*_domain an Enumeration the permitted coded values
Meta.verbose_name the entity's long name the readable name later stages match on

Relationships

A Relationship is emitted as a ForeignKey only when its upper bound is 1. A relation appears on both endpoints in the blueprint; emitting both would give two foreign keys for one link, so only the to-one side becomes a column. The to-many side stays in the blueprint because ETL traversal needs to walk the graph in both directions.

models.ForeignKey("<target>", models.SET_NULL, blank=True, null=True,
                  related_name="<ClassName>_to_<field_name>s")

related_name has to be unique across the whole Django app, hence the class-qualified construction and the 200-character truncation.

Annotations

Some LDM facts have no place in a Django class: the composite business key, which foreign keys are identifying, which domain an attribute came from, which discriminator value identifies a subtype. These are written as a plain class attribute:

__bird_annotations__ = {
    "ldm": {
        "primary_key": [...],
        "foreign_keys": [...],
        "fields": {...},
        "entity_member": {...},
    }
}

This is a documented contract - see specs/BIRD_LDM_ANNOTATIONS_SPEC.md. It is hand-editable, tool-editable, and consumed by the forward-engineering step that folds the LDM into input-layer-shaped models. It is a passive class attribute, so Django ignores it entirely.

7. From generated file to database

The emitter writes to birds_nest/results/database_configuration_files/. The database setup step then copies the result into the app and lets Django take over:

flowchart TD
  A["results/database_configuration_files/models.py"] --> B["pybirdai/models/bird_data_model.py"]
  A2["results/database_configuration_files/admin.py"] --> B2["pybirdai/admin.py"]
  B --> C["manage.py makemigrations pybirdai"]
  C --> D["pybirdai/migrations/0001_initial.py"]
  D --> E["manage.py migrate"]
  E --> F[("db.sqlite3")]
Loading

This is database_setup_first_use.py. It deletes the existing migration and database first, because the model is regenerated wholesale rather than evolved.

How inheritance lands in SQL

Django's multi-table inheritance gives each class in the hierarchy its own table and links a child to its parent with an implicit one-to-one primary key.

flowchart TD
  subgraph python["Python classes"]
    P1["INSTRMNT_CLLTRL_ASSGNMNT"]
    P2["RVRS_RPRCHS_TRNSCTN_GLD_CLLTRL_RCVD_ASSGNMNT"]
    P1 --- P2
  end

  subgraph sql["Database tables"]
    T1["pybirdai_instrmnt_clltrl_assgnmnt<br/>uniqueID_PK, TYP, PRTCTN_ALLCTD_VL"]
    T2["pybirdai_rvrs_rprchs_..._assgnmnt<br/>parent_ptr_PK_FK, own_columns"]
    T2 -->|"parent_ptr_id"| T1
  end

  P1 -.-> T1
  P2 -.-> T2
Loading

The practical consequences:

  • Querying the parent model returns all rows, including subtype rows - "give me every instrument" works.
  • Querying a subtype joins to the parent automatically - inherited columns are still readable.
  • Columns are stored once, where they are declared. No wide sparse tables.

This is the payoff for keeping the object model: the taxonomy survives all the way into the database, and it did not cost a line of SQL.

8. Running it

The whole import is one entry point, create_django_models.py:

# Stage one: build the model blueprint from the SQL Developer CSVs.
SQLDevLDMImport.do_import(self, context)

# Stage two: emit Django source from the finished blueprint.
BlueprintToDjango.convert(self, context)

In practice it is triggered from the PyBIRD AI web interface (automode setup, or the Create Django Models step), which then runs the database setup described above. It can also be driven directly:

cd birds_nest
python -c "
import os, django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'birds_nest.settings')
django.setup()
from pybirdai.entry_points.create_django_models import RunCreateDjangoModels
RunCreateDjangoModels('pybirdai', 'birds_nest').ready()
"

Inputs are read from birds_nest/resources/ldm/; outputs are written to birds_nest/results/database_configuration_files/.

Scale

A full BIRD LDM import produces, indicatively:

Count
Classes ~646 (199 root, 447 subclasses)
Arc classes and their delegates 46
Ordinary foreign keys ~400
choices dictionaries ~1500
Classes carrying annotations ~570

The generated bird_data_model.py is a few megabytes. That is why every later step reads it by parsing the source with Python's ast module rather than importing it - importing requires a fully configured Django environment, and the tooling needs to inspect the model without one.

9. The other consumer: ETL test data

The same blueprint feeds a second output. When context.generate_etl is on, the SubtypeExploder walks the graph and enumerates every valid combination of subtypes - every combination of delegate targets and direct subclasses - and writes them as discriminator combination CSVs in results/csv/. Those drive GenerateETL.

flowchart LR
  blueprint["Model_blueprint"]
  django["bird_data_model.py"]
  disc["Discriminator_combination_CSVs"]
  etl["GenerateETL"]
  blueprint --> django
  blueprint --> disc
  disc --> etl
Loading

This is the second reason the object graph is worth building. Enumerating "all the kinds of thing BIRD can describe" is a question about a taxonomy; it is easy against an inheritance graph and very hard against flat tables.

10. Where things live

Path Role
pybirdai/model_blueprint/ The blueprint types, shared by both stages
.../import_sqldev_ldm_to_blueprint.py Stage 1, LDM
.../import_sqldev_il_to_blueprint.py Stage 1, input layer
.../emit_django_from_blueprint.py Stage 2
.../ldm_annotation_enricher.py Adds LDM annotations to an already generated file
generate_test_data/traverser.py Subtype explosion for ETL
context/context.py Packages, primitive types, import settings
entry_points/create_django_models.py Runs both stages
specs/BIRD_LDM_ANNOTATIONS_SPEC.md The __bird_annotations__ contract

A note on history

Earlier versions used an in-memory ECore-style metamodel (RegDNA, built on the pyecore library) as the intermediate, and could also export .xcore files for Eclipse tooling. The valuable part was never ECore itself - it was having an object graph with arcs and delegates between the CSVs and the generated Django code. That role is now played by the model blueprint, which is plain Python, named after Django concepts, and has no external dependency.

Clone this wiki locally