class Comment(BaseModel):
# 评论ID, 前缀JD, SN等, 小米商城和小米有品共用MI前缀(评论互通)
comment_id = pw.CharField(max_length=20, primary_key=True)
create_time = pw.DateTimeField() # 评论创建时间
content = pw.TextField() # 评论内容
star = pw.SmallIntegerField(constraints=[pw.Check('star between 0 and 5')
]) # 评分星级
after_time = pw.DateTimeField(null=True) # 追评时间
after_content = pw.TextField(null=True) # 追评内容
after_days = pw.SmallIntegerField(null=True) # 追评间隔时间
order_time = pw.DateTimeField(null=True) # 下单时间, 限京东数据源
order_days = pw.SmallIntegerField(null=True) # 从下单到评论的时间(包括物流时间)
user_device = pw.CharField(
max_length=10, null=True) # 用户设备类型, Android, iOS, other, 限京东和苏宁数据源
# 产品型号信息
product_color = pw.CharField(
max_length=4,
constraints=[
pw.Check('product_color in ("国风雅灰", "钛银黑", "冰海蓝", "蜜桃金")')
]) # 产品颜色版本
product_ram = pw.CharField(max_length=4,
constraints=[
pw.Check('product_ram in ("8GB", "12GB")')
]) # 内存大小
product_rom = pw.CharField(
max_length=5,
constraints=[pw.Check('product_rom in ("128GB", "256GB")')]) # 储存大小
class ModelSummary(BaseModel):
# 产品型号信息
product_color = pw.CharField(
max_length=4,
constraints=[
pw.Check('product_color in ("国风雅灰", "钛银黑", "冰海蓝", "蜜桃金")')
]) # 产品颜色版本
product_ram = pw.CharField(max_length=4,
constraints=[
pw.Check('product_ram in ("8GB", "12GB")')
]) # 内存大小
product_rom = pw.CharField(
max_length=5,
constraints=[pw.Check('product_rom in ("128GB", "256GB")')]) # 储存大小
total = pw.IntegerField()
good_rate = pw.CharField(max_length=4, null=True) # 好评率
default_good = pw.IntegerField(null=True) # 默认好评数
star_one = pw.IntegerField(null=True) # 一星数量
star_two = pw.IntegerField(null=True) # 二星数量
star_three = pw.IntegerField(null=True) # 三星数量
star_four = pw.IntegerField(null=True) # 四星数量
star_five = pw.IntegerField(null=True) # 五星数量
class Meta:
primary_key = pw.CompositeKey('source', 'is_official', 'product_color',
'product_ram', 'product_rom') # 复合主键
class ModelCount(BaseModel):
# 产品型号信息
product_color = pw.CharField(
max_length=4,
constraints=[
pw.Check('product_color in ("国风雅灰", "钛银黑", "冰海蓝", "蜜桃金")')
]) # 产品颜色版本
product_ram = pw.CharField(max_length=4,
constraints=[
pw.Check('product_ram in ("8GB", "12GB")')
]) # 内存大小
product_rom = pw.CharField(
max_length=5,
constraints=[pw.Check('product_rom in ("128GB", "256GB")')]) # 储存大小
total = pw.IntegerField(default=0)
percentage = pw.CharField(max_length=4, null=True) # 该型号占总数的百分比
cal_total = pw.IntegerField(default=0) # 用于计算好评率的总数 (京东计算规则)
good_rate = pw.CharField(max_length=4, null=True) # 好评率
default_good = pw.IntegerField(default=0) # 默认好评数
good_count = pw.IntegerField(default=0) # 好评数
general_count = pw.IntegerField(default=0) # 中评数
bad_count = pw.IntegerField(default=0) # 差评数
star_one = pw.IntegerField(default=0) # 一星数量
star_two = pw.IntegerField(default=0) # 二星数量
star_three = pw.IntegerField(default=0) # 三星数量
star_four = pw.IntegerField(default=0) # 四星数量
star_five = pw.IntegerField(default=0) # 五星数量
class Meta:
primary_key = pw.CompositeKey('product_color', 'product_ram',
'product_rom') # 复合主键
class ModelCount(BaseModel):
color = pw.CharField(
max_length=2,
constraints=[
pw.Check('color in ("黑色", "白色", "红色", "黄色", "紫色", "绿色")')
])
rom = pw.CharField(
max_length=5,
constraints=[pw.Check('rom in ("64GB", "128GB", "256GB")')])
total = pw.IntegerField(default=0)
percentage = pw.CharField(max_length=4, null=True) # 该型号占总数的百分比
cal_total = pw.IntegerField(default=0) # 用于计算好评率的总数 (京东计算规则)
good_rate = pw.CharField(max_length=4, null=True) # 好评率
default_good = pw.IntegerField(default=0) # 默认好评数
good_count = pw.IntegerField(default=0) # 好评数
general_count = pw.IntegerField(default=0) # 中评数
bad_count = pw.IntegerField(default=0) # 差评数
star_one = pw.IntegerField(default=0) # 一星数量
star_two = pw.IntegerField(default=0) # 二星数量
star_three = pw.IntegerField(default=0) # 三星数量
star_four = pw.IntegerField(default=0) # 四星数量
star_five = pw.IntegerField(default=0) # 五星数量
class Meta:
primary_key = pw.CompositeKey('color', 'rom') # 复合主键
class Comment(BaseModel):
comment_id = pw.CharField(max_length=20,
primary_key=True) # 评论ID, 前缀JD, SN
source = pw.CharField(max_length=4,
constraints=[pw.Check('source in ("京东", "苏宁")')])
is_self = pw.BooleanField()
create_time = pw.DateTimeField() # 评论创建时间
content = pw.TextField() # 评论内容
star = pw.SmallIntegerField(constraints=[pw.Check('star between 0 and 5')
]) # 评分星级
after_time = pw.DateTimeField(null=True) # 追评时间
after_content = pw.TextField(null=True) # 追评内容
after_days = pw.SmallIntegerField(null=True) # 追评间隔时间
order_time = pw.DateTimeField(null=True) # 下单时间, 限京东数据源
order_days = pw.SmallIntegerField(null=True) # 从下单到评论的时间(包括物流时间)
user_device = pw.CharField(
max_length=10, null=True) # 用户设备类型, Android, iOS, other, 限京东和苏宁数据源
color = pw.CharField(
max_length=2,
constraints=[
pw.Check('color in ("黑色", "白色", "红色", "黄色", "紫色", "绿色")')
])
rom = pw.CharField(
max_length=5,
constraints=[pw.Check('rom in ("64GB", "128GB", "256GB")')])
class Media(BaseModel):
# base table containing all media entries
title = peewee.CharField(unique=True)
alt_title = peewee.CharField(null=True)
series = peewee.ForeignKeyField(Series, backref='sequels', null=True)
order = peewee.DecimalField(
max_digits=2, null=True
) # the story's chronological order ex: Star Wars Ep. 1, 2, 3, 4, 5, 6 | NOT 4, 5, 6, 1, 2, 3
media_type = peewee.ForeignKeyField(
MediaTypes, backref='media') # movie | tv show | etc
animated = peewee.BooleanField()
country = peewee.ForeignKeyField(Countries,
backref='media') # USA | UK | Japan | etc
language = peewee.ManyToManyField(Languages, backref='media')
subtitles = peewee.BooleanField(null=True)
year = peewee.IntegerField(constraints=[peewee.Check('year > 1900')],
null=True) # release year
genres = peewee.ManyToManyField(Genres, backref='media')
director = peewee.ForeignKeyField(Directors, backref='media', null=True)
studio = peewee.ForeignKeyField(Studios, backref='media', null=True)
actors = peewee.ManyToManyField(
Actors, backref='media') # ManyToManyField does not support null=True
plot = peewee.CharField(null=True)
rating = peewee.IntegerField(constraints=[
peewee.Check('rating >= 1 AND rating <=10')
],
null=True) # 1 to 10
tags = peewee.ManyToManyField(
Tags, backref='media') # ManyToManyField does not support null=True
notes = peewee.CharField(null=True)
class UserTable(BaseModel):
'''
Defines the schema of the Users table
'''
user_id = pw.CharField(primary_key=True,
constraints=[pw.Check('LENGTH(user_id) < 30')])
user_name = pw.CharField(constraints=[pw.Check('LENGTH(user_name) < 30')])
user_last_name = pw.CharField(
constraints=[pw.Check('LENGTH(user_last_name) < 100')])
user_email = pw.CharField()
class Department(BaseModel):
"""
Defines the department, which contains the department number,
department name, job name(s), and manager.
"""
logger.info("Defining table: Department.")
logger.info(
"Adding department_name primary key field to Department table.")
department_name = pw.CharField(max_length=40, null=False, primary_key=True)
logger.info("Adding manager field to Department table.")
manager = pw.CharField(max_length=30)
logger.info("Adding department_number field to Department table. "
"The field has four digits and must start with a letter.\n")
department_number = pw.CharField(
max_length=4,
unique=True,
constraints=[
pw.Check('length(department_number) >= 4'),
pw.Check('substr(department_number, 1) >= "A"'),
pw.Check('substr(department_number, 1) <= "Z"'),
pw.Check('substr(department_number, 2, 1) >= "0"'),
pw.Check('substr(department_number, 2, 1) <= "9"'),
pw.Check('substr(department_number, 3, 1) >= "0"'),
pw.Check('substr(department_number, 3, 1) <= "9"'),
pw.Check('substr(department_number, 4, 1) >= "0"'),
pw.Check('substr(department_number, 4, 1) <= "9"')
])
class UserModel(GenericModel):
"""
Represents the table "usermodel"
"""
id = peewee.IntegerField(primary_key=True)
name = peewee.CharField(unique=True,
null=False,
constraints=[peewee.Check('name <> ""')])
token = peewee.CharField(unique=True,
null=False,
constraints=[peewee.Check('token <> ""')])
class MiSku(BaseModel):
sku = pw.CharField(max_length=20, primary_key=True) # 商品SKU编号
# 产品型号信息
product_color = pw.CharField(
max_length=4,
constraints=[
pw.Check('product_color in ("国风雅灰", "钛银黑", "冰海蓝", "蜜桃金")')
]) # 产品颜色版本
product_ram = pw.CharField(max_length=4,
constraints=[
pw.Check('product_ram in ("8GB", "12GB")')
]) # 内存大小
product_rom = pw.CharField(
max_length=5,
constraints=[pw.Check('product_rom in ("128GB", "256GB")')]) # 储存大小
class LogoAnnotation(BaseModel):
"""Annotation(s) for an image prediction
(an image prediction might lead to several annotations)
At the moment, this is mostly for logo (see run_object_detection),
when we have a logo prediction above a certain threshold we create an entry,
to ask user for annotation on the logo (https://hunger.openfoodfacts.org/logos)
and eventual annotation will land there.
"""
image_prediction = peewee.ForeignKeyField(ImagePrediction,
null=False,
backref="logo_detections")
index = peewee.IntegerField(null=False,
constraints=[peewee.Check("index >= 0")])
bounding_box = BinaryJSONField(null=False)
score = peewee.FloatField(null=False)
annotation_value = peewee.CharField(null=True, index=True)
annotation_value_tag = peewee.CharField(null=True, index=True)
taxonomy_value = peewee.CharField(null=True, index=True)
annotation_type = peewee.CharField(null=True, index=True)
username = peewee.TextField(null=True, index=True)
completed_at = peewee.DateTimeField(null=True, index=True)
nearest_neighbors = BinaryJSONField(null=True)
class Meta:
constraints = [peewee.SQL("UNIQUE(image_prediction_id, index)")]
def get_crop_image_url(self) -> str:
return crop_image_url(self.image_prediction.image.source_image,
self.bounding_box)
class RecordPage(Model):
"""
Records paginator db model:
record - record foreign key
page - page number
"""
record = peewee.ForeignKeyField(Record,
db_column='record',
to_field='uid',
related_name='record_records_pages',
on_delete='CASCADE',
index=True,
unique=True)
page = peewee.IntegerField(default=0,
index=True,
constraints=[
peewee.Check('page >= 0'),
])
class Meta:
indexes = ((
(
'record',
'page',
),
True,
), )
constraints = (peewee.SQL('UNIQUE (record, page)'), )
class Ee0(peewee.Model):
id = peewee.PrimaryKeyField(sequence='ee0_seq' if db.sequences else None)
port = peewee.IntegerField(constraints=[peewee.Check('port > 0')])
user = peewee.CharField(unique=True)
available = peewee.BooleanField(default=False)
class Meta:
database = db
class TargetSku(BaseModel):
source = pw.CharField(max_length=4,
constraints=[pw.Check('source in ("京东", "苏宁")')
]) # 数据源平台
sku = pw.CharField(max_length=20) # 商品SKU编号
class Meta:
primary_key = pw.CompositeKey('source', 'sku')
class Transaction(BaseModel):
id = p.AutoField(primary_key=True)
from_account = p.ForeignKeyField(Account, backref="transactions_from", null=False)
to_account = p.ForeignKeyField(Account, backref="transactions_to", null=False)
timestamp = p.DateTimeField(default=datetime.datetime.now)
amount = p.IntegerField(constraints=[
p.Check('amount > 0')
])
class BaseModel(pw.Model):
# 平台数据源
source = pw.CharField(
max_length=4,
constraints=[pw.Check('source in ("京东", "苏宁", "小米有品", "小米商城")')])
is_official = pw.BooleanField() # 是否为官方自营/官方旗舰店
class Meta:
database = mi10_db
class Users(peewee.Model):
id = peewee.IntegerField(primary_key=True,
constraints=[peewee.Check(constraint="id > 0")])
first_name = peewee.CharField(max_length=255)
last_name = peewee.CharField(max_length=255, default=None, null=True)
username = peewee.CharField(max_length=32, default=None, null=True)
timestamp = peewee.DateTimeField(default=datetime.datetime.utcnow,
null=False)
# prevent forwards to master
is_blocked = peewee.BooleanField(
default=False,
null=False,
constraints=[peewee.Check(constraint="is_blocked BETWEEN 0 AND 1")],
)
class Meta:
database = DB
class User2Option(peewee.Model):
option = peewee.ForeignKeyField(Option)
user = peewee.ForeignKeyField(User)
vote = peewee.IntegerField(constraints = [peewee.Check('vote = 1')])
class Meta:
indexes = (
(('option', 'user'), True),
)
database = psql_db
class UserActivity(BaseModel):
source = pw.CharField(max_length=4,
primary_key=True,
constraints=[pw.Check('source in ("京东", "苏宁")')])
total = pw.IntegerField()
active_count = pw.IntegerField() # 活跃用户数 (非默认好评)
active_percentage = pw.CharField(max_length=4)
inactive_count = pw.IntegerField() # 非活跃用户数 (默认好评数)
inactive_percentage = pw.CharField(max_length=4)
def test_str_constraints(tmpdir):
manager = DatabaseManager('sqlite:///:memory:', directory=tmpdir)
with manager.migrator.create_table('awesome') as table:
table.primary_key('id')
table.char('username',
constraints=[
"check (username in ('tim', 'bob'))",
peewee.Check("username in ('tim', 'bob')")
])
class Task(BaseModel):
project = peewee.ForeignKeyField(Project, related_name='tasks')
name = peewee.CharField(null=False)
details = peewee.TextField()
progress = peewee.IntegerField(default=0,
constraints=[
peewee.Check('progress >= 0'),
peewee.Check('progress <= 100')
])
start_date = peewee.DateField(default=datetime.today())
end_date = peewee.DateField()
def dict(self):
return {
"id": self.id,
"name": self.name,
"details": self.details,
"progress": self.progress,
"start_date": self.start_date.strftime('%m/%d/%Y'),
"end_date": self.end_date.strftime('%m/%d/%Y')
}
class Department(BaseModel):
"""
This class defines Department
"""
department_num_check = 'upper( substr( department_num, 1, 1 ) BETWEEN "A" AND "Z" )'
logger.info('Department Name')
department_name = pw.CharField(primary_key=True, max_length=30)
logger.info('Department Manager')
department_manager = pw.CharField(max_length=30)
logger.info('Department Number')
department_num = pw.CharField(max_length=4, constraints=[pw.Check(department_num_check)])
class OrderedResult(BaseModel):
class Meta:
indexes = [(('parent', 'order'), True)]
order_by = ('parent', 'order')
order = pw.IntegerField()
parent = pw.ForeignKeyField('self', null=True, related_name='children')
artifact = pw.ForeignKeyField(ArtifactProxy, null=True, constraints=[
pw.Check(
'(artifact_id IS NULL AND primitive IS NULL) OR '
'(artifact_id IS NULL AND primitive IS NOT NULL) OR '
'(artifact_id IS NOT NULL AND primitive IS NULL)'
)])
primitive = pw.BlobField(null=True)
class ModelSummary(BaseModel):
source = pw.CharField(max_length=4,
constraints=[pw.Check('source in ("京东", "苏宁")')])
is_self = pw.BooleanField()
color = pw.CharField(
max_length=2,
constraints=[
pw.Check('color in ("黑色", "白色", "红色", "黄色", "紫色", "绿色")')
])
rom = pw.CharField(
max_length=5,
constraints=[pw.Check('rom in ("64GB", "128GB", "256GB")')])
total = pw.IntegerField()
good_rate = pw.CharField(max_length=4, null=True) # 好评率
default_good = pw.IntegerField(null=True) # 默认好评数
star_one = pw.IntegerField(null=True) # 一星数量
star_two = pw.IntegerField(null=True) # 二星数量
star_three = pw.IntegerField(null=True) # 三星数量
star_four = pw.IntegerField(null=True) # 四星数量
star_five = pw.IntegerField(null=True) # 五星数量
class Meta:
primary_key = pw.CompositeKey('source', 'is_self', 'color',
'rom') # 复合主键
class CommentSummary(BaseModel):
source = pw.CharField(max_length=4,
constraints=[pw.Check('source in ("京东", "苏宁")')])
is_self = pw.BooleanField()
total = pw.IntegerField() # 评论总数
good_rate = pw.CharField(max_length=4, null=True) # 好评率
default_good = pw.IntegerField(null=True) # 默认好评数
star_one = pw.IntegerField(null=True) # 一星数量
star_two = pw.IntegerField(null=True) # 二星数量
star_three = pw.IntegerField(null=True) # 三星数量
star_four = pw.IntegerField(null=True) # 四星数量
star_five = pw.IntegerField(null=True) # 五星数量
class Meta:
primary_key = pw.CompositeKey('source', 'is_self') # 复合主键
class Department(BaseModel):
"""
This class defines Department, which maintains details of the department
in which a person held a job.
"""
dept_num_check = 'upper( substr( dept_num, 1, 1 ) BETWEEN "A" AND "Z" )'
logger.info('The department name')
dept_name = pw.CharField(primary_key=True, max_length=30)
logger.info('The department manager')
dept_mgr = pw.CharField(max_length=30)
logger.info('The department number')
dept_num = pw.CharField(max_length=4,
constraints=[pw.Check(dept_num_check)])
class Total(BaseModel):
source = pw.CharField(max_length=4,
primary_key=True,
constraints=[pw.Check('source in ("京东", "苏宁")')])
total = pw.IntegerField() # 基于CommentSummary的总数
all_models_total = pw.IntegerField() # 基于所有ModelSummary加和的数据
good_rate = pw.CharField(max_length=4) # 好评率
default_good = pw.IntegerField() # 默认好评数
good_count = pw.IntegerField() # 好评数
general_count = pw.IntegerField() # 中评数
bad_count = pw.IntegerField() # 差评数
star_one = pw.IntegerField() # 一星数量
star_two = pw.IntegerField() # 二星数量
star_three = pw.IntegerField() # 三星数量
star_four = pw.IntegerField() # 四星数量
star_five = pw.IntegerField() # 五星数量
class Listings(BaseModel):
id = pw.IntegerField(primary_key=True)
name = pw.CharField()
host_id = pw.IntegerField()
host_name = pw.CharField()
neighbourhood = pw.CharField()
latitude = pw.DecimalField(max_digits=16, decimal_places=14)
longitude = pw.DecimalField(max_digits=16, decimal_places=14)
room_type = pw.CharField()
price = pw.DecimalField(max_digits=6, decimal_places=2, constraints=[pw.Check('price >=0')])
minimum_nights = pw.IntegerField()
number_of_reviews = pw.IntegerField()
last_review = pw.DateField()
reviews_per_month = pw.DecimalField(max_digits=5, decimal_places=2)
calculated_host_listings_count = pw.IntegerField()
availability_365 = pw.IntegerField()
class Comment(BaseModel):
# 评论ID, 前缀JD, SN, YP, YX
comment_id = pw.CharField(max_length=20, primary_key=True)
source = pw.CharField(max_length=4)
is_official = pw.BooleanField()
create_time = pw.DateTimeField() # 评论创建时间
content = pw.TextField() # 评论内容
star = pw.SmallIntegerField(constraints=[pw.Check('star between 0 and 5')
]) # 评分星级
spec = pw.CharField(max_length=120) # 产品型号信息
after_time = pw.DateTimeField(null=True) # 追评时间
after_content = pw.TextField(null=True) # 追评内容
after_days = pw.SmallIntegerField(null=True) # 追评间隔时间
order_time = pw.DateTimeField(null=True) # 下单时间, 限京东数据源
order_days = pw.SmallIntegerField(null=True) # 从下单到评论的时间(包括物流时间)
user_device = pw.CharField(
max_length=10, null=True) # 用户设备类型, Android, iOS, other, 限京东和苏宁数据源
class RomCount(BaseModel):
rom = pw.CharField(
max_length=5,
primary_key=True,
constraints=[pw.Check('rom in ("64GB", "128GB", "256GB")')])
total = pw.IntegerField()
percentage = pw.CharField(max_length=4)
cal_total = pw.IntegerField()
good_rate = pw.CharField(max_length=4)
default_good = pw.IntegerField() # 默认好评数
good_count = pw.IntegerField() # 好评数
general_count = pw.IntegerField() # 中评数
bad_count = pw.IntegerField() # 差评数
star_one = pw.IntegerField() # 一星数量
star_two = pw.IntegerField() # 二星数量
star_three = pw.IntegerField() # 三星数量
star_four = pw.IntegerField() # 四星数量
star_five = pw.IntegerField() # 五星数量