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)
