1use crate::error::DbError;
2use crate::postgres::params::{json_signature, keyed_sql, PgUntypedNull};
3use crate::postgres::transaction::{
4 next_cursor_name, normalize_stream_sql, validate_prepared_gid, TransactionOptions,
5 DEFAULT_STREAM_BATCH_SIZE,
6};
7use crate::postgres::{QueryBuilder, Transaction};
8use crate::{BackendCapabilities, PoolStatus, POSTGRES_CAPABILITIES};
9use sqlx::postgres::{PgPool, PgSslMode};
10use std::collections::VecDeque;
11use std::path::PathBuf;
12use std::sync::atomic::{AtomicUsize, Ordering};
13use std::sync::Arc;
14use tokio::sync::OnceCell;
15
16const MAX_APPLICATION_NAME_BYTES: usize = 63;
18
19const DISCARD_ALL_SQL: &str = "DISCARD ALL";
21
22const CANCEL_BACKEND_SQL: &str = "SELECT pg_cancel_backend($1)";
24
25const TERMINATE_BACKEND_SQL: &str = "SELECT pg_terminate_backend($1)";
27
28#[derive(Debug, Clone, Copy, PartialEq, Eq)]
34#[non_exhaustive]
35pub enum SslMode {
36 Disable,
38 Prefer,
40 Require,
43 VerifyCa,
45 VerifyFull,
47}
48
49impl SslMode {
50 fn as_pg_ssl_mode(self) -> PgSslMode {
51 match self {
52 Self::Disable => PgSslMode::Disable,
53 Self::Prefer => PgSslMode::Prefer,
54 Self::Require => PgSslMode::Require,
55 Self::VerifyCa => PgSslMode::VerifyCa,
56 Self::VerifyFull => PgSslMode::VerifyFull,
57 }
58 }
59
60 fn requires_root_cert(self) -> bool {
62 matches!(self, Self::VerifyCa | Self::VerifyFull)
63 }
64}
65
66#[derive(Debug, Clone)]
89#[non_exhaustive]
90pub struct DatabaseConfig {
91 pub max_connections: u32,
93 pub connect_timeout: u64,
95 pub idle_timeout: u64,
97 pub enable_logging: bool,
99 pub min_connections: u32,
101 pub max_lifetime: Option<u64>,
103 pub test_before_acquire: bool,
105 pub search_path: Option<String>,
110 pub statement_timeout_ms: Option<u64>,
119 pub lock_timeout_ms: Option<u64>,
124 pub idle_in_transaction_timeout_ms: Option<u64>,
129 pub application_name: Option<String>,
134 pub statement_cache_size: Option<usize>,
139 pub reset_on_release: bool,
145 pub require_utf8_client_encoding: bool,
150 pub ssl_mode: Option<SslMode>,
157 pub ssl_root_cert: Option<PathBuf>,
162 pub ssl_client_cert: Option<PathBuf>,
164 pub ssl_client_key: Option<PathBuf>,
166}
167
168impl Default for DatabaseConfig {
169 fn default() -> Self {
170 Self {
171 max_connections: 10,
172 connect_timeout: 30,
173 idle_timeout: 600,
174 enable_logging: false,
175 min_connections: 0,
176 max_lifetime: None,
177 test_before_acquire: false,
178 search_path: None,
179 statement_timeout_ms: None,
180 lock_timeout_ms: None,
181 idle_in_transaction_timeout_ms: None,
182 application_name: None,
183 statement_cache_size: None,
184 reset_on_release: false,
185 require_utf8_client_encoding: true,
186 ssl_mode: None,
187 ssl_root_cert: None,
188 ssl_client_cert: None,
189 ssl_client_key: None,
190 }
191 }
192}
193
194impl DatabaseConfig {
195 pub fn with_min_connections(mut self, n: u32) -> Self {
197 self.min_connections = n;
198 self
199 }
200
201 pub fn with_max_lifetime(mut self, secs: Option<u64>) -> Self {
203 self.max_lifetime = secs;
204 self
205 }
206
207 pub fn with_test_before_acquire(mut self, enabled: bool) -> Self {
209 self.test_before_acquire = enabled;
210 self
211 }
212
213 pub fn with_search_path(mut self, search_path: impl Into<String>) -> Self {
218 self.search_path = Some(search_path.into());
219 self
220 }
221
222 pub fn with_statement_timeout_ms(mut self, timeout_ms: impl Into<Option<u64>>) -> Self {
224 self.statement_timeout_ms = timeout_ms.into();
225 self
226 }
227
228 pub fn with_lock_timeout_ms(mut self, timeout_ms: impl Into<Option<u64>>) -> Self {
230 self.lock_timeout_ms = timeout_ms.into();
231 self
232 }
233
234 pub fn with_idle_in_transaction_timeout_ms(
236 mut self,
237 timeout_ms: impl Into<Option<u64>>,
238 ) -> Self {
239 self.idle_in_transaction_timeout_ms = timeout_ms.into();
240 self
241 }
242
243 pub fn with_application_name(mut self, application_name: impl Into<String>) -> Self {
245 self.application_name = Some(application_name.into());
246 self
247 }
248
249 pub fn with_statement_cache_size(mut self, capacity: usize) -> Self {
251 self.statement_cache_size = Some(capacity);
252 self
253 }
254
255 pub fn with_reset_on_release(mut self, enabled: bool) -> Self {
257 self.reset_on_release = enabled;
258 self
259 }
260
261 pub fn with_require_utf8_client_encoding(mut self, enabled: bool) -> Self {
263 self.require_utf8_client_encoding = enabled;
264 self
265 }
266
267 pub fn with_ssl_mode(mut self, mode: Option<SslMode>) -> Self {
269 self.ssl_mode = mode;
270 self
271 }
272
273 pub fn with_ssl_root_cert(mut self, path: impl Into<PathBuf>) -> Self {
275 self.ssl_root_cert = Some(path.into());
276 self
277 }
278
279 pub fn effective_ssl_mode(&self) -> Option<SslMode> {
287 match self.ssl_mode {
288 Some(mode) => Some(mode),
289 None if self.ssl_root_cert.is_some() => Some(SslMode::VerifyCa),
290 None => None,
291 }
292 }
293
294 pub fn with_ssl_client_cert(mut self, path: impl Into<PathBuf>) -> Self {
296 self.ssl_client_cert = Some(path.into());
297 self
298 }
299
300 pub fn with_ssl_client_key(mut self, path: impl Into<PathBuf>) -> Self {
302 self.ssl_client_key = Some(path.into());
303 self
304 }
305
306 pub fn validate(&self) -> std::result::Result<(), DbError> {
311 if self.max_connections == 0 {
312 return Err(DbError::InvalidArgument(
313 "PostgreSQL max_connections 必须大于 0".to_string(),
314 ));
315 }
316 if self.min_connections > self.max_connections {
317 return Err(DbError::InvalidArgument(format!(
318 "PostgreSQL min_connections({}) 不能大于 max_connections({})",
319 self.min_connections, self.max_connections
320 )));
321 }
322 if self.connect_timeout == 0 {
323 return Err(DbError::InvalidArgument(
324 "PostgreSQL connect_timeout 必须大于 0 秒".to_string(),
325 ));
326 }
327 if self.idle_timeout == 0 {
328 return Err(DbError::InvalidArgument(
329 "PostgreSQL idle_timeout 必须大于 0 秒".to_string(),
330 ));
331 }
332 if self.idle_timeout <= self.connect_timeout {
333 return Err(DbError::InvalidArgument(format!(
334 "PostgreSQL idle_timeout({}) 必须大于 connect_timeout({})",
335 self.idle_timeout, self.connect_timeout
336 )));
337 }
338 if matches!(self.max_lifetime, Some(0)) {
339 return Err(DbError::InvalidArgument(
340 "PostgreSQL max_lifetime 为 Some 时必须大于 0 秒".to_string(),
341 ));
342 }
343 if let Some(search_path) = &self.search_path {
344 if search_path.trim().is_empty() {
345 return Err(DbError::InvalidArgument(
346 "PostgreSQL search_path 不能为空".to_string(),
347 ));
348 }
349 for segment in search_path.split(',') {
350 let segment = segment.trim();
351 if segment.is_empty() || crate::sql_types::Identifier::parse(segment).is_err() {
352 return Err(DbError::InvalidArgument(format!(
353 "PostgreSQL search_path 片段非法: {segment:?}"
354 )));
355 }
356 }
357 }
358 if let Some(application_name) = &self.application_name {
359 if application_name.is_empty() {
360 return Err(DbError::InvalidArgument(
361 "PostgreSQL application_name 不能为空字符串".to_string(),
362 ));
363 }
364 if application_name.contains('\0') {
365 return Err(DbError::InvalidArgument(
366 "PostgreSQL application_name 不能包含 NUL 字符".to_string(),
367 ));
368 }
369 if application_name.len() > MAX_APPLICATION_NAME_BYTES {
370 return Err(DbError::InvalidArgument(format!(
371 "PostgreSQL application_name 不能超过 {MAX_APPLICATION_NAME_BYTES} 字节,当前 {} 字节",
372 application_name.len()
373 )));
374 }
375 }
376 if let Some(mode) = self.ssl_mode {
377 if mode.requires_root_cert() && self.ssl_root_cert.is_none() {
378 return Err(DbError::InvalidArgument(format!(
379 "PostgreSQL ssl_mode = {mode:?} 必须提供 ssl_root_cert(CA 证书)"
380 )));
381 }
382 if self.ssl_root_cert.is_some()
383 && matches!(mode, SslMode::Prefer | SslMode::Require | SslMode::Disable)
384 {
385 return Err(DbError::InvalidArgument(format!(
386 "PostgreSQL ssl_mode = {mode:?} 不能与 ssl_root_cert 同时配置;根证书只适用于 \
387 VerifyCa/VerifyFull(ssl_mode 为 None 时会自动按 VerifyCa 使用根证书)"
388 )));
389 }
390 if mode == SslMode::Disable
391 && (self.ssl_root_cert.is_some()
392 || self.ssl_client_cert.is_some()
393 || self.ssl_client_key.is_some())
394 {
395 return Err(DbError::InvalidArgument(
396 "PostgreSQL ssl_mode = Disable 时不能配置 TLS 证书".to_string(),
397 ));
398 }
399 }
400 if self.ssl_client_cert.is_some() != self.ssl_client_key.is_some() {
401 return Err(DbError::InvalidArgument(
402 "PostgreSQL ssl_client_cert 与 ssl_client_key 必须成对提供".to_string(),
403 ));
404 }
405 Ok(())
406 }
407}
408
409pub struct Database {
435 pool: PgPool,
436 config: DatabaseConfig,
437 server_version: OnceCell<i32>,
439 waiting: Arc<AtomicUsize>,
441}
442
443impl Clone for Database {
444 fn clone(&self) -> Self {
445 Self {
446 pool: self.pool.clone(),
447 config: self.config.clone(),
448 server_version: OnceCell::new(),
449 waiting: Arc::clone(&self.waiting),
450 }
451 }
452}
453
454impl Database {
455 pub const fn capabilities() -> &'static BackendCapabilities {
457 &POSTGRES_CAPABILITIES
458 }
459
460 pub async fn connect(url: &str) -> Result<Self, DbError> {
469 Self::connect_with_config(url, DatabaseConfig::default()).await
470 }
471
472 pub async fn connect_with_config(url: &str, config: DatabaseConfig) -> Result<Self, DbError> {
474 use sqlx::postgres::PgPoolOptions;
475 use std::str::FromStr;
476 use std::time::Duration;
477
478 config.validate()?;
479
480 let mut options = PgPoolOptions::new()
482 .max_connections(config.max_connections)
483 .min_connections(config.min_connections)
484 .acquire_timeout(Duration::from_secs(config.connect_timeout))
485 .idle_timeout(Duration::from_secs(config.idle_timeout))
486 .test_before_acquire(config.test_before_acquire);
487 if let Some(secs) = config.max_lifetime {
488 options = options.max_lifetime(Duration::from_secs(secs));
489 }
490
491 let mut connect_options =
494 sqlx::postgres::PgConnectOptions::from_str(url).map_err(|error| {
495 DbError::ConnectionError(format!("无效的 PostgreSQL 连接串: {error}"))
496 })?;
497 if let Some(search_path) = &config.search_path {
498 let normalized = search_path
501 .split(',')
502 .map(str::trim)
503 .collect::<Vec<_>>()
504 .join(",");
505 connect_options = connect_options.options([("search_path", normalized)]);
506 }
507 if let Some(mode) = config.effective_ssl_mode() {
511 connect_options = connect_options.ssl_mode(mode.as_pg_ssl_mode());
512 }
513 if let Some(path) = &config.ssl_root_cert {
514 connect_options = connect_options.ssl_root_cert(path);
515 }
516 if let Some(path) = &config.ssl_client_cert {
517 connect_options = connect_options.ssl_client_cert(path);
518 }
519 if let Some(path) = &config.ssl_client_key {
520 connect_options = connect_options.ssl_client_key(path);
521 }
522 if let Some(capacity) = config.statement_cache_size {
523 connect_options = connect_options.statement_cache_capacity(capacity);
524 }
525
526 let statement_timeout_ms = config.statement_timeout_ms;
529 let lock_timeout_ms = config.lock_timeout_ms;
530 let idle_in_transaction_timeout_ms = config.idle_in_transaction_timeout_ms;
531 let timeout_statements = timeout_statements(
532 statement_timeout_ms,
533 lock_timeout_ms,
534 idle_in_transaction_timeout_ms,
535 );
536 let mut session_statements = vec![
539 "SET TimeZone = 'UTC'".to_string(),
540 "SET DateStyle = 'ISO, YMD'".to_string(),
541 "SET IntervalStyle = 'postgres'".to_string(),
542 "SET extra_float_digits = 3".to_string(),
543 "SET standard_conforming_strings = on".to_string(),
544 ];
545 session_statements.extend(timeout_statements);
546 let session_setup = SessionSetup {
547 application_name: config.application_name.clone(),
548 statements: session_statements,
549 require_utf8_client_encoding: config.require_utf8_client_encoding,
550 };
551 let after_connect_setup = session_setup.clone();
552 options = options.after_connect(move |conn, _meta| {
553 let setup = after_connect_setup.clone();
554 Box::pin(async move { setup.apply(conn).await })
555 });
556 if config.reset_on_release {
557 options = options.after_release(|conn, _meta| {
560 Box::pin(async move {
561 sqlx::query(DISCARD_ALL_SQL).execute(&mut *conn).await?;
562 sqlx::Connection::clear_cached_statements(&mut *conn).await?;
568 Ok(true)
569 })
570 });
571 let before_acquire_setup = session_setup;
572 options = options.before_acquire(move |conn, _meta| {
573 let setup = before_acquire_setup.clone();
574 Box::pin(async move { setup.apply(conn).await.map(|_| true) })
575 });
576 }
577
578 let pool = options.connect_with(connect_options).await?;
579
580 Ok(Self {
581 pool,
582 config,
583 server_version: OnceCell::new(),
584 waiting: Arc::new(AtomicUsize::new(0)),
585 })
586 }
587
588 pub fn from_pool(pool: PgPool, config: DatabaseConfig) -> Result<Self, DbError> {
600 config.validate()?;
601 Ok(Self {
602 pool,
603 config,
604 server_version: OnceCell::new(),
605 waiting: Arc::new(AtomicUsize::new(0)),
606 })
607 }
608
609 pub fn table(&self, table: &crate::TableRef) -> QueryBuilder<'_> {
611 QueryBuilder::new(&self.pool, table.as_str(), self.config.enable_logging)
612 }
613
614 pub fn pool(&self) -> &PgPool {
620 &self.pool
621 }
622
623 pub fn pool_status(&self) -> PoolStatus {
635 PoolStatus {
636 max_size: self.config.max_connections as usize,
637 size: self.pool.size() as usize,
638 available: self.pool.num_idle(),
639 waiting: self.waiting.load(Ordering::Relaxed),
640 }
641 }
642
643 fn track_acquire(&self) -> AcquireWaiter<'_> {
648 let would_wait = self.pool.num_idle() == 0
649 && self.pool.size() >= self.pool.options().get_max_connections();
650 if would_wait {
651 self.waiting.fetch_add(1, Ordering::Relaxed);
652 }
653 AcquireWaiter {
654 counter: &self.waiting,
655 armed: would_wait,
656 }
657 }
658
659 pub(crate) async fn acquire(
661 &self,
662 ) -> Result<sqlx::pool::PoolConnection<sqlx::Postgres>, DbError> {
663 let waiter = self.track_acquire();
664 let conn = self.pool.acquire().await?;
665 drop(waiter);
666 Ok(conn)
667 }
668
669 pub async fn health_check(&self) -> Result<bool, DbError> {
675 let mut conn = self.acquire().await?;
676 sqlx::query("SELECT 1").execute(&mut *conn).await?;
677 Ok(true)
678 }
679
680 pub async fn close(&self) {
684 self.pool.close().await;
685 }
686
687 pub fn is_closed(&self) -> bool {
689 self.pool.is_closed()
690 }
691
692 #[deprecated(
697 since = "0.1.0",
698 note = "使用 query_with_params 替代,避免 SQL 注入风险"
699 )]
700 pub async fn query<T>(&self, sql: &str) -> Result<Vec<T>, DbError>
701 where
702 T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
703 {
704 if self.config.enable_logging {
705 log::debug!("执行原生查询: {}", sql);
706 }
707
708 let rows = sqlx::query_as::<_, T>(sql).fetch_all(&self.pool).await?;
709
710 Ok(rows)
711 }
712
713 #[deprecated(
718 since = "0.1.0",
719 note = "使用 execute_with_params 替代,避免 SQL 注入风险"
720 )]
721 pub async fn execute(&self, sql: &str) -> Result<u64, DbError> {
722 if self.config.enable_logging {
723 log::debug!("执行原生语句: {}", sql);
724 }
725
726 let result = sqlx::query(sql).execute(&self.pool).await?;
727
728 Ok(result.rows_affected())
729 }
730
731 pub async fn transaction(&self) -> Result<Transaction, DbError> {
733 let waiter = self.track_acquire();
734 let tx = self.pool.begin().await?;
735 drop(waiter);
736 Ok(Transaction::new(tx, self.config.enable_logging))
737 }
738
739 pub async fn transaction_with_isolation(
744 &self,
745 isolation: crate::isolation::IsolationLevel,
746 ) -> Result<Transaction, DbError> {
747 self.transaction_with_options(TransactionOptions::default().with_isolation(isolation))
748 .await
749 }
750
751 pub async fn transaction_with_options(
757 &self,
758 options: TransactionOptions,
759 ) -> Result<Transaction, DbError> {
760 let waiter = self.track_acquire();
761 let mut tx = self.pool.begin().await?;
762 drop(waiter);
763 if let Some(sql) = options.set_transaction_sql() {
764 if self.config.enable_logging {
765 log::debug!("设置 PostgreSQL 事务属性: {}", sql);
766 }
767 sqlx::query(&sql).execute(&mut *tx).await?;
768 }
769 Ok(Transaction::new(tx, self.config.enable_logging))
770 }
771
772 pub async fn read_only_transaction(&self) -> Result<Transaction, DbError> {
774 self.transaction_with_options(TransactionOptions::read_only())
775 .await
776 }
777
778 pub async fn retry_transaction<T, F, Fut>(
790 &self,
791 operation: F,
792 max_attempts: u32,
793 ) -> Result<T, DbError>
794 where
795 F: FnMut(Transaction) -> Fut,
796 Fut: std::future::Future<Output = Result<T, DbError>>,
797 {
798 self.retry_transaction_with_options(TransactionOptions::default(), operation, max_attempts)
799 .await
800 }
801
802 pub async fn retry_transaction_with_options<T, F, Fut>(
804 &self,
805 options: TransactionOptions,
806 mut operation: F,
807 max_attempts: u32,
808 ) -> Result<T, DbError>
809 where
810 F: FnMut(Transaction) -> Fut,
811 Fut: std::future::Future<Output = Result<T, DbError>>,
812 {
813 if max_attempts == 0 {
814 return Err(DbError::InvalidArgument(
815 "重试次数 max_attempts 必须大于 0".to_string(),
816 ));
817 }
818
819 let mut attempt = 0u32;
820 loop {
821 attempt += 1;
822 let transaction = self.transaction_with_options(options).await?;
823 match operation(transaction).await {
824 Ok(value) => return Ok(value),
825 Err(error) => {
826 if !is_serialization_conflict(&error) || attempt >= max_attempts {
827 return Err(error);
828 }
829 let backoff = retry_backoff(attempt);
830 if self.config.enable_logging {
831 log::warn!(
832 "事务序列化冲突(第 {attempt} 次尝试),{:?} 后重试: {error}",
833 backoff
834 );
835 }
836 tokio::time::sleep(backoff).await;
837 }
838 }
839 }
840 }
841
842 #[deprecated(
847 since = "0.1.4",
848 note = "请使用 execute_script(整脚本单事务,正确处理函数/DO 块与 dollar-quote)"
849 )]
850 pub async fn init(&self, sql_script: &str) -> Result<(), DbError> {
851 self.execute_script(sql_script).await.map(|_| ())
852 }
853
854 #[deprecated(
859 since = "0.2.2",
860 note = "裸 SQL 建表逃生舱;请使用 execute_script(单事务、可含多条 DDL)或外部迁移工具"
861 )]
862 pub async fn create_table(&self, create_sql: &str) -> Result<(), DbError> {
863 let mut conn = self.acquire().await?;
864 sqlx::query(create_sql).execute(&mut *conn).await?;
865 Ok(())
866 }
867
868 pub async fn drop_table(&self, table_name: &crate::TableRef) -> Result<(), DbError> {
873 let quoted = crate::postgres::identifier::quote_table(table_name.as_str())?;
874 let sql = format!("DROP TABLE IF EXISTS {}", quoted);
875 let mut conn = self.acquire().await?;
876 sqlx::query(&sql).execute(&mut *conn).await?;
877 Ok(())
878 }
879
880 pub async fn table_exists(&self, table_name: &crate::TableRef) -> Result<bool, DbError> {
886 let quoted = crate::postgres::identifier::quote_table(table_name.as_str())?;
889 let exists: bool = sqlx::query_scalar("SELECT to_regclass($1) IS NOT NULL")
890 .bind(quoted)
891 .fetch_one(&self.pool)
892 .await?;
893
894 Ok(exists)
895 }
896
897 pub async fn query_with_params<T>(
921 &self,
922 sql: &str,
923 params: Vec<serde_json::Value>,
924 ) -> Result<Vec<T>, DbError>
925 where
926 T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
927 {
928 if self.config.enable_logging {
929 log::debug!("执行参数化查询: {}, 参数数量: {}", sql, params.len());
930 }
931
932 let mut conn = self.acquire().await?;
935 let statement = keyed_sql(sql, json_signature(¶ms));
936 let mut query = sqlx::query_as::<_, T>(&statement);
937 for param in ¶ms {
938 query = bind_json_param_as(query, param);
939 }
940
941 let rows = query.fetch_all(&mut *conn).await?;
942 Ok(rows)
943 }
944
945 pub async fn execute_with_params(
969 &self,
970 sql: &str,
971 params: Vec<serde_json::Value>,
972 ) -> Result<u64, DbError> {
973 if self.config.enable_logging {
974 log::debug!("执行参数化语句: {}, 参数数量: {}", sql, params.len());
975 }
976
977 let mut conn = self.acquire().await?;
980 let statement = keyed_sql(sql, json_signature(¶ms));
981 let mut query = sqlx::query(&statement);
982 for param in ¶ms {
983 query = bind_json_param(query, param);
984 }
985
986 let result = query.execute(&mut *conn).await?;
987 Ok(result.rows_affected())
988 }
989
990 pub async fn stream_query(
1010 &self,
1011 sql: &str,
1012 params: Vec<serde_json::Value>,
1013 ) -> Result<PgRowCursor, DbError> {
1014 self.stream_query_with_batch_size(sql, params, DEFAULT_STREAM_BATCH_SIZE)
1015 .await
1016 }
1017
1018 pub async fn stream_query_with_batch_size(
1020 &self,
1021 sql: &str,
1022 params: Vec<serde_json::Value>,
1023 batch_size: usize,
1024 ) -> Result<PgRowCursor, DbError> {
1025 if batch_size == 0 {
1026 return Err(DbError::InvalidArgument(
1027 "流式查询 batch_size 必须大于 0".to_string(),
1028 ));
1029 }
1030 let body = normalize_stream_sql(sql)?;
1031 let mut conn = self.acquire().await?;
1032 sqlx::query("BEGIN READ ONLY").execute(&mut *conn).await?;
1034
1035 let cursor_name = next_cursor_name();
1036 let declare = format!("DECLARE {} CURSOR FOR {}", cursor_name, body);
1037 if self.config.enable_logging {
1038 log::debug!("声明流式游标: {}", declare);
1039 }
1040 let keyed_declare = keyed_sql(&declare, json_signature(¶ms));
1041 let mut query = sqlx::query(&keyed_declare);
1042 for param in ¶ms {
1043 query = bind_json_param(query, param);
1044 }
1045 if let Err(error) = query.execute(&mut *conn).await {
1046 let _ = sqlx::query("ROLLBACK").execute(&mut *conn).await;
1048 return Err(error.into());
1049 }
1050
1051 Ok(PgRowCursor::new(
1052 conn,
1053 cursor_name,
1054 batch_size,
1055 self.config.enable_logging,
1056 ))
1057 }
1058
1059 pub async fn advisory_lock(&self, key: i64) -> Result<PgAdvisoryLock, DbError> {
1065 self.acquire_advisory_lock(key, AdvisoryLockMode::Exclusive)
1066 .await
1067 }
1068
1069 pub async fn advisory_lock_shared(&self, key: i64) -> Result<PgAdvisoryLock, DbError> {
1074 self.acquire_advisory_lock(key, AdvisoryLockMode::Shared)
1075 .await
1076 }
1077
1078 pub async fn try_advisory_lock(&self, key: i64) -> Result<Option<PgAdvisoryLock>, DbError> {
1082 self.try_acquire_advisory_lock(key, AdvisoryLockMode::Exclusive)
1083 .await
1084 }
1085
1086 pub async fn try_advisory_lock_shared(
1088 &self,
1089 key: i64,
1090 ) -> Result<Option<PgAdvisoryLock>, DbError> {
1091 self.try_acquire_advisory_lock(key, AdvisoryLockMode::Shared)
1092 .await
1093 }
1094
1095 async fn acquire_advisory_lock(
1096 &self,
1097 key: i64,
1098 mode: AdvisoryLockMode,
1099 ) -> Result<PgAdvisoryLock, DbError> {
1100 let mut conn = self.acquire().await?;
1101 if self.config.enable_logging {
1102 log::debug!("获取 PG 会话级 advisory lock: key={key}, mode={mode:?}");
1103 }
1104 sqlx::query(mode.lock_sql())
1105 .bind(key)
1106 .execute(&mut *conn)
1107 .await?;
1108 Ok(PgAdvisoryLock {
1109 conn: Some(conn),
1110 key,
1111 mode,
1112 })
1113 }
1114
1115 async fn try_acquire_advisory_lock(
1116 &self,
1117 key: i64,
1118 mode: AdvisoryLockMode,
1119 ) -> Result<Option<PgAdvisoryLock>, DbError> {
1120 let mut conn = self.acquire().await?;
1121 let acquired: bool = sqlx::query_scalar(mode.try_lock_sql())
1122 .bind(key)
1123 .fetch_one(&mut *conn)
1124 .await?;
1125 if acquired {
1126 Ok(Some(PgAdvisoryLock {
1127 conn: Some(conn),
1128 key,
1129 mode,
1130 }))
1131 } else {
1132 Ok(None)
1133 }
1134 }
1135
1136 pub async fn server_version_num(&self) -> Result<i32, DbError> {
1140 let version = self
1141 .server_version
1142 .get_or_try_init(|| async {
1143 let mut conn = self.acquire().await?;
1144 let value: i32 =
1145 sqlx::query_scalar("SELECT current_setting('server_version_num')::int")
1146 .fetch_one(&mut *conn)
1147 .await?;
1148 Ok::<i32, DbError>(value)
1149 })
1150 .await?;
1151 Ok(*version)
1152 }
1153
1154 pub async fn supports(&self, min_version: i32) -> Result<bool, DbError> {
1158 Ok(self.server_version_num().await? >= min_version)
1159 }
1160
1161 pub async fn prepare_transaction(
1167 &self,
1168 transaction: Transaction,
1169 gid: &str,
1170 ) -> Result<(), DbError> {
1171 transaction.prepare(gid).await
1172 }
1173
1174 pub async fn commit_prepared(&self, gid: &str) -> Result<(), DbError> {
1178 validate_prepared_gid(gid)?;
1179 let sql = format!("COMMIT PREPARED '{gid}'");
1180 if self.config.enable_logging {
1181 log::debug!("提交已准备事务: gid={gid}");
1182 }
1183 let mut conn = self.acquire().await?;
1184 sqlx::query(&sql).execute(&mut *conn).await?;
1185 Ok(())
1186 }
1187
1188 pub async fn rollback_prepared(&self, gid: &str) -> Result<(), DbError> {
1192 validate_prepared_gid(gid)?;
1193 let sql = format!("ROLLBACK PREPARED '{gid}'");
1194 if self.config.enable_logging {
1195 log::debug!("回滚已准备事务: gid={gid}");
1196 }
1197 let mut conn = self.acquire().await?;
1198 sqlx::query(&sql).execute(&mut *conn).await?;
1199 Ok(())
1200 }
1201
1202 pub async fn explain(
1210 &self,
1211 sql: &str,
1212 params: Vec<serde_json::Value>,
1213 ) -> Result<Vec<String>, DbError> {
1214 let statement = explain_statement(sql)?;
1215 let keyed_statement = keyed_sql(&statement, json_signature(¶ms));
1216 if self.config.enable_logging {
1217 log::debug!("执行 EXPLAIN: {}, 参数数量: {}", statement, params.len());
1218 }
1219 let mut query = sqlx::query_as::<_, (serde_json::Value,)>(&keyed_statement);
1220 for param in ¶ms {
1221 query = bind_json_param_as(query, param);
1222 }
1223 let mut conn = self.acquire().await?;
1224 let rows = query.fetch_all(&mut *conn).await?;
1225 explain_rows_to_strings(rows)
1226 }
1227
1228 pub async fn active_queries(&self) -> Result<Vec<ActiveQuery>, DbError> {
1233 let mut conn = self.acquire().await?;
1234 let rows = sqlx::query_as::<_, ActiveQuery>(
1235 "SELECT pid,
1236 usename::text AS usename,
1237 application_name,
1238 host(client_addr) AS client_addr,
1239 state,
1240 wait_event_type,
1241 wait_event,
1242 query_start,
1243 query
1244 FROM pg_stat_activity
1245 ORDER BY query_start DESC NULLS LAST, pid",
1246 )
1247 .fetch_all(&mut *conn)
1248 .await?;
1249 Ok(rows)
1250 }
1251
1252 pub async fn lock_waits(&self) -> Result<Vec<LockWait>, DbError> {
1257 let mut conn = self.acquire().await?;
1258 let rows = sqlx::query_as::<_, LockWait>(
1259 "SELECT blocked.pid AS blocked_pid,
1260 blocking.pid AS blocking_pid,
1261 blocked.query AS blocked_query,
1262 blocking.query AS blocking_query,
1263 CASE
1264 WHEN l.relation IS NULL OR l.relation = 0 THEN NULL
1265 ELSE l.relation::regclass::text
1266 END AS relation,
1267 l.locktype AS lock_type,
1268 l.mode AS mode,
1269 blocked.wait_event AS wait_event
1270 FROM pg_locks l
1271 JOIN pg_stat_activity blocked ON blocked.pid = l.pid
1272 CROSS JOIN LATERAL unnest(pg_blocking_pids(l.pid)) AS bp(blocking_pid)
1273 JOIN pg_stat_activity blocking ON blocking.pid = bp.blocking_pid
1274 WHERE NOT l.granted
1275 ORDER BY blocked.pid, blocking.pid",
1276 )
1277 .fetch_all(&mut *conn)
1278 .await?;
1279 Ok(rows)
1280 }
1281
1282 pub async fn acquire_with_cancel_token(
1294 &self,
1295 ) -> Result<(sqlx::pool::PoolConnection<sqlx::Postgres>, CancelToken), DbError> {
1296 if self.config.max_connections < 2 {
1297 return Err(DbError::InvalidArgument(format!(
1298 "PostgreSQL 取消令牌需要至少 2 条连接(取消要另借一条连接),当前 \
1299 max_connections = {}",
1300 self.config.max_connections
1301 )));
1302 }
1303 let mut conn = self.acquire().await?;
1304 let backend_pid: i32 = sqlx::query_scalar("SELECT pg_backend_pid()")
1305 .fetch_one(&mut *conn)
1306 .await?;
1307 let token = CancelToken {
1308 pool: self.pool.clone(),
1309 backend_pid,
1310 };
1311 Ok((conn, token))
1312 }
1313
1314 pub async fn reset_connection(&self) -> Result<(), DbError> {
1323 let mut conn = self.acquire().await?;
1324 sqlx::query(DISCARD_ALL_SQL).execute(&mut *conn).await?;
1325 sqlx::Connection::clear_cached_statements(&mut *conn).await?;
1326 Ok(())
1327 }
1328}
1329
1330#[derive(Debug, Clone)]
1340pub struct CancelToken {
1341 pool: PgPool,
1342 backend_pid: i32,
1343}
1344
1345impl CancelToken {
1346 pub fn backend_pid(&self) -> i32 {
1348 self.backend_pid
1349 }
1350
1351 pub async fn cancel(&self) -> Result<bool, DbError> {
1356 Ok(sqlx::query_scalar(CANCEL_BACKEND_SQL)
1357 .bind(self.backend_pid)
1358 .fetch_one(&self.pool)
1359 .await?)
1360 }
1361
1362 pub async fn terminate(&self) -> Result<bool, DbError> {
1364 Ok(sqlx::query_scalar(TERMINATE_BACKEND_SQL)
1365 .bind(self.backend_pid)
1366 .fetch_one(&self.pool)
1367 .await?)
1368 }
1369}
1370
1371#[derive(Debug, Clone, sqlx::FromRow)]
1373pub struct ActiveQuery {
1374 pub pid: i32,
1376 pub usename: Option<String>,
1378 pub application_name: Option<String>,
1380 pub client_addr: Option<String>,
1382 pub state: Option<String>,
1384 pub wait_event_type: Option<String>,
1386 pub wait_event: Option<String>,
1388 pub query_start: Option<chrono::DateTime<chrono::Utc>>,
1390 pub query: String,
1392}
1393
1394#[derive(Debug, Clone, sqlx::FromRow)]
1396pub struct LockWait {
1397 pub blocked_pid: i32,
1399 pub blocking_pid: i32,
1401 pub blocked_query: Option<String>,
1403 pub blocking_query: Option<String>,
1405 pub relation: Option<String>,
1407 pub lock_type: Option<String>,
1409 pub mode: Option<String>,
1411 pub wait_event: Option<String>,
1413}
1414
1415fn explain_statement(sql: &str) -> Result<String, DbError> {
1417 let trimmed = sql.trim().trim_end_matches(';').trim_end();
1418 if trimmed.is_empty() {
1419 return Err(DbError::InvalidArgument(
1420 "EXPLAIN 的 SQL 不能为空".to_string(),
1421 ));
1422 }
1423 Ok(format!("EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) {trimmed}"))
1424}
1425
1426fn explain_rows_to_strings(rows: Vec<(serde_json::Value,)>) -> Result<Vec<String>, DbError> {
1431 let mut plans = Vec::new();
1432 for (row,) in rows {
1433 let items = match row {
1434 serde_json::Value::Array(items) => items,
1435 other => vec![other],
1436 };
1437 for item in items {
1438 plans.push(serde_json::to_string_pretty(&item).map_err(|error| {
1439 DbError::SerializationError(format!("EXPLAIN 计划序列化失败: {error}"))
1440 })?);
1441 }
1442 }
1443 Ok(plans)
1444}
1445
1446fn timeout_statements(
1450 statement_timeout_ms: Option<u64>,
1451 lock_timeout_ms: Option<u64>,
1452 idle_in_transaction_timeout_ms: Option<u64>,
1453) -> Vec<String> {
1454 let mut statements = Vec::new();
1455 if let Some(ms) = statement_timeout_ms {
1456 statements.push(format!("SET statement_timeout = {ms}"));
1457 }
1458 if let Some(ms) = lock_timeout_ms {
1459 statements.push(format!("SET lock_timeout = {ms}"));
1460 }
1461 if let Some(ms) = idle_in_transaction_timeout_ms {
1462 statements.push(format!("SET idle_in_transaction_session_timeout = {ms}"));
1463 }
1464 statements
1465}
1466
1467fn is_serialization_conflict(error: &DbError) -> bool {
1469 matches!(error.sqlstate(), Some("40001") | Some("40P01"))
1470}
1471
1472fn retry_backoff(attempt: u32) -> std::time::Duration {
1474 let exponent = attempt.saturating_sub(1).min(7);
1475 std::time::Duration::from_millis((10u64 << exponent).min(1_000))
1476}
1477
1478struct AcquireWaiter<'a> {
1480 counter: &'a AtomicUsize,
1481 armed: bool,
1482}
1483
1484impl Drop for AcquireWaiter<'_> {
1485 fn drop(&mut self) {
1486 if self.armed {
1487 self.counter.fetch_sub(1, Ordering::Relaxed);
1488 }
1489 }
1490}
1491
1492#[derive(Debug, Clone)]
1494struct SessionSetup {
1495 application_name: Option<String>,
1497 statements: Vec<String>,
1499 require_utf8_client_encoding: bool,
1501}
1502
1503impl SessionSetup {
1504 async fn apply(&self, conn: &mut sqlx::PgConnection) -> Result<(), sqlx::Error> {
1509 if let Some(application_name) = &self.application_name {
1510 sqlx::query("SELECT set_config('application_name', $1, false)")
1511 .bind(application_name)
1512 .execute(&mut *conn)
1513 .await?;
1514 }
1515 for sql in &self.statements {
1516 sqlx::query(sql).execute(&mut *conn).await?;
1517 }
1518 if self.require_utf8_client_encoding {
1519 let encoding: String = sqlx::query_scalar("SELECT current_setting('client_encoding')")
1520 .fetch_one(&mut *conn)
1521 .await?;
1522 if !client_encoding_is_utf8(&encoding) {
1523 return Err(sqlx::Error::Configuration(
1524 format!(
1525 "PostgreSQL client_encoding 必须为 UTF8,当前为 {encoding:?};\
1526 如确需其他编码请设置 DatabaseConfig::with_require_utf8_client_encoding(false)"
1527 )
1528 .into(),
1529 ));
1530 }
1531 }
1532 Ok(())
1533 }
1534}
1535
1536fn client_encoding_is_utf8(encoding: &str) -> bool {
1538 matches!(
1539 encoding.trim().to_ascii_uppercase().as_str(),
1540 "UTF8" | "UTF-8" | "UNICODE"
1541 )
1542}
1543
1544#[derive(Debug, Clone, Copy, PartialEq, Eq)]
1546enum AdvisoryLockMode {
1547 Exclusive,
1548 Shared,
1549}
1550
1551impl AdvisoryLockMode {
1552 fn lock_sql(self) -> &'static str {
1554 match self {
1555 Self::Exclusive => "SELECT pg_advisory_lock($1)",
1556 Self::Shared => "SELECT pg_advisory_lock_shared($1)",
1557 }
1558 }
1559
1560 fn try_lock_sql(self) -> &'static str {
1562 match self {
1563 Self::Exclusive => "SELECT pg_try_advisory_lock($1)",
1564 Self::Shared => "SELECT pg_try_advisory_lock_shared($1)",
1565 }
1566 }
1567
1568 fn unlock_sql(self) -> &'static str {
1570 match self {
1571 Self::Exclusive => "SELECT pg_advisory_unlock($1)",
1572 Self::Shared => "SELECT pg_advisory_unlock_shared($1)",
1573 }
1574 }
1575}
1576
1577pub struct PgAdvisoryLock {
1584 conn: Option<sqlx::pool::PoolConnection<sqlx::Postgres>>,
1585 key: i64,
1586 mode: AdvisoryLockMode,
1587}
1588
1589impl PgAdvisoryLock {
1590 pub fn key(&self) -> i64 {
1592 self.key
1593 }
1594
1595 pub fn is_shared(&self) -> bool {
1597 self.mode == AdvisoryLockMode::Shared
1598 }
1599
1600 pub async fn unlock(mut self) -> Result<bool, DbError> {
1607 let mode = self.mode;
1608 let conn = self
1609 .conn
1610 .as_mut()
1611 .ok_or_else(|| DbError::TransactionError("advisory lock 已释放".to_string()))?;
1612 let unlocked: bool = sqlx::query_scalar(mode.unlock_sql())
1613 .bind(self.key)
1614 .fetch_one(&mut **conn)
1615 .await?;
1616 if unlocked {
1617 self.conn.take();
1619 } else {
1620 if let Some(conn) = self.conn.take() {
1622 let _ = conn.detach();
1623 }
1624 }
1625 Ok(unlocked)
1626 }
1627
1628 pub async fn advisory_unlock(self) -> Result<bool, DbError> {
1634 self.unlock().await
1635 }
1636}
1637
1638impl Drop for PgAdvisoryLock {
1639 fn drop(&mut self) {
1640 if let Some(conn) = self.conn.take() {
1641 log::warn!(
1642 "PG 会话级 advisory lock({}) 未显式 unlock 即被丢弃;关闭连接以让服务端回收锁",
1643 self.key
1644 );
1645 let _ = conn.detach();
1646 }
1647 }
1648}
1649
1650pub struct PgRowCursor {
1654 conn: Option<sqlx::pool::PoolConnection<sqlx::Postgres>>,
1655 cursor_name: String,
1656 batch_size: usize,
1657 buffer: VecDeque<sqlx::postgres::PgRow>,
1658 exhausted: bool,
1659 closed: bool,
1660 enable_logging: bool,
1661}
1662
1663impl PgRowCursor {
1664 fn new(
1665 conn: sqlx::pool::PoolConnection<sqlx::Postgres>,
1666 cursor_name: String,
1667 batch_size: usize,
1668 enable_logging: bool,
1669 ) -> Self {
1670 Self {
1671 conn: Some(conn),
1672 cursor_name,
1673 batch_size,
1674 buffer: VecDeque::with_capacity(batch_size.min(1024)),
1675 exhausted: false,
1676 closed: false,
1677 enable_logging,
1678 }
1679 }
1680
1681 pub fn batch_size(&self) -> usize {
1683 self.batch_size
1684 }
1685
1686 pub async fn next(&mut self) -> Result<Option<sqlx::postgres::PgRow>, DbError> {
1688 loop {
1689 if let Some(row) = self.buffer.pop_front() {
1690 return Ok(Some(row));
1691 }
1692 if self.exhausted {
1693 self.release().await?;
1694 return Ok(None);
1695 }
1696 self.fetch_batch().await?;
1697 }
1698 }
1699
1700 pub async fn close(mut self) -> Result<(), DbError> {
1702 self.buffer.clear();
1703 self.release().await
1704 }
1705
1706 async fn fetch_batch(&mut self) -> Result<(), DbError> {
1707 let conn = self
1708 .conn
1709 .as_mut()
1710 .ok_or_else(|| DbError::TransactionError("流式游标已关闭".to_string()))?;
1711 let sql = format!(
1712 "FETCH FORWARD {} FROM {}",
1713 self.batch_size, self.cursor_name
1714 );
1715 if self.enable_logging {
1716 log::debug!("游标拉取: {}", sql);
1717 }
1718 let rows = sqlx::query(&sql).fetch_all(&mut **conn).await?;
1719 if rows.len() < self.batch_size {
1720 self.exhausted = true;
1721 }
1722 self.buffer.extend(rows);
1723 Ok(())
1724 }
1725
1726 async fn release(&mut self) -> Result<(), DbError> {
1728 if self.closed {
1729 return Ok(());
1730 }
1731 self.closed = true;
1732 self.exhausted = true;
1733
1734 if let Some(mut conn) = self.conn.take() {
1735 let close_sql = format!("CLOSE {}", self.cursor_name);
1736 let close_ok = sqlx::query(&close_sql).execute(&mut *conn).await.is_ok();
1737 let rollback_ok = sqlx::query("ROLLBACK").execute(&mut *conn).await.is_ok();
1738 if !(close_ok && rollback_ok) {
1739 let _ = conn.detach();
1741 return Err(DbError::TransactionError(
1742 "流式游标关闭失败,连接已丢弃".to_string(),
1743 ));
1744 }
1745 }
1746 Ok(())
1747 }
1748}
1749
1750impl Drop for PgRowCursor {
1751 fn drop(&mut self) {
1752 if let Some(conn) = self.conn.take() {
1753 let _ = conn.detach();
1755 }
1756 }
1757}
1758
1759macro_rules! bind_json_value {
1774 ($query:expr, $param:expr) => {
1775 match $param {
1776 serde_json::Value::String(s) => $query.bind(s.clone()),
1778 serde_json::Value::Number(n) => match crate::json_param::classify_json_number(n) {
1780 Some(crate::json_param::JsonNumber::Int(i)) => $query.bind(i),
1781 Some(crate::json_param::JsonNumber::Uint(u)) => $query.bind(u.to_string()),
1782 Some(crate::json_param::JsonNumber::Float(f)) => $query.bind(f),
1783 None => $query.bind(PgUntypedNull),
1784 },
1785 serde_json::Value::Bool(b) => $query.bind(*b),
1787 serde_json::Value::Null => $query.bind(PgUntypedNull),
1789 other => $query.bind(other.clone()),
1791 }
1792 };
1793}
1794
1795fn bind_json_param_as<'q, T>(
1804 query: sqlx::query::QueryAs<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>,
1805 param: &serde_json::Value,
1806) -> sqlx::query::QueryAs<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>
1807where
1808 T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow>,
1809{
1810 bind_json_value!(query, param)
1811}
1812
1813fn bind_json_param<'q>(
1822 query: sqlx::query::Query<'q, sqlx::Postgres, sqlx::postgres::PgArguments>,
1823 param: &serde_json::Value,
1824) -> sqlx::query::Query<'q, sqlx::Postgres, sqlx::postgres::PgArguments> {
1825 bind_json_value!(query, param)
1826}
1827
1828#[cfg(test)]
1829#[allow(clippy::unwrap_used, clippy::expect_used)]
1830mod tests {
1831 use super::*;
1832
1833 #[test]
1834 fn test_database_config_validate_accepts_default_config() {
1835 let config = DatabaseConfig::default();
1836
1837 assert!(config.validate().is_ok());
1838 }
1839
1840 #[test]
1841 fn test_database_config_validate_rejects_invalid_pool_size() {
1842 assert!(matches!(
1843 DatabaseConfig {
1844 max_connections: 0,
1845 ..DatabaseConfig::default()
1846 }
1847 .validate(),
1848 Err(DbError::InvalidArgument(_))
1849 ));
1850
1851 assert!(matches!(
1852 DatabaseConfig {
1853 max_connections: 2,
1854 min_connections: 3,
1855 ..DatabaseConfig::default()
1856 }
1857 .validate(),
1858 Err(DbError::InvalidArgument(_))
1859 ));
1860 }
1861
1862 #[test]
1863 fn test_database_config_validate_rejects_invalid_timeouts() {
1864 for config in [
1865 DatabaseConfig {
1866 connect_timeout: 0,
1867 ..DatabaseConfig::default()
1868 },
1869 DatabaseConfig {
1870 idle_timeout: 0,
1871 ..DatabaseConfig::default()
1872 },
1873 DatabaseConfig {
1874 max_lifetime: Some(0),
1875 ..DatabaseConfig::default()
1876 },
1877 ] {
1878 assert!(matches!(
1879 config.validate(),
1880 Err(DbError::InvalidArgument(_))
1881 ));
1882 }
1883
1884 assert!(matches!(
1885 DatabaseConfig {
1886 connect_timeout: 30,
1887 idle_timeout: 30,
1888 ..DatabaseConfig::default()
1889 }
1890 .validate(),
1891 Err(DbError::InvalidArgument(_))
1892 ));
1893 }
1894
1895 #[tokio::test]
1896 async fn test_connect_with_config_rejects_invalid_config_before_connecting() {
1897 let config = DatabaseConfig {
1898 max_connections: 0,
1899 ..DatabaseConfig::default()
1900 };
1901 let result =
1902 Database::connect_with_config("postgres://postgres:bad@127.0.0.1:1/test", config).await;
1903
1904 assert!(matches!(result, Err(DbError::InvalidArgument(_))));
1905 }
1906
1907 #[test]
1908 fn test_search_path_validation_accepts_identifier_lists_only() {
1909 assert!(DatabaseConfig::default().search_path.is_none());
1910
1911 let config = DatabaseConfig::default().with_search_path("app, public");
1912 assert_eq!(config.search_path.as_deref(), Some("app, public"));
1913 assert!(config.validate().is_ok());
1914
1915 for invalid in [
1916 "",
1917 " ",
1918 "app,,public",
1919 "app; DROP SCHEMA public",
1920 "app public",
1921 ] {
1922 let config = DatabaseConfig::default().with_search_path(invalid);
1923 assert!(
1924 matches!(config.validate(), Err(DbError::InvalidArgument(_))),
1925 "search_path 应拒绝 {invalid:?}"
1926 );
1927 }
1928 }
1929
1930 #[tokio::test]
1931 async fn health_check_propagates_closed_pool_error() -> Result<(), sqlx::Error> {
1932 let pool = sqlx::postgres::PgPoolOptions::new()
1933 .connect_lazy("postgres://postgres:test@127.0.0.1:5432/test")?;
1934 let db = Database {
1935 pool,
1936 config: DatabaseConfig::default(),
1937 server_version: OnceCell::new(),
1938 waiting: Arc::new(AtomicUsize::new(0)),
1939 };
1940
1941 db.close().await;
1942
1943 assert!(db.is_closed());
1944 assert!(matches!(db.health_check().await, Err(DbError::PoolClosed)));
1945 Ok(())
1946 }
1947
1948 #[test]
1949 fn timeout_config_is_opt_in_and_renders_set_statements() {
1950 let default_config = DatabaseConfig::default();
1951 assert!(default_config.statement_timeout_ms.is_none());
1952 assert!(default_config.lock_timeout_ms.is_none());
1953 assert!(default_config.idle_in_transaction_timeout_ms.is_none());
1954 assert!(timeout_statements(None, None, None).is_empty());
1955
1956 let config = DatabaseConfig::default()
1957 .with_statement_timeout_ms(5_000u64)
1958 .with_lock_timeout_ms(Some(250))
1959 .with_idle_in_transaction_timeout_ms(30_000u64);
1960 assert_eq!(config.statement_timeout_ms, Some(5_000));
1961 assert_eq!(config.lock_timeout_ms, Some(250));
1962 assert_eq!(config.idle_in_transaction_timeout_ms, Some(30_000));
1963 assert!(config.validate().is_ok());
1964
1965 assert_eq!(
1966 timeout_statements(
1967 config.statement_timeout_ms,
1968 config.lock_timeout_ms,
1969 config.idle_in_transaction_timeout_ms
1970 ),
1971 vec![
1972 "SET statement_timeout = 5000".to_string(),
1973 "SET lock_timeout = 250".to_string(),
1974 "SET idle_in_transaction_session_timeout = 30000".to_string(),
1975 ]
1976 );
1977 }
1978
1979 #[test]
1980 fn only_serialization_failures_and_deadlocks_are_retried() {
1981 use crate::error::SqlErrorInfo;
1982
1983 assert!(is_serialization_conflict(&DbError::DatabaseError(
1984 Box::new(SqlErrorInfo::new("40001", "could not serialize access"))
1985 )));
1986 assert!(is_serialization_conflict(&DbError::DatabaseError(
1987 Box::new(SqlErrorInfo::new("40P01", "deadlock detected"))
1988 )));
1989 for other in ["23505", "57014", "55P03", "42P01"] {
1990 assert!(
1991 !is_serialization_conflict(&DbError::DatabaseError(Box::new(SqlErrorInfo::new(
1992 other, "other"
1993 )))),
1994 "SQLSTATE {other} 不应触发事务重试"
1995 );
1996 }
1997 assert!(!is_serialization_conflict(&DbError::TransactionError(
1998 "local".to_string()
1999 )));
2000 }
2001
2002 #[test]
2003 fn retry_backoff_grows_exponentially_and_caps() {
2004 use std::time::Duration;
2005
2006 assert_eq!(retry_backoff(1), Duration::from_millis(10));
2007 assert_eq!(retry_backoff(2), Duration::from_millis(20));
2008 assert_eq!(retry_backoff(3), Duration::from_millis(40));
2009 assert_eq!(retry_backoff(7), Duration::from_millis(640));
2010 assert_eq!(retry_backoff(8), Duration::from_millis(1_000));
2011 assert_eq!(retry_backoff(100), Duration::from_millis(1_000));
2012 }
2013
2014 #[test]
2015 fn explain_statement_prefixes_trusted_sql_and_rejects_empty() {
2016 assert_eq!(
2017 explain_statement("SELECT $1::int + 1").unwrap(),
2018 "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT $1::int + 1"
2019 );
2020 assert_eq!(
2021 explain_statement(" SELECT 1; ").unwrap(),
2022 "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT 1"
2023 );
2024 for invalid in ["", " ", " ; "] {
2025 assert!(matches!(
2026 explain_statement(invalid),
2027 Err(DbError::InvalidArgument(_))
2028 ));
2029 }
2030 }
2031
2032 #[test]
2033 fn explain_rows_are_flattened_to_pretty_json_per_plan() {
2034 let rows = vec![
2035 (serde_json::json!([
2036 {"Plan": {"Node Type": "Result"}, "Execution Time": 0.1}
2037 ]),),
2038 (serde_json::json!([{"Plan": {"Node Type": "Seq Scan"}}]),),
2039 ];
2040 let plans = explain_rows_to_strings(rows).unwrap();
2041 assert_eq!(plans.len(), 2);
2042 for plan in &plans {
2043 let parsed: serde_json::Value = serde_json::from_str(plan).unwrap();
2044 assert!(parsed.get("Plan").is_some(), "计划文本应可解析为 JSON");
2045 }
2046 assert!(plans[0].contains("\"Node Type\": \"Result\""));
2047
2048 let plans = explain_rows_to_strings(vec![(serde_json::json!({"Plan": {}}),)]).unwrap();
2050 assert_eq!(plans.len(), 1);
2051 assert!(plans[0].contains("Plan"));
2052 }
2053
2054 #[tokio::test]
2056 async fn acquire_with_cancel_token_requires_at_least_two_connections() {
2057 let pool = sqlx::postgres::PgPoolOptions::new()
2058 .max_connections(1)
2059 .connect_lazy("postgres://postgres:test@127.0.0.1:5432/test")
2060 .unwrap();
2061 let db = Database {
2062 pool,
2063 config: DatabaseConfig {
2064 max_connections: 1,
2065 ..DatabaseConfig::default()
2066 },
2067 server_version: OnceCell::new(),
2068 waiting: Arc::new(AtomicUsize::new(0)),
2069 };
2070 assert!(matches!(
2071 db.acquire_with_cancel_token().await,
2072 Err(DbError::InvalidArgument(_))
2073 ));
2074 }
2075
2076 #[test]
2078 fn cancel_token_sql_and_57014_mapping() {
2079 use crate::error::{DbErrorCategory, SqlErrorInfo};
2080
2081 assert_eq!(CANCEL_BACKEND_SQL, "SELECT pg_cancel_backend($1)");
2082 assert_eq!(TERMINATE_BACKEND_SQL, "SELECT pg_terminate_backend($1)");
2083 assert!(CANCEL_BACKEND_SQL.contains("$1"));
2084 assert!(TERMINATE_BACKEND_SQL.contains("$1"));
2085
2086 let canceled = DbError::DatabaseError(Box::new(SqlErrorInfo::new(
2087 "57014",
2088 "canceling statement due to user request",
2089 )));
2090 assert_eq!(canceled.sqlstate(), Some("57014"));
2091 assert_eq!(canceled.category(), DbErrorCategory::Timeout);
2092 assert!(!canceled.is_retryable());
2093 }
2094
2095 #[test]
2097 fn advisory_lock_sql_covers_shared_and_exclusive() {
2098 assert_eq!(
2099 AdvisoryLockMode::Exclusive.lock_sql(),
2100 "SELECT pg_advisory_lock($1)"
2101 );
2102 assert_eq!(
2103 AdvisoryLockMode::Shared.lock_sql(),
2104 "SELECT pg_advisory_lock_shared($1)"
2105 );
2106 assert_eq!(
2107 AdvisoryLockMode::Exclusive.try_lock_sql(),
2108 "SELECT pg_try_advisory_lock($1)"
2109 );
2110 assert_eq!(
2111 AdvisoryLockMode::Shared.try_lock_sql(),
2112 "SELECT pg_try_advisory_lock_shared($1)"
2113 );
2114 assert_eq!(
2115 AdvisoryLockMode::Exclusive.unlock_sql(),
2116 "SELECT pg_advisory_unlock($1)"
2117 );
2118 assert_eq!(
2119 AdvisoryLockMode::Shared.unlock_sql(),
2120 "SELECT pg_advisory_unlock_shared($1)"
2121 );
2122 }
2123
2124 #[tokio::test]
2126 #[ignore = "需要真实 PostgreSQL 实例"]
2127 async fn advisory_unlock_false_detaches_instead_of_returning_connection() {
2128 let url = std::env::var("PG_TEST_URL")
2129 .unwrap_or_else(|_| "postgres://postgres:postgres@127.0.0.1:55432/test".to_string());
2130 let db = Database::connect(&url).await.expect("PG 必须可连接");
2131 let conn = db.acquire().await.expect("借出连接应成功");
2132 assert_eq!(db.pool.size(), 1);
2133
2134 let lock = PgAdvisoryLock {
2136 conn: Some(conn),
2137 key: 987_654_321,
2138 mode: AdvisoryLockMode::Exclusive,
2139 };
2140 assert!(!lock.unlock().await.expect("解锁调用应成功"));
2141 assert_eq!(
2142 db.pool.size(),
2143 0,
2144 "unlock 返回 false 时必须 detach 关闭会话,而不是把连接还池"
2145 );
2146 }
2147
2148 #[test]
2150 fn runtime_config_defaults_and_setters() {
2151 let default = DatabaseConfig::default();
2152 assert!(default.application_name.is_none());
2153 assert!(default.statement_cache_size.is_none());
2154 assert!(!default.reset_on_release);
2155 assert!(default.require_utf8_client_encoding);
2156 assert!(default.ssl_mode.is_none());
2157
2158 let config = DatabaseConfig::default()
2159 .with_application_name("yang-test")
2160 .with_statement_cache_size(0)
2161 .with_reset_on_release(true)
2162 .with_require_utf8_client_encoding(false);
2163 assert_eq!(config.application_name.as_deref(), Some("yang-test"));
2164 assert_eq!(config.statement_cache_size, Some(0));
2165 assert!(config.reset_on_release);
2166 assert!(!config.require_utf8_client_encoding);
2167 assert!(config.validate().is_ok());
2168
2169 assert_eq!(DISCARD_ALL_SQL, "DISCARD ALL");
2170 }
2171
2172 #[test]
2173 fn application_name_is_validated() {
2174 for invalid in [String::new(), "a\0b".to_string(), "a".repeat(64)] {
2175 let config = DatabaseConfig::default().with_application_name(invalid.clone());
2176 assert!(
2177 matches!(config.validate(), Err(DbError::InvalidArgument(_))),
2178 "application_name 应拒绝 {invalid:?}"
2179 );
2180 }
2181 let config = DatabaseConfig::default().with_application_name("a".repeat(63));
2183 assert!(config.validate().is_ok());
2184 }
2185
2186 #[test]
2188 fn ssl_config_validation_and_mode_mapping() {
2189 assert!(matches!(
2190 SslMode::Disable.as_pg_ssl_mode(),
2191 PgSslMode::Disable
2192 ));
2193 assert!(matches!(
2194 SslMode::Prefer.as_pg_ssl_mode(),
2195 PgSslMode::Prefer
2196 ));
2197 assert!(matches!(
2198 SslMode::Require.as_pg_ssl_mode(),
2199 PgSslMode::Require
2200 ));
2201 assert!(matches!(
2202 SslMode::VerifyCa.as_pg_ssl_mode(),
2203 PgSslMode::VerifyCa
2204 ));
2205 assert!(matches!(
2206 SslMode::VerifyFull.as_pg_ssl_mode(),
2207 PgSslMode::VerifyFull
2208 ));
2209
2210 for mode in [SslMode::VerifyCa, SslMode::VerifyFull] {
2212 let missing = DatabaseConfig::default().with_ssl_mode(Some(mode));
2213 assert!(
2214 matches!(missing.validate(), Err(DbError::InvalidArgument(_))),
2215 "{mode:?} 缺 ssl_root_cert 应报错"
2216 );
2217 let with_root = DatabaseConfig::default()
2218 .with_ssl_mode(Some(mode))
2219 .with_ssl_root_cert("/etc/ssl/root.pem");
2220 assert!(with_root.validate().is_ok());
2221 }
2222
2223 assert!(matches!(
2225 DatabaseConfig::default()
2226 .with_ssl_mode(Some(SslMode::Disable))
2227 .with_ssl_root_cert("/etc/ssl/root.pem")
2228 .validate(),
2229 Err(DbError::InvalidArgument(_))
2230 ));
2231
2232 assert!(matches!(
2234 DatabaseConfig::default()
2235 .with_ssl_mode(Some(SslMode::Require))
2236 .with_ssl_client_cert("/etc/ssl/client.pem")
2237 .validate(),
2238 Err(DbError::InvalidArgument(_))
2239 ));
2240 assert!(DatabaseConfig::default()
2241 .with_ssl_mode(Some(SslMode::Require))
2242 .with_ssl_client_cert("/etc/ssl/client.pem")
2243 .with_ssl_client_key("/etc/ssl/client.key")
2244 .validate()
2245 .is_ok());
2246
2247 assert!(DatabaseConfig::default().effective_ssl_mode().is_none());
2249 assert_eq!(
2251 DatabaseConfig::default()
2252 .with_ssl_root_cert("/etc/ssl/root.pem")
2253 .effective_ssl_mode(),
2254 Some(SslMode::VerifyCa)
2255 );
2256 for mode in [
2258 SslMode::Disable,
2259 SslMode::Prefer,
2260 SslMode::Require,
2261 SslMode::VerifyCa,
2262 SslMode::VerifyFull,
2263 ] {
2264 assert_eq!(
2265 DatabaseConfig::default()
2266 .with_ssl_mode(Some(mode))
2267 .effective_ssl_mode(),
2268 Some(mode)
2269 );
2270 }
2271 assert!(DatabaseConfig::default()
2273 .with_ssl_root_cert("/etc/ssl/root.pem")
2274 .validate()
2275 .is_ok());
2276
2277 for mode in [SslMode::Prefer, SslMode::Require] {
2280 assert!(
2281 matches!(
2282 DatabaseConfig::default()
2283 .with_ssl_mode(Some(mode))
2284 .with_ssl_root_cert("/etc/ssl/root.pem")
2285 .validate(),
2286 Err(DbError::InvalidArgument(_))
2287 ),
2288 "{mode:?} + ssl_root_cert 应报错(根证书不会被该校验模式使用)"
2289 );
2290 }
2291 }
2292
2293 #[test]
2295 fn client_encoding_must_be_utf8() {
2296 for ok in ["UTF8", "utf8", "Utf-8", "UNICODE", " UTF8 "] {
2297 assert!(client_encoding_is_utf8(ok), "{ok:?} 应视为 UTF8");
2298 }
2299 for bad in ["SQL_ASCII", "LATIN1", "WIN1252", "EUC_JP", ""] {
2300 assert!(!client_encoding_is_utf8(bad), "{bad:?} 不应视为 UTF8");
2301 }
2302 }
2303
2304 #[tokio::test]
2306 async fn waiting_counter_is_raii_and_reflected_in_pool_status() -> Result<(), sqlx::Error> {
2307 let counter = AtomicUsize::new(0);
2308 {
2309 counter.fetch_add(1, Ordering::Relaxed);
2310 let _waiter = AcquireWaiter {
2311 counter: &counter,
2312 armed: true,
2313 };
2314 assert_eq!(counter.load(Ordering::Relaxed), 1);
2315 }
2316 assert_eq!(counter.load(Ordering::Relaxed), 0);
2317
2318 {
2320 let _waiter = AcquireWaiter {
2321 counter: &counter,
2322 armed: false,
2323 };
2324 }
2325 assert_eq!(counter.load(Ordering::Relaxed), 0);
2326
2327 let pool = sqlx::postgres::PgPoolOptions::new()
2328 .connect_lazy("postgres://postgres:test@127.0.0.1:5432/test")?;
2329 let db = Database {
2330 pool,
2331 config: DatabaseConfig::default(),
2332 server_version: OnceCell::new(),
2333 waiting: Arc::new(AtomicUsize::new(3)),
2334 };
2335 assert_eq!(db.pool_status().waiting, 3);
2336 Ok(())
2337 }
2338}