class EstratoCosto(db.Model):
__tablename__ = 'estratocostocc'
id = db.Column('idcosto', db.Integer, primary_key=True)
estrato = db.Column('estrato', db.String(100), nullable=False)
costo = db.Column('costo', db.Float)
dispositivo_estrato = db.relationship('DeviceConfiguration',
uselist=False,
backref='estrato')
class Rstate(db.Model):
"""
State for Rules
"""
id = db.Column(db.Integer, primary_key=True)
description = db.Column(db.String(260))
def __init__(self, description):
self.description = description
class Categoria(db.Model):
__tablename__ = 'devicecategory'
id = db.Column('IdDeviceCategory', db.Integer, primary_key=True)
title = db.Column('Description', db.String(100), nullable=False)
deviceCategoria = db.relationship('UserDevice', backref='dispCategoria')
def __repr__(self):
return "<Categoria(id='%s', name='%s')>" % (self.id, self.title)
class DeviceConsumption(db.Model):
__tablename__ = 'deviceconsumption'
id = db.Column('IdConsumption', db.Integer, primary_key=True)
date = db.Column('Date', db.DateTime, nullable=False,
default=datetime.utcnow)
quantity = db.Column('Quantity', db.Float)
idUnitFk = db.Column('IdUnit', db.Integer, db.ForeignKey('unit.IdUnit'))
idUserDevice = db.Column('IdUserDevice', db.Integer,
db.ForeignKey('userdevice.IdUserDevice'))
class Organization(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(150), unique=True)
arange = db.Column(db.Text)
def __init__(self, name, arange):
self.name = name
self.arange = arange
def __repr__(self):
return self.name
class Role(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(20), unique=True)
description = db.Column(db.String(260))
def __init__(self, name, description):
self.name = name
self.description = description
def __repr__(self):
return self.name
class Post(db.Model):
__tablename__ = 'post'
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(100), nullable=False)
date_posted = db.Column(db.DateTime, nullable=False,
default=datetime.utcnow)
content = db.Column(db.Text, nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey(
'user.IdUser'), nullable=False)
def __repr__(self):
return f"Post('{self.title}', '{self.date_posted}')"
class Action(db.Model):
"""
Action for rule
"""
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(120), unique=True)
command = db.Column(db.String(120), unique=True)
description = db.Column(db.String(260))
role_id = db.Column(db.Integer, db.ForeignKey('role.id'), nullable=False)
role = db.relationship('Role', backref='action')
def __init__(self, name, command, description, role_id=2):
self.name = name
self.command = command
self.description = description
self.role_id = role_id
class UserDevice(db.Model):
__tablename__ = 'userdevice'
id = db.Column('IdUserDevice', db.Integer, primary_key=True)
linkDate = db.Column('LinkDate',
db.DateTime,
nullable=False,
default=datetime.utcnow)
idDeviceFK = db.Column(
'IdDevice', db.Integer,
db.ForeignKey('device.IdDevice')) # Como se llama en la tabla
idUserFK = db.Column('IdUser', db.Integer, db.ForeignKey('user.IdUser'))
active = db.Column('Active', db.String(1))
idDeviceCategoryFK = db.Column(
'IdDeviceCategory', db.Integer,
db.ForeignKey('devicecategory.IdDeviceCategory'))
zona = db.Column('Zone', db.String(100))
consumosDispositivo = db.relationship('DeviceConsumption',
backref='userDevice',
cascade="all, delete-orphan")
configLimiteDispositivo = db.relationship('DeviceConfiguration',
backref='userDeviceConfigParent',
uselist=False,
cascade="all, delete-orphan")
class User(db.Model, UserMixin):
__tablename__ = 'user'
id = db.Column('IdUser', db.Integer, primary_key=True)
username = db.Column('UserName', db.String(30))
dateCreation = db.Column('CreatedDate', db.DateTime, nullable=False,
default=datetime.utcnow)
password = db.Column('password', db.String(100))
active = db.Column('Active', db.String(100), default='S')
rol = db.Column('IdRole', db.Integer, default='111')
profile = db.relationship('UserProfile', backref='usuario', uselist=False)
posts = db.relationship('Post', backref='author', lazy=True)
deviceUsuario = db.relationship('UserDevice', backref='dispositivo')
def get_reset_token(self, expires_sec=1800):
s = Serializer(app.config['SECRET_KEY'], expires_sec)
return s.dumps({'user_id': self.id}).decode('utf-8')
@staticmethod
def verify_reset_token(token):
s = Serializer(app.config['SECRET_KEY'])
try:
user_id = s.loads(token)['user_id']
except:
return None
return User.query.get(user_id)
class DeviceConfiguration(db.Model):
__tablename__ = 'deviceconfiguration'
id = db.Column('IdDeviceConfiguration', db.Integer, primary_key=True)
limitDefined = db.Column('LimitDefined', db.Float)
startDateConfig = db.Column('StartDate', db.DateTime, nullable=False)
endDateConfig = db.Column('EndDate', db.DateTime, nullable=False)
isPeriodic = db.Column('IsPeriodic', db.Integer, default=1)
idUserDevice = db.Column('IdUserDevice', db.Integer,
db.ForeignKey('userdevice.IdUserDevice'))
estrato_id = db.Column('estrato', db.Integer,
db.ForeignKey('estratocostocc.idcosto'))
class User(db.Model):
"""
App User
"""
id = db.Column(db.Integer, primary_key=True)
uuid = db.Column(db.String(180), unique=True)
comment = db.Column(db.String(500))
email = db.Column(db.String(255))
name = db.Column(db.String(255))
phone = db.Column(db.String(255))
apikeys = db.relationship('ApiKey', backref='user2', lazy='dynamic')
role = db.relationship('Role',
secondary=user_role,
lazy='dynamic',
backref='user')
organization = db.relationship('Organization',
secondary=user_organization,
lazy='dynamic',
backref='user')
def __init__(self, uuid, name=None, phone=None, email=None, comment=None):
self.uuid = uuid
self.phone = phone
self.name = name
self.email = email
self.comment = comment
def update(self, form):
"""
update the user with values from form object
:param form: flask form from request
:return: None
"""
self.uuid = form.uuid.data
self.name = form.name.data
self.email = form.email.data
self.phone = form.phone.data
self.comment = form.comment.data
# first clear existing roles and orgs
for role in self.role:
self.role.remove(role)
for org in self.organization:
self.organization.remove(org)
for role_id in form.role_ids.data:
r = db.session.query(Role).filter_by(id=role_id).first()
if not r in self.role:
ro = self.role.append(r)
for org_id in form.org_ids.data:
o = db.session.query(Organization).filter_by(id=org_id).first()
if not o in self.organization:
org = self.organization.append(o)
db.session.commit()
class Log(db.Model):
id = db.Column(db.Integer, primary_key=True)
time = db.Column(db.DateTime)
task = db.Column(db.String(1000))
author = db.Column(db.String(1000))
rule_type = db.Column(db.Integer)
rule_id = db.Column(db.Integer)
user_id = db.Column(db.Integer)
def __init__(self, time, task, user_id, rule_type, rule_id, author):
self.time = time
self.task = task
self.rule_type = rule_type
self.rule_id = rule_id
self.user_id = user_id
self.author = author
class UserProfile(db.Model):
__tablename__ = "userprofile"
id = db.Column('IdUser', db.Integer, db.ForeignKey(
'user.IdUser'), primary_key=True)
firstName = db.Column('FirstName', db.String(100))
lastName = db.Column('LastName', db.String(100))
email = db.Column('Email', db.String(100))
phone = db.Column('Phone', db.Integer)
image_file = db.Column(db.String(20), nullable=False,
default='default2.jpg')
def __repr__(self):
return f"UserProfile('{self.firstName}','{self.lastName}','{self.email}')"
class Community(db.Model):
"""
Community for RTBH rule
"""
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(120), unique=True)
comm = db.Column(db.String(2047))
larcomm = db.Column(db.String(2047))
extcomm = db.Column(db.String(2047))
description = db.Column(db.String(255))
role_id = db.Column(db.Integer, db.ForeignKey('role.id'), nullable=False)
role = db.relationship('Role', backref='community')
def __init__(self, name, comm, larcomm, extcomm, description, role_id=2):
self.name = name
self.comm = comm
self.larcomm = larcomm
self.extcomm = extcomm
self.description = description
self.role_id = role_id
class Log(db.Model):
id = db.Column(db.Integer, primary_key=True)
time = db.Column(db.DateTime)
task = db.Column(db.String(1000))
rule_type = db.Column(db.Integer)
rule_id = db.Column(db.Integer)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
user = db.relationship('User', backref='log')
def __init__(self, time, task, user_id, rule_type, rule_id):
self.time = time
self.task = task
self.rule_type = rule_type
self.rule_id = rule_id
self.user_id = user_id
def __repr__(self):
return '<Log %r>' % (self.hostname)
class ApiKey(db.Model):
id = db.Column(db.Integer, primary_key=True)
machine = db.Column(db.String(255))
key = db.Column(db.String(255))
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
user = db.relationship('User', backref='apikey')
class Device(db.Model):
__tablename__ = 'device'
id = db.Column('IdDevice', db.Integer, primary_key=True)
serialID = db.Column('SerialID', db.String(25))
deviceUsuario = db.relationship('UserDevice', backref='dispUser')
class Recomendacion(db.Model):
__tablename__ = 'recommendation'
id = db.Column('IdTip', db.Integer, primary_key=True)
msg_tip = db.Column('Recommendation', db.String(500), nullable=False)
idTipe = db.Column('IdType', db.Integer,
db.ForeignKey('recommendationtype.IdType'))
class Flowspec6(db.Model):
id = db.Column(db.Integer, primary_key=True)
source = db.Column(db.String(255))
source_mask = db.Column(db.Integer)
source_port = db.Column(db.String(255))
dest = db.Column(db.String(255))
dest_mask = db.Column(db.Integer)
dest_port = db.Column(db.String(255))
next_header = db.Column(db.String(255))
flags = db.Column(db.String(255))
packet_len = db.Column(db.String(255))
comment = db.Column(db.Text)
expires = db.Column(db.DateTime)
created = db.Column(db.DateTime)
action_id = db.Column(db.Integer,
db.ForeignKey('action.id'),
nullable=False)
action = db.relationship('Action', backref='flowspec6')
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
user = db.relationship('User', backref='flowspec6')
rstate_id = db.Column(db.Integer,
db.ForeignKey('rstate.id'),
nullable=False)
rstate = db.relationship('Rstate', backref='flowspec6')
def __init__(self,
source,
source_mask,
source_port,
destination,
destination_mask,
destination_port,
next_header,
flags,
packet_len,
expires,
user_id,
action_id,
created=None,
comment=None,
rstate_id=1):
self.source = source
self.source_mask = source_mask
self.dest = destination
self.dest_mask = destination_mask
self.source_port = source_port
self.dest_port = destination_port
self.next_header = next_header
self.flags = flags
self.packet_len = packet_len
self.comment = comment
self.expires = expires
self.user_id = user_id
self.action_id = action_id
if created is None:
created = datetime.now()
self.created = created
self.rstate_id = rstate_id
def __eq__(self, other):
"""
Two models are equal if all the network parameters equals. User_id and time fields can differ.
:param other: other Flowspec4 instance
:return: boolean
"""
return self.source == other.source and self.source_mask == other.source_mask and self.dest == other.dest \
and self.dest_mask == other.dest_mask and self.source_port == other.source_port \
and self.dest_port == other.dest_port and self.next_header == other.next_header \
and self.flags == other.flags and self.packet_len == other.packet_len \
and self.action_id == other.action_id and self.rstate_id == other.rstate_id
def to_dict(self, prefered_format='yearfirst'):
"""
Serialize to dict
:param prefered_format: string with prefered time format
:returns: dictionary
"""
if prefered_format == 'timestamp':
expires = int(datetime.timestamp(self.expires))
created = int(datetime.timestamp(self.expires))
else:
expires = utils.datetime_to_webpicker(self.expires,
prefered_format)
created = utils.datetime_to_webpicker(self.created,
prefered_format)
return {
"id": str(self.id),
"source": self.source,
"source_mask": self.source_mask,
"source_port": self.source_port,
"dest": self.dest,
"dest_mask": self.dest_mask,
"dest_port": self.dest_port,
"next_header": self.next_header,
"flags": self.flags,
"packet_len": self.packet_len,
"comment": self.comment,
"expires": expires,
"created": created,
"action": self.action.name,
"user": self.user.uuid,
"rstate": self.rstate.description
}
def to_table_source(self):
"""
Serialize to dict
:return: dictionary
"""
s_slash = ''
d_slash = ''
s_mask = ''
d_mask = ''
s_source = self.source
if self.source_mask:
s_slash = '/' if self.source_mask >= 0 else ''
s_mask = self.source_mask if self.source_mask >= 0 else ''
d_source = self.dest
if self.dest_mask:
d_slash = '/' if self.dest_mask >= 0 else ''
d_mask = self.dest_mask if self.dest_mask >= 0 else ''
return {
"id": self.id,
"source": "{}{}{}".format(s_source, s_slash, s_mask),
"source_port": self.source_port,
"dest": "{}{}{}".format(d_source, d_slash, d_mask),
"dest_port": self.dest_port,
"protocol": self.next_header,
"flags": self.flags,
"packet_len": self.packet_len,
"comment": self.comment,
"expires": utils.datetime_to_webpicker(self.expires),
"created": utils.datetime_to_webpicker(self.created),
"action": self.action.name,
"user": self.user.uuid,
"rstate": self.rstate.description
}
class Unit(db.Model):
__tablename__ = 'unit'
id = db.Column('IdUnit', db.Integer, primary_key=True)
name = db.Column('Name', db.String(45))
deviceConsumptionChild = db.relationship(
'DeviceConsumption', backref='unit')
class RTBH(db.Model):
id = db.Column(db.Integer, primary_key=True)
ipv4 = db.Column(db.String(255))
ipv4_mask = db.Column(db.Integer)
ipv6 = db.Column(db.String(255))
ipv6_mask = db.Column(db.Integer)
community_id = db.Column(db.Integer,
db.ForeignKey('community.id'),
nullable=False)
community = db.relationship('Community', backref='rtbh')
comment = db.Column(db.Text)
expires = db.Column(db.DateTime)
created = db.Column(db.DateTime)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
user = db.relationship('User', backref='rtbh')
rstate_id = db.Column(db.Integer,
db.ForeignKey('rstate.id'),
nullable=False)
rstate = db.relationship('Rstate', backref='RTBH')
def __init__(self,
ipv4,
ipv4_mask,
ipv6,
ipv6_mask,
community_id,
expires,
user_id,
comment=None,
created=None,
rstate_id=1):
self.ipv4 = ipv4
self.ipv4_mask = ipv4_mask
self.ipv6 = ipv6
self.ipv6_mask = ipv6_mask
self.community_id = community_id
self.expires = expires
self.user_id = user_id
self.comment = comment
if created is None:
created = datetime.now()
self.created = created
self.rstate_id = rstate_id
def __eq__(self, other):
"""
Two models are equal if all the network parameters equals. User_id and time fields can differ.
:param other: other RTBH instance
:return: boolean
"""
return self.ipv4 == other.ipv4 and self.ipv4_mask == other.ipv4_mask and self.ipv6 == other.ipv6 \
and self.ipv6_mask == other.ipv6_mask and self.community_id == other.community_id \
and self.rstate_id == other.rstate_id
def __ne__(self, other):
"""
Two models are not equal if all the network parameters are not equal. User_id and time fields can differ.
:param other: other RTBH instance
:return: boolean
"""
compars = self.ipv4 == other.ipv4 and self.ipv4_mask == other.ipv4_mask and self.ipv6 == other.ipv6 \
and self.ipv6_mask == other.ipv6_mask and self.community_id == other.community_id \
and self.rstate_id == other.rstate_id
return not compars
def update_time(self, form):
self.expires = utils.webpicker_to_datetime(form.expire_date.data)
db.session.commit()
def to_dict(self, prefered_format='yearfirst'):
"""
Serialize to dict used in API
:param prefered_format: string with prefered time format
:return: dictionary
"""
if prefered_format == 'timestamp':
expires = int(datetime.timestamp(self.expires))
created = int(datetime.timestamp(self.expires))
else:
expires = utils.datetime_to_webpicker(self.expires,
prefered_format)
created = utils.datetime_to_webpicker(self.created,
prefered_format)
return {
"id": self.id,
"ipv4": self.ipv4,
"ipv4_mask": self.ipv4_mask,
"ipv6": self.ipv6,
"ipv6_mask": self.ipv6_mask,
"community": self.community.name,
"comment": self.comment,
"expires": expires,
"created": created,
"user": self.user.uuid,
"rstate": self.rstate.description
}
def to_table_source(self):
"""
Serialize to dict / user for rendering in React table
:return: dictionary
"""
s_source = self.ipv4
s_slash = ''
d_slash = ''
s_mask = ''
d_mask = ''
if self.ipv4_mask:
s_slash = '/' if self.ipv4_mask >= 0 else ''
s_mask = self.ipv4_mask if self.ipv4_mask >= 0 else ''
d_source = self.ipv6
if self.ipv6_mask:
d_slash = '/' if self.ipv6_mask >= 0 else ''
d_mask = self.ipv6_mask if self.ipv6_mask >= 0 else ''
return {
"id": "{}".format(self.id),
"ipv4": "{}{}{}".format(s_source, s_slash, s_mask),
"ipv6": "{}{}{}".format(d_source, d_slash, d_mask),
"community": self.community.name,
"comment": self.comment,
"expires": utils.datetime_to_webpicker(self.expires),
"created": utils.datetime_to_webpicker(self.created),
"user": "******".format(self.user.name),
"rstate": self.rstate.description
}
from sqlalchemy import event
from datetime import datetime
from flowapp import db, utils
# models and tables
user_role = db.Table(
'user_role',
db.Column('user_id', db.Integer, db.ForeignKey('user.id'), nullable=False),
db.Column('role_id', db.Integer, db.ForeignKey('role.id'), nullable=False),
db.PrimaryKeyConstraint('user_id', 'role_id'))
user_organization = db.Table(
'user_organization',
db.Column('user_id', db.Integer, db.ForeignKey('user.id'), nullable=False),
db.Column('organization_id',
db.Integer,
db.ForeignKey('organization.id'),
nullable=False),
db.PrimaryKeyConstraint('user_id', 'organization_id'))
class User(db.Model):
"""
App User
"""
id = db.Column(db.Integer, primary_key=True)
uuid = db.Column(db.String(180), unique=True)
comment = db.Column(db.String(500))
email = db.Column(db.String(255))
name = db.Column(db.String(255))
