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

soar_db/repository/
metadata.rs
