teaql_sql/
dialect.rs

1use teaql_core::{
2    Aggregate, AggregateFunction, BinaryOp, DataType, DeleteCommand, EntityDescriptor, Expr,
3    ExprFunction, OrderBy, PropertyDescriptor, RecoverCommand, SelectQuery, SortDirection, Value,
4};
5
6use crate::{CompiledQuery, DatabaseKind, SqlCompileError};
7
8const SQL_KEYWORDS: &[&str] = &[
9    "all", "alter", "and", "as", "asc", "between", "by", "case", "create", "delete", "desc",
10    "distinct", "drop", "exists", "false", "from", "group", "having", "in", "insert", "into", "is",
11    "join", "like", "limit", "not", "null", "offset", "on", "or", "order", "select", "set",
12    "table", "true", "type", "union", "update", "values", "where",
13];
14
15pub fn quote_identifier_if_needed(ident: &str, quote: char) -> String {
16    if is_wrapped_identifier(ident) {
17        return ident.to_owned();
18    }
19    if needs_quoted_identifier(ident) {
20        let quote_string = quote.to_string();
21        let escaped = ident.replace(quote, &(quote_string.clone() + &quote_string));
22        return format!("{quote}{escaped}{quote}");
23    }
24    ident.to_owned()
25}
26
27fn is_wrapped_identifier(ident: &str) -> bool {
28    (ident.starts_with('"') && ident.ends_with('"'))
29        || (ident.starts_with('`') && ident.ends_with('`'))
30        || (ident.starts_with('[') && ident.ends_with(']'))
31}
32
33fn needs_quoted_identifier(ident: &str) -> bool {
34    if ident.is_empty()
35        || SQL_KEYWORDS
36            .binary_search(&ident.to_ascii_lowercase().as_str())
37            .is_ok()
38    {
39        return true;
40    }
41    let mut chars = ident.chars();
42    match chars.next() {
43        Some(first) if first == '_' || first.is_ascii_alphabetic() => {}
44        _ => return true,
45    }
46    chars.any(|ch| ch != '_' && !ch.is_ascii_alphanumeric())
47}
48
49pub trait SqlDialect {
50    fn kind(&self) -> DatabaseKind;
51    fn quote_ident(&self, ident: &str) -> String;
52    fn placeholder(&self, index: usize) -> String;
53
54    fn schema_setup_sqls(&self) -> &'static [&'static str] {
55        &[]
56    }
57
58    fn schema_type_sql(
59        &self,
60        data_type: DataType,
61        _property: &PropertyDescriptor,
62    ) -> Result<&'static str, SqlCompileError> {
63        match data_type {
64            DataType::Bool => Ok("BOOLEAN"),
65            DataType::I64 | DataType::U64 => Ok("INTEGER"),
66            DataType::F64 => Ok("REAL"),
67            DataType::Decimal => Ok("NUMERIC"),
68            DataType::Text => Ok("VARCHAR(255)"),
69            DataType::LargeText | DataType::Json | DataType::Date | DataType::Timestamp => {
70                Ok("TEXT")
71            }
72        }
73    }
74
75    fn column_definition_sql(
76        &self,
77        property: &PropertyDescriptor,
78    ) -> Result<String, SqlCompileError> {
79        let mut parts = vec![
80            self.quote_ident(&property.column_name),
81            self.schema_type_sql(property.data_type, property)?
82                .to_owned(),
83        ];
84
85        if property.is_id {
86            parts.push("PRIMARY KEY".to_owned());
87        }
88        if property.is_id || !property.nullable {
89            parts.push("NOT NULL".to_owned());
90        }
91
92        Ok(parts.join(" "))
93    }
94
95    fn compile_create_table(&self, entity: &EntityDescriptor) -> Result<String, SqlCompileError> {
96        let columns = entity
97            .properties
98            .iter()
99            .map(|property| self.column_definition_sql(property))
100            .collect::<Result<Vec<_>, _>>()?
101            .join(", ");
102        Ok(format!(
103            "CREATE TABLE IF NOT EXISTS {} ({columns})",
104            self.quote_ident(&entity.table_name)
105        ))
106    }
107
108    fn schema_indexes_sqls(
109        &self,
110        entity: &EntityDescriptor,
111    ) -> Result<Vec<String>, SqlCompileError> {
112        let mut sqls = Vec::new();
113        let table_name_upper = entity.table_name.to_uppercase();
114        let quoted_table = self.quote_ident(&entity.table_name);
115
116        if let Some(version_col) = entity.properties.iter().find(|p| p.is_version) {
117            let default_id = "id".to_string();
118            let id_col = entity
119                .properties
120                .iter()
121                .find(|p| p.is_id)
122                .map(|p| &p.column_name)
123                .unwrap_or(&default_id);
124            let idx_name = format!("PK_{}_ID_VERSION", table_name_upper);
125            sqls.push(format!(
126                "CREATE UNIQUE INDEX IF NOT EXISTS {} ON {} ({}, {})",
127                self.quote_ident(&idx_name),
128                quoted_table,
129                self.quote_ident(id_col),
130                self.quote_ident(&version_col.column_name)
131            ));
132        }
133
134        for p in &entity.properties {
135            if p.name.ends_with("Id")
136                || p.name.ends_with("Time")
137                || p.name.ends_with("_time")
138                || p.name == "create_time"
139                || p.name == "update_time"
140            {
141                let idx_name = format!("IDX_{}_{}", table_name_upper, p.column_name.to_uppercase());
142                sqls.push(format!(
143                    "CREATE INDEX IF NOT EXISTS {} ON {} ({})",
144                    self.quote_ident(&idx_name),
145                    quoted_table,
146                    self.quote_ident(&p.column_name)
147                ));
148            }
149        }
150
151        Ok(sqls)
152    }
153
154    fn fallback_default_value_sql(&self, data_type: DataType) -> &'static str {
155        match data_type {
156            DataType::Bool => "FALSE",
157            DataType::I64 | DataType::U64 | DataType::F64 | DataType::Decimal => "0",
158            DataType::Text | DataType::LargeText => "''",
159            DataType::Json => "'{}'",
160            DataType::Date => "'1970-01-01'",
161            DataType::Timestamp => "'1970-01-01 00:00:00Z'",
162        }
163    }
164
165    fn compile_add_column(
166        &self,
167        entity: &EntityDescriptor,
168        property: &PropertyDescriptor,
169    ) -> Result<String, SqlCompileError> {
170        let mut def = self.column_definition_sql(property)?;
171        if !property.nullable && !property.is_id {
172            def.push_str(" DEFAULT ");
173            def.push_str(self.fallback_default_value_sql(property.data_type));
174        }
175        Ok(format!(
176            "ALTER TABLE {} ADD COLUMN {}",
177            self.quote_ident(&entity.table_name),
178            def
179        ))
180    }
181
182    fn compile_select(
183        &self,
184        entity: &EntityDescriptor,
185        query: &SelectQuery,
186    ) -> Result<CompiledQuery, SqlCompileError> {
187        let mut params = Vec::new();
188        let sql = self.compile_select_sql(entity, query, &mut params)?;
189        Ok(CompiledQuery {
190            sql,
191            params,
192            comment: query.comment.clone(),
193        })
194    }
195
196    fn compile_select_sql(
197        &self,
198        entity: &EntityDescriptor,
199        query: &SelectQuery,
200        params: &mut Vec<Value>,
201    ) -> Result<String, SqlCompileError> {
202        if let Some(raw_sql) = &query.raw_sql {
203            return Ok(raw_sql.clone());
204        }
205
206        let projection = self.compile_projection(entity, query, params)?;
207
208        let mut sql = format!(
209            "SELECT {projection} FROM {}",
210            self.quote_ident(&entity.table_name)
211        );
212
213        let mut where_parts = Vec::new();
214        if let Some(filter) = &query.filter {
215            where_parts.push(self.compile_expr(entity, filter, params)?);
216        }
217
218        if let Some(search_text) = &query.search_with_text {
219            let mut or_parts = Vec::new();
220            let like_value = format!("%{}%", search_text);
221            for property in &entity.properties {
222                if property.data_type == teaql_core::DataType::Text
223                    || property.data_type == teaql_core::DataType::LargeText
224                {
225                    params.push(teaql_core::Value::from(like_value.clone()));
226                    or_parts.push(format!(
227                        "{} LIKE {}",
228                        self.quote_ident(&property.column_name),
229                        self.placeholder(params.len())
230                    ));
231                }
232            }
233            if !or_parts.is_empty() {
234                where_parts.push(format!("({})", or_parts.join(" OR ")));
235            }
236        }
237
238        where_parts.extend(query.raw_sql_search_criteria.iter().cloned());
239        if !where_parts.is_empty() {
240            sql.push_str(" WHERE ");
241            sql.push_str(&where_parts.join(" AND "));
242        }
243
244        if !query.group_by.is_empty() {
245            let group_by = query
246                .group_by
247                .iter()
248                .map(|field| self.column_sql(entity, field))
249                .collect::<Result<Vec<_>, _>>()?
250                .join(", ");
251            sql.push_str(" GROUP BY ");
252            sql.push_str(&group_by);
253        }
254
255        if let Some(having) = &query.having {
256            let having_sql = self.compile_expr(entity, having, params)?;
257            sql.push_str(" HAVING ");
258            sql.push_str(&having_sql);
259        }
260
261        if !query.order_by.is_empty() {
262            let order_by = query
263                .order_by
264                .iter()
265                .map(|order| self.order_by_sql(entity, order, params))
266                .collect::<Result<Vec<_>, _>>()?
267                .join(", ");
268            sql.push_str(" ORDER BY ");
269            sql.push_str(&order_by);
270        }
271
272        if let Some(slice) = query.slice {
273            if let Some(limit) = slice.limit {
274                sql.push_str(&format!(" LIMIT {limit}"));
275            }
276            if slice.offset > 0 {
277                sql.push_str(&format!(" OFFSET {}", slice.offset));
278            }
279        }
280
281        Ok(sql)
282    }
283
284    fn compile_insert(
285        &self,
286        entity: &EntityDescriptor,
287        command: &teaql_core::InsertCommand,
288    ) -> Result<CompiledQuery, SqlCompileError> {
289        let mut columns = Vec::new();
290        let mut placeholders = Vec::new();
291        let mut params = Vec::new();
292
293        for property in &entity.properties {
294            if let Some(value) = command.values.get(&property.name) {
295                columns.push(self.quote_ident(&property.column_name));
296                let mut v = value.clone();
297                if let Value::Null = v {
298                    v = Value::TypedNull(property.data_type);
299                }
300                params.push(v);
301                placeholders.push(self.placeholder(params.len()));
302            }
303        }
304
305        if columns.is_empty() {
306            return Err(SqlCompileError::EmptyMutation("insert".to_owned()));
307        }
308
309        Ok(CompiledQuery {
310            sql: format!(
311                "INSERT INTO {} ({}) VALUES ({})",
312                self.quote_ident(&entity.table_name),
313                columns.join(", "),
314                placeholders.join(", ")
315            ),
316            params,
317            comment: None,
318        })
319    }
320
321    fn compile_batch_insert(
322        &self,
323        entity: &EntityDescriptor,
324        command: &teaql_core::BatchInsertCommand,
325    ) -> Result<CompiledQuery, SqlCompileError> {
326        if command.batch_values.is_empty() {
327            return Err(SqlCompileError::EmptyMutation("batch_insert".to_owned()));
328        }
329
330        let mut columns = Vec::new();
331        let first_record = &command.batch_values[0];
332
333        for property in &entity.properties {
334            if first_record.contains_key(&property.name) {
335                columns.push(property.clone());
336            }
337        }
338
339        if columns.is_empty() {
340            return Err(SqlCompileError::EmptyMutation("batch_insert".to_owned()));
341        }
342
343        let column_names: Vec<String> = columns
344            .iter()
345            .map(|p| self.quote_ident(&p.column_name))
346            .collect();
347        let mut params = Vec::new();
348        let mut values_clauses = Vec::new();
349
350        for record in &command.batch_values {
351            let mut row_placeholders = Vec::new();
352            for property in &columns {
353                let mut value = record
354                    .get(&property.name)
355                    .cloned()
356                    .unwrap_or(teaql_core::Value::Null);
357                if let Value::Null = value {
358                    value = Value::TypedNull(property.data_type);
359                }
360                params.push(value);
361                row_placeholders.push(self.placeholder(params.len()));
362            }
363            values_clauses.push(format!("({})", row_placeholders.join(", ")));
364        }
365
366        Ok(CompiledQuery {
367            sql: format!(
368                "INSERT INTO {} ({}) VALUES {}",
369                self.quote_ident(&entity.table_name),
370                column_names.join(", "),
371                values_clauses.join(", ")
372            ),
373            params,
374            comment: None,
375        })
376    }
377
378    fn compile_update(
379        &self,
380        entity: &EntityDescriptor,
381        command: &teaql_core::UpdateCommand,
382    ) -> Result<CompiledQuery, SqlCompileError> {
383        let id_property = entity
384            .id_property()
385            .ok_or_else(|| SqlCompileError::MissingIdProperty(entity.name.clone()))?;
386        let mut assignments = Vec::new();
387        let mut params = Vec::new();
388
389        for property in &entity.properties {
390            if property.is_id {
391                continue;
392            }
393            if property.is_version && command.expected_version.is_some() {
394                continue;
395            }
396            if let Some(value) = command.values.get(&property.name) {
397                let mut v = value.clone();
398                if let Value::Null = v {
399                    v = Value::TypedNull(property.data_type);
400                }
401                params.push(v);
402                assignments.push(format!(
403                    "{} = {}",
404                    self.quote_ident(&property.column_name),
405                    self.placeholder(params.len())
406                ));
407            }
408        }
409
410        if let Some(expected_version) = command.expected_version {
411            let version_property = entity
412                .version_property()
413                .ok_or_else(|| SqlCompileError::MissingVersionProperty(entity.name.clone()))?;
414            params.push(Value::I64(expected_version + 1));
415            assignments.push(format!(
416                "{} = {}",
417                self.quote_ident(&version_property.column_name),
418                self.placeholder(params.len())
419            ));
420        }
421
422        if assignments.is_empty() {
423            return Err(SqlCompileError::EmptyMutation("update".to_owned()));
424        }
425
426        params.push(command.id.clone());
427        let mut predicates = vec![format!(
428            "{} = {}",
429            self.quote_ident(&id_property.column_name),
430            self.placeholder(params.len())
431        )];
432
433        if let Some(expected_version) = command.expected_version {
434            let version_property = entity
435                .version_property()
436                .ok_or_else(|| SqlCompileError::MissingVersionProperty(entity.name.clone()))?;
437            params.push(Value::I64(expected_version));
438            predicates.push(format!(
439                "{} = {}",
440                self.quote_ident(&version_property.column_name),
441                self.placeholder(params.len())
442            ));
443        }
444
445        Ok(CompiledQuery {
446            sql: format!(
447                "UPDATE {} SET {} WHERE {}",
448                self.quote_ident(&entity.table_name),
449                assignments.join(", "),
450                predicates.join(" AND ")
451            ),
452            params,
453            comment: None,
454        })
455    }
456
457    fn compile_batch_update(
458        &self,
459        entity: &EntityDescriptor,
460        command: &teaql_core::BatchUpdateCommand,
461    ) -> Result<CompiledQuery, SqlCompileError> {
462        if command.batch_values.is_empty() {
463            return Err(SqlCompileError::EmptyMutation("batch_update".to_owned()));
464        }
465
466        let id_property = entity
467            .id_property()
468            .ok_or_else(|| SqlCompileError::MissingIdProperty(entity.name.clone()))?;
469
470        let mut params = Vec::new();
471        let mut set_clauses = Vec::new();
472
473        // Build CASE statement for each updated field
474        for field_name in &command.update_fields {
475            let property = entity
476                .property_by_name(field_name)
477                .ok_or_else(|| SqlCompileError::UnknownField(field_name.clone()))?;
478
479            let mut case_parts = Vec::new();
480            case_parts.push(format!(
481                "CASE {}",
482                self.quote_ident(&id_property.column_name)
483            ));
484
485            for (i, record) in command.batch_values.iter().enumerate() {
486                let id = &command.batch_ids[i];
487                let mut val = record
488                    .get(field_name)
489                    .cloned()
490                    .unwrap_or(teaql_core::Value::Null);
491                if let Value::Null = val {
492                    val = Value::TypedNull(property.data_type);
493                }
494
495                params.push(id.clone());
496                let id_ph = self.placeholder(params.len());
497
498                params.push(val);
499                let val_ph = self.placeholder(params.len());
500
501                case_parts.push(format!("WHEN {} THEN {}", id_ph, val_ph));
502            }
503
504            case_parts.push(format!(
505                "ELSE {} END",
506                self.quote_ident(&property.column_name)
507            ));
508            set_clauses.push(format!(
509                "{} = {}",
510                self.quote_ident(&property.column_name),
511                case_parts.join(" ")
512            ));
513        }
514
515        let mut has_versions = false;
516        if let Some(version_property) = entity.version_property() {
517            let mut case_parts = Vec::new();
518            case_parts.push(format!(
519                "CASE {}",
520                self.quote_ident(&id_property.column_name)
521            ));
522
523            for (i, exp_ver_opt) in command.batch_expected_versions.iter().enumerate() {
524                if let Some(exp_ver) = exp_ver_opt {
525                    has_versions = true;
526                    let id = &command.batch_ids[i];
527
528                    params.push(id.clone());
529                    let id_ph = self.placeholder(params.len());
530
531                    params.push(teaql_core::Value::I64(*exp_ver + 1));
532                    let val_ph = self.placeholder(params.len());
533
534                    case_parts.push(format!("WHEN {} THEN {}", id_ph, val_ph));
535                }
536            }
537
538            if has_versions {
539                case_parts.push(format!(
540                    "ELSE {} END",
541                    self.quote_ident(&version_property.column_name)
542                ));
543                set_clauses.push(format!(
544                    "{} = {}",
545                    self.quote_ident(&version_property.column_name),
546                    case_parts.join(" ")
547                ));
548            }
549        }
550
551        if set_clauses.is_empty() {
552            return Err(SqlCompileError::EmptyMutation("batch_update".to_owned()));
553        }
554
555        let mut in_placeholders = Vec::new();
556        for id in &command.batch_ids {
557            params.push(id.clone());
558            in_placeholders.push(self.placeholder(params.len()));
559        }
560        let mut predicates = vec![format!(
561            "{} IN ({})",
562            self.quote_ident(&id_property.column_name),
563            in_placeholders.join(", ")
564        )];
565
566        if has_versions {
567            let version_property = entity.version_property().unwrap();
568            let mut case_parts = Vec::new();
569            case_parts.push(format!(
570                "CASE {}",
571                self.quote_ident(&id_property.column_name)
572            ));
573
574            for (i, exp_ver_opt) in command.batch_expected_versions.iter().enumerate() {
575                if let Some(exp_ver) = exp_ver_opt {
576                    let id = &command.batch_ids[i];
577
578                    params.push(id.clone());
579                    let id_ph = self.placeholder(params.len());
580
581                    params.push(teaql_core::Value::I64(*exp_ver));
582                    let val_ph = self.placeholder(params.len());
583
584                    case_parts.push(format!("WHEN {} THEN {}", id_ph, val_ph));
585                }
586            }
587            case_parts.push(format!(
588                "ELSE {} END",
589                self.quote_ident(&version_property.column_name)
590            ));
591
592            predicates.push(format!(
593                "{} = {}",
594                self.quote_ident(&version_property.column_name),
595                case_parts.join(" ")
596            ));
597        }
598
599        Ok(CompiledQuery {
600            sql: format!(
601                "UPDATE {} SET {} WHERE {}",
602                self.quote_ident(&entity.table_name),
603                set_clauses.join(", "),
604                predicates.join(" AND ")
605            ),
606            params,
607            comment: None,
608        })
609    }
610
611    fn compile_delete(
612        &self,
613        entity: &EntityDescriptor,
614        command: &DeleteCommand,
615    ) -> Result<CompiledQuery, SqlCompileError> {
616        let id_property = entity
617            .id_property()
618            .ok_or_else(|| SqlCompileError::MissingIdProperty(entity.name.clone()))?;
619        let mut params = Vec::new();
620
621        if command.soft_delete {
622            let version_property = entity
623                .version_property()
624                .ok_or_else(|| SqlCompileError::MissingVersionProperty(entity.name.clone()))?;
625            params.push(match command.expected_version {
626                Some(version) => Value::I64(-(version + 1)),
627                None => Value::I64(-1),
628            });
629
630            params.push(command.id.clone());
631            let mut predicates = vec![format!(
632                "{} = {}",
633                self.quote_ident(&id_property.column_name),
634                self.placeholder(params.len())
635            )];
636
637            if let Some(expected_version) = command.expected_version {
638                params.push(Value::I64(expected_version));
639                predicates.push(format!(
640                    "{} = {}",
641                    self.quote_ident(&version_property.column_name),
642                    self.placeholder(params.len())
643                ));
644            }
645
646            return Ok(CompiledQuery {
647                sql: format!(
648                    "UPDATE {} SET {} = {} WHERE {}",
649                    self.quote_ident(&entity.table_name),
650                    self.quote_ident(&version_property.column_name),
651                    self.placeholder(1),
652                    predicates.join(" AND ")
653                ),
654                params,
655                comment: None,
656            });
657        }
658
659        params.push(command.id.clone());
660        let mut predicates = vec![format!(
661            "{} = {}",
662            self.quote_ident(&id_property.column_name),
663            self.placeholder(params.len())
664        )];
665
666        if let Some(expected_version) = command.expected_version {
667            let version_property = entity
668                .version_property()
669                .ok_or_else(|| SqlCompileError::MissingVersionProperty(entity.name.clone()))?;
670            params.push(Value::I64(expected_version));
671            predicates.push(format!(
672                "{} = {}",
673                self.quote_ident(&version_property.column_name),
674                self.placeholder(params.len())
675            ));
676        }
677
678        Ok(CompiledQuery {
679            sql: format!(
680                "DELETE FROM {} WHERE {}",
681                self.quote_ident(&entity.table_name),
682                predicates.join(" AND ")
683            ),
684            params,
685            comment: None,
686        })
687    }
688
689    fn compile_recover(
690        &self,
691        entity: &EntityDescriptor,
692        command: &RecoverCommand,
693    ) -> Result<CompiledQuery, SqlCompileError> {
694        if command.expected_version >= 0 {
695            return Err(SqlCompileError::InvalidRecoverVersion(
696                command.expected_version,
697            ));
698        }
699
700        let id_property = entity
701            .id_property()
702            .ok_or_else(|| SqlCompileError::MissingIdProperty(entity.name.clone()))?;
703        let version_property = entity
704            .version_property()
705            .ok_or_else(|| SqlCompileError::MissingVersionProperty(entity.name.clone()))?;
706        let params = vec![
707            Value::I64(-command.expected_version + 1),
708            command.id.clone(),
709            Value::I64(command.expected_version),
710        ];
711
712        Ok(CompiledQuery {
713            sql: format!(
714                "UPDATE {} SET {} = {} WHERE {} = {} AND {} = {}",
715                self.quote_ident(&entity.table_name),
716                self.quote_ident(&version_property.column_name),
717                self.placeholder(1),
718                self.quote_ident(&id_property.column_name),
719                self.placeholder(2),
720                self.quote_ident(&version_property.column_name),
721                self.placeholder(3),
722            ),
723            params,
724            comment: None,
725        })
726    }
727
728    fn column_sql(
729        &self,
730        entity: &EntityDescriptor,
731        field: &str,
732    ) -> Result<String, SqlCompileError> {
733        let property = entity
734            .property_by_name(field)
735            .ok_or_else(|| SqlCompileError::UnknownField(field.to_owned()))?;
736        Ok(self.quote_ident(&property.column_name))
737    }
738
739    fn order_by_sql(
740        &self,
741        entity: &EntityDescriptor,
742        order_by: &OrderBy,
743        params: &mut Vec<Value>,
744    ) -> Result<String, SqlCompileError> {
745        let field = self.resolve_order_field(entity, order_by, params)?;
746        let direction = match order_by.direction {
747            SortDirection::Asc => "ASC",
748            SortDirection::Desc => "DESC",
749        };
750        Ok(format!("{field} {direction}"))
751    }
752
753    fn select_projection(
754        &self,
755        entity: &EntityDescriptor,
756        query: &SelectQuery,
757        params: &mut Vec<Value>,
758    ) -> Result<String, SqlCompileError> {
759        let property_projection = |property: &PropertyDescriptor| self.column_with_alias(property);
760
761        if query.projection.is_empty()
762            && query.expr_projection.is_empty()
763            && query.raw_projections.is_empty()
764            && query.dynamic_properties.is_empty()
765        {
766            return Ok(entity
767                .properties
768                .iter()
769                .map(property_projection)
770                .collect::<Vec<_>>()
771                .join(", "));
772        }
773        let mut parts = query
774            .projection
775            .iter()
776            .map(|field| {
777                let property = entity
778                    .property_by_name(field)
779                    .ok_or_else(|| SqlCompileError::UnknownField(field.to_owned()))?;
780                Ok(property_projection(property))
781            })
782            .collect::<Result<Vec<_>, _>>()?;
783        for projection in &query.expr_projection {
784            let expr = self.compile_expr(entity, &projection.expr, params)?;
785            parts.push(format!("{expr} AS {}", self.quote_ident(&projection.alias)));
786        }
787        for projection in query
788            .raw_projections
789            .iter()
790            .chain(query.dynamic_properties.iter())
791        {
792            parts.push(format!(
793                "{} AS {}",
794                projection.raw_sql_segment,
795                self.quote_ident(&projection.property_name)
796            ));
797        }
798        Ok(parts.join(", "))
799    }
800
801    fn aggregate_projection(
802        &self,
803        entity: &EntityDescriptor,
804        query: &SelectQuery,
805        params: &mut Vec<Value>,
806    ) -> Result<String, SqlCompileError> {
807        let mut parts = Vec::new();
808        for field in query.group_by.iter().chain(query.projection.iter()) {
809            let column = self.column_sql(entity, field)?;
810            if !parts.contains(&column) {
811                parts.push(column);
812            }
813        }
814        for projection in &query.expr_projection {
815            let expr = self.compile_expr(entity, &projection.expr, params)?;
816            let aliased = format!("{expr} AS {}", self.quote_ident(&projection.alias));
817            if !parts.contains(&aliased) {
818                parts.push(aliased);
819            }
820        }
821        for projection in query
822            .raw_projections
823            .iter()
824            .chain(query.dynamic_properties.iter())
825        {
826            let aliased = format!(
827                "{} AS {}",
828                projection.raw_sql_segment,
829                self.quote_ident(&projection.property_name)
830            );
831            if !parts.contains(&aliased) {
832                parts.push(aliased);
833            }
834        }
835        parts.extend(
836            query
837                .aggregates
838                .iter()
839                .map(|aggregate| {
840                    let field = self.resolve_aggregate_field(entity, aggregate)?;
841                    let call = self.aggregate_call_sql(aggregate.function, &field);
842                    Ok(format!("{call} AS {}", self.quote_ident(&aggregate.alias)))
843                })
844                .collect::<Result<Vec<_>, _>>()?,
845        );
846        Ok(parts.join(", "))
847    }
848
849    fn aggregate_call_sql(&self, function: AggregateFunction, field: &str) -> String {
850        let function_sql = self.aggregate_function_sql(function);
851        format!("{function_sql}({field})")
852    }
853
854    fn aggregate_function_sql(&self, function: AggregateFunction) -> &'static str {
855        match function {
856            AggregateFunction::Count => "COUNT",
857            AggregateFunction::Sum => "SUM",
858            AggregateFunction::Avg => "AVG",
859            AggregateFunction::Min => "MIN",
860            AggregateFunction::Max => "MAX",
861            AggregateFunction::Stddev => "STDDEV",
862            AggregateFunction::StddevPop => "STDDEV_POP",
863            AggregateFunction::VarSamp => "VAR_SAMP",
864            AggregateFunction::VarPop => "VAR_POP",
865            AggregateFunction::BitAnd => "BIT_AND",
866            AggregateFunction::BitOr => "BIT_OR",
867            AggregateFunction::BitXor => "BIT_XOR",
868        }
869    }
870
871    fn compile_expr(
872        &self,
873        entity: &EntityDescriptor,
874        expr: &Expr,
875        params: &mut Vec<Value>,
876    ) -> Result<String, SqlCompileError> {
877        match expr {
878            Expr::Column(name) => self.column_sql(entity, name),
879            Expr::Value(value) => {
880                params.push(value.clone());
881                Ok(self.placeholder(params.len()))
882            }
883            Expr::Function { function, args } => {
884                self.compile_function(entity, *function, args, params)
885            }
886            Expr::Binary { left, op, right } => {
887                if matches!(
888                    op,
889                    BinaryOp::In | BinaryOp::NotIn | BinaryOp::InLarge | BinaryOp::NotInLarge
890                ) {
891                    return self.compile_in(entity, left, *op, right, params);
892                }
893                let lhs = self.compile_expr(entity, left, params)?;
894                let rhs = self.compile_expr(entity, right, params)?;
895                let op = match op {
896                    BinaryOp::Eq => "=",
897                    BinaryOp::Ne => "!=",
898                    BinaryOp::Gt => ">",
899                    BinaryOp::Gte => ">=",
900                    BinaryOp::Lt => "<",
901                    BinaryOp::Lte => "<=",
902                    BinaryOp::Like => "LIKE",
903                    BinaryOp::NotLike => "NOT LIKE",
904                    BinaryOp::In | BinaryOp::NotIn | BinaryOp::InLarge | BinaryOp::NotInLarge => {
905                        unreachable!()
906                    }
907                };
908                Ok(format!("({lhs} {op} {rhs})"))
909            }
910            Expr::SubQuery {
911                left,
912                op,
913                entity: sub_entity,
914                query,
915            } => self.compile_subquery(entity, left, *op, sub_entity, query, params),
916            Expr::Between { expr, lower, upper } => {
917                let expr = self.compile_expr(entity, expr, params)?;
918                let lower = self.compile_expr(entity, lower, params)?;
919                let upper = self.compile_expr(entity, upper, params)?;
920                Ok(format!("({expr} BETWEEN {lower} AND {upper})"))
921            }
922            Expr::IsNull(expr) => {
923                let expr = self.compile_expr(entity, expr, params)?;
924                Ok(format!("({expr} IS NULL)"))
925            }
926            Expr::IsNotNull(expr) => {
927                let expr = self.compile_expr(entity, expr, params)?;
928                Ok(format!("({expr} IS NOT NULL)"))
929            }
930            Expr::And(parts) => self.compile_joined(entity, parts, "AND", params),
931            Expr::Or(parts) => self.compile_joined(entity, parts, "OR", params),
932            Expr::Not(expr) => {
933                let expr = self.compile_expr(entity, expr, params)?;
934                Ok(format!("(NOT {expr})"))
935            }
936        }
937    }
938
939    fn compile_function(
940        &self,
941        entity: &EntityDescriptor,
942        function: ExprFunction,
943        args: &[Expr],
944        params: &mut Vec<Value>,
945    ) -> Result<String, SqlCompileError> {
946        match function {
947            ExprFunction::Soundex => {
948                let [arg] = args else {
949                    return Err(SqlCompileError::InvalidFunctionArguments(
950                        "SOUNDEX expects exactly one argument".to_owned(),
951                    ));
952                };
953                let arg = self.compile_expr(entity, arg, params)?;
954                Ok(format!("SOUNDEX({arg})"))
955            }
956            ExprFunction::Gbk => self.compile_gbk_function(entity, args, params),
957            ExprFunction::Count if args.is_empty() => Ok("COUNT(*)".to_owned()),
958            ExprFunction::Count => self.compile_single_arg_function(entity, "COUNT", args, params),
959            ExprFunction::Sum => self.compile_single_arg_function(entity, "SUM", args, params),
960            ExprFunction::Avg => self.compile_single_arg_function(entity, "AVG", args, params),
961            ExprFunction::Min => self.compile_single_arg_function(entity, "MIN", args, params),
962            ExprFunction::Max => self.compile_single_arg_function(entity, "MAX", args, params),
963            ExprFunction::Stddev => {
964                self.compile_single_arg_function(entity, "STDDEV", args, params)
965            }
966            ExprFunction::StddevPop => {
967                self.compile_single_arg_function(entity, "STDDEV_POP", args, params)
968            }
969            ExprFunction::VarSamp => {
970                self.compile_single_arg_function(entity, "VAR_SAMP", args, params)
971            }
972            ExprFunction::VarPop => {
973                self.compile_single_arg_function(entity, "VAR_POP", args, params)
974            }
975            ExprFunction::BitAnd => {
976                self.compile_single_arg_function(entity, "BIT_AND", args, params)
977            }
978            ExprFunction::BitOr => self.compile_single_arg_function(entity, "BIT_OR", args, params),
979            ExprFunction::BitXor => {
980                self.compile_single_arg_function(entity, "BIT_XOR", args, params)
981            }
982        }
983    }
984
985    fn compile_single_arg_function(
986        &self,
987        entity: &EntityDescriptor,
988        function: &str,
989        args: &[Expr],
990        params: &mut Vec<Value>,
991    ) -> Result<String, SqlCompileError> {
992        let [arg] = args else {
993            return Err(SqlCompileError::InvalidFunctionArguments(format!(
994                "{function} expects exactly one argument"
995            )));
996        };
997        let arg = self.compile_expr(entity, arg, params)?;
998        Ok(format!("{function}({arg})"))
999    }
1000
1001    /// Compile a GBK sort expression. The default implementation returns an error
1002    /// because GBK encoding conversion is dialect-specific. PostgreSQL dialects
1003    /// should override this to use `convert_to(arg, 'GBK')`.
1004    fn compile_gbk_function(
1005        &self,
1006        entity: &EntityDescriptor,
1007        args: &[Expr],
1008        params: &mut Vec<Value>,
1009    ) -> Result<String, SqlCompileError> {
1010        let [arg] = args else {
1011            return Err(SqlCompileError::InvalidFunctionArguments(
1012                "GBK expects exactly one argument".to_owned(),
1013            ));
1014        };
1015        // Default: pass through the column as-is (no GBK conversion).
1016        // Dialects with GBK support (e.g. PostgreSQL) should override this method.
1017        let arg = self.compile_expr(entity, arg, params)?;
1018        Ok(arg)
1019    }
1020
1021    fn compile_subquery(
1022        &self,
1023        entity: &EntityDescriptor,
1024        left: &Expr,
1025        op: BinaryOp,
1026        sub_entity: &EntityDescriptor,
1027        query: &SelectQuery,
1028        params: &mut Vec<Value>,
1029    ) -> Result<String, SqlCompileError> {
1030        let lhs = self.compile_expr(entity, left, params)?;
1031        let operator = match op {
1032            BinaryOp::In | BinaryOp::InLarge => "IN",
1033            BinaryOp::NotIn | BinaryOp::NotInLarge => "NOT IN",
1034            _ => return Err(SqlCompileError::InvalidSubQueryOperator(format!("{op:?}"))),
1035        };
1036        let subquery = self.compile_select_sql(sub_entity, query, params)?;
1037        Ok(format!("({lhs} {operator} ({subquery}))"))
1038    }
1039
1040    fn compile_joined(
1041        &self,
1042        entity: &EntityDescriptor,
1043        parts: &[Expr],
1044        joiner: &str,
1045        params: &mut Vec<Value>,
1046    ) -> Result<String, SqlCompileError> {
1047        let compiled = parts
1048            .iter()
1049            .map(|part| self.compile_expr(entity, part, params))
1050            .collect::<Result<Vec<_>, _>>()?;
1051        Ok(format!("({})", compiled.join(&format!(" {joiner} "))))
1052    }
1053
1054    fn compile_in(
1055        &self,
1056        entity: &EntityDescriptor,
1057        left: &Expr,
1058        op: BinaryOp,
1059        right: &Expr,
1060        params: &mut Vec<Value>,
1061    ) -> Result<String, SqlCompileError> {
1062        let lhs = self.compile_expr(entity, left, params)?;
1063        let operator = match op {
1064            BinaryOp::In | BinaryOp::InLarge => "IN",
1065            BinaryOp::NotIn | BinaryOp::NotInLarge => "NOT IN",
1066            _ => unreachable!(),
1067        };
1068        match right {
1069            Expr::Value(Value::List(values)) => {
1070                if values.is_empty() {
1071                    return Err(SqlCompileError::EmptyInList);
1072                }
1073                let mut placeholders = Vec::with_capacity(values.len());
1074                for value in values {
1075                    params.push(value.clone());
1076                    placeholders.push(self.placeholder(params.len()));
1077                }
1078                Ok(format!("({lhs} {operator} ({}))", placeholders.join(", ")))
1079            }
1080            _ => {
1081                let rhs = self.compile_expr(entity, right, params)?;
1082                Ok(format!("({lhs} {operator} ({rhs}))"))
1083            }
1084        }
1085    }
1086
1087    fn compile_projection(
1088        &self,
1089        entity: &EntityDescriptor,
1090        query: &SelectQuery,
1091        params: &mut Vec<Value>,
1092    ) -> Result<String, SqlCompileError> {
1093        match query.aggregates.is_empty() {
1094            true => self.select_projection(entity, query, params),
1095            false => self.aggregate_projection(entity, query, params),
1096        }
1097    }
1098
1099    fn resolve_order_field(
1100        &self,
1101        entity: &EntityDescriptor,
1102        order_by: &OrderBy,
1103        params: &mut Vec<Value>,
1104    ) -> Result<String, SqlCompileError> {
1105        match &order_by.expr {
1106            Some(expr) => self.compile_expr(entity, expr, params),
1107            None => self.column_sql(entity, &order_by.field),
1108        }
1109    }
1110
1111    fn column_with_alias(&self, property: &PropertyDescriptor) -> String {
1112        let column = self.quote_ident(&property.column_name);
1113        match property.column_name == property.name {
1114            true => column,
1115            false => format!("{column} AS {}", self.quote_ident(&property.name)),
1116        }
1117    }
1118
1119    fn resolve_aggregate_field(
1120        &self,
1121        entity: &EntityDescriptor,
1122        aggregate: &Aggregate,
1123    ) -> Result<String, SqlCompileError> {
1124        match aggregate.function == AggregateFunction::Count && aggregate.field == "*" {
1125            true => Ok("*".to_owned()),
1126            false => self.column_sql(entity, &aggregate.field),
1127        }
1128    }
1129}
1130
1131#[cfg(test)]
1132mod tests {
1133    use super::*;
1134    use crate::DatabaseKind;
1135    use teaql_core::{DataType, EntityDescriptor, InsertCommand, PropertyDescriptor, Value};
1136
1137    struct TestDialect;
1138    impl crate::SqlDialect for TestDialect {
1139        fn kind(&self) -> DatabaseKind {
1140            DatabaseKind::PostgreSql
1141        }
1142        fn quote_ident(&self, ident: &str) -> String {
1143            ident.to_owned()
1144        }
1145        fn placeholder(&self, index: usize) -> String {
1146            format!("${index}")
1147        }
1148        fn schema_type_sql(
1149            &self,
1150            _data_type: DataType,
1151            _property: &PropertyDescriptor,
1152        ) -> Result<&'static str, SqlCompileError> {
1153            Ok("TEST")
1154        }
1155    }
1156
1157    #[test]
1158    fn test_regression_issue_56_typed_null_conversion() {
1159        let dialect = TestDialect;
1160        let mut entity = EntityDescriptor::new("User");
1161        entity
1162            .properties
1163            .push(PropertyDescriptor::new("paid_at", DataType::Timestamp));
1164
1165        let mut command = InsertCommand::new("User");
1166        command = command.value("paid_at", Value::Null);
1167
1168        let query = dialect.compile_insert(&entity, &command).unwrap();
1169        // The value should be converted to TypedNull(Timestamp)
1170        assert_eq!(query.params.len(), 1);
1171        assert_eq!(query.params[0], Value::TypedNull(DataType::Timestamp));
1172    }
1173}
teaql_sql/dialect.rs

teaql_sql/
dialect.rs
