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のときは頭から5件 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")]"