models.py

# coding: utf-8
from sqlalchemy import PrimaryKeyConstraint, Column, DateTime, Float, ForeignKey, Integer, text, \
                       Table, String
from sqlalchemy.orm import relationship
from sqlalchemy.sql.sqltypes import NullType
from sqlalchemy.dialects.postgresql import JSONB

from toardb.timeseries.models import Timeseries
from toardb.base import Base


class Data(Base):
    """                         Table "public.data"  

    +---------------+--------------------------+-----------+----------+---------+
    |Column         |Type                      |Collation  |Nullable  |Default  |
    +===============+==========================+===========+==========+=========+
    | datetime      | timestamp with time zone |           | not null |         |
    +---------------+--------------------------+-----------+----------+---------+  
    | value         | double precision         |           | not null |         |
    +---------------+--------------------------+-----------+----------+---------+ 
    | flags         | integer                  |           | not null |         |
    +---------------+--------------------------+-----------+----------+---------+ 
    | timeseries_id | integer                  |           | not null |         |
    +---------------+--------------------------+-----------+----------+---------+ 

    Indexes:  
     "data_pkey" PRIMARY KEY, btree (timeseries_id, datetime)  
     "data_datetime_idx" btree (datetime)  
     "data_timeseries_id_idx" btree (timeseries_id)  
     "data_value_idx" btree (value)  
    Check constraints:  
     "data_flags_check" CHECK (flags >= 0)  
    Foreign-key constraints:
     "data_flags_fk_df_vocabulary_enum_val" FOREIGN KEY (flags) REFERENCES df_vocabulary(enum_val)
     "data_timeseries_id_a38c5a1a_fk_timeseries_id" FOREIGN KEY (timeseries_id) REFERENCES timeseries(id) DEFERRABLE INITIALLY DEFERRED
    """
    
    __tablename__ = 'data'
    __table_args__ = (
                        PrimaryKeyConstraint('timeseries_id', 'datetime'),
                     )

    datetime = Column(DateTime(True), nullable=False, index=True)
    value = Column(Float(53), nullable=False, index=True)
    flags = Column(ForeignKey('df_vocabulary.enum_val'), 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
    timeseries_id = Column(ForeignKey(Timeseries.id, deferrable=True, initially='DEFERRED'), nullable=False, index=True)

# controlled vocabulary

# Data Access Rights
DF_enum_table = Table("df_vocabulary",
                      Base.metadata,
                      Column("enum_val", Integer, primary_key=True),
                      Column("enum_str", String),
                      Column("enum_display_str", String)
                      )
# The following code is just a workaround (see stationmeta/models.py):
from collections import namedtuple
Enumdict=namedtuple("Dict",["value","string","display_str"])
DF_enum = (
    Enumdict( 0,'OK', 'OK'),
    Enumdict( 1,'OKPreliminary', 'OK preliminary'),
    Enumdict( 2,'OKModified', 'OK modified'),
    Enumdict( 3,'OKPreliminaryModified', 'OK preliminary modified'),
    Enumdict( 4,'Inconsistent', 'inconsistent'),
    Enumdict( 5,'InconsistentPreliminary', 'inconsistent preliminary'),
    Enumdict( 6,'Doubtful', 'doubtful'),
    Enumdict( 7,'DoubtfulPreliminary', 'doubtful preliminary'),
    Enumdict( 8,'DoubtfulModified', 'doubtful modified'),
    Enumdict( 9,'DoubtfulPreliminaryModified', 'doubtful preliminary modified'),
    Enumdict(10,'Wrong', 'wrong'),
    Enumdict(11,'WrongPreliminary', 'wrong preliminary'),
    Enumdict(12,'NotCheckedPreliminary', 'not checked preliminary'),
    Enumdict(13,'Changed', 'changed'),
    Enumdict(14,'Estimated', 'estimated'),
    Enumdict(15,'MissingValue', 'missing value')
    )