|
|
"""
|
|
|
数据库模型 - SQLAlchemy Models
|
|
|
"""
|
|
|
from datetime import datetime
|
|
|
from sqlalchemy import Column, Integer, String, Numeric, BigInteger, DateTime, Boolean, Text, ForeignKey
|
|
|
from sqlalchemy.orm import declarative_base, relationship
|
|
|
|
|
|
# TimescaleDB 基础类 (时序数据)
|
|
|
TimescaleBase = declarative_base()
|
|
|
|
|
|
# SQLite 基础类 (配置数据)
|
|
|
SQLiteBase = declarative_base()
|
|
|
|
|
|
|
|
|
# ==================== SQLite Models (配置数据) ====================
|
|
|
|
|
|
class User(SQLiteBase):
|
|
|
"""用户模型"""
|
|
|
__tablename__ = "users"
|
|
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
|
username = Column(String(50), unique=True, nullable=False, index=True)
|
|
|
password_hash = Column(String(255), nullable=False)
|
|
|
email = Column(String(100))
|
|
|
role = Column(String(20), default="user") # admin, user
|
|
|
is_active = Column(Boolean, default=True)
|
|
|
created_at = Column(DateTime, default=datetime.utcnow)
|
|
|
updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
|
|
|
|
|
|
# 关系
|
|
|
api_keys = relationship("APIKey", back_populates="user", cascade="all, delete-orphan")
|
|
|
alerts = relationship("Alert", back_populates="user", cascade="all, delete-orphan")
|
|
|
subscriptions = relationship("Subscription", back_populates="user", cascade="all, delete-orphan")
|
|
|
|
|
|
|
|
|
class APIKey(SQLiteBase):
|
|
|
"""API Key 模型"""
|
|
|
__tablename__ = "api_keys"
|
|
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
|
user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
|
key_hash = Column(String(255), unique=True, nullable=False, index=True)
|
|
|
name = Column(String(100))
|
|
|
permissions = Column(Text) # JSON 格式存储权限
|
|
|
expires_at = Column(DateTime)
|
|
|
is_active = Column(Boolean, default=True)
|
|
|
created_at = Column(DateTime, default=datetime.utcnow)
|
|
|
last_used_at = Column(DateTime)
|
|
|
|
|
|
# 关系
|
|
|
user = relationship("User", back_populates="api_keys")
|
|
|
|
|
|
|
|
|
class Alert(SQLiteBase):
|
|
|
"""告警配置模型"""
|
|
|
__tablename__ = "alerts"
|
|
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
|
user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
|
symbol = Column(String(20), nullable=False, index=True)
|
|
|
condition_type = Column(String(20), nullable=False) # greater_than, less_than, equals
|
|
|
condition_value = Column(Numeric(20, 8), nullable=False)
|
|
|
alert_type = Column(String(20), default="price") # price, percent_change
|
|
|
status = Column(String(20), default="active") # active, triggered, disabled
|
|
|
triggered_at = Column(DateTime)
|
|
|
created_at = Column(DateTime, default=datetime.utcnow)
|
|
|
updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
|
|
|
|
|
|
# 关系
|
|
|
user = relationship("User", back_populates="alerts")
|
|
|
|
|
|
|
|
|
class Subscription(SQLiteBase):
|
|
|
"""数据订阅模型"""
|
|
|
__tablename__ = "subscriptions"
|
|
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
|
user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
|
symbol = Column(String(20), nullable=False, index=True)
|
|
|
period = Column(String(10)) # 1m, 5m, 1h, 1d 等
|
|
|
subscription_type = Column(String(20), default="kline") # kline, realtime
|
|
|
is_active = Column(Boolean, default=True)
|
|
|
created_at = Column(DateTime, default=datetime.utcnow)
|
|
|
|
|
|
# 关系
|
|
|
user = relationship("User", back_populates="subscriptions")
|
|
|
|
|
|
|
|
|
# ==================== TimescaleDB Models (时序数据) ====================
|
|
|
# 注意：TimescaleDB 表使用原生 SQL 操作，这里仅作为参考
|
|
|
|
|
|
class KlineData(TimescaleBase):
|
|
|
"""K 线数据模型 (TimescaleDB hypertable)"""
|
|
|
__tablename__ = "kline_data"
|
|
|
|
|
|
time = Column(DateTime, primary_key=True)
|
|
|
symbol = Column(String(20), primary_key=True)
|
|
|
period = Column(String(10), primary_key=True)
|
|
|
open = Column(Numeric(20, 8), nullable=False)
|
|
|
high = Column(Numeric(20, 8), nullable=False)
|
|
|
low = Column(Numeric(20, 8), nullable=False)
|
|
|
close = Column(Numeric(20, 8), nullable=False)
|
|
|
volume = Column(BigInteger, nullable=False)
|
|
|
amount = Column(Numeric(30, 8), default=0)
|
|
|
open_interest = Column(BigInteger, default=0)
|
|
|
created_at = Column(DateTime, default=datetime.utcnow)
|
|
|
|
|
|
|
|
|
class RealtimeQuote(TimescaleBase):
|
|
|
"""实时行情模型 (TimescaleDB hypertable)"""
|
|
|
__tablename__ = "realtime_quotes"
|
|
|
|
|
|
time = Column(DateTime, primary_key=True)
|
|
|
symbol = Column(String(20), primary_key=True)
|
|
|
last_price = Column(Numeric(20, 8), nullable=False)
|
|
|
open_price = Column(Numeric(20, 8))
|
|
|
high_price = Column(Numeric(20, 8))
|
|
|
low_price = Column(Numeric(20, 8))
|
|
|
prev_close = Column(Numeric(20, 8))
|
|
|
volume = Column(BigInteger)
|
|
|
amount = Column(Numeric(30, 8))
|
|
|
bid_price_1 = Column(Numeric(20, 8))
|
|
|
bid_volume_1 = Column(BigInteger)
|
|
|
ask_price_1 = Column(Numeric(20, 8))
|
|
|
ask_volume_1 = Column(BigInteger)
|
|
|
position = Column(BigInteger, default=0)
|
