1use std::collections::HashSet;
11use std::str::FromStr;
12use std::sync::Arc;
13#[cfg(feature = "reliable_report")]
14use std::time::Duration;
15
16#[cfg(feature = "reliable_report")]
17use chrono::TimeDelta;
18use serde_json::json;
19use sqlx::postgres::PgRow;
20use sqlx::{AssertSqlSafe, PgPool, Row};
21
22use async_trait::async_trait;
23
24fn sqlx_err_to_string(e: &sqlx::Error) -> String {
26 match e {
27 sqlx::Error::Database(db_err) => db_err.message().to_owned(),
28 _ => e.to_string(),
29 }
30}
31use cadence::StatsdClient;
32use serde::{Deserialize, Serialize};
33use uuid::Uuid;
34
35use crate::db::client::DbClient;
36use crate::db::error::{DbError, DbResult};
37use crate::db::{DbSettings, User};
38use crate::metric_name::MetricName;
39use crate::metrics::StatsdClientExt;
40use crate::notification::Notification;
41use crate::{MAX_ROUTER_TTL_SECS, util};
42
43use super::client::FetchMessageResponse;
44
45#[cfg(feature = "reliable_report")]
46const RELIABLE_LOG_TTL: TimeDelta = TimeDelta::days(60);
47
48#[derive(Debug, Clone, Deserialize, Serialize)]
49#[serde(default)]
50pub struct PostgresDbSettings {
51 pub schema: Option<String>, pub router_table: String, pub message_table: String, pub meta_table: String, pub reliability_table: String, max_router_ttl: u64, }
60
61impl Default for PostgresDbSettings {
62 fn default() -> Self {
63 Self {
64 schema: None,
65 router_table: "router".to_owned(),
66 message_table: "message".to_owned(),
67 meta_table: "meta".to_owned(),
68 reliability_table: "reliability".to_owned(),
69 max_router_ttl: MAX_ROUTER_TTL_SECS,
70 }
72 }
73}
74
75impl TryFrom<&str> for PostgresDbSettings {
76 type Error = DbError;
77 fn try_from(setting_string: &str) -> Result<Self, Self::Error> {
78 if setting_string.trim().is_empty() {
79 return Ok(PostgresDbSettings::default());
80 }
81 serde_json::from_str(setting_string).map_err(|e| {
82 DbError::General(format!(
83 "Could not parse configuration db_settings: {:?}",
84 e
85 ))
86 })
87 }
88}
89
90#[derive(Clone)]
91pub struct PgClientImpl {
92 metrics: Arc<StatsdClient>,
93 db_settings: PostgresDbSettings,
94 pool: PgPool,
95 cached_router_table: String,
97 cached_message_table: String,
98 cached_meta_table: String,
99 #[cfg(feature = "reliable_report")]
100 cached_reliability_table: String,
101}
102
103impl PgClientImpl {
104 fn incr_message_stored(&self, is_topic: bool) {
106 self.metrics
107 .incr_with_tags(MetricName::NotificationMessageStored)
108 .with_tag("topic", &is_topic.to_string())
109 .with_tag("database", &self.name())
110 .send();
111 }
112
113 pub fn new(metrics: Arc<StatsdClient>, settings: &DbSettings) -> DbResult<Self> {
121 let db_settings = PostgresDbSettings::try_from(settings.db_settings.as_ref())?;
122 info!(
123 "📮 Initializing Postgres DB Client with settings: {:?} from {:?}",
124 db_settings, &settings.db_settings
125 );
126 if let Some(dsn) = settings.dsn.clone() {
127 trace!("📮 Postgres Connect {}", &dsn);
128
129 let connect_options = sqlx::postgres::PgConnectOptions::from_str(&dsn)
131 .map_err(|e| DbError::General(format!("Invalid Postgres DSN: {e}")))?;
132 let pool = sqlx::postgres::PgPoolOptions::new().connect_lazy_with(connect_options);
133 let cached_router_table = if let Some(schema) = &db_settings.schema {
134 format!("{}.{}", schema, db_settings.router_table)
135 } else {
136 db_settings.router_table.clone()
137 };
138 let cached_message_table = if let Some(schema) = &db_settings.schema {
139 format!("{}.{}", schema, db_settings.message_table)
140 } else {
141 db_settings.message_table.clone()
142 };
143 let cached_meta_table = if let Some(schema) = &db_settings.schema {
144 format!("{}.{}", schema, db_settings.meta_table)
145 } else {
146 db_settings.meta_table.clone()
147 };
148 #[cfg(feature = "reliable_report")]
149 let cached_reliability_table = if let Some(schema) = &db_settings.schema {
150 format!("{}.{}", schema, db_settings.reliability_table)
151 } else {
152 db_settings.reliability_table.clone()
153 };
154 return Ok(Self {
155 metrics,
156 db_settings,
157 pool,
158 cached_router_table,
159 cached_message_table,
160 cached_meta_table,
161 #[cfg(feature = "reliable_report")]
162 cached_reliability_table,
163 });
164 };
165 Err(DbError::ConnectionError("No DSN specified".to_owned()))
166 }
167
168 async fn table_exists(&self, table_name: &str) -> DbResult<bool> {
170 let (schema, table_name) = if table_name.contains('.') {
171 let mut parts = table_name.splitn(2, '.');
172 (
173 parts.next().unwrap_or("public").to_owned(),
174 parts.next().unwrap().to_owned(),
177 )
178 } else {
179 ("public".to_owned(), table_name.to_owned())
180 };
181 let row: (bool,) = sqlx::query_as(
182 "SELECT EXISTS (SELECT FROM pg_tables WHERE schemaname=$1 AND tablename=$2);",
183 )
184 .bind(&schema)
185 .bind(&table_name)
186 .fetch_one(&self.pool)
187 .await
188 .map_err(DbError::PgError)?;
189 Ok(row.0)
190 }
191
192 fn router_expiry(&self) -> u64 {
194 util::sec_since_epoch() + self.db_settings.max_router_ttl
195 }
196
197 pub(crate) fn router_table(&self) -> &str {
199 &self.cached_router_table
200 }
201
202 pub(crate) fn message_table(&self) -> &str {
204 &self.cached_message_table
205 }
206
207 pub(crate) fn meta_table(&self) -> &str {
211 &self.cached_meta_table
212 }
213
214 #[cfg(feature = "reliable_report")]
218 pub(crate) fn reliability_table(&self) -> &str {
219 &self.cached_reliability_table
220 }
221}
222
223#[async_trait]
224impl DbClient for PgClientImpl {
225 async fn add_user(&self, user: &User) -> DbResult<()> {
227 let sql = format!(
228 "INSERT INTO {tablename} (uaid, connected_at, router_type, router_data, node_id, record_version, version, last_update, priv_channels, expiry)
229 VALUES($1, $2::BIGINT, $3, $4, $5, $6::BIGINT, $7, $8::BIGINT, $9, $10::BIGINT)
230 ON CONFLICT (uaid) DO
231 UPDATE SET connected_at=EXCLUDED.connected_at,
232 router_type=EXCLUDED.router_type,
233 router_data=EXCLUDED.router_data,
234 node_id=EXCLUDED.node_id,
235 record_version=EXCLUDED.record_version,
236 version=EXCLUDED.version,
237 last_update=EXCLUDED.last_update,
238 priv_channels=EXCLUDED.priv_channels,
239 expiry=EXCLUDED.expiry
240 ;
241 ",
242 tablename = self.router_table()
243 );
244 sqlx::query(AssertSqlSafe(sql))
245 .bind(user.uaid.simple().to_string()) .bind(user.connected_at as i64) .bind(&user.router_type) .bind(json!(user.router_data).to_string()) .bind(&user.node_id) .bind(user.record_version.map(|i| i as i64)) .bind(user.version.map(|v| v.simple().to_string())) .bind(user.current_timestamp.map(|i| i as i64)) .bind(
254 user.priv_channels
255 .iter()
256 .map(|v| v.to_string())
257 .collect::<Vec<String>>(),
258 )
259 .bind(self.router_expiry() as i64) .execute(&self.pool)
261 .await
262 .map_err(|e| DbError::PgDbError(sqlx_err_to_string(&e)))?;
263 Ok(())
264 }
265
266 async fn update_user(&self, user: &mut User) -> DbResult<bool> {
268 let cmd = format!(
269 "UPDATE {tablename} SET connected_at=$2::BIGINT,
270 router_type=$3,
271 router_data=$4,
272 node_id=$5,
273 record_version=$6::BIGINT,
274 version=$7,
275 last_update=$8::BIGINT,
276 priv_channels=$9,
277 expiry=$10::BIGINT
278 WHERE
279 uaid = $1 AND connected_at < $2::BIGINT;
280 ",
281 tablename = self.router_table()
282 );
283 let result = sqlx::query(AssertSqlSafe(&*cmd))
284 .bind(user.uaid.simple().to_string()) .bind(user.connected_at as i64) .bind(&user.router_type) .bind(json!(user.router_data).to_string()) .bind(&user.node_id) .bind(user.record_version.map(|i| i as i64)) .bind(user.version.map(|v| v.simple().to_string())) .bind(user.current_timestamp.map(|i| i as i64)) .bind(
293 user.priv_channels
294 .iter()
295 .map(|v| v.to_string())
296 .collect::<Vec<String>>(),
297 )
298 .bind(self.router_expiry() as i64) .execute(&self.pool)
300 .await
301 .map_err(|e| DbError::PgDbError(sqlx_err_to_string(&e)))?;
302 Ok(result.rows_affected() > 0)
303 }
304
305 async fn get_user(&self, uaid: &Uuid) -> DbResult<Option<User>> {
307 let row: Option<PgRow> = sqlx::query(AssertSqlSafe(format!(
308 "SELECT connected_at, router_type, router_data, node_id, record_version, last_update, version, priv_channels
309 FROM {tablename}
310 WHERE uaid = $1",
311 tablename = self.router_table()
312 )))
313 .bind(uaid.simple().to_string())
314 .fetch_optional(&self.pool)
315 .await
316 .map_err(|e| DbError::PgDbError(sqlx_err_to_string(&e)))?;
317
318 let Some(row) = row else {
319 return Ok(None);
320 };
321
322 let priv_channels = if let Ok(Some(channels)) =
325 row.try_get::<Option<Vec<String>>, _>("priv_channels")
326 {
327 let mut priv_channels = HashSet::new();
328 for channel in channels.iter() {
329 let uuid = Uuid::from_str(channel).map_err(|e| DbError::General(e.to_string()))?;
330 priv_channels.insert(uuid);
331 }
332 priv_channels
333 } else {
334 HashSet::new()
335 };
336 let resp = User {
337 uaid: *uaid,
338 connected_at: row
339 .try_get::<i64, _>("connected_at")
340 .map_err(DbError::PgError)? as u64,
341 router_type: row
342 .try_get::<String, _>("router_type")
343 .map_err(DbError::PgError)?,
344 router_data: serde_json::from_str(
345 row.try_get::<&str, _>("router_data")
346 .map_err(DbError::PgError)?,
347 )
348 .map_err(|e| DbError::General(e.to_string()))?,
349 node_id: row
350 .try_get::<Option<String>, _>("node_id")
351 .map_err(DbError::PgError)?,
352 record_version: row
353 .try_get::<Option<i64>, _>("record_version")
354 .map_err(DbError::PgError)?
355 .map(|v| v as u64),
356 current_timestamp: row
357 .try_get::<Option<i64>, _>("last_update")
358 .map_err(DbError::PgError)?
359 .map(|v| v as u64),
360 version: row
361 .try_get::<Option<String>, _>("version")
362 .map_err(DbError::PgError)?
363 .map(|v| {
365 Uuid::from_str(&v).map_err(|e| {
366 DbError::Integrity("Invalid UUID found".to_owned(), Some(e.to_string()))
367 })
368 })
369 .transpose()?,
370 priv_channels,
371 };
372 Ok(Some(resp))
373 }
374
375 async fn remove_user(&self, uaid: &Uuid) -> DbResult<()> {
377 sqlx::query(AssertSqlSafe(format!(
378 "DELETE FROM {tablename}
379 WHERE uaid = $1",
380 tablename = self.router_table()
381 )))
382 .bind(uaid.simple().to_string())
383 .execute(&self.pool)
384 .await
385 .map_err(DbError::PgError)?;
386 Ok(())
387 }
388
389 async fn add_channel(&self, uaid: &Uuid, channel_id: &Uuid) -> DbResult<()> {
398 sqlx::query(AssertSqlSafe(format!(
399 "INSERT
400 INTO {tablename} (uaid, channel_id) VALUES ($1, $2)
401 ON CONFLICT DO NOTHING",
402 tablename = self.meta_table()
403 )))
404 .bind(uaid.simple().to_string())
405 .bind(channel_id.simple().to_string())
406 .execute(&self.pool)
407 .await
408 .map_err(DbError::PgError)?;
409 Ok(())
410 }
411
412 async fn add_channels(&self, uaid: &Uuid, channels: HashSet<Uuid>) -> DbResult<()> {
414 if channels.is_empty() {
415 trace!("📮 No channels to save.");
416 return Ok(());
417 };
418 let uaid_str = uaid.simple().to_string();
419
420 let mut query_builder: sqlx::QueryBuilder<sqlx::Postgres> =
421 sqlx::QueryBuilder::new(format!(
422 "INSERT INTO {tablename} (uaid, channel_id) ",
423 tablename = self.meta_table()
424 ));
425 query_builder.push_values(channels.iter(), |mut b, channel| {
426 b.push_bind(uaid_str.clone());
427 b.push_bind(channel.simple().to_string());
428 });
429 query_builder.push(" ON CONFLICT DO NOTHING");
430
431 query_builder
432 .build()
433 .execute(&self.pool)
434 .await
435 .map_err(DbError::PgError)?;
436 Ok(())
437 }
438
439 async fn get_channels(&self, uaid: &Uuid) -> DbResult<HashSet<Uuid>> {
441 let mut result = HashSet::new();
442 let rows: Vec<PgRow> = sqlx::query(AssertSqlSafe(format!(
443 "SELECT distinct channel_id FROM {tablename} WHERE uaid = $1;",
444 tablename = self.meta_table()
445 )))
446 .bind(uaid.simple().to_string())
447 .fetch_all(&self.pool)
448 .await
449 .map_err(DbError::PgError)?;
450 for row in rows.iter() {
451 let s = row
452 .try_get::<&str, _>("channel_id")
453 .map_err(DbError::PgError)?;
454 result.insert(Uuid::from_str(s).map_err(|e| DbError::General(e.to_string()))?);
455 }
456 Ok(result)
457 }
458
459 async fn remove_channel(&self, uaid: &Uuid, channel_id: &Uuid) -> DbResult<bool> {
461 let cmd = format!(
462 "DELETE FROM {tablename}
463 WHERE uaid = $1 AND channel_id = $2;",
464 tablename = self.meta_table()
465 );
466 let result = sqlx::query(AssertSqlSafe(cmd))
467 .bind(uaid.simple().to_string())
468 .bind(channel_id.simple().to_string())
469 .execute(&self.pool)
470 .await
471 .map_err(DbError::PgError)?;
472 Ok(result.rows_affected() > 0)
474 }
475
476 async fn remove_node_id(
478 &self,
479 uaid: &Uuid,
480 node_id: &str,
481 connected_at: u64,
482 version: &Option<Uuid>,
483 ) -> DbResult<bool> {
484 let Some(version) = version else {
485 return Err(DbError::General("Expected a user version field".to_owned()));
486 };
487 sqlx::query(AssertSqlSafe(format!(
488 "UPDATE {tablename}
489 SET node_id = null
490 WHERE uaid=$1 AND node_id = $2 AND connected_at = $3 AND version= $4;",
491 tablename = self.router_table()
492 )))
493 .bind(uaid.simple().to_string())
494 .bind(node_id)
495 .bind(connected_at as i64)
496 .bind(version.simple().to_string())
497 .execute(&self.pool)
498 .await
499 .map_err(DbError::PgError)?;
500 Ok(true)
501 }
502
503 async fn save_message(&self, uaid: &Uuid, message: Notification) -> DbResult<()> {
505 #[allow(unused_mut)]
508 let mut fields = vec![
509 "uaid",
510 "channel_id",
511 "chid_message_id",
512 "version",
513 "ttl",
514 "expiry",
515 "topic",
516 "timestamp",
517 "data",
518 "sortkey_timestamp",
519 "headers",
520 ];
521 #[allow(unused_mut)]
523 let mut inputs = vec![
524 "$1", "$2", "$3", "$4", "$5", "$6", "$7", "$8", "$9", "$10", "$11",
525 ];
526 #[cfg(feature = "reliable_report")]
527 {
528 fields.append(&mut ["reliability_id"].to_vec());
529 inputs.append(&mut ["$12"].to_vec());
530 }
531 let cmd = format!(
532 "INSERT INTO {tablename}
533 ({fields})
534 VALUES
535 ({inputs}) ON CONFLICT (chid_message_id) DO UPDATE SET
536 uaid=EXCLUDED.uaid,
537 channel_id=EXCLUDED.channel_id,
538 version=EXCLUDED.version,
539 ttl=EXCLUDED.ttl,
540 expiry=EXCLUDED.expiry,
541 topic=EXCLUDED.topic,
542 timestamp=EXCLUDED.timestamp,
543 data=EXCLUDED.data,
544 sortkey_timestamp=EXCLUDED.sortkey_timestamp,
545 headers=EXCLUDED.headers",
546 tablename = &self.message_table(),
547 fields = fields.join(","),
548 inputs = inputs.join(",")
549 );
550 #[allow(unused_mut)]
551 let mut q = sqlx::query(AssertSqlSafe(cmd))
552 .bind(uaid.simple().to_string())
553 .bind(message.channel_id.simple().to_string())
554 .bind(message.chidmessageid())
555 .bind(&message.version)
556 .bind(message.ttl as i64)
557 .bind(util::sec_since_epoch() as i64 + message.ttl as i64)
558 .bind(message.topic.as_ref().filter(|v| !v.is_empty()).cloned())
559 .bind(message.timestamp as i64)
560 .bind(message.data.as_deref().unwrap_or_default().to_owned())
561 .bind(message.sortkey_timestamp.map(|v| v as i64))
562 .bind(json!(message.headers).to_string());
563 #[cfg(feature = "reliable_report")]
564 {
565 q = q.bind(message.reliability_id.clone());
566 }
567 let is_topic = message.topic.as_ref().is_some_and(|v| !v.is_empty());
568 q.execute(&self.pool)
569 .await
570 .map_err(|e| DbError::PgDbError(sqlx_err_to_string(&e)))?;
571 self.incr_message_stored(is_topic);
572 Ok(())
573 }
574
575 async fn remove_message(&self, uaid: &Uuid, chidmessageid: &str) -> DbResult<()> {
577 debug!(
578 "📮 Removing message for user {} with chid_message_id {}",
579 uaid.simple(),
580 chidmessageid
581 );
582 let result = sqlx::query(AssertSqlSafe(format!(
583 "DELETE FROM {tablename}
584 WHERE uaid=$1 AND chid_message_id = $2;",
585 tablename = self.message_table()
586 )))
587 .bind(uaid.simple().to_string())
588 .bind(chidmessageid)
589 .execute(&self.pool)
590 .await
591 .map_err(|e| DbError::PgDbError(sqlx_err_to_string(&e)))?;
592 debug!(
593 "📮 Deleted {} rows for user {} with chid_message_id {}",
594 result.rows_affected(),
595 uaid.simple(),
596 chidmessageid
597 );
598 Ok(())
599 }
600
601 async fn save_messages(&self, uaid: &Uuid, messages: Vec<Notification>) -> DbResult<()> {
602 if messages.is_empty() {
603 return Ok(());
604 }
605 if messages.len() == 1 {
606 return self
608 .save_message(uaid, messages.into_iter().next().unwrap())
609 .await;
610 }
611
612 let field_names = {
613 #[allow(unused_mut)]
614 let mut fields = vec![
615 "uaid",
616 "channel_id",
617 "chid_message_id",
618 "version",
619 "ttl",
620 "expiry",
621 "topic",
622 "timestamp",
623 "data",
624 "sortkey_timestamp",
625 "headers",
626 ];
627 #[cfg(feature = "reliable_report")]
628 fields.push("reliability_id");
629 fields.join(",")
630 };
631
632 let uaid_str = uaid.simple().to_string();
633 let now = util::sec_since_epoch() as i64;
634
635 struct MessageParams {
637 channel_id: String,
638 chidmessageid: String,
639 version: String,
640 ttl: i64,
641 expiry: i64,
642 topic: Option<String>,
643 timestamp: i64,
644 data: String,
645 sortkey_timestamp: Option<i64>,
646 headers: String,
647 #[cfg(feature = "reliable_report")]
648 reliability_id: Option<String>,
649 }
650
651 let msg_params: Vec<MessageParams> = messages
652 .into_iter()
653 .map(|m| {
654 let topic = m.topic.as_ref().filter(|v| !v.is_empty()).cloned();
655 MessageParams {
656 channel_id: m.channel_id.simple().to_string(),
657 chidmessageid: m.chidmessageid(),
658 version: m.version,
659 ttl: m.ttl as i64,
660 expiry: now + m.ttl as i64,
661 topic,
662 timestamp: m.timestamp as i64,
663 data: m.data.as_deref().unwrap_or_default().to_owned(),
664 sortkey_timestamp: m.sortkey_timestamp.map(|v| v as i64),
665 headers: json!(m.headers).to_string(),
666 #[cfg(feature = "reliable_report")]
667 reliability_id: m.reliability_id,
668 }
669 })
670 .collect();
671
672 let mut query_builder: sqlx::QueryBuilder<sqlx::Postgres> =
673 sqlx::QueryBuilder::new(format!(
674 "INSERT INTO {tablename} ({field_names}) ",
675 tablename = &self.message_table(),
676 ));
677 query_builder.push_values(msg_params.iter(), |mut query, mp| {
679 query.push_bind(uaid_str.clone());
680 query.push_bind(mp.channel_id.clone());
681 query.push_bind(mp.chidmessageid.clone());
682 query.push_bind(mp.version.clone());
683 query.push_bind(mp.ttl);
684 query.push_bind(mp.expiry);
685 query.push_bind(mp.topic.clone());
686 query.push_bind(mp.timestamp);
687 query.push_bind(mp.data.clone());
688 query.push_bind(mp.sortkey_timestamp);
689 query.push_bind(mp.headers.clone());
690 #[cfg(feature = "reliable_report")]
691 query.push_bind(mp.reliability_id.clone());
692 });
693 query_builder.push(
694 " ON CONFLICT (chid_message_id) DO UPDATE SET
695 uaid=EXCLUDED.uaid,
696 channel_id=EXCLUDED.channel_id,
697 version=EXCLUDED.version,
698 ttl=EXCLUDED.ttl,
699 expiry=EXCLUDED.expiry,
700 topic=EXCLUDED.topic,
701 timestamp=EXCLUDED.timestamp,
702 data=EXCLUDED.data,
703 sortkey_timestamp=EXCLUDED.sortkey_timestamp,
704 headers=EXCLUDED.headers",
705 );
706
707 query_builder
708 .build()
709 .execute(&self.pool)
710 .await
711 .map_err(|e| DbError::PgDbError(sqlx_err_to_string(&e)))?;
712 for mp in &msg_params {
713 self.incr_message_stored(mp.topic.is_some());
714 }
715 Ok(())
716 }
717
718 async fn fetch_topic_messages(
721 &self,
722 uaid: &Uuid,
723 limit: usize,
724 ) -> DbResult<FetchMessageResponse> {
725 let messages: Vec<Notification> = sqlx::query(AssertSqlSafe(format!(
726 "SELECT channel_id, version, ttl, topic, timestamp, data, sortkey_timestamp, headers
727 FROM {tablename}
728 WHERE uaid=$1 AND expiry >= $2 AND (topic IS NOT NULL AND topic != '')
729 ORDER BY timestamp DESC
730 LIMIT $3",
731 tablename = &self.message_table(),
732 )))
733 .bind(uaid.simple().to_string())
734 .bind(util::sec_since_epoch() as i64)
735 .bind(limit as i64)
736 .fetch_all(&self.pool)
737 .await
738 .map_err(|e| DbError::PgDbError(sqlx_err_to_string(&e)))?
739 .iter()
740 .map(|row: &PgRow| row.try_into())
741 .collect::<Result<Vec<Notification>, DbError>>()?;
742
743 if messages.is_empty() {
744 Ok(Default::default())
745 } else {
746 Ok(FetchMessageResponse {
747 timestamp: Some(messages[0].timestamp),
748 messages,
749 })
750 }
751 }
752
753 async fn fetch_timestamp_messages(
755 &self,
756 uaid: &Uuid,
757 timestamp: Option<u64>,
758 limit: usize,
759 ) -> DbResult<FetchMessageResponse> {
760 let uaid = uaid.simple().to_string();
761 let response: Vec<PgRow> = if let Some(ts) = timestamp {
762 trace!("📮 Fetching messages for user {} since {}", &uaid, ts);
763 sqlx::query(AssertSqlSafe(format!(
764 "SELECT * FROM {}
765 WHERE uaid = $1 AND timestamp > $2 AND expiry >= $3
766 ORDER BY timestamp
767 LIMIT $4",
768 self.message_table()
769 )))
770 .bind(&uaid)
771 .bind(ts as i64)
772 .bind(util::sec_since_epoch() as i64)
773 .bind(limit as i64)
774 .fetch_all(&self.pool)
775 .await
776 } else {
777 trace!("📮 Fetching messages for user {}", &uaid);
778 sqlx::query(AssertSqlSafe(format!(
779 "SELECT *
780 FROM {}
781 WHERE uaid = $1
782 AND expiry >= $2
783 LIMIT $3",
784 self.message_table()
785 )))
786 .bind(&uaid)
787 .bind(util::sec_since_epoch() as i64)
788 .bind(limit as i64)
789 .fetch_all(&self.pool)
790 .await
791 }
792 .map_err(|e| DbError::PgDbError(sqlx_err_to_string(&e)))?;
793 let messages: Vec<Notification> = response
794 .iter()
795 .map(|row: &PgRow| row.try_into())
796 .collect::<Result<Vec<Notification>, DbError>>()?;
797 let timestamp = if !messages.is_empty() {
798 Some(messages[0].timestamp)
799 } else {
800 None
801 };
802
803 Ok(FetchMessageResponse {
804 timestamp,
805 messages,
806 })
807 }
808
809 async fn router_table_exists(&self) -> DbResult<bool> {
811 self.table_exists(self.router_table()).await
812 }
813
814 async fn message_table_exists(&self) -> DbResult<bool> {
816 self.table_exists(self.message_table()).await
817 }
818
819 #[cfg(feature = "reliable_report")]
820 async fn log_report(
821 &self,
822 reliability_id: &str,
823 new_state: crate::reliability::ReliabilityState,
824 ) -> DbResult<()> {
825 let timestamp_epoch = (std::time::SystemTime::now()
826 + Duration::from_secs(RELIABLE_LOG_TTL.num_seconds() as u64))
827 .duration_since(std::time::UNIX_EPOCH)
828 .map_err(|e| DbError::General(format!("System time before UNIX epoch: {e}")))?
829 .as_secs() as i64;
830 debug!("📮 Logging report for {reliability_id} as {new_state}");
831
832 let tablename = &self.reliability_table();
833 let state = new_state.to_string();
834 sqlx::query(AssertSqlSafe(format!(
835 "INSERT INTO {tablename} (id, states, last_update_timestamp) VALUES ($1, json_build_object($2, $3), $3)
836 ON CONFLICT (id) DO
837 UPDATE SET states = EXCLUDED.states,
838 last_update_timestamp = EXCLUDED.last_update_timestamp;",
839 tablename = tablename
840 )))
841 .bind(reliability_id)
842 .bind(&state)
843 .bind(timestamp_epoch)
844 .execute(&self.pool)
845 .await
846 .map_err(|e| DbError::PgDbError(sqlx_err_to_string(&e)))?;
847 Ok(())
848 }
849
850 async fn increment_storage(&self, uaid: &Uuid, timestamp: u64) -> DbResult<()> {
851 debug!("📮 Updating {uaid} current_timestamp:{timestamp}");
852 let tablename = &self.router_table();
853
854 trace!("📮 Purging git{uaid} for < {timestamp}");
855 let mut tx = self.pool.begin().await.map_err(DbError::PgError)?;
856 sqlx::query(AssertSqlSafe(format!(
858 "DELETE FROM {} WHERE uaid = $1 and expiry < $2",
859 &self.message_table()
860 )))
861 .bind(uaid.simple().to_string())
862 .bind(util::sec_since_epoch() as i64)
863 .execute(&mut *tx)
864 .await
865 .map_err(|e| DbError::PgDbError(sqlx_err_to_string(&e)))?;
866 sqlx::query(AssertSqlSafe(format!(
868 "DELETE FROM {} WHERE uaid = $1 AND timestamp IS NOT NULL AND timestamp < $2",
869 &self.message_table()
870 )))
871 .bind(uaid.simple().to_string())
872 .bind(timestamp as i64)
873 .execute(&mut *tx)
874 .await
875 .map_err(|e| DbError::PgDbError(sqlx_err_to_string(&e)))?;
876 sqlx::query(AssertSqlSafe(format!(
877 "UPDATE {tablename} SET last_update = $2::BIGINT, expiry= $3::BIGINT WHERE uaid = $1"
878 )))
879 .bind(uaid.simple().to_string())
880 .bind(timestamp as i64)
881 .bind(self.router_expiry() as i64)
882 .execute(&mut *tx)
883 .await
884 .map_err(|e| DbError::PgDbError(sqlx_err_to_string(&e)))?;
885 tx.commit().await.map_err(DbError::PgError)?;
886 Ok(())
887 }
888
889 fn name(&self) -> String {
890 "Postgres".to_owned()
891 }
892
893 async fn health_check(&self) -> DbResult<bool> {
894 let row: (bool,) = sqlx::query_as("select true")
895 .fetch_one(&self.pool)
896 .await
897 .map_err(DbError::PgError)?;
898 if !row.0 {
899 error!("📮 Failed to fetch from database");
900 return Ok(false);
901 }
902 if !self.router_table_exists().await? {
903 error!("📮 Router table does not exist");
904 return Ok(false);
905 }
906 if !self.message_table_exists().await? {
907 error!("📮 Message table does not exist");
908 return Ok(false);
909 }
910 Ok(true)
911 }
912
913 fn box_clone(&self) -> Box<dyn DbClient> {
915 Box::new(self.clone())
916 }
917}
918
919#[cfg(test)]
932mod tests {
933 use crate::util::sec_since_epoch;
934 use crate::{logging::init_test_logging, util::ms_since_epoch};
935 use rand::prelude::*;
936 use serde_json::json;
937 use std::env;
938
939 use super::*;
940 const TEST_CHID: &str = "DECAFBAD-0000-0000-0000-0123456789AB";
941 const TOPIC_CHID: &str = "DECAFBAD-1111-0000-0000-0123456789AB";
942
943 fn new_client() -> DbResult<PgClientImpl> {
944 let host = env::var("POSTGRES_HOST").unwrap_or("localhost".into());
946 let env_dsn = format!("postgres://{host}");
947 debug!("📮 Connecting to {env_dsn}");
948 let settings = DbSettings {
949 dsn: Some(env_dsn),
950 db_settings: json!(PostgresDbSettings {
951 schema: Some("autopush".to_owned()),
952 ..Default::default()
953 })
954 .to_string(),
955 };
956 let metrics = Arc::new(StatsdClient::builder("", cadence::NopMetricSink).build());
957 PgClientImpl::new(metrics, &settings)
958 }
959
960 fn gen_test_user() -> String {
961 let mut rng = rand::rng();
963 let test_num = rng.random::<u8>();
964 format!(
965 "DEADBEEF-0000-0000-{:04}-{:012}",
966 test_num,
967 sec_since_epoch()
968 )
969 }
970
971 #[actix_rt::test]
972 async fn health_check() {
973 let client = new_client().unwrap();
974
975 let result = client.health_check().await;
976 assert!(result.is_ok());
977 assert!(result.unwrap());
978 }
979
980 #[actix_rt::test]
982 async fn wipe_expired() -> DbResult<()> {
983 init_test_logging();
984 let client = new_client()?;
985
986 let connected_at = ms_since_epoch();
987
988 let uaid = Uuid::parse_str(&gen_test_user()).unwrap();
989 let chid = Uuid::parse_str(TEST_CHID).unwrap();
990
991 let node_id = "test_node".to_owned();
992
993 let _ = client.remove_user(&uaid).await;
995
996 let test_user = User {
997 uaid,
998 router_type: "webpush".to_owned(),
999 connected_at,
1000 router_data: None,
1001 node_id: Some(node_id.clone()),
1002 ..Default::default()
1003 };
1004
1005 let _ = client.remove_user(&uaid).await;
1008
1009 let timestamp = sec_since_epoch();
1011 client.add_user(&test_user).await?;
1012 let test_notification = crate::db::Notification {
1013 channel_id: chid,
1014 version: "test".to_owned(),
1015 ttl: 1,
1016 timestamp,
1017 data: Some("Encrypted".into()),
1018 sortkey_timestamp: Some(timestamp),
1019 ..Default::default()
1020 };
1021 client.save_message(&uaid, test_notification).await?;
1022 client.increment_storage(&uaid, timestamp + 1).await?;
1023 let msgs = client.fetch_timestamp_messages(&uaid, None, 999).await?;
1024 assert_eq!(msgs.messages.len(), 0);
1025 assert!(client.remove_user(&uaid).await.is_ok());
1026 Ok(())
1027 }
1028
1029 #[actix_rt::test]
1032 async fn run_gauntlet() -> DbResult<()> {
1033 init_test_logging();
1034 let client = new_client()?;
1035
1036 let connected_at = ms_since_epoch();
1037
1038 let user_id = &gen_test_user();
1039 let uaid = Uuid::parse_str(user_id).unwrap();
1040 let chid = Uuid::parse_str(TEST_CHID).unwrap();
1041 let topic_chid = Uuid::parse_str(TOPIC_CHID).unwrap();
1042
1043 let node_id = "test_node".to_owned();
1044
1045 let _ = client.remove_user(&uaid).await;
1047
1048 let test_user = User {
1049 uaid,
1050 router_type: "webpush".to_owned(),
1051 connected_at,
1052 router_data: None,
1053 node_id: Some(node_id.clone()),
1054 ..Default::default()
1055 };
1056
1057 let _ = client.remove_user(&uaid).await;
1060
1061 trace!("📮 Adding user {}", &user_id);
1063 client.add_user(&test_user).await?;
1064 let fetched = client.get_user(&uaid).await?;
1065 assert!(fetched.is_some());
1066 let fetched = fetched.unwrap();
1067 assert_eq!(fetched.router_type, "webpush".to_owned());
1068
1069 trace!("📮 Adding channel {} to user {}", &chid, &user_id);
1071 client.add_channel(&uaid, &chid).await?;
1072 let channels = client.get_channels(&uaid).await?;
1073 assert!(channels.contains(&chid));
1074
1075 let mut new_channels: HashSet<Uuid> = HashSet::new();
1077 trace!("📮 Adding multiple channels to user {}", &user_id);
1078 new_channels.insert(chid);
1079 for _ in 1..10 {
1080 new_channels.insert(uuid::Uuid::new_v4());
1081 }
1082 let chid_to_remove = uuid::Uuid::new_v4();
1083 trace!(
1084 "📮 Adding removable channel {} to user {}",
1085 &chid_to_remove, &user_id
1086 );
1087 new_channels.insert(chid_to_remove);
1088 client.add_channels(&uaid, new_channels.clone()).await?;
1089 let channels = client.get_channels(&uaid).await?;
1090 assert_eq!(channels, new_channels);
1091
1092 trace!(
1094 "📮 Removing channel {} from user {}",
1095 &chid_to_remove, &user_id
1096 );
1097 assert!(client.remove_channel(&uaid, &chid_to_remove).await?);
1098 trace!(
1099 "📮 retrying Removing channel {} from user {}",
1100 &chid_to_remove, &user_id
1101 );
1102 assert!(!client.remove_channel(&uaid, &chid_to_remove).await?);
1103 new_channels.remove(&chid_to_remove);
1104 let channels = client.get_channels(&uaid).await?;
1105 assert_eq!(channels, new_channels);
1106
1107 let mut updated = User {
1111 connected_at,
1112 ..test_user.clone()
1113 };
1114 trace!(
1115 "📮 Attempting to update user {} with old connected_at: {}",
1116 &user_id, &updated.connected_at
1117 );
1118 let result = client.update_user(&mut updated).await;
1119 assert!(result.is_ok());
1120 assert!(!result.unwrap());
1121
1122 let fetched2 = client.get_user(&fetched.uaid).await?.unwrap();
1124 assert_eq!(fetched.connected_at, fetched2.connected_at);
1125
1126 let mut updated = User {
1128 connected_at: fetched.connected_at + 300,
1129 ..fetched2
1130 };
1131 trace!(
1132 "📮 Attempting to update user {} with new connected_at",
1133 &user_id
1134 );
1135 let result = client.update_user(&mut updated).await;
1136 assert!(result.is_ok());
1137 assert!(result.unwrap());
1138 assert_ne!(
1139 fetched2.connected_at,
1140 client.get_user(&uaid).await?.unwrap().connected_at
1141 );
1142 trace!("📮 Incrementing storage timestamp for user {}", &user_id);
1144 client
1145 .increment_storage(&fetched.uaid, sec_since_epoch())
1146 .await?;
1147
1148 let test_data = "An_encrypted_pile_of_crap".to_owned();
1149 let timestamp = sec_since_epoch();
1150 let sort_key = sec_since_epoch();
1151 let fetch_timestamp = timestamp;
1152 let test_notification = crate::db::Notification {
1154 channel_id: chid,
1155 version: "test".to_owned(),
1156 ttl: 300,
1157 timestamp,
1158 data: Some(test_data.clone()),
1159 sortkey_timestamp: Some(sort_key),
1160 ..Default::default()
1161 };
1162 trace!("📮 Saving message for user {}", &user_id);
1163 let res = client.save_message(&uaid, test_notification.clone()).await;
1164 assert!(res.is_ok());
1165
1166 trace!("📮 Fetching all messages for user {}", &user_id);
1167 let mut fetched = client.fetch_timestamp_messages(&uaid, None, 999).await?;
1168 assert_ne!(fetched.messages.len(), 0);
1169 let fm = fetched.messages.pop().unwrap();
1170 assert_eq!(fm.channel_id, test_notification.channel_id);
1171 assert_eq!(fm.data, Some(test_data));
1172
1173 trace!(
1175 "📮 Fetching messages for user {} within the past 10 seconds",
1176 &user_id
1177 );
1178 let fetched = client
1179 .fetch_timestamp_messages(&uaid, Some(fetch_timestamp - 10), 999)
1180 .await?;
1181 assert_ne!(fetched.messages.len(), 0);
1182
1183 trace!(
1185 "📮 Fetching messages for user {} 10 seconds in the future",
1186 &user_id
1187 );
1188 let fetched = client
1189 .fetch_timestamp_messages(&uaid, Some(fetch_timestamp + 10), 999)
1190 .await?;
1191 assert_eq!(fetched.messages.len(), 0);
1192
1193 trace!(
1195 "📮 Removing message for user {} :: {}",
1196 &user_id,
1197 &test_notification.chidmessageid()
1198 );
1199 assert!(
1200 client
1201 .remove_message(&uaid, &test_notification.chidmessageid())
1202 .await
1203 .is_ok()
1204 );
1205
1206 trace!("📮 Removing channel for user {}", &user_id);
1207 assert!(client.remove_channel(&uaid, &chid).await.is_ok());
1208
1209 trace!("📮 Making sure no messages remain for user {}", &user_id);
1210 let msgs = client
1211 .fetch_timestamp_messages(&uaid, None, 999)
1212 .await?
1213 .messages;
1214 assert!(msgs.is_empty());
1215
1216 client.add_channel(&uaid, &topic_chid).await?;
1220 let test_data = "An_encrypted_pile_of_crap_with_a_topic".to_owned();
1221 let timestamp = sec_since_epoch();
1222 let sort_key = sec_since_epoch();
1223
1224 let test_notification_0 = crate::db::Notification {
1226 channel_id: topic_chid,
1227 version: "version0".to_owned(),
1228 ttl: 300,
1229 topic: Some("topic".to_owned()),
1230 timestamp,
1231 data: Some(test_data.clone()),
1232 sortkey_timestamp: Some(sort_key),
1233 ..Default::default()
1234 };
1235 assert!(
1236 client
1237 .save_message(&uaid, test_notification_0.clone())
1238 .await
1239 .is_ok()
1240 );
1241
1242 let test_notification = crate::db::Notification {
1243 timestamp: sec_since_epoch(),
1244 version: "version1".to_owned(),
1245 sortkey_timestamp: Some(sort_key + 10),
1246 ..test_notification_0
1247 };
1248
1249 assert!(
1250 client
1251 .save_message(&uaid, test_notification.clone())
1252 .await
1253 .is_ok()
1254 );
1255
1256 let mut fetched = client.fetch_timestamp_messages(&uaid, None, 999).await?;
1257 assert_eq!(fetched.messages.len(), 1);
1258 let fm = fetched.messages.pop().unwrap();
1259 assert_eq!(fm.channel_id, test_notification.channel_id);
1260 assert_eq!(fm.data, Some(test_data));
1261
1262 let fetched = client.fetch_timestamp_messages(&uaid, None, 999).await?;
1264 assert_ne!(fetched.messages.len(), 0);
1265
1266 assert!(
1268 client
1269 .remove_message(&uaid, &test_notification.chidmessageid())
1270 .await
1271 .is_ok()
1272 );
1273
1274 assert!(client.remove_channel(&uaid, &topic_chid).await.is_ok());
1275
1276 let msgs = client
1277 .fetch_timestamp_messages(&uaid, None, 999)
1278 .await?
1279 .messages;
1280 assert!(msgs.is_empty());
1281
1282 let fetched = client.get_user(&uaid).await?.unwrap();
1283 assert!(
1284 client
1285 .remove_node_id(&uaid, &node_id, fetched.connected_at, &fetched.version)
1286 .await
1287 .is_ok()
1288 );
1289 let fetched = client.get_user(&uaid).await?.unwrap();
1291 assert_eq!(fetched.node_id, None);
1292
1293 assert!(client.remove_user(&uaid).await.is_ok());
1294
1295 assert!(client.get_user(&uaid).await?.is_none());
1296 Ok(())
1297 }
1298
1299 #[actix_rt::test]
1300 async fn test_expiry() -> DbResult<()> {
1301 init_test_logging();
1303 let client = new_client()?;
1304
1305 let uaid = Uuid::parse_str(&gen_test_user()).unwrap();
1306 let chid = Uuid::parse_str(TEST_CHID).unwrap();
1307 let now = sec_since_epoch();
1308
1309 let test_notification = crate::db::Notification {
1310 channel_id: chid,
1311 version: "test".to_owned(),
1312 ttl: 2,
1313 timestamp: now,
1314 data: Some("SomeData".into()),
1315 sortkey_timestamp: Some(now),
1316 ..Default::default()
1317 };
1318 client
1319 .add_user(&User {
1320 uaid,
1321 router_type: "test".to_owned(),
1322 connected_at: ms_since_epoch(),
1323 ..Default::default()
1324 })
1325 .await?;
1326 client.add_channel(&uaid, &chid).await?;
1327 debug!("🧪Writing test notif");
1328 client
1329 .save_message(&uaid, test_notification.clone())
1330 .await?;
1331 let key = uaid.simple().to_string();
1332 debug!("🧪Checking {}...", &key);
1333 let msg = client
1334 .fetch_timestamp_messages(&uaid, None, 1)
1335 .await?
1336 .messages
1337 .pop();
1338 assert!(msg.is_some());
1339 debug!("🧪Purging...");
1340 client.increment_storage(&uaid, now + 2).await?;
1341 debug!("🧪Checking for empty {}...", &key);
1342 let cc = client
1343 .fetch_timestamp_messages(&uaid, None, 1)
1344 .await?
1345 .messages
1346 .pop();
1347 assert!(cc.is_none());
1348 assert!(client.remove_user(&uaid).await.is_ok());
1350 Ok(())
1351 }
1352}