use sqlx::{Sqlite, Transaction};
use crate::error_handling::DatabaseError;
pub(crate) async fn insert_geoip_data_in_tx(
tx: &mut Transaction<'_, Sqlite>,
url_status_id: i64,
geoip: &crate::geoip::GeoIpResult,
) -> Result<(), DatabaseError> {
sqlx::query(
"INSERT INTO url_geoip (
url_status_id, country_code, country_name, region, city,
latitude, longitude, postal_code, timezone, asn, asn_org
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(url_status_id) DO UPDATE SET
country_code=excluded.country_code,
country_name=excluded.country_name,
region=excluded.region,
city=excluded.city,
latitude=excluded.latitude,
longitude=excluded.longitude,
postal_code=excluded.postal_code,
timezone=excluded.timezone,
asn=excluded.asn,
asn_org=excluded.asn_org",
)
.bind(url_status_id)
.bind(&geoip.country_code)
.bind(&geoip.country_name)
.bind(&geoip.region)
.bind(&geoip.city)
.bind(geoip.latitude)
.bind(geoip.longitude)
.bind(&geoip.postal_code)
.bind(&geoip.timezone)
.bind(geoip.asn.map(i64::from))
.bind(&geoip.asn_org)
.execute(&mut **tx)
.await
.map_err(DatabaseError::SqlError)?;
Ok(())
}
#[cfg(test)]
mod tests {
use super::*;
use crate::geoip::GeoIpResult;
use sqlx::Row;
use crate::storage::insert::enrichment::commit_in_tx;
use crate::storage::test_helpers::{create_test_pool, create_test_url_status_default};
fn create_test_geoip_result() -> GeoIpResult {
GeoIpResult {
country_code: Some("US".to_string()),
country_name: Some("United States".to_string()),
region: Some("California".to_string()),
city: Some("San Francisco".to_string()),
latitude: Some(37.7749),
longitude: Some(-122.4194),
postal_code: Some("94102".to_string()),
timezone: Some("America/Los_Angeles".to_string()),
asn: Some(15169),
asn_org: Some("Google LLC".to_string()),
}
}
#[tokio::test]
async fn test_insert_geoip_data_basic() {
let pool = create_test_pool().await;
let url_status_id = create_test_url_status_default(&pool).await;
let geoip = create_test_geoip_result();
let result = commit_in_tx!(&pool, |tx| {
insert_geoip_data_in_tx(&mut tx, url_status_id, &geoip).await
});
assert!(result.is_ok());
let row = sqlx::query(
"SELECT country_code, country_name, region, city, latitude, longitude, asn, asn_org FROM url_geoip WHERE url_status_id = ?",
)
.bind(url_status_id)
.fetch_one(&pool)
.await
.expect("Failed to fetch geoip data");
assert_eq!(
row.get::<Option<String>, _>("country_code"),
Some("US".to_string())
);
assert_eq!(
row.get::<Option<String>, _>("country_name"),
Some("United States".to_string())
);
assert_eq!(
row.get::<Option<String>, _>("region"),
Some("California".to_string())
);
assert_eq!(
row.get::<Option<String>, _>("city"),
Some("San Francisco".to_string())
);
assert_eq!(row.get::<Option<f64>, _>("latitude"), Some(37.7749));
assert_eq!(row.get::<Option<f64>, _>("longitude"), Some(-122.4194));
assert_eq!(row.get::<Option<i64>, _>("asn"), Some(15169));
assert_eq!(
row.get::<Option<String>, _>("asn_org"),
Some("Google LLC".to_string())
);
}
#[tokio::test]
async fn test_insert_geoip_data_upsert() {
let pool = create_test_pool().await;
let url_status_id = create_test_url_status_default(&pool).await;
let mut geoip = create_test_geoip_result();
let result1 = commit_in_tx!(&pool, |tx| {
insert_geoip_data_in_tx(&mut tx, url_status_id, &geoip).await
});
assert!(result1.is_ok());
geoip.city = Some("Los Angeles".to_string());
geoip.region = Some("California".to_string());
let result2 = commit_in_tx!(&pool, |tx| {
insert_geoip_data_in_tx(&mut tx, url_status_id, &geoip).await
});
assert!(result2.is_ok());
let count: i64 =
sqlx::query_scalar("SELECT COUNT(*) FROM url_geoip WHERE url_status_id = ?")
.bind(url_status_id)
.fetch_one(&pool)
.await
.expect("Failed to count geoip records");
assert_eq!(count, 1);
let row = sqlx::query("SELECT city FROM url_geoip WHERE url_status_id = ?")
.bind(url_status_id)
.fetch_one(&pool)
.await
.expect("Failed to fetch updated geoip data");
assert_eq!(
row.get::<Option<String>, _>("city"),
Some("Los Angeles".to_string())
);
}
#[tokio::test]
async fn test_insert_geoip_data_partial() {
let pool = create_test_pool().await;
let url_status_id = create_test_url_status_default(&pool).await;
let geoip = GeoIpResult {
country_code: Some("US".to_string()),
country_name: Some("United States".to_string()),
region: None,
city: None,
latitude: None,
longitude: None,
postal_code: None,
timezone: None,
asn: None,
asn_org: None,
};
let result = commit_in_tx!(&pool, |tx| {
insert_geoip_data_in_tx(&mut tx, url_status_id, &geoip).await
});
assert!(result.is_ok());
let row =
sqlx::query("SELECT country_code, city, asn FROM url_geoip WHERE url_status_id = ?")
.bind(url_status_id)
.fetch_one(&pool)
.await
.expect("Failed to fetch geoip data");
assert_eq!(
row.get::<Option<String>, _>("country_code"),
Some("US".to_string())
);
assert_eq!(row.get::<Option<String>, _>("city"), None);
assert_eq!(row.get::<Option<i64>, _>("asn"), None);
}
}