Repository: FlexMeasures/flexmeasures (language: python)

Issue:
Investigate why migration file adds changes to annotations and datasources
              I didn't notice these before, actually. When I rerun `flexmeasures db migrate` just now (against `main`) the annotation table is not mentioned in the auto-generated revision statements for me (I'm running `alembic==1.12.0`). Perhaps either one of our databases is in an abnormal state when we run the migrate command. If you can reproduce these statements on your current db, we could try again on a fresh db to find out.

In case we do need to include them, follow-ups are:
- Find out what the alterations actually are. Maybe these two columns were not nullable before?
- Discuss what we want. Change the Annotation class to reflect the db state, or vice versa. (For instance, my first thought would be that annotations *should* always have a source.)
- Adapt the downgrade command to reflect the changes. The annotation table is currently not mentioned in the downgrade.

_Originally posted by @Flix6x in https://github.com/FlexMeasures/flexmeasures/pull/921#discussion_r1420328684_
            


Discussion / hints:

Fix patch:
diff --git a/.coveragerc b/.coveragerc
index 9236c48e8..5e6ed14d4 100644
--- a/.coveragerc
+++ b/.coveragerc
@@ -1,2 +1,2 @@
 [run]
-omit = */tests/*, */scripts/*, **/*.jinja, **/*.html, **/*.txt
+omit = */tests/*, */scripts/*, **/*.jinja, **/*.html, **/*.txt, flexmeasures/data/migrations/versions/*
diff --git a/documentation/changelog.rst b/documentation/changelog.rst
index 5fc8df922..9d930e0d1 100644
--- a/documentation/changelog.rst
+++ b/documentation/changelog.rst
@@ -19,6 +19,7 @@ New features
 Infrastructure / Support
 ----------------------
 
+* Align database and models of `annotations`, `data_sources`, and `timed_belief` [see `PR #929 <https://github.com/FlexMeasures/flexmeasures/pull/929>`_]
 * New documentation section on constructing a flex model for :abbr:`V2G (vehicle-to-grid)` [see `PR #885 <https://github.com/FlexMeasures/flexmeasures/pull/885>`_]
 * Allow charts in plugins to show currency codes (such as EUR) as currency symbols (€) [see `PR #922 <https://github.com/FlexMeasures/flexmeasures/pull/922>`_]
 * Remove obsolete database tables `price`, `power`, `market`, `market_type`, `weather`, `asset`, and `weather_sensor` [see `PR #921 <https://github.com/FlexMeasures/flexmeasures/pull/921>`_]
diff --git a/flexmeasures/data/migrations/versions/c349f52c700d_update_data_sources.py b/flexmeasures/data/migrations/versions/c349f52c700d_update_data_sources.py
new file mode 100644
index 000000000..bb21fea80
--- /dev/null
+++ b/flexmeasures/data/migrations/versions/c349f52c700d_update_data_sources.py
@@ -0,0 +1,73 @@
+"""Update data sources
+
+Revision ID: c349f52c700d
+Revises: ad98460751d9
+Create Date: 2023-12-14 10:31:02.612590
+
+"""
+from alembic import op
+import sqlalchemy as sa
+from sqlalchemy.dialects import postgresql
+
+# revision identifiers, used by Alembic.
+revision = "c349f52c700d"
+down_revision = "ad98460751d9"
+branch_labels = None
+depends_on = None
+
+
+def upgrade():
+    # The name of the data_source should be 120 String, this was not correctly set in an earlier revision of the db.
+    with op.batch_alter_table("data_source", schema=None) as batch_op:
+        batch_op.alter_column(
+            "name",
+            existing_type=sa.VARCHAR(length=80),
+            type_=sa.String(length=120),
+            existing_nullable=False,
+        )
+        # The attributes were initially set as nullable=False but the migration file did not reflect that.
+        # In this migration the model and db are brought in line.
+        batch_op.alter_column(
+            "attributes",
+            existing_type=postgresql.JSON(astext_type=sa.Text()),
+            nullable=False,
+        )
+
+    # This constraint is renamed to include the full name of the `data_source` table.
+    with op.batch_alter_table("timed_belief", schema=None) as batch_op:
+        batch_op.drop_constraint(
+            "timed_belief_source_id_source_fkey", type_="foreignkey"
+        )
+        batch_op.create_foreign_key(
+            batch_op.f("timed_belief_source_id_data_source_fkey"),
+            "data_source",
+            ["source_id"],
+            ["id"],
+        )
+
+
+def downgrade():
+    with op.batch_alter_table("timed_belief", schema=None) as batch_op:
+        batch_op.drop_constraint(
+            batch_op.f("timed_belief_source_id_data_source_fkey"), type_="foreignkey"
+        )
+        batch_op.create_foreign_key(
+            "timed_belief_source_id_source_fkey",
+            "data_source",
+            ["source_id"],
+            ["id"],
+            ondelete="CASCADE",
+        )
+
+    with op.batch_alter_table("data_source", schema=None) as batch_op:
+        batch_op.alter_column(
+            "attributes",
+            existing_type=postgresql.JSON(astext_type=sa.Text()),
+            nullable=True,
+        )
+        batch_op.alter_column(
+            "name",
+            existing_type=sa.String(length=120),
+            type_=sa.VARCHAR(length=80),
+            existing_nullable=False,
+        )
diff --git a/flexmeasures/data/models/annotations.py b/flexmeasures/data/models/annotations.py
index 882a19bfc..fe19c5223 100644
--- a/flexmeasures/data/models/annotations.py
+++ b/flexmeasures/data/models/annotations.py
@@ -22,14 +22,15 @@ class Annotation(db.Model):
     start = db.Column(db.DateTime(timezone=True), nullable=False)
     end = db.Column(db.DateTime(timezone=True), nullable=False)
     belief_time = db.Column(db.DateTime(timezone=True), nullable=True)
-    source_id = db.Column(db.Integer, db.ForeignKey("data_source.id"))
+    source_id = db.Column(db.Integer, db.ForeignKey("data_source.id"), nullable=False)
     source = db.relationship(
         "DataSource",
         foreign_keys=[source_id],
         backref=db.backref("annotations", lazy=True),
     )
     type = db.Column(
-        db.Enum("alert", "holiday", "label", "feedback", name="annotation_type")
+        db.Enum("alert", "holiday", "label", "feedback", name="annotation_type"),
+        nullable=False,
     )
     content = db.Column(db.String(1024), nullable=False)
     __table_args__ = (


Test patch:
diff --git a/flexmeasures/data/tests/test_annotations.py b/flexmeasures/data/tests/test_annotations.py
index e0c8cfde4..dd8c86ba0 100644
--- a/flexmeasures/data/tests/test_annotations.py
+++ b/flexmeasures/data/tests/test_annotations.py
@@ -4,7 +4,7 @@
 from flexmeasures.data.models.data_sources import DataSource
 
 
-def test_get_or_create_annotation(db):
+def test_get_or_create_annotation(db, setup_sources):
     """Save an annotation, then get_or_create a new annotation with the same contents."""
     num_annotations_before = Annotation.query.count()
     source = DataSource.query.first()