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

teaql_sql/
dialect.rs
