class PredictedDataModel(db.Model):
__tablename__ = 'predicted_data'
__table_args__ = {'extend_existing': True}
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
scrape_id = db.Column(db.Integer)
category_id = db.Column(db.Integer)
probability = db.Column(db.Float)
#min_scrape_idより小さいscrape_idのレコードをかえす。Noneのときは頭から５件
def getRecordFromCategoryIDWithJoin(category_id, min_scrape_id):
sql = "select scrape_data.*, predicted_data.category_id, predicted_data.probability from predicted_data join scrape_data on predicted_data.scrape_id = scrape_data.id "
if min_scrape_id is not None:
sql += "where scrape_data.id < %s and " % (min_scrape_id)
else:
sql += "where "
sql += "predicted_data.category_id = %s order by scrape_data.id DESC LIMIT 5" % (
category_id)
print(sql)
val = db.session.execute(sql)
result = []
for row in val:
d = dict(row)
result.append(d)
return result
class UserRole(db.Model):
__tablename__ = "UserRole"
Id = db.Column(db.Integer, primary_key=True)
Name = db.Column(db.String, nullable=False, unique=True)
Users = db.relationship('User', backref='UserRole', lazy=True)
def __repr__(self):
return '<Role: %s>' % str(self.Id)
class AgentTypeOperatingSystem(db.Model):
__tablename__ = "AgentTypeOperatingSystem"
Id = db.Column(db.Integer, primary_key=True)
Name = db.Column(db.String)
AgentTypeId = db.Column(db.Integer, db.ForeignKey('AgentType.Id'), nullable=False)
Payloads = db.relationship('Payload', backref='AgentTypeOperatingSystem', lazy=True)
def __repr__(self):
if self.Name:
return '<AgentTypeOperatingSystem: %s>' % self.Name
else:
return '<AgentTypeOperatingSystem: %s>' % str(self.Id)
class Session(db.Model):
__tablename__ = 'session'
id = db.Column(UUIDType(), primary_key=True)
uid = db.Column(UUIDType(), db.ForeignKey('user.id'), nullable=False)
user = db.relationship('User', backref=db.backref('sessions', lazy=True))
expire = db.Column(db.DateTime(), nullable=False)
@staticmethod
def new_session(username, expire=None) -> 'Session':
if expire == None:
expire = datetime.now() + timedelta(hours=3)
if type(expire) in (int, float):
expire = datetime.fromtimestamp(expire)
user = User.get_by_username(username)
return Session(
id=uuid4(),
user=user,
expire=expire,
)
@staticmethod
def get_by_id(id) -> 'Session':
if type(id) != UUID:
try:
id = UUID(id)
except:
raise AttributeError("Invalid UUID")
query = Session.query.filter_by(id=id)
if query.count() == 0:
raise ValueError(f"Session {str(id)} not found.")
session = query.first()
if session.expired():
db.session.delete(session)
db.session.commit()
raise ValueError(f"Session {str(id)} not found.")
return session
def json(self) -> dict:
return {
"id": str(self.id),
"user": self.user.json(),
"expire": int(self.expire.timestamp()),
}
def expired(self) -> bool:
return self.expire < datetime.now()
class FactionFile(db.Model):
__tablename__ = "FactionFile"
Id = db.Column(db.Integer, primary_key=True)
Name = db.Column(db.String)
Hash = db.Column(db.String)
HashMatch = db.Column(db.Boolean)
UserId = db.Column(db.Integer, db.ForeignKey('User.Id'))
AgentId = db.Column(db.Integer, db.ForeignKey('Agent.Id'))
Created = db.Column(db.DateTime)
LastDownloaded = db.Column(db.DateTime)
Visible = db.Column(db.Boolean)
def __repr__(self):
return '<FactionFile: %s>' % str(self.Id)
class Command(db.Model):
__tablename__ = "Command"
Id = db.Column(db.Integer, primary_key=True)
Name = db.Column(db.String)
Description = db.Column(db.String)
Help = db.Column(db.String)
MitreReference = db.Column(db.String)
OpsecSafe = db.Column(db.Boolean)
ModuleId = db.Column(db.Integer, db.ForeignKey('Module.Id'))
AgentTypeId = db.Column(db.Integer, db.ForeignKey('AgentType.Id'))
Parameters = db.relationship('CommandParameter', backref='Command', lazy=True)
class LaptimeModel(db.Model):
__tablename__ = 'laptime'
lap_id = db.Column(UUID(as_uuid=True),
primary_key=True,
default=uuid.uuid4,
unique=True,
nullable=False)
session_id = db.Column(UUID(as_uuid=True),
db.ForeignKey('session.session_id'),
nullable=False)
lap_no = db.Column(db.Integer, nullable=False)
valid = db.Column(db.Boolean, nullable=False, default=True)
track_layout = db.Column(db.String, nullable=False, default="A")
laptime_seconds = db.Column(db.Float, nullable=False)
sectors = db.Column(JSON, nullable=False)
datetime_created = db.Column(db.DateTime,
nullable=False,
default=db.utcnow)
datetime_last_modified = db.Column(db.DateTime,
nullable=False,
default=db.utcnow,
onupdate=db.utcnow)
datetime_display = db.Column(db.DateTime,
nullable=False,
default=db.utcnow)
session = db.relationship(
'SessionModel',
backref=db.backref('laptimes',
order_by='LaptimeModel.datetime_display.asc()'))
def __repr__(self):
return f"Lap time[" \
f"'{self.lap_id}': (" \
f"'{self.datetime_display}', " \
f"training_id: '{self.training_id}', " \
f"bike_id: '{self.bike_id}', " \
f"setup_id: '{self.setup_id}', " \
f")]"
class Transport(db.Model):
__tablename__ = "Transport"
Id = db.Column(db.Integer, primary_key=True)
Name = db.Column(db.String)
TransportType = db.Column(db.String)
Guid = db.Column(db.String)
Created = db.Column(db.DateTime)
LastCheckin = db.Column(db.DateTime)
Configuration = db.Column(db.String)
ApiKeyId = db.Column(db.Integer,
db.ForeignKey('ApiKey.Id'),
nullable=False)
Enabled = db.Column(db.Boolean)
Visible = db.Column(db.Boolean)
Agents = db.relationship('Agent', backref='Transport', lazy=True)
Payloads = db.relationship('Payload', backref='Transport', lazy=True)
def __repr__(self):
return '<Transport: %s>' % str(self.Id)
class SessionModel(db.Model):
__tablename__ = 'session'
session_id = db.Column(UUID(as_uuid=True),
primary_key=True,
default=uuid.uuid4,
unique=True,
nullable=False)
training_id = db.Column(UUID(as_uuid=True),
db.ForeignKey('training.training_id'),
nullable=False)
setup_id = db.Column(UUID(as_uuid=True),
db.ForeignKey('setup.setup_id'),
nullable=True)
bike_id = db.Column(UUID(as_uuid=True),
db.ForeignKey('bike.bike_id'),
nullable=True)
application = db.Column(db.String(50), nullable=True)
datetime_created = db.Column(db.DateTime,
nullable=False,
default=db.utcnow)
datetime_last_modified = db.Column(db.DateTime,
nullable=False,
default=db.utcnow,
onupdate=db.utcnow)
datetime_display = db.Column(db.DateTime,
nullable=False,
default=db.utcnow)
bike = db.relationship('BikeModel',
backref=db.backref(
'sessions',
order_by='SessionModel.datetime_display.asc()'))
training = db.relationship(
'TrainingModel',
backref=db.backref('sessions',
order_by='SessionModel.datetime_display.asc()'))
setup = db.relationship(
'SetupModel',
backref=db.backref('sessions',
order_by='SessionModel.datetime_display.asc()'))
def __repr__(self):
return f"Session[" \
f"'{self.session_id}': (" \
f"'{self.datetime_display}', " \
f"training_id: '{self.training_id}', " \
f"bike_id: '{self.bike_id}', " \
f"setup_id: '{self.setup_id}', " \
f")]"
class CommandParameter(db.Model):
__tablename__ = "CommandParameter"
Id = db.Column(db.Integer, primary_key=True)
Name = db.Column(db.String)
Help = db.Column(db.String)
Required = db.Column(db.Boolean)
Position = db.Column(db.Integer)
Values = db.Column(db.String)
CommandId = db.Column(db.Integer, db.ForeignKey('Command.Id'))
class HistoryModel(db.Model):
__tablename__ = 'history'
history_id = db.Column(UUID(as_uuid=True),
primary_key=True,
default=uuid.uuid4,
unique=True,
nullable=False)
maintenance_id = db.Column(UUID(as_uuid=True),
db.ForeignKey('maintenance.maintenance_id'),
nullable=False)
bike_id = db.Column(UUID(as_uuid=True),
db.ForeignKey('bike.bike_id'),
nullable=False)
operating_hours = db.Column(db.Float, nullable=False)
comment = db.Column(db.Text, nullable=True)
tags = db.Column(ARRAY(db.String), nullable=True)
datetime_created = db.Column(db.DateTime,
nullable=False,
default=db.utcnow)
datetime_last_modified = db.Column(db.DateTime,
nullable=False,
default=db.utcnow,
onupdate=db.utcnow)
datetime_display = db.Column(db.DateTime,
nullable=False,
default=db.utcnow)
bike = db.relationship('BikeModel', backref=db.backref('history'))
maintenance = db.relationship(
'MaintenanceModel',
backref=db.backref('history',
order_by='HistoryModel.operating_hours.desc()'))
def __repr__(self):
return f"History[" \
f"'{self.history_id}': (" \
f"'{self.operating_hours}', " \
f"'{self.comment}', " \
f"'{self.datetime_display}', " \
f"mtn_id: '{self.maintenance_id}'," \
f"bike_id: '{self.bike_id}'" \
f")]"
class TrainingModel(db.Model):
__tablename__ = 'training'
training_id = db.Column(UUID(as_uuid=True),
primary_key=True,
default=uuid.uuid4,
unique=True,
nullable=False)
location = db.Column(db.String, nullable=False)
weather_hourly = db.Column(JSON, nullable=True)
datetime_created = db.Column(db.DateTime,
nullable=False,
default=db.utcnow)
datetime_last_modified = db.Column(db.DateTime,
nullable=False,
default=db.utcnow,
onupdate=db.utcnow)
datetime_display = db.Column(db.DateTime,
nullable=False,
default=db.utcnow)
def __repr__(self):
return f"Training[" \
f"'{self.training_id}': (" \
f"'{self.location}', " \
f"'{self.datetime_display}'" \
f")]"
class Module(db.Model):
__tablename__ = "Module"
Id = db.Column(db.Integer, primary_key=True)
Name = db.Column(db.String)
Description = db.Column(db.String)
BuildCommand = db.Column(db.String)
BuildLocation = db.Column(db.String)
LanguageId = db.Column(db.Integer, db.ForeignKey('Language.Id'))
class ConsoleMessage(db.Model):
__tablename__ = "ConsoleMessage"
Id = db.Column(db.Integer, primary_key=True)
AgentId = db.Column(db.Integer, db.ForeignKey('Agent.Id'), nullable=False)
AgentTaskId = db.Column(db.Integer,
db.ForeignKey('AgentTask.Id'),
nullable=False)
UserId = db.Column(db.Integer, db.ForeignKey('User.Id'), nullable=False)
Type = db.Column(db.String)
Content = db.Column(db.String)
Display = db.Column(db.String)
Received = db.Column(db.DateTime)
def __repr__(self):
return '<ConsoleCommand: %s>' % str(self.Id)
class MaintenanceModel(db.Model):
__tablename__ = 'maintenance'
maintenance_id = db.Column(UUID(as_uuid=True),
primary_key=True,
default=uuid.uuid4,
unique=True,
nullable=False)
bike_id = db.Column(UUID(as_uuid=True),
db.ForeignKey('bike.bike_id'),
nullable=False)
category = db.Column(db.String(20), nullable=False)
name = db.Column(db.String(100), unique=True, nullable=False)
interval_type = db.Column(db.String(25), nullable=False)
interval_amount = db.Column(db.Float, nullable=True)
interval_unit = db.Column(db.String(25), nullable=True)
tags_default = db.Column(ARRAY(db.String), nullable=True)
datetime_created = db.Column(db.DateTime,
nullable=False,
default=db.utcnow)
datetime_last_modified = db.Column(db.DateTime,
nullable=False,
default=db.utcnow,
onupdate=db.utcnow)
def __repr__(self):
return f"Maintenance[" \
f"'{self.maintenance_id}': (" \
f"'{self.category}', " \
f"'{self.name}', " \
f"'{self.interval_amount}', " \
f"'{self.interval_unit}', " \
f"'{self.interval_type}'" \
f")]"
class StagingResponse(db.Model):
__tablename__ = "StagingResponse"
Id = db.Column(db.Integer, primary_key=True)
AgentId = db.Column(db.Integer, db.ForeignKey('Agent.Id'), nullable=False)
StagingMessageId = db.Column(db.Integer,
db.ForeignKey('StagingMessageId.Id'),
nullable=False)
IV = db.Column(db.String)
HMAC = db.Column(db.String)
Message = db.Column(db.String)
Sent = db.Column(db.Boolean)
def __repr__(self):
return '<StagingResponse: %s>' % str(self.Id)
class AgentCheckin(db.Model):
__tablename__ = "AgentCheckin"
Id = db.Column(db.Integer, primary_key=True)
AgentId = db.Column(db.Integer, db.ForeignKey('Agent.Id'))
IV = db.Column(db.String)
HMAC = db.Column(db.String)
Message = db.Column(db.String)
Received = db.Column(db.DateTime)
def __repr__(self):
return '<AgentCheckin: %s>' % str(self.Id)
class CoachModel(db.Model):
__tablename__ = 'coach'
coach_id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, unique=True, nullable=False)
category = db.Column(db.String(6), nullable=False)
symptom = db.Column(db.JSON, nullable=False)
notes = db.Column(db.Text, nullable=True)
questions = db.Column(ARRAY(db.String), nullable=True, default=[])
advice = db.Column(JSON, nullable=False)
datetime_created = db.Column(db.DateTime, nullable=False, default=db.utcnow)
datetime_last_modified = db.Column(db.DateTime, nullable=False, default=db.utcnow, onupdate=db.utcnow)
def __repr__(self):
return f"Coach[" \
f"'{self.coach_id}': (" \
f"'{self.category}', " \
f"'{self.symptom}'" \
f")]"
class SparepartModel(db.Model):
__tablename__ = 'sparepart'
sparepart_id = db.Column(UUID(as_uuid=True),
primary_key=True,
default=uuid.uuid4,
unique=True,
nullable=False)
name = db.Column(db.String, nullable=False)
items = db.relationship('SparepartitemModel',
backref=db.backref('sparepart'))
module = db.Column(db.String, nullable=False)
min_stock = db.Column(db.Integer, nullable=True)
current_stock = db.column_property(
db.select([func.sum(SparepartitemModel.stock)
]).where(SparepartitemModel.sparepart_id ==
sparepart_id).correlate_except(SparepartitemModel))
datetime_created = db.Column(db.DateTime,
nullable=False,
default=db.utcnow)
datetime_last_modified = db.Column(db.DateTime,
nullable=False,
default=db.utcnow,
onupdate=db.utcnow)
datetime_display = db.Column(db.DateTime,
nullable=False,
default=db.utcnow)
def __repr__(self):
return f"Sparepart[" \
f"'{self.sparepart_id}': (" \
f"'{self.module}', " \
f"'{self.alarm}', " \
f"'{self.datetime_display}'" \
f")]"
