class Transaction(db.Model):
__tablename__ = 'transaction'
user_id = db.Column(db.Integer, db.ForeignKey(User.id))
user = db.relationship('User')
id = db.Column(db.Integer, primary_key=True)
description = db.Column(db.String(200))
occurrence_date = db.Column(db.DateTime())
booking_date = db.Column(db.DateTime())
amount = db.Column(db.Numeric(precision=10, scale=4))
is_credit = db.Column(db.Boolean())
category_id = db.Column(db.Integer, db.ForeignKey(TransactionCategory.id))
category = db.relationship('TransactionCategory')
account_id = db.Column(db.Integer, db.ForeignKey(Account.id))
account = db.relationship('Account')
class Host(db.Model):
__tablename__ = 'hosts'
uuid = db.Column(UUIDType(binary=False),
primary_key=True,
default=uuid.uuid4())
code = db.Column(db.String(64), unique=True, nullable=False)
ip_address = db.Column(IPAddressType, unique=True, nullable=False)
conn_user = db.Column(db.String(64), nullable=False)
lab_uuid = db.Column(UUIDType(binary=False), db.ForeignKey('labs.uuid'))
def __init__(self, data):
self.uuid = uuid.uuid4()
self.code = data['code'].upper()
self.ip_address = data['ip_address']
self.conn_user = data['conn_user']
def save(self):
try:
db.session.add(self)
db.session.commit()
except KeyError or IntegrityError as e:
db.session.rollback()
raise e
@staticmethod
def get(host_uuid=None):
return Host.query.get(host_uuid) if host_uuid else Host.query.all()
def update(self, data):
if 'code' in data and data['code'] != "":
self.code = data['code']
if 'ip_address' in data and data['ip_address'] != "":
self.ip_address = data['ip_address']
if 'conn_user' in data and data['conn_user'] != "":
self.conn_user = data['conn_user']
if 'lab_uuid' in data and data['lab_uuid'] != "":
self.lab_uuid = uuid.UUID(data['lab_uuid'])
try:
db.session.commit()
except Exception as e:
db.session.rollback()
raise e
@staticmethod
def delete(host):
try:
db.session.delete(host)
db.session.commit()
except UnmappedInstanceError as e:
db.session.rollback()
raise e
def to_dict(self):
return dict(uuid=str(self.uuid),
code=self.code,
ip_address=self.ip_address.compressed,
conn_user=self.conn_user,
lab_id=self.lab.id)
class Comment(db.Model):
""" A Comment about a blog Post
"""
__tablename__ = 'comments'
id = db.Column(db.Integer, primary_key=True)
author = db.Column(db.String(100), nullable=False)
body = db.Column(db.Text, nullable=False)
created = db.Column(db.DateTime, default=datetime.utcnow())
post_id = db.Column(db.Integer, db.ForeignKey('posts.id'))
class Post(db.Model):
""" A Post defining a blog article
:param int id: Unique id for this post
:param str title: Unique blog post title
:param str body: The complete text of the post, written in Markdown
:param datetime created: When the post was created
:param int author_id: the id of the author who wrote this article (possibly worthless)
"""
__tablename__ = 'posts'
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(100), unique=True)
body = db.Column(db.Text)
created = db.Column(db.DateTime, default=datetime.utcnow())
author_id = db.Column(db.Integer, db.ForeignKey(User.id))
comments = relationship("Comment", backref='posts')
# TODO: tags for each post?
@classmethod
def query_all(cls):
""" Return all stored, ordered in reverse chronological (newest to oldest)
:return Query:
"""
return Post.query.order_by(desc(Post.created))
@classmethod
def group_by_year_month(cls):
""" Returns all posts grouped by year and month
:return dict: { (year, month): [Posts] }
"""
order_group = collections.OrderedDict()
posts = Post.query.with_entities(Post.id, Post.title,
Post.created).order_by(
desc(Post.created))
for ((year, month), grouped_posts) in groupby(
posts, lambda x: (x.created.year, x.created.month)):
grouped = []
for post in grouped_posts:
slugged_post = post._asdict()
slugged_post['slug'] = slugify(post.title, '-')
grouped.append(slugged_post)
order_group[(year, calendar.month_name[month])] = grouped
return order_group
@property
def slug(self):
""" Returns 'sluggified' version of the title
:return str:
"""
return slugify(self.title, '-')
def __repr__(self):
return '<Post %s>' % self.title
class DeviceDocumentation(db.Model):
__tablename__ = 'device_documentations'
device_id = db.Column(db.Integer,
db.ForeignKey('devices.id'),
primary_key=True)
text = db.Column(db.Text, nullable=False)
created_at = db.Column(db.DateTime,
nullable=False,
default=db.func.current_timestamp())
modified_at = db.Column(db.DateTime,
nullable=False,
default=db.func.current_timestamp(),
onupdate=db.func.current_timestamp())
def __init__(self, device_id, text):
self.device_id = device_id
self.text = text
def save(self):
"""
Stores this device documentation to database
This may raise errors
"""
db.session.add(self)
db.session.commit()
@staticmethod
def get(**kwargs):
"""
Get device documentation with given filters
Available filters:
* device_id
"""
return DeviceDocumentation.query.filter_by(**kwargs).first()
@staticmethod
def get_for_device(device_id):
"""
Get documentation for device with device id passed in
parameter
"""
return (DeviceDocumentation.get(device_id=device_id)
or DeviceDocumentation(device_id, ""))
def __repr__(self):
return '<DeviceDocumentation (device_id %s)>' % self.device_id
class Dashboard(db.Model):
__tablename__ = 'dashboards'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
dashboard_data = db.Column(JSON, nullable=False)
account_id = db.Column(db.Integer,
db.ForeignKey('accounts.id'),
primary_key=True)
active = db.Column(db.Boolean, nullable=False, default=False)
name = db.Column(db.String, nullable=False, default="")
created_at = db.Column(db.DateTime,
nullable=False,
default=db.func.current_timestamp())
modified_at = db.Column(db.DateTime,
nullable=False,
default=db.func.current_timestamp(),
onupdate=db.func.current_timestamp())
widgets = db.relationship("DashboardWidget",
cascade="save-update, merge, delete")
def __init__(self, account_id, dashboard_data, name):
self.account_id = account_id
self.dashboard_data = dashboard_data
self.name = name
def save(self):
"""
Stores this dashboard to database
This may raise errors
"""
db.session.add(self)
db.session.commit()
def delete(self):
"""
Deletes this dashboard from database
"""
db.session.delete(self)
db.session.commit()
@staticmethod
def exists_with_any_of(**kwargs):
"""
Checks if dashboard with any of the given arguments exists
"""
for key, value in kwargs.items():
args = {key: value}
if Dashboard.query.filter_by(**args).first():
return True
return False
@staticmethod
def exists(**kwargs):
"""
Checks if dashboard with all of the given arguments exists
"""
if Dashboard.query.filter_by(**kwargs).first():
return True
return False
@staticmethod
def get_all():
"""
Get all stored dashboards
"""
return Dashboard.query.all()
@staticmethod
def get_many(**kwargs):
"""
Get dashboards with given filters
Available filters:
* id
* account_id
"""
return Dashboard.query.filter_by(**kwargs).all()
@staticmethod
def get_many_filtered(account_id, active):
"""
Get many dashboard with given filters
Available filters:
* active
"""
query = Dashboard.query.filter(Dashboard.account_id == account_id)
if active is not None:
query = query.filter(Dashboard.active == active)
return query.all()
@staticmethod
def deactivate_all_for_user(account_id):
"""
Deactivates all dashboards for this user
"""
db.session.query(Dashboard).filter(account_id == account_id) \
.update({'active': False})
db.session.commit()
@staticmethod
def get(**kwargs):
"""
Get dashboard with given filters
Available filters:
* id
* account_id
"""
return Dashboard.query.filter_by(**kwargs).first_or_404()
def __repr__(self):
return '<Dashboard (dashboard_data=%s, account_id=%s)>' % (
self.dashboard_data, self.account_id)
class DashboardWidget(db.Model):
__tablename__ = 'dashboard_widgets'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
dashboard_id = db.Column(db.Integer,
db.ForeignKey('dashboards.id'),
nullable=False)
device_id = db.Column(db.Integer,
db.ForeignKey('devices.id'),
nullable=False)
name = db.Column(db.String, nullable=False)
height = db.Column(db.Integer, nullable=False)
width = db.Column(db.Integer, nullable=False)
x = db.Column(db.Integer, nullable=False)
y = db.Column(db.Integer, nullable=False)
chart_type = db.Column(db.String, nullable=False)
filters = db.Column(JSON, nullable=False)
created_at = db.Column(db.DateTime,
nullable=False,
default=db.func.current_timestamp())
modified_at = db.Column(db.DateTime,
nullable=False,
default=db.func.current_timestamp(),
onupdate=db.func.current_timestamp())
dashboard = db.relationship("Dashboard", foreign_keys=[dashboard_id])
def __init__(self, dashboard_id, device_id, name, height, width, x, y,
chart_type, filters):
self.dashboard_id = dashboard_id
self.device_id = device_id
self.name = name
self.height = height
self.width = width
self.x = x
self.y = y
self.chart_type = chart_type
self.filters = filters
def save(self):
"""
Stores this widget to database
This may raise errors
"""
db.session.add(self)
db.session.commit()
def delete(self):
"""
Deletes this widget from database
"""
db.session.delete(self)
db.session.commit()
@staticmethod
def exists_with_any_of(**kwargs):
"""
Checks if widget with any of the given arguments exists
"""
for key, value in kwargs.items():
args = {key: value}
if DashboardWidget.query.filter_by(**args).first():
return True
return False
@staticmethod
def exists(**kwargs):
"""
Checks if widget with all of the given arguments exists
"""
if DashboardWidget.query.filter_by(**kwargs).first():
return True
return False
@staticmethod
def get_all():
"""
Get all stored widgets
"""
return DashboardWidget.query.paginate(None, None, False).items
@staticmethod
def get_many(**kwargs):
"""
Get widgets with given filters
Available filters:
* id
* dashboard_id
* device_id
* dimensions and positions, but useless
* chart_type
* filters, but useless
"""
return DashboardWidget.query.filter_by(**kwargs).paginate(
None, None, False).items
@staticmethod
def get_many_for_dashboard(dashboard_id):
"""
Get widgets for given dashboard
"""
query = DashboardWidget.query.filter(
DashboardWidget.dashboard_id == dashboard_id)
return query.paginate(None, None, False).items
@staticmethod
def get(**kwargs):
"""
Get widget with given filters
Available filters:
* id
* dashboard_id
* device_id
* dimensions and positions, but useless
* chart_type
* filters, but useless
"""
return DashboardWidget.query.filter_by(**kwargs).first_or_404()
def __repr__(self):
return '<DashboardWidget (id=%s, dashboard_id=%s)>' % (
self.dashboard_data, self.account_id)
# -*- coding: utf-8 -*-
"""
app.users.models
~~~~~~~~~~~~~~~~~~~~~
User models
"""
from flask_security import UserMixin, RoleMixin, SQLAlchemyUserDatastore
from app.core import db as db_sec
roles_users = db_sec.Table(
'roles_users',
db_sec.Column('user_id', db_sec.Integer(), db_sec.ForeignKey('user.id')),
db_sec.Column('role_id', db_sec.Integer(), db_sec.ForeignKey('role.id')))
class Role(RoleMixin, db_sec.Model):
__tablename__ = 'role'
id = db_sec.Column(db_sec.Integer(), primary_key=True)
name = db_sec.Column(db_sec.String(80), unique=True)
description = db_sec.Column(db_sec.String(255))
def __eq__(self, other):
return (self.name == other
or self.name == getattr(other, 'name', None))
def __ne__(self, other):
return (self.name != other
and self.name != getattr(other, 'name', None))
class Account(db.Model):
__tablename__ = 'accounts'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
username = db.Column(db.String, index=True, unique=True)
password = db.Column(db.String)
email = db.Column(db.String, index=True, unique=True)
role_id = db.Column(db.Integer, db.ForeignKey("roles.id"))
role = db.relationship("Role", foreign_keys=[role_id])
confirmed = db.Column(db.Boolean, default=False, nullable=False)
confirmed_at = db.Column(db.DateTime, nullable=True)
created_at = db.Column(db.DateTime,
nullable=False,
default=db.func.current_timestamp())
modified_at = db.Column(db.DateTime,
nullable=False,
default=db.func.current_timestamp(),
onupdate=db.func.current_timestamp())
dashboards = db.relationship("Dashboard",
cascade="save-update, merge, delete")
def __init__(self, username, password, email, role=2):
self.username = str(username)
self.password = str(password)
self.email = str(email)
if isinstance(role, Role):
self.role_id = role.id
else:
self.role_id = int(role)
def save(self):
"""
Stores this user to database
This may raise errors
"""
db.session.add(self)
db.session.commit()
@staticmethod
def exists_with_any_of(**kwargs):
"""
Checks if user with any of the given arguments exists
"""
for key, value in kwargs.items():
args = {key: value}
if Account.query.filter_by(**args).first():
return True
return False
@staticmethod
def exists(**kwargs):
"""
Checks if user with all of the given arguments exists
"""
if Account.query.filter_by(**kwargs).first():
return True
return False
@staticmethod
def get_all():
"""
Get all stored accounts
"""
return Account.query.all()
@staticmethod
def get(**kwargs):
"""
Get account with given filters
Available filters:
* username
* email
* role_id
* id
* password (useless, but not forbidden)
"""
return Account.query.filter_by(**kwargs).first()
def create_auth_token(self):
"""
Generates the Auth Token
:return: string
"""
current_time = datetime.datetime.utcnow()
payload = {
'exp': current_time + datetime.timedelta(days=0, hours=1),
'iat': current_time,
'sub': self.id
}
return jwt.encode(payload,
app.config.get('SECRET_KEY'),
algorithm='HS256').decode('utf-8')
@staticmethod
def validate_token(token):
"""
Validates given Auth token
:rtype: Account
:return: Account associated with token
"""
payload = jwt.decode(token,
app.config.get('SECRET_KEY'),
algorithms=['HS256'])
current_time = timegm(datetime.datetime.utcnow().utctimetuple())
if current_time > payload['exp']:
raise ValueError("Expired token")
return Account.get(id=payload['sub'])
def __repr__(self):
return '<Account (name=%s, role=%s)>' % (self.username, self.role)
class Recording(db.Model):
__tablename__ = 'recordings'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
recorded_at = db.Column(db.DateTime,
index=True,
default=db.func.current_timestamp())
received_at = db.Column(db.DateTime,
index=True,
default=db.func.current_timestamp())
device_id = db.Column(db.Integer, db.ForeignKey('devices.id'))
record_type = db.Column(db.Integer, nullable=False)
record_value = db.Column(db.Float, nullable=False)
raw_record = db.Column(JSON, nullable=True)
def __init__(self, device_id, record_type, record_value, recorded_at,
raw_json):
self.device_id = int(device_id)
self.record_type = int(record_type)
self.record_value = float(record_value)
self.recorded_at = datetime.fromtimestamp(int(recorded_at))
self.received_at = datetime.utcnow()
self.raw_record = raw_json
def save(self):
"""
Stores this recording to database
This may raise errors
"""
db.session.add(self)
db.session.commit()
def delete(self):
"""
Deletes this recording from database
"""
db.session.delete(self)
db.session.commit()
@staticmethod
def get_all():
return Recording.query.all()
@staticmethod
def get_many(**kwargs):
"""
Get many recording with given filters as a list
Available filters:
* id
* device_id
* record_type
* recorded_at
* received_at
* record_value (probably useless)
* raw_record (useless)
"""
return Recording.query.filter_by(**kwargs).all()
@staticmethod
def get_many_filtered(device_id, record_type, date_start, date_end):
"""
Get many recording with given filters as a list
Available filters:
* record_type
* recorded_at (upper and lower limit)
"""
query = Recording.query.filter(Recording.device_id == device_id)
if record_type is not None:
query = query.filter(Recording.record_type == record_type)
if date_start is not None:
lower_limit = time.mktime(
datetime.strptime(date_start, "%d-%m-%Y").timetuple())
query = query.filter(
Recording.recorded_at > db.func.to_timestamp(lower_limit))
if date_end is not None:
upper_limit = time.mktime(
(datetime.strptime(date_end, "%d-%m-%Y") +
datetime_module.timedelta(days=1)).timetuple())
query = query.filter(
Recording.recorded_at < db.func.to_timestamp(upper_limit))
return query.all()
@staticmethod
def get(**kwargs):
"""
Get recordings with given filters
Available filters:
* id
* device_id
* record_type
* recorded_at
* received_at
* record_value (probably useless)
* raw_record (useless)
"""
return Recording.query.filter_by(**kwargs).first_or_404()
@staticmethod
def get_latest(device_id):
"""
Get latest recording for device with id device_id
"""
return Recording.query.order_by('recorded_at desc').first_or_404()
def __repr__(self):
return '<Recording (value=%s, recorded_at=%s)>' % (self.record_value,
self.recorded_at)
class DeviceAssociation(db.Model):
__tablename__ = 'device_associations'
device_id = db.Column(db.Integer,
db.ForeignKey('devices.id'),
primary_key=True)
account_id = db.Column(db.Integer,
db.ForeignKey('accounts.id'),
primary_key=True)
access_level = db.Column(db.Integer,
db.ForeignKey('access_levels.id'),
nullable=False)
created_at = db.Column(db.DateTime,
nullable=False,
default=db.func.current_timestamp())
modified_at = db.Column(db.DateTime,
nullable=False,
default=db.func.current_timestamp(),
onupdate=db.func.current_timestamp())
access_level_data = db.relationship("AccessLevel",
foreign_keys=[access_level])
def __init__(self, device_id, account_id, access_level=1):
self.device_id = device_id
self.account_id = account_id
self.access_level = access_level
def save(self):
"""
Stores this device association to database
This may raise errors
"""
db.session.add(self)
db.session.commit()
@staticmethod
def get_many(**kwargs):
"""
Get many device associations with given filters as a list
Available filters:
* device_id
* account_id
"""
return DeviceAssociation.query.filter_by(**kwargs).all()
@staticmethod
def get_for_user(account_id):
"""
Get many device associations for user with account id passed in
parameter
"""
return DeviceAssociation.get_many(account_id=account_id)
@staticmethod
def get_for_device(device_id):
"""
Get many device associations for device with account id passed in
parameter
"""
return DeviceAssociation.get_many(device_id=device_id)
def __repr__(self):
return '<DeviceAssociation (device_id=%s, accoount_id=%s)>' % (
self.device_id, self.account_id)
class Device(db.Model):
__tablename__ = 'devices'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
created_at = db.Column(db.DateTime,
nullable=False,
default=db.func.current_timestamp())
modified_at = db.Column(db.DateTime,
nullable=False,
default=db.func.current_timestamp(),
onupdate=db.func.current_timestamp())
name = db.Column(db.String, nullable=False)
device_type_id = db.Column(db.Integer, db.ForeignKey('device_types.id'))
device_type = db.relationship("DeviceType", foreign_keys=[device_type_id])
device_secret = db.Column(db.String, nullable=False)
secret_algorithm = db.Column(db.String, nullable=False)
configuration = db.Column(JSON, nullable=True)
users = db.relationship("DeviceAssociation",
cascade="save-update, merge, delete")
recordings = db.relationship("Recording",
cascade="save-update, merge, delete")
widgets = db.relationship("DashboardWidget",
cascade="save-update, merge, delete")
documentations = db.relationship("DeviceDocumentation",
cascade="save-update, merge, delete")
def __init__(self, name, configuration=None, device_type=1):
self.name = name
self.configuration = configuration
self.device_type_id = device_type
self.secret_algorithm = 'sha256'
self.device_secret = token_urlsafe(32)
def save(self):
"""
Stores this device to database
This may raise errors
"""
db.session.add(self)
db.session.commit()
def delete(self):
"""
Deletes this recording from database
"""
db.session.delete(self)
db.session.commit()
@staticmethod
def get_many(**kwargs):
"""
Get many devices with given filters as a list
Available filters:
* id
* name
* device_type_id
* created_at
* modified_at
* configuration (useless)
"""
return Device.query.filter_by(**kwargs).paginate(None, None,
False).items
@staticmethod
def get_many_for_user(account_id):
"""
Get many devices which are associated to account
"""
return Device.query.filter(Device.users.any(
account_id=account_id)).paginate(None, None, False).items
@staticmethod
def get(**kwargs):
"""
Get device with given filters
Available filters:
* id
* name
* device_type_id
* created_at
* modified_at
* configuration (useless)
"""
return Device.query.filter_by(**kwargs).first_or_404()
@staticmethod
def exists(**kwargs):
"""
Checks if device with all of the given arguments exists
"""
if Device.query.filter_by(**kwargs).first():
return True
return False
def __repr__(self):
return '<Device (name=%s, type=%s)>' % (self.name, self.device_type_id)
