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