1use crate::error::DbError;
2use crate::postgres::transaction::{
3 next_cursor_name, normalize_stream_sql, validate_prepared_gid, TransactionOptions,
4 DEFAULT_STREAM_BATCH_SIZE,
5};
6use crate::postgres::{QueryBuilder, Transaction};
7use crate::{BackendCapabilities, PoolStatus, POSTGRES_CAPABILITIES};
8use sqlx::postgres::{PgPool, PgSslMode};
9use std::collections::VecDeque;
10use std::path::PathBuf;
11use std::sync::atomic::{AtomicUsize, Ordering};
12use std::sync::Arc;
13use tokio::sync::OnceCell;
14
15const MAX_APPLICATION_NAME_BYTES: usize = 63;
17
18const DISCARD_ALL_SQL: &str = "DISCARD ALL";
20
21const CANCEL_BACKEND_SQL: &str = "SELECT pg_cancel_backend($1)";
23
24const TERMINATE_BACKEND_SQL: &str = "SELECT pg_terminate_backend($1)";
26
27#[derive(Debug, Clone, Copy, PartialEq, Eq)]
33#[non_exhaustive]
34pub enum SslMode {
35 Disable,
37 Prefer,
39 Require,
42 VerifyCa,
44 VerifyFull,
46}
47
48impl SslMode {
49 fn as_pg_ssl_mode(self) -> PgSslMode {
50 match self {
51 Self::Disable => PgSslMode::Disable,
52 Self::Prefer => PgSslMode::Prefer,
53 Self::Require => PgSslMode::Require,
54 Self::VerifyCa => PgSslMode::VerifyCa,
55 Self::VerifyFull => PgSslMode::VerifyFull,
56 }
57 }
58
59 fn requires_root_cert(self) -> bool {
61 matches!(self, Self::VerifyCa | Self::VerifyFull)
62 }
63}
64
65#[derive(Debug, Clone)]
88#[non_exhaustive]
89pub struct DatabaseConfig {
90 pub max_connections: u32,
92 pub connect_timeout: u64,
94 pub idle_timeout: u64,
96 pub enable_logging: bool,
98 pub min_connections: u32,
100 pub max_lifetime: Option<u64>,
102 pub test_before_acquire: bool,
104 pub search_path: Option<String>,
109 pub statement_timeout_ms: Option<u64>,
118 pub lock_timeout_ms: Option<u64>,
123 pub idle_in_transaction_timeout_ms: Option<u64>,
128 pub application_name: Option<String>,
133 pub statement_cache_size: Option<usize>,
138 pub reset_on_release: bool,
144 pub require_utf8_client_encoding: bool,
149 pub ssl_mode: Option<SslMode>,
156 pub ssl_root_cert: Option<PathBuf>,
161 pub ssl_client_cert: Option<PathBuf>,
163 pub ssl_client_key: Option<PathBuf>,
165}
166
167impl Default for DatabaseConfig {
168 fn default() -> Self {
169 Self {
170 max_connections: 10,
171 connect_timeout: 30,
172 idle_timeout: 600,
173 enable_logging: false,
174 min_connections: 0,
175 max_lifetime: None,
176 test_before_acquire: false,
177 search_path: None,
178 statement_timeout_ms: None,
179 lock_timeout_ms: None,
180 idle_in_transaction_timeout_ms: None,
181 application_name: None,
182 statement_cache_size: None,
183 reset_on_release: false,
184 require_utf8_client_encoding: true,
185 ssl_mode: None,
186 ssl_root_cert: None,
187 ssl_client_cert: None,
188 ssl_client_key: None,
189 }
190 }
191}
192
193impl DatabaseConfig {
194 pub fn with_min_connections(mut self, n: u32) -> Self {
196 self.min_connections = n;
197 self
198 }
199
200 pub fn with_max_lifetime(mut self, secs: Option<u64>) -> Self {
202 self.max_lifetime = secs;
203 self
204 }
205
206 pub fn with_test_before_acquire(mut self, enabled: bool) -> Self {
208 self.test_before_acquire = enabled;
209 self
210 }
211
212 pub fn with_search_path(mut self, search_path: impl Into<String>) -> Self {
217 self.search_path = Some(search_path.into());
218 self
219 }
220
221 pub fn with_statement_timeout_ms(mut self, timeout_ms: impl Into<Option<u64>>) -> Self {
223 self.statement_timeout_ms = timeout_ms.into();
224 self
225 }
226
227 pub fn with_lock_timeout_ms(mut self, timeout_ms: impl Into<Option<u64>>) -> Self {
229 self.lock_timeout_ms = timeout_ms.into();
230 self
231 }
232
233 pub fn with_idle_in_transaction_timeout_ms(
235 mut self,
236 timeout_ms: impl Into<Option<u64>>,
237 ) -> Self {
238 self.idle_in_transaction_timeout_ms = timeout_ms.into();
239 self
240 }
241
242 pub fn with_application_name(mut self, application_name: impl Into<String>) -> Self {
244 self.application_name = Some(application_name.into());
245 self
246 }
247
248 pub fn with_statement_cache_size(mut self, capacity: usize) -> Self {
250 self.statement_cache_size = Some(capacity);
251 self
252 }
253
254 pub fn with_reset_on_release(mut self, enabled: bool) -> Self {
256 self.reset_on_release = enabled;
257 self
258 }
259
260 pub fn with_require_utf8_client_encoding(mut self, enabled: bool) -> Self {
262 self.require_utf8_client_encoding = enabled;
263 self
264 }
265
266 pub fn with_ssl_mode(mut self, mode: Option<SslMode>) -> Self {
268 self.ssl_mode = mode;
269 self
270 }
271
272 pub fn with_ssl_root_cert(mut self, path: impl Into<PathBuf>) -> Self {
274 self.ssl_root_cert = Some(path.into());
275 self
276 }
277
278 pub fn effective_ssl_mode(&self) -> Option<SslMode> {
286 match self.ssl_mode {
287 Some(mode) => Some(mode),
288 None if self.ssl_root_cert.is_some() => Some(SslMode::VerifyCa),
289 None => None,
290 }
291 }
292
293 pub fn with_ssl_client_cert(mut self, path: impl Into<PathBuf>) -> Self {
295 self.ssl_client_cert = Some(path.into());
296 self
297 }
298
299 pub fn with_ssl_client_key(mut self, path: impl Into<PathBuf>) -> Self {
301 self.ssl_client_key = Some(path.into());
302 self
303 }
304
305 pub fn validate(&self) -> std::result::Result<(), DbError> {
310 if self.max_connections == 0 {
311 return Err(DbError::InvalidArgument(
312 "PostgreSQL max_connections 必须大于 0".to_string(),
313 ));
314 }
315 if self.min_connections > self.max_connections {
316 return Err(DbError::InvalidArgument(format!(
317 "PostgreSQL min_connections({}) 不能大于 max_connections({})",
318 self.min_connections, self.max_connections
319 )));
320 }
321 if self.connect_timeout == 0 {
322 return Err(DbError::InvalidArgument(
323 "PostgreSQL connect_timeout 必须大于 0 秒".to_string(),
324 ));
325 }
326 if self.idle_timeout == 0 {
327 return Err(DbError::InvalidArgument(
328 "PostgreSQL idle_timeout 必须大于 0 秒".to_string(),
329 ));
330 }
331 if self.idle_timeout <= self.connect_timeout {
332 return Err(DbError::InvalidArgument(format!(
333 "PostgreSQL idle_timeout({}) 必须大于 connect_timeout({})",
334 self.idle_timeout, self.connect_timeout
335 )));
336 }
337 if matches!(self.max_lifetime, Some(0)) {
338 return Err(DbError::InvalidArgument(
339 "PostgreSQL max_lifetime 为 Some 时必须大于 0 秒".to_string(),
340 ));
341 }
342 if let Some(search_path) = &self.search_path {
343 if search_path.trim().is_empty() {
344 return Err(DbError::InvalidArgument(
345 "PostgreSQL search_path 不能为空".to_string(),
346 ));
347 }
348 for segment in search_path.split(',') {
349 let segment = segment.trim();
350 if segment.is_empty() || crate::sql_types::Identifier::parse(segment).is_err() {
351 return Err(DbError::InvalidArgument(format!(
352 "PostgreSQL search_path 片段非法: {segment:?}"
353 )));
354 }
355 }
356 }
357 if let Some(application_name) = &self.application_name {
358 if application_name.is_empty() {
359 return Err(DbError::InvalidArgument(
360 "PostgreSQL application_name 不能为空字符串".to_string(),
361 ));
362 }
363 if application_name.contains('\0') {
364 return Err(DbError::InvalidArgument(
365 "PostgreSQL application_name 不能包含 NUL 字符".to_string(),
366 ));
367 }
368 if application_name.len() > MAX_APPLICATION_NAME_BYTES {
369 return Err(DbError::InvalidArgument(format!(
370 "PostgreSQL application_name 不能超过 {MAX_APPLICATION_NAME_BYTES} 字节,当前 {} 字节",
371 application_name.len()
372 )));
373 }
374 }
375 if let Some(mode) = self.ssl_mode {
376 if mode.requires_root_cert() && self.ssl_root_cert.is_none() {
377 return Err(DbError::InvalidArgument(format!(
378 "PostgreSQL ssl_mode = {mode:?} 必须提供 ssl_root_cert(CA 证书)"
379 )));
380 }
381 if self.ssl_root_cert.is_some()
382 && matches!(mode, SslMode::Prefer | SslMode::Require | SslMode::Disable)
383 {
384 return Err(DbError::InvalidArgument(format!(
385 "PostgreSQL ssl_mode = {mode:?} 不能与 ssl_root_cert 同时配置;根证书只适用于 \
386 VerifyCa/VerifyFull(ssl_mode 为 None 时会自动按 VerifyCa 使用根证书)"
387 )));
388 }
389 if mode == SslMode::Disable
390 && (self.ssl_root_cert.is_some()
391 || self.ssl_client_cert.is_some()
392 || self.ssl_client_key.is_some())
393 {
394 return Err(DbError::InvalidArgument(
395 "PostgreSQL ssl_mode = Disable 时不能配置 TLS 证书".to_string(),
396 ));
397 }
398 }
399 if self.ssl_client_cert.is_some() != self.ssl_client_key.is_some() {
400 return Err(DbError::InvalidArgument(
401 "PostgreSQL ssl_client_cert 与 ssl_client_key 必须成对提供".to_string(),
402 ));
403 }
404 Ok(())
405 }
406}
407
408pub struct Database {
434 pool: PgPool,
435 config: DatabaseConfig,
436 server_version: OnceCell<i32>,
438 waiting: Arc<AtomicUsize>,
440}
441
442impl Database {
443 pub const fn capabilities() -> &'static BackendCapabilities {
445 &POSTGRES_CAPABILITIES
446 }
447
448 pub async fn connect(url: &str) -> Result<Self, DbError> {
457 Self::connect_with_config(url, DatabaseConfig::default()).await
458 }
459
460 pub async fn connect_with_config(url: &str, config: DatabaseConfig) -> Result<Self, DbError> {
462 use sqlx::postgres::PgPoolOptions;
463 use std::str::FromStr;
464 use std::time::Duration;
465
466 config.validate()?;
467
468 let mut options = PgPoolOptions::new()
470 .max_connections(config.max_connections)
471 .min_connections(config.min_connections)
472 .acquire_timeout(Duration::from_secs(config.connect_timeout))
473 .idle_timeout(Duration::from_secs(config.idle_timeout))
474 .test_before_acquire(config.test_before_acquire);
475 if let Some(secs) = config.max_lifetime {
476 options = options.max_lifetime(Duration::from_secs(secs));
477 }
478
479 let mut connect_options =
482 sqlx::postgres::PgConnectOptions::from_str(url).map_err(|error| {
483 DbError::ConnectionError(format!("无效的 PostgreSQL 连接串: {error}"))
484 })?;
485 if let Some(search_path) = &config.search_path {
486 let normalized = search_path
489 .split(',')
490 .map(str::trim)
491 .collect::<Vec<_>>()
492 .join(",");
493 connect_options = connect_options.options([("search_path", normalized)]);
494 }
495 if let Some(mode) = config.effective_ssl_mode() {
499 connect_options = connect_options.ssl_mode(mode.as_pg_ssl_mode());
500 }
501 if let Some(path) = &config.ssl_root_cert {
502 connect_options = connect_options.ssl_root_cert(path);
503 }
504 if let Some(path) = &config.ssl_client_cert {
505 connect_options = connect_options.ssl_client_cert(path);
506 }
507 if let Some(path) = &config.ssl_client_key {
508 connect_options = connect_options.ssl_client_key(path);
509 }
510 if let Some(capacity) = config.statement_cache_size {
511 connect_options = connect_options.statement_cache_capacity(capacity);
512 }
513
514 let statement_timeout_ms = config.statement_timeout_ms;
517 let lock_timeout_ms = config.lock_timeout_ms;
518 let idle_in_transaction_timeout_ms = config.idle_in_transaction_timeout_ms;
519 let timeout_statements = timeout_statements(
520 statement_timeout_ms,
521 lock_timeout_ms,
522 idle_in_transaction_timeout_ms,
523 );
524 let mut session_statements = vec![
527 "SET TimeZone = 'UTC'".to_string(),
528 "SET DateStyle = 'ISO, YMD'".to_string(),
529 "SET IntervalStyle = 'postgres'".to_string(),
530 "SET extra_float_digits = 3".to_string(),
531 "SET standard_conforming_strings = on".to_string(),
532 ];
533 session_statements.extend(timeout_statements);
534 let session_setup = SessionSetup {
535 application_name: config.application_name.clone(),
536 statements: session_statements,
537 require_utf8_client_encoding: config.require_utf8_client_encoding,
538 };
539 let after_connect_setup = session_setup.clone();
540 options = options.after_connect(move |conn, _meta| {
541 let setup = after_connect_setup.clone();
542 Box::pin(async move { setup.apply(conn).await })
543 });
544 if config.reset_on_release {
545 options = options.after_release(|conn, _meta| {
548 Box::pin(async move {
549 sqlx::query(DISCARD_ALL_SQL).execute(&mut *conn).await?;
550 sqlx::Connection::clear_cached_statements(&mut *conn).await?;
556 Ok(true)
557 })
558 });
559 let before_acquire_setup = session_setup;
560 options = options.before_acquire(move |conn, _meta| {
561 let setup = before_acquire_setup.clone();
562 Box::pin(async move { setup.apply(conn).await.map(|_| true) })
563 });
564 }
565
566 let pool = options.connect_with(connect_options).await?;
567
568 Ok(Self {
569 pool,
570 config,
571 server_version: OnceCell::new(),
572 waiting: Arc::new(AtomicUsize::new(0)),
573 })
574 }
575
576 pub fn from_pool(pool: PgPool, config: DatabaseConfig) -> Result<Self, DbError> {
588 config.validate()?;
589 Ok(Self {
590 pool,
591 config,
592 server_version: OnceCell::new(),
593 waiting: Arc::new(AtomicUsize::new(0)),
594 })
595 }
596
597 pub fn table(&self, table: &crate::TableRef) -> QueryBuilder<'_> {
599 QueryBuilder::new(&self.pool, table.as_str(), self.config.enable_logging)
600 }
601
602 pub fn pool(&self) -> &PgPool {
608 &self.pool
609 }
610
611 pub fn pool_status(&self) -> PoolStatus {
623 PoolStatus {
624 max_size: self.config.max_connections as usize,
625 size: self.pool.size() as usize,
626 available: self.pool.num_idle(),
627 waiting: self.waiting.load(Ordering::Relaxed),
628 }
629 }
630
631 fn track_acquire(&self) -> AcquireWaiter<'_> {
636 let would_wait = self.pool.num_idle() == 0
637 && self.pool.size() >= self.pool.options().get_max_connections();
638 if would_wait {
639 self.waiting.fetch_add(1, Ordering::Relaxed);
640 }
641 AcquireWaiter {
642 counter: &self.waiting,
643 armed: would_wait,
644 }
645 }
646
647 pub(crate) async fn acquire(
649 &self,
650 ) -> Result<sqlx::pool::PoolConnection<sqlx::Postgres>, DbError> {
651 let waiter = self.track_acquire();
652 let conn = self.pool.acquire().await?;
653 drop(waiter);
654 Ok(conn)
655 }
656
657 pub async fn health_check(&self) -> Result<bool, DbError> {
663 let mut conn = self.acquire().await?;
664 sqlx::query("SELECT 1").execute(&mut *conn).await?;
665 Ok(true)
666 }
667
668 pub async fn close(&self) {
672 self.pool.close().await;
673 }
674
675 pub fn is_closed(&self) -> bool {
677 self.pool.is_closed()
678 }
679
680 #[deprecated(
685 since = "0.1.0",
686 note = "使用 query_with_params 替代,避免 SQL 注入风险"
687 )]
688 pub async fn query<T>(&self, sql: &str) -> Result<Vec<T>, DbError>
689 where
690 T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
691 {
692 if self.config.enable_logging {
693 log::debug!("执行原生查询: {}", sql);
694 }
695
696 let rows = sqlx::query_as::<_, T>(sql).fetch_all(&self.pool).await?;
697
698 Ok(rows)
699 }
700
701 #[deprecated(
706 since = "0.1.0",
707 note = "使用 execute_with_params 替代,避免 SQL 注入风险"
708 )]
709 pub async fn execute(&self, sql: &str) -> Result<u64, DbError> {
710 if self.config.enable_logging {
711 log::debug!("执行原生语句: {}", sql);
712 }
713
714 let result = sqlx::query(sql).execute(&self.pool).await?;
715
716 Ok(result.rows_affected())
717 }
718
719 pub async fn transaction(&self) -> Result<Transaction, DbError> {
721 let waiter = self.track_acquire();
722 let tx = self.pool.begin().await?;
723 drop(waiter);
724 Ok(Transaction::new(tx, self.config.enable_logging))
725 }
726
727 pub async fn transaction_with_isolation(
732 &self,
733 isolation: crate::isolation::IsolationLevel,
734 ) -> Result<Transaction, DbError> {
735 self.transaction_with_options(TransactionOptions::default().with_isolation(isolation))
736 .await
737 }
738
739 pub async fn transaction_with_options(
745 &self,
746 options: TransactionOptions,
747 ) -> Result<Transaction, DbError> {
748 let waiter = self.track_acquire();
749 let mut tx = self.pool.begin().await?;
750 drop(waiter);
751 if let Some(sql) = options.set_transaction_sql() {
752 if self.config.enable_logging {
753 log::debug!("设置 PostgreSQL 事务属性: {}", sql);
754 }
755 sqlx::query(&sql).execute(&mut *tx).await?;
756 }
757 Ok(Transaction::new(tx, self.config.enable_logging))
758 }
759
760 pub async fn read_only_transaction(&self) -> Result<Transaction, DbError> {
762 self.transaction_with_options(TransactionOptions::read_only())
763 .await
764 }
765
766 pub async fn retry_transaction<T, F, Fut>(
778 &self,
779 operation: F,
780 max_attempts: u32,
781 ) -> Result<T, DbError>
782 where
783 F: FnMut(Transaction) -> Fut,
784 Fut: std::future::Future<Output = Result<T, DbError>>,
785 {
786 self.retry_transaction_with_options(TransactionOptions::default(), operation, max_attempts)
787 .await
788 }
789
790 pub async fn retry_transaction_with_options<T, F, Fut>(
792 &self,
793 options: TransactionOptions,
794 mut operation: F,
795 max_attempts: u32,
796 ) -> Result<T, DbError>
797 where
798 F: FnMut(Transaction) -> Fut,
799 Fut: std::future::Future<Output = Result<T, DbError>>,
800 {
801 if max_attempts == 0 {
802 return Err(DbError::InvalidArgument(
803 "重试次数 max_attempts 必须大于 0".to_string(),
804 ));
805 }
806
807 let mut attempt = 0u32;
808 loop {
809 attempt += 1;
810 let transaction = self.transaction_with_options(options).await?;
811 match operation(transaction).await {
812 Ok(value) => return Ok(value),
813 Err(error) => {
814 if !is_serialization_conflict(&error) || attempt >= max_attempts {
815 return Err(error);
816 }
817 let backoff = retry_backoff(attempt);
818 if self.config.enable_logging {
819 log::warn!(
820 "事务序列化冲突(第 {attempt} 次尝试),{:?} 后重试: {error}",
821 backoff
822 );
823 }
824 tokio::time::sleep(backoff).await;
825 }
826 }
827 }
828 }
829
830 #[deprecated(
835 since = "0.1.4",
836 note = "请使用 execute_script(整脚本单事务,正确处理函数/DO 块与 dollar-quote)"
837 )]
838 pub async fn init(&self, sql_script: &str) -> Result<(), DbError> {
839 self.execute_script(sql_script).await.map(|_| ())
840 }
841
842 #[deprecated(
847 since = "0.2.2",
848 note = "裸 SQL 建表逃生舱;请使用 execute_script(单事务、可含多条 DDL)或外部迁移工具"
849 )]
850 pub async fn create_table(&self, create_sql: &str) -> Result<(), DbError> {
851 let mut conn = self.acquire().await?;
852 sqlx::query(create_sql).execute(&mut *conn).await?;
853 Ok(())
854 }
855
856 pub async fn drop_table(&self, table_name: &crate::TableRef) -> Result<(), DbError> {
861 let quoted = crate::postgres::identifier::quote_table(table_name.as_str())?;
862 let sql = format!("DROP TABLE IF EXISTS {}", quoted);
863 let mut conn = self.acquire().await?;
864 sqlx::query(&sql).execute(&mut *conn).await?;
865 Ok(())
866 }
867
868 pub async fn table_exists(&self, table_name: &crate::TableRef) -> Result<bool, DbError> {
874 let quoted = crate::postgres::identifier::quote_table(table_name.as_str())?;
877 let exists: bool = sqlx::query_scalar("SELECT to_regclass($1) IS NOT NULL")
878 .bind(quoted)
879 .fetch_one(&self.pool)
880 .await?;
881
882 Ok(exists)
883 }
884
885 pub async fn query_with_params<T>(
909 &self,
910 sql: &str,
911 params: Vec<serde_json::Value>,
912 ) -> Result<Vec<T>, DbError>
913 where
914 T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
915 {
916 if self.config.enable_logging {
917 log::debug!("执行参数化查询: {}, 参数数量: {}", sql, params.len());
918 }
919
920 let mut conn = self.acquire().await?;
922 let mut query = sqlx::query_as::<_, T>(sql);
923 for param in ¶ms {
924 query = bind_json_param_as(query, param);
925 }
926
927 let rows = query.fetch_all(&mut *conn).await?;
928 Ok(rows)
929 }
930
931 pub async fn execute_with_params(
955 &self,
956 sql: &str,
957 params: Vec<serde_json::Value>,
958 ) -> Result<u64, DbError> {
959 if self.config.enable_logging {
960 log::debug!("执行参数化语句: {}, 参数数量: {}", sql, params.len());
961 }
962
963 let mut conn = self.acquire().await?;
965 let mut query = sqlx::query(sql);
966 for param in ¶ms {
967 query = bind_json_param(query, param);
968 }
969
970 let result = query.execute(&mut *conn).await?;
971 Ok(result.rows_affected())
972 }
973
974 pub async fn stream_query(
994 &self,
995 sql: &str,
996 params: Vec<serde_json::Value>,
997 ) -> Result<PgRowCursor, DbError> {
998 self.stream_query_with_batch_size(sql, params, DEFAULT_STREAM_BATCH_SIZE)
999 .await
1000 }
1001
1002 pub async fn stream_query_with_batch_size(
1004 &self,
1005 sql: &str,
1006 params: Vec<serde_json::Value>,
1007 batch_size: usize,
1008 ) -> Result<PgRowCursor, DbError> {
1009 if batch_size == 0 {
1010 return Err(DbError::InvalidArgument(
1011 "流式查询 batch_size 必须大于 0".to_string(),
1012 ));
1013 }
1014 let body = normalize_stream_sql(sql)?;
1015 let mut conn = self.acquire().await?;
1016 sqlx::query("BEGIN READ ONLY").execute(&mut *conn).await?;
1018
1019 let cursor_name = next_cursor_name();
1020 let declare = format!("DECLARE {} CURSOR FOR {}", cursor_name, body);
1021 if self.config.enable_logging {
1022 log::debug!("声明流式游标: {}", declare);
1023 }
1024 let mut query = sqlx::query(&declare);
1025 for param in ¶ms {
1026 query = bind_json_param(query, param);
1027 }
1028 if let Err(error) = query.execute(&mut *conn).await {
1029 let _ = sqlx::query("ROLLBACK").execute(&mut *conn).await;
1031 return Err(error.into());
1032 }
1033
1034 Ok(PgRowCursor::new(
1035 conn,
1036 cursor_name,
1037 batch_size,
1038 self.config.enable_logging,
1039 ))
1040 }
1041
1042 pub async fn advisory_lock(&self, key: i64) -> Result<PgAdvisoryLock, DbError> {
1048 self.acquire_advisory_lock(key, AdvisoryLockMode::Exclusive)
1049 .await
1050 }
1051
1052 pub async fn advisory_lock_shared(&self, key: i64) -> Result<PgAdvisoryLock, DbError> {
1057 self.acquire_advisory_lock(key, AdvisoryLockMode::Shared)
1058 .await
1059 }
1060
1061 pub async fn try_advisory_lock(&self, key: i64) -> Result<Option<PgAdvisoryLock>, DbError> {
1065 self.try_acquire_advisory_lock(key, AdvisoryLockMode::Exclusive)
1066 .await
1067 }
1068
1069 pub async fn try_advisory_lock_shared(
1071 &self,
1072 key: i64,
1073 ) -> Result<Option<PgAdvisoryLock>, DbError> {
1074 self.try_acquire_advisory_lock(key, AdvisoryLockMode::Shared)
1075 .await
1076 }
1077
1078 async fn acquire_advisory_lock(
1079 &self,
1080 key: i64,
1081 mode: AdvisoryLockMode,
1082 ) -> Result<PgAdvisoryLock, DbError> {
1083 let mut conn = self.acquire().await?;
1084 if self.config.enable_logging {
1085 log::debug!("获取 PG 会话级 advisory lock: key={key}, mode={mode:?}");
1086 }
1087 sqlx::query(mode.lock_sql())
1088 .bind(key)
1089 .execute(&mut *conn)
1090 .await?;
1091 Ok(PgAdvisoryLock {
1092 conn: Some(conn),
1093 key,
1094 mode,
1095 })
1096 }
1097
1098 async fn try_acquire_advisory_lock(
1099 &self,
1100 key: i64,
1101 mode: AdvisoryLockMode,
1102 ) -> Result<Option<PgAdvisoryLock>, DbError> {
1103 let mut conn = self.acquire().await?;
1104 let acquired: bool = sqlx::query_scalar(mode.try_lock_sql())
1105 .bind(key)
1106 .fetch_one(&mut *conn)
1107 .await?;
1108 if acquired {
1109 Ok(Some(PgAdvisoryLock {
1110 conn: Some(conn),
1111 key,
1112 mode,
1113 }))
1114 } else {
1115 Ok(None)
1116 }
1117 }
1118
1119 pub async fn server_version_num(&self) -> Result<i32, DbError> {
1123 let version = self
1124 .server_version
1125 .get_or_try_init(|| async {
1126 let mut conn = self.acquire().await?;
1127 let value: i32 =
1128 sqlx::query_scalar("SELECT current_setting('server_version_num')::int")
1129 .fetch_one(&mut *conn)
1130 .await?;
1131 Ok::<i32, DbError>(value)
1132 })
1133 .await?;
1134 Ok(*version)
1135 }
1136
1137 pub async fn supports(&self, min_version: i32) -> Result<bool, DbError> {
1141 Ok(self.server_version_num().await? >= min_version)
1142 }
1143
1144 pub async fn prepare_transaction(
1150 &self,
1151 transaction: Transaction,
1152 gid: &str,
1153 ) -> Result<(), DbError> {
1154 transaction.prepare(gid).await
1155 }
1156
1157 pub async fn commit_prepared(&self, gid: &str) -> Result<(), DbError> {
1161 validate_prepared_gid(gid)?;
1162 let sql = format!("COMMIT PREPARED '{gid}'");
1163 if self.config.enable_logging {
1164 log::debug!("提交已准备事务: gid={gid}");
1165 }
1166 let mut conn = self.acquire().await?;
1167 sqlx::query(&sql).execute(&mut *conn).await?;
1168 Ok(())
1169 }
1170
1171 pub async fn rollback_prepared(&self, gid: &str) -> Result<(), DbError> {
1175 validate_prepared_gid(gid)?;
1176 let sql = format!("ROLLBACK PREPARED '{gid}'");
1177 if self.config.enable_logging {
1178 log::debug!("回滚已准备事务: gid={gid}");
1179 }
1180 let mut conn = self.acquire().await?;
1181 sqlx::query(&sql).execute(&mut *conn).await?;
1182 Ok(())
1183 }
1184
1185 pub async fn explain(
1193 &self,
1194 sql: &str,
1195 params: Vec<serde_json::Value>,
1196 ) -> Result<Vec<String>, DbError> {
1197 let statement = explain_statement(sql)?;
1198 if self.config.enable_logging {
1199 log::debug!("执行 EXPLAIN: {}, 参数数量: {}", statement, params.len());
1200 }
1201 let mut query = sqlx::query_as::<_, (serde_json::Value,)>(&statement);
1202 for param in ¶ms {
1203 query = bind_json_param_as(query, param);
1204 }
1205 let mut conn = self.acquire().await?;
1206 let rows = query.fetch_all(&mut *conn).await?;
1207 explain_rows_to_strings(rows)
1208 }
1209
1210 pub async fn active_queries(&self) -> Result<Vec<ActiveQuery>, DbError> {
1215 let mut conn = self.acquire().await?;
1216 let rows = sqlx::query_as::<_, ActiveQuery>(
1217 "SELECT pid,
1218 usename::text AS usename,
1219 application_name,
1220 host(client_addr) AS client_addr,
1221 state,
1222 wait_event_type,
1223 wait_event,
1224 query_start,
1225 query
1226 FROM pg_stat_activity
1227 ORDER BY query_start DESC NULLS LAST, pid",
1228 )
1229 .fetch_all(&mut *conn)
1230 .await?;
1231 Ok(rows)
1232 }
1233
1234 pub async fn lock_waits(&self) -> Result<Vec<LockWait>, DbError> {
1239 let mut conn = self.acquire().await?;
1240 let rows = sqlx::query_as::<_, LockWait>(
1241 "SELECT blocked.pid AS blocked_pid,
1242 blocking.pid AS blocking_pid,
1243 blocked.query AS blocked_query,
1244 blocking.query AS blocking_query,
1245 CASE
1246 WHEN l.relation IS NULL OR l.relation = 0 THEN NULL
1247 ELSE l.relation::regclass::text
1248 END AS relation,
1249 l.locktype AS lock_type,
1250 l.mode AS mode,
1251 blocked.wait_event AS wait_event
1252 FROM pg_locks l
1253 JOIN pg_stat_activity blocked ON blocked.pid = l.pid
1254 CROSS JOIN LATERAL unnest(pg_blocking_pids(l.pid)) AS bp(blocking_pid)
1255 JOIN pg_stat_activity blocking ON blocking.pid = bp.blocking_pid
1256 WHERE NOT l.granted
1257 ORDER BY blocked.pid, blocking.pid",
1258 )
1259 .fetch_all(&mut *conn)
1260 .await?;
1261 Ok(rows)
1262 }
1263
1264 pub async fn acquire_with_cancel_token(
1276 &self,
1277 ) -> Result<(sqlx::pool::PoolConnection<sqlx::Postgres>, CancelToken), DbError> {
1278 if self.config.max_connections < 2 {
1279 return Err(DbError::InvalidArgument(format!(
1280 "PostgreSQL 取消令牌需要至少 2 条连接(取消要另借一条连接),当前 \
1281 max_connections = {}",
1282 self.config.max_connections
1283 )));
1284 }
1285 let mut conn = self.acquire().await?;
1286 let backend_pid: i32 = sqlx::query_scalar("SELECT pg_backend_pid()")
1287 .fetch_one(&mut *conn)
1288 .await?;
1289 let token = CancelToken {
1290 pool: self.pool.clone(),
1291 backend_pid,
1292 };
1293 Ok((conn, token))
1294 }
1295
1296 pub async fn reset_connection(&self) -> Result<(), DbError> {
1305 let mut conn = self.acquire().await?;
1306 sqlx::query(DISCARD_ALL_SQL).execute(&mut *conn).await?;
1307 sqlx::Connection::clear_cached_statements(&mut *conn).await?;
1308 Ok(())
1309 }
1310}
1311
1312#[derive(Debug, Clone)]
1322pub struct CancelToken {
1323 pool: PgPool,
1324 backend_pid: i32,
1325}
1326
1327impl CancelToken {
1328 pub fn backend_pid(&self) -> i32 {
1330 self.backend_pid
1331 }
1332
1333 pub async fn cancel(&self) -> Result<bool, DbError> {
1338 Ok(sqlx::query_scalar(CANCEL_BACKEND_SQL)
1339 .bind(self.backend_pid)
1340 .fetch_one(&self.pool)
1341 .await?)
1342 }
1343
1344 pub async fn terminate(&self) -> Result<bool, DbError> {
1346 Ok(sqlx::query_scalar(TERMINATE_BACKEND_SQL)
1347 .bind(self.backend_pid)
1348 .fetch_one(&self.pool)
1349 .await?)
1350 }
1351}
1352
1353#[derive(Debug, Clone, sqlx::FromRow)]
1355pub struct ActiveQuery {
1356 pub pid: i32,
1358 pub usename: Option<String>,
1360 pub application_name: Option<String>,
1362 pub client_addr: Option<String>,
1364 pub state: Option<String>,
1366 pub wait_event_type: Option<String>,
1368 pub wait_event: Option<String>,
1370 pub query_start: Option<chrono::DateTime<chrono::Utc>>,
1372 pub query: String,
1374}
1375
1376#[derive(Debug, Clone, sqlx::FromRow)]
1378pub struct LockWait {
1379 pub blocked_pid: i32,
1381 pub blocking_pid: i32,
1383 pub blocked_query: Option<String>,
1385 pub blocking_query: Option<String>,
1387 pub relation: Option<String>,
1389 pub lock_type: Option<String>,
1391 pub mode: Option<String>,
1393 pub wait_event: Option<String>,
1395}
1396
1397fn explain_statement(sql: &str) -> Result<String, DbError> {
1399 let trimmed = sql.trim().trim_end_matches(';').trim_end();
1400 if trimmed.is_empty() {
1401 return Err(DbError::InvalidArgument(
1402 "EXPLAIN 的 SQL 不能为空".to_string(),
1403 ));
1404 }
1405 Ok(format!("EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) {trimmed}"))
1406}
1407
1408fn explain_rows_to_strings(rows: Vec<(serde_json::Value,)>) -> Result<Vec<String>, DbError> {
1413 let mut plans = Vec::new();
1414 for (row,) in rows {
1415 let items = match row {
1416 serde_json::Value::Array(items) => items,
1417 other => vec![other],
1418 };
1419 for item in items {
1420 plans.push(serde_json::to_string_pretty(&item).map_err(|error| {
1421 DbError::SerializationError(format!("EXPLAIN 计划序列化失败: {error}"))
1422 })?);
1423 }
1424 }
1425 Ok(plans)
1426}
1427
1428fn timeout_statements(
1432 statement_timeout_ms: Option<u64>,
1433 lock_timeout_ms: Option<u64>,
1434 idle_in_transaction_timeout_ms: Option<u64>,
1435) -> Vec<String> {
1436 let mut statements = Vec::new();
1437 if let Some(ms) = statement_timeout_ms {
1438 statements.push(format!("SET statement_timeout = {ms}"));
1439 }
1440 if let Some(ms) = lock_timeout_ms {
1441 statements.push(format!("SET lock_timeout = {ms}"));
1442 }
1443 if let Some(ms) = idle_in_transaction_timeout_ms {
1444 statements.push(format!("SET idle_in_transaction_session_timeout = {ms}"));
1445 }
1446 statements
1447}
1448
1449fn is_serialization_conflict(error: &DbError) -> bool {
1451 matches!(error.sqlstate(), Some("40001") | Some("40P01"))
1452}
1453
1454fn retry_backoff(attempt: u32) -> std::time::Duration {
1456 let exponent = attempt.saturating_sub(1).min(7);
1457 std::time::Duration::from_millis((10u64 << exponent).min(1_000))
1458}
1459
1460struct AcquireWaiter<'a> {
1462 counter: &'a AtomicUsize,
1463 armed: bool,
1464}
1465
1466impl Drop for AcquireWaiter<'_> {
1467 fn drop(&mut self) {
1468 if self.armed {
1469 self.counter.fetch_sub(1, Ordering::Relaxed);
1470 }
1471 }
1472}
1473
1474#[derive(Debug, Clone)]
1476struct SessionSetup {
1477 application_name: Option<String>,
1479 statements: Vec<String>,
1481 require_utf8_client_encoding: bool,
1483}
1484
1485impl SessionSetup {
1486 async fn apply(&self, conn: &mut sqlx::PgConnection) -> Result<(), sqlx::Error> {
1491 if let Some(application_name) = &self.application_name {
1492 sqlx::query("SELECT set_config('application_name', $1, false)")
1493 .bind(application_name)
1494 .execute(&mut *conn)
1495 .await?;
1496 }
1497 for sql in &self.statements {
1498 sqlx::query(sql).execute(&mut *conn).await?;
1499 }
1500 if self.require_utf8_client_encoding {
1501 let encoding: String = sqlx::query_scalar("SELECT current_setting('client_encoding')")
1502 .fetch_one(&mut *conn)
1503 .await?;
1504 if !client_encoding_is_utf8(&encoding) {
1505 return Err(sqlx::Error::Configuration(
1506 format!(
1507 "PostgreSQL client_encoding 必须为 UTF8,当前为 {encoding:?};\
1508 如确需其他编码请设置 DatabaseConfig::with_require_utf8_client_encoding(false)"
1509 )
1510 .into(),
1511 ));
1512 }
1513 }
1514 Ok(())
1515 }
1516}
1517
1518fn client_encoding_is_utf8(encoding: &str) -> bool {
1520 matches!(
1521 encoding.trim().to_ascii_uppercase().as_str(),
1522 "UTF8" | "UTF-8" | "UNICODE"
1523 )
1524}
1525
1526#[derive(Debug, Clone, Copy, PartialEq, Eq)]
1528enum AdvisoryLockMode {
1529 Exclusive,
1530 Shared,
1531}
1532
1533impl AdvisoryLockMode {
1534 fn lock_sql(self) -> &'static str {
1536 match self {
1537 Self::Exclusive => "SELECT pg_advisory_lock($1)",
1538 Self::Shared => "SELECT pg_advisory_lock_shared($1)",
1539 }
1540 }
1541
1542 fn try_lock_sql(self) -> &'static str {
1544 match self {
1545 Self::Exclusive => "SELECT pg_try_advisory_lock($1)",
1546 Self::Shared => "SELECT pg_try_advisory_lock_shared($1)",
1547 }
1548 }
1549
1550 fn unlock_sql(self) -> &'static str {
1552 match self {
1553 Self::Exclusive => "SELECT pg_advisory_unlock($1)",
1554 Self::Shared => "SELECT pg_advisory_unlock_shared($1)",
1555 }
1556 }
1557}
1558
1559pub struct PgAdvisoryLock {
1566 conn: Option<sqlx::pool::PoolConnection<sqlx::Postgres>>,
1567 key: i64,
1568 mode: AdvisoryLockMode,
1569}
1570
1571impl PgAdvisoryLock {
1572 pub fn key(&self) -> i64 {
1574 self.key
1575 }
1576
1577 pub fn is_shared(&self) -> bool {
1579 self.mode == AdvisoryLockMode::Shared
1580 }
1581
1582 pub async fn unlock(mut self) -> Result<bool, DbError> {
1589 let mode = self.mode;
1590 let conn = self
1591 .conn
1592 .as_mut()
1593 .ok_or_else(|| DbError::TransactionError("advisory lock 已释放".to_string()))?;
1594 let unlocked: bool = sqlx::query_scalar(mode.unlock_sql())
1595 .bind(self.key)
1596 .fetch_one(&mut **conn)
1597 .await?;
1598 if unlocked {
1599 self.conn.take();
1601 } else {
1602 if let Some(conn) = self.conn.take() {
1604 let _ = conn.detach();
1605 }
1606 }
1607 Ok(unlocked)
1608 }
1609
1610 pub async fn advisory_unlock(self) -> Result<bool, DbError> {
1616 self.unlock().await
1617 }
1618}
1619
1620impl Drop for PgAdvisoryLock {
1621 fn drop(&mut self) {
1622 if let Some(conn) = self.conn.take() {
1623 log::warn!(
1624 "PG 会话级 advisory lock({}) 未显式 unlock 即被丢弃;关闭连接以让服务端回收锁",
1625 self.key
1626 );
1627 let _ = conn.detach();
1628 }
1629 }
1630}
1631
1632pub struct PgRowCursor {
1636 conn: Option<sqlx::pool::PoolConnection<sqlx::Postgres>>,
1637 cursor_name: String,
1638 batch_size: usize,
1639 buffer: VecDeque<sqlx::postgres::PgRow>,
1640 exhausted: bool,
1641 closed: bool,
1642 enable_logging: bool,
1643}
1644
1645impl PgRowCursor {
1646 fn new(
1647 conn: sqlx::pool::PoolConnection<sqlx::Postgres>,
1648 cursor_name: String,
1649 batch_size: usize,
1650 enable_logging: bool,
1651 ) -> Self {
1652 Self {
1653 conn: Some(conn),
1654 cursor_name,
1655 batch_size,
1656 buffer: VecDeque::with_capacity(batch_size.min(1024)),
1657 exhausted: false,
1658 closed: false,
1659 enable_logging,
1660 }
1661 }
1662
1663 pub fn batch_size(&self) -> usize {
1665 self.batch_size
1666 }
1667
1668 pub async fn next(&mut self) -> Result<Option<sqlx::postgres::PgRow>, DbError> {
1670 loop {
1671 if let Some(row) = self.buffer.pop_front() {
1672 return Ok(Some(row));
1673 }
1674 if self.exhausted {
1675 self.release().await?;
1676 return Ok(None);
1677 }
1678 self.fetch_batch().await?;
1679 }
1680 }
1681
1682 pub async fn close(mut self) -> Result<(), DbError> {
1684 self.buffer.clear();
1685 self.release().await
1686 }
1687
1688 async fn fetch_batch(&mut self) -> Result<(), DbError> {
1689 let conn = self
1690 .conn
1691 .as_mut()
1692 .ok_or_else(|| DbError::TransactionError("流式游标已关闭".to_string()))?;
1693 let sql = format!(
1694 "FETCH FORWARD {} FROM {}",
1695 self.batch_size, self.cursor_name
1696 );
1697 if self.enable_logging {
1698 log::debug!("游标拉取: {}", sql);
1699 }
1700 let rows = sqlx::query(&sql).fetch_all(&mut **conn).await?;
1701 if rows.len() < self.batch_size {
1702 self.exhausted = true;
1703 }
1704 self.buffer.extend(rows);
1705 Ok(())
1706 }
1707
1708 async fn release(&mut self) -> Result<(), DbError> {
1710 if self.closed {
1711 return Ok(());
1712 }
1713 self.closed = true;
1714 self.exhausted = true;
1715
1716 if let Some(mut conn) = self.conn.take() {
1717 let close_sql = format!("CLOSE {}", self.cursor_name);
1718 let close_ok = sqlx::query(&close_sql).execute(&mut *conn).await.is_ok();
1719 let rollback_ok = sqlx::query("ROLLBACK").execute(&mut *conn).await.is_ok();
1720 if !(close_ok && rollback_ok) {
1721 let _ = conn.detach();
1723 return Err(DbError::TransactionError(
1724 "流式游标关闭失败,连接已丢弃".to_string(),
1725 ));
1726 }
1727 }
1728 Ok(())
1729 }
1730}
1731
1732impl Drop for PgRowCursor {
1733 fn drop(&mut self) {
1734 if let Some(conn) = self.conn.take() {
1735 let _ = conn.detach();
1737 }
1738 }
1739}
1740
1741#[derive(Debug, Clone, Copy)]
1749struct PgUntypedNull;
1750
1751impl sqlx::Type<sqlx::Postgres> for PgUntypedNull {
1752 fn type_info() -> sqlx::postgres::PgTypeInfo {
1753 sqlx::postgres::PgTypeInfo::with_oid(sqlx::postgres::types::Oid(0))
1755 }
1756}
1757
1758impl sqlx::Encode<'_, sqlx::Postgres> for PgUntypedNull {
1759 fn encode_by_ref(
1760 &self,
1761 _buffer: &mut sqlx::postgres::PgArgumentBuffer,
1762 ) -> Result<sqlx::encode::IsNull, sqlx::error::BoxDynError> {
1763 Ok(sqlx::encode::IsNull::Yes)
1764 }
1765
1766 fn size_hint(&self) -> usize {
1767 0
1768 }
1769}
1770
1771macro_rules! bind_json_value {
1786 ($query:expr, $param:expr) => {
1787 match $param {
1788 serde_json::Value::String(s) => $query.bind(s.clone()),
1790 serde_json::Value::Number(n) => match crate::json_param::classify_json_number(n) {
1792 Some(crate::json_param::JsonNumber::Int(i)) => $query.bind(i),
1793 Some(crate::json_param::JsonNumber::Uint(u)) => $query.bind(u.to_string()),
1794 Some(crate::json_param::JsonNumber::Float(f)) => $query.bind(f),
1795 None => $query.bind(PgUntypedNull),
1796 },
1797 serde_json::Value::Bool(b) => $query.bind(*b),
1799 serde_json::Value::Null => $query.bind(PgUntypedNull),
1801 other => $query.bind(other.clone()),
1803 }
1804 };
1805}
1806
1807fn bind_json_param_as<'q, T>(
1816 query: sqlx::query::QueryAs<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>,
1817 param: &serde_json::Value,
1818) -> sqlx::query::QueryAs<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>
1819where
1820 T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow>,
1821{
1822 bind_json_value!(query, param)
1823}
1824
1825fn bind_json_param<'q>(
1834 query: sqlx::query::Query<'q, sqlx::Postgres, sqlx::postgres::PgArguments>,
1835 param: &serde_json::Value,
1836) -> sqlx::query::Query<'q, sqlx::Postgres, sqlx::postgres::PgArguments> {
1837 bind_json_value!(query, param)
1838}
1839
1840#[cfg(test)]
1841#[allow(clippy::unwrap_used, clippy::expect_used)]
1842mod tests {
1843 use super::*;
1844
1845 #[test]
1846 fn test_database_config_validate_accepts_default_config() {
1847 let config = DatabaseConfig::default();
1848
1849 assert!(config.validate().is_ok());
1850 }
1851
1852 #[test]
1853 fn test_database_config_validate_rejects_invalid_pool_size() {
1854 assert!(matches!(
1855 DatabaseConfig {
1856 max_connections: 0,
1857 ..DatabaseConfig::default()
1858 }
1859 .validate(),
1860 Err(DbError::InvalidArgument(_))
1861 ));
1862
1863 assert!(matches!(
1864 DatabaseConfig {
1865 max_connections: 2,
1866 min_connections: 3,
1867 ..DatabaseConfig::default()
1868 }
1869 .validate(),
1870 Err(DbError::InvalidArgument(_))
1871 ));
1872 }
1873
1874 #[test]
1875 fn test_database_config_validate_rejects_invalid_timeouts() {
1876 for config in [
1877 DatabaseConfig {
1878 connect_timeout: 0,
1879 ..DatabaseConfig::default()
1880 },
1881 DatabaseConfig {
1882 idle_timeout: 0,
1883 ..DatabaseConfig::default()
1884 },
1885 DatabaseConfig {
1886 max_lifetime: Some(0),
1887 ..DatabaseConfig::default()
1888 },
1889 ] {
1890 assert!(matches!(
1891 config.validate(),
1892 Err(DbError::InvalidArgument(_))
1893 ));
1894 }
1895
1896 assert!(matches!(
1897 DatabaseConfig {
1898 connect_timeout: 30,
1899 idle_timeout: 30,
1900 ..DatabaseConfig::default()
1901 }
1902 .validate(),
1903 Err(DbError::InvalidArgument(_))
1904 ));
1905 }
1906
1907 #[tokio::test]
1908 async fn test_connect_with_config_rejects_invalid_config_before_connecting() {
1909 let config = DatabaseConfig {
1910 max_connections: 0,
1911 ..DatabaseConfig::default()
1912 };
1913 let result =
1914 Database::connect_with_config("postgres://postgres:bad@127.0.0.1:1/test", config).await;
1915
1916 assert!(matches!(result, Err(DbError::InvalidArgument(_))));
1917 }
1918
1919 #[test]
1920 fn test_search_path_validation_accepts_identifier_lists_only() {
1921 assert!(DatabaseConfig::default().search_path.is_none());
1922
1923 let config = DatabaseConfig::default().with_search_path("app, public");
1924 assert_eq!(config.search_path.as_deref(), Some("app, public"));
1925 assert!(config.validate().is_ok());
1926
1927 for invalid in [
1928 "",
1929 " ",
1930 "app,,public",
1931 "app; DROP SCHEMA public",
1932 "app public",
1933 ] {
1934 let config = DatabaseConfig::default().with_search_path(invalid);
1935 assert!(
1936 matches!(config.validate(), Err(DbError::InvalidArgument(_))),
1937 "search_path 应拒绝 {invalid:?}"
1938 );
1939 }
1940 }
1941
1942 #[tokio::test]
1943 async fn health_check_propagates_closed_pool_error() -> Result<(), sqlx::Error> {
1944 let pool = sqlx::postgres::PgPoolOptions::new()
1945 .connect_lazy("postgres://postgres:test@127.0.0.1:5432/test")?;
1946 let db = Database {
1947 pool,
1948 config: DatabaseConfig::default(),
1949 server_version: OnceCell::new(),
1950 waiting: Arc::new(AtomicUsize::new(0)),
1951 };
1952
1953 db.close().await;
1954
1955 assert!(db.is_closed());
1956 assert!(matches!(db.health_check().await, Err(DbError::PoolClosed)));
1957 Ok(())
1958 }
1959
1960 #[test]
1961 fn timeout_config_is_opt_in_and_renders_set_statements() {
1962 let default_config = DatabaseConfig::default();
1963 assert!(default_config.statement_timeout_ms.is_none());
1964 assert!(default_config.lock_timeout_ms.is_none());
1965 assert!(default_config.idle_in_transaction_timeout_ms.is_none());
1966 assert!(timeout_statements(None, None, None).is_empty());
1967
1968 let config = DatabaseConfig::default()
1969 .with_statement_timeout_ms(5_000u64)
1970 .with_lock_timeout_ms(Some(250))
1971 .with_idle_in_transaction_timeout_ms(30_000u64);
1972 assert_eq!(config.statement_timeout_ms, Some(5_000));
1973 assert_eq!(config.lock_timeout_ms, Some(250));
1974 assert_eq!(config.idle_in_transaction_timeout_ms, Some(30_000));
1975 assert!(config.validate().is_ok());
1976
1977 assert_eq!(
1978 timeout_statements(
1979 config.statement_timeout_ms,
1980 config.lock_timeout_ms,
1981 config.idle_in_transaction_timeout_ms
1982 ),
1983 vec![
1984 "SET statement_timeout = 5000".to_string(),
1985 "SET lock_timeout = 250".to_string(),
1986 "SET idle_in_transaction_session_timeout = 30000".to_string(),
1987 ]
1988 );
1989 }
1990
1991 #[test]
1992 fn only_serialization_failures_and_deadlocks_are_retried() {
1993 use crate::error::SqlErrorInfo;
1994
1995 assert!(is_serialization_conflict(&DbError::DatabaseError(
1996 Box::new(SqlErrorInfo::new("40001", "could not serialize access"))
1997 )));
1998 assert!(is_serialization_conflict(&DbError::DatabaseError(
1999 Box::new(SqlErrorInfo::new("40P01", "deadlock detected"))
2000 )));
2001 for other in ["23505", "57014", "55P03", "42P01"] {
2002 assert!(
2003 !is_serialization_conflict(&DbError::DatabaseError(Box::new(SqlErrorInfo::new(
2004 other, "other"
2005 )))),
2006 "SQLSTATE {other} 不应触发事务重试"
2007 );
2008 }
2009 assert!(!is_serialization_conflict(&DbError::TransactionError(
2010 "local".to_string()
2011 )));
2012 }
2013
2014 #[test]
2015 fn retry_backoff_grows_exponentially_and_caps() {
2016 use std::time::Duration;
2017
2018 assert_eq!(retry_backoff(1), Duration::from_millis(10));
2019 assert_eq!(retry_backoff(2), Duration::from_millis(20));
2020 assert_eq!(retry_backoff(3), Duration::from_millis(40));
2021 assert_eq!(retry_backoff(7), Duration::from_millis(640));
2022 assert_eq!(retry_backoff(8), Duration::from_millis(1_000));
2023 assert_eq!(retry_backoff(100), Duration::from_millis(1_000));
2024 }
2025
2026 #[test]
2027 fn explain_statement_prefixes_trusted_sql_and_rejects_empty() {
2028 assert_eq!(
2029 explain_statement("SELECT $1::int + 1").unwrap(),
2030 "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT $1::int + 1"
2031 );
2032 assert_eq!(
2033 explain_statement(" SELECT 1; ").unwrap(),
2034 "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT 1"
2035 );
2036 for invalid in ["", " ", " ; "] {
2037 assert!(matches!(
2038 explain_statement(invalid),
2039 Err(DbError::InvalidArgument(_))
2040 ));
2041 }
2042 }
2043
2044 #[test]
2045 fn explain_rows_are_flattened_to_pretty_json_per_plan() {
2046 let rows = vec![
2047 (serde_json::json!([
2048 {"Plan": {"Node Type": "Result"}, "Execution Time": 0.1}
2049 ]),),
2050 (serde_json::json!([{"Plan": {"Node Type": "Seq Scan"}}]),),
2051 ];
2052 let plans = explain_rows_to_strings(rows).unwrap();
2053 assert_eq!(plans.len(), 2);
2054 for plan in &plans {
2055 let parsed: serde_json::Value = serde_json::from_str(plan).unwrap();
2056 assert!(parsed.get("Plan").is_some(), "计划文本应可解析为 JSON");
2057 }
2058 assert!(plans[0].contains("\"Node Type\": \"Result\""));
2059
2060 let plans = explain_rows_to_strings(vec![(serde_json::json!({"Plan": {}}),)]).unwrap();
2062 assert_eq!(plans.len(), 1);
2063 assert!(plans[0].contains("Plan"));
2064 }
2065
2066 #[tokio::test]
2068 async fn acquire_with_cancel_token_requires_at_least_two_connections() {
2069 let pool = sqlx::postgres::PgPoolOptions::new()
2070 .max_connections(1)
2071 .connect_lazy("postgres://postgres:test@127.0.0.1:5432/test")
2072 .unwrap();
2073 let db = Database {
2074 pool,
2075 config: DatabaseConfig {
2076 max_connections: 1,
2077 ..DatabaseConfig::default()
2078 },
2079 server_version: OnceCell::new(),
2080 waiting: Arc::new(AtomicUsize::new(0)),
2081 };
2082 assert!(matches!(
2083 db.acquire_with_cancel_token().await,
2084 Err(DbError::InvalidArgument(_))
2085 ));
2086 }
2087
2088 #[test]
2090 fn cancel_token_sql_and_57014_mapping() {
2091 use crate::error::{DbErrorCategory, SqlErrorInfo};
2092
2093 assert_eq!(CANCEL_BACKEND_SQL, "SELECT pg_cancel_backend($1)");
2094 assert_eq!(TERMINATE_BACKEND_SQL, "SELECT pg_terminate_backend($1)");
2095 assert!(CANCEL_BACKEND_SQL.contains("$1"));
2096 assert!(TERMINATE_BACKEND_SQL.contains("$1"));
2097
2098 let canceled = DbError::DatabaseError(Box::new(SqlErrorInfo::new(
2099 "57014",
2100 "canceling statement due to user request",
2101 )));
2102 assert_eq!(canceled.sqlstate(), Some("57014"));
2103 assert_eq!(canceled.category(), DbErrorCategory::Timeout);
2104 assert!(!canceled.is_retryable());
2105 }
2106
2107 #[test]
2109 fn advisory_lock_sql_covers_shared_and_exclusive() {
2110 assert_eq!(
2111 AdvisoryLockMode::Exclusive.lock_sql(),
2112 "SELECT pg_advisory_lock($1)"
2113 );
2114 assert_eq!(
2115 AdvisoryLockMode::Shared.lock_sql(),
2116 "SELECT pg_advisory_lock_shared($1)"
2117 );
2118 assert_eq!(
2119 AdvisoryLockMode::Exclusive.try_lock_sql(),
2120 "SELECT pg_try_advisory_lock($1)"
2121 );
2122 assert_eq!(
2123 AdvisoryLockMode::Shared.try_lock_sql(),
2124 "SELECT pg_try_advisory_lock_shared($1)"
2125 );
2126 assert_eq!(
2127 AdvisoryLockMode::Exclusive.unlock_sql(),
2128 "SELECT pg_advisory_unlock($1)"
2129 );
2130 assert_eq!(
2131 AdvisoryLockMode::Shared.unlock_sql(),
2132 "SELECT pg_advisory_unlock_shared($1)"
2133 );
2134 }
2135
2136 #[tokio::test]
2138 #[ignore = "需要真实 PostgreSQL 实例"]
2139 async fn advisory_unlock_false_detaches_instead_of_returning_connection() {
2140 let url = std::env::var("PG_TEST_URL")
2141 .unwrap_or_else(|_| "postgres://postgres:postgres@127.0.0.1:55432/test".to_string());
2142 let db = Database::connect(&url).await.expect("PG 必须可连接");
2143 let conn = db.acquire().await.expect("借出连接应成功");
2144 assert_eq!(db.pool.size(), 1);
2145
2146 let lock = PgAdvisoryLock {
2148 conn: Some(conn),
2149 key: 987_654_321,
2150 mode: AdvisoryLockMode::Exclusive,
2151 };
2152 assert!(!lock.unlock().await.expect("解锁调用应成功"));
2153 assert_eq!(
2154 db.pool.size(),
2155 0,
2156 "unlock 返回 false 时必须 detach 关闭会话,而不是把连接还池"
2157 );
2158 }
2159
2160 #[test]
2162 fn runtime_config_defaults_and_setters() {
2163 let default = DatabaseConfig::default();
2164 assert!(default.application_name.is_none());
2165 assert!(default.statement_cache_size.is_none());
2166 assert!(!default.reset_on_release);
2167 assert!(default.require_utf8_client_encoding);
2168 assert!(default.ssl_mode.is_none());
2169
2170 let config = DatabaseConfig::default()
2171 .with_application_name("yang-test")
2172 .with_statement_cache_size(0)
2173 .with_reset_on_release(true)
2174 .with_require_utf8_client_encoding(false);
2175 assert_eq!(config.application_name.as_deref(), Some("yang-test"));
2176 assert_eq!(config.statement_cache_size, Some(0));
2177 assert!(config.reset_on_release);
2178 assert!(!config.require_utf8_client_encoding);
2179 assert!(config.validate().is_ok());
2180
2181 assert_eq!(DISCARD_ALL_SQL, "DISCARD ALL");
2182 }
2183
2184 #[test]
2185 fn application_name_is_validated() {
2186 for invalid in [String::new(), "a\0b".to_string(), "a".repeat(64)] {
2187 let config = DatabaseConfig::default().with_application_name(invalid.clone());
2188 assert!(
2189 matches!(config.validate(), Err(DbError::InvalidArgument(_))),
2190 "application_name 应拒绝 {invalid:?}"
2191 );
2192 }
2193 let config = DatabaseConfig::default().with_application_name("a".repeat(63));
2195 assert!(config.validate().is_ok());
2196 }
2197
2198 #[test]
2200 fn ssl_config_validation_and_mode_mapping() {
2201 assert!(matches!(
2202 SslMode::Disable.as_pg_ssl_mode(),
2203 PgSslMode::Disable
2204 ));
2205 assert!(matches!(
2206 SslMode::Prefer.as_pg_ssl_mode(),
2207 PgSslMode::Prefer
2208 ));
2209 assert!(matches!(
2210 SslMode::Require.as_pg_ssl_mode(),
2211 PgSslMode::Require
2212 ));
2213 assert!(matches!(
2214 SslMode::VerifyCa.as_pg_ssl_mode(),
2215 PgSslMode::VerifyCa
2216 ));
2217 assert!(matches!(
2218 SslMode::VerifyFull.as_pg_ssl_mode(),
2219 PgSslMode::VerifyFull
2220 ));
2221
2222 for mode in [SslMode::VerifyCa, SslMode::VerifyFull] {
2224 let missing = DatabaseConfig::default().with_ssl_mode(Some(mode));
2225 assert!(
2226 matches!(missing.validate(), Err(DbError::InvalidArgument(_))),
2227 "{mode:?} 缺 ssl_root_cert 应报错"
2228 );
2229 let with_root = DatabaseConfig::default()
2230 .with_ssl_mode(Some(mode))
2231 .with_ssl_root_cert("/etc/ssl/root.pem");
2232 assert!(with_root.validate().is_ok());
2233 }
2234
2235 assert!(matches!(
2237 DatabaseConfig::default()
2238 .with_ssl_mode(Some(SslMode::Disable))
2239 .with_ssl_root_cert("/etc/ssl/root.pem")
2240 .validate(),
2241 Err(DbError::InvalidArgument(_))
2242 ));
2243
2244 assert!(matches!(
2246 DatabaseConfig::default()
2247 .with_ssl_mode(Some(SslMode::Require))
2248 .with_ssl_client_cert("/etc/ssl/client.pem")
2249 .validate(),
2250 Err(DbError::InvalidArgument(_))
2251 ));
2252 assert!(DatabaseConfig::default()
2253 .with_ssl_mode(Some(SslMode::Require))
2254 .with_ssl_client_cert("/etc/ssl/client.pem")
2255 .with_ssl_client_key("/etc/ssl/client.key")
2256 .validate()
2257 .is_ok());
2258
2259 assert!(DatabaseConfig::default().effective_ssl_mode().is_none());
2261 assert_eq!(
2263 DatabaseConfig::default()
2264 .with_ssl_root_cert("/etc/ssl/root.pem")
2265 .effective_ssl_mode(),
2266 Some(SslMode::VerifyCa)
2267 );
2268 for mode in [
2270 SslMode::Disable,
2271 SslMode::Prefer,
2272 SslMode::Require,
2273 SslMode::VerifyCa,
2274 SslMode::VerifyFull,
2275 ] {
2276 assert_eq!(
2277 DatabaseConfig::default()
2278 .with_ssl_mode(Some(mode))
2279 .effective_ssl_mode(),
2280 Some(mode)
2281 );
2282 }
2283 assert!(DatabaseConfig::default()
2285 .with_ssl_root_cert("/etc/ssl/root.pem")
2286 .validate()
2287 .is_ok());
2288
2289 for mode in [SslMode::Prefer, SslMode::Require] {
2292 assert!(
2293 matches!(
2294 DatabaseConfig::default()
2295 .with_ssl_mode(Some(mode))
2296 .with_ssl_root_cert("/etc/ssl/root.pem")
2297 .validate(),
2298 Err(DbError::InvalidArgument(_))
2299 ),
2300 "{mode:?} + ssl_root_cert 应报错(根证书不会被该校验模式使用)"
2301 );
2302 }
2303 }
2304
2305 #[test]
2307 fn client_encoding_must_be_utf8() {
2308 for ok in ["UTF8", "utf8", "Utf-8", "UNICODE", " UTF8 "] {
2309 assert!(client_encoding_is_utf8(ok), "{ok:?} 应视为 UTF8");
2310 }
2311 for bad in ["SQL_ASCII", "LATIN1", "WIN1252", "EUC_JP", ""] {
2312 assert!(!client_encoding_is_utf8(bad), "{bad:?} 不应视为 UTF8");
2313 }
2314 }
2315
2316 #[tokio::test]
2318 async fn waiting_counter_is_raii_and_reflected_in_pool_status() -> Result<(), sqlx::Error> {
2319 let counter = AtomicUsize::new(0);
2320 {
2321 counter.fetch_add(1, Ordering::Relaxed);
2322 let _waiter = AcquireWaiter {
2323 counter: &counter,
2324 armed: true,
2325 };
2326 assert_eq!(counter.load(Ordering::Relaxed), 1);
2327 }
2328 assert_eq!(counter.load(Ordering::Relaxed), 0);
2329
2330 {
2332 let _waiter = AcquireWaiter {
2333 counter: &counter,
2334 armed: false,
2335 };
2336 }
2337 assert_eq!(counter.load(Ordering::Relaxed), 0);
2338
2339 let pool = sqlx::postgres::PgPoolOptions::new()
2340 .connect_lazy("postgres://postgres:test@127.0.0.1:5432/test")?;
2341 let db = Database {
2342 pool,
2343 config: DatabaseConfig::default(),
2344 server_version: OnceCell::new(),
2345 waiting: Arc::new(AtomicUsize::new(3)),
2346 };
2347 assert_eq!(db.pool_status().waiting, 3);
2348 Ok(())
2349 }
2350}