Utility for merging sqitch migrations

README.md

Merge sqitch migrations easily. Example:

❯ sqitch-merge --verbose                                                                                                                                                                                           
Path to sqitch plan file [./sqitch.plan]: ./db/migrations/sqitch.plan                                                                                                                                              
27 items found in plan                                                                                                                                                                                             
Choose range to merge from following 27 items                                                                                                                                                                      
1. 0000000000_initial                                    # Add schema for KEA                                                                                                                                      
2. 0000000001_appschema                                  # Add schema for KEA                                                                                                                                      
3. m_0000000002_contrib_extensions_0000000003_postgis_0000000004_libphonenumber_ext_0000000005_plperlu_00                                                                                                          
00000006_uri_ext                 # Merged 0000000002_contrib_extensions 0000000003_postgis 0000000004_lib                                                                                                          
phonenumber_ext 0000000005_plperlu 0000000006_uri_ext                                                                                                                                                              
4. m_0000000007_types_utils_schema_0000000008_custom_types_0000000009_enums_0000000010_composite_types_00                                                                                                          
00000011_set_types               # Merged 0000000007_types_utils_schema 0000000008_custom_types 000000000                                                                                                          
9_enums 0000000010_composite_types 0000000011_set_types                                                                                                                                                            
5. 0000000012_postgrest_tables                           # Add postgrest tables and types                                                                                                                          
6. 0000000013_independent_tables                         # Add independent tables: agents, brands, users                                                                                                           
7. 0000000014_stores_table                               # Add stores table                                                                                                                                        
8. 0000000015_managers_table                             # Add managers table                                                                                                                                      
9. 0000000016_billings_table                             # Add billings table                                                                                                                                      
10. 0000000017_calls_table                               # Add calls table                                                                                                                                         
11. 0000000018_items_table                               # Add items table
12. 0000000019_tickets_table                             # Add tickets table
13. 0000000020_receipts_table                            # Add receipts table
14. 0000000021_tickets_receipt_id_col                    # Add receipt_id col to tickets table
15. 0000000022_receipt_items                             # Add receipt_items table
16. 0000000023_agent_tickets                             # Add agent_tickets table
17. 0000000024_brand_items                               # Add brand_items table
18. 0000000025_brand_stores                              # Add brand_stores table
19. 0000000026_store_tickets                             # Add store_tickets table
20. 0000000027_store_billings                            # Add store_billings table
21. 0000000028_store_receipts                            # Add store_receipts table
22. 0000000029_store_managers                            # Add store_managers table
23. 0000000030_store_calls                               # Add store_calls table
24. 0000000031_user_calls                                # Add user_calls table
25. 0000000032_user_tickets                              # Add user_tickets table
26. 0000000033_user_receipts                             # Add user_receipts table
27. 0000000034-empty                                     # empty migration
Merge start at [1]: 5
Merge end at [27]: 26
Merging /home/diwank/github.com/keacloud/postgres/db/migrations/deploy/0000000012_postgrest_tables.sql, /
home/diwank/github.com/keacloud/postgres/db/migrations/deploy/0000000013_independent_tables.sql, /home/di

sqitch_migrate.py

#!/usr/bin/env python3

from collections import deque
from datetime import datetime
from itertools import cycle, filterfalse
import os
import os.path as path
import sys

import click
import sqlparse

DEPLOY_DIR = 'deploy'
VERIFY_DIR = 'verify'
REVERT_DIR = 'revert'

@click.command()
@click.option(
  '--plan',
  default='./sqitch.plan',
  prompt='Path to sqitch plan file',
  type=str )
@click.option(
  '--verbose',
  default=False,
  is_flag=True,
  type=bool )
def sqitch_merge( plan, verbose=False ):

    # Parse directories
    base_dir = path.realpath( path.dirname( plan ))

    deploy_dir = path.join( base_dir, DEPLOY_DIR )
    revert_dir = path.join( base_dir, REVERT_DIR )
    verify_dir = path.join( base_dir, VERIFY_DIR )

    plan_file = path.join( base_dir, path.basename( plan ))

    # Check paths
    invalid_paths = check_paths( base_dir, deploy_dir, revert_dir, verify_dir, plan_file )

    for invalid_path in invalid_paths:
        assert invalid_path, f'{invalid_path} not found'

    # Parse sqitch plan
    plan_attrs, plan_items = parse_plan_file( plan_file )
    plan_len = len( plan_items )

    assert plan_len, "No items in plan file"

    # Prompt for item range
    verbose and click.echo( f"{plan_len} items found in plan" )
    verbose and click.echo( f"Choose range to merge from following {len( plan_items )} items" )

    for idx, plan_item in enumerate( plan_items ):
        item_name = plan_item[0]
        item_desc = ' '.join( plan_item[ plan_item.index('#'): ])
        click.echo( f"{idx+1}. {item_name.ljust(36)} \t\t {item_desc}" )

    @click.command()
    @click.option(
      '--start',
      default=1,
      prompt='Merge start at',
      type=click.IntRange( 1, plan_len, clamp=True ))
    @click.option(
      '--end',
      default=plan_len,
      prompt='Merge end at',
      type=click.IntRange( 2, plan_len, clamp=True ))
    @click.option(
      '--verbose',
      default=verbose,
      is_flag=True,
      type=bool )
    def start_sqitch_merge( start, end, verbose ):

        selected_migrations = [ item[0] for item in plan_items[start-1:end] ]

        verbose and click.echo( f"Merging items [{start}-{end}]" )
        commit_merge( selected_migrations, deploy_dir, verify_dir, revert_dir )

        m_name = gen_combined_name( selected_migrations )
        m_item = [
            m_name, datetime.now().isoformat(), '#', 'Merged' ] + selected_migrations

        new_plan_items = plan_items[:start-1] + [ m_item ] + plan_items[end:]

        verbose and click.echo( f"Writing plan file" )
        write_plan_file( plan_file, new_plan_items, plan_attrs )

    # Prompt for merge range and start merge
    start_sqitch_merge()

