class StoreComponent(db.Model):
"""
Maps subclass of declarative_base() to a Python class
to table store_components
"""
__tablename__ = 'store_components'
id = db.Column(db.Integer)
# MAJOR TODO: need to retrieve correct store_id
store_id = db.Column(db.Integer, nullable=False)
# TODO: move component_type data to its own tables
component_type = db.Column(db.String, nullable=False)
hostname = db.Column(db.String, nullable=False) # unique
# MAJOR TODO: use conversion functions or the postgresql datatype for ip
ip_address = db.Column(db.String) # unique
created_date = db.Column(db.DateTime(timezone=True),
server_default=func.now())
updated_date = db.Column(db.DateTime(timezone=True), onupdate=func.now())
# __table_args__ value must be a tuple, dict, or None
__table_args__ = (
db.PrimaryKeyConstraint('id', name='pk_store_components'),
# 1:M - one store can have many components
db.ForeignKeyConstraint(['store_id'], ['stores.id']),
db.UniqueConstraint('store_id',
'hostname',
name='uq_store_components_1'))
def get_all_by_type(store_id, component_type):
""" Returns store components based on store_id and component_type """
components = StoreComponent.query.filter_by(
store_id=store_id, component_type=component_type).all()
return components
def to_dict(self):
""" Convert the model to a dictionary that can go into a JSON """
return {
# id is unnecessary
# MAJOR TODO: rewrite store_id to something readable
'store_id': self.store_id,
'component_type': self.component_type,
'hostname': self.hostname,
'ip_address': self.ip_address,
'created_date': self.created_date,
'updated_date': self.updated_date
}
class Store(db.Model):
"""
Maps subclass of declarative_base() to a Python class
to table stores
"""
__tablename__ = 'stores'
id = db.Column(db.Integer)
user_id = db.Column(db.Integer, nullable=False)
country_code = db.Column(db.String(2), nullable=False)
dc_id = db.Column(db.Integer, nullable=False)
number = db.Column(db.Integer, nullable=False)
name = db.Column(db.String, nullable=False)
status_id = db.Column(db.Integer, nullable=False)
# TODO: move address data to its own tables
street_number = db.Column(db.String)
street_name = db.Column(db.String)
postal_code = db.Column(db.String)
city = db.Column(db.String)
created_date = db.Column(db.DateTime(timezone=True),
server_default=func.now())
updated_date = db.Column(db.DateTime(timezone=True), onupdate=func.now())
# __table_args__ value must be a tuple, dict, or None
__table_args__ = (
db.PrimaryKeyConstraint('id', name='pk_stores'),
db.ForeignKeyConstraint(['user_id'], ['users.id']),
db.ForeignKeyConstraint(['country_code'], ['countries.country_code']),
db.ForeignKeyConstraint(['dc_id'], ['distribution_centers.id']),
db.ForeignKeyConstraint(['status_id'], ['store_status.id']),
# db.ForeignKeyConstraint(['id'], ['distribution_centers.id']),
db.UniqueConstraint('country_code', 'number', name='uq_stores_1'))
def get_id(country_code, number):
""" Returns id (pk) based on country_code and number """
# We need to find the store_id first to link the components
# In table stores we can use the unique country_code, number
store = Store.query.filter_by(country_code=country_code,
number=number).first()
return store.id
def get(country_code, number):
""" Returns a store by country_code, number """
return Store.query.filter_by(country_code=country_code,
number=number).first()
def get_user_id(country_code, number):
""" Returns Store.user_id by country_code, number """
store = Store.query.filter_by(country_code=country_code,
number=number).first()
return store.user_id
def get_dc_id(country_code, number):
""" Returns Store.dc_id by country_code, number """
store = Store.query.filter_by(country_code=country_code,
number=number).first()
return store.dc_id
def get_status_id(country_code, number):
""" Returns Store.status_id by country_code, number """
store = Store.query.filter_by(country_code=country_code,
number=number).first()
return store.status_id
def get_all():
"""
Returns all stores ordered by country_code, number.
"""
return Store.query.order_by('country_code', 'number').all()
def to_dict(self):
"""
Convert the model to a dictionary that can go into a JSON.
TODO: it should return a non pk for dc, status
"""
return {
# id is unnecessary
'country_code': self.country_code,
#
'dc_id': self.dc_id,
'number': self.number,
'name': self.name,
'status_id': self.status_id,
'street_name': self.street_name,
'street_number': self.street_number,
'postal_code': self.postal_code,
'city': self.city,
'created_date': self.created_date,
'updated_date': self.updated_date
}
class DistributionCenter(db.Model):
"""
Maps subclass of declarative_base() to a Python class
to table distribution_centers
"""
__tablename__ = 'distribution_centers'
id = db.Column(db.Integer)
country_code = db.Column(db.String(2), nullable=False) # unique
number = db.Column(db.Integer, nullable=False) # unique
name = db.Column(db.String, nullable=False) # unique
tag = db.Column(db.String)
created_date = db.Column(db.DateTime(timezone=True),
server_default=func.now())
updated_date = db.Column(db.DateTime(timezone=True), onupdate=func.now())
stores = db.relationship('Store', backref='dc', lazy='dynamic')
# __table_args__ value must be a tuple, dict, or None
__table_args__ = (db.PrimaryKeyConstraint('id',
name='pk_distribution_centers'),
db.ForeignKeyConstraint(['country_code'],
['countries.country_code']),
db.UniqueConstraint('country_code',
'number',
name='uq_distribution_centers_1'))
def get_id(country_code, number):
"""
Returns id (pk) based on country_code and number.
Some countries (like LU) have no DC, it needs to be handled in imports.
"""
# In table distribution_centers we can use the unique
# country_code, number
dc = DistributionCenter.query.filter_by(country_code=country_code,
number=number).first()
return dc.id
def get_name(id):
"""
Returns name based on id (pk).
"""
dc = DistributionCenter.query.filter_by(id=id).first()
return dc.name
def get_all():
"""
Returns all distribution centers ordered by country_code, number
"""
return DistributionCenter.query.order_by('country_code',
'number').all()
def to_dict(self):
""" Convert the model to a dictionary that can go into a JSON """
return {
# id is unnecessary
'country_code': self.country_code,
'number': self.number,
'name': self.name,
'tag': self.tag,
'created_date': self.created_date,
'updated_date': self.updated_date
}