Metadata-Version: 2.4
Name: sql-ddl-regenerator
Version: 0.1.0
Summary: Universal SQL DDL regenerator starting with production-grade SQL Server table DDL extraction.
Author: SQL DDL Regenerator Maintainers
License: MIT
Keywords: automation,database,ddl,schema,sql,sqlserver
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: Intended Audience :: System Administrators
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: SQL
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Code Generators
Requires-Python: >=3.10
Requires-Dist: pyodbc<6,>=5.1
Provides-Extra: dev
Requires-Dist: mypy<2,>=1.10; extra == 'dev'
Requires-Dist: pytest<9,>=8; extra == 'dev'
Requires-Dist: ruff<1,>=0.5; extra == 'dev'
Description-Content-Type: text/markdown

# SQL DDL Regenerator v0.1.0

A production-oriented SQL DDL regenerator for live database metadata.

Version `0.1.0` supports SQL Server table DDL generation through a clean adapter structure. The tool connects to a SQL Server database, reads system catalog metadata for one table, and emits deterministic T-SQL that can recreate the table structure and related table-level artifacts.

## What this version generates

For SQL Server base tables, v0.1.0 generates:

- `CREATE TABLE` statement with schema-qualified, safely quoted identifiers.
- Column definitions for built-in types, alias/user-defined types, XML schema collections, computed columns, sparse columns, column sets, `FILESTREAM`, `ROWGUIDCOL`, hidden columns, temporal generated columns, identity columns, collations, defaults, dynamic data masking, and Always Encrypted metadata.
- Primary key and unique constraints with backing index options.
- Check constraints, including disabled and untrusted state restoration.
- Foreign keys, including column order, referential actions, disabled state, untrusted state, and `NOT FOR REPLICATION`.
- Non-constraint indexes, including included columns, descending keys, filtered indexes, columnstore indexes, XML indexes, hash indexes, partition scheme target, filegroup target, selected index options, and compression metadata.
- System-versioned temporal table period and `SYSTEM_VERSIONING` settings.
- Memory-optimized table options exposed by catalog metadata.
- Graph node/edge table marker when exposed by the SQL Server catalog.
- FileTable creation statement.
- Full-text index statement when present.
- Table DML triggers with disabled state restoration.
- Table and column extended properties.
- Optional `USE [database]`, optional guarded `DROP TABLE`, and optional `GO` batch separators.

## Design principles

- Fail closed for unsupported lossless regeneration cases.
- Use SQL Server catalog views instead of `INFORMATION_SCHEMA` because SQL Server-specific features are not fully represented in `INFORMATION_SCHEMA`.
- Never concatenate user input into catalog queries. Schema and table names are passed as parameters.
- Quote every emitted SQL identifier with SQL Server bracket quoting.
- Keep passwords out of logs.
- Support CLI and Python API usage.

## Requirements

- Python `3.10+`
- Microsoft ODBC Driver for SQL Server installed on the machine running the tool.
- A SQL Server login with metadata visibility for the target table and related constraints/indexes/triggers/properties.
- Network access to the SQL Server instance.

Install the Microsoft ODBC Driver separately because it is a system-level dependency. The Python package depends on `pyodbc`.

## Install from source

```bash
cd sql-ddl-regenerator-v0.1.0
python -m venv .venv
source .venv/bin/activate
python -m pip install --upgrade pip
python -m pip install -e .
```

On Windows PowerShell:

```powershell
cd sql-ddl-regenerator-v0.1.0
py -m venv .venv
.\.venv\Scripts\Activate.ps1
python -m pip install --upgrade pip
python -m pip install -e .
```

Verify installation:

```bash
sql-ddl-regenerator --version
sqlddl --version
```

Both console commands point to the same CLI.

## Quick start

Generate DDL for `dbo.Customer` and print to stdout:

```bash
sql-ddl-regenerator generate \
  --dbms sqlserver \
  --server localhost,1433 \
  --database SalesDb \
  --schema dbo \
  --table Customer \
  --username sa \
  --prompt-password \
  --trust-server-certificate yes
```

Write the result to a file:

```bash
sql-ddl-regenerator generate \
  --server localhost,1433 \
  --database SalesDb \
  --schema dbo \
  --table Customer \
  --username sa \
  --prompt-password \
  --output ddl/dbo.Customer.sql \
  --include-use-database
```

