Coverage for toardb/timeseries/models_core.py: 100%
35 statements
« prev ^ index » next coverage.py v7.11.0, created at 2025-11-03 20:32 +0000
1# SPDX-FileCopyrightText: 2021 Forschungszentrum Jülich GmbH
2# SPDX-License-Identifier: MIT
4"""
5class Timeseries (Base)
6=======================
7"""
8from sqlalchemy import Column, DateTime, Float, ForeignKey, Integer, text, String, \
9 Text, CheckConstraint, UniqueConstraint, PrimaryKeyConstraint, \
10 Sequence
11from sqlalchemy.orm import relationship
12from sqlalchemy.dialects.postgresql import JSONB
13from toardb.stationmeta.models import StationmetaCore
14from toardb.variables.models import Variable
15from .models_programme import TimeseriesProgramme
16from toardb.base import Base
19TIMESERIES_ID_SEQ = Sequence('timeseries_id_seq') # define sequence explicitly
20class Timeseries(Base):
21 """
22 Table "public.timeseries"
24 +------------------------------+--------------------------+-----------+----------+----------------------------------------+
25 | Column | Type | Collation | Nullable | Default |
26 +==============================+==========================+===========+==========+========================================+
27 | id | integer | | not null | nextval('timeseries_id_seq'::regclass) |
28 +------------------------------+--------------------------+-----------+----------+----------------------------------------+
29 | label | character varying(128) | | not null | |
30 +------------------------------+--------------------------+-----------+----------+----------------------------------------+
31 | order | integer | | not null | |
32 +------------------------------+--------------------------+-----------+----------+----------------------------------------+
33 | sampling_frequency | integer | | not null | |
34 +------------------------------+--------------------------+-----------+----------+----------------------------------------+
35 | aggregation | integer | | not null | |
36 +------------------------------+--------------------------+-----------+----------+----------------------------------------+
37 | data_start_date | timestamp with time zone | | not null | '2050-01-01 00:00:00' |
38 +------------------------------+--------------------------+-----------+----------+----------------------------------------+
39 | data_end_date | timestamp with time zone | | not null | '1900-01-01 00:00:00' |
40 +------------------------------+--------------------------+-----------+----------+----------------------------------------+
41 | data_origin_type | integer | | not null | 0 |
42 +------------------------------+--------------------------+-----------+----------+----------------------------------------+
43 | data_origin | integer | | not null | 0 |
44 +------------------------------+--------------------------+-----------+----------+----------------------------------------+
45 | sampling_height | double precision | | not null | |
46 +------------------------------+--------------------------+-----------+----------+----------------------------------------+
47 | additional_metadata | jsonb | | not null | |
48 +------------------------------+--------------------------+-----------+----------+----------------------------------------+
49 | provider_version | character varying(28) | | not null | 'NA' |
50 +------------------------------+--------------------------+-----------+----------+----------------------------------------+
51 | latest_version | character varying(28) | | not null | '000001.000000.00000000000000' |
52 +------------------------------+--------------------------+-----------+----------+----------------------------------------+
53 | data_license_accepted | timestamp with time zone | | not null | '1900-01-01' |
54 +------------------------------+--------------------------+-----------+----------+----------------------------------------+
55 | dataset_approved_by_provider | timestamp with time zone | | not null | '1900-01-01' |
56 +------------------------------+--------------------------+-----------+----------+----------------------------------------+
57 | station_id | integer | | not null | |
58 +------------------------------+--------------------------+-----------+----------+----------------------------------------+
59 | variable_id | integer | | not null | |
60 +------------------------------+--------------------------+-----------+----------+----------------------------------------+
61 | programme_id | integer | | not null | 0 |
62 +------------------------------+--------------------------+-----------+----------+----------------------------------------+
63 | doi | character varying(64) | | not null | '' |
64 +------------------------------+--------------------------+-----------+----------+----------------------------------------+
65 | coverage | double precision | | not null | -1.0 |
66 +------------------------------+--------------------------+-----------+----------+----------------------------------------+
68 Indexes:
69 "timeseries_pkey" PRIMARY KEY, btree (id)
70 "timeseries_station_id" btree (station_id)
71 "timeseries_variable_id" btree (variable_id)
72 Check constraints:
73 "timeseries_aggregation_check" CHECK (aggregation >= 0)
74 "timeseries_order_check" CHECK ("order" >= 0)
75 "timeseries_sampling_frequency_check" CHECK (sampling_frequency >= 0)
76 Foreign-key constraints:
77 "timeseries_station_id_fk_stationmeta_core_id" FOREIGN KEY (station_id) REFERENCES stationmeta_core(id) DEFERRABLE INITIALLY DEFERRED
78 "timeseries_variable_id_fk_variables_id" FOREIGN KEY (variable_id) REFERENCES variables(id) DEFERRABLE INITIALLY DEFERREDForeign-key constraints:
79 "timeseries_aggregation_fk_at_vocabulary_enum_val" FOREIGN KEY (aggregation) REFERENCES at_vocabulary(enum_val)
80 "timeseries_sampling_frequency_fk_sf_vocabulary_enum_val" FOREIGN KEY (sampling_frequency) REFERENCES sf_vocabulary(enum_val)
81 "timeseries_data_origin_fk_do_vocabulary_enum_val" FOREIGN KEY (data_origin) REFERENCES do_vocabulary(enum_val)
82 "timeseries_data_origin_type_fk_ot_vocabulary_enum_val" FOREIGN KEY (data_origin_type) REFERENCES ot_vocabulary(enum_val)
83 "timeseries_programme_id_fk_timeseries_programmes_id" FOREIGN KEY (programme_id) REFERENCES timeseries_programmes(id)
84 Referenced by:
85 TABLE "data" CONSTRAINT "data_timeseries_id_fk_timeseries_id" FOREIGN KEY (timeseries_id) REFERENCES timeseries(id) DEFERRABLE INITIALLY DEFERRED
86 TABLE "timeseries_timeseries_annotations" CONSTRAINT "timeseries_timeseries_annotations_timeseries_id_fk_timeseries_id" FOREIGN KEY (timeseries_id) REFERENCES timeseries(id) DEFERRABLE INITIALLY DEFERRED
87 TABLE "timeseries_timeseries_roles" CONSTRAINT "timeseries_timeseries_roles_timeseries_id_fk_timeseries_id" FOREIGN KEY (timeseries_id) REFERENCES timeseries(id) DEFERRABLE INITIALLY DEFERRED
88 TABLE "timeseries_timeseries_programmes" CONSTRAINT "timeseries_timeseries_programmes_timeseries_id_fk_timeseries_id" FOREIGN KEY (timeseries_id) REFERENCES timeseries(id) DEFERRABLE INITIALLY DEFERRED
89 """
91 __tablename__ = 'timeseries'
92 __table_args__ = (
93 CheckConstraint('"order" >= 0'),
94 CheckConstraint('aggregation >= 0'),
95 CheckConstraint('sampling_frequency >= 0'),
96 UniqueConstraint('station_id', 'variable_id', 'label'),
97 )
99 id = Column(Integer, TIMESERIES_ID_SEQ, primary_key=True, server_default=TIMESERIES_ID_SEQ.next_value())
100 label = Column(String(128), nullable=False)
101 order = Column(Integer, nullable=False)
102 sampling_frequency = Column(ForeignKey('sf_vocabulary.enum_val'), nullable=False)
103 aggregation = Column(ForeignKey('at_vocabulary.enum_val'), nullable=False)
104 data_start_date = Column(DateTime(True), nullable=False)
105 data_end_date = Column(DateTime(True), nullable=False)
106 data_origin_type = Column(ForeignKey('ot_vocabulary.enum_val'), nullable=False, server_default=text("0"))
107 data_origin = Column(ForeignKey('do_vocabulary.enum_val'), nullable=False, server_default=text("0"))
108 sampling_height = Column(Float(53), nullable=False)
109 provider_version = Column(String(28), nullable=False, server_default=text("'NA'"))
110 latest_version = Column(String(28), nullable=False, server_default=text("'000001.000000.00000000000000'"))
111 data_license_accepted = Column(DateTime(True), nullable=False, server_default=text("'1900-01-01 00:00:00+00'::timestamp with time zone"))
112 dataset_approved_by_provider = Column(DateTime(True), nullable=False, server_default=text("'1900-01-01 00:00:00+00'::timestamp with time zone"))
113# do not use string declaration here (not working for pytest)
114# use the explicit class name here,
115# see: https://groups.google.com/forum/#!topic/sqlalchemy/YjGhE4d6K4U
116 station_id = Column(ForeignKey(StationmetaCore.id, deferrable=True, initially='DEFERRED'), nullable=False, index=True)
117 variable_id = Column(ForeignKey(Variable.id, deferrable=True, initially='DEFERRED'), nullable=False, index=True)
118 programme_id = Column(ForeignKey(TimeseriesProgramme.id, deferrable=True, initially='DEFERRED'), nullable=False, server_default=text("0"))
119 doi = Column(String(64), nullable=False, server_default=text("'000001.000000.00000000000000'"))
120 coverage = Column(Float, nullable=False, server_default=text('-1.0'))
122 # for the nested view
123 # problems with station and coordinates in nested views!!!
124 station = relationship('StationmetaCore')
125 variable = relationship('Variable')
126 programme = relationship('TimeseriesProgramme')
127 changelog = relationship('TimeseriesChangelog', back_populates='timeseries')
129 additional_metadata = Column(JSONB(astext_type=Text()), nullable=True)