~ knotless Search/
↑↓ move↵ open⇧↵ all results

johnandersen777.bsky.social/vuln-notif

0
0
main
681 lines · 20.6 KB Raw History
//! Schema, canonical release-candidate, queue, and normalization tests for the
//! identity/release cache tables.

use rusqlite::Connection;
use tempfile::TempDir;
use vuln_notif::db::DbHandle;
use vuln_notif::db::model::{
    IdentityMigrationMark, LinkRepairMark, ProjectAliasMark, ProjectChannelMark, PublishErrorMark,
    ReleaseArtifactMark, ReleaseCandidate, ReleaseCveLinkMark, ReleaseHistoryJobMark,
    ReleaseManifestMark, ReleaseOsvMark, ReleaseProjectStateMark, ReleaseSourceStateMark,
    ReleaseStatusEventMark, ReleaseVersionMetaMark, SoftwareIdentityMark, SoftwareMark,
    SourceHostStateMark,
};

const REPO: &str = "did:plc:test";

const NEW_TABLES: &[&str] = &[
    "software_identity",
    "project_aliases",
    "project_channels",
    "release_artifacts",
    "release_manifests",
    "release_history_jobs",
    "release_osv",
    "release_cve_links",
    "release_source_state",
    "release_status_events",
    "release_project_state",
    "release_version_meta",
    "identity_migration",
    "link_repairs",
    "publish_errors",
    "source_host_state",
];

const NEW_INDEXES: &[&str] = &[
    "release_history_jobs_status_idx",
    "release_cve_links_cve_idx",
    "identity_migration_status_idx",
    "link_repairs_next_retry_idx",
    "publish_errors_next_retry_idx",
];

async fn temp_db() -> (TempDir, std::path::PathBuf, DbHandle) {
    let temp = tempfile::tempdir().expect("temp dir");
    let path = temp.path().join("cache.db");
    let db = DbHandle::open(&path).await.expect("open db");
    (temp, path, db)
}

async fn seed_software(db: &DbHandle, raw_slug: &str, canonical_slug: &str) {
    db.mark_software(SoftwareMark {
        repo_did: REPO.to_owned(),
        slug: raw_slug.to_owned(),
        part: "a".to_owned(),
        vendor: "acme".to_owned(),
        product: raw_slug.to_owned(),
        project_uri: format!("at://{REPO}/software.release.project/{raw_slug}"),
        project_cid: "bafyproject".to_owned(),
        software_uri: format!("at://{REPO}/com.nahdig.software/{raw_slug}"),
        software_cid: format!("bafysoftware-{raw_slug}"),
    })
    .await
    .expect("mark software");
    db.mark_software_identity(SoftwareIdentityMark {
        repo_did: REPO.to_owned(),
        raw_slug: raw_slug.to_owned(),
        canonical_slug: canonical_slug.to_owned(),
        identity_key: Some(format!("os:acme:{canonical_slug}")),
        source: Some("nvd".to_owned()),
        confidence: Some(1.0),
    })
    .await
    .expect("mark identity");
}

async fn seed_purl(db: &DbHandle, purl: &str, software_slug: &str) {
    db.mark_purl(
        REPO,
        purl,
        software_slug,
        &format!("at://{REPO}/com.nahdig.purl/{}", purl.replace(':', "-")),
        "bafypurl",
    )
    .await
    .expect("mark purl");
}

fn slugs(rows: &[ReleaseCandidate]) -> Vec<String> {
    rows.iter().map(|row| row.slug.clone()).collect()
}

#[tokio::test]
async fn schema_opens_empty_db_with_all_new_tables() {
    let (temp, path, db) = temp_db().await;
    db.close().await.expect("close");
    let conn = Connection::open(&path).expect("reopen");
    let mut statement = conn
        .prepare("SELECT name FROM sqlite_master WHERE type='table'")
        .expect("prepare tables");
    let tables: Vec<String> = statement
        .query_map([], |row| row.get(0))
        .expect("query tables")
        .collect::<Result<_, _>>()
        .expect("collect tables");
    for expected in NEW_TABLES {
        assert!(
            tables.iter().any(|table| table == expected),
            "missing table {expected}"
        );
    }
    let mut statement = conn
        .prepare("SELECT name FROM sqlite_master WHERE type='index'")
        .expect("prepare indexes");
    let indexes: Vec<String> = statement
        .query_map([], |row| row.get(0))
        .expect("query indexes")
        .collect::<Result<_, _>>()
        .expect("collect indexes");
    for expected in NEW_INDEXES {
        assert!(
            indexes.iter().any(|index| index == expected),
            "missing index {expected}"
        );
    }
    drop(temp);
}

