class TcpIpPortClient(NetworkElement):
"""Defines the tcp_ip_port_client object for the database
Table :
| object_id [CHAR(32)] FK(network_element.object_id) PK
| name [String(128)]
| port [Integer]
| ip_address [CHAR(32)] FK(ip_address.object_id) PK
| lable [String(256)]
Constraints :
| tcp_ip_port_client_constraint(name)
"""
__tablename__ = 'tcp_ip_port_client'
## CNS: it's redundant to have ip and the container on the constraint list,
## but I'm putting this in until I build out the resultProcessing to handle
## constraints found through the links section (i.e. the container)
_constraints = ['container', 'name', 'ip', 'is_tcp']
_captionRule = {"condition": ["name"]}
__table_args__ = (UniqueConstraint(*_constraints,
name='tcp_ip_port_client_constraint'), {
'schema': 'data'
})
object_id = Column(CHAR(32),
ForeignKey(NetworkElement.object_id),
primary_key=True)
container = Column(None, ForeignKey(IpAddress.object_id), nullable=False)
name = Column(String(128), nullable=False)
port = Column(Integer, nullable=True)
ip = Column(String(128), nullable=False)
is_tcp = Column(Boolean, nullable=False)
port_type = Column(String(16), nullable=True)
label = Column(String(256), nullable=True)
container_relationship = relationship(
'IpAddress',
backref=backref('tcp_ip_port_client_objects', cascade='all, delete'),
foreign_keys='TcpIpPortClient.container')
__mapper_args__ = {
'with_polymorphic': '*',
'polymorphic_identity': 'tcp_ip_port_client',
'inherit_condition': object_id == NetworkElement.object_id
}
@classmethod
def unique_filter(cls, query, **kwargs):
return query.filter(TcpIpPortClient.name == kwargs['name'])
class UserAddress(Base):
"""地址表"""
__tablename__ = 'account_user_address'
id = Column(Integer, primary_key=True, comment='地址ID')
area = Column(String(256), nullable=False, comment='地址')
phone_no = Column(CHAR(11), unique=True, comment='手机号')
remark = Column(String(512), comment='备注')
create_at = Column(DateTime, default=datetime.now(), comment='创建时间')
is_valid = Column(Boolean, default=True, comment='是否有效')
# 外键
user_id = Column(Integer,
ForeignKey(User.id),
nullable=False,
comment='关联的用户ID')
# 一对多关联：
# relationship('关联模型', backref='模型反向关联属性', lazy='指定如何加载相关记录')
user = relationship('User', backref='addresses')
class Tls906Person(Base):
__tablename__ = 'tls906_person'
person_id = Column(INT, primary_key=True, default=0)
person_name = Column(NVARCHAR(500), default='')
person_name_orig_lg = Column(NVARCHAR(500), default='')
person_address = Column(NVARCHAR(1000), default='')
person_ctry_code = Column(CHAR(2), default='')
nuts = Column(VARCHAR(5), default='')
nuts_level = Column(SMALLINT, default=9)
doc_std_name_id = Column(INT, default=0)
doc_std_name = Column(NVARCHAR(500), default='')
psn_id = Column(INT, default=0)
psn_name = Column(NVARCHAR(500), default='')
psn_level = Column(SMALLINT, default=0)
psn_sector = Column(VARCHAR(50), default='')
han_id = Column(INT, default=0)
han_name = Column(NVARCHAR(500), default='')
han_harmonized = Column(INT, default=0)
class InstrumentCommissionRate(BaseModel):
__tablename__ = 'instrument_commission_rate'
ticker_type = Column(CHAR(20), primary_key=True)
open_ratio_by_money = Column(Float)
open_ratio_by_volume = Column(Float)
close_ratio_by_money = Column(Float)
close_ratio_by_volume = Column(Float)
close_today_ratio_by_money = Column(Float)
close_today_ratio_by_volume = Column(Float)
def info_str(self):
return 'ticker_type:%s, open_ratio_by_money:%s, open_ratio_by_volume:%s' % \
(self.ticker_type, self.open_ratio_by_money, self.open_ratio_by_volume)
def copy(self):
return copy.deepcopy(self)
class User(Base): # 用户表 #添加聊天专用chattoken
__tablename__ = 'User'
Uid = Column(Integer, nullable=False, primary_key=True) # 主键
Upassword = Column(VARCHAR(64), nullable=False)
Utel = Column(CHAR(32), nullable=False,unique=True)
Ualais = Column(VARCHAR(24),nullable=False,unique=True) # 昵称，可能为微信昵称
Uname = Column(VARCHAR(24)) # 真实姓名
Ulocation = Column(VARCHAR(128))
Uopenid = Column(VARCHAR(128))
Umailbox = Column(VARCHAR(32)) # 邮箱
Ubirthday = Column(DateTime)
Uscore = Column(Integer, default=0)
UregistT = Column(DateTime(timezone=True), default=func.now())
Usex = Column(Boolean, nullable=False)
Usign = Column(VARCHAR(256))
Usessionid = Column(VARCHAR(32)) #用于验证用户
Uvalid = Column(Integer, nullable=False, default=1)
class FutureMainContract(BaseModel):
__tablename__ = 'future_main_contract'
ticker_type = Column(CHAR(20), primary_key=True)
exchange_id = Column(Integer, primary_key=True)
pre_main_symbol = Column(CHAR(11))
main_symbol = Column(CHAR(11))
next_main_symbol = Column(CHAR(11))
night_flag = Column(CHAR(11))
warning_days = Column(Integer)
update_flag = Column(CHAR(11))
def to_dict(self):
return {c.name: getattr(self, c.name) for c in self.__table__.columns}
class UserVerification(Base):
__tablename__ = 'UserVerification'
code = Column('code', CHAR(6), primary_key=True)
user_id = Column('user_id', BIGINT, ForeignKey('User.id'), unique=True, nullable=False)
user = relationship(User)
def __init__(self, code: str, user_id: int):
self.code = code
self.user_id = user_id
@classmethod
def add(cls, session, user_id: int, code: str=None) -> 'UserVerification':
assert user_id is not None
code = code or cls.generate_code()
verification = UserVerification(code, user_id)
session.add(verification)
session.commit()
session.refresh(verification)
return verification
@classmethod
def get_by_code(cls, session, code: str) -> Optional['UserVerification']:
"""Get entity by code"""
return session.query(cls).filter_by(code=code).one_or_none()
@classmethod
def get_by_user(cls, session, user_id: int) -> Optional['UserVerification']:
"""Get entity by user"""
return session.query(cls).filter_by(user_id=user_id).one_or_none()
@classmethod
def delete(cls, session, user_id: int) -> bool:
"""Delete instance"""
success = session.query(cls).filter_by(user_id=user_id).delete()
return success
@classmethod
def generate_code(cls, length: int=6) -> str:
"""Generate a new verification code"""
chars = string.ascii_uppercase + string.digits
code = random.choices(chars, k=length)
return ''.join(code)
class IpAddress(NetworkElement):
"""Defines an ip_address object for the database.
Table :
| ip_address
Columns :
| object_id [CHAR(32)] FK(network_element.object_id) PK
| time_created [DateTime]
| time_updated [DateTime]
| object_created_by [String(128)]
| object_updated_by [String(128)]
| description [String(256)]
| address [String(128)] PK
| realm [String(256)] PK
| address_type [String(64)]
| is_ipv4 [Boolean]
Constraints :
| ip_address_constraint(address)
"""
__tablename__ = 'ip_address'
_constraints = ['address', 'realm']
_captionRule = {"condition": ["address"]}
__table_args__ = (UniqueConstraint(*_constraints,
name='ip_address_constraint'), {
"schema": "data"
})
object_id = Column(CHAR(32),
ForeignKey(NetworkElement.object_id),
primary_key=True)
address = Column(String(128), nullable=False)
realm = Column(String(256), nullable=False)
address_type = Column(String(64), nullable=True)
is_ipv4 = Column(Boolean, nullable=True)
__mapper_args__ = {
'with_polymorphic': '*',
'polymorphic_identity': 'ip_address',
'inherit_condition': object_id == NetworkElement.object_id
}
@classmethod
def unique_filter(cls, query, **kwargs):
return query.filter(IpAddress.address == kwargs['address'])
class UUID(TypeDecorator):
impl = CHAR(32)
@classmethod
def new_random(cls):
return uuid.uuid4()
def process_bind_param(self, value, dialect):
if value is None:
return value
else:
if not isinstance(value, uuid.UUID):
return uuid.UUID(value).hex
else:
return value.hex
def process_result_value(self, value, dialect):
if value is None:
return value
else:
return uuid.UUID(value)
class WActivity(Base): # 活动表
__tablename__ = 'WActivity'
WACid = Column(Integer, nullable=False, primary_key=True)
WACsponsorid = Column(Integer, ForeignKey('User.Uid', onupdate='CASCADE')) # 活动发起者
WAClocation = Column(VARCHAR(128), nullable=False) # 位置
WACtitle = Column(VARCHAR(24), nullable=False) # 活动的名称？确认长度
WACstartT = Column(DateTime, nullable=False) # 开始时间
WACendT = Column(DateTime, nullable=False) # 结束时间
WACjoinT = Column(DateTime) # 活动报名截止时间
WACcontent = Column(VARCHAR(256), nullable=False) # 活动介绍
WACfree = Column(Boolean, default=1) # 是否免费，默认为免费
WACprice = Column(VARCHAR(64)) # 价格描述
WACclosed = Column(Boolean, default=0, nullable=False) # 活动是否已经结束,0为未结束，1为结束
WACcreateT = Column(DateTime(timezone=True), default=func.now()) # 活动创建时间
WACmaxp = Column(Integer,nullable=False, default=0) # 活动最小人数
WACminp = Column(Integer,nullable=False, default=100) # 活动报名人上限
WACregistN = Column(Integer,nullable=False, default=0) # 报名人数
WACstatus =Column(Integer, nullable=False, default=0) # 活动状态，1为报名中，2为进行中，3为已结束
WACvalid = Column(Boolean, nullable=False, default=0) # 活动是否已经删除, 1为有效
WACusercontact = Column(CHAR(32),nullable=False)
class Password(TypeDecorator):
"""Password hash
Hash is SHA-256
"""
impl = CHAR(75)
def process_literal_param(self, value: str, dialect):
return value if value is not None else None
def process_bind_param(self, value: str, dialect) -> str:
return bcrypt_sha256.hash(value)
def process_result_value(self, value: str, dialect) -> str:
return value
class comparator_factory(String.comparator_factory):
def __eq__(self, other: str) -> bool:
if other is None:
return False
else:
return bcrypt_sha256.verify(other, self.expr)
class Notice_Stat(BaseModel):
__tablename__ = "notice_stat"
id = Column(Integer, primary_key=True)
timestamp = Column(BigInteger, index=True)
tsHour = Column(BigInteger)
key = Column(VARCHAR(512), index=True)
check_type = Column(CHAR(100))
strategy_name = Column(CHAR(100))
scene_name = Column(CHAR(100))
decision = Column(CHAR(100))
test = Column(Integer)
tag = Column(CHAR(100))
geo_city = Column(CHAR(100))
uri_stem = Column(String(1024))
ip = Column(CHAR(20))
uid = Column(VARCHAR(512))
did = Column(VARCHAR(512))
count = Column(Integer)
last_modified = Column(BigInteger)
class Synonym(Base):
__tablename__ = "tbl_molecule_synonym"
fk_molecule_id = Column(Integer, index=True)
synonym = Column(String, nullable=False, index=True)
# Store the IUPAC chemical name if available
note = Column(CHAR(10), index=True)
__table_args__ = (
ForeignKeyConstraint(
["fk_molecule_id"],
["tbl_molecule.id"],
onupdate="CASCADE",
ondelete="CASCADE",
),
PrimaryKeyConstraint("fk_molecule_id", "synonym"),
{}, # {'schema': Base.metadata.schema} #'autoload': True
)
molecule = relation(Molecule, backref=backref("synonyms", order_by=synonym))
def __init__(self, synonym, note=False):
"""
synonym is a synonym corresponding to one molecule that is
identified through fk_molecule_id
note is an additional note to one synonym, maybe you know
that a synonym is a PubChem id or is a IUPAC name, than give as note
CID or IUPAC ... note is not necessary and will default to null
"""
self.synonym = synonym
# True is a placeholder for nothing in the parser so ignore any insert
if not note:
self.note = note
else:
self.note = None
def __repr__(self):
return "Synonyms(%s)" % (self.chembl_id)
class StrategyDefaultModel(BaseModel):
__tablename__ = 'strategy_default'
id = Column(Integer, primary_key=True)
app = Column(CHAR(100))
name = Column(CHAR(100))
category = Column(CHAR(100))
score = Column(Integer)
isLock = Column(Integer)
tags = Column(CHAR(200))
remark = Column(String(1000))
version = Column(CHAR(100))
status = Column(CHAR(100))
createtime = Column(BigInteger)
modifytime = Column(BigInteger)
starteffect = Column(BigInteger)
endeffect = Column(BigInteger)
last_modified = Column(BigInteger)
config = Column(BLOB)
group_id = Column(Integer)
def __str__(self):
return str(self.__dict__)
def to_strategy(self):
return Strategy.from_json(self.config)
@staticmethod
def from_strategy(strategy):
return StrategyDefaultModel(app=strategy.app,
name=strategy.name,
category=strategy.category,
isLock=strategy.isLock,
score=strategy.score,
tags=strategy.tags,
remark=strategy.remark,
version=strategy.version,
status=strategy.status,
createtime=strategy.create_time,
modifytime=strategy.modify_time,
starteffect=strategy.start_effect,
endeffect=strategy.end_effect,
group_id=strategy.group_id,
last_modified=millis_now(),
config=strategy.get_json())
class LogQueryModel(BaseModel):
__tablename__ = 'logquery'
id = Column(Integer, primary_key=True)
fromtime = Column(Integer)
endtime = Column(Integer)
terms = Column(VARCHAR(2000))
show_cols = Column(VARCHAR(1000))
user_id = Column(Integer)
page = Column(Integer)
total = Column(Integer)
temp_query_file = Column(CHAR(100))
last_modified = Column(Integer)
@staticmethod
def from_dict(logquery):
return LogQueryModel(fromtime=logquery['fromtime'],
endtime=logquery['endtime'],
terms=json_dumps(logquery['terms']),
show_cols=','.join(logquery['show_cols']),
user_id=logquery['user_id'],
page=logquery.get('page', 1),
total=logquery.get('total', 1),
temp_query_file=logquery.get(
'temp_query_file', ''),
last_modified=millis_now())
def to_dict(self):
return dict(id=self.id,
fromtime=self.fromtime,
endtime=self.endtime,
terms=json.loads(self.terms),
show_cols=self.show_cols.split(','),
user_id=self.user_id,
page=self.page,
total=self.total,
temp_query_file=self.temp_query_file)
class Notice(BaseModel):
__tablename__ = 'notice'
id = Column(Integer, primary_key=True)
timestamp = Column(BigInteger, index=True)
key = Column(VARCHAR(512), index=True)
strategy_name = Column(CHAR(100))
scene_name = Column(CHAR(100))
checkpoints = Column(CHAR(100))
check_type = Column(CHAR(100))
decision = Column(CHAR(100))
risk_score = Column(Integer)
expire = Column(Integer)
remark = Column(VARCHAR(1000))
last_modified = Column(BigInteger)
variable_values = Column(BLOB)
geo_province = Column(CHAR(100))
geo_city = Column(CHAR(100))
test = Column(Integer)
tip = Column(String(1024))
uri_stem = Column(String(1024))
trigger_event = Column(BLOB)