Use a raw ODBC connection string:

```bash
sql-ddl-regenerator generate \
  --connection-string 'DRIVER={ODBC Driver 18 for SQL Server};SERVER=localhost,1433;DATABASE=SalesDb;UID=sa;PWD=StrongPassword;Encrypt=yes;TrustServerCertificate=yes;' \
  --schema dbo \
  --table Customer
```

Use Windows integrated authentication where supported:

```bash
sql-ddl-regenerator generate \
  --server localhost \
  --database SalesDb \
  --trusted-connection \
  --schema dbo \
  --table Customer
```

Use Microsoft Entra / Azure AD authentication through the ODBC driver:

```bash
sql-ddl-regenerator generate \
  --server your-server.database.windows.net \
  --database SalesDb \
  --authentication ActiveDirectoryInteractive \
  --username user@example.com \
  --schema dbo \
  --table Customer
```

## Environment variables

Every connection option can be supplied through environment variables. CLI flags take precedence.

```bash
export SQLDDL_SERVER='localhost,1433'
export SQLDDL_DATABASE='SalesDb'
export SQLDDL_USERNAME='sa'
export SQLDDL_PASSWORD='StrongPassword'
export SQLDDL_DRIVER='ODBC Driver 18 for SQL Server'
export SQLDDL_ENCRYPT='yes'
export SQLDDL_TRUST_SERVER_CERTIFICATE='yes'

sql-ddl-regenerator generate --schema dbo --table Customer --output ddl/customer.sql
```

Supported environment variables:

| Variable | Purpose |
|---|---|
| `SQLDDL_CONNECTION_STRING` | Raw ODBC connection string. Overrides individual connection fields. |
| `SQLDDL_SERVER` | SQL Server host, host\\instance, or host,port. |
| `SQLDDL_PORT` | SQL Server port when not embedded in `SQLDDL_SERVER`. |
| `SQLDDL_DATABASE` | Database name. |
| `SQLDDL_USERNAME` | SQL login or Entra username. |
| `SQLDDL_PASSWORD` | Password. |
| `SQLDDL_DRIVER` | ODBC driver name. Defaults to `ODBC Driver 18 for SQL Server`. |
| `SQLDDL_TRUSTED_CONNECTION` | `true`, `false`, `yes`, `no`, `1`, or `0`. |
| `SQLDDL_AUTHENTICATION` | ODBC authentication mode, for example `ActiveDirectoryInteractive`. |
| `SQLDDL_ENCRYPT` | ODBC encryption setting. Defaults to `yes`. |
| `SQLDDL_TRUST_SERVER_CERTIFICATE` | `yes` or `no`. Defaults to `no`. |
| `SQLDDL_APPLICATION_INTENT` | `ReadOnly` or `ReadWrite`. |
| `SQLDDL_CONNECT_TIMEOUT` | Connection timeout in seconds. |
| `SQLDDL_QUERY_TIMEOUT` | Query timeout in seconds. |

## CLI reference

```bash
sql-ddl-regenerator generate --help
```

Important output flags:

| Flag | Behavior |
|---|---|
| `--output PATH` | Writes DDL to file. Without it, writes to stdout. |
| `--include-use-database` | Adds `USE [database];`. |
| `--include-drop-if-exists` | Adds guarded `DROP TABLE`. |
| `--no-indexes` | Excludes non-constraint indexes. Primary key and unique constraints are still included. |
| `--no-foreign-keys` | Excludes foreign keys. |
| `--no-triggers` | Excludes triggers. |
| `--no-extended-properties` | Excludes extended properties. |
| `--no-fulltext` | Excludes full-text index DDL. |
| `--no-go` | Removes `GO` batch separators. |
| `--verbose` | Enables debug logs. |
| `--json-logs` | Emits logs as JSON. |

## Python API

Use an existing pyodbc connection:

