Simple Schema Diff Viewer Clone

mkdir schema-diff-clone
cd schema-diff-clone
python3 -m venv .venv
source .venv/bin/activate
# Upgrade pip and freeze an empty requirements file
pip install --upgrade pip
printf "# Add future deps here\n" > requirements.txt

import sqlite3
import sys

def extract_schema(db_path: str) -> str:
    """Return a single string with all CREATE statements from a SQLite file.
    Objects are sorted alphabetically to ensure deterministic output.
    """
    conn = sqlite3.connect(db_path)
    cur = conn.cursor()
    cur.execute(
        "SELECT type, name, sql FROM sqlite_master "
        "WHERE sql NOT NULL ORDER BY type, name;"
    )
    statements = []
    for obj_type, name, sql in cur.fetchall():
        statements.append(f"-- {obj_type.upper()}: {name}\n{sql.strip()};\n")
    conn.close()
    return "\n".join(statements)

if __name__ == "__main__":
    if len(sys.argv) != 3:
        print("Usage: python loader.py <db1.sqlite> <db2.sqlite>")
        sys.exit(1)
    schema_a = extract_schema(sys.argv[1])
    schema_b = extract_schema(sys.argv[2])
    # For now just print the raw schemas; later phases will diff them.
    print("--- Schema A ---")
    print(schema_a)
    print("--- Schema B ---")
    print(schema_b)

import sys, sqlite3, difflib

def extract_schema(db_path):
    conn = sqlite3.connect(db_path)
    cur = conn.cursor()
    cur.execute("SELECT sql FROM sqlite_master WHERE sql NOT NULL ORDER BY name")
    rows = cur.fetchall()
    conn.close()
    # Join each CREATE statement on a new line
    return "\n".join(row[0] for row in rows)

def generate_html_diff(schema_a, schema_b):
    diff = difflib.HtmlDiff(wrapcolumn=80)
    return diff.make_file(schema_a.splitlines(), schema_b.splitlines(),
                         fromdesc='Schema A', todesc='Schema B')

if __name__ == "__main__":
    if len(sys.argv) != 3:
        print("Usage: python differ.py <db_a.sqlite> <db_b.sqlite>")
        sys.exit(1)
    a, b = sys.argv[1], sys.argv[2]
    html = generate_html_diff(extract_schema(a), extract_schema(b))
    print(html)

from flask import Flask, request, render_template_string
import sqlite3, difflib, io

app = Flask(__name__)

HTML_TEMPLATE = '''
<!doctype html>
<title>Schema Diff Viewer</title>
<h2>Upload two SQLite databases</h2>
<form method=post enctype=multipart/form-data>
  <input type=file name=db_a required> <br><br>
  <input type=file name=db_b required> <br><br>
  <input type=submit value='Show Diff'>
</form>
''' 

def extract_schema(file_storage):
    # file_storage is a Werkzeug FileStorage object
    data = file_storage.read()
    conn = sqlite3.connect(':memory:')
    conn.executescript(data.decode('utf-8')) if data else None
    cur = conn.cursor()
    cur.execute("SELECT sql FROM sqlite_master WHERE sql NOT NULL ORDER BY name")
    rows = cur.fetchall()
    conn.close()
    return "\n".join(row[0] for row in rows)

def generate_html_diff(a_schema, b_schema):
    diff = difflib.HtmlDiff(wrapcolumn=80)
    return diff.make_file(a_schema.splitlines(), b_schema.splitlines(),
                         fromdesc='Schema A', todesc='Schema B')

@app.route('/', methods=['GET', 'POST'])
def index():
    if request.method == 'POST':
        if 'db_a' not in request.files or 'db_b' not in request.files:
            return 'Both files are required', 400
        a_schema = extract_schema(request.files['db_a'])
        b_schema = extract_schema(request.files['db_b'])
        html = generate_html_diff(a_schema, b_schema)
        return html
    return render_template_string(HTML_TEMPLATE)

if __name__ == '__main__':
    app.run(debug=True)

export FLASK_APP=app.py
flask run