class Enforcements(Model, BaseModelMixin): """ EnforcementAction Object """ __tablename__ = "enforcements" enforcement_id = Column(Integer(unsigned=True), primary_key=True, autoincrement=True) account_id = Column(Integer(unsigned=True)) resource_id = Column(String(256)) action = Column(String(64)) timestamp = Column(DateTime(timezone=True), default=func.now()) metrics = Column(JSON) @staticmethod def get(enforcement_id): """Return enforcement by ID Args: enforcement_id (str): Unique Enforcement identifier Returns: :obj:`Enforcement`: Returns Enforcement object if found, else None """ return db.EnforcementAction.find_one( Enforcements.enforcement_id == enforcement_id) def __repr__(self): return "{}({}, {}, '{}', '{}', '{}', '{}')".format( self.__class__.__name__, self.enforcement_id, self.account_id, self.resource_id, self.action, self.timestamp, self.metrics)
class ShowTag(Base): """connection between Shows and Tags""" __tablename__ = 'show_tags' show_tag = Column(Integer(unsigned=True), primary_key=True, autoincrement=True) show_id = Column("show", Integer(unsigned=True), ForeignKey('shows.show', onupdate="CASCADE", ondelete="CASCADE"), nullable=False) show = relationship("Show", backref=backref('tags', cascade="all, delete-orphan")) tag_id = Column("tag", Integer(unsigned=True), ForeignKey('tags.tag', onupdate="CASCADE", ondelete="RESTRICT"), nullable=False) tag = relationship("Tag", backref=backref('shows', cascade="all, delete-orphan")) def __init__(self, tag): self.tag = tag
class Score(_Model): __tablename__ = 'score' __table_args__ = (UniqueConstraint('userid', 'key', 'time', name='score_userid_key_time_unique'), { 'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8', }) id = Column(Integer(unsigned=True), primary_key=True, autoincrement=True) userid = Column(Integer(unsigned=True), index=True) # mapped via SCORE_TYPE key = Column(SmallInteger) time = Column(Date) value = Column(Integer) def __init__(self, *args, **kw): if 'time' not in kw: kw['time'] = datetime.datetime.utcnow().date() super(Score, self).__init__(*args, **kw) @property def name(self): return SCORE_TYPE_INVERSE.get(self.key, '') @name.setter def name(self, value): self.key = SCORE_TYPE[value]
class UserShow(Base): """connection between users and show""" __tablename__ = 'user_shows' userShow = Column(Integer(unsigned=True), primary_key=True, autoincrement=True) user_id = Column("user", Integer(unsigned=True), ForeignKey('users.user', onupdate="CASCADE", ondelete="CASCADE"), nullable=False) user = relationship("User", backref=backref('shows', cascade="all, delete-orphan")) show_id = Column("show", Integer(unsigned=True), ForeignKey('shows.show', onupdate="CASCADE", ondelete="CASCADE"), nullable=False) show = relationship("Show", backref=backref('users', cascade="all, delete-orphan")) role_id = Column("role", Integer(unsigned=True), ForeignKey('roles.role', onupdate="CASCADE", ondelete="RESTRICT"), nullable=False) role = relationship("Role") status = Column(Integer(unsigned=True), default=0) STATUS = ENUM(['UNKNOWN', 'STREAMING', 'STREAMED'])
class RelayStatistic(Base): __tablename__ = 'relay_statistics' relaystat = Column(Integer(unsigned=True), primary_key=True, autoincrement=True) statistic_id = Column( "statistic", Integer(unsigned=True), ForeignKey('statistics.statistic', onupdate="CASCADE", ondelete="RESTRICT")) statistic = relationship("Statistic", cascade="all") relay_id = Column( "relay", Integer(unsigned=True), ForeignKey('relays.relay', onupdate="CASCADE", ondelete="RESTRICT")) relay = relationship("Relay", cascade="all") type = Column(Integer(unsigned=True)) TYPE = ENUM(['TRAFFIC', 'LISTENERCOUNT']) @staticmethod def get_relaystatistic(relay, type): try: return RelayStatistic.query.filter( RelayStatistic.relay == relay, RelayStatistic.type == type).one() except exc.NoResultFound: s = Statistic(name='{0}:{1}-{2}'.format(relay.address, relay.port, type), identifier='{0}:{1}-{2}'.format( relay.address, relay.port, type)) rfk.database.session.add(s) rs = RelayStatistic(relay=relay, type=type, statistic=s) rfk.database.session.add(rs) rfk.database.session.flush() return rs
class StationMixin(BboxMixin, PositionMixin, TimeTrackingMixin, CreationMixin, ScoreMixin): """A model mix-in with common station columns.""" radius = Column(Integer(unsigned=True)) #: region = Column(String(2)) #: samples = Column(Integer(unsigned=True)) #: source = Column(TinyIntEnum(StationSource)) #: weight = Column(Double(asdecimal=False)) #: last_seen = Column(Date) #: block_first = Column(Date) #: block_last = Column(Date) #: block_count = Column(TinyInteger(unsigned=True)) #: def blocked(self, today=None): """Is the station currently blocked?""" if (self.block_count and self.block_count >= PERMANENT_BLOCKLIST_THRESHOLD): return True temporary = False if self.block_last: if today is None: today = util.utcnow().date() age = today - self.block_last temporary = age < TEMPORARY_BLOCKLIST_DURATION return bool(temporary)
class UserStatistic(Base): __tablename__ = 'user_statistics' user_statistic = Column(Integer(unsigned=True), primary_key=True, autoincrement=True) user_id = Column( "user", Integer(unsigned=True), ForeignKey('users.user', onupdate="CASCADE", ondelete="RESTRICT")) user = relationship("User", cascade="all") statistic_id = Column( "statistic", Integer(unsigned=True), ForeignKey('statistics.statistic', onupdate="CASCADE", ondelete="RESTRICT")) statistic = relationship("Statistic", cascade="all") code = Column(String(20), nullable=False) @staticmethod def get_userstatistic(user, code): try: return UserStatistic.query.filter( UserStatistic.user == user, UserStatistic.code == code).one() except exc.NoResultFound: s = Statistic(name='user-{user}-{code}'.format(user=user.user, code=code), identifier='user-{user}-{code}'.format( user=user.user, code=code)) rfk.database.session.add(s) rs = UserStatistic(user=user, code=code, statistic=s) rfk.database.session.add(rs) rfk.database.session.flush() return rs
class Log(Base): __tablename__ = 'log' log = Column(Integer(unsigned=True), primary_key=True, autoincrement=True) timestamp = Column(UTCDateTime, default=now) severity = Column(Integer(unsigned=True)) module = Column(String(50)) message = Column(Text)
class Loop(Base): __tablename__ = 'loops' loop = Column(Integer(unsigned=True), primary_key=True, autoincrement=True) begin = Column(Integer(unsigned=True), default=0) end = Column(Integer(unsigned=True), default=1440) filename = Column(String(50)) @hybrid_property def length(self): if (self.end >= self.begin): return abs(self.end - self.begin) else: return abs((self.end + 2400) - self.begin) @length.expression def length(cls): return func.abs( cast( case([(cls.begin <= cls.end, cls.end), (cls.begin >= cls.end, cls.end + 2400)]), Integer) - cast(cls.begin, Integer)) @hybrid_method def contains(self, point): return case([(self.begin <= self.end, (self.begin <= point) & (self.end >= point)), (self.begin >= self.end, (self.begin <= point) | (self.end >= point))]) @hybrid_property def file_exists(self): if self.filename is None: return False return os.path.exists( os.path.join(get_path(CONFIG.get('liquidsoap', 'looppath')), self.filename)) @staticmethod def get_current_loop(): """ returns the current loop to be scheduled @todo maybe broken ;_; """ n = now() #try to find a loop that should be running loops = Loop.query.filter( Loop.contains(int(n.hour * 100 + (n.minute / 60.) * 100))).order_by( Loop.length.asc()).all() for loop in loops: if loop.file_exists: return loop # we found no loops # just try to find the longest one loops = Loop.query.order_by(Loop.length.asc()).all() for loop in loops: if loop.file_exists: return loop #okay, now we have a problem, just retun none return None
class Ban(Base): __tablename__ = 'bans' ban = Column(Integer(unsigned=True), primary_key=True, autoincrement=True) user_id = Column( "user", Integer(unsigned=True), ForeignKey('users.user', onupdate="CASCADE", ondelete="RESTRICT")) user = relationship("User", backref=backref('bans')) range = Column(String(50)) expiration = Column(UTCDateTime)
def upgrade(): op.alter_column('cell_blacklist', 'created', new_column_name='time', existing_type=DateTime()) op.alter_column('wifi_blacklist', 'created', new_column_name='time', existing_type=DateTime()) op.add_column('cell_blacklist', Column('count', Integer())) op.add_column('wifi_blacklist', Column('count', Integer())) op.execute("UPDATE cell_blacklist SET count = 1 WHERE count IS NULL") op.execute("UPDATE wifi_blacklist SET count = 1 WHERE count IS NULL")
class Cell(_Model): __tablename__ = 'cell' __table_args__ = ( Index('cell_created_idx', 'created'), Index('cell_modified_idx', 'modified'), Index('cell_new_measures_idx', 'new_measures'), Index('cell_total_measures_idx', 'total_measures'), { 'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8', } ) created = Column(DateTime) modified = Column(DateTime) # lat/lon lat = Column(Double(asdecimal=False)) max_lat = Column(Double(asdecimal=False)) min_lat = Column(Double(asdecimal=False)) lon = Column(Double(asdecimal=False)) max_lon = Column(Double(asdecimal=False)) min_lon = Column(Double(asdecimal=False)) # mapped via RADIO_TYPE radio = Column(TinyInteger, autoincrement=False, primary_key=True) # int in the range 0-1000 mcc = Column(SmallInteger, autoincrement=False, primary_key=True) # int in the range 0-1000 for gsm # int in the range 0-32767 for cdma (system id) mnc = Column(SmallInteger, autoincrement=False, primary_key=True) lac = Column( SmallInteger(unsigned=True), autoincrement=False, primary_key=True) cid = Column(Integer(unsigned=True), autoincrement=False, primary_key=True) psc = Column(SmallInteger) range = Column(Integer) new_measures = Column(Integer(unsigned=True)) total_measures = Column(Integer(unsigned=True)) def __init__(self, *args, **kw): if 'created' not in kw: kw['created'] = util.utcnow() if 'modified' not in kw: kw['modified'] = util.utcnow() if 'lac' not in kw or not kw['lac']: kw['lac'] = 0 if 'cid' not in kw or not kw['cid']: kw['cid'] = 0 if 'range' not in kw: kw['range'] = 0 if 'new_measures' not in kw: kw['new_measures'] = 0 if 'total_measures' not in kw: kw['total_measures'] = 0 super(Cell, self).__init__(*args, **kw)
class News(Base): __tablename__ = 'news' news = Column(Integer(unsigned=True), primary_key=True, autoincrement=True) time = Column(UTCDateTime, default=now()) user_id = Column( "user", Integer(unsigned=True), ForeignKey('users.user', onupdate="CASCADE", ondelete="RESTRICT")) user = relationship("User") title = Column(String(255)) content = Column(Text)
class StatsistcsData(Base): __tablename__ = 'statisticsdata' stat = Column(Integer(unsigned=True), primary_key=True, autoincrement=True) statistic_id = Column( "statistic", Integer(unsigned=True), ForeignKey('statistics.statistic', onupdate="CASCADE", ondelete="RESTRICT")) statistic = relationship("Statistic", cascade="all") timestamp = Column(UTCDateTime(), nullable=False) value = Column(Integer(unsigned=True), nullable=False)
class CellAreaMixin(PositionMixin, TimeTrackingMixin, CreationMixin): _valid_schema = ValidCellAreaSchema() areaid = Column(CellAreaColumn(7)) radio = Column(TinyIntEnum(Radio), nullable=False) mcc = Column(SmallInteger, nullable=False) mnc = Column(SmallInteger, nullable=False) lac = Column(SmallInteger(unsigned=True), nullable=False) radius = Column(Integer) region = Column(String(2)) avg_cell_radius = Column(Integer(unsigned=True)) num_cells = Column(Integer(unsigned=True)) last_seen = Column(Date) @declared_attr def __table_args__(cls): # NOQA prefix = cls.__tablename__ _indices = ( PrimaryKeyConstraint('areaid'), UniqueConstraint('radio', 'mcc', 'mnc', 'lac', name='%s_areaid_unique' % prefix), Index('%s_region_radio_idx' % prefix, 'region', 'radio'), Index('%s_created_idx' % prefix, 'created'), Index('%s_modified_idx' % prefix, 'modified'), Index('%s_latlon_idx' % prefix, 'lat', 'lon'), ) return _indices + (cls._settings, ) @classmethod def validate(cls, entry, _raise_invalid=False, **kw): validated = super(CellAreaMixin, cls).validate(entry, _raise_invalid=_raise_invalid, **kw) if validated is not None and 'areaid' not in validated: validated['areaid'] = ( validated['radio'], validated['mcc'], validated['mnc'], validated['lac'], ) if (('region' not in validated or not validated['region']) and validated['lat'] is not None and validated['lon'] is not None): validated['region'] = GEOCODER.region_for_cell( validated['lat'], validated['lon'], validated['mcc']) return validated
class CellAreaMixin(PositionMixin, TimeTrackingMixin, CreationMixin): _valid_schema = ValidCellAreaSchema() areaid = Column(CellAreaColumn(7)) radio = Column(TinyIntEnum(Radio), nullable=False) mcc = Column(SmallInteger, nullable=False) mnc = Column(SmallInteger, nullable=False) lac = Column(SmallInteger(unsigned=True), nullable=False) radius = Column(Integer) region = Column(String(2)) avg_cell_radius = Column(Integer(unsigned=True)) num_cells = Column(Integer(unsigned=True)) last_seen = Column(Date) @declared_attr def __table_args__(cls): prefix = cls.__tablename__ _indices = ( PrimaryKeyConstraint("areaid"), UniqueConstraint("radio", "mcc", "mnc", "lac", name="%s_areaid_unique" % prefix), Index("%s_region_radio_idx" % prefix, "region", "radio"), Index("%s_created_idx" % prefix, "created"), Index("%s_modified_idx" % prefix, "modified"), Index("%s_latlon_idx" % prefix, "lat", "lon"), ) return _indices + (cls._settings, ) @classmethod def validate(cls, entry, _raise_invalid=False, **kw): validated = super(CellAreaMixin, cls).validate(entry, _raise_invalid=_raise_invalid, **kw) if validated is not None and "areaid" not in validated: validated["areaid"] = ( validated["radio"], validated["mcc"], validated["mnc"], validated["lac"], ) if (("region" not in validated or not validated["region"]) and validated["lat"] is not None and validated["lon"] is not None): validated["region"] = GEOCODER.region_for_cell( validated["lat"], validated["lon"], validated["mcc"]) return validated
class MetaTitle(Base): """Metaclass for Titles this should not be used outside of Track() """ __tablename__ = 'metatitles' metatitle = Column(Integer(unsigned=True), primary_key=True, autoincrement=True) title_id = Column("title", Integer(unsigned=True), ForeignKey('titles.title', onupdate="CASCADE", ondelete="RESTRICT")) title = relationship("Title", backref=backref('metatitles')) name = Column(String(255))
class Cell(_Model): __tablename__ = 'cell' __table_args__ = ( UniqueConstraint( 'radio', 'mcc', 'mnc', 'lac', 'cid', name='cell_idx_unique'), Index('cell_created_idx', 'created'), Index('cell_new_measures_idx', 'new_measures'), Index('cell_total_measures_idx', 'total_measures'), { 'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8', } ) id = Column(BigInteger(unsigned=True), primary_key=True, autoincrement=True) created = Column(DateTime) # lat/lon * DEGREE_SCALE_FACTOR lat = Column(Integer) max_lat = Column(Integer) min_lat = Column(Integer) lon = Column(Integer) max_lon = Column(Integer) min_lon = Column(Integer) # mapped via RADIO_TYPE radio = Column(SmallInteger) # int in the range 0-1000 mcc = Column(SmallInteger) # int in the range 0-1000 for gsm # int in the range 0-32767 for cdma (system id) mnc = Column(Integer) lac = Column(Integer) cid = Column(Integer) psc = Column(Integer) range = Column(Integer) new_measures = Column(Integer(unsigned=True)) total_measures = Column(Integer(unsigned=True)) def __init__(self, *args, **kw): if 'created' not in kw: kw['created'] = datetime.datetime.utcnow() if 'lac' not in kw: kw['lac'] = -1 if 'cid' not in kw: kw['cid'] = -1 if 'new_measures' not in kw: kw['new_measures'] = 0 if 'total_measures' not in kw: kw['total_measures'] = 0 super(Cell, self).__init__(*args, **kw)
class Series(Base): __tablename__ = 'series' series = Column(Integer(unsigned=True), primary_key=True, autoincrement=True) user_id = Column("user", Integer(unsigned=True), ForeignKey('users.user', onupdate="CASCADE", ondelete="SET NULL")) user = relationship("User", backref=backref('series')) public = Column(Boolean) name = Column(String(50)) description = Column(String(255)) logo = Column(String(255))
class RegionStat(_Model): """RegionStat model.""" __tablename__ = "region_stat" _indices = (PrimaryKeyConstraint("region"), ) region = Column(String(2)) gsm = Column(Integer(unsigned=True)) wcdma = Column(Integer(unsigned=True)) lte = Column(Integer(unsigned=True)) blue = Column(BigInteger(unsigned=True)) wifi = Column(BigInteger(unsigned=True))
class StationMixin(BboxMixin, PositionMixin, TimeTrackingMixin, CreationMixin): """A model mix-in with common station columns.""" radius = Column(Integer(unsigned=True)) region = Column(String(2)) samples = Column(Integer(unsigned=True)) source = Column(TinyIntEnum(constants.ReportSource)) weight = Column(Double(asdecimal=False)) last_seen = Column(Date) block_first = Column(Date) block_last = Column(Date) block_count = Column(TinyInteger(unsigned=True))
class RegionStat(_Model): """RegionStat model.""" __tablename__ = 'region_stat' _indices = ( PrimaryKeyConstraint('region'), ) region = Column(String(2)) #: gsm = Column(Integer(unsigned=True)) #: wcdma = Column(Integer(unsigned=True)) #: lte = Column(Integer(unsigned=True)) #: wifi = Column(BigInteger(unsigned=True)) #:
class Donation(Base): __tablename__ = 'donations' donation = Column(Integer(unsigned=True), primary_key=True, autoincrement=True) value = Column(String(10), nullable=False) currency = Column(String(10), nullable=False) method = Column(Integer(unsigned=True), nullable=False) reference = Column(String(255)) date = Column(UTCDateTime, default=now, nullable=False) country = Column(String(3)) comment = Column(String(255)) METHOD = ENUM(['BTC', 'MANUAL'])
class Track(Base): """Database representation of a Track played in a show""" __tablename__ = 'tracks' track = Column(Integer(unsigned=True), primary_key=True, autoincrement=True) begin = Column(UTCDateTime()) end = Column(UTCDateTime()) title_id = Column( "title", Integer(unsigned=True), ForeignKey('titles.title', onupdate="CASCADE", ondelete="RESTRICT")) title = relationship("Title", backref=backref('tracks')) show_id = Column( "show", Integer(unsigned=True), ForeignKey('shows.show', onupdate="CASCADE", ondelete="RESTRICT")) show = relationship("Show", backref=backref('tracks')) @staticmethod def current_track(): """returns the current track (not yet ended)""" try: return Track.query.filter(Track.end == None).one() except exc.NoResultFound: return None def end_track(self, end=None): """ends the track and updates length in artist/title DB""" if end is None: self.end = now() else: self.end = end length = self.end - self.begin self.title.update_length(length.total_seconds()) @staticmethod def new_track(show, artist, title, begin=None): """adds a new Track to database and ends the current track (if any)""" if begin is None: current_track = Track.current_track() if current_track: current_track.end_track() title = Title.add_title(artist, title) if begin is None: begin = now() track = Track(title=title, begin=begin, show=show) rfk.database.session.add(track) rfk.database.session.flush() return track
def upgrade(): op.create_table( 'api_request_logs', sa.Column('id', Integer(unsigned=True), primary_key=True), sa.Column('service', sa.String(50), nullable=False), sa.Column('user', sa.String(50), nullable=True), sa.Column('url', sa.String(255), nullable=False), sa.Column('status', Integer(unsigned=True), nullable=False), sa.Column('response', sa.Text, nullable=True), sa.Column('args', sa.Text, nullable=True), sa.Column('time', sa.Float, nullable=True), sa.Column('created_at', sa.DateTime, nullable=False, default=datetime.now()), )
class Tag(Base): __tablename__ = 'tags' tag = Column(Integer(unsigned=True), primary_key=True, autoincrement=True) name = Column(String(25), nullable=False, unique=True) icon = Column(String(30)) description = Column(Text, nullable=False) @staticmethod def get_tag(name): """returns a Tag object by given identifier""" try: return Tag.query.filter(Tag.name == name).one() except exc.NoResultFound: tag = Tag(name=name, description=name) rfk.database.session.add(tag) rfk.database.session.flush() return tag @staticmethod def parse_tags(tags): """parses a space separated list of tags and returns a list of Tag objects""" def unique(seq): seen = set() seen_add = seen.add return [x for x in seq if x not in seen and not seen_add(x)] r = [] if tags is not None and len(tags) > 0: for str_tag in unique(tags.strip().split(' ')): if str_tag == '': continue tag = Tag.get_tag(str_tag) r.append(tag) return r
class PhysicalLink(model_base.BASEV2, models_v2.HasId): """ Represents a network cable that connects two network interfaces. It can represent an ethernet cable or an internal link between a blade server and an internal switch. """ __tablename__ = 'physical_link' name = sa.Column(sa.String(36), nullable=False) description = sa.Column(sa.String(255), nullable=True) cost = sa.Column(Integer(5), nullable=False) status = sa.Column(STATUS, default=cst.ACTIVE, nullable=False) physical_network = sa.Column(sa.String(36), sa.ForeignKey('physical_network.id', ondelete="SET NULL")) def __init__(self, name=None, description=None, cost=None, status=None, physical_network=None): # TODO, Without the initialisation of id parameter, # TODO, I have always 'None' value self.id = uuidutils.generate_uuid() self.description = description self.cost = cost self.name = name self.status = status self.physical_network = physical_network
class ResourceProperty(Model, BaseModelMixin): """Resource Property object""" __tablename__ = 'resource_properties' property_id = Column(Integer(unsigned=True), primary_key=True, autoincrement=True) resource_id = Column(String(256), ForeignKey('resources.resource_id', name='fk_resource_property_resource_id', ondelete='CASCADE'), nullable=False, primary_key=True, index=True) name = Column(String(50), nullable=False, index=True) value = Column(JSON, nullable=False) def __str__(self): return self.value def __repr__(self): return "{}({}, '{}', '{}', '{}')".format(self.__class__.__name__, self.property_id, self.resource_id, self.name, self.value)
class Issue(db.Model, BaseModelMixin): """Issue object Attributes: issue_id (str): Unique Issue identifier issue_type (str): :obj:`IssueType` reference properties (`list` of :obj:`IssueProperty`): List of properties of the issue """ __tablename__ = 'issues' issue_id = Column(String(256), primary_key=True) issue_type_id = Column(Integer(unsigned=True), index=True) properties = relationship('IssueProperty', lazy='select', uselist=True, primaryjoin=issue_id == foreign( IssueProperty.issue_id), cascade='all, delete-orphan') @staticmethod def get(issue_id, issue_type_id): """Return issue by ID Args: issue_id (str): Unique Issue identifier issue_type_id (str): Type of issue to get Returns: :obj:`Issue`: Returns Issue object if found, else None """ return Issue.query.filter( Issue.issue_id == issue_id, Issue.issue_type_id == issue_type_id).first()
class Score(IdMixin, HashKeyMixin, _Model): __tablename__ = 'score' _indices = ( UniqueConstraint('userid', 'key', 'time', name='score_userid_key_time_unique'), ) _hashkey_cls = ScoreHashKey userid = Column(Integer(unsigned=True), index=True) key = Column(TinyIntEnum(ScoreKey)) time = Column(Date) value = Column(Integer) @classmethod def incr(cls, session, key, value): score = cls.getkey(session, key) if score is not None: score.value += int(value) else: stmt = cls.__table__.insert( on_duplicate='value = value + %s' % int(value)).values( userid=key.userid, key=key.key, time=key.time, value=value) session.execute(stmt) return value