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