Skip to main content

openauth_core/db/sql/
dialect.rs

1use super::*;
2
3impl SqlDialect {
4    pub fn quote_identifier(self, identifier: &str) -> Result<String, OpenAuthError> {
5        validate_identifier(self, identifier)?;
6        let quote = match self {
7            Self::MySql => '`',
8            Self::Postgres | Self::Sqlite => '"',
9        };
10        Ok(format!("{quote}{identifier}{quote}"))
11    }
12
13    pub fn sanitize_identifier(self, identifier: &str) -> Result<String, OpenAuthError> {
14        let sanitized = identifier
15            .chars()
16            .map(|character| {
17                if character.is_ascii_alphanumeric() || character == '_' {
18                    character
19                } else {
20                    '_'
21                }
22            })
23            .collect::<String>();
24        validate_identifier(self, &sanitized)?;
25        Ok(sanitized)
26    }
27
28    pub fn placeholder(self, index: usize) -> String {
29        match self {
30            Self::Postgres => format!("${index}"),
31            Self::MySql | Self::Sqlite => "?".to_owned(),
32        }
33    }
34
35    pub fn where_clause(
36        self,
37        table: &DbTable,
38        clauses: &[Where],
39    ) -> Result<SqlFragment, OpenAuthError> {
40        self.where_clause_starting_at(table, clauses, 1)
41    }
42
43    pub fn where_clause_starting_at(
44        self,
45        table: &DbTable,
46        clauses: &[Where],
47        first_placeholder: usize,
48    ) -> Result<SqlFragment, OpenAuthError> {
49        if clauses.is_empty() {
50            return Ok(SqlFragment::default());
51        }
52
53        let mut and_clauses = Vec::new();
54        let mut or_clauses = Vec::new();
55        for clause in clauses {
56            match clause.connector {
57                Connector::And => and_clauses.push(clause),
58                Connector::Or => or_clauses.push(clause),
59            }
60        }
61
62        let mut sql = String::from(" WHERE ");
63        let mut parts = Vec::new();
64        let mut params = Vec::new();
65
66        for clause in and_clauses {
67            parts.push(self.clause_sql(table, clause, &mut params, first_placeholder)?);
68        }
69
70        if !or_clauses.is_empty() {
71            let mut or_parts = Vec::new();
72            for clause in or_clauses {
73                or_parts.push(self.clause_sql(table, clause, &mut params, first_placeholder)?);
74            }
75            let or_sql = or_parts.join(" OR ");
76            if parts.is_empty() && or_parts.len() == 1 {
77                parts.push(or_sql);
78            } else {
79                parts.push(format!("({or_sql})"));
80            }
81        }
82
83        sql.push_str(&parts.join(" AND "));
84        Ok(SqlFragment { sql, params })
85    }
86
87    fn clause_sql(
88        self,
89        table: &DbTable,
90        clause: &Where,
91        params: &mut Vec<SqlParam>,
92        first_placeholder: usize,
93    ) -> Result<String, OpenAuthError> {
94        let (_, field) = resolve_field(table, &clause.field)?;
95        let column = self.quote_identifier(&field.name)?;
96        if clause.value == DbValue::Null {
97            return Ok(match clause.operator {
98                WhereOperator::Eq => format!("{column} IS NULL"),
99                WhereOperator::Ne => format!("{column} IS NOT NULL"),
100                _ => {
101                    return Err(OpenAuthError::Adapter(
102                        "null only supports Eq and Ne operators".to_owned(),
103                    ))
104                }
105            });
106        }
107
108        match clause.operator {
109            WhereOperator::Eq
110            | WhereOperator::Ne
111            | WhereOperator::Lt
112            | WhereOperator::Lte
113            | WhereOperator::Gt
114            | WhereOperator::Gte => {
115                let operator = match clause.operator {
116                    WhereOperator::Eq => "=",
117                    WhereOperator::Ne => "!=",
118                    WhereOperator::Lt => "<",
119                    WhereOperator::Lte => "<=",
120                    WhereOperator::Gt => ">",
121                    WhereOperator::Gte => ">=",
122                    _ => {
123                        return Err(OpenAuthError::Adapter(
124                            "unsupported scalar where operator".to_owned(),
125                        ));
126                    }
127                };
128                let placeholder =
129                    self.push_param(params, field, clause.value.clone(), first_placeholder);
130                if clause.mode == WhereMode::Insensitive
131                    && field.field_type == DbFieldType::String
132                    && matches!(&clause.value, DbValue::String(_))
133                    && matches!(clause.operator, WhereOperator::Eq | WhereOperator::Ne)
134                {
135                    Ok(format!("LOWER({column}) {operator} LOWER({placeholder})"))
136                } else {
137                    Ok(format!("{column} {operator} {placeholder}"))
138                }
139            }
140            WhereOperator::In | WhereOperator::NotIn => {
141                let placeholders =
142                    self.push_array_params(params, field, &clause.value, first_placeholder)?;
143                if placeholders.is_empty() {
144                    return Ok(if clause.operator == WhereOperator::In {
145                        "1 = 0".to_owned()
146                    } else {
147                        "1 = 1".to_owned()
148                    });
149                }
150                let operator = if clause.operator == WhereOperator::In {
151                    "IN"
152                } else {
153                    "NOT IN"
154                };
155                let placeholders = if clause.mode == WhereMode::Insensitive
156                    && field.field_type == DbFieldType::String
157                    && matches!(&clause.value, DbValue::StringArray(_))
158                {
159                    placeholders
160                        .into_iter()
161                        .map(|placeholder| format!("LOWER({placeholder})"))
162                        .collect::<Vec<_>>()
163                } else {
164                    placeholders
165                };
166                let column = if clause.mode == WhereMode::Insensitive
167                    && field.field_type == DbFieldType::String
168                    && matches!(&clause.value, DbValue::StringArray(_))
169                {
170                    format!("LOWER({column})")
171                } else {
172                    column
173                };
174                Ok(format!("{column} {operator} ({})", placeholders.join(", ")))
175            }
176            WhereOperator::Contains | WhereOperator::StartsWith | WhereOperator::EndsWith => {
177                let DbValue::String(value) = &clause.value else {
178                    return Err(OpenAuthError::Adapter(
179                        "string pattern operators require string values".to_owned(),
180                    ));
181                };
182                let value = escape_like_pattern(value);
183                let pattern = match clause.operator {
184                    WhereOperator::Contains => format!("%{value}%"),
185                    WhereOperator::StartsWith => format!("{value}%"),
186                    WhereOperator::EndsWith => format!("%{value}"),
187                    _ => {
188                        return Err(OpenAuthError::Adapter(
189                            "unsupported string pattern where operator".to_owned(),
190                        ));
191                    }
192                };
193                let placeholder =
194                    self.push_param(params, field, DbValue::String(pattern), first_placeholder);
195                if clause.mode == WhereMode::Insensitive {
196                    if self == Self::Postgres {
197                        Ok(format!(
198                            "{column} ILIKE {placeholder} {}",
199                            self.like_escape_clause()
200                        ))
201                    } else {
202                        Ok(format!(
203                            "LOWER({column}) LIKE LOWER({placeholder}) {}",
204                            self.like_escape_clause()
205                        ))
206                    }
207                } else {
208                    Ok(format!(
209                        "{column} LIKE {placeholder} {}",
210                        self.like_escape_clause()
211                    ))
212                }
213            }
214        }
215    }
216
217    fn push_param(
218        &self,
219        params: &mut Vec<SqlParam>,
220        field: &DbField,
221        value: DbValue,
222        first_placeholder: usize,
223    ) -> String {
224        params.push(SqlParam::new(field, value));
225        self.placeholder(first_placeholder + params.len() - 1)
226    }
227
228    fn push_array_params(
229        self,
230        params: &mut Vec<SqlParam>,
231        field: &DbField,
232        value: &DbValue,
233        first_placeholder: usize,
234    ) -> Result<Vec<String>, OpenAuthError> {
235        match value {
236            DbValue::StringArray(values) => Ok(values
237                .iter()
238                .map(|value| {
239                    self.push_param(
240                        params,
241                        field,
242                        DbValue::String(value.clone()),
243                        first_placeholder,
244                    )
245                })
246                .collect()),
247            DbValue::NumberArray(values) => Ok(values
248                .iter()
249                .map(|value| {
250                    self.push_param(params, field, DbValue::Number(*value), first_placeholder)
251                })
252                .collect()),
253            _ => Err(OpenAuthError::Adapter(
254                "IN and NOT IN require array values".to_owned(),
255            )),
256        }
257    }
258
259    pub fn order_limit_offset(
260        self,
261        table: &DbTable,
262        sort_by: Option<&Sort>,
263        limit: Option<usize>,
264        offset: Option<usize>,
265    ) -> Result<String, OpenAuthError> {
266        let mut sql = String::new();
267        if let Some(sort) = sort_by {
268            let (_, field) = resolve_field(table, &sort.field)?;
269            let direction = match sort.direction {
270                SortDirection::Asc => "ASC",
271                SortDirection::Desc => "DESC",
272            };
273            sql.push_str(" ORDER BY ");
274            sql.push_str(&self.quote_identifier(&field.name)?);
275            sql.push(' ');
276            sql.push_str(direction);
277        }
278        if let Some(limit) = limit {
279            sql.push_str(" LIMIT ");
280            sql.push_str(&limit.to_string());
281        }
282        if let Some(offset) = offset {
283            sql.push_str(" OFFSET ");
284            sql.push_str(&offset.to_string());
285        }
286        Ok(sql)
287    }
288
289    pub fn column_definition(
290        self,
291        logical_name: &str,
292        field: &DbField,
293    ) -> Result<String, OpenAuthError> {
294        let mut parts = vec![
295            self.quote_identifier(&field.name)?,
296            self.sql_type(logical_name, field),
297        ];
298        if logical_name == "id" || field.name == "id" {
299            match (self, field.generated_id) {
300                (Self::Postgres, Some(IdGeneration::Serial)) => {
301                    parts.push("GENERATED BY DEFAULT AS IDENTITY".to_owned());
302                }
303                (Self::Postgres, Some(IdGeneration::Uuid)) => {
304                    parts.push("DEFAULT pg_catalog.gen_random_uuid()".to_owned());
305                }
306                (Self::MySql, Some(IdGeneration::Serial)) => {
307                    parts.push("AUTO_INCREMENT".to_owned());
308                }
309                _ => {}
310            }
311            parts.push("PRIMARY KEY".to_owned());
312        } else {
313            if field.required {
314                parts.push("NOT NULL".to_owned());
315            }
316            if field.unique {
317                parts.push("UNIQUE".to_owned());
318            }
319        }
320        if let Some(foreign_key) = &field.foreign_key {
321            parts.push(format!(
322                "REFERENCES {} ({})",
323                self.quote_identifier(&foreign_key.table)?,
324                self.quote_identifier(&foreign_key.field)?
325            ));
326            parts.push(on_delete_sql(foreign_key.on_delete).to_owned());
327        }
328        Ok(parts.join(" "))
329    }
330
331    pub fn create_table_statement(self, table: &DbTable) -> Result<String, OpenAuthError> {
332        let columns = table
333            .fields
334            .iter()
335            .map(|(logical_name, field)| self.column_definition(logical_name, field))
336            .collect::<Result<Vec<_>, _>>()?;
337        let suffix = match self {
338            Self::MySql => " ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
339            Self::Postgres | Self::Sqlite => "",
340        };
341        Ok(format!(
342            "CREATE TABLE IF NOT EXISTS {} ({}){}",
343            self.quote_identifier(&table.name)?,
344            columns.join(", "),
345            suffix
346        ))
347    }
348
349    pub fn add_column_statement(
350        self,
351        table: &str,
352        logical_name: &str,
353        field: &DbField,
354    ) -> Result<String, OpenAuthError> {
355        Ok(format!(
356            "ALTER TABLE {} ADD COLUMN {}",
357            self.quote_identifier(table)?,
358            self.column_definition(logical_name, field)?,
359        ))
360    }
361
362    pub fn create_index_statement(
363        self,
364        table: &str,
365        column: &str,
366        index: &str,
367        unique: bool,
368    ) -> Result<String, OpenAuthError> {
369        let if_not_exists = match self {
370            Self::Postgres | Self::Sqlite => " IF NOT EXISTS",
371            Self::MySql => "",
372        };
373        let unique = if unique { "UNIQUE " } else { "" };
374        Ok(format!(
375            "CREATE {unique}INDEX{} {} ON {} ({})",
376            if_not_exists,
377            self.quote_identifier(index)?,
378            self.quote_identifier(table)?,
379            self.quote_identifier(column)?,
380        ))
381    }
382
383    pub fn sql_type(self, logical_name: &str, field: &DbField) -> String {
384        match self {
385            Self::Postgres => match field.field_type {
386                DbFieldType::String if field.generated_id == Some(IdGeneration::Uuid) => "UUID",
387                DbFieldType::String => "TEXT",
388                DbFieldType::Number => "BIGINT",
389                DbFieldType::Boolean => "BOOLEAN",
390                DbFieldType::Timestamp => "TIMESTAMPTZ",
391                DbFieldType::Json => "JSONB",
392                DbFieldType::StringArray => "TEXT[]",
393                DbFieldType::NumberArray => "BIGINT[]",
394            }
395            .to_owned(),
396            Self::Sqlite => match field.field_type {
397                DbFieldType::Number if field.generated_id == Some(IdGeneration::Serial) => {
398                    "INTEGER"
399                }
400                DbFieldType::String
401                | DbFieldType::Timestamp
402                | DbFieldType::Json
403                | DbFieldType::StringArray
404                | DbFieldType::NumberArray => "TEXT",
405                DbFieldType::Number | DbFieldType::Boolean => "INTEGER",
406            }
407            .to_owned(),
408            Self::MySql => match field.field_type {
409                DbFieldType::Number if field.generated_id == Some(IdGeneration::Serial) => "BIGINT",
410                DbFieldType::String if logical_name == "id" || field.unique || field.index => {
411                    "VARCHAR(255)"
412                }
413                DbFieldType::String => "TEXT",
414                DbFieldType::Number => "BIGINT",
415                DbFieldType::Boolean => "BOOLEAN",
416                DbFieldType::Timestamp => "DATETIME(6)",
417                DbFieldType::Json | DbFieldType::StringArray | DbFieldType::NumberArray => "JSON",
418            }
419            .to_owned(),
420        }
421    }
422
423    pub fn type_matches(self, actual: &str, field: &DbField) -> bool {
424        let actual = normalized_type(actual);
425        match self {
426            Self::Postgres => match field.field_type {
427                DbFieldType::String => {
428                    matches!(
429                        actual.as_str(),
430                        "text" | "character varying" | "varchar" | "uuid"
431                    )
432                }
433                DbFieldType::Number => matches!(
434                    actual.as_str(),
435                    "bigint"
436                        | "integer"
437                        | "smallint"
438                        | "numeric"
439                        | "real"
440                        | "double precision"
441                        | "int8"
442                        | "int4"
443                        | "int2"
444                ),
445                DbFieldType::Boolean => matches!(actual.as_str(), "boolean" | "bool"),
446                DbFieldType::Timestamp => matches!(
447                    actual.as_str(),
448                    "timestamp with time zone"
449                        | "timestamp without time zone"
450                        | "timestamp"
451                        | "timestamptz"
452                        | "date"
453                ),
454                DbFieldType::Json => matches!(actual.as_str(), "jsonb" | "json"),
455                DbFieldType::StringArray => {
456                    matches!(actual.as_str(), "text[]" | "_text" | "_varchar" | "_bpchar")
457                }
458                DbFieldType::NumberArray => matches!(
459                    actual.as_str(),
460                    "bigint[]" | "integer[]" | "_int8" | "_int4" | "_int2"
461                ),
462            },
463            Self::MySql => match field.field_type {
464                DbFieldType::String => matches!(actual.as_str(), "varchar" | "text" | "uuid"),
465                DbFieldType::Number => matches!(
466                    actual.as_str(),
467                    "integer" | "int" | "bigint" | "smallint" | "decimal" | "float" | "double"
468                ),
469                DbFieldType::Boolean => matches!(actual.as_str(), "boolean" | "tinyint"),
470                DbFieldType::Timestamp => {
471                    matches!(actual.as_str(), "timestamp" | "datetime" | "date")
472                }
473                DbFieldType::Json | DbFieldType::StringArray | DbFieldType::NumberArray => {
474                    actual.as_str() == "json"
475                }
476            },
477            Self::Sqlite => match field.field_type {
478                DbFieldType::String
479                | DbFieldType::Timestamp
480                | DbFieldType::Json
481                | DbFieldType::StringArray
482                | DbFieldType::NumberArray => matches!(
483                    actual.as_str(),
484                    "text" | "varchar" | "character varying" | "nvarchar" | "clob"
485                ),
486                DbFieldType::Number => matches!(
487                    actual.as_str(),
488                    "integer"
489                        | "int"
490                        | "bigint"
491                        | "smallint"
492                        | "tinyint"
493                        | "numeric"
494                        | "real"
495                        | "double"
496                ),
497                DbFieldType::Boolean => matches!(
498                    actual.as_str(),
499                    "integer" | "int" | "bigint" | "smallint" | "tinyint" | "boolean" | "bool"
500                ),
501            },
502        }
503    }
504}
505
506fn escape_like_pattern(value: &str) -> String {
507    let mut escaped = String::with_capacity(value.len());
508    for character in value.chars() {
509        if matches!(character, '%' | '_' | '\\') {
510            escaped.push('\\');
511        }
512        escaped.push(character);
513    }
514    escaped
515}
516
517fn validate_identifier(dialect: SqlDialect, identifier: &str) -> Result<(), OpenAuthError> {
518    let mut chars = identifier.chars();
519    let Some(first) = chars.next() else {
520        return Err(OpenAuthError::Adapter(format!(
521            "{} identifier cannot be empty",
522            dialect.name()
523        )));
524    };
525    if !(first.is_ascii_alphabetic() || first == '_') {
526        return Err(invalid_identifier(dialect, identifier));
527    }
528    if chars.any(|character| !(character.is_ascii_alphanumeric() || character == '_')) {
529        return Err(invalid_identifier(dialect, identifier));
530    }
531    Ok(())
532}
533
534fn invalid_identifier(dialect: SqlDialect, identifier: &str) -> OpenAuthError {
535    OpenAuthError::Adapter(format!(
536        "invalid {} identifier `{identifier}`",
537        dialect.name()
538    ))
539}
540
541impl SqlDialect {
542    fn name(self) -> &'static str {
543        match self {
544            Self::Postgres => "postgres",
545            Self::MySql => "mysql",
546            Self::Sqlite => "sqlite",
547        }
548    }
549
550    fn like_escape_clause(self) -> &'static str {
551        match self {
552            Self::MySql => "ESCAPE '\\\\'",
553            Self::Postgres | Self::Sqlite => "ESCAPE '\\'",
554        }
555    }
556}
557
558fn normalized_type(value: &str) -> String {
559    value
560        .trim()
561        .split_once('(')
562        .map(|(prefix, _)| prefix)
563        .unwrap_or(value)
564        .trim()
565        .to_ascii_lowercase()
566}
567
568fn on_delete_sql(on_delete: OnDelete) -> &'static str {
569    match on_delete {
570        OnDelete::NoAction => "ON DELETE NO ACTION",
571        OnDelete::Restrict => "ON DELETE RESTRICT",
572        OnDelete::Cascade => "ON DELETE CASCADE",
573        OnDelete::SetNull => "ON DELETE SET NULL",
574        OnDelete::SetDefault => "ON DELETE SET DEFAULT",
575    }
576}
577
578#[cfg(test)]
579mod tests {
580    use super::*;
581
582    fn user_table() -> DbTable {
583        let mut fields = IndexMap::new();
584        fields.insert(
585            "email".to_owned(),
586            DbField::new("email", DbFieldType::String),
587        );
588        fields.insert("name".to_owned(), DbField::new("name", DbFieldType::String));
589        DbTable {
590            name: "users".to_owned(),
591            fields,
592            order: None,
593        }
594    }
595
596    #[test]
597    fn where_clause_applies_insensitive_mode_to_eq() -> Result<(), OpenAuthError> {
598        let clause =
599            Where::new("email", DbValue::String("ADA@EXAMPLE.COM".to_owned())).insensitive();
600
601        let fragment = SqlDialect::Postgres.where_clause(&user_table(), &[clause])?;
602
603        assert_eq!(fragment.sql, r#" WHERE LOWER("email") = LOWER($1)"#);
604        Ok(())
605    }
606
607    #[test]
608    fn where_clause_applies_insensitive_mode_to_ne() -> Result<(), OpenAuthError> {
609        let clause = Where::new("email", DbValue::String("ADA@EXAMPLE.COM".to_owned()))
610            .operator(WhereOperator::Ne)
611            .insensitive();
612
613        let fragment = SqlDialect::Postgres.where_clause(&user_table(), &[clause])?;
614
615        assert_eq!(fragment.sql, r#" WHERE LOWER("email") != LOWER($1)"#);
616        Ok(())
617    }
618
619    #[test]
620    fn where_clause_applies_insensitive_mode_to_in() -> Result<(), OpenAuthError> {
621        let clause = Where::new(
622            "email",
623            DbValue::StringArray(vec![
624                "ADA@EXAMPLE.COM".to_owned(),
625                "GRACE@EXAMPLE.COM".to_owned(),
626            ]),
627        )
628        .operator(WhereOperator::In)
629        .insensitive();
630
631        let fragment = SqlDialect::Postgres.where_clause(&user_table(), &[clause])?;
632
633        assert_eq!(
634            fragment.sql,
635            r#" WHERE LOWER("email") IN (LOWER($1), LOWER($2))"#
636        );
637        Ok(())
638    }
639
640    #[test]
641    fn where_clause_applies_insensitive_mode_to_not_in() -> Result<(), OpenAuthError> {
642        let clause = Where::new(
643            "email",
644            DbValue::StringArray(vec!["ADA@EXAMPLE.COM".to_owned()]),
645        )
646        .operator(WhereOperator::NotIn)
647        .insensitive();
648
649        let fragment = SqlDialect::Postgres.where_clause(&user_table(), &[clause])?;
650
651        assert_eq!(fragment.sql, r#" WHERE LOWER("email") NOT IN (LOWER($1))"#);
652        Ok(())
653    }
654}