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)