class WifiMeasure(_Model): __tablename__ = 'wifi_measure' __table_args__ = ( Index('wifi_measure_created_idx', 'created'), Index('wifi_measure_key_idx', 'key'), Index('wifi_measure_key_created_idx', 'key', 'created'), { 'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8', 'mysql_row_format': 'compressed', 'mysql_key_block_size': '4', } ) id = Column(BigInteger(unsigned=True), primary_key=True, autoincrement=True) measure_id = Column(BigInteger(unsigned=True)) created = Column(DateTime) # the insert time of the record into the DB # lat/lon * DEGREE_SCALE_FACTOR lat = Column(Integer) lon = Column(Integer) time = Column(DateTime) # the time of observation of this data accuracy = Column(Integer) altitude = Column(Integer) altitude_accuracy = Column(Integer) key = Column(String(12)) channel = Column(SmallInteger) signal = Column(SmallInteger) def __init__(self, *args, **kw): if 'measure_id' not in kw: kw['measure_id'] = 0 if 'created' not in kw: kw['created'] = datetime.datetime.utcnow() super(WifiMeasure, self).__init__(*args, **kw)
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 FloatPropertyValue(Base): __tablename__ = 'float_values' id = Column(BigInteger(unsigned=True), primary_key=True) property_id = Column(Integer, ForeignKey('properties.id'), #index=True, nullable=False) tm = Column(BigInteger(unsigned=True), #index=True, nullable=False) value = Column(Float(precision=53), nullable=False) def __repr__(self): return "<Value('%s')='%s'>" % (self.tm, self.value)
class AnyPropertyValue(Base): __tablename__ = 'any_values' id = Column(BigInteger(unsigned=True), primary_key=True) property_id = Column(Integer, ForeignKey('properties.id'), #index=True, nullable=False) tm = Column(BigInteger(unsigned=True), #index=True, nullable=False) value = Column(JSON(65532), nullable=False) def __repr__(self): return "<Value('%s')='%s'>" % (self.tm, self.value)
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 RecordEntry(Base): __tablename__ = 'record_entries' id = Column(Integer, primary_key=True) property_id = Column(Integer, ForeignKey('properties.id'), #index=True, nullable=False) start_tm = Column(BigInteger(unsigned=True), #index=True, nullable=False) stop_tm = Column(BigInteger(unsigned=True)) enabled = Column(Boolean, nullable=False) def __repr__(self): return "<LogEntry('%s','%s','%s')" % (self.start_tm, self.stop_tm, self.enabled)
class CellBlacklist(_Model): __tablename__ = 'cell_blacklist' __table_args__ = (UniqueConstraint('radio', 'mcc', 'mnc', 'lac', 'cid', name='cell_blacklist_idx_unique'), { 'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8', }) id = Column(BigInteger(unsigned=True), primary_key=True, autoincrement=True) time = Column(DateTime) radio = Column(TinyInteger) mcc = Column(SmallInteger) mnc = Column(SmallInteger) lac = Column(Integer) cid = Column(Integer) count = Column(Integer) def __init__(self, *args, **kw): if 'time' not in kw: kw['time'] = util.utcnow() if 'count' not in kw: kw['count'] = 1 super(CellBlacklist, self).__init__(*args, **kw)
class Measure(_Model): __tablename__ = 'measure' __table_args__ = { 'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8', } id = Column(BigInteger(unsigned=True), primary_key=True, autoincrement=True)
class Stat(_Model): """Stat model.""" __tablename__ = "stat" _indices = (PrimaryKeyConstraint("key", "time"), ) key = Column(TinyIntEnum(StatKey)) time = Column(Date) value = Column(BigInteger(unsigned=True))
class MeasureBlock(_Model): __tablename__ = 'measure_block' __table_args__ = (Index('idx_measure_block_archive_date', 'archive_date'), Index('idx_measure_block_s3_key', 's3_key'), Index('idx_measure_block_end_id', 'end_id'), { 'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8', 'mysql_row_format': 'compressed', 'mysql_key_block_size': '4', }) id = Column(BigInteger(unsigned=True), primary_key=True, autoincrement=True) measure_type = Column(SmallInteger) s3_key = Column(String(80)) archive_date = Column(DateTime) archive_sha = Column(BINARY(length=20)) start_id = Column(BigInteger(unsigned=True)) end_id = Column(BigInteger(unsigned=True))
class CellMeasure(_Model): __tablename__ = 'cell_measure' __table_args__ = ( Index('cell_measure_created_idx', 'created'), Index('cell_measure_key_idx', 'radio', 'mcc', 'mnc', 'lac', 'cid'), { 'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8', 'mysql_row_format': 'compressed', 'mysql_key_block_size': '4', } ) id = Column(BigInteger(unsigned=True), primary_key=True, autoincrement=True) measure_id = Column(BigInteger(unsigned=True)) created = Column(DateTime) # the insert time of the record into the DB # lat/lon * DEGREE_SCALE_FACTOR lat = Column(Integer) lon = Column(Integer) time = Column(DateTime) # the time of observation of this data accuracy = Column(Integer) altitude = Column(Integer) altitude_accuracy = Column(Integer) # mapped via RADIO_TYPE radio = Column(SmallInteger) mcc = Column(SmallInteger) mnc = Column(Integer) lac = Column(Integer) cid = Column(Integer) psc = Column(Integer) asu = Column(SmallInteger) signal = Column(SmallInteger) ta = Column(SmallInteger) def __init__(self, *args, **kw): if 'measure_id' not in kw: kw['measure_id'] = 0 if 'created' not in kw: kw['created'] = datetime.datetime.utcnow() super(CellMeasure, self).__init__(*args, **kw)
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 Stat(_Model): """Stat model.""" __tablename__ = 'stat' _indices = ( PrimaryKeyConstraint('key', 'time'), ) key = Column(TinyIntEnum(StatKey), autoincrement=False) #: time = Column(Date) #: value = Column(BigInteger(unsigned=True)) #:
class ObservationBlock(BigIdMixin, _Model): __tablename__ = 'measure_block' _indices = ( Index('idx_measure_block_archive_date', 'archive_date'), Index('idx_measure_block_s3_key', 's3_key'), Index('idx_measure_block_end_id', 'end_id'), ) _settings = { 'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8', 'mysql_row_format': 'compressed', 'mysql_key_block_size': '4', } measure_type = Column(TinyIntEnum(ObservationType)) s3_key = Column(String(80)) archive_date = Column(DateTime) archive_sha = Column(BINARY(length=20)) start_id = Column(BigInteger(unsigned=True)) end_id = Column(BigInteger(unsigned=True))
def upgrade(): op.create_table( 'measure_block', sa.Column('id', BigInteger(unsigned=True), primary_key=True, autoincrement=True), sa.Column('measure_type', sa.SmallInteger()), sa.Column('s3_key', sa.String(80)), sa.Column('archive_date', sa.DateTime()), sa.Column('archive_sha', sa.BINARY(length=20)), sa.Column('start_id', BigInteger(unsigned=True)), sa.Column('end_id', BigInteger(unsigned=True)), mysql_engine='InnoDB', mysql_charset='utf8', mysql_row_format='compressed', mysql_key_block_size='4', ) op.create_index('idx_measure_block_archive_date', 'measure_block', ['archive_date']) op.create_index('idx_measure_block_s3_key', 'measure_block', ['s3_key']) op.create_index('idx_measure_block_end_id', 'measure_block', ['end_id'])
class WifiBlacklist(_Model): __tablename__ = 'wifi_blacklist' __table_args__ = ( UniqueConstraint('key', name='wifi_blacklist_key_unique'), { 'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8', } ) id = Column(BigInteger(unsigned=True), primary_key=True, autoincrement=True) created = Column(DateTime) key = Column(String(12)) def __init__(self, *args, **kw): if 'created' not in kw: kw['created'] = datetime.datetime.utcnow() super(WifiBlacklist, self).__init__(*args, **kw)
class CellMeasure(_Model): __tablename__ = 'cell_measure' __table_args__ = ( Index('cell_measure_created_idx', 'created'), Index('cell_measure_key_idx', 'radio', 'mcc', 'mnc', 'lac', 'cid'), { 'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8', } ) id = Column(BigInteger(unsigned=True), primary_key=True, autoincrement=True) report_id = Column(BINARY(length=16)) created = Column(DateTime) # the insert time of the record into the DB # lat/lon lat = Column(Double(asdecimal=False)) lon = Column(Double(asdecimal=False)) time = Column(DateTime) # the time of observation of this data accuracy = Column(Integer) altitude = Column(Integer) altitude_accuracy = Column(Integer) # http://dev.w3.org/geo/api/spec-source.html#heading heading = Column(Float) # http://dev.w3.org/geo/api/spec-source.html#speed speed = Column(Float) # mapped via RADIO_TYPE radio = Column(TinyInteger) mcc = Column(SmallInteger) mnc = Column(SmallInteger) lac = Column(SmallInteger(unsigned=True)) cid = Column(Integer(unsigned=True)) psc = Column(SmallInteger) asu = Column(SmallInteger) signal = Column(SmallInteger) ta = Column(TinyInteger) def __init__(self, *args, **kw): if 'created' not in kw: kw['created'] = util.utcnow() super(CellMeasure, self).__init__(*args, **kw)
class Wifi(_Model): __tablename__ = 'wifi' __table_args__ = ( UniqueConstraint('key', name='wifi_key_unique'), Index('wifi_created_idx', 'created'), Index('wifi_new_measures_idx', 'new_measures'), Index('wifi_total_measures_idx', 'total_measures'), { 'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8', } ) id = Column(BigInteger(unsigned=True), primary_key=True, autoincrement=True) created = Column(DateTime) modified = Column(DateTime) key = Column(String(12)) # 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)) 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 'new_measures' not in kw: kw['new_measures'] = 0 if 'total_measures' not in kw: kw['total_measures'] = 0 super(Wifi, self).__init__(*args, **kw)
class WifiMeasure(_Model): __tablename__ = 'wifi_measure' __table_args__ = ( Index('wifi_measure_created_idx', 'created'), Index('wifi_measure_key_idx', 'key'), Index('wifi_measure_key_created_idx', 'key', 'created'), { 'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8', } ) id = Column(BigInteger(unsigned=True), primary_key=True, autoincrement=True) report_id = Column(BINARY(length=16)) created = Column(DateTime) # the insert time of the record into the DB # lat/lon lat = Column(Double(asdecimal=False)) lon = Column(Double(asdecimal=False)) time = Column(DateTime) # the time of observation of this data accuracy = Column(Integer) altitude = Column(Integer) altitude_accuracy = Column(Integer) # http://dev.w3.org/geo/api/spec-source.html#heading heading = Column(Float) # http://dev.w3.org/geo/api/spec-source.html#speed speed = Column(Float) key = Column(String(12)) channel = Column(SmallInteger) signal = Column(SmallInteger) snr = Column(SmallInteger) def __init__(self, *args, **kw): if 'created' not in kw: kw['created'] = util.utcnow() super(WifiMeasure, self).__init__(*args, **kw)
Column('email', String(255)), Column('admin', Integer), mysql_charset='utf8mb4', ) """ Table representing a card associated with a user. Users may have zero or more cards associated with them. When a new card is used in a game a new user will be created to associate with a card, but it can later be unlinked. """ card = Table( 'card', metadata, Column('id', String(16), nullable=False, unique=True), Column('userid', BigInteger(unsigned=True), nullable=False, index=True), mysql_charset='utf8mb4', ) """ Table representing an extid for a user across a game series. Each game series on the network gets its own extid (8 digit number) for each user. """ extid = Table( 'extid', metadata, Column('game', String(32), nullable=False), Column('extid', Integer, nullable=False, unique=True), Column('userid', BigInteger(unsigned=True), nullable=False), UniqueConstraint('game', 'userid', name='game_userid'), mysql_charset='utf8mb4',
from bemani.data.types import Score, Attempt, Song, UserID """ Table for storing a score for a particular game. This is keyed by userid and musicid, as a user can only have one score for a particular song/chart combo. This has a JSON blob for any data the game wishes to store, such as points, medals, ghost, etc. Note that this is NOT keyed by game song id and chart, but by an internal musicid managed by the music table. This is so we can support keeping the same score across multiple games, even if the game changes the ID it refers to the song by. """ score = Table( # type: ignore 'score', metadata, Column('id', Integer, nullable=False, primary_key=True), Column('userid', BigInteger(unsigned=True), nullable=False), Column('musicid', Integer, nullable=False, index=True), Column('points', Integer, nullable=False, index=True), Column('timestamp', Integer, nullable=False, index=True), Column('update', Integer, nullable=False, index=True), Column('lid', Integer, nullable=False, index=True), Column('data', JSON, nullable=False), UniqueConstraint('userid', 'musicid', name='userid_musicid'), mysql_charset='utf8mb4', ) """ Table for storing score history for a particular game. Every entry that is stored or updated in score will be written into this table as well, for looking up history over time. """ score_history = Table( # type: ignore
'name', 'schedule', name='game_version_name_schedule'), mysql_charset='utf8mb4', ) """ Table for storing audit entries, such as crashes, PCBID denials, daily song selection, etc. Anything that could be inspected later to verify correct operation of the network. """ audit = Table( # type: ignore 'audit', metadata, Column('id', Integer, nullable=False, primary_key=True), Column('timestamp', Integer, nullable=False, index=True), Column('userid', BigInteger(unsigned=True), index=True), Column('arcadeid', Integer, index=True), Column('type', String(64), nullable=False, index=True), Column('data', JSON, nullable=False), mysql_charset='utf8mb4', ) class NetworkData(BaseData): def get_all_news(self) -> List[News]: """ Grab all news in the system. Returns: A list of News objects sorted by timestamp. """
class BigIdMixin(object): id = Column(BigInteger(unsigned=True), primary_key=True, autoincrement=True)