use sqlx::{Sqlite, Transaction};
use crate::error_handling::DatabaseError;
use crate::storage::insert::utils::build_batch_insert_query;
pub(crate) async fn insert_analytics_ids_in_tx(
tx: &mut Transaction<'_, Sqlite>,
url_status_id: i64,
analytics_ids: &[crate::parse::AnalyticsId],
) -> Result<(), DatabaseError> {
if analytics_ids.is_empty() {
return Ok(());
}
let query = build_batch_insert_query(
"url_analytics_ids",
&["url_status_id", "provider", "tracking_id"],
analytics_ids.len(),
Some("ON CONFLICT(url_status_id, provider, tracking_id) DO NOTHING"),
);
let mut query_builder = crate::sql::query(query);
for analytics_id in analytics_ids {
query_builder = query_builder
.bind(url_status_id)
.bind(analytics_id.provider.as_str())
.bind(&analytics_id.id);
}
query_builder
.execute(&mut **tx)
.await
.map_err(DatabaseError::SqlError)?;
Ok(())
}
#[cfg(test)]
mod tests {
use super::*;
use crate::parse::{AnalyticsId, AnalyticsProvider};
use sqlx::Row;
use crate::storage::insert::enrichment::commit_in_tx;
use crate::storage::test_helpers::{create_test_pool, create_test_url_status_default};
#[tokio::test]
async fn test_insert_analytics_ids_basic() {
let pool = create_test_pool().await;
let url_status_id = create_test_url_status_default(&pool).await;
let analytics_ids = vec![
AnalyticsId {
provider: AnalyticsProvider::GoogleAnalytics,
id: "UA-123456-1".to_string(),
},
AnalyticsId {
provider: AnalyticsProvider::GoogleTagManager,
id: "GTM-XXXXX".to_string(),
},
];
let result = commit_in_tx!(&pool, |tx| {
insert_analytics_ids_in_tx(&mut tx, url_status_id, &analytics_ids).await
});
assert!(result.is_ok());
let rows = sqlx::query(
"SELECT provider, tracking_id FROM url_analytics_ids WHERE url_status_id = ? ORDER BY provider",
)
.bind(url_status_id)
.fetch_all(&pool)
.await
.expect("Failed to fetch analytics IDs");
assert_eq!(rows.len(), 2);
assert_eq!(rows[0].get::<String, _>("provider"), "Google Analytics");
assert_eq!(rows[0].get::<String, _>("tracking_id"), "UA-123456-1");
assert_eq!(rows[1].get::<String, _>("provider"), "Google Tag Manager");
assert_eq!(rows[1].get::<String, _>("tracking_id"), "GTM-XXXXX");
}
#[tokio::test]
async fn test_insert_analytics_ids_empty() {
let pool = create_test_pool().await;
let url_status_id = create_test_url_status_default(&pool).await;
let analytics_ids = vec![];
let result = commit_in_tx!(&pool, |tx| {
insert_analytics_ids_in_tx(&mut tx, url_status_id, &analytics_ids).await
});
assert!(result.is_ok());
let count: i64 =
sqlx::query_scalar("SELECT COUNT(*) FROM url_analytics_ids WHERE url_status_id = ?")
.bind(url_status_id)
.fetch_one(&pool)
.await
.expect("Failed to count analytics IDs");
assert_eq!(count, 0);
}
#[tokio::test]
async fn test_insert_analytics_ids_duplicate() {
let pool = create_test_pool().await;
let url_status_id = create_test_url_status_default(&pool).await;
let analytics_ids = vec![AnalyticsId {
provider: AnalyticsProvider::GoogleAnalytics,
id: "UA-123456-1".to_string(),
}];
let result1 = commit_in_tx!(&pool, |tx| {
insert_analytics_ids_in_tx(&mut tx, url_status_id, &analytics_ids).await
});
assert!(result1.is_ok());
let result2 = commit_in_tx!(&pool, |tx| {
insert_analytics_ids_in_tx(&mut tx, url_status_id, &analytics_ids).await
});
assert!(result2.is_ok());
let count: i64 =
sqlx::query_scalar("SELECT COUNT(*) FROM url_analytics_ids WHERE url_status_id = ?")
.bind(url_status_id)
.fetch_one(&pool)
.await
.expect("Failed to count analytics IDs");
assert_eq!(count, 1);
}
#[tokio::test]
async fn test_insert_analytics_ids_multiple_providers() {
let pool = create_test_pool().await;
let url_status_id = create_test_url_status_default(&pool).await;
let analytics_ids = vec![
AnalyticsId {
provider: AnalyticsProvider::GoogleAnalytics,
id: "UA-123456-1".to_string(),
},
AnalyticsId {
provider: AnalyticsProvider::GoogleAnalytics,
id: "UA-789012-3".to_string(),
},
AnalyticsId {
provider: AnalyticsProvider::FacebookPixel,
id: "123456789".to_string(),
},
];
let result = commit_in_tx!(&pool, |tx| {
insert_analytics_ids_in_tx(&mut tx, url_status_id, &analytics_ids).await
});
assert!(result.is_ok());
let count: i64 =
sqlx::query_scalar("SELECT COUNT(*) FROM url_analytics_ids WHERE url_status_id = ?")
.bind(url_status_id)
.fetch_one(&pool)
.await
.expect("Failed to count analytics IDs");
assert_eq!(count, 3);
}
}