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}