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

teaql_sql/
dialect.rs
