class Order(BaseModel):
__tablename__ = 'Order'
OrderId = Column('OrderId',
Integer,
primary_key=True,
nullable=False,
autoincrement=True) #订单序号
OrderName = Column('OrderName', NVARCHAR(100), nullable=False) #商品名
# OrderType = Column('OrderType', Integer,nullable=False) #商品类型
# Member = relationship('Member') #会员
PayMode = Column('PayMode', Integer, nullable=False) #支付方式
PayStatus = Column('PayStatus', Integer, nullable=False) #支付状态
OrderType = Column('OrderType', NVARCHAR(100), nullable=False) #订单类型
# Goods = relationship('Goods') #商品
# Course = relationship('Course') #课程
Price = Column('Price', DECIMAL(10, 2), nullable=False) #价格
Amount = Column('Amount', Integer, default=1) #数量
Rebate = Column('Rebate', Integer, default=100) #实际折扣
BuyName = Column('BuyName', NVARCHAR(100), nullable=False) #会员 /学员
BuyerName = Column('BuyerName', NVARCHAR(100), nullable=False) #挂账人员
Comment = Column('Comment', VARCHAR(500)) #备注
Operator = Column('Operator',
Integer,
ForeignKey('UserProfile.UserId'),
nullable=False)
CreateDate = Column('CreateDate', DateTime, nullable=False)
class Account(Base, BaseEntity):
__tablename__ = 'account'
_id = Column('id', INTEGER, primary_key=True)
_username = Column('username', NVARCHAR(100), nullable=False, unique=True)
_password = Column('password', NVARCHAR(40), nullable=False)
_role = Column('role', Enum(AccountRole), nullable=False)
@hybrid_property
def username(self):
return self._username
@username.setter
def username(self, value):
if value not in [None, '']:
self._username = value
@hybrid_property
def password(self):
return self._password
@password.setter
def password(self, value):
if value not in [None, '']:
self._password = value
@hybrid_property
def role(self):
return self._role
@role.setter
def role(self, vale):
if vale in list(AccountRole):
self._role = vale
class Site(EntityBase):
"""考场"""
__tablename__ = "ExaminationSite"
Name = Column(NVARCHAR(50), unique=True)
Address = Column(NVARCHAR(100))
ExaminationRooms = relationship("Room", backref="ExaminationSite")
class AMinerAuthor(Base):
__tablename__ = 'T_AMinerAuthor'
author_sid = Column(Integer, primary_key=True)
id = Column(NVARCHAR(24))
name = Column(NVARCHAR(50))
org = Column(TEXT)
def upgrade(migrate_engine):
meta.bind = migrate_engine
u = Table("user", meta, autoload=True)
p = Table("pseudo_random_id_provider", meta, autoload=True)
t = Table(
"pseudo_random_id",
meta,
Column("id", Integer, primary_key=True),
Column("pseudo_random_id_provider_id", Integer, ForeignKey(p.c.id), index=True, nullable=False),
Column("ordinal", Integer),
Column("unique_code", Integer),
Column("check_character", NVARCHAR(1)),
Column("full_code", NVARCHAR(20)),
Column("last_updated_datetime", DateTime, nullable=False),
Column("last_updated_by_user_id", Integer, ForeignKey(u.c.id), index=True, nullable=False),
UniqueConstraint(
'pseudo_random_id_provider_id',
'ordinal',
name='uix_pseudo_random_id_pseudo_random_id_provider_id_ordinal'
),
UniqueConstraint(
'pseudo_random_id_provider_id',
'unique_code',
name='uix_pseudo_random_id_pseudo_random_id_provider_id_unique_code'
),
UniqueConstraint(
'full_code',
name='uix_pseudo_random_id_full_code'
),
)
t.create()
def upgrade(migrate_engine):
meta.bind = migrate_engine
u = Table("user", meta, autoload=True)
rp = Table("redcap_project", meta, autoload=True)
t = Table(
"ecrf_detail",
meta,
Column("id", Integer, primary_key=True),
Column("project_id", Integer, ForeignKey(rp.c.id), index=True, nullable=False),
Column("ecrf_participant_identifier", NVARCHAR(100), index=True, nullable=False),
Column("recruitment_date", Date),
Column("first_name", NVARCHAR(100)),
Column("last_name", NVARCHAR(100)),
Column("sex", NVARCHAR(1)),
Column("postcode", NVARCHAR(10)),
Column("birth_date", Date),
Column("complete_or_expected", Boolean),
Column("non_completion_reason", Integer),
Column("withdrawal_date", Date),
Column("post_withdrawal_keep_samples", Boolean),
Column("post_withdrawal_keep_data", Boolean),
Column("brc_opt_out", Boolean),
Column("ecrf_timestamp", BigInteger, index=True, nullable=False),
Column("last_updated_datetime", DateTime, nullable=False),
Column("last_updated_by_user_id", Integer, ForeignKey(u.c.id), index=True, nullable=False),
UniqueConstraint(
'project_id',
'ecrf_participant_identifier',
name='uix__ecrf_details__project_id__ecrf_participant_identifier',
)
)
t.create()
class District(Base):
__tablename__ = 'district'
__table_args__ = {'comment': '?????'}
id = Column(VARCHAR(36), primary_key=True, comment='??ID')
city_code = Column(VARCHAR(4), index=True, comment='????')
city_name = Column(NVARCHAR(64), comment='????')
county_code = Column(VARCHAR(2), comment='????')
county_name = Column(NVARCHAR(64), comment='????')
town_code = Column(VARCHAR(3), comment='????')
town_name = Column(NVARCHAR(64), comment='????')
village_code = Column(VARCHAR(3), comment='???')
village_name = Column(NVARCHAR(64), comment='???')
group_code = Column(VARCHAR(2), comment='???')
group_name = Column(NVARCHAR(64), comment='???')
index_code = Column(NUMBER(asdecimal=False), comment='????')
create_worker = Column(VARCHAR(36), comment='???')
create_time = Column(DateTime, comment='????')
latest_modify_worker = Column(VARCHAR(36), comment='?????')
latest_modify_time = Column(DateTime, comment='??????')
isvalid = Column(NUMBER(asdecimal=False), comment='????')
bz1 = Column(NVARCHAR(255), comment='??1')
bz2 = Column(NVARCHAR(255), comment='??2')
bz3 = Column(NVARCHAR(255), comment='??3')
bz4 = Column(NVARCHAR(255), comment='??4')
bz = Column(NVARCHAR(255), comment='??')
full_code = Column(VARCHAR(14), index=True, comment='????')
def get_audit_mixin_columns():
return [
Column("last_update_date", DateTime, nullable=False),
Column("last_update_by", NVARCHAR(500), nullable=False),
Column("created_date", DateTime, nullable=False),
Column("created_by", NVARCHAR(500), nullable=False),
]
class PhoneType(Base):
__tablename__ = 'phoneType'
__table_args__ = (PrimaryKeyConstraint('phoneTypeId'), )
phoneTypeId = Column(NVARCHAR(length=2), nullable=False)
type = Column(NVARCHAR(length=30))
statusId = Column(NVARCHAR(length=2), default=0)
def upgrade(migrate_engine):
meta.bind = migrate_engine
t = Table("user", meta, autoload=True)
t.c.created_datetime.alter(name='date_created')
email = Column("email", NVARCHAR(255), unique=True)
email.create(t, unique_name='udx__user__email')
password = Column("password", NVARCHAR(255))
password.create(t)
confirmed_at = Column("confirmed_at", DateTime())
confirmed_at.create(t)
last_login_at = Column("last_login_at", DateTime())
last_login_at.create(t)
current_login_at = Column("current_login_at", DateTime())
current_login_at.create(t)
last_login_ip = Column("last_login_ip", NVARCHAR(50))
last_login_ip.create(t)
current_login_ip = Column("current_login_ip", NVARCHAR(50))
current_login_ip.create(t)
login_count = Column("login_count", Integer)
login_count.create(t)
class Sex(Base):
__tablename__ = 'sex'
__table_args__ = (PrimaryKeyConstraint('sexId'), )
sexId = Column(NVARCHAR(length=1))
sex = Column(NVARCHAR(length=10))
statusId = Column(NVARCHAR(length=2), default=0)
class User(Base):
__tablename__= "user"
id = Column(Integer, primary_key= True)
name = Column(NVARCHAR(100), nullable= False)
email = Column(NVARCHAR(100), nullable= False,unique=True)
password = Column(NVARCHAR(100), nullable= False)
created_dt = Column(DateTime, default=func.now())
def upgrade(migrate_engine):
meta.bind = migrate_engine
t = Table(
"edge_site_study",
meta,
Column("id", Integer, primary_key=True),
Column("project_short_title", NVARCHAR(500)),
Column("project_id", Integer, index=True, nullable=False),
Column("principle_investigator", NVARCHAR(500)),
Column("project_type", NVARCHAR(500)),
Column("research_theme", NVARCHAR(500)),
Column("start_date", DateTime),
Column("project_site_date_open_to_recruitment", DateTime),
Column("project_site_start_date_nhs_permission", DateTime),
Column("end_date", DateTime),
Column("project_site_closed_date", DateTime),
Column("project_site_planned_closing_date", DateTime),
Column("recruitment_end_date", DateTime),
Column("project_site_actual_recruitment_end_date", DateTime),
Column("project_site_planned_recruitment_end_date", DateTime),
Column("recruited_total", Integer),
Column("recruited_org", Integer),
Column("project_status", NVARCHAR(500)),
Column("project_site_target_participants", Integer),
)
t.create()
def upgrade(migrate_engine):
meta.bind = migrate_engine
u = Table("user", meta, autoload=True)
t = Table(
"demographics_request",
meta,
Column("id", Integer, primary_key=True),
Column("created_datetime", DateTime, nullable=False),
Column("owner_user_id",
Integer,
ForeignKey(u.c.id),
index=True,
nullable=False),
Column("filename", NVARCHAR(500), nullable=False),
Column("extension", NVARCHAR(100), nullable=False),
Column("last_updated_datetime", DateTime, nullable=False),
Column("last_updated_by_user_id",
Integer,
ForeignKey(u.c.id),
index=True,
nullable=False),
)
t.create()
class SkillScore(Base):
__tablename__ = 'skillScore'
__table_args__ = (PrimaryKeyConstraint('peopleCode', 'skillCode'), )
peopleCode = Column(NVARCHAR(length=20))
skillCode = Column(NVARCHAR(length=20))
skillBlockScore = Column(NVARCHAR(length=10))
statusId = Column(NVARCHAR(length=2), default=0)
class PossibleMatch(Base):
__tablename__ = 'PossibleMatches'
id = Column(Integer, primary_key=True, name='ID')
music_id = Column(Integer, name='MusicID')
artist = Column(NVARCHAR(256), name='Artist')
title = Column(NVARCHAR(256), name='Title')
music_brainz_id = Column(NVARCHAR(256), name='MusicBrainzID')
class OrgPos(Base):
__tablename__ = 'orgPos'
__table_args__ = (PrimaryKeyConstraint('rowId'), )
rowId = Column(Integer, nullable=False)
orgCode = Column(NVARCHAR(length=20))
posCode = Column(NVARCHAR(length=20))
statusId = Column(NVARCHAR(length=2), default=0)
class SkillBlockSkill(Base):
__tablename__ = 'skillBlockSkill'
__table_args__ = (PrimaryKeyConstraint('rowId'), )
rowId = Column(Integer, nullable=False)
skillBlockCode = Column(NVARCHAR(length=20))
skillCode = Column(NVARCHAR(length=20))
statusId = Column(NVARCHAR(length=2), default=0)
class GoodsTracking(Base):
__tablename__ = 'goods_tracking'
id = Column(Integer, primary_key=True)
order_id = Column(Integer, ForeignKey('orders.id'))
delivery_start_date = Column(DateTime)
delivery_end_date = Column(DateTime)
tracking_number = Column(NVARCHAR(50))
tracking_status = Column(NVARCHAR(30))
class Album(Base):
__tablename__ = 'Albums'
id = Column(Integer, primary_key=True, name='ID')
music_brainz_id = Column(NVARCHAR(256), name='MusicBrainzID')
name = Column(NVARCHAR(256), name='Name')
date = Column(DATE, name='Date')
cover_art = Column(VARBINARY, name='CoverArt')
mime_type = Column(NVARCHAR(64), name='MimeType')
def get_role_table(meta):
return Table(
"role",
meta,
Column("id", Integer, primary_key=True),
Column("name", NVARCHAR(100)),
Column("description", NVARCHAR(250)),
Column("date_created", DateTime),
)
class UserLink(Base):
__tablename__ = 'user_link'
__table_args__ = {'comment': '???????'}
user_id = Column(VARCHAR(36), primary_key=True, comment='??ID')
third_user_id = Column(NVARCHAR(64), index=True, comment='?????ID')
third_type = Column(NVARCHAR(36),
index=True,
comment='????? ??-dd ??-wx ??')
class Classtable(db.Model):
__table_name__ = "ClassTable"
classtableid = Column(NVARCHAR(50),
primary_key=True,
unique=True,
nullable=True)
price = Column(INTEGER, ForeignKey('Course.Price'), nullable=True)
hoursone = Column(NVARCHAR(40),
ForeignKey('Course.HoursOne'),
nullable=True)
number_count = Column(db.NVARCHAR(40),
ForeignKey('Course.NumberCountClass'),
nullable=True)
firstnamestudent = Column(db.NVARCHAR(40),
ForeignKey('Student.FirstNameStudent'),
nullable=True)
firstnameteacher = Column(db.NVARCHAR(40),
ForeignKey('Teacher.FirstNameTeacher'),
nullable=True)
lastnamestudent = Column(db.NVARCHAR(40),
ForeignKey('Student.LastNameStudent'),
nullable=True)
lastnameteacher = Column(db.NVARCHAR(40),
ForeignKey('Teacher.LastNameTeacher'),
nullable=True)
teacherid = Column(db.NVARCHAR(50),
ForeignKey('Teacher.TeacherID'),
nullable=True)
studentid = Column(db.NVARCHAR(50),
ForeignKey('Student.StudentID'),
nullable=True)
courseid = Column(db.NVARCHAR(50),
ForeignKey('Course.CourseID'),
nullable=True)
day = Column(NVARCHAR(40), ForeignKey('Course.Day'), nullable=True)
hoursinday = Column(NVARCHAR(40),
ForeignKey('Course.HoursInDay'),
nullable=True)
def __init__(self, price, hoursone, number_count, firstnamestudent,
firstnameteacher, lastnamestudent, lastnameteacher, day,
hoursinday, point):
self.price = price
self.hoursone = hoursone
self.number_count = number_count
self.firstnamestudent = firstnamestudent
self.firstnameteacher = firstnameteacher
self.lastnamestudent = lastnamestudent
self.lastnameteacher = lastnameteacher
self.day = day
self.hoursinday = hoursinday
self.point = point
def __repr__(self):
return '<Class Day{},HoursInDay{},LastNameStudent{},LastNameTeacher{}>'.format(
self.secctionname, self.hoursinday, self.lastnamestudent,
self.lastnameteacher, self.point)
class Goods(Base):
__tablename__ = 'goods'
id = Column(Integer, primary_key=True)
goods_name = Column(NVARCHAR(255))
price = Column(Integer)
goods_photo = Column(NVARCHAR(255))
goods_cnt = Column(Integer)
goods_ranking = Column(Integer)
goods_description = Column(NVARCHAR(None))
class Phone(Base):
__tablename__ = 'phone'
__table_args__ = (PrimaryKeyConstraint('rowId', 'peopleCode'), )
rowId = Column(Integer, nullable=False)
peopleCode = Column(NVARCHAR(length=20), nullable=False)
phone = Column(NVARCHAR(length=15))
ext = Column(NVARCHAR(length=5))
phoneTypeId = Column(NVARCHAR(length=2))
statusId = Column(NVARCHAR(length=2), default=0)
class TypingErrors(Db.Base):
__tablename__ = "typing_errors"
id = Column(Integer, primary_key=True, autoincrement=True)
lesson_id = Column(Integer, ForeignKey("lessons.id"), nullable=False)
char_entered = Column(NVARCHAR(1), nullable=False)
char_target = Column(NVARCHAR(1), nullable=False)
CheckConstraint("len(char_entered) == 1",
name="Incorrect letter must be one char only")
CheckConstraint("len(char_target) == 1",
name="Target letter must be one char only")
class Detail(Model):
__tablename__ = 'Detail'
run_id = Column(NVARCHAR(length=100, collation=COLLATION),
primary_key=True)
name = Column(NVARCHAR(length=None, collation=COLLATION))
value = Column(NVARCHAR(length=None, collation=COLLATION))
type = Column(NVARCHAR(length=None, collation=COLLATION))
def __repr__(self):
return f"<{self.__tablename__}(run_id={self.run_id}, name={self.name}, value={self.value}, type={self.type})>"
class Room(EntityBase):
"""
考场教室信息
"""
__tablename__ = "ExaminationRoom"
Name = Column(NVARCHAR(50), unique=True)
Address = Column(NVARCHAR(100))
PassWord = Column(String(20))
ExaminationSiteId = Column(String(32), ForeignKey("ExaminationSite.Id"))
class TESTLibrary(EntityBase):
__tablename__ = "TESTLibrary"
Type = Column(Enum(TESTLibraryType))
Answer = Column(Integer)
Stem = Column(NVARCHAR(500), unique=True)
Options = Column(NVARCHAR(500))
Category = relationship("LibraryCategory",
secondary=TESTLibraryAndCategory,
backref="TESTLibrary")
def upgrade(migrate_engine):
meta.bind = migrate_engine
t = Table(
"etl_user",
meta,
Column("practice_code", NVARCHAR(50), index=True, nullable=False),
Column("email_address", NVARCHAR(500), index=True, nullable=False),
)
t.create()
