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