#[tokio::test]
async fn candidates_group_by_canonical_slug_and_dedupe_purls() {
    let (_temp, _path, db) = temp_db().await;
    seed_software(
        &db,
        "red-hat-red-hat-enterprise-linux-8",
        "red-hat-enterprise-linux",
    )
    .await;
    seed_software(&db, "redhat-enterprise-linux", "red-hat-enterprise-linux").await;
    // One purl row on the canonical slug joins both raw rows.
    seed_purl(&db, "pkg:rpm/redhat/shared", "red-hat-enterprise-linux").await;

    let rows = db
        .release_candidates(REPO, 0, 0.0)
        .await
        .expect("candidates");
    assert_eq!(rows.len(), 1, "canonical groups collapse to one candidate");
    assert_eq!(rows[0].slug, "red-hat-enterprise-linux");
    assert_eq!(rows[0].purls, vec!["pkg:rpm/redhat/shared".to_owned()]);
}

#[tokio::test]
async fn candidates_join_legacy_full_at_uri_purl_slugs() {
    let (_temp, _path, db) = temp_db().await;
    seed_software(&db, "rhel-8", "red-hat-enterprise-linux").await;
    seed_purl(
        &db,
        "pkg:rpm/redhat/enterprise_linux",
        &format!("at://{REPO}/com.nahdig.software/red-hat-enterprise-linux"),
    )
    .await;

    let rows = db
        .release_candidates(REPO, 0, 0.0)
        .await
        .expect("candidates");
    assert_eq!(rows.len(), 1);
    assert_eq!(rows[0].slug, "red-hat-enterprise-linux");
    assert_eq!(
        rows[0].purls,
        vec!["pkg:rpm/redhat/enterprise_linux".to_owned()]
    );
}

#[tokio::test]
async fn candidates_limit_after_grouping_and_rotation_returns_later_projects() {
    let (_temp, _path, db) = temp_db().await;
    // Two raw rows for acme-a: a pre-grouping limit of 2 would yield one group.
    seed_software(&db, "acme-a-raw", "acme-a").await;
    seed_software(&db, "acme-a-other", "acme-a").await;
    seed_software(&db, "acme-b-raw", "acme-b").await;
    seed_software(&db, "acme-c-raw", "acme-c").await;
    seed_purl(&db, "pkg:npm/acme-a", "acme-a").await;
    seed_purl(&db, "pkg:npm/acme-b", "acme-b").await;
    seed_purl(&db, "pkg:npm/acme-c", "acme-c").await;

    let first = db
        .release_candidates(REPO, 2, 0.0)
        .await
        .expect("first page");
    assert_eq!(slugs(&first), vec!["acme-a", "acme-b"]);
    assert_eq!(first[0].purls, vec!["pkg:npm/acme-a".to_owned()]);

    // Fixed-order limits would never reach acme-c; the cursor rotates to it.
    let second = db
        .release_candidates_after(REPO, 2, 0.0, Some("acme-b"))
        .await
        .expect("second page");
    assert_eq!(slugs(&second), vec!["acme-c", "acme-a"]);

    // Least-recently-checked first: a checked project yields to never-checked
    // projects even though its slug sorts earlier.
    db.mark_release_project_state(ReleaseProjectStateMark {
        repo_did: REPO.to_owned(),
        slug: "acme-a".to_owned(),
        canonical_purl: Some("pkg:npm/acme-a".to_owned()),
        source: Some("npm".to_owned()),
        raw_versions_json: None,
        checked_at: Some("2024-01-01T00:00:00Z".to_owned()),
        unsupported_reason: None,
    })
    .await
    .expect("mark project state");

    let third = db
        .release_candidates(REPO, 2, 0.0)
        .await
        .expect("third page");
    assert_eq!(slugs(&third), vec!["acme-b", "acme-c"]);
}

#[tokio::test]
async fn normalize_purl_slugs_converts_at_uris_to_tails() {
    let (_temp, _path, db) = temp_db().await;
    seed_software(&db, "rhel-8", "red-hat-enterprise-linux").await;
    let uri = format!("at://{REPO}/com.nahdig.software/red-hat-enterprise-linux");
    seed_purl(&db, "pkg:rpm/redhat/enterprise_linux", &uri).await;

    assert_eq!(db.normalize_purl_slugs(REPO).await.expect("normalize"), 1);
    let row = db
        .get_purl(REPO, "pkg:rpm/redhat/enterprise_linux")
        .await
        .expect("get purl")
        .expect("purl row");
    assert_eq!(row.software_slug, "red-hat-enterprise-linux");
    assert_eq!(
        db.normalize_purl_slugs(REPO)
            .await
            .expect("second normalize"),
        0
    );
}

