325f7f400f
Draft/published article library with stable section IDs, breadcrumbs, link remediation, question and card associations, manual card creation and side-by-side/mobile-drawer reading. Migration e8d4f1a27c93. Verified 42 deployed-image backend tests, 72 frontend tests/build and PostgreSQL migration round-trip.
67 lines
2.9 KiB
Python
67 lines
2.9 KiB
Python
"""Topic articles and card/question associations.
|
|
|
|
Revision ID: e8d4f1a27c93
|
|
Revises: d94a26b8f302
|
|
"""
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
|
|
revision = "e8d4f1a27c93"
|
|
down_revision = "d94a26b8f302"
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
|
|
def upgrade():
|
|
op.execute("""
|
|
CREATE TABLE IF NOT EXISTS articles (
|
|
id SERIAL PRIMARY KEY,
|
|
slug VARCHAR(120) NOT NULL UNIQUE,
|
|
title VARCHAR(300) NOT NULL,
|
|
summary TEXT,
|
|
content TEXT,
|
|
sections JSON NOT NULL DEFAULT '[]',
|
|
category_id INTEGER REFERENCES question_categories(id) ON DELETE SET NULL,
|
|
section_id INTEGER REFERENCES sections(id) ON DELETE SET NULL,
|
|
user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'draft',
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
)""")
|
|
op.execute("CREATE INDEX IF NOT EXISTS ix_articles_id ON articles (id)")
|
|
op.execute("CREATE INDEX IF NOT EXISTS ix_articles_slug ON articles (slug)")
|
|
op.execute("""
|
|
CREATE TABLE IF NOT EXISTS question_article_links (
|
|
id SERIAL PRIMARY KEY,
|
|
question_id INTEGER NOT NULL REFERENCES questions(id) ON DELETE CASCADE,
|
|
article_id INTEGER NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
|
|
section_id VARCHAR(64),
|
|
user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT uq_question_article_section UNIQUE (question_id, article_id, section_id)
|
|
)""")
|
|
op.execute("CREATE INDEX IF NOT EXISTS ix_question_article_links_id ON question_article_links (id)")
|
|
op.execute("""
|
|
CREATE TABLE IF NOT EXISTS flashcard_question_links (
|
|
id SERIAL PRIMARY KEY,
|
|
flashcard_id INTEGER NOT NULL REFERENCES flashcards(id) ON DELETE CASCADE,
|
|
question_id INTEGER NOT NULL REFERENCES questions(id) ON DELETE CASCADE,
|
|
CONSTRAINT uq_card_question UNIQUE (flashcard_id, question_id)
|
|
)""")
|
|
op.execute("CREATE INDEX IF NOT EXISTS ix_flashcard_question_links_id ON flashcard_question_links (id)")
|
|
op.execute("""
|
|
CREATE TABLE IF NOT EXISTS flashcard_article_links (
|
|
id SERIAL PRIMARY KEY,
|
|
flashcard_id INTEGER NOT NULL REFERENCES flashcards(id) ON DELETE CASCADE,
|
|
article_id INTEGER NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
|
|
article_section_id VARCHAR(64),
|
|
CONSTRAINT uq_card_article_section UNIQUE (flashcard_id, article_id, article_section_id)
|
|
)""")
|
|
op.execute("CREATE INDEX IF NOT EXISTS ix_flashcard_article_links_id ON flashcard_article_links (id)")
|
|
|
|
|
|
def downgrade():
|
|
op.execute("DROP TABLE IF EXISTS flashcard_article_links")
|
|
op.execute("DROP TABLE IF EXISTS flashcard_question_links")
|
|
op.execute("DROP TABLE IF EXISTS question_article_links")
|
|
op.execute("DROP TABLE IF EXISTS articles")
|