bvandeusen
b761db4c44
The Dockerfile runs `alembic stamp --purge base && alembic upgrade head` on every restart, so all migrations re-run against an existing database. Migration 0004 used `op.add_column()` which fails on re-run with "column already exists". Migration 0005 used `op.create_table()` which would fail similarly. Both now use raw SQL with idempotency guards: - 0004: `DO $$ BEGIN ALTER TABLE ADD COLUMN ... EXCEPTION WHEN duplicate_column` + `IF EXISTS` check before touching the tasks table - 0005: `CREATE TABLE IF NOT EXISTS` + `CREATE INDEX IF NOT EXISTS` Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
76 lines
2.3 KiB
Python
76 lines
2.3 KiB
Python
"""merge tasks into notes table
|
|
|
|
Revision ID: 0004
|
|
Revises: 0003
|
|
Create Date: 2025-01-01 00:00:00.000000
|
|
"""
|
|
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
|
|
revision = "0004"
|
|
down_revision = "0003"
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
conn = op.get_bind()
|
|
|
|
# Add task columns to notes table (idempotent)
|
|
conn.execute(sa.text("""
|
|
DO $$ BEGIN
|
|
ALTER TABLE notes ADD COLUMN status TEXT;
|
|
EXCEPTION WHEN duplicate_column THEN null;
|
|
END $$
|
|
"""))
|
|
conn.execute(sa.text("""
|
|
DO $$ BEGIN
|
|
ALTER TABLE notes ADD COLUMN priority TEXT;
|
|
EXCEPTION WHEN duplicate_column THEN null;
|
|
END $$
|
|
"""))
|
|
conn.execute(sa.text("""
|
|
DO $$ BEGIN
|
|
ALTER TABLE notes ADD COLUMN due_date DATE;
|
|
EXCEPTION WHEN duplicate_column THEN null;
|
|
END $$
|
|
"""))
|
|
conn.execute(sa.text(
|
|
"CREATE INDEX IF NOT EXISTS ix_notes_status ON notes (status)"
|
|
))
|
|
|
|
# Migrate tasks that have companion notes (only if tasks table exists):
|
|
conn.execute(sa.text("""
|
|
DO $$ BEGIN
|
|
IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'tasks') THEN
|
|
UPDATE notes
|
|
SET status = t.status,
|
|
priority = t.priority,
|
|
due_date = t.due_date,
|
|
body = COALESCE(NULLIF(t.description, ''), notes.body),
|
|
tags = CASE
|
|
WHEN array_length(t.tags, 1) IS NOT NULL THEN t.tags
|
|
ELSE notes.tags
|
|
END
|
|
FROM tasks t
|
|
WHERE t.note_id = notes.id;
|
|
|
|
INSERT INTO notes (title, body, tags, status, priority, due_date, created_at, updated_at)
|
|
SELECT title, description, tags, status, priority, due_date, created_at, updated_at
|
|
FROM tasks
|
|
WHERE note_id IS NULL;
|
|
|
|
DROP TABLE tasks;
|
|
END IF;
|
|
END $$
|
|
"""))
|
|
|
|
# Drop the old enum types that are no longer used by any table
|
|
conn.execute(sa.text("DROP TYPE IF EXISTS task_status"))
|
|
conn.execute(sa.text("DROP TYPE IF EXISTS task_priority"))
|
|
|
|
|
|
def downgrade() -> None:
|
|
raise NotImplementedError("Downgrade not supported for this migration")
|