#[tokio::test]
async fn pending_queue_ordering_is_null_then_next_retry() {
    let (_temp, _path, db) = temp_db().await;
    for (src_uri, next_retry_at) in [
        (format!("at://{REPO}/links/1"), None),
        (
            format!("at://{REPO}/links/2"),
            Some("2024-01-02T00:00:00Z".to_owned()),
        ),
        (
            format!("at://{REPO}/links/3"),
            Some("2024-01-01T00:00:00Z".to_owned()),
        ),
    ] {
        db.mark_link_repair(LinkRepairMark {
            repo_did: REPO.to_owned(),
            src_uri,
            src_cid: None,
            collection: Some("com.nahdig.cve.atprotoV1".to_owned()),
            record_json: "{}".to_owned(),
            attempts: 1,
            last_error: Some("boom".to_owned()),
            next_retry_at,
        })
        .await
        .expect("mark repair");
    }
    for (cve_id, next_retry_at) in [
        ("CVE-1".to_owned(), None),
        ("CVE-2".to_owned(), Some("2024-01-02T00:00:00Z".to_owned())),
        ("CVE-3".to_owned(), Some("2024-01-01T00:00:00Z".to_owned())),
    ] {
        db.mark_publish_error(PublishErrorMark {
            repo_did: REPO.to_owned(),
            cve_id,
            attempts: 2,
            last_error: Some("nope".to_owned()),
            next_retry_at,
        })
        .await
        .expect("mark publish error");
    }

    let repairs = db.pending_link_repairs(0).await.expect("repairs");
    let repair_uris: Vec<String> = repairs.iter().map(|row| row.src_uri.clone()).collect();
    assert_eq!(
        repair_uris,
        vec![
            format!("at://{REPO}/links/1"),
            format!("at://{REPO}/links/3"),
            format!("at://{REPO}/links/2"),
        ]
    );
    assert_eq!(db.pending_link_repairs(2).await.expect("limited").len(), 2);

    let errors = db.pending_publish_errors(0).await.expect("errors");
    let cve_ids: Vec<String> = errors.iter().map(|row| row.cve_id.clone()).collect();
    assert_eq!(
        cve_ids,
        vec!["CVE-1".to_owned(), "CVE-3".to_owned(), "CVE-2".to_owned()]
    );
    assert_eq!(
        db.pending_publish_errors(1).await.expect("limited")[0].cve_id,
        "CVE-1"
    );
}

