class Bypass(Base):
__tablename__= "bypasses"
id = Column(Integer, Sequence("bypass_seq"), primary_key=True)
name = Column(String(255), unique=True)
code = Column(Text)
created_at = Column(UtcDateTime, nullable=False, default=utcnow())
updated_at = Column(UtcDateTime, default=utcnow(), onupdate=utcnow(), nullable=False)
class BaseSQLModel(object):
"""Base model for SQLAlchemy."""
id = Column('id', Integer, primary_key=True)
created = Column(UtcDateTime(), default=utcnow())
modified = Column(UtcDateTime(), default=utcnow(), onupdate=utcnow())
is_enabled = Column(Boolean(), default=True)
is_deleted = Column(Boolean(), default=False)
class Collection(db.Model):
"""Table for storing references to various media"""
__tablename__ = 'collection'
id = Column(Integer, primary_key=True)
title = Column(String(64, convert_unicode=True), nullable=False)
username = Column(String(16, convert_unicode=True), nullable=False)
notes = Column(Text)
archived = Column(Boolean, default=False)
created_at = Column(UtcDateTime(), default=utcnow())
updated_at = Column(UtcDateTime(), onupdate=utcnow())
medias = relationship("Media", secondary="collection_media")
def toJSON(self):
return {
'id': self.id,
'title': self.title,
'username': self.username,
'notes': self.notes,
'archived': self.archived,
'created_at': self.created_at,
'updated_at': self.updated_at,
}
def toFullJSON(self):
return {
'id': self.id,
'title': self.title,
'username': self.username,
'notes': self.notes,
'archived': self.archived,
'created_at': self.created_at,
'updated_at': self.updated_at,
'media': [media.toJSON() for media in self.medias],
}
def commit_request(request_id: uuid.UUID):
req = session.query(Request).get(request_id)
if not req:
return error('object_not_found', f'Request "{request_id}" not found',
404)
if req.committed:
return error('request_already_committed',
f'Request "{request_id}" has been already committed.',
400)
if isinstance(req, CreationRequest):
be = req.create()
req.committed_at = utcnow()
session.add(be)
session.commit()
return success(business_entity=serialize(be))
elif isinstance(req, MarkAsDuplicateRequest):
ber = req.mark_as_duplicate()
req.committed_at = utcnow()
session.add(ber)
session.commit()
return success(business_entity=serialize(req.business_entity))
elif isinstance(req, RevisionRequest):
ber = req.revise()
req.committed_at = utcnow()
session.add(ber)
session.commit()
return success(business_entity=serialize(ber.business_entity))
elif isinstance(req, BlockUserRequest):
req.block()
req.committed_at = utcnow()
session.commit()
return success(user=serialize(req.blocking_user))
else:
return error('invalid_request',
f'Request {req} is not a valid request.', 400)
class Tasking(Base):
__tablename__ = 'taskings'
id = Column(Integer, primary_key=True)
agent_id = Column(String(255),
ForeignKey('agents.session_id'),
primary_key=True)
agent = relationship(Agent, lazy="joined", innerjoin=True)
input = Column(Text)
input_full = deferred(Column(Text))
output = Column(Text, nullable=True)
# In most cases, this isn't needed and will match output. However, with the filter feature, we want to store
# a copy of the original output if it gets modified by a filter.
original_output = deferred(Column(Text, nullable=True))
user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
user = relationship(User)
created_at = Column(UtcDateTime, default=utcnow(), nullable=False)
updated_at = Column(UtcDateTime,
default=utcnow(),
onupdate=utcnow(),
nullable=False)
module_name = Column(Text)
task_name = Column(Text)
status = Column(Enum(TaskingStatus))
def __repr__(self):
return "<Tasking(id='%s')>" % (self.id)
class Profile(Base):
__tablename__ = "profiles"
name = Column(String(255), primary_key=True)
file_path = Column(String(255))
category = Column(String(255))
data = Column(Text, nullable=False)
created_at = Column(UtcDateTime, nullable=False, default=utcnow())
updated_at = Column(UtcDateTime, default=utcnow(), onupdate=utcnow(), nullable=False)
def list_current_events(db: Session = Depends(get_db),
current_user: User = Depends(get_current_user)):
res: List[models.Event] = db \
.query(models.Event) \
.filter(models.Event.start_date < utcnow()) \
.filter(models.Event.end_date > utcnow()) \
.all()
return res
class TimeMixin(object):
"""A mixin that adds ``created_at`` and ``updated_at`` columns to any declarative-mapped class."""
__table_args__ = {'extend_existing': True}
created_at = Column(UtcDateTime(), nullable=False, default=utcnow())
updated_at = Column(UtcDateTime(),
nullable=False,
default=utcnow(),
onupdate=utcnow())
class User(Base):
__tablename__ = 'users'
id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
username = Column(String(255), nullable=False)
password = Column(String(255), nullable=False)
api_token = Column(String(50))
last_logon_time = Column(UtcDateTime, default=utcnow(), onupdate=utcnow())
enabled = Column(Boolean, nullable=False)
admin = Column(Boolean, nullable=False)
notes = Column(Text)
def __repr__(self):
return "<User(username='******')>" % (self.username)
class Agent(Base):
__tablename__ = 'agents'
id = Column(Integer, Sequence("agent_id_seq"), primary_key=True)
name = Column(String(255), nullable=False)
host_id = Column(Integer, ForeignKey('hosts.id'))
host = relationship(Host, lazy="joined")
listener = Column(String(255), nullable=False)
session_id = Column(String(255), nullable=False, unique=True)
language = Column(String(255))
language_version = Column(String(255))
delay = Column(Integer)
jitter = Column(Float)
external_ip = Column(String(255))
internal_ip = Column(String(255))
username = Column(Text)
high_integrity = Column(Boolean)
process_name = Column(Text)
process_id = Column(Integer)
hostname = Column(String(255))
os_details = Column(String(255))
session_key = Column(String(255))
nonce = Column(String(255))
checkin_time = Column(UtcDateTime, default=utcnow())
lastseen_time = Column(UtcDateTime, default=utcnow(), onupdate=utcnow())
parent = Column(String(255))
children = Column(String(255))
servers = Column(String(255))
profile = Column(String(255))
functions = Column(String(255))
kill_date = Column(String(255))
working_hours = Column(String(255))
lost_limit = Column(Integer)
notes = Column(Text)
architecture = Column(String(255))
killed = Column(Boolean, nullable=False)
proxy = Column(PickleType)
@hybrid_property # todo @stale.expression
def stale(self):
return is_stale(self.lastseen_time, self.delay, self.jitter)
def __repr__(self):
return "<Agent(name='%s')>" % (
self.name)
def __getitem__(self, key):
return self.__dict__[key]
def __setitem__(self, key, value):
self.__dict__[key] = value
class Upload(db.Model):
"""Table for storing references to various media"""
__tablename__ = 'uploads'
id = Column(Integer, primary_key=True)
sha256 = Column(HashColumn(32), nullable=False)
ext = Column(String(4, convert_unicode=True), nullable=False)
username = Column(String(16, convert_unicode=True), nullable=False)
created_at = Column(UtcDateTime(), default=utcnow())
def toJSON(self):
return {
'id': self.id,
'sha256': self.sha256,
'ext': self.ext,
'username': self.username,
'url': self.url(),
'created_at': self.created_at,
}
def filename(self):
return "{}{}".format(self.sha256, self.ext)
def filepath(self):
return join(app_cfg.DIR_UPLOADS, sha256_tree(self.sha256))
def fullpath(self):
return join(self.filepath(), self.filename())
def url(self):
return join(app_cfg.URL_UPLOADS, sha256_tree(self.sha256),
self.filename())
class OAuthSession(Base):
id = Column(String, primary_key=True)
secret = Column(String, nullable=False)
created_at = Column(UtcDateTime, nullable=False, default=utcnow())
__tablename__ = 'oauth_session'
class BusinessEntity(Base):
id = Column(UUIDType, primary_key=True, default=uuid.uuid4)
latest_revision_id = Column(UUIDType,
ForeignKey('business_entity_revision.id'),
unique=True)
latest_revision = relationship('BusinessEntityRevision',
uselist=False,
lazy='joined',
post_update=True,
foreign_keys=latest_revision_id,
backref=backref('business_entity',
uselist=False))
first_revision_id = Column(UUIDType,
ForeignKey('business_entity_revision.id'))
first_revision = relationship('BusinessEntityRevision',
uselist=False,
foreign_keys=first_revision_id,
post_update=True)
created_at = Column(UtcDateTime,
nullable=False,
default=utcnow(),
index=True)
__tablename__ = 'business_entity'
class model(TableBase):
idmodel = Column(Integer,
primary_key=True,
autoincrement=True,
nullable=False)
idmodel_set = Column(Integer,
ForeignKey('model_set.idmodel_set',
onupdate='RESTRICT',
ondelete='CASCADE'),
nullable=False)
idalgorithm = Column(Integer,
ForeignKey('algorithm.idalgorithm',
onupdate='RESTRICT',
ondelete='CASCADE'),
nullable=False)
idmodel_status = Column(Integer,
ForeignKey('model_status.idmodel_status',
onupdate='RESTRICT',
ondelete='RESTRICT'),
nullable=False)
insert_time = Column(UtcDateTime(timezone=True),
default=utcnow(),
nullable=False)
uuid = Column(String(34), nullable=False)
hyperparameters = Column(String(STRING_LENGTH), nullable=False)
trained_time = Column(UtcDateTime(timezone=True), nullable=True)
trained_package = Column(LargeBinary(STRING_LENGTH), nullable=True)
__table_args__ = (UniqueConstraint('uuid'), Index('idxmodel_uuid', 'uuid'))
class User(Base):
id = Column(UUIDType, primary_key=True, default=uuid.uuid4)
display_name = Column(Unicode, nullable=False)
admin = Column(Boolean, nullable=False, default=False)
created_at = Column(UtcDateTime,
nullable=False,
default=utcnow(),
index=True)
blocked_at = Column(UtcDateTime)
@hybrid_property
def blocked(self) -> bool:
return self.blocked_at is not None
@blocked.expression
def blocked(cls):
return cls.blocked_at.isnot(null())
def is_authenticated(self):
return True
def is_active(self):
return not self.blocked
def is_anonymous(self):
return False
def get_id(self):
return str(self.id)
__tablename__ = 'user'
def fake_inventory_generator(length, faker):
for x in range(length):
yield Inventory(item_name='{} ({})'.format(faker.word(),
faker.color_name()),
price=faker.random_int(100, 99900),
qty=faker.random_int(0, 1000),
date=utcnow())
class Comment(Base):
id = Column(UUID(as_uuid=True), default=uuid4, primary_key=True)
author_id = Column(UUID(as_uuid=True), nullable=False)
target_id = Column(UUID(as_uuid=True), nullable=False, )
created_at = Column(UtcDateTime(), nullable=False, default=utcnow())
comment = Column(JSON(), nullable=False)
__tablename__ = 'comments'
def fx_order(fx_user, fx_inventory):
return Order(user_id=fx_user.user_id,
item_id=fx_inventory.item_id,
item_qty=fake.random_int(1, 100),
date=utcnow(),
deliver_phone=fake.phone_number(),
deliver_address=fake.address(),
total_price=fake.random_int(100, 1000000))
def fx_user():
return User(user_id=fake.user_name(),
password=fake.password(),
email=fake.email(),
first_name=fake.first_name(),
last_name=fake.last_name(),
phone=fake.phone_number(),
address=fake.address(),
date=utcnow())
class FeatureType(db.Model):
"""Table for referencing feature indexes"""
__tablename__ = 'feature_type'
id = Column(Integer, primary_key=True)
active = Column(Boolean, default=True)
modelzoo_name = Column(String(64, convert_unicode=True),
nullable=False,
unique=True)
index_type = Column(String(16, convert_unicode=True),
nullable=False) # faiss or annoy
username = Column(String(16, convert_unicode=True))
created_at = Column(UtcDateTime(), default=utcnow())
updated_at = Column(UtcDateTime(), onupdate=utcnow())
process_id = Column(Integer, default=0)
processed_at = Column(UtcDateTime(), nullable=True)
index_id = Column(Integer, default=0)
indexed_at = Column(UtcDateTime(), nullable=True)
index_settings = Column(Text, nullable=True)
def toJSON(self):
return {
'id': self.id,
'active': self.active,
'modelzoo_name': self.modelzoo_name,
'username': self.username,
'index_type': self.index_type, # faiss, annoy
'created_at': self.created_at,
'processed_at': self.processed_at,
'process_id': self.process_id,
'indexed_at': self.indexed_at,
'index_id': self.index_id,
'index_settings':
self.index_settings, # a JSON blob with e.g. factory type
}
def get_recipe(self, type):
try:
data = json.loads(self.index_settings)
except Exception as e:
print(e)
return {}
if type in data:
return data[type]
return {}
class Reporting(Base):
__tablename__ = 'reporting'
id = Column(Integer, Sequence("reporting_id_seq"), primary_key=True)
name = Column(String(255), nullable=False)
event_type = Column(String(255))
message = Column(Text)
timestamp = Column(UtcDateTime, default=utcnow(), nullable=False)
taskID = Column(Integer, ForeignKey('taskings.id'))
def __repr__(self):
return "<Reporting(id='%s')>" % (self.id)
class Media(db.Model):
"""Table for storing references to various media"""
__tablename__ = 'media'
id = Column(Integer, primary_key=True)
# parent_id = Column(Integer, ForeignKey('media.id'), nullable=True)
mediaType = Column(MediaTypeColumn(), nullable=False)
sha256 = Column(HashColumn(32), nullable=False)
ext = Column(String(4, convert_unicode=True), nullable=False)
frame = Column(Integer, nullable=True)
created_at = Column(UtcDateTime(), default=utcnow())
# children = relationship("Media")
media_features = relationship("MediaFeature")
media_metadata = relationship("MediaMetadata")
def toJSON(self):
return {
'id': self.id,
# 'parent_id': self.parent_id,
'mediaType': self.mediaType,
'sha256': self.sha256,
'frame': self.frame,
'ext': self.ext,
'url': self.url(),
'created_at': self.created_at,
}
def toFullJSON(self):
siblings = db.session.query(Media).filter(
Media.sha256 == self.sha256).all()
return {
'el': self.toJSON(),
'siblings': [el.toJSON() for el in siblings],
}
def filename(self):
if self.mediaType == 'video_frame':
return "{}_{:03d}{}".format(self.sha256, self.frame, self.ext)
return "{}{}".format(self.sha256, self.ext)
def filetree(self):
return sha256_tree(self.sha256)
def filepath(self):
return join(app_cfg.DIR_MEDIA, self.filetree())
def fullpath(self):
return join(self.filepath(), self.filename())
def archivepath(self):
return join('media', self.filename())
def url(self):
return join(app_cfg.URL_MEDIA, self.filetree(), self.filename())
def fake_user_generator(length, faker):
for x in range(length):
yield User(user_id='{}{}'.format(faker.user_name(),
faker.random_int(0, 1000)),
password=faker.password(),
email='{}{}'.format(faker.random_int(0, 1000),
faker.email()),
first_name=faker.first_name(),
last_name=faker.last_name(),
phone=faker.phone_number(),
address=faker.address(),
date=utcnow())
class Tasking(Base):
__tablename__ = 'taskings'
id = Column(Integer, primary_key=True)
agent = Column(String(255),
ForeignKey('agents.session_id'),
primary_key=True)
input = Column(Text)
input_full = deferred(Column(Text))
output = Column(Text, nullable=True)
user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
user = relationship(User)
created_at = Column(UtcDateTime, default=utcnow(), nullable=False)
updated_at = Column(UtcDateTime,
default=utcnow(),
onupdate=utcnow(),
nullable=False)
module_name = Column(Text)
task_name = Column(Text)
status = Column(Enum(TaskingStatus))
def __repr__(self):
return "<Tasking(id='%s')>" % (self.id)
class Throwup(Base):
__tablename__ = "throwups"
id = Column("id", Integer, primary_key=True, index=True)
user_id = Column("user_id",
Integer,
ForeignKey("users.id"),
nullable=False)
timestamp = Column("timestamp", UtcDateTime(), default=utcnow())
event_id = Column("event_id",
Integer,
ForeignKey("events.id"),
nullable=False)
class Drink(Base):
__tablename__ = "drinks"
id = Column("id", Integer, primary_key=True, index=True)
drink = Column("drink", String)
timestamp = Column("timestamp", UtcDateTime(), default=utcnow())
user_id = Column("user_id",
Integer,
ForeignKey("users.id"),
nullable=False)
event_id = Column("event_id",
Integer,
ForeignKey("events.id"),
nullable=False)
class widget(TableBase):
idwidget = Column(Integer,
primary_key=True,
autoincrement=True,
nullable=False)
iddatasource = Column(Integer,
ForeignKey('datasource.iddatasource',
onupdate='RESTRICT',
ondelete='CASCADE'),
nullable=False)
insert_time = Column(UtcDateTime(timezone=True),
default=utcnow(),
nullable=False)
uuid = Column(String(STRING_LENGTH), nullable=False)
__table_args__ = (UniqueConstraint('iddatasource', 'uuid'),
Index('idxwidget_idatasource_uuid', 'iddatasource',
'uuid'),
Index('idxwidget_iddatasource', 'iddatasource'))
class Frog(db.Model):
__tablename__ = 'frogs'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50), unique=False, nullable=False)
food = db.Column(db.Integer, nullable=False, default=0)
cleanliness = db.Column(db.Integer, nullable=False, default=0)
money = db.Column(db.Integer, nullable=False, default=0)
image_id = db.Column(db.Integer,
db.ForeignKey('images.id'),
nullable=False)
owner_id = db.Column(db.Integer, db.ForeignKey('users.id'))
last_request = db.Column(UtcDateTime(), default=utcnow(), nullable=False)
image = db.relationship("Image", backref=db.backref("frog", uselist=False))
def __repr__(self):
return f'<Frog {self.id}; {self.name}>'
class CollectionMedia(db.Model):
"""Table for storing references to various media"""
__tablename__ = 'collection_media'
collection_id = Column(Integer,
ForeignKey('collection.id'),
primary_key=True)
media_id = Column(Integer, ForeignKey('media.id'), primary_key=True)
username = Column(String(16, convert_unicode=True), nullable=False)
created_at = Column(UtcDateTime(), default=utcnow())
media = relationship("Media")
def toJSON(self):
return {
'collection_id': self.collection_id,
'media_id': self.media_id,
'username': self.username,
'created_at': self.created_at,
}
class Listener(Base):
__tablename__ = 'listeners'
id = Column(Integer, Sequence("listener_id_seq"), primary_key=True)
name = Column(String(255), nullable=False, unique=True)
module = Column(String(255), nullable=False)
listener_type = Column(String(255), nullable=True)
listener_category = Column(String(255), nullable=False)
enabled = Column(Boolean, nullable=False)
options = Column(PickleType) # Todo Json?
created_at = Column(UtcDateTime, nullable=False, default=utcnow())
def __repr__(self):
return "<Listener(name='%s')>" % (self.name)
def __getitem__(self, key):
return self.__dict__[key]
def __setitem__(self, key, value):
self.__dict__[key] = value
import datetime
from pytest import yield_fixture
from sqlalchemy import Column, MetaData, Table, select
from sqlalchemy_utc import UtcDateTime, utc, utcnow
TABLE = Table(
'test_table', MetaData(),
Column('time', UtcDateTime, default=utcnow()))
@yield_fixture
def fx_connection(fx_engine):
connection = fx_engine.connect()
try:
transaction = connection.begin()
try:
TABLE.create(connection)
yield connection
finally:
transaction.rollback()
finally:
connection.close()
def test_utcnow_timezone(fx_connection):
fx_connection.execute(TABLE.insert(), [{}])
rows = fx_connection.execute(select([TABLE])).fetchall()
