Tiny postgresql build script

README.md

Use

Inlines

1. Create a file named "Main.sql" containing something like this:

BEGIN TRANSACTION;
--@inline {filepath0}
--@inline {filepath1}
--[...]
COMMIT;

2. In any of the imported files, use --@inline {path} to include more files. This fits a lot of workflows, but my personal folder structure looks like this:

Schemas/
- AppName/
- - Tables
- - Views
- REFS/
- - Tables
- - Data
Main.sql
- imports Permissions
- imports Schemas
Permissions.sql
Schemas.sql
- imports Schemas/REFS/Tables
- imports Schemas/Refs/Data
- imports Schemas/AppName/Tables
- imports Schemas/AppName/Views

Since the import statement is prefaced by --, it will register as a comment to sql linters and won't cause any trouble.

Conditionals

Conditionals can be specified in the CLI and supports alphanumeric defines.

You can use unary conditions, i.e.

--@if abcdefg
-- something here
--@else
-- something else
--@end

by specifying abcdefg=true, abcdefg=false, or even just abcdefg to check if it's defined. Currently there is no else-if block, but nested conditions function:

--@if something
-- dead block
--@else
--@if something2
-- something 2
--@else
-- something 3
--@end
--@end

Numerics, text, booleans, and plain defines are all supported:

./build.py letters=abc numbers=123 bool=false define

--@if letters < def
-- this would go to the output
--@end
--@if bool == false
-- this would go to the output
--@end
--@if numbers < 200
-- this would go to the output
--@end
--@if define
-- this would go to the output
--@end

These will function recursively, so

--@if someflag
--@inline file1
--@else
--@inline file2
--@end

is fully functional and intended.

Limits

IF comparisons:

• These will not automatically converge types, and come with the jank of python's <>= string compares.
• In conditional blocks, comparisons are NOT REVERSIBLE! true == bool will attempt to compare a string variable named true to the string bool.

Update 1:

Added support for conditional blocks using the cli. I didn't test it particularly well. This also required me to import some modules, but it's still fully python core.

Update 0:

This script is hosted on my quick links. You can access it from anywhere with wget/curl/your favorite REST client at https://sh.crinfarr.zip/sqlbuild.py

build.py

#!/usr/bin/python
from io import TextIOWrapper
import re
import sys

build = open("compiled.pgsql", "w")
setvals:dict[str, str|int|bool] = {}
def parse_arguments():
    matcher = re.compile(r'^([a-zA-Z0-9]+)(=([a-zA-Z0-9]+))?$')
    for arg in sys.argv[1:]:
        key, _, val = matcher.match(arg).groups()
        if val is None:
            setvals[key] = None
        elif re.match(r'[0-9]+', val):
            setvals[key] = int(val)
        elif val.lower().strip() in ('true', 'false'):
            setvals[key]=val=='true'
        elif re.match(r'^[a-zA-Z0-9]+$', val):
            setvals[key] = val
        else:
            raise ValueError(f"Invalid argument: {arg} (how did you do that?)")

def handle_inline(line:str):
    if not line.startswith('--@inline'):
        raise ValueError(f'Inline handler called with non-inline marker {line}')
    fpath = line[9:].strip()
    if (fpath is None) or (fpath == ''):
        raise ValueError(f'Inline handler called with empty path {line}')
    with open(fpath, 'r') as f:
        yield f'\n--{fpath}\n'
        for line in f:
            if line.startswith('--@'):
                for cl in handle_component(line, f):
                    yield cl
            else:
                yield line
        yield f'--End {fpath}\n'