#[tokio::test]
async fn new_table_marks_roundtrip() {
    let (_temp, _path, db) = temp_db().await;

    db.mark_software_identity(SoftwareIdentityMark {
        repo_did: REPO.to_owned(),
        raw_slug: "raw".to_owned(),
        canonical_slug: "canonical".to_owned(),
        identity_key: Some("key".to_owned()),
        source: Some("nvd".to_owned()),
        confidence: Some(0.5),
    })
    .await
    .expect("mark identity");
    let row = db
        .get_software_identity(REPO, "raw")
        .await
        .expect("get identity")
        .expect("identity row");
    assert_eq!(row.canonical_slug, "canonical");
    assert_eq!(row.confidence, Some(0.5));
    assert_eq!(
        db.list_software_identities(REPO).await.expect("list").len(),
        1
    );

    db.mark_project_alias(ProjectAliasMark {
        repo_did: REPO.to_owned(),
        alias_key: "alias".to_owned(),
        canonical_slug: "canonical".to_owned(),
        source: Some("curated".to_owned()),
    })
    .await
    .expect("mark alias");
    assert!(
        db.get_project_alias(REPO, "alias")
            .await
            .expect("get")
            .is_some()
    );

    db.mark_project_channel(ProjectChannelMark {
        repo_did: REPO.to_owned(),
        project_slug: "canonical".to_owned(),
        channel: "9".to_owned(),
        latest_version: Some("9.4".to_owned()),
        eol_at: None,
        status: "active".to_owned(),
    })
    .await
    .expect("mark channel");
    let channel = db
        .get_project_channel(REPO, "canonical", "9")
        .await
        .expect("get channel")
        .expect("channel row");
    assert_eq!(channel.latest_version.as_deref(), Some("9.4"));
    assert_eq!(channel.status, "active");

    db.mark_release_artifact(ReleaseArtifactMark {
        repo_did: REPO.to_owned(),
        source: "pypi".to_owned(),
        package: "acme".to_owned(),
        version: "1.0".to_owned(),
        file_name: Some("acme-1.0.whl".to_owned()),
        url: "https://example.test/acme-1.0.whl".to_owned(),
        sha256: Some("abc".to_owned()),
        cid: Some("bafyblob".to_owned()),
        size: Some(42),
        mime_type: Some("application/zip".to_owned()),
        metadata_json: None,
    })
    .await
    .expect("mark artifact");
    let artifact = db
        .get_release_artifact(
            REPO,
            "pypi",
            "acme",
            "1.0",
            "https://example.test/acme-1.0.whl",
        )
        .await
        .expect("get artifact")
        .expect("artifact row");
    assert_eq!(artifact.size, Some(42));
    assert_eq!(
        db.list_release_artifacts(REPO, "pypi", "acme", "1.0")
            .await
            .expect("list artifacts")
            .len(),
        1
    );

    db.mark_release_manifest(ReleaseManifestMark {
        repo_did: REPO.to_owned(),
        project_slug: "canonical".to_owned(),
        manifest_key: "latest".to_owned(),
        uri: Some("at://manifest".to_owned()),
        cid: Some("bafymanifest".to_owned()),
        artifacts_json: Some("[]".to_owned()),
    })
    .await
    .expect("mark manifest");
    assert!(
        db.get_release_manifest(REPO, "canonical", "latest")
            .await
            .expect("get manifest")
            .is_some()
    );

    db.mark_release_history_job(ReleaseHistoryJobMark {
        repo_did: REPO.to_owned(),
        project_slug: "canonical".to_owned(),
        version_raw: "v1".to_owned(),
        version: Some("1.0".to_owned()),
        source: Some("pypi".to_owned()),
        purl: Some("pkg:pypi/acme".to_owned()),
        status: "pending".to_owned(),
        attempts: 0,
        error: None,
    })
    .await
    .expect("mark job");
    assert_eq!(
        db.get_release_history_job(REPO, "canonical", "v1")
            .await
            .expect("get job")
            .expect("job row")
            .version
            .as_deref(),
        Some("1.0")
    );
    assert_eq!(
        db.list_release_history_jobs(REPO, Some("pending"), 0)
            .await
            .expect("list jobs")
            .len(),
        1
    );

    db.mark_release_osv(ReleaseOsvMark {
        repo_did: REPO.to_owned(),
        purl: "pkg:pypi/acme".to_owned(),
        osv_id: "OSV-1".to_owned(),
        aliases_json: Some("[]".to_owned()),
        affected_json: Some("[]".to_owned()),
        modified: Some("2024-01-01T00:00:00Z".to_owned()),
    })
    .await
    .expect("mark osv");
    assert!(
        db.get_release_osv(REPO, "pkg:pypi/acme", "OSV-1")
            .await
            .expect("get osv")
            .is_some()
    );

    db.mark_release_cve_link(ReleaseCveLinkMark {
        repo_did: REPO.to_owned(),
        cve_id: "CVE-2024-1".to_owned(),
        project_slug: "canonical".to_owned(),
        version_uri: Some("at://version".to_owned()),
        version_cid: Some("bafyversion".to_owned()),
        channel_uri: None,
        channel_cid: None,
        manifest_uri: None,
        manifest_cid: None,
        version_label: Some("9.4".to_owned()),
        affected_ranges: Some("[]".to_owned()),
    })
    .await
    .expect("mark cve link");
    assert!(
        db.get_release_cve_link(REPO, "CVE-2024-1", "canonical")
            .await
            .expect("get cve link")
            .is_some()
    );
    assert_eq!(
        db.list_release_cve_links(REPO, "CVE-2024-1")
            .await
            .expect("list cve links")
            .len(),
        1
    );

    db.mark_release_source_state(ReleaseSourceStateMark {
        repo_did: REPO.to_owned(),
        source: "osv".to_owned(),
        cursor: Some("cursor-1".to_owned()),
        etag: Some("etag-1".to_owned()),
    })
    .await
    .expect("mark source state");
    assert_eq!(
        db.get_release_source_state(REPO, "osv")
            .await
            .expect("get source state")
            .expect("source state row")
            .cursor
            .as_deref(),
        Some("cursor-1")
    );

    let event = ReleaseStatusEventMark {
        repo_did: REPO.to_owned(),
        subject_uri: "at://version".to_owned(),
        rkey: "3event".to_owned(),
        status: "deprecated".to_owned(),
        kind: Some("security".to_owned()),
        reason: Some("advisory".to_owned()),
        cid: Some("bafyevent".to_owned()),
        created_at: None,
    };
    db.append_release_status_event(event.clone())
        .await
        .expect("append event");
    db.append_release_status_event(event)
        .await
        .expect("append duplicate event");
    assert_eq!(
        db.list_release_status_events(REPO, "at://version", 0)
            .await
            .expect("list events")
            .len(),
        1
    );

    db.mark_release_project_state(ReleaseProjectStateMark {
        repo_did: REPO.to_owned(),
        slug: "canonical".to_owned(),
        canonical_purl: Some("pkg:pypi/acme".to_owned()),
        source: Some("pypi".to_owned()),
        raw_versions_json: Some("[]".to_owned()),
        checked_at: Some("2024-01-01T00:00:00Z".to_owned()),
        unsupported_reason: None,
    })
    .await
    .expect("mark project state");
    assert!(
        db.get_release_project_state(REPO, "canonical")
            .await
            .expect("get project state")
            .is_some()
    );
    assert_eq!(
        db.list_release_project_states(REPO, 0)
            .await
            .expect("list project states")
            .len(),
        1
    );

    db.mark_release_version_meta(ReleaseVersionMetaMark {
        repo_did: REPO.to_owned(),
        project_slug: "canonical".to_owned(),
        version: "1.0".to_owned(),
        source: Some("pypi".to_owned()),
        published_at: Some("2024-01-01T00:00:00Z".to_owned()),
        raw_version: Some("v1.0".to_owned()),
        artifact_cid: Some("bafyblob".to_owned()),
        artifact_size: Some(42),
    })
    .await
    .expect("mark version meta");
    assert!(
        db.get_release_version_meta(REPO, "canonical", "1.0")
            .await
            .expect("get version meta")
            .is_some()
    );

    db.mark_identity_migration(IdentityMigrationMark {
        repo_did: REPO.to_owned(),
        phase: "apply".to_owned(),
        raw_slug: "raw".to_owned(),
        canonical_slug: Some("canonical".to_owned()),
        action: Some("remap".to_owned()),
        status: "done".to_owned(),
        attempts: 1,
        error: None,
    })
    .await
    .expect("mark migration");
    assert_eq!(
        db.list_identity_migration(REPO, Some("done"), 0)
            .await
            .expect("list migration")
            .len(),
        1
    );

    db.mark_link_repair(LinkRepairMark {
        repo_did: REPO.to_owned(),
        src_uri: format!("at://{REPO}/links/1"),
        src_cid: None,
        collection: Some("com.nahdig.cve.atprotoV1".to_owned()),
        record_json: "{}".to_owned(),
        attempts: 1,
        last_error: Some("boom".to_owned()),
        next_retry_at: None,
    })
    .await
    .expect("mark repair");
    assert!(
        db.get_link_repair(REPO, &format!("at://{REPO}/links/1"))
            .await
            .expect("get repair")
            .is_some()
    );
    db.delete_link_repair(REPO, &format!("at://{REPO}/links/1"))
        .await
        .expect("delete repair");
    assert!(
        db.get_link_repair(REPO, &format!("at://{REPO}/links/1"))
            .await
            .expect("get deleted repair")
            .is_none()
    );

    db.mark_publish_error(PublishErrorMark {
        repo_did: REPO.to_owned(),
        cve_id: "CVE-2024-1".to_owned(),
        attempts: 2,
        last_error: Some("nope".to_owned()),
        next_retry_at: None,
    })
    .await
    .expect("mark publish error");
    assert!(
        db.get_publish_error(REPO, "CVE-2024-1")
            .await
            .expect("get publish error")
            .is_some()
    );
    db.delete_publish_error(REPO, "CVE-2024-1")
        .await
        .expect("delete publish error");
    assert!(
        db.get_publish_error(REPO, "CVE-2024-1")
            .await
            .expect("get deleted publish error")
            .is_none()
    );

    db.mark_source_host_state(SourceHostStateMark {
        host: "pypi.org".to_owned(),
        window_start: 0.0,
        requests: 3,
        blocked_until: Some("2024-01-01T00:00:00Z".to_owned()),
    })
    .await
    .expect("mark host state");
    assert_eq!(
        db.get_source_host_state("pypi.org")
            .await
            .expect("get host state")
            .expect("host state row")
            .requests,
        3
    );
    assert_eq!(
        db.list_source_host_states()
            .await
            .expect("list hosts")
            .len(),
        1
    );

    db.close().await.expect("close");
}