diff --git a/app/db/migrations/versions/20faa9f18c0a_init.py b/app/db/migrations/versions/20faa9f18c0a_init.py index adefe4b..0bcbd56 100644 --- a/app/db/migrations/versions/20faa9f18c0a_init.py +++ b/app/db/migrations/versions/20faa9f18c0a_init.py @@ -5,6 +5,7 @@ Revises: Create Date: 2024-01-15 09:48:24.808505 """ + import os from alembic import op import sqlalchemy as sa @@ -28,7 +29,6 @@ def upgrade() -> None: sa.Column('password_reset_at', sa.DateTime(), nullable=True), sa.PrimaryKeyConstraint('id') ) - op.create_index(op.f('ix_admins_id'), 'admins', ['id'], unique=False) op.create_index(op.f('ix_admins_username'), 'admins', ['username'], unique=True) jwttable = op.create_table('jwt', @@ -40,7 +40,7 @@ def upgrade() -> None: op.create_table('nodes', sa.Column('id', sa.Integer(), nullable=False), - sa.Column('name', sa.String(length=256, collation='NOCASE'), nullable=True), + sa.Column('name', sa.String(length=256), nullable=True), sa.Column('address', sa.String(length=256), nullable=True), sa.Column('port', sa.Integer(), nullable=True), sa.Column('xray_version', sa.String(length=32), nullable=True), @@ -52,17 +52,15 @@ def upgrade() -> None: sa.Column('downlink', sa.BigInteger(), nullable=True), sa.Column('usage_coefficient', sa.Float(), server_default=sa.text('(1.0)'), nullable=False), sa.PrimaryKeyConstraint('id'), - sa.UniqueConstraint('name') + sa.UniqueConstraint('name'), + sa.UniqueConstraint('address', 'port') ) - op.create_index(op.f('ix_nodes_id'), 'nodes', ['id'], unique=False) op.create_table('services', sa.Column('id', sa.Integer(), nullable=False), sa.Column('name', sa.String(length=64), nullable=True), sa.PrimaryKeyConstraint('id') ) - op.create_index(op.f('ix_services_id'), 'services', ['id'], unique=False) - op.create_index(op.f('ix_services_name'), 'services', ['name'], unique=False) - + systable = op.create_table('system', sa.Column('id', sa.Integer(), nullable=False), sa.Column('uplink', sa.BigInteger(), nullable=True), @@ -70,8 +68,7 @@ def upgrade() -> None: sa.PrimaryKeyConstraint('id') ) op.bulk_insert(systable, [{"id": 1, "uplink": 0, "downlink": 0}]) - op.create_index(op.f('ix_system_id'), 'system', ['id'], unique=False) - + tlstable = op.create_table('tls', sa.Column('id', sa.Integer(), nullable=False), sa.Column('key', sa.String(length=4096), nullable=False), @@ -83,15 +80,15 @@ def upgrade() -> None: op.create_table('inbounds', sa.Column('id', sa.Integer(), nullable=False), - sa.Column('protocol', sa.String(), nullable=True), + sa.Column('protocol', sa.Enum('VMess', 'VLESS', 'Trojan', 'Shadowsocks', name='proxytypes'), nullable=True), sa.Column('tag', sa.String(length=256), nullable=False), sa.Column('config', sa.String(), nullable=False), sa.Column('node_id', sa.Integer(), nullable=True), sa.ForeignKeyConstraint(['node_id'], ['nodes.id'], ), - sa.PrimaryKeyConstraint('id') + sa.PrimaryKeyConstraint('id'), + sa.UniqueConstraint("node_id", "tag") ) op.create_index(op.f('ix_inbounds_node_id'), 'inbounds', ['node_id'], unique=False) - op.create_index(op.f('ix_inbounds_tag'), 'inbounds', ['tag'], unique=False) op.create_table('node_usages', sa.Column('id', sa.Integer(), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), @@ -102,10 +99,9 @@ def upgrade() -> None: sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('created_at', 'node_id') ) - op.create_index(op.f('ix_node_usages_id'), 'node_usages', ['id'], unique=False) op.create_table('users', sa.Column('id', sa.Integer(), nullable=False), - sa.Column('username', sa.String(length=32, collation='NOCASE'), nullable=True), + sa.Column('username', sa.String(length=32), nullable=True), sa.Column('key', sa.String(length=64), nullable=True), sa.Column('status', sa.Enum('active', 'disabled', 'limited', 'expired', 'on_hold', name='userstatus'), nullable=False), sa.Column('used_traffic', sa.BigInteger(), nullable=True), @@ -124,10 +120,9 @@ def upgrade() -> None: sa.Column('on_hold_timeout', sa.DateTime(), nullable=True), sa.Column('edit_at', sa.DateTime(), nullable=True), sa.ForeignKeyConstraint(['admin_id'], ['admins.id'], ), - sa.PrimaryKeyConstraint('id') + sa.PrimaryKeyConstraint('id'), + sa.UniqueConstraint('key') ) - op.create_index(op.f('ix_users_id'), 'users', ['id'], unique=False) - op.create_index(op.f('ix_users_key'), 'users', ['key'], unique=True) op.create_index(op.f('ix_users_username'), 'users', ['username'], unique=True) op.create_table('hosts', sa.Column('id', sa.Integer(), nullable=False), @@ -164,7 +159,6 @@ def upgrade() -> None: sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('created_at', 'user_id', 'node_id') ) - op.create_index(op.f('ix_node_user_usages_id'), 'node_user_usages', ['id'], unique=False) op.create_table('notification_reminders', sa.Column('id', sa.Integer(), nullable=False), sa.Column('user_id', sa.Integer(), nullable=True), @@ -174,7 +168,6 @@ def upgrade() -> None: sa.ForeignKeyConstraint(['user_id'], ['users.id'], ), sa.PrimaryKeyConstraint('id') ) - op.create_index(op.f('ix_notification_reminders_id'), 'notification_reminders', ['id'], unique=False) op.create_table('user_usage_logs', sa.Column('id', sa.Integer(), nullable=False), sa.Column('user_id', sa.Integer(), nullable=True), @@ -183,7 +176,6 @@ def upgrade() -> None: sa.ForeignKeyConstraint(['user_id'], ['users.id'], ), sa.PrimaryKeyConstraint('id') ) - op.create_index(op.f('ix_user_usage_logs_id'), 'user_usage_logs', ['id'], unique=False) op.create_table('users_services', sa.Column('user_id', sa.Integer(), nullable=False), sa.Column('service_id', sa.Integer(), nullable=False), @@ -197,33 +189,21 @@ def upgrade() -> None: def downgrade() -> None: # ### commands auto generated by Alembic - please adjust! ### op.drop_table('users_services') - op.drop_index(op.f('ix_user_usage_logs_id'), table_name='user_usage_logs') op.drop_table('user_usage_logs') - op.drop_index(op.f('ix_notification_reminders_id'), table_name='notification_reminders') op.drop_table('notification_reminders') - op.drop_index(op.f('ix_node_user_usages_id'), table_name='node_user_usages') op.drop_table('node_user_usages') op.drop_table('inbounds_services') op.drop_table('hosts') op.drop_index(op.f('ix_users_username'), table_name='users') - op.drop_index(op.f('ix_users_key'), table_name='users') - op.drop_index(op.f('ix_users_id'), table_name='users') op.drop_table('users') - op.drop_index(op.f('ix_node_usages_id'), table_name='node_usages') op.drop_table('node_usages') - op.drop_index(op.f('ix_inbounds_tag'), table_name='inbounds') op.drop_index(op.f('ix_inbounds_node_id'), table_name='inbounds') op.drop_table('inbounds') op.drop_table('tls') - op.drop_index(op.f('ix_system_id'), table_name='system') op.drop_table('system') - op.drop_index(op.f('ix_services_name'), table_name='services') - op.drop_index(op.f('ix_services_id'), table_name='services') op.drop_table('services') - op.drop_index(op.f('ix_nodes_id'), table_name='nodes') op.drop_table('nodes') op.drop_table('jwt') op.drop_index(op.f('ix_admins_username'), table_name='admins') - op.drop_index(op.f('ix_admins_id'), table_name='admins') op.drop_table('admins') # ### end Alembic commands ### diff --git a/app/db/models.py b/app/db/models.py index f2a9eea..accd8ce 100644 --- a/app/db/models.py +++ b/app/db/models.py @@ -18,13 +18,13 @@ from app.models.user import (ReminderType, UserDataLimitResetStrategy, class Admin(Base): __tablename__ = "admins" - id = Column(Integer, primary_key=True, index=True) + id = Column(Integer, primary_key=True) username = Column(String(32), unique=True, index=True) hashed_password = Column(String(128)) users = relationship("User", back_populates="admin") created_at = Column(DateTime, default=datetime.utcnow) is_sudo = Column(Boolean, default=False) - password_reset_at = Column(DateTime, nullable=True) + password_reset_at = Column(DateTime) inbounds_services = Table( @@ -44,9 +44,9 @@ users_services = Table( class Service(Base): __tablename__ = "services" - id = Column(Integer, primary_key=True, index=True) - name = Column(String(64), index=True) - # modifications = Column(String(1024), nullable=True, default=None) + id = Column(Integer, primary_key=True) + name = Column(String(64)) + # modifications = Column(String(1024)) users = relationship("User", secondary=users_services, back_populates="services") inbounds = relationship("Inbound", secondary=inbounds_services, back_populates="services") @@ -54,9 +54,9 @@ class Service(Base): class User(Base): __tablename__ = "users" - id = Column(Integer, primary_key=True, index=True) - username = Column(String(32, collation='NOCASE'), unique=True, index=True) - key = Column(String(64), unique=True, index=True) + id = Column(Integer, primary_key=True) + username = Column(String(32), unique=True, index=True) + key = Column(String(64), unique=True) services = relationship("Service", secondary=users_services, back_populates="users", lazy="joined") inbounds = relationship( "Inbound", @@ -71,26 +71,26 @@ class User(Base): lazy="joined") notification_reminders = relationship("NotificationReminder", back_populates="user", cascade="all,delete,delete-orphan") - data_limit = Column(BigInteger, nullable=True) + data_limit = Column(BigInteger) data_limit_reset_strategy = Column( Enum(UserDataLimitResetStrategy), nullable=False, default=UserDataLimitResetStrategy.no_reset, ) ip_limit = Column(Integer, nullable=False, default=-1) - settings = Column(String, nullable=True, default=None) + settings = Column(String) usage_logs = relationship("UserUsageResetLogs", back_populates="user", lazy="joined") - expire = Column(Integer, nullable=True) + expire = Column(Integer) admin_id = Column(Integer, ForeignKey("admins.id")) admin = relationship("Admin", back_populates="users") - sub_updated_at = Column(DateTime, nullable=True, default=None) - sub_last_user_agent = Column(String(512), nullable=True, default=None) + sub_updated_at = Column(DateTime) + sub_last_user_agent = Column(String(512)) created_at = Column(DateTime, default=datetime.utcnow) - note = Column(String(500), nullable=True, default=None) - online_at = Column(DateTime, nullable=True, default=None) - on_hold_expire_duration = Column(BigInteger, nullable=True, default=None) - on_hold_timeout = Column(DateTime, nullable=True, default=None) - edit_at = Column(DateTime, nullable=True, default=None) + note = Column(String(500)) + online_at = Column(DateTime) + on_hold_expire_duration = Column(BigInteger) + on_hold_timeout = Column(DateTime) + edit_at = Column(DateTime) @property def lifetime_used_traffic(self): @@ -107,7 +107,7 @@ class User(Base): class UserUsageResetLogs(Base): __tablename__ = "user_usage_logs" - id = Column(Integer, primary_key=True, index=True) + id = Column(Integer, primary_key=True) user_id = Column(Integer, ForeignKey("users.id")) user = relationship("User", back_populates="usage_logs") used_traffic_at_reset = Column(BigInteger, nullable=False) @@ -116,10 +116,13 @@ class UserUsageResetLogs(Base): class Inbound(Base): __tablename__ = "inbounds" + __table_args__ = ( + UniqueConstraint("node_id", "tag"), + ) id = Column(Integer, primary_key=True) protocol = Column(Enum(ProxyTypes)) - tag = Column(String(256), nullable=False, index=True) + tag = Column(String(256), nullable=False) config = Column(String(), nullable=False) node_id = Column(Integer, ForeignKey("nodes.id"), index=True) node = relationship("Node", back_populates="inbounds") @@ -133,28 +136,25 @@ class InboundHost(Base): __tablename__ = "hosts" id = Column(Integer, primary_key=True) - remark = Column(String(256), unique=False, nullable=False) - address = Column(String(256), unique=False, nullable=False) - port = Column(Integer, nullable=True) - path = Column(String(256), unique=False, nullable=True) - sni = Column(String(256), unique=False, nullable=True) - host = Column(String(256), unique=False, nullable=True) + remark = Column(String(256), nullable=False) + address = Column(String(256), nullable=False) + port = Column(Integer) + path = Column(String(256)) + sni = Column(String(256)) + host = Column(String(256)) security = Column( Enum(InboundHostSecurity), - unique=False, nullable=False, default=InboundHostSecurity.inbound_default, ) alpn = Column( Enum(InboundHostALPN), - unique=False, nullable=False, default=InboundHostSecurity.none, server_default=InboundHostSecurity.none.name ) fingerprint = Column( Enum(InboundHostFingerprint), - unique=False, nullable=False, default=InboundHostSecurity.none, server_default=InboundHostSecurity.none.name @@ -162,14 +162,14 @@ class InboundHost(Base): inbound_id = Column(Integer, ForeignKey("inbounds.id"), nullable=False) inbound = relationship("Inbound", back_populates="hosts") - allowinsecure = Column(Boolean, nullable=True) - is_disabled = Column(Boolean, nullable=True, default=False) + allowinsecure = Column(Boolean, default=False) + is_disabled = Column(Boolean, default=False) class System(Base): __tablename__ = "system" - id = Column(Integer, primary_key=True, index=True) + id = Column(Integer, primary_key=True) uplink = Column(BigInteger, default=0) downlink = Column(BigInteger, default=0) @@ -193,15 +193,18 @@ class TLS(Base): class Node(Base): __tablename__ = "nodes" - id = Column(Integer, primary_key=True, index=True) - name = Column(String(256, collation='NOCASE'), unique=True) - address = Column(String(256), unique=False, nullable=True) - port = Column(Integer, unique=False, nullable=True) - xray_version = Column(String(32), nullable=True) - inbounds = relationship("Inbound", back_populates="node") + __table_args__ = ( + UniqueConstraint("address", "port"), + ) + id = Column(Integer, primary_key=True) + name = Column(String(256), unique=True) + address = Column(String(256)) + port = Column(Integer) + xray_version = Column(String(32)) + inbounds = relationship("Inbound", back_populates="node", cascade="all, delete") status = Column(Enum(NodeStatus), nullable=False, default=NodeStatus.unhealthy) last_status_change = Column(DateTime, default=datetime.utcnow) - message = Column(String(1024), nullable=True) + message = Column(String(1024)) created_at = Column(DateTime, default=datetime.utcnow) uplink = Column(BigInteger, default=0) downlink = Column(BigInteger, default=0) @@ -216,8 +219,8 @@ class NodeUserUsage(Base): UniqueConstraint('created_at', 'user_id', 'node_id'), ) - id = Column(Integer, primary_key=True, index=True) - created_at = Column(DateTime, unique=False, nullable=False) # one hour per record + id = Column(Integer, primary_key=True) + created_at = Column(DateTime, nullable=False) # one hour per record user_id = Column(Integer, ForeignKey("users.id")) user = relationship("User", back_populates="node_usages") node_id = Column(Integer, ForeignKey("nodes.id")) @@ -231,8 +234,8 @@ class NodeUsage(Base): UniqueConstraint('created_at', 'node_id'), ) - id = Column(Integer, primary_key=True, index=True) - created_at = Column(DateTime, unique=False, nullable=False) # one hour per record + id = Column(Integer, primary_key=True) + created_at = Column(DateTime, nullable=False) # one hour per record node_id = Column(Integer, ForeignKey("nodes.id")) node = relationship("Node", back_populates="usages") uplink = Column(BigInteger, default=0) @@ -242,9 +245,9 @@ class NodeUsage(Base): class NotificationReminder(Base): __tablename__ = "notification_reminders" - id = Column(Integer, primary_key=True, index=True) + id = Column(Integer, primary_key=True) user_id = Column(Integer, ForeignKey("users.id")) user = relationship("User", back_populates="notification_reminders") type = Column(Enum(ReminderType), nullable=False) - expires_at = Column(DateTime, nullable=True) + expires_at = Column(DateTime) created_at = Column(DateTime, default=datetime.utcnow) diff --git a/app/models/user.py b/app/models/user.py index 34d4a7f..ab965ff 100644 --- a/app/models/user.py +++ b/app/models/user.py @@ -2,9 +2,10 @@ import re import secrets from datetime import datetime from enum import Enum -from typing import List, Optional, Union +from typing import List, Optional, Union, Annotated -from pydantic import field_validator, ConfigDict, BaseModel, Field, validator, computed_field, ValidationInfo +from pydantic import field_validator, ConfigDict, BaseModel, Field, validator, computed_field, ValidationInfo, constr, \ + StringConstraints from app.models.proxy import InboundBase from config import XRAY_SUBSCRIPTION_URL_PREFIX @@ -50,7 +51,7 @@ class UserDataLimitResetStrategy(str, Enum): class UserBase(BaseModel): # proxies: Dict[ProxyTypes, ProxySettings] = {} id: Optional[int] = None - username: str + username: Annotated[str, StringConstraints(to_lower=True)] expire: Optional[int] = Field(None, nullable=True) key: str = Field(default_factory=lambda: secrets.token_hex(16)) data_limit: Optional[int] = Field( @@ -97,7 +98,7 @@ class UserBase(BaseModel): from app.models.service import ServiceBase -class User(UserBase): +class User(UserBase): pass