Metadata-Version: 2.4
Name: sqlcompose
Version: 0.0.4a2
Summary: Composition of SQL files
Author-email: Anders Madsen <anders.madsen@alphavue.com>
License-Expression: MIT
Project-URL: repository, https://github.com/apmadsen/sqlcompose
Keywords: sql,composition,windows,linux
Classifier: Development Status :: 5 - Production/Stable
Classifier: Development Status :: 6 - Mature
Classifier: Operating System :: Microsoft :: Windows
Classifier: Operating System :: POSIX :: Linux
Classifier: Intended Audience :: Developers
Classifier: Natural Language :: English
Classifier: Programming Language :: Python :: 3 :: Only
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 :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Classifier: Programming Language :: Python
Classifier: Topic :: Software Development :: Libraries :: Python Modules
Classifier: Topic :: Software Development :: Libraries
Classifier: Typing :: Typed
Requires-Python: <3.15,>=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Provides-Extra: test
Requires-Dist: pytest>=8.3.0; extra == "test"
Requires-Dist: pytest-cov>=6.1.0; extra == "test"
Requires-Dist: pytest-mock>=3.15; extra == "test"
Dynamic: license-file

[![Test](https://github.com/apmadsen/sqlcompose/actions/workflows/python-test.yml/badge.svg)](https://github.com/apmadsen/sqlcompose/actions/workflows/python-test.yml)
[![Coverage](https://github.com/apmadsen/sqlcompose/actions/workflows/python-test-coverage.yml/badge.svg)](https://github.com/apmadsen/sqlcompose/actions/workflows/python-test-coverage.yml)
[![Stable Version](https://img.shields.io/pypi/v/sqlcompose?label=stable&sort=semver&color=blue)](https://github.com/apmadsen/sqlcompose/releases)
![Pre-release Version](https://img.shields.io/github/v/release/apmadsen/sqlcompose?label=pre-release&include_prereleases&sort=semver&color=blue)
![PyPI - Python Version](https://img.shields.io/pypi/pyversions/sqlcompose)
[![PyPI Downloads](https://static.pepy.tech/badge/sqlcompose/week)](https://pepy.tech/projects/sqlcompose)

# sqlcompose: Composition of SQL files
sqlcompose lets you to compose sql files from multiple files by introducing `$INCLUDE` keywords. The SQL output is composed as CTE's or Common Table Expressions.

Using composition, you reduce both complexity and duplication of code thus adhering to the DRY principle.

### SQL Dialect
sqlcompose outputs SQL as standard ANSI SQL. Note though, that no validation is done on either the input or the output.

## Examples

### 1. Execute the script with the filename as an argument and output to the console:
```bash
sqlcompose query.sql
```

### 2. Pipe data into application and output to a file
```bash
cat query.sql | sqlcompose > output.sql
```

### 3. Execute the script with SQL string as argument
```bash
sqlcompose 'select * from $INCLUDE(included-query1.sql)'
```
> NOTE: Different consoles have different limitations, so you may have to switch from single to double quotes to allow for using the dollar sign.

### 4. Import it in another python application or package
```python
from sqlcompose import load, loads
# method 1 : loading from a file
sql1 = load("query.sql")

# method 2 : loading from an SQL string
sql2 = loads("""
    select *
      from dataset.table main
inner join $INCLUDE(other.sql) other
        on other.field = main.field
  """)
```

## Preparing SQL scripts
Insert a `$INCLUDE(filename)` where the reference to the file should be in the resulting SQL, keeping in mind that references are loaded relative to the file loaded or the current working dir in case of an SQL string.

```sql
--main-query.sql
select * from $INCLUDE(includes\included-query2.sql)
```
```sql
--included-query1.sql
select 1 as test
```
```sql
--included-query2.sql
select * from $INCLUDE(included-query1.sql)
union all
select * from $INCLUDE(nested\included-query3.sql)
```
```sql
--nested\included-query3.sql
select 1 as test
```
Which outputs:
```sql
WITH Q_1_1 AS (
  WITH Q_2_1 AS (
    --includes\included-query1.sql
    select 1 as test
  ), Q_2_2 AS (
    --includes\nested\included-query3.sql
    select 1 as test
  ), Q_2 AS (
    --includes\included-query2.sql
    select * from Q_2_1
    union all
    select * from Q_2_2
  )
  SELECT * FROM Q_2
), Q_1 AS (
  --test\main-query.sql
  select * from Q_1_1
)
SELECT * FROM Q_1
```
