326 lines
14 KiB
Python
326 lines
14 KiB
Python
# -*- coding: utf-8 -*-
|
|
"""数据库模型"""
|
|
from datetime import datetime
|
|
from werkzeug.security import generate_password_hash, check_password_hash
|
|
from extensions import db
|
|
|
|
|
|
class Provider(db.Model):
|
|
"""厂商（一个厂商对应多个配置）"""
|
|
__tablename__ = "providers"
|
|
|
|
id = db.Column(db.Integer, primary_key=True)
|
|
name = db.Column(db.String(64), nullable=False, unique=True, index=True)
|
|
official_url = db.Column(db.String(512), nullable=True) # 厂商默认官网
|
|
|
|
plans = db.relationship("VPSPlan", backref="provider_rel", lazy="dynamic", foreign_keys="VPSPlan.provider_id")
|
|
|
|
|
|
class User(db.Model):
|
|
"""前台用户"""
|
|
__tablename__ = "users"
|
|
|
|
id = db.Column(db.Integer, primary_key=True)
|
|
username = db.Column(db.String(32), nullable=False, unique=True, index=True)
|
|
password_hash = db.Column(db.String(255), nullable=False)
|
|
created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow, index=True)
|
|
last_login_at = db.Column(db.DateTime, nullable=True)
|
|
is_banned = db.Column(db.Boolean, nullable=False, default=False, index=True)
|
|
banned_at = db.Column(db.DateTime, nullable=True)
|
|
banned_reason = db.Column(db.String(255), nullable=True)
|
|
|
|
posts = db.relationship(
|
|
"ForumPost",
|
|
backref="author_rel",
|
|
lazy="dynamic",
|
|
cascade="all, delete-orphan",
|
|
foreign_keys="ForumPost.user_id",
|
|
)
|
|
comments = db.relationship(
|
|
"ForumComment",
|
|
backref="author_rel",
|
|
lazy="dynamic",
|
|
cascade="all, delete-orphan",
|
|
foreign_keys="ForumComment.user_id",
|
|
)
|
|
reports = db.relationship(
|
|
"ForumReport",
|
|
backref="reporter_rel",
|
|
lazy="dynamic",
|
|
cascade="all, delete-orphan",
|
|
foreign_keys="ForumReport.reporter_id",
|
|
)
|
|
notifications = db.relationship(
|
|
"ForumNotification",
|
|
backref="recipient_rel",
|
|
lazy="dynamic",
|
|
cascade="all, delete-orphan",
|
|
foreign_keys="ForumNotification.user_id",
|
|
)
|
|
acted_notifications = db.relationship(
|
|
"ForumNotification",
|
|
backref="actor_rel",
|
|
lazy="dynamic",
|
|
foreign_keys="ForumNotification.actor_id",
|
|
)
|
|
post_likes = db.relationship(
|
|
"ForumPostLike",
|
|
backref="user_rel",
|
|
lazy="dynamic",
|
|
cascade="all, delete-orphan",
|
|
foreign_keys="ForumPostLike.user_id",
|
|
)
|
|
post_bookmarks = db.relationship(
|
|
"ForumPostBookmark",
|
|
backref="user_rel",
|
|
lazy="dynamic",
|
|
cascade="all, delete-orphan",
|
|
foreign_keys="ForumPostBookmark.user_id",
|
|
)
|
|
|
|
def set_password(self, raw_password):
|
|
self.password_hash = generate_password_hash(raw_password)
|
|
|
|
def check_password(self, raw_password):
|
|
if not self.password_hash:
|
|
return False
|
|
return check_password_hash(self.password_hash, raw_password)
|
|
|
|
|
|
class ForumCategory(db.Model):
|
|
"""论坛分类（后台可维护）"""
|
|
__tablename__ = "forum_categories"
|
|
|
|
id = db.Column(db.Integer, primary_key=True)
|
|
name = db.Column(db.String(32), nullable=False, unique=True, index=True)
|
|
sort_order = db.Column(db.Integer, nullable=False, default=100, index=True)
|
|
is_active = db.Column(db.Boolean, nullable=False, default=True, index=True)
|
|
created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow, index=True)
|
|
|
|
|
|
class VPSPlan(db.Model):
|
|
"""云服务器配置/方案（属于某厂商）"""
|
|
__tablename__ = "vps_plans"
|
|
|
|
id = db.Column(db.Integer, primary_key=True)
|
|
provider_id = db.Column(db.Integer, db.ForeignKey("providers.id"), nullable=True, index=True) # 关联厂商
|
|
provider = db.Column(db.String(64), nullable=True, index=True) # 冗余显示名，与 provider_id 二选一兼容旧数据
|
|
region = db.Column(db.String(128), nullable=True, index=True) # 保留兼容，不再在表单中填写
|
|
name = db.Column(db.String(128), nullable=True) # 可选，不填则用配置项生成显示
|
|
vcpu = db.Column(db.Integer, nullable=True)
|
|
memory_gb = db.Column(db.Integer, nullable=True)
|
|
storage_gb = db.Column(db.Integer, nullable=True)
|
|
bandwidth_mbps = db.Column(db.Integer, nullable=True)
|
|
traffic = db.Column(db.String(64), nullable=True)
|
|
price_cny = db.Column(db.Float, nullable=True)
|
|
price_usd = db.Column(db.Float, nullable=True)
|
|
currency = db.Column(db.String(8), default="CNY")
|
|
official_url = db.Column(db.String(512), nullable=True) # 该配置详情页，可覆盖厂商默认
|
|
countries = db.Column(db.String(255), nullable=True, index=True)
|
|
price_histories = db.relationship(
|
|
"PriceHistory",
|
|
backref="plan_rel",
|
|
lazy="dynamic",
|
|
cascade="all, delete-orphan",
|
|
foreign_keys="PriceHistory.plan_id",
|
|
)
|
|
|
|
@property
|
|
def provider_name(self):
|
|
if self.provider_rel:
|
|
return self.provider_rel.name
|
|
return self.provider or ""
|
|
|
|
@property
|
|
def display_name(self):
|
|
"""无规格名称时用配置项生成，如 2核4G 80GB"""
|
|
if self.name and self.name.strip():
|
|
return self.name.strip()
|
|
parts = []
|
|
if self.vcpu is not None:
|
|
parts.append("{}核".format(self.vcpu))
|
|
if self.memory_gb is not None:
|
|
parts.append("{}G".format(self.memory_gb))
|
|
if self.storage_gb is not None:
|
|
parts.append("{}GB".format(self.storage_gb))
|
|
if self.traffic and self.traffic.strip():
|
|
parts.append(self.traffic.strip())
|
|
return " ".join(parts) if parts else "—"
|
|
|
|
def to_dict(self):
|
|
return {
|
|
"id": self.id,
|
|
"provider": self.provider_name,
|
|
"region": self.region or "",
|
|
"name": self.display_name,
|
|
"vcpu": self.vcpu,
|
|
"memory_gb": self.memory_gb,
|
|
"storage_gb": self.storage_gb,
|
|
"bandwidth_mbps": self.bandwidth_mbps,
|
|
"traffic": self.traffic or "",
|
|
"price_cny": float(self.price_cny) if self.price_cny is not None else None,
|
|
"price_usd": float(self.price_usd) if self.price_usd is not None else None,
|
|
"currency": self.currency or "CNY",
|
|
"official_url": self.official_url or (self.provider_rel.official_url if self.provider_rel else "") or "",
|
|
"countries": self.countries or "",
|
|
}
|
|
|
|
|
|
class PriceHistory(db.Model):
|
|
"""配置价格历史快照"""
|
|
__tablename__ = "price_histories"
|
|
|
|
id = db.Column(db.Integer, primary_key=True)
|
|
plan_id = db.Column(db.Integer, db.ForeignKey("vps_plans.id"), nullable=False, index=True)
|
|
price_cny = db.Column(db.Float, nullable=True)
|
|
price_usd = db.Column(db.Float, nullable=True)
|
|
currency = db.Column(db.String(8), nullable=False, default="CNY")
|
|
source = db.Column(db.String(32), nullable=True) # manual / import / bootstrap
|
|
captured_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow, index=True)
|
|
|
|
|
|
class ForumPost(db.Model):
|
|
"""论坛帖子"""
|
|
__tablename__ = "forum_posts"
|
|
|
|
id = db.Column(db.Integer, primary_key=True)
|
|
user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False, index=True)
|
|
category = db.Column(db.String(32), nullable=False, default="综合讨论", index=True)
|
|
title = db.Column(db.String(160), nullable=False, index=True)
|
|
content = db.Column(db.Text, nullable=False)
|
|
view_count = db.Column(db.Integer, nullable=False, default=0)
|
|
is_pinned = db.Column(db.Boolean, nullable=False, default=False, index=True)
|
|
is_featured = db.Column(db.Boolean, nullable=False, default=False, index=True)
|
|
is_locked = db.Column(db.Boolean, nullable=False, default=False, index=True)
|
|
created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow, index=True)
|
|
updated_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow)
|
|
|
|
comments = db.relationship(
|
|
"ForumComment",
|
|
backref="post_rel",
|
|
lazy="dynamic",
|
|
cascade="all, delete-orphan",
|
|
foreign_keys="ForumComment.post_id",
|
|
)
|
|
likes = db.relationship(
|
|
"ForumPostLike",
|
|
backref="post_rel",
|
|
lazy="dynamic",
|
|
cascade="all, delete-orphan",
|
|
foreign_keys="ForumPostLike.post_id",
|
|
)
|
|
bookmarks = db.relationship(
|
|
"ForumPostBookmark",
|
|
backref="post_rel",
|
|
lazy="dynamic",
|
|
cascade="all, delete-orphan",
|
|
foreign_keys="ForumPostBookmark.post_id",
|
|
)
|
|
|
|
|
|
class ForumComment(db.Model):
|
|
"""论坛评论"""
|
|
__tablename__ = "forum_comments"
|
|
|
|
id = db.Column(db.Integer, primary_key=True)
|
|
post_id = db.Column(db.Integer, db.ForeignKey("forum_posts.id"), nullable=False, index=True)
|
|
user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False, index=True)
|
|
content = db.Column(db.Text, nullable=False)
|
|
created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow, index=True)
|
|
updated_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow)
|
|
|
|
|
|
class ForumPostLike(db.Model):
|
|
"""论坛帖子点赞"""
|
|
__tablename__ = "forum_post_likes"
|
|
__table_args__ = (
|
|
db.UniqueConstraint("post_id", "user_id", name="uq_forum_post_like_post_user"),
|
|
)
|
|
|
|
id = db.Column(db.Integer, primary_key=True)
|
|
post_id = db.Column(db.Integer, db.ForeignKey("forum_posts.id"), nullable=False, index=True)
|
|
user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False, index=True)
|
|
created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow, index=True)
|
|
|
|
|
|
class ForumPostBookmark(db.Model):
|
|
"""论坛帖子收藏"""
|
|
__tablename__ = "forum_post_bookmarks"
|
|
__table_args__ = (
|
|
db.UniqueConstraint("post_id", "user_id", name="uq_forum_post_bookmark_post_user"),
|
|
)
|
|
|
|
id = db.Column(db.Integer, primary_key=True)
|
|
post_id = db.Column(db.Integer, db.ForeignKey("forum_posts.id"), nullable=False, index=True)
|
|
user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False, index=True)
|
|
created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow, index=True)
|
|
|
|
|
|
class ForumReport(db.Model):
|
|
"""论坛举报"""
|
|
__tablename__ = "forum_reports"
|
|
|
|
id = db.Column(db.Integer, primary_key=True)
|
|
reporter_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False, index=True)
|
|
target_type = db.Column(db.String(16), nullable=False, index=True) # post / comment
|
|
target_id = db.Column(db.Integer, nullable=False, index=True)
|
|
reason = db.Column(db.String(64), nullable=False)
|
|
detail = db.Column(db.String(500), nullable=True)
|
|
snapshot_title = db.Column(db.String(160), nullable=True)
|
|
snapshot_content = db.Column(db.Text, nullable=True)
|
|
status = db.Column(db.String(16), nullable=False, default="pending", index=True) # pending/processed/rejected
|
|
review_note = db.Column(db.String(500), nullable=True)
|
|
reviewed_by = db.Column(db.String(64), nullable=True)
|
|
reviewed_at = db.Column(db.DateTime, nullable=True)
|
|
created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow, index=True)
|
|
|
|
|
|
class ForumNotification(db.Model):
|
|
"""站内通知"""
|
|
__tablename__ = "forum_notifications"
|
|
|
|
id = db.Column(db.Integer, primary_key=True)
|
|
user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False, index=True)
|
|
actor_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=True, index=True)
|
|
notif_type = db.Column(db.String(32), nullable=False, index=True) # post_commented/thread_replied/report_processed/content_removed
|
|
post_id = db.Column(db.Integer, nullable=True, index=True)
|
|
comment_id = db.Column(db.Integer, nullable=True, index=True)
|
|
report_id = db.Column(db.Integer, nullable=True, index=True)
|
|
message = db.Column(db.String(255), nullable=False)
|
|
is_read = db.Column(db.Boolean, nullable=False, default=False, index=True)
|
|
created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow, index=True)
|
|
|
|
|
|
class ForumTrackEvent(db.Model):
|
|
"""论坛埋点事件（用于漏斗与转化分析）"""
|
|
__tablename__ = "forum_track_events"
|
|
|
|
id = db.Column(db.Integer, primary_key=True)
|
|
event_name = db.Column(db.String(64), nullable=False, index=True)
|
|
label = db.Column(db.String(120), nullable=True)
|
|
post_id = db.Column(db.Integer, nullable=True, index=True)
|
|
user_id = db.Column(db.Integer, nullable=True, index=True)
|
|
visitor_id = db.Column(db.String(64), nullable=True, index=True)
|
|
cta_variant = db.Column(db.String(16), nullable=True, index=True)
|
|
device_type = db.Column(db.String(16), nullable=True, index=True)
|
|
page_path = db.Column(db.String(255), nullable=True)
|
|
endpoint_path = db.Column(db.String(64), nullable=True)
|
|
referer = db.Column(db.String(255), nullable=True)
|
|
ip = db.Column(db.String(120), nullable=True)
|
|
created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow, index=True)
|
|
|
|
|
|
class ForumTrackDailySummary(db.Model):
|
|
"""论坛埋点按天汇总（用于看板与导出）"""
|
|
__tablename__ = "forum_track_daily_summary"
|
|
__table_args__ = (
|
|
db.UniqueConstraint("event_day", "cta_variant", "event_name", name="uq_forum_track_daily"),
|
|
)
|
|
|
|
id = db.Column(db.Integer, primary_key=True)
|
|
event_day = db.Column(db.Date, nullable=False, index=True)
|
|
cta_variant = db.Column(db.String(16), nullable=False, default="unknown", index=True)
|
|
event_name = db.Column(db.String(64), nullable=False, index=True)
|
|
total = db.Column(db.Integer, nullable=False, default=0)
|
|
updated_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow, index=True)
|