"""create the reference library (参考文献) Two tables for the literature itself, and one foreign key that has been waiting for them: ``reference`` one document: an article, a book, a patent, a dataset. Global rather than owned by a paper, because the same article is cited from several papers. ``reference_author`` its contributors, in the order the citation prints them. A child table rather than a column: a citation prints an ordered list with 编 / 译 marks, and styles rewrite names (「张三」 / "Zhang, S." / "S. Zhang"), so the printed form and its two halves are stored separately. The columns are the intersection of CSL JSON, GB/T 7714-2015, BibTeX and RIS — the reasoning is on :mod:`app.models.reference`. Two notes that belong to the schema rather than the model: * Partial dates are three nullable integers (year, month, day) rather than one ``DATE``, because that is what CSL models as ``issued``: a book has a year, a newspaper article a day, a patent its 公告日期, and a citation must not invent the rest. * ``volume`` / ``issue`` / ``pages`` are strings. Journals print ``12(Suppl.)``, ``S1`` and ``e12345``, and a citation reproduces what is printed. ``paper_sentence_reference.reference_id`` finally becomes a real foreign key, ``ON DELETE RESTRICT``: a citation must not be able to point at literature that has been removed from the library. The library is empty at this revision and so is the citation table, so no existing row can violate it. Revision ID: e4b7c1f9a036 Revises: b19e4d7a3c58 Create Date: 2026-09-22 """ from collections.abc import Sequence import sqlalchemy as sa from alembic import op # revision identifiers, used by Alembic. revision: str = "e4b7c1f9a036" down_revision: str | None = "b19e4d7a3c58" branch_labels: str | Sequence[str] | None = None depends_on: str | Sequence[str] | None = None _NOW = sa.text("CURRENT_TIMESTAMP") #: (name, type) for every text column, in one place so the model and the table #: cannot drift apart on a length. _TEXT_COLUMNS: tuple[tuple[str, int], ...] = ( ("subtitle", 512), ("title_translated", 512), ("title_short", 255), ("container_title", 512), ("container_title_short", 255), ("series_title", 512), ("series_number", 32), ("edition", 64), ("volume", 32), ("issue", 32), ("pages", 64), ("article_number", 64), ("publisher", 255), ("publisher_place", 255), ("event_title", 512), ("event_place", 255), ("event_date", 64), ("doi", 255), ("isbn", 32), ("issn", 32), ("url", 1024), ("patent_country", 64), ("patent_number", 64), ("standard_number", 64), ("report_number", 64), ("arxiv_id", 64), ("keywords", 512), ("citation_key", 64), ("language", 16), ) def upgrade() -> None: op.create_table( "reference", sa.Column("id", sa.Integer(), autoincrement=True, nullable=False), sa.Column( "type", sa.String(length=32), server_default=sa.text("'journal-article'"), nullable=False, ), sa.Column("title", sa.String(length=512), nullable=False), *[ sa.Column(name, sa.String(length=length), nullable=True) for name, length in _TEXT_COLUMNS ], sa.Column("publication_year", sa.SmallInteger(), nullable=True), sa.Column("publication_month", sa.SmallInteger(), nullable=True), sa.Column("publication_day", sa.SmallInteger(), nullable=True), sa.Column("accessed_date", sa.Date(), nullable=True), sa.Column("abstract", sa.Text(), nullable=True), sa.Column("note", sa.Text(), nullable=True), sa.Column("created_at", sa.DateTime(), server_default=_NOW, nullable=False), sa.Column("updated_at", sa.DateTime(), server_default=_NOW, nullable=False), sa.PrimaryKeyConstraint("id"), ) op.create_index( "ix_reference_type_year", "reference", ["type", "publication_year"], unique=False, ) op.create_table( "reference_author", sa.Column("id", sa.Integer(), autoincrement=True, nullable=False), sa.Column("reference_id", sa.Integer(), nullable=False), sa.Column("sort", sa.Integer(), nullable=False), sa.Column("name", sa.String(length=255), nullable=False), sa.Column("family_name", sa.String(length=128), nullable=True), sa.Column("given_name", sa.String(length=128), nullable=True), sa.Column( "role", sa.String(length=16), server_default=sa.text("'author'"), nullable=False, ), sa.ForeignKeyConstraint(["reference_id"], ["reference.id"], ondelete="CASCADE"), sa.PrimaryKeyConstraint("id"), ) op.create_index( "ix_reference_author_reference_sort", "reference_author", ["reference_id", "sort"], unique=False, ) # The citation column stops being a loose integer: it now points at the # library, and the database refuses a citation to literature that is gone. op.create_foreign_key( "fk_paper_sentence_reference_reference", "paper_sentence_reference", "reference", ["reference_id"], ["id"], ondelete="RESTRICT", ) def downgrade() -> None: op.drop_constraint( "fk_paper_sentence_reference_reference", "paper_sentence_reference", type_="foreignkey", ) op.drop_index("ix_reference_author_reference_sort", table_name="reference_author") op.drop_table("reference_author") op.drop_index("ix_reference_type_year", table_name="reference") op.drop_table("reference")