stateset_db/sqlite/
custom_objects.rs

1//! SQLite custom objects repository implementation
2
3use super::{map_db_error, parse_datetime_row, parse_json_row, parse_uuid_row};
4use chrono::Utc;
5use r2d2::Pool;
6use r2d2_sqlite::SqliteConnectionManager;
7use rusqlite::OptionalExtension;
8use stateset_core::{
9    CommerceError, CreateCustomObject, CreateCustomObjectType, CustomFieldDefinition, CustomObject,
10    CustomObjectFilter, CustomObjectRepository, CustomObjectType, CustomObjectTypeFilter, Result,
11    UpdateCustomObject, UpdateCustomObjectType, validate_custom_object_type_input,
12    validate_required_text, validate_sku,
13};
14use uuid::Uuid;
15
16/// SQLite implementation of `CustomObjectRepository`
17#[derive(Debug)]
18pub struct SqliteCustomObjectRepository {
19    pool: Pool<SqliteConnectionManager>,
20}
21
22impl SqliteCustomObjectRepository {
23    #[must_use]
24    pub const fn new(pool: Pool<SqliteConnectionManager>) -> Self {
25        Self { pool }
26    }
27
28    fn conn(&self) -> Result<r2d2::PooledConnection<SqliteConnectionManager>> {
29        self.pool.get().map_err(|e| CommerceError::DatabaseError(e.to_string()))
30    }
31
32    fn row_to_type(row: &rusqlite::Row<'_>) -> rusqlite::Result<CustomObjectType> {
33        let fields_json: String = row.get("fields_json")?;
34        let fields: Vec<CustomFieldDefinition> =
35            parse_json_row(&fields_json, "custom_object_type", "fields_json")?;
36
37        Ok(CustomObjectType {
38            id: parse_uuid_row(&row.get::<_, String>("id")?, "custom_object_type", "id")?,
39            handle: row.get("handle")?,
40            display_name: row.get("display_name")?,
41            description: row.get("description")?,
42            fields,
43            created_at: parse_datetime_row(
44                &row.get::<_, String>("created_at")?,
45                "custom_object_type",
46                "created_at",
47            )?,
48            updated_at: parse_datetime_row(
49                &row.get::<_, String>("updated_at")?,
50                "custom_object_type",
51                "updated_at",
52            )?,
53            version: row.get::<_, Option<i32>>("version")?.unwrap_or(1),
54        })
55    }
56
57    fn row_to_object(row: &rusqlite::Row<'_>) -> rusqlite::Result<CustomObject> {
58        let values_json: String = row.get("values_json")?;
59        let values: serde_json::Value =
60            parse_json_row(&values_json, "custom_object", "values_json")?;
61
62        Ok(CustomObject {
63            id: parse_uuid_row(&row.get::<_, String>("id")?, "custom_object", "id")?,
64            type_id: parse_uuid_row(&row.get::<_, String>("type_id")?, "custom_object", "type_id")?,
65            type_handle: row.get("type_handle")?,
66            handle: row.get::<_, Option<String>>("handle")?,
67            owner_type: row.get::<_, Option<String>>("owner_type")?,
68            owner_id: row.get::<_, Option<String>>("owner_id")?,
69            values,
70            created_at: parse_datetime_row(
71                &row.get::<_, String>("created_at")?,
72                "custom_object",
73                "created_at",
74            )?,
75            updated_at: parse_datetime_row(
76                &row.get::<_, String>("updated_at")?,
77                "custom_object",
78                "updated_at",
79            )?,
80            version: row.get::<_, Option<i32>>("version")?.unwrap_or(1),
81        })
82    }
83
84    fn get_type_by_handle_conn(
85        conn: &rusqlite::Connection,
86        handle: &str,
87    ) -> Result<Option<CustomObjectType>> {
88        let mut stmt = conn
89            .prepare(
90                "SELECT id, handle, display_name, description, fields_json, created_at, updated_at, version
91                 FROM custom_object_types
92                 WHERE handle = ?",
93            )
94            .map_err(map_db_error)?;
95        let ty = stmt.query_row([handle], Self::row_to_type).optional().map_err(map_db_error)?;
96        Ok(ty)
97    }
98
99    fn validate_owner_pair(owner_type: &Option<String>, owner_id: &Option<String>) -> Result<()> {
100        match (owner_type.as_deref(), owner_id.as_deref()) {
101            (Some(_), Some(_)) => Ok(()),
102            (None, None) => Ok(()),
103            _ => Err(CommerceError::InvalidInput {
104                field: "custom_object.owner".into(),
105                message: "owner_type and owner_id must be provided together".into(),
106            }),
107        }
108    }
109}
110
111impl CustomObjectRepository for SqliteCustomObjectRepository {
112    // ------------------------------------------------------------------------
113    // Types
114    // ------------------------------------------------------------------------
115
116    fn create_type(&self, input: CreateCustomObjectType) -> Result<CustomObjectType> {
117        validate_custom_object_type_input(&input)?;
118
119        let mut conn = self.conn()?;
120        let tx = super::begin_immediate(&mut conn).map_err(map_db_error)?;
121
122        let exists: i32 = tx
123            .query_row(
124                "SELECT COUNT(*) FROM custom_object_types WHERE handle = ?",
125                [&input.handle],
126                |row| row.get(0),
127            )
128            .map_err(map_db_error)?;
129        if exists > 0 {
130            return Err(CommerceError::Conflict(format!(
131                "custom_object_type.handle already exists: {}",
132                input.handle
133            )));
134        }
135
136        let id = Uuid::new_v4();
137        let now = Utc::now();
138        let description = input.description.clone().unwrap_or_default();
139        let fields_json = serde_json::to_string(&input.fields).map_err(|e| {
140            CommerceError::DatabaseError(format!(
141                "Failed to serialize custom_object_type.fields: {e}"
142            ))
143        })?;
144
145        tx.execute(
146            "INSERT INTO custom_object_types (id, handle, display_name, description, fields_json, created_at, updated_at, version)
147             VALUES (?, ?, ?, ?, ?, ?, ?, 1)",
148            rusqlite::params![
149                id.to_string(),
150                &input.handle,
151                &input.display_name,
152                &description,
153                &fields_json,
154                now.to_rfc3339(),
155                now.to_rfc3339()
156            ],
157        )
158        .map_err(map_db_error)?;
159
160        tx.commit().map_err(map_db_error)?;
161
162        Ok(CustomObjectType {
163            id,
164            handle: input.handle,
165            display_name: input.display_name,
166            description,
167            fields: input.fields,
168            created_at: now,
169            updated_at: now,
170            version: 1,
171        })
172    }
173
174    fn get_type(&self, id: Uuid) -> Result<Option<CustomObjectType>> {
175        let conn = self.conn()?;
176        let mut stmt = conn
177            .prepare(
178                "SELECT id, handle, display_name, description, fields_json, created_at, updated_at, version
179                 FROM custom_object_types
180                 WHERE id = ?",
181            )
182            .map_err(map_db_error)?;
183        let ty =
184            stmt.query_row([id.to_string()], Self::row_to_type).optional().map_err(map_db_error)?;
185        Ok(ty)
186    }
187
188    fn get_type_by_handle(&self, handle: &str) -> Result<Option<CustomObjectType>> {
189        let conn = self.conn()?;
190        Self::get_type_by_handle_conn(&conn, handle)
191    }
192
193    fn update_type(&self, id: Uuid, input: UpdateCustomObjectType) -> Result<CustomObjectType> {
194        let mut conn = self.conn()?;
195        let tx = super::begin_immediate(&mut conn).map_err(map_db_error)?;
196
197        let existing: Option<(i32, String, String, String, String)> = tx
198            .query_row(
199                "SELECT version, handle, display_name, description, fields_json
200                 FROM custom_object_types
201                 WHERE id = ?",
202                [id.to_string()],
203                |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?, row.get(3)?, row.get(4)?)),
204            )
205            .optional()
206            .map_err(map_db_error)?;
207
208        let (current_version, _handle, current_display_name, current_description, current_fields) =
209            match existing {
210                Some(v) => v,
211                None => return Err(CommerceError::NotFound),
212            };
213
214        let next_display_name =
215            input.display_name.clone().unwrap_or_else(|| current_display_name.clone());
216        validate_required_text("custom_object_type.display_name", &next_display_name, 128)?;
217
218        let next_description =
219            input.description.clone().unwrap_or_else(|| current_description.clone());
220
221        let next_fields_json = if let Some(fields) = input.fields {
222            // Validate keys uniqueness and format.
223            let mut keys = std::collections::HashSet::new();
224            for f in &fields {
225                f.validate()?;
226                if !keys.insert(f.key.clone()) {
227                    return Err(CommerceError::InvalidInput {
228                        field: "custom_object_type.fields".into(),
229                        message: format!("duplicate field key: {}", f.key),
230                    });
231                }
232            }
233            serde_json::to_string(&fields).map_err(|e| {
234                CommerceError::DatabaseError(format!(
235                    "Failed to serialize custom_object_type.fields: {e}"
236                ))
237            })?
238        } else {
239            current_fields
240        };
241
242        let now = Utc::now();
243        let updated = tx
244            .execute(
245                "UPDATE custom_object_types
246                 SET display_name = ?, description = ?, fields_json = ?, updated_at = ?, version = version + 1
247                 WHERE id = ? AND version = ?",
248                rusqlite::params![
249                    &next_display_name,
250                    &next_description,
251                    &next_fields_json,
252                    now.to_rfc3339(),
253                    id.to_string(),
254                    current_version
255                ],
256            )
257            .map_err(map_db_error)?;
258
259        if updated == 0 {
260            return Err(CommerceError::VersionConflict {
261                entity: "custom_object_type".into(),
262                id: id.to_string(),
263                expected_version: current_version,
264            });
265        }
266
267        tx.commit().map_err(map_db_error)?;
268
269        // Re-read for authoritative values.
270        self.get_type(id)?.ok_or(CommerceError::NotFound)
271    }
272
273    fn list_types(&self, filter: CustomObjectTypeFilter) -> Result<Vec<CustomObjectType>> {
274        let conn = self.conn()?;
275        let mut sql =
276            "SELECT id, handle, display_name, description, fields_json, created_at, updated_at, version FROM custom_object_types"
277                .to_string();
278        let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
279
280        if let Some(search) = filter.search.as_ref().map(|s| s.trim()).filter(|s| !s.is_empty()) {
281            sql.push_str(" WHERE handle LIKE ? OR display_name LIKE ?");
282            let pat = format!("%{search}%");
283            params.push(Box::new(pat.clone()));
284            params.push(Box::new(pat));
285        }
286
287        sql.push_str(" ORDER BY handle ASC");
288
289        let limit = i64::from(filter.limit.unwrap_or(100).min(1000));
290        let offset = i64::from(filter.offset.unwrap_or(0));
291        sql.push_str(" LIMIT ? OFFSET ?");
292        params.push(Box::new(limit));
293        params.push(Box::new(offset));
294
295        let mut stmt = conn.prepare(&sql).map_err(map_db_error)?;
296        let param_refs = params.iter().map(std::convert::AsRef::as_ref);
297        let rows = stmt
298            .query_map(rusqlite::params_from_iter(param_refs), Self::row_to_type)
299            .map_err(map_db_error)?;
300
301        rows.collect::<std::result::Result<Vec<_>, _>>().map_err(map_db_error)
302    }
303
304    fn delete_type(&self, id: Uuid) -> Result<()> {
305        let conn = self.conn()?;
306        let deleted = conn
307            .execute("DELETE FROM custom_object_types WHERE id = ?", [id.to_string()])
308            .map_err(map_db_error)?;
309        if deleted == 0 {
310            return Err(CommerceError::NotFound);
311        }
312        Ok(())
313    }
314
315    // ------------------------------------------------------------------------
316    // Records
317    // ------------------------------------------------------------------------
318
319    fn create_object(&self, input: CreateCustomObject) -> Result<CustomObject> {
320        validate_required_text("custom_object.type_handle", &input.type_handle, 100)?;
321        if let Some(handle) = input.handle.as_deref() {
322            validate_sku(handle)?;
323        }
324        Self::validate_owner_pair(&input.owner_type, &input.owner_id)?;
325
326        let mut conn = self.conn()?;
327        let tx = super::begin_immediate(&mut conn).map_err(map_db_error)?;
328
329        let ty: CustomObjectType = tx
330            .query_row(
331                "SELECT id, handle, display_name, description, fields_json, created_at, updated_at, version
332                 FROM custom_object_types
333                 WHERE handle = ?",
334                [&input.type_handle],
335                Self::row_to_type,
336            )
337            .optional()
338            .map_err(map_db_error)?
339            .ok_or(CommerceError::NotFound)?;
340
341        ty.validate_values(&input.values)?;
342
343        if let Some(handle) = input.handle.as_deref() {
344            let exists: i32 = tx
345                .query_row(
346                    "SELECT COUNT(*) FROM custom_object_records WHERE type_id = ? AND handle = ?",
347                    rusqlite::params![ty.id.to_string(), handle],
348                    |row| row.get(0),
349                )
350                .map_err(map_db_error)?;
351            if exists > 0 {
352                return Err(CommerceError::Conflict(format!(
353                    "custom_object.handle already exists for type {}: {}",
354                    ty.handle, handle
355                )));
356            }
357        }
358
359        let id = Uuid::new_v4();
360        let now = Utc::now();
361        let values_json = serde_json::to_string(&input.values).map_err(|e| {
362            CommerceError::DatabaseError(format!("Failed to serialize custom_object.values: {e}"))
363        })?;
364
365        tx.execute(
366            "INSERT INTO custom_object_records (id, type_id, handle, owner_type, owner_id, values_json, created_at, updated_at, version)
367             VALUES (?, ?, ?, ?, ?, ?, ?, ?, 1)",
368            rusqlite::params![
369                id.to_string(),
370                ty.id.to_string(),
371                input.handle.as_deref(),
372                input.owner_type.as_deref(),
373                input.owner_id.as_deref(),
374                values_json,
375                now.to_rfc3339(),
376                now.to_rfc3339()
377            ],
378        )
379        .map_err(map_db_error)?;
380
381        tx.commit().map_err(map_db_error)?;
382
383        Ok(CustomObject {
384            id,
385            type_id: ty.id,
386            type_handle: ty.handle,
387            handle: input.handle,
388            owner_type: input.owner_type,
389            owner_id: input.owner_id,
390            values: input.values,
391            created_at: now,
392            updated_at: now,
393            version: 1,
394        })
395    }
396
397    fn get_object(&self, id: Uuid) -> Result<Option<CustomObject>> {
398        let conn = self.conn()?;
399        let mut stmt = conn
400            .prepare(
401                "SELECT r.id, r.type_id, t.handle AS type_handle, r.handle, r.owner_type, r.owner_id,
402                        r.values_json, r.created_at, r.updated_at, r.version
403                 FROM custom_object_records r
404                 JOIN custom_object_types t ON t.id = r.type_id
405                 WHERE r.id = ?",
406            )
407            .map_err(map_db_error)?;
408        let obj = stmt
409            .query_row([id.to_string()], Self::row_to_object)
410            .optional()
411            .map_err(map_db_error)?;
412        Ok(obj)
413    }
414
415    fn get_object_by_handle(
416        &self,
417        type_handle: &str,
418        object_handle: &str,
419    ) -> Result<Option<CustomObject>> {
420        let conn = self.conn()?;
421        let mut stmt = conn
422            .prepare(
423                "SELECT r.id, r.type_id, t.handle AS type_handle, r.handle, r.owner_type, r.owner_id,
424                        r.values_json, r.created_at, r.updated_at, r.version
425                 FROM custom_object_records r
426                 JOIN custom_object_types t ON t.id = r.type_id
427                 WHERE t.handle = ? AND r.handle = ?",
428            )
429            .map_err(map_db_error)?;
430        let obj = stmt
431            .query_row(rusqlite::params![type_handle, object_handle], Self::row_to_object)
432            .optional()
433            .map_err(map_db_error)?;
434        Ok(obj)
435    }
436
437    fn update_object(&self, id: Uuid, input: UpdateCustomObject) -> Result<CustomObject> {
438        if let Some(handle) = input.handle.as_deref() {
439            validate_sku(handle)?;
440        }
441        if input.owner_type.is_some() || input.owner_id.is_some() {
442            // This update requires both to be present (we don't support partial mutation here).
443            Self::validate_owner_pair(&input.owner_type, &input.owner_id)?;
444        }
445
446        let mut conn = self.conn()?;
447        let tx = super::begin_immediate(&mut conn).map_err(map_db_error)?;
448
449        type ExistingObjectRow =
450            (String, String, Option<String>, Option<String>, Option<String>, String, i32);
451        let existing: Option<ExistingObjectRow> = tx
452            .query_row(
453                "SELECT r.type_id, t.handle AS type_handle, r.handle, r.owner_type, r.owner_id, r.values_json, r.version
454                 FROM custom_object_records r
455                 JOIN custom_object_types t ON t.id = r.type_id
456                 WHERE r.id = ?",
457                [id.to_string()],
458                |row| {
459                    Ok((
460                        row.get(0)?,
461                        row.get(1)?,
462                        row.get(2)?,
463                        row.get(3)?,
464                        row.get(4)?,
465                        row.get(5)?,
466                        row.get(6)?,
467                    ))
468                },
469            )
470            .optional()
471            .map_err(map_db_error)?;
472
473        let (
474            type_id_raw,
475            type_handle,
476            current_handle,
477            current_owner_type,
478            current_owner_id,
479            current_values_json,
480            current_version,
481        ) = match existing {
482            Some(v) => v,
483            None => return Err(CommerceError::NotFound),
484        };
485
486        // Load type for validation.
487        let ty: CustomObjectType = tx
488            .query_row(
489                "SELECT id, handle, display_name, description, fields_json, created_at, updated_at, version
490                 FROM custom_object_types
491                 WHERE id = ?",
492                [&type_id_raw],
493                Self::row_to_type,
494            )
495            .optional()
496            .map_err(map_db_error)?
497            .ok_or(CommerceError::NotFound)?;
498
499        let next_handle = input.handle.clone().or(current_handle.clone());
500        if let Some(h) = next_handle.as_deref() {
501            if current_handle.as_deref() != Some(h) {
502                let exists: i32 = tx
503                    .query_row(
504                        "SELECT COUNT(*) FROM custom_object_records WHERE type_id = ? AND handle = ? AND id != ?",
505                        rusqlite::params![type_id_raw, h, id.to_string()],
506                        |row| row.get(0),
507                    )
508                    .map_err(map_db_error)?;
509                if exists > 0 {
510                    return Err(CommerceError::Conflict(format!(
511                        "custom_object.handle already exists for type {type_handle}: {h}"
512                    )));
513                }
514            }
515        }
516
517        let next_owner_type = input.owner_type.or(current_owner_type);
518        let next_owner_id = input.owner_id.or(current_owner_id);
519
520        Self::validate_owner_pair(&next_owner_type, &next_owner_id)?;
521
522        let next_values = if let Some(values) = input.values {
523            ty.validate_values(&values)?;
524            serde_json::to_string(&values).map_err(|e| {
525                CommerceError::DatabaseError(format!(
526                    "Failed to serialize custom_object.values: {e}"
527                ))
528            })?
529        } else {
530            current_values_json
531        };
532
533        let now = Utc::now();
534        let updated_rows = tx
535            .execute(
536                "UPDATE custom_object_records
537                 SET handle = ?, owner_type = ?, owner_id = ?, values_json = ?, updated_at = ?, version = version + 1
538                 WHERE id = ? AND version = ?",
539                rusqlite::params![
540                    next_handle.as_deref(),
541                    next_owner_type.as_deref(),
542                    next_owner_id.as_deref(),
543                    next_values,
544                    now.to_rfc3339(),
545                    id.to_string(),
546                    current_version
547                ],
548            )
549            .map_err(map_db_error)?;
550
551        if updated_rows == 0 {
552            return Err(CommerceError::VersionConflict {
553                entity: "custom_object".into(),
554                id: id.to_string(),
555                expected_version: current_version,
556            });
557        }
558
559        tx.commit().map_err(map_db_error)?;
560
561        self.get_object(id)?.ok_or(CommerceError::NotFound)
562    }
563
564    fn list_objects(&self, filter: CustomObjectFilter) -> Result<Vec<CustomObject>> {
565        if filter.owner_type.is_some() || filter.owner_id.is_some() {
566            Self::validate_owner_pair(&filter.owner_type, &filter.owner_id)?;
567        }
568
569        let conn = self.conn()?;
570        let mut sql = String::from(
571            "SELECT r.id, r.type_id, t.handle AS type_handle, r.handle, r.owner_type, r.owner_id,
572                    r.values_json, r.created_at, r.updated_at, r.version
573             FROM custom_object_records r
574             JOIN custom_object_types t ON t.id = r.type_id",
575        );
576
577        let mut where_parts: Vec<String> = Vec::new();
578        let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
579
580        if let Some(type_handle) =
581            filter.type_handle.as_ref().map(|s| s.trim()).filter(|s| !s.is_empty())
582        {
583            where_parts.push("t.handle = ?".into());
584            params.push(Box::new(type_handle.to_string()));
585        }
586
587        if let Some(handle) = filter.handle.as_ref().map(|s| s.trim()).filter(|s| !s.is_empty()) {
588            where_parts.push("r.handle = ?".into());
589            params.push(Box::new(handle.to_string()));
590        }
591
592        if let (Some(owner_type), Some(owner_id)) = (&filter.owner_type, &filter.owner_id) {
593            where_parts.push("r.owner_type = ? AND r.owner_id = ?".into());
594            params.push(Box::new(owner_type.clone()));
595            params.push(Box::new(owner_id.clone()));
596        }
597
598        if !where_parts.is_empty() {
599            sql.push_str(" WHERE ");
600            sql.push_str(&where_parts.join(" AND "));
601        }
602
603        sql.push_str(" ORDER BY r.created_at DESC");
604
605        let limit = i64::from(filter.limit.unwrap_or(100).min(1000));
606        let offset = i64::from(filter.offset.unwrap_or(0));
607        sql.push_str(" LIMIT ? OFFSET ?");
608        params.push(Box::new(limit));
609        params.push(Box::new(offset));
610
611        let mut stmt = conn.prepare(&sql).map_err(map_db_error)?;
612        let param_refs = params.iter().map(std::convert::AsRef::as_ref);
613        let rows = stmt
614            .query_map(rusqlite::params_from_iter(param_refs), Self::row_to_object)
615            .map_err(map_db_error)?;
616
617        rows.collect::<std::result::Result<Vec<_>, _>>().map_err(map_db_error)
618    }
619
620    fn delete_object(&self, id: Uuid) -> Result<()> {
621        let conn = self.conn()?;
622        let deleted = conn
623            .execute("DELETE FROM custom_object_records WHERE id = ?", [id.to_string()])
624            .map_err(map_db_error)?;
625        if deleted == 0 {
626            return Err(CommerceError::NotFound);
627        }
628        Ok(())
629    }
630}
stateset_db/sqlite/custom_objects.rs

stateset_db/sqlite/
custom_objects.rs
