use crate::queue::message_queue::{Message, MessageQueueError, MessageQueueRepository};
use async_trait::async_trait;
use chrono::{DateTime, Utc};
use dbnexus::sea_orm;
use dbnexus::sea_orm::{
ConnectionTrait, DatabaseBackend, DbErr, FromQueryResult, JsonValue, Statement,
};
use dbnexus::DbPool;
use std::sync::Arc;
impl From<DbErr> for MessageQueueError {
fn from(e: DbErr) -> Self {
MessageQueueError::Database(e.to_string())
}
}
pub struct PostgresMessageQueueRepository {
pool: Arc<DbPool>,
}
impl PostgresMessageQueueRepository {
pub fn new(pool: Arc<DbPool>) -> Self {
Self { pool }
}
async fn get_conn(&self) -> Result<dbnexus::sea_orm::DatabaseConnection, MessageQueueError> {
let session = self
.pool
.get_session("system")
.await
.map_err(|e| MessageQueueError::Database(e.to_string()))?;
let conn = session
.connection()
.map_err(|e| MessageQueueError::Database(e.to_string()))?;
Ok(conn.clone())
}
}
#[derive(Debug, FromQueryResult)]
struct RawMessage {
msg_id: i64,
queue_name: String,
message: JsonValue,
read_ct: i32,
enqueued_at: DateTime<Utc>,
}
impl From<RawMessage> for Message {
fn from(raw: RawMessage) -> Self {
Self {
msg_id: raw.msg_id,
queue_name: raw.queue_name,
message: raw.message,
read_ct: raw.read_ct,
enqueued_at: raw.enqueued_at,
}
}
}
#[async_trait]
impl MessageQueueRepository for PostgresMessageQueueRepository {
async fn send(
&self,
queue_name: &str,
message: &serde_json::Value,
) -> Result<i64, MessageQueueError> {
let conn = self.get_conn().await?;
let stmt = Statement::from_sql_and_values(
DatabaseBackend::Postgres,
r#"INSERT INTO message_queues (queue_name, message)
VALUES ($1, $2) RETURNING msg_id"#,
[queue_name.into(), message.clone().into()],
);
let result = conn
.query_one_raw(stmt)
.await?
.ok_or_else(|| MessageQueueError::Database("INSERT RETURNING failed".into()))?;
result
.try_get("", "msg_id")
.map_err(|e| MessageQueueError::Database(e.to_string()))
}
async fn send_batch(
&self,
queue_name: &str,
messages: &[serde_json::Value],
) -> Result<Vec<i64>, MessageQueueError> {
if messages.is_empty() {
return Ok(vec![]);
}
let conn = self.get_conn().await?;
let mut value_placeholders = Vec::with_capacity(messages.len());
let mut values = Vec::with_capacity(messages.len() * 2);
for (i, msg) in messages.iter().enumerate() {
let p1 = 2 * i as i32 + 1;
let p2 = 2 * i as i32 + 2;
value_placeholders.push(format!("(${}, ${})", p1, p2));
values.push(queue_name.into());
values.push(msg.clone().into());
}
let sql = format!(
"INSERT INTO message_queues (queue_name, message) VALUES {} RETURNING msg_id",
value_placeholders.join(", ")
);
let stmt = Statement::from_sql_and_values(DatabaseBackend::Postgres, &sql, values);
let rows = conn.query_all_raw(stmt).await?;
let mut msg_ids = Vec::with_capacity(messages.len());
for row in rows {
let msg_id: i64 = row
.try_get("", "msg_id")
.map_err(|e| MessageQueueError::Database(e.to_string()))?;
msg_ids.push(msg_id);
}
Ok(msg_ids)
}
async fn read_batch(
&self,
queue_name: &str,
vt: i32,
batch_size: i32,
) -> Result<Vec<Message>, MessageQueueError> {
let conn = self.get_conn().await?;
let stmt = Statement::from_sql_and_values(
DatabaseBackend::Postgres,
r#"
WITH available AS (
SELECT msg_id
FROM message_queues
WHERE queue_name = $1
AND archived = FALSE
AND (vt IS NULL OR vt <= NOW())
ORDER BY msg_id ASC
LIMIT $2
FOR UPDATE SKIP LOCKED
)
UPDATE message_queues mq
SET vt = NOW() + ($3 || ' seconds')::INTERVAL,
read_ct = mq.read_ct + 1,
updated_at = NOW()
FROM available a
WHERE mq.msg_id = a.msg_id
RETURNING mq.msg_id, mq.queue_name, mq.message, mq.read_ct, mq.enqueued_at
"#,
[
queue_name.into(),
(batch_size as i64).into(),
vt.to_string().into(),
],
);
let rows = conn.query_all_raw(stmt).await?;
let messages: Vec<Message> = rows
.into_iter()
.filter_map(|row| {
RawMessage::from_query_result(&row, "")
.ok()
.map(Message::from)
})
.collect();
Ok(messages)
}
async fn delete(&self, queue_name: &str, msg_id: i64) -> Result<bool, MessageQueueError> {
let conn = self.get_conn().await?;
let stmt = Statement::from_sql_and_values(
DatabaseBackend::Postgres,
"DELETE FROM message_queues WHERE queue_name = $1 AND msg_id = $2 AND archived = FALSE",
[queue_name.into(), msg_id.into()],
);
let result = conn.execute_raw(stmt).await?;
Ok(result.rows_affected() > 0)
}
async fn delete_batch(
&self,
queue_name: &str,
msg_ids: &[i64],
) -> Result<u64, MessageQueueError> {
if msg_ids.is_empty() {
return Ok(0);
}
let conn = self.get_conn().await?;
let placeholders: Vec<String> = (2..2 + msg_ids.len() as i32)
.map(|i| format!("${}", i))
.collect();
let sql = format!(
"DELETE FROM message_queues WHERE queue_name = $1 AND msg_id IN ({}) AND archived = FALSE",
placeholders.join(", ")
);
let mut values: Vec<dbnexus::sea_orm::Value> = vec![queue_name.into()];
for id in msg_ids {
values.push((*id).into());
}
let stmt = Statement::from_sql_and_values(DatabaseBackend::Postgres, &sql, values);
let result = conn.execute_raw(stmt).await?;
Ok(result.rows_affected())
}
async fn archive(&self, queue_name: &str, msg_id: i64) -> Result<bool, MessageQueueError> {
let conn = self.get_conn().await?;
let stmt = Statement::from_sql_and_values(
DatabaseBackend::Postgres,
r#"
WITH moved AS (
INSERT INTO message_queues_archive
(msg_id, queue_name, message, read_ct, enqueued_at, updated_at, vt)
SELECT msg_id, queue_name, message, read_ct, enqueued_at, updated_at, vt
FROM message_queues
WHERE queue_name = $1 AND msg_id = $2 AND archived = FALSE
RETURNING msg_id
)
UPDATE message_queues
SET archived = TRUE, updated_at = NOW()
WHERE queue_name = $1 AND msg_id = $2 AND archived = FALSE
AND EXISTS (SELECT 1 FROM moved)
"#,
[queue_name.into(), msg_id.into()],
);
let result = conn.execute_raw(stmt).await?;
Ok(result.rows_affected() > 0)
}
async fn archive_batch(
&self,
queue_name: &str,
msg_ids: &[i64],
) -> Result<u64, MessageQueueError> {
if msg_ids.is_empty() {
return Ok(0);
}
let conn = self.get_conn().await?;
let placeholders: Vec<String> = (2..2 + msg_ids.len() as i32)
.map(|i| format!("${}", i))
.collect();
let sql = format!(
r#"
WITH moved AS (
INSERT INTO message_queues_archive
(msg_id, queue_name, message, read_ct, enqueued_at, updated_at, vt)
SELECT msg_id, queue_name, message, read_ct, enqueued_at, updated_at, vt
FROM message_queues
WHERE queue_name = $1 AND msg_id IN ({}) AND archived = FALSE
RETURNING msg_id
)
UPDATE message_queues
SET archived = TRUE, updated_at = NOW()
WHERE queue_name = $1 AND msg_id IN ({}) AND archived = FALSE
AND EXISTS (
SELECT 1 FROM moved WHERE moved.msg_id = message_queues.msg_id
)
"#,
placeholders.join(", "),
placeholders.join(", "),
);
let mut values: Vec<dbnexus::sea_orm::Value> = vec![queue_name.into()];
for id in msg_ids {
values.push((*id).into());
}
for id in msg_ids {
values.push((*id).into());
}
let stmt = Statement::from_sql_and_values(DatabaseBackend::Postgres, &sql, values);
let result = conn.execute_raw(stmt).await?;
Ok(result.rows_affected())
}
}