class Address(Model): __tablename__ = 'addresses' address_id = Column(db.Integer, autoincrement=True, primary_key=True, nullable=False) address = Column(db.String(100), nullable=False) address2 = Column(db.String(64)) district = Column(db.String(64)) city = Column(db.String(100), nullable=False) country = Column(db.String(100), nullable=False) postal_code = Column(db.String(64), nullable=False) phone = Column(db.String(64)) updated_at = Column( TIMESTAMP, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')) created_at = Column(TIMESTAMP, server_default=func.now()) # Methods def __repr__(self): return f'<Address address_id={self.address_id} address={self.address} postal_code={self.postal_code}>' # Relationships on other tables users = relationship('User', backref=db.backref('address'))
class EventingDevice(Model): __tablename__ = 'eventing_devices' ed_id = Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) device_id = Column(db.String(64), unique=True)
class DeviceEventSource(Model): __tablename__ = 'device_events' des_id = Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) event_data = Column(db.JSON) created_at = Column(TIMESTAMP, server_default=func.now()) ed_id = Column(db.Integer, db.ForeignKey(EventingDevice.ed_id), unique=False, nullable=False)
class Role(Model): __tablename__ = 'roles' role_id = Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) name = Column(db.Enum(RoleType), unique=True, nullable=False) # Methods def __repr__(self): return f'<Role role_id={self.role_id} name={self.name}>'
class Transformer(Model): __tablename__ = 'transformers' transformer_id = Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) feeder = Column(db.String(45), nullable=False) capacity = Column(db.Integer, nullable=False) updated_at = Column( TIMESTAMP, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')) created_at = Column(TIMESTAMP, server_default=func.now())
class TransformerInterval(Model): __tablename__ = 'transformer_intervals' transformer_interval_id = Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) transformer_id = Column(db.Integer, db.ForeignKey('transformers.transformer_id'), nullable=False) import_capacity = Column(db.Float, nullable=False) export_capacity = Column(db.Float, nullable=False) q = Column(db.Float, nullable=False) unresp_load = Column(db.Float, nullable=False) start_time = Column(TIMESTAMP, nullable=False) end_time = Column(TIMESTAMP, nullable=False) # Methods def __repr__(self): return f'<TransformerInterval transformer_interval_id={self.transformer_interval_id} transformer_id={self.transformer_id}>' # Relationships transformer = relationship('Transformer', backref=db.backref('transformer_intervals'))
class Pv(Model): __tablename__ = 'pvs' pv_id = Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) home_hub_id = Column(db.Integer, db.ForeignKey('home_hubs.home_hub_id'), unique=True, nullable=False) meter_id = Column(db.Integer, db.ForeignKey('meters.meter_id'), unique=True, nullable=False) q_rated = Column(db.Float, nullable=False) is_active = Column(db.Boolean(), default=False, nullable=False) is_archived = Column(db.Boolean(), default=False, nullable=False) updated_at = Column( TIMESTAMP, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')) created_at = Column(TIMESTAMP, server_default=func.now()) def __repr__(self): return f'<Pv pv_id={self.pv_id} home_hub_id={self.home_hub_id} created_at={self.created_at}>'
class Market(Model): __tablename__ = 'markets' market_id = Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) source = Column(db.Text, nullable=False) ts = Column(db.Float, nullable=False) p_max = Column(db.Float, nullable=False) is_active = Column(db.Boolean, default=False, nullable=False) is_archived = Column(db.Boolean, default=False, nullable=False) updated_at = Column( TIMESTAMP, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')) created_at = Column(TIMESTAMP, server_default=func.now()) # Methods def __repr__(self): return f'<Market market_id={self.market_id} p_max={self.p_max} created_at={self.created_at}>' # Relationships home_hubs = relationship('HomeHub', backref=db.backref('market'))
class Rate(Model): __tablename__ = 'rates' rate_id = Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) description = Column(db.Text, nullable=False) # Methods def __repr__(self): return f'<Rate rate_id={self.rate_id} description={self.description}>' # Relationships meter_intervals = relationship('MeterInterval', backref=db.backref('rate'))
class HomeHub(Model): __tablename__ = 'home_hubs' home_hub_id = Column(db.Integer, autoincrement=True, primary_key=True, nullable=False) service_location_id = Column( db.Integer, db.ForeignKey('service_locations.service_location_id'), unique=True, nullable=False) market_id = Column(db.Integer, db.ForeignKey('markets.market_id'), nullable=False) is_active = Column(db.Boolean(), default=False, nullable=False) is_archived = Column(db.Boolean(), default=False, nullable=False) updated_at = Column( TIMESTAMP, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')) created_at = Column(TIMESTAMP, server_default=func.now()) # Methods def __repr__(self): return f'<HomeHub home_hub_id={self.home_hub_id} service_location_id={self.service_location_id} created_at={self.created_at}>' # Relationships pv = relationship('Pv', backref=db.backref('home_hub'), uselist=False)
class Channel(Model): __tablename__ = 'channels' channel_id = Column(db.Integer, autoincrement=True, primary_key=True, nullable=False) meter_id = Column(db.Integer, db.ForeignKey('meters.meter_id'), nullable=False) setting = Column(db.Integer, nullable=False) channel_type = Column(db.Enum(ChannelType), nullable=False) # Methods def __repr__(self): return f'<Channel channel_id={self.channel_id} meter_id={self.meter_id} channel_type={self.channel_type}>'
class Login(UserMixin, Model): __tablename__ = 'logins' login_id = Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) user_id = Column(db.Integer, db.ForeignKey('users.id'), unique=True, nullable=False) username = Column(db.String(50), unique=True, nullable=False) password_hash = db.Column(db.String(128)) updated_at = Column( TIMESTAMP, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')) created_at = Column(TIMESTAMP, server_default=func.now()) # Methods def __repr__(self): return f'<Login login_id={self.login_id} user_id={self.user_id} updated_at={self.updated_at}>' @property def password(self): raise AttributeError('Password is not a readable attribute.') @password.setter def set_password(self, password): self.password_hash = generate_password_hash(password) def check_password(self, password): is_correct_password = check_password_hash(self.password_hash, password) if not is_correct_password: raise ValueError('Incorrect Password') return is_correct_password
class HceBids(Model): __tablename__ = 'hce_bids' bid_id = Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) start_time = Column(TIMESTAMP, nullable=False) end_time = Column(TIMESTAMP, nullable=False) p_bid = Column(db.Float, default=0, nullable=False) q_bid = Column(db.Float, default=0, nullable=False) is_supply = Column(db.Boolean(), default=True, nullable=False) comment = Column(db.String(512), nullable=False) market_id = Column(db.Integer, db.ForeignKey('markets.market_id'), nullable=False) updated_at = Column( TIMESTAMP, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')) created_at = Column(TIMESTAMP, server_default=func.now()) def __repr__(self): return f'<HceBids bid_id={self.bid_id}>'
class Role(SurrogatePK, Model): """A role for a user.""" __tablename__ = 'roles' name = Column(db.String(80), unique=True, nullable=False) user_id = reference_col('users', nullable=True) user = relationship('User', backref='roles') def __init__(self, name, **kwargs): """Create instance.""" db.Model.__init__(self, name=name, **kwargs) def __repr__(self): """Represent instance as a unique string.""" return '<Role({name})>'.format(name=self.name)
class Notification(Model): __tablename__ = 'notifications' notification_id = Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) alert_type_id = Column(db.Integer, db.ForeignKey('alert_types.alert_type_id'), nullable=False) email = Column(db.String(255), nullable=False) is_active = Column(db.Boolean, default=False, nullable=False) created_by = Column(db.Integer, db.ForeignKey('users.id'), nullable=False) updated_at = Column( TIMESTAMP, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')) created_at = Column(TIMESTAMP, server_default=func.now()) # Unique constraint for alert_type_id and email __table_args__ = (UniqueConstraint('alert_type_id', 'email', name='_alert_type_id_email_uc'), ) # Methods @validates('email') def empty_string_to_null(self, key, value): # converts empty string to null as field is not nullable # throws an exeption to keep the db from being populated with empty strings # email validation is already done on the react side for certain pages # this is an extra layer preventing unwanted data value = str(value).strip() if value == '': return None else: return value def __repr__(self): return f'<Notification notification_id={self.notification_id} alert_type_id={self.alert_type_id} is_active={self.is_active}>'
class User(UserMixin, SurrogatePK, Model): """A user of the app.""" __tablename__ = 'users' username = Column(db.String(80), unique=True, nullable=False) email = Column(db.String(80), unique=True, nullable=False) #: The hashed password password = Column(db.Binary(128), nullable=True) created_at = Column(db.DateTime, nullable=False, default=dt.datetime.utcnow) first_name = Column(db.String(30), nullable=True) last_name = Column(db.String(30), nullable=True) active = Column(db.Boolean(), default=False) is_admin = Column(db.Boolean(), default=False) def __init__(self, username, email, password=None, **kwargs): """Create instance.""" db.Model.__init__(self, username=username, email=email, **kwargs) if password: self.set_password(password) else: self.password = None def set_password(self, password): """Set password.""" self.password = bcrypt.generate_password_hash(password) def check_password(self, value): """Check password.""" return bcrypt.check_password_hash(self.password, value) @property def full_name(self): """Full user name.""" return '{0} {1}'.format(self.first_name, self.last_name) def __repr__(self): """Represent instance as a unique string.""" return '<User({username!r})>'.format(username=self.username)
class ServiceLocation(Model): __tablename__ = 'service_locations' service_location_id = Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) alternate_service_location_id = Column(db.String(64), unique=True) address_id = Column(db.Integer, db.ForeignKey('addresses.address_id'), unique=True, nullable=False) map_location = Column(db.String(64), nullable=False) is_active = Column(db.Boolean(), default=False, nullable=False) is_archived = Column(db.Boolean(), default=False, nullable=False) updated_at = Column( TIMESTAMP, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')) created_at = Column(TIMESTAMP, server_default=func.now()) def __repr__(self): return f'<ServiceLocation service_location_id={self.service_location_id} address_id={self.address_id}>' # Relationships home_hub = relationship('HomeHub', backref=db.backref('service_location'), uselist=False) meters = relationship('Meter', backref=db.backref('service_location'))
class Group(Model): __tablename__ = 'groups' group_id = Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) role_id = Column(db.Integer, db.ForeignKey('roles.role_id'), primary_key=True, nullable=False) user_id = Column(db.Integer, db.ForeignKey('users.id'), primary_key=True, nullable=False) is_active = Column(db.Boolean(), default=False, nullable=False) is_archived = Column(db.Boolean(), default=False, nullable=False) updated_at = Column( TIMESTAMP, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')) created_at = Column(TIMESTAMP, server_default=func.now()) # Unique constraint for role_id and user_id __table_args__ = (UniqueConstraint('role_id', 'user_id', name='_role_user_uc'), ) # Methods def __repr__(self): return f'<Group group_id={self.group_id} role_id={self.role_id} user_id={self.user_id}>'
class AlertType(Model): __tablename__ = 'alert_types' alert_type_id = Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) utility_id = Column(db.Integer, db.ForeignKey('utilities.utility_id'), nullable=False) name = Column(db.Enum(AlertName), nullable=False) limit = Column(db.Float, nullable=False) updated_at = Column( TIMESTAMP, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')) created_at = Column(TIMESTAMP, server_default=func.now()) # Unique constraint for utility_id and name __table_args__ = (UniqueConstraint('utility_id', 'name', name='_utility_name_uc'), ) # Relationships notifications = relationship('Notification', backref=db.backref('alert_type')) alerts = relationship('Alert', backref=db.backref('alert_type')) # Methods def __repr__(self): return f'<AlertType alert_type_id={self.alert_type_id} utility_id={self.utility_id} name={self.name}>'
class Meter(Model): __tablename__ = 'meters' # Composite primary key: meter_id, utility_id, and service_location_id meter_id = Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) utility_id = Column(db.Integer, db.ForeignKey('utilities.utility_id'), primary_key=True, nullable=False) service_location_id = Column( db.Integer, db.ForeignKey('service_locations.service_location_id'), primary_key=True, nullable=False) home_hub_id = Column(db.Integer, db.ForeignKey('home_hubs.home_hub_id'), nullable=False) transformer_id = Column(db.Integer, db.ForeignKey('transformers.transformer_id'), nullable=True) alternate_meter_id = Column(db.String(64), unique=True) feeder = Column(db.String(45), nullable=False) substation = Column(db.String(45), nullable=False) meter_type = Column(db.Enum(MeterType), nullable=False) is_active = Column(db.Boolean(), default=False, nullable=False) is_archived = Column(db.Boolean(), default=False, nullable=False) updated_at = Column( TIMESTAMP, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')) created_at = Column(TIMESTAMP, server_default=func.now()) # Methods def get_interval_count(self, start, end): '''Takes in start and end ISO8601 times, returns the interval count (integer) between start / end times, inclusively''' self_intervals = self.meter_intervals selected_intervals = [] if start == None: start = datetime.now() - timedelta(days=1) if end == None: end = datetime.now() for meter_interval in self_intervals: if meter_interval.start_time >= start and meter_interval.end_time <= end: selected_intervals.append(meter_interval) return len(selected_intervals) def get_rates(self): '''Returns meter instance's rates as a list''' rates = [] for meter_interval in self.meter_intervals: if meter_interval.rate.description not in rates: rates.append(meter_interval.rate.description) return rates def get_channels(self): '''Returns meter instance's channel settings as a list''' channels = Meter.query.\ join(Meter.channels). \ all() return channels def get_all_intervals(self): '''Returns all meter instances's intervals in a list''' intervals_list = [] for meter_interval in self.meter_intervals: intervals_list.append(meter_interval.meter_interval_id) return intervals_list def __repr__(self): return f'<Meter meter_id={self.meter_id} is_active={self.is_active}>' # Relationships channels = relationship('Channel', backref=db.backref('meter')) meter_intervals = relationship('MeterInterval', backref=db.backref('meter'))
class User(UserMixin, Model): __tablename__ = 'users' id = Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) # User email information email = Column(db.String(255), unique=True, nullable=False) email_confirmed_at = Column(TIMESTAMP) # User information first_name = Column(db.String(64), nullable=False) last_name = Column(db.String(64), nullable=False) address_id = Column(db.Integer, db.ForeignKey('addresses.address_id'), nullable=False) utility_id = Column(db.Integer, db.ForeignKey('utilities.utility_id'), nullable=False) is_active = Column(db.Boolean(), default=False, nullable=False) is_archived = Column(db.Boolean(), default=False, nullable=False) updated_at = Column( TIMESTAMP, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')) created_at = Column(TIMESTAMP, server_default=func.now()) # Methods def get_roles(self): '''Returns list of user role objects''' roles = [] for group in self.groups: roles.append(group.role) return roles def does_user_role_exist(self, role_name): '''Returns true or false whether user has assigned role''' for group in self.groups: if group.role.name.value == role_name: return True return False def __repr__(self): return f'<User id={self.id} email_id={self.email}>' # Relationships login = relationship('Login', backref=db.backref('user'), uselist=False) notifications = relationship('Notification', backref=db.backref('user')) alerts = relationship('Alert', backref=db.backref('user'))
class Alert(Model): __tablename__ = 'alerts' alert_id = Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) alert_type_id = Column(db.Integer, db.ForeignKey('alert_types.alert_type_id'), nullable=False) assigned_to = Column(db.String(255), db.ForeignKey('users.email'), nullable=False) description = Column(db.Text, nullable=False) status = Column(db.Enum(Status), nullable=False) context = Column(db.Enum(ContextType), nullable=False) context_id = Column(db.String(64), nullable=False) resolution = Column(db.Text, nullable=False) updated_at = Column( TIMESTAMP, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')) created_at = Column(TIMESTAMP, server_default=func.now()) # Methods def __repr__(self): return f'<Alert alert_id={self.alert_id} alert_type_id={self.alert_type_id} created_at={self.created_at}>' def get_filter_type(self): '''Returns filter type as a string for alert frontend set-up''' if self.alert_type.name.value == 'Import Capacity' \ or self.alert_type.name.value == 'Export Capacity' \ or self.alert_type.name.value == 'Load Yellow' \ or self.alert_type.name.value == 'Load Red': return 'Capacity bounds' if self.alert_type.name.value == 'Price Yellow' \ or self.alert_type.name.value == 'Price Red' \ or self.alert_type.name.value == 'Price': return 'Price alerts' if self.alert_type.name.value == 'Telecomm': return 'Telecomm alerts' if self.alert_type.name.value == 'Resource': return 'Resource depletion' if self.alert_type.name.value == 'Peak Event': return 'Peak event' def create_alert_notification_message(self): '''Returns tuple of alert type name and notification message for alert''' # Create appropriate notification message if self.alert_type.name.value == 'Resource': message = 'TESS is showing "battery" resource depleted.' elif self.alert_type.name.value == 'Telecomm': message = 'TESS is observing a telecomm alert.' elif self.alert_type.name.value == 'Price': message = f'TESS is observing a price alert at ${self.alert_type.limit}/MW.' elif self.alert_type.name.value == 'Load Yellow' \ or self.alert_type.name.value == 'Load Red': message = f'TESS {self.alert_type.name.value} alert: {self.context.value} {self.context_id} is above {self.alert_type.limit}% capacity at {self.created_at}.' elif self.alert_type.name.value == 'Price Yellow' \ or self.alert_type.name.value == 'Price Red': message = f'TESS {self.alert_type.name.value} alert: {self.context.value} {self.context_id} is above {self.alert_type.limit}% of the alert price at {self.created_at}.' elif self.alert_type.name.value == 'Import Capacity': message = f'TESS System alert: {self.context.value} {self.context_id} is above {self.alert_type.limit} kW import capacity at {self.created_at}.' elif self.alert_type.name.value == 'Export Capacity': message = f'TESS System alert: {self.context.value} {self.context_id} is above {self.alert_type.limit} kW export capacity at {self.created_at}.' return (self.alert_type.name.value, message)
class MeterInterval(Model): __tablename__ = 'meter_intervals' meter_interval_id = Column(db.Integer, autoincrement=True, primary_key=True, nullable=False) meter_id = Column(db.Integer, db.ForeignKey('meters.meter_id'), nullable=False) rate_id = Column(db.Integer, db.ForeignKey('rates.rate_id'), nullable=False) start_time = Column(TIMESTAMP, nullable=False) end_time = Column(TIMESTAMP, nullable=False) e = Column(db.Float, nullable=False) qmtp = Column(db.Float, nullable=False) p_bid = Column(db.Float, default=0, nullable=False) q_bid = Column(db.Float, default=0, nullable=False) mode = Column(db.Boolean(create_constraint=True), default=0, nullable=False) is_bid = Column(db.Boolean(), default=False, nullable=False) @staticmethod def get_interval_coverage(interval_id_list): '''Takes in list of meter interval ids, returns list of tuples for start and end times in ISO8601 format''' selected_intervals = MeterInterval.query.filter( MeterInterval.meter_interval_id.in_(interval_id_list)).all() start_end_tuples_list = [] for meter_interval in selected_intervals: start_end_tuples_list.append( (meter_interval.start_time, meter_interval.end_time)) return start_end_tuples_list # Methods def __repr__(self): return f'<MeterInterval meter_interval_id={self.meter_interval_id} meter_id={self.meter_id} end_time={self.end_time} e={self.e}>'