SQL Alchemy
===========

`SQL Alchemy <https://docs.sqlalchemy.org/>`_ is an Object Relational
Mapper (ORM) for Python

The SQL Alchemy generator will generate Python code for mapping an
Object Model to a SQL Database. The structure of the SQL Database
structure is consistent with the one generated by :doc:`sqltable`

In principle, two styles of mappings are possible:

1. Declarative, in which SQLA-style classes are generated, with
   combined mappings
2. Imperative, in which separate mappings are created for a
   pre-existing object model, such as one created by the existing
   python or pydantic generators

Currently only the Declarative style is fully supported   

Overview
--------


Declarative Mode
^^^^^^^^^^^^^^^^

See `declarative mapping docs <https://docs.sqlalchemy.org/en/latest/orm/declarative_mapping.html>`_ on SQL Alchemy site

Given input such as:

.. code:: yaml

    Person:
      is_a: NamedThing
      description: >-
        A person (alive, dead, undead, or fictional).
      class_uri: schema:Person
      mixins:
        - HasAliases
      slots:
        - primary_email
        - birth_date
        - age_in_years
        - gender
        - current_address
        - has_employment_history
        - has_familial_relationships
        - has_medical_history
    Event:
      slots:
        - started_at_time
        - ended_at_time
        - duration
        - is_current
    MedicalEvent:
      is_a: Event
      slots:
        - in_location
        - diagnosis
        - procedure
          

The generated Python will look like:          
          
.. code:: python

    class Person(NamedThing):
        """
        A person (alive, dead, undead, or fictional).
        """
        __tablename__ = 'Person'
        
        primary_email = Column(Text())
        birth_date = Column(Text())
        age_in_years = Column(Integer())
        gender = Column(Enum('nonbinary man', 'nonbinary woman', 'transgender woman', 'transgender man', 'cisgender man', 'cisgender woman', name='GenderType'))
        id = Column(Text(), primary_key=True)
        name = Column(Text())
        description = Column(Text())
        image = Column(Text())
        Container_id = Column(Text(), ForeignKey('Container.id'))
        current_address_id = Column(Text(), ForeignKey('Address.id'))
        current_address = relationship("Address", uselist=False)
        has_employment_history = relationship( "EmploymentEvent", foreign_keys="[EmploymentEvent.Person_id]")
        has_familial_relationships = relationship( "FamilialRelationship", foreign_keys="[FamilialRelationship.Person_id]")
        has_medical_history = relationship( "MedicalEvent", foreign_keys="[MedicalEvent.Person_id]")
        aliases_rel = relationship( "Person_alias" )
        aliases = association_proxy("aliases_rel", "alias",
                                      creator=lambda x_: Person_alias(alias=x_))
        has_news_events = relationship( "NewsEvent", secondary="Person_has_news_event")
          

The first line of the class is a `Declarative Table
<https://docs.sqlalchemy.org/en/14/orm/declarative_tables.html>`_
declaration, the maps the class to the table name. This should
correspond to the table from the SQL DDL generator.

The use of the relationship construct is described in `mapped
properties
<https://docs.sqlalchemy.org/en/14/orm/declarative_config.html>`_
section of the SQLA docs.

Note that class slots are "rolled down".
        
Docs
----

Command Line
^^^^^^^^^^^^

.. currentmodule:: linkml.generators.sqlalchemygen

.. click:: linkml.generators.sqlalchemygen:cli
    :prog: gen-sqla
    :nested: short

Code
^^^^

                   
.. autoclass:: SQLAlchemyGenerator
    :members: serialize