1use crate::{
2 models::{
3 InsertQueryBuilder, UpdateQueryBuilder,
4 database_host::{DatabaseTransaction, DatabaseType},
5 },
6 prelude::*,
7 storage::StorageUrlRetriever,
8};
9use garde::Validate;
10use rand::distr::SampleString;
11use serde::{Deserialize, Serialize};
12use sqlx::{Row, postgres::PgRow};
13use std::{
14 collections::BTreeMap,
15 sync::{Arc, LazyLock},
16};
17use utoipa::ToSchema;
18
19#[derive(Serialize, Deserialize, Clone)]
20pub struct ServerDatabase {
21 pub uuid: uuid::Uuid,
22 pub server: Fetchable<super::server::Server>,
23 pub database_host: super::database_host::DatabaseHost,
24
25 pub name: compact_str::CompactString,
26 pub locked: bool,
27
28 pub username: compact_str::CompactString,
29 pub password: Vec<u8>,
30
31 pub created: chrono::NaiveDateTime,
32}
33
34impl BaseModel for ServerDatabase {
35 const NAME: &'static str = "server_database";
36
37 #[inline]
38 fn columns(prefix: Option<&str>) -> BTreeMap<&'static str, compact_str::CompactString> {
39 let prefix = prefix.unwrap_or_default();
40
41 let mut columns = BTreeMap::from([
42 (
43 "server_databases.uuid",
44 compact_str::format_compact!("{prefix}uuid"),
45 ),
46 (
47 "server_databases.server_uuid",
48 compact_str::format_compact!("{prefix}server_uuid"),
49 ),
50 (
51 "server_databases.name",
52 compact_str::format_compact!("{prefix}name"),
53 ),
54 (
55 "server_databases.locked",
56 compact_str::format_compact!("{prefix}locked"),
57 ),
58 (
59 "server_databases.username",
60 compact_str::format_compact!("{prefix}username"),
61 ),
62 (
63 "server_databases.password",
64 compact_str::format_compact!("{prefix}password"),
65 ),
66 (
67 "server_databases.created",
68 compact_str::format_compact!("{prefix}created"),
69 ),
70 ]);
71
72 columns.extend(super::database_host::DatabaseHost::columns(Some(
73 "database_host_",
74 )));
75
76 columns
77 }
78
79 #[inline]
80 fn map(prefix: Option<&str>, row: &PgRow) -> Result<Self, crate::database::DatabaseError> {
81 let prefix = prefix.unwrap_or_default();
82
83 Ok(Self {
84 uuid: row.try_get(compact_str::format_compact!("{prefix}uuid").as_str())?,
85 server: super::server::Server::get_fetchable(
86 row.try_get(compact_str::format_compact!("{prefix}server_uuid").as_str())?,
87 ),
88 database_host: super::database_host::DatabaseHost::map(Some("database_host_"), row)?,
89 name: row.try_get(compact_str::format_compact!("{prefix}name").as_str())?,
90 locked: row.try_get(compact_str::format_compact!("{prefix}locked").as_str())?,
91 username: row.try_get(compact_str::format_compact!("{prefix}username").as_str())?,
92 password: row.try_get(compact_str::format_compact!("{prefix}password").as_str())?,
93 created: row.try_get(compact_str::format_compact!("{prefix}created").as_str())?,
94 })
95 }
96}
97
98impl ServerDatabase {
99 pub async fn by_uuid(
100 database: &crate::database::Database,
101 uuid: uuid::Uuid,
102 ) -> Result<Option<Self>, crate::database::DatabaseError> {
103 let row = sqlx::query(&format!(
104 r#"
105 SELECT {}
106 FROM server_databases
107 JOIN database_hosts ON database_hosts.uuid = server_databases.database_host_uuid
108 WHERE server_databases.uuid = $1
109 "#,
110 Self::columns_sql(None)
111 ))
112 .bind(uuid)
113 .fetch_optional(database.read())
114 .await?;
115
116 row.try_map(|row| Self::map(None, &row))
117 }
118
119 pub async fn by_server_uuid_uuid(
120 database: &crate::database::Database,
121 server_uuid: uuid::Uuid,
122 uuid: uuid::Uuid,
123 ) -> Result<Option<Self>, crate::database::DatabaseError> {
124 let row = sqlx::query(&format!(
125 r#"
126 SELECT {}
127 FROM server_databases
128 JOIN database_hosts ON database_hosts.uuid = server_databases.database_host_uuid
129 WHERE server_databases.server_uuid = $1 AND server_databases.uuid = $2
130 "#,
131 Self::columns_sql(None)
132 ))
133 .bind(server_uuid)
134 .bind(uuid)
135 .fetch_optional(database.read())
136 .await?;
137
138 row.try_map(|row| Self::map(None, &row))
139 }
140
141 pub async fn by_database_host_uuid_with_pagination(
142 database: &crate::database::Database,
143 database_host_uuid: uuid::Uuid,
144 page: i64,
145 per_page: i64,
146 search: Option<&str>,
147 ) -> Result<super::Pagination<Self>, crate::database::DatabaseError> {
148 let offset = (page - 1) * per_page;
149
150 let rows = sqlx::query(&format!(
151 r#"
152 SELECT {}, COUNT(*) OVER() AS total_count
153 FROM server_databases
154 JOIN database_hosts ON database_hosts.uuid = server_databases.database_host_uuid
155 WHERE server_databases.database_host_uuid = $1 AND ($2 IS NULL OR server_databases.name ILIKE '%' || $2 || '%')
156 ORDER BY server_databases.created
157 LIMIT $3 OFFSET $4
158 "#,
159 Self::columns_sql(None)
160 ))
161 .bind(database_host_uuid)
162 .bind(search)
163 .bind(per_page)
164 .bind(offset)
165 .fetch_all(database.read())
166 .await?;
167
168 Ok(super::Pagination {
169 total: rows
170 .first()
171 .map_or(Ok(0), |row| row.try_get("total_count"))?,
172 per_page,
173 page,
174 data: rows
175 .into_iter()
176 .map(|row| Self::map(None, &row))
177 .try_collect_vec()?,
178 })
179 }
180
181 pub async fn by_server_uuid_with_pagination(
182 database: &crate::database::Database,
183 server_uuid: uuid::Uuid,
184 page: i64,
185 per_page: i64,
186 search: Option<&str>,
187 ) -> Result<super::Pagination<Self>, crate::database::DatabaseError> {
188 let offset = (page - 1) * per_page;
189
190 let rows = sqlx::query(&format!(
191 r#"
192 SELECT {}, COUNT(*) OVER() AS total_count
193 FROM server_databases
194 JOIN database_hosts ON database_hosts.uuid = server_databases.database_host_uuid
195 WHERE server_databases.server_uuid = $1 AND ($2 IS NULL OR server_databases.name ILIKE '%' || $2 || '%')
196 ORDER BY server_databases.created
197 LIMIT $3 OFFSET $4
198 "#,
199 Self::columns_sql(None)
200 ))
201 .bind(server_uuid)
202 .bind(search)
203 .bind(per_page)
204 .bind(offset)
205 .fetch_all(database.read())
206 .await?;
207
208 Ok(super::Pagination {
209 total: rows
210 .first()
211 .map_or(Ok(0), |row| row.try_get("total_count"))?,
212 per_page,
213 page,
214 data: rows
215 .into_iter()
216 .map(|row| Self::map(None, &row))
217 .try_collect_vec()?,
218 })
219 }
220
221 pub async fn all_by_server_uuid(
222 database: &crate::database::Database,
223 server_uuid: uuid::Uuid,
224 ) -> Result<Vec<Self>, crate::database::DatabaseError> {
225 let rows = sqlx::query(&format!(
226 r#"
227 SELECT {}
228 FROM server_databases
229 JOIN database_hosts ON database_hosts.uuid = server_databases.database_host_uuid
230 WHERE server_databases.server_uuid = $1
231 "#,
232 Self::columns_sql(None)
233 ))
234 .bind(server_uuid)
235 .fetch_all(database.read())
236 .await?;
237
238 rows.into_iter()
239 .map(|row| Self::map(None, &row))
240 .try_collect_vec()
241 }
242
243 pub async fn count_by_server_uuid(
244 database: &crate::database::Database,
245 server_uuid: uuid::Uuid,
246 ) -> i64 {
247 sqlx::query_scalar(
248 r#"
249 SELECT COUNT(*)
250 FROM server_databases
251 WHERE server_databases.server_uuid = $1
252 "#,
253 )
254 .bind(server_uuid)
255 .fetch_one(database.read())
256 .await
257 .unwrap_or(0)
258 }
259
260 pub async fn count_by_database_host_uuid(
261 database: &crate::database::Database,
262 database_host_uuid: uuid::Uuid,
263 ) -> i64 {
264 sqlx::query_scalar(
265 r#"
266 SELECT COUNT(*)
267 FROM server_databases
268 WHERE server_databases.database_host_uuid = $1
269 "#,
270 )
271 .bind(database_host_uuid)
272 .fetch_one(database.read())
273 .await
274 .unwrap_or(0)
275 }
276
277 pub async fn rotate_password(
278 &self,
279 database: &crate::database::Database,
280 ) -> Result<String, anyhow::Error> {
281 let new_password = rand::distr::Alphanumeric.sample_string(&mut rand::rng(), 24);
282
283 match self.database_host.get_connection(database).await? {
284 crate::models::database_host::DatabasePool::Mysql(pool) => {
285 sqlx::query(&format!(
286 "ALTER USER '{}'@'%' IDENTIFIED BY '{}'",
287 self.username, new_password
288 ))
289 .execute(pool.as_ref())
290 .await?;
291 }
292 crate::models::database_host::DatabasePool::Postgres(pool) => {
293 sqlx::query(&format!(
294 "ALTER USER \"{}\" WITH PASSWORD '{}'",
295 self.username, new_password
296 ))
297 .execute(pool.as_ref())
298 .await?;
299 }
300 }
301
302 sqlx::query(
303 r#"
304 UPDATE server_databases
305 SET password = $1
306 WHERE server_databases.uuid = $2
307 "#,
308 )
309 .bind(database.encrypt(new_password.clone()).await?)
310 .bind(self.uuid)
311 .execute(database.write())
312 .await?;
313
314 Ok(new_password)
315 }
316
317 pub async fn get_size(
318 &self,
319 database: &crate::database::Database,
320 ) -> Result<i64, crate::database::DatabaseError> {
321 match self.database_host.get_connection(database).await? {
322 crate::models::database_host::DatabasePool::Mysql(pool) => {
323 let row = sqlx::query(&format!(
324 "SELECT CAST(SUM(data_length + index_length) AS INTEGER) FROM information_schema.tables WHERE table_schema = '{}'",
325 self.name
326 ))
327 .fetch_one(pool.as_ref())
328 .await?;
329
330 Ok(row.get::<Option<i64>, _>(0).unwrap_or(0))
331 }
332 crate::models::database_host::DatabasePool::Postgres(pool) => {
333 let row = sqlx::query(&format!("SELECT pg_database_size('{}')", self.name))
334 .fetch_one(pool.as_ref())
335 .await?;
336
337 Ok(row.get::<Option<i64>, _>(0).unwrap_or(0))
338 }
339 }
340 }
341
342 #[inline]
343 pub async fn into_admin_api_object(
344 self,
345 database: &crate::database::Database,
346 storage_url_retriever: &StorageUrlRetriever<'_>,
347 ) -> Result<AdminApiServerDatabase, anyhow::Error> {
348 Ok(AdminApiServerDatabase {
349 uuid: self.uuid,
350 server: self
351 .server
352 .fetch_cached(database)
353 .await?
354 .into_admin_api_object(database, storage_url_retriever)
355 .await?,
356 r#type: self.database_host.r#type,
357 host: self
358 .database_host
359 .public_host
360 .unwrap_or(self.database_host.host),
361 port: self
362 .database_host
363 .public_port
364 .unwrap_or(self.database_host.port),
365 name: self.name,
366 is_locked: self.locked,
367 username: self.username,
368 password: database.decrypt(self.password).await?,
369 created: self.created.and_utc(),
370 })
371 }
372
373 #[inline]
374 pub async fn into_api_object(
375 self,
376 database: &crate::database::Database,
377 show_password: bool,
378 ) -> Result<ApiServerDatabase, anyhow::Error> {
379 let mut username = self.username;
380 let space_idx = username.find(' ');
381
382 if let Some(space_idx) = space_idx {
383 username.truncate(space_idx);
384 }
385
386 Ok(ApiServerDatabase {
387 uuid: self.uuid,
388 r#type: self.database_host.r#type,
389 host: self
390 .database_host
391 .public_host
392 .unwrap_or(self.database_host.host),
393 port: self
394 .database_host
395 .public_port
396 .unwrap_or(self.database_host.port),
397 name: self.name,
398 is_locked: self.locked,
399 username,
400 password: if show_password {
401 Some(database.decrypt(self.password).await?)
402 } else {
403 None
404 },
405 created: self.created.and_utc(),
406 })
407 }
408}
409
410#[derive(Validate)]
411pub struct CreateServerDatabaseOptions<'a> {
412 #[garde(skip)]
413 pub server: &'a super::server::Server,
414 #[garde(skip)]
415 pub database_host: &'a super::database_host::DatabaseHost,
416
417 #[garde(length(chars, min = 3, max = 31), pattern("^[a-zA-Z0-9_]+$"))]
418 pub name: compact_str::CompactString,
419}
420
421#[async_trait::async_trait]
422impl CreatableModel for ServerDatabase {
423 type CreateOptions<'a> = CreateServerDatabaseOptions<'a>;
424 type CreateResult = Self;
425
426 fn get_create_handlers() -> &'static LazyLock<CreateListenerList<Self>> {
427 static CREATE_LISTENERS: LazyLock<CreateListenerList<ServerDatabase>> =
428 LazyLock::new(|| Arc::new(ModelHandlerList::default()));
429
430 &CREATE_LISTENERS
431 }
432
433 async fn create(
434 state: &crate::State,
435 mut options: Self::CreateOptions<'_>,
436 ) -> Result<Self, crate::database::DatabaseError> {
437 options.validate()?;
438
439 let server_id = format!("{:08x}", options.server.uuid_short);
440 let name = format!("s{}_{}", server_id, options.name);
441 let username = format!(
442 "u{}_{}",
443 server_id,
444 rand::distr::Alphanumeric.sample_string(&mut rand::rng(), 10)
445 );
446 let password = rand::distr::Alphanumeric.sample_string(&mut rand::rng(), 24);
447
448 let transaction: DatabaseTransaction = match options
449 .database_host
450 .get_connection(&state.database)
451 .await?
452 {
453 crate::models::database_host::DatabasePool::Mysql(pool) => {
454 let mut transaction = pool.begin().await?;
455
456 sqlx::query(&format!(
457 "CREATE USER IF NOT EXISTS '{username}'@'%' IDENTIFIED BY '{password}'"
458 ))
459 .execute(&mut *transaction)
460 .await?;
461 sqlx::query(&format!("CREATE DATABASE IF NOT EXISTS `{name}` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"))
462 .execute(&mut *transaction)
463 .await?;
464 sqlx::query(&format!(
465 "GRANT ALL PRIVILEGES ON `{name}`.* TO '{username}'@'%' WITH GRANT OPTION"
466 ))
467 .execute(&mut *transaction)
468 .await?;
469
470 DatabaseTransaction::Mysql(transaction)
471 }
472 crate::models::database_host::DatabasePool::Postgres(pool) => {
473 let transaction = pool.begin().await?;
474
475 sqlx::query(&format!(
476 "CREATE USER \"{username}\" WITH PASSWORD '{password}'"
477 ))
478 .execute(pool.as_ref())
479 .await?;
480 sqlx::query(&format!(
481 "CREATE DATABASE \"{name}\" WITH OWNER \"{username}\" ENCODING 'UTF8'"
482 ))
483 .execute(pool.as_ref())
484 .await?;
485
486 DatabaseTransaction::Postgres(transaction, pool)
487 }
488 };
489
490 let mut panel_transaction = state.database.write().begin().await?;
491
492 let mut query_builder = InsertQueryBuilder::new("server_databases");
493
494 Self::run_create_handlers(
495 &mut options,
496 &mut query_builder,
497 state,
498 &mut panel_transaction,
499 )
500 .await?;
501
502 query_builder
503 .set("server_uuid", options.server.uuid)
504 .set("database_host_uuid", options.database_host.uuid)
505 .set("name", &name)
506 .set("username", &username)
507 .set("password", state.database.encrypt(password.clone()).await?);
508
509 let row = match query_builder
510 .returning("uuid")
511 .fetch_one(&mut *panel_transaction)
512 .await
513 {
514 Ok(row) => row,
515 Err(err) => {
516 match transaction {
517 DatabaseTransaction::Mysql(transaction) => {
518 transaction.rollback().await?;
519 }
520 DatabaseTransaction::Postgres(transaction, pool) => {
521 transaction.rollback().await?;
522
523 let drop_database = format!("DROP DATABASE IF EXISTS \"{name}\"");
524 let drop_user = format!("DROP USER IF EXISTS \"{username}\"");
525
526 let (_, _) = tokio::join!(
527 sqlx::query(&drop_database).execute(pool.as_ref()),
528 sqlx::query(&drop_user).execute(pool.as_ref())
529 );
530 }
531 }
532
533 return Err(err.into());
534 }
535 };
536
537 let uuid: uuid::Uuid = row.get("uuid");
538
539 match match transaction {
540 DatabaseTransaction::Mysql(transaction) => transaction.commit().await,
541 DatabaseTransaction::Postgres(transaction, _) => transaction.commit().await,
542 } {
543 Ok(_) => {}
544 Err(err) => {
545 sqlx::query(
546 r#"
547 DELETE FROM server_databases
548 WHERE server_databases.uuid = $1
549 "#,
550 )
551 .bind(uuid)
552 .execute(&mut *panel_transaction)
553 .await
554 .ok();
555
556 return Err(err.into());
557 }
558 }
559
560 panel_transaction.commit().await?;
561
562 Self::by_uuid(&state.database, uuid)
563 .await?
564 .ok_or(sqlx::Error::RowNotFound.into())
565 }
566}
567
568#[derive(ToSchema, Serialize, Deserialize, Validate, Default)]
569pub struct UpdateServerDatabaseOptions {
570 #[garde(skip)]
571 pub locked: Option<bool>,
572}
573
574#[async_trait::async_trait]
575impl UpdatableModel for ServerDatabase {
576 type UpdateOptions = UpdateServerDatabaseOptions;
577
578 fn get_update_handlers() -> &'static LazyLock<UpdateListenerList<Self>> {
579 static UPDATE_LISTENERS: LazyLock<UpdateListenerList<ServerDatabase>> =
580 LazyLock::new(|| Arc::new(ModelHandlerList::default()));
581
582 &UPDATE_LISTENERS
583 }
584
585 async fn update(
586 &mut self,
587 state: &crate::State,
588 mut options: Self::UpdateOptions,
589 ) -> Result<(), crate::database::DatabaseError> {
590 options.validate()?;
591
592 let mut transaction = state.database.write().begin().await?;
593
594 let mut query_builder = UpdateQueryBuilder::new("server_databases");
595
596 Self::run_update_handlers(
597 self,
598 &mut options,
599 &mut query_builder,
600 state,
601 &mut transaction,
602 )
603 .await?;
604
605 query_builder
606 .set("locked", options.locked)
607 .where_eq("uuid", self.uuid);
608
609 query_builder.execute(&mut *transaction).await?;
610
611 if let Some(locked) = options.locked {
612 self.locked = locked;
613 }
614
615 transaction.commit().await?;
616
617 Ok(())
618 }
619}
620
621#[derive(Default)]
622pub struct DeleteServerDatabaseOptions {
623 pub force: bool,
624}
625
626#[async_trait::async_trait]
627impl DeletableModel for ServerDatabase {
628 type DeleteOptions = DeleteServerDatabaseOptions;
629
630 fn get_delete_handlers() -> &'static LazyLock<DeleteListenerList<Self>> {
631 static DELETE_LISTENERS: LazyLock<DeleteListenerList<ServerDatabase>> =
632 LazyLock::new(|| Arc::new(ModelHandlerList::default()));
633
634 &DELETE_LISTENERS
635 }
636
637 async fn delete(
638 &self,
639 state: &crate::State,
640 options: Self::DeleteOptions,
641 ) -> Result<(), anyhow::Error> {
642 let mut transaction = state.database.write().begin().await?;
643
644 if self.name.contains(|c| ['"', '\'', '`'].contains(&c))
645 || self.username.contains(|c| ['"', '\'', '`'].contains(&c))
646 {
647 return Err(anyhow::anyhow!(
648 "unable to delete database with escape characters"
649 ));
650 }
651
652 self.run_delete_handlers(&options, state, &mut transaction)
653 .await?;
654
655 let connection = self.database_host.get_connection(&state.database).await?;
656 let database_name = self.name.clone();
657 let database_username = self.username.trim_end().to_string();
658 let database_uuid = self.uuid;
659
660 tokio::spawn(async move {
661 let run_delete = async || {
662 match connection {
663 crate::models::database_host::DatabasePool::Mysql(pool) => {
664 sqlx::query(&format!("DROP DATABASE IF EXISTS `{}`", database_name))
665 .execute(pool.as_ref())
666 .await?;
667 sqlx::query(&format!("DROP USER IF EXISTS '{}'@'%'", database_username))
668 .execute(pool.as_ref())
669 .await?;
670 }
671 crate::models::database_host::DatabasePool::Postgres(pool) => {
672 sqlx::query(&format!("DROP DATABASE IF EXISTS \"{}\"", database_name))
673 .execute(pool.as_ref())
674 .await?;
675 sqlx::query(&format!("DROP USER IF EXISTS \"{}\"", database_username))
676 .execute(pool.as_ref())
677 .await?;
678 }
679 }
680
681 Ok::<_, anyhow::Error>(())
682 };
683
684 if let Err(err) = run_delete().await
685 && !options.force
686 {
687 if err
688 .downcast_ref::<sqlx::Error>()
689 .and_then(|e| e.as_database_error())
690 .is_some_and(|e| e.message().contains("is being accessed"))
691 {
692 return Err(crate::response::DisplayError::new(
693 "this database is being accessed, unable to delete.",
694 )
695 .into());
696 }
697
698 return Err(err);
699 }
700
701 sqlx::query(
702 r#"
703 DELETE FROM server_databases
704 WHERE server_databases.uuid = $1
705 "#,
706 )
707 .bind(database_uuid)
708 .execute(&mut *transaction)
709 .await?;
710
711 transaction.commit().await?;
712
713 Ok(())
714 })
715 .await?
716 }
717}
718
719#[derive(ToSchema, Serialize)]
720#[schema(title = "AdminServerDatabase")]
721pub struct AdminApiServerDatabase {
722 pub uuid: uuid::Uuid,
723 pub server: super::server::AdminApiServer,
724
725 pub r#type: DatabaseType,
726 pub host: compact_str::CompactString,
727 pub port: i32,
728
729 pub name: compact_str::CompactString,
730 pub is_locked: bool,
731
732 pub username: compact_str::CompactString,
733 pub password: compact_str::CompactString,
734
735 pub created: chrono::DateTime<chrono::Utc>,
736}
737
738#[derive(ToSchema, Serialize)]
739#[schema(title = "ServerDatabase")]
740pub struct ApiServerDatabase {
741 pub uuid: uuid::Uuid,
742
743 pub r#type: DatabaseType,
744 pub host: compact_str::CompactString,
745 pub port: i32,
746
747 pub name: compact_str::CompactString,
748 pub is_locked: bool,
749
750 pub username: compact_str::CompactString,
751 pub password: Option<compact_str::CompactString>,
752
753 pub created: chrono::DateTime<chrono::Utc>,
754}