SQL DDL#

Example Output#

personinfo.sql

(sqlite syntax)

Overview#

Docs#

Command Line#

gen-sqltables#

Generate SQL DDL representation

gen-sqltables [OPTIONS] YAMLFILE

Options

--dialect <dialect>#

SQL-Alchemy dialect, e.g. sqlite, mysql+odbc

Default:

'sqlite'

--sqla-file <sqla_file>#

Path to sqlalchemy generated python

--relmodel-output <relmodel_output>#

Path to intermediate LinkML YAML of transformed relational model

--python-import <python_import>#

Python import header for generated sql-alchemy code

--use-foreign-keys, --no-use-foreign-keys#

Emit FK declarations

Default:

True

-V, --version#

Show the version and exit.

-f, --format <format>#

Output format

Default:

'sql'

Options:

sql

--metadata, --no-metadata#

Include metadata in output

Default:

True

--useuris, --metauris#

Use class and slot URIs over model uris

Default:

True

-im, --importmap <importmap>#

Import mapping file

--log_level <log_level>#

Logging level

Default:

'WARNING'

Options:

CRITICAL | ERROR | WARNING | INFO | DEBUG

-v, --verbose#

Verbosity. Takes precedence over –log_level.

--mergeimports, --no-mergeimports#

Merge imports into source file (default=mergeimports)

--stacktrace, --no-stacktrace#

Print a stack trace when an error occurs

Default:

False

Arguments

YAMLFILE#

Required argument

Code#

class linkml.generators.sqltablegen.SQLTableGenerator(schema: str | ~typing.TextIO | ~linkml_runtime.linkml_model.meta.SchemaDefinition | ~linkml.utils.generator.Generator | ~pathlib.Path, schemaview: ~linkml_runtime.utils.schemaview.SchemaView | None = None, format: str | None = None, metadata: bool = True, useuris: bool | None = None, log_level: int | None = 30, mergeimports: bool | None = True, source_file_date: str | None = None, source_file_size: int | None = None, logger: ~logging.Logger | None = None, verbose: bool | None = None, output: str | None = None, namespaces: ~linkml_runtime.utils.namespaces.Namespaces | None = None, directory_output: bool = False, base_dir: str = None, metamodel_name_map: ~typing.Dict[str, str] = None, importmap: str | ~typing.Mapping[str, str] | None = None, emit_prefixes: ~typing.Set[str] = <factory>, metamodel: ~linkml.utils.schemaloader.SchemaLoader = None, stacktrace: bool = False, include: str | ~pathlib.Path | ~linkml_runtime.linkml_model.meta.SchemaDefinition | None = None, use_inherits: bool = False, dialect: str = 'sqlite', inject_primary_keys: bool = True, use_foreign_keys: bool = True, rename_foreign_keys: bool = False, direct_mapping: bool = False, relative_slot_num: bool = False, **_kwargs)[source]#

A Generator for creating SQL DDL

The basic algorithm for mapping a linkml schema S is as follows:

  • Each schema S corresponds to one database schema D (see SQLSchema)

  • Each Class C in S is mapped to a table T (see SQLTable)

  • Each slot S in each C is mapped to a column Col (see SQLColumn)

if the direct_mapping attribute is set to true, then no further transformations are applied. Note that this means:

  • inline objects are modeled as Text strings

  • multivalued fields are modeled as single Text strings

this direct mapping is useful for simple spreadsheet/denormalized representations of complex data. however, for other applications, additional transformations should occur. these are:

MULTIVALUED SLOTS

The relational model does not have direct representation of lists. These are normalized as follows.

If the range of the slot is a class, and there are no other slots whose range is this class, and the slot is for a class that has a singular primary key, then a backref is added.

E.g. if we have User 0..* Address, then add a field User_id to Address.

When SQLAlchemy bindings are created, a backref mapping is added

If the range of the slot is an enum or type, then a new linktable is created, and a backref added

E.g. if a class User has a multivalues slot alias whose range is a string, then create a table user_aliases, with two columns (1) alias [a string] and (2) a backref to user

Each mapped slot C.S has a range R

ranges that are types (literals):

  • If R is a type, and the slot is NOT multivalued, do a direct type conversion

  • If R is a type, and the slot is multivalued:

    • do not include the mapped column

    • create a new table T_S, with 2 columns: S, and a backref to T

ranges that are classes:

  • Ref = map_class_to_table(R)

  • if R is a class, and the slot is NOT multivalued, and Ref has a singular primary key:

    • Col.type = ForeignKey(Ref.PK)

  • if R is a class, and the slot is NOT multivalued, and Ref has NO singular primary key:

    • add a foreign key C.pk to Ref

    • add a backref C.S => Ref, C.pk

    • remove Col from T

  • If R is a class, and the slot IS multivalued

serialize(**kwargs) → str[source]#

Generate output in the required format

Parameters:

kwargs – Generator specific parameters

Returns:

Generated output