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
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 Stat(IdMixin, _Model):
__tablename__ = 'stat'
_indices = (UniqueConstraint('key', 'time', name='stat_key_time_unique'), )
key = Column(TinyIntEnum(StatKey))
time = Column(Date)
value = Column(Integer(unsigned=True))
class CellAreaKeyMixin(HashKeyMixin):
_hashkey_cls = CellAreaKey
radio = Column(TinyIntEnum(Radio), autoincrement=False)
mcc = Column(SmallInteger, autoincrement=False)
mnc = Column(SmallInteger, autoincrement=False)
lac = Column(SmallInteger(unsigned=True), autoincrement=False)
class Stat(_Model):
"""Stat model."""
__tablename__ = "stat"
_indices = (PrimaryKeyConstraint("key", "time"), )
key = Column(TinyIntEnum(StatKey))
time = Column(Date)
value = Column(BigInteger(unsigned=True))
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 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 Score(IdMixin, _Model):
__tablename__ = 'score'
_indices = (UniqueConstraint('userid',
'key',
'time',
name='score_userid_key_time_unique'), )
userid = Column(Integer(unsigned=True), index=True)
key = Column(TinyIntEnum(ScoreKey))
time = Column(Date)
value = Column(Integer)
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 Score(_Model):
# BBB
__tablename__ = 'score'
_indices = (
PrimaryKeyConstraint('key', 'userid', 'time'),
)
# this is a foreign key to user.id
userid = Column(Integer(unsigned=True), autoincrement=False)
key = Column(TinyIntEnum(ScoreKey), autoincrement=False)
time = Column(Date)
value = Column(Integer)
class CellBlocklist(_Model):
__tablename__ = 'cell_blacklist'
_indices = (PrimaryKeyConstraint('radio', 'mcc', 'mnc', 'lac', 'cid'), )
radio = Column(TinyIntEnum(Radio), autoincrement=False, default=None)
mcc = Column(SmallInteger, autoincrement=False, default=None)
mnc = Column(SmallInteger, autoincrement=False, default=None)
lac = Column(SmallInteger(unsigned=True),
autoincrement=False,
default=None)
cid = Column(Integer(unsigned=True), autoincrement=False, default=None)
time = Column(DateTime)
count = Column(Integer)
class BaseCell(StationMixin):
_valid_schema = ValidCellShardSchema()
cellid = Column(CellIdColumn(11))
radio = Column(TinyIntEnum(Radio), autoincrement=False, nullable=False)
mcc = Column(SmallInteger, autoincrement=False, nullable=False)
mnc = Column(SmallInteger, autoincrement=False, nullable=False)
lac = Column(SmallInteger(unsigned=True),
autoincrement=False,
nullable=False)
cid = Column(Integer(unsigned=True), autoincrement=False, nullable=False)
psc = Column(SmallInteger, autoincrement=False)
@classmethod
def validate(cls, entry, _raise_invalid=False, **kw):
validated = super(BaseCell,
cls).validate(entry,
_raise_invalid=_raise_invalid,
**kw)
if validated is not None:
if 'cellid' not in validated:
validated['cellid'] = (
validated['radio'],
validated['mcc'],
validated['mnc'],
validated['lac'],
validated['cid'],
)
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
@property
def areaid(self):
return encode_cellarea(self.radio, self.mcc, self.mnc, self.lac)
@property
def unique_key(self):
return encode_cellid(*self.cellid)
class Cell(BboxMixin, PositionMixin, TimeTrackingMixin, _Model): # BBB
__tablename__ = 'cell'
_indices = (
PrimaryKeyConstraint('radio', 'mcc', 'mnc', 'lac', 'cid'),
Index('cell_created_idx', 'created'),
Index('cell_modified_idx', 'modified'),
)
radio = Column(TinyIntEnum(Radio), autoincrement=False, default=None)
mcc = Column(SmallInteger, autoincrement=False, default=None)
mnc = Column(SmallInteger, autoincrement=False, default=None)
lac = Column(SmallInteger(unsigned=True),
autoincrement=False,
default=None)
cid = Column(Integer(unsigned=True), autoincrement=False, default=None)
psc = Column(SmallInteger, autoincrement=False)
radius = Column(Integer)
samples = Column(Integer(unsigned=True))
new_measures = Column(Integer(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))
class CellShard(StationMixin):
"""Cell shard."""
_shards = CELL_SHARDS
_valid_schema = ValidCellShardSchema()
cellid = Column(CellIdColumn(11))
radio = Column(TinyIntEnum(Radio), nullable=False)
mcc = Column(SmallInteger, nullable=False)
mnc = Column(SmallInteger, nullable=False)
lac = Column(SmallInteger(unsigned=True), nullable=False)
cid = Column(Integer(unsigned=True), nullable=False)
psc = Column(SmallInteger)
@declared_attr
def __table_args__(cls): # NOQA
_indices = (
PrimaryKeyConstraint('cellid'),
UniqueConstraint('radio',
'mcc',
'mnc',
'lac',
'cid',
name='%s_cellid_unique' % cls.__tablename__),
Index('%s_region_idx' % cls.__tablename__, 'region'),
Index('%s_created_idx' % cls.__tablename__, 'created'),
Index('%s_modified_idx' % cls.__tablename__, 'modified'),
Index('%s_latlon_idx' % cls.__tablename__, 'lat', 'lon'),
)
return _indices + (cls._settings, )
@property
def unique_key(self):
return encode_cellid(*self.cellid)
@classmethod
def validate(cls, entry, _raise_invalid=False, **kw):
validated = super(CellShard,
cls).validate(entry,
_raise_invalid=_raise_invalid,
**kw)
if validated is not None:
if 'cellid' not in validated:
validated['cellid'] = (
validated['radio'],
validated['mcc'],
validated['mnc'],
validated['lac'],
validated['cid'],
)
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
@classmethod
def create(cls, _raise_invalid=False, **kw):
"""
Returns an instance of the correct shard model class, if the
passed in keyword arguments pass schema validation,
otherwise returns None.
"""
validated = cls.validate(kw, _raise_invalid=_raise_invalid)
if validated is None: # pragma: no cover
return None
shard = cls.shard_model(validated['radio'])
return shard(**validated)
@classmethod
def shard_id(cls, radio):
"""
Given a radio type return the correct shard id.
"""
if type(radio) == bytes and len(radio) == 11:
# extract radio from cellid
radio = decode_cellid(radio)[0]
if type(radio) == Radio:
return radio.name
if isinstance(radio, tuple) and len(radio) == 5:
return radio[0].name
try:
return Radio[radio].name
except KeyError:
pass
return None
@classmethod
def shard_model(cls, radio):
"""
Given a radio type return the correct DB model class.
"""
return cls._shards.get(cls.shard_id(radio), None)
@classmethod
def shards(cls):
"""Return a dict of shard id to model classes."""
return cls._shards
@classmethod
def export_header(cls):
return ('radio,mcc,mnc,lac,cid,psc,'
'lat,lon,max_lat,min_lat,max_lon,min_lon,'
'radius,region,samples,source,weight,'
'created,modified,last_seen,'
'block_first,block_last,block_count')
@classmethod
def export_stmt(cls):
stmt = '''SELECT
`cellid` AS `export_key`,
CONCAT_WS(",",
CASE radio
WHEN 0 THEN "GSM"
WHEN 2 THEN "WCDMA"
WHEN 3 THEN "LTE"
ELSE ""
END,
`mcc`,
`mnc`,
`lac`,
`cid`,
COALESCE(`psc`, ""),
COALESCE(ROUND(`lat`, 7), ""),
COALESCE(ROUND(`lon`, 7), ""),
COALESCE(ROUND(`max_lat`, 7), ""),
COALESCE(ROUND(`min_lat`, 7), ""),
COALESCE(ROUND(`max_lon`, 7), ""),
COALESCE(ROUND(`min_lon`, 7), ""),
COALESCE(`radius`, "0"),
COALESCE(`region`, ""),
COALESCE(`samples`, "0"),
COALESCE(`source`, ""),
COALESCE(`weight`, "0"),
COALESCE(UNIX_TIMESTAMP(`created`), ""),
COALESCE(UNIX_TIMESTAMP(`modified`), ""),
COALESCE(UNIX_TIMESTAMP(`last_seen`), ""),
COALESCE(UNIX_TIMESTAMP(`block_first`), ""),
COALESCE(UNIX_TIMESTAMP(`block_last`), ""),
COALESCE(`block_count`, "0")
) AS `export_value`
FROM %s
WHERE `cellid` > :export_key
ORDER BY `cellid`
LIMIT :limit
''' % cls.__tablename__
return stmt.replace('\n', ' ')
class CellAreaMixin(PositionMixin, TimeTrackingMixin, CreationMixin,
ScoreMixin):
_valid_schema = ValidCellAreaSchema()
areaid = Column(CellAreaColumn(7))
radio = Column(TinyIntEnum(Radio), autoincrement=False, nullable=False)
mcc = Column(SmallInteger, autoincrement=False, nullable=False)
mnc = Column(SmallInteger, autoincrement=False, nullable=False)
lac = Column(SmallInteger(unsigned=True),
autoincrement=False,
nullable=False)
radius = Column(Integer)
region = Column(String(2))
avg_cell_radius = Column(Integer(unsigned=True))
num_cells = Column(Integer(unsigned=True))
def score_sample_weight(self):
# treat areas for which we get the exact same
# cells multiple times as if we only got 1 cell
samples = self.num_cells
if samples > 1 and not self.radius:
samples = 1
# sample_weight is a number between:
# 1.0 for 1 sample
# 1.41 for 2 samples
# 10 for 100 samples
# we use a sqrt scale instead of log2 here, as this represents
# the number of cells in an area and not the sum of samples
# from all cells in the area
return min(math.sqrt(max(samples, 1)), 10.0)
@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 CellShard(StationMixin):
"""Cell shard."""
_shards = CELL_SHARDS
_valid_schema = ValidCellShardSchema()
cellid = Column(CellIdColumn(11))
radio = Column(TinyIntEnum(Radio), autoincrement=False, nullable=False)
mcc = Column(SmallInteger, autoincrement=False, nullable=False)
mnc = Column(SmallInteger, autoincrement=False, nullable=False)
lac = Column(SmallInteger(unsigned=True),
autoincrement=False,
nullable=False)
cid = Column(Integer(unsigned=True), autoincrement=False, nullable=False)
psc = Column(SmallInteger, autoincrement=False)
@declared_attr
def __table_args__(cls): # NOQA
_indices = (
PrimaryKeyConstraint('cellid'),
UniqueConstraint('radio',
'mcc',
'mnc',
'lac',
'cid',
name='%s_cellid_unique' % cls.__tablename__),
Index('%s_region_idx' % cls.__tablename__, 'region'),
Index('%s_created_idx' % cls.__tablename__, 'created'),
Index('%s_modified_idx' % cls.__tablename__, 'modified'),
Index('%s_latlon_idx' % cls.__tablename__, 'lat', 'lon'),
)
return _indices + (cls._settings, )
@property
def areaid(self):
return encode_cellarea(self.radio, self.mcc, self.mnc, self.lac)
@property
def unique_key(self):
return encode_cellid(*self.cellid)
@classmethod
def validate(cls, entry, _raise_invalid=False, **kw):
validated = super(CellShard,
cls).validate(entry,
_raise_invalid=_raise_invalid,
**kw)
if validated is not None:
if 'cellid' not in validated:
validated['cellid'] = (
validated['radio'],
validated['mcc'],
validated['mnc'],
validated['lac'],
validated['cid'],
)
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
@classmethod
def create(cls, _raise_invalid=False, **kw):
"""
Returns an instance of the correct shard model class, if the
passed in keyword arguments pass schema validation,
otherwise returns None.
"""
validated = cls.validate(kw, _raise_invalid=_raise_invalid)
if validated is None: # pragma: no cover
return None
shard = cls.shard_model(validated['radio'])
return shard(**validated)
@classmethod
def shard_id(cls, radio):
"""
Given a radio type return the correct shard id.
"""
if type(radio) == bytes and len(radio) == 11:
# extract radio from cellid
radio = decode_cellid(radio)[0]
if type(radio) == Radio:
return radio.name
if isinstance(radio, tuple) and len(radio) == 5:
return radio[0].name
try:
return Radio[radio].name
except KeyError:
pass
return None
@classmethod
def shard_model(cls, radio):
"""
Given a radio type return the correct DB model class.
"""
return cls._shards.get(cls.shard_id(radio), None)
@classmethod
def shards(cls):
"""Return a dict of shard id to model classes."""
return cls._shards