spring_batch_rs/item/rdbc/select_builder.rs
1//! Fluent SQL SELECT builder for RDBC item readers.
2//!
3//! This module provides [`SelectBuilder`], a type-safe fluent API for constructing
4//! SQL `SELECT` statements with optional `WHERE` conditions and `ORDER BY` clauses.
5//!
6//! # Key Types
7//!
8//! - [`SelectBuilder`] — the public builder; configure via method chaining and call
9//! [`SelectBuilder::build_sql`] to get the final SQL string.
10//!
11//! # Examples
12//!
13//! ```
14//! use spring_batch_rs::item::rdbc::SelectBuilder;
15//!
16//! struct Row;
17//!
18//! let sql = SelectBuilder::<Row>::from("users")
19//! .columns(&["id", "name", "email"])
20//! .where_eq("active", true)
21//! .order_by_asc("name")
22//! .build_sql();
23//!
24//! assert_eq!(sql, "SELECT id, name, email FROM users WHERE active = true ORDER BY name ASC");
25//! ```
26
27use std::marker::PhantomData;
28
29// ──────────────────────────────────────────────────────────────────────────────
30// Internal types
31// ──────────────────────────────────────────────────────────────────────────────
32
33/// A typed SQL literal value used inside WHERE conditions.
34///
35/// Users never construct this directly; instead they pass any type that
36/// implements `Into<ConditionValue>` (e.g. `i32`, `bool`, `&str`).
37pub(crate) enum ConditionValue {
38 /// A 64-bit signed integer.
39 Integer(i64),
40 /// A 64-bit floating-point number.
41 Float(f64),
42 /// A text string.
43 Text(String),
44 /// A boolean.
45 Bool(bool),
46}
47
48impl ConditionValue {
49 /// Renders the value as a SQL literal string.
50 pub(crate) fn to_sql(&self) -> String {
51 match self {
52 ConditionValue::Integer(n) => n.to_string(),
53 ConditionValue::Float(f) => f.to_string(),
54 ConditionValue::Bool(b) => b.to_string(),
55 ConditionValue::Text(s) => format!("'{}'", s.replace('\'', "''")),
56 }
57 }
58}
59
60// `From` implementations — cover the most common primitive types.
61
62impl From<i32> for ConditionValue {
63 fn from(v: i32) -> Self {
64 ConditionValue::Integer(i64::from(v))
65 }
66}
67
68impl From<i64> for ConditionValue {
69 fn from(v: i64) -> Self {
70 ConditionValue::Integer(v)
71 }
72}
73
74impl From<f32> for ConditionValue {
75 fn from(v: f32) -> Self {
76 ConditionValue::Float(f64::from(v))
77 }
78}
79
80impl From<f64> for ConditionValue {
81 fn from(v: f64) -> Self {
82 ConditionValue::Float(v)
83 }
84}
85
86impl From<bool> for ConditionValue {
87 fn from(v: bool) -> Self {
88 ConditionValue::Bool(v)
89 }
90}
91
92impl From<&str> for ConditionValue {
93 fn from(v: &str) -> Self {
94 ConditionValue::Text(v.to_owned())
95 }
96}
97
98impl From<String> for ConditionValue {
99 fn from(v: String) -> Self {
100 ConditionValue::Text(v)
101 }
102}
103
104// ──────────────────────────────────────────────────────────────────────────────
105
106/// A single WHERE predicate.
107pub(crate) enum WhereClause {
108 /// `col = val`
109 Eq(String, ConditionValue),
110 /// `col != val`
111 NotEq(String, ConditionValue),
112 /// `col > val`
113 Gt(String, ConditionValue),
114 /// `col >= val`
115 Gte(String, ConditionValue),
116 /// `col < val`
117 Lt(String, ConditionValue),
118 /// `col <= val`
119 Lte(String, ConditionValue),
120 /// `col LIKE 'pattern'`
121 Like(String, String),
122 /// `col IS NULL`
123 IsNull(String),
124 /// `col IS NOT NULL`
125 IsNotNull(String),
126}
127
128impl WhereClause {
129 /// Renders the clause as a SQL fragment.
130 pub(crate) fn to_sql(&self) -> String {
131 match self {
132 WhereClause::Eq(col, val) => format!("{} = {}", col, val.to_sql()),
133 WhereClause::NotEq(col, val) => format!("{} != {}", col, val.to_sql()),
134 WhereClause::Gt(col, val) => format!("{} > {}", col, val.to_sql()),
135 WhereClause::Gte(col, val) => format!("{} >= {}", col, val.to_sql()),
136 WhereClause::Lt(col, val) => format!("{} < {}", col, val.to_sql()),
137 WhereClause::Lte(col, val) => format!("{} <= {}", col, val.to_sql()),
138 WhereClause::Like(col, pat) => {
139 format!("{} LIKE '{}'", col, pat.replace('\'', "''"))
140 }
141 WhereClause::IsNull(col) => format!("{} IS NULL", col),
142 WhereClause::IsNotNull(col) => format!("{} IS NOT NULL", col),
143 }
144 }
145}
146
147// ──────────────────────────────────────────────────────────────────────────────
148
149/// A single ORDER BY directive.
150pub(crate) enum OrderClause {
151 /// `col ASC`
152 Asc(String),
153 /// `col DESC`
154 Desc(String),
155}
156
157impl OrderClause {
158 fn to_sql(&self) -> String {
159 match self {
160 OrderClause::Asc(col) => format!("{} ASC", col),
161 OrderClause::Desc(col) => format!("{} DESC", col),
162 }
163 }
164}
165
166// ──────────────────────────────────────────────────────────────────────────────
167// Public struct
168// ──────────────────────────────────────────────────────────────────────────────
169
170/// Fluent builder for SQL `SELECT` statements used by RDBC item readers.
171///
172/// Call [`SelectBuilder::from`] to create a builder for a given table, chain
173/// optional filter/order methods, then call [`SelectBuilder::build_sql`] to
174/// obtain the final SQL string.
175///
176/// # Type Parameters
177///
178/// * `I` — The item type that will be read from the database. Used only for
179/// the keyset key function; when keyset pagination is not needed `I` can be
180/// any type (e.g. a unit struct).
181///
182/// # Examples
183///
184/// ```
185/// use spring_batch_rs::item::rdbc::SelectBuilder;
186///
187/// struct Product;
188///
189/// let sql = SelectBuilder::<Product>::from("products")
190/// .columns(&["id", "name", "price"])
191/// .where_gte("price", 10.0_f64)
192/// .where_eq("active", true)
193/// .order_by_desc("price")
194/// .build_sql();
195///
196/// assert!(sql.starts_with("SELECT id, name, price FROM products WHERE"));
197/// assert!(sql.contains("ORDER BY price DESC"));
198/// ```
199pub struct SelectBuilder<I> {
200 table: String,
201 columns: Vec<String>,
202 conditions: Vec<WhereClause>,
203 order_by: Vec<OrderClause>,
204 /// Column name used as the keyset cursor.
205 pub(crate) keyset_column: Option<String>,
206 /// Extracts the keyset cursor value from the last-read item.
207 #[allow(clippy::type_complexity)]
208 pub(crate) keyset_key_fn: Option<Box<dyn Fn(&I) -> String>>,
209 _phantom: PhantomData<I>,
210}
211
212#[allow(private_bounds)]
213impl<I> SelectBuilder<I> {
214 /// Creates a new `SelectBuilder` targeting the given table.
215 ///
216 /// # Examples
217 ///
218 /// ```
219 /// use spring_batch_rs::item::rdbc::SelectBuilder;
220 ///
221 /// struct Row;
222 ///
223 /// let sql = SelectBuilder::<Row>::from("orders").build_sql();
224 /// assert_eq!(sql, "SELECT * FROM orders");
225 /// ```
226 pub fn from(table: impl Into<String>) -> Self {
227 SelectBuilder {
228 table: table.into(),
229 columns: Vec::new(),
230 conditions: Vec::new(),
231 order_by: Vec::new(),
232 keyset_column: None,
233 keyset_key_fn: None,
234 _phantom: PhantomData,
235 }
236 }
237
238 /// Specifies the columns to select.
239 ///
240 /// When not called (or called with an empty slice), the query uses `SELECT *`.
241 ///
242 /// # Examples
243 ///
244 /// ```
245 /// use spring_batch_rs::item::rdbc::SelectBuilder;
246 ///
247 /// struct Row;
248 ///
249 /// let sql = SelectBuilder::<Row>::from("users")
250 /// .columns(&["id", "email"])
251 /// .build_sql();
252 ///
253 /// assert_eq!(sql, "SELECT id, email FROM users");
254 /// ```
255 pub fn columns(mut self, cols: &[&str]) -> Self {
256 self.columns = cols.iter().map(|c| c.to_string()).collect();
257 self
258 }
259
260 /// Adds a `col = val` condition.
261 ///
262 /// # Examples
263 ///
264 /// ```
265 /// use spring_batch_rs::item::rdbc::SelectBuilder;
266 ///
267 /// struct Row;
268 ///
269 /// let sql = SelectBuilder::<Row>::from("users")
270 /// .where_eq("status", "ACTIVE")
271 /// .build_sql();
272 ///
273 /// assert_eq!(sql, "SELECT * FROM users WHERE status = 'ACTIVE'");
274 /// ```
275 pub fn where_eq(mut self, col: &str, val: impl Into<ConditionValue>) -> Self {
276 self.conditions
277 .push(WhereClause::Eq(col.to_owned(), val.into()));
278 self
279 }
280
281 /// Adds a `col != val` condition.
282 ///
283 /// # Examples
284 ///
285 /// ```
286 /// use spring_batch_rs::item::rdbc::SelectBuilder;
287 ///
288 /// struct Row;
289 ///
290 /// let sql = SelectBuilder::<Row>::from("users")
291 /// .where_not_eq("role", "ADMIN")
292 /// .build_sql();
293 ///
294 /// assert_eq!(sql, "SELECT * FROM users WHERE role != 'ADMIN'");
295 /// ```
296 pub fn where_not_eq(mut self, col: &str, val: impl Into<ConditionValue>) -> Self {
297 self.conditions
298 .push(WhereClause::NotEq(col.to_owned(), val.into()));
299 self
300 }
301
302 /// Adds a `col > val` condition.
303 ///
304 /// # Examples
305 ///
306 /// ```
307 /// use spring_batch_rs::item::rdbc::SelectBuilder;
308 ///
309 /// struct Row;
310 ///
311 /// let sql = SelectBuilder::<Row>::from("orders")
312 /// .where_gt("amount", 100_i32)
313 /// .build_sql();
314 ///
315 /// assert_eq!(sql, "SELECT * FROM orders WHERE amount > 100");
316 /// ```
317 pub fn where_gt(mut self, col: &str, val: impl Into<ConditionValue>) -> Self {
318 self.conditions
319 .push(WhereClause::Gt(col.to_owned(), val.into()));
320 self
321 }
322
323 /// Adds a `col >= val` condition.
324 ///
325 /// # Examples
326 ///
327 /// ```
328 /// use spring_batch_rs::item::rdbc::SelectBuilder;
329 ///
330 /// struct Row;
331 ///
332 /// let sql = SelectBuilder::<Row>::from("orders")
333 /// .where_gte("score", 4.5_f64)
334 /// .build_sql();
335 ///
336 /// assert!(sql.starts_with("SELECT * FROM orders WHERE score >= "));
337 /// ```
338 pub fn where_gte(mut self, col: &str, val: impl Into<ConditionValue>) -> Self {
339 self.conditions
340 .push(WhereClause::Gte(col.to_owned(), val.into()));
341 self
342 }
343
344 /// Adds a `col < val` condition.
345 ///
346 /// # Examples
347 ///
348 /// ```
349 /// use spring_batch_rs::item::rdbc::SelectBuilder;
350 ///
351 /// struct Row;
352 ///
353 /// let sql = SelectBuilder::<Row>::from("items")
354 /// .where_lt("stock", 10_i32)
355 /// .build_sql();
356 ///
357 /// assert_eq!(sql, "SELECT * FROM items WHERE stock < 10");
358 /// ```
359 pub fn where_lt(mut self, col: &str, val: impl Into<ConditionValue>) -> Self {
360 self.conditions
361 .push(WhereClause::Lt(col.to_owned(), val.into()));
362 self
363 }
364
365 /// Adds a `col <= val` condition.
366 ///
367 /// # Examples
368 ///
369 /// ```
370 /// use spring_batch_rs::item::rdbc::SelectBuilder;
371 ///
372 /// struct Row;
373 ///
374 /// let sql = SelectBuilder::<Row>::from("items")
375 /// .where_lte("rank", 100_i32)
376 /// .build_sql();
377 ///
378 /// assert_eq!(sql, "SELECT * FROM items WHERE rank <= 100");
379 /// ```
380 pub fn where_lte(mut self, col: &str, val: impl Into<ConditionValue>) -> Self {
381 self.conditions
382 .push(WhereClause::Lte(col.to_owned(), val.into()));
383 self
384 }
385
386 /// Adds a `col LIKE 'pattern'` condition.
387 ///
388 /// Single quotes in `pat` are escaped automatically.
389 ///
390 /// # Examples
391 ///
392 /// ```
393 /// use spring_batch_rs::item::rdbc::SelectBuilder;
394 ///
395 /// struct Row;
396 ///
397 /// let sql = SelectBuilder::<Row>::from("users")
398 /// .where_like("email", "%@corp.com")
399 /// .build_sql();
400 ///
401 /// assert_eq!(sql, "SELECT * FROM users WHERE email LIKE '%@corp.com'");
402 /// ```
403 pub fn where_like(mut self, col: &str, pat: &str) -> Self {
404 self.conditions
405 .push(WhereClause::Like(col.to_owned(), pat.to_owned()));
406 self
407 }
408
409 /// Adds a `col IS NULL` condition.
410 ///
411 /// # Examples
412 ///
413 /// ```
414 /// use spring_batch_rs::item::rdbc::SelectBuilder;
415 ///
416 /// struct Row;
417 ///
418 /// let sql = SelectBuilder::<Row>::from("users")
419 /// .where_is_null("deleted_at")
420 /// .build_sql();
421 ///
422 /// assert_eq!(sql, "SELECT * FROM users WHERE deleted_at IS NULL");
423 /// ```
424 pub fn where_is_null(mut self, col: &str) -> Self {
425 self.conditions.push(WhereClause::IsNull(col.to_owned()));
426 self
427 }
428
429 /// Adds a `col IS NOT NULL` condition.
430 ///
431 /// # Examples
432 ///
433 /// ```
434 /// use spring_batch_rs::item::rdbc::SelectBuilder;
435 ///
436 /// struct Row;
437 ///
438 /// let sql = SelectBuilder::<Row>::from("users")
439 /// .where_is_not_null("confirmed_at")
440 /// .build_sql();
441 ///
442 /// assert_eq!(sql, "SELECT * FROM users WHERE confirmed_at IS NOT NULL");
443 /// ```
444 pub fn where_is_not_null(mut self, col: &str) -> Self {
445 self.conditions.push(WhereClause::IsNotNull(col.to_owned()));
446 self
447 }
448
449 /// Appends an `ORDER BY col ASC` clause.
450 ///
451 /// # Examples
452 ///
453 /// ```
454 /// use spring_batch_rs::item::rdbc::SelectBuilder;
455 ///
456 /// struct Row;
457 ///
458 /// let sql = SelectBuilder::<Row>::from("users")
459 /// .order_by_asc("created_at")
460 /// .build_sql();
461 ///
462 /// assert_eq!(sql, "SELECT * FROM users ORDER BY created_at ASC");
463 /// ```
464 pub fn order_by_asc(mut self, col: &str) -> Self {
465 self.order_by.push(OrderClause::Asc(col.to_owned()));
466 self
467 }
468
469 /// Appends an `ORDER BY col DESC` clause.
470 ///
471 /// # Examples
472 ///
473 /// ```
474 /// use spring_batch_rs::item::rdbc::SelectBuilder;
475 ///
476 /// struct Row;
477 ///
478 /// let sql = SelectBuilder::<Row>::from("users")
479 /// .order_by_desc("score")
480 /// .build_sql();
481 ///
482 /// assert_eq!(sql, "SELECT * FROM users ORDER BY score DESC");
483 /// ```
484 pub fn order_by_desc(mut self, col: &str) -> Self {
485 self.order_by.push(OrderClause::Desc(col.to_owned()));
486 self
487 }
488
489 /// Configures keyset (cursor-based) pagination on `col`.
490 ///
491 /// Clears any previously configured `ORDER BY` clauses, sets `ORDER BY col ASC`,
492 /// and stores `col` and `key_fn` for use by the reader at runtime.
493 ///
494 /// `key_fn` extracts the cursor value (as a `String`) from the last item
495 /// returned by a page, so the next page can request `WHERE col > last_cursor`.
496 ///
497 /// # Examples
498 ///
499 /// ```
500 /// use spring_batch_rs::item::rdbc::SelectBuilder;
501 ///
502 /// struct Event { id: i64 }
503 ///
504 /// let sql = SelectBuilder::<Event>::from("events")
505 /// .order_by_keyset("id", |e: &Event| e.id.to_string())
506 /// .build_sql();
507 ///
508 /// assert_eq!(sql, "SELECT * FROM events ORDER BY id ASC");
509 /// ```
510 pub fn order_by_keyset(mut self, col: &str, key_fn: impl Fn(&I) -> String + 'static) -> Self {
511 self.order_by.clear();
512 self.order_by.push(OrderClause::Asc(col.to_owned()));
513 self.keyset_column = Some(col.to_owned());
514 self.keyset_key_fn = Some(Box::new(key_fn));
515 self
516 }
517
518 /// Builds and returns the SQL `SELECT` statement.
519 ///
520 /// - If no columns were specified, emits `SELECT *`.
521 /// - Multiple WHERE conditions are joined with `AND`.
522 /// - Multiple ORDER BY clauses are joined with `, `.
523 ///
524 /// # Examples
525 ///
526 /// ```
527 /// use spring_batch_rs::item::rdbc::SelectBuilder;
528 ///
529 /// struct Row;
530 ///
531 /// let sql = SelectBuilder::<Row>::from("orders")
532 /// .columns(&["id", "total"])
533 /// .where_eq("status", "OPEN")
534 /// .where_gt("total", 0_i32)
535 /// .order_by_asc("id")
536 /// .build_sql();
537 ///
538 /// assert_eq!(
539 /// sql,
540 /// "SELECT id, total FROM orders WHERE status = 'OPEN' AND total > 0 ORDER BY id ASC"
541 /// );
542 /// ```
543 pub fn build_sql(&self) -> String {
544 let col_part = if self.columns.is_empty() {
545 "*".to_owned()
546 } else {
547 self.columns.join(", ")
548 };
549
550 let mut sql = format!("SELECT {} FROM {}", col_part, self.table);
551
552 if !self.conditions.is_empty() {
553 let where_part = self
554 .conditions
555 .iter()
556 .map(WhereClause::to_sql)
557 .collect::<Vec<_>>()
558 .join(" AND ");
559 sql.push_str(" WHERE ");
560 sql.push_str(&where_part);
561 }
562
563 if !self.order_by.is_empty() {
564 let order_part = self
565 .order_by
566 .iter()
567 .map(OrderClause::to_sql)
568 .collect::<Vec<_>>()
569 .join(", ");
570 sql.push_str(" ORDER BY ");
571 sql.push_str(&order_part);
572 }
573
574 sql
575 }
576
577 /// Generates the base SQL string without the `ORDER BY` clause.
578 ///
579 /// Used internally when keyset pagination is active, since the reader
580 /// constructs the `ORDER BY` clause itself. Calling this method on a builder
581 /// that has no `ORDER BY` configured produces the same result as
582 /// [`SelectBuilder::build_sql`].
583 pub(crate) fn build_sql_no_order(&self) -> String {
584 let cols = if self.columns.is_empty() {
585 "*".to_string()
586 } else {
587 self.columns.join(", ")
588 };
589
590 let mut sql = format!("SELECT {} FROM {}", cols, self.table);
591
592 if !self.conditions.is_empty() {
593 let clauses: Vec<String> = self.conditions.iter().map(WhereClause::to_sql).collect();
594 sql.push_str(" WHERE ");
595 sql.push_str(&clauses.join(" AND "));
596 }
597
598 sql
599 }
600}
601
602// ──────────────────────────────────────────────────────────────────────────────
603// Tests
604// ──────────────────────────────────────────────────────────────────────────────
605
606#[cfg(test)]
607mod tests {
608 use super::*;
609
610 struct Dummy;
611
612 // ── SELECT column list ────────────────────────────────────────────────────
613
614 #[test]
615 fn should_generate_select_star_when_no_columns_given() {
616 let sql = SelectBuilder::<Dummy>::from("users").build_sql();
617 assert_eq!(
618 sql, "SELECT * FROM users",
619 "expected SELECT * when no columns specified"
620 );
621 }
622
623 #[test]
624 fn should_generate_column_list() {
625 let sql = SelectBuilder::<Dummy>::from("orders")
626 .columns(&["id", "amount", "status"])
627 .build_sql();
628 assert_eq!(
629 sql, "SELECT id, amount, status FROM orders",
630 "column list was not rendered correctly"
631 );
632 }
633
634 // ── WHERE conditions ─────────────────────────────────────────────────────
635
636 #[test]
637 fn should_generate_where_eq_for_string() {
638 let sql = SelectBuilder::<Dummy>::from("users")
639 .where_eq("status", "ACTIVE")
640 .build_sql();
641 assert_eq!(
642 sql, "SELECT * FROM users WHERE status = 'ACTIVE'",
643 "string equality condition was not rendered correctly"
644 );
645 }
646
647 #[test]
648 fn should_generate_where_eq_for_integer() {
649 let sql = SelectBuilder::<Dummy>::from("users")
650 .where_eq("age", 30_i32)
651 .build_sql();
652 assert_eq!(
653 sql, "SELECT * FROM users WHERE age = 30",
654 "integer equality condition was not rendered correctly"
655 );
656 }
657
658 #[test]
659 fn should_generate_where_eq_for_bool() {
660 let sql = SelectBuilder::<Dummy>::from("users")
661 .where_eq("active", true)
662 .build_sql();
663 assert_eq!(
664 sql, "SELECT * FROM users WHERE active = true",
665 "boolean equality condition was not rendered correctly"
666 );
667 }
668
669 #[test]
670 fn should_escape_single_quotes_in_string_values() {
671 let sql = SelectBuilder::<Dummy>::from("users")
672 .where_eq("name", "O'Brien")
673 .build_sql();
674 assert_eq!(
675 sql, "SELECT * FROM users WHERE name = 'O''Brien'",
676 "single quotes in string values were not escaped"
677 );
678 }
679
680 #[test]
681 fn should_generate_where_not_eq() {
682 let sql = SelectBuilder::<Dummy>::from("users")
683 .where_not_eq("role", "ADMIN")
684 .build_sql();
685 assert_eq!(
686 sql, "SELECT * FROM users WHERE role != 'ADMIN'",
687 "not-equal condition was not rendered correctly"
688 );
689 }
690
691 #[test]
692 fn should_generate_where_gt() {
693 let sql = SelectBuilder::<Dummy>::from("orders")
694 .where_gt("amount", 100_i32)
695 .build_sql();
696 assert_eq!(
697 sql, "SELECT * FROM orders WHERE amount > 100",
698 "greater-than condition was not rendered correctly"
699 );
700 }
701
702 #[test]
703 fn should_generate_where_gte() {
704 let sql = SelectBuilder::<Dummy>::from("orders")
705 .where_gte("score", 4.5_f64)
706 .build_sql();
707 assert!(
708 sql.starts_with("SELECT * FROM orders WHERE score >= "),
709 "greater-than-or-equal condition was not rendered correctly; got: {sql}"
710 );
711 }
712
713 #[test]
714 fn should_generate_where_lt() {
715 let sql = SelectBuilder::<Dummy>::from("items")
716 .where_lt("stock", 10_i32)
717 .build_sql();
718 assert_eq!(
719 sql, "SELECT * FROM items WHERE stock < 10",
720 "less-than condition was not rendered correctly"
721 );
722 }
723
724 #[test]
725 fn should_generate_where_lte() {
726 let sql = SelectBuilder::<Dummy>::from("items")
727 .where_lte("rank", 100_i32)
728 .build_sql();
729 assert_eq!(
730 sql, "SELECT * FROM items WHERE rank <= 100",
731 "less-than-or-equal condition was not rendered correctly"
732 );
733 }
734
735 #[test]
736 fn should_generate_where_like() {
737 let sql = SelectBuilder::<Dummy>::from("users")
738 .where_like("email", "%@corp.com")
739 .build_sql();
740 assert_eq!(
741 sql, "SELECT * FROM users WHERE email LIKE '%@corp.com'",
742 "LIKE condition was not rendered correctly"
743 );
744 }
745
746 #[test]
747 fn should_generate_where_is_null() {
748 let sql = SelectBuilder::<Dummy>::from("users")
749 .where_is_null("deleted_at")
750 .build_sql();
751 assert_eq!(
752 sql, "SELECT * FROM users WHERE deleted_at IS NULL",
753 "IS NULL condition was not rendered correctly"
754 );
755 }
756
757 #[test]
758 fn should_generate_where_is_not_null() {
759 let sql = SelectBuilder::<Dummy>::from("users")
760 .where_is_not_null("confirmed_at")
761 .build_sql();
762 assert_eq!(
763 sql, "SELECT * FROM users WHERE confirmed_at IS NOT NULL",
764 "IS NOT NULL condition was not rendered correctly"
765 );
766 }
767
768 #[test]
769 fn should_join_multiple_conditions_with_and() {
770 let sql = SelectBuilder::<Dummy>::from("orders")
771 .where_eq("status", "OPEN")
772 .where_gt("amount", 50_i32)
773 .where_is_null("deleted_at")
774 .build_sql();
775 assert_eq!(
776 sql,
777 "SELECT * FROM orders WHERE status = 'OPEN' AND amount > 50 AND deleted_at IS NULL",
778 "multiple conditions were not joined with AND"
779 );
780 }
781
782 // ── ORDER BY ─────────────────────────────────────────────────────────────
783
784 #[test]
785 fn should_generate_order_by_asc() {
786 let sql = SelectBuilder::<Dummy>::from("users")
787 .order_by_asc("created_at")
788 .build_sql();
789 assert_eq!(
790 sql, "SELECT * FROM users ORDER BY created_at ASC",
791 "ORDER BY ASC was not rendered correctly"
792 );
793 }
794
795 #[test]
796 fn should_generate_order_by_desc() {
797 let sql = SelectBuilder::<Dummy>::from("users")
798 .order_by_desc("score")
799 .build_sql();
800 assert_eq!(
801 sql, "SELECT * FROM users ORDER BY score DESC",
802 "ORDER BY DESC was not rendered correctly"
803 );
804 }
805
806 #[test]
807 fn should_generate_multiple_order_by_clauses() {
808 let sql = SelectBuilder::<Dummy>::from("users")
809 .order_by_asc("last_name")
810 .order_by_desc("score")
811 .build_sql();
812 assert!(
813 sql.contains("last_name ASC, score DESC"),
814 "multiple ORDER BY clauses were not rendered correctly; got: {sql}"
815 );
816 }
817
818 // ── Full query ────────────────────────────────────────────────────────────
819
820 #[test]
821 fn should_generate_full_select_with_columns_conditions_and_order() {
822 let sql = SelectBuilder::<Dummy>::from("orders")
823 .columns(&["id", "total", "status"])
824 .where_eq("status", "OPEN")
825 .where_gt("total", 0_i32)
826 .order_by_asc("id")
827 .build_sql();
828 assert_eq!(
829 sql,
830 "SELECT id, total, status FROM orders WHERE status = 'OPEN' AND total > 0 ORDER BY id ASC",
831 "full SELECT query was not rendered correctly"
832 );
833 }
834
835 // ── Keyset pagination ─────────────────────────────────────────────────────
836
837 #[test]
838 fn should_set_keyset_column_and_key_fn_on_order_by_keyset() {
839 let builder = SelectBuilder::<Dummy>::from("users")
840 .order_by_keyset("id", |_: &Dummy| "42".to_owned());
841 assert_eq!(
842 builder.keyset_column.as_deref(),
843 Some("id"),
844 "keyset_column was not set correctly"
845 );
846 assert!(
847 builder.keyset_key_fn.is_some(),
848 "keyset_key_fn should be Some after order_by_keyset"
849 );
850 }
851
852 #[test]
853 fn should_replace_previous_order_by_on_keyset() {
854 let sql = SelectBuilder::<Dummy>::from("events")
855 .order_by_desc("created_at")
856 .order_by_keyset("id", |_: &Dummy| "1".to_owned())
857 .build_sql();
858 assert_eq!(
859 sql, "SELECT * FROM events ORDER BY id ASC",
860 "previous ORDER BY clauses should be cleared when keyset is configured"
861 );
862 }
863
864 #[test]
865 fn should_generate_order_by_asc_in_sql_for_keyset() {
866 let sql = SelectBuilder::<Dummy>::from("events")
867 .order_by_keyset("id", |_: &Dummy| "1".to_owned())
868 .build_sql();
869 assert_eq!(
870 sql, "SELECT * FROM events ORDER BY id ASC",
871 "keyset pagination should produce ORDER BY id ASC"
872 );
873 }
874
875 #[test]
876 fn should_generate_where_gte_for_float() {
877 let sql = SelectBuilder::<Dummy>::from("orders")
878 .where_gte("score", 4.5_f64)
879 .build_sql();
880 assert_eq!(
881 sql, "SELECT * FROM orders WHERE score >= 4.5",
882 "unexpected: {sql}"
883 );
884 }
885
886 // ── build_sql_no_order ────────────────────────────────────────────────────
887
888 #[test]
889 fn should_omit_order_by_in_build_sql_no_order() {
890 let sql = SelectBuilder::<Dummy>::from("events")
891 .columns(&["id", "name"])
892 .order_by_keyset("id", |_: &Dummy| "1".to_owned())
893 .build_sql_no_order();
894 assert_eq!(
895 sql, "SELECT id, name FROM events",
896 "build_sql_no_order should not include ORDER BY clause"
897 );
898 }
899
900 #[test]
901 fn should_preserve_where_conditions_in_build_sql_no_order() {
902 let sql = SelectBuilder::<Dummy>::from("items")
903 .where_eq("active", true)
904 .order_by_keyset("id", |_: &Dummy| "1".to_owned())
905 .build_sql_no_order();
906 assert_eq!(
907 sql, "SELECT * FROM items WHERE active = true",
908 "build_sql_no_order should keep WHERE conditions but drop ORDER BY"
909 );
910 }
911
912 #[test]
913 fn should_match_build_sql_when_no_order_by_configured() {
914 let without_order = SelectBuilder::<Dummy>::from("users")
915 .columns(&["id"])
916 .where_eq("status", "ACTIVE")
917 .build_sql_no_order();
918 let with_build_sql = SelectBuilder::<Dummy>::from("users")
919 .columns(&["id"])
920 .where_eq("status", "ACTIVE")
921 .build_sql();
922 assert_eq!(
923 without_order, with_build_sql,
924 "build_sql_no_order should equal build_sql when no ORDER BY is set"
925 );
926 }
927}