use std::path::Path;
use std::time::{SystemTime, UNIX_EPOCH};
use rusqlite::{params, Connection};
use crate::error::Result;
use crate::models::{
CertificateInfo, NewProbeResult, ProbeResult, ProbeStatus, TimingReport, TlsReport, TlsStatus,
};
#[derive(Debug)]
pub struct SqliteStore {
conn: Connection,
}
impl SqliteStore {
pub fn open<P: AsRef<Path>>(path: P) -> Result<Self> {
let conn = Connection::open(path)?;
let store = Self { conn };
store.init()?;
Ok(store)
}
pub fn in_memory() -> Result<Self> {
let conn = Connection::open_in_memory()?;
let store = Self { conn };
store.init()?;
Ok(store)
}
pub fn init(&self) -> Result<()> {
self.conn.execute(
"CREATE TABLE IF NOT EXISTS runs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
target TEXT NOT NULL,
request_path TEXT NOT NULL DEFAULT '/',
dns_ms INTEGER NOT NULL,
tcp_ms INTEGER NOT NULL,
tls_ms INTEGER NOT NULL,
ttfb_ms INTEGER NOT NULL,
download_ms INTEGER NOT NULL DEFAULT 0,
total_ms INTEGER NOT NULL,
status TEXT NOT NULL,
response_status_code INTEGER,
response_status_text TEXT,
response_bytes INTEGER,
error_message TEXT,
tls_enabled INTEGER,
tls_status TEXT,
tls_version TEXT,
tls_cipher_suite TEXT,
tls_certificate_subject TEXT,
tls_certificate_issuer TEXT,
tls_certificate_not_before TEXT,
tls_certificate_not_after TEXT,
tls_error_message TEXT,
created_at_ms INTEGER NOT NULL
)",
[],
)?;
self.ensure_column("runs", "request_path", "TEXT NOT NULL DEFAULT '/'")?;
self.ensure_column("runs", "response_status_code", "INTEGER")?;
self.ensure_column("runs", "response_status_text", "TEXT")?;
self.ensure_column("runs", "response_bytes", "INTEGER")?;
self.ensure_column("runs", "download_ms", "INTEGER NOT NULL DEFAULT 0")?;
self.conn.execute(
"CREATE INDEX IF NOT EXISTS idx_runs_target_created_at
ON runs(target, created_at_ms DESC)",
[],
)?;
Ok(())
}
pub fn save_result(&self, result: &NewProbeResult) -> Result<ProbeResult> {
let created_at_ms = current_time_ms()?;
self.conn.execute(
"INSERT INTO runs (
target,
request_path,
dns_ms,
tcp_ms,
tls_ms,
ttfb_ms,
download_ms,
total_ms,
status,
response_status_code,
response_status_text,
response_bytes,
error_message,
tls_enabled,
tls_status,
tls_version,
tls_cipher_suite,
tls_certificate_subject,
tls_certificate_issuer,
tls_certificate_not_before,
tls_certificate_not_after,
tls_error_message,
created_at_ms
) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20, ?21, ?22, ?23)",
params![
&result.target,
&result.request_path,
result.report.dns_ms as i64,
result.report.tcp_ms as i64,
result.report.tls_ms as i64,
result.report.ttfb_ms as i64,
result.report.download_ms as i64,
result.report.total_ms as i64,
probe_status_as_str(result.status),
result.response_status_code.map(i64::from),
&result.response_status_text,
result.response_bytes.map(|value| value as i64),
&result.error_message,
result.tls.as_ref().map(|tls| tls.enabled as i64),
result.tls.as_ref().map(|tls| tls_status_as_str(tls.status)),
result.tls.as_ref().and_then(|tls| tls.version.as_deref()),
result
.tls
.as_ref()
.and_then(|tls| tls.cipher_suite.as_deref()),
result
.tls
.as_ref()
.and_then(|tls| tls.certificate.as_ref().map(|cert| cert.subject.as_str())),
result
.tls
.as_ref()
.and_then(|tls| tls.certificate.as_ref().map(|cert| cert.issuer.as_str())),
result.tls.as_ref().and_then(|tls| {
tls.certificate
.as_ref()
.and_then(|cert| cert.not_before.as_deref())
}),
result.tls.as_ref().and_then(|tls| {
tls.certificate
.as_ref()
.and_then(|cert| cert.not_after.as_deref())
}),
result
.tls
.as_ref()
.and_then(|tls| tls.error_message.as_deref()),
created_at_ms,
],
)?;
Ok(ProbeResult {
id: self.conn.last_insert_rowid(),
target: result.target.clone(),
request_path: result.request_path.clone(),
report: result.report.clone(),
status: result.status,
response_status_code: result.response_status_code,
response_status_text: result.response_status_text.clone(),
response_bytes: result.response_bytes,
error_message: result.error_message.clone(),
tls: result.tls.clone(),
created_at_ms,
})
}
pub fn save_report(&self, target: &str, report: &TimingReport) -> Result<ProbeResult> {
let result = NewProbeResult {
target: target.to_string(),
request_path: request_path_from_target(target),
report: report.clone(),
status: ProbeStatus::Succeeded,
response_status_code: None,
response_status_text: None,
response_bytes: None,
error_message: None,
tls: None,
};
self.save_result(&result)
}
pub fn recent_results(&self, limit: usize) -> Result<Vec<ProbeResult>> {
let mut statement = self.conn.prepare(
"SELECT id, target, request_path, dns_ms, tcp_ms, tls_ms, ttfb_ms, download_ms, total_ms, status,
response_status_code, response_status_text, response_bytes, error_message,
tls_enabled, tls_status, tls_version, tls_cipher_suite,
tls_certificate_subject, tls_certificate_issuer,
tls_certificate_not_before, tls_certificate_not_after,
tls_error_message, created_at_ms
FROM runs
ORDER BY created_at_ms DESC, id DESC
LIMIT ?1",
)?;
let rows = statement.query_map([limit as i64], read_probe_result)?;
rows.collect::<std::result::Result<Vec<_>, _>>()
.map_err(Into::into)
}
pub fn recent_results_for_target(&self, target: &str, limit: usize) -> Result<Vec<ProbeResult>> {
let mut statement = self.conn.prepare(
"SELECT id, target, request_path, dns_ms, tcp_ms, tls_ms, ttfb_ms, download_ms, total_ms, status,
response_status_code, response_status_text, response_bytes, error_message,
tls_enabled, tls_status, tls_version, tls_cipher_suite,
tls_certificate_subject, tls_certificate_issuer,
tls_certificate_not_before, tls_certificate_not_after,
tls_error_message, created_at_ms
FROM runs
WHERE target = ?1
ORDER BY created_at_ms DESC, id DESC
LIMIT ?2",
)?;
let rows = statement.query_map(params![target, limit as i64], read_probe_result)?;
rows.collect::<std::result::Result<Vec<_>, _>>()
.map_err(Into::into)
}
pub fn recent_runs(&self, limit: usize) -> Result<Vec<ProbeResult>> {
self.recent_results(limit)
}
pub fn delete_results_for_target(&self, target: &str) -> Result<usize> {
let deleted = self
.conn
.execute("DELETE FROM runs WHERE target = ?1", params![target])?;
Ok(deleted)
}
pub fn count_results_for_target(&self, target: &str) -> Result<usize> {
let mut statement = self
.conn
.prepare("SELECT COUNT(*) FROM runs WHERE target = ?1")?;
let count = statement.query_row(params![target], |row| row.get::<_, i64>(0))?;
Ok(count.max(0) as usize)
}
pub fn latest_result_for_target(&self, target: &str) -> Result<Option<ProbeResult>> {
let mut statement = self.conn.prepare(
"SELECT id, target, request_path, dns_ms, tcp_ms, tls_ms, ttfb_ms, download_ms, total_ms, status,
response_status_code, response_status_text, response_bytes, error_message,
tls_enabled, tls_status, tls_version, tls_cipher_suite,
tls_certificate_subject, tls_certificate_issuer,
tls_certificate_not_before, tls_certificate_not_after,
tls_error_message, created_at_ms
FROM runs
WHERE target = ?1
ORDER BY created_at_ms DESC, id DESC
LIMIT 1",
)?;
let mut rows = statement.query([target])?;
match rows.next()? {
Some(row) => Ok(Some(read_probe_result(row)?)),
None => Ok(None),
}
}
fn ensure_column(&self, table: &str, column: &str, definition: &str) -> Result<()> {
let pragma = format!("PRAGMA table_info({table})");
let mut statement = self.conn.prepare(&pragma)?;
let rows = statement.query_map([], |row| row.get::<_, String>(1))?;
let existing_columns = rows.collect::<std::result::Result<Vec<_>, _>>()?;
if !existing_columns.iter().any(|existing| existing == column) {
let alter = format!("ALTER TABLE {table} ADD COLUMN {column} {definition}");
self.conn.execute(&alter, [])?;
}
Ok(())
}
pub fn latest_run_for_target(&self, target: &str) -> Result<Option<ProbeResult>> {
self.latest_result_for_target(target)
}
}
fn current_time_ms() -> Result<i64> {
let now = SystemTime::now().duration_since(UNIX_EPOCH)?;
Ok(now.as_millis() as i64)
}
fn read_probe_result(row: &rusqlite::Row<'_>) -> rusqlite::Result<ProbeResult> {
Ok(ProbeResult {
id: row.get(0)?,
target: row.get(1)?,
request_path: row.get(2)?,
report: TimingReport {
dns_ms: row.get::<_, i64>(3)? as u64,
tcp_ms: row.get::<_, i64>(4)? as u64,
tls_ms: row.get::<_, i64>(5)? as u64,
ttfb_ms: row.get::<_, i64>(6)? as u64,
download_ms: row.get::<_, i64>(7)? as u64,
total_ms: row.get::<_, i64>(8)? as u64,
},
status: probe_status_from_str(&row.get::<_, String>(9)?)?,
response_status_code: row.get::<_, Option<i64>>(10)?.map(|value| value as u16),
response_status_text: row.get(11)?,
response_bytes: row.get::<_, Option<i64>>(12)?.map(|value| value as u64),
error_message: row.get(13)?,
tls: read_tls_report(row)?,
created_at_ms: row.get(23)?,
})
}
fn probe_status_as_str(status: ProbeStatus) -> &'static str {
match status {
ProbeStatus::Succeeded => "succeeded",
ProbeStatus::Failed => "failed",
}
}
fn probe_status_from_str(value: &str) -> rusqlite::Result<ProbeStatus> {
match value {
"succeeded" => Ok(ProbeStatus::Succeeded),
"failed" => Ok(ProbeStatus::Failed),
_ => Err(rusqlite::Error::InvalidQuery),
}
}
fn tls_status_as_str(status: TlsStatus) -> &'static str {
match status {
TlsStatus::NotUsed => "not_used",
TlsStatus::HandshakeSucceeded => "handshake_succeeded",
TlsStatus::HandshakeFailed => "handshake_failed",
}
}
fn tls_status_from_str(value: &str) -> rusqlite::Result<TlsStatus> {
match value {
"not_used" => Ok(TlsStatus::NotUsed),
"handshake_succeeded" => Ok(TlsStatus::HandshakeSucceeded),
"handshake_failed" => Ok(TlsStatus::HandshakeFailed),
_ => Err(rusqlite::Error::InvalidQuery),
}
}
fn read_tls_report(row: &rusqlite::Row<'_>) -> rusqlite::Result<Option<TlsReport>> {
let enabled = row.get::<_, Option<i64>>(14)?;
let Some(enabled) = enabled else {
return Ok(None);
};
let subject = row.get::<_, Option<String>>(18)?;
let issuer = row.get::<_, Option<String>>(19)?;
let not_before = row.get::<_, Option<String>>(20)?;
let not_after = row.get::<_, Option<String>>(21)?;
let certificate = match (subject, issuer, not_before, not_after) {
(Some(subject), Some(issuer), not_before, not_after) => Some(CertificateInfo {
subject,
issuer,
not_before,
not_after,
}),
_ => None,
};
Ok(Some(TlsReport {
enabled: enabled != 0,
status: tls_status_from_str(&row.get::<_, String>(15)?)?,
version: row.get(16)?,
cipher_suite: row.get(17)?,
certificate,
error_message: row.get(22)?,
}))
}
fn request_path_from_target(target: &str) -> String {
url::Url::parse(target)
.ok()
.map(|url| {
let mut path = url.path().to_string();
if path.is_empty() {
path.push('/');
}
if let Some(query) = url.query() {
path.push('?');
path.push_str(query);
}
path
})
.unwrap_or_else(|| "/".to_string())
}
#[cfg(test)]
mod tests {
use crate::engine::timing::build_report;
use crate::models::{NewProbeResult, ProbeStatus, TlsReport, TlsStatus};
use super::SqliteStore;
#[test]
fn save_report_and_read_recent_results() {
let store = SqliteStore::in_memory().expect("store should initialize");
let report = build_report(10, 20, 30, 40, 5);
let saved_result = store
.save_report("http://localhost:3000", &report)
.expect("report should save");
let runs = store
.recent_results(10)
.expect("recent results should load");
assert_eq!(runs.len(), 1);
assert_eq!(runs[0].id, saved_result.id);
assert_eq!(runs[0].target, "http://localhost:3000");
assert_eq!(runs[0].request_path, "/");
assert_eq!(runs[0].report, report);
assert_eq!(runs[0].status, ProbeStatus::Succeeded);
assert_eq!(runs[0].response_status_code, None);
assert_eq!(runs[0].error_message, None);
assert_eq!(runs[0].tls, None);
}
#[test]
fn latest_result_for_target_returns_most_recent_match() {
let store = SqliteStore::in_memory().expect("store should initialize");
let first_report = build_report(1, 2, 3, 4, 5);
let second_report = build_report(4, 5, 6, 7, 8);
store
.save_report("http://localhost:3000", &first_report)
.expect("first report should save");
let latest_result = store
.save_report("http://localhost:3000", &second_report)
.expect("second report should save");
store
.save_report("http://localhost:4000", &first_report)
.expect("other target report should save");
let run = store
.latest_result_for_target("http://localhost:3000")
.expect("latest result should load")
.expect("target should have a run");
assert_eq!(run.id, latest_result.id);
assert_eq!(run.target, "http://localhost:3000");
assert_eq!(run.request_path, "/");
assert_eq!(run.report, second_report);
assert_eq!(run.status, ProbeStatus::Succeeded);
}
#[test]
fn recent_results_for_target_filters_by_target() {
let store = SqliteStore::in_memory().expect("store should initialize");
let report = build_report(1, 2, 3, 4, 5);
store
.save_report("http://localhost:3000", &report)
.expect("first target report should save");
store
.save_report("http://localhost:4000", &report)
.expect("second target report should save");
store
.save_report("http://localhost:3000", &report)
.expect("third target report should save");
let runs = store
.recent_results_for_target("http://localhost:3000", 10)
.expect("filtered results should load");
assert_eq!(runs.len(), 2);
assert!(runs.iter().all(|run| run.target == "http://localhost:3000"));
}
#[test]
fn delete_results_for_target_removes_matching_rows() {
let store = SqliteStore::in_memory().expect("store should initialize");
let report = build_report(1, 2, 3, 4, 5);
store
.save_report("http://localhost:3000", &report)
.expect("first target report should save");
store
.save_report("http://localhost:4000", &report)
.expect("second target report should save");
store
.save_report("http://localhost:3000", &report)
.expect("third target report should save");
let deleted = store
.delete_results_for_target("http://localhost:3000")
.expect("deletion should succeed");
assert_eq!(deleted, 2);
assert_eq!(
store
.recent_results_for_target("http://localhost:3000", 10)
.expect("filtered results should load")
.len(),
0
);
assert_eq!(
store
.recent_results_for_target("http://localhost:4000", 10)
.expect("remaining results should load")
.len(),
1
);
assert_eq!(
store
.count_results_for_target("http://localhost:3000")
.expect("count should load"),
0
);
assert_eq!(
store
.count_results_for_target("http://localhost:4000")
.expect("remaining count should load"),
1
);
}
#[test]
fn save_result_persists_failure_details() {
let store = SqliteStore::in_memory().expect("store should initialize");
let result = NewProbeResult {
target: "http://localhost:3000".to_string(),
request_path: "/".to_string(),
report: build_report(0, 0, 0, 0, 0),
status: ProbeStatus::Failed,
response_status_code: None,
response_status_text: None,
response_bytes: None,
error_message: Some("request timed out".to_string()),
tls: Some(TlsReport {
enabled: true,
status: TlsStatus::HandshakeFailed,
version: None,
cipher_suite: None,
certificate: None,
error_message: Some("request timed out".to_string()),
}),
};
let saved = store
.save_result(&result)
.expect("result should save");
let loaded = store
.latest_result_for_target("http://localhost:3000")
.expect("latest result should load")
.expect("target should have a result");
assert_eq!(loaded.id, saved.id);
assert_eq!(loaded.status, ProbeStatus::Failed);
assert_eq!(loaded.request_path, "/");
assert_eq!(loaded.error_message.as_deref(), Some("request timed out"));
assert_eq!(
loaded.tls.expect("tls should be stored").status,
TlsStatus::HandshakeFailed
);
}
}