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 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 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}>'
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 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 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 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 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 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 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 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 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)
from flask_security import RoleMixin, UserMixin from web.database import db roles_users = db.Table( "roles_users", db.Column("user_id", db.Integer(), db.ForeignKey("web_users.id")), db.Column("role_id", db.Integer(), db.ForeignKey("web_roles.id")), ) class Role(db.Model, RoleMixin): __tablename__ = "web_roles" id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String(80), unique=True) description = db.Column(db.String(255)) def __repr__(self): return "%s - %s" % (self.name, self.description) class User(db.Model, UserMixin): __tablename__ = "web_users" id = db.Column(db.Integer, primary_key=True) email = db.Column(db.String(255), unique=True) password = db.Column(db.String(255)) last_login_at = db.Column(db.DateTime()) current_login_at = db.Column(db.DateTime()) last_login_ip = db.Column(db.String(100)) current_login_ip = db.Column(db.String(100)) login_count = db.Column(db.Integer)
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'))