loadsmith_thunderstore/index/
sqlite.rs

1use std::{
2    path::Path,
3    sync::Arc,
4    time::{Duration, Instant},
5};
6
7use chrono::{DateTime, Utc};
8use futures::{TryStreamExt, pin_mut};
9use loadsmith_core::{PackageId, PackageRef, Version};
10use parking_lot::Mutex;
11use rusqlite::OptionalExtension;
12use thunderstore::VersionIdent;
13use tracing::{debug, trace};
14
15use crate::Result;
16
17/// A SQLite-backed package index using the thunderstore API.
18///
19/// Stores package data in a local SQLite database with WAL journal mode for
20/// concurrent reads.
21///
22/// # Examples
23///
24/// ```
25/// use loadsmith_thunderstore::sqlite::SqliteIndex;
26/// use thunderstore::Client;
27///
28/// let client = Client::new();
29/// let db = rusqlite::Connection::open_in_memory().unwrap();
30/// let index = SqliteIndex::connect(client, db).unwrap();
31/// ```
32#[derive(Debug, Clone)]
33pub struct SqliteIndex {
34    client: thunderstore::Client,
35    db: Arc<Mutex<rusqlite::Connection>>,
36}
37
38/// Metadata about a community's last index update.
39///
40/// # Examples
41///
42/// ```
43/// use loadsmith_thunderstore::sqlite::CommunityMetadata;
44///
45/// let meta = CommunityMetadata {
46///     community: "rounds".into(),
47///     last_updated: None,
48/// };
49/// assert_eq!(meta.community, "rounds");
50/// ```
51#[derive(Debug, Clone)]
52pub struct CommunityMetadata {
53    pub community: String,
54    pub last_updated: Option<DateTime<Utc>>,
55}
56
57impl SqliteIndex {
58    /// Creates a new SQLite index from an existing connection and initialises
59    /// the schema.
60    ///
61    /// # Examples
62    ///
63    /// ```
64    /// use loadsmith_thunderstore::sqlite::SqliteIndex;
65    /// use thunderstore::Client;
66    ///
67    /// let db = rusqlite::Connection::open_in_memory().unwrap();
68    /// let index = SqliteIndex::connect(Client::new(), db).unwrap();
69    /// ```
70    pub fn connect(client: thunderstore::Client, db: rusqlite::Connection) -> Result<Self> {
71        db.execute_batch(include_str!("queries/create_schema.sql"))?;
72        Ok(Self {
73            client,
74            db: Arc::new(Mutex::new(db)),
75        })
76    }
77
78    /// Opens a SQLite database file and initialises the schema.
79    ///
80    /// Enables WAL journal mode for better concurrent read performance.
81    ///
82    /// # Examples
83    ///
84    /// ```no_run
85    /// use loadsmith_thunderstore::sqlite::SqliteIndex;
86    /// use thunderstore::Client;
87    ///
88    /// let index = SqliteIndex::open(Client::new(), "./thunderstore.db").unwrap();
89    /// ```
90    pub fn open(client: thunderstore::Client, db_path: impl AsRef<Path>) -> Result<Self> {
91        let db = rusqlite::Connection::open(db_path)?;
92        db.pragma_update(None, "journal_mode", "WAL")?;
93        Self::connect(client, db)
94    }
95
96    /// Returns metadata about a community, including the last update time.
97    ///
98    /// Returns `None` if the community has not been indexed yet.
99    ///
100    /// # Examples
101    ///
102    /// ```
103    /// use loadsmith_thunderstore::sqlite::SqliteIndex;
104    /// use thunderstore::Client;
105    ///
106    /// let db = rusqlite::Connection::open_in_memory().unwrap();
107    /// let index = SqliteIndex::connect(Client::new(), db).unwrap();
108    /// let meta = index.community_metadata("rounds").unwrap();
109    /// assert!(meta.is_none());
110    /// ```
111    pub fn community_metadata(
112        &self,
113        community: impl AsRef<str>,
114    ) -> Result<Option<CommunityMetadata>> {
115        let db = self.db.lock();
116
117        let metadata = db
118            .prepare("select * from community_meta where community = ?")?
119            .query_row(rusqlite::params![community.as_ref()], |row| {
120                let community: String = row.get(0)?;
121                let last_updated: Option<String> = row.get(1)?;
122                let last_updated = last_updated
123                    .map(|s| DateTime::parse_from_rfc3339(&s).map(|dt| dt.with_timezone(&Utc)))
124                    .transpose()
125                    .unwrap();
126
127                Ok(CommunityMetadata {
128                    community,
129                    last_updated,
130                })
131            })
132            .optional()?;
133
134        Ok(metadata)
135    }
136
137    fn set_community_metadata(
138        &self,
139        community: impl AsRef<str>,
140        last_updated: Option<DateTime<Utc>>,
141    ) -> Result<()> {
142        let db = self.db.lock();
143
144        db.execute(
145            "insert or replace into community_meta (community, last_updated) values (?1, ?2)",
146            rusqlite::params![community.as_ref(), last_updated.map(|dt| dt.to_rfc3339())],
147        )?;
148
149        Ok(())
150    }
151
152    /// Fetches and stores the package index for a thunderstore community.
153    ///
154    /// Inserts or replaces all packages for the given community and records
155    /// the update timestamp.
156    ///
157    /// # Examples
158    ///
159    /// ```no_run
160    /// use loadsmith_thunderstore::sqlite::SqliteIndex;
161    /// use thunderstore::Client;
162    ///
163    /// let db = rusqlite::Connection::open_in_memory().unwrap();
164    /// let index = SqliteIndex::connect(Client::new(), db).unwrap();
165    /// let rt = tokio::runtime::Runtime::new().unwrap();
166    /// rt.block_on(async {
167    ///     index.update("lethal-company").await.unwrap();
168    /// });
169    /// ```
170    pub async fn update(&self, community: impl AsRef<str>) -> Result<()> {
171        let community = community.as_ref();
172
173        debug!(community, "fetching package index from thunderstore");
174
175        let mut start = Instant::now();
176        let mut time_waiting = Duration::ZERO;
177
178        let stream = self.client.stream_package_index_v1(community).await?;
179        pin_mut!(stream);
180
181        time_waiting += start.elapsed();
182        start = Instant::now();
183
184        while let Some(batch) = stream.try_next().await? {
185            trace!(len = batch.len(), waited = ?start.elapsed(), "received package batch");
186
187            time_waiting += start.elapsed();
188            start = Instant::now();
189
190            let mut db = self.db.lock();
191            let tx = db.transaction()?;
192
193            for package in batch {
194                tx.execute(
195                    "insert or replace into packages (community, package_id, package) values (?1, ?2, ?3)",
196                    rusqlite::params![community, package.ident.as_str(), serde_json::to_string(&package)?],
197                )?;
198            }
199
200            tx.commit()?;
201
202            trace!(
203                duration = ?start.elapsed(),
204                "inserted package batch into database"
205            );
206
207            start = Instant::now();
208        }
209
210        self.set_community_metadata(community, Some(Utc::now()))?;
211
212        debug!(
213            total_time_waiting = ?time_waiting,
214            "finished fetching package index from thunderstore"
215        );
216
217        Ok(())
218    }
219
220    /// Returns version information for a package, if found.
221    ///
222    /// # Examples
223    ///
224    /// ```no_run
225    /// use loadsmith_core::PackageId;
226    /// use loadsmith_thunderstore::sqlite::SqliteIndex;
227    /// use thunderstore::Client;
228    ///
229    /// let db = rusqlite::Connection::open_in_memory().unwrap();
230    /// let index = SqliteIndex::connect(Client::new(), db).unwrap();
231    /// let versions = index.version_info(&PackageId::new("Author-Pkg")).unwrap();
232    /// ```
233    pub fn version_info(
234        &self,
235        id: &PackageId,
236    ) -> Result<Option<Vec<loadsmith_registry::VersionInfo>>> {
237        let db = self.db.lock();
238
239        let versions = db
240            .prepare(include_str!("queries/select_version_info.sql"))?
241            .query_map(rusqlite::params![id.as_str()], |row| {
242                let version: Version = row.get::<_, String>(0)?.parse().unwrap();
243
244                Ok(loadsmith_registry::VersionInfo { version })
245            })?
246            .collect::<rusqlite::Result<Vec<_>>>()?;
247
248        if versions.is_empty() {
249            Ok(None)
250        } else {
251            Ok(Some(versions))
252        }
253    }
254
255    /// Resolves a package reference to download URL, file size, and
256    /// dependencies.
257    ///
258    /// # Examples
259    ///
260    /// ```no_run
261    /// use loadsmith_core::{PackageId, PackageRef, Version};
262    /// use loadsmith_thunderstore::sqlite::SqliteIndex;
263    /// use thunderstore::Client;
264    ///
265    /// let db = rusqlite::Connection::open_in_memory().unwrap();
266    /// let index = SqliteIndex::connect(Client::new(), db).unwrap();
267    /// let resolved = index
268    ///     .resolve(&PackageRef::new(
269    ///         PackageId::new("Author-Pkg"),
270    ///         Version::new(1, 0, 0),
271    ///     ))
272    ///     .unwrap();
273    /// ```
274    pub fn resolve(
275        &self,
276        ref_: &PackageRef,
277    ) -> Result<Option<loadsmith_registry::ResolvedVersion>> {
278        let db = self.db.lock();
279
280        let resolved = db
281            .prepare(include_str!("queries/select_resolved.sql"))?
282            .query_one(
283                rusqlite::params![ref_.id().as_str(), ref_.version().to_string()],
284                |row| {
285                    let url = row.get::<_, String>(0)?;
286                    let size = row.get::<_, i64>(1)?;
287
288                    let deps_json = row.get::<_, String>(2)?;
289                    let deps: Vec<VersionIdent> = serde_json::from_str(&deps_json).unwrap();
290
291                    let categories_json = row.get::<_, String>(3)?;
292                    let categories: Vec<String> = serde_json::from_str(&categories_json).unwrap();
293                    let is_modpack = categories.contains(&super::MODPACK_CATEGORY.to_string());
294                    let deps = super::dependencies_from_idents(&deps, is_modpack);
295
296                    let url = loadsmith_core::FileUrl::try_from_url(&url).unwrap();
297
298                    Ok(loadsmith_registry::ResolvedVersion {
299                        url,
300                        deps,
301                        size: Some(size as u64),
302                        checksum: None,
303                    })
304                },
305            )?;
306
307        Ok(Some(resolved))
308    }
309
310    /// Searches for packages by name, optionally scoped to a community.
311    ///
312    /// Results are ordered by pinned status, rating score, and total
313    /// downloads.
314    ///
315    /// # Examples
316    ///
317    /// ```no_run
318    /// use loadsmith_thunderstore::sqlite::SqliteIndex;
319    /// use thunderstore::Client;
320    ///
321    /// let db = rusqlite::Connection::open_in_memory().unwrap();
322    /// let index = SqliteIndex::connect(Client::new(), db).unwrap();
323    /// let results = index.search_packages("BepInEx", Some("rounds")).unwrap();
324    /// ```
325    pub fn search_packages(&self, query: &str, community: Option<&str>) -> Result<Vec<PackageId>> {
326        let db = self.db.lock();
327
328        let packages = db
329            .prepare(include_str!("queries/search_packages.sql"))?
330            .query_map(
331                rusqlite::params![format!("%{}%", query), community],
332                |row| {
333                    let id: String = row.get(0)?;
334                    Ok(PackageId::new(&id))
335                },
336            )?
337            .collect::<rusqlite::Result<Vec<_>>>()?;
338
339        Ok(packages)
340    }
341}
342
343#[cfg(test)]
344mod tests {
345    use super::*;
346
347    #[tokio::test]
348    #[ignore]
349    async fn fetch_and_resolve_version() {
350        const COMMUNITY: &str = "rounds";
351
352        let client = thunderstore::Client::new();
353        let db = rusqlite::Connection::open_in_memory().unwrap();
354        let index = SqliteIndex::connect(client, db).unwrap();
355
356        assert!(index.community_metadata(COMMUNITY).unwrap().is_none());
357
358        index.update("rounds").await.unwrap();
359
360        assert!(
361            index
362                .community_metadata(COMMUNITY)
363                .unwrap()
364                .and_then(|c| c.last_updated)
365                .is_some()
366        );
367
368        let rounds_with_friends = PackageId::new("olavim-RoundsWithFriends");
369
370        let versions = index.version_info(&rounds_with_friends).unwrap();
371        assert!(versions.is_some());
372        println!("{versions:#?}");
373
374        let resolved = index
375            .resolve(&PackageRef::new(
376                rounds_with_friends,
377                versions.unwrap().pop().unwrap().version,
378            ))
379            .unwrap();
380        assert!(resolved.is_some());
381        println!("{resolved:#?}");
382
383        let versions = index.version_info(&PackageId::new("fake_package")).unwrap();
384
385        assert!(versions.is_none());
386    }
387}
loadsmith_thunderstore/index/sqlite.rs

loadsmith_thunderstore/index/
sqlite.rs