```python
import pyodbc
from sql_ddl_regenerator import generate_sqlserver_table_ddl
from sql_ddl_regenerator.dbms.sqlserver import RenderOptions

connection = pyodbc.connect(
    "DRIVER={ODBC Driver 18 for SQL Server};"
    "SERVER=localhost,1433;"
    "DATABASE=SalesDb;"
    "UID=sa;"
    "PWD=StrongPassword;"
    "Encrypt=yes;"
    "TrustServerCertificate=yes;"
)

ddl = generate_sqlserver_table_ddl(
    connection=connection,
    schema="dbo",
    table="Customer",
    render_options=RenderOptions(include_use_database=True),
)
print(ddl)
```

Use a connection config:

```python
from sql_ddl_regenerator import generate_sqlserver_table_ddl
from sql_ddl_regenerator.dbms.sqlserver import RenderOptions, SqlServerConnectionConfig

config = SqlServerConnectionConfig(
    server="localhost,1433",
    database="SalesDb",
    username="sa",
    password="StrongPassword",
    trust_server_certificate="yes",
)

ddl = generate_sqlserver_table_ddl(
    config=config,
    schema="dbo",
    table="Customer",
    render_options=RenderOptions(include_drop_if_exists=True),
)
```

## Project structure

```text
sql-ddl-regenerator-v0.1.0/
├── README.md
├── pyproject.toml
├── examples/
│   ├── example_output.sql
│   └── sqlserver.env.example
├── sql_ddl_regenerator/
│   ├── __init__.py
│   ├── __main__.py
│   ├── api.py
│   ├── cli.py
│   ├── core/
│   │   ├── errors.py
│   │   ├── identifiers.py
│   │   └── logging.py
│   └── dbms/
│       ├── base.py
│       └── sqlserver/
│           ├── connection.py
│           ├── generator.py
│           ├── introspector.py
│           ├── models.py
│           ├── queries.py
│           └── renderer.py
└── tests/
    ├── test_connection.py
    ├── test_identifiers.py
    └── test_renderer.py
```

## How it works

1. The CLI or Python API receives connection details, schema, and table name.
2. The SQL Server adapter opens a read-only metadata session through ODBC.
3. The introspector reads version-safe SQL Server catalog metadata. It checks catalog feature flags before referencing newer catalog columns.
4. The metadata is normalized into typed dataclasses.
5. The renderer emits deterministic T-SQL from the normalized metadata.
6. Unsupported lossless cases raise a typed exception instead of silently producing incomplete DDL.

## SQL Server coverage notes

This version scripts SQL Server user tables from `sys.tables`. It is designed for table DDL regeneration, not full database scripting.

Generated DDL may depend on objects outside the requested table. Examples:

- Referenced tables for foreign keys.
- User-defined data types.
- XML schema collections.
- Column encryption keys and column master keys.
- Full-text catalogs, stoplists, and search property lists.
- Partition schemes and functions.
- Filegroups and memory-optimized filegroups.
- Trigger dependencies.

Those objects are referenced by name because they are separate database-level or schema-level artifacts.

## Fail-closed behavior

The generator raises a clear error when it detects a feature that cannot be regenerated safely as part of a single-table script. In v0.1.0 this includes SQL Server ledger tables because ledger regeneration can require database-level artifacts and digest/view behavior that is not table-local. It also fails closed for spatial and JSON indexes instead of emitting incomplete index DDL.

## Security

- Use `--prompt-password` or `SQLDDL_PASSWORD` instead of putting passwords directly into shell history.
- Prefer `Encrypt=yes` with `TrustServerCertificate=no` for production.
- Use least-privilege SQL users that can view metadata for the target objects.
- The tool only reads catalog metadata and sets read-uncommitted isolation for metadata reads.
- Connection strings are redacted in debug logs.

## Testing

Run unit tests:

```bash
python -m pip install -e '.[dev]'
python -m pytest
```

Run the CLI module directly during development:

```bash
python -m sql_ddl_regenerator --version
python -m sql_ddl_regenerator generate --help
```

## Packaging

Build a wheel and source distribution:

```bash
python -m pip install build
python -m build
```

Install the built wheel:

```bash
python -m pip install dist/sql_ddl_regenerator-0.1.0-py3-none-any.whl
```

## Exit codes

| Code | Meaning |
|---:|---|
| `0` | Success. |
| `1` | Expected application error: configuration, connection, object not found, or unsupported feature. |
| `2` | CLI argument parsing error. |
| `130` | Interrupted by user. |
