class ProjectCoin(db.Model):
"""项目方支持的币种"""
__tablename__ = 'project_coin'
__table_args__ = (
UniqueConstraint(
'project_id', 'coin_id', name='uk_project_id_coin_id'), {
'mysql_engine': "INNODB"})
id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
project_id = db.Column(db.Integer, nullable=False, comment="项目 ID")
coin_id = db.Column(db.Integer, nullable=False, comment="主链币 ID")
hot_address = db.Column(db.VARCHAR(128), nullable=False, comment="热钱包地址")
hot_secret = db.Column(db.VARCHAR(128), nullable=False, comment="热钱包密钥, 保留字段, 未使用")
hot_pb = db.Column(db.TEXT, comment="热钱包密钥加密公钥")
hot_pk = db.Column(db.TEXT, comment="热钱包密钥加密私钥")
gas = db.Column(db.VARCHAR(64), nullable=False, default='150000',
comment="gas, 默认150000, 0为自动处理")
gas_price = db.Column(db.VARCHAR(64), nullable=False, default=str(20 * 1000 * 1000 * 1000),
comment="gasPrice, 默认20G, 0为自动处理")
fee = db.Column(db.VARCHAR(64), nullable=False, default=0, comment="真实手续费")
cold_address = db.Column(db.VARCHAR(128), default=None, comment="冷钱包地址")
fee_address = db.Column(db.VARCHAR(128), default=None, comment="手续费钱包地址")
last_collection_time = db.Column(db.DateTime, default=None,
comment="最后归集时间", onupdate=datetime.now)
create_time = db.Column(db.DateTime, nullable=False,
comment="创建时间", default=datetime.now)
update_time = db.Column(db.DateTime, nullable=False,
comment="更新时间", default=datetime.now, onupdate=datetime.now)
project = orm.relationship('Project',
primaryjoin="ProjectCoin.project_id==Project.id",
foreign_keys="ProjectCoin.project_id",
backref="ProjectCoin")
coin = orm.relationship('Coin',
primaryjoin="ProjectCoin.project_id==Coin.id",
foreign_keys="ProjectCoin.project_id",
backref="ProjectCoin")
def __str__(self):
return "{id}-{project_id}".format(id=self.id, project_id=self.project_id)
@staticmethod
def get_pro_coin_by_pid_cname(project_id, coin_name):
"""根据币种名称及项目方ID 获取项目方支持币种"""
session = db.session()
project_coin = session.query(
ProjectCoin,
Coin).join(
Coin,
ProjectCoin.coin_id == Coin.id).filter(
ProjectCoin.project_id == project_id,
Coin.name == coin_name)
return project_coin.first()
class Membership(db.Model):
__tablename__ = "memberships"
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey("users.id"))
group_id = db.Column(db.Integer, db.ForeignKey("groups.id"))
user = orm.relationship(User,
backref=orm.backref("orders",
cascade="all, delete-orphan"))
group = orm.relationship(Group,
backref=orm.backref("groups",
cascade="all, delete-orphan"))
class Project(db.Model):
"""项目方"""
__tablename__ = 'project'
__table_args__ = (
{'mysql_engine': "INNODB"}
)
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.VARCHAR(32), comment="项目名称")
callback_url = db.Column(db.VARCHAR(256),
comment="回调地址, 示例http[s]://[user]:[passwd]@[ip|domain]:[port]")
access_key = db.Column(db.VARCHAR(128), comment="回调签名 access_key")
secret_key = db.Column(db.VARCHAR(128), comment="回调签名 secret_key")
create_time = db.Column(db.DateTime, nullable=False,
comment="创建时间", default=datetime.now)
update_time = db.Column(db.DateTime, nullable=False,
comment="更新时间", default=datetime.now, onupdate=datetime.now)
project_coin = orm.relationship('ProjectCoin',
primaryjoin="Project.id==ProjectCoin.project_id",
foreign_keys="Project.id",
backref="Project")
def __str__(self):
return "{id}-{name}-{access_key}-{callback_url}".format(
id=self.id, name=self.name, access_key=self.access_key, callback_url=self.callback_url)
class GameResult(DB.Model):
id = DB.Column(DB.Integer, primary_key=True)
winner_id = DB.Column(DB.Integer, DB.ForeignKey('player.id'), nullable=True)
winner = orm.relationship("Player")
timestamp = DB.Column(DB.DateTime, server_default=sqlalchemy.sql.func.now(), index=True)
def __repr__(self):
return f'<GameResult {self.winner} {self.timestamp}>'
class GameInvitation(DB.Model):
id = DB.Column(DB.Integer, primary_key=True)
invitee_id = DB.Column(DB.Integer, DB.ForeignKey('player.id'), nullable=False)
invitee = orm.relationship("Player")
choice_id = DB.Column(DB.Integer)
def __repr__(self):
return f'<GameSession {self.id}>'
class AddressTransaction(db.Model):
__tablename__ = "address_transactions"
id = db.Column(db.Integer, primary_key=True)
address_id = db.Column(db.Integer, db.ForeignKey("addresses.id"))
transaction_id = db.Column(db.Integer, db.ForeignKey("transactions.id"))
address = orm.relationship(Address,
backref=orm.backref(
"addresses", cascade="all, delete-orphan"))
transaction = orm.relationship(Transaction,
backref=orm.backref(
"transactions",
cascade="all, delete-orphan"))
@property
def hash(self):
return self.transaction.hash
class Game(db.Model):
__tablename__ = 'games'
__table_args__ = {'extend_existing': True}
# id игры
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
# Название, класс, тип игры(домино, пенальти и т.п., время начала, время конца,
# формат(командная/личная), приватность(закрытая/открытая), информация об игре, количество
# задач, максимальный размер команды, минимальный размер команды, путь к файлу с решениями,
# id авторов и проверяющих соответсвенно
title = db.Column(db.String)
grade = db.Column(db.String)
game_type = db.Column(db.String)
start_time = db.Column(db.String)
end_time = db.Column(db.String)
game_format = db.Column(db.String)
privacy = db.Column(db.String)
info = db.Column(db.Text)
tasks_positions = db.Column(db.Text)
tasks_number = db.Column(db.Integer)
sets_number = db.Column(db.Integer)
min_team_size = db.Column(db.Integer)
max_team_size = db.Column(db.Integer)
solutions = db.Column(db.String)
authors = orm.relation("User",
secondary=authors_to_games_assoc_table,
back_populates='authoring')
checkers = orm.relation("User",
secondary=checkers_to_games_assoc_table,
back_populates='checkering')
teams = orm.relationship('Team',
secondary=teams_to_games_assoc_table,
back_populates='games')
players = orm.relation('User',
secondary=players_to_games_assoc_table,
back_populates='playing')
def __init__(self, title, grade, game_type, start_time, end_time,
game_format, privacy, info, author, task_number,
min_team_size, max_team_size, sets_number, tasks_positions):
self.title = title
self.grade = grade
self.game_type = game_type
self.start_time = start_time
self.end_time = end_time
self.game_format = game_format
self.privacy = privacy
self.info = info
self.authors.append(author)
self.tasks_number = task_number
self.min_team_size = min_team_size
self.max_team_size = max_team_size
self.sets_number = sets_number
self.tasks_positions = tasks_positions
class SyncConfig(db.Model):
"""项目方支持的币种"""
__tablename__ = 'sync_config'
__table_args__ = (
{'mysql_engine': "INNODB"}
)
id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
coin_id = db.Column(db.Integer, nullable=False, comment="主链币 ID", index=True)
synced_height = db.Column(db.Integer, nullable=False, comment="已同步高度")
highest_height = db.Column(db.Integer, nullable=False, comment="最新高度")
create_time = db.Column(db.DateTime, nullable=False, comment="创建时间", default=datetime.now)
update_time = db.Column(db.DateTime, nullable=False, comment="更新时间",
default=datetime.now, onupdate=datetime.now)
coin = orm.relationship('Coin',
primaryjoin="SyncConfig.coin_id==Coin.id",
foreign_keys="SyncConfig.coin_id",
backref="SyncConfig")
def __str__(self):
return "{id}-{coin_id}-{synced_height}-{highest_height}".format(
id=self.id, coin_id=self.coin_id, synced_height=self.synced_height,
highest_height=self.highest_height)
@staticmethod
def get_sync_info(coin_name):
"""获取同步信息"""
session = db.session()
coin_sync = session.query(
SyncConfig,
Coin).join(
SyncConfig,
SyncConfig.coin_id == Coin.id).filter(
Coin.name == coin_name).first()
return coin_sync
class Coin(db.Model):
"""
币种表
"""
__tablename__ = 'coin'
__table_args__ = (
UniqueConstraint(
'master_id', 'contract', name='uk_master_id_contract'), {
'mysql_engine': "INNODB"})
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
master_id = db.Column(db.Integer, unique=False, default=0, comment="主链ID")
name = db.Column(db.String(64), unique=True, comment='币种名称')
symbol = db.Column(db.String(64), comment='缩写')
decimal = db.Column(db.SmallInteger, nullable=False, default=0, comment='小数位')
supply = db.Column(db.BigInteger, comment='发行量')
is_master = db.Column(db.SmallInteger, default=1, comment='是否主链币 1 是 0 否')
bip44 = db.Column(db.Integer, default=1, comment='bip44 编码')
contract = db.Column(db.VARCHAR(128), comment="代币名称", unique=True)
create_time = db.Column(db.DateTime, nullable=False, comment="创建时间", default=datetime.now)
sync_config = orm.relationship('SyncConfig',
primaryjoin="SyncConfig.coin_id==Coin.id",
foreign_keys="Coin.id",
backref="Coin")
address = orm.relationship('Address',
primaryjoin="Address.coin_id==Coin.id",
foreign_keys="Coin.id",
backref="Coin")
transaction = orm.relationship('Transaction',
primaryjoin="Transaction.coin_id==Coin.id",
foreign_keys="Coin.id",
backref="Coin")
# coin = orm.relationship('Coin',
# primaryjoin="Coin.master_id==Coin.id",
# foreign_keys="Coin.id",
# backref="Coin")
def __str__(self):
return "{id}-{master_id}-{name}-{symbol}-{is_contract}".format(
id=self.id, master_id=self.master_id, name=self.name, symbol=self.symbol,
is_contract=self.is_contract and True or False)
@staticmethod
def get_coin(contract=None, symbol=None, name=None, is_master=1):
"""从数据库中获取某个币种"""
params = {'is_master': is_master}
if contract is not None:
params['contract'] = contract
params['is_master'] = 0
if symbol is not None:
params['symbol'] = symbol
if name is not None:
params['name'] = name
coin = Coin.query.filter_by(**params).first()
if not coin:
return None
return coin
@staticmethod
def get_coin_by_symbol(contract=None, symbol=None, name=None, is_master=1):
"""根据 symbol 获取币种"""
params = {'is_master': is_master}
if contract is not None:
params['contract'] = contract
params['is_master'] = 0
if symbol is not None:
params['symbol'] = symbol
if name is not None and symbol is None:
params['name'] = name
coin = Coin.query.filter_by(**params).first()
if not coin:
return None
return coin
@staticmethod
def get_erc20_usdt_coin():
"""获取 ETH 的 ERC20 USDT"""
coin = Coin.get_coin_by_symbol(symbol='USDT', is_master=0)
if not coin:
return None
return coin
class Transaction(db.Model):
"""
交易表
"""
__tablename__ = 'tx'
__table_args__ = (
UniqueConstraint('tx_hash', 'coin_id', name='uk_tx_hash_coin_id'),
{'mysql_engine': "INNODB"}
)
id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
block_id = db.Column(db.Integer, nullable=False, comment="块表关联键", index=True)
coin_id = db.Column(db.Integer, nullable=False, comment="币种表关联键", index=True)
tx_hash = db.Column(db.VARCHAR(128), nullable=False, comment="交易hash")
height = db.Column(db.Integer, nullable=False, comment="交易所在高度", index=True)
block_time = db.Column(db.Integer, nullable=False, comment="交易时间戳")
amount = db.Column(db.VARCHAR(64), nullable=False, comment="交易金额")
sender = db.Column(db.VARCHAR(128), nullable=False, comment="发送人")
receiver = db.Column(db.VARCHAR(128), nullable=False, comment="接收人")
gas = db.Column(db.VARCHAR(64), nullable=False, default=0, comment="手续费金额， ETH使用")
gas_price = db.Column(db.VARCHAR(64), nullable=False, default=0, comment="手续费金额, ETH使用")
fee = db.Column(db.VARCHAR(64), nullable=False, default=0, comment="真手续费金额")
contract = db.Column(db.VARCHAR(128), comment="代币名称或地址")
status = db.Column(db.SmallInteger, nullable=False, comment="交易是否有效 1）有效 0）无效 2) 未知")
type = db.Column(db.SmallInteger, nullable=False, comment="交易类型")
is_send = db.Column(db.SmallInteger, nullable=False, comment="是否推送 0:未推 1:已推 2:不用推")
create_time = db.Column(db.DateTime, nullable=False, comment="创建时间", default=datetime.now)
update_time = db.Column(db.DateTime, nullable=False, comment="更新时间",
default=datetime.now, onupdate=datetime.now)
coin = orm.relationship('Coin',
primaryjoin="Transaction.coin_id==Coin.id",
foreign_keys="Transaction.coin_id",
backref="Transaction")
def __str__(self):
return "{id}-{tx_hash}-{height}-{status}".format(
id=self.id, tx_hash=self.tx_hash, height=self.height, status=self.status)
@classmethod
def get_tx_coin_tx(cls, **params):
"""获取"""
session = db.session()
block_tx = session.query(
Transaction,
Coin).join(
Transaction,
Transaction.coin_id == Coin.id).filter(
**params)
return block_tx
@classmethod
def get_tx_coin_by_tx_hash(cls, tx_hash):
"""
根据tx hash 获取获取交易tx 及 币种信息
:param tx_hash:
:return:
"""
session = db.session()
txs = session.query(
Transaction,
Coin).join(
Transaction,
Transaction.coin_id == Coin.id).filter(
Transaction.tx_hash == tx_hash)
return txs.first()
@classmethod
def add_transaction(cls, coin_id, tx_hash, block_time, sender, receiver, amount, status,
tx_type, block_id, height, gas=0, gas_price=0, fee=0,
contract=None, *, commit=True):
"""添加交易"""
session = db.session()
trx = Transaction(coin_id=coin_id, tx_hash=tx_hash, block_time=block_time,
sender=sender, receiver=receiver, amount=amount,
status=status, type=tx_type, gas=gas, gas_price=gas_price,
fee=fee, contract=contract, block_id=block_id,
height=height, is_send=SendEnum.NEEDLESS.value)
# saved 如果成功情况下是 None
saved = session.add(trx)
if commit:
# 自动提交
session.commit()
return saved
# 返回 session 等待自行处理
return session
@classmethod
def add_transaction_or_update(cls, coin_id, tx_hash, block_time, sender, receiver, amount,
status, tx_type, block_id, height, gas=0, gas_price=0, fee=0,
contract=None, is_send=0, *, commit=True, session=None):
"""添加交易或更新交易"""
session = session or db.session()
sql = (
"insert into {table_name} (coin_id, tx_hash, block_time, sender, receiver, amount, "
"status, type, gas, gas_price, fee, contract, block_id, height, is_send, "
"create_time, update_time) "
"values "
"({coin_id}, '{tx_hash}', {block_time}, '{sender}', '{receiver}', {amount}, {status}, "
"{type}, {gas}, {gas_price}, {fee}, {contract}, {block_id}, {height}, {is_send}, "
"'{create_time}', '{update_time}')"
"ON DUPLICATE KEY UPDATE block_time=values(block_time), gas=values(gas), "
"gas_price=values(gas_price), "
"fee=values(fee), block_id=values(block_id), height=values(height)").format(
table_name=cls.__tablename__,
coin_id=coin_id,
tx_hash=tx_hash,
block_time=block_time,
sender=sender,
receiver=receiver,
amount=amount,
status=status,
type=tx_type,
gas=gas,
gas_price=gas_price,
fee=fee,
contract="'{}'".format(contract) if contract is not None else 'null',
block_id=block_id,
height=height,
is_send=is_send,
create_time=now(),
update_time=now())
# saved 如果成功情况下是 None
saved = session.execute(sql)
if commit:
# 自动提交
session.commit()
return saved
# 返回 session 等待自行处理
return session
class Address(db.Model):
"""地址表"""
__tablename__ = 'address'
__table_args__ = (
{'mysql_engine': "INNODB"}
)
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
coin_id = db.Column(db.Integer, nullable=False, comment="币种ID", index=True)
project_id = db.Column(db.Integer, nullable=False, comment="项目方 ID")
address = db.Column(
db.VARCHAR(128),
unique=True,
nullable=False,
comment="地址")
address_type = db.Column(db.SmallInteger, default=0, comment="地址类型，0充 1提")
is_send = db.Column(
db.SmallInteger,
default=0,
index=True,
comment="是否已给予，0否 1是")
status = db.Column(db.SmallInteger, default=1, comment="是否有效，0否 1是")
create_time = db.Column(
db.DateTime,
nullable=False,
comment="生成时间",
default=datetime.now)
coin = orm.relationship('Coin',
primaryjoin="Address.coin_id==Coin.id",
foreign_keys="Address.coin_id",
backref="Address")
transaction = orm.relationship(
'Transaction',
primaryjoin="Address.address==Transaction.receiver",
foreign_keys="Address.address",
backref="Address")
project = orm.relationship('Project',
primaryjoin="Address.project_id==Project.id",
foreign_keys="Address.project_id",
backref="Address")
def __str__(self):
return "{id}-{coin_id}-{project_id}-{address}".format(
id=self.id, coin_id=self.coin_id, project_id=self.project_id, address=self.address)
@staticmethod
def add_address(project_id, coin_id, address, address_type=0, is_send=0, *, commit=True):
"""添加地址"""
address = Address(project_id=project_id, coin_id=coin_id, address=address,
address_type=address_type, is_send=is_send)
session = db.session()
saved = session.add(address)
if commit:
session.commit()
return saved
return session
@staticmethod
def add_addresses(addresses: list, *, commit=True):
"""
添加多个地址进入数据库, 字段仅是将 address中的拆解, 其他都一样,
:param addresses: lidt<dict>, [{project_id, coin_id, address, address_type, is_send}]
:param commit:
:return:
"""
session = db.session()
try:
for address_dict in addresses:
address = Address(**address_dict)
session.add(address)
if commit:
session.commit()
return None
return session
except Exception:
session.rollback()
return None
@staticmethod
def get_coin_address_by_coin_name(coin_name):
"""
这种方式返回的格式固定为 list<tuple<row>>
row 取决于 with_entities 里面的字段
:return address, project_id, coin_id, coin_name
"""
session = db.session()
addresses = session.query(Address, Coin).join(
Address, Address.coin_id == Coin.id).with_entities(
Address.address, Address.project_id, Address.coin_id, Coin.name
).filter(Coin.name == coin_name).all()
return addresses
class User(db.Model, UserMixin):
__tablename__ = 'users'
__table_args__ = {'extend_existing': True}
# id в бд
id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
index=True)
# Информация которую о себе вводит пользователь:
# Имя, Фамилия, класс, школа, учителя математики, информация о себе, электонная почта, её хеш,
# логин, хеш пароля соответсвенно
name = db.Column(db.String, nullable=False)
surname = db.Column(db.String, nullable=False)
last_name = db.Column(db.String, nullable=False)
grade = db.Column(db.Integer, nullable=True)
school = db.Column(db.String, nullable=True)
work_place = db.Column(db.String, nullable=True)
phone_number = db.Column(db.String, nullable=True)
teachers = db.Column(db.Text, nullable=True)
info = db.Column(db.Text, nullable=True)
email = db.Column(db.String, nullable=False)
hashed_email = db.Column(db.String, nullable=False)
login = db.Column(db.String, nullable=False)
hashed_password = db.Column(db.String, nullable=False)
# Системная информация о пользователе:
# задани, которые создал, права, команды, в которых состоит, команды, в которые приглашён,
# потверждена ли почта, заблокирован ли соответсвенно
created_tasks = orm.relationship("Task")
rights = db.Column(db.String, nullable=False, default='user')
authoring = orm.relationship('Game',
secondary=authors_to_games_assoc_table,
back_populates="authors")
checkering = orm.relationship('Game',
secondary=checkers_to_games_assoc_table,
back_populates='checkers')
playing = orm.relationship('Game',
secondary=players_to_games_assoc_table,
back_populates='players')
captaining = orm.relationship('Team',
secondary=captains_to_teams_assoc_table,
back_populates='captain')
teams = orm.relationship('Team',
secondary=members_to_teams_assoc_table,
back_populates='members')
managed_teams = orm.relationship('Team',
secondary=teachers_to_teams_assoc_table,
back_populates='teacher')
is_approved = db.Column(db.Boolean, default=False)
is_banned = db.Column(db.Boolean, default=False)
is_authenticated = True
is_teacher = db.Column(db.Boolean, default=False)
def set_password(self, password):
self.hashed_password = generate_password_hash(password)
def check_password(self, password):
return check_password_hash(self.hashed_password, password)
def set_email(self, email):
self.email = email
self.hashed_email = generate_password_hash(email)
def __init__(self, role, params):
self.login = params['login']
self.name = params['name']
self.surname = params['surname']
self.last_name = params['last_name']
if role == 'Student':
self.grade = params['grade']
self.school = params['school']
self.teachers = params['teachers']
self.info = params['info']
elif role == 'Teacher':
self.is_teacher = True
self.work_place = params['work_place']
self.phone_number = params['phone_number']