soar_db/repository/
metadata.rs

1//! Metadata database repository for package queries.
2
3use std::sync::OnceLock;
4
5use diesel::{dsl::sql, prelude::*, sql_types::Text};
6use regex::Regex;
7use serde_json::json;
8use soar_registry::RemotePackage;
9use tracing::{debug, trace};
10
11/// Regex for extracting name and contact from maintainer string format "Name (contact)".
12static MAINTAINER_RE: OnceLock<Regex> = OnceLock::new();
13
14use super::core::SortDirection;
15use crate::{
16    models::{
17        metadata::{
18            FuzzyCandidate, Maintainer, NewMaintainer, NewPackage, NewPackageMaintainer,
19            NewRepository, Package, PackageListing,
20        },
21        types::PackageProvide,
22    },
23    schema::metadata::{maintainers, package_maintainers, packages, repository},
24};
25
26/// Helper struct for raw SQL queries returning just pkg_id.
27#[derive(Debug, QueryableByName)]
28struct PkgIdOnly {
29    #[diesel(sql_type = Text)]
30    pkg_id: String,
31}
32
33/// Repository for package metadata operations.
34pub struct MetadataRepository;
35
36impl MetadataRepository {
37    /// Lists all packages using Diesel DSL.
38    pub fn list_all(conn: &mut SqliteConnection) -> QueryResult<Vec<Package>> {
39        trace!("listing all packages");
40        let result = packages::table
41            .order(packages::pkg_name.asc())
42            .select(Package::as_select())
43            .load(conn);
44        if let Ok(ref packages) = result {
45            debug!(count = packages.len(), "listed all packages");
46        }
47        result
48    }
49
50    /// Lists all packages with only the fields needed for display.
51    /// This is much more memory-efficient than list_all for large package lists.
52    pub fn list_all_minimal(conn: &mut SqliteConnection) -> QueryResult<Vec<PackageListing>> {
53        trace!("listing all packages (minimal fields)");
54        let result = packages::table
55            .order(packages::pkg_name.asc())
56            .select(PackageListing::as_select())
57            .load(conn);
58        if let Ok(ref packages) = result {
59            debug!(count = packages.len(), "listed all packages (minimal)");
60        }
61        result
62    }
63
64    /// Loads lightweight package data for fuzzy matching.
65    /// Returns only (id, pkg_name, pkg_id, description) to minimize memory and deserialization.
66    pub fn load_fuzzy_candidates(conn: &mut SqliteConnection) -> QueryResult<Vec<FuzzyCandidate>> {
67        trace!("loading fuzzy candidates");
68        packages::table
69            .select(FuzzyCandidate::as_select())
70            .load(conn)
71    }
72
73    /// Fetches full package details for a set of package IDs.
74    pub fn find_by_ids(conn: &mut SqliteConnection, ids: &[i32]) -> QueryResult<Vec<Package>> {
75        if ids.is_empty() {
76            return Ok(Vec::new());
77        }
78        packages::table
79            .filter(packages::id.eq_any(ids))
80            .select(Package::as_select())
81            .load(conn)
82    }
83
84    /// Lists packages with pagination and sorting using Diesel DSL.
85    pub fn list_paginated(
86        conn: &mut SqliteConnection,
87        page: i64,
88        per_page: i64,
89    ) -> QueryResult<Vec<Package>> {
90        let offset = (page - 1) * per_page;
91        trace!(
92            page = page,
93            per_page = per_page,
94            offset = offset,
95            "listing paginated packages"
96        );
97
98        let result = packages::table
99            .order(packages::pkg_name.asc())
100            .limit(per_page)
101            .offset(offset)
102            .select(Package::as_select())
103            .load(conn);
104        if let Ok(ref packages) = result {
105            debug!(
106                count = packages.len(),
107                page = page,
108                "fetched paginated packages"
109            );
110        }
111        result
112    }
113
114    /// Gets the repository name from the database.
115    pub fn get_repo_name(conn: &mut SqliteConnection) -> QueryResult<Option<String>> {
116        repository::table
117            .select(repository::name)
118            .first(conn)
119            .optional()
120    }
121
122    /// Gets the repository etag from the database.
123    pub fn get_repo_etag(conn: &mut SqliteConnection) -> QueryResult<Option<String>> {
124        repository::table
125            .select(repository::etag)
126            .first(conn)
127            .optional()
128    }
129
130    /// Updates the repository metadata (name and etag).
131    pub fn update_repo_metadata(
132        conn: &mut SqliteConnection,
133        name: &str,
134        etag: &str,
135    ) -> QueryResult<usize> {
136        diesel::update(repository::table)
137            .set((repository::name.eq(name), repository::etag.eq(etag)))
138            .execute(conn)
139    }
140
141    /// Finds a package by ID using Diesel DSL.
142    pub fn find_by_id(conn: &mut SqliteConnection, id: i32) -> QueryResult<Option<Package>> {
143        trace!(id = id, "finding package by id");
144        let result = packages::table
145            .filter(packages::id.eq(id))
146            .select(Package::as_select())
147            .first(conn)
148            .optional();
149        if let Ok(ref pkg) = result {
150            if pkg.is_some() {
151                debug!(id = id, "found package by id");
152            } else {
153                trace!(id = id, "package not found by id");
154            }
155        }
156        result
157    }
158
159    /// Finds packages by name (exact match) using Diesel DSL.
160    pub fn find_by_name(conn: &mut SqliteConnection, name: &str) -> QueryResult<Vec<Package>> {
161        trace!(name = name, "finding packages by name");
162        let result = packages::table
163            .filter(packages::pkg_name.eq(name))
164            .select(Package::as_select())
165            .load(conn);
166        if let Ok(ref packages) = result {
167            debug!(
168                name = name,
169                count = packages.len(),
170                "found packages by name"
171            );
172        }
173        result
174    }
175
176    /// Finds a package by pkg_id using Diesel DSL.
177    pub fn find_by_pkg_id(
178        conn: &mut SqliteConnection,
179        pkg_id: &str,
180    ) -> QueryResult<Option<Package>> {
181        trace!(pkg_id = pkg_id, "finding package by pkg_id");
182        let result = packages::table
183            .filter(packages::pkg_id.eq(pkg_id))
184            .select(Package::as_select())
185            .first(conn)
186            .optional();
187        if let Ok(ref pkg) = result {
188            if pkg.is_some() {
189                debug!(pkg_id = pkg_id, "found package by pkg_id");
190            } else {
191                trace!(pkg_id = pkg_id, "package not found by pkg_id");
192            }
193        }
194        result
195    }
196
197    /// Finds packages that match pkg_name and optionally pkg_id and version using Diesel DSL.
198    pub fn find_by_query(
199        conn: &mut SqliteConnection,
200        pkg_name: Option<&str>,
201        pkg_id: Option<&str>,
202        version: Option<&str>,
203    ) -> QueryResult<Vec<Package>> {
204        let mut query = packages::table.into_boxed();
205
206        if let Some(name) = pkg_name {
207            query = query.filter(packages::pkg_name.eq(name));
208        }
209        if let Some(id) = pkg_id {
210            if id != "all" {
211                query = query.filter(packages::pkg_id.eq(id));
212            }
213        }
214        if let Some(ver) = version {
215            query = query.filter(packages::version.eq(ver));
216        }
217
218        query.select(Package::as_select()).load(conn)
219    }
220
221    /// Searches packages by pattern (case-insensitive LIKE query) using Diesel DSL.
222    /// Searches across pkg_name and pkg_id fields.
223    pub fn search(
224        conn: &mut SqliteConnection,
225        pattern: &str,
226        limit: Option<i64>,
227    ) -> QueryResult<Vec<Package>> {
228        debug!(pattern = pattern, limit = ?limit, "searching packages");
229        let like_pattern = format!("%{}%", pattern.to_lowercase());
230
231        let mut query = packages::table
232            .filter(
233                sql::<diesel::sql_types::Bool>("LOWER(pkg_name) LIKE ")
234                    .bind::<Text, _>(&like_pattern)
235                    .sql(" OR LOWER(pkg_id) LIKE ")
236                    .bind::<Text, _>(&like_pattern),
237            )
238            .order(packages::pkg_name.asc())
239            .into_boxed();
240
241        if let Some(lim) = limit {
242            query = query.limit(lim);
243        }
244
245        let result = query.select(Package::as_select()).load(conn);
246        if let Ok(ref packages) = result {
247            debug!(
248                pattern = pattern,
249                count = packages.len(),
250                "search completed"
251            );
252        }
253        result
254    }
255
256    /// Searches packages (case-sensitive LIKE query) using Diesel DSL.
257    pub fn search_case_sensitive(
258        conn: &mut SqliteConnection,
259        pattern: &str,
260        limit: Option<i64>,
261    ) -> QueryResult<Vec<Package>> {
262        let like_pattern = format!("%{}%", pattern);
263
264        let mut query = packages::table
265            .filter(
266                packages::pkg_name
267                    .like(&like_pattern)
268                    .or(packages::pkg_id.like(&like_pattern)),
269            )
270            .order(packages::pkg_name.asc())
271            .into_boxed();
272
273        if let Some(lim) = limit {
274            query = query.limit(lim);
275        }
276
277        query.select(Package::as_select()).load(conn)
278    }
279
280    /// Checks if a package exists that replaces the given pkg_id.
281    /// Returns the pkg_id of the replacement package if found.
282    /// Uses raw SQL for JSON array search since Diesel doesn't support json_each.
283    pub fn find_replacement_pkg_id(
284        conn: &mut SqliteConnection,
285        pkg_id: &str,
286    ) -> QueryResult<Option<String>> {
287        let query = "SELECT pkg_id FROM packages WHERE EXISTS \
288                     (SELECT 1 FROM json_each(replaces) WHERE json_each.value = ?) LIMIT 1";
289
290        diesel::sql_query(query)
291            .bind::<Text, _>(pkg_id)
292            .load::<PkgIdOnly>(conn)
293            .map(|mut v| v.pop().map(|p| p.pkg_id))
294    }
295
296    /// Counts total packages.
297    pub fn count(conn: &mut SqliteConnection) -> QueryResult<i64> {
298        packages::table.count().get_result(conn)
299    }
300
301    /// Counts packages matching a search pattern using Diesel DSL.
302    pub fn count_search(conn: &mut SqliteConnection, pattern: &str) -> QueryResult<i64> {
303        let like_pattern = format!("%{}%", pattern.to_lowercase());
304
305        packages::table
306            .filter(
307                sql::<diesel::sql_types::Bool>("LOWER(pkg_name) LIKE ")
308                    .bind::<Text, _>(&like_pattern)
309                    .sql(" OR LOWER(pkg_id) LIKE ")
310                    .bind::<Text, _>(&like_pattern),
311            )
312            .count()
313            .get_result(conn)
314    }
315
316    /// Inserts a new package.
317    pub fn insert(conn: &mut SqliteConnection, package: &NewPackage) -> QueryResult<usize> {
318        diesel::insert_into(packages::table)
319            .values(package)
320            .execute(conn)
321    }
322
323    /// Gets the last inserted package ID.
324    pub fn last_insert_id(conn: &mut SqliteConnection) -> QueryResult<i32> {
325        diesel::select(sql::<diesel::sql_types::Integer>("last_insert_rowid()")).get_result(conn)
326    }
327
328    /// Finds or creates a maintainer.
329    pub fn find_or_create_maintainer(
330        conn: &mut SqliteConnection,
331        contact: &str,
332        name: &str,
333    ) -> QueryResult<i32> {
334        let existing: Option<Maintainer> = maintainers::table
335            .filter(maintainers::contact.eq(contact))
336            .select(Maintainer::as_select())
337            .first(conn)
338            .optional()?;
339
340        if let Some(m) = existing {
341            return Ok(m.id);
342        }
343
344        let new_maintainer = NewMaintainer {
345            contact,
346            name,
347        };
348        diesel::insert_into(maintainers::table)
349            .values(&new_maintainer)
350            .execute(conn)?;
351
352        Self::last_insert_id(conn)
353    }
354
355    /// Links a maintainer to a package.
356    pub fn link_maintainer(
357        conn: &mut SqliteConnection,
358        package_id: i32,
359        maintainer_id: i32,
360    ) -> QueryResult<usize> {
361        let link = NewPackageMaintainer {
362            package_id,
363            maintainer_id,
364        };
365        diesel::insert_into(package_maintainers::table)
366            .values(&link)
367            .on_conflict_do_nothing()
368            .execute(conn)
369    }
370
371    /// Gets maintainers for a package.
372    pub fn get_maintainers(
373        conn: &mut SqliteConnection,
374        package_id: i32,
375    ) -> QueryResult<Vec<Maintainer>> {
376        maintainers::table
377            .inner_join(
378                package_maintainers::table
379                    .on(maintainers::id.eq(package_maintainers::maintainer_id)),
380            )
381            .filter(package_maintainers::package_id.eq(package_id))
382            .select(Maintainer::as_select())
383            .load(conn)
384    }
385
386    /// Deletes all packages (for reimport).
387    pub fn delete_all(conn: &mut SqliteConnection) -> QueryResult<usize> {
388        diesel::delete(packages::table).execute(conn)
389    }
390
391    /// Finds packages with flexible filtering using Diesel DSL.
392    pub fn find_filtered(
393        conn: &mut SqliteConnection,
394        pkg_name: Option<&str>,
395        pkg_id: Option<&str>,
396        version: Option<&str>,
397        limit: Option<i64>,
398        sort_by_name: Option<SortDirection>,
399    ) -> QueryResult<Vec<Package>> {
400        let mut query = packages::table.into_boxed();
401
402        if let Some(name) = pkg_name {
403            query = query.filter(packages::pkg_name.eq(name));
404        }
405        if let Some(id) = pkg_id {
406            if id != "all" {
407                query = query.filter(packages::pkg_id.eq(id));
408            }
409        }
410        if let Some(ver) = version {
411            query = query.filter(packages::version.eq(ver));
412        }
413
414        if let Some(direction) = sort_by_name {
415            query = match direction {
416                SortDirection::Asc => query.order(packages::pkg_name.asc()),
417                SortDirection::Desc => query.order(packages::pkg_name.desc()),
418            };
419        }
420
421        if let Some(lim) = limit {
422            query = query.limit(lim);
423        }
424
425        query.select(Package::as_select()).load(conn)
426    }
427
428    /// Finds packages with a newer version than the given version.
429    /// Used for update checking.
430    /// Uses Diesel DSL with raw SQL filter for version comparison.
431    pub fn find_newer_version(
432        conn: &mut SqliteConnection,
433        pkg_name: &str,
434        pkg_id: &str,
435        current_version: &str,
436    ) -> QueryResult<Option<Package>> {
437        trace!(
438            pkg_name = pkg_name,
439            pkg_id = pkg_id,
440            current_version = current_version,
441            "checking for newer version"
442        );
443        // Handle both regular versions and HEAD- versions
444        let head_version = if current_version.starts_with("HEAD-") && current_version.len() > 14 {
445            current_version[14..].to_string()
446        } else {
447            String::new()
448        };
449
450        let result = packages::table
451            .filter(packages::pkg_name.eq(pkg_name))
452            .filter(packages::pkg_id.eq(pkg_id))
453            .filter(
454                sql::<diesel::sql_types::Bool>("(version > ")
455                    .bind::<Text, _>(current_version)
456                    .sql(" OR (version LIKE 'HEAD-%' AND substr(version, 15) > ")
457                    .bind::<Text, _>(&head_version)
458                    .sql("))"),
459            )
460            .order(packages::version.desc())
461            .select(Package::as_select())
462            .first(conn)
463            .optional();
464        if let Ok(Some(ref p)) = result {
465            debug!(
466                "newer version available: {}#{} -> {}",
467                pkg_name, pkg_id, p.version
468            );
469        }
470        result
471    }
472
473    /// Checks if a package with the given pkg_id exists.
474    pub fn exists_by_pkg_id(conn: &mut SqliteConnection, pkg_id: &str) -> QueryResult<bool> {
475        diesel::select(diesel::dsl::exists(
476            packages::table.filter(packages::pkg_id.eq(pkg_id)),
477        ))
478        .get_result(conn)
479    }
480
481    /// Imports packages from remote metadata (JSON format).
482    pub fn import_packages(
483        conn: &mut SqliteConnection,
484        metadata: &[RemotePackage],
485        repo_name: &str,
486    ) -> QueryResult<()> {
487        debug!(
488            repo_name = repo_name,
489            count = metadata.len(),
490            "importing packages from remote metadata"
491        );
492        conn.transaction(|conn| {
493            diesel::insert_into(repository::table)
494                .values(NewRepository {
495                    name: repo_name,
496                    etag: "",
497                })
498                .on_conflict(repository::name)
499                .do_update()
500                .set(repository::etag.eq(""))
501                .execute(conn)?;
502            trace!(repo_name = repo_name, "repository record upserted");
503
504            for package in metadata {
505                Self::insert_remote_package(conn, package)?;
506            }
507            debug!(
508                repo_name = repo_name,
509                count = metadata.len(),
510                "package import completed"
511            );
512            Ok(())
513        })
514    }
515
516    /// Inserts a single remote package.
517    fn insert_remote_package(
518        conn: &mut SqliteConnection,
519        package: &RemotePackage,
520    ) -> QueryResult<()> {
521        trace!(
522            pkg_id = package.pkg_id,
523            pkg_name = package.pkg_name,
524            version = package.version,
525            "inserting remote package"
526        );
527        let provides = package.provides.as_ref().map(|vec| {
528            vec.iter()
529                .map(|p| PackageProvide::from_string(p))
530                .collect::<Vec<_>>()
531        });
532
533        let new_package = NewPackage {
534            pkg_id: &package.pkg_id,
535            pkg_name: &package.pkg_name,
536            pkg_family: package.pkg_family.as_deref(),
537            pkg_type: package.pkg_type.as_deref(),
538            pkg_webpage: package.pkg_webpage.as_deref(),
539            app_id: package.app_id.as_deref(),
540            description: Some(&package.description),
541            version: &package.version,
542            licenses: Some(json!(package.licenses)),
543            download_url: &package.download_url,
544            size: package.size_raw.map(|s| s as i64),
545            ghcr_pkg: package.ghcr_pkg.as_deref(),
546            ghcr_size: package.ghcr_size_raw.map(|s| s as i64),
547            ghcr_blob: package.ghcr_blob.as_deref(),
548            ghcr_url: package.ghcr_url.as_deref(),
549            bsum: package.bsum.as_deref(),
550            icon: package.icon.as_deref(),
551            desktop: package.desktop.as_deref(),
552            appstream: package.appstream.as_deref(),
553            homepages: Some(json!(package.homepages)),
554            notes: Some(json!(package.notes)),
555            source_urls: Some(json!(package.src_urls)),
556            tags: Some(json!(&package.tags)),
557            categories: Some(json!(package.categories)),
558            build_id: package.build_id.as_deref(),
559            build_date: package.build_date.as_deref(),
560            build_action: package.build_action.as_deref(),
561            build_script: package.build_script.as_deref(),
562            build_log: package.build_log.as_deref(),
563            provides: Some(json!(provides)),
564            snapshots: Some(json!(package.snapshots)),
565            replaces: Some(json!(package.replaces)),
566            soar_syms: package.soar_syms.unwrap_or(false),
567            desktop_integration: package.desktop_integration,
568            portable: package.portable,
569        };
570
571        let inserted = diesel::insert_into(packages::table)
572            .values(&new_package)
573            .on_conflict((packages::pkg_id, packages::pkg_name, packages::version))
574            .do_nothing()
575            .execute(conn)?;
576
577        if inserted == 0 {
578            trace!(pkg_id = package.pkg_id, "package already exists, skipping");
579            return Ok(());
580        }
581
582        let package_id = Self::last_insert_id(conn)?;
583
584        if let Some(maintainers) = &package.maintainers {
585            for maintainer in maintainers {
586                if let Some((name, contact)) = Self::extract_name_and_contact(maintainer) {
587                    let maintainer_id = Self::find_or_create_maintainer(conn, &contact, &name)?;
588                    Self::link_maintainer(conn, package_id, maintainer_id)?;
589                }
590            }
591        }
592
593        Ok(())
594    }
595
596    /// Extracts name and contact from maintainer string format "Name (contact)".
597    fn extract_name_and_contact(input: &str) -> Option<(String, String)> {
598        let re = MAINTAINER_RE.get_or_init(|| Regex::new(r"^([^()]+) \(([^)]+)\)$").unwrap());
599
600        if let Some(captures) = re.captures(input) {
601            let name = captures.get(1).map_or("", |m| m.as_str()).to_string();
602            let contact = captures.get(2).map_or("", |m| m.as_str()).to_string();
603            Some((name, contact))
604        } else {
605            None
606        }
607    }
608}
soar_db/repository/metadata.rs

soar_db/repository/
metadata.rs
