dmsc/database/
mod.rs

1//! Copyright © 2025-2026 Wenze Wei. All Rights Reserved.
2//!
3//! This file is part of DMSC.
4//! The DMSC project belongs to the Dunimd Team.
5//!
6//! Licensed under the Apache License, Version 2.0 (the "License");
7//! You may not use this file except in compliance with the License.
8//! You may obtain a copy of the License at
9//!
10//!     http://www.apache.org/licenses/LICENSE-2.0
11//!
12//! Unless required by applicable law or agreed to in writing, software
13//! distributed under the License is distributed on an "AS IS" BASIS,
14//! WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15//! See the License for the specific language governing permissions and
16//! limitations under the License.
17
18//! # Database Module
19//!
20//! This module provides a comprehensive database abstraction layer for DMSC,
21//! supporting multiple database backends with a unified interface.
22//!
23//! ## Key Components
24//!
25//! - **DMSCDatabase**: Trait defining the database interface for all supported databases
26//! - **DMSCDatabasePool**: Connection pool management with automatic reuse and health checks
27//! - **DMSCDatabaseConfig**: Builder-style configuration for database connections
28//! - **DMSCDatabaseMigrator**: Database schema migration management
29//! - **DMSCDBRow**: Row representation with type-safe column access
30//! - **DMSCDBResult**: Query result set with iterator support
31//! - **DMSCDatabaseTransaction**: ACID transaction support for data integrity
32//!
33//! ## Supported Databases
34//!
35//! - **PostgreSQL** - Enterprise-grade relational database with full feature support
36//! - **MySQL** - Popular open-source database with high performance
37//! - **SQLite** - Embedded database for lightweight scenarios
38//!
39//! ## Features
40//!
41//! - **Connection Pooling**: Efficient connection reuse with configurable pool size
42//! - **Async/Await**: Full asynchronous API using Tokio runtime
43//! - **Parameter Binding**: Safe SQL parameter binding to prevent injection attacks
44//! - **Batch Operations**: Efficient bulk insert/update operations
45//! - **Transactions**: ACID-compliant transaction support
46//! - **Migrations**: Version-controlled schema migrations
47//! - **Type Conversion**: Automatic conversion between database and JSON types
48//!
49//! ## Usage Example
50//!
51//! ```rust,ignore
52//! use dmsc::database::{DMSCDatabase, DMSCDatabaseConfig, PooledDatabase};
53//!
54//! #[tokio::main]
55//! async fn main() -> DMSCResult<()> {
56//!     let config = DMSCDatabaseConfig::postgres()
57//!         .host("localhost")
58//!         .port(5432)
59//!         .database("mydb")
60//!         .user("user")
61//!         .password("pass")
62//!         .max_connections(10)
63//!         .min_idle_connections(2)
64//!         .connection_timeout_secs(30)
65//!         .build();
66//!
67//!     let pool = DMSCDatabasePool::new(config).await?;
68//!     let db = pool.get().await?;
69//!
70//!     // Execute a query with parameter binding
71//!     use serde_json::json;
72//!     let rows = db.query("SELECT * FROM users WHERE id = $1", &[&json!(1)]).await?;
73//!     for row in rows {
74//!         let id: i64 = row.get("id");
75//!         let name: String = row.get("name");
76//!         println!("User: {} - {}", id, name);
77//!     }
78//!
79//!     Ok(())
80//! }
81//! ```
82//!
83//! ## Batch Operations Example
84//!
85//! ```rust,ignore
86//! use dmsc::database::{DMSCDatabasePool, DMSCDatabaseConfig};
87//!
88//! #[tokio::main]
89//! async fn main() -> DMSCResult<()> {
90//!     let config = DMSCDatabaseConfig::postgres()
91//!         .host("localhost")
92//!         .database("mydb")
93//!         .build();
94//!
95//!     let pool = DMSCDatabasePool::new(config).await?;
96//!     let db = pool.get().await?;
97//!
98//!     // Batch insert users
99//!     let users = vec![
100//!         vec![serde_json::json!("alice"), serde_json::json!("alice@example.com")],
101//!         vec![serde_json::json!("bob"), serde_json::json!("bob@example.com")],
102//!         vec![serde_json::json!("charlie"), serde_json::json!("charlie@example.com")],
103//!     ];
104//!
105//!     let results = db.batch_execute(
106//!         "INSERT INTO users (name, email) VALUES ($1, $2)",
107//!         &users
108//!     ).await?;
109//!
110//!     println!("Inserted {} rows", results.len());
111//!     Ok(())
112//! }
113//! ```
114//!
115//! ## Transaction Example
116//!
117//! ```rust,ignore
118//! use dmsc::database::{DMSCDatabasePool, DMSCDatabaseConfig};
119//!
120//! #[tokio::main]
121//! async fn main() -> DMSCResult<()> {
122//!     let config = DMSCDatabaseConfig::postgres()
123//!         .host("localhost")
124//!         .database("mydb")
125//!         .build();
126//!
127//!     let pool = DMSCDatabasePool::new(config).await?;
128//!     let db = pool.get().await?;
129//!
130//!     // Start transaction
131//!     let mut tx = db.transaction().await?;
132//!
133//!     // Execute operations within transaction
134//!     tx.execute("UPDATE accounts SET balance = balance - 100 WHERE id = $1", &[&1]).await?;
135//!     tx.execute("UPDATE accounts SET balance = balance + 100 WHERE id = $1", &[&2]).await?;
136//!
137//!     // Commit transaction
138//!     tx.commit().await?;
139//!
140//!     Ok(())
141//! }
142//! ```
143
144mod config;
145mod pool;
146mod row;
147mod result;
148mod migration;
149pub mod orm;
150
151#[cfg(feature = "postgres")]
152pub mod postgres;
153#[cfg(feature = "mysql")]
154pub mod mysql;
155#[cfg(feature = "sqlite")]
156pub mod sqlite;
157
158pub use config::{DMSCDatabaseConfig, DatabaseType};
159pub use pool::{DMSCDatabasePool, PooledDatabase, DatabaseMetrics};
160pub use row::DMSCDBRow;
161pub use result::DMSCDBResult;
162pub use migration::{DMSCDatabaseMigration, DMSCMigrationHistory, DMSCDatabaseMigrator};
163pub use orm::{QueryBuilder, Criteria, SortOrder, Pagination, ComparisonOperator, 
164    TableDefinition, ColumnDefinition, IndexDefinition, ForeignKeyDefinition,
165    DMSCORMSimpleRepository, DMSCORMCrudRepository, DMSCORMRepository};
166
167use crate::core::{DMSCResult, DMSCError};
168use async_trait::async_trait;
169use thiserror::Error as ThisError;
170use serde::{Serialize, Deserialize};
171
172#[derive(Debug, ThisError, Clone, Serialize, Deserialize)]
173pub enum DMSCDatabaseTransactionError {
174    #[error("Transaction commit failed: {message}")]
175    CommitFailed { message: String },
176    #[error("Transaction rollback failed: {message}")]
177    RollbackFailed { message: String },
178    #[error("Transaction already completed")]
179    AlreadyCompleted,
180    #[error("Transaction operation failed: {message}")]
181    OperationFailed { message: String },
182}
183
184impl From<DMSCDatabaseTransactionError> for DMSCError {
185    fn from(e: DMSCDatabaseTransactionError) -> Self {
186        DMSCError::Database(e.to_string())
187    }
188}
189
190impl From<DMSCDatabaseTransactionError> for DMSCResult<()> {
191    fn from(e: DMSCDatabaseTransactionError) -> Self {
192        Err(e.into())
193    }
194}
195
196/// Trait defining the database interface for all supported databases.
197///
198/// This trait provides a unified API for database operations across different
199/// database backends. Implementations handle backend-specific details while
200/// presenting a consistent interface to callers.
201///
202/// ## Supported Operations
203///
204/// - **Execution**: Execute SQL statements without returning results
205/// - **Querying**: Execute SELECT statements and iterate over results
206/// - **Parameter Binding**: Safe SQL parameter binding to prevent injection
207/// - **Batch Operations**: Efficient bulk execution and querying
208/// - **Transactions**: ACID-compliant transaction support
209///
210/// ## Example
211///
212/// ```rust,ignore
213/// use dmsc::database::{DMSCDatabase, DMSCDatabasePool, DMSCDatabaseConfig};
214///
215/// #[tokio::main]
216/// async fn main() -> DMSCResult<()> {
217///     let config = DMSCDatabaseConfig::postgres()
218///         .host("localhost")
219///         .database("mydb")
220///         .build();
221///
222///     let pool = DMSCDatabasePool::new(config).await?;
223///     let db = pool.get().await?;
224///
225///     // Execute a statement
226///     db.execute("CREATE TABLE IF NOT EXISTS users (id SERIAL PRIMARY KEY, name TEXT)").await?;
227///
228///     // Query with parameters
229///     let rows = db.query_with_params(
230///         "SELECT * FROM users WHERE name = $1",
231///         &[serde_json::json!("alice")]
232///     ).await?;
233///
234///     Ok(())
235/// }
236/// ```
237#[async_trait]
238pub trait DMSCDatabase: Send + Sync {
239    /// Returns the type of database this instance connects to.
240    ///
241    /// This can be used to identify which database backend is in use
242    /// and potentially branch behavior based on database type.
243    fn database_type(&self) -> DatabaseType;
244
245    /// Executes a SQL statement without returning results.
246    ///
247    /// This method is suitable for INSERT, UPDATE, DELETE, and DDL statements.
248    /// Returns the number of rows affected.
249    ///
250    /// ## Parameters
251    ///
252    /// - `sql`: The SQL statement to execute
253    ///
254    /// ## Returns
255    ///
256    /// The number of rows affected, or an error if execution fails.
257    async fn execute(&self, sql: &str) -> DMSCResult<u64>;
258
259    /// Executes a SQL query and returns all matching rows.
260    ///
261    /// This method is suitable for SELECT statements. The returned result
262    /// can be iterated over to access individual rows.
263    ///
264    /// ## Parameters
265    ///
266    /// - `sql`: The SQL query to execute
267    ///
268    /// ## Returns
269    ///
270    /// A result set containing all matching rows, or an error if the query fails.
271    async fn query(&self, sql: &str) -> DMSCResult<DMSCDBResult>;
272
273    /// Executes a SQL query and returns at most one row.
274    ///
275    /// This method is equivalent to adding "LIMIT 1" to the query.
276    /// Returns `None` if no rows match.
277    ///
278    /// ## Parameters
279    ///
280    /// - `sql`: The SQL query to execute
281    ///
282    /// ## Returns
283    ///
284    /// `Some(row)` if a row was found, `None` if no rows match, or an error.
285    async fn query_one(&self, sql: &str) -> DMSCResult<Option<DMSCDBRow>>;
286
287    /// Checks if the database connection is alive.
288    ///
289    /// This method can be used to verify that the connection is still
290    /// responsive before executing important operations.
291    ///
292    /// ## Returns
293    ///
294    /// `true` if the connection is healthy, `false` otherwise.
295    async fn ping(&self) -> DMSCResult<bool>;
296
297    /// Checks if the database connection is currently connected.
298    ///
299    /// Unlike `ping()`, this method does not perform any network operation.
300    ///
301    /// ## Returns
302    ///
303    /// `true` if connected, `false` otherwise.
304    fn is_connected(&self) -> bool;
305
306    /// Closes the database connection.
307    ///
308    /// This method should be called when the connection is no longer needed
309    /// to release resources. After calling this method, the connection
310    /// should not be used.
311    async fn close(&self) -> DMSCResult<()>;
312
313    /// Executes the same SQL statement multiple times with different parameters.
314    ///
315    /// This is more efficient than executing statements individually when
316    /// performing bulk operations.
317    ///
318    /// ## Parameters
319    ///
320    /// - `sql`: The SQL statement with placeholders ($1, $2, etc.)
321    /// - `params`: A slice of parameter sets, one for each execution
322    ///
323    /// ## Returns
324    ///
325    /// A vector of affected row counts, one for each execution.
326    async fn batch_execute(&self, sql: &str, params: &[Vec<serde_json::Value>]) -> DMSCResult<Vec<u64>> {
327        let mut results = Vec::with_capacity(params.len());
328        for param_set in params {
329            let result = self.execute_with_params(sql, param_set).await?;
330            results.push(result);
331        }
332        Ok(results)
333    }
334
335    /// Executes the same query multiple times with different parameters.
336    ///
337    /// This is more efficient than executing queries individually when
338    /// performing bulk reads.
339    ///
340    /// ## Parameters
341    ///
342    /// - `sql`: The SQL query with placeholders ($1, $2, etc.)
343    /// - `params`: A slice of parameter sets, one for each query
344    ///
345    /// ## Returns
346    ///
347    /// A vector of result sets, one for each query.
348    async fn batch_query(&self, sql: &str, params: &[Vec<serde_json::Value>]) -> DMSCResult<Vec<DMSCDBResult>> {
349        let mut results = Vec::with_capacity(params.len());
350        for param_set in params {
351            let result = self.query_with_params(sql, param_set).await?;
352            results.push(result);
353        }
354        Ok(results)
355    }
356
357    /// Executes a SQL statement with parameters.
358    ///
359    /// Parameters are bound using placeholder syntax ($1, $2, etc.) to
360    /// prevent SQL injection attacks.
361    ///
362    /// ## Parameters
363    ///
364    /// - `sql`: The SQL statement with placeholders
365    /// - `params`: The parameter values to bind
366    ///
367    /// ## Returns
368    ///
369    /// The number of rows affected.
370    async fn execute_with_params(&self, sql: &str, params: &[serde_json::Value]) -> DMSCResult<u64>;
371
372    /// Executes a SQL query with parameters.
373    ///
374    /// Parameters are bound using placeholder syntax ($1, $2, etc.) to
375    /// prevent SQL injection attacks.
376    ///
377    /// ## Parameters
378    ///
379    /// - `sql`: The SQL query with placeholders
380    /// - `params`: The parameter values to bind
381    ///
382    /// ## Returns
383    ///
384    /// A result set containing all matching rows.
385    async fn query_with_params(&self, sql: &str, params: &[serde_json::Value]) -> DMSCResult<DMSCDBResult>;
386
387    /// Starts a new database transaction.
388    ///
389    /// Transactions allow multiple operations to be grouped together
390    /// with atomic commit/rollback semantics.
391    ///
392    /// ## Returns
393    ///
394    /// A transaction handle that can be used to execute operations
395    /// within the transaction.
396    async fn transaction(&self) -> DMSCResult<Box<dyn DMSCDatabaseTransaction>>;
397}
398
399/// Trait representing a database transaction.
400///
401/// Transactions provide atomic operations where multiple SQL statements
402/// can be executed together and either committed or rolled back as a unit.
403/// This ensures data integrity even when operations fail partway through.
404///
405/// ## Transaction Lifecycle
406///
407/// 1. **Begin**: Transaction starts with `DMSCDatabase::transaction()`
408/// 2. **Execute**: Run SQL statements within the transaction
409/// 3. **Commit**: Save all changes with `commit()`
410///    OR **Rollback**: Discard all changes with `rollback()`
411///
412/// ## Example
413///
414/// ```rust,ignore
415/// use dmsc::database::{DMSCDatabasePool, DMSCDatabaseConfig};
416///
417/// #[tokio::main]
418/// async fn main() -> DMSCResult<()> {
419///     let config = DMSCDatabaseConfig::postgres()
420///         .host("localhost")
421///         .database("mydb")
422///         .build();
423///
424///     let pool = DMSCDatabasePool::new(config).await?;
425///     let db = pool.get().await?;
426///
427///     let mut tx = db.transaction().await?;
428///
429///     // Transfer funds between accounts
430///     tx.execute("UPDATE accounts SET balance = balance - 100 WHERE id = $1", &[&1]).await?;
431///     tx.execute("UPDATE accounts SET balance = balance + 100 WHERE id = $1", &[&2]).await?;
432///
433///     tx.commit().await?;
434///     Ok(())
435/// }
436/// ```
437///
438/// ## Auto-Rollback
439///
440/// If a transaction is dropped without explicitly calling `commit()` or
441/// `rollback()`, it will automatically be rolled back to ensure no
442/// partial changes are persisted.
443#[async_trait]
444pub trait DMSCDatabaseTransaction: Send + Sync {
445    /// Executes a SQL statement within the transaction.
446    ///
447    /// See [`DMSCDatabase::execute()`] for more details.
448    async fn execute(&self, sql: &str) -> DMSCResult<u64>;
449
450    /// Executes a SQL query within the transaction.
451    ///
452    /// See [`DMSCDatabase::query()`] for more details.
453    async fn query(&self, sql: &str) -> DMSCResult<DMSCDBResult>;
454
455    /// Commits all changes made within the transaction.
456    ///
457    /// After calling this method, the transaction is complete and
458    /// all changes are permanent.
459    ///
460    /// ## Errors
461    ///
462    /// Returns an error if the commit fails. In this case, the
463    /// transaction should be considered failed and no further
464    /// operations should be attempted.
465    async fn commit(&self) -> DMSCResult<()>;
466
467    /// Rolls back all changes made within the transaction.
468    ///
469    /// This discards all changes made since the transaction began.
470    /// The transaction is then complete and cannot be used further.
471    ///
472    /// ## Errors
473    ///
474    /// Returns an error if the rollback fails. In this case, the
475    /// transaction state is uncertain and the database should be
476    /// checked for consistency.
477    async fn rollback(&self) -> DMSCResult<()>;
478
479    /// Closes the transaction without committing.
480    ///
481    /// If the transaction has not been committed, this will implicitly
482    /// roll back any changes. This method is primarily for cleanup
483    /// and should not be used as a substitute for explicit rollback.
484    async fn close(&self) -> DMSCResult<()>;
485}
dmsc/database/mod.rs

dmsc/database/
mod.rs
