143 lines
4.8 KiB
Python
143 lines
4.8 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
Tech Stack Selector Database Migration Script
|
|
This script creates minimal tables for tech stack recommendations.
|
|
"""
|
|
|
|
import os
|
|
import sys
|
|
import asyncio
|
|
import asyncpg
|
|
from pathlib import Path
|
|
|
|
async def get_database_connection():
|
|
"""Get database connection using environment variables."""
|
|
try:
|
|
# Get database connection parameters from environment
|
|
db_host = os.getenv('POSTGRES_HOST', 'postgres')
|
|
db_port = int(os.getenv('POSTGRES_PORT', '5432'))
|
|
db_name = os.getenv('POSTGRES_DB', 'dev_pipeline')
|
|
db_user = os.getenv('POSTGRES_USER', 'pipeline_admin')
|
|
db_password = os.getenv('POSTGRES_PASSWORD', 'secure_pipeline_2024')
|
|
|
|
# Create connection
|
|
conn = await asyncpg.connect(
|
|
host=db_host,
|
|
port=db_port,
|
|
database=db_name,
|
|
user=db_user,
|
|
password=db_password
|
|
)
|
|
|
|
return conn
|
|
except Exception as e:
|
|
print(f"❌ Failed to connect to database: {e}")
|
|
sys.exit(1)
|
|
|
|
async def create_migrations_table(conn):
|
|
"""Create the migrations tracking table if it doesn't exist."""
|
|
await conn.execute("""
|
|
CREATE TABLE IF NOT EXISTS schema_migrations (
|
|
id SERIAL PRIMARY KEY,
|
|
version VARCHAR(255) NOT NULL UNIQUE,
|
|
service VARCHAR(100) NOT NULL,
|
|
applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
description TEXT
|
|
)
|
|
""")
|
|
|
|
async def is_migration_applied(conn, version):
|
|
"""Check if a migration has already been applied."""
|
|
result = await conn.fetchval(
|
|
'SELECT 1 FROM schema_migrations WHERE version = $1 AND service = $2',
|
|
version, 'tech-stack-selector'
|
|
)
|
|
return result is not None
|
|
|
|
async def mark_migration_applied(conn, version, description):
|
|
"""Mark a migration as applied."""
|
|
await conn.execute(
|
|
'INSERT INTO schema_migrations (version, service, description) VALUES ($1, $2, $3) ON CONFLICT (version) DO NOTHING',
|
|
version, 'tech-stack-selector', description
|
|
)
|
|
|
|
async def run_migration():
|
|
"""Run the database migration."""
|
|
print('🚀 Starting Tech Stack Selector database migrations...')
|
|
|
|
# Define migrations
|
|
migrations = [
|
|
{
|
|
'file': '001_minimal_schema.sql',
|
|
'version': '001_minimal_schema',
|
|
'description': 'Create minimal tech stack recommendation tables'
|
|
}
|
|
]
|
|
|
|
try:
|
|
# Get database connection
|
|
conn = await get_database_connection()
|
|
print('✅ Database connection established')
|
|
|
|
# Ensure required extensions exist
|
|
print('🔧 Ensuring required PostgreSQL extensions...')
|
|
await conn.execute('CREATE EXTENSION IF NOT EXISTS "uuid-ossp";')
|
|
print('✅ Extensions ready')
|
|
|
|
# Create migrations tracking table
|
|
await create_migrations_table(conn)
|
|
print('✅ Migration tracking table ready')
|
|
|
|
applied_count = 0
|
|
skipped_count = 0
|
|
|
|
for migration in migrations:
|
|
migration_path = Path(__file__).parent / 'db' / migration['file']
|
|
|
|
if not migration_path.exists():
|
|
print(f"⚠️ Migration file {migration['file']} not found, skipping...")
|
|
continue
|
|
|
|
# Check if migration was already applied
|
|
if await is_migration_applied(conn, migration['version']):
|
|
print(f"⏭️ Migration {migration['file']} already applied, skipping...")
|
|
skipped_count += 1
|
|
continue
|
|
|
|
# Read and execute migration SQL
|
|
migration_sql = migration_path.read_text()
|
|
print(f"📄 Running migration: {migration['file']}")
|
|
|
|
await conn.execute(migration_sql)
|
|
await mark_migration_applied(conn, migration['version'], migration['description'])
|
|
print(f"✅ Migration {migration['file']} completed!")
|
|
applied_count += 1
|
|
|
|
print(f"📊 Migration summary: {applied_count} applied, {skipped_count} skipped")
|
|
|
|
# Verify tables were created
|
|
result = await conn.fetch("""
|
|
SELECT
|
|
schemaname,
|
|
tablename,
|
|
tableowner
|
|
FROM pg_tables
|
|
WHERE schemaname = 'public'
|
|
AND tablename IN ('tech_stack_recommendations', 'stack_analysis_cache')
|
|
ORDER BY tablename
|
|
""")
|
|
|
|
print('🔍 Verified tables:')
|
|
for row in result:
|
|
print(f" - {row['tablename']}")
|
|
|
|
await conn.close()
|
|
print('✅ Tech Stack Selector migrations completed successfully!')
|
|
|
|
except Exception as error:
|
|
print(f"❌ Migration failed: {error}")
|
|
sys.exit(1)
|
|
|
|
if __name__ == '__main__':
|
|
asyncio.run(run_migration())
|