1"""Initiate Marzneshin database
2
3Revision ID: 20faa9f18c0a
4Revises:
5Create Date: 2024-01-15 09:48:24.808505
6
7"""
8
9import os
10from alembic import op
11import sqlalchemy as sa
12from app.utils.crypto import generate_certificate
13
14# revision identifiers, used by Alembic.
15revision = "20faa9f18c0a"
16down_revision = None
17branch_labels = None
18depends_on = None
19
20
21def upgrade() -> None:
22 # ### commands auto generated by Alembic - please adjust! ###
23 op.create_table(
24 "admins",
25 sa.Column("id", sa.Integer(), nullable=False),
26 sa.Column("username", sa.String(length=32), nullable=True),
27 sa.Column("hashed_password", sa.String(length=128), nullable=True),
28 sa.Column("created_at", sa.DateTime(), nullable=True),
29 sa.Column("is_sudo", sa.Boolean(), nullable=True),
30 sa.Column("password_reset_at", sa.DateTime(), nullable=True),
31 sa.PrimaryKeyConstraint("id"),
32 )
33 op.create_index(
34 op.f("ix_admins_username"), "admins", ["username"], unique=True
35 )
36
37 jwttable = op.create_table(
38 "jwt",
39 sa.Column("id", sa.Integer(), nullable=False),
40 sa.Column("secret_key", sa.String(length=64), nullable=False),
41 sa.PrimaryKeyConstraint("id"),
42 )
43 op.bulk_insert(jwttable, [{"id": 1, "secret_key": os.urandom(32).hex()}])
44
45 nodestable = op.create_table(
46 "nodes",
47 sa.Column("id", sa.Integer(), nullable=False),
48 sa.Column("name", sa.String(length=256), nullable=True),
49 sa.Column("address", sa.String(length=256), nullable=True),
50 sa.Column("port", sa.Integer(), nullable=True),
51 sa.Column("connection_backend", sa.String(32), nullable=True),
52 sa.Column("xray_version", sa.String(length=32), nullable=True),
53 sa.Column(
54 "status",
55 sa.Enum("healthy", "unhealthy", "disabled", name="nodestatus"),
56 nullable=False,
57 ),
58 sa.Column("last_status_change", sa.DateTime(), nullable=True),
59 sa.Column("message", sa.String(length=1024), nullable=True),
60 sa.Column("created_at", sa.DateTime(), nullable=True),
61 sa.Column("uplink", sa.BigInteger(), nullable=True),
62 sa.Column("downlink", sa.BigInteger(), nullable=True),
63 sa.Column(
64 "usage_coefficient",
65 sa.Float(),
66 server_default=sa.text("(1.0)"),
67 nullable=False,
68 ),
69 sa.PrimaryKeyConstraint("id"),
70 sa.UniqueConstraint("name"),
71 sa.UniqueConstraint("address", "port"),
72 )
73 op.bulk_insert(
74 nodestable,
75 [
76 {
77 "name": "local",
78 "address": "127.0.0.1",
79 "port": 53042,
80 "connection_backend": "grpcio",
81 "status": "unhealthy",
82 }
83 ],
84 )
85 op.create_table(
86 "services",
87 sa.Column("id", sa.Integer(), nullable=False),
88 sa.Column("name", sa.String(length=64), nullable=True),
89 sa.PrimaryKeyConstraint("id"),
90 )
91
92 systable = op.create_table(
93 "system",
94 sa.Column("id", sa.Integer(), nullable=False),
95 sa.Column("uplink", sa.BigInteger(), nullable=True),
96 sa.Column("downlink", sa.BigInteger(), nullable=True),
97 sa.PrimaryKeyConstraint("id"),
98 )
99 op.bulk_insert(systable, [{"id": 1, "uplink": 0, "downlink": 0}])
100
101 tlstable = op.create_table(
102 "tls",
103 sa.Column("id", sa.Integer(), nullable=False),
104 sa.Column("key", sa.String(length=4096), nullable=False),
105 sa.Column("certificate", sa.String(length=2048), nullable=False),
106 sa.PrimaryKeyConstraint("id"),
107 )
108 tls = generate_certificate()
109 op.bulk_insert(
110 tlstable, [{"id": 1, "key": tls["key"], "certificate": tls["cert"]}]
111 )
112
113 op.create_table(
114 "inbounds",
115 sa.Column("id", sa.Integer(), nullable=False),
116 sa.Column(
117 "protocol",
118 sa.Enum(
119 "VMess", "VLESS", "Trojan", "Shadowsocks", name="proxytypes"
120 ),
121 nullable=True,
122 ),
123 sa.Column("tag", sa.String(length=256), nullable=False),
124 sa.Column("config", sa.String(512), nullable=False),
125 sa.Column("node_id", sa.Integer(), nullable=True),
126 sa.ForeignKeyConstraint(
127 ["node_id"],
128 ["nodes.id"],
129 ),
130 sa.PrimaryKeyConstraint("id"),
131 sa.UniqueConstraint("node_id", "tag"),
132 )
133 op.create_index(
134 op.f("ix_inbounds_node_id"), "inbounds", ["node_id"], unique=False
135 )
136 op.create_table(
137 "node_usages",
138 sa.Column("id", sa.Integer(), nullable=False),
139 sa.Column("created_at", sa.DateTime(), nullable=False),
140 sa.Column("node_id", sa.Integer(), nullable=True),
141 sa.Column("uplink", sa.BigInteger(), nullable=True),
142 sa.Column("downlink", sa.BigInteger(), nullable=True),
143 sa.ForeignKeyConstraint(
144 ["node_id"],
145 ["nodes.id"],
146 ),
147 sa.PrimaryKeyConstraint("id"),
148 sa.UniqueConstraint("created_at", "node_id"),
149 )
150 op.create_table(
151 "users",
152 sa.Column("id", sa.Integer(), nullable=False),
153 sa.Column("username", sa.String(length=32), nullable=True),
154 sa.Column("key", sa.String(length=64), nullable=True),
155 sa.Column(
156 "status",
157 sa.Enum(
158 "active", "limited", "expired", "on_hold", name="userstatus"
159 ),
160 nullable=False,
161 ),
162 sa.Column("used_traffic", sa.BigInteger(), nullable=True),
163 sa.Column("data_limit", sa.BigInteger(), nullable=True),
164 sa.Column(
165 "data_limit_reset_strategy",
166 sa.Enum(
167 "no_reset",
168 "day",
169 "week",
170 "month",
171 "year",
172 name="userdatalimitresetstrategy",
173 ),
174 nullable=False,
175 ),
176 sa.Column("ip_limit", sa.Integer(), nullable=False),
177 sa.Column("settings", sa.String(1024), nullable=True),
178 sa.Column("expire", sa.DateTime(), nullable=True),
179 sa.Column("admin_id", sa.Integer(), nullable=True),
180 sa.Column("sub_updated_at", sa.DateTime(), nullable=True),
181 sa.Column("sub_last_user_agent", sa.String(length=512), nullable=True),
182 sa.Column("created_at", sa.DateTime(), nullable=True),
183 sa.Column("note", sa.String(length=500), nullable=True),
184 sa.Column("online_at", sa.DateTime(), nullable=True),
185 sa.Column("on_hold_expire_duration", sa.BigInteger(), nullable=True),
186 sa.Column("on_hold_timeout", sa.DateTime(), nullable=True),
187 sa.Column("edit_at", sa.DateTime(), nullable=True),
188 sa.ForeignKeyConstraint(
189 ["admin_id"],
190 ["admins.id"],
191 ),
192 sa.PrimaryKeyConstraint("id"),
193 sa.UniqueConstraint("key"),
194 )
195 op.create_index(
196 op.f("ix_users_username"), "users", ["username"], unique=True
197 )
198 op.create_table(
199 "hosts",
200 sa.Column("id", sa.Integer(), nullable=False),
201 sa.Column("remark", sa.String(length=256), nullable=False),
202 sa.Column("address", sa.String(length=256), nullable=False),
203 sa.Column("port", sa.Integer(), nullable=True),
204 sa.Column("path", sa.String(length=256), nullable=True),
205 sa.Column("sni", sa.String(length=1024), nullable=True),
206 sa.Column("host", sa.String(length=1024), nullable=True),
207 sa.Column(
208 "security",
209 sa.Enum(
210 "inbound_default", "none", "tls", name="inboundhostsecurity"
211 ),
212 nullable=False,
213 ),
214 sa.Column(
215 "alpn",
216 sa.Enum(
217 "none", "h2", "http/1.1", "h2,http/1.1", name="proxyhostalpn"
218 ),
219 server_default="none",
220 nullable=False,
221 ),
222 sa.Column(
223 "fingerprint",
224 sa.Enum(
225 "none",
226 "chrome",
227 "firefox",
228 "safari",
229 "ios",
230 "android",
231 "edge",
232 "360",
233 "qq",
234 "random",
235 "randomized",
236 name="proxyhostfingerprint",
237 ),
238 server_default="none",
239 nullable=False,
240 ),
241 sa.Column("inbound_id", sa.Integer(), nullable=False),
242 sa.Column("allowinsecure", sa.Boolean(), nullable=True),
243 sa.Column("is_disabled", sa.Boolean(), nullable=True),
244 sa.ForeignKeyConstraint(
245 ["inbound_id"],
246 ["inbounds.id"],
247 ),
248 sa.PrimaryKeyConstraint("id"),
249 )
250 op.create_table(
251 "inbounds_services",
252 sa.Column("inbound_id", sa.Integer(), nullable=False),
253 sa.Column("service_id", sa.Integer(), nullable=False),
254 sa.ForeignKeyConstraint(
255 ["inbound_id"],
256 ["inbounds.id"],
257 ),
258 sa.ForeignKeyConstraint(
259 ["service_id"],
260 ["services.id"],
261 ),
262 sa.PrimaryKeyConstraint("inbound_id", "service_id"),
263 )
264 op.create_table(
265 "node_user_usages",
266 sa.Column("id", sa.Integer(), nullable=False),
267 sa.Column("created_at", sa.DateTime(), nullable=False),
268 sa.Column("user_id", sa.Integer(), nullable=True),
269 sa.Column("node_id", sa.Integer(), nullable=True),
270 sa.Column("used_traffic", sa.BigInteger(), nullable=True),
271 sa.ForeignKeyConstraint(
272 ["node_id"],
273 ["nodes.id"],
274 ),
275 sa.ForeignKeyConstraint(
276 ["user_id"],
277 ["users.id"],
278 ),
279 sa.PrimaryKeyConstraint("id"),
280 sa.UniqueConstraint("created_at", "user_id", "node_id"),
281 )
282 op.create_table(
283 "notification_reminders",
284 sa.Column("id", sa.Integer(), nullable=False),
285 sa.Column("user_id", sa.Integer(), nullable=True),
286 sa.Column(
287 "type",
288 sa.Enum("expiration_date", "data_usage", name="remindertype"),
289 nullable=False,
290 ),
291 sa.Column("expires_at", sa.DateTime(), nullable=True),
292 sa.Column("created_at", sa.DateTime(), nullable=True),
293 sa.ForeignKeyConstraint(
294 ["user_id"],
295 ["users.id"],
296 ),
297 sa.PrimaryKeyConstraint("id"),
298 )
299 op.create_table(
300 "user_usage_logs",
301 sa.Column("id", sa.Integer(), nullable=False),
302 sa.Column("user_id", sa.Integer(), nullable=True),
303 sa.Column("used_traffic_at_reset", sa.BigInteger(), nullable=False),
304 sa.Column("reset_at", sa.DateTime(), nullable=True),
305 sa.ForeignKeyConstraint(
306 ["user_id"],
307 ["users.id"],
308 ),
309 sa.PrimaryKeyConstraint("id"),
310 )
311 op.create_table(
312 "users_services",
313 sa.Column("user_id", sa.Integer(), nullable=False),
314 sa.Column("service_id", sa.Integer(), nullable=False),
315 sa.ForeignKeyConstraint(
316 ["service_id"],
317 ["services.id"],
318 ),
319 sa.ForeignKeyConstraint(
320 ["user_id"],
321 ["users.id"],
322 ),
323 sa.PrimaryKeyConstraint("user_id", "service_id"),
324 )
325 # ### end Alembic commands ###
326
327
328def downgrade() -> None:
329 # ### commands auto generated by Alembic - please adjust! ###
330 op.drop_table("users_services")
331 op.drop_table("user_usage_logs")
332 op.drop_table("notification_reminders")
333 op.drop_table("node_user_usages")
334 op.drop_table("inbounds_services")
335 op.drop_table("hosts")
336 op.drop_index(op.f("ix_users_username"), table_name="users")
337 op.drop_table("users")
338 op.drop_table("node_usages")
339 op.drop_index(op.f("ix_inbounds_node_id"), table_name="inbounds")
340 op.drop_table("inbounds")
341 op.drop_table("tls")
342 op.drop_table("system")
343 op.drop_table("services")
344 op.drop_table("nodes")
345 op.drop_table("jwt")
346 op.drop_index(op.f("ix_admins_username"), table_name="admins")
347 op.drop_table("admins")
348 # ### end Alembic commands ###