class ApplyStatusModel(db.Model):
__tablename__ = 'apply_status'
# one to one
user_id = db.Column(db.String(32),
db.ForeignKey('user.user_id', ondelete='CASCADE', onupdate='CASCADE'), primary_key=True)
final_submit = db.Column(db.Boolean, nullable=False, default=False)
pass_status = db.Column(db.Boolean, nullable=False, default=False)
payment = db.Column(db.Boolean, nullable=False, default=False)
receipt = db.Column(db.Boolean, nullable=False, default=False)
receipt_code = db.Column(Integer(display_width=3, zerofill=True, unsigned=True),
unique=True, nullable=False, autoincrement=True)
exam_code = db.Column(db.String(6), nullable=True, unique=True)
created_at = db.Column(db.DateTime, nullable=False)
updated_at = db.Column(db.DateTime, nullable=False)
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 GraduateGradeModel(db.Model):
__tablename__ = 'graduate_grade'
# one to many
user_id = db.Column(db.String(32),
db.ForeignKey('user.user_id', ondelete='CASCADE', onupdate='CASCADE'), primary_key=True)
semester = db.Column(Integer(), primary_key=True)
korean = db.Column(db.Enum(GradeChoice), nullable=False, default=GradeChoice.X)
social = db.Column(db.Enum(GradeChoice), nullable=False, default=GradeChoice.X)
history = db.Column(db.Enum(GradeChoice), nullable=False, default=GradeChoice.X)
math = db.Column(db.Enum(GradeChoice), nullable=False, default=GradeChoice.X)
science = db.Column(db.Enum(GradeChoice), nullable=False, default=GradeChoice.X)
tech = db.Column(db.Enum(GradeChoice), nullable=False, default=GradeChoice.X)
english = db.Column(db.Enum(GradeChoice), nullable=False, default=GradeChoice.X)
created_at = db.Column(db.DateTime, nullable=False)
updated_at = db.Column(db.DateTime, nullable=False)
class MapStat(_Model):
__tablename__ = 'mapstat'
__table_args__ = (PrimaryKeyConstraint('key', 'lat', 'lon'), {
'mysql_engine': 'InnoDB',
'mysql_charset': 'utf8',
})
# lat/lon * 10000, so 12.3456 is stored as 123456
lat = Column(Integer, autoincrement=False)
lon = Column(Integer, autoincrement=False)
# mapped via MAPSTAT_TYPE
key = Column(SmallInteger, autoincrement=False)
value = Column(Integer(unsigned=True))
def __init__(self, *args, **kw):
if 'key' not in kw:
kw['key'] = MAPSTAT_TYPE['location']
super(MapStat, self).__init__(*args, **kw)
class IssueType(db.Model, BaseModelMixin):
"""Issue type object
Attributes:
issue_type_id (int): Unique issue type identifier
issue_type (str): Issue type name
"""
__tablename__ = 'issue_types'
issue_type_id = Column(Integer(unsigned=True),
primary_key=True,
autoincrement=True)
issue_type = Column(String(100), nullable=False, index=True)
@classmethod
def get(cls, issue_type):
"""Returns the IssueType object for `issue_type`. If no existing object was found, a new type will
be created in the database and returned
Args:
issue_type (str,int,IssueType): Issue type name, id or class
Returns:
:obj:`IssueType`
"""
if isinstance(issue_type, str):
obj = cls.query.filter_by(issue_type=issue_type).first()
elif isinstance(issue_type, int):
obj = cls.query.filter_by(issue_type_id=issue_type).first()
elif isinstance(issue_type, cls):
return issue_type
else:
obj = None
if not obj:
obj = cls()
obj.issue_type = issue_type
db.session.add(obj)
db.session.commit()
db.session.refresh(obj)
return obj
class Artist(Base):
"""Artist representation in Database"""
__tablename__ = 'artists'
artist = Column(Integer(unsigned=True), primary_key=True, autoincrement=True)
name = Column(String(255), unique=True)
@staticmethod
def get_artist(name):
"""returns an Artist object for name"""
metaartist = MetaArtist.get_metaartist(name)
if metaartist.artist is None:
artist = Artist(name=name)
metaartist.artist = artist
rfk.database.session.add(artist)
rfk.database.session.flush()
return artist
else:
return metaartist.artist
class Permission(Base):
__tablename__ = 'permissions'
permission = Column(Integer(unsigned=True),
primary_key=True,
autoincrement=True)
code = Column(String(25), unique=True)
name = Column(String(50))
@staticmethod
def get_permission(code):
return Permission.query.filter(Permission.code == code).one()
@staticmethod
def add_permission(code, name):
try:
return Permission.query.filter(Permission.code == code).one()
except exc.NoResultFound:
return Permission(code=code, name=name)
class Statistic(Base):
__tablename__ = 'statistics'
statistic = Column(Integer(unsigned=True),
primary_key=True,
autoincrement=True)
name = Column(String(50), nullable=False)
identifier = Column(String(50), unique=True, nullable=False)
def set(self, timestamp, value):
try:
ls = StatsistcsData.query.filter(
StatsistcsData.timestamp == timestamp,
StatsistcsData.statistic == self).one()
ls.value = value
rfk.database.session.flush()
except exc.NoResultFound:
ls = StatsistcsData(statistic=self,
timestamp=timestamp,
value=value)
rfk.database.session.add(ls)
rfk.database.session.flush()
def get(self, start=None, stop=None, num=None, reverse=False):
clauses = []
if start is not None:
clauses.append(StatsistcsData.timestamp >= start)
if stop is not None:
clauses.append(StatsistcsData.timestamp <= stop)
clauses.append(StatsistcsData.statistic == self)
qry = StatsistcsData.query.filter(*clauses)
if reverse:
qry = qry.order_by(StatsistcsData.timestamp.desc())
else:
qry = qry.order_by(StatsistcsData.timestamp.asc())
if num is not None:
qry = qry.limit(num)
return qry.yield_per(100)
def current_value(self):
ret = self.get(stop=now(), num=1, reverse=True)
if ret:
return ret[0]
else:
return None
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 Currency(Mixin, Base):
__tablename__ = 'currencies'
symbol = Column(String(10))
name = Column(String(50))
market_cap = Column(BigInteger(), default=0)
cmc_id = Column(String(50))
num_market_pairs = Column(Integer(10), default=0)
circulating_supply = Column(BigInteger(), default=0)
total_supply = Column(BigInteger(), default=0)
max_supply = Column(BigInteger(), default=0)
price = Column(Numeric(24, 12), default=0)
volume_24h = Column(Numeric(24, 12), default=0)
percent_change_1h = Column(Numeric(24, 12), default=0)
percent_change_24h = Column(Numeric(24, 12), default=0)
percent_change_7d = Column(Numeric(24, 12), default=0)
def __repr__(self):
return '{s.symbol}[{s.id}]'.format(s=self)
class EntryTable(Base):
__tablename__ = 't_entry'
__table_args__ = {'mysql_engine': 'InnoDB',
'mysql_charset': 'utf8'}
id = Column(Integer(unsigned=True), autoincrement=True, primary_key=True)
title = Column(String(length=255), nullable=False)
text = Column(Text, nullable=False)
username = Column(String(length=128), nullable=False)
created_date = Column(DateTime, default=func.now())
#user_id = Column(Integer, ForeignKey('t_user.id'), nullable=False)
#user = relationship("UserTable", backref='entry', lazy='join')
def __init__(self, title, text, username):
self.title = title
self.text = text
self.username = username
def __repr__(self):
return "<id:%s title:%s>" % (self.id, self.title)
class AuditLog(Model, BaseModelMixin):
"""Audit Log Event
Attributes:
audit_log_event_id (int): Internal unique ID
timestamp (datetime): Timestamp for the event
actor (str): Actor (user or subsystem) triggering the event
event (str): Action performed
data (dict): Any extra data necessary for describing the event
"""
__tablename__ = 'auditlog'
audit_log_event_id = Column(Integer(unsigned=True),
autoincrement=True,
primary_key=True)
timestamp = Column(DateTime,
nullable=False,
server_default=text('CURRENT_TIMESTAMP'))
actor = Column(String(100), nullable=False, index=True)
event = Column(String(50), nullable=False, index=True)
data = Column(JSON, nullable=False)
@classmethod
@deprecated(
'AuditLog.log is deprecated, use cloud_inquisitor.log.auditlog instead'
)
def log(cls, event=None, actor=None, data=None):
"""Generate and insert a new event
Args:
event (str): Action performed
actor (str): Actor (user or subsystem) triggering the event
data (dict): Any extra data necessary for describing the event
Returns:
`None`
"""
if 'auditlog' not in locals():
from cloud_inquisitor.log import auditlog
auditlog(event=event, actor=actor, data=data)
class CellBlacklist(_Model):
__tablename__ = 'cell_blacklist'
__table_args__ = ({
'mysql_engine': 'InnoDB',
'mysql_charset': 'utf8',
})
time = Column(DateTime)
radio = Column(TinyInteger, autoincrement=False, primary_key=True)
mcc = Column(SmallInteger, autoincrement=False, primary_key=True)
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)
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 User(Base):
""" Attributes for the User model. Custom MapperExtension declarative for
before insert and update methods. The migrate.versioning api does not
handle sqlalchemy.dialects.mysql for custom column attributes. I.E.
INTEGER(unsigned=True), so they need to be modified manually.
"""
__tablename__ = 'users'
__table_args__ = {'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8'}
## mapper extension declarative for before insert and before update
__mapper_args__ = {'extension': BaseExtension()}
id = Column('user_id', Integer(unsigned=True), primary_key=True)
email_address = Column(String(255),
unique=True,
index=True,
nullable=False)
created_at = Column(DateTime(), nullable=False)
updated_at = Column(DateTime(), nullable=False)
def __init__(self, email_address):
self.email_address = email_address
class CellArea(_Model):
__tablename__ = 'cell_area'
__table_args__ = {
'mysql_engine': 'InnoDB',
'mysql_charset': 'utf8',
}
created = Column(DateTime)
modified = Column(DateTime)
# lat/lon
lat = Column(Double(asdecimal=False))
lon = Column(Double(asdecimal=False))
# radio mapped via RADIO_TYPE
radio = Column(TinyInteger,
autoincrement=False, primary_key=True)
mcc = Column(SmallInteger,
autoincrement=False, primary_key=True)
mnc = Column(SmallInteger,
autoincrement=False, primary_key=True)
lac = Column(SmallInteger(unsigned=True),
autoincrement=False, primary_key=True)
range = Column(Integer)
avg_cell_range = Column(Integer)
num_cells = 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 'range' not in kw:
kw['range'] = 0
if 'avg_cell_range' not in kw:
kw['avg_cell_range'] = 0
if 'num_cells' not in kw:
kw['num_cells'] = 0
super(CellArea, self).__init__(*args, **kw)
class ResourceProperty(db.Model, BaseModelMixin):
"""Resource Property object"""
__tablename__ = 'resource_properties'
property_id = Column(Integer(unsigned=True),
primary_key=True,
autoincrement=True)
resource_id = Column(String(256),
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 AccountType(Model, BaseModelMixin):
__tablename__ = 'account_types'
account_type_id = Column(Integer(unsigned=True),
primary_key=True,
autoincrement=True)
account_type = Column(String(100), nullable=False, index=True, unique=True)
@classmethod
def get(cls, account_type):
if isinstance(account_type, str):
obj = getattr(
db, cls.__name__).find_one(cls.account_type == account_type)
elif isinstance(account_type, int):
obj = getattr(
db, cls.__name__).find_one(cls.account_type_id == account_type)
elif isinstance(account_type, cls):
return account_type
else:
obj = None
if not obj:
if type(account_type) == str:
obj = cls()
obj.account_type = account_type
db.session.add(obj)
db.session.commit()
db.session.refresh(obj)
else:
raise ValueError(
'Unable to find or create a new account type: {}'.format(
account_type))
return obj
class GedScoreModel(db.Model):
__tablename__ = 'ged_score'
# one to one
user_id = db.Column(db.String(32),
db.ForeignKey('user.user_id',
ondelete='CASCADE',
onupdate='CASCADE'),
primary_key=True)
grade = db.Column(Double(unsigned=True), nullable=False, default=0.0)
conversion_score = db.Column(Double(unsigned=True),
nullable=False,
default=0.0)
attendance_score = db.Column(Integer(unsigned=True),
nullable=False,
default=15)
volunteer_score = db.Column(Double(unsigned=True),
nullable=False,
default=0.0)
final_score = db.Column(Double(), nullable=False)
created_at = db.Column(db.DateTime, nullable=False)
updated_at = db.Column(db.DateTime, nullable=False)
class GraduateScoreModel(db.Model):
__tablename__ = 'graduate_score'
# one to one
user_id = db.Column(db.String(32),
db.ForeignKey('user.user_id', ondelete='CASCADE', onupdate='CASCADE'), primary_key=True)
first_grade = db.Column(Double(unsigned=True), nullable=False, default=0.0)
second_grade = db.Column(Double(unsigned=True), nullable=False, default=0.0)
third_grade = db.Column(Double(unsigned=True), nullable=False, default=0.0)
conversion_score = db.Column(Double(unsigned=True), nullable=False, default=0.0)
attendance_score = db.Column(Integer(unsigned=True), nullable=False, default=0)
volunteer_score = db.Column(Double(unsigned=True), nullable=False, default=0.0)
final_score = db.Column(Double(), nullable=False)
volunteer_time = db.Column(Integer(unsigned=True), nullable=False, default=0)
period_cut = db.Column(Integer(unsigned=True), nullable=False, default=0)
full_cut = db.Column(Integer(unsigned=True), nullable=False, default=0)
late = db.Column(Integer(unsigned=True), nullable=False, default=0)
early_leave = db.Column(Integer(unsigned=True), nullable=False, default=0)
created_at = db.Column(db.DateTime, nullable=False)
updated_at = db.Column(db.DateTime, nullable=False)
class Show(Base):
"""Show"""
__tablename__ = 'shows'
show = Column(Integer(unsigned=True), primary_key=True, autoincrement=True)
series_id = Column("series", Integer(unsigned=True), ForeignKey('series.series',
onupdate="CASCADE",
ondelete="RESTRICT"))
series = relationship("Series", backref=backref('shows'))
logo = Column(String(255))
begin = Column(UTCDateTime, default=now)
end = Column(UTCDateTime)
updated = Column(UTCDateTime, default=now)
name = Column(String(50))
description = Column(Text)
flags = Column(Integer(unsigned=True), default=0)
FLAGS = SET(['DELETED', 'PLANNED', 'UNPLANNED', 'RECORD'])
@hybrid_property
def length(self):
return self.end - self.begin
@hybrid_method
def contains(self, point):
return (self.begin <= point) & (point < self.end)
@hybrid_method
def intersects(self, other):
return self.contains(other.begin) | self.contains(other.end)
def end_show(self):
"""ends the Show
raises exception if the show is planned since it doesn't need to be ended"""
if self.flags & Show.FLAGS.PLANNED:
raise Exception
self.end = now()
rfk.database.session.flush()
def add_tags(self, tags):
"""adds a list of Tags to the Show"""
for tag in tags:
self.add_tag(tag=tag)
def sync_tags(self, tags):
old_tags = []
for tag in self.tags:
old_tags.append(tag.tag)
for tag in tags:
if tag in old_tags:
old_tags.remove(tag)
self.add_tag(tag=tag)
for tag in old_tags:
ShowTag.query.filter(ShowTag.show == self,
ShowTag.tag == tag).delete()
rfk.database.session.flush()
def add_tag(self, tag=None, name=None):
"""adds a Tag to the Show either by object or by identifier"""
assert tag or name
if tag is None:
tag = Tag.get_tag(name)
if tag is None:
return False
try:
ShowTag.query.filter(ShowTag.show == self,
ShowTag.tag == tag).one()
return False
except exc.NoResultFound:
self.tags.append(ShowTag(tag))
rfk.database.session.flush()
return True
def add_user(self, user, role=None):
if role is None:
role = Role.get_role('host')
try:
us = UserShow.query.filter(UserShow.user == user,
UserShow.show == self).one()
if us.role != role:
us.role = role
rfk.database.session.flush()
return us
except exc.NoResultFound:
us = UserShow(show=self, user=user, role=role)
rfk.database.session.add(us)
rfk.database.session.flush()
return us
def remove_user(self, user):
"""removes the association to user"""
UserShow.query.filter(UserShow.user == user,
UserShow.show == self).delete()
def get_usershow(self, user):
try:
return UserShow.query.filter(UserShow.user == user,
UserShow.show == self).one()
except exc.NoResultFound:
return None
@staticmethod
def get_current_show(user=None, only_planned=False):
"""returns the current show"""
clauses = []
clauses.append((between(datetime.utcnow(), Show.begin, Show.end)) | (Show.end == None))
clauses.append(UserShow.user == user)
if only_planned:
clauses.append(Show.flags == Show.FLAGS.PLANNED)
shows = Show.query.join(UserShow).filter(*clauses).all()
if len(shows) == 1:
return shows[0]
elif len(shows) > 1:
for show in shows:
if show.flags & Show.FLAGS.PLANNED:
return show
return shows[0]
else:
return None
@staticmethod
def get_active_show():
try:
return Show.query.join(UserShow).filter(UserShow.status == UserShow.STATUS.STREAMING).one()
except exc.NoResultFound:
return None
def get_active_user(self):
try:
return UserShow.query.filter(UserShow.show == self,
UserShow.status == UserShow.STATUS.STREAMING).one().user
except exc.NoResultFound:
return None
def get_logo(self):
"""return the logourl for this show
falls back to serieslogo if set"""
if self.logo is not None:
return self.logo
elif self.series is not None:
return self.series.logo
def get_duration(self):
if self.end is not None:
return (self.end - self.begin).total_seconds()
else:
return 0
@property
def link(self):
return url_for('show.show_view', show=self.show)
def is_fulfilled(self):
""" this function returns True under the folling circumstances:
somebody streamed or is streaming, or
this show has not ended yet
"""
if self.end > now():
return True
for user in self.users:
if user.status > UserShow.STATUS.UNKNOWN:
return True
return False
def __repr__(self):
return "<rfk.database.show.Show id=%d flags=%s name=%s >" % (self.show, Show.FLAGS.name(self.flags), self.name)
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 CellKeyMixin(CellAreaKeyMixin):
_hashkey_cls = CellKey
cid = Column(Integer(unsigned=True), autoincrement=False)
class Cell(_Model):
__tablename__ = 'cell'
__table_args__ = (UniqueConstraint('radio',
'mcc',
'mnc',
'lac',
'cid',
name='cell_idx_unique'),
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',
})
id = Column(BigInteger(unsigned=True),
primary_key=True,
autoincrement=True)
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)
# 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(SmallInteger)
lac = Column(Integer)
cid = Column(Integer)
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:
kw['lac'] = -1
if 'cid' not in kw:
kw['cid'] = -1
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)
from flask_bcrypt import check_password_hash, generate_password_hash
from flask_user import UserMixin
# Define the WSGI application object
app = Flask(__name__, instance_relative_config=True)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_DATABASE_URI'] = os.getenv('SQLALCHEMY_DATABASE_URI')
VAULT_SEED = os.getenv('VAULT_SEED')
db = SQLAlchemy(app)
Base = db.Model
db_session = db.session
engine = db.engine
FKInteger = Integer(10, unsigned=True)
VAL_SYM = 'BTC'
DUST_AMT = 9e-8
log = logging.getLogger(__name__)
@event.listens_for(db.engine, "engine_connect")
def ping_connection(connection, branch):
if branch:
return
save_should_close_with_result = connection.should_close_with_result
connection.should_close_with_result = False
try:
class Cube(Mixin, Base):
__tablename__ = 'cubes'
user_id = Column(FKInteger, ForeignKey('users.id'))
algorithm_id = Column(FKInteger, ForeignKey('algorithms.id'))
trading_status = Column(Enum('live', 'off'), default='live')
closed_at = Column(DateTime, nullable=True)
suspended_at = Column(DateTime, nullable=True)
fiat_id = Column(FKInteger, ForeignKey('currencies.id'))
threshold = Column(Numeric(20, 2), nullable=True)
rebalance_interval = Column(Integer(10), nullable=True)
balanced_at = Column(DateTime, nullable=True)
reallocated_at = Column(DateTime, nullable=True)
risk_tolerance = Column(Integer(2))
index_id = Column(FKInteger, ForeignKey('indices.id'), nullable=True)
auto_rebalance = Column(Boolean, nullable=True)
unrecognized_activity = Column(Boolean, nullable=True)
requires_exchange_transfer = Column(Boolean, default=False)
name = Column(String(20))
exchange_id = Column(FKInteger, ForeignKey('exchanges.id'))
update_charts = Column(Boolean, default=False)
user = relationship('User')
algorithm = relationship('Algorithm')
fiat = relationship('Currency')
cube_cache = relationship('CubeCache')
exchange = relationship('Exchange')
index = relationship('Indices')
api_connections = relationship('Connection')
connections = relationship(
'Connection',
collection_class=attribute_mapped_collection('exchange.name'),
backref='cube')
allocations = relationship(
'AssetAllocation',
collection_class=attribute_mapped_collection('currency.symbol'),
backref='cube')
balances = relationship('Balance')
custom_portfolios = relationship('CustomPortfolio', backref='cube')
orders = relationship('Order')
all_orders = relationship(
'Order',
collection_class=attribute_mapped_collection('order_id'),
backref='cube',
cascade='all, delete, delete-orphan')
transactions = relationship('Transaction', lazy='dynamic', backref='cube')
@property
def val_cur(self):
### TO DO
## Should be a Cube attribute (i.e. cube.var_cur) which user can select
return Currency.query.filter_by(symbol='BTC').first()
@property
def is_rebalancing(self):
if self.reallocated_at and self.balanced_at:
if self.reallocated_at > self.balanced_at:
return True
else:
return False
if self.reallocated_at:
return True
else:
return False
@property
def supported_assets(self):
# Check to see if assets are available on connected exchanges
supported_assets = []
for conn in self.connections.values():
ex_pairs = ExPair.query.filter_by(exchange_id=conn.exchange.id,
active=True)
for pair in ex_pairs:
if pair.quote_currency not in supported_assets:
supported_assets.append(pair.quote_currency)
if pair.base_currency not in supported_assets:
supported_assets.append(pair.base_currency)
# Order by market capitalization (highest to lowest)
ordered_assets = []
for cur in supported_assets:
ordered_assets.append((cur.market_cap, cur.symbol))
ordered_assets.sort(key=lambda x: x[0] or 0, reverse=True)
sorted_assets = []
for x in ordered_assets:
sorted_assets.append(x[1])
return sorted_assets
def get_external_balances(self):
accounts = list(map(lambda ex: ex.balances, self.external_addresses))
flattened_accounts = [
balance for account in accounts for balance in account
]
return flattened_accounts
def log_user_action(self, action_name, details=None):
a = CubeUserAction(cube_id=self.id,
action=action_name,
details=details)
db_session.add(a)
db_session.commit()
def data_frame(self, query, columns):
# Takes a sqlalchemy query and a list of columns, returns a dataframe.
def make_row(x):
return dict([(c, getattr(x, c)) for c in columns])
return pd.DataFrame([make_row(x) for x in query])
def get_val_btc(self, bal, ex_id, cur_id):
if cur_id == 2: # If BTC, return balance
return bal
else:
try:
# Find BTC ex_pair for incoming cur_id
ex_pair = ExPair.query.filter_by(
exchange_id=ex_id,
quote_symbol='BTC',
base_currency_id=cur_id).first()
if ex_pair == None:
ex_pair = ExPair.query.filter_by(
exchange_id=ex_id,
quote_currency_id=cur_id,
base_symbol='BTC').first()
close = ex_pair.get_close()
if ex_pair.quote_symbol in [
'USDT', 'USDC', 'TUSD', 'GUSD', 'USD', 'EUR', 'GBP'
]: # Fiat currencies
val_btc = bal / Decimal(close)
else:
val_btc = bal * Decimal(close)
except:
val_btc = 0
return val_btc
def get_ex_bals(self):
balances = {}
for bal in self.balances:
if not bal.exchange.name in balances:
balances[bal.exchange.name] = [bal]
else:
balances[bal.exchange.name].append(bal)
return balances
def valuations(self):
# sets val_btc and val_fiat for individual balances, and
# returns dict of total btc and fiat valuations
btc = Currency.query.filter_by(symbol='BTC').one()
ep = IndexPair.query.filter_by(quote_currency=self.user.fiat,
base_currency=btc).first()
log.debug(ep)
btc_price = float(ep.get_close())
log.debug('BTC price', btc_price)
val_btc = 0
for b in self.balances:
log.debug(b)
if b.currency == btc:
b.val_btc = float(b.total)
elif not b.total:
b.val_btc = 0
else:
q = IndexPair.query.filter_by(active=True)
ep = q.filter_by(quote_currency=btc,
base_currency=b.currency).first()
log.debug(ep)
if not ep:
ep = q.filter_by(base_currency=btc,
quote_currency=b.currency).first()
flipped = True
else:
flipped = False
# Use ExPair if no IndexPair available
if not ep:
q = ExPair.query.filter_by(active=True)
ep = q.filter_by(quote_currency=btc,
base_currency=b.currency).first()
if not ep:
ep = q.filter_by(base_currency=btc,
quote_currency=b.currency).first()
flipped = True
else:
flipped = False
log.debug(ep)
price = float(ep.get_close())
log.debug(price)
if flipped:
price = 1 / price
b.val_btc = float(b.total) * price
log.debug(b.val_btc)
val_btc += b.val_btc
# b.val_fiat = b.val_btc * btc_price
val_fiat = val_btc * btc_price
return {'val_btc': val_btc, 'val_fiat': val_fiat}
def tx_to_ledger(self, transactions=None, start_date=None, end_date=None):
if transactions is None:
transactions = self.transactions
if start_date:
transactions = transactions.filter(
Transaction.created_at >= start_date)
if end_date:
transactions = transactions.filter(
Transaction.created_at <= end_date)
txs = [(tx.created_at, tx.type, tx.ex_pair.exchange.name,
tx.ex_pair.quote_currency.symbol, tx.quote_amount,
tx.ex_pair.base_currency.symbol, tx.base_amount)
for tx in transactions]
ledger = []
for tx in txs:
if tx[4]:
ledger.append((tx[0], tx[1], tx[2], tx[3], float(tx[4])))
if tx[6]:
ledger.append((tx[0], tx[1], tx[2], tx[5], float(tx[6])))
return ledger
def get_trades(self):
return self.transactions.filter(Transaction.type.in_(["buy",
"sell"])).all()
def __repr__(self):
return '[Cube %d]' % self.id
class CellAreaMixin(CellAreaKeyMixin, TimeTrackingMixin, PositionMixin):
range = Column(Integer)
avg_cell_range = Column(Integer)
num_cells = Column(Integer(unsigned=True))
class IdMixin(object):
id = Column(Integer(unsigned=True), primary_key=True, autoincrement=True)
class Account(Model, BaseModelMixin):
__tablename__ = 'accounts'
account_id = Column(Integer(unsigned=True),
primary_key=True,
autoincrement=True)
account_name = Column(String(256), nullable=False, index=True, unique=True)
account_type_id = Column(Integer(unsigned=True),
ForeignKey(AccountType.account_type_id,
name='fk_account_account_type_id',
ondelete='CASCADE'),
nullable=False,
index=True)
contacts = Column(JSON, nullable=False)
enabled = Column(SmallInteger(unsigned=True), nullable=False, default=1)
required_roles = Column(JSON, nullable=True)
properties = relationship('AccountProperty',
lazy='select',
uselist=True,
primaryjoin=account_id == foreign(
AccountProperty.account_id),
cascade='all, delete-orphan')
@staticmethod
def get(account_id, account_type_id=None):
"""Return account by ID and type
Args:
account_id (`int`, `str`): Unique Account identifier
account_type_id (str): Type of account to get
Returns:
:obj:`Account`: Returns an Account object if found, else None
"""
if type(account_id) == str:
args = {'account_name': account_id}
else:
args = {'account_id': account_id}
if account_type_id:
args['account_type_id'] = account_type_id
return db.Account.find_one(**args)
def user_has_access(self, user):
"""Check if a user has access to view information for the account
Args:
user (:obj:`User`): User object to check
Returns:
True if user has access to the account, else false
"""
if ROLE_ADMIN in user.roles:
return True
# Non-admin users should only see active accounts
if self.enabled:
if not self.required_roles:
return True
for role in self.required_roles:
if role in user.roles:
return True
return False
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 Resource(Model, BaseModelMixin):
"""Resource object
Attributes:
resource_id (`str`): Unique Resource identifier
account_id (`int`): ID of the account owning the resource
location (`str`, optional): Optional location of the resource (eg. aws region)
resource_type (`str`): :obj:`ResourceType` reference
tags (`list` of :obj:`Tag`): List of tags applied to the volume
properties (`list` of :obj:`ResourceProperty`): List of properties of the resource
"""
__tablename__ = 'resources'
resource_id = Column(String(256), primary_key=True)
account_id = Column(Integer(unsigned=True),
ForeignKey('accounts.account_id',
name='fk_resource_account_id',
ondelete='CASCADE'),
index=True)
location = Column(String(50), nullable=True, index=True)
resource_type_id = Column(Integer(unsigned=True),
ForeignKey(
'resource_types.resource_type_id',
name='fk_resource_types_resource_type_id',
ondelete='CASCADE'),
index=True)
tags = relationship('Tag',
lazy='select',
uselist=True,
primaryjoin=resource_id == foreign(Tag.resource_id),
order_by=Tag.key,
cascade='all, delete-orphan')
properties = relationship('ResourceProperty',
lazy='select',
uselist=True,
primaryjoin=resource_id == foreign(
ResourceProperty.resource_id),
cascade='all, delete-orphan')
account = relationship('Account',
lazy='joined',
uselist=False,
primaryjoin=account_id == foreign(
Account.account_id),
viewonly=True)
children = relationship(
'Resource',
lazy='select',
uselist=True,
secondary='resource_mappings',
primaryjoin='Resource.resource_id == ResourceMapping.parent',
secondaryjoin='Resource.resource_id == ResourceMapping.child',
cascade='all, delete',
backref='parents')
@staticmethod
def get(resource_id):
"""Return resource by ID
Args:
resource_id (str): Unique Resource identifier
Returns:
:obj:`Resource`: Returns Resource object if found, else None
"""
return db.Resource.find_one(Resource.resource_id == resource_id)
def __repr__(self):
return '<Resource({})>'.format(self.resource_id)
