Chapter 25: Migrating the Music Time Machine to PostgreSQL

import sqlite3

# Connect to your Music Time Machine database
conn = sqlite3.connect('music_time_machine.db')
cursor = conn.cursor()

# Get all tables
cursor.execute("""
    SELECT name FROM sqlite_master 
    WHERE type='table'
    ORDER BY name
""")
tables = cursor.fetchall()

print("Tables in Music Time Machine:")
for table_name, in tables:
    print(f"\n{'='*50}")
    print(f"Table: {table_name}")
    print('='*50)
    
    # Get schema
    cursor.execute(f"PRAGMA table_info({table_name})")
    columns = cursor.fetchall()
    
    for col in columns:
        col_id, name, type_, not_null, default, is_pk = col
        constraints = []
        if is_pk:
            constraints.append("PRIMARY KEY")
        if not_null:
            constraints.append("NOT NULL")
        if default:
            constraints.append(f"DEFAULT {default}")
        
        constraint_str = " ".join(constraints) if constraints else ""
        print(f"  {name}: {type_} {constraint_str}")
    
    # Get row count
    cursor.execute(f"SELECT COUNT(*) FROM {table_name}")
    count = cursor.fetchone()[0]
    print(f"\n  Rows: {count:,}")
    
    # Sample data
    cursor.execute(f"SELECT * FROM {table_name} LIMIT 1")
    sample = cursor.fetchone()
    if sample:
        print(f"  Sample: {sample[:3]}...")  # First 3 columns

conn.close()

Tables in Music Time Machine:

==================================================
Table: artists
==================================================
  id: INTEGER PRIMARY KEY
  spotify_id: TEXT NOT NULL
  name: TEXT NOT NULL
  created_at: TEXT NOT NULL

  Rows: 847
  Sample: (1, '4Z8W4fKeB5YxbusRsdQVPb', 'Radiohead')...

==================================================
Table: playlist_tracks
==================================================
  id: INTEGER PRIMARY KEY
  playlist_id: TEXT NOT NULL
  track_id: INTEGER NOT NULL
  position: INTEGER NOT NULL
  created_at: TEXT NOT NULL

  Rows: 2,341
  Sample: (1, 'playlist_2020_december', 523, 1)...

==================================================
Table: tracks
==================================================
  id: INTEGER PRIMARY KEY
  spotify_id: TEXT NOT NULL
  name: TEXT NOT NULL
  artist_id: INTEGER 
  album_name: TEXT 
  release_date: TEXT 
  duration_ms: INTEGER 
  popularity: INTEGER 
  audio_features: TEXT 
  created_at: TEXT NOT NULL

  Rows: 1,872
  Sample: (1, '3n3Ppam7vgaVa1iaRUc9Lp', 'Mr. Brightside')...

CREATE TABLE artists (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    spotify_id TEXT NOT NULL,
    name TEXT NOT NULL,
    created_at TEXT NOT NULL
);

CREATE TABLE artists (
    id SERIAL PRIMARY KEY,
    spotify_id VARCHAR(22) UNIQUE NOT NULL,
    name VARCHAR(500) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Index for lookups by Spotify ID (happens frequently)
CREATE INDEX idx_artists_spotify_id ON artists(spotify_id);

CREATE TABLE tracks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    spotify_id TEXT NOT NULL,
    name TEXT NOT NULL,
    artist_id INTEGER,
    album_name TEXT,
    release_date TEXT,
    duration_ms INTEGER,
    popularity INTEGER,
    audio_features TEXT,  -- JSON stored as TEXT
    created_at TEXT NOT NULL,
    FOREIGN KEY (artist_id) REFERENCES artists(id)
);

CREATE TABLE tracks (
    id SERIAL PRIMARY KEY,
    spotify_id VARCHAR(22) UNIQUE NOT NULL,
    name VARCHAR(500) NOT NULL,
    artist_id INTEGER,
    album_name VARCHAR(500),
    release_date DATE,
    duration_ms INTEGER,
    popularity SMALLINT CHECK (popularity BETWEEN 0 AND 100),
    audio_features JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (artist_id) REFERENCES artists(id) ON DELETE CASCADE
);

-- Essential indexes
CREATE INDEX idx_tracks_spotify_id ON tracks(spotify_id);
CREATE INDEX idx_tracks_artist_id ON tracks(artist_id);
CREATE INDEX idx_tracks_popularity ON tracks(popularity) WHERE popularity > 50;

-- JSONB index for audio feature queries - the game changer!
CREATE INDEX idx_tracks_audio_features ON tracks USING GIN (audio_features);

CREATE TABLE playlist_tracks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    playlist_id TEXT NOT NULL,
    track_id INTEGER NOT NULL,
    position INTEGER NOT NULL,
    created_at TEXT NOT NULL,
    FOREIGN KEY (track_id) REFERENCES tracks(id)
);

CREATE TABLE playlist_tracks (
    id SERIAL PRIMARY KEY,
    playlist_id VARCHAR(100) NOT NULL,
    track_id INTEGER NOT NULL,
    position SMALLINT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (track_id) REFERENCES tracks(id) ON DELETE CASCADE,
    -- Ensure same track doesn't appear twice in same playlist
    UNIQUE (playlist_id, track_id)
);

-- Index for "get all tracks in this playlist" queries
CREATE INDEX idx_playlist_tracks_playlist ON playlist_tracks(playlist_id, position);

-- Index for "which playlists contain this track" queries
CREATE INDEX idx_playlist_tracks_track ON playlist_tracks(track_id);

-- Music Time Machine PostgreSQL Schema
-- Migrated from SQLite with improvements

