128 lines
5.3 KiB
Python
128 lines
5.3 KiB
Python
"""Dynamic attribute and criterion value records.
|
|
|
|
Revision ID: 0004_dynamic_attributes
|
|
Revises: 0003_discovery_schema
|
|
Create Date: 2026-10-04
|
|
"""
|
|
|
|
from collections.abc import Sequence
|
|
|
|
import sqlalchemy as sa
|
|
from alembic import op
|
|
from sqlalchemy.dialects import postgresql
|
|
|
|
revision: str = "0004_dynamic_attributes"
|
|
down_revision: str | None = "0003_discovery_schema"
|
|
branch_labels: str | Sequence[str] | None = None
|
|
depends_on: str | Sequence[str] | None = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
op.add_column("criterion_definitions", sa.Column("category", sa.String(128)))
|
|
op.add_column("source_categories", sa.Column("canonical_category", sa.String(128)))
|
|
op.create_table(
|
|
"project_criteria",
|
|
sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True),
|
|
sa.Column("project_id", postgresql.UUID(as_uuid=True), nullable=False),
|
|
sa.Column("version", sa.Integer(), nullable=False),
|
|
sa.Column("item_id", sa.String(128), nullable=False),
|
|
sa.Column("criterion_key", sa.String(128), sa.ForeignKey("criterion_definitions.key"), nullable=False),
|
|
sa.Column("operator", sa.String(16), nullable=False),
|
|
sa.Column("value", postgresql.JSONB),
|
|
sa.Column("mandatory", sa.Boolean(), nullable=False, server_default=sa.true()),
|
|
sa.ForeignKeyConstraint(
|
|
["project_id", "version"],
|
|
["buying_project_criteria.project_id", "buying_project_criteria.version"],
|
|
name="fk_project_criteria_version",
|
|
),
|
|
sa.UniqueConstraint("project_id", "version", "item_id", name="uq_project_criterion_item"),
|
|
)
|
|
op.create_table(
|
|
"product_observation_attributes",
|
|
sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True),
|
|
sa.Column(
|
|
"observation_id",
|
|
postgresql.UUID(as_uuid=True),
|
|
sa.ForeignKey("product_observations.id"),
|
|
nullable=False,
|
|
),
|
|
sa.Column("attribute_key", sa.String(128), nullable=False),
|
|
sa.Column("raw_value", postgresql.JSONB),
|
|
sa.Column("normalized_value", postgresql.JSONB),
|
|
sa.Column("unit", sa.String(32)),
|
|
sa.UniqueConstraint("observation_id", "attribute_key", name="uq_observation_attribute"),
|
|
)
|
|
_migrate_criteria_json()
|
|
_migrate_observation_json()
|
|
op.drop_column("buying_project_criteria", "definition")
|
|
op.drop_column("product_observations", "raw_attributes")
|
|
op.drop_column("product_observations", "normalized_attributes")
|
|
|
|
|
|
def _migrate_criteria_json() -> None:
|
|
bind = op.get_bind()
|
|
rows = bind.execute(sa.text("SELECT project_id, version, definition FROM buying_project_criteria")).mappings()
|
|
for row in rows:
|
|
items = (row["definition"] or {}).get("items") or []
|
|
for raw in items:
|
|
bind.execute(
|
|
sa.text(
|
|
"""
|
|
INSERT INTO project_criteria
|
|
(id, project_id, version, item_id, criterion_key, operator, value, mandatory)
|
|
VALUES
|
|
(gen_random_uuid(), :project_id, :version, :item_id, :criterion_key, :operator, :value, :mandatory)
|
|
"""
|
|
),
|
|
{
|
|
"project_id": row["project_id"],
|
|
"version": row["version"],
|
|
"item_id": str(raw.get("id") or raw.get("criterion")),
|
|
"criterion_key": str(raw.get("criterion")),
|
|
"operator": str(raw.get("operator")),
|
|
"value": raw.get("value"),
|
|
"mandatory": bool(raw.get("mandatory", True)),
|
|
},
|
|
)
|
|
|
|
|
|
def _migrate_observation_json() -> None:
|
|
bind = op.get_bind()
|
|
rows = bind.execute(
|
|
sa.text("SELECT id, raw_attributes, normalized_attributes FROM product_observations")
|
|
).mappings()
|
|
for row in rows:
|
|
raw = row["raw_attributes"] or {}
|
|
normalized = row["normalized_attributes"] or {}
|
|
keys = set(raw) | set(normalized)
|
|
for key in keys:
|
|
bind.execute(
|
|
sa.text(
|
|
"""
|
|
INSERT INTO product_observation_attributes
|
|
(id, observation_id, attribute_key, raw_value, normalized_value, unit)
|
|
VALUES
|
|
(gen_random_uuid(), :observation_id, :attribute_key, :raw_value, :normalized_value, NULL)
|
|
"""
|
|
),
|
|
{
|
|
"observation_id": row["id"],
|
|
"attribute_key": key,
|
|
"raw_value": raw.get(key),
|
|
"normalized_value": normalized.get(key),
|
|
},
|
|
)
|
|
|
|
|
|
def downgrade() -> None:
|
|
op.add_column("product_observations", sa.Column("raw_attributes", postgresql.JSONB, nullable=False, server_default="{}"))
|
|
op.add_column(
|
|
"product_observations",
|
|
sa.Column("normalized_attributes", postgresql.JSONB, nullable=False, server_default="{}"),
|
|
)
|
|
op.add_column("buying_project_criteria", sa.Column("definition", postgresql.JSONB, nullable=False, server_default='{"items":[]}'))
|
|
op.drop_table("product_observation_attributes")
|
|
op.drop_table("project_criteria")
|
|
op.drop_column("source_categories", "canonical_category")
|
|
op.drop_column("criterion_definitions", "category")
|