gitscope
/
← marzneshin / app/db/migrations/versions/20faa9f18c0a_init.py / blame
Files Commits 446 Branches 1 Tags 43 Markers 4 README License
https://dev.kronoz.win/r/marzneshin.git
app/db/migrations/versions/20faa9f18c0a_init.py · v0.3.0-alpha2
Source
1 a47eb4c Dawsh 2y ago """Initiate Marzneshin database
2 a47eb4c Dawsh 2y ago
3 a47eb4c Dawsh 2y ago Revision ID: 20faa9f18c0a
4 a47eb4c Dawsh 2y ago Revises:
5 a47eb4c Dawsh 2y ago Create Date: 2024-01-15 09:48:24.808505
6 a47eb4c Dawsh 2y ago
7 a47eb4c Dawsh 2y ago """
8 6457378 Dawsh 2y ago
9 a47eb4c Dawsh 2y ago import os
10 a47eb4c Dawsh 2y ago from alembic import op
11 a47eb4c Dawsh 2y ago import sqlalchemy as sa
12 a47eb4c Dawsh 2y ago from app.utils.crypto import generate_certificate
13 a47eb4c Dawsh 2y ago
14 a47eb4c Dawsh 2y ago # revision identifiers, used by Alembic.
15 a47eb4c Dawsh 2y ago revision = '20faa9f18c0a'
16 a47eb4c Dawsh 2y ago down_revision = None
17 a47eb4c Dawsh 2y ago branch_labels = None
18 a47eb4c Dawsh 2y ago depends_on = None
19 a47eb4c Dawsh 2y ago
20 a47eb4c Dawsh 2y ago
21 a47eb4c Dawsh 2y ago def upgrade() -> None:
22 a47eb4c Dawsh 2y ago # ### commands auto generated by Alembic - please adjust! ###
23 a47eb4c Dawsh 2y ago op.create_table('admins',
24 a47eb4c Dawsh 2y ago sa.Column('id', sa.Integer(), nullable=False),
25 a47eb4c Dawsh 2y ago sa.Column('username', sa.String(length=32), nullable=True),
26 a47eb4c Dawsh 2y ago sa.Column('hashed_password', sa.String(length=128), nullable=True),
27 a47eb4c Dawsh 2y ago sa.Column('created_at', sa.DateTime(), nullable=True),
28 a47eb4c Dawsh 2y ago sa.Column('is_sudo', sa.Boolean(), nullable=True),
29 a47eb4c Dawsh 2y ago sa.Column('password_reset_at', sa.DateTime(), nullable=True),
30 a47eb4c Dawsh 2y ago sa.PrimaryKeyConstraint('id')
31 a47eb4c Dawsh 2y ago )
32 a47eb4c Dawsh 2y ago op.create_index(op.f('ix_admins_username'), 'admins', ['username'], unique=True)
33 a47eb4c Dawsh 2y ago
34 a47eb4c Dawsh 2y ago jwttable = op.create_table('jwt',
35 a47eb4c Dawsh 2y ago sa.Column('id', sa.Integer(), nullable=False),
36 a47eb4c Dawsh 2y ago sa.Column('secret_key', sa.String(length=64), nullable=False),
37 a47eb4c Dawsh 2y ago sa.PrimaryKeyConstraint('id')
38 a47eb4c Dawsh 2y ago )
39 a47eb4c Dawsh 2y ago op.bulk_insert(jwttable, [{"id": 1, "secret_key": os.urandom(32).hex()}])
40 a47eb4c Dawsh 2y ago
41 af7254f Dawsh 2y ago nodestable = op.create_table('nodes',
42 a47eb4c Dawsh 2y ago sa.Column('id', sa.Integer(), nullable=False),
43 6457378 Dawsh 2y ago sa.Column('name', sa.String(length=256), nullable=True),
44 4d647dd Dawsh 2y ago sa.Column('address', sa.String(length=256), nullable=True),
45 4d647dd Dawsh 2y ago sa.Column('port', sa.Integer(), nullable=True),
46 f6a32ea Dawsh 2y ago sa.Column('connection_backend', sa.String(), nullable=True),
47 a47eb4c Dawsh 2y ago sa.Column('xray_version', sa.String(length=32), nullable=True),
48 4d647dd Dawsh 2y ago sa.Column('status', sa.Enum('healthy', 'unhealthy', 'disabled', name='nodestatus'), nullable=False),
49 a47eb4c Dawsh 2y ago sa.Column('last_status_change', sa.DateTime(), nullable=True),
50 a47eb4c Dawsh 2y ago sa.Column('message', sa.String(length=1024), nullable=True),
51 a47eb4c Dawsh 2y ago sa.Column('created_at', sa.DateTime(), nullable=True),
52 a47eb4c Dawsh 2y ago sa.Column('uplink', sa.BigInteger(), nullable=True),
53 a47eb4c Dawsh 2y ago sa.Column('downlink', sa.BigInteger(), nullable=True),
54 a47eb4c Dawsh 2y ago sa.Column('usage_coefficient', sa.Float(), server_default=sa.text('(1.0)'), nullable=False),
55 a47eb4c Dawsh 2y ago sa.PrimaryKeyConstraint('id'),
56 6457378 Dawsh 2y ago sa.UniqueConstraint('name'),
57 6457378 Dawsh 2y ago sa.UniqueConstraint('address', 'port')
58 a47eb4c Dawsh 2y ago )
59 af7254f Dawsh 2y ago op.bulk_insert(nodestable, [{"name": "local", "address": "127.0.0.1", "port": 53042, "connection_backend": "grpcio", "status": "unhealthy"}])
60 a47eb4c Dawsh 2y ago op.create_table('services',
61 a47eb4c Dawsh 2y ago sa.Column('id', sa.Integer(), nullable=False),
62 a47eb4c Dawsh 2y ago sa.Column('name', sa.String(length=64), nullable=True),
63 a47eb4c Dawsh 2y ago sa.PrimaryKeyConstraint('id')
64 a47eb4c Dawsh 2y ago )
65 6457378 Dawsh 2y ago
66 a47eb4c Dawsh 2y ago systable = op.create_table('system',
67 a47eb4c Dawsh 2y ago sa.Column('id', sa.Integer(), nullable=False),
68 a47eb4c Dawsh 2y ago sa.Column('uplink', sa.BigInteger(), nullable=True),
69 a47eb4c Dawsh 2y ago sa.Column('downlink', sa.BigInteger(), nullable=True),
70 a47eb4c Dawsh 2y ago sa.PrimaryKeyConstraint('id')
71 a47eb4c Dawsh 2y ago )
72 a47eb4c Dawsh 2y ago op.bulk_insert(systable, [{"id": 1, "uplink": 0, "downlink": 0}])
73 6457378 Dawsh 2y ago
74 a47eb4c Dawsh 2y ago tlstable = op.create_table('tls',
75 a47eb4c Dawsh 2y ago sa.Column('id', sa.Integer(), nullable=False),
76 a47eb4c Dawsh 2y ago sa.Column('key', sa.String(length=4096), nullable=False),
77 a47eb4c Dawsh 2y ago sa.Column('certificate', sa.String(length=2048), nullable=False),
78 a47eb4c Dawsh 2y ago sa.PrimaryKeyConstraint('id')
79 a47eb4c Dawsh 2y ago )
80 a47eb4c Dawsh 2y ago tls = generate_certificate()
81 a47eb4c Dawsh 2y ago op.bulk_insert(tlstable, [{"id": 1, "key": tls['key'], "certificate": tls['cert']}])
82 a47eb4c Dawsh 2y ago
83 a47eb4c Dawsh 2y ago op.create_table('inbounds',
84 a47eb4c Dawsh 2y ago sa.Column('id', sa.Integer(), nullable=False),
85 6457378 Dawsh 2y ago sa.Column('protocol', sa.Enum('VMess', 'VLESS', 'Trojan', 'Shadowsocks', name='proxytypes'), nullable=True),
86 a47eb4c Dawsh 2y ago sa.Column('tag', sa.String(length=256), nullable=False),
87 4d647dd Dawsh 2y ago sa.Column('config', sa.String(), nullable=False),
88 a47eb4c Dawsh 2y ago sa.Column('node_id', sa.Integer(), nullable=True),
89 a47eb4c Dawsh 2y ago sa.ForeignKeyConstraint(['node_id'], ['nodes.id'], ),
90 6457378 Dawsh 2y ago sa.PrimaryKeyConstraint('id'),
91 6457378 Dawsh 2y ago sa.UniqueConstraint("node_id", "tag")
92 a47eb4c Dawsh 2y ago )
93 a47eb4c Dawsh 2y ago op.create_index(op.f('ix_inbounds_node_id'), 'inbounds', ['node_id'], unique=False)
94 a47eb4c Dawsh 2y ago op.create_table('node_usages',
95 a47eb4c Dawsh 2y ago sa.Column('id', sa.Integer(), nullable=False),
96 a47eb4c Dawsh 2y ago sa.Column('created_at', sa.DateTime(), nullable=False),
97 a47eb4c Dawsh 2y ago sa.Column('node_id', sa.Integer(), nullable=True),
98 a47eb4c Dawsh 2y ago sa.Column('uplink', sa.BigInteger(), nullable=True),
99 a47eb4c Dawsh 2y ago sa.Column('downlink', sa.BigInteger(), nullable=True),
100 a47eb4c Dawsh 2y ago sa.ForeignKeyConstraint(['node_id'], ['nodes.id'], ),
101 a47eb4c Dawsh 2y ago sa.PrimaryKeyConstraint('id'),
102 a47eb4c Dawsh 2y ago sa.UniqueConstraint('created_at', 'node_id')
103 a47eb4c Dawsh 2y ago )
104 a47eb4c Dawsh 2y ago op.create_table('users',
105 a47eb4c Dawsh 2y ago sa.Column('id', sa.Integer(), nullable=False),
106 6457378 Dawsh 2y ago sa.Column('username', sa.String(length=32), nullable=True),
107 a47eb4c Dawsh 2y ago sa.Column('key', sa.String(length=64), nullable=True),
108 a47eb4c Dawsh 2y ago sa.Column('status', sa.Enum('active', 'disabled', 'limited', 'expired', 'on_hold', name='userstatus'), nullable=False),
109 a47eb4c Dawsh 2y ago sa.Column('used_traffic', sa.BigInteger(), nullable=True),
110 a47eb4c Dawsh 2y ago sa.Column('data_limit', sa.BigInteger(), nullable=True),
111 a47eb4c Dawsh 2y ago sa.Column('data_limit_reset_strategy', sa.Enum('no_reset', 'day', 'week', 'month', 'year', name='userdatalimitresetstrategy'), nullable=False),
112 a47eb4c Dawsh 2y ago sa.Column('ip_limit', sa.Integer(), nullable=False),
113 a47eb4c Dawsh 2y ago sa.Column('settings', sa.String(), nullable=True),
114 f446990 Dawsh 2y ago sa.Column('expire', sa.DateTime(), nullable=True),
115 a47eb4c Dawsh 2y ago sa.Column('admin_id', sa.Integer(), nullable=True),
116 a47eb4c Dawsh 2y ago sa.Column('sub_updated_at', sa.DateTime(), nullable=True),
117 a47eb4c Dawsh 2y ago sa.Column('sub_last_user_agent', sa.String(length=512), nullable=True),
118 a47eb4c Dawsh 2y ago sa.Column('created_at', sa.DateTime(), nullable=True),
119 a47eb4c Dawsh 2y ago sa.Column('note', sa.String(length=500), nullable=True),
120 a47eb4c Dawsh 2y ago sa.Column('online_at', sa.DateTime(), nullable=True),
121 a47eb4c Dawsh 2y ago sa.Column('on_hold_expire_duration', sa.BigInteger(), nullable=True),
122 a47eb4c Dawsh 2y ago sa.Column('on_hold_timeout', sa.DateTime(), nullable=True),
123 a47eb4c Dawsh 2y ago sa.Column('edit_at', sa.DateTime(), nullable=True),
124 a47eb4c Dawsh 2y ago sa.ForeignKeyConstraint(['admin_id'], ['admins.id'], ),
125 6457378 Dawsh 2y ago sa.PrimaryKeyConstraint('id'),
126 6457378 Dawsh 2y ago sa.UniqueConstraint('key')
127 a47eb4c Dawsh 2y ago )
128 a47eb4c Dawsh 2y ago op.create_index(op.f('ix_users_username'), 'users', ['username'], unique=True)
129 a47eb4c Dawsh 2y ago op.create_table('hosts',
130 a47eb4c Dawsh 2y ago sa.Column('id', sa.Integer(), nullable=False),
131 a47eb4c Dawsh 2y ago sa.Column('remark', sa.String(length=256), nullable=False),
132 a47eb4c Dawsh 2y ago sa.Column('address', sa.String(length=256), nullable=False),
133 a47eb4c Dawsh 2y ago sa.Column('port', sa.Integer(), nullable=True),
134 a47eb4c Dawsh 2y ago sa.Column('path', sa.String(length=256), nullable=True),
135 a47eb4c Dawsh 2y ago sa.Column('sni', sa.String(length=256), nullable=True),
136 a47eb4c Dawsh 2y ago sa.Column('host', sa.String(length=256), nullable=True),
137 a47eb4c Dawsh 2y ago sa.Column('security', sa.Enum('inbound_default', 'none', 'tls', name='inboundhostsecurity'), nullable=False),
138 a47eb4c Dawsh 2y ago sa.Column('alpn', sa.Enum('none', 'h2', 'http/1.1', 'h2,http/1.1', name='proxyhostalpn'), server_default='none', nullable=False),
139 a47eb4c Dawsh 2y ago sa.Column('fingerprint', sa.Enum('none', 'chrome', 'firefox', 'safari', 'ios', 'android', 'edge', '360', 'qq', 'random', 'randomized', name='proxyhostfingerprint'), server_default='none', nullable=False),
140 a47eb4c Dawsh 2y ago sa.Column('inbound_id', sa.Integer(), nullable=False),
141 a47eb4c Dawsh 2y ago sa.Column('allowinsecure', sa.Boolean(), nullable=True),
142 a47eb4c Dawsh 2y ago sa.Column('is_disabled', sa.Boolean(), nullable=True),
143 a47eb4c Dawsh 2y ago sa.ForeignKeyConstraint(['inbound_id'], ['inbounds.id'], ),
144 a47eb4c Dawsh 2y ago sa.PrimaryKeyConstraint('id')
145 a47eb4c Dawsh 2y ago )
146 a47eb4c Dawsh 2y ago op.create_table('inbounds_services',
147 a47eb4c Dawsh 2y ago sa.Column('inbound_id', sa.Integer(), nullable=False),
148 a47eb4c Dawsh 2y ago sa.Column('service_id', sa.Integer(), nullable=False),
149 a47eb4c Dawsh 2y ago sa.ForeignKeyConstraint(['inbound_id'], ['inbounds.id'], ),
150 a47eb4c Dawsh 2y ago sa.ForeignKeyConstraint(['service_id'], ['services.id'], ),
151 a47eb4c Dawsh 2y ago sa.PrimaryKeyConstraint('inbound_id', 'service_id')
152 a47eb4c Dawsh 2y ago )
153 a47eb4c Dawsh 2y ago op.create_table('node_user_usages',
154 a47eb4c Dawsh 2y ago sa.Column('id', sa.Integer(), nullable=False),
155 a47eb4c Dawsh 2y ago sa.Column('created_at', sa.DateTime(), nullable=False),
156 a47eb4c Dawsh 2y ago sa.Column('user_id', sa.Integer(), nullable=True),
157 a47eb4c Dawsh 2y ago sa.Column('node_id', sa.Integer(), nullable=True),
158 a47eb4c Dawsh 2y ago sa.Column('used_traffic', sa.BigInteger(), nullable=True),
159 a47eb4c Dawsh 2y ago sa.ForeignKeyConstraint(['node_id'], ['nodes.id'], ),
160 a47eb4c Dawsh 2y ago sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
161 a47eb4c Dawsh 2y ago sa.PrimaryKeyConstraint('id'),
162 a47eb4c Dawsh 2y ago sa.UniqueConstraint('created_at', 'user_id', 'node_id')
163 a47eb4c Dawsh 2y ago )
164 a47eb4c Dawsh 2y ago op.create_table('notification_reminders',
165 a47eb4c Dawsh 2y ago sa.Column('id', sa.Integer(), nullable=False),
166 a47eb4c Dawsh 2y ago sa.Column('user_id', sa.Integer(), nullable=True),
167 a47eb4c Dawsh 2y ago sa.Column('type', sa.Enum('expiration_date', 'data_usage', name='remindertype'), nullable=False),
168 a47eb4c Dawsh 2y ago sa.Column('expires_at', sa.DateTime(), nullable=True),
169 a47eb4c Dawsh 2y ago sa.Column('created_at', sa.DateTime(), nullable=True),
170 a47eb4c Dawsh 2y ago sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
171 a47eb4c Dawsh 2y ago sa.PrimaryKeyConstraint('id')
172 a47eb4c Dawsh 2y ago )
173 a47eb4c Dawsh 2y ago op.create_table('user_usage_logs',
174 a47eb4c Dawsh 2y ago sa.Column('id', sa.Integer(), nullable=False),
175 a47eb4c Dawsh 2y ago sa.Column('user_id', sa.Integer(), nullable=True),
176 a47eb4c Dawsh 2y ago sa.Column('used_traffic_at_reset', sa.BigInteger(), nullable=False),
177 a47eb4c Dawsh 2y ago sa.Column('reset_at', sa.DateTime(), nullable=True),
178 a47eb4c Dawsh 2y ago sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
179 a47eb4c Dawsh 2y ago sa.PrimaryKeyConstraint('id')
180 a47eb4c Dawsh 2y ago )
181 a47eb4c Dawsh 2y ago op.create_table('users_services',
182 a47eb4c Dawsh 2y ago sa.Column('user_id', sa.Integer(), nullable=False),
183 a47eb4c Dawsh 2y ago sa.Column('service_id', sa.Integer(), nullable=False),
184 a47eb4c Dawsh 2y ago sa.ForeignKeyConstraint(['service_id'], ['services.id'], ),
185 a47eb4c Dawsh 2y ago sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
186 a47eb4c Dawsh 2y ago sa.PrimaryKeyConstraint('user_id', 'service_id')
187 a47eb4c Dawsh 2y ago )
188 a47eb4c Dawsh 2y ago # ### end Alembic commands ###
189 a47eb4c Dawsh 2y ago
190 a47eb4c Dawsh 2y ago
191 a47eb4c Dawsh 2y ago def downgrade() -> None:
192 a47eb4c Dawsh 2y ago # ### commands auto generated by Alembic - please adjust! ###
193 a47eb4c Dawsh 2y ago op.drop_table('users_services')
194 a47eb4c Dawsh 2y ago op.drop_table('user_usage_logs')
195 a47eb4c Dawsh 2y ago op.drop_table('notification_reminders')
196 a47eb4c Dawsh 2y ago op.drop_table('node_user_usages')
197 a47eb4c Dawsh 2y ago op.drop_table('inbounds_services')
198 a47eb4c Dawsh 2y ago op.drop_table('hosts')
199 a47eb4c Dawsh 2y ago op.drop_index(op.f('ix_users_username'), table_name='users')
200 a47eb4c Dawsh 2y ago op.drop_table('users')
201 a47eb4c Dawsh 2y ago op.drop_table('node_usages')
202 a47eb4c Dawsh 2y ago op.drop_index(op.f('ix_inbounds_node_id'), table_name='inbounds')
203 a47eb4c Dawsh 2y ago op.drop_table('inbounds')
204 a47eb4c Dawsh 2y ago op.drop_table('tls')
205 a47eb4c Dawsh 2y ago op.drop_table('system')
206 a47eb4c Dawsh 2y ago op.drop_table('services')
207 a47eb4c Dawsh 2y ago op.drop_table('nodes')
208 a47eb4c Dawsh 2y ago op.drop_table('jwt')
209 a47eb4c Dawsh 2y ago op.drop_index(op.f('ix_admins_username'), table_name='admins')
210 a47eb4c Dawsh 2y ago op.drop_table('admins')
211 a47eb4c Dawsh 2y ago # ### end Alembic commands ###