Buckets:

download
raw
16.1 kB
#!/usr/bin/env python3
"""
MyWork-AI Database Migration Tool
==================================
Universal database migration management.
Supports: SQLite, PostgreSQL, MySQL (via connection string auto-detect).
Commands:
mw migrate init Create migrations directory
mw migrate create <name> Create a new migration file
mw migrate up Run pending migrations
mw migrate down [n] Rollback last N migrations (default: 1)
mw migrate status Show migration status
mw migrate history Show migration history
mw migrate reset Rollback all migrations
"""
import os
import sys
import json
import sqlite3
import hashlib
from datetime import datetime, timezone
from pathlib import Path
from typing import List, Optional, Tuple
# ANSI colors
RED = "\033[91m"
GREEN = "\033[92m"
YELLOW = "\033[93m"
BLUE = "\033[94m"
CYAN = "\033[96m"
BOLD = "\033[1m"
DIM = "\033[2m"
RESET = "\033[0m"
MIGRATIONS_DIR = "migrations"
MIGRATION_TABLE = "_mw_migrations"
def _find_project_root() -> Path:
"""Find project root by looking for common markers."""
cwd = Path.cwd()
markers = ["package.json", "pyproject.toml", "setup.py", "Cargo.toml", "go.mod", ".git"]
for parent in [cwd] + list(cwd.parents):
if any((parent / m).exists() for m in markers):
return parent
return cwd
def _get_migrations_dir() -> Path:
"""Get migrations directory path."""
root = _find_project_root()
return root / MIGRATIONS_DIR
def _get_db_path() -> str:
"""Auto-detect database connection from environment or config."""
# Check env vars
for key in ["DATABASE_URL", "DB_URL", "SQLITE_PATH", "DB_PATH"]:
val = os.environ.get(key)
if val:
return val
# Check .env file
env_file = _find_project_root() / ".env"
if env_file.exists():
for line in env_file.read_text().splitlines():
line = line.strip()
if line.startswith("#") or "=" not in line:
continue
key, _, val = line.partition("=")
key = key.strip()
val = val.strip().strip('"').strip("'")
if key in ("DATABASE_URL", "DB_URL", "SQLITE_PATH", "DB_PATH") and val and not val.startswith("xxx"):
return val
# Default to SQLite
return f"sqlite:///{_find_project_root() / 'db.sqlite3'}"
def _connect_sqlite(path: str) -> sqlite3.Connection:
"""Connect to SQLite database."""
if path.startswith("sqlite:///"):
path = path[len("sqlite:///"):]
elif path.startswith("sqlite://"):
path = path[len("sqlite://"):]
conn = sqlite3.connect(path)
conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA foreign_keys=ON")
return conn
def _ensure_migration_table(conn: sqlite3.Connection):
"""Create migration tracking table if it doesn't exist."""
conn.execute(f"""
CREATE TABLE IF NOT EXISTS {MIGRATION_TABLE} (
id INTEGER PRIMARY KEY AUTOINCREMENT,
version TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
checksum TEXT NOT NULL,
applied_at TEXT NOT NULL,
execution_time_ms INTEGER DEFAULT 0
)
""")
conn.commit()
def _get_applied_migrations(conn: sqlite3.Connection) -> List[dict]:
"""Get list of applied migrations."""
_ensure_migration_table(conn)
cursor = conn.execute(
f"SELECT version, name, checksum, applied_at, execution_time_ms FROM {MIGRATION_TABLE} ORDER BY version"
)
return [
{"version": r[0], "name": r[1], "checksum": r[2], "applied_at": r[3], "time_ms": r[4]}
for r in cursor.fetchall()
]
def _get_pending_migrations(migrations_dir: Path, applied: List[dict]) -> List[Tuple[str, Path]]:
"""Get list of pending migration files."""
applied_versions = {m["version"] for m in applied}
pending = []
if not migrations_dir.exists():
return pending
for f in sorted(migrations_dir.glob("*.sql")):
# Extract version from filename: YYYYMMDDHHMMSS_name.sql
version = f.stem.split("_")[0]
if version not in applied_versions and not f.name.endswith(".down.sql"):
pending.append((version, f))
return pending
def _file_checksum(path: Path) -> str:
"""Calculate SHA256 checksum of a file."""
return hashlib.sha256(path.read_bytes()).hexdigest()[:16]
def cmd_migrate_init(args: List[str]) -> int:
"""Initialize migrations directory."""
migrations_dir = _get_migrations_dir()
if migrations_dir.exists():
print(f"{YELLOW}⚠️ Migrations directory already exists: {migrations_dir}{RESET}")
return 0
migrations_dir.mkdir(parents=True)
# Create README
readme = migrations_dir / "README.md"
readme.write_text("""# Database Migrations
This directory contains database migration files managed by `mw migrate`.
## Naming Convention
Files follow the pattern: `YYYYMMDDHHMMSS_description.sql`
Each migration can have an optional rollback file: `YYYYMMDDHHMMSS_description.down.sql`
## Commands
```bash
mw migrate create <name> # Create new migration
mw migrate up # Apply pending migrations
mw migrate down # Rollback last migration
mw migrate status # Show current status
mw migrate history # Full migration history
```
""")
print(f"{GREEN}✅ Migrations directory created: {migrations_dir}{RESET}")
print(f"{DIM} Create your first migration: mw migrate create initial_schema{RESET}")
return 0
def cmd_migrate_create(args: List[str]) -> int:
"""Create a new migration file."""
if not args:
print(f"{RED}❌ Usage: mw migrate create <name>{RESET}")
print(f"{DIM} Example: mw migrate create add_users_table{RESET}")
return 1
name = "_".join(args).lower().replace(" ", "_").replace("-", "_")
migrations_dir = _get_migrations_dir()
if not migrations_dir.exists():
migrations_dir.mkdir(parents=True)
print(f"{DIM}Created migrations directory{RESET}")
timestamp = datetime.now(timezone.utc).strftime("%Y%m%d%H%M%S")
# Up migration
up_file = migrations_dir / f"{timestamp}_{name}.sql"
up_file.write_text(f"""-- Migration: {name}
-- Created: {datetime.now(timezone.utc).isoformat()}
-- Description: TODO - describe what this migration does
-- Write your SQL here
-- Example:
-- CREATE TABLE users (
-- id INTEGER PRIMARY KEY AUTOINCREMENT,
-- email TEXT NOT NULL UNIQUE,
-- name TEXT NOT NULL,
-- created_at TEXT NOT NULL DEFAULT (datetime('now'))
-- );
""")
# Down migration
down_file = migrations_dir / f"{timestamp}_{name}.down.sql"
down_file.write_text(f"""-- Rollback: {name}
-- Reverses the migration above
-- Write your rollback SQL here
-- Example:
-- DROP TABLE IF EXISTS users;
""")
print(f"{GREEN}✅ Created migration:{RESET}")
print(f" {CYAN}Up: {up_file.name}{RESET}")
print(f" {CYAN}Down: {down_file.name}{RESET}")
print(f"\n{DIM}Edit the files, then run: mw migrate up{RESET}")
return 0
def cmd_migrate_up(args: List[str]) -> int:
"""Run pending migrations."""
db_path = _get_db_path()
if not db_path.startswith("sqlite"):
print(f"{YELLOW}⚠️ Only SQLite is supported in this version.{RESET}")
print(f"{DIM} Found: {db_path}{RESET}")
print(f"{DIM} PostgreSQL/MySQL support coming in v2.2{RESET}")
return 1
conn = _connect_sqlite(db_path)
applied = _get_applied_migrations(conn)
migrations_dir = _get_migrations_dir()
pending = _get_pending_migrations(migrations_dir, applied)
if not pending:
print(f"{GREEN}✅ Database is up to date. No pending migrations.{RESET}")
conn.close()
return 0
# Limit if specified
limit = None
if args and args[0].isdigit():
limit = int(args[0])
pending = pending[:limit]
print(f"{BLUE}🔄 Running {len(pending)} migration(s)...{RESET}\n")
success_count = 0
for version, path in pending:
name = path.stem.split("_", 1)[1] if "_" in path.stem else path.stem
sql = path.read_text()
checksum = _file_checksum(path)
print(f" {CYAN}▶ {version}{RESET} {name}...", end=" ", flush=True)
start = datetime.now()
try:
conn.executescript(sql)
elapsed = int((datetime.now() - start).total_seconds() * 1000)
conn.execute(
f"INSERT INTO {MIGRATION_TABLE} (version, name, checksum, applied_at, execution_time_ms) VALUES (?, ?, ?, ?, ?)",
(version, name, checksum, datetime.now(timezone.utc).isoformat(), elapsed)
)
conn.commit()
print(f"{GREEN}✅{RESET} {DIM}({elapsed}ms){RESET}")
success_count += 1
except Exception as e:
print(f"{RED}❌ FAILED{RESET}")
print(f" {RED}{e}{RESET}")
conn.rollback()
break
conn.close()
total = len(pending)
if success_count == total:
print(f"\n{GREEN}✅ All {total} migration(s) applied successfully.{RESET}")
else:
print(f"\n{YELLOW}⚠️ {success_count}/{total} migration(s) applied. Fix errors and retry.{RESET}")
return 0 if success_count == total else 1
def cmd_migrate_down(args: List[str]) -> int:
"""Rollback migrations."""
count = 1
if args and args[0].isdigit():
count = int(args[0])
db_path = _get_db_path()
if not db_path.startswith("sqlite"):
print(f"{YELLOW}⚠️ Only SQLite supported in this version.{RESET}")
return 1
conn = _connect_sqlite(db_path)
applied = _get_applied_migrations(conn)
if not applied:
print(f"{GREEN}✅ No migrations to rollback.{RESET}")
conn.close()
return 0
to_rollback = list(reversed(applied))[:count]
migrations_dir = _get_migrations_dir()
print(f"{BLUE}🔄 Rolling back {len(to_rollback)} migration(s)...{RESET}\n")
success_count = 0
for mig in to_rollback:
version = mig["version"]
name = mig["name"]
# Find down file
down_files = list(migrations_dir.glob(f"{version}_*.down.sql"))
print(f" {CYAN}◀ {version}{RESET} {name}...", end=" ", flush=True)
if not down_files:
print(f"{YELLOW}⚠️ No rollback file found, removing record only{RESET}")
else:
sql = down_files[0].read_text()
try:
conn.executescript(sql)
except Exception as e:
print(f"{RED}❌ FAILED: {e}{RESET}")
conn.rollback()
break
conn.execute(f"DELETE FROM {MIGRATION_TABLE} WHERE version = ?", (version,))
conn.commit()
print(f"{GREEN}✅{RESET}")
success_count += 1
conn.close()
print(f"\n{GREEN}✅ Rolled back {success_count} migration(s).{RESET}")
return 0
def cmd_migrate_status(args: List[str]) -> int:
"""Show migration status."""
db_path = _get_db_path()
migrations_dir = _get_migrations_dir()
print(f"{BOLD}📊 Migration Status{RESET}\n")
print(f" {DIM}Database:{RESET} {db_path}")
print(f" {DIM}Directory:{RESET} {migrations_dir}\n")
if not db_path.startswith("sqlite"):
print(f"{YELLOW}⚠️ Only SQLite supported{RESET}")
return 1
conn = _connect_sqlite(db_path)
applied = _get_applied_migrations(conn)
pending = _get_pending_migrations(migrations_dir, applied)
conn.close()
if applied:
print(f" {GREEN}✅ Applied ({len(applied)}):{RESET}")
for m in applied:
print(f" {GREEN}●{RESET} {m['version']} — {m['name']}")
if pending:
print(f" {YELLOW}⏳ Pending ({len(pending)}):{RESET}")
for version, path in pending:
name = path.stem.split("_", 1)[1] if "_" in path.stem else path.stem
print(f" {YELLOW}○{RESET} {version} — {name}")
if not applied and not pending:
print(f" {DIM}No migrations found.{RESET}")
print(f" {DIM}Create one: mw migrate create initial_schema{RESET}")
return 0
def cmd_migrate_history(args: List[str]) -> int:
"""Show full migration history."""
db_path = _get_db_path()
if not db_path.startswith("sqlite"):
print(f"{YELLOW}⚠️ Only SQLite supported{RESET}")
return 1
conn = _connect_sqlite(db_path)
applied = _get_applied_migrations(conn)
conn.close()
print(f"{BOLD}📜 Migration History{RESET}\n")
if not applied:
print(f" {DIM}No migrations have been applied yet.{RESET}")
return 0
print(f" {'Version':<16} {'Name':<30} {'Applied At':<22} {'Time':>8}")
print(f" {'─'*16} {'─'*30} {'─'*22} {'─':─>8}")
for m in applied:
applied_at = m["applied_at"][:19].replace("T", " ")
time_str = f"{m['time_ms']}ms" if m['time_ms'] else "—"
print(f" {GREEN}{m['version']:<16}{RESET} {m['name']:<30} {DIM}{applied_at:<22}{RESET} {time_str:>8}")
print(f"\n {DIM}Total: {len(applied)} migration(s){RESET}")
return 0
def cmd_migrate_reset(args: List[str]) -> int:
"""Reset all migrations (rollback everything)."""
db_path = _get_db_path()
if not db_path.startswith("sqlite"):
return 1
conn = _connect_sqlite(db_path)
applied = _get_applied_migrations(conn)
conn.close()
if not applied:
print(f"{GREEN}✅ Nothing to reset.{RESET}")
return 0
print(f"{YELLOW}⚠️ This will rollback ALL {len(applied)} migration(s).{RESET}")
# In CLI context, proceed (non-interactive for automation)
return cmd_migrate_down([str(len(applied))])
def cmd_migrate(args: List[str] = None) -> int:
"""Main migration command dispatcher."""
args = args or []
if not args:
# Default to status
return cmd_migrate_status([])
subcmd = args[0].lower()
sub_args = args[1:]
commands = {
"init": cmd_migrate_init,
"create": cmd_migrate_create,
"new": cmd_migrate_create,
"up": cmd_migrate_up,
"apply": cmd_migrate_up,
"down": cmd_migrate_down,
"rollback": cmd_migrate_down,
"status": cmd_migrate_status,
"history": cmd_migrate_history,
"log": cmd_migrate_history,
"reset": cmd_migrate_reset,
"help": lambda _: _print_help(),
"-h": lambda _: _print_help(),
"--help": lambda _: _print_help(),
}
if subcmd in commands:
return commands[subcmd](sub_args)
print(f"{RED}❌ Unknown migrate command: {subcmd}{RESET}")
_print_help()
return 1
def _print_help() -> int:
"""Print help for migrate command."""
print(f"""{BOLD}🔄 mw migrate — Database Migration Manager{RESET}
{BOLD}Usage:{RESET}
mw migrate Show migration status
mw migrate init Initialize migrations directory
mw migrate create <name> Create new migration
mw migrate up [n] Apply pending migrations (optionally limit to n)
mw migrate down [n] Rollback last n migrations (default: 1)
mw migrate status Show current migration status
mw migrate history Show full migration history
mw migrate reset Rollback all migrations
{BOLD}Database Detection:{RESET}
Auto-detects from: DATABASE_URL, DB_URL, SQLITE_PATH, DB_PATH
Falls back to: sqlite:///db.sqlite3
{BOLD}Migration Files:{RESET}
Up: migrations/YYYYMMDDHHMMSS_name.sql
Down: migrations/YYYYMMDDHHMMSS_name.down.sql
""")
return 0
if __name__ == "__main__":
sys.exit(cmd_migrate(sys.argv[1:]))

Xet Storage Details

Size:
16.1 kB
·
Xet hash:
660ea194932d0c4387965898aacba5da8d38e748607af85574611b375932efc8

Xet efficiently stores files, intelligently splitting them into unique chunks and accelerating uploads and downloads. More info.