1use crate::{
2 models::{
3 InsertQueryBuilder, UpdateQueryBuilder,
4 database_host::{DatabaseTransaction, DatabaseType},
5 },
6 prelude::*,
7};
8use garde::Validate;
9use rand::{RngExt, distr::SampleString};
10use serde::{Deserialize, Serialize};
11use sqlx::{Row, postgres::PgRow};
12use std::{
13 collections::BTreeMap,
14 sync::{Arc, LazyLock},
15};
16use utoipa::ToSchema;
17
18#[derive(Serialize, Deserialize, Clone)]
19pub struct ServerDatabase {
20 pub uuid: uuid::Uuid,
21 pub server: Fetchable<super::server::Server>,
22 pub database_host: super::database_host::DatabaseHost,
23
24 pub name: compact_str::CompactString,
25 pub locked: bool,
26
27 pub username: compact_str::CompactString,
28 pub password: Vec<u8>,
29
30 pub created: chrono::NaiveDateTime,
31
32 extension_data: super::ModelExtensionData,
33}
34
35impl BaseModel for ServerDatabase {
36 const NAME: &'static str = "server_database";
37
38 fn get_extension_list() -> &'static super::ModelExtensionList {
39 static EXTENSIONS: LazyLock<super::ModelExtensionList> =
40 LazyLock::new(|| parking_lot::RwLock::new(Vec::new()));
41
42 &EXTENSIONS
43 }
44
45 fn get_extension_data(&self) -> &super::ModelExtensionData {
46 &self.extension_data
47 }
48
49 #[inline]
50 fn base_columns(prefix: Option<&str>) -> BTreeMap<&'static str, compact_str::CompactString> {
51 let prefix = prefix.unwrap_or_default();
52
53 let mut columns = BTreeMap::from([
54 (
55 "server_databases.uuid",
56 compact_str::format_compact!("{prefix}uuid"),
57 ),
58 (
59 "server_databases.server_uuid",
60 compact_str::format_compact!("{prefix}server_uuid"),
61 ),
62 (
63 "server_databases.name",
64 compact_str::format_compact!("{prefix}name"),
65 ),
66 (
67 "server_databases.locked",
68 compact_str::format_compact!("{prefix}locked"),
69 ),
70 (
71 "server_databases.username",
72 compact_str::format_compact!("{prefix}username"),
73 ),
74 (
75 "server_databases.password",
76 compact_str::format_compact!("{prefix}password"),
77 ),
78 (
79 "server_databases.created",
80 compact_str::format_compact!("{prefix}created"),
81 ),
82 ]);
83
84 columns.extend(super::database_host::DatabaseHost::base_columns(Some(
85 "database_host_",
86 )));
87
88 columns
89 }
90
91 #[inline]
92 fn map(prefix: Option<&str>, row: &PgRow) -> Result<Self, crate::database::DatabaseError> {
93 let prefix = prefix.unwrap_or_default();
94
95 Ok(Self {
96 uuid: row.try_get(compact_str::format_compact!("{prefix}uuid").as_str())?,
97 server: super::server::Server::get_fetchable(
98 row.try_get(compact_str::format_compact!("{prefix}server_uuid").as_str())?,
99 ),
100 database_host: super::database_host::DatabaseHost::map(Some("database_host_"), row)?,
101 name: row.try_get(compact_str::format_compact!("{prefix}name").as_str())?,
102 locked: row.try_get(compact_str::format_compact!("{prefix}locked").as_str())?,
103 username: row.try_get(compact_str::format_compact!("{prefix}username").as_str())?,
104 password: row.try_get(compact_str::format_compact!("{prefix}password").as_str())?,
105 created: row.try_get(compact_str::format_compact!("{prefix}created").as_str())?,
106 extension_data: Self::map_extensions(prefix, row)?,
107 })
108 }
109}
110
111impl ServerDatabase {
112 pub async fn by_server_uuid_uuid(
113 database: &crate::database::Database,
114 server_uuid: uuid::Uuid,
115 uuid: uuid::Uuid,
116 ) -> Result<Option<Self>, crate::database::DatabaseError> {
117 let row = sqlx::query(sqlx::AssertSqlSafe(format!(
118 r#"
119 SELECT {}
120 FROM server_databases
121 JOIN database_hosts ON database_hosts.uuid = server_databases.database_host_uuid
122 WHERE server_databases.server_uuid = $1 AND server_databases.uuid = $2
123 "#,
124 Self::columns_sql(None)
125 )))
126 .bind(server_uuid)
127 .bind(uuid)
128 .fetch_optional(database.read())
129 .await?;
130
131 row.try_map(|row| Self::map(None, &row))
132 }
133
134 pub async fn by_database_host_uuid_with_pagination(
135 database: &crate::database::Database,
136 database_host_uuid: uuid::Uuid,
137 page: i64,
138 per_page: i64,
139 search: Option<&str>,
140 ) -> Result<super::Pagination<Self>, crate::database::DatabaseError> {
141 let offset = (page - 1) * per_page;
142
143 let rows = sqlx::query(sqlx::AssertSqlSafe(format!(
144 r#"
145 SELECT {}, COUNT(*) OVER() AS total_count
146 FROM server_databases
147 JOIN database_hosts ON database_hosts.uuid = server_databases.database_host_uuid
148 WHERE server_databases.database_host_uuid = $1 AND ($2 IS NULL OR server_databases.name ILIKE '%' || $2 || '%')
149 ORDER BY server_databases.created
150 LIMIT $3 OFFSET $4
151 "#,
152 Self::columns_sql(None)
153 )))
154 .bind(database_host_uuid)
155 .bind(search)
156 .bind(per_page)
157 .bind(offset)
158 .fetch_all(database.read())
159 .await?;
160
161 Ok(super::Pagination {
162 total: rows
163 .first()
164 .map_or(Ok(0), |row| row.try_get("total_count"))?,
165 per_page,
166 page,
167 data: rows
168 .into_iter()
169 .map(|row| Self::map(None, &row))
170 .try_collect_vec()?,
171 })
172 }
173
174 pub async fn by_server_uuid_with_pagination(
175 database: &crate::database::Database,
176 server_uuid: uuid::Uuid,
177 page: i64,
178 per_page: i64,
179 search: Option<&str>,
180 ) -> Result<super::Pagination<Self>, crate::database::DatabaseError> {
181 let offset = (page - 1) * per_page;
182
183 let rows = sqlx::query(sqlx::AssertSqlSafe(format!(
184 r#"
185 SELECT {}, COUNT(*) OVER() AS total_count
186 FROM server_databases
187 JOIN database_hosts ON database_hosts.uuid = server_databases.database_host_uuid
188 WHERE server_databases.server_uuid = $1 AND ($2 IS NULL OR server_databases.name ILIKE '%' || $2 || '%')
189 ORDER BY server_databases.created
190 LIMIT $3 OFFSET $4
191 "#,
192 Self::columns_sql(None)
193 )))
194 .bind(server_uuid)
195 .bind(search)
196 .bind(per_page)
197 .bind(offset)
198 .fetch_all(database.read())
199 .await?;
200
201 Ok(super::Pagination {
202 total: rows
203 .first()
204 .map_or(Ok(0), |row| row.try_get("total_count"))?,
205 per_page,
206 page,
207 data: rows
208 .into_iter()
209 .map(|row| Self::map(None, &row))
210 .try_collect_vec()?,
211 })
212 }
213
214 pub async fn all_by_server_uuid(
215 database: &crate::database::Database,
216 server_uuid: uuid::Uuid,
217 ) -> Result<Vec<Self>, crate::database::DatabaseError> {
218 let rows = sqlx::query(sqlx::AssertSqlSafe(format!(
219 r#"
220 SELECT {}
221 FROM server_databases
222 JOIN database_hosts ON database_hosts.uuid = server_databases.database_host_uuid
223 WHERE server_databases.server_uuid = $1
224 "#,
225 Self::columns_sql(None)
226 )))
227 .bind(server_uuid)
228 .fetch_all(database.read())
229 .await?;
230
231 rows.into_iter()
232 .map(|row| Self::map(None, &row))
233 .try_collect_vec()
234 }
235
236 pub async fn count_by_server_uuid(
237 database: &crate::database::Database,
238 server_uuid: uuid::Uuid,
239 ) -> Result<i64, sqlx::Error> {
240 sqlx::query_scalar(
241 r#"
242 SELECT COUNT(*)
243 FROM server_databases
244 WHERE server_databases.server_uuid = $1
245 "#,
246 )
247 .bind(server_uuid)
248 .fetch_one(database.read())
249 .await
250 }
251
252 pub async fn count_by_database_host_uuid(
253 database: &crate::database::Database,
254 database_host_uuid: uuid::Uuid,
255 ) -> Result<i64, sqlx::Error> {
256 sqlx::query_scalar(
257 r#"
258 SELECT COUNT(*)
259 FROM server_databases
260 WHERE server_databases.database_host_uuid = $1
261 "#,
262 )
263 .bind(database_host_uuid)
264 .fetch_one(database.read())
265 .await
266 }
267
268 #[inline]
269 pub fn generate_database_name(server_uuid: uuid::Uuid, name: &str) -> String {
270 let server_id = format!("{:08x}", server_uuid.as_u128() >> 96);
271 format!("s{}_{}", server_id, name)
272 }
273
274 #[inline]
275 pub fn generate_username(server_uuid: uuid::Uuid) -> String {
276 let server_id = format!("{:08x}", server_uuid.as_u128() >> 96);
277 format!(
278 "u{}_{}",
279 server_id,
280 rand::distr::Alphanumeric.sample_string(&mut rand::rng(), 10)
281 )
282 }
283
284 #[inline]
285 pub fn generate_password() -> String {
286 const PASSWORD_SPECIAL_CHARS: &[u8] = b"!@#$%^&*()<>-_";
287
288 let mut rng = rand::rng();
289 let mut password = rand::distr::Alphanumeric
290 .sample_string(&mut rng, 24)
291 .into_bytes();
292
293 for _ in 0..rng.random_range(1..=5) {
294 let pos = rng.random_range(0..password.len());
295 password[pos] =
296 PASSWORD_SPECIAL_CHARS[rng.random_range(0..PASSWORD_SPECIAL_CHARS.len())];
297 }
298
299 String::from_utf8(password).unwrap()
300 }
301
302 pub async fn rotate_password(
303 &mut self,
304 database: &crate::database::Database,
305 ) -> Result<String, anyhow::Error> {
306 let new_password = Self::generate_password();
307
308 match self.database_host.get_connection(database).await? {
309 crate::models::database_host::DatabasePool::Mysql(pool) => {
310 sqlx::query(sqlx::AssertSqlSafe(format!(
311 "ALTER USER '{}'@'%' IDENTIFIED BY '{}'",
312 self.username, new_password
313 )))
314 .execute(&pool)
315 .await?;
316 }
317 crate::models::database_host::DatabasePool::Postgres(pool) => {
318 sqlx::query(sqlx::AssertSqlSafe(format!(
319 "ALTER USER \"{}\" WITH PASSWORD '{}'",
320 self.username, new_password
321 )))
322 .execute(&pool)
323 .await?;
324 }
325 crate::models::database_host::DatabasePool::Mongodb(client) => {
326 let cmd = mongodb::bson::doc! {
327 "updateUser": self.username.to_string(),
328 "pwd": &new_password
329 };
330 client.database(&self.name).run_command(cmd).await?;
331 }
332 }
333
334 sqlx::query(
335 r#"
336 UPDATE server_databases
337 SET password = $1
338 WHERE server_databases.uuid = $2
339 "#,
340 )
341 .bind(database.encrypt(new_password.clone()).await?)
342 .bind(self.uuid)
343 .execute(database.write())
344 .await?;
345
346 Ok(new_password)
347 }
348
349 pub async fn get_size(
350 &mut self,
351 database: &crate::database::Database,
352 ) -> Result<i64, crate::database::DatabaseError> {
353 match self.database_host.get_connection(database).await? {
354 crate::models::database_host::DatabasePool::Mysql(pool) => {
355 let row = sqlx::query(
356 "SELECT CAST(SUM(data_length + index_length) AS INTEGER) FROM information_schema.tables WHERE table_schema = ?",
357 )
358 .bind(&self.name)
359 .fetch_one(&pool)
360 .await?;
361
362 Ok(row.get::<Option<i64>, _>(0).unwrap_or(0))
363 }
364 crate::models::database_host::DatabasePool::Postgres(pool) => {
365 let row = sqlx::query("SELECT pg_database_size($1)")
366 .bind(&self.name)
367 .fetch_one(&pool)
368 .await?;
369
370 Ok(row.get::<Option<i64>, _>(0).unwrap_or(0))
371 }
372 crate::models::database_host::DatabasePool::Mongodb(client) => {
373 let cmd = mongodb::bson::doc! { "dbStats": 1, "scale": 1 };
374 let stats = client.database(&self.name).run_command(cmd).await?;
375
376 let size = match stats.get("dataSize") {
377 Some(mongodb::bson::Bson::Int32(i)) => *i as i64,
378 Some(mongodb::bson::Bson::Int64(i)) => *i,
379 Some(mongodb::bson::Bson::Double(f)) => *f as i64,
380 _ => 0,
381 };
382
383 Ok(size)
384 }
385 }
386 }
387
388 pub async fn recreate(
389 &mut self,
390 database: &crate::database::Database,
391 ) -> Result<(), anyhow::Error> {
392 let mut run_recreate = async || {
393 match self.database_host.get_connection(database).await? {
394 crate::models::database_host::DatabasePool::Mysql(pool) => {
395 sqlx::query(sqlx::AssertSqlSafe(format!(
396 "DROP DATABASE IF EXISTS `{}`",
397 self.name
398 )))
399 .execute(&pool)
400 .await?;
401 sqlx::query(sqlx::AssertSqlSafe(format!("CREATE DATABASE `{}` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;", self.name)))
402 .execute(&pool)
403 .await?;
404 }
405 crate::models::database_host::DatabasePool::Postgres(pool) => {
406 sqlx::query(sqlx::AssertSqlSafe(format!(
407 "DROP DATABASE IF EXISTS \"{}\"",
408 self.name
409 )))
410 .execute(&pool)
411 .await?;
412 sqlx::query(sqlx::AssertSqlSafe(format!(
413 "CREATE DATABASE \"{}\" WITH OWNER \"{}\" ENCODING 'UTF8'",
414 self.name, self.username
415 )))
416 .execute(&pool)
417 .await?;
418 }
419 crate::models::database_host::DatabasePool::Mongodb(client) => {
420 let db = client.database(&self.name);
421
422 let drop_user_cmd =
423 mongodb::bson::doc! { "dropUser": self.username.to_string() };
424 let _ = db.run_command(drop_user_cmd).await;
425
426 db.drop().await?;
427
428 let password = database.decrypt(self.password.clone()).await?;
429 let cmd = mongodb::bson::doc! {
430 "createUser": self.username.to_string(),
431 "pwd": password.into_string(),
432 "roles": [
433 { "role": "readWrite", "db": self.name.to_string() }
434 ]
435 };
436 db.run_command(cmd).await?;
437 }
438 }
439
440 Ok::<(), anyhow::Error>(())
441 };
442
443 if let Err(err) = run_recreate().await {
444 if err
445 .downcast_ref::<sqlx::Error>()
446 .and_then(|e| e.as_database_error())
447 .is_some_and(|e| e.message().contains("is being accessed"))
448 {
449 return Err(crate::response::DisplayError::new(
450 "this database is being accessed, unable to recreate.",
451 )
452 .into());
453 }
454
455 return Err(err);
456 }
457
458 Ok(())
459 }
460}
461
462#[async_trait::async_trait]
463impl IntoAdminApiObject for ServerDatabase {
464 type AdminApiObject = AdminApiServerDatabase;
465 type ExtraArgs<'a> = &'a crate::storage::StorageUrlRetriever<'a>;
466
467 async fn into_admin_api_object<'a>(
468 self,
469 state: &crate::State,
470 storage_url_retriever: Self::ExtraArgs<'a>,
471 ) -> Result<Self::AdminApiObject, crate::database::DatabaseError> {
472 let api_object = AdminApiServerDatabase::init_hooks(&self, state).await?;
473
474 let details = self
475 .database_host
476 .credentials
477 .parse_connection_details(&state.database)
478 .await?;
479
480 let api_object = finish_extendible!(
481 AdminApiServerDatabase {
482 uuid: self.uuid,
483 server: self
484 .server
485 .fetch_cached(&state.database)
486 .await?
487 .into_admin_api_object(state, storage_url_retriever)
488 .await?,
489 r#type: self.database_host.r#type,
490 host: self.database_host.public_host.unwrap_or(details.host),
491 port: self
492 .database_host
493 .public_port
494 .unwrap_or(details.port as i32),
495 name: self.name,
496 is_locked: self.locked,
497 username: self.username,
498 password: state.database.decrypt(self.password).await?,
499 created: self.created.and_utc(),
500 },
501 api_object,
502 state
503 )?;
504
505 Ok(api_object)
506 }
507}
508
509#[async_trait::async_trait]
510impl IntoApiObject for ServerDatabase {
511 type ApiObject = ApiServerDatabase;
512 type ExtraArgs<'a> = bool;
513
514 async fn into_api_object<'a>(
515 self,
516 state: &crate::State,
517 show_password: Self::ExtraArgs<'a>,
518 ) -> Result<Self::ApiObject, crate::database::DatabaseError> {
519 let api_object = ApiServerDatabase::init_hooks(&self, state).await?;
520
521 let mut username = self.username;
522 let space_idx = username.find(' ');
523
524 if let Some(space_idx) = space_idx {
525 username.truncate(space_idx);
526 }
527
528 let details = self
529 .database_host
530 .credentials
531 .parse_connection_details(&state.database)
532 .await?;
533
534 let api_object = finish_extendible!(
535 ApiServerDatabase {
536 uuid: self.uuid,
537 r#type: self.database_host.r#type,
538 host: self.database_host.public_host.unwrap_or(details.host),
539 port: self
540 .database_host
541 .public_port
542 .unwrap_or(details.port as i32),
543 name: self.name,
544 is_locked: self.locked,
545 username,
546 password: if show_password {
547 Some(state.database.decrypt(self.password).await?)
548 } else {
549 None
550 },
551 created: self.created.and_utc(),
552 },
553 api_object,
554 state
555 )?;
556
557 Ok(api_object)
558 }
559}
560
561#[async_trait::async_trait]
562impl ByUuid for ServerDatabase {
563 async fn by_uuid(
564 database: &crate::database::Database,
565 uuid: uuid::Uuid,
566 ) -> Result<Self, crate::database::DatabaseError> {
567 let row = sqlx::query(sqlx::AssertSqlSafe(format!(
568 r#"
569 SELECT {}
570 FROM server_databases
571 JOIN database_hosts ON database_hosts.uuid = server_databases.database_host_uuid
572 WHERE server_databases.uuid = $1
573 "#,
574 Self::columns_sql(None)
575 )))
576 .bind(uuid)
577 .fetch_one(database.read())
578 .await?;
579
580 Self::map(None, &row)
581 }
582
583 async fn by_uuid_with_transaction(
584 transaction: &mut sqlx::Transaction<'_, sqlx::Postgres>,
585 uuid: uuid::Uuid,
586 ) -> Result<Self, crate::database::DatabaseError> {
587 let row = sqlx::query(sqlx::AssertSqlSafe(format!(
588 r#"
589 SELECT {}
590 FROM server_databases
591 JOIN database_hosts ON database_hosts.uuid = server_databases.database_host_uuid
592 WHERE server_databases.uuid = $1
593 "#,
594 Self::columns_sql(None)
595 )))
596 .bind(uuid)
597 .fetch_one(&mut **transaction)
598 .await?;
599
600 Self::map(None, &row)
601 }
602}
603
604#[derive(Validate)]
605pub struct CreateServerDatabaseOptions<'a> {
606 #[garde(skip)]
607 pub server: &'a super::server::Server,
608 #[garde(skip)]
609 pub database_host: &'a super::database_host::DatabaseHost,
610
611 #[garde(length(chars, min = 3, max = 31), pattern("^[a-zA-Z0-9_]+$"))]
612 pub name: compact_str::CompactString,
613}
614
615#[async_trait::async_trait]
616impl CreatableModel for ServerDatabase {
617 type CreateOptions<'a> = CreateServerDatabaseOptions<'a>;
618 type CreateResult = Self;
619
620 fn get_create_handlers() -> &'static LazyLock<CreateListenerList<Self>> {
621 static CREATE_LISTENERS: LazyLock<CreateListenerList<ServerDatabase>> =
622 LazyLock::new(|| Arc::new(ModelHandlerList::default()));
623
624 &CREATE_LISTENERS
625 }
626
627 async fn create_with_transaction(
628 state: &crate::State,
629 mut options: Self::CreateOptions<'_>,
630 transaction: &mut sqlx::Transaction<'_, sqlx::Postgres>,
631 ) -> Result<Self, crate::database::DatabaseError> {
632 options.validate()?;
633
634 let name = Self::generate_database_name(options.server.uuid, &options.name);
635 let username = Self::generate_username(options.server.uuid);
636 let password = Self::generate_password();
637
638 let db_transaction: DatabaseTransaction = match options
639 .database_host
640 .clone()
641 .get_connection(&state.database)
642 .await?
643 {
644 crate::models::database_host::DatabasePool::Mysql(pool) => {
645 let mut transaction = pool.begin().await?;
646
647 sqlx::query(sqlx::AssertSqlSafe(format!(
648 "CREATE USER IF NOT EXISTS '{username}'@'%' IDENTIFIED BY '{password}'"
649 )))
650 .execute(&mut *transaction)
651 .await?;
652 sqlx::query(sqlx::AssertSqlSafe(format!("CREATE DATABASE IF NOT EXISTS `{name}` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;")))
653 .execute(&mut *transaction)
654 .await?;
655 sqlx::query(sqlx::AssertSqlSafe(format!(
656 "GRANT ALL PRIVILEGES ON `{name}`.* TO '{username}'@'%' WITH GRANT OPTION"
657 )))
658 .execute(&mut *transaction)
659 .await?;
660
661 DatabaseTransaction::Mysql(transaction, pool)
662 }
663 crate::models::database_host::DatabasePool::Postgres(pool) => {
664 let transaction = pool.begin().await?;
665
666 sqlx::query(sqlx::AssertSqlSafe(format!(
667 "CREATE USER \"{username}\" WITH PASSWORD '{password}'"
668 )))
669 .execute(&pool)
670 .await?;
671 sqlx::query(sqlx::AssertSqlSafe(format!(
672 "CREATE DATABASE \"{name}\" WITH OWNER \"{username}\" ENCODING 'UTF8'"
673 )))
674 .execute(&pool)
675 .await?;
676
677 DatabaseTransaction::Postgres(transaction, pool)
678 }
679 crate::models::database_host::DatabasePool::Mongodb(client) => {
680 let db = client.database(&name);
681 let cmd = mongodb::bson::doc! {
682 "createUser": &username,
683 "pwd": &password,
684 "roles": [
685 { "role": "readWrite", "db": &name }
686 ]
687 };
688 db.run_command(cmd).await?;
689
690 DatabaseTransaction::Mongodb(client)
691 }
692 };
693
694 let mut query_builder = InsertQueryBuilder::new("server_databases");
695
696 Self::run_create_handlers(&mut options, &mut query_builder, state, transaction).await?;
697
698 query_builder
699 .set("server_uuid", options.server.uuid)
700 .set("database_host_uuid", options.database_host.uuid)
701 .set("name", &name)
702 .set("username", &username)
703 .set("password", state.database.encrypt(password.clone()).await?);
704
705 let row = match query_builder
706 .returning("uuid")
707 .fetch_one(&mut **transaction)
708 .await
709 {
710 Ok(row) => row,
711 Err(err) => {
712 match db_transaction {
713 DatabaseTransaction::Mysql(db_tx, pool) => {
714 db_tx.rollback().await?;
715
716 let drop_database = format!("DROP DATABASE IF EXISTS `{name}`");
717 let drop_user = format!("DROP USER IF EXISTS '{username}'@'%'");
718
719 let (_, _) = tokio::join!(
720 sqlx::query(sqlx::AssertSqlSafe(drop_database)).execute(&pool),
721 sqlx::query(sqlx::AssertSqlSafe(drop_user)).execute(&pool)
722 );
723 }
724 DatabaseTransaction::Postgres(db_tx, pool) => {
725 db_tx.rollback().await?;
726
727 let drop_database = format!("DROP DATABASE IF EXISTS \"{name}\"");
728 let drop_user = format!("DROP USER IF EXISTS \"{username}\"");
729
730 let (_, _) = tokio::join!(
731 sqlx::query(sqlx::AssertSqlSafe(drop_database)).execute(&pool),
732 sqlx::query(sqlx::AssertSqlSafe(drop_user)).execute(&pool)
733 );
734 }
735 DatabaseTransaction::Mongodb(client) => {
736 let _ = client.database(&name).drop().await;
737 }
738 }
739
740 return Err(err.into());
741 }
742 };
743
744 let uuid: uuid::Uuid = row.try_get("uuid")?;
745
746 match match db_transaction {
747 DatabaseTransaction::Mysql(db_tx, _) => db_tx.commit().await,
748 DatabaseTransaction::Postgres(db_tx, _) => db_tx.commit().await,
749 DatabaseTransaction::Mongodb(_) => Ok(()),
750 } {
751 Ok(_) => {}
752 Err(err) => {
753 sqlx::query(
754 r#"
755 DELETE FROM server_databases
756 WHERE server_databases.uuid = $1
757 "#,
758 )
759 .bind(uuid)
760 .execute(&mut **transaction)
761 .await
762 .ok();
763
764 return Err(err.into());
765 }
766 }
767
768 let mut result = Self::by_uuid_with_transaction(transaction, uuid).await?;
769
770 Self::run_after_create_handlers(&mut result, &options, state, transaction).await?;
771
772 Ok(result)
773 }
774}
775
776#[derive(ToSchema, Serialize, Deserialize, Validate, Default)]
777pub struct UpdateServerDatabaseOptions {
778 #[garde(skip)]
779 pub locked: Option<bool>,
780}
781
782#[async_trait::async_trait]
783impl UpdatableModel for ServerDatabase {
784 type UpdateOptions = UpdateServerDatabaseOptions;
785
786 fn get_update_handlers() -> &'static LazyLock<UpdateHandlerList<Self>> {
787 static UPDATE_LISTENERS: LazyLock<UpdateHandlerList<ServerDatabase>> =
788 LazyLock::new(|| Arc::new(ModelHandlerList::default()));
789
790 &UPDATE_LISTENERS
791 }
792
793 async fn update_with_transaction(
794 &mut self,
795 state: &crate::State,
796 mut options: Self::UpdateOptions,
797 transaction: &mut sqlx::Transaction<'_, sqlx::Postgres>,
798 ) -> Result<(), crate::database::DatabaseError> {
799 options.validate()?;
800
801 let mut query_builder = UpdateQueryBuilder::new("server_databases");
802
803 self.run_update_handlers(&mut options, &mut query_builder, state, transaction)
804 .await?;
805
806 query_builder
807 .set("locked", options.locked)
808 .where_eq("uuid", self.uuid);
809
810 query_builder.execute(&mut **transaction).await?;
811
812 if let Some(locked) = options.locked {
813 self.locked = locked;
814 }
815
816 self.run_after_update_handlers(state, transaction).await?;
817
818 Ok(())
819 }
820}
821
822#[derive(Clone, Default)]
823pub struct DeleteServerDatabaseOptions {
824 pub force: bool,
825}
826
827#[async_trait::async_trait]
828impl DeletableModel for ServerDatabase {
829 type DeleteOptions = DeleteServerDatabaseOptions;
830
831 fn get_delete_handlers() -> &'static LazyLock<DeleteHandlerList<Self>> {
832 static DELETE_LISTENERS: LazyLock<DeleteHandlerList<ServerDatabase>> =
833 LazyLock::new(|| Arc::new(ModelHandlerList::default()));
834
835 &DELETE_LISTENERS
836 }
837
838 async fn delete_with_transaction(
839 &self,
840 state: &crate::State,
841 options: Self::DeleteOptions,
842 transaction: &mut sqlx::Transaction<'_, sqlx::Postgres>,
843 ) -> Result<(), anyhow::Error> {
844 self.run_delete_handlers(&options, state, transaction)
845 .await?;
846
847 sqlx::query(
848 r#"
849 DELETE FROM server_databases
850 WHERE server_databases.uuid = $1
851 "#,
852 )
853 .bind(self.uuid)
854 .execute(&mut **transaction)
855 .await?;
856
857 self.run_after_delete_handlers(&options, state, transaction)
858 .await?;
859
860 Ok(())
861 }
862
863 async fn delete(
864 &self,
865 state: &crate::State,
866 options: Self::DeleteOptions,
867 ) -> Result<(), anyhow::Error> {
868 if self.name.contains(|c| ['"', '\'', '`'].contains(&c))
869 || self.username.contains(|c| ['"', '\'', '`'].contains(&c))
870 {
871 return Err(anyhow::anyhow!(
872 "unable to delete database with escape characters"
873 ));
874 }
875
876 let connection = self
877 .database_host
878 .clone()
879 .get_connection(&state.database)
880 .await?;
881 let database_name = self.name.clone();
882 let database_username = self.username.trim_end().to_string();
883 let self_clone = self.clone();
884 let state_clone = state.clone();
885
886 tokio::spawn(async move {
887 let run_delete = async || {
888 match connection {
889 crate::models::database_host::DatabasePool::Mysql(pool) => {
890 sqlx::query(sqlx::AssertSqlSafe(format!(
891 "DROP DATABASE IF EXISTS `{}`",
892 database_name
893 )))
894 .execute(&pool)
895 .await?;
896 sqlx::query(sqlx::AssertSqlSafe(format!(
897 "DROP USER IF EXISTS '{}'@'%'",
898 database_username
899 )))
900 .execute(&pool)
901 .await?;
902 }
903 crate::models::database_host::DatabasePool::Postgres(pool) => {
904 sqlx::query(sqlx::AssertSqlSafe(format!(
905 "DROP DATABASE IF EXISTS \"{}\"",
906 database_name
907 )))
908 .execute(&pool)
909 .await?;
910 sqlx::query(sqlx::AssertSqlSafe(format!(
911 "DROP USER IF EXISTS \"{}\"",
912 database_username
913 )))
914 .execute(&pool)
915 .await?;
916 }
917 crate::models::database_host::DatabasePool::Mongodb(client) => {
918 let db = client.database(&database_name);
919
920 db.run_command(mongodb::bson::doc! { "dropUser": &database_username })
921 .await?;
922
923 db.drop().await?;
924 }
925 }
926
927 Ok::<_, anyhow::Error>(())
928 };
929
930 if let Err(err) = run_delete().await
931 && !options.force
932 {
933 if err
934 .downcast_ref::<sqlx::Error>()
935 .and_then(|e| e.as_database_error())
936 .is_some_and(|e| e.message().contains("is being accessed"))
937 {
938 return Err(crate::response::DisplayError::new(
939 "this database is being accessed, unable to delete.",
940 )
941 .into());
942 }
943
944 return Err(err);
945 }
946
947 let mut transaction = state_clone.database.write().begin().await?;
948 self_clone
949 .delete_with_transaction(&state_clone, options, &mut transaction)
950 .await?;
951 transaction.commit().await?;
952
953 Ok(())
954 })
955 .await?
956 }
957}
958
959#[schema_extension_derive::extendible]
960#[init_args(ServerDatabase, crate::State)]
961#[hook_args(crate::State)]
962#[derive(ToSchema, Serialize)]
963#[schema(title = "AdminServerDatabase")]
964pub struct AdminApiServerDatabase {
965 pub uuid: uuid::Uuid,
966 pub server: super::server::AdminApiServer,
967
968 pub r#type: DatabaseType,
969 pub host: compact_str::CompactString,
970 pub port: i32,
971
972 pub name: compact_str::CompactString,
973 pub is_locked: bool,
974
975 pub username: compact_str::CompactString,
976 pub password: compact_str::CompactString,
977
978 pub created: chrono::DateTime<chrono::Utc>,
979}
980
981#[schema_extension_derive::extendible]
982#[init_args(ServerDatabase, crate::State)]
983#[hook_args(crate::State)]
984#[derive(ToSchema, Serialize)]
985#[schema(title = "ServerDatabase")]
986pub struct ApiServerDatabase {
987 pub uuid: uuid::Uuid,
988
989 pub r#type: DatabaseType,
990 pub host: compact_str::CompactString,
991 pub port: i32,
992
993 pub name: compact_str::CompactString,
994 pub is_locked: bool,
995
996 pub username: compact_str::CompactString,
997 pub password: Option<compact_str::CompactString>,
998
999 pub created: chrono::DateTime<chrono::Utc>,
1000}