teaql_sql/
dialect.rs

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

teaql_sql/
dialect.rs
