# coding: utf-8 """ class Timeseries (Base) ======================= """ from sqlalchemy import Column, DateTime, Float, ForeignKey, Integer, text, String, \ Text, CheckConstraint, UniqueConstraint, PrimaryKeyConstraint, \ Table from sqlalchemy.orm import relationship from sqlalchemy.dialects.postgresql import JSONB from sqlalchemy.ext.declarative import declarative_base from toardb.stationmeta.models import StationmetaCore from toardb.variables.models import Variable from toardb.contacts.models import Person Base = declarative_base() metadata = Base.metadata # many-to-many relationships timeseries_timeseries_roles_table = Table('timeseries_timeseries_roles', Base.metadata, Column('timeseries_id', Integer, ForeignKey('timeseries.id')), Column('role_id', Integer, ForeignKey('timeseries_roles.id')) ) class TimeseriesRole(Base): """ Table "public.timeseries_roles" +---------------+---------+-----------+----------+----------------------------------------------+ | Column | Type | Collation | Nullable | Default | +===============+=========+===========+==========+==============================================+ | id | integer | | not null | nextval('timeseries_roles_id_seq'::regclass) | +---------------+---------+-----------+----------+----------------------------------------------+ | role | integer | | not null | | +---------------+---------+-----------+----------+----------------------------------------------+ | status | integer | | not null | | +---------------+---------+-----------+----------+----------------------------------------------+ | person_id | integer | | not null | | +---------------+---------+-----------+----------+----------------------------------------------+ Indexes: "timeseries_roles_pkey" PRIMARY KEY, btree (id) "timeseries_roles_role_person_id_uniq" UNIQUE CONSTRAINT, btree (role, person_id) "timeseries_roles_person_id" btree (person_id) Check constraints: "timeseries_roles_role_check" CHECK (role >= 0) "timeseries_roles_status_check" CHECK (status >= 0) Foreign-key constraints: "timeseries_roles_person_id_3e26200e_fk_persons_id" FOREIGN KEY (person_id) REFERENCES persons(id) DEFERRABLE INITIALLY DEFERRED """ __tablename__ = 'timeseries_roles' __table_args__ = ( CheckConstraint('role >= 0'), CheckConstraint('status >= 0'), UniqueConstraint('role', 'person_id'), {'extend_existing': True} ) id = Column(Integer, primary_key=True, server_default=text("nextval('timeseries_roles_id_seq'::regclass)")) role = Column(Integer, nullable=False) status = Column(Integer, nullable=False) # do not use string declaration here (not working for pytest) # use the explicit class name here, # see: https://groups.google.com/forum/#!topic/sqlalchemy/YjGhE4d6K4U person_id = Column(ForeignKey(Person.id, deferrable=True, initially='DEFERRED'), nullable=False, index=True) person = relationship(Person) timeseries = relationship("Timeseries", secondary=timeseries_timeseries_roles_table, backref="roles") class Timeseries(Base): """ Table "public.timeseries" +---------------------+--------------------------+-----------+----------+----------------------------------------+ | Column | Type | Collation | Nullable | Default | +=====================+==========================+===========+==========+========================================+ | id | integer | | not null | nextval('timeseries_id_seq'::regclass) | +---------------------+--------------------------+-----------+----------+----------------------------------------+ | label | character varying(128) | | not null | | +---------------------+--------------------------+-----------+----------+----------------------------------------+ | order | integer | | not null | | +---------------------+--------------------------+-----------+----------+----------------------------------------+ | access_rights | integer | | not null | | +---------------------+--------------------------+-----------+----------+----------------------------------------+ | sampling_frequency | integer | | not null | | +---------------------+--------------------------+-----------+----------+----------------------------------------+ | aggregation | integer | | not null | | +---------------------+--------------------------+-----------+----------+----------------------------------------+ | data_start_date | timestamp with time zone | | not null | | +---------------------+--------------------------+-----------+----------+----------------------------------------+ | data_end_date | timestamp with time zone | | not null | | +---------------------+--------------------------+-----------+----------+----------------------------------------+ | measurement_method | character varying(128) | | not null | | +---------------------+--------------------------+-----------+----------+----------------------------------------+ | sampling_height | double precision | | not null | | +---------------------+--------------------------+-----------+----------+----------------------------------------+ | additional_metadata | jsonb | | not null | | +---------------------+--------------------------+-----------+----------+----------------------------------------+ | date_added | timestamp with time zone | | not null | | +---------------------+--------------------------+-----------+----------+----------------------------------------+ | date_modified | timestamp with time zone | | not null | | +---------------------+--------------------------+-----------+----------+----------------------------------------+ | station_id | integer | | | | +---------------------+--------------------------+-----------+----------+----------------------------------------+ | variable_id | integer | | | | +---------------------+--------------------------+-----------+----------+----------------------------------------+ Indexes: "timeseries_pkey" PRIMARY KEY, btree (id) "timeseries_station_id_variable_id_label_uniq" UNIQUE CONSTRAINT, btree (station_id, variable_id, label) "timeseries_station_id" btree (station_id) "timeseries_variable_id" btree (variable_id) Check constraints: "timeseries_access_rights_check" CHECK (access_rights >= 0) "timeseries_aggregation_check" CHECK (aggregation >= 0) "timeseries_order_check" CHECK ("order" >= 0) "timeseries_sampling_frequency_check" CHECK (sampling_frequency >= 0) Foreign-key constraints: "timeseries_station_id_fk_stationmeta_core_id" FOREIGN KEY (station_id) REFERENCES stationmeta_core(id) DEFERRABLE INITIALLY DEFERRED "timeseries_variable_id_fk_variables_id" FOREIGN KEY (variable_id) REFERENCES variables(id) DEFERRABLE INITIALLY DEFERRED Referenced by: TABLE "data" CONSTRAINT "data_timeseries_id_fk_timeseries_id" FOREIGN KEY (timeseries_id) REFERENCES timeseries(id) DEFERRABLE INITIALLY DEFERRED TABLE "timeseries_timeseries_annotations" CONSTRAINT "timeseries_timeseries_annotations_timeseries_id_fk_timeseries_id" FOREIGN KEY (timeseries_id) REFERENCES timeseries(id) DEFERRABLE INITIALLY DEFERRED TABLE "timeseries_timeseries_roles" CONSTRAINT "timeseries_timeseries_roles_timeseries_id_fk_timeseries_id" FOREIGN KEY (timeseries_id) REFERENCES timeseries(id) DEFERRABLE INITIALLY DEFERRED TABLE "timeseries_timeseries_programmes" CONSTRAINT "timeseries_timeseries_programmes_timeseries_id_fk_timeseries_id" FOREIGN KEY (timeseries_id) REFERENCES timeseries(id) DEFERRABLE INITIALLY DEFERRED """ __tablename__ = 'timeseries' __table_args__ = ( CheckConstraint('"order" >= 0'), CheckConstraint('access_rights >= 0'), CheckConstraint('aggregation >= 0'), CheckConstraint('sampling_frequency >= 0'), UniqueConstraint('station_id', 'variable_id', 'label'), ) id = Column(Integer, primary_key=True, server_default=text("nextval('timeseries_id_seq'::regclass)")) label = Column(String(128), nullable=False) order = Column(Integer, nullable=False) access_rights = Column(Integer, nullable=False) sampling_frequency = Column(Integer, nullable=False) aggregation = Column(Integer, nullable=False) data_start_date = Column(DateTime(True), nullable=False) data_end_date = Column(DateTime(True), nullable=False) measurement_method = Column(String(128), nullable=False) sampling_height = Column(Float(53), nullable=False) additional_metadata = Column(JSONB(astext_type=Text()), nullable=True) # date_added, date_modified: # how to have them changed automatically date_added = Column(DateTime(True), nullable=False) date_modified = Column(DateTime(True), nullable=False) # do not use string declaration here (not working for pytest) # use the explicit class name here, # see: https://groups.google.com/forum/#!topic/sqlalchemy/YjGhE4d6K4U station_id = Column(ForeignKey(StationmetaCore.id, deferrable=True, initially='DEFERRED'), index=True) variable_id = Column(ForeignKey(Variable.id, deferrable=True, initially='DEFERRED'), index=True) # how to reactivate the following two lines?! # station = relationship('StationmetaCore') # variable = relationship('Variable')