def build_conditional(condition:str):
    matcher = re.compile(r'([a-zA-Z0-9]+) ?([<>]=?|[=!]=)? ?([a-zA-Z0-9]+)?')
    groups = matcher.match(condition).groups()
    if groups is None:
        raise ValueError(f'Invalid conditional: {condition}')
    key, op, val = groups
    if op is None and val is not None:
        raise ValueError(f'Conditional {condition} does not have a valid operator')
    if (key not in setvals) and op is None:# Op being none implies val is none (^ see check ^)
        return False
    def _compareHandler(v1, op, v2):
        if type(v1) != type(v2):
            raise ValueError(f'Cannot compare {v1}({type(v1)}) and {v2}({type(v2)})')
        match op:
            case '==':
                if type(v1) in (int, str):
                    return v1 == v2
                if type(v1) == bool and v2 != False:
                    print(f'[Warn]: Unnecessary operator {key}{op}{v2}: Comparing bool to true can be simplified to --@if {key}')
                return v1 == v2
            case '!=':
                if type(v1) in (int, str):
                    return v1 != v2
                if type(v1) == bool and v2 == False:
                    print(f'[Warn]: Unnecessary operator {key}{op}{v2}: Comparing bool to not false can be simplified to --@if {key}')
                return v1 != v2
            case '<':
                if type(v1) == bool:
                    raise ValueError(f'Bools cannot be numerically compared')
                return v1 < v2
            case '>':
                if type(v1) == bool:
                    raise ValueError(f'Bools cannot be numerically compared')
                return v1 > v2
            case '<=':
                if type(v1) == bool:
                    raise ValueError(f'Bools cannot be numerically compared')
                return v1 <= v2
            case '>=':
                if type(v1) == bool:
                    raise ValueError(f'Bools cannot be numerically compared')
                return v1 >= v2
            case _:
                raise ValueError(f'Invalid operator: {op}')
    if val is not None:
        if re.match(r'^[0-9]+$', val):
            return _compareHandler(setvals[key], op, int(val))
        elif val.lower().strip() in ('true', 'false'):
            return _compareHandler(setvals[key], op, val.lower().strip() == 'true')
        elif re.match(r'^[a-zA-Z0-9]+$', val):
            return _compareHandler(setvals[key], op, val)
        else:
            raise ValueError(f'Invalid value in conditional: {val}')
    else: #unary conditional
        return key in setvals

def handle_conditional(component:str, handle:TextIOWrapper):
    if not component.startswith('--@if'):
        raise ValueError(f'Conditional handler called with non-conditional marker {line}')
    condition:bool = build_conditional(component[5:].strip())
    if condition: # conditional active
        yield component# return conditonal line
        for line in handle:
            if line.startswith('--@'):# component handler
                if line.strip() == '--@end':
                    yield line# return end line
                    break
                elif line.strip() == '--@else':
                    for line in handle:# inactive else block
                        if line.startswith('--@'):
                            for cl in handle_component(line, handle): yield cl# handle nested ifs and includes
                        elif line.strip() != '--@end':
                            continue# discard
                        else:
                            return line# end inactive else block (also end of full conditional block)
                else:
                    for cl in handle_component(line, handle): yield cl# handle nested ifs and includes
            else:
                yield line# normal line
    else:# conditional inactive
        for line in handle:
            if line.startswith('--@'):# component handler
                if line.strip() == '--@end':
                    break# end of inactive conditional block
                elif line.strip() == '--@else':
                    yield f'--@else [{component[5:].strip()}]\n'
                    for line in handle:# active else block
                        if line.startswith('--@'):
                            for cl in handle_component(line, handle): yield cl# handle nested ifs and includes
                        elif line != '--@end':
                            yield line
                        else:
                            return line
                else:
                    continue# refuse to handle inactive components in block
            else:
                continue

def handle_component(line:str, handle:TextIOWrapper):
    if not line.startswith('--@'):
        raise ValueError(f'Component handler called with non-component line {line}')
    match line.split(' ')[0]:
        case '--@inline':
            for line in handle_inline(line): yield line
        case '--@if':
            for line in handle_conditional(line, handle): yield line

def build_file(path:str):
    yield f"--{path}\n"
    with open(path, 'r') as f:
        for line in f:
            if line.startswith('--@'):
                for cl in handle_component(line, f):
                    yield cl
            else:
                yield line

parse_arguments()
for line in build_file('Main.sql'):
    build.write(line)
build.close()