use sqlx::{Row, SqlitePool};
use crate::error_handling::DatabaseError;
use super::super::models::{UrlFailureRecord, UrlPartialFailureRecord};
use super::retry::with_sqlite_retry;
use super::utils::insert_key_value_batch;
pub async fn insert_url_failure(
pool: &SqlitePool,
failure: &UrlFailureRecord,
) -> Result<i64, DatabaseError> {
with_sqlite_retry(|| insert_url_failure_impl(pool, failure)).await
}
async fn insert_failure_redirect_chain(
tx: &mut sqlx::Transaction<'_, sqlx::Sqlite>,
failure_id: i64,
redirect_chain: &[String],
) -> Result<(), DatabaseError> {
if redirect_chain.is_empty() {
return Ok(());
}
use super::utils::build_batch_insert_query;
let query = build_batch_insert_query(
"url_failure_redirect_chain",
&["url_failure_id", "sequence_order", "redirect_url"],
redirect_chain.len(),
Some("ON CONFLICT(url_failure_id, sequence_order) DO NOTHING"),
);
let mut query_builder = crate::sql::query(query);
for (order, redirect_url) in redirect_chain.iter().enumerate() {
#[allow(clippy::cast_possible_wrap)] let sequence_order = (order + 1) as i64; query_builder = query_builder
.bind(failure_id)
.bind(sequence_order)
.bind(redirect_url);
}
query_builder.execute(&mut **tx).await.map_err(|e| {
log::error!("Failed to batch insert redirect chain for failure_id {failure_id}: {e}");
DatabaseError::SqlError(e)
})?;
Ok(())
}
async fn insert_failure_response_headers(
tx: &mut sqlx::Transaction<'_, sqlx::Sqlite>,
failure_id: i64,
response_headers: &[(String, String)],
) -> Result<(), DatabaseError> {
if response_headers.is_empty() {
return Ok(());
}
insert_key_value_batch(
tx,
"url_failure_response_headers",
"url_failure_id",
"header_name",
"header_value",
failure_id,
response_headers,
Some("ON CONFLICT(url_failure_id, header_name) DO UPDATE SET header_value=excluded.header_value"),
)
.await
.map_err(|e| {
log::error!(
"Failed to insert {} response headers for failure_id {}: {}",
response_headers.len(),
failure_id,
e
);
DatabaseError::SqlError(e)
})
}
async fn insert_failure_satellite_data(
tx: &mut sqlx::Transaction<'_, sqlx::Sqlite>,
failure_id: i64,
failure: &UrlFailureRecord,
) -> Result<(), DatabaseError> {
insert_failure_redirect_chain(tx, failure_id, &failure.redirect_chain).await?;
insert_failure_response_headers(tx, failure_id, &failure.response_headers).await?;
Ok(())
}
async fn insert_url_failure_impl(
pool: &SqlitePool,
failure: &UrlFailureRecord,
) -> Result<i64, DatabaseError> {
let mut tx = pool.begin().await.map_err(DatabaseError::SqlError)?;
let failure_id_result = sqlx::query(
"INSERT INTO url_failures (
attempted_url, final_url, initial_domain, final_domain, error_type, error_message,
http_status, retry_count, elapsed_time_seconds, observed_at_ms, run_id
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
RETURNING id",
)
.bind(&failure.url)
.bind(failure.final_url.as_ref())
.bind(&failure.domain)
.bind(failure.final_domain.as_ref())
.bind(failure.error_type.as_str())
.bind(&failure.error_message)
.bind(failure.http_status.map(i64::from))
.bind(i64::from(failure.retry_count))
.bind(failure.elapsed_time_seconds)
.bind(failure.timestamp)
.bind(failure.run_id.as_ref())
.fetch_one(&mut *tx)
.await
.map_err(|e| {
log::error!(
"Failed to insert URL failure record for url '{}' (domain: {}, error_type: {}, timestamp: {}): {} (SQL: INSERT INTO url_failures ... RETURNING id)",
failure.url,
failure.domain,
failure.error_type.as_str(),
failure.timestamp,
e
);
DatabaseError::SqlError(e)
});
let failure_id = match failure_id_result {
Ok(row) => row.get::<i64, _>(0),
Err(e) => {
return Err(e);
}
};
let satellite_result = insert_failure_satellite_data(&mut tx, failure_id, failure).await;
match satellite_result {
Ok(()) => {
tx.commit().await.map_err(|e| {
log::error!("Failed to commit transaction for failure_id {failure_id}: {e}");
DatabaseError::SqlError(e)
})?;
Ok(failure_id)
}
Err(e) => Err(e),
}
}
#[cfg_attr(not(test), allow(dead_code))] pub async fn insert_url_partial_failure(
pool: &SqlitePool,
partial_failure: &UrlPartialFailureRecord,
) -> Result<i64, DatabaseError> {
with_sqlite_retry(|| insert_url_partial_failure_impl(pool, partial_failure)).await
}
#[cfg_attr(not(test), allow(dead_code))]
async fn insert_url_partial_failure_impl(
pool: &SqlitePool,
partial_failure: &UrlPartialFailureRecord,
) -> Result<i64, DatabaseError> {
let partial_failure_id = sqlx::query(
"INSERT INTO url_partial_failures (
url_status_id, error_type, error_message, observed_at_ms, run_id
) VALUES (?, ?, ?, ?, ?)
RETURNING id",
)
.bind(partial_failure.url_status_id)
.bind(partial_failure.error_type.as_str())
.bind(&partial_failure.error_message)
.bind(partial_failure.timestamp)
.bind(partial_failure.run_id.as_ref())
.fetch_one(pool)
.await
.map_err(DatabaseError::SqlError)?
.get::<i64, _>(0);
Ok(partial_failure_id)
}
pub(crate) async fn insert_url_partial_failure_in_tx(
tx: &mut sqlx::Transaction<'_, sqlx::Sqlite>,
partial_failure: &UrlPartialFailureRecord,
) -> Result<i64, DatabaseError> {
let partial_failure_id = sqlx::query(
"INSERT INTO url_partial_failures (
url_status_id, error_type, error_message, observed_at_ms, run_id
) VALUES (?, ?, ?, ?, ?)
RETURNING id",
)
.bind(partial_failure.url_status_id)
.bind(partial_failure.error_type.as_str())
.bind(&partial_failure.error_message)
.bind(partial_failure.timestamp)
.bind(partial_failure.run_id.as_ref())
.fetch_one(&mut **tx)
.await
.map_err(DatabaseError::SqlError)?
.get::<i64, _>(0);
Ok(partial_failure_id)
}
#[cfg(test)]
mod tests {
use super::*;
use crate::error_handling::ErrorType;
use crate::storage::models::{UrlFailureRecord, UrlPartialFailureRecord};
use sqlx::Row;
use crate::storage::test_helpers::{
create_test_pool, create_test_run, create_test_url_status_default,
};
#[tokio::test]
async fn test_insert_url_failure_basic() {
let pool = create_test_pool().await;
create_test_run(&pool, "test-run-123", 1704067200000i64).await;
let failure = UrlFailureRecord {
url: "http://example.com".to_string(),
final_url: Some("https://example.com".to_string()),
domain: "example.com".to_string(),
final_domain: Some("example.com".to_string()),
error_type: ErrorType::HttpRequestOtherError,
error_message: "Connection timeout".to_string(),
http_status: None,
retry_count: 3,
elapsed_time_seconds: Some(5.5),
timestamp: 1704067200000,
run_id: Some("test-run-123".to_string()),
redirect_chain: vec![],
response_headers: vec![],
};
let result = insert_url_failure(&pool, &failure).await;
assert!(result.is_ok());
let failure_id = result.unwrap();
let row = sqlx::query(
"SELECT attempted_url, final_url, initial_domain, error_type, error_message, retry_count FROM url_failures WHERE id = ?",
)
.bind(failure_id)
.fetch_one(&pool)
.await
.expect("Failed to fetch failure record");
assert_eq!(row.get::<String, _>("attempted_url"), "http://example.com");
assert_eq!(
row.get::<Option<String>, _>("final_url"),
Some("https://example.com".to_string())
);
assert_eq!(row.get::<String, _>("initial_domain"), "example.com");
assert_eq!(
row.get::<String, _>("error_type"),
ErrorType::HttpRequestOtherError.as_str()
);
assert_eq!(row.get::<String, _>("error_message"), "Connection timeout");
assert_eq!(row.get::<i64, _>("retry_count"), 3);
}
#[tokio::test]
async fn test_insert_url_failure_with_redirect_chain() {
let pool = create_test_pool().await;
let failure = UrlFailureRecord {
url: "http://example.com".to_string(),
final_url: Some("https://www.example.com".to_string()),
domain: "example.com".to_string(),
final_domain: Some("www.example.com".to_string()),
error_type: ErrorType::HttpRequestOtherError,
error_message: "500 Internal Server Error".to_string(),
http_status: Some(500),
retry_count: 0,
elapsed_time_seconds: Some(2.0),
timestamp: 1704067200000,
run_id: None,
redirect_chain: vec![
"http://example.com".to_string(),
"https://example.com".to_string(),
"https://www.example.com".to_string(),
],
response_headers: vec![],
};
let result = insert_url_failure(&pool, &failure).await;
assert!(result.is_ok());
let failure_id = result.unwrap();
let rows = sqlx::query(
"SELECT redirect_url, sequence_order FROM url_failure_redirect_chain WHERE url_failure_id = ? ORDER BY sequence_order",
)
.bind(failure_id)
.fetch_all(&pool)
.await
.expect("Failed to fetch redirect chain");
assert_eq!(rows.len(), 3);
assert_eq!(
rows[0].get::<String, _>("redirect_url"),
"http://example.com"
);
assert_eq!(rows[0].get::<i64, _>("sequence_order"), 1); assert_eq!(
rows[1].get::<String, _>("redirect_url"),
"https://example.com"
);
assert_eq!(rows[1].get::<i64, _>("sequence_order"), 2); assert_eq!(
rows[2].get::<String, _>("redirect_url"),
"https://www.example.com"
);
assert_eq!(rows[2].get::<i64, _>("sequence_order"), 3); }
#[tokio::test]
async fn test_insert_url_failure_with_headers() {
let pool = create_test_pool().await;
create_test_run(&pool, "test-run-456", 1704067200000i64).await;
let failure = UrlFailureRecord {
url: "http://example.com".to_string(),
final_url: None,
domain: "example.com".to_string(),
final_domain: None,
error_type: ErrorType::HttpRequestOtherError,
error_message: "403 Forbidden".to_string(),
http_status: Some(403),
retry_count: 1,
elapsed_time_seconds: None,
timestamp: 1704067200000,
run_id: Some("test-run-456".to_string()),
redirect_chain: vec![],
response_headers: vec![
("Server".to_string(), "nginx/1.18.0".to_string()),
("Content-Type".to_string(), "text/html".to_string()),
],
};
let result = insert_url_failure(&pool, &failure).await;
assert!(result.is_ok());
let failure_id = result.unwrap();
let response_rows = sqlx::query(
"SELECT header_name, header_value FROM url_failure_response_headers WHERE url_failure_id = ? ORDER BY header_name",
)
.bind(failure_id)
.fetch_all(&pool)
.await
.expect("Failed to fetch response headers");
assert_eq!(response_rows.len(), 2);
assert_eq!(
response_rows[0].get::<String, _>("header_name"),
"Content-Type"
);
assert_eq!(response_rows[1].get::<String, _>("header_name"), "Server");
}
#[tokio::test]
async fn test_insert_url_failure_with_http_status() {
let pool = create_test_pool().await;
let failure = UrlFailureRecord {
url: "http://example.com".to_string(),
final_url: None,
domain: "example.com".to_string(),
final_domain: None,
error_type: ErrorType::HttpRequestOtherError,
error_message: "404 Not Found".to_string(),
http_status: Some(404),
retry_count: 0,
elapsed_time_seconds: Some(1.5),
timestamp: 1704067200000,
run_id: None,
redirect_chain: vec![],
response_headers: vec![],
};
let result = insert_url_failure(&pool, &failure).await;
assert!(result.is_ok());
let failure_id = result.unwrap();
let row = sqlx::query("SELECT http_status FROM url_failures WHERE id = ?")
.bind(failure_id)
.fetch_one(&pool)
.await
.expect("Failed to fetch failure record");
assert_eq!(row.get::<Option<i64>, _>("http_status"), Some(404));
}
#[tokio::test]
async fn test_insert_url_partial_failure_basic() {
let pool = create_test_pool().await;
create_test_run(&pool, "test-run-789", 1704067200000i64).await;
let url_status_id = create_test_url_status_default(&pool).await;
let partial_failure = UrlPartialFailureRecord {
url_status_id,
error_type: ErrorType::DnsNsLookupError,
error_message: "DNS lookup failed".to_string(),
timestamp: 1704067200000,
run_id: Some("test-run-789".to_string()),
};
let result = insert_url_partial_failure(&pool, &partial_failure).await;
assert!(result.is_ok());
let partial_failure_id = result.unwrap();
let row = sqlx::query(
"SELECT url_status_id, error_type, error_message, run_id FROM url_partial_failures WHERE id = ?",
)
.bind(partial_failure_id)
.fetch_one(&pool)
.await
.expect("Failed to fetch partial failure record");
assert_eq!(row.get::<i64, _>("url_status_id"), url_status_id);
assert_eq!(
row.get::<String, _>("error_type"),
ErrorType::DnsNsLookupError.as_str()
);
assert_eq!(row.get::<String, _>("error_message"), "DNS lookup failed");
assert_eq!(
row.get::<Option<String>, _>("run_id"),
Some("test-run-789".to_string())
);
}
#[tokio::test]
async fn test_insert_url_partial_failure_satellite_insert_error() {
let pool = create_test_pool().await;
create_test_run(&pool, "test-run-sat", 1704067200000i64).await;
let url_status_id = create_test_url_status_default(&pool).await;
let partial_failure = UrlPartialFailureRecord {
url_status_id,
error_type: ErrorType::SatelliteInsertError,
error_message: "url_cookies: database is locked".to_string(),
timestamp: 1704067200000,
run_id: Some("test-run-sat".to_string()),
};
let id = insert_url_partial_failure(&pool, &partial_failure)
.await
.expect("insert satellite insert error");
let error_type: String =
sqlx::query_scalar("SELECT error_type FROM url_partial_failures WHERE id = ?")
.bind(id)
.fetch_one(&pool)
.await
.expect("fetch");
assert_eq!(error_type, "Satellite insert error");
}
#[tokio::test]
async fn test_insert_url_partial_failure_without_run_id() {
let pool = create_test_pool().await;
let url_status_id = create_test_url_status_default(&pool).await;
let partial_failure = UrlPartialFailureRecord {
url_status_id,
error_type: ErrorType::TlsCertificateError,
error_message: "Certificate validation failed".to_string(),
timestamp: 1704067200000,
run_id: None,
};
let result = insert_url_partial_failure(&pool, &partial_failure).await;
assert!(result.is_ok());
let partial_failure_id = result.unwrap();
let row = sqlx::query(
"SELECT error_type, error_message, run_id FROM url_partial_failures WHERE id = ?",
)
.bind(partial_failure_id)
.fetch_one(&pool)
.await
.expect("Failed to fetch partial failure record");
assert_eq!(
row.get::<String, _>("error_type"),
ErrorType::TlsCertificateError.as_str()
);
assert_eq!(
row.get::<String, _>("error_message"),
"Certificate validation failed"
);
assert_eq!(row.get::<Option<String>, _>("run_id"), None);
}
#[tokio::test]
async fn test_insert_url_partial_failure_multiple() {
let pool = create_test_pool().await;
let url_status_id = create_test_url_status_default(&pool).await;
let failure1 = UrlPartialFailureRecord {
url_status_id,
error_type: ErrorType::DnsNsLookupError,
error_message: "DNS lookup failed".to_string(),
timestamp: 1704067200000,
run_id: None,
};
let failure2 = UrlPartialFailureRecord {
url_status_id,
error_type: ErrorType::TlsCertificateError,
error_message: "TLS handshake failed".to_string(),
timestamp: 1704067201000,
run_id: None,
};
let result1 = insert_url_partial_failure(&pool, &failure1).await;
let result2 = insert_url_partial_failure(&pool, &failure2).await;
assert!(result1.is_ok());
assert!(result2.is_ok());
let count: i64 =
sqlx::query_scalar("SELECT COUNT(*) FROM url_partial_failures WHERE url_status_id = ?")
.bind(url_status_id)
.fetch_one(&pool)
.await
.expect("Failed to count partial failures");
assert_eq!(count, 2);
}
#[tokio::test]
async fn test_insert_url_failure_transaction_rollback_on_satellite_failure() {
let pool = create_test_pool().await;
create_test_run(&pool, "test-run-rollback", 1704067200000i64).await;
let failure = UrlFailureRecord {
url: "http://example.com".to_string(),
final_url: None,
domain: "example.com".to_string(),
final_domain: None,
error_type: ErrorType::HttpRequestOtherError,
error_message: "Test error".to_string(),
http_status: None,
retry_count: 0,
elapsed_time_seconds: None,
timestamp: 1704067200000,
run_id: Some("test-run-rollback".to_string()),
redirect_chain: vec![],
response_headers: vec![],
};
let result = insert_url_failure(&pool, &failure).await;
assert!(result.is_ok());
}
#[tokio::test]
async fn test_insert_url_failure_redirect_chain_sequence_ordering() {
let pool = create_test_pool().await;
let failure = UrlFailureRecord {
url: "http://example.com".to_string(),
final_url: Some("https://www.example.com".to_string()),
domain: "example.com".to_string(),
final_domain: Some("www.example.com".to_string()),
error_type: ErrorType::HttpRequestOtherError,
error_message: "500 Internal Server Error".to_string(),
http_status: Some(500),
retry_count: 0,
elapsed_time_seconds: Some(2.0),
timestamp: 1704067200000,
run_id: None,
redirect_chain: vec![
"http://example.com".to_string(),
"https://example.com".to_string(),
"https://www.example.com".to_string(),
],
response_headers: vec![],
};
let result = insert_url_failure(&pool, &failure).await;
assert!(result.is_ok());
let failure_id = result.unwrap();
let first_redirect = sqlx::query(
"SELECT sequence_order FROM url_failure_redirect_chain WHERE url_failure_id = ? ORDER BY sequence_order LIMIT 1",
)
.bind(failure_id)
.fetch_one(&pool)
.await
.expect("Failed to fetch first redirect");
let sequence_order: i64 = first_redirect.get("sequence_order");
assert_eq!(
sequence_order, 1,
"Redirect chain sequence_order should be 1-based, not 0-based"
);
}
#[tokio::test]
async fn test_insert_url_failure_redirect_chain_empty_preserves_ordering() {
let pool = create_test_pool().await;
let failure = UrlFailureRecord {
url: "http://example.com".to_string(),
final_url: None,
domain: "example.com".to_string(),
final_domain: None,
error_type: ErrorType::HttpRequestOtherError,
error_message: "Connection timeout".to_string(),
http_status: None,
retry_count: 0,
elapsed_time_seconds: None,
timestamp: 1704067200000,
run_id: None,
redirect_chain: vec![], response_headers: vec![],
};
let result = insert_url_failure(&pool, &failure).await;
assert!(result.is_ok());
let failure_id = result.unwrap();
let count: i64 = sqlx::query_scalar(
"SELECT COUNT(*) FROM url_failure_redirect_chain WHERE url_failure_id = ?",
)
.bind(failure_id)
.fetch_one(&pool)
.await
.expect("Failed to count redirect chain entries");
assert_eq!(count, 0, "Empty redirect chain should result in 0 entries");
}
#[tokio::test]
async fn test_insert_url_failure_response_headers_conflict_handling() {
let pool = create_test_pool().await;
create_test_run(&pool, "test-run-headers", 1704067200000i64).await;
let failure = UrlFailureRecord {
url: "http://example.com".to_string(),
final_url: None,
domain: "example.com".to_string(),
final_domain: None,
error_type: ErrorType::HttpRequestOtherError,
error_message: "Test error".to_string(),
http_status: None,
retry_count: 0,
elapsed_time_seconds: None,
timestamp: 1704067200000,
run_id: Some("test-run-headers".to_string()),
redirect_chain: vec![],
response_headers: vec![
("Server".to_string(), "nginx/1.18.0".to_string()),
("Server".to_string(), "nginx/1.20.0".to_string()), ],
};
let result = insert_url_failure(&pool, &failure).await;
assert!(result.is_ok());
let failure_id = result.unwrap();
let count: i64 = sqlx::query_scalar(
"SELECT COUNT(*) FROM url_failure_response_headers WHERE url_failure_id = ? AND header_name = 'Server'",
)
.bind(failure_id)
.fetch_one(&pool)
.await
.expect("Failed to count Server headers");
assert_eq!(
count, 1,
"Duplicate header names should result in one entry (last value wins)"
);
let row = sqlx::query(
"SELECT header_value FROM url_failure_response_headers WHERE url_failure_id = ? AND header_name = 'Server'",
)
.bind(failure_id)
.fetch_one(&pool)
.await
.expect("Failed to fetch Server header");
assert_eq!(row.get::<String, _>("header_value"), "nginx/1.20.0");
}
#[tokio::test]
async fn test_insert_url_failure_all_satellite_data_empty() {
let pool = create_test_pool().await;
create_test_run(&pool, "test-run-empty", 1704067200000i64).await;
let failure = UrlFailureRecord {
url: "http://example.com".to_string(),
final_url: None,
domain: "example.com".to_string(),
final_domain: None,
error_type: ErrorType::HttpRequestOtherError,
error_message: "Test error".to_string(),
http_status: None,
retry_count: 0,
elapsed_time_seconds: None,
timestamp: 1704067200000,
run_id: Some("test-run-empty".to_string()),
redirect_chain: vec![],
response_headers: vec![],
};
let result = insert_url_failure(&pool, &failure).await;
assert!(result.is_ok());
let failure_id = result.unwrap();
let redirect_count: i64 = sqlx::query_scalar(
"SELECT COUNT(*) FROM url_failure_redirect_chain WHERE url_failure_id = ?",
)
.bind(failure_id)
.fetch_one(&pool)
.await
.expect("Failed to count redirect chain");
let response_count: i64 = sqlx::query_scalar(
"SELECT COUNT(*) FROM url_failure_response_headers WHERE url_failure_id = ?",
)
.bind(failure_id)
.fetch_one(&pool)
.await
.expect("Failed to count response headers");
assert_eq!(redirect_count, 0);
assert_eq!(response_count, 0);
}
}