class RedfinHouseGrade(db.Model):
__tablename__ = "redfin_house_grade"
redfin_house_grade_id = db.Column(UUID(as_uuid=True), primary_key=True, server_default=sa_text("uuid_generate_v4()"))
redfin_house_id = db.Column(UUID(as_uuid=True))
grade = db.Column(db.Float())
created_at = db.Column(db.DateTime(),server_default=sa_text("now()"))
updated_at = db.Column(db.DateTime(),server_default=sa_text("now()"))
class RedfinHouse(db.Model):
__tablename__ = "redfin_house"
redfin_house_id = db.Column(UUID(as_uuid=True), primary_key=True, server_default=sa_text("uuid_generate_v4()"))
address_1 = db.Column(db.String(256))
city = db.Column(db.String(128))
state = db.Column(db.String(8))
zip = db.Column(db.String(16))
latitude = db.Column(db.Float())
longitude = db.Column(db.Float())
price = db.Column(db.Float())
sale_type = db.Column(db.String(128))
sold_date = db.Column(db.DateTime())
property_type = db.Column(db.String(128))
year_built = db.Column(db.Integer())
beds = db.Column(db.Integer())
bath = db.Column(db.Float())
location = db.Column(db.String(128))
square_feet = db.Column(db.Float())
lot_size = db.Column(db.Float())
days_on_market = db.Column(db.Integer())
price_square_foot = db.Column(db.Float())
hoa_month = db.Column(db.Float())
house_url = db.Column(db.String(256))
source = db.Column(db.String(128))
mls_no = db.Column(db.String(16))
status = db.Column(db.String(32))
next_open_house_start_time = db.Column(db.DateTime())
next_open_house_end_time = db.Column(db.DateTime())
favorite = db.Column(db.Boolean())
interested = db.Column(db.Boolean())
created_at = db.Column(db.DateTime(),server_default=sa_text("now()"))
updated_at = db.Column(db.DateTime(),server_default=sa_text("now()"))
class RedfinHouseIngestChecksum(db.Model):
__tablename__ = "redfin_house_ingest_checksum"
checksum_id = db.Column(UUID(as_uuid=True), primary_key=True, server_default=sa_text("uuid_generate_v4()"))
checksum = db.Column(db.String(64))
filename = db.Column(db.String(256))
created_at = db.Column(db.DateTime(),server_default=sa_text("now()"))
updated_at = db.Column(db.DateTime(),server_default=sa_text("now()"))
class RedfinHousePriceEstimate(db.Model):
__tablename__ = "redfin_house_price_estimate"
price_estimate_id = db.Column(UUID(as_uuid=True), primary_key=True, server_default=sa_text("uuid_generate_v4()"))
house_id = db.Column(UUID(as_uuid=True))
price = db.Column(db.Float())
predicted_price = db.Column(db.Float())
created_at = db.Column(db.DateTime(),server_default=sa_text("now()"))
updated_at = db.Column(db.DateTime(),server_default=sa_text("now()"))
def push_data(data_in_dict):
if not data_in_dict or len(data_in_dict) == 0:
raise Exception('Empty_data_from_HA')
connection_str = f'mysql+pymysql://{SETTINGS["Database"]["username"]}:{SETTINGS["Database"]["password"]}@{SETTINGS["Database"]["ip"]}:{SETTINGS["Database"]["port"]}/{SETTINGS["Database"]["base"]}'
engine = create_engine(connection_str,
echo=SETTINGS['Database']['echo_debug'])
Base.metadata.create_all(bind=engine)
Session = sessionmaker(bind=engine, autocommit=False)
session = Session()
session.execute(sa_text('''Truncate table HAhosts'''))
counter_insert = 0
for h in data_in_dict:
if h['svname'] in ('BACKEND', 'FRONTEND'):
continue
if h['pxname'] not in SETTINGS['HAproxy']['pxname_filter']:
continue
host1 = Host()
host1.pxname = h['pxname']
host1.svname = h['svname']
host1.status = h['status']
host1.scur = h['scur']
host1.addr, host1.port = h['addr'].split(':')
host1.algo = h['algo']
session.add(host1)
counter_insert += 1
session.commit()
logging.info(f'Inserted {counter_insert} lines')
session.close()
class User(db.Model):
__tablename__ = 'users'
__table_args__ = {'extend_existing': True}
id = db.Column(UUID(as_uuid=True),
primary_key=True,
server_default=sa_text("uuid_generate_v4()"))
email = db.Column(db.String(254), unique=True, nullable=False)
fname = db.Column(db.String)
lname = db.Column(db.String)
password = db.Column(db.String, nullable=False)
salt = db.Column(db.String)
hash_version = db.Column(db.Integer)
roleid = db.Column(db.Integer,
db.ForeignKey('roles.id'),
nullable=False,
default=2)
role = db.relationship('Roles', backref=db.backref('roles', lazy=True))
preferences = db.Column(db.String)
def __repr__(self):
return '<User %r>' % self.email
def serialize(self):
return {
'id': self.id,
'email': self.email,
'fname': self.fname,
'lname': self.lname,
'role': self.role.name,
'preferences': self.preferences
}
class BaseMixin:
uuid: UUID = Column(
UUID2STR, primary_key=True, unique=True, nullable=False, server_default=sa_text("uuid_generate_v4()")
)
created: datetime = Column(UTCDatetime, default=datetime.now)
@db_handler
def add(self):
SESSION.add(self)
SESSION.commit()
# pylint: disable=R0201
@db_handler
def update(self):
SESSION.commit()
# pylint: enable=R0201
@db_handler
def delete(self):
SESSION.delete(self)
SESSION.commit()
@staticmethod
def rollback():
SESSION.rollback()
def _try_fetching_version_info_for_schema(
engine: Engine, schema: str) -> Optional[Tuple[int, int]]:
sql = SQL_SELECT_VERSIONS.format(quote=_quote_char(engine))
query = sa_text(sql)
result = engine.execute(query, col=schema)
rows = result.fetchall() # type: List[Tuple[int, int]]
return rows[0] if len(rows) == 1 else None
class RedfinHouse(db.Model):
__tablename__ = "redfin_house"
redfin_house_id = db.Column(UUID(as_uuid=True),
primary_key=True,
server_default=sa_text("uuid_generate_v4()"))
address_1 = db.Column(db.String(256))
class Follower(db.Model):
__tablename__ = "followers"
id = db.Column(db.Integer, primary_key=True)
uuid = db.Column(UUID(as_uuid=True),
server_default=sa_text("uuid_generate_v4()"),
unique=True)
actor_id = db.Column(db.Integer, db.ForeignKey("actor.id"))
target_id = db.Column(db.Integer, db.ForeignKey("actor.id"))
activity_url = db.Column(URLType(), unique=True, nullable=True)
creation_date = db.Column(db.DateTime(timezone=False),
default=datetime.datetime.utcnow)
modification_date = db.Column(db.DateTime(timezone=False),
onupdate=datetime.datetime.now)
actor = db.relationship("Actor", foreign_keys=[actor_id])
target = db.relationship("Actor", foreign_keys=[target_id])
__table_args__ = (UniqueConstraint("actor_id",
"target_id",
name="unique_following"), )
def __repr__(self):
return f"<Follower(id='{self.id}', actor_id='{self.actor_id}', target_id='{self.target_id}')>"
def follow_back(self):
f = (db.session.query(Follower.id).filter(
Follower.actor_id == self.target_id,
Follower.target_id == self.actor_id).first())
return f
class Registry(ModelMixin):
__tablename__ = 'registries'
id = db.Column(UUID(as_uuid=True),
primary_key=True,
server_default=sa_text("uuid_generate_v4()"))
name = db.Column(db.String, nullable=False, unique=True)
date_created = db.Column(db.DateTime, default=db.func.current_timestamp())
class RentalZipInfo(db.Model):
__tablename__ = "rental_zip_info"
zip = db.Column(db.String(16), primary_key=True)
state_name = db.Column(db.String(64))
state_abbr = db.Column(db.String(8))
county_name = db.Column(db.String(64))
area_name = db.Column(db.String(128))
area_token = db.Column(db.String(128))
metro = db.Column(db.Integer())
fmr_type = db.Column(db.Integer())
fmr0 = db.Column(db.Float())
fmr1 = db.Column(db.Float())
fmr2 = db.Column(db.Float())
fmr3 = db.Column(db.Float())
fmr4 = db.Column(db.Float())
overall_rank = db.Column(db.Integer())
overall_percentile = db.Column(db.Float())
year = db.Column(db.Integer())
created_at = db.Column(db.DateTime(),server_default=sa_text("now()"))
updated_at = db.Column(db.DateTime(),server_default=sa_text("now()"))
class RentalProperty(db.Model):
__tablename__ = "rental_property"
rental_property_id = db.Column(UUID(as_uuid=True), primary_key=True, server_default=sa_text("uuid_generate_v4()"))
address_1 = db.Column(db.String(256))
city = db.Column(db.String(128))
state = db.Column(db.String(8))
zip = db.Column(db.String(16))
latitude = db.Column(db.Float())
longitude = db.Column(db.Float())
purchase_price = db.Column(db.Float())
after_repair_price = db.Column(db.Float())
purchase_closing_cost = db.Column(db.Float())
repair_cost = db.Column(db.Float())
down_payment_percentage = db.Column(db.Float())
loan_interest_rate = db.Column(db.Float())
rent_per_unit = db.Column(db.Float())
rent_num_units = db.Column(db.Integer())
property_taxes = db.Column(db.Float())
insurance = db.Column(db.Float())
created_at = db.Column(db.DateTime(),server_default=sa_text("now()"))
updated_at = db.Column(db.DateTime(),server_default=sa_text("now()"))
class Project(ModelMixin):
__tablename__ = 'project'
id = db.Column(UUID(as_uuid=True), primary_key=True, server_default=sa_text("uuid_generate_v4()"))
name = db.Column(db.String(256), nullable=True)
alias = db.Column(db.String(256), nullable=False, unique=True)
owner_id = db.Column(UUID(as_uuid=True), db.ForeignKey('user.id'), nullable=False)
cluster_id = db.Column(UUID(as_uuid=True), db.ForeignKey('clusters.id'), nullable=False)
apps = db.relationship('App', backref='project', lazy=True)
description = db.Column(db.String, nullable=True)
date_created = db.Column(db.DateTime, default=db.func.current_timestamp())
project_databases = db.relationship(
'ProjectDatabase', backref='project', lazy=True)
class Cluster(ModelMixin):
__tablename__ = 'clusters'
id = db.Column(UUID(as_uuid=True),
primary_key=True,
server_default=sa_text("uuid_generate_v4()"))
name = db.Column(db.String, nullable=False, unique=True)
host = db.Column(db.String, nullable=False, unique=True)
token = db.Column(EncryptedType(db.String, secret), nullable=False)
description = db.Column(db.String, nullable=False)
projects = db.relationship('Project', backref='cluster', lazy=True)
date_created = db.Column(db.DateTime, default=db.func.current_timestamp())
class App(ModelMixin):
__tablename__ = 'app'
id = db.Column(UUID(as_uuid=True),
primary_key=True,
server_default=sa_text("uuid_generate_v4()"))
name = db.Column(db.String(256), nullable=True)
image = db.Column(db.String(256), nullable=False)
project_id = db.Column(UUID(as_uuid=True),
db.ForeignKey('project.id'),
nullable=False)
url = db.Column(db.String(256), nullable=True)
alias = db.Column(db.String(256), nullable=True, unique=True)
port = db.Column(db.Integer, nullable=False)
date_created = db.Column(db.DateTime, default=db.func.current_timestamp())
class Role(ModelMixin):
""" Roles Table Definition """
_tablename_ = 'role'
# fields of the Roles table
id = db.Column(UUID(as_uuid=True),
primary_key=True,
server_default=sa_text("uuid_generate_v4()"))
name = db.Column(db.String(256), nullable=False)
users = relationship('User', secondary='user_role', backref='roles')
def __init__(self, name):
""" initialize with name """
self.name = name
class Organisation(ModelMixin):
""" Organisation table definition """
_tablename_ = 'organisations'
__table_args__ = (db.UniqueConstraint('name', name='organisation_unique_name'),)
# fields of the Organisation table
id = db.Column(UUID(as_uuid=True), primary_key=True, server_default=sa_text("uuid_generate_v4()"))
name = db.Column(db.String(256), nullable=False)
date_created = db.Column(db.DateTime, default=db.func.current_timestamp())
admins = relationship('User', secondary='organisation_admins', backref='organisation_admin')
members = relationship('User', secondary='organisation_members', backref='organisations')
def __init__(self, name):
""" initialize with name, member and namespace """
self.name = name
class Activity(db.Model):
__tablename__ = "activity"
id = db.Column(db.Integer, primary_key=True)
actor = db.Column(db.Integer, db.ForeignKey("actor.id"))
uuid = db.Column(UUID(as_uuid=True), server_default=sa_text("uuid_generate_v4()"), unique=True)
url = db.Column(URLType(), unique=True, nullable=True)
type = db.Column(db.String(100), index=True)
box = db.Column(db.String(100))
payload = db.Column(JSONB())
creation_date = db.Column(db.DateTime(timezone=False), default=datetime.datetime.utcnow)
delivered = db.Column(db.Boolean, default=None, nullable=True)
delivered_date = db.Column(db.DateTime(timezone=False), nullable=True)
local = db.Column(db.Boolean, default=True)
meta_deleted = db.Column(db.Boolean, default=False)
meta_undo = db.Column(db.Boolean, default=False)
meta_pinned = db.Column(db.Boolean, default=False)
class ProjectDatabase(ModelMixin):
__tablename__ = 'project_database'
id = db.Column(UUID(as_uuid=True),
primary_key=True,
server_default=sa_text("uuid_generate_v4()"))
host = db.Column(db.String(256), nullable=True)
name = db.Column(db.String(256), nullable=False)
user = db.Column(db.String(256), nullable=False)
password = db.Column(db.String(256), nullable=False)
project_id = db.Column(UUID(as_uuid=True), db.ForeignKey('project.id'))
date_created = db.Column(db.DateTime, default=db.func.current_timestamp())
port = db.Column(db.Integer, nullable=True)
database_flavour_name = db.Column(db.String(256))
# todo: make database_flavour_name nullable=false
def password_is_valid(self, password):
""" checks the password against it's hash to validate the user's password """
return Bcrypt().check_password_hash(self.password, password)
class Raw_data(db.Model):
id = db.Column(UUID(as_uuid=True),
primary_key=True,
server_default=sa_text("uuid_generate_v4()"))
eventtime = db.Column(db.DateTime(timezone=True))
devicename = db.Column(db.String(200))
item = db.Column(db.String(200))
detailstatus = db.Column(db.String(200))
filename = db.Column(db.String(200))
linenumber = db.Column(db.Integer)
def __init__(self, id, eventtime, devicename, item, detailstatus, filename,
linenumber):
self.id = id
self.eventtime = eventtime
self.devicename = devicename
self.item = item
self.detailstatus = detailstatus
self.filename = filename
self.linenumber = linenumber
class Namespace(ModelMixin):
""" Namespace table definition """
_tablename_ = 'namespace'
__table_args__ = (db.UniqueConstraint('name',
name='namespace_unique_name'), )
# fields of the Namespace table
id = db.Column(UUID(as_uuid=True),
primary_key=True,
server_default=sa_text("uuid_generate_v4()"))
name = db.Column(db.String(256), nullable=False)
organisation_id = db.Column(
"organisation_id", UUID(as_uuid=True),
db.ForeignKey(Organisation.id, ondelete='CASCADE'))
date_created = db.Column(db.DateTime, default=db.func.current_timestamp())
def __init__(self, name, organisation_id):
""" initialize with name and org_id """
self.name = name
self.organisation_id = organisation_id
class User(ModelMixin):
""" user table definition """
_tablename_ = "users"
# fields of the user table
id = db.Column(UUID(as_uuid=True),
primary_key=True,
server_default=sa_text("uuid_generate_v4()"))
email = db.Column(db.String(256), unique=True, nullable=False, default="")
name = db.Column(db.String(256), nullable=False, default="")
username = db.Column(db.String(256), nullable=False, default="")
password = db.Column(db.String(256), nullable=False, default="")
verified = db.Column(db.Boolean, nullable=False, default=False)
date_created = db.Column(db.DateTime, default=db.func.current_timestamp())
projects = db.relationship('Project', backref='owner', lazy=True)
def __init__(self, email, name, password):
""" initialize with email, username and password """
self.email = email
self.name = name
self.username = name
self.password = Bcrypt().generate_password_hash(password).decode()
def password_is_valid(self, password):
""" checks the password against it's hash to validate the user's password """
return Bcrypt().check_password_hash(self.password, password)
def generate_token(self, user):
""" generates the access token """
# set token expiry period
expiry = timedelta(days=10)
return create_access_token(user, expires_delta=expiry)
def __repr__(self):
return "<User: {}>".format(self.email)
class BaseMixin:
uuid: UUID = Column(UUID2STR,
primary_key=True,
unique=True,
nullable=False,
server_default=sa_text("uuid_generate_v4()"))
created: datetime = Column(UTCDatetime, default=datetime.now)
@db_handler
def add(self, session: Session):
session.add(self)
session.commit()
# pylint: disable=R0201
@db_handler
def update(self, session: Session):
session.commit()
# pylint: enable=R0201
@db_handler
def delete(self, session: Session):
session.delete(self)
session.commit()
class Prediction(database.Model):
__tablename__ = 'prediction'
id = database.Column(database.BigInteger,
primary_key=True,
autoincrement=True)
file_name = database.Column(database.String(75),
unique=False,
nullable=False)
model_name = database.Column(database.String(75),
unique=False,
nullable=False)
model_path = database.Column(database.String(75),
unique=False,
nullable=False)
results = database.Column(database.Text(), unique=False, nullable=False)
creation_date = database.Column(
database.DateTime,
unique=False,
nullable=False,
server_default=sa_text("date '2019-08-01'"))
@property
def serialize(self):
return {
'id': self.id,
'file_name': self.file_name,
'model_name': self.model_name,
'model_path': self.model_path,
'results': self.results,
'creation_date': self.creation_date
}
@property
def serialize_without_sensitive_data(self):
return {
'id': self.id,
'file_name': self.file_name,
'creation_date': self.creation_date
}
@staticmethod
def check_data(object_json):
if 'file_name' not in object_json:
return False
if 'model_name' not in object_json:
return False
if 'model_path' not in object_json:
return False
if 'results' not in object_json:
return False
return True
def __init__(self, json):
if self.check_data(json):
self.file_name = json['file_name']
self.model_name = json['model_name']
self.model_path = json['model_path']
self.results = json['results']
self.creation_date = datetime.now()
else:
raise PredictException('MALFORMED')
def __repr__(self):
return 'ID %s and file_name %s.' % (self.id, self.file_name)
def _query(self, query, **kwargs):
"""Converts a text SQL expression to a sqlalchemy object, adding named
parameters and returning the result as a list of dicts."""
query = sa_text(query)
result = self.connection.execute(query, kwargs)
return self._resultproxy_to_list_of_dicts(result)
def truncate_table(self):
self.engine.execute(sa_text('''TRUNCATE TABLE webpage''').execution_options(autocommit=True))
def now():
"""
Return a function for setting actual time of datetime columns
"""
return sa_text('statement_timestamp()')
def truncate_api_db():
with get_contexted_session() as session:
meta = ApiBase.metadata
for table in reversed(meta.sorted_tables):
session.execute(sa_text(f'DELETE FROM {table.name};'))
session.commit()
def truncate_structured_db(db):
session = db.get_session()
meta = StructuredBase.metadata
for table in reversed(meta.sorted_tables):
session.execute(sa_text(f'DELETE FROM {table.name};'))
def _execute_sql_ignore_errors(engine: Engine, query: str) -> None:
try:
engine.execute(sa_text(query))
except SQLAlchemyError:
pass