-- Artists table (foundation - no foreign keys)
CREATE TABLE artists (
    id SERIAL PRIMARY KEY,
    spotify_id VARCHAR(22) UNIQUE NOT NULL,
    name VARCHAR(500) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Tracks table (core data with foreign keys and JSONB)
CREATE TABLE tracks (
    id SERIAL PRIMARY KEY,
    spotify_id VARCHAR(22) UNIQUE NOT NULL,
    name VARCHAR(500) NOT NULL,
    artist_id INTEGER,
    album_name VARCHAR(500),
    release_date DATE,
    duration_ms INTEGER,
    popularity SMALLINT CHECK (popularity BETWEEN 0 AND 100),
    audio_features JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (artist_id) REFERENCES artists(id) ON DELETE CASCADE
);

-- Playlist tracks table (junction table)
CREATE TABLE playlist_tracks (
    id SERIAL PRIMARY KEY,
    playlist_id VARCHAR(100) NOT NULL,
    track_id INTEGER NOT NULL,
    position SMALLINT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (track_id) REFERENCES tracks(id) ON DELETE CASCADE,
    UNIQUE (playlist_id, track_id)
);

-- Indexes for artists table
CREATE INDEX idx_artists_spotify_id ON artists(spotify_id);

-- Indexes for tracks table
CREATE INDEX idx_tracks_spotify_id ON tracks(spotify_id);
CREATE INDEX idx_tracks_artist_id ON tracks(artist_id);
CREATE INDEX idx_tracks_popularity ON tracks(popularity) WHERE popularity > 50;

-- JSONB index for audio features (critical for mood playlist queries)
CREATE INDEX idx_tracks_audio_features ON tracks USING GIN (audio_features);

-- Indexes for playlist_tracks table
CREATE INDEX idx_playlist_tracks_playlist ON playlist_tracks(playlist_id, position);
CREATE INDEX idx_playlist_tracks_track ON playlist_tracks(track_id);

# Create the database
createdb music_time_machine_pg

# Verify it was created
psql -l | grep music_time_machine

"""
Create PostgreSQL schema for Music Time Machine
Run this once locally, once on production
"""
import psycopg2
import os
from dotenv import load_dotenv

load_dotenv()

def create_schema():
    """Create all tables and indexes from schema file"""
    
    # Connect to PostgreSQL
    conn = psycopg2.connect(
        host=os.getenv('DB_HOST', 'localhost'),
        database=os.getenv('DB_NAME', 'music_time_machine_pg'),
        user=os.getenv('DB_USER', os.getenv('USER')),  # OS username by default
        password=os.getenv('DB_PASSWORD', '')
    )
    
    try:
        with conn.cursor() as cursor:
            # Read schema file
            with open('schema_postgres.sql', 'r') as f:
                schema_sql = f.read()
            
            # Execute schema (creates all tables and indexes)
            cursor.execute(schema_sql)
        
        conn.commit()
        print("✓ Schema created successfully")
        
        # Verify tables were created
        with conn.cursor() as cursor:
            cursor.execute("""
                SELECT table_name 
                FROM information_schema.tables 
                WHERE table_schema = 'public'
                ORDER BY table_name
            """)
            tables = cursor.fetchall()
            
            print(f"\n✓ Created {len(tables)} tables:")
            for table, in tables:
                print(f"  - {table}")
    
    except psycopg2.Error as e:
        print(f"✗ Database error: {e}")
        conn.rollback()
        raise
    
    finally:
        conn.close()

if __name__ == '__main__':
    create_schema()

python create_schema.py

✓ Schema created successfully

✓ Created 3 tables:
  - artists
  - playlist_tracks
  - tracks

import psycopg2
import os
from dotenv import load_dotenv

load_dotenv()

# Test connection
conn = psycopg2.connect(
    host=os.getenv('DB_HOST', 'localhost'),
    database=os.getenv('DB_NAME', 'music_time_machine_pg'),
    user=os.getenv('DB_USER', os.getenv('USER')),
    password=os.getenv('DB_PASSWORD', '')
)

# Test query
with conn.cursor() as cursor:
    cursor.execute("SELECT COUNT(*) FROM artists")
    count = cursor.fetchone()[0]
    print(f"Artists table: {count} rows")
    
    cursor.execute("SELECT COUNT(*) FROM tracks")
    count = cursor.fetchone()[0]
    print(f"Tracks table: {count} rows")
    
    cursor.execute("SELECT COUNT(*) FROM playlist_tracks")
    count = cursor.fetchone()[0]
    print(f"Playlist tracks table: {count} rows")

conn.close()
print("\n✓ Connection test successful")

Artists table: 0 rows
Tracks table: 0 rows
Playlist tracks table: 0 rows

✓ Connection test successful

# Production PostgreSQL on Railway
DB_HOST=monorail.proxy.rlwy.net
DB_NAME=railway
DB_USER=postgres
DB_PASSWORD=your_railway_password_here
DB_PORT=12345  # Railway assigns a port

# Spotify credentials (same as local)
SPOTIPY_CLIENT_ID=your_spotify_client_id
SPOTIPY_CLIENT_SECRET=your_spotify_client_secret
SPOTIPY_REDIRECT_URI=https://your-app.railway.app/callback

# Load production environment and create schema
export $(cat .env.production | xargs) && python create_schema.py

"""
migrate_to_postgresql.py
Migrate Music Time Machine data from SQLite to PostgreSQL
"""
import sqlite3
import psycopg2
import json
import os
from datetime import datetime
from dotenv import load_dotenv

load_dotenv()

def get_sqlite_connection():
    """Connect to source SQLite database"""
    return sqlite3.connect('music_time_machine.db')

def get_postgres_connection():
    """Connect to target PostgreSQL database"""
    return psycopg2.connect(
        host=os.getenv('DB_HOST', 'localhost'),
        database=os.getenv('DB_NAME', 'music_time_machine_pg'),
        user=os.getenv('DB_USER', os.getenv('USER')),
        password=os.getenv('DB_PASSWORD', '')
    )

def migrate_artists(sqlite_conn, pg_conn):
    """Migrate artists table (no dependencies)"""
    pass  # Implementation follows

def migrate_tracks(sqlite_conn, pg_conn):
    """Migrate tracks table (depends on artists)"""
    pass  # Implementation follows

def migrate_playlist_tracks(sqlite_conn, pg_conn):
    """Migrate playlist_tracks table (depends on tracks)"""
    pass  # Implementation follows

def verify_migration(sqlite_conn, pg_conn):
    """Verify row counts match between databases"""
    pass  # Implementation follows

def main():
    """Run complete migration"""
    print("="*60)
    print("Music Time Machine: SQLite → PostgreSQL Migration")
    print("="*60)
    
    sqlite_conn = get_sqlite_connection()
    pg_conn = get_postgres_connection()
    
    try:
        # Phase 1: Migrate artists (foundation)
        migrate_artists(sqlite_conn, pg_conn)
        
        # Phase 2: Migrate tracks (depends on artists)
        migrate_tracks(sqlite_conn, pg_conn)
        
        # Phase 3: Migrate playlist_tracks (depends on tracks)
        migrate_playlist_tracks(sqlite_conn, pg_conn)
        
        # Phase 4: Verify data integrity
        verify_migration(sqlite_conn, pg_conn)
        
        print("\n" + "="*60)
        print("✓ Migration completed successfully!")
        print("="*60)
    
    except Exception as e:
        print(f"\n✗ Migration failed: {e}")
        pg_conn.rollback()
        raise
    
    finally:
        sqlite_conn.close()
        pg_conn.close()

if __name__ == '__main__':
    main()

def migrate_artists(sqlite_conn, pg_conn):
    """Migrate artists table from SQLite to PostgreSQL"""
    print("\nMigrating artists table...")
    
    # Fetch all artists from SQLite
    sqlite_cursor = sqlite_conn.cursor()
    sqlite_cursor.execute("SELECT id, spotify_id, name, created_at FROM artists")
    artists = sqlite_cursor.fetchall()
    
    print(f"  Found {len(artists)} artists to migrate")
    
    # Insert into PostgreSQL with batch commits for performance
    pg_cursor = pg_conn.cursor()
    migrated = 0
    errors = 0
    
    for artist in artists:
        artist_id, spotify_id, name, created_at = artist
        
        try:
            # Convert TEXT timestamp to proper timestamp
            # SQLite stores as ISO format string: "2024-03-15 14:30:22"
            if created_at:
                timestamp = datetime.fromisoformat(created_at.replace('Z', '+00:00'))
            else:
                timestamp = datetime.now()
            
            pg_cursor.execute("""
                INSERT INTO artists (id, spotify_id, name, created_at)
                VALUES (%s, %s, %s, %s)
                ON CONFLICT (spotify_id) DO NOTHING
            """, (artist_id, spotify_id, name, timestamp))
            
            migrated += 1
            
            # Progress indicator every 100 artists
            if migrated % 100 == 0:
                print(f"  Progress: {migrated}/{len(artists)} artists")
        
        except Exception as e:
            errors += 1
            print(f"  Warning: Skipped artist {spotify_id}: {e}")
    
    # Commit all inserts
    pg_conn.commit()
    
    print(f"  ✓ Migrated {migrated} artists ({errors} errors)")
    
    # Reset sequence to match highest ID (important for future inserts)
    pg_cursor.execute("""
        SELECT setval('artists_id_seq', (SELECT MAX(id) FROM artists))
    """)
    pg_conn.commit()

def migrate_tracks(sqlite_conn, pg_conn):
    """Migrate tracks table with JSON to JSONB conversion"""
    print("\nMigrating tracks table...")
    
    # Fetch all tracks from SQLite
    sqlite_cursor = sqlite_conn.cursor()
    sqlite_cursor.execute("""
        SELECT id, spotify_id, name, artist_id, album_name, 
               release_date, duration_ms, popularity, audio_features, created_at
        FROM tracks
    """)
    tracks = sqlite_cursor.fetchall()
    
    print(f"  Found {len(tracks)} tracks to migrate")
    
    pg_cursor = pg_conn.cursor()
    migrated = 0
    errors = 0
    
    for track in tracks:
        (track_id, spotify_id, name, artist_id, album_name,
         release_date, duration_ms, popularity, audio_features, created_at) = track
        
        try:
            # Convert TEXT timestamp to proper timestamp
            if created_at:
                timestamp = datetime.fromisoformat(created_at.replace('Z', '+00:00'))
            else:
                timestamp = datetime.now()
            
            # Convert release_date TEXT to DATE
            # Spotify returns various formats: "2020", "2020-03", "2020-03-15"
            release_date_obj = None
            if release_date:
                try:
                    if len(release_date) == 4:  # Year only: "2020"
                        release_date_obj = datetime(int(release_date), 1, 1).date()
                    elif len(release_date) == 7:  # Year-month: "2020-03"
                        year, month = release_date.split('-')
                        release_date_obj = datetime(int(year), int(month), 1).date()
                    else:  # Full date: "2020-03-15"
                        release_date_obj = datetime.fromisoformat(release_date).date()
                except (ValueError, AttributeError):
                    release_date_obj = None
            
            # Convert JSON string to dict for JSONB storage
            # This is the key transformation: TEXT → Python dict → JSONB
            audio_features_dict = None
            if audio_features:
                try:
                    audio_features_dict = json.loads(audio_features)
                except json.JSONDecodeError as e:
                    print(f"  Warning: Invalid JSON for track {spotify_id}: {e}")
                    audio_features_dict = None
            
            pg_cursor.execute("""
                INSERT INTO tracks (
                    id, spotify_id, name, artist_id, album_name, release_date,
                    duration_ms, popularity, audio_features, created_at
                )
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                ON CONFLICT (spotify_id) DO NOTHING
            """, (
                track_id, spotify_id, name, artist_id, album_name, release_date_obj,
                duration_ms, popularity, 
                json.dumps(audio_features_dict) if audio_features_dict else None,
                timestamp
            ))
            
            migrated += 1
            
            if migrated % 200 == 0:
                print(f"  Progress: {migrated}/{len(tracks)} tracks")
        
        except Exception as e:
            errors += 1
            print(f"  Warning: Skipped track {spotify_id}: {e}")
    
    pg_conn.commit()
    
    print(f"  ✓ Migrated {migrated} tracks ({errors} errors)")
    
    # Reset sequence
    pg_cursor.execute("""
        SELECT setval('tracks_id_seq', (SELECT MAX(id) FROM tracks))
    """)
    pg_conn.commit()

def migrate_playlist_tracks(sqlite_conn, pg_conn):
    """Migrate playlist_tracks junction table"""
    print("\nMigrating playlist_tracks table...")
    
    # Fetch all playlist tracks from SQLite
    sqlite_cursor = sqlite_conn.cursor()
    sqlite_cursor.execute("""
        SELECT id, playlist_id, track_id, position, created_at
        FROM playlist_tracks
    """)
    playlist_tracks = sqlite_cursor.fetchall()
    
    print(f"  Found {len(playlist_tracks)} playlist track entries to migrate")
    
    pg_cursor = pg_conn.cursor()
    migrated = 0
    errors = 0
    
    for entry in playlist_tracks:
        entry_id, playlist_id, track_id, position, created_at = entry
        
        try:
            # Convert timestamp
            if created_at:
                timestamp = datetime.fromisoformat(created_at.replace('Z', '+00:00'))
            else:
                timestamp = datetime.now()
            
            pg_cursor.execute("""
                INSERT INTO playlist_tracks (
                    id, playlist_id, track_id, position, created_at
                )
                VALUES (%s, %s, %s, %s, %s)
                ON CONFLICT (playlist_id, track_id) DO NOTHING
            """, (entry_id, playlist_id, track_id, position, timestamp))
            
            migrated += 1
            
            if migrated % 300 == 0:
                print(f"  Progress: {migrated}/{len(playlist_tracks)} entries")
        
        except psycopg2.errors.ForeignKeyViolation:
            errors += 1
            print(f"  Warning: Track ID {track_id} doesn't exist (orphaned entry)")
        
        except Exception as e:
            errors += 1
            print(f"  Warning: Skipped playlist entry {entry_id}: {e}")
    
    pg_conn.commit()
    
    print(f"  ✓ Migrated {migrated} playlist entries ({errors} errors)")
    
    # Reset sequence
    pg_cursor.execute("""
        SELECT setval('playlist_tracks_id_seq', (SELECT MAX(id) FROM playlist_tracks))
    """)
    pg_conn.commit()

def verify_migration(sqlite_conn, pg_conn):
    """Verify migration completed successfully"""
    print("\nVerifying migration...")
    
    tables = ['artists', 'tracks', 'playlist_tracks']
    all_match = True
    
    for table in tables:
        # Count rows in SQLite
        sqlite_cursor = sqlite_conn.cursor()
        sqlite_cursor.execute(f"SELECT COUNT(*) FROM {table}")
        sqlite_count = sqlite_cursor.fetchone()[0]
        
        # Count rows in PostgreSQL
        pg_cursor = pg_conn.cursor()
        pg_cursor.execute(f"SELECT COUNT(*) FROM {table}")
        pg_count = pg_cursor.fetchone()[0]
        
        # Compare counts
        if sqlite_count == pg_count:
            print(f"  ✓ {table}: {pg_count} rows (matches SQLite)")
        else:
            print(f"  ✗ {table}: {pg_count} rows in PostgreSQL, {sqlite_count} in SQLite")
            all_match = False
    
    # Verify JSONB queryability
    print("\n  Testing JSONB audio features queries...")
    pg_cursor = pg_conn.cursor()
    
    # Test 1: Count tracks with audio features
    pg_cursor.execute("""
        SELECT COUNT(*) FROM tracks WHERE audio_features IS NOT NULL
    """)
    features_count = pg_cursor.fetchone()[0]
    print(f"  ✓ {features_count} tracks have audio features")
    
    # Test 2: Query by energy level (impossible in SQLite)
    pg_cursor.execute("""
        SELECT COUNT(*) FROM tracks
        WHERE audio_features->>'energy' IS NOT NULL
        AND (audio_features->>'energy')::float > 0.8
    """)
    high_energy_count = pg_cursor.fetchone()[0]
    print(f"  ✓ {high_energy_count} tracks have high energy (>0.8)")
    
    # Test 3: Sample a track and verify JSON structure
    pg_cursor.execute("""
        SELECT name, audio_features->>'energy', audio_features->>'valence'
        FROM tracks
        WHERE audio_features IS NOT NULL
        LIMIT 1
    """)
    sample = pg_cursor.fetchone()
    if sample:
        name, energy, valence = sample
        print(f"  ✓ Sample track '{name}': energy={energy}, valence={valence}")
    
    if all_match:
        print("\n  ✓ All row counts match - migration successful!")
    else:
        print("\n  ✗ Row count mismatch - review migration logs")
    
    return all_match

python migrate_to_postgresql.py

============================================================
Music Time Machine: SQLite → PostgreSQL Migration
============================================================

Migrating artists table...
  Found 847 artists to migrate
  Progress: 100/847 artists
  Progress: 200/847 artists
  Progress: 300/847 artists
  Progress: 400/847 artists
  Progress: 500/847 artists
  Progress: 600/847 artists
  Progress: 700/847 artists
  Progress: 800/847 artists
  ✓ Migrated 847 artists (0 errors)

Migrating tracks table...
  Found 1872 tracks to migrate
  Progress: 200/1872 tracks
  Progress: 400/1872 tracks
  Progress: 600/1872 tracks
  Progress: 800/1872 tracks
  Progress: 1000/1872 tracks
  Progress: 1200/1872 tracks
  Progress: 1400/1872 tracks
  Progress: 1600/1872 tracks
  Progress: 1800/1872 tracks
  ✓ Migrated 1872 tracks (0 errors)

Migrating playlist_tracks table...
  Found 2341 playlist track entries to migrate
  Progress: 300/2341 entries
  Progress: 600/2341 entries
  Progress: 900/2341 entries
  Progress: 1200/2341 entries
  Progress: 1500/2341 entries
  Progress: 1800/2341 entries
  Progress: 2100/2341 entries
  ✓ Migrated 2341 playlist entries (0 errors)

Verifying migration...
  ✓ artists: 847 rows (matches SQLite)
  ✓ tracks: 1872 rows (matches SQLite)
  ✓ playlist_tracks: 2341 rows (matches SQLite)

  Testing JSONB audio features queries...
  ✓ 1658 tracks have audio features
  ✓ 412 tracks have high energy (>0.8)
  ✓ Sample track 'Karma Police': energy=0.55, valence=0.31

  ✓ All row counts match - migration successful!

============================================================
✓ Migration completed successfully!
============================================================

"""
database.py
PostgreSQL connection management with pooling
"""
import os
import psycopg2
from psycopg2 import pool
from dotenv import load_dotenv

load_dotenv()

# Connection pool (initialized once at application startup)
connection_pool = None

def init_db_pool():
    """Initialize database connection pool
    
    Call this once when your Flask app starts.
    The pool maintains 2-10 reusable connections.
    """
    global connection_pool
    
    if connection_pool is None:
        connection_pool = psycopg2.pool.SimpleConnectionPool(
            minconn=2,          # Minimum connections to maintain
            maxconn=10,         # Maximum concurrent connections
            host=os.getenv('DB_HOST', 'localhost'),
            database=os.getenv('DB_NAME', 'music_time_machine_pg'),
            user=os.getenv('DB_USER', os.getenv('USER')),
            password=os.getenv('DB_PASSWORD', ''),
            port=int(os.getenv('DB_PORT', 5432))
        )
        print("✓ Database connection pool initialized (2-10 connections)")

def get_db_connection():
    """Get a connection from the pool
    
    Always use in a try/finally block to ensure
    the connection is returned to the pool.
    
    Example:
        conn = get_db_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("SELECT ...")
            conn.commit()
        finally:
            return_db_connection(conn)
    """
    if connection_pool is None:
        init_db_pool()
    
    return connection_pool.getconn()

def return_db_connection(conn):
    """Return a connection to the pool
    
    Must be called after every get_db_connection().
    The connection isn't closed, just returned for reuse.
    """
    if connection_pool:
        connection_pool.putconn(conn)

def close_db_pool():
    """Close all connections in the pool
    
    Call this when your Flask app shuts down.
    """
    global connection_pool
    if connection_pool:
        connection_pool.closeall()
        connection_pool = None
        print("✓ Database connection pool closed")

"""
app.py
Music Time Machine Flask application
"""
from flask import Flask, render_template, redirect, url_for, session, request
import os
from dotenv import load_dotenv
import spotipy
from spotipy.oauth2 import SpotifyOAuth
import atexit

# Import database module
from database import init_db_pool, close_db_pool, get_db_connection, return_db_connection

load_dotenv()

# Create Flask app
app = Flask(__name__)
app.secret_key = os.getenv('FLASK_SECRET_KEY', 'dev-secret-key-change-in-production')

# Initialize database connection pool at startup
init_db_pool()

# Register cleanup function to close pool on shutdown
atexit.register(close_db_pool)

# Spotify OAuth setup
scope = "user-top-read playlist-modify-public playlist-modify-private"

def get_spotify_client():
    """Get Spotify client with OAuth token from session"""
    token_info = session.get('token_info')
    if not token_info:
        return None
    
    return spotipy.Spotify(auth=token_info['access_token'])

@app.route('/')
def index():
    """Home page"""
    return render_template('index.html')

# More routes follow...

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

import sqlite3
import json

@app.route('/tracks/')
def get_track(track_id):
    """Get track details"""
    conn = sqlite3.connect('music_time_machine.db')
    cursor = conn.cursor()
    
    # Fetch track
    cursor.execute("""
        SELECT t.name, t.duration_ms, t.audio_features, a.name as artist_name
        FROM tracks t
        JOIN artists a ON t.artist_id = a.id
        WHERE t.id = ?
    """, (track_id,))
    
    row = cursor.fetchone()
    conn.close()
    
    if not row:
        return "Track not found", 404
    
    # Parse JSON manually
    name, duration, features_json, artist = row
    features = json.loads(features_json) if features_json else {}
    
    return render_template('track.html',
                         name=name,
                         artist=artist,
                         duration=duration,
                         energy=features.get('energy'),
                         valence=features.get('valence'))

from database import get_db_connection, return_db_connection

@app.route('/tracks/')
def get_track(track_id):
    """Get track details"""
    conn = get_db_connection()
    
    try:
        cursor = conn.cursor()
        
        # Fetch track with JSONB extraction
        cursor.execute("""
            SELECT t.name, t.duration_ms, 
                   audio_features->>'energy' as energy,
                   audio_features->>'valence' as valence,
                   a.name as artist_name
            FROM tracks t
            JOIN artists a ON t.artist_id = a.id
            WHERE t.id = %s
        """, (track_id,))
        
        row = cursor.fetchone()
        
        if not row:
            return "Track not found", 404
        
        # JSONB values come as strings, convert to float
        name, duration, energy, valence, artist = row
        
        return render_template('track.html',
                             name=name,
                             artist=artist,
                             duration=duration,
                             energy=float(energy) if energy else None,
                             valence=float(valence) if valence else None)
    
    finally:
        # Always return connection to pool
        return_db_connection(conn)

from database import get_db_connection, return_db_connection
import json
from datetime import datetime, timezone

def save_track(track_data, audio_features=None):
    """Save track to PostgreSQL with optional audio features
    
    Args:
        track_data: Dict from Spotify API with track metadata
        audio_features: Dict from Spotify audio_features endpoint (optional)
    
    Returns:
        int: Database ID of saved track
    """
    conn = get_db_connection()
    
    try:
        cursor = conn.cursor()
        
        # First, ensure artist exists
        artist_id = None
        if track_data['artists']:
            artist = track_data['artists'][0]
            
            cursor.execute("""
                INSERT INTO artists (spotify_id, name, created_at)
                VALUES (%s, %s, %s)
                ON CONFLICT (spotify_id) DO UPDATE
                SET name = EXCLUDED.name
                RETURNING id
            """, (artist['id'], artist['name'], datetime.now(timezone.utc)))
            
            artist_id = cursor.fetchone()[0]
        
        # Convert audio features dict to JSON string for JSONB column
        features_json = json.dumps(audio_features) if audio_features else None
        
        # Parse release date (Spotify returns "2020", "2020-03", or "2020-03-15")
        release_date = None
        if track_data.get('album', {}).get('release_date'):
            date_str = track_data['album']['release_date']
            try:
                if len(date_str) == 4:
                    release_date = f"{date_str}-01-01"
                elif len(date_str) == 7:
                    release_date = f"{date_str}-01"
                else:
                    release_date = date_str
            except:
                release_date = None
        
        # Insert or update track
        cursor.execute("""
            INSERT INTO tracks (
                spotify_id, name, artist_id, album_name, release_date,
                duration_ms, popularity, audio_features, created_at
            )
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
            ON CONFLICT (spotify_id) DO UPDATE
            SET name = EXCLUDED.name,
                popularity = EXCLUDED.popularity,
                audio_features = EXCLUDED.audio_features
            RETURNING id
        """, (
            track_data['id'],
            track_data['name'],
            artist_id,
            track_data.get('album', {}).get('name'),
            release_date,
            track_data['duration_ms'],
            track_data.get('popularity', 0),
            features_json,
            datetime.now(timezone.utc)
        ))
        
        track_id = cursor.fetchone()[0]
        
        # CRITICAL: Commit the transaction
        conn.commit()
        
        return track_id
    
    except Exception as e:
        # Roll back on error
        conn.rollback()
        print(f"Error saving track: {e}")
        raise
    
    finally:
        return_db_connection(conn)

from database import get_db_connection, return_db_connection
from flask import render_template
import json

@app.route('/dashboard')
def dashboard():
    """Dashboard showing listening analytics"""
    conn = get_db_connection()
    
    try:
        cursor = conn.cursor()
        
        # Get total counts
        cursor.execute("SELECT COUNT(*) FROM tracks")
        total_tracks = cursor.fetchone()[0]
        
        cursor.execute("SELECT COUNT(*) FROM artists")
        total_artists = cursor.fetchone()[0]
        
        cursor.execute("SELECT COUNT(DISTINCT playlist_id) FROM playlist_tracks")
        total_playlists = cursor.fetchone()[0]
        
        # Audio feature distribution (JSONB power query!)
        # Find average energy, valence, and danceability
        cursor.execute("""
            SELECT 
                AVG((audio_features->>'energy')::float) as avg_energy,
                AVG((audio_features->>'valence')::float) as avg_valence,
                AVG((audio_features->>'danceability')::float) as avg_danceability
            FROM tracks
            WHERE audio_features IS NOT NULL
        """)
        features = cursor.fetchone()
        
        # Most popular tracks (by popularity score)
        cursor.execute("""
            SELECT t.name, a.name as artist_name, t.popularity
            FROM tracks t
            JOIN artists a ON t.artist_id = a.id
            ORDER BY t.popularity DESC
            LIMIT 10
        """)
        top_tracks = cursor.fetchall()
        
        # Tracks by energy level (impossible in SQLite without Python parsing)
        cursor.execute("""
            SELECT 
                CASE 
                    WHEN (audio_features->>'energy')::float >= 0.8 THEN 'High Energy'
                    WHEN (audio_features->>'energy')::float >= 0.5 THEN 'Medium Energy'
                    ELSE 'Low Energy'
                END as energy_level,
                COUNT(*) as count
            FROM tracks
            WHERE audio_features IS NOT NULL
            GROUP BY energy_level
            ORDER BY MIN((audio_features->>'energy')::float) DESC
        """)
        energy_distribution = cursor.fetchall()
        
        return render_template('dashboard.html',
                             total_tracks=total_tracks,
                             total_artists=total_artists,
                             total_playlists=total_playlists,
                             avg_energy=round(features[0], 2) if features[0] else 0,
                             avg_valence=round(features[1], 2) if features[1] else 0,
                             avg_danceability=round(features[2], 2) if features[2] else 0,
                             top_tracks=top_tracks,
                             energy_distribution=energy_distribution)
    
    finally:
        return_db_connection(conn)

from flask import jsonify
from database import get_db_connection, return_db_connection

@app.route('/api/energy-distribution')
def api_energy_distribution():
    """API endpoint for Chart.js energy distribution"""
    conn = get_db_connection()
    
    try:
        cursor = conn.cursor()
        
        cursor.execute("""
            SELECT 
                CASE 
                    WHEN (audio_features->>'energy')::float >= 0.8 THEN 'High'
                    WHEN (audio_features->>'energy')::float >= 0.5 THEN 'Medium'
                    ELSE 'Low'
                END as level,
                COUNT(*) as count
            FROM tracks
            WHERE audio_features IS NOT NULL
            GROUP BY level
            ORDER BY MIN((audio_features->>'energy')::float) DESC
        """)
        
        results = cursor.fetchall()
        
        # Format for Chart.js: {labels: [], data: []}
        labels = [row[0] for row in results]
        counts = [int(row[1]) for row in results]  # Ensure integers, not Decimals
        
        return jsonify({
            'labels': labels,
            'data': counts
        })
    
    finally:
        return_db_connection(conn)

// Fetch energy distribution data and create chart
fetch('/api/energy-distribution')
    .then(response => response.json())
    .then(data => {
        const ctx = document.getElementById('energyChart').getContext('2d');
        new Chart(ctx, {
            type: 'bar',
            data: {
                labels: data.labels,
                datasets: [{
                    label: 'Tracks by Energy Level',
                    data: data.data,
                    backgroundColor: [
                        'rgba(255, 99, 132, 0.2)',
                        'rgba(54, 162, 235, 0.2)',
                        'rgba(255, 206, 86, 0.2)'
                    ],
                    borderColor: [
                        'rgba(255, 99, 132, 1)',
                        'rgba(54, 162, 235, 1)',
                        'rgba(255, 206, 86, 1)'
                    ],
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                scales: {
                    y: {
                        beginAtZero: true,
                        ticks: {
                            stepSize: 100
                        }
                    }
                }
            }
        });
    })
    .catch(error => {
        console.error('Error loading energy chart:', error);
    });

"""
Test concurrent database writes to verify PostgreSQL handles them
"""
import threading
import time
from database import get_db_connection, return_db_connection, init_db_pool

init_db_pool()

def write_to_database(thread_id, num_writes):
    """Simulate a user creating playlist entries"""
    successes = 0
    failures = 0
    start_time = time.time()
    
    for i in range(num_writes):
        conn = get_db_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                INSERT INTO playlist_tracks (playlist_id, track_id, position, created_at)
                VALUES (%s, %s, %s, CURRENT_TIMESTAMP)
            """, (f'test_playlist_{thread_id}', 1, i))
            conn.commit()
            successes += 1
        except Exception as e:
            failures += 1
            print(f"Thread {thread_id} error: {e}")
            conn.rollback()
        finally:
            return_db_connection(conn)
    
    elapsed = time.time() - start_time
    print(f"Thread {thread_id}: {successes} successes, {failures} failures in {elapsed:.2f}s")
    return successes, failures

def test_concurrent_writes(num_threads=10, writes_per_thread=50):
    """Test concurrent writes from multiple threads"""
    print(f"\nTesting {num_threads} concurrent threads, {writes_per_thread} writes each")
    print("="*60)
    
    threads = []
    start_time = time.time()
    
    # Create and start threads
    for i in range(num_threads):
        thread = threading.Thread(target=write_to_database, args=(i, writes_per_thread))
        threads.append(thread)
        thread.start()
    
    # Wait for all threads to complete
    for thread in threads:
        thread.join()
    
    total_time = time.time() - start_time
    total_writes = num_threads * writes_per_thread
    
    print("="*60)
    print(f"Total time: {total_time:.2f}s")
    print(f"Writes per second: {total_writes / total_time:.1f}")
    print(f"Average latency: {(total_time / total_writes) * 1000:.1f}ms per write")
    
    # Clean up test data
    conn = get_db_connection()
    try:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM playlist_tracks WHERE playlist_id LIKE 'test_playlist_%'")
        deleted = cursor.rowcount
        conn.commit()
        print(f"\nCleaned up {deleted} test entries")
    finally:
        return_db_connection(conn)

if __name__ == '__main__':
    test_concurrent_writes(num_threads=10, writes_per_thread=50)

Testing 10 concurrent threads, 50 writes each
============================================================
Thread 0: 50 successes, 0 failures in 0.82s
Thread 1: 50 successes, 0 failures in 0.85s
Thread 2: 50 successes, 0 failures in 0.83s
Thread 3: 50 successes, 0 failures in 0.86s
Thread 4: 50 successes, 0 failures in 0.84s
Thread 5: 50 successes, 0 failures in 0.87s
Thread 6: 50 successes, 0 failures in 0.85s
Thread 7: 50 successes, 0 failures in 0.83s
Thread 8: 50 successes, 0 failures in 0.86s
Thread 9: 50 successes, 0 failures in 0.84s
============================================================
Total time: 0.89s
Writes per second: 561.8
Average latency: 1.8ms per write

Cleaned up 500 test entries

# Step 1: Load production environment variables
export $(cat .env.production | xargs)

# Step 2: Create schema on production database
python create_schema.py

# Step 3: Migrate data to production database
python migrate_to_postgresql.py

# Step 4: Verify migration
psql $DATABASE_URL -c "SELECT COUNT(*) FROM tracks"
psql $DATABASE_URL -c "SELECT COUNT(*) FROM artists"
psql $DATABASE_URL -c "SELECT COUNT(*) FROM playlist_tracks"

-- Add tsvector column for full-text search
ALTER TABLE tracks ADD COLUMN search_vector tsvector;

-- Create function to update search vector automatically
CREATE OR REPLACE FUNCTION tracks_search_update() RETURNS trigger AS $$
BEGIN
    NEW.search_vector := 
        setweight(to_tsvector('english', coalesce(NEW.name, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(NEW.album_name, '')), 'B');
    RETURN NEW;
END
$$ LANGUAGE plpgsql;

-- Trigger to update search vector on insert or update
CREATE TRIGGER tracks_search_trigger
BEFORE INSERT OR UPDATE ON tracks
FOR EACH ROW EXECUTE FUNCTION tracks_search_update();

-- Populate search vectors for existing tracks
UPDATE tracks SET name = name;  -- Triggers the update function

-- Create GIN index for fast full-text search
CREATE INDEX idx_tracks_search ON tracks USING GIN(search_vector);

from database import get_db_connection, return_db_connection
from flask import request, render_template

@app.route('/search')
def search_tracks():
    """Search tracks with full-text search and ranking"""
    query = request.args.get('q', '')
    
    if not query:
        return render_template('search.html', results=[], query='')
    
    conn = get_db_connection()
    
    try:
        cursor = conn.cursor()
        
        # Full-text search with ranking
        cursor.execute("""
            SELECT 
                t.id,
                t.name,
                a.name as artist_name,
                t.album_name,
                ts_rank(t.search_vector, query) as rank
            FROM tracks t
            JOIN artists a ON t.artist_id = a.id,
                 plainto_tsquery('english', %s) query
            WHERE t.search_vector @@ query
            ORDER BY rank DESC, t.popularity DESC
            LIMIT 50
        """, (query,))
        
        results = cursor.fetchall()
        
        return render_template('search.html', 
                             results=results, 
                             query=query,
                             count=len(results))
    
    finally:
        return_db_connection(conn)

-- Find tracks similar to a given track based on audio features
WITH target AS (
    SELECT audio_features
    FROM tracks
    WHERE spotify_id = '3n3Ppam7vgaVa1iaRUc9Lp'  -- Mr. Brightside
)
SELECT 
    t.name,
    a.name as artist,
    -- Calculate similarity score across multiple features
    1 - (
        ABS((t.audio_features->>'energy')::float - (target.audio_features->>'energy')::float) +
        ABS((t.audio_features->>'valence')::float - (target.audio_features->>'valence')::float) +
        ABS((t.audio_features->>'danceability')::float - (target.audio_features->>'danceability')::float)
    ) / 3 as similarity
FROM tracks t
JOIN artists a ON t.artist_id = a.id
CROSS JOIN target
WHERE t.audio_features IS NOT NULL
  AND t.spotify_id != '3n3Ppam7vgaVa1iaRUc9Lp'  -- Exclude the target track itself
ORDER BY similarity DESC
LIMIT 20;

-- Categorize your library into mood quadrants
SELECT 
    CASE 
        WHEN (audio_features->>'energy')::float > 0.5 AND (audio_features->>'valence')::float > 0.5 
            THEN 'Happy & Energetic'
        WHEN (audio_features->>'energy')::float > 0.5 AND (audio_features->>'valence')::float <= 0.5 
            THEN 'Angry & Intense'
        WHEN (audio_features->>'energy')::float <= 0.5 AND (audio_features->>'valence')::float > 0.5 
            THEN 'Calm & Peaceful'
        ELSE 'Sad & Melancholic'
    END as mood,
    COUNT(*) as track_count,
    ROUND(AVG((audio_features->>'danceability')::float)::numeric, 2) as avg_danceability,
    ROUND(AVG((audio_features->>'acousticness')::float)::numeric, 2) as avg_acousticness
FROM tracks
WHERE audio_features IS NOT NULL
GROUP BY mood
ORDER BY track_count DESC;

-- Analyze a potentially slow query
EXPLAIN ANALYZE
SELECT t.name, a.name as artist, t.popularity
FROM tracks t
JOIN artists a ON t.artist_id = a.id
WHERE (t.audio_features->>'energy')::float > 0.8
ORDER BY t.popularity DESC
LIMIT 50;

Limit  (cost=156.34..156.47 rows=50 width=1032) (actual time=12.453..12.489 rows=50 loops=1)
  ->  Sort  (cost=156.34..158.09 rows=702 width=1032) (actual time=12.451..12.469 rows=50 loops=1)
        Sort Key: t.popularity DESC
        Sort Method: top-N heapsort  Memory: 35kB
        ->  Hash Join  (cost=28.15..141.72 rows=702 width=1032) (actual time=1.234..9.876 rows=412 loops=1)
              Hash Cond: (t.artist_id = a.id)
              ->  Bitmap Heap Scan on tracks t  (cost=12.24..119.56 rows=702 width=1028) (actual time=0.523..7.234 rows=412 loops=1)
                    Recheck Cond: (((audio_features ->> 'energy'::text))::double precision > 0.8)
                    Heap Blocks: exact=156
                    ->  Bitmap Index Scan on idx_tracks_audio_features  (cost=0.00..12.07 rows=702 width=0) (actual time=0.445..0.445 rows=412 loops=1)
                          Index Cond: (((audio_features ->> 'energy'::text))::double precision > 0.8)
              ->  Hash  (cost=11.47..11.47 rows=847 width=16) (actual time=0.687..0.688 rows=847 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 53kB
                    ->  Seq Scan on artists a  (cost=0.00..11.47 rows=847 width=16) (actual time=0.012..0.334 rows=847 loops=1)
Planning Time: 0.523 ms
Execution Time: 12.567 ms