class ServiceEvent(Base): __tablename__ = 'serviceEvent' id = Column(u'ServiceID', INTEGER(), primary_key=True) host_id = Column(u'HostID', INTEGER(), ForeignKey('hosts.HostID', ondelete='cascade')) app_id = Column( u'app_id', SMALLINT(), ForeignKey('app_definitions.AppID'), ) # This is deliberately _not_ linked to the hosts table; we want to record # the hostname at the time of the event and not have it change later. hostname = Column(String(length=30)) username = Column(String(length=30)) control_host = Column(String(length=30)) event = Column(Enum(u'RMA', u'commission', u'decommission')) service_status = Column(u'serviceStatus', String(length=100)) comments = Column(TEXT()) service_date = Column(u'serviceDate', TIMESTAMP(), nullable=False, default=func.current_timestamp(), onupdate=func.current_timestamp(), server_onupdate=func.current_timestamp())
def add_analysis(self, rid, aliquot, step, irradpos, runtype, **kw): if isinstance(aliquot, int): aliquot = '{:02d}'.format(aliquot) # query the IrradiationPositionTable irradpos = self.get_irradiation_position(irradpos, ) params = dict(RID=rid, Aliquot=aliquot, Aliquot_pychron=int(aliquot), RunDateTime=func.current_timestamp(), LoginSessionID=1, SpecRunType=runtype, Increment=step) # IrradPosition cannot be null if irradpos is not None: ip = irradpos.IrradPosition sa = irradpos.SampleID else: ip = -2 sa = 0 params['RedundantSampleID'] = sa params['IrradPosition'] = ip params.update(kw) analysis = AnalysesTable(**params) self._add_item(analysis, ) return analysis
class ResultsMixin(BaseMixin): timestamp = Column(DateTime, default=func.current_timestamp()) @declared_attr def path(self): name = self.__name__.replace('Table', 'PathTable') return relationship(name, uselist=False)
def _generate_mapping(table): """Generate an ORM mapping class from an entry in table_schemas. """ name = table.capitalize() schema = table_schemas[table] table_args = {} if isinstance(schema[0], str): table_args['comment'] = schema[0] schema = schema[1:] props = { '__tablename__': table, '__table_args__': table_args, 'id': Column(Integer, primary_key=True), 'time_created': Column(DateTime, default=func.now()), 'time_modified': Column(DateTime, onupdate=func.current_timestamp()), 'meta': Column(JSONB), } for column in schema: colname, coltype = column[:2] kwds = {} if len(column) < 4 else column[3] kwds['comment'] = None if len(column) < 3 else column[2] if coltype not in _coltypes: if not coltype.endswith('.id'): raise ValueError("Unrecognized column type %s" % coltype) props[colname] = Column(Integer, ForeignKey(coltype), **kwds) else: ctyp = _coltypes[coltype] props[colname] = Column(ctyp, **kwds) return type(name, (ORMBase, ), props)
class Trial(ORMBase): __tablename__ = 'trials' id = Column(Integer, primary_key=True) experiment_id = Column(Integer, ForeignKey('experiments.id'), nullable=False) run_number = Column(Integer, nullable=False) outcome = Column(Float, nullable=False) parameter_configs = relationship('ParameterConfig') timestamp = Column(TIMESTAMP, server_default=func.now(), onupdate=func.current_timestamp()) obj_parameters = Column(JSON, nullable=False) def __repr__(self): return ( f'Trial(' f'experiment_id={self.experiment_id}, run_number={self.run_number}, outcome={self.outcome}, ' f'timestamp={self.timestamp!r}, parameter_configs={self.parameter_configs!r}), ' f'objective_parameters={self.obj_parameters!r}' ) def asdict(self): return { 'experiment_id': self.experiment_id, 'run_number': self.run_number, 'outcome': self.outcome, 'parameter_configs': [config.asdict() for config in self.parameter_configs], 'timestamp': self.timestamp, 'obj_parameters': self.obj_parameters }
def add_analysis(self, rid, aliquot, step, irradpos, runtype, sess=None, # refdetlabel, # overwrite=True, **kw): ''' this function does not check for existence of the record_id ''' # if analysis is None: # rid, aliquot = rid.split('-') # r = int(args[0]) # d = int(args[1]) if len(args) == 2 else None # rd = self.get_detector(refdetlabel) # refid, label = 0, '' # if rd: # refid = rd.DetectorID # label = rd.Label if isinstance(aliquot, int): aliquot = '{:02n}'.format(aliquot) # query the IrradiationPositionTable irradpos = self.get_irradiation_position(irradpos,) params = dict(RID=rid, # make_runid(rid, aliquot, step), # '{}-{}{}'.format(rid, aliquot, step), Aliquot=aliquot, RunDateTime=func.current_timestamp(), LoginSessionID=1, SpecRunType=runtype, Increment=step, # RefDetID=refid, # ReferenceDetectorLabel=label ) # IrradPosition cannot be null if irradpos is not None: ip = irradpos.IrradPosition sa = irradpos.SampleID else: ip = -2 sa = 0 params['RedundantSampleID'] = sa params['IrradPosition'] = ip params.update(kw) # analysis = self.get_analysis(rid, aliquot, step) # if analysis is None: analysis = AnalysesTable(**params) self._add_item(analysis,) # elif overwrite: # for k, v in params.iteritems(): # setattr(analysis, k, v) return analysis
class PackageDefinition(Base, HasDummy): __tablename__ = 'package_definitions' id = Column(u'pkg_def_id', INTEGER(), primary_key=True) deploy_type = Column(String(length=30), nullable=False) validation_type = Column(String(length=15), nullable=False) pkg_name = Column(String(length=255), nullable=False) name = synonym('pkg_name') path = Column(String(length=255), nullable=False) repository = Column(String(length=255), nullable=True) arch = Column(Enum('i386', 'x86_64', 'noarch'), nullable=False, server_default='noarch') build_type = Column(Enum(u'developer', u'hudson', u'jenkins'), nullable=False, server_default='jenkins') build_host = Column(String(length=255), nullable=False) env_specific = Column(BOOLEAN(), nullable=False, server_default='0') environment_specific = synonym('env_specific') created = Column(TIMESTAMP(), nullable=False, server_default=func.current_timestamp()) packages = relationship( 'Package', primaryjoin=("(Package.pkg_def_id == PackageDefinition.id)" " & (Package.status != 'removed')"), passive_deletes=True, ) all_packages = relationship( 'Package', back_populates='package_definition', passive_deletes=True, ) package_names = relationship( 'PackageName', back_populates="package_definition", passive_deletes=True, ) applications = relationship( 'AppDefinition', secondary=lambda: Base.metadata.tables['project_package'], passive_deletes=True, back_populates='package_definitions', viewonly=True, ) projects = relationship( 'Project', secondary=lambda: Base.metadata.tables['project_package'], passive_deletes=True, back_populates='package_definitions', viewonly=True, )
class Package(Base): __tablename__ = 'packages' id = Column(u'package_id', INTEGER(), primary_key=True) pkg_def_id = Column( INTEGER(), ForeignKey('package_definitions.pkg_def_id', ondelete='cascade'), nullable=False ) pkg_name = Column(String(length=255), nullable=False) name = synonym('pkg_name') version = Column(String(length=63), nullable=False) revision = Column(String(length=63), nullable=False) job = Column(String(length=255), nullable=True) commit_hash = Column(String(length=40), nullable=True) status = Column( Enum('completed', 'failed', 'pending', 'processing', 'removed'), nullable=False ) created = Column( TIMESTAMP(), nullable=False, server_default=func.current_timestamp() ) creator = Column(String(length=255), nullable=False) builder = Column( Enum(u'developer', u'hudson', u'jenkins'), nullable=False, server_default='developer' ) project_type = Column( Enum(u'application', u'kafka-config', u'tagconfig'), nullable=False, server_default='application' ) package_definition = relationship( 'PackageDefinition', back_populates='packages' ) application = synonym('package_definition') app_deployments = relationship( 'AppDeployment', back_populates='package', order_by="AppDeployment.created_at, AppDeployment.id" ) host_deployments = relationship( 'HostDeployment', back_populates='package', order_by="HostDeployment.created_at, HostDeployment.id" ) __table_args__ = ( UniqueConstraint(u'pkg_name', u'version', u'revision', u'builder', name=u'unique_package'), { 'mysql_engine' : 'InnoDB', 'mysql_charset' : 'utf8', }, )
class Bill(db.Model): id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.Integer, db.ForeignKey('user.id')) user = db.relationship(User, uselist=False, backref="bills") accumulated_items = db.Column(db.Integer, nullable=False, default=0) accumulated_price = db.Column(db.Integer, nullable=False, default=0) created = db.Column(db.DateTime, default=sqla_func.current_timestamp()) def update_accumulated(self): self.accumulated_price = sum([entry.price for entry in self.entries]) self.accumulated_items = len(self.entries)
def lint_sql(self): if not SQL_EXTENSION_AVAILABLE: print "SQLALCHEMY extension is not enabled" return False from sqlalchemy.sql.expression import func session = get_session(self.config.get(self.section, 'dbconnectstring')) try: dbtime = session.execute(func.current_timestamp()).scalar() print "DB connection successful. Server time: %s" % dbtime session.close() return True except Exception, e: print e return False
def lint_sql(self): if not SQL_EXTENSION_AVAILABLE: print("SQLALCHEMY extension is not enabled") return False from sqlalchemy.sql.expression import func session = get_session( self.config.get(self.section, 'dbconnectstring')) try: dbtime = session.execute(func.current_timestamp()).scalar() print("DB connection successful. Server time: %s" % dbtime) session.close() return True except Exception as e: print(e) return False
def generate_mapping(table, schema, base=None): """Generate an ORM mapping class from an entry in table_schemas. """ name = table.capitalize() table_args = {} if isinstance(schema[0], str): table_args['comment'] = schema[0] schema = schema[1:] props = { '__tablename__': table, '__table_args__': table_args, 'id': Column(Integer, primary_key=True), } for column in schema: colname, coltype = column[:2] kwds = {} if len(column) < 4 else column[3] kwds['comment'] = None if len(column) < 3 else column[2] defer_col = kwds.pop('deferred', False) if coltype not in _coltypes: if not coltype.endswith('.id'): raise ValueError("Unrecognized column type %s" % coltype) props[colname] = Column(Integer, ForeignKey(coltype), **kwds) else: ctyp = _coltypes[coltype] props[colname] = Column(ctyp, **kwds) if defer_col: props[colname] = deferred(props[colname]) props['time_created'] = Column(DateTime, default=func.now()) props['time_modified'] = Column(DateTime, onupdate=func.current_timestamp()) props['meta'] = Column(JSONB) if base is None: return type(name, (ORMBase, ), props) else: def init(self, *args, **kwds): base.__init__(self) ORMBase.__init__(self, *args, **kwds) props['__init__'] = init # doesn't work? return type(name, (base, ORMBase), props)
def add_package(app_name, version, revision, user): """Add the requested version for the package of a given application""" pkg_def = find_package_definition(app_name) if find_package(app_name, version, revision): raise PackageException('Current version of application "%s" ' 'already found in Package table' % app_name) pkg = Package(pkg_def_id=pkg_def.id, pkg_name=app_name, version=version, revision=revision, status='pending', created=func.current_timestamp(), creator=user, builder=pkg_def.build_type, project_type='application') Session.add(pkg)
class HostDeployment(Base): __tablename__ = 'host_deployments' id = Column(u'HostDeploymentID', INTEGER(), primary_key=True) deployment_id = Column(u'DeploymentID', INTEGER(), ForeignKey('deployments.DeploymentID', ondelete='cascade'), nullable=False) deployment = relationship("Deployment", uselist=False) host_id = Column(u'HostID', INTEGER(), ForeignKey('hosts.HostID', ondelete='cascade'), nullable=False) host = relationship("Host", uselist=False) package_id = Column(INTEGER(), ForeignKey('packages.package_id', ondelete='cascade'), nullable=False) package = relationship( "Package", uselist=False, back_populates='host_deployments', ) user = Column(String(length=32), nullable=False) status = Column(Enum( 'failed', 'inprogress', 'ok', 'pending', ), nullable=False) realized = Column(TIMESTAMP(), nullable=False, server_default=func.current_timestamp()) created_at = synonym('realized') duration = Column(FLOAT(), nullable=False, server_default="0") deploy_result = Column(TEXT()) __table_args__ = (UniqueConstraint(u'package_id', u'HostID', u'realized'), )
def add_package(app_name, version, revision, user): """Add the requested version for the package of a given application""" pkg_def = find_package_definition(app_name) if find_package(app_name, version, revision): raise PackageException('Current version of application "%s" ' 'already found in Package table' % app_name) pkg = Package( pkg_def_id=pkg_def.id, pkg_name=app_name, version=version, revision=revision, status='pending', created=func.current_timestamp(), creator=user, builder=pkg_def.build_type, project_type='application' ) Session.add(pkg)
class Deployment(Base): __tablename__ = 'deployments' id = Column(u'DeploymentID', INTEGER(), primary_key=True) user = Column(String(length=32), nullable=False) status = Column( Enum('pending', 'queued', 'inprogress', 'complete', 'failed', 'canceled', 'stopped'), server_default='pending', nullable=False, ) delay = Column(u'delay', INTEGER(), server_default='0') declared = Column(TIMESTAMP(), nullable=False, server_default=func.current_timestamp()) created_at = synonym('declared') duration = Column(FLOAT(), nullable=False, server_default="0") app_deployments = relationship( 'AppDeployment', order_by="AppDeployment.created_at, AppDeployment.id") host_deployments = relationship( 'HostDeployment', order_by="HostDeployment.created_at, HostDeployment.id")
class SampleStageFile(db.Model, metaclass=ResourceMetaClass): __tablename__ = 'sample_stage_file' __hashidgen__ = HashIds('SampleStageFile') relative_source_path = Column(Text, unique=True) relative_target_path = Column(Text, unique=True) _status = Column('status', Enum(*list(FileStatus.__members__.keys()))) # PORTABILITY WARNING: SQLite renders `now` in UTC, which is what we want. # This behaviour may not be true for all stores and so may need custom type # handling to ensure that timestamps are consistently handled in UTC. _created_ts = Column('created_ts', TIMESTAMP, server_default=func.now()) modified_ts = Column(TIMESTAMP, server_default=func.now(), onupdate=func.current_timestamp()) # relationships _sample_stage_id = Column('sample_stage_id', Integer, ForeignKey('sample_stage.id')) @property def sample_stage_id(self): return self.sample_stage.obfuscated_id @hybrid_property def status(self): return FileStatus(self._status) @status.setter def status(self, s): self._status = s.value @status.expression def status(cls): return cls._status # create a sample stage file object def __init__(self, relative_upload_name, sample_stage, status=FileStatus.prepared): self.sample_stage = sample_stage # construct the file path based on project, sample, and method method = sample_stage.method sample = sample_stage.sample project = sample.project relpath, counter = create_archive_filename( app.config['STORE_PATH'], [ 'project-{project_id}'.format( project_id=project.obfuscated_id), 'sample-{sample_id}'.format(sample_id=sample.obfuscated_id), 'stage-{stage_id}.method-{method_id}'.format( stage_id=sample_stage.obfuscated_id, method_id=method.obfuscated_id) ], os.path.basename(relative_upload_name)) self.relative_source_path = relative_upload_name self.relative_target_path = relpath self.status = status def _file_repr_(self): stageid = self.sample_stage.id sample = self.sample_stage.sample project = sample.project return '{project:}/{sample:}/{stage:}/{fname:}'.format( stage=stageid, project=project.name, sample=sample.name, fname=os.path.basename(self.relative_target_path)) def __repr__(self): return '<Sample Stage File {id:}: ' \ '{file:} ({relpath:}), status={stat:}>'.format( id=self.id, file=self._file_repr_(), relpath=self.relative_target_path, stat=self.status) def mark_archived(self): self.status = FileStatus.archived return with_transaction(db.session, lambda session: session.add(self)) def mark_cleaned(self): # Today, cleaning is the last step in the proces, so we jump straight # to `complete`. self.status = FileStatus.complete return with_transaction(db.session, lambda session: session.add(self))
def json_search(): user_id = request.args.get('id') name = request.args.get('name') login = request.args.get('login') mac = request.args.get('mac') ip_address = request.args.get('ip_address') traffic_group_id = request.args.get('traffic_group_id') property_group_id = request.args.get('property_group_id') building_id = request.args.get('building_id') query = request.args.get("query") result = User.q count_no_filter = result.count() if user_id is not None and user_id != "": try: result = result.filter(User.id == int(user_id)) except ValueError: return abort(400) if name: result = result.filter(User.name.ilike("%{}%".format(name))) if login: result = result.filter(User.login.ilike("%{}%".format(login))) if mac: if not re.match(mac_regex, mac): return abort(400) result = result.join(User.hosts)\ .join(Host.interfaces)\ .filter(Interface.mac == mac) if ip_address: if not re.match(ip_regex, ip_address): return abort(400) result = result.join(User.hosts) \ .join(Host.ips) \ .filter(IP.address == ip_address) search_for_pg = property_group_id is not None and property_group_id != "" \ and property_group_id != "__None" search_for_tg = traffic_group_id is not None and traffic_group_id != "" \ and traffic_group_id != "__None" if search_for_pg or search_for_tg: result = result.join(Membership) result = result.filter(or_( Membership.ends_at.is_(None), Membership.ends_at > func.current_timestamp())) \ .filter(or_( Membership.begins_at.is_(None), Membership.begins_at < func.current_timestamp())) try: result_pg, result_tg = None, None if search_for_pg: pg_id = int(property_group_id) result_pg = result.join(PropertyGroup, PropertyGroup.id == Membership.group_id) \ .filter(PropertyGroup.id == pg_id) if not search_for_tg: result = result_pg if search_for_tg: tg_id = int(traffic_group_id) result_tg = result.join(TrafficGroup, TrafficGroup.id == Membership.group_id) \ .filter(TrafficGroup.id == tg_id) if not search_for_pg: result = result_tg if search_for_pg and search_for_tg: result = result_pg.intersect(result_tg) except ValueError: return abort(400) if building_id is not None and building_id != "" and building_id != "__None": try: result = result.join(User.room) \ .filter(Room.building_id == int(building_id)) except ValueError: return abort(400) if query: query = query.strip() if re.match(mac_regex, query): result = result.join(User.hosts) \ .join(Host.interfaces) \ .filter(Interface.mac == query) elif re.match(ip_regex, query): result = result.join(User.hosts) \ .join(Host.ips) \ .filter(IP.address == query) else: result = result.filter(or_( func.lower(User.name).like( func.lower(u"%{0}%".format(query))), func.lower(User.login).like( func.lower(u"%{0}%".format(query))), cast(User.id, Text).like(u"{0}%".format(query)) )) return jsonify(items=[{ 'id': found_user.id, 'name': found_user.name, 'url': { 'href': url_for('.user_show', user_id=found_user.id), 'title': found_user.name }, 'login': found_user.login, 'room_id': found_user.room_id if found_user.room_id is not None else None } for found_user in (result.all() if result.count() < count_no_filter else [])])
def add_data_reduction_session(self, **kw): drs = DataReductionSessionTable( SessionDate=func.current_timestamp()) self._add_item(drs, ) return drs
class DefaultMixin(object): """Mixin to add Parenchym's standard fields to a model class. These are: id, ctime, owner, mtime, editor. """ IDENTITY_COL = 'name' """ Name of a column that can be used to identify a record uniquely, besides ID. """ id = Column(Integer, primary_key=True, nullable=False, info={'colanderalchemy': { 'title': _("ID") }}) """Primary key of table.""" ctime = Column(DateTime, server_default=func.current_timestamp(), nullable=False, info={'colanderalchemy': { 'title': _("Creation Time") }}) """Timestamp, creation time.""" # noinspection PyMethodParameters @declared_attr def owner_id(cls): """ID of user who created this record.""" return Column(Integer(), ForeignKey("pym.user.id", onupdate="CASCADE", ondelete="RESTRICT"), nullable=False, info={'colanderalchemy': { 'title': _("OwnerID") }}) mtime = Column(DateTime, onupdate=func.current_timestamp(), nullable=True, info={'colanderalchemy': { 'title': _("Mod Time") }}) """Timestamp, last edit time.""" # noinspection PyMethodParameters @declared_attr def editor_id(cls): """ID of user who was last editor.""" return Column(Integer(), ForeignKey("pym.user.id", onupdate="CASCADE", ondelete="RESTRICT"), nullable=True, info={'colanderalchemy': { 'title': _("EditorID") }}) dtime = Column(DateTime, nullable=True, info={'colanderalchemy': { 'title': _("Deletion Time") }}) """Timestamp, deletion time.""" # noinspection PyMethodParameters @declared_attr def deleter_id(cls): """ID of user who tagged this this record as deleted.""" return Column(Integer(), ForeignKey("pym.user.id", onupdate="CASCADE", ondelete="RESTRICT"), nullable=True, info={'colanderalchemy': { 'title': _("DeleterID") }}) # noinspection PyMethodParameters @declared_attr def deletion_reason(cls): """Optional reason for deletion.""" return Column( Unicode(255), nullable=True, info={'colanderalchemy': { 'title': _("Deletion Reason") }}) def dump(self): from pym.models import todict from pprint import pprint pprint(todict(self)) @classmethod def find(cls, sess, obj): """ Finds given object and returns its instance. Input object may be the integer ID of a DB record, or a value that is checked in IDENTITY_COL. If object is already the requested instance, it is returned unchanged. Raises NoResultFound if object is unknown. """ if isinstance(obj, int): o = sess.query(cls).get(obj) if not o: raise sa.orm.exc.NoResultFound() return o elif isinstance(obj, cls): return obj else: if not cls.IDENTITY_COL: raise TypeError('{} has no IDENTITY_COL'.format(cls.__name__)) fil = {cls.IDENTITY_COL: obj} return sess.query(cls).filter_by(**fil).one() def is_deleted(self): return self.deleter_id is not None
class AppDeployment(Base): __tablename__ = 'app_deployments' id = Column(u'AppDeploymentID', INTEGER(), primary_key=True) deployment_id = Column(u'DeploymentID', INTEGER(), ForeignKey('deployments.DeploymentID', ondelete='cascade'), nullable=False) deployment = relationship("Deployment", uselist=False) app_id = Column(u'AppID', SMALLINT(display_width=6), ForeignKey('app_definitions.AppID', ondelete='cascade'), nullable=False) application = relationship("AppDefinition", uselist=False) target = synonym('application') package_id = Column(INTEGER(), ForeignKey('packages.package_id', ondelete='cascade'), nullable=False) package = relationship( "Package", uselist=False, back_populates="app_deployments", ) user = Column(String(length=32), nullable=False) status = Column(Enum( 'complete', 'incomplete', 'inprogress', 'invalidated', 'pending', 'validated', ), nullable=False) environment_id = Column(u'environment_id', INTEGER(), ForeignKey('environments.environmentID', ondelete='cascade'), nullable=False) realized = Column(TIMESTAMP(), nullable=False, server_default=func.current_timestamp()) created_at = synonym('realized') duration = Column(FLOAT(), nullable=False, server_default="0") skewed = Column(BOOLEAN(), nullable=False, server_default='0') environment_obj = relationship('Environment') __table_args__ = (UniqueConstraint(u'package_id', u'AppID', u'environment_id', u'realized'), ) @hybrid_property def env(self): return self.environment_obj.env @env.expression def env(cls): return select([Environment.env]).\ where(Environment.id == cls.environment_id).correlate(cls).\ label('env') @hybrid_property def environment(self): return self.environment_obj.environment @environment.expression def environment(cls): return select([Environment.environment]).\ where(Environment.id == cls.environment_id).correlate(cls).\ label('environment') @hybrid_property def needs_validation(self): """ Complete and incomplete deployments require validation """ return self.status in ('complete', 'incomplete') @needs_validation.expression def needs_validation(cls): return cls.status.in_(['complete', 'incomplete'])
def add_data_reduction_session(self, **kw): drs = DataReductionSessionTable( SessionDate=func.current_timestamp() ) self._add_item(drs,) return drs