shaperail_runtime/db/
query.rs

1use serde::{Deserialize, Serialize};
2use shaperail_core::{DatabaseEngine, FieldSchema, FieldType, ResourceDefinition, ShaperailError};
3use sqlx::postgres::PgRow;
4use sqlx::{PgPool, Row};
5
6use super::filter::FilterSet;
7use super::pagination::{decode_cursor, encode_cursor, PageRequest};
8use super::search::SearchParam;
9use super::sort::SortParam;
10
11/// A single row returned from a resource query, represented as a JSON object.
12#[derive(Debug, Clone, Serialize, Deserialize)]
13pub struct ResourceRow(pub serde_json::Value);
14
15/// Dynamic query executor for a resource defined by a `ResourceDefinition`.
16///
17/// Generates and executes parameterized SQL queries against a PgPool,
18/// returning results as `ResourceRow` (JSON objects).
19///
20/// # SQL injection safety
21///
22/// All user-controllable input (filter values, search term, cursor, pagination
23/// offset/limit, insert/update body) is passed only as bound parameters via
24/// `BindValue` and `query.bind()`. Table and column names in the SQL string
25/// come solely from `ResourceDefinition` (trusted schema). Filter/sort field
26/// names are allow-listed (FilterSet::from_query_params, SortParam::parse).
27/// See db_integration tests `test_sql_injection_*` for verification.
28pub struct ResourceQuery<'a> {
29    pub resource: &'a ResourceDefinition,
30    pub pool: &'a PgPool,
31}
32
33impl<'a> ResourceQuery<'a> {
34    pub fn new(resource: &'a ResourceDefinition, pool: &'a PgPool) -> Self {
35        Self { resource, pool }
36    }
37
38    /// Returns the table name (same as resource name).
39    fn table(&self) -> &str {
40        &self.resource.resource
41    }
42
43    /// Returns all column names from the schema.
44    fn columns(&self) -> Vec<&str> {
45        self.resource.schema.keys().map(|k| k.as_str()).collect()
46    }
47
48    /// Returns the primary key field name.
49    fn primary_key(&self) -> &str {
50        self.resource
51            .schema
52            .iter()
53            .find(|(_, fs)| fs.primary)
54            .map(|(name, _)| name.as_str())
55            .unwrap_or("id")
56    }
57
58    /// Builds a SELECT column list.
59    fn select_columns(&self) -> String {
60        self.columns()
61            .iter()
62            .map(|c| format!("\"{c}\""))
63            .collect::<Vec<_>>()
64            .join(", ")
65    }
66
67    /// Converts a `PgRow` to a `serde_json::Value` object based on schema field types.
68    fn row_to_json(&self, row: &PgRow) -> Result<serde_json::Value, ShaperailError> {
69        let mut obj = serde_json::Map::new();
70        for (name, field) in &self.resource.schema {
71            let value = extract_column_value(row, name, field)?;
72            obj.insert(name.clone(), value);
73        }
74        Ok(serde_json::Value::Object(obj))
75    }
76
77    /// Returns `true` if any endpoint on this resource has `soft_delete: true`.
78    fn has_soft_delete(&self) -> bool {
79        self.resource
80            .endpoints
81            .as_ref()
82            .map(|eps| eps.values().any(|ep| ep.soft_delete))
83            .unwrap_or(false)
84    }
85
86    // -- Query methods --
87
88    /// Find a single record by its primary key.
89    pub async fn find_by_id(&self, id: &uuid::Uuid) -> Result<ResourceRow, ShaperailError> {
90        let pk = self.primary_key();
91        let soft_delete_clause = if self.has_soft_delete() {
92            " AND \"deleted_at\" IS NULL"
93        } else {
94            ""
95        };
96        let sql = format!(
97            "SELECT {} FROM \"{}\" WHERE \"{}\" = $1{soft_delete_clause}",
98            self.select_columns(),
99            self.table(),
100            pk,
101        );
102
103        let row = sqlx::query(&sql)
104            .bind(id)
105            .fetch_optional(self.pool)
106            .await?
107            .ok_or(ShaperailError::NotFound)?;
108
109        let json = self.row_to_json(&row)?;
110        Ok(ResourceRow(json))
111    }
112
113    /// Find all records with filtering, searching, sorting, and pagination.
114    ///
115    /// Returns `(rows, cursor_page)` for cursor pagination or `(rows, offset_page)` for offset.
116    pub async fn find_all(
117        &self,
118        filters: &FilterSet,
119        search: Option<&SearchParam>,
120        sort: &SortParam,
121        page: &PageRequest,
122    ) -> Result<(Vec<ResourceRow>, serde_json::Value), ShaperailError> {
123        let mut sql = format!("SELECT {} FROM \"{}\"", self.select_columns(), self.table());
124        let mut has_where = false;
125        let mut param_offset: usize = 1;
126        let mut bind_values: Vec<BindValue> = Vec::new();
127
128        // Exclude soft-deleted rows
129        if self.has_soft_delete() {
130            sql.push_str(" WHERE \"deleted_at\" IS NULL");
131            has_where = true;
132        }
133
134        // Apply filters
135        if !filters.is_empty() {
136            param_offset = filters.apply_to_sql(&mut sql, has_where, param_offset);
137            has_where = true;
138            for f in &filters.filters {
139                bind_values.push(self.coerce_filter_value(&f.field, &f.value));
140            }
141        }
142
143        // Apply search
144        if let Some(sp) = search {
145            param_offset = sp.apply_to_sql(&mut sql, has_where, param_offset);
146            has_where = true;
147            bind_values.push(BindValue::Text(sp.term.clone()));
148        }
149
150        // Apply cursor/offset pagination
151        match page {
152            PageRequest::Cursor { after, limit } => {
153                let decoded_cursor = if let Some(cursor_str) = after {
154                    let id_str = decode_cursor(cursor_str)?;
155                    let id = uuid::Uuid::parse_str(&id_str).map_err(|_| {
156                        ShaperailError::Validation(vec![shaperail_core::FieldError {
157                            field: "cursor".to_string(),
158                            message: "Invalid cursor value".to_string(),
159                            code: "invalid_cursor".to_string(),
160                        }])
161                    })?;
162                    Some(id)
163                } else {
164                    None
165                };
166
167                if decoded_cursor.is_some() {
168                    if has_where {
169                        sql.push_str(" AND ");
170                    } else {
171                        sql.push_str(" WHERE ");
172                    }
173                    sql.push_str(&format!("\"id\" > ${param_offset}"));
174                    bind_values.push(BindValue::Uuid(decoded_cursor.unwrap_or_default()));
175                }
176
177                // Apply sort or default to id ASC for cursor pagination
178                if sort.is_empty() {
179                    sql.push_str(" ORDER BY \"id\" ASC");
180                } else {
181                    sort.apply_to_sql(&mut sql);
182                }
183                sql.push_str(&format!(" LIMIT {}", limit + 1));
184
185                let rows = self.execute_query(&sql, &bind_values).await?;
186
187                let has_more = rows.len() as i64 > *limit;
188                let result_rows: Vec<ResourceRow> =
189                    rows.into_iter().take(*limit as usize).collect();
190
191                let cursor = if has_more {
192                    result_rows
193                        .last()
194                        .and_then(|r| r.0.get("id"))
195                        .and_then(|v| v.as_str())
196                        .map(encode_cursor)
197                } else {
198                    None
199                };
200
201                let meta = serde_json::json!({
202                    "cursor": cursor,
203                    "has_more": has_more,
204                });
205                Ok((result_rows, meta))
206            }
207            PageRequest::Offset { offset, limit } => {
208                // For offset pagination, get total count
209                let mut count_sql = format!("SELECT COUNT(*) FROM \"{}\"", self.table());
210                let mut count_has_where = false;
211                let mut count_offset: usize = 1;
212                let mut count_binds: Vec<BindValue> = Vec::new();
213
214                // Exclude soft-deleted rows
215                if self.has_soft_delete() {
216                    count_sql.push_str(" WHERE \"deleted_at\" IS NULL");
217                    count_has_where = true;
218                }
219
220                if !filters.is_empty() {
221                    count_offset =
222                        filters.apply_to_sql(&mut count_sql, count_has_where, count_offset);
223                    count_has_where = true;
224                    for f in &filters.filters {
225                        count_binds.push(self.coerce_filter_value(&f.field, &f.value));
226                    }
227                }
228                if let Some(sp) = search {
229                    sp.apply_to_sql(&mut count_sql, count_has_where, count_offset);
230                    count_binds.push(BindValue::Text(sp.term.clone()));
231                }
232
233                let total = self.execute_count(&count_sql, &count_binds).await?;
234
235                // Apply sort
236                if !sort.is_empty() {
237                    sort.apply_to_sql(&mut sql);
238                }
239                sql.push_str(&format!(" LIMIT {limit} OFFSET {offset}"));
240
241                let rows = self.execute_query(&sql, &bind_values).await?;
242
243                let meta = serde_json::json!({
244                    "offset": offset,
245                    "limit": limit,
246                    "total": total,
247                });
248                Ok((rows, meta))
249            }
250        }
251    }
252
253    /// Insert a new record. Returns the inserted row.
254    pub async fn insert(
255        &self,
256        data: &serde_json::Map<String, serde_json::Value>,
257    ) -> Result<ResourceRow, ShaperailError> {
258        let mut columns = Vec::new();
259        let mut placeholders = Vec::new();
260        let mut bind_values = Vec::new();
261        let mut idx = 1usize;
262
263        // Add generated fields
264        for (name, field) in &self.resource.schema {
265            if field.generated {
266                match field.field_type {
267                    FieldType::Uuid => {
268                        columns.push(format!("\"{name}\""));
269                        placeholders.push(format!("${idx}"));
270                        bind_values.push(BindValue::Uuid(uuid::Uuid::new_v4()));
271                        idx += 1;
272                    }
273                    FieldType::Timestamp => {
274                        columns.push(format!("\"{name}\""));
275                        placeholders.push(format!("${idx}"));
276                        bind_values.push(BindValue::Timestamp(chrono::Utc::now()));
277                        idx += 1;
278                    }
279                    _ => {}
280                }
281                continue;
282            }
283
284            if let Some(value) = data.get(name) {
285                columns.push(format!("\"{name}\""));
286                placeholders.push(format!("${idx}"));
287                bind_values.push(json_to_bind(value, field));
288                idx += 1;
289            } else if let Some(default) = &field.default {
290                columns.push(format!("\"{name}\""));
291                placeholders.push(format!("${idx}"));
292                bind_values.push(json_to_bind(default, field));
293                idx += 1;
294            }
295        }
296
297        let sql = format!(
298            "INSERT INTO \"{}\" ({}) VALUES ({}) RETURNING {}",
299            self.table(),
300            columns.join(", "),
301            placeholders.join(", "),
302            self.select_columns(),
303        );
304
305        let rows = self.execute_query(&sql, &bind_values).await?;
306        rows.into_iter()
307            .next()
308            .ok_or_else(|| ShaperailError::Internal("Insert returned no rows".to_string()))
309    }
310
311    /// Update a record by primary key. Returns the updated row.
312    pub async fn update_by_id(
313        &self,
314        id: &uuid::Uuid,
315        data: &serde_json::Map<String, serde_json::Value>,
316    ) -> Result<ResourceRow, ShaperailError> {
317        let mut set_clauses = Vec::new();
318        let mut bind_values = Vec::new();
319        let mut idx = 1usize;
320
321        for (name, value) in data {
322            if let Some(field) = self.resource.schema.get(name) {
323                if field.primary || field.generated {
324                    continue;
325                }
326                set_clauses.push(format!("\"{name}\" = ${idx}"));
327                bind_values.push(json_to_bind(value, field));
328                idx += 1;
329            }
330        }
331
332        // Auto-update updated_at if it exists and is generated
333        if let Some(field) = self.resource.schema.get("updated_at") {
334            if field.generated && field.field_type == FieldType::Timestamp {
335                set_clauses.push(format!("\"updated_at\" = ${idx}"));
336                bind_values.push(BindValue::Timestamp(chrono::Utc::now()));
337                idx += 1;
338            }
339        }
340
341        if set_clauses.is_empty() {
342            return Err(ShaperailError::Validation(vec![
343                shaperail_core::FieldError {
344                    field: "body".to_string(),
345                    message: "No valid fields to update".to_string(),
346                    code: "empty_update".to_string(),
347                },
348            ]));
349        }
350
351        let pk = self.primary_key();
352        let soft_delete_clause = if self.has_soft_delete() {
353            " AND \"deleted_at\" IS NULL"
354        } else {
355            ""
356        };
357        let sql = format!(
358            "UPDATE \"{}\" SET {} WHERE \"{}\" = ${}{soft_delete_clause} RETURNING {}",
359            self.table(),
360            set_clauses.join(", "),
361            pk,
362            idx,
363            self.select_columns(),
364        );
365        bind_values.push(BindValue::Uuid(*id));
366
367        let rows = self.execute_query(&sql, &bind_values).await?;
368        rows.into_iter().next().ok_or(ShaperailError::NotFound)
369    }
370
371    /// Soft-delete a record by setting `deleted_at` to now.
372    pub async fn soft_delete_by_id(&self, id: &uuid::Uuid) -> Result<ResourceRow, ShaperailError> {
373        let pk = self.primary_key();
374        let sql = format!(
375            "UPDATE \"{}\" SET \"deleted_at\" = $1 WHERE \"{}\" = $2 AND \"deleted_at\" IS NULL RETURNING {}",
376            self.table(),
377            pk,
378            self.select_columns(),
379        );
380
381        let bind_values = vec![
382            BindValue::Timestamp(chrono::Utc::now()),
383            BindValue::Uuid(*id),
384        ];
385
386        let rows = self.execute_query(&sql, &bind_values).await?;
387        rows.into_iter().next().ok_or(ShaperailError::NotFound)
388    }
389
390    /// Hard-delete a record permanently.
391    pub async fn hard_delete_by_id(&self, id: &uuid::Uuid) -> Result<ResourceRow, ShaperailError> {
392        let pk = self.primary_key();
393        let sql = format!(
394            "DELETE FROM \"{}\" WHERE \"{}\" = $1 RETURNING {}",
395            self.table(),
396            pk,
397            self.select_columns(),
398        );
399
400        let bind_values = vec![BindValue::Uuid(*id)];
401        let rows = self.execute_query(&sql, &bind_values).await?;
402        rows.into_iter().next().ok_or(ShaperailError::NotFound)
403    }
404
405    // -- Internal helpers --
406
407    /// Coerces a filter string value to the correct `BindValue` based on the field's schema type.
408    fn coerce_filter_value(&self, field_name: &str, value: &str) -> BindValue {
409        if let Some(field) = self.resource.schema.get(field_name) {
410            match field.field_type {
411                FieldType::Uuid => {
412                    if let Ok(u) = uuid::Uuid::parse_str(value) {
413                        return BindValue::Uuid(u);
414                    }
415                }
416                FieldType::Integer => {
417                    if let Ok(n) = value.parse::<i32>() {
418                        return BindValue::Int(n);
419                    }
420                }
421                FieldType::Bigint => {
422                    if let Ok(n) = value.parse::<i64>() {
423                        return BindValue::Bigint(n);
424                    }
425                }
426                FieldType::Number => {
427                    if let Ok(n) = value.parse::<f64>() {
428                        return BindValue::Float(n);
429                    }
430                }
431                FieldType::Boolean => {
432                    if let Ok(b) = value.parse::<bool>() {
433                        return BindValue::Bool(b);
434                    }
435                }
436                _ => {}
437            }
438        }
439        BindValue::Text(value.to_string())
440    }
441
442    async fn execute_query(
443        &self,
444        sql: &str,
445        binds: &[BindValue],
446    ) -> Result<Vec<ResourceRow>, ShaperailError> {
447        let span = crate::observability::telemetry::db_span("query", self.table(), sql);
448        let _enter = span.enter();
449        let start = std::time::Instant::now();
450
451        // Dynamic queries use sqlx::query() with bind params (not string interpolation).
452        // The query_as! macro requires compile-time SQL; generated code (M04+) will use it.
453        let mut query = sqlx::query(sql);
454        for bind in binds {
455            query = match bind {
456                BindValue::Text(v) => query.bind(v),
457                BindValue::Int(v) => query.bind(v),
458                BindValue::Bigint(v) => query.bind(v),
459                BindValue::Float(v) => query.bind(v),
460                BindValue::Bool(v) => query.bind(v),
461                BindValue::Uuid(v) => query.bind(v),
462                BindValue::Timestamp(v) => query.bind(v),
463                BindValue::Date(v) => query.bind(v),
464                BindValue::Json(v) => query.bind(v),
465                BindValue::Null => query.bind(None::<String>),
466            };
467        }
468
469        let pg_rows = query.fetch_all(self.pool).await?;
470        let duration_ms = start.elapsed().as_millis() as u64;
471        log_slow_query(sql, duration_ms);
472
473        let mut results = Vec::with_capacity(pg_rows.len());
474        for row in &pg_rows {
475            results.push(ResourceRow(self.row_to_json(row)?));
476        }
477        Ok(results)
478    }
479
480    async fn execute_count(&self, sql: &str, binds: &[BindValue]) -> Result<i64, ShaperailError> {
481        let span = crate::observability::telemetry::db_span("count", self.table(), sql);
482        let _enter = span.enter();
483        let start = std::time::Instant::now();
484
485        let mut query = sqlx::query_scalar::<_, i64>(sql);
486        for bind in binds {
487            query = match bind {
488                BindValue::Text(v) => query.bind(v),
489                BindValue::Int(v) => query.bind(v),
490                BindValue::Bigint(v) => query.bind(v),
491                BindValue::Float(v) => query.bind(v),
492                BindValue::Bool(v) => query.bind(v),
493                BindValue::Uuid(v) => query.bind(v),
494                BindValue::Timestamp(v) => query.bind(v),
495                BindValue::Date(v) => query.bind(v),
496                BindValue::Json(v) => query.bind(v),
497                BindValue::Null => query.bind(None::<String>),
498            };
499        }
500        let count = query.fetch_one(self.pool).await?;
501        let duration_ms = start.elapsed().as_millis() as u64;
502        log_slow_query(sql, duration_ms);
503
504        Ok(count)
505    }
506}
507
508/// Internal enum for type-safe query parameter binding.
509#[derive(Debug, Clone)]
510enum BindValue {
511    Text(String),
512    Int(i32),
513    Bigint(i64),
514    Float(f64),
515    Bool(bool),
516    Uuid(uuid::Uuid),
517    Timestamp(chrono::DateTime<chrono::Utc>),
518    Date(chrono::NaiveDate),
519    Json(serde_json::Value),
520    Null,
521}
522
523/// Converts a JSON value to the appropriate `BindValue` based on the field schema.
524fn json_to_bind(value: &serde_json::Value, field: &FieldSchema) -> BindValue {
525    if value.is_null() {
526        return BindValue::Null;
527    }
528    match field.field_type {
529        FieldType::Uuid => {
530            if let Some(s) = value.as_str() {
531                if let Ok(u) = uuid::Uuid::parse_str(s) {
532                    return BindValue::Uuid(u);
533                }
534            }
535            BindValue::Text(value.to_string().trim_matches('"').to_string())
536        }
537        FieldType::String | FieldType::Enum | FieldType::File => {
538            BindValue::Text(value.as_str().unwrap_or(&value.to_string()).to_string())
539        }
540        FieldType::Integer => BindValue::Int(value.as_i64().unwrap_or(0) as i32),
541        FieldType::Bigint => BindValue::Bigint(value.as_i64().unwrap_or(0)),
542        FieldType::Number => BindValue::Float(value.as_f64().unwrap_or(0.0)),
543        FieldType::Boolean => BindValue::Bool(value.as_bool().unwrap_or(false)),
544        FieldType::Timestamp => {
545            if let Some(s) = value.as_str() {
546                if let Ok(dt) = s.parse::<chrono::DateTime<chrono::Utc>>() {
547                    return BindValue::Timestamp(dt);
548                }
549            }
550            BindValue::Timestamp(chrono::Utc::now())
551        }
552        FieldType::Date => {
553            if let Some(s) = value.as_str() {
554                if let Ok(d) = s.parse::<chrono::NaiveDate>() {
555                    return BindValue::Date(d);
556                }
557            }
558            BindValue::Date(chrono::Utc::now().date_naive())
559        }
560        FieldType::Json | FieldType::Array => BindValue::Json(value.clone()),
561    }
562}
563
564/// Extracts a column value from a `PgRow` as a `serde_json::Value`.
565fn extract_column_value(
566    row: &PgRow,
567    name: &str,
568    field: &FieldSchema,
569) -> Result<serde_json::Value, ShaperailError> {
570    // Try to get the column; if it doesn't exist, return null
571    let map_err = |e: sqlx::Error| ShaperailError::Internal(format!("Column '{name}' error: {e}"));
572
573    match field.field_type {
574        FieldType::Uuid => {
575            let v: Option<uuid::Uuid> = row.try_get(name).map_err(map_err)?;
576            Ok(v.map(|u| serde_json::Value::String(u.to_string()))
577                .unwrap_or(serde_json::Value::Null))
578        }
579        FieldType::String | FieldType::Enum | FieldType::File => {
580            let v: Option<String> = row.try_get(name).map_err(map_err)?;
581            Ok(v.map(serde_json::Value::String)
582                .unwrap_or(serde_json::Value::Null))
583        }
584        FieldType::Integer => {
585            let v: Option<i32> = row.try_get(name).map_err(map_err)?;
586            Ok(v.map(|n| serde_json::Value::Number(n.into()))
587                .unwrap_or(serde_json::Value::Null))
588        }
589        FieldType::Bigint => {
590            let v: Option<i64> = row.try_get(name).map_err(map_err)?;
591            Ok(v.map(|n| serde_json::Value::Number(n.into()))
592                .unwrap_or(serde_json::Value::Null))
593        }
594        FieldType::Number => {
595            let v: Option<f64> = row.try_get(name).map_err(map_err)?;
596            Ok(
597                v.and_then(|n| serde_json::Number::from_f64(n).map(serde_json::Value::Number))
598                    .unwrap_or(serde_json::Value::Null),
599            )
600        }
601        FieldType::Boolean => {
602            let v: Option<bool> = row.try_get(name).map_err(map_err)?;
603            Ok(v.map(serde_json::Value::Bool)
604                .unwrap_or(serde_json::Value::Null))
605        }
606        FieldType::Timestamp => {
607            let v: Option<chrono::DateTime<chrono::Utc>> = row.try_get(name).map_err(map_err)?;
608            Ok(v.map(|dt| serde_json::Value::String(dt.to_rfc3339()))
609                .unwrap_or(serde_json::Value::Null))
610        }
611        FieldType::Date => {
612            let v: Option<chrono::NaiveDate> = row.try_get(name).map_err(map_err)?;
613            Ok(v.map(|d| serde_json::Value::String(d.to_string()))
614                .unwrap_or(serde_json::Value::Null))
615        }
616        FieldType::Json | FieldType::Array => {
617            let v: Option<serde_json::Value> = row.try_get(name).map_err(map_err)?;
618            Ok(v.unwrap_or(serde_json::Value::Null))
619        }
620    }
621}
622
623/// Logs a warning if a query exceeds the slow query threshold.
624///
625/// The threshold is read from `SHAPERAIL_SLOW_QUERY_MS` env var (default: 100ms).
626fn log_slow_query(sql: &str, duration_ms: u64) {
627    let threshold: u64 = std::env::var("SHAPERAIL_SLOW_QUERY_MS")
628        .ok()
629        .and_then(|v| v.parse().ok())
630        .unwrap_or(100);
631
632    if duration_ms >= threshold {
633        tracing::warn!(
634            duration_ms = duration_ms,
635            sql = %sql,
636            threshold_ms = threshold,
637            "Slow query detected"
638        );
639    }
640}
641
642/// Builds a SQL `CREATE TABLE` statement for the given engine (M14 multi-DB).
643///
644/// Returns engine-specific SQL (Postgres, MySQL, or SQLite). MongoDB is not supported for CREATE TABLE.
645pub fn build_create_table_sql_for_engine(
646    engine: DatabaseEngine,
647    resource: &ResourceDefinition,
648) -> String {
649    match engine {
650        DatabaseEngine::Postgres => build_create_table_sql_postgres(resource),
651        DatabaseEngine::MySQL => build_create_table_sql_mysql(resource),
652        DatabaseEngine::SQLite => build_create_table_sql_sqlite(resource),
653        DatabaseEngine::MongoDB => {
654            // MongoDB uses collections, not CREATE TABLE
655            String::new()
656        }
657    }
658}
659
660/// Builds a SQL `CREATE TABLE` statement from a `ResourceDefinition` (Postgres).
661///
662/// Used by the migration generator to produce initial table creation SQL.
663pub fn build_create_table_sql(resource: &ResourceDefinition) -> String {
664    build_create_table_sql_postgres(resource)
665}
666
667fn build_create_table_sql_postgres(resource: &ResourceDefinition) -> String {
668    let mut columns = Vec::new();
669    let mut constraints = Vec::new();
670    let has_soft_delete = resource
671        .endpoints
672        .as_ref()
673        .map(|eps| eps.values().any(|ep| ep.soft_delete))
674        .unwrap_or(false);
675
676    for (name, field) in &resource.schema {
677        let mut col = format!(
678            "\"{}\" {}",
679            name,
680            field_type_to_sql(&field.field_type, field)
681        );
682
683        if field.primary {
684            col.push_str(" PRIMARY KEY");
685        }
686        if field.required && !field.primary && !field.nullable {
687            col.push_str(" NOT NULL");
688        }
689        if field.unique && !field.primary {
690            col.push_str(" UNIQUE");
691        }
692        if let Some(default) = &field.default {
693            col.push_str(&format!(" DEFAULT {}", sql_default_value(default, field)));
694        }
695        if field.field_type == FieldType::Uuid && field.generated {
696            col.push_str(" DEFAULT gen_random_uuid()");
697        }
698        if field.field_type == FieldType::Timestamp && field.generated {
699            col.push_str(" DEFAULT NOW()");
700        }
701        if field.field_type == FieldType::Date && field.generated {
702            col.push_str(" DEFAULT CURRENT_DATE");
703        }
704
705        // Enum CHECK constraint
706        if field.field_type == FieldType::Enum {
707            if let Some(values) = &field.values {
708                let vals = values
709                    .iter()
710                    .map(|v| format!("'{v}'"))
711                    .collect::<Vec<_>>()
712                    .join(", ");
713                constraints.push(format!(
714                    "CONSTRAINT \"chk_{table}_{name}\" CHECK (\"{name}\" IN ({vals}))",
715                    table = resource.resource,
716                ));
717            }
718        }
719
720        // Foreign key constraint
721        if let Some(reference) = &field.reference {
722            if let Some((ref_table, ref_col)) = reference.split_once('.') {
723                constraints.push(format!(
724                    "CONSTRAINT \"fk_{table}_{name}\" FOREIGN KEY (\"{name}\") REFERENCES \"{ref_table}\"(\"{ref_col}\")",
725                    table = resource.resource,
726                ));
727            }
728        }
729
730        columns.push(col);
731    }
732
733    if has_soft_delete && !resource.schema.contains_key("deleted_at") {
734        columns.push("\"deleted_at\" TIMESTAMPTZ".to_string());
735    }
736
737    let mut sql = format!(
738        "CREATE TABLE IF NOT EXISTS \"{}\" (\n  {}",
739        resource.resource,
740        columns.join(",\n  ")
741    );
742
743    if !constraints.is_empty() {
744        sql.push_str(",\n  ");
745        sql.push_str(&constraints.join(",\n  "));
746    }
747    sql.push_str("\n)");
748
749    // Add indexes
750    if let Some(indexes) = &resource.indexes {
751        for (i, idx) in indexes.iter().enumerate() {
752            let idx_cols = idx
753                .fields
754                .iter()
755                .map(|f| format!("\"{f}\""))
756                .collect::<Vec<_>>()
757                .join(", ");
758            let unique = if idx.unique { "UNIQUE " } else { "" };
759            let order = idx
760                .order
761                .as_deref()
762                .map(|o| format!(" {}", o.to_uppercase()))
763                .unwrap_or_default();
764            sql.push_str(&format!(
765                ";\nCREATE {unique}INDEX IF NOT EXISTS \"idx_{}_{i}\" ON \"{}\" ({idx_cols}{order})",
766                resource.resource, resource.resource,
767            ));
768        }
769    }
770
771    sql
772}
773
774fn build_create_table_sql_mysql(resource: &ResourceDefinition) -> String {
775    let q = |s: &str| format!("`{s}`");
776    let mut columns = Vec::new();
777    let mut constraints = Vec::new();
778    let has_soft_delete = resource
779        .endpoints
780        .as_ref()
781        .map(|eps| eps.values().any(|ep| ep.soft_delete))
782        .unwrap_or(false);
783
784    for (name, field) in &resource.schema {
785        let mut col = format!(
786            "{} {}",
787            q(name),
788            field_type_to_sql_mysql(&field.field_type, field)
789        );
790        if field.primary {
791            col.push_str(" PRIMARY KEY");
792        }
793        if field.required && !field.primary && !field.nullable {
794            col.push_str(" NOT NULL");
795        }
796        if field.unique && !field.primary {
797            col.push_str(" UNIQUE");
798        }
799        if let Some(default) = &field.default {
800            col.push_str(&format!(" DEFAULT {}", sql_default_value(default, field)));
801        }
802        if field.field_type == FieldType::Uuid && field.generated {
803            col.push_str(" DEFAULT (UUID())");
804        }
805        if field.field_type == FieldType::Timestamp && field.generated {
806            col.push_str(" DEFAULT (CURRENT_TIMESTAMP)");
807        }
808        if field.field_type == FieldType::Date && field.generated {
809            col.push_str(" DEFAULT (CURDATE())");
810        }
811        if field.field_type == FieldType::Enum {
812            if let Some(values) = &field.values {
813                let vals = values
814                    .iter()
815                    .map(|v| format!("'{v}'"))
816                    .collect::<Vec<_>>()
817                    .join(", ");
818                constraints.push(format!(
819                    "CONSTRAINT chk_{}_{} CHECK ({} IN ({vals}))",
820                    resource.resource,
821                    name,
822                    q(name),
823                ));
824            }
825        }
826        if let Some(reference) = &field.reference {
827            if let Some((ref_table, ref_col)) = reference.split_once('.') {
828                constraints.push(format!(
829                    "CONSTRAINT fk_{}_{} FOREIGN KEY ({}) REFERENCES {}({})",
830                    resource.resource,
831                    name,
832                    q(name),
833                    q(ref_table),
834                    q(ref_col),
835                ));
836            }
837        }
838        columns.push(col);
839    }
840    if has_soft_delete && !resource.schema.contains_key("deleted_at") {
841        columns.push(format!("{} DATETIME", q("deleted_at")));
842    }
843    let mut sql = format!(
844        "CREATE TABLE IF NOT EXISTS {} (\n  {}",
845        q(&resource.resource),
846        columns.join(",\n  ")
847    );
848    if !constraints.is_empty() {
849        sql.push_str(",\n  ");
850        sql.push_str(&constraints.join(",\n  "));
851    }
852    sql.push_str("\n)");
853    if let Some(indexes) = &resource.indexes {
854        for (i, idx) in indexes.iter().enumerate() {
855            let idx_cols = idx
856                .fields
857                .iter()
858                .map(|f| q(f))
859                .collect::<Vec<_>>()
860                .join(", ");
861            let unique = if idx.unique { "UNIQUE " } else { "" };
862            let order = idx
863                .order
864                .as_deref()
865                .map(|o| format!(" {}", o.to_uppercase()))
866                .unwrap_or_default();
867            sql.push_str(&format!(
868                ";\nCREATE {unique}INDEX idx_{resource}_{i} ON {tbl} ({idx_cols}{order})",
869                unique = unique,
870                resource = resource.resource,
871                i = i,
872                tbl = q(&resource.resource),
873                idx_cols = idx_cols,
874                order = order,
875            ));
876        }
877    }
878    sql
879}
880
881fn build_create_table_sql_sqlite(resource: &ResourceDefinition) -> String {
882    let q = |s: &str| format!("\"{s}\"");
883    let mut columns = Vec::new();
884    let mut constraints = Vec::new();
885    let has_soft_delete = resource
886        .endpoints
887        .as_ref()
888        .map(|eps| eps.values().any(|ep| ep.soft_delete))
889        .unwrap_or(false);
890
891    for (name, field) in &resource.schema {
892        let mut col = format!(
893            "{} {}",
894            q(name),
895            field_type_to_sql_sqlite(&field.field_type, field)
896        );
897        if field.primary {
898            col.push_str(" PRIMARY KEY");
899        }
900        if field.required && !field.primary && !field.nullable {
901            col.push_str(" NOT NULL");
902        }
903        if field.unique && !field.primary {
904            col.push_str(" UNIQUE");
905        }
906        if let Some(default) = &field.default {
907            col.push_str(&format!(" DEFAULT {}", sql_default_value(default, field)));
908        }
909        if field.field_type == FieldType::Uuid && field.generated {
910            col.push_str(" DEFAULT (lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab',abs(random()) % 4 + 1, 1) || lower(hex(randomblob(2))) || '-' || lower(hex(randomblob(6))))");
911        }
912        if field.field_type == FieldType::Timestamp && field.generated {
913            col.push_str(" DEFAULT (datetime('now'))");
914        }
915        if field.field_type == FieldType::Date && field.generated {
916            col.push_str(" DEFAULT (date('now'))");
917        }
918        if field.field_type == FieldType::Enum {
919            if let Some(values) = &field.values {
920                let vals = values
921                    .iter()
922                    .map(|v| format!("'{v}'"))
923                    .collect::<Vec<_>>()
924                    .join(", ");
925                constraints.push(format!(
926                    "CONSTRAINT chk_{}_{} CHECK ({} IN ({vals}))",
927                    resource.resource,
928                    name,
929                    q(name),
930                ));
931            }
932        }
933        if let Some(reference) = &field.reference {
934            if let Some((ref_table, ref_col)) = reference.split_once('.') {
935                constraints.push(format!(
936                    "CONSTRAINT fk_{}_{} FOREIGN KEY ({}) REFERENCES {}({})",
937                    resource.resource,
938                    name,
939                    q(name),
940                    q(ref_table),
941                    q(ref_col),
942                ));
943            }
944        }
945        columns.push(col);
946    }
947    if has_soft_delete && !resource.schema.contains_key("deleted_at") {
948        columns.push(format!("{} TEXT", q("deleted_at")));
949    }
950    let mut sql = format!(
951        "CREATE TABLE IF NOT EXISTS {} (\n  {}",
952        q(&resource.resource),
953        columns.join(",\n  ")
954    );
955    if !constraints.is_empty() {
956        sql.push_str(",\n  ");
957        sql.push_str(&constraints.join(",\n  "));
958    }
959    sql.push_str("\n)");
960    if let Some(indexes) = &resource.indexes {
961        for (i, idx) in indexes.iter().enumerate() {
962            let idx_cols = idx
963                .fields
964                .iter()
965                .map(|f| q(f))
966                .collect::<Vec<_>>()
967                .join(", ");
968            let unique = if idx.unique { "UNIQUE " } else { "" };
969            let order = idx
970                .order
971                .as_deref()
972                .map(|o| format!(" {}", o.to_uppercase()))
973                .unwrap_or_default();
974            sql.push_str(&format!(
975                ";\nCREATE {unique}INDEX IF NOT EXISTS idx_{resource}_{i} ON {tbl} ({idx_cols}{order})",
976                unique = unique,
977                resource = resource.resource,
978                i = i,
979                tbl = q(&resource.resource),
980                idx_cols = idx_cols,
981                order = order,
982            ));
983        }
984    }
985    sql
986}
987
988/// Maps a `FieldType` to its PostgreSQL SQL type string.
989fn field_type_to_sql(ft: &FieldType, field: &FieldSchema) -> String {
990    match ft {
991        FieldType::Uuid => "UUID".to_string(),
992        FieldType::String => {
993            if let Some(max) = &field.max {
994                if let Some(n) = max.as_u64() {
995                    return format!("VARCHAR({n})");
996                }
997            }
998            "TEXT".to_string()
999        }
1000        FieldType::Integer => "INTEGER".to_string(),
1001        FieldType::Bigint => "BIGINT".to_string(),
1002        FieldType::Number => "NUMERIC".to_string(),
1003        FieldType::Boolean => "BOOLEAN".to_string(),
1004        FieldType::Timestamp => "TIMESTAMPTZ".to_string(),
1005        FieldType::Date => "DATE".to_string(),
1006        FieldType::Enum => "TEXT".to_string(),
1007        FieldType::Json => "JSONB".to_string(),
1008        FieldType::Array => {
1009            if let Some(items) = &field.items {
1010                let item_sql = match items.as_str() {
1011                    "string" => "TEXT",
1012                    "integer" => "INTEGER",
1013                    "uuid" => "UUID",
1014                    _ => "TEXT",
1015                };
1016                format!("{item_sql}[]")
1017            } else {
1018                "TEXT[]".to_string()
1019            }
1020        }
1021        FieldType::File => "TEXT".to_string(),
1022    }
1023}
1024
1025fn field_type_to_sql_mysql(ft: &FieldType, field: &FieldSchema) -> String {
1026    match ft {
1027        FieldType::Uuid => "CHAR(36)".to_string(),
1028        FieldType::String => {
1029            if let Some(max) = &field.max {
1030                if let Some(n) = max.as_u64() {
1031                    return format!("VARCHAR({n})");
1032                }
1033            }
1034            "TEXT".to_string()
1035        }
1036        FieldType::Integer => "INTEGER".to_string(),
1037        FieldType::Bigint => "BIGINT".to_string(),
1038        FieldType::Number => "DECIMAL(65,30)".to_string(),
1039        FieldType::Boolean => "BOOLEAN".to_string(),
1040        FieldType::Timestamp => "DATETIME(6)".to_string(),
1041        FieldType::Date => "DATE".to_string(),
1042        FieldType::Enum => "VARCHAR(255)".to_string(),
1043        FieldType::Json => "JSON".to_string(),
1044        FieldType::Array => "JSON".to_string(),
1045        FieldType::File => "TEXT".to_string(),
1046    }
1047}
1048
1049fn field_type_to_sql_sqlite(ft: &FieldType, field: &FieldSchema) -> String {
1050    match ft {
1051        FieldType::Uuid => "TEXT".to_string(),
1052        FieldType::String => {
1053            if let Some(max) = &field.max {
1054                if let Some(n) = max.as_u64() {
1055                    return format!("VARCHAR({n})");
1056                }
1057            }
1058            "TEXT".to_string()
1059        }
1060        FieldType::Integer => "INTEGER".to_string(),
1061        FieldType::Bigint => "INTEGER".to_string(),
1062        FieldType::Number => "REAL".to_string(),
1063        FieldType::Boolean => "INTEGER".to_string(),
1064        FieldType::Timestamp => "TEXT".to_string(),
1065        FieldType::Date => "TEXT".to_string(),
1066        FieldType::Enum => "TEXT".to_string(),
1067        FieldType::Json => "TEXT".to_string(),
1068        FieldType::Array => "TEXT".to_string(),
1069        FieldType::File => "TEXT".to_string(),
1070    }
1071}
1072
1073/// Converts a JSON default value to a SQL literal.
1074fn sql_default_value(value: &serde_json::Value, _field: &FieldSchema) -> String {
1075    match value {
1076        serde_json::Value::String(s) => format!("'{s}'"),
1077        serde_json::Value::Number(n) => n.to_string(),
1078        serde_json::Value::Bool(b) => b.to_string().to_uppercase(),
1079        serde_json::Value::Null => "NULL".to_string(),
1080        other => format!("'{}'", other),
1081    }
1082}
1083
1084#[cfg(test)]
1085mod tests {
1086    use super::*;
1087    use indexmap::IndexMap;
1088    use shaperail_core::IndexSpec;
1089
1090    fn test_resource() -> ResourceDefinition {
1091        let mut schema = IndexMap::new();
1092        schema.insert(
1093            "id".to_string(),
1094            FieldSchema {
1095                field_type: FieldType::Uuid,
1096                primary: true,
1097                generated: true,
1098                required: false,
1099                unique: false,
1100                nullable: false,
1101                reference: None,
1102                min: None,
1103                max: None,
1104                format: None,
1105                values: None,
1106                default: None,
1107                sensitive: false,
1108                search: false,
1109                items: None,
1110            },
1111        );
1112        schema.insert(
1113            "email".to_string(),
1114            FieldSchema {
1115                field_type: FieldType::String,
1116                primary: false,
1117                generated: false,
1118                required: true,
1119                unique: true,
1120                nullable: false,
1121                reference: None,
1122                min: None,
1123                max: Some(serde_json::json!(255)),
1124                format: Some("email".to_string()),
1125                values: None,
1126                default: None,
1127                sensitive: false,
1128                search: true,
1129                items: None,
1130            },
1131        );
1132        schema.insert(
1133            "name".to_string(),
1134            FieldSchema {
1135                field_type: FieldType::String,
1136                primary: false,
1137                generated: false,
1138                required: true,
1139                unique: false,
1140                nullable: false,
1141                reference: None,
1142                min: Some(serde_json::json!(1)),
1143                max: Some(serde_json::json!(200)),
1144                format: None,
1145                values: None,
1146                default: None,
1147                sensitive: false,
1148                search: true,
1149                items: None,
1150            },
1151        );
1152        schema.insert(
1153            "role".to_string(),
1154            FieldSchema {
1155                field_type: FieldType::Enum,
1156                primary: false,
1157                generated: false,
1158                required: true,
1159                unique: false,
1160                nullable: false,
1161                reference: None,
1162                min: None,
1163                max: None,
1164                format: None,
1165                values: Some(vec![
1166                    "admin".to_string(),
1167                    "member".to_string(),
1168                    "viewer".to_string(),
1169                ]),
1170                default: Some(serde_json::json!("member")),
1171                sensitive: false,
1172                search: false,
1173                items: None,
1174            },
1175        );
1176        schema.insert(
1177            "org_id".to_string(),
1178            FieldSchema {
1179                field_type: FieldType::Uuid,
1180                primary: false,
1181                generated: false,
1182                required: true,
1183                unique: false,
1184                nullable: false,
1185                reference: Some("organizations.id".to_string()),
1186                min: None,
1187                max: None,
1188                format: None,
1189                values: None,
1190                default: None,
1191                sensitive: false,
1192                search: false,
1193                items: None,
1194            },
1195        );
1196        schema.insert(
1197            "created_at".to_string(),
1198            FieldSchema {
1199                field_type: FieldType::Timestamp,
1200                primary: false,
1201                generated: true,
1202                required: false,
1203                unique: false,
1204                nullable: false,
1205                reference: None,
1206                min: None,
1207                max: None,
1208                format: None,
1209                values: None,
1210                default: None,
1211                sensitive: false,
1212                search: false,
1213                items: None,
1214            },
1215        );
1216        schema.insert(
1217            "updated_at".to_string(),
1218            FieldSchema {
1219                field_type: FieldType::Timestamp,
1220                primary: false,
1221                generated: true,
1222                required: false,
1223                unique: false,
1224                nullable: false,
1225                reference: None,
1226                min: None,
1227                max: None,
1228                format: None,
1229                values: None,
1230                default: None,
1231                sensitive: false,
1232                search: false,
1233                items: None,
1234            },
1235        );
1236
1237        ResourceDefinition {
1238            resource: "users".to_string(),
1239            version: 1,
1240            db: None,
1241            tenant_key: None,
1242            schema,
1243            endpoints: None,
1244            relations: None,
1245            indexes: Some(vec![
1246                IndexSpec {
1247                    fields: vec!["org_id".to_string(), "role".to_string()],
1248                    unique: false,
1249                    order: None,
1250                },
1251                IndexSpec {
1252                    fields: vec!["created_at".to_string()],
1253                    unique: false,
1254                    order: Some("desc".to_string()),
1255                },
1256            ]),
1257        }
1258    }
1259
1260    #[test]
1261    fn create_table_sql_basic() {
1262        let resource = test_resource();
1263        let sql = build_create_table_sql(&resource);
1264
1265        assert!(sql.contains("CREATE TABLE IF NOT EXISTS \"users\""));
1266        assert!(sql.contains("\"id\" UUID PRIMARY KEY DEFAULT gen_random_uuid()"));
1267        assert!(sql.contains("\"email\" VARCHAR(255) NOT NULL UNIQUE"));
1268        assert!(sql.contains("\"name\" VARCHAR(200) NOT NULL"));
1269        assert!(sql.contains("\"role\" TEXT NOT NULL DEFAULT 'member'"));
1270        assert!(sql.contains("\"org_id\" UUID NOT NULL"));
1271        assert!(sql.contains("\"created_at\" TIMESTAMPTZ DEFAULT NOW()"));
1272        assert!(sql.contains("\"updated_at\" TIMESTAMPTZ DEFAULT NOW()"));
1273    }
1274
1275    #[test]
1276    fn create_table_sql_constraints() {
1277        let resource = test_resource();
1278        let sql = build_create_table_sql(&resource);
1279
1280        assert!(sql.contains("CONSTRAINT \"chk_users_role\" CHECK"));
1281        assert!(sql.contains("'admin', 'member', 'viewer'"));
1282        assert!(sql.contains("CONSTRAINT \"fk_users_org_id\" FOREIGN KEY"));
1283        assert!(sql.contains("REFERENCES \"organizations\"(\"id\")"));
1284    }
1285
1286    #[test]
1287    fn create_table_sql_indexes() {
1288        let resource = test_resource();
1289        let sql = build_create_table_sql(&resource);
1290
1291        assert!(sql.contains(
1292            "CREATE INDEX IF NOT EXISTS \"idx_users_0\" ON \"users\" (\"org_id\", \"role\")"
1293        ));
1294        assert!(sql.contains(
1295            "CREATE INDEX IF NOT EXISTS \"idx_users_1\" ON \"users\" (\"created_at\" DESC)"
1296        ));
1297    }
1298
1299    #[test]
1300    fn create_table_sql_for_engine_mysql() {
1301        let resource = test_resource();
1302        let sql = build_create_table_sql_for_engine(DatabaseEngine::MySQL, &resource);
1303        assert!(sql.contains("CREATE TABLE IF NOT EXISTS `users`"));
1304        assert!(sql.contains("CHAR(36)"));
1305        assert!(sql.contains("DEFAULT (UUID())"));
1306        assert!(sql.contains("DEFAULT (CURRENT_TIMESTAMP)"));
1307    }
1308
1309    #[test]
1310    fn create_table_sql_for_engine_sqlite() {
1311        let resource = test_resource();
1312        let sql = build_create_table_sql_for_engine(DatabaseEngine::SQLite, &resource);
1313        assert!(sql.contains("CREATE TABLE IF NOT EXISTS \"users\""));
1314        assert!(sql.contains("DEFAULT (datetime('now'))"));
1315    }
1316
1317    #[test]
1318    fn create_table_sql_adds_deleted_at_for_soft_delete() {
1319        let mut resource = test_resource();
1320        resource.endpoints = Some(indexmap::IndexMap::from([(
1321            "delete".to_string(),
1322            shaperail_core::EndpointSpec {
1323                method: shaperail_core::HttpMethod::Delete,
1324                path: "/users/:id".to_string(),
1325                auth: None,
1326                input: None,
1327                filters: None,
1328                search: None,
1329                pagination: None,
1330                sort: None,
1331                cache: None,
1332                controller: None,
1333                events: None,
1334                jobs: None,
1335                upload: None,
1336                soft_delete: true,
1337            },
1338        )]));
1339
1340        let sql = build_create_table_sql(&resource);
1341        assert!(sql.contains("\"deleted_at\" TIMESTAMPTZ"));
1342    }
1343
1344    #[test]
1345    fn field_type_to_sql_mapping() {
1346        let default_field = FieldSchema {
1347            field_type: FieldType::String,
1348            primary: false,
1349            generated: false,
1350            required: false,
1351            unique: false,
1352            nullable: false,
1353            reference: None,
1354            min: None,
1355            max: None,
1356            format: None,
1357            values: None,
1358            default: None,
1359            sensitive: false,
1360            search: false,
1361            items: None,
1362        };
1363
1364        assert_eq!(field_type_to_sql(&FieldType::Uuid, &default_field), "UUID");
1365        assert_eq!(
1366            field_type_to_sql(&FieldType::String, &default_field),
1367            "TEXT"
1368        );
1369        assert_eq!(
1370            field_type_to_sql(&FieldType::Integer, &default_field),
1371            "INTEGER"
1372        );
1373        assert_eq!(
1374            field_type_to_sql(&FieldType::Bigint, &default_field),
1375            "BIGINT"
1376        );
1377        assert_eq!(
1378            field_type_to_sql(&FieldType::Number, &default_field),
1379            "NUMERIC"
1380        );
1381        assert_eq!(
1382            field_type_to_sql(&FieldType::Boolean, &default_field),
1383            "BOOLEAN"
1384        );
1385        assert_eq!(
1386            field_type_to_sql(&FieldType::Timestamp, &default_field),
1387            "TIMESTAMPTZ"
1388        );
1389        assert_eq!(field_type_to_sql(&FieldType::Date, &default_field), "DATE");
1390        assert_eq!(field_type_to_sql(&FieldType::Enum, &default_field), "TEXT");
1391        assert_eq!(field_type_to_sql(&FieldType::Json, &default_field), "JSONB");
1392        assert_eq!(field_type_to_sql(&FieldType::File, &default_field), "TEXT");
1393    }
1394
1395    #[test]
1396    fn field_type_to_sql_varchar() {
1397        let field = FieldSchema {
1398            field_type: FieldType::String,
1399            primary: false,
1400            generated: false,
1401            required: false,
1402            unique: false,
1403            nullable: false,
1404            reference: None,
1405            min: None,
1406            max: Some(serde_json::json!(100)),
1407            format: None,
1408            values: None,
1409            default: None,
1410            sensitive: false,
1411            search: false,
1412            items: None,
1413        };
1414        assert_eq!(
1415            field_type_to_sql(&FieldType::String, &field),
1416            "VARCHAR(100)"
1417        );
1418    }
1419
1420    #[test]
1421    fn field_type_to_sql_array() {
1422        let field = FieldSchema {
1423            field_type: FieldType::Array,
1424            primary: false,
1425            generated: false,
1426            required: false,
1427            unique: false,
1428            nullable: false,
1429            reference: None,
1430            min: None,
1431            max: None,
1432            format: None,
1433            values: None,
1434            default: None,
1435            sensitive: false,
1436            search: false,
1437            items: Some("string".to_string()),
1438        };
1439        assert_eq!(field_type_to_sql(&FieldType::Array, &field), "TEXT[]");
1440    }
1441
1442    #[test]
1443    fn json_to_bind_types() {
1444        let str_field = FieldSchema {
1445            field_type: FieldType::String,
1446            primary: false,
1447            generated: false,
1448            required: false,
1449            unique: false,
1450            nullable: false,
1451            reference: None,
1452            min: None,
1453            max: None,
1454            format: None,
1455            values: None,
1456            default: None,
1457            sensitive: false,
1458            search: false,
1459            items: None,
1460        };
1461
1462        let bind = json_to_bind(&serde_json::json!("hello"), &str_field);
1463        assert!(matches!(bind, BindValue::Text(s) if s == "hello"));
1464
1465        let bind = json_to_bind(&serde_json::Value::Null, &str_field);
1466        assert!(matches!(bind, BindValue::Null));
1467    }
1468}
shaperail_runtime/db/query.rs

shaperail_runtime/db/
query.rs
