linkrs_server/web/storage/
sqlite.rs

1//! SQLite Storage Implementation
2
3use chrono::Utc;
4use sqlx::{sqlite::SqlitePoolOptions, Pool, Row, Sqlite};
5
6use crate::web::error::{WebError, WebResult};
7use crate::web::models::metadata::{FavoriteItem, HistoryItem};
8
9use super::MetadataStorage;
10
11/// SQLite storage for metadata
12pub struct SqliteStorage {
13    pool: Pool<Sqlite>,
14}
15
16impl SqliteStorage {
17    /// Create a new SQLite storage instance
18    pub async fn new(database_path: &str) -> WebResult<Self> {
19        let pool = SqlitePoolOptions::new()
20            .max_connections(5)
21            .connect(database_path)
22            .await
23            .map_err(|e| WebError::Storage(format!("Failed to connect to SQLite: {}", e)))?;
24
25        let storage = Self { pool };
26        storage.init_tables().await?;
27
28        Ok(storage)
29    }
30
31    /// Initialize database tables
32    async fn init_tables(&self) -> WebResult<()> {
33        sqlx::query(
34            r#"
35            CREATE TABLE IF NOT EXISTS query_history (
36                id TEXT PRIMARY KEY,
37                session_id TEXT NOT NULL,
38                query TEXT NOT NULL,
39                executed_at TIMESTAMP NOT NULL,
40                execution_time_ms INTEGER NOT NULL,
41                rows_returned INTEGER NOT NULL,
42                success BOOLEAN NOT NULL,
43                error_message TEXT,
44                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
45            )
46            "#,
47        )
48        .execute(&self.pool)
49        .await
50        .map_err(|e| WebError::Storage(format!("Failed to create history table: {}", e)))?;
51
52        sqlx::query(
53            r#"
54            CREATE TABLE IF NOT EXISTS query_favorites (
55                id TEXT PRIMARY KEY,
56                session_id TEXT NOT NULL,
57                name TEXT NOT NULL,
58                query TEXT NOT NULL,
59                description TEXT,
60                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
61                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
62            )
63            "#,
64        )
65        .execute(&self.pool)
66        .await
67        .map_err(|e| WebError::Storage(format!("Failed to create favorites table: {}", e)))?;
68
69        // Create indexes
70        sqlx::query(
71            "CREATE INDEX IF NOT EXISTS idx_history_session ON query_history(session_id, executed_at DESC)"
72        )
73        .execute(&self.pool)
74        .await
75        .map_err(|e| WebError::Storage(format!("Failed to create history index: {}", e)))?;
76
77        sqlx::query(
78            "CREATE INDEX IF NOT EXISTS idx_favorites_session ON query_favorites(session_id, created_at DESC)"
79        )
80        .execute(&self.pool)
81        .await
82        .map_err(|e| WebError::Storage(format!("Failed to create favorites index: {}", e)))?;
83
84        Ok(())
85    }
86}
87
88#[async_trait::async_trait]
89impl MetadataStorage for SqliteStorage {
90    async fn add_history(&self, item: &HistoryItem) -> WebResult<()> {
91        sqlx::query(
92            r#"
93            INSERT INTO query_history (id, session_id, query, executed_at, execution_time_ms, rows_returned, success, error_message)
94            VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)
95            "#,
96        )
97        .bind(&item.id)
98        .bind(&item.session_id)
99        .bind(&item.query)
100        .bind(item.executed_at)
101        .bind(item.execution_time_ms)
102        .bind(item.rows_returned)
103        .bind(item.success)
104        .bind(&item.error_message)
105        .execute(&self.pool)
106        .await
107        .map_err(|e| WebError::Storage(format!("Failed to add history: {}", e)))?;
108
109        Ok(())
110    }
111
112    async fn get_history(
113        &self,
114        session_id: &str,
115        limit: usize,
116        offset: usize,
117    ) -> WebResult<(Vec<HistoryItem>, i64)> {
118        let items: Vec<HistoryItem> = sqlx::query_as(
119            r#"
120            SELECT id, session_id, query, executed_at, execution_time_ms, rows_returned, success, error_message
121            FROM query_history
122            WHERE session_id = ?1
123            ORDER BY executed_at DESC
124            LIMIT ?2 OFFSET ?3
125            "#,
126        )
127        .bind(session_id)
128        .bind(limit as i64)
129        .bind(offset as i64)
130        .fetch_all(&self.pool)
131        .await
132        .map_err(|e| WebError::Storage(format!("Failed to get history: {}", e)))?;
133
134        let total: i64 =
135            sqlx::query_scalar("SELECT COUNT(*) FROM query_history WHERE session_id = ?1")
136                .bind(session_id)
137                .fetch_one(&self.pool)
138                .await
139                .map_err(|e| WebError::Storage(format!("Failed to count history: {}", e)))?;
140
141        Ok((items, total))
142    }
143
144    async fn delete_history(&self, id: &str, session_id: &str) -> WebResult<()> {
145        let result = sqlx::query("DELETE FROM query_history WHERE id = ?1 AND session_id = ?2")
146            .bind(id)
147            .bind(session_id)
148            .execute(&self.pool)
149            .await
150            .map_err(|e| WebError::Storage(format!("Failed to delete history: {}", e)))?;
151
152        if result.rows_affected() == 0 {
153            return Err(WebError::NotFound("History item not found".to_string()));
154        }
155
156        Ok(())
157    }
158
159    async fn clear_history(&self, session_id: &str) -> WebResult<()> {
160        sqlx::query("DELETE FROM query_history WHERE session_id = ?1")
161            .bind(session_id)
162            .execute(&self.pool)
163            .await
164            .map_err(|e| WebError::Storage(format!("Failed to clear history: {}", e)))?;
165
166        Ok(())
167    }
168
169    async fn add_favorite(&self, item: &FavoriteItem) -> WebResult<()> {
170        sqlx::query(
171            r#"
172            INSERT INTO query_favorites (id, session_id, name, query, description, created_at, updated_at)
173            VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)
174            "#,
175        )
176        .bind(&item.id)
177        .bind(&item.session_id)
178        .bind(&item.name)
179        .bind(&item.query)
180        .bind(&item.description)
181        .bind(item.created_at)
182        .bind(item.created_at)
183        .execute(&self.pool)
184        .await
185        .map_err(|e| WebError::Storage(format!("Failed to add favorite: {}", e)))?;
186
187        Ok(())
188    }
189
190    async fn get_favorites(&self, session_id: &str) -> WebResult<Vec<FavoriteItem>> {
191        let items: Vec<FavoriteItem> = sqlx::query_as(
192            r#"
193            SELECT id, session_id, name, query, description, created_at
194            FROM query_favorites
195            WHERE session_id = ?1
196            ORDER BY created_at DESC
197            "#,
198        )
199        .bind(session_id)
200        .fetch_all(&self.pool)
201        .await
202        .map_err(|e| WebError::Storage(format!("Failed to get favorites: {}", e)))?;
203
204        Ok(items)
205    }
206
207    async fn get_favorite(&self, id: &str, session_id: &str) -> WebResult<FavoriteItem> {
208        let item: FavoriteItem = sqlx::query_as(
209            r#"
210            SELECT id, session_id, name, query, description, created_at
211            FROM query_favorites
212            WHERE id = ?1 AND session_id = ?2
213            "#,
214        )
215        .bind(id)
216        .bind(session_id)
217        .fetch_one(&self.pool)
218        .await
219        .map_err(|e| match e {
220            sqlx::Error::RowNotFound => WebError::NotFound("Favorite not found".to_string()),
221            _ => WebError::Storage(format!("Failed to get favorite: {}", e)),
222        })?;
223
224        Ok(item)
225    }
226
227    async fn update_favorite(
228        &self,
229        id: &str,
230        session_id: &str,
231        item: &FavoriteItem,
232    ) -> WebResult<()> {
233        let result = sqlx::query(
234            r#"
235            UPDATE query_favorites
236            SET name = ?1, query = ?2, description = ?3, updated_at = ?4
237            WHERE id = ?5 AND session_id = ?6
238            "#,
239        )
240        .bind(&item.name)
241        .bind(&item.query)
242        .bind(&item.description)
243        .bind(Utc::now())
244        .bind(id)
245        .bind(session_id)
246        .execute(&self.pool)
247        .await
248        .map_err(|e| WebError::Storage(format!("Failed to update favorite: {}", e)))?;
249
250        if result.rows_affected() == 0 {
251            return Err(WebError::NotFound("Favorite not found".to_string()));
252        }
253
254        Ok(())
255    }
256
257    async fn delete_favorite(&self, id: &str, session_id: &str) -> WebResult<()> {
258        let result = sqlx::query("DELETE FROM query_favorites WHERE id = ?1 AND session_id = ?2")
259            .bind(id)
260            .bind(session_id)
261            .execute(&self.pool)
262            .await
263            .map_err(|e| WebError::Storage(format!("Failed to delete favorite: {}", e)))?;
264
265        if result.rows_affected() == 0 {
266            return Err(WebError::NotFound("Favorite not found".to_string()));
267        }
268
269        Ok(())
270    }
271
272    async fn delete_all_favorites(&self, session_id: &str) -> WebResult<()> {
273        sqlx::query("DELETE FROM query_favorites WHERE session_id = ?1")
274            .bind(session_id)
275            .execute(&self.pool)
276            .await
277            .map_err(|e| WebError::Storage(format!("Failed to delete all favorites: {}", e)))?;
278
279        Ok(())
280    }
281}
282
283// SQLx row mapping for HistoryItem
284impl sqlx::FromRow<'_, sqlx::sqlite::SqliteRow> for HistoryItem {
285    fn from_row(row: &sqlx::sqlite::SqliteRow) -> Result<Self, sqlx::Error> {
286        Ok(HistoryItem {
287            id: row.try_get("id")?,
288            session_id: row.try_get("session_id")?,
289            query: row.try_get("query")?,
290            executed_at: row.try_get("executed_at")?,
291            execution_time_ms: row.try_get("execution_time_ms")?,
292            rows_returned: row.try_get("rows_returned")?,
293            success: row.try_get("success")?,
294            error_message: row.try_get("error_message")?,
295        })
296    }
297}
298
299// SQLx row mapping for FavoriteItem
300impl sqlx::FromRow<'_, sqlx::sqlite::SqliteRow> for FavoriteItem {
301    fn from_row(row: &sqlx::sqlite::SqliteRow) -> Result<Self, sqlx::Error> {
302        Ok(FavoriteItem {
303            id: row.try_get("id")?,
304            session_id: row.try_get("session_id")?,
305            name: row.try_get("name")?,
306            query: row.try_get("query")?,
307            description: row.try_get("description")?,
308            created_at: row.try_get("created_at")?,
309        })
310    }
311}
linkrs_server/web/storage/sqlite.rs

linkrs_server/web/storage/
sqlite.rs
