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