use sqlx::Sqlite;
use sqlx::Transaction;
use crate::fingerprint::DetectedTechnology;
use super::super::super::utils::build_batch_insert_query;
pub(crate) async fn insert_technologies(
tx: &mut Transaction<'_, Sqlite>,
url_status_id: i64,
technologies: &[DetectedTechnology],
) -> Result<(), sqlx::Error> {
if technologies.is_empty() {
return Ok(());
}
log::debug!(
"Inserting {} technologies for url_status_id {}",
technologies.len(),
url_status_id
);
let mut seen = std::collections::HashSet::new();
let mut deduped: Vec<&DetectedTechnology> = Vec::new();
for tech in technologies {
let key = (tech.name.as_str(), tech.version.as_deref());
if seen.insert(key) {
deduped.push(tech);
}
}
if deduped.is_empty() {
return Ok(());
}
let query_str = build_batch_insert_query(
"url_technologies",
&[
"url_status_id",
"technology_name",
"technology_version",
"technology_category",
"is_implied",
"detection_source",
],
deduped.len(),
Some("ON CONFLICT DO NOTHING"),
);
let mut query = crate::sql::query(query_str);
for tech in &deduped {
query = query
.bind(url_status_id)
.bind(&tech.name)
.bind(&tech.version)
.bind(&tech.category)
.bind(i64::from(tech.is_implied))
.bind(&tech.detection_source);
}
let result = query.execute(&mut **tx).await?;
log::debug!(
"Batch inserted {} technologies for url_status_id {} ({} rows affected, {} unique from {} total)",
deduped.len(),
url_status_id,
result.rows_affected(),
deduped.len(),
technologies.len()
);
Ok(())
}
#[cfg(test)]
mod tests {
use super::*;
use sqlx::Row;
use crate::storage::test_helpers::{create_test_pool, create_test_url_status_default};
#[tokio::test]
async fn test_insert_technologies_basic() {
let pool = create_test_pool().await;
let url_status_id = create_test_url_status_default(&pool).await;
let mut tx = pool.begin().await.expect("Failed to start transaction");
let technologies = vec![
crate::fingerprint::DetectedTechnology {
name: "WordPress".to_string(),
version: None,
category: None,
is_implied: false,
detection_source: None,
},
crate::fingerprint::DetectedTechnology {
name: "PHP".to_string(),
version: None,
category: None,
is_implied: false,
detection_source: None,
},
];
insert_technologies(&mut tx, url_status_id, &technologies)
.await
.expect("insert");
tx.commit().await.expect("Failed to commit transaction");
let rows = sqlx::query(
"SELECT technology_name, technology_category FROM url_technologies WHERE url_status_id = ? ORDER BY technology_name",
)
.bind(url_status_id)
.fetch_all(&pool)
.await
.expect("Failed to fetch technologies");
assert_eq!(rows.len(), 2);
assert_eq!(rows[0].get::<String, _>("technology_name"), "PHP");
assert_eq!(rows[1].get::<String, _>("technology_name"), "WordPress");
}
#[tokio::test]
async fn test_insert_technologies_duplicates() {
let pool = create_test_pool().await;
let url_status_id = create_test_url_status_default(&pool).await;
let mut tx = pool.begin().await.expect("Failed to start transaction");
let technologies = vec![
crate::fingerprint::DetectedTechnology {
name: "WordPress".to_string(),
version: None,
category: None,
is_implied: false,
detection_source: None,
},
crate::fingerprint::DetectedTechnology {
name: "WordPress".to_string(),
version: None,
category: None,
is_implied: false,
detection_source: None,
},
];
insert_technologies(&mut tx, url_status_id, &technologies)
.await
.expect("insert");
tx.commit().await.expect("Failed to commit transaction");
let count: i64 =
sqlx::query_scalar("SELECT COUNT(*) FROM url_technologies WHERE url_status_id = ?")
.bind(url_status_id)
.fetch_one(&pool)
.await
.expect("Failed to count technologies");
assert_eq!(count, 1);
}
#[tokio::test]
async fn test_insert_technologies_with_versions() {
let pool = create_test_pool().await;
let url_status_id = create_test_url_status_default(&pool).await;
let mut tx = pool.begin().await.expect("Failed to start transaction");
let technologies = vec![
crate::fingerprint::DetectedTechnology {
name: "WordPress".to_string(),
version: None,
category: None,
is_implied: false,
detection_source: None,
},
crate::fingerprint::DetectedTechnology {
name: "WordPress".to_string(),
version: Some("6.9".to_string()),
category: None,
is_implied: false,
detection_source: None,
},
crate::fingerprint::DetectedTechnology {
name: "PHP".to_string(),
version: Some("8.1".to_string()),
category: None,
is_implied: false,
detection_source: None,
},
];
insert_technologies(&mut tx, url_status_id, &technologies)
.await
.expect("insert");
tx.commit().await.expect("Failed to commit transaction");
let rows = sqlx::query(
"SELECT technology_name, technology_version FROM url_technologies WHERE url_status_id = ? ORDER BY technology_name, technology_version",
)
.bind(url_status_id)
.fetch_all(&pool)
.await
.expect("Failed to fetch technologies");
assert_eq!(rows.len(), 3);
assert_eq!(rows[0].get::<String, _>("technology_name"), "PHP");
assert_eq!(
rows[0].get::<Option<String>, _>("technology_version"),
Some("8.1".to_string())
);
assert_eq!(rows[1].get::<String, _>("technology_name"), "WordPress");
assert_eq!(rows[1].get::<Option<String>, _>("technology_version"), None);
assert_eq!(rows[2].get::<String, _>("technology_name"), "WordPress");
assert_eq!(
rows[2].get::<Option<String>, _>("technology_version"),
Some("6.9".to_string())
);
}
#[tokio::test]
async fn test_insert_technologies_empty() {
let pool = create_test_pool().await;
let url_status_id = create_test_url_status_default(&pool).await;
let mut tx = pool.begin().await.expect("Failed to start transaction");
let technologies = vec![];
insert_technologies(&mut tx, url_status_id, &technologies)
.await
.expect("insert");
tx.commit().await.expect("Failed to commit transaction");
let count: i64 =
sqlx::query_scalar("SELECT COUNT(*) FROM url_technologies WHERE url_status_id = ?")
.bind(url_status_id)
.fetch_one(&pool)
.await
.expect("Failed to count technologies");
assert_eq!(count, 0);
}
#[tokio::test]
async fn test_insert_technologies_is_implied() {
let pool = create_test_pool().await;
let url_status_id = create_test_url_status_default(&pool).await;
let mut tx = pool.begin().await.expect("Failed to start transaction");
let technologies = vec![
crate::fingerprint::DetectedTechnology {
name: "WordPress".to_string(),
version: None,
category: Some("CMS".to_string()),
is_implied: false,
detection_source: Some("html".to_string()),
},
crate::fingerprint::DetectedTechnology {
name: "MySQL".to_string(),
version: None,
category: Some("Databases".to_string()),
is_implied: true,
detection_source: Some("implied".to_string()),
},
];
insert_technologies(&mut tx, url_status_id, &technologies)
.await
.expect("insert");
tx.commit().await.expect("Failed to commit transaction");
let rows = sqlx::query(
"SELECT technology_name, is_implied, detection_source FROM url_technologies WHERE url_status_id = ? ORDER BY technology_name",
)
.bind(url_status_id)
.fetch_all(&pool)
.await
.expect("Failed to fetch technologies");
assert_eq!(rows.len(), 2);
assert_eq!(rows[0].get::<String, _>("technology_name"), "MySQL");
assert_eq!(rows[0].get::<i64, _>("is_implied"), 1);
assert_eq!(
rows[0].get::<Option<String>, _>("detection_source"),
Some("implied".to_string())
);
assert_eq!(rows[1].get::<String, _>("technology_name"), "WordPress");
assert_eq!(rows[1].get::<i64, _>("is_implied"), 0);
assert_eq!(
rows[1].get::<Option<String>, _>("detection_source"),
Some("html".to_string())
);
}
#[tokio::test]
async fn test_insert_technologies_preserves_colon_in_name() {
let pool = create_test_pool().await;
let url_status_id = create_test_url_status_default(&pool).await;
let mut tx = pool.begin().await.expect("Failed to start transaction");
let technologies = vec![
crate::fingerprint::DetectedTechnology {
name: "Re:amaze".to_string(),
version: Some("1.0".to_string()),
category: None,
is_implied: false,
detection_source: None,
},
crate::fingerprint::DetectedTechnology {
name: "Re:amaze".to_string(),
version: Some("1.0".to_string()),
category: None,
is_implied: false,
detection_source: None,
},
];
insert_technologies(&mut tx, url_status_id, &technologies)
.await
.expect("insert");
tx.commit().await.expect("Failed to commit transaction");
let rows = sqlx::query(
"SELECT technology_name, technology_version FROM url_technologies WHERE url_status_id = ?",
)
.bind(url_status_id)
.fetch_all(&pool)
.await
.expect("fetch");
assert_eq!(rows.len(), 1);
assert_eq!(rows[0].get::<String, _>("technology_name"), "Re:amaze");
assert_eq!(rows[0].get::<String, _>("technology_version"), "1.0");
}
}