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 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 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 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 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 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 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)
active = db.Column(db.Boolean())
confirmed_at = db.Column(db.DateTime())
timezone = db.Column(db.String(25))
steamid = db.Column(db.String(17), unique=True)
roles = db.relationship("Role",
secondary=roles_users,
backref=db.backref("users", lazy="dynamic"))
def __repr__(self):
return "%s - %s" % (self.code, 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'))
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}>'
# Relationships on other tables
Role.groups = db.relationship('Group', backref=db.backref('role'))
User.groups = db.relationship('Group', backref=db.backref('user'))
##########################
### MARSHMALLOW SCHEMA ###
##########################
class GroupSchema(SQLAlchemyAutoSchema):
role = fields.Nested(RoleSchema(only=('name', )), dump_only=True)
user = fields.Nested(UserSchema(only=('email', )), dump_only=True)
class Meta:
model = Group
include_fk = True
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'))
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}>'
# Relationships on other tables
Meter.pv = relationship('Pv', backref=db.backref('meter'), uselist=False)
##########################
### MARSHMALLOW SCHEMA ###
##########################
class PvSchema(SQLAlchemyAutoSchema):
meter = fields.Nested(MeterSchema(), dump_only=True)
class Meta:
model = Pv
load_instance = True
include_fk = True
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'))
# Relationships on other tables
Address.service_location = relationship('ServiceLocation',
backref=db.backref('address'),
uselist=False)
##########################
### MARSHMALLOW SCHEMA ###
##########################
class ServiceLocationSchema(SQLAlchemyAutoSchema):
address = fields.Nested(AddressSchema(), dump_only=True)
class Meta:
model = ServiceLocation
load_instance = True
include_fk = True