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

teaql_sql/
dialect.rs
