Simple DDL Generator

What is it?

Simple DDL Generator generate SQL DDL from 3 different inputs. Idea of the generator same as for parser to support as much as possible DDLs in future.

Simple DDL Generator generate SQL DDL from 3 input formats - 1st from output Simple DDL Parser (https://github.com/xnuinside/simple-ddl-parser), 2nd from py-models-parser - https://github.com/xnuinside/py-models-parser. Or you can directly pass TableMeta classes (https://github.com/xnuinside/table-meta) to generator

Now DDL support pure SQL DDL diclaect and bunch of HQL statements.

Generate DDL from Django, SQLAlchemy, Dataclasses, Pydantic models and other

Generator can generate DDL from all models that supported & parsed by https://github.com/xnuinside/py-models-parser.

If you need DDL generation from another Python Model types - open issue request to add support for this models in parser.

How to use

As usually - more samples in tests/

    pip install simple-ddl-generator

Generate / Modify using existed DDL with Simple-DDL-Parser

Sample how you can modify your DDL using Simple DDL Parser & Simple DDL Parser

    from simple_ddl_generator import DDLGenerator
    from simple_ddl_parser import DDLParser

    # take initial DDL
    ddl = """CREATE EXTERNAL TABLE IF NOT EXISTS database.table_name
        (
            day_long_nm     string,
            calendar_dt     date,
            source_batch_id string,
            field_qty       decimal(10, 0),
            field_bool      boolean,
            field_float     float,
            create_tmst     timestamp,
            field_double    double,
            field_long      bigint
        ) PARTITIONED BY (batch_id int);"""
    # get result from parser
    data = DDLParser(ddl).run(group_by_type=True, output_mode="bigquery")

    # rename, for example, table name

    data["tables"][0]["table_name"] = "new_table_name"
    g = DDLGenerator(data)
    g.generate()
    print(g.result)

    # and result will be:

    """
    CREATE EXTERNAL TABLE "database.new_table_name" (
    day_long_nm string,
    calendar_dt date,
    source_batch_id string,
    field_qty decimal(10, 0),
    field_bool boolean,
    field_float float,
    create_tmst timestamp,
    field_double double,
    field_long bigint)
    PARTITIONED BY (batch_id int);
    """

Generate DDL from various Python Models with py-models-parser

    from simple_ddl_generator import DDLGenerator
    from py_models_parser import parse

    # you can also read them from file
    model_from = """
        class Material(BaseModel):

            id: int
            title: str
            description: Optional[str]
            link: str = 'http://'
            type: Optional[MaterialType]
            additional_properties: Optional[Json]
            created_at: Optional[datetime.datetime] = datetime.datetime.now()
            updated_at: Optional[datetime.datetime]
        """
    # get data with parser
    result = parse(model_from)

    # if you want lower case table name before DDL generation you can just change in the result metadata, like this:
    # result[0].table_name = "material"
    # pass data to DDL Generator
    g = DDLGenerator(result)
    g.generate()
    print(g.result)  

    # resul will be

    """CREATE TABLE "Material" (
id INTEGER,
title VARCHAR,
description VARCHAR,
link VARCHAR DEFAULT 'http://',
type MaterialType,
additional_properties JSON,
created_at DATETIME DEFAULT now(),
updated_at DATETIME);
"""

Generate DDL Enum types from Python Enum & DDLs

Now parser also generate CREATE TYPE statements.

For example (sample for generation DDL from Dataclasses):

    from simple_ddl_generator import DDLGenerator
    from py_models_parser import parse

    model_from = """

    class MaterialType(str, Enum):

        article = 'article'
        video = 'video'


    @dataclass
    class Material:

        id: int
        description: str = None
        additional_properties: Union[dict, list, tuple, anything] = None
        created_at: datetime.datetime = datetime.datetime.now()
        updated_at: datetime.datetime = None

    @dataclass
    class Material2:

        id: int
        description: str = None
        additional_properties: Union[dict, list] = None
        created_at: datetime.datetime = datetime.datetime.now()
        updated_at: datetime.datetime = None

    """
    result = parse(model_from)

    g = DDLGenerator(result)
    g.generate()
    print(g.result)

# result will be:

"""CREATE TYPE MaterialType AS ENUM  ('article','video');

CREATE TABLE Material (
id INTEGER,
description VARCHAR DEFAULT NULL,
additional_properties JSON DEFAULT NULL,
created_at DATETIME DEFAULT now(),
updated_at DATETIME DEFAULT NULL);

CREATE TABLE Material2 (
id INTEGER,
description VARCHAR DEFAULT NULL,
additional_properties JSON DEFAULT NULL,
created_at DATETIME DEFAULT now(),
updated_at DATETIME DEFAULT NULL);
"""

Changelog

v0.4.1 New Features:

1. Added COMMENT statement to table generation

Improvements:

1. Added test to catch debug output (reminder: stop release at the middle night)

Fixes:

1. Fixed issue with

v0.4.0 New Features:

1. Added base support for REFERENCE statement generation
2. Added UNIQUE to column
3. Added PRIMARY KEY to column
4. To DDLGenerator added param lowercase to lowercase tables name.

v0.3.0 New Features:

1. Added CREATE TYPE generation from Python Enum & simple-ddl-parser types metadata

Improvements:

1. Added more test cases with models into tests
2. Now output generated with empty line at the end

Fixes:

1. Fixed issue with "" in names if quotes already exists in table-name in metadata

v0.2.0

1. Updated parser version in tests.
2. Added support for EXTERNAL & IF NOT EXISTS statetements.
3. Added support for using py-models-parser output as input and added sample in README.md:

DDL Generation from Pydantic, SQLAlchemy and other python models.

v0.1.0

Base Generator Functionality with several test cases.