sqlcompose 0.0.4a2

Composition of SQL files

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:

sqlcompose query.sql

2. Pipe data into application and output to a file

cat query.sql | sqlcompose > output.sql

3. Execute the script with SQL string as argument

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

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.

--main-query.sql
select * from $INCLUDE(includes\included-query2.sql)

--included-query1.sql
select 1 as test

--included-query2.sql
select * from $INCLUDE(included-query1.sql)
union all
select * from $INCLUDE(nested\included-query3.sql)

--nested\included-query3.sql
select 1 as test

Which outputs:

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