use sqlx::{Row, SqlitePool};
use crate::error_handling::DatabaseError;
pub struct RunMetadata<'a> {
pub run_id: &'a str,
pub start_time_ms: i64,
pub version: &'a str,
pub fingerprints_source: Option<&'a str>,
pub fingerprints_version: Option<&'a str>,
pub geoip_version: Option<&'a str>,
}
pub struct RunStats<'a> {
pub run_id: &'a str,
pub total_urls: i32,
pub successful_urls: i32,
pub failed_urls: i32,
pub skipped_urls: i32,
pub elapsed_seconds: f64,
}
pub async fn insert_run_metadata(
pool: &SqlitePool,
meta: &RunMetadata<'_>,
) -> Result<(), DatabaseError> {
sqlx::query(
"INSERT INTO runs (run_id, version, fingerprints_source, fingerprints_version, geoip_version, start_time_ms)
VALUES (?, ?, ?, ?, ?, ?)
ON CONFLICT(run_id) DO UPDATE SET
version=excluded.version,
fingerprints_source=excluded.fingerprints_source,
fingerprints_version=excluded.fingerprints_version,
geoip_version=excluded.geoip_version,
start_time_ms=excluded.start_time_ms",
)
.bind(meta.run_id)
.bind(meta.version)
.bind(meta.fingerprints_source)
.bind(meta.fingerprints_version)
.bind(meta.geoip_version)
.bind(meta.start_time_ms)
.execute(pool)
.await
.map_err(DatabaseError::SqlError)?;
Ok(())
}
pub async fn update_run_stats(
pool: &SqlitePool,
stats: &RunStats<'_>,
) -> Result<(), DatabaseError> {
let end_time_ms = chrono::Utc::now().timestamp_millis();
sqlx::query(
"UPDATE runs
SET end_time_ms = ?, total_urls = ?, successful_urls = ?, failed_urls = ?, skipped_urls = ?, elapsed_seconds = ?
WHERE run_id = ?",
)
.bind(end_time_ms)
.bind(stats.total_urls)
.bind(stats.successful_urls)
.bind(stats.failed_urls)
.bind(stats.skipped_urls)
.bind(stats.elapsed_seconds)
.bind(stats.run_id)
.execute(pool)
.await
.map_err(DatabaseError::SqlError)
.map(|result| {
if result.rows_affected() == 0 {
log::warn!(
"update_run_stats: run_id '{}' not found (0 rows updated) — stats not persisted",
stats.run_id
);
}
})?;
Ok(())
}
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub(crate) struct RunFactCounts {
pub successful_urls: i64,
pub failed_urls: i64,
}
pub(crate) async fn count_run_fact_rows(
pool: &SqlitePool,
run_id: &str,
) -> Result<RunFactCounts, DatabaseError> {
let successful_urls: i64 =
sqlx::query_scalar("SELECT COUNT(*) FROM url_status WHERE run_id = ?")
.bind(run_id)
.fetch_one(pool)
.await
.map_err(DatabaseError::SqlError)?;
let failed_urls: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM url_failures WHERE run_id = ?")
.bind(run_id)
.fetch_one(pool)
.await
.map_err(DatabaseError::SqlError)?;
Ok(RunFactCounts {
successful_urls,
failed_urls,
})
}
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub(crate) struct RunPartialFailureCounts {
pub total: i64,
pub satellite_insert_errors: i64,
}
pub(crate) async fn count_run_partial_failures(
pool: &SqlitePool,
run_id: &str,
) -> Result<RunPartialFailureCounts, DatabaseError> {
let total: i64 =
sqlx::query_scalar("SELECT COUNT(*) FROM url_partial_failures WHERE run_id = ?")
.bind(run_id)
.fetch_one(pool)
.await
.map_err(DatabaseError::SqlError)?;
let satellite_insert_errors: i64 = sqlx::query_scalar(
"SELECT COUNT(*) FROM url_partial_failures WHERE run_id = ? AND error_type = ?",
)
.bind(run_id)
.bind(crate::error_handling::ErrorType::SatelliteInsertError.as_str())
.fetch_one(pool)
.await
.map_err(DatabaseError::SqlError)?;
Ok(RunPartialFailureCounts {
total,
satellite_insert_errors,
})
}
pub(crate) fn saturating_i32_count(count: i64) -> i32 {
i32::try_from(count.max(0)).unwrap_or(i32::MAX)
}
pub async fn query_run_history(
pool: &SqlitePool,
limit: Option<usize>,
) -> Result<Vec<RunSummary>, DatabaseError> {
let query = if let Some(limit) = limit {
format!(
"SELECT run_id, version, start_time_ms, end_time_ms, total_urls, successful_urls, failed_urls, skipped_urls, elapsed_seconds
FROM runs
WHERE end_time_ms IS NOT NULL
ORDER BY start_time_ms DESC
LIMIT {limit}"
)
} else {
"SELECT run_id, version, start_time_ms, end_time_ms, total_urls, successful_urls, failed_urls, skipped_urls, elapsed_seconds
FROM runs
WHERE end_time_ms IS NOT NULL
ORDER BY start_time_ms DESC"
.to_string()
};
let rows = crate::sql::query(query)
.fetch_all(pool)
.await
.map_err(DatabaseError::SqlError)?;
Ok(rows
.into_iter()
.map(|row| run_summary_from_row(&row))
.collect())
}
fn run_summary_from_row(row: &sqlx::sqlite::SqliteRow) -> RunSummary {
RunSummary {
run_id: row.get("run_id"),
version: row.get("version"),
start_time_ms: row.get("start_time_ms"),
end_time_ms: row.get("end_time_ms"),
total_urls: row.get("total_urls"),
successful_urls: row.get("successful_urls"),
failed_urls: row.get("failed_urls"),
skipped_urls: row.get("skipped_urls"),
elapsed_seconds: row.get("elapsed_seconds"),
}
}
pub(crate) async fn query_run_by_id(
pool: &SqlitePool,
run_id: &str,
) -> Result<Option<RunSummary>, DatabaseError> {
let row = sqlx::query(
"SELECT run_id, version, start_time_ms, end_time_ms, total_urls, successful_urls, \
failed_urls, skipped_urls, elapsed_seconds \
FROM runs WHERE run_id = ?",
)
.bind(run_id)
.fetch_optional(pool)
.await
.map_err(DatabaseError::SqlError)?;
Ok(row.map(|row| run_summary_from_row(&row)))
}
#[derive(Debug, Clone)]
pub struct RunSummary {
pub run_id: String,
pub version: Option<String>,
pub start_time_ms: i64,
pub end_time_ms: Option<i64>,
pub total_urls: i32,
pub successful_urls: i32,
pub failed_urls: i32,
pub skipped_urls: i32,
pub elapsed_seconds: Option<f64>,
}
#[cfg(test)]
mod tests {
use super::*;
use sqlx::Row;
use crate::storage::test_helpers::{create_test_pool, create_test_url_status};
#[tokio::test]
async fn test_insert_run_metadata_basic() {
let pool = create_test_pool().await;
let meta = RunMetadata {
run_id: "test-run-123",
start_time_ms: 1704067200000, version: "0.1.4",
fingerprints_source: Some("https://github.com/wappalyzer/wappalyzer"),
fingerprints_version: Some("abc123def456"),
geoip_version: Some("2024-01-01"),
};
let result = insert_run_metadata(&pool, &meta).await;
assert!(result.is_ok());
let row = sqlx::query(
"SELECT run_id, fingerprints_source, fingerprints_version, geoip_version, start_time_ms FROM runs WHERE run_id = ?",
)
.bind("test-run-123")
.fetch_one(&pool)
.await
.expect("Failed to fetch run metadata");
assert_eq!(row.get::<String, _>("run_id"), "test-run-123");
assert_eq!(
row.get::<Option<String>, _>("fingerprints_source"),
Some("https://github.com/wappalyzer/wappalyzer".to_string())
);
assert_eq!(
row.get::<Option<String>, _>("fingerprints_version"),
Some("abc123def456".to_string())
);
assert_eq!(
row.get::<Option<String>, _>("geoip_version"),
Some("2024-01-01".to_string())
);
assert_eq!(row.get::<i64, _>("start_time_ms"), 1704067200000);
}
#[tokio::test]
async fn test_insert_run_metadata_with_none_values() {
let pool = create_test_pool().await;
let meta = RunMetadata {
run_id: "test-run-456",
start_time_ms: 1704067200000,
version: "0.1.4",
fingerprints_source: None,
fingerprints_version: None,
geoip_version: None,
};
let result = insert_run_metadata(&pool, &meta).await;
assert!(result.is_ok());
let row = sqlx::query(
"SELECT version, fingerprints_source, fingerprints_version, geoip_version FROM runs WHERE run_id = ?",
)
.bind("test-run-456")
.fetch_one(&pool)
.await
.expect("Failed to fetch run metadata");
assert_eq!(
row.get::<Option<String>, _>("version"),
Some("0.1.4".to_string())
);
assert_eq!(row.get::<Option<String>, _>("fingerprints_source"), None);
assert_eq!(row.get::<Option<String>, _>("fingerprints_version"), None);
assert_eq!(row.get::<Option<String>, _>("geoip_version"), None);
}
#[tokio::test]
async fn test_insert_run_metadata_upsert() {
let pool = create_test_pool().await;
let meta1 = RunMetadata {
run_id: "test-run-789",
start_time_ms: 1704067200000,
version: "0.1.4",
fingerprints_source: Some("https://github.com/wappalyzer/wappalyzer"),
fingerprints_version: Some("abc123"),
geoip_version: Some("2024-01-01"),
};
insert_run_metadata(&pool, &meta1)
.await
.expect("Failed to insert initial metadata");
let meta2 = RunMetadata {
run_id: "test-run-789",
start_time_ms: 1704153600000, version: "0.1.5", fingerprints_source: Some("https://github.com/wappalyzer/wappalyzer"),
fingerprints_version: Some("def456"), geoip_version: Some("2024-01-02"), };
let result = insert_run_metadata(&pool, &meta2).await;
assert!(result.is_ok());
let row = sqlx::query(
"SELECT version, fingerprints_version, geoip_version, start_time_ms FROM runs WHERE run_id = ?",
)
.bind("test-run-789")
.fetch_one(&pool)
.await
.expect("Failed to fetch run metadata");
assert_eq!(
row.get::<Option<String>, _>("version"),
Some("0.1.5".to_string())
);
assert_eq!(
row.get::<Option<String>, _>("fingerprints_version"),
Some("def456".to_string())
);
assert_eq!(
row.get::<Option<String>, _>("geoip_version"),
Some("2024-01-02".to_string())
);
assert_eq!(row.get::<i64, _>("start_time_ms"), 1704153600000);
let count: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM runs WHERE run_id = ?")
.bind("test-run-789")
.fetch_one(&pool)
.await
.expect("Failed to count runs");
assert_eq!(count, 1);
}
#[test]
fn test_saturating_i32_count_clamps_non_negative() {
assert_eq!(saturating_i32_count(0), 0);
assert_eq!(saturating_i32_count(42), 42);
assert_eq!(saturating_i32_count(-1), 0);
assert_eq!(saturating_i32_count(i64::from(i32::MAX)), i32::MAX);
assert_eq!(saturating_i32_count(i64::from(i32::MAX) + 1), i32::MAX);
}
#[tokio::test]
async fn test_count_run_fact_rows() {
let pool = create_test_pool().await;
insert_run_metadata(
&pool,
&RunMetadata {
run_id: "count-run",
start_time_ms: 1,
version: "0.1.0",
fingerprints_source: None,
fingerprints_version: None,
geoip_version: None,
},
)
.await
.expect("insert run");
create_test_url_status(
&pool,
"a.example.com",
"a.example.com",
200,
Some("count-run"),
1,
)
.await;
create_test_url_status(
&pool,
"b.example.com",
"b.example.com",
200,
Some("count-run"),
1,
)
.await;
sqlx::query(
"INSERT INTO url_failures (
attempted_url, initial_domain, error_type, error_message,
retry_count, observed_at_ms, run_id
) VALUES ('https://fail.example.com/', 'fail.example.com', 'timeout', 't', 0, 1, 'count-run')",
)
.execute(&pool)
.await
.expect("insert failure");
let facts = count_run_fact_rows(&pool, "count-run")
.await
.expect("count facts");
assert_eq!(facts.successful_urls, 2);
assert_eq!(facts.failed_urls, 1);
let empty = count_run_fact_rows(&pool, "missing-run")
.await
.expect("count missing run");
assert_eq!(
empty,
RunFactCounts {
successful_urls: 0,
failed_urls: 0,
}
);
}
#[tokio::test]
async fn test_count_run_partial_failures() {
let pool = create_test_pool().await;
insert_run_metadata(
&pool,
&RunMetadata {
run_id: "partial-run",
start_time_ms: 1,
version: "0.1.0",
fingerprints_source: None,
fingerprints_version: None,
geoip_version: None,
},
)
.await
.expect("insert run");
let url_status_id = create_test_url_status(
&pool,
"a.example.com",
"a.example.com",
200,
Some("partial-run"),
1,
)
.await;
sqlx::query(
"INSERT INTO url_partial_failures (
url_status_id, error_type, error_message, observed_at_ms, run_id
) VALUES (?, ?, 'mx timeout', 1, 'partial-run')",
)
.bind(url_status_id)
.bind(crate::error_handling::ErrorType::DnsMxLookupError.as_str())
.execute(&pool)
.await
.expect("insert dns partial");
sqlx::query(
"INSERT INTO url_partial_failures (
url_status_id, error_type, error_message, observed_at_ms, run_id
) VALUES (?, ?, 'url_cookies: constraint', 1, 'partial-run')",
)
.bind(url_status_id)
.bind(crate::error_handling::ErrorType::SatelliteInsertError.as_str())
.execute(&pool)
.await
.expect("insert satellite partial");
let counts = count_run_partial_failures(&pool, "partial-run")
.await
.expect("count partials");
assert_eq!(counts.total, 2);
assert_eq!(counts.satellite_insert_errors, 1);
let empty = count_run_partial_failures(&pool, "missing-run")
.await
.expect("count missing");
assert_eq!(
empty,
RunPartialFailureCounts {
total: 0,
satellite_insert_errors: 0,
}
);
}
#[tokio::test]
async fn test_update_run_stats_basic() {
let pool = create_test_pool().await;
let meta = RunMetadata {
run_id: "test-run-stats",
start_time_ms: 1704067200000,
version: "0.1.4",
fingerprints_source: Some("https://github.com/wappalyzer/wappalyzer"),
fingerprints_version: Some("abc123"),
geoip_version: None,
};
insert_run_metadata(&pool, &meta)
.await
.expect("Failed to insert run metadata");
let stats = RunStats {
run_id: "test-run-stats",
total_urls: 100,
successful_urls: 95,
failed_urls: 5,
skipped_urls: 0,
elapsed_seconds: 10.5,
};
let result = update_run_stats(&pool, &stats).await;
assert!(result.is_ok());
let row = sqlx::query(
"SELECT end_time_ms, total_urls, successful_urls, failed_urls, skipped_urls, elapsed_seconds FROM runs WHERE run_id = ?",
)
.bind("test-run-stats")
.fetch_one(&pool)
.await
.expect("Failed to fetch run stats");
let end_time_ms: i64 = row.get("end_time_ms");
assert!(end_time_ms > 0); assert_eq!(row.get::<i32, _>("total_urls"), 100);
assert_eq!(row.get::<i32, _>("successful_urls"), 95);
assert_eq!(row.get::<i32, _>("failed_urls"), 5);
assert_eq!(row.get::<i32, _>("skipped_urls"), 0);
assert_eq!(row.get::<Option<f64>, _>("elapsed_seconds"), Some(10.5));
}
#[tokio::test]
async fn test_update_run_stats_zero_values() {
let pool = create_test_pool().await;
let meta = RunMetadata {
run_id: "test-run-zero",
start_time_ms: 1704067200000,
version: "0.1.4",
fingerprints_source: None,
fingerprints_version: None,
geoip_version: None,
};
insert_run_metadata(&pool, &meta)
.await
.expect("Failed to insert run metadata");
let stats = RunStats {
run_id: "test-run-zero",
total_urls: 0,
successful_urls: 0,
failed_urls: 0,
skipped_urls: 0,
elapsed_seconds: 0.0,
};
let result = update_run_stats(&pool, &stats).await;
assert!(result.is_ok());
let row = sqlx::query(
"SELECT total_urls, successful_urls, failed_urls, skipped_urls, elapsed_seconds FROM runs WHERE run_id = ?",
)
.bind("test-run-zero")
.fetch_one(&pool)
.await
.expect("Failed to fetch run stats");
assert_eq!(row.get::<i32, _>("total_urls"), 0);
assert_eq!(row.get::<i32, _>("successful_urls"), 0);
assert_eq!(row.get::<i32, _>("failed_urls"), 0);
assert_eq!(row.get::<i32, _>("skipped_urls"), 0);
assert_eq!(row.get::<Option<f64>, _>("elapsed_seconds"), Some(0.0));
}
#[tokio::test]
async fn test_update_run_stats_nonexistent_run() {
let pool = create_test_pool().await;
let stats = RunStats {
run_id: "nonexistent-run",
total_urls: 100,
successful_urls: 95,
failed_urls: 5,
skipped_urls: 0,
elapsed_seconds: 15.0,
};
let result = update_run_stats(&pool, &stats).await;
assert!(result.is_ok());
let count: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM runs WHERE run_id = ?")
.bind("nonexistent-run")
.fetch_one(&pool)
.await
.expect("Failed to count runs");
assert_eq!(count, 0);
}
#[tokio::test]
async fn test_insert_and_update_run_complete_flow() {
let pool = create_test_pool().await;
let meta = RunMetadata {
run_id: "complete-run",
start_time_ms: 1704067200000,
version: "0.1.4",
fingerprints_source: Some("https://github.com/wappalyzer/wappalyzer"),
fingerprints_version: Some("abc123"),
geoip_version: Some("2024-01-01"),
};
insert_run_metadata(&pool, &meta)
.await
.expect("Failed to insert run metadata");
let stats = RunStats {
run_id: "complete-run",
total_urls: 50,
successful_urls: 48,
failed_urls: 2,
skipped_urls: 0,
elapsed_seconds: 25.3,
};
let result = update_run_stats(&pool, &stats).await;
assert!(result.is_ok());
let row = sqlx::query(
"SELECT run_id, version, fingerprints_source, fingerprints_version, geoip_version, start_time_ms, end_time_ms, total_urls, successful_urls, failed_urls, skipped_urls, elapsed_seconds FROM runs WHERE run_id = ?",
)
.bind("complete-run")
.fetch_one(&pool)
.await
.expect("Failed to fetch complete run record");
assert_eq!(row.get::<String, _>("run_id"), "complete-run");
assert_eq!(
row.get::<Option<String>, _>("version"),
Some("0.1.4".to_string())
);
assert_eq!(
row.get::<Option<String>, _>("fingerprints_source"),
Some("https://github.com/wappalyzer/wappalyzer".to_string())
);
assert_eq!(row.get::<i64, _>("start_time_ms"), 1704067200000);
let end_time_ms: i64 = row.get("end_time_ms");
assert!(end_time_ms > 0);
assert_eq!(row.get::<i32, _>("total_urls"), 50);
assert_eq!(row.get::<i32, _>("successful_urls"), 48);
assert_eq!(row.get::<i32, _>("failed_urls"), 2);
assert_eq!(row.get::<i32, _>("skipped_urls"), 0);
assert_eq!(row.get::<Option<f64>, _>("elapsed_seconds"), Some(25.3));
}
#[tokio::test]
async fn test_query_run_history_with_limit() {
let pool = create_test_pool().await;
for i in 0..5 {
let run_id = format!("test-run-limit-{}", i);
let meta = RunMetadata {
run_id: &run_id,
start_time_ms: 1704067200000 + (i64::from(i) * 1000),
version: "0.1.4",
fingerprints_source: None,
fingerprints_version: None,
geoip_version: None,
};
insert_run_metadata(&pool, &meta)
.await
.expect("Failed to insert run metadata");
let stats = RunStats {
run_id: &run_id,
total_urls: 10,
successful_urls: 9,
failed_urls: 1,
skipped_urls: 0,
elapsed_seconds: 5.0,
};
update_run_stats(&pool, &stats)
.await
.expect("Failed to update run stats");
}
let result = query_run_history(&pool, Some(3)).await;
assert!(result.is_ok());
let runs = result.unwrap();
assert_eq!(
runs.len(),
3,
"Should return exactly 3 runs when limit is 3"
);
}
#[tokio::test]
async fn test_query_run_history_without_limit() {
let pool = create_test_pool().await;
for i in 0..5 {
let run_id = format!("test-run-unlimited-{}", i);
let meta = RunMetadata {
run_id: &run_id,
start_time_ms: 1704067200000 + (i64::from(i) * 1000),
version: "0.1.4",
fingerprints_source: None,
fingerprints_version: None,
geoip_version: None,
};
insert_run_metadata(&pool, &meta)
.await
.expect("Failed to insert run metadata");
let stats = RunStats {
run_id: &run_id,
total_urls: 10,
successful_urls: 9,
failed_urls: 1,
skipped_urls: 0,
elapsed_seconds: 5.0,
};
update_run_stats(&pool, &stats)
.await
.expect("Failed to update run stats");
}
let result = query_run_history(&pool, None).await;
assert!(result.is_ok());
let runs = result.unwrap();
assert_eq!(runs.len(), 5, "Should return all 5 runs when limit is None");
}
#[tokio::test]
async fn test_query_run_history_empty_result() {
let pool = create_test_pool().await;
let meta = RunMetadata {
run_id: "test-run-incomplete",
start_time_ms: 1704067200000,
version: "0.1.4",
fingerprints_source: None,
fingerprints_version: None,
geoip_version: None,
};
insert_run_metadata(&pool, &meta)
.await
.expect("Failed to insert run metadata");
let result = query_run_history(&pool, None).await;
assert!(result.is_ok());
let runs = result.unwrap();
assert!(
runs.is_empty(),
"Should return empty vector when no completed runs exist"
);
}
#[tokio::test]
async fn test_query_run_history_sorted_by_start_time_desc() {
let pool = create_test_pool().await;
let run_ids = ["run-1", "run-2", "run-3"];
let start_times = [1704067200000, 1704067300000, 1704067400000];
for (run_id, start_time) in run_ids.iter().zip(start_times.iter()) {
let meta = RunMetadata {
run_id,
start_time_ms: *start_time,
version: "0.1.4",
fingerprints_source: None,
fingerprints_version: None,
geoip_version: None,
};
insert_run_metadata(&pool, &meta)
.await
.expect("Failed to insert run metadata");
let stats = RunStats {
run_id,
total_urls: 10,
successful_urls: 9,
failed_urls: 1,
skipped_urls: 0,
elapsed_seconds: 5.0,
};
update_run_stats(&pool, &stats)
.await
.expect("Failed to update run stats");
}
let result = query_run_history(&pool, None).await;
assert!(result.is_ok());
let runs = result.unwrap();
assert_eq!(runs.len(), 3);
assert_eq!(runs[0].run_id, "run-3");
assert_eq!(runs[0].start_time_ms, 1704067400000);
assert_eq!(runs[2].run_id, "run-1");
assert_eq!(runs[2].start_time_ms, 1704067200000);
}
}