def commit_merge( original_migrations,
                  deploy_dir=DEPLOY_DIR, verify_dir=VERIFY_DIR, revert_dir=REVERT_DIR ):

    original_scripts = [ m+'.sql' for m in original_migrations ]

    deploys = [ path.join( deploy_dir, script ) for script in original_scripts ]
    verifies = [ path.join( verify_dir, script ) for script in original_scripts ]
    reverts = [ path.join( revert_dir, script ) for script in original_scripts ]

    all_scripts = deploys + verifies + reverts

    m_name = gen_combined_name( original_migrations )

    # Merge and write deploys
    m_deploy_content = merge_scripts( deploys )
    write_script( m_deploy_content, f"{deploy_dir}/{m_name}.sql" )

    # Merge and write verifies
    m_verify_content = merge_scripts( verifies )
    write_script( m_verify_content, f"{verify_dir}/{m_name}.sql" )

    # Merge and write reverts
    m_revert_content = merge_scripts( list( reversed( reverts )))
    write_script( m_revert_content, f"{revert_dir}/{m_name}.sql" )

    for script in all_scripts:
        delete_script( script )

# Utils
def unnest_txn( sql ):
    has_txn = False
    raw_sql = strip_comments( sql )

    try:
        parsed = sqlparse.parse( raw_sql )
        first, last = parsed[0], parsed[-1]
        has_txn = first.tokens[0].normalized.upper() == "BEGIN"

    except BaseException as e:
        print( f"WARNING: Error occurred while parsing sql: {e}" )

    if not has_txn:
        return sql

    unnested = map( lambda s: s.value, parsed[1:-1] )
    return sqlparse.format( '\n'.join( unnested ))

def strip_comments( sql ):
    stmts = [ s.strip() for s in sql.split('\n') ]
    stmts_no_comments = [s for s in stmts if len(s) and not s.startswith('--')]

    return '\n'.join( stmts_no_comments )

def merge_scripts( scripts ):

    merging = f"Merging {', '.join( scripts )}"
    click.echo( merging )

    header = (
        f"-- %Script generated by sqitch_merge" "\n"
        f"-- %Merged on {datetime.now().isoformat()}" "\n"
        f"-- %{merging}" "\n"
        "\n"
        f"BEGIN;" )

    sep = (
        "\n"
        "-------------------------------------------------" "\n"
        "\n"
        "\n" )

    content = header + sep

    for script in scripts:

        with open( script, 'r' ) as f:
            script_sql = unnest_txn( f.read() )
            content += script_sql + sep

    return content + "COMMIT;"

def gen_combined_name( migrations ):
    return f"m_{'_'.join( migrations )}"[:254]

def write_script( content, script ):

    click.echo( f"Writing {script}" )
    with open( script, 'w' ) as f:
        f.write( content )

def delete_script( script ):

    click.echo( f"Deleting {script}" )
    os.unlink( script )

def check_paths( *paths ):

    invalid_paths = filterfalse( path.exists, paths )
    return list( invalid_paths )


def parse_attr( line ):

    attrs = {}

    line = line[1:].strip()   # Drop % char
    key, val = line.split('=')
    attrs[key] = val

    return attrs

def parse_plan_file( plan_file ):

    plan_attrs, plan_items = {}, []

    with open( plan_file, 'r' ) as f:

        lines = filter( None, map( lambda l: l.strip(), f.readlines() ))
        for line in lines:

            if line.startswith('%'):
                plan_attrs.update( parse_attr( line ))

            else:
                plan_items.append( line.split() )

    return plan_attrs, plan_items

def write_plan_file( plan_file, plan_items, plan_attrs={} ):

    with open( plan_file, 'w' ) as f:
        f.write( '\n'.join( f"%{key}={val}" for key, val in plan_attrs.items() ))
        f.write( '\n' )
        f.write( '\n' )

        f.write( '\n'.join( map( ' '.join, plan_items ) ))

def uncurry( f ):
    return lambda xs: f( *xs )


if __name__ == "__main__":
    sqitch_merge()