sqlx_plus_builder/lib.rs
1//! Simple SQL code generator.
2//!
3//! ## Usage
4//!
5//! To use `sql-builder`, first add this to your `Cargo.toml`:
6//!
7//! ```toml
8//! [dependencies]
9//! sql-builder = "4.0"
10//! ```
11//!
12//! # Examples:
13//!
14//! ## SELECT
15//!
16//! ```
17//! use sql_builder::SqlBuilder;
18//! # use anyhow::Result;
19//!
20//! # fn main() -> Result<()> {
21//! let sql = SqlBuilder::select_from("company")
22//! .field("id")
23//! .field("name")
24//! .and_where_gt("salary", 25_000)
25//! .sql()?;
26//!
27//! assert_eq!("SELECT id, name FROM company WHERE salary > 25000;", &sql);
28//! # Ok(())
29//! # }
30//! ```
31//!
32//! ```
33//! # use anyhow::Result;
34//! use sql_builder::prelude::*;
35//!
36//! # fn main() -> Result<()> {
37//! let sql = SqlBuilder::select_from("company")
38//! .fields(&["id", "name"])
39//! .and_where("salary BETWEEN ? AND ?".binds(&[&10_000, &25_000]))
40//! .and_where("staff BETWEEN ? AND ?".bind(&100).bind(&200))
41//! .sql()?;
42//!
43//! assert_eq!("SELECT id, name FROM company WHERE (salary BETWEEN 10000 AND 25000) AND (staff BETWEEN 100 AND 200);", &sql);
44//! # Ok(())
45//! # }
46//! ```
47//!
48//! ## INSERT
49//!
50//! ```
51//! use sql_builder::{SqlBuilder, quote};
52//! # use anyhow::Result;
53//!
54//! # fn main() -> Result<()> {
55//! let sql = SqlBuilder::insert_into("company")
56//! .field("name")
57//! .field("salary")
58//! .field("staff")
59//! .values(&["e("D&G"), &10_000.to_string(), &100.to_string()])
60//! .values(&["e("G&D"), &25_000.to_string(), &200.to_string()])
61//! .sql()?;
62//!
63//! assert_eq!("INSERT INTO company (name, salary, staff) VALUES ('D&G', 10000, 100), ('G&D', 25000, 200);", &sql);
64//! # Ok(())
65//! # }
66//! ```
67//!
68//! ```
69//! use sql_builder::prelude::*;
70//! # use anyhow::Result;
71//!
72//! # fn main() -> Result<()> {
73//! let sql = SqlBuilder::insert_into("company")
74//! .field("name")
75//! .field("salary")
76//! .field("staff")
77//! .values(&["$1, ?, ?"])
78//! .values(&["$2, ?, ?"])
79//! .sql()?
80//! .bind_nums(&[&"D&G", &"G&D"])
81//! .binds(&[&10_000, &100]);
82//!
83//! assert_eq!("INSERT INTO company (name, salary, staff) VALUES ('D&G', 10000, 100), ('G&D', 10000, 100);", &sql);
84//! # Ok(())
85//! # }
86//! ```
87//!
88//! ## UPDATE
89//!
90//! ```
91//! use sql_builder::SqlBuilder;
92//! # use anyhow::Result;
93//!
94//! # fn main() -> Result<()> {
95//! let sql = SqlBuilder::update_table("company")
96//! .set("salary", "salary + 100")
97//! .and_where_lt("salary", 1_000)
98//! .sql()?;
99//!
100//! assert_eq!("UPDATE company SET salary = salary + 100 WHERE salary < 1000;", &sql);
101//! # Ok(())
102//! # }
103//! ```
104//!
105//! ```
106//! use sql_builder::prelude::*;
107//! # use anyhow::Result;
108//!
109//! # fn main() -> Result<()> {
110//! let sql = SqlBuilder::update_table("company")
111//! .set("salary", "salary + $1")
112//! .set("comment", "e("up $1$$"))
113//! .and_where("salary < ?".bind(&1_000))
114//! .sql()?
115//! .bind_nums(&[&100]);
116//!
117//! assert_eq!("UPDATE company SET salary = salary + 100, comment = 'up 100$' WHERE salary < 1000;", &sql);
118//! # Ok(())
119//! # }
120//! ```
121//!
122//! ## DELETE
123//!
124//! ```
125//! use sql_builder::SqlBuilder;
126//! # use anyhow::Result;
127//!
128//! # fn main() -> Result<()> {
129//! let sql = SqlBuilder::delete_from("company")
130//! .or_where_lt("salary", 1_000)
131//! .or_where_gt("salary", 25_000)
132//! .sql()?;
133//!
134//! assert_eq!("DELETE FROM company WHERE salary < 1000 OR salary > 25000;", &sql);
135//! # Ok(())
136//! # }
137//! ```
138//!
139//! ```
140//! use sql_builder::prelude::*;
141//! use std::collections::HashMap;
142//! # use anyhow::Result;
143//!
144//! # fn main() -> Result<()> {
145//! let mut names: HashMap<&str, &dyn SqlArg> = HashMap::new();
146//! names.insert("min", &1_000);
147//! names.insert("max", &25_000);
148//!
149//! let sql = SqlBuilder::delete_from("company")
150//! .and_where("salary >= :min:")
151//! .and_where("salary <= :max:")
152//! .sql()?
153//! .bind_names(&names);
154//!
155//! assert_eq!("DELETE FROM company WHERE (salary >= 1000) AND (salary <= 25000);", &sql);
156//! # Ok(())
157//! # }
158//! ```
159//!
160//! See [more examples](https://docs.rs/sql-builder/4.0.0/sql_builder/struct.SqlBuilder.html)
161
162pub mod arg;
163pub mod bind;
164pub mod error;
165pub mod name;
166pub mod prelude;
167#[path = "where-builder.rs"]
168pub mod where_builder;
169
170pub use crate::error::SqlBuilderError;
171pub use crate::name::SqlName;
172//pub use crate::where::WhereBuilder;
173use anyhow::Result;
174
175/// Main SQL builder
176#[derive(Clone)]
177pub struct SqlBuilder {
178 statement: Statement,
179 table: String,
180 join_natural: bool,
181 join_operator: JoinOperator,
182 joins: Vec<String>,
183 distinct: bool,
184 fields: Vec<String>,
185 sets: Vec<String>,
186 values: Values,
187 returning: Option<String>,
188 group_by: Vec<String>,
189 having: Option<String>,
190 unions: String,
191 wheres: Vec<String>,
192 order_by: Vec<String>,
193 limit: Option<String>,
194 offset: Option<String>,
195 error: Option<SqlBuilderError>,
196}
197
198/// SQL query statement
199#[derive(Clone)]
200enum Statement {
201 SelectFrom,
202 SelectValues,
203 UpdateTable,
204 InsertInto,
205 DeleteFrom,
206}
207
208/// Operator for JOIN
209#[derive(Clone)]
210enum JoinOperator {
211 Join,
212 LeftJoin,
213 LeftOuterJoin,
214 RightJoin,
215 RightOuterJoin,
216 InnerJoin,
217 CrossJoin,
218}
219
220/// INSERT values
221#[derive(Clone)]
222enum Values {
223 Empty,
224 List(Vec<String>),
225 Select(String),
226}
227
228impl SqlBuilder {
229 /// Default constructor for struct
230 fn default() -> Self {
231 Self {
232 statement: Statement::SelectFrom,
233 table: String::new(),
234 join_natural: false,
235 join_operator: JoinOperator::Join,
236 joins: Vec::new(),
237 distinct: false,
238 fields: Vec::new(),
239 sets: Vec::new(),
240 values: Values::Empty,
241 returning: None,
242 group_by: Vec::new(),
243 having: None,
244 unions: String::new(),
245 wheres: Vec::new(),
246 order_by: Vec::new(),
247 limit: None,
248 offset: None,
249 error: None::<SqlBuilderError>,
250 }
251 }
252
253 /// Create SELECT query.
254 /// You may specify comma separted list of tables.
255 ///
256 /// ```
257 /// # use anyhow::Result;
258 /// use sql_builder::SqlBuilder;
259 ///
260 /// # fn main() -> Result<()> {
261 /// let sql = SqlBuilder::select_from("books")
262 /// .field("title")
263 /// .field("price")
264 /// .and_where("price > 100")
265 /// .and_where_like_left("title", "Harry Potter")
266 /// .sql()?;
267 ///
268 /// assert_eq!("SELECT title, price FROM books WHERE (price > 100) AND (title LIKE 'Harry Potter%');", &sql);
269 /// // add ^^^^^
270 /// // here table
271 /// # Ok(())
272 /// # }
273 /// ```
274 pub fn select_from<S: ToString>(table: S) -> Self {
275 Self {
276 table: table.to_string(),
277 ..Self::default()
278 }
279 }
280
281 /// SELECT from additional table.
282 /// Adds table name to comma separted list of tables.
283 ///
284 /// ```
285 /// # use anyhow::Result;
286 /// use sql_builder::SqlBuilder;
287 ///
288 /// # fn main() -> Result<()> {
289 /// let sql = SqlBuilder::select_from("books")
290 /// .and_table("newspapers")
291 /// .field("title")
292 /// .field("price")
293 /// .and_where("price > 100")
294 /// .sql()?;
295 ///
296 /// assert_eq!("SELECT title, price FROM books, newspapers WHERE price > 100;", &sql);
297 /// // add ^^^^^^^^^^
298 /// // here table
299 /// # Ok(())
300 /// # }
301 /// ```
302 pub fn and_table<S: ToString>(&mut self, table: S) -> &mut Self {
303 self.table = format!("{}, {}", self.table, table.to_string());
304 self
305 }
306
307 /// Create SELECT query without a table.
308 ///
309 /// ```
310 /// # use anyhow::Result;
311 /// use sql_builder::{SqlBuilder, quote};
312 ///
313 /// # fn main() -> Result<()> {
314 /// let sql = SqlBuilder::select_values(&["10", "e("100")])
315 /// .sql()?;
316 ///
317 /// assert_eq!("SELECT 10, '100';", &sql);
318 /// // add ^^^^^^^^^
319 /// // here values
320 /// # Ok(())
321 /// # }
322 /// ```
323 pub fn select_values<S: ToString>(values: &[S]) -> Self {
324 let mut sel = Self {
325 statement: Statement::SelectValues,
326 ..Self::default()
327 };
328 sel.fields(values);
329 sel
330 }
331
332 /// Create INSERT query.
333 ///
334 /// ```
335 /// # use anyhow::Result;
336 /// use sql_builder::{SqlBuilder, quote};
337 ///
338 /// # fn main() -> Result<()> {
339 /// let sql = SqlBuilder::insert_into("books")
340 /// .field("title")
341 /// .field("price")
342 /// .values(&[quote("In Search of Lost Time"), 150.to_string()])
343 /// .values(&["'Don Quixote', 200"])
344 /// .sql()?;
345 ///
346 /// assert_eq!("INSERT INTO books (title, price) VALUES ('In Search of Lost Time', 150), ('Don Quixote', 200);", &sql);
347 /// // add ^^^^^
348 /// // here table
349 /// # Ok(())
350 /// # }
351 /// ```
352 pub fn insert_into<S: ToString>(table: S) -> Self {
353 Self {
354 statement: Statement::InsertInto,
355 table: table.to_string(),
356 ..Self::default()
357 }
358 }
359
360 /// Create UPDATE query.
361 ///
362 /// ```
363 /// # use anyhow::Result;
364 /// use sql_builder::SqlBuilder;
365 ///
366 /// # fn main() -> Result<()> {
367 /// let sql = SqlBuilder::update_table("books")
368 /// .set("price", "price + 10")
369 /// .sql()?;
370 ///
371 /// assert_eq!("UPDATE books SET price = price + 10;", &sql);
372 /// // add ^^^^^
373 /// // here table
374 /// # Ok(())
375 /// # }
376 /// ```
377 pub fn update_table<S: ToString>(table: S) -> Self {
378 Self {
379 statement: Statement::UpdateTable,
380 table: table.to_string(),
381 ..Self::default()
382 }
383 }
384
385 /// Create DELETE query.
386 ///
387 /// ```
388 /// # use anyhow::Result;
389 /// use sql_builder::SqlBuilder;
390 ///
391 /// # fn main() -> Result<()> {
392 /// let sql = SqlBuilder::delete_from("books")
393 /// .and_where("price > 100")
394 /// .sql()?;
395 ///
396 /// assert_eq!("DELETE FROM books WHERE price > 100;", &sql);
397 /// // add ^^^^^
398 /// // here table
399 /// # Ok(())
400 /// # }
401 /// ```
402 pub fn delete_from<S: ToString>(table: S) -> Self {
403 Self {
404 statement: Statement::DeleteFrom,
405 table: table.to_string(),
406 ..Self::default()
407 }
408 }
409
410 /// Use NATURAL JOIN
411 ///
412 /// ```
413 /// # use anyhow::Result;
414 /// use sql_builder::SqlBuilder;
415 ///
416 /// # fn main() -> Result<()> {
417 /// let sql = SqlBuilder::select_from("books")
418 /// .field("title")
419 /// .field("total")
420 /// .natural()
421 /// .join("orders")
422 /// .sql()?;
423 ///
424 /// assert_eq!("SELECT title, total FROM books NATURAL JOIN orders;", &sql);
425 /// // add here ^^^^^^^
426 /// # Ok(())
427 /// # }
428 /// ```
429 pub fn natural(&mut self) -> &mut Self {
430 self.join_natural = true;
431 self
432 }
433
434 /// Use LEFT JOIN
435 ///
436 /// ```
437 /// # use anyhow::Result;
438 /// use sql_builder::SqlBuilder;
439 ///
440 /// # fn main() -> Result<()> {
441 /// let sql = SqlBuilder::select_from("books")
442 /// .field("title")
443 /// .field("total")
444 /// .natural()
445 /// .left()
446 /// .join("orders")
447 /// .sql()?;
448 ///
449 /// assert_eq!("SELECT title, total FROM books NATURAL LEFT JOIN orders;", &sql);
450 /// // add here ^^^^
451 /// # Ok(())
452 /// # }
453 /// ```
454 pub fn left(&mut self) -> &mut Self {
455 self.join_operator = JoinOperator::LeftJoin;
456 self
457 }
458
459 /// Use LEFT OUTER JOIN
460 ///
461 /// ```
462 /// # use anyhow::Result;
463 /// use sql_builder::SqlBuilder;
464 ///
465 /// # fn main() -> Result<()> {
466 /// let sql = SqlBuilder::select_from("books")
467 /// .field("title")
468 /// .field("total")
469 /// .natural()
470 /// .left_outer()
471 /// .join("orders")
472 /// .sql()?;
473 ///
474 /// assert_eq!("SELECT title, total FROM books NATURAL LEFT OUTER JOIN orders;", &sql);
475 /// // add here ^^^^^^^^^^
476 /// # Ok(())
477 /// # }
478 /// ```
479 pub fn left_outer(&mut self) -> &mut Self {
480 self.join_operator = JoinOperator::LeftOuterJoin;
481 self
482 }
483
484 /// Use RIGHT JOIN
485 ///
486 /// ```
487 /// # use anyhow::Result;
488 /// use sql_builder::SqlBuilder;
489 ///
490 /// # fn main() -> Result<()> {
491 /// let sql = SqlBuilder::select_from("books")
492 /// .field("title")
493 /// .field("total")
494 /// .natural()
495 /// .right()
496 /// .join("orders")
497 /// .sql()?;
498 ///
499 /// assert_eq!("SELECT title, total FROM books NATURAL RIGHT JOIN orders;", &sql);
500 /// // add here ^^^^^
501 /// # Ok(())
502 /// # }
503 /// ```
504 pub fn right(&mut self) -> &mut Self {
505 self.join_operator = JoinOperator::RightJoin;
506 self
507 }
508
509 /// Use RIGHT OUTER JOIN
510 ///
511 /// ```
512 /// # use anyhow::Result;
513 /// use sql_builder::SqlBuilder;
514 ///
515 /// # fn main() -> Result<()> {
516 /// let sql = SqlBuilder::select_from("books")
517 /// .field("title")
518 /// .field("total")
519 /// .natural()
520 /// .right_outer()
521 /// .join("orders")
522 /// .sql()?;
523 ///
524 /// assert_eq!("SELECT title, total FROM books NATURAL RIGHT OUTER JOIN orders;", &sql);
525 /// // add here ^^^^^^^^^^^
526 /// # Ok(())
527 /// # }
528 /// ```
529 pub fn right_outer(&mut self) -> &mut Self {
530 self.join_operator = JoinOperator::RightOuterJoin;
531 self
532 }
533
534 /// Use INNER JOIN
535 ///
536 /// ```
537 /// # use anyhow::Result;
538 /// use sql_builder::SqlBuilder;
539 ///
540 /// # fn main() -> Result<()> {
541 /// let sql = SqlBuilder::select_from("books")
542 /// .field("title")
543 /// .field("total")
544 /// .natural()
545 /// .inner()
546 /// .join("orders")
547 /// .sql()?;
548 ///
549 /// assert_eq!("SELECT title, total FROM books NATURAL INNER JOIN orders;", &sql);
550 /// // add here ^^^^^
551 /// # Ok(())
552 /// # }
553 /// ```
554 pub fn inner(&mut self) -> &mut Self {
555 self.join_operator = JoinOperator::InnerJoin;
556 self
557 }
558
559 /// Use CROSS JOIN
560 ///
561 /// ```
562 /// # use anyhow::Result;
563 /// use sql_builder::SqlBuilder;
564 ///
565 /// # fn main() -> Result<()> {
566 /// let sql = SqlBuilder::select_from("books")
567 /// .field("title")
568 /// .field("total")
569 /// .natural()
570 /// .cross()
571 /// .join("orders")
572 /// .sql()?;
573 ///
574 /// assert_eq!("SELECT title, total FROM books NATURAL CROSS JOIN orders;", &sql);
575 /// // add here ^^^^^
576 /// # Ok(())
577 /// # }
578 /// ```
579 pub fn cross(&mut self) -> &mut Self {
580 self.join_operator = JoinOperator::CrossJoin;
581 self
582 }
583
584 /// Join with table.
585 ///
586 /// ```
587 /// #[macro_use] extern crate sql_builder;
588 /// # use anyhow::Result;
589 /// use sql_builder::{SqlBuilder, SqlName};
590 ///
591 /// # fn main() -> Result<()> {
592 /// let sql = SqlBuilder::select_from(name!("books"; "b"))
593 /// .field("b.title")
594 /// .field("s.total")
595 /// .left()
596 /// .join(name!("shops"; "s"))
597 /// .on("b.id = s.book")
598 /// .sql()?;
599 ///
600 /// assert_eq!("SELECT b.title, s.total FROM books AS b LEFT JOIN shops AS s ON b.id = s.book;", &sql);
601 /// // add ^^^^^^^^^^
602 /// // here table
603 /// # Ok(())
604 /// # }
605 /// ```
606 pub fn join<S: ToString>(&mut self, table: S) -> &mut Self {
607 let mut text = match &self.join_operator {
608 JoinOperator::Join if self.join_natural => "NATURAL JOIN ",
609 JoinOperator::Join => "JOIN ",
610 JoinOperator::LeftJoin if self.join_natural => "NATURAL LEFT JOIN ",
611 JoinOperator::LeftJoin => "LEFT JOIN ",
612 JoinOperator::LeftOuterJoin if self.join_natural => "NATURAL LEFT OUTER JOIN ",
613 JoinOperator::LeftOuterJoin => "LEFT OUTER JOIN ",
614 JoinOperator::RightJoin if self.join_natural => "NATURAL RIGHT JOIN ",
615 JoinOperator::RightJoin => "RIGHT JOIN ",
616 JoinOperator::RightOuterJoin if self.join_natural => "NATURAL RIGHT OUTER JOIN ",
617 JoinOperator::RightOuterJoin => "RIGHT OUTER JOIN ",
618 JoinOperator::InnerJoin if self.join_natural => "NATURAL INNER JOIN ",
619 JoinOperator::InnerJoin => "INNER JOIN ",
620 JoinOperator::CrossJoin if self.join_natural => "NATURAL CROSS JOIN ",
621 JoinOperator::CrossJoin => "CROSS JOIN ",
622 }
623 .to_string();
624
625 self.join_natural = false;
626
627 text.push_str(&table.to_string());
628
629 self.joins.push(text);
630 self
631 }
632
633 /// Join constraint to the last JOIN part.
634 ///
635 /// ```
636 /// # use anyhow::Result;
637 /// use sql_builder::SqlBuilder;
638 ///
639 /// # fn main() -> Result<()> {
640 /// let sql = SqlBuilder::select_from("books AS b")
641 /// .field("b.title")
642 /// .field("s.total")
643 /// .join("shops AS s")
644 /// .on("b.id = s.book")
645 /// .sql()?;
646 ///
647 /// assert_eq!("SELECT b.title, s.total FROM books AS b JOIN shops AS s ON b.id = s.book;", &sql);
648 /// // add ^^^^^^^^^^^^^
649 /// // here constraint
650 /// # Ok(())
651 /// # }
652 /// ```
653 pub fn on<S: ToString>(&mut self, constraint: S) -> &mut Self {
654 if let Some(last) = self.joins.last_mut() {
655 last.push_str(" ON ");
656 last.push_str(&constraint.to_string());
657 }
658 self
659 }
660
661 /// Join constraint to the last JOIN part.
662 ///
663 /// ```
664 /// # use anyhow::Result;
665 /// use sql_builder::SqlBuilder;
666 ///
667 /// # fn main() -> Result<()> {
668 /// let sql = SqlBuilder::select_from("books AS b")
669 /// .field("b.title")
670 /// .field("s.total")
671 /// .join("shops AS s")
672 /// .on_eq("b.id", "s.book")
673 /// .sql()?;
674 ///
675 /// assert_eq!("SELECT b.title, s.total FROM books AS b JOIN shops AS s ON b.id = s.book;", &sql);
676 /// // add ^^^^ ^^^^^^
677 /// // here c1 c2
678 /// # Ok(())
679 /// # }
680 /// ```
681 pub fn on_eq<S: ToString, T: ToString>(&mut self, c1: S, c2: T) -> &mut Self {
682 if let Some(last) = self.joins.last_mut() {
683 last.push_str(" ON ");
684 last.push_str(&c1.to_string());
685 last.push_str(" = ");
686 last.push_str(&c2.to_string());
687 }
688 self
689 }
690
691 /// Set DISTINCT for fields.
692 ///
693 /// ```
694 /// # use anyhow::Result;
695 /// use sql_builder::SqlBuilder;
696 ///
697 /// # fn main() -> Result<()> {
698 /// let sql = SqlBuilder::select_from("books")
699 /// .distinct()
700 /// .field("price")
701 /// .sql()?;
702 ///
703 /// assert_eq!("SELECT DISTINCT price FROM books;", &sql);
704 /// // add here ^^^^^^^^
705 /// # Ok(())
706 /// # }
707 /// ```
708 pub fn distinct(&mut self) -> &mut Self {
709 self.distinct = true;
710 self
711 }
712
713 /// Add fields.
714 ///
715 /// ```
716 /// # use anyhow::Result;
717 /// use sql_builder::SqlBuilder;
718 ///
719 /// # fn main() -> Result<()> {
720 /// let sql = SqlBuilder::select_from("books")
721 /// .fields(&["title", "price"])
722 /// .sql()?;
723 ///
724 /// assert_eq!("SELECT title, price FROM books;", &sql);
725 /// // add ^^^^^^^^^^^^
726 /// // here fields
727 /// # Ok(())
728 /// # }
729 /// ```
730 pub fn fields<S: ToString>(&mut self, fields: &[S]) -> &mut Self {
731 let mut fields = fields
732 .iter()
733 .map(|f| (*f).to_string())
734 .collect::<Vec<String>>();
735 self.fields.append(&mut fields);
736 self
737 }
738
739 /// Replace fields.
740 ///
741 /// ```
742 /// # use anyhow::Result;
743 /// use sql_builder::SqlBuilder;
744 /// # #[derive(Default)]
745 /// # struct ReqData { filter: Option<String>, price_min: Option<u64>, price_max: Option<u64>,
746 /// # limit: Option<usize>, offset: Option<usize> }
747 ///
748 /// # fn main() -> Result<()> {
749 /// # let req_data = ReqData::default();
750 /// // Prepare query for total count
751 ///
752 /// let mut db = SqlBuilder::select_from("books");
753 ///
754 /// db.field("COUNT(id)");
755 ///
756 /// if let Some(filter) = &req_data.filter {
757 /// db.and_where_like_any("LOWER(title)", filter.to_lowercase());
758 /// }
759 ///
760 /// if let Some(price_min) = &req_data.price_min {
761 /// db.and_where_ge("price", price_min);
762 /// }
763 ///
764 /// if let Some(price_max) = &req_data.price_max {
765 /// db.and_where_le("price", price_max);
766 /// }
767 ///
768 /// let sql_count = db.sql()?;
769 /// println!("Database query: total_count: {}", &sql_count);
770 ///
771 /// // Prepare query for results
772 ///
773 /// db.set_fields(&["id", "title", "price"]);
774 ///
775 /// if let (Some(limit), Some(offset)) = (req_data.limit, req_data.offset) {
776 /// db.limit(limit).offset(offset);
777 /// }
778 ///
779 /// let sql_results = db.sql()?;
780 /// println!("Database query: results: {}", &sql_results);
781 /// # Ok(())
782 /// # }
783 /// ```
784 pub fn set_fields<S: ToString>(&mut self, fields: &[S]) -> &mut Self {
785 let fields = fields
786 .iter()
787 .map(|f| (*f).to_string())
788 .collect::<Vec<String>>();
789 self.fields = fields;
790 self
791 }
792
793 /// Add field.
794 ///
795 /// ```
796 /// # use anyhow::Result;
797 /// use sql_builder::SqlBuilder;
798 ///
799 /// # fn main() -> Result<()> {
800 /// let sql = SqlBuilder::select_from("books")
801 /// .field("title")
802 /// .field("price")
803 /// .sql()?;
804 ///
805 /// assert_eq!("SELECT title, price FROM books;", &sql);
806 /// // add ^^^^^ ^^^^^
807 /// // here field field
808 /// # Ok(())
809 /// # }
810 /// ```
811 pub fn field<S: ToString>(&mut self, field: S) -> &mut Self {
812 self.fields.push(field.to_string());
813 self
814 }
815
816 /// Replace fields with choosed one.
817 ///
818 /// ```
819 /// # use anyhow::Result;
820 /// use sql_builder::SqlBuilder;
821 /// # #[derive(Default)]
822 /// # struct ReqData { filter: Option<String>, price_min: Option<u64>, price_max: Option<u64>,
823 /// # limit: Option<usize>, offset: Option<usize> }
824 ///
825 /// # fn main() -> Result<()> {
826 /// # let req_data = ReqData::default();
827 /// // Prepare query for total count
828 ///
829 /// let mut db = SqlBuilder::select_from("books");
830 ///
831 /// db.field("COUNT(id)");
832 ///
833 /// if let Some(filter) = &req_data.filter {
834 /// db.and_where_like_any("LOWER(title)", filter.to_lowercase());
835 /// }
836 ///
837 /// if let Some(price_min) = &req_data.price_min {
838 /// db.and_where_ge("price", price_min);
839 /// }
840 ///
841 /// if let Some(price_max) = &req_data.price_max {
842 /// db.and_where_le("price", price_max);
843 /// }
844 ///
845 /// let sql_count = db.sql()?;
846 /// println!("Database query: total_count: {}", &sql_count);
847 ///
848 /// // Prepare query for results
849 ///
850 /// db.set_field("id");
851 /// db.field("title");
852 /// db.field("price");
853 ///
854 /// if let (Some(limit), Some(offset)) = (req_data.limit, req_data.offset) {
855 /// db.limit(limit).offset(offset);
856 /// }
857 ///
858 /// let sql_results = db.sql()?;
859 /// println!("Database query: results: {}", &sql_results);
860 /// # Ok(())
861 /// # }
862 /// ```
863 pub fn set_field<S: ToString>(&mut self, field: S) -> &mut Self {
864 self.fields = vec![field.to_string()];
865 self
866 }
867
868 /// Add COUNT(field).
869 ///
870 /// ```
871 /// # use anyhow::Result;
872 /// use sql_builder::SqlBuilder;
873 ///
874 /// # fn main() -> Result<()> {
875 /// let sql = SqlBuilder::select_from("books")
876 /// .field("title")
877 /// .count("price")
878 /// .group_by("title")
879 /// .sql()?;
880 ///
881 /// assert_eq!("SELECT title, COUNT(price) FROM books GROUP BY title;", &sql);
882 /// // add ^^^^^
883 /// // here field
884 /// # Ok(())
885 /// # }
886 /// ```
887 pub fn count<S: ToString>(&mut self, field: S) -> &mut Self {
888 self.fields.push(format!("COUNT({})", field.to_string()));
889 self
890 }
891
892 /// Add COUNT(field) AS name.
893 ///
894 /// ```
895 /// # use anyhow::Result;
896 /// use sql_builder::SqlBuilder;
897 ///
898 /// # fn main() -> Result<()> {
899 /// let sql = SqlBuilder::select_from("books")
900 /// .field("title")
901 /// .count_as("price", "cnt")
902 /// .group_by("title")
903 /// .sql()?;
904 ///
905 /// assert_eq!("SELECT title, COUNT(price) AS cnt FROM books GROUP BY title;", &sql);
906 /// // add ^^^^^
907 /// // here field
908 /// # Ok(())
909 /// # }
910 /// ```
911 pub fn count_as<S, T>(&mut self, field: S, name: T) -> &mut Self
912 where
913 S: ToString,
914 T: ToString,
915 {
916 self.fields.push(format!(
917 "COUNT({}) AS {}",
918 field.to_string(),
919 name.to_string()
920 ));
921 self
922 }
923
924 /// Add SET part (for UPDATE).
925 ///
926 /// ```
927 /// # use anyhow::Result;
928 /// use sql_builder::SqlBuilder;
929 ///
930 /// # fn main() -> Result<()> {
931 /// let sql = SqlBuilder::update_table("books")
932 /// .set("price", "price + 10")
933 /// .sql()?;
934 ///
935 /// assert_eq!("UPDATE books SET price = price + 10;", &sql);
936 /// // add ^^^^^ ^^^^^^^^^^
937 /// // here field value
938 /// # Ok(())
939 /// # }
940 /// ```
941 pub fn set<S, T>(&mut self, field: S, value: T) -> &mut Self
942 where
943 S: ToString,
944 T: ToString,
945 {
946 let expr = format!("{} = {}", &field.to_string(), &value.to_string());
947 self.sets.push(expr);
948 self
949 }
950
951 /// Add SET part with escaped string value (for UPDATE).
952 ///
953 /// ```
954 /// # use anyhow::Result;
955 /// use sql_builder::SqlBuilder;
956 ///
957 /// # fn main() -> Result<()> {
958 /// let sql = SqlBuilder::update_table("books")
959 /// .set_str("comment", "Don't distribute!")
960 /// .and_where_le("price", "100")
961 /// .sql()?;
962 ///
963 /// assert_eq!("UPDATE books SET comment = 'Don''t distribute!' WHERE price <= 100;", &sql);
964 /// // add ^^^^^^^ ^^^^^^^^^^^^^^^^^^
965 /// // here field value
966 /// # Ok(())
967 /// # }
968 /// ```
969 pub fn set_str<S, T>(&mut self, field: S, value: T) -> &mut Self
970 where
971 S: ToString,
972 T: ToString,
973 {
974 let expr = format!("{} = '{}'", &field.to_string(), &esc(&value.to_string()));
975 self.sets.push(expr);
976 self
977 }
978
979 /// Add VALUES part (for INSERT).
980 ///
981 /// ```
982 /// # use anyhow::Result;
983 /// use sql_builder::{SqlBuilder, quote};
984 ///
985 /// # fn main() -> Result<()> {
986 /// let sql = SqlBuilder::insert_into("books")
987 /// .field("title")
988 /// .field("price")
989 /// .values(&[quote("In Search of Lost Time"), 150.to_string()])
990 /// .values(&["'Don Quixote', 200"])
991 /// .sql()?;
992 ///
993 /// assert_eq!("INSERT INTO books (title, price) VALUES ('In Search of Lost Time', 150), ('Don Quixote', 200);", &sql);
994 /// // add ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^
995 /// // here values values
996 /// # Ok(())
997 /// # }
998 /// ```
999 pub fn values<S: ToString>(&mut self, values: &[S]) -> &mut Self {
1000 let values: Vec<String> = values
1001 .iter()
1002 .map(|v| (*v).to_string())
1003 .collect::<Vec<String>>();
1004 let values = format!("({})", values.join(", "));
1005
1006 match &mut self.values {
1007 Values::Empty => self.values = Values::List(vec![values]),
1008 Values::Select(_) => self.values = Values::List(vec![values]),
1009 Values::List(v) => v.push(values),
1010 };
1011
1012 self
1013 }
1014
1015 /// Add SELECT part (for INSERT).
1016 ///
1017 /// ```
1018 /// # use anyhow::Result;
1019 /// use sql_builder::SqlBuilder;
1020 ///
1021 /// # fn main() -> Result<()> {
1022 /// let query = SqlBuilder::select_from("warehouse")
1023 /// .field("title")
1024 /// .field("preliminary_price * 2")
1025 /// .query()?;
1026 ///
1027 /// assert_eq!("SELECT title, preliminary_price * 2 FROM warehouse", &query);
1028 ///
1029 /// let sql = SqlBuilder::insert_into("books")
1030 /// .field("title")
1031 /// .field("price")
1032 /// .select(&query)
1033 /// .sql()?;
1034 ///
1035 /// assert_eq!("INSERT INTO books (title, price) SELECT title, preliminary_price * 2 FROM warehouse;", &sql);
1036 /// // add ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
1037 /// // here query
1038 /// # Ok(())
1039 /// # }
1040 /// ```
1041 pub fn select<S: ToString>(&mut self, query: S) -> &mut Self {
1042 self.values = Values::Select(query.to_string());
1043 self
1044 }
1045
1046 /// Add RETURNING part.
1047 ///
1048 /// ```
1049 /// # use anyhow::Result;
1050 /// use sql_builder::SqlBuilder;
1051 ///
1052 /// # fn main() -> Result<()> {
1053 /// let sql = SqlBuilder::insert_into("books")
1054 /// .field("title")
1055 /// .field("price")
1056 /// .values(&["'Don Quixote', 200"])
1057 /// .returning("id")
1058 /// .sql()?;
1059 ///
1060 /// assert_eq!("INSERT INTO books (title, price) VALUES ('Don Quixote', 200) RETURNING id;", &sql);
1061 /// // add ^^
1062 /// // here field
1063 /// # Ok(())
1064 /// # }
1065 /// ```
1066 pub fn returning<S: ToString>(&mut self, field: S) -> &mut Self {
1067 self.returning = Some(field.to_string());
1068 self
1069 }
1070
1071 /// Add RETURNING id.
1072 ///
1073 /// ```
1074 /// # use anyhow::Result;
1075 /// use sql_builder::SqlBuilder;
1076 ///
1077 /// # fn main() -> Result<()> {
1078 /// let sql = SqlBuilder::insert_into("books")
1079 /// .field("title")
1080 /// .field("price")
1081 /// .values(&["'Don Quixote', 200"])
1082 /// .returning_id()
1083 /// .sql()?;
1084 ///
1085 /// assert_eq!("INSERT INTO books (title, price) VALUES ('Don Quixote', 200) RETURNING id;", &sql);
1086 /// // add here ^^^^^^^^^^^^
1087 /// # Ok(())
1088 /// # }
1089 /// ```
1090 pub fn returning_id(&mut self) -> &mut Self {
1091 self.returning("id")
1092 }
1093
1094 /// Add GROUP BY part.
1095 ///
1096 /// ```
1097 /// # use anyhow::Result;
1098 /// use sql_builder::SqlBuilder;
1099 ///
1100 /// # fn main() -> Result<()> {
1101 /// let sql = SqlBuilder::select_from("books")
1102 /// .field("price")
1103 /// .field("COUNT(price) AS cnt")
1104 /// .group_by("price")
1105 /// .order_desc("cnt")
1106 /// .sql()?;
1107 ///
1108 /// assert_eq!("SELECT price, COUNT(price) AS cnt FROM books GROUP BY price ORDER BY cnt DESC;", &sql);
1109 /// // add ^^^^^
1110 /// // here field
1111 /// # Ok(())
1112 /// # }
1113 /// ```
1114 pub fn group_by<S: ToString>(&mut self, field: S) -> &mut Self {
1115 self.group_by.push(field.to_string());
1116 self
1117 }
1118
1119 /// Add HAVING condition.
1120 ///
1121 /// ```
1122 /// # use anyhow::Result;
1123 /// use sql_builder::SqlBuilder;
1124 ///
1125 /// # fn main() -> Result<()> {
1126 /// let sql = SqlBuilder::select_from("books")
1127 /// .field("price")
1128 /// .field("COUNT(price) AS cnt")
1129 /// .group_by("price")
1130 /// .having("price > 100")
1131 /// .order_desc("cnt")
1132 /// .sql()?;
1133 ///
1134 /// assert_eq!("SELECT price, COUNT(price) AS cnt FROM books GROUP BY price HAVING price > 100 ORDER BY cnt DESC;", &sql);
1135 /// // add ^^^^^^^^^^^
1136 /// // here cond
1137 /// # Ok(())
1138 /// # }
1139 /// ```
1140 pub fn having<S: ToString>(&mut self, cond: S) -> &mut Self {
1141 self.having = Some(cond.to_string());
1142 self
1143 }
1144
1145 /// Add WHERE condition.
1146 ///
1147 /// ```
1148 /// # use anyhow::Result;
1149 /// use sql_builder::SqlBuilder;
1150 ///
1151 /// # fn main() -> Result<()> {
1152 /// let sql = SqlBuilder::select_from("books")
1153 /// .field("title")
1154 /// .field("price")
1155 /// .and_where("price > 100")
1156 /// .and_where("title LIKE 'Harry Potter%'")
1157 /// .sql()?;
1158 ///
1159 /// assert_eq!("SELECT title, price FROM books WHERE (price > 100) AND (title LIKE 'Harry Potter%');", &sql);
1160 /// // add ^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^
1161 /// // here cond cond
1162 /// # Ok(())
1163 /// # }
1164 /// ```
1165 pub fn and_where<S: ToString>(&mut self, cond: S) -> &mut Self {
1166 // Checks
1167 let cond = cond.to_string();
1168 if cond.is_empty() {
1169 return self.set_error(&SqlBuilderError::NoWhereCond);
1170 }
1171
1172 // Change
1173 self.wheres.push(cond);
1174 self
1175 }
1176
1177 /// Add WHERE condition for equal parts.
1178 ///
1179 /// ```
1180 /// # use anyhow::Result;
1181 /// use sql_builder::{SqlBuilder, quote};
1182 ///
1183 /// # fn main() -> Result<()> {
1184 /// let sql = SqlBuilder::select_from("books")
1185 /// .field("price")
1186 /// .and_where_eq("title", "e("Harry Potter and the Philosopher's Stone"))
1187 /// .sql()?;
1188 ///
1189 /// assert_eq!("SELECT price FROM books WHERE title = 'Harry Potter and the Philosopher''s Stone';", &sql);
1190 /// // add ^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
1191 /// // here field value
1192 /// # Ok(())
1193 /// # }
1194 /// ```
1195 pub fn and_where_eq<S, T>(&mut self, field: S, value: T) -> &mut Self
1196 where
1197 S: ToString,
1198 T: ToString,
1199 {
1200 // Checks
1201 let field = field.to_string();
1202 if field.is_empty() {
1203 return self.set_error(&SqlBuilderError::NoWhereField);
1204 }
1205 let value = value.to_string();
1206 if value.is_empty() {
1207 return self.set_error(&SqlBuilderError::NoWhereValue(field));
1208 }
1209
1210 // Change
1211 let mut cond = field;
1212 cond.push_str(" = ");
1213 cond.push_str(&value);
1214 self.and_where(&cond)
1215 }
1216
1217 /// Add WHERE condition for non-equal parts.
1218 ///
1219 /// ```
1220 /// # use anyhow::Result;
1221 /// use sql_builder::{SqlBuilder, quote};
1222 ///
1223 /// # fn main() -> Result<()> {
1224 /// let sql = SqlBuilder::select_from("books")
1225 /// .field("price")
1226 /// .and_where_ne("title", "e("Harry Potter and the Philosopher's Stone"))
1227 /// .sql()?;
1228 ///
1229 /// assert_eq!("SELECT price FROM books WHERE title <> 'Harry Potter and the Philosopher''s Stone';", &sql);
1230 /// // add ^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
1231 /// // here field value
1232 /// # Ok(())
1233 /// # }
1234 /// ```
1235 pub fn and_where_ne<S, T>(&mut self, field: S, value: T) -> &mut Self
1236 where
1237 S: ToString,
1238 T: ToString,
1239 {
1240 // Checks
1241 let field = field.to_string();
1242 if field.is_empty() {
1243 return self.set_error(&SqlBuilderError::NoWhereField);
1244 }
1245 let value = value.to_string();
1246 if value.is_empty() {
1247 return self.set_error(&SqlBuilderError::NoWhereValue(field));
1248 }
1249
1250 // Change
1251 let mut cond = field;
1252 cond.push_str(" <> ");
1253 cond.push_str(&value);
1254 self.and_where(&cond)
1255 }
1256
1257 /// Add WHERE condition for field greater than value.
1258 ///
1259 /// ```
1260 /// # use anyhow::Result;
1261 /// use sql_builder::SqlBuilder;
1262 ///
1263 /// # fn main() -> Result<()> {
1264 /// let sql = SqlBuilder::select_from("books")
1265 /// .field("title")
1266 /// .field("price")
1267 /// .and_where_gt("price", 300)
1268 /// .sql()?;
1269 ///
1270 /// assert_eq!("SELECT title, price FROM books WHERE price > 300;", &sql);
1271 /// // add ^^^^^ ^^^
1272 /// // here field value
1273 /// # Ok(())
1274 /// # }
1275 /// ```
1276 pub fn and_where_gt<S, T>(&mut self, field: S, value: T) -> &mut Self
1277 where
1278 S: ToString,
1279 T: ToString,
1280 {
1281 // Checks
1282 let field = field.to_string();
1283 if field.is_empty() {
1284 return self.set_error(&SqlBuilderError::NoWhereField);
1285 }
1286 let value = value.to_string();
1287 if value.is_empty() {
1288 return self.set_error(&SqlBuilderError::NoWhereValue(field));
1289 }
1290
1291 // Change
1292 let mut cond = field;
1293 cond.push_str(" > ");
1294 cond.push_str(&value);
1295 self.and_where(&cond)
1296 }
1297
1298 /// Add WHERE condition for field not less than value.
1299 ///
1300 /// ```
1301 /// # use anyhow::Result;
1302 /// use sql_builder::SqlBuilder;
1303 ///
1304 /// # fn main() -> Result<()> {
1305 /// let sql = SqlBuilder::select_from("books")
1306 /// .field("title")
1307 /// .field("price")
1308 /// .and_where_ge("price", 300)
1309 /// .sql()?;
1310 ///
1311 /// assert_eq!("SELECT title, price FROM books WHERE price >= 300;", &sql);
1312 /// // add ^^^^^ ^^^
1313 /// // here field value
1314 /// # Ok(())
1315 /// # }
1316 /// ```
1317 pub fn and_where_ge<S, T>(&mut self, field: S, value: T) -> &mut Self
1318 where
1319 S: ToString,
1320 T: ToString,
1321 {
1322 // Checks
1323 let field = field.to_string();
1324 if field.is_empty() {
1325 return self.set_error(&SqlBuilderError::NoWhereField);
1326 }
1327 let value = value.to_string();
1328 if value.is_empty() {
1329 return self.set_error(&SqlBuilderError::NoWhereValue(field));
1330 }
1331
1332 // Change
1333 let mut cond = field;
1334 cond.push_str(" >= ");
1335 cond.push_str(&value);
1336 self.and_where(&cond)
1337 }
1338
1339 /// Add WHERE condition for field less than value.
1340 ///
1341 /// ```
1342 /// # use anyhow::Result;
1343 /// use sql_builder::SqlBuilder;
1344 ///
1345 /// # fn main() -> Result<()> {
1346 /// let sql = SqlBuilder::select_from("books")
1347 /// .field("title")
1348 /// .field("price")
1349 /// .and_where_lt("price", 300)
1350 /// .sql()?;
1351 ///
1352 /// assert_eq!("SELECT title, price FROM books WHERE price < 300;", &sql);
1353 /// // add ^^^^^ ^^^
1354 /// // here field value
1355 /// # Ok(())
1356 /// # }
1357 /// ```
1358 pub fn and_where_lt<S, T>(&mut self, field: S, value: T) -> &mut Self
1359 where
1360 S: ToString,
1361 T: ToString,
1362 {
1363 // Checks
1364 let field = field.to_string();
1365 if field.is_empty() {
1366 return self.set_error(&SqlBuilderError::NoWhereField);
1367 }
1368 let value = value.to_string();
1369 if value.is_empty() {
1370 return self.set_error(&SqlBuilderError::NoWhereValue(field));
1371 }
1372
1373 // Change
1374 let mut cond = field;
1375 cond.push_str(" < ");
1376 cond.push_str(&value);
1377 self.and_where(&cond)
1378 }
1379
1380 /// Add WHERE condition for field not greater than value.
1381 ///
1382 /// ```
1383 /// # use anyhow::Result;
1384 /// use sql_builder::SqlBuilder;
1385 ///
1386 /// # fn main() -> Result<()> {
1387 /// let sql = SqlBuilder::select_from("books")
1388 /// .field("title")
1389 /// .field("price")
1390 /// .and_where_le("price", 300)
1391 /// .sql()?;
1392 ///
1393 /// assert_eq!("SELECT title, price FROM books WHERE price <= 300;", &sql);
1394 /// // add ^^^^^ ^^^
1395 /// // here field value
1396 /// # Ok(())
1397 /// # }
1398 /// ```
1399 pub fn and_where_le<S, T>(&mut self, field: S, value: T) -> &mut Self
1400 where
1401 S: ToString,
1402 T: ToString,
1403 {
1404 // Checks
1405 let field = field.to_string();
1406 if field.is_empty() {
1407 return self.set_error(&SqlBuilderError::NoWhereField);
1408 }
1409 let value = value.to_string();
1410 if value.is_empty() {
1411 return self.set_error(&SqlBuilderError::NoWhereValue(field));
1412 }
1413
1414 // Change
1415 let mut cond = field;
1416 cond.push_str(" <= ");
1417 cond.push_str(&value);
1418 self.and_where(&cond)
1419 }
1420
1421 /// Add WHERE LIKE condition.
1422 ///
1423 /// ```
1424 /// # use anyhow::Result;
1425 /// use sql_builder::SqlBuilder;
1426 ///
1427 /// # fn main() -> Result<()> {
1428 /// let sql = SqlBuilder::select_from("books")
1429 /// .field("price")
1430 /// .and_where_like("title", "%Philosopher's%")
1431 /// .sql()?;
1432 ///
1433 /// assert_eq!("SELECT price FROM books WHERE title LIKE '%Philosopher''s%';", &sql);
1434 /// // add ^^^^^ ^^^^^^^^^^^^^^^^
1435 /// // here field mask
1436 /// # Ok(())
1437 /// # }
1438 /// ```
1439 pub fn and_where_like<S, T>(&mut self, field: S, mask: T) -> &mut Self
1440 where
1441 S: ToString,
1442 T: ToString,
1443 {
1444 // Checks
1445 let field = field.to_string();
1446 if field.is_empty() {
1447 return self.set_error(&SqlBuilderError::NoWhereField);
1448 }
1449
1450 // Change
1451 let mut cond = field;
1452 cond.push_str(" LIKE '");
1453 cond.push_str(&esc(&mask.to_string()));
1454 cond.push('\'');
1455 self.and_where(&cond)
1456 }
1457
1458 /// Add WHERE LIKE %condition.
1459 ///
1460 /// ```
1461 /// # use anyhow::Result;
1462 /// use sql_builder::SqlBuilder;
1463 ///
1464 /// # fn main() -> Result<()> {
1465 /// let sql = SqlBuilder::select_from("books")
1466 /// .field("price")
1467 /// .and_where_like_right("title", "Stone")
1468 /// .sql()?;
1469 ///
1470 /// assert_eq!("SELECT price FROM books WHERE title LIKE '%Stone';", &sql);
1471 /// // add ^^^^^ ^^^^^
1472 /// // here field mask
1473 /// # Ok(())
1474 /// # }
1475 /// ```
1476 pub fn and_where_like_right<S, T>(&mut self, field: S, mask: T) -> &mut Self
1477 where
1478 S: ToString,
1479 T: ToString,
1480 {
1481 // Checks
1482 let field = field.to_string();
1483 if field.is_empty() {
1484 return self.set_error(&SqlBuilderError::NoWhereField);
1485 }
1486
1487 // Change
1488 let mut cond = field;
1489 cond.push_str(" LIKE '%");
1490 cond.push_str(&esc(&mask.to_string()));
1491 cond.push('\'');
1492 self.and_where(&cond)
1493 }
1494
1495 /// Add WHERE LIKE condition%.
1496 ///
1497 /// ```
1498 /// # use anyhow::Result;
1499 /// use sql_builder::SqlBuilder;
1500 ///
1501 /// # fn main() -> Result<()> {
1502 /// let sql = SqlBuilder::select_from("books")
1503 /// .field("price")
1504 /// .and_where_like_left("title", "Harry")
1505 /// .sql()?;
1506 ///
1507 /// assert_eq!("SELECT price FROM books WHERE title LIKE 'Harry%';", &sql);
1508 /// // add ^^^^^ ^^^^^
1509 /// // here field mask
1510 /// # Ok(())
1511 /// # }
1512 /// ```
1513 pub fn and_where_like_left<S, T>(&mut self, field: S, mask: T) -> &mut Self
1514 where
1515 S: ToString,
1516 T: ToString,
1517 {
1518 // Checks
1519 let field = field.to_string();
1520 if field.is_empty() {
1521 return self.set_error(&SqlBuilderError::NoWhereField);
1522 }
1523
1524 // Change
1525 let mut cond = field;
1526 cond.push_str(" LIKE '");
1527 cond.push_str(&esc(&mask.to_string()));
1528 cond.push_str("%'");
1529 self.and_where(&cond)
1530 }
1531
1532 /// Add WHERE LIKE %condition%.
1533 ///
1534 /// ```
1535 /// # use anyhow::Result;
1536 /// use sql_builder::SqlBuilder;
1537 ///
1538 /// # fn main() -> Result<()> {
1539 /// let sql = SqlBuilder::select_from("books")
1540 /// .field("price")
1541 /// .and_where_like_any("title", " and ")
1542 /// .sql()?;
1543 ///
1544 /// assert_eq!("SELECT price FROM books WHERE title LIKE '% and %';", &sql);
1545 /// // add ^^^^^ ^^^^^
1546 /// // here field mask
1547 /// # Ok(())
1548 /// # }
1549 /// ```
1550 pub fn and_where_like_any<S, T>(&mut self, field: S, mask: T) -> &mut Self
1551 where
1552 S: ToString,
1553 T: ToString,
1554 {
1555 // Checks
1556 let field = field.to_string();
1557 if field.is_empty() {
1558 return self.set_error(&SqlBuilderError::NoWhereField);
1559 }
1560
1561 // Change
1562 let mut cond = field;
1563 cond.push_str(" LIKE '%");
1564 cond.push_str(&esc(&mask.to_string()));
1565 cond.push_str("%'");
1566 self.and_where(&cond)
1567 }
1568
1569 /// Add WHERE NOT LIKE condition.
1570 ///
1571 /// ```
1572 /// # use anyhow::Result;
1573 /// use sql_builder::SqlBuilder;
1574 ///
1575 /// # fn main() -> Result<()> {
1576 /// let sql = SqlBuilder::select_from("books")
1577 /// .field("title")
1578 /// .and_where_not_like("title", "%Alice's%")
1579 /// .sql()?;
1580 ///
1581 /// assert_eq!("SELECT title FROM books WHERE title NOT LIKE '%Alice''s%';", &sql);
1582 /// // add ^^^^^ ^^^^^^^^^^
1583 /// // here field mask
1584 /// # Ok(())
1585 /// # }
1586 /// ```
1587 pub fn and_where_not_like<S, T>(&mut self, field: S, mask: T) -> &mut Self
1588 where
1589 S: ToString,
1590 T: ToString,
1591 {
1592 // Checks
1593 let field = field.to_string();
1594 if field.is_empty() {
1595 return self.set_error(&SqlBuilderError::NoWhereField);
1596 }
1597 let mask = mask.to_string();
1598 if mask.is_empty() {
1599 return self.set_error(&SqlBuilderError::NoWhereValue(field));
1600 }
1601
1602 // Change
1603 let mut cond = field;
1604 cond.push_str(" NOT LIKE '");
1605 cond.push_str(&esc(&mask));
1606 cond.push('\'');
1607 self.and_where(&cond)
1608 }
1609
1610 /// Add WHERE NOT LIKE %condition.
1611 ///
1612 /// ```
1613 /// # use anyhow::Result;
1614 /// use sql_builder::SqlBuilder;
1615 ///
1616 /// # fn main() -> Result<()> {
1617 /// let sql = SqlBuilder::select_from("books")
1618 /// .field("price")
1619 /// .and_where_not_like_right("title", "Stone")
1620 /// .sql()?;
1621 ///
1622 /// assert_eq!("SELECT price FROM books WHERE title NOT LIKE '%Stone';", &sql);
1623 /// // add ^^^^^ ^^^^^
1624 /// // here field mask
1625 /// # Ok(())
1626 /// # }
1627 /// ```
1628 pub fn and_where_not_like_right<S, T>(&mut self, field: S, mask: T) -> &mut Self
1629 where
1630 S: ToString,
1631 T: ToString,
1632 {
1633 // Checks
1634 let field = field.to_string();
1635 if field.is_empty() {
1636 return self.set_error(&SqlBuilderError::NoWhereField);
1637 }
1638 let mask = mask.to_string();
1639 if mask.is_empty() {
1640 return self.set_error(&SqlBuilderError::NoWhereValue(field));
1641 }
1642
1643 // Change
1644 let mut cond = field;
1645 cond.push_str(" NOT LIKE '%");
1646 cond.push_str(&esc(&mask));
1647 cond.push('\'');
1648 self.and_where(&cond)
1649 }
1650
1651 /// Add WHERE NOT LIKE condition%.
1652 ///
1653 /// ```
1654 /// # use anyhow::Result;
1655 /// use sql_builder::SqlBuilder;
1656 ///
1657 /// # fn main() -> Result<()> {
1658 /// let sql = SqlBuilder::select_from("books")
1659 /// .field("price")
1660 /// .and_where_not_like_left("title", "Harry")
1661 /// .sql()?;
1662 ///
1663 /// assert_eq!("SELECT price FROM books WHERE title NOT LIKE 'Harry%';", &sql);
1664 /// // add ^^^^^ ^^^^^
1665 /// // here field mask
1666 /// # Ok(())
1667 /// # }
1668 /// ```
1669 pub fn and_where_not_like_left<S, T>(&mut self, field: S, mask: T) -> &mut Self
1670 where
1671 S: ToString,
1672 T: ToString,
1673 {
1674 // Checks
1675 let field = field.to_string();
1676 if field.is_empty() {
1677 return self.set_error(&SqlBuilderError::NoWhereField);
1678 }
1679 let mask = mask.to_string();
1680 if mask.is_empty() {
1681 return self.set_error(&SqlBuilderError::NoWhereValue(field));
1682 }
1683
1684 // Change
1685 let mut cond = field;
1686 cond.push_str(" NOT LIKE '");
1687 cond.push_str(&esc(&mask));
1688 cond.push_str("%'");
1689 self.and_where(&cond)
1690 }
1691
1692 /// Add WHERE NOT LIKE %condition%.
1693 ///
1694 /// ```
1695 /// # use anyhow::Result;
1696 /// use sql_builder::SqlBuilder;
1697 ///
1698 /// # fn main() -> Result<()> {
1699 /// let sql = SqlBuilder::select_from("books")
1700 /// .field("price")
1701 /// .and_where_not_like_any("title", " and ")
1702 /// .sql()?;
1703 ///
1704 /// assert_eq!("SELECT price FROM books WHERE title NOT LIKE '% and %';", &sql);
1705 /// // add ^^^^^ ^^^^^
1706 /// // here field mask
1707 /// # Ok(())
1708 /// # }
1709 /// ```
1710 pub fn and_where_not_like_any<S, T>(&mut self, field: S, mask: T) -> &mut Self
1711 where
1712 S: ToString,
1713 T: ToString,
1714 {
1715 // Checks
1716 let field = field.to_string();
1717 if field.is_empty() {
1718 return self.set_error(&SqlBuilderError::NoWhereField);
1719 }
1720 let mask = mask.to_string();
1721 if mask.is_empty() {
1722 return self.set_error(&SqlBuilderError::NoWhereValue(field));
1723 }
1724
1725 // Change
1726 let mut cond = field;
1727 cond.push_str(" NOT LIKE '%");
1728 cond.push_str(&esc(&mask));
1729 cond.push_str("%'");
1730 self.and_where(&cond)
1731 }
1732
1733 /// Add WHERE IS NULL condition.
1734 ///
1735 /// ```
1736 /// # use anyhow::Result;
1737 /// use sql_builder::SqlBuilder;
1738 ///
1739 /// # fn main() -> Result<()> {
1740 /// let sql = SqlBuilder::select_from("books")
1741 /// .field("title")
1742 /// .and_where_is_null("price")
1743 /// .sql()?;
1744 ///
1745 /// assert_eq!("SELECT title FROM books WHERE price IS NULL;", &sql);
1746 /// // add ^^^^^
1747 /// // here field
1748 /// # Ok(())
1749 /// # }
1750 /// ```
1751 pub fn and_where_is_null<S: ToString>(&mut self, field: S) -> &mut Self {
1752 // Checks
1753 let field = field.to_string();
1754 if field.is_empty() {
1755 return self.set_error(&SqlBuilderError::NoWhereField);
1756 }
1757
1758 // Change
1759 let mut cond = field;
1760 cond.push_str(" IS NULL");
1761 self.and_where(&cond)
1762 }
1763
1764 /// Add WHERE IS NOT NULL condition.
1765 ///
1766 /// ```
1767 /// # use anyhow::Result;
1768 /// use sql_builder::SqlBuilder;
1769 ///
1770 /// # fn main() -> Result<()> {
1771 /// let sql = SqlBuilder::select_from("books")
1772 /// .field("title")
1773 /// .and_where_is_not_null("price")
1774 /// .sql()?;
1775 ///
1776 /// assert_eq!("SELECT title FROM books WHERE price IS NOT NULL;", &sql);
1777 /// // add ^^^^^
1778 /// // here field
1779 /// # Ok(())
1780 /// # }
1781 /// ```
1782 pub fn and_where_is_not_null<S: ToString>(&mut self, field: S) -> &mut Self {
1783 // Checks
1784 let field = field.to_string();
1785 if field.is_empty() {
1786 return self.set_error(&SqlBuilderError::NoWhereField);
1787 }
1788
1789 // Change
1790 let mut cond = field;
1791 cond.push_str(" IS NOT NULL");
1792 self.and_where(&cond)
1793 }
1794
1795 /// Add WHERE field IN (list).
1796 ///
1797 /// ```
1798 /// # use anyhow::Result;
1799 /// use sql_builder::{SqlBuilder, quote};
1800 ///
1801 /// # fn main() -> Result<()> {
1802 /// let sql = SqlBuilder::select_from("books")
1803 /// .field("title")
1804 /// .field("price")
1805 /// .and_where_in("title", &[quote("G"), quote("L"), quote("t")])
1806 /// .sql()?;
1807 ///
1808 /// assert_eq!("SELECT title, price FROM books WHERE title IN ('G', 'L', 't');", &sql);
1809 /// // add ^^^^^ ^^^^^^^^^^^^^
1810 /// // here field list
1811 /// # Ok(())
1812 /// # }
1813 /// ```
1814 pub fn and_where_in<S, T>(&mut self, field: S, list: &[T]) -> &mut Self
1815 where
1816 S: ToString,
1817 T: ToString,
1818 {
1819 // Checks
1820 let field = field.to_string();
1821 if field.is_empty() {
1822 return self.set_error(&SqlBuilderError::NoWhereField);
1823 }
1824 if list.is_empty() {
1825 return self.set_error(&SqlBuilderError::NoWhereList(field));
1826 }
1827
1828 // Change
1829 let list: Vec<String> = list
1830 .iter()
1831 .map(|v| (*v).to_string())
1832 .collect::<Vec<String>>();
1833 let list = list.join(", ");
1834
1835 let mut cond = field;
1836 cond.push_str(" IN (");
1837 cond.push_str(&list);
1838 cond.push(')');
1839 self.and_where(&cond)
1840 }
1841
1842 /// Add WHERE field IN (string list).
1843 ///
1844 /// ```
1845 /// # use anyhow::Result;
1846 /// use sql_builder::{SqlBuilder, quote};
1847 ///
1848 /// # fn main() -> Result<()> {
1849 /// let sql = SqlBuilder::select_from("books")
1850 /// .field("title")
1851 /// .field("price")
1852 /// .and_where_in_quoted("title", &["G", "L", "t"])
1853 /// .sql()?;
1854 ///
1855 /// assert_eq!("SELECT title, price FROM books WHERE title IN ('G', 'L', 't');", &sql);
1856 /// // add ^^^^^ ^^^^^^^^^^^^^
1857 /// // here field list
1858 /// # Ok(())
1859 /// # }
1860 /// ```
1861 pub fn and_where_in_quoted<S, T>(&mut self, field: S, list: &[T]) -> &mut Self
1862 where
1863 S: ToString,
1864 T: ToString,
1865 {
1866 // Checks
1867 let field = field.to_string();
1868 if field.is_empty() {
1869 return self.set_error(&SqlBuilderError::NoWhereField);
1870 }
1871 if list.is_empty() {
1872 return self.set_error(&SqlBuilderError::NoWhereList(field));
1873 }
1874
1875 // Change
1876 let list: Vec<String> = list
1877 .iter()
1878 .map(|v| quote((*v).to_string()))
1879 .collect::<Vec<String>>();
1880 let list = list.join(", ");
1881
1882 let mut cond = field;
1883 cond.push_str(" IN (");
1884 cond.push_str(&list);
1885 cond.push(')');
1886 self.and_where(&cond)
1887 }
1888
1889 /// Add WHERE field NOT IN (list).
1890 ///
1891 /// ```
1892 /// # use anyhow::Result;
1893 /// use sql_builder::{SqlBuilder, quote};
1894 ///
1895 /// # fn main() -> Result<()> {
1896 /// let sql = SqlBuilder::select_from("books")
1897 /// .field("title")
1898 /// .field("price")
1899 /// .and_where_not_in("title", &[quote("G"), quote("L"), quote("t")])
1900 /// .sql()?;
1901 ///
1902 /// assert_eq!("SELECT title, price FROM books WHERE title NOT IN ('G', 'L', 't');", &sql);
1903 /// // add ^^^^^ ^^^^^^^^^^^^^
1904 /// // here field list
1905 /// # Ok(())
1906 /// # }
1907 /// ```
1908 pub fn and_where_not_in<S, T>(&mut self, field: S, list: &[T]) -> &mut Self
1909 where
1910 S: ToString,
1911 T: ToString,
1912 {
1913 // Checks
1914 let field = field.to_string();
1915 if field.is_empty() {
1916 return self.set_error(&SqlBuilderError::NoWhereField);
1917 }
1918 if list.is_empty() {
1919 return self.set_error(&SqlBuilderError::NoWhereList(field));
1920 }
1921
1922 // Change
1923 let list: Vec<String> = list
1924 .iter()
1925 .map(|v| (*v).to_string())
1926 .collect::<Vec<String>>();
1927 let list = list.join(", ");
1928
1929 let mut cond = field;
1930 cond.push_str(" NOT IN (");
1931 cond.push_str(&list);
1932 cond.push(')');
1933 self.and_where(&cond)
1934 }
1935
1936 /// Add WHERE field NOT IN (string list).
1937 ///
1938 /// ```
1939 /// # use anyhow::Result;
1940 /// use sql_builder::{SqlBuilder, quote};
1941 ///
1942 /// # fn main() -> Result<()> {
1943 /// let sql = SqlBuilder::select_from("books")
1944 /// .field("title")
1945 /// .field("price")
1946 /// .and_where_not_in_quoted("title", &["G", "L", "t"])
1947 /// .sql()?;
1948 ///
1949 /// assert_eq!("SELECT title, price FROM books WHERE title NOT IN ('G', 'L', 't');", &sql);
1950 /// // add ^^^^^ ^^^^^^^^^^^^^
1951 /// // here field list
1952 /// # Ok(())
1953 /// # }
1954 /// ```
1955 pub fn and_where_not_in_quoted<S, T>(&mut self, field: S, list: &[T]) -> &mut Self
1956 where
1957 S: ToString,
1958 T: ToString,
1959 {
1960 // Checks
1961 let field = field.to_string();
1962 if field.is_empty() {
1963 return self.set_error(&SqlBuilderError::NoWhereField);
1964 }
1965 if list.is_empty() {
1966 return self.set_error(&SqlBuilderError::NoWhereList(field));
1967 }
1968
1969 // Change
1970 let list: Vec<String> = list
1971 .iter()
1972 .map(|v| quote((*v).to_string()))
1973 .collect::<Vec<String>>();
1974 let list = list.join(", ");
1975
1976 let mut cond = field;
1977 cond.push_str(" NOT IN (");
1978 cond.push_str(&list);
1979 cond.push(')');
1980 self.and_where(&cond)
1981 }
1982
1983 /// Add WHERE field IN (query).
1984 ///
1985 /// ```
1986 /// # use anyhow::Result;
1987 /// use sql_builder::{SqlBuilder, quote};
1988 ///
1989 /// # fn main() -> Result<()> {
1990 /// let query = SqlBuilder::select_from("shop")
1991 /// .field("title")
1992 /// .and_where("sold")
1993 /// .query()?;
1994 ///
1995 /// assert_eq!("SELECT title FROM shop WHERE sold", &query);
1996 ///
1997 /// let sql = SqlBuilder::select_from("books")
1998 /// .field("title")
1999 /// .field("price")
2000 /// .and_where_in_query("title", &query)
2001 /// .sql()?;
2002 ///
2003 /// assert_eq!("SELECT title, price FROM books WHERE title IN (SELECT title FROM shop WHERE sold);", &sql);
2004 /// // add ^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2005 /// // here field query
2006 /// # Ok(())
2007 /// # }
2008 /// ```
2009 pub fn and_where_in_query<S, T>(&mut self, field: S, query: T) -> &mut Self
2010 where
2011 S: ToString,
2012 T: ToString,
2013 {
2014 // Checks
2015 let field = field.to_string();
2016 if field.is_empty() {
2017 return self.set_error(&SqlBuilderError::NoWhereField);
2018 }
2019 let query = query.to_string();
2020 if query.is_empty() {
2021 return self.set_error(&SqlBuilderError::NoWhereQuery(field));
2022 }
2023
2024 // Change
2025 let mut cond = field;
2026 cond.push_str(" IN (");
2027 cond.push_str(&query);
2028 cond.push(')');
2029 self.and_where(&cond)
2030 }
2031
2032 /// Add WHERE field NOT IN (query).
2033 ///
2034 /// ```
2035 /// # use anyhow::Result;
2036 /// use sql_builder::{SqlBuilder, quote};
2037 ///
2038 /// # fn main() -> Result<()> {
2039 /// let query = SqlBuilder::select_from("shop")
2040 /// .field("title")
2041 /// .and_where("sold")
2042 /// .query()?;
2043 ///
2044 /// assert_eq!("SELECT title FROM shop WHERE sold", &query);
2045 ///
2046 /// let sql = SqlBuilder::select_from("books")
2047 /// .field("title")
2048 /// .field("price")
2049 /// .and_where_not_in_query("title", &query)
2050 /// .sql()?;
2051 ///
2052 /// assert_eq!("SELECT title, price FROM books WHERE title NOT IN (SELECT title FROM shop WHERE sold);", &sql);
2053 /// // add ^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2054 /// // here field query
2055 /// # Ok(())
2056 /// # }
2057 /// ```
2058 pub fn and_where_not_in_query<S, T>(&mut self, field: S, query: T) -> &mut Self
2059 where
2060 S: ToString,
2061 T: ToString,
2062 {
2063 // Checks
2064 let field = field.to_string();
2065 if field.is_empty() {
2066 return self.set_error(&SqlBuilderError::NoWhereField);
2067 }
2068 let query = query.to_string();
2069 if query.is_empty() {
2070 return self.set_error(&SqlBuilderError::NoWhereQuery(field));
2071 }
2072
2073 // Change
2074 let mut cond = field;
2075 cond.push_str(" NOT IN (");
2076 cond.push_str(&query);
2077 cond.push(')');
2078 self.and_where(&cond)
2079 }
2080
2081 /// Add WHERE field BETWEEN values.
2082 ///
2083 /// ```
2084 /// # use anyhow::Result;
2085 /// use sql_builder::SqlBuilder;
2086 ///
2087 /// # fn main() -> Result<()> {
2088 /// let sql = SqlBuilder::select_from("books")
2089 /// .field("title")
2090 /// .field("price")
2091 /// .and_where_between("price", 10_000, 20_000)
2092 /// .sql()?;
2093 ///
2094 /// assert_eq!("SELECT title, price FROM books WHERE price BETWEEN 10000 AND 20000;", &sql);
2095 /// // add ^^^^^ ^^^^^ ^^^^^
2096 /// // here field min max
2097 /// # Ok(())
2098 /// # }
2099 /// ```
2100 pub fn and_where_between<S, T, U>(&mut self, field: S, min: T, max: U) -> &mut Self
2101 where
2102 S: ToString,
2103 T: ToString,
2104 U: ToString,
2105 {
2106 // Checks
2107 let field = field.to_string();
2108 if field.is_empty() {
2109 return self.set_error(&SqlBuilderError::NoWhereField);
2110 }
2111 let min = min.to_string();
2112 if min.is_empty() {
2113 return self.set_error(&SqlBuilderError::NoWhereValue(field));
2114 }
2115 let max = max.to_string();
2116 if max.is_empty() {
2117 return self.set_error(&SqlBuilderError::NoWhereValue(field));
2118 }
2119
2120 // Change
2121 let mut cond = field;
2122 cond.push_str(" BETWEEN ");
2123 cond.push_str(&min);
2124 cond.push_str(" AND ");
2125 cond.push_str(&max);
2126 self.and_where(&cond)
2127 }
2128
2129 /// Add WHERE field NOT BETWEEN values.
2130 ///
2131 /// ```
2132 /// # use anyhow::Result;
2133 /// use sql_builder::SqlBuilder;
2134 ///
2135 /// # fn main() -> Result<()> {
2136 /// let sql = SqlBuilder::select_from("books")
2137 /// .field("title")
2138 /// .field("price")
2139 /// .and_where_not_between("price", 10_000, 20_000)
2140 /// .sql()?;
2141 ///
2142 /// assert_eq!("SELECT title, price FROM books WHERE price NOT BETWEEN 10000 AND 20000;", &sql);
2143 /// // add ^^^^^ ^^^^^ ^^^^^
2144 /// // here field min max
2145 /// # Ok(())
2146 /// # }
2147 /// ```
2148 pub fn and_where_not_between<S, T, U>(&mut self, field: S, min: T, max: U) -> &mut Self
2149 where
2150 S: ToString,
2151 T: ToString,
2152 U: ToString,
2153 {
2154 // Checks
2155 let field = field.to_string();
2156 if field.is_empty() {
2157 return self.set_error(&SqlBuilderError::NoWhereField);
2158 }
2159 let min = min.to_string();
2160 if min.is_empty() {
2161 return self.set_error(&SqlBuilderError::NoWhereValue(field));
2162 }
2163 let max = max.to_string();
2164 if max.is_empty() {
2165 return self.set_error(&SqlBuilderError::NoWhereValue(field));
2166 }
2167
2168 // Change
2169 let mut cond = field;
2170 cond.push_str(" NOT BETWEEN ");
2171 cond.push_str(&min);
2172 cond.push_str(" AND ");
2173 cond.push_str(&max);
2174 self.and_where(&cond)
2175 }
2176
2177 /// Add OR condition to the last WHERE condition.
2178 ///
2179 /// ```
2180 /// # use anyhow::Result;
2181 /// use sql_builder::SqlBuilder;
2182 ///
2183 /// # fn main() -> Result<()> {
2184 /// let sql = SqlBuilder::select_from("books")
2185 /// .field("title")
2186 /// .field("price")
2187 /// .and_where("price < 10")
2188 /// .or_where("price > 1000")
2189 /// .sql()?;
2190 ///
2191 /// assert_eq!("SELECT title, price FROM books WHERE price < 10 OR price > 1000;", &sql);
2192 /// // add ^^^^^^^^^^^^
2193 /// // here cond
2194 /// # Ok(())
2195 /// # }
2196 /// ```
2197 pub fn or_where<S: ToString>(&mut self, cond: S) -> &mut Self {
2198 // Checks
2199 let cond = cond.to_string();
2200 if cond.is_empty() {
2201 return self.set_error(&SqlBuilderError::NoWhereCond);
2202 }
2203
2204 // Change
2205 if self.wheres.is_empty() {
2206 self.wheres.push(cond);
2207 } else if let Some(last) = self.wheres.last_mut() {
2208 last.push_str(" OR ");
2209 last.push_str(&cond);
2210 }
2211 self
2212 }
2213
2214 /// Add OR condition of equal parts to the last WHERE condition.
2215 ///
2216 /// ```
2217 /// # use anyhow::Result;
2218 /// use sql_builder::{SqlBuilder, quote};
2219 ///
2220 /// # fn main() -> Result<()> {
2221 /// let sql = SqlBuilder::select_from("books")
2222 /// .field("price")
2223 /// .and_where_eq("title", "e("Harry Potter and the Philosopher's Stone"))
2224 /// .or_where_eq("title", "e("Harry Potter and the Chamber of Secrets"))
2225 /// .sql()?;
2226 ///
2227 /// assert_eq!("SELECT price FROM books WHERE title = 'Harry Potter and the Philosopher''s Stone' OR title = 'Harry Potter and the Chamber of Secrets';", &sql);
2228 /// // add ^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2229 /// // here field value
2230 /// # Ok(())
2231 /// # }
2232 /// ```
2233 pub fn or_where_eq<S, T>(&mut self, field: S, value: T) -> &mut Self
2234 where
2235 S: ToString,
2236 T: ToString,
2237 {
2238 // Checks
2239 let field = field.to_string();
2240 if field.is_empty() {
2241 return self.set_error(&SqlBuilderError::NoWhereField);
2242 }
2243 let value = value.to_string();
2244 if value.is_empty() {
2245 return self.set_error(&SqlBuilderError::NoWhereValue(field));
2246 }
2247
2248 // Change
2249 let mut cond = field;
2250 cond.push_str(" = ");
2251 cond.push_str(&value);
2252 self.or_where(&cond)
2253 }
2254
2255 /// Add OR condition of non-equal parts to the last WHERE condition.
2256 ///
2257 /// ```
2258 /// # use anyhow::Result;
2259 /// use sql_builder::{SqlBuilder, quote};
2260 ///
2261 /// # fn main() -> Result<()> {
2262 /// let sql = SqlBuilder::select_from("books")
2263 /// .field("price")
2264 /// .or_where_ne("title", "e("Harry Potter and the Philosopher's Stone"))
2265 /// .or_where_ne("title", "e("Harry Potter and the Chamber of Secrets"))
2266 /// .sql()?;
2267 ///
2268 /// assert_eq!("SELECT price FROM books WHERE title <> 'Harry Potter and the Philosopher''s Stone' OR title <> 'Harry Potter and the Chamber of Secrets';", &sql);
2269 /// // add ^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2270 /// // here field value field value
2271 /// # Ok(())
2272 /// # }
2273 /// ```
2274 pub fn or_where_ne<S, T>(&mut self, field: S, value: T) -> &mut Self
2275 where
2276 S: ToString,
2277 T: ToString,
2278 {
2279 // Checks
2280 let field = field.to_string();
2281 if field.is_empty() {
2282 return self.set_error(&SqlBuilderError::NoWhereField);
2283 }
2284 let value = value.to_string();
2285 if value.is_empty() {
2286 return self.set_error(&SqlBuilderError::NoWhereValue(field));
2287 }
2288
2289 // Change
2290 let mut cond = field;
2291 cond.push_str(" <> ");
2292 cond.push_str(&value);
2293 self.or_where(&cond)
2294 }
2295
2296 /// Add OR condition for field greater than value to the last WHERE condition.
2297 ///
2298 /// ```
2299 /// # use anyhow::Result;
2300 /// use sql_builder::SqlBuilder;
2301 ///
2302 /// # fn main() -> Result<()> {
2303 /// let sql = SqlBuilder::select_from("books")
2304 /// .field("title")
2305 /// .field("price")
2306 /// .and_where_lt("price", 100)
2307 /// .or_where_gt("price", 300)
2308 /// .sql()?;
2309 ///
2310 /// assert_eq!("SELECT title, price FROM books WHERE price < 100 OR price > 300;", &sql);
2311 /// // add ^^^^^ ^^^
2312 /// // here field value
2313 /// # Ok(())
2314 /// # }
2315 /// ```
2316 pub fn or_where_gt<S, T>(&mut self, field: S, value: T) -> &mut Self
2317 where
2318 S: ToString,
2319 T: ToString,
2320 {
2321 // Checks
2322 let field = field.to_string();
2323 if field.is_empty() {
2324 return self.set_error(&SqlBuilderError::NoWhereField);
2325 }
2326 let value = value.to_string();
2327 if value.is_empty() {
2328 return self.set_error(&SqlBuilderError::NoWhereValue(field));
2329 }
2330
2331 // Change
2332 let mut cond = field;
2333 cond.push_str(" > ");
2334 cond.push_str(&value);
2335 self.or_where(&cond)
2336 }
2337
2338 /// Add OR condition for field not less than value to the last WHERE condition.
2339 ///
2340 /// ```
2341 /// # use anyhow::Result;
2342 /// use sql_builder::SqlBuilder;
2343 ///
2344 /// # fn main() -> Result<()> {
2345 /// let sql = SqlBuilder::select_from("books")
2346 /// .field("title")
2347 /// .field("price")
2348 /// .or_where_lt("price", 100)
2349 /// .or_where_ge("price", 300)
2350 /// .sql()?;
2351 ///
2352 /// assert_eq!("SELECT title, price FROM books WHERE price < 100 OR price >= 300;", &sql);
2353 /// // add ^^^^^ ^^^
2354 /// // here field value
2355 /// # Ok(())
2356 /// # }
2357 /// ```
2358 pub fn or_where_ge<S, T>(&mut self, field: S, value: T) -> &mut Self
2359 where
2360 S: ToString,
2361 T: ToString,
2362 {
2363 // Checks
2364 let field = field.to_string();
2365 if field.is_empty() {
2366 return self.set_error(&SqlBuilderError::NoWhereField);
2367 }
2368 let value = value.to_string();
2369 if value.is_empty() {
2370 return self.set_error(&SqlBuilderError::NoWhereValue(field));
2371 }
2372
2373 // Change
2374 let mut cond = field;
2375 cond.push_str(" >= ");
2376 cond.push_str(&value);
2377 self.or_where(&cond)
2378 }
2379
2380 /// Add OR condition for field less than value to the last WHERE condition.
2381 ///
2382 /// ```
2383 /// # use anyhow::Result;
2384 /// use sql_builder::SqlBuilder;
2385 ///
2386 /// # fn main() -> Result<()> {
2387 /// let sql = SqlBuilder::select_from("books")
2388 /// .field("title")
2389 /// .field("price")
2390 /// .and_where_lt("price", 100)
2391 /// .or_where_lt("price", 300)
2392 /// .sql()?;
2393 ///
2394 /// assert_eq!("SELECT title, price FROM books WHERE price < 100 OR price < 300;", &sql);
2395 /// // add ^^^^^ ^^^
2396 /// // here field value
2397 /// # Ok(())
2398 /// # }
2399 /// ```
2400 pub fn or_where_lt<S, T>(&mut self, field: S, value: T) -> &mut Self
2401 where
2402 S: ToString,
2403 T: ToString,
2404 {
2405 // Checks
2406 let field = field.to_string();
2407 if field.is_empty() {
2408 return self.set_error(&SqlBuilderError::NoWhereField);
2409 }
2410 let value = value.to_string();
2411 if value.is_empty() {
2412 return self.set_error(&SqlBuilderError::NoWhereValue(field));
2413 }
2414
2415 // Change
2416 let mut cond = field;
2417 cond.push_str(" < ");
2418 cond.push_str(&value);
2419 self.or_where(&cond)
2420 }
2421
2422 /// Add OR condition for field not greater than value to the last WHERE condition.
2423 ///
2424 /// ```
2425 /// # use anyhow::Result;
2426 /// use sql_builder::SqlBuilder;
2427 ///
2428 /// # fn main() -> Result<()> {
2429 /// let sql = SqlBuilder::select_from("books")
2430 /// .field("title")
2431 /// .field("price")
2432 /// .or_where_le("price", 100)
2433 /// .or_where_ge("price", 300)
2434 /// .sql()?;
2435 ///
2436 /// assert_eq!("SELECT title, price FROM books WHERE price <= 100 OR price >= 300;", &sql);
2437 /// // add ^^^^^ ^^^
2438 /// // here field value
2439 /// # Ok(())
2440 /// # }
2441 /// ```
2442 pub fn or_where_le<S, T>(&mut self, field: S, value: T) -> &mut Self
2443 where
2444 S: ToString,
2445 T: ToString,
2446 {
2447 // Checks
2448 let field = field.to_string();
2449 if field.is_empty() {
2450 return self.set_error(&SqlBuilderError::NoWhereField);
2451 }
2452 let value = value.to_string();
2453 if value.is_empty() {
2454 return self.set_error(&SqlBuilderError::NoWhereValue(field));
2455 }
2456
2457 // Change
2458 let mut cond = field;
2459 cond.push_str(" <= ");
2460 cond.push_str(&value);
2461 self.or_where(&cond)
2462 }
2463
2464 /// Add OR LIKE condition to the last WHERE condition.
2465 ///
2466 /// ```
2467 /// # use anyhow::Result;
2468 /// use sql_builder::SqlBuilder;
2469 ///
2470 /// # fn main() -> Result<()> {
2471 /// let sql = SqlBuilder::select_from("books")
2472 /// .field("price")
2473 /// .or_where_like("title", "%Alice's%")
2474 /// .or_where_like("title", "%Philosopher's%")
2475 /// .sql()?;
2476 ///
2477 /// assert_eq!("SELECT price FROM books WHERE title LIKE '%Alice''s%' OR title LIKE '%Philosopher''s%';", &sql);
2478 /// // add ^^^^^ ^^^^^^^^^^^^ ^^^^^ ^^^^^^^^^^^^^^^^^^
2479 /// // here field mask field mask
2480 /// # Ok(())
2481 /// # }
2482 /// ```
2483 pub fn or_where_like<S, T>(&mut self, field: S, mask: T) -> &mut Self
2484 where
2485 S: ToString,
2486 T: ToString,
2487 {
2488 // Checks
2489 let field = field.to_string();
2490 if field.is_empty() {
2491 return self.set_error(&SqlBuilderError::NoWhereField);
2492 }
2493
2494 // Change
2495 let mut cond = field;
2496 cond.push_str(" LIKE '");
2497 cond.push_str(&esc(&mask.to_string()));
2498 cond.push('\'');
2499 self.or_where(&cond)
2500 }
2501
2502 /// Add OR LIKE condition to the last WHERE %condition.
2503 ///
2504 /// ```
2505 /// # use anyhow::Result;
2506 /// use sql_builder::SqlBuilder;
2507 ///
2508 /// # fn main() -> Result<()> {
2509 /// let sql = SqlBuilder::select_from("books")
2510 /// .field("price")
2511 /// .or_where_like_right("title", "Alice's")
2512 /// .or_where_like_right("title", "Philosopher's")
2513 /// .sql()?;
2514 ///
2515 /// assert_eq!("SELECT price FROM books WHERE title LIKE '%Alice''s' OR title LIKE '%Philosopher''s';", &sql);
2516 /// // add ^^^^^ ^^^^^^^^ ^^^^^ ^^^^^^^^^^^^^^
2517 /// // here field mask field mask
2518 /// # Ok(())
2519 /// # }
2520 /// ```
2521 pub fn or_where_like_right<S, T>(&mut self, field: S, mask: T) -> &mut Self
2522 where
2523 S: ToString,
2524 T: ToString,
2525 {
2526 // Checks
2527 let field = field.to_string();
2528 if field.is_empty() {
2529 return self.set_error(&SqlBuilderError::NoWhereField);
2530 }
2531
2532 // Change
2533 let mut cond = field;
2534 cond.push_str(" LIKE '%");
2535 cond.push_str(&esc(&mask.to_string()));
2536 cond.push('\'');
2537 self.or_where(&cond)
2538 }
2539
2540 /// Add OR LIKE condition to the last WHERE condition%.
2541 ///
2542 /// ```
2543 /// # use anyhow::Result;
2544 /// use sql_builder::SqlBuilder;
2545 ///
2546 /// # fn main() -> Result<()> {
2547 /// let sql = SqlBuilder::select_from("books")
2548 /// .field("price")
2549 /// .or_where_like_left("title", "Alice's")
2550 /// .or_where_like_left("title", "Philosopher's")
2551 /// .sql()?;
2552 ///
2553 /// assert_eq!("SELECT price FROM books WHERE title LIKE 'Alice''s%' OR title LIKE 'Philosopher''s%';", &sql);
2554 /// // add ^^^^^ ^^^^^^^^ ^^^^^ ^^^^^^^^^^^^^^
2555 /// // here field mask field mask
2556 /// # Ok(())
2557 /// # }
2558 /// ```
2559 pub fn or_where_like_left<S, T>(&mut self, field: S, mask: T) -> &mut Self
2560 where
2561 S: ToString,
2562 T: ToString,
2563 {
2564 // Checks
2565 let field = field.to_string();
2566 if field.is_empty() {
2567 return self.set_error(&SqlBuilderError::NoWhereField);
2568 }
2569
2570 // Change
2571 let mut cond = field;
2572 cond.push_str(" LIKE '");
2573 cond.push_str(&esc(&mask.to_string()));
2574 cond.push_str("%'");
2575 self.or_where(&cond)
2576 }
2577
2578 /// Add OR LIKE condition to the last WHERE %condition%.
2579 ///
2580 /// ```
2581 /// # use anyhow::Result;
2582 /// use sql_builder::SqlBuilder;
2583 ///
2584 /// # fn main() -> Result<()> {
2585 /// let sql = SqlBuilder::select_from("books")
2586 /// .field("price")
2587 /// .or_where_like_any("title", "Alice's")
2588 /// .or_where_like_any("title", "Philosopher's")
2589 /// .sql()?;
2590 ///
2591 /// assert_eq!("SELECT price FROM books WHERE title LIKE '%Alice''s%' OR title LIKE '%Philosopher''s%';", &sql);
2592 /// // add ^^^^^ ^^^^^^^^^^^^ ^^^^^ ^^^^^^^^^^^^^^^^^^
2593 /// // here field mask field mask
2594 /// # Ok(())
2595 /// # }
2596 /// ```
2597 pub fn or_where_like_any<S, T>(&mut self, field: S, mask: T) -> &mut Self
2598 where
2599 S: ToString,
2600 T: ToString,
2601 {
2602 // Checks
2603 let field = field.to_string();
2604 if field.is_empty() {
2605 return self.set_error(&SqlBuilderError::NoWhereField);
2606 }
2607
2608 // Change
2609 let mut cond = field;
2610 cond.push_str(" LIKE '%");
2611 cond.push_str(&esc(&mask.to_string()));
2612 cond.push_str("%'");
2613 self.or_where(&cond)
2614 }
2615
2616 /// Add OR NOT LIKE condition to the last WHERE condition.
2617 ///
2618 /// ```
2619 /// # use anyhow::Result;
2620 /// use sql_builder::SqlBuilder;
2621 ///
2622 /// # fn main() -> Result<()> {
2623 /// let sql = SqlBuilder::select_from("books")
2624 /// .field("title")
2625 /// .and_where_not_like("title", "%Alice's%")
2626 /// .or_where_not_like("title", "%Philosopher's%")
2627 /// .sql()?;
2628 ///
2629 /// assert_eq!("SELECT title FROM books WHERE title NOT LIKE '%Alice''s%' OR title NOT LIKE '%Philosopher''s%';", &sql);
2630 /// // add ^^^^^ ^^^^^^^^^^^^^^^^^^
2631 /// // here field mask
2632 /// # Ok(())
2633 /// # }
2634 /// ```
2635 pub fn or_where_not_like<S, T>(&mut self, field: S, mask: T) -> &mut Self
2636 where
2637 S: ToString,
2638 T: ToString,
2639 {
2640 // Checks
2641 let field = field.to_string();
2642 if field.is_empty() {
2643 return self.set_error(&SqlBuilderError::NoWhereField);
2644 }
2645 let mask = mask.to_string();
2646 if mask.is_empty() {
2647 return self.set_error(&SqlBuilderError::NoWhereValue(field));
2648 }
2649
2650 // Change
2651 let mut cond = field;
2652 cond.push_str(" NOT LIKE '");
2653 cond.push_str(&esc(&mask));
2654 cond.push('\'');
2655 self.or_where(&cond)
2656 }
2657
2658 /// Add OR NOT LIKE condition to the last WHERE %condition.
2659 ///
2660 /// ```
2661 /// # use anyhow::Result;
2662 /// use sql_builder::SqlBuilder;
2663 ///
2664 /// # fn main() -> Result<()> {
2665 /// let sql = SqlBuilder::select_from("books")
2666 /// .field("price")
2667 /// .or_where_not_like_right("title", "Alice's")
2668 /// .or_where_not_like_right("title", "Philosopher's")
2669 /// .sql()?;
2670 ///
2671 /// assert_eq!("SELECT price FROM books WHERE title NOT LIKE '%Alice''s' OR title NOT LIKE '%Philosopher''s';", &sql);
2672 /// // add ^^^^^ ^^^^^^^^ ^^^^^ ^^^^^^^^^^^^^^
2673 /// // here field mask field mask
2674 /// # Ok(())
2675 /// # }
2676 /// ```
2677 pub fn or_where_not_like_right<S, T>(&mut self, field: S, mask: T) -> &mut Self
2678 where
2679 S: ToString,
2680 T: ToString,
2681 {
2682 // Checks
2683 let field = field.to_string();
2684 if field.is_empty() {
2685 return self.set_error(&SqlBuilderError::NoWhereField);
2686 }
2687 let mask = mask.to_string();
2688 if mask.is_empty() {
2689 return self.set_error(&SqlBuilderError::NoWhereValue(field));
2690 }
2691
2692 // Change
2693 let mut cond = field;
2694 cond.push_str(" NOT LIKE '%");
2695 cond.push_str(&esc(&mask));
2696 cond.push('\'');
2697 self.or_where(&cond)
2698 }
2699
2700 /// Add OR NOT LIKE condition to the last WHERE condition%.
2701 ///
2702 /// ```
2703 /// # use anyhow::Result;
2704 /// use sql_builder::SqlBuilder;
2705 ///
2706 /// # fn main() -> Result<()> {
2707 /// let sql = SqlBuilder::select_from("books")
2708 /// .field("price")
2709 /// .or_where_not_like_left("title", "Alice's")
2710 /// .or_where_not_like_left("title", "Philosopher's")
2711 /// .sql()?;
2712 ///
2713 /// assert_eq!("SELECT price FROM books WHERE title NOT LIKE 'Alice''s%' OR title NOT LIKE 'Philosopher''s%';", &sql);
2714 /// // add ^^^^^ ^^^^^^^^ ^^^^^ ^^^^^^^^^^^^^^
2715 /// // here field mask field mask
2716 /// # Ok(())
2717 /// # }
2718 /// ```
2719 pub fn or_where_not_like_left<S, T>(&mut self, field: S, mask: T) -> &mut Self
2720 where
2721 S: ToString,
2722 T: ToString,
2723 {
2724 // Checks
2725 let field = field.to_string();
2726 if field.is_empty() {
2727 return self.set_error(&SqlBuilderError::NoWhereField);
2728 }
2729 let mask = mask.to_string();
2730 if mask.is_empty() {
2731 return self.set_error(&SqlBuilderError::NoWhereValue(field));
2732 }
2733
2734 // Change
2735 let mut cond = field;
2736 cond.push_str(" NOT LIKE '");
2737 cond.push_str(&esc(&mask));
2738 cond.push_str("%'");
2739 self.or_where(&cond)
2740 }
2741
2742 /// Add OR NOT LIKE condition to the last WHERE %condition%.
2743 ///
2744 /// ```
2745 /// # use anyhow::Result;
2746 /// use sql_builder::SqlBuilder;
2747 ///
2748 /// # fn main() -> Result<()> {
2749 /// let sql = SqlBuilder::select_from("books")
2750 /// .field("price")
2751 /// .or_where_not_like_any("title", "Alice's")
2752 /// .or_where_not_like_any("title", "Philosopher's")
2753 /// .sql()?;
2754 ///
2755 /// assert_eq!("SELECT price FROM books WHERE title NOT LIKE '%Alice''s%' OR title NOT LIKE '%Philosopher''s%';", &sql);
2756 /// // add ^^^^^ ^^^^^^^^^^^^ ^^^^^ ^^^^^^^^^^^^^^^^^^
2757 /// // here field mask field mask
2758 /// # Ok(())
2759 /// # }
2760 /// ```
2761 pub fn or_where_not_like_any<S, T>(&mut self, field: S, mask: T) -> &mut Self
2762 where
2763 S: ToString,
2764 T: ToString,
2765 {
2766 // Checks
2767 let field = field.to_string();
2768 if field.is_empty() {
2769 return self.set_error(&SqlBuilderError::NoWhereField);
2770 }
2771 let mask = mask.to_string();
2772 if mask.is_empty() {
2773 return self.set_error(&SqlBuilderError::NoWhereValue(field));
2774 }
2775
2776 // Change
2777 let mut cond = field;
2778 cond.push_str(" NOT LIKE '%");
2779 cond.push_str(&esc(&mask));
2780 cond.push_str("%'");
2781 self.or_where(&cond)
2782 }
2783
2784 /// Add OR IS NULL condition to the last WHERE condition.
2785 ///
2786 /// ```
2787 /// # use anyhow::Result;
2788 /// use sql_builder::SqlBuilder;
2789 ///
2790 /// # fn main() -> Result<()> {
2791 /// let sql = SqlBuilder::select_from("books")
2792 /// .field("title")
2793 /// .and_where_eq("price", 0)
2794 /// .or_where_is_null("price")
2795 /// .sql()?;
2796 ///
2797 /// assert_eq!("SELECT title FROM books WHERE price = 0 OR price IS NULL;", &sql);
2798 /// // add ^^^^^
2799 /// // here field
2800 /// # Ok(())
2801 /// # }
2802 /// ```
2803 pub fn or_where_is_null<S: ToString>(&mut self, field: S) -> &mut Self {
2804 // Checks
2805 let field = field.to_string();
2806 if field.is_empty() {
2807 return self.set_error(&SqlBuilderError::NoWhereField);
2808 }
2809
2810 // Change
2811 let mut cond = field;
2812 cond.push_str(" IS NULL");
2813 self.or_where(&cond)
2814 }
2815
2816 /// Add OR IS NOT NULL condition to the last WHERE condition.
2817 ///
2818 /// ```
2819 /// # use anyhow::Result;
2820 /// use sql_builder::SqlBuilder;
2821 ///
2822 /// # fn main() -> Result<()> {
2823 /// let sql = SqlBuilder::select_from("books")
2824 /// .field("title")
2825 /// .or_where_is_not_null("title")
2826 /// .or_where_is_not_null("price")
2827 /// .sql()?;
2828 ///
2829 /// assert_eq!("SELECT title FROM books WHERE title IS NOT NULL OR price IS NOT NULL;", &sql);
2830 /// // add ^^^^^ ^^^^^
2831 /// // here field field
2832 /// # Ok(())
2833 /// # }
2834 /// ```
2835 pub fn or_where_is_not_null<S: ToString>(&mut self, field: S) -> &mut Self {
2836 // Checks
2837 let field = field.to_string();
2838 if field.is_empty() {
2839 return self.set_error(&SqlBuilderError::NoWhereField);
2840 }
2841
2842 // Change
2843 let mut cond = field;
2844 cond.push_str(" IS NOT NULL");
2845 self.or_where(&cond)
2846 }
2847
2848 /// Add OR field IN (list) to the last WHERE condition.
2849 ///
2850 /// ```
2851 /// # use anyhow::Result;
2852 /// use sql_builder::{SqlBuilder, quote};
2853 ///
2854 /// # fn main() -> Result<()> {
2855 /// let sql = SqlBuilder::select_from("books")
2856 /// .field("title")
2857 /// .field("price")
2858 /// .or_where_lt("price", 100)
2859 /// .or_where_in("title", &[quote("G"), quote("L"), quote("t")])
2860 /// .sql()?;
2861 ///
2862 /// assert_eq!("SELECT title, price FROM books WHERE price < 100 OR title IN ('G', 'L', 't');", &sql);
2863 /// // add ^^^^^ ^^^^^^^^^^^^^
2864 /// // here field list
2865 /// # Ok(())
2866 /// # }
2867 /// ```
2868 pub fn or_where_in<S, T>(&mut self, field: S, list: &[T]) -> &mut Self
2869 where
2870 S: ToString,
2871 T: ToString,
2872 {
2873 // Checks
2874 let field = field.to_string();
2875 if field.is_empty() {
2876 return self.set_error(&SqlBuilderError::NoWhereField);
2877 }
2878 if list.is_empty() {
2879 return self.set_error(&SqlBuilderError::NoWhereList(field));
2880 }
2881
2882 // Change
2883 let list: Vec<String> = list
2884 .iter()
2885 .map(|v| (*v).to_string())
2886 .collect::<Vec<String>>();
2887 let list = list.join(", ");
2888
2889 let mut cond = field;
2890 cond.push_str(" IN (");
2891 cond.push_str(&list);
2892 cond.push(')');
2893 self.or_where(&cond)
2894 }
2895
2896 /// Add OR field IN (string list) to the last WHERE condition.
2897 ///
2898 /// ```
2899 /// # use anyhow::Result;
2900 /// use sql_builder::{SqlBuilder, quote};
2901 ///
2902 /// # fn main() -> Result<()> {
2903 /// let sql = SqlBuilder::select_from("books")
2904 /// .field("title")
2905 /// .field("price")
2906 /// .or_where_lt("price", 100)
2907 /// .or_where_in_quoted("title", &["G", "L", "t"])
2908 /// .sql()?;
2909 ///
2910 /// assert_eq!("SELECT title, price FROM books WHERE price < 100 OR title IN ('G', 'L', 't');", &sql);
2911 /// // add ^^^^^ ^^^^^^^^^^^^^
2912 /// // here field list
2913 /// # Ok(())
2914 /// # }
2915 /// ```
2916 pub fn or_where_in_quoted<S, T>(&mut self, field: S, list: &[T]) -> &mut Self
2917 where
2918 S: ToString,
2919 T: ToString,
2920 {
2921 // Checks
2922 let field = field.to_string();
2923 if field.is_empty() {
2924 return self.set_error(&SqlBuilderError::NoWhereField);
2925 }
2926 if list.is_empty() {
2927 return self.set_error(&SqlBuilderError::NoWhereList(field));
2928 }
2929
2930 // Change
2931 let list: Vec<String> = list
2932 .iter()
2933 .map(|v| quote((*v).to_string()))
2934 .collect::<Vec<String>>();
2935 let list = list.join(", ");
2936
2937 let mut cond = field;
2938 cond.push_str(" IN (");
2939 cond.push_str(&list);
2940 cond.push(')');
2941 self.or_where(&cond)
2942 }
2943
2944 /// Add OR field NOT IN (list) to the last WHERE condition.
2945 ///
2946 /// ```
2947 /// # use anyhow::Result;
2948 /// use sql_builder::{SqlBuilder, quote};
2949 ///
2950 /// # fn main() -> Result<()> {
2951 /// let sql = SqlBuilder::select_from("books")
2952 /// .field("title")
2953 /// .field("price")
2954 /// .or_where_lt("price", 100)
2955 /// .or_where_not_in("title", &[quote("G"), quote("L"), quote("t")])
2956 /// .sql()?;
2957 ///
2958 /// assert_eq!("SELECT title, price FROM books WHERE price < 100 OR title NOT IN ('G', 'L', 't');", &sql);
2959 /// // add ^^^^^ ^^^^^^^^^^^^^
2960 /// // here field list
2961 /// # Ok(())
2962 /// # }
2963 /// ```
2964 pub fn or_where_not_in<S, T>(&mut self, field: S, list: &[T]) -> &mut Self
2965 where
2966 S: ToString,
2967 T: ToString,
2968 {
2969 // Checks
2970 let field = field.to_string();
2971 if field.is_empty() {
2972 return self.set_error(&SqlBuilderError::NoWhereField);
2973 }
2974 if list.is_empty() {
2975 return self.set_error(&SqlBuilderError::NoWhereList(field));
2976 }
2977
2978 // Change
2979 let list: Vec<String> = list
2980 .iter()
2981 .map(|v| (*v).to_string())
2982 .collect::<Vec<String>>();
2983 let list = list.join(", ");
2984
2985 let mut cond = field;
2986 cond.push_str(" NOT IN (");
2987 cond.push_str(&list);
2988 cond.push(')');
2989 self.or_where(&cond)
2990 }
2991
2992 /// Add OR field NOT IN (string list) to the last WHERE condition.
2993 ///
2994 /// ```
2995 /// # use anyhow::Result;
2996 /// use sql_builder::{SqlBuilder, quote};
2997 ///
2998 /// # fn main() -> Result<()> {
2999 /// let sql = SqlBuilder::select_from("books")
3000 /// .field("title")
3001 /// .field("price")
3002 /// .or_where_lt("price", 100)
3003 /// .or_where_not_in_quoted("title", &["G", "L", "t"])
3004 /// .sql()?;
3005 ///
3006 /// assert_eq!("SELECT title, price FROM books WHERE price < 100 OR title NOT IN ('G', 'L', 't');", &sql);
3007 /// // add ^^^^^ ^^^^^^^^^^^^^
3008 /// // here field list
3009 /// # Ok(())
3010 /// # }
3011 /// ```
3012 pub fn or_where_not_in_quoted<S, T>(&mut self, field: S, list: &[T]) -> &mut Self
3013 where
3014 S: ToString,
3015 T: ToString,
3016 {
3017 // Checks
3018 let field = field.to_string();
3019 if field.is_empty() {
3020 return self.set_error(&SqlBuilderError::NoWhereField);
3021 }
3022 if list.is_empty() {
3023 return self.set_error(&SqlBuilderError::NoWhereList(field));
3024 }
3025
3026 // Change
3027 let list: Vec<String> = list
3028 .iter()
3029 .map(|v| quote((*v).to_string()))
3030 .collect::<Vec<String>>();
3031 let list = list.join(", ");
3032
3033 let mut cond = field;
3034 cond.push_str(" NOT IN (");
3035 cond.push_str(&list);
3036 cond.push(')');
3037 self.or_where(&cond)
3038 }
3039
3040 /// Add OR field IN (query) to the last WHERE condition.
3041 ///
3042 /// ```
3043 /// # use anyhow::Result;
3044 /// use sql_builder::SqlBuilder;
3045 ///
3046 /// # fn main() -> Result<()> {
3047 /// let query = SqlBuilder::select_from("shop")
3048 /// .field("title")
3049 /// .and_where("sold")
3050 /// .query()?;
3051 ///
3052 /// assert_eq!("SELECT title FROM shop WHERE sold", &query);
3053 ///
3054 /// let sql = SqlBuilder::select_from("books")
3055 /// .field("title")
3056 /// .field("price")
3057 /// .or_where_lt("price", 100)
3058 /// .or_where_in_query("title", &query)
3059 /// .sql()?;
3060 ///
3061 /// assert_eq!("SELECT title, price FROM books WHERE price < 100 OR title IN (SELECT title FROM shop WHERE sold);", &sql);
3062 /// // add ^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
3063 /// // here field query
3064 /// # Ok(())
3065 /// # }
3066 /// ```
3067 pub fn or_where_in_query<S, T>(&mut self, field: S, query: T) -> &mut Self
3068 where
3069 S: ToString,
3070 T: ToString,
3071 {
3072 // Checks
3073 let field = field.to_string();
3074 if field.is_empty() {
3075 return self.set_error(&SqlBuilderError::NoWhereField);
3076 }
3077 let query = query.to_string();
3078 if query.is_empty() {
3079 return self.set_error(&SqlBuilderError::NoWhereQuery(field));
3080 }
3081
3082 // Change
3083 let mut cond = field;
3084 cond.push_str(" IN (");
3085 cond.push_str(&query);
3086 cond.push(')');
3087 self.or_where(&cond)
3088 }
3089
3090 /// Add OR field NOT IN (query) to the last WHERE condition.
3091 ///
3092 /// ```
3093 /// # use anyhow::Result;
3094 /// use sql_builder::SqlBuilder;
3095 ///
3096 /// # fn main() -> Result<()> {
3097 /// let query = SqlBuilder::select_from("shop")
3098 /// .field("title")
3099 /// .and_where("sold")
3100 /// .query()?;
3101 ///
3102 /// assert_eq!("SELECT title FROM shop WHERE sold", &query);
3103 ///
3104 /// let sql = SqlBuilder::select_from("books")
3105 /// .field("title")
3106 /// .field("price")
3107 /// .or_where_lt("price", 100)
3108 /// .or_where_not_in_query("title", &query)
3109 /// .sql()?;
3110 ///
3111 /// assert_eq!("SELECT title, price FROM books WHERE price < 100 OR title NOT IN (SELECT title FROM shop WHERE sold);", &sql);
3112 /// // add ^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
3113 /// // here field query
3114 /// # Ok(())
3115 /// # }
3116 /// ```
3117 pub fn or_where_not_in_query<S, T>(&mut self, field: S, query: T) -> &mut Self
3118 where
3119 S: ToString,
3120 T: ToString,
3121 {
3122 // Checks
3123 let field = field.to_string();
3124 if field.is_empty() {
3125 return self.set_error(&SqlBuilderError::NoWhereField);
3126 }
3127 let query = query.to_string();
3128 if query.is_empty() {
3129 return self.set_error(&SqlBuilderError::NoWhereQuery(field));
3130 }
3131
3132 // Change
3133 let mut cond = field;
3134 cond.push_str(" NOT IN (");
3135 cond.push_str(&query);
3136 cond.push(')');
3137 self.or_where(&cond)
3138 }
3139
3140 /// Add OR field BETWEEN values to the last WHERE condition.
3141 ///
3142 /// ```
3143 /// # use anyhow::Result;
3144 /// use sql_builder::SqlBuilder;
3145 ///
3146 /// # fn main() -> Result<()> {
3147 /// let sql = SqlBuilder::select_from("books")
3148 /// .field("title")
3149 /// .field("price")
3150 /// .or_where_between("price", 100, 200)
3151 /// .or_where_between("price", 10_000, 20_000)
3152 /// .sql()?;
3153 ///
3154 /// assert_eq!("SELECT title, price FROM books WHERE price BETWEEN 100 AND 200 OR price BETWEEN 10000 AND 20000;", &sql);
3155 /// // add ^^^^^ ^^^ ^^^ ^^^^^ ^^^^^ ^^^^^
3156 /// // here field min max field min max
3157 /// # Ok(())
3158 /// # }
3159 /// ```
3160 pub fn or_where_between<S, T, U>(&mut self, field: S, min: T, max: U) -> &mut Self
3161 where
3162 S: ToString,
3163 T: ToString,
3164 U: ToString,
3165 {
3166 // Checks
3167 let field = field.to_string();
3168 if field.is_empty() {
3169 return self.set_error(&SqlBuilderError::NoWhereField);
3170 }
3171 let min = min.to_string();
3172 if min.is_empty() {
3173 return self.set_error(&SqlBuilderError::NoWhereValue(field));
3174 }
3175 let max = max.to_string();
3176 if max.is_empty() {
3177 return self.set_error(&SqlBuilderError::NoWhereValue(field));
3178 }
3179
3180 // Change
3181 let mut cond = field;
3182 cond.push_str(" BETWEEN ");
3183 cond.push_str(&min);
3184 cond.push_str(" AND ");
3185 cond.push_str(&max);
3186 self.or_where(&cond)
3187 }
3188
3189 /// Add OR field NOT BETWEEN values to the last WHERE condition.
3190 ///
3191 /// ```
3192 /// # use anyhow::Result;
3193 /// use sql_builder::SqlBuilder;
3194 ///
3195 /// # fn main() -> Result<()> {
3196 /// let sql = SqlBuilder::select_from("books")
3197 /// .field("title")
3198 /// .field("price")
3199 /// .or_where_not_between("price", 100, 200)
3200 /// .or_where_not_between("price", 10_000, 20_000)
3201 /// .sql()?;
3202 ///
3203 /// assert_eq!("SELECT title, price FROM books WHERE price NOT BETWEEN 100 AND 200 OR price NOT BETWEEN 10000 AND 20000;", &sql);
3204 /// // add ^^^^^ ^^^ ^^^ ^^^^^ ^^^^^ ^^^^^
3205 /// // here field min max field min max
3206 /// # Ok(())
3207 /// # }
3208 /// ```
3209 pub fn or_where_not_between<S, T, U>(&mut self, field: S, min: T, max: U) -> &mut Self
3210 where
3211 S: ToString,
3212 T: ToString,
3213 U: ToString,
3214 {
3215 // Checks
3216 let field = field.to_string();
3217 if field.is_empty() {
3218 return self.set_error(&SqlBuilderError::NoWhereField);
3219 }
3220 let min = min.to_string();
3221 if min.is_empty() {
3222 return self.set_error(&SqlBuilderError::NoWhereValue(field));
3223 }
3224 let max = max.to_string();
3225 if max.is_empty() {
3226 return self.set_error(&SqlBuilderError::NoWhereValue(field));
3227 }
3228
3229 // Change
3230 let mut cond = field;
3231 cond.push_str(" NOT BETWEEN ");
3232 cond.push_str(&min);
3233 cond.push_str(" AND ");
3234 cond.push_str(&max);
3235 self.or_where(&cond)
3236 }
3237
3238 /// Union query with subquery.
3239 /// ORDER BY must be in the last subquery.
3240 ///
3241 /// ```
3242 /// # use anyhow::Result;
3243 /// use sql_builder::SqlBuilder;
3244 ///
3245 /// # fn main() -> Result<()> {
3246 /// let append = SqlBuilder::select_from("books")
3247 /// .field("title")
3248 /// .field("price")
3249 /// .and_where("price < 100")
3250 /// .order_asc("title")
3251 /// .query()?;
3252 ///
3253 /// let sql = SqlBuilder::select_from("books")
3254 /// .field("title")
3255 /// .field("price")
3256 /// .and_where_like_left("title", "Harry Potter")
3257 /// .order_desc("price")
3258 /// .union(&append)
3259 /// .sql()?;
3260 ///
3261 /// assert_eq!("SELECT title, price FROM books WHERE title LIKE 'Harry Potter%' UNION SELECT title, price FROM books WHERE price < 100 ORDER BY title;", &sql);
3262 /// // add ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
3263 /// // here query
3264 /// # Ok(())
3265 /// # }
3266 /// ```
3267 pub fn union<S: ToString>(&mut self, query: S) -> &mut Self {
3268 let append = format!(" UNION {}", &query.to_string());
3269 self.unions.push_str(&append);
3270 self
3271 }
3272
3273 /// Union query with all subquery.
3274 /// ORDER BY must be in the last subquery.
3275 ///
3276 /// ```
3277 /// # use anyhow::Result;
3278 /// use sql_builder::SqlBuilder;
3279 ///
3280 /// # fn main() -> Result<()> {
3281 /// let append = SqlBuilder::select_values(&["'The Great Gatsby'", "124"])
3282 /// .query_values()?;
3283 ///
3284 /// let sql = SqlBuilder::select_from("books")
3285 /// .field("title")
3286 /// .field("price")
3287 /// .and_where_like_left("title", "Harry Potter")
3288 /// .order_desc("price")
3289 /// .union_all(&append)
3290 /// .sql()?;
3291 ///
3292 /// assert_eq!("SELECT title, price FROM books WHERE title LIKE 'Harry Potter%' UNION ALL SELECT 'The Great Gatsby', 124;", &sql);
3293 /// // add ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
3294 /// // here query
3295 /// # Ok(())
3296 /// # }
3297 /// ```
3298 pub fn union_all<S: ToString>(&mut self, query: S) -> &mut Self {
3299 self.unions.push_str(" UNION ALL ");
3300 self.unions.push_str(&query.to_string());
3301 self
3302 }
3303
3304 /// Add ORDER BY.
3305 ///
3306 /// ```
3307 /// # use anyhow::Result;
3308 /// use sql_builder::SqlBuilder;
3309 ///
3310 /// # fn main() -> Result<()> {
3311 /// let sql = SqlBuilder::select_from("books")
3312 /// .field("title")
3313 /// .field("price")
3314 /// .and_where_like_left("title", "Harry Potter")
3315 /// .order_by("price", false)
3316 /// .sql()?;
3317 ///
3318 /// assert_eq!("SELECT title, price FROM books WHERE title LIKE 'Harry Potter%' ORDER BY price;", &sql);
3319 /// // add ^^^^^
3320 /// // here field
3321 /// # Ok(())
3322 /// # }
3323 /// ```
3324 pub fn order_by<S: ToString>(&mut self, field: S, desc: bool) -> &mut Self {
3325 let order = if desc {
3326 format!("{} DESC", &field.to_string())
3327 } else {
3328 field.to_string()
3329 };
3330 self.order_by.push(order);
3331 self
3332 }
3333
3334 /// Add ORDER BY ASC.
3335 ///
3336 /// ```
3337 /// # use anyhow::Result;
3338 /// use sql_builder::SqlBuilder;
3339 ///
3340 /// # fn main() -> Result<()> {
3341 /// let sql = SqlBuilder::select_from("books")
3342 /// .field("title")
3343 /// .field("price")
3344 /// .and_where_like_left("title", "Harry Potter")
3345 /// .order_asc("title")
3346 /// .sql()?;
3347 ///
3348 /// assert_eq!("SELECT title, price FROM books WHERE title LIKE 'Harry Potter%' ORDER BY title;", &sql);
3349 /// // add ^^^^^
3350 /// // here field
3351 /// # Ok(())
3352 /// # }
3353 /// ```
3354 pub fn order_asc<S: ToString>(&mut self, field: S) -> &mut Self {
3355 self.order_by(&field.to_string(), false)
3356 }
3357
3358 /// Add ORDER BY DESC.
3359 ///
3360 /// ```
3361 /// # use anyhow::Result;
3362 /// use sql_builder::SqlBuilder;
3363 ///
3364 /// # fn main() -> Result<()> {
3365 /// let sql = SqlBuilder::select_from("books")
3366 /// .field("title")
3367 /// .field("price")
3368 /// .and_where_like_left("title", "Harry Potter")
3369 /// .order_desc("price")
3370 /// .sql()?;
3371 ///
3372 /// assert_eq!("SELECT title, price FROM books WHERE title LIKE 'Harry Potter%' ORDER BY price DESC;", &sql);
3373 /// // add ^^^^^
3374 /// // here field
3375 /// # Ok(())
3376 /// # }
3377 /// ```
3378 pub fn order_desc<S: ToString>(&mut self, field: S) -> &mut Self {
3379 self.order_by(&field.to_string(), true)
3380 }
3381
3382 /// Set LIMIT.
3383 ///
3384 /// ```
3385 /// # use anyhow::Result;
3386 /// use sql_builder::SqlBuilder;
3387 ///
3388 /// # fn main() -> Result<()> {
3389 /// let sql = SqlBuilder::select_from("books")
3390 /// .field("title")
3391 /// .field("price")
3392 /// .and_where_like_left("title", "Harry Potter")
3393 /// .order_desc("price")
3394 /// .limit(10)
3395 /// .sql()?;
3396 ///
3397 /// assert_eq!("SELECT title, price FROM books WHERE title LIKE 'Harry Potter%' ORDER BY price DESC LIMIT 10;", &sql);
3398 /// // add ^^
3399 /// // here limit
3400 /// # Ok(())
3401 /// # }
3402 /// ```
3403 pub fn limit<S: ToString>(&mut self, limit: S) -> &mut Self {
3404 self.limit = Some(limit.to_string());
3405 self
3406 }
3407
3408 /// Set OFFSET.
3409 ///
3410 /// ```
3411 /// # use anyhow::Result;
3412 /// use sql_builder::SqlBuilder;
3413 ///
3414 /// # fn main() -> Result<()> {
3415 /// let sql = SqlBuilder::select_from("books")
3416 /// .field("title")
3417 /// .field("price")
3418 /// .and_where_like_left("title", "Harry Potter")
3419 /// .order_desc("price")
3420 /// .limit(10)
3421 /// .offset(100)
3422 /// .sql()?;
3423 ///
3424 /// assert_eq!("SELECT title, price FROM books WHERE title LIKE 'Harry Potter%' ORDER BY price DESC LIMIT 10 OFFSET 100;", &sql);
3425 /// // add ^^^
3426 /// // here offset
3427 /// # Ok(())
3428 /// # }
3429 /// ```
3430 pub fn offset<S: ToString>(&mut self, offset: S) -> &mut Self {
3431 self.offset = Some(offset.to_string());
3432 self
3433 }
3434
3435 /// Set error during build.
3436 fn set_error(&mut self, err: &SqlBuilderError) -> &mut Self {
3437 self.error = Some(err.clone());
3438 self
3439 }
3440
3441 /// Clean error code.
3442 pub fn drop_error(&mut self) -> &mut Self {
3443 self.error = None;
3444 self
3445 }
3446
3447 /// Build complete SQL command.
3448 ///
3449 /// ```
3450 /// # use anyhow::Result;
3451 /// use sql_builder::SqlBuilder;
3452 ///
3453 /// # fn main() -> Result<()> {
3454 /// let sql = SqlBuilder::select_from("books").sql()?;
3455 ///
3456 /// assert_eq!("SELECT * FROM books;", &sql);
3457 /// # Ok(())
3458 /// # }
3459 /// ```
3460 pub fn sql(&self) -> Result<String> {
3461 if let Some(err) = &self.error {
3462 return Err(err.clone().into());
3463 }
3464 match self.statement {
3465 Statement::SelectFrom => self.sql_select(),
3466 Statement::SelectValues => self.sql_select_values(),
3467 Statement::UpdateTable => self.sql_update(),
3468 Statement::InsertInto => self.sql_insert(),
3469 Statement::DeleteFrom => self.sql_delete(),
3470 }
3471 }
3472
3473 /// Build complete SQL command for SELECT statement
3474 fn sql_select(&self) -> Result<String> {
3475 // Checks
3476 if self.table.is_empty() {
3477 return Err(SqlBuilderError::NoTableName.into());
3478 }
3479
3480 // Build query
3481 let mut text = self.query()?;
3482 text.push(';');
3483 Ok(text)
3484 }
3485
3486 /// Build complete SQL command for SELECT statement without a table
3487 fn sql_select_values(&self) -> Result<String> {
3488 // Checks
3489 if self.fields.is_empty() {
3490 return Err(SqlBuilderError::NoValues.into());
3491 }
3492
3493 // Build query
3494 let mut text = self.query_values()?;
3495 text.push(';');
3496 Ok(text)
3497 }
3498
3499 /// Build subquery SQL command.
3500 ///
3501 /// ```
3502 /// # use anyhow::Result;
3503 /// use sql_builder::SqlBuilder;
3504 ///
3505 /// # fn main() -> Result<()> {
3506 /// let cat = SqlBuilder::select_from("books")
3507 /// .field("CASE WHEN price < 100 THEN 'cheap' ELSE 'expensive' END AS category")
3508 /// .subquery()?;
3509 ///
3510 /// assert_eq!("(SELECT CASE WHEN price < 100 THEN 'cheap' ELSE 'expensive' END AS category FROM books)", &cat);
3511 ///
3512 /// let sql = SqlBuilder::select_from(&cat)
3513 /// .field("category")
3514 /// .field("COUNT(category) AS cnt")
3515 /// .group_by("category")
3516 /// .order_desc("cnt")
3517 /// .order_asc("category")
3518 /// .sql()?;
3519 ///
3520 /// assert_eq!("SELECT category, COUNT(category) AS cnt FROM (SELECT CASE WHEN price < 100 THEN 'cheap' ELSE 'expensive' END AS category FROM books) GROUP BY category ORDER BY cnt DESC, category;", &sql);
3521 /// # Ok(())
3522 /// # }
3523 /// ```
3524 pub fn subquery(&self) -> Result<String> {
3525 let text = self.query()?;
3526 let text = format!("({})", &text);
3527 Ok(text)
3528 }
3529
3530 /// Build named subquery SQL command.
3531 ///
3532 /// ```
3533 /// # use anyhow::Result;
3534 /// use sql_builder::SqlBuilder;
3535 ///
3536 /// # fn main() -> Result<()> {
3537 /// let cat = SqlBuilder::select_from("books")
3538 /// .field("CASE WHEN price < 100 THEN 'cheap' ELSE 'expensive' END")
3539 /// .subquery_as("category")?;
3540 ///
3541 /// assert_eq!("(SELECT CASE WHEN price < 100 THEN 'cheap' ELSE 'expensive' END FROM books) AS category", &cat);
3542 /// // add ^^^^^^^^
3543 /// // here name
3544 ///
3545 /// let sql = SqlBuilder::select_from("books")
3546 /// .field("title")
3547 /// .field("price")
3548 /// .field(&cat)
3549 /// .sql()?;
3550 ///
3551 /// assert_eq!("SELECT title, price, (SELECT CASE WHEN price < 100 THEN 'cheap' ELSE 'expensive' END FROM books) AS category FROM books;", &sql);
3552 /// # Ok(())
3553 /// # }
3554 /// ```
3555 pub fn subquery_as<S: ToString>(&self, name: S) -> Result<String> {
3556 let mut text = "(".to_string();
3557 text.push_str(&self.query()?);
3558 text.push_str(") AS ");
3559 text.push_str(&name.to_string());
3560 Ok(text)
3561 }
3562
3563 /// SQL command generator for query or subquery.
3564 ///
3565 /// ```
3566 /// # use anyhow::Result;
3567 /// use sql_builder::SqlBuilder;
3568 ///
3569 /// # fn main() -> Result<()> {
3570 /// let query = SqlBuilder::select_from("warehouse")
3571 /// .field("title")
3572 /// .field("preliminary_price * 2")
3573 /// .query()?;
3574 ///
3575 /// assert_eq!("SELECT title, preliminary_price * 2 FROM warehouse", &query);
3576 ///
3577 /// let sql = SqlBuilder::insert_into("books")
3578 /// .field("title")
3579 /// .field("price")
3580 /// .select(&query)
3581 /// .sql()?;
3582 ///
3583 /// assert_eq!("INSERT INTO books (title, price) SELECT title, preliminary_price * 2 FROM warehouse;", &sql);
3584 /// # Ok(())
3585 /// # }
3586 /// ```
3587 pub fn query(&self) -> Result<String> {
3588 if let Some(err) = &self.error {
3589 return Err(err.clone().into());
3590 }
3591
3592 // Distinct results
3593 let distinct = if self.distinct { " DISTINCT" } else { "" };
3594
3595 // Make fields
3596 let fields = if self.fields.is_empty() {
3597 "*".to_string()
3598 } else {
3599 self.fields.join(", ")
3600 };
3601
3602 // Make JOIN parts
3603 let joins = if self.joins.is_empty() {
3604 String::new()
3605 } else {
3606 format!(" {}", self.joins.join(" "))
3607 };
3608
3609 // Make GROUP BY part
3610 let group_by = if self.group_by.is_empty() {
3611 String::new()
3612 } else {
3613 let having = if let Some(having) = &self.having {
3614 format!(" HAVING {}", having)
3615 } else {
3616 String::new()
3617 };
3618 format!(" GROUP BY {}{}", self.group_by.join(", "), having)
3619 };
3620
3621 // Make WHERE part
3622 let wheres = SqlBuilder::make_wheres(&self.wheres);
3623
3624 // Make ORDER BY part
3625 let order_by = if self.order_by.is_empty() || !self.unions.is_empty() {
3626 String::new()
3627 } else {
3628 format!(" ORDER BY {}", self.order_by.join(", "))
3629 };
3630
3631 // Make LIMIT part
3632 let limit = match &self.limit {
3633 Some(limit) => format!(" LIMIT {}", limit),
3634 None => String::new(),
3635 };
3636
3637 // Make OFFSET part
3638 let offset = match &self.offset {
3639 Some(offset) => format!(" OFFSET {}", offset),
3640 None => String::new(),
3641 };
3642
3643 // Make SQL
3644 let sql = format!("SELECT{distinct} {fields} FROM {table}{joins}{wheres}{group_by}{unions}{order_by}{limit}{offset}",
3645 distinct = distinct,
3646 fields = fields,
3647 table = &self.table,
3648 joins = joins,
3649 group_by = group_by,
3650 wheres = wheres,
3651 unions = &self.unions,
3652 order_by = order_by,
3653 limit = limit,
3654 offset = offset,
3655 );
3656 Ok(sql)
3657 }
3658
3659 /// SQL command generator for query or subquery without a table.
3660 ///
3661 /// ```
3662 /// # use anyhow::Result;
3663 /// use sql_builder::{SqlBuilder, quote};
3664 ///
3665 /// # fn main() -> Result<()> {
3666 /// let values = SqlBuilder::select_values(&["10", "e("100")])
3667 /// .query_values()?;
3668 ///
3669 /// assert_eq!("SELECT 10, '100'", &values);
3670 /// # Ok(())
3671 /// # }
3672 /// ```
3673 pub fn query_values(&self) -> Result<String> {
3674 // Checks
3675 if let Some(err) = &self.error {
3676 return Err(err.clone().into());
3677 }
3678
3679 // Make values
3680 let fields = self.fields.join(", ");
3681
3682 // Make SQL
3683 let sql = format!("SELECT {fields}", fields = fields);
3684 Ok(sql)
3685 }
3686
3687 /// Build SQL command for INSERT statement
3688 fn sql_insert(&self) -> Result<String> {
3689 // Checks
3690 if let Some(err) = &self.error {
3691 return Err(err.clone().into());
3692 }
3693 if self.table.is_empty() {
3694 return Err(SqlBuilderError::NoTableName.into());
3695 }
3696
3697 // Make SET part
3698 let fields = self.fields.join(", ");
3699
3700 // Add values or query
3701 let sql = match &self.values {
3702 Values::Empty => return Err(SqlBuilderError::NoValues.into()),
3703 Values::List(values) => {
3704 if values.is_empty() {
3705 return Err(SqlBuilderError::NoValues.into());
3706 }
3707
3708 // Make VALUES part
3709 let values = values.join(", ");
3710
3711 // Make RETURNING part
3712 let returning = if let Some(ret) = &self.returning {
3713 format!(" RETURNING {}", ret)
3714 } else {
3715 "".to_string()
3716 };
3717
3718 // Make SQL
3719 format!(
3720 "INSERT INTO {table} ({fields}) VALUES {values}{returning};",
3721 table = &self.table,
3722 fields = fields,
3723 values = values,
3724 returning = returning,
3725 )
3726 }
3727 Values::Select(query) => {
3728 // Make SQL
3729 format!(
3730 "INSERT INTO {table} ({fields}) {query};",
3731 table = &self.table,
3732 fields = fields,
3733 query = query,
3734 )
3735 }
3736 };
3737
3738 Ok(sql)
3739 }
3740
3741 /// Build SQL command for UPDATE statement
3742 fn sql_update(&self) -> Result<String> {
3743 // Checks
3744 if let Some(err) = &self.error {
3745 return Err(err.clone().into());
3746 }
3747 if self.table.is_empty() {
3748 return Err(SqlBuilderError::NoTableName.into());
3749 }
3750 if self.sets.is_empty() {
3751 return Err(SqlBuilderError::NoSetFields.into());
3752 }
3753
3754 // Make SET part
3755 let sets = self.sets.join(", ");
3756
3757 // Make WHERE part
3758 let wheres = SqlBuilder::make_wheres(&self.wheres);
3759
3760 // Make RETURNING part
3761 let returning = if let Some(ret) = &self.returning {
3762 format!(" RETURNING {}", ret)
3763 } else {
3764 "".to_string()
3765 };
3766
3767 // Make SQL
3768 let sql = format!(
3769 "UPDATE {table} SET {sets}{wheres}{returning};",
3770 table = &self.table,
3771 sets = sets,
3772 wheres = wheres,
3773 returning = returning,
3774 );
3775 Ok(sql)
3776 }
3777
3778 /// Build SQL command for DELETE statement
3779 fn sql_delete(&self) -> Result<String> {
3780 // Checks
3781 if let Some(err) = &self.error {
3782 return Err(err.clone().into());
3783 }
3784 if self.table.is_empty() {
3785 return Err(SqlBuilderError::NoTableName.into());
3786 }
3787
3788 // Make WHERE part
3789 let wheres = SqlBuilder::make_wheres(&self.wheres);
3790
3791 // Make SQL
3792 let sql = format!(
3793 "DELETE FROM {table}{wheres};",
3794 table = &self.table,
3795 wheres = wheres,
3796 );
3797 Ok(sql)
3798 }
3799
3800 /// Make WHERE part
3801 fn make_wheres(wheres: &[String]) -> String {
3802 match wheres.len() {
3803 0 => String::new(),
3804 1 => {
3805 let wheres = wheres[0].to_string();
3806 format!(" WHERE {}", wheres)
3807 }
3808 _ => {
3809 let wheres: Vec<String> = wheres.iter().map(|w| format!("({})", w)).collect();
3810 format!(" WHERE {}", wheres.join(" AND "))
3811 }
3812 }
3813 }
3814}
3815
3816/// Escape string for SQL.
3817///
3818/// ```
3819/// use sql_builder::esc;
3820///
3821/// let sql = esc("Hello, 'World'");
3822///
3823/// assert_eq!(&sql, "Hello, ''World''");
3824/// ```
3825pub fn esc<S: ToString>(src: S) -> String {
3826 src.to_string().replace("'", "''")
3827}
3828
3829/// Quote string for SQL.
3830///
3831/// ```
3832/// use sql_builder::quote;
3833///
3834/// let sql = quote("Hello, 'World'");
3835///
3836/// assert_eq!(&sql, "'Hello, ''World'''");
3837/// ```
3838pub fn quote<S: ToString>(src: S) -> String {
3839 format!("'{}'", esc(src.to_string()))
3840}
3841
3842/// Backquote string for SQL.
3843///
3844/// ```
3845/// use sql_builder::baquote;
3846///
3847/// let sql = baquote("Hello, 'World'");
3848///
3849/// assert_eq!(&sql, "`Hello, 'World'`");
3850/// ```
3851pub fn baquote<S: ToString>(src: S) -> String {
3852 format!("`{}`", src.to_string().replace("`", "\\`"))
3853}
3854
3855/// Quote string with [brackets].
3856///
3857/// ```
3858/// use sql_builder::brquote;
3859///
3860/// let sql = brquote("Hello, [awesome] World");
3861///
3862/// assert_eq!(&sql, "[Hello, [awesome]] World]");
3863/// ```
3864pub fn brquote<S: ToString>(src: S) -> String {
3865 format!("[{}]", src.to_string().replace("]", "]]"))
3866}
3867
3868/// Double quote string for SQL.
3869///
3870/// ```
3871/// use sql_builder::dquote;
3872///
3873/// let sql = dquote("Hello, 'World'");
3874///
3875/// assert_eq!(&sql, "\"Hello, 'World'\"");
3876/// ```
3877pub fn dquote<S: ToString>(src: S) -> String {
3878 format!("\"{}\"", src.to_string())
3879}
3880
3881#[cfg(test)]
3882mod tests {
3883 use super::*;
3884
3885 #[test]
3886 fn test_esc() -> Result<()> {
3887 let sql = esc("Hello, 'World'");
3888
3889 assert_eq!(&sql, "Hello, ''World''");
3890
3891 Ok(())
3892 }
3893
3894 #[test]
3895 fn test_quote() -> Result<()> {
3896 let sql = quote("Hello, 'World'");
3897 assert_eq!(&sql, "'Hello, ''World'''");
3898
3899 let sql = baquote("Hello, 'World'");
3900 assert_eq!(&sql, "`Hello, 'World'`");
3901
3902 let sql = dquote("Hello, 'World'");
3903 assert_eq!(&sql, "\"Hello, 'World'\"");
3904
3905 Ok(())
3906 }
3907
3908 #[test]
3909 fn test_select_only_values() -> Result<()> {
3910 let values = SqlBuilder::select_values(&["10", "e("100")]).sql()?;
3911
3912 assert_eq!("SELECT 10, '100';", &values);
3913
3914 Ok(())
3915 }
3916
3917 #[test]
3918 fn test_select_all_books() -> Result<()> {
3919 let sql = SqlBuilder::select_from("books").sql()?;
3920
3921 assert_eq!(&sql, "SELECT * FROM books;");
3922
3923 Ok(())
3924 }
3925
3926 #[test]
3927 fn test_show_all_prices() -> Result<()> {
3928 let sql = SqlBuilder::select_from("books")
3929 .distinct()
3930 .field("price")
3931 .sql()?;
3932
3933 assert_eq!(&sql, "SELECT DISTINCT price FROM books;");
3934
3935 Ok(())
3936 }
3937
3938 #[test]
3939 fn test_select_title_and_price() -> Result<()> {
3940 let sql = SqlBuilder::select_from("books")
3941 .fields(&["title", "price"])
3942 .sql()?;
3943
3944 assert_eq!(&sql, "SELECT title, price FROM books;");
3945
3946 let sql = SqlBuilder::select_from("books")
3947 .field("title")
3948 .field("price")
3949 .sql()?;
3950
3951 assert_eq!(&sql, "SELECT title, price FROM books;");
3952
3953 Ok(())
3954 }
3955
3956 #[test]
3957 fn test_select_expensive_books() -> Result<()> {
3958 let sql = SqlBuilder::select_from("books")
3959 .field("title")
3960 .field("price")
3961 .and_where("price > 100")
3962 .sql()?;
3963
3964 assert_eq!(&sql, "SELECT title, price FROM books WHERE price > 100;");
3965
3966 let sql = SqlBuilder::select_from("books")
3967 .field("title")
3968 .field("price")
3969 .and_where_gt("price", 200)
3970 .sql()?;
3971
3972 assert_eq!(&sql, "SELECT title, price FROM books WHERE price > 200;");
3973
3974 let sql = SqlBuilder::select_from("books")
3975 .field("title")
3976 .field("price")
3977 .and_where_ge("price", 300)
3978 .sql()?;
3979
3980 assert_eq!(&sql, "SELECT title, price FROM books WHERE price >= 300;");
3981
3982 Ok(())
3983 }
3984
3985 #[test]
3986 fn test_select_price_for_harry_potter_and_phil_stone() -> Result<()> {
3987 let sql = SqlBuilder::select_from("books")
3988 .field("price")
3989 .and_where_eq("title", quote("Harry Potter and the Philosopher's Stone"))
3990 .sql()?;
3991
3992 assert_eq!(
3993 &sql,
3994 "SELECT price FROM books WHERE title = 'Harry Potter and the Philosopher''s Stone';"
3995 );
3996
3997 Ok(())
3998 }
3999
4000 #[test]
4001 fn test_select_price_not_for_harry_potter_and_phil_stone() -> Result<()> {
4002 let sql = SqlBuilder::select_from("books")
4003 .field("price")
4004 .and_where_ne("title", quote("Harry Potter and the Philosopher's Stone"))
4005 .sql()?;
4006
4007 assert_eq!(
4008 &sql,
4009 "SELECT price FROM books WHERE title <> 'Harry Potter and the Philosopher''s Stone';"
4010 );
4011
4012 Ok(())
4013 }
4014
4015 #[test]
4016 fn test_select_expensive_harry_potter() -> Result<()> {
4017 let sql = SqlBuilder::select_from("books")
4018 .field("title")
4019 .field("price")
4020 .and_where("price > 100")
4021 .and_where_like_left("title", "Harry Potter")
4022 .sql()?;
4023
4024 assert_eq!(
4025 &sql,
4026 "SELECT title, price FROM books WHERE (price > 100) AND (title LIKE 'Harry Potter%');"
4027 );
4028
4029 Ok(())
4030 }
4031
4032 #[test]
4033 fn test_select_strange_books() -> Result<()> {
4034 let sql = SqlBuilder::select_from("books")
4035 .field("title")
4036 .field("price")
4037 .and_where("price < 2")
4038 .or_where("price > 1000")
4039 .or_where_eq("title", quote("Harry Potter and the Philosopher's Stone"))
4040 .or_where_ne("price", 100)
4041 .or_where_like("title", "Alice's")
4042 .or_where_not_like_any("LOWER(title)", " the ")
4043 .or_where_is_null("title")
4044 .or_where_is_not_null("price")
4045 .sql()?;
4046
4047 assert_eq!(
4048 &sql,
4049 "SELECT title, price FROM books WHERE price < 2 OR price > 1000 OR title = 'Harry Potter and the Philosopher''s Stone' OR price <> 100 OR title LIKE 'Alice''s' OR LOWER(title) NOT LIKE '% the %' OR title IS NULL OR price IS NOT NULL;"
4050 );
4051
4052 Ok(())
4053 }
4054
4055 #[test]
4056 fn test_order_harry_potter_by_price() -> Result<()> {
4057 let sql = SqlBuilder::select_from("books")
4058 .field("title")
4059 .field("price")
4060 .and_where_like_left("title", "Harry Potter")
4061 .order_by("price", false)
4062 .sql()?;
4063
4064 assert_eq!(
4065 &sql,
4066 "SELECT title, price FROM books WHERE title LIKE 'Harry Potter%' ORDER BY price;"
4067 );
4068
4069 let sql = SqlBuilder::select_from("books")
4070 .field("title")
4071 .field("price")
4072 .and_where_like_left("title", "Harry Potter")
4073 .order_desc("price")
4074 .sql()?;
4075
4076 assert_eq!(
4077 &sql,
4078 "SELECT title, price FROM books WHERE title LIKE 'Harry Potter%' ORDER BY price DESC;"
4079 );
4080
4081 let sql = SqlBuilder::select_from("books")
4082 .field("title")
4083 .field("price")
4084 .and_where_like_left("title", "Harry Potter")
4085 .order_desc("price")
4086 .order_asc("title")
4087 .sql()?;
4088
4089 assert_eq!(&sql, "SELECT title, price FROM books WHERE title LIKE 'Harry Potter%' ORDER BY price DESC, title;");
4090
4091 Ok(())
4092 }
4093
4094 #[test]
4095 fn test_find_cheap_or_harry_potter() -> Result<()> {
4096 let append = SqlBuilder::select_from("books")
4097 .field("title")
4098 .field("price")
4099 .and_where("price < 100")
4100 .order_asc("title")
4101 .query()?;
4102
4103 let sql = SqlBuilder::select_from("books")
4104 .field("title")
4105 .field("price")
4106 .and_where_like_left("title", "Harry Potter")
4107 .order_desc("price")
4108 .union(&append)
4109 .sql()?;
4110
4111 assert_eq!(
4112 "SELECT title, price FROM books WHERE title LIKE 'Harry Potter%' UNION SELECT title, price FROM books WHERE price < 100 ORDER BY title;",
4113 &sql
4114 );
4115
4116 let append = SqlBuilder::select_values(&["'The Great Gatsby'", "124"]).query_values()?;
4117
4118 let sql = SqlBuilder::select_from("books")
4119 .field("title")
4120 .field("price")
4121 .and_where_like_left("title", "Harry Potter")
4122 .order_desc("price")
4123 .union_all(&append)
4124 .sql()?;
4125
4126 assert_eq!(
4127 "SELECT title, price FROM books WHERE title LIKE 'Harry Potter%' UNION ALL SELECT 'The Great Gatsby', 124;",
4128 &sql
4129 );
4130
4131 Ok(())
4132 }
4133
4134 #[test]
4135 fn test_select_first_3_harry_potter_books() -> Result<()> {
4136 let sql = SqlBuilder::select_from("books")
4137 .field("title")
4138 .field("price")
4139 .and_where_like_left("title", "Harry Potter")
4140 .order_asc("title")
4141 .limit(3)
4142 .sql()?;
4143
4144 assert_eq!(&sql, "SELECT title, price FROM books WHERE title LIKE 'Harry Potter%' ORDER BY title LIMIT 3;");
4145
4146 Ok(())
4147 }
4148
4149 #[test]
4150 fn test_select_harry_potter_from_second_book() -> Result<()> {
4151 let sql = SqlBuilder::select_from("books")
4152 .field("title")
4153 .field("price")
4154 .and_where_like_left("title", "Harry Potter")
4155 .order_asc("title")
4156 .offset(2)
4157 .sql()?;
4158
4159 assert_eq!(&sql, "SELECT title, price FROM books WHERE title LIKE 'Harry Potter%' ORDER BY title OFFSET 2;");
4160
4161 let sql = SqlBuilder::select_from("books")
4162 .field("title")
4163 .field("price")
4164 .and_where_like_left("title", "Harry Potter")
4165 .order_asc("title")
4166 .limit(3)
4167 .offset(2)
4168 .sql()?;
4169
4170 assert_eq!(&sql, "SELECT title, price FROM books WHERE title LIKE 'Harry Potter%' ORDER BY title LIMIT 3 OFFSET 2;");
4171
4172 Ok(())
4173 }
4174
4175 #[test]
4176 fn test_find_books_not_about_alice() -> Result<()> {
4177 let sql = SqlBuilder::select_from("books")
4178 .field("title")
4179 .and_where_not_like_any("title", "Alice's")
4180 .sql()?;
4181
4182 assert_eq!(
4183 "SELECT title FROM books WHERE title NOT LIKE '%Alice''s%';",
4184 &sql
4185 );
4186
4187 Ok(())
4188 }
4189
4190 #[test]
4191 fn test_books_without_price() -> Result<()> {
4192 let sql = SqlBuilder::select_from("books")
4193 .field("title")
4194 .and_where_is_null("price")
4195 .sql()?;
4196
4197 assert_eq!(&sql, "SELECT title FROM books WHERE price IS NULL;");
4198
4199 let sql = SqlBuilder::select_from("books")
4200 .field("title")
4201 .and_where_is_not_null("price")
4202 .sql()?;
4203
4204 assert_eq!(&sql, "SELECT title FROM books WHERE price IS NOT NULL;");
4205
4206 Ok(())
4207 }
4208
4209 #[test]
4210 fn test_group_books_by_price() -> Result<()> {
4211 let sql = SqlBuilder::select_from("books")
4212 .field("price")
4213 .field("COUNT(price) AS cnt")
4214 .group_by("price")
4215 .order_desc("cnt")
4216 .sql()?;
4217
4218 assert_eq!(
4219 &sql,
4220 "SELECT price, COUNT(price) AS cnt FROM books GROUP BY price ORDER BY cnt DESC;"
4221 );
4222
4223 let sql = SqlBuilder::select_from("books")
4224 .field("price")
4225 .field("COUNT(price) AS cnt")
4226 .group_by("price")
4227 .having("price > 100")
4228 .order_desc("cnt")
4229 .sql()?;
4230
4231 assert_eq!(&sql, "SELECT price, COUNT(price) AS cnt FROM books GROUP BY price HAVING price > 100 ORDER BY cnt DESC;");
4232
4233 let sql = SqlBuilder::select_from("books")
4234 .field("price")
4235 .field("COUNT(price) AS cnt")
4236 .group_by("price")
4237 .and_where("price > 100")
4238 .order_desc("cnt")
4239 .sql()?;
4240
4241 assert_eq!(&sql, "SELECT price, COUNT(price) AS cnt FROM books WHERE price > 100 GROUP BY price ORDER BY cnt DESC;");
4242
4243 Ok(())
4244 }
4245
4246 #[test]
4247 fn test_group_books_by_price_category() -> Result<()> {
4248 let cat = SqlBuilder::select_from("books")
4249 .field("CASE WHEN price < 100 THEN 'cheap' ELSE 'expensive' END AS category")
4250 .subquery()?;
4251
4252 assert_eq!("(SELECT CASE WHEN price < 100 THEN 'cheap' ELSE 'expensive' END AS category FROM books)", &cat);
4253
4254 let sql = SqlBuilder::select_from(&cat)
4255 .field("category")
4256 .field("COUNT(category) AS cnt")
4257 .group_by("category")
4258 .order_desc("cnt")
4259 .order_asc("category")
4260 .sql()?;
4261
4262 assert_eq!("SELECT category, COUNT(category) AS cnt FROM (SELECT CASE WHEN price < 100 THEN 'cheap' ELSE 'expensive' END AS category FROM books) GROUP BY category ORDER BY cnt DESC, category;", &sql);
4263
4264 let cat = SqlBuilder::select_from("books")
4265 .field("CASE WHEN price < 100 THEN 'cheap' ELSE 'expensive' END")
4266 .subquery_as("category")?;
4267
4268 assert_eq!("(SELECT CASE WHEN price < 100 THEN 'cheap' ELSE 'expensive' END FROM books) AS category", &cat);
4269
4270 let sql = SqlBuilder::select_from("books")
4271 .field("title")
4272 .field("price")
4273 .field(&cat)
4274 .sql()?;
4275
4276 assert_eq!("SELECT title, price, (SELECT CASE WHEN price < 100 THEN 'cheap' ELSE 'expensive' END FROM books) AS category FROM books;", &sql);
4277
4278 Ok(())
4279 }
4280
4281 #[test]
4282 fn test_grow_price() -> Result<()> {
4283 let sql = SqlBuilder::update_table("books")
4284 .set("price", "price + 10")
4285 .sql()?;
4286
4287 assert_eq!(&sql, "UPDATE books SET price = price + 10;");
4288
4289 let sql = SqlBuilder::update_table("books")
4290 .set("price", "price * 0.1")
4291 .and_where_like_left("title", "Harry Potter")
4292 .returning_id()
4293 .sql()?;
4294
4295 assert_eq!(
4296 &sql,
4297 "UPDATE books SET price = price * 0.1 WHERE title LIKE 'Harry Potter%' RETURNING id;"
4298 );
4299
4300 Ok(())
4301 }
4302
4303 #[test]
4304 fn test_add_new_books() -> Result<()> {
4305 let sql = SqlBuilder::insert_into("books")
4306 .field("title")
4307 .field("price")
4308 .values(&[quote("In Search of Lost Time"), 150.to_string()])
4309 .values(&["'Don Quixote', 200"])
4310 .sql()?;
4311
4312 assert_eq!(&sql, "INSERT INTO books (title, price) VALUES ('In Search of Lost Time', 150), ('Don Quixote', 200);");
4313
4314 let sql = SqlBuilder::insert_into("books")
4315 .field("title")
4316 .field("price")
4317 .values(&["'Don Quixote', 200"])
4318 .returning_id()
4319 .sql()?;
4320
4321 assert_eq!(
4322 &sql,
4323 "INSERT INTO books (title, price) VALUES ('Don Quixote', 200) RETURNING id;"
4324 );
4325
4326 Ok(())
4327 }
4328
4329 #[test]
4330 fn test_add_books_from_warehouse() -> Result<()> {
4331 let query = SqlBuilder::select_from("warehouse")
4332 .field("title")
4333 .field("preliminary_price * 2")
4334 .query()?;
4335
4336 assert_eq!("SELECT title, preliminary_price * 2 FROM warehouse", &query);
4337
4338 let sql = SqlBuilder::insert_into("books")
4339 .field("title")
4340 .field("price")
4341 .select(&query)
4342 .sql()?;
4343
4344 assert_eq!(
4345 "INSERT INTO books (title, price) SELECT title, preliminary_price * 2 FROM warehouse;",
4346 &sql
4347 );
4348
4349 Ok(())
4350 }
4351
4352 #[test]
4353 fn test_sold_all_harry_potter() -> Result<()> {
4354 let sql = SqlBuilder::update_table("books")
4355 .set("price", 0)
4356 .set("title", "'[SOLD!]' || title")
4357 .and_where_like_left("title", "Harry Potter")
4358 .sql()?;
4359
4360 assert_eq!(&sql, "UPDATE books SET price = 0, title = '[SOLD!]' || title WHERE title LIKE 'Harry Potter%';");
4361
4362 Ok(())
4363 }
4364
4365 #[test]
4366 fn test_mark_as_not_distr() -> Result<()> {
4367 let sql = SqlBuilder::update_table("books")
4368 .set_str("comment", "Don't distribute!")
4369 .and_where_le("price", "100")
4370 .returning("id, comment")
4371 .sql()?;
4372
4373 assert_eq!(
4374 "UPDATE books SET comment = 'Don''t distribute!' WHERE price <= 100 RETURNING id, comment;",
4375 &sql
4376 );
4377
4378 Ok(())
4379 }
4380
4381 #[test]
4382 fn test_remove_all_expensive_books() -> Result<()> {
4383 let sql = SqlBuilder::delete_from("books")
4384 .and_where("price > 100")
4385 .sql()?;
4386
4387 assert_eq!(&sql, "DELETE FROM books WHERE price > 100;");
4388
4389 Ok(())
4390 }
4391
4392 #[test]
4393 fn test_count_books_in_shops() -> Result<()> {
4394 let sql = SqlBuilder::select_from("books AS b")
4395 .field("b.title")
4396 .field("s.total")
4397 .left_outer()
4398 .join("shops AS s")
4399 .on("b.id = s.book")
4400 .sql()?;
4401
4402 assert_eq!(
4403 &sql,
4404 "SELECT b.title, s.total FROM books AS b LEFT OUTER JOIN shops AS s ON b.id = s.book;"
4405 );
4406
4407 let sql = SqlBuilder::select_from("books AS b")
4408 .field("b.title")
4409 .field("s.total")
4410 .left_outer()
4411 .join("shops AS s")
4412 .on_eq("b.id", "s.book")
4413 .sql()?;
4414
4415 assert_eq!(
4416 &sql,
4417 "SELECT b.title, s.total FROM books AS b LEFT OUTER JOIN shops AS s ON b.id = s.book;"
4418 );
4419
4420 Ok(())
4421 }
4422
4423 #[test]
4424 fn test_drop_error() -> Result<()> {
4425 let sql = SqlBuilder::select_from("books")
4426 .and_where("")
4427 .drop_error()
4428 .sql()?;
4429
4430 assert_eq!(&sql, "SELECT * FROM books;");
4431
4432 Ok(())
4433 }
4434
4435 #[test]
4436 fn test_empty_where() -> Result<()> {
4437 let res = SqlBuilder::select_from("books").and_where("").sql();
4438 if let Err(err) = res {
4439 assert_eq!(&err.to_string(), "WHERE condition is empty");
4440 } else {
4441 panic!("Error checking does not works");
4442 }
4443
4444 Ok(())
4445 }
4446
4447 #[test]
4448 fn test_empty_where_eq() -> Result<()> {
4449 let res = SqlBuilder::select_from("books")
4450 .and_where_eq("", "aaa")
4451 .sql();
4452 if let Err(err) = res {
4453 assert_eq!(&err.to_string(), "WHERE field not defined");
4454 } else {
4455 panic!("Error checking does not works");
4456 }
4457
4458 Ok(())
4459 }
4460}
4461
4462//#[cfg(test)]
4463//mod benches {
4464// use super::*;
4465// use test::Bencher;
4466//
4467// #[bench]
4468// fn bench_select_from(b: &mut Bencher) {
4469// b.iter(|| SqlBuilder::select_from("foo"));
4470// }
4471//
4472// #[bench]
4473// fn bench_select_values(b: &mut Bencher) {
4474// b.iter(|| SqlBuilder::select_values(&["foo", "bar"]));
4475// }
4476//
4477// #[bench]
4478// fn bench_insert_into(b: &mut Bencher) {
4479// b.iter(|| SqlBuilder::insert_into("foo"));
4480// }
4481//
4482// #[bench]
4483// fn bench_update_table(b: &mut Bencher) {
4484// b.iter(|| SqlBuilder::update_table("foo"));
4485// }
4486//
4487// #[bench]
4488// fn bench_delete_from(b: &mut Bencher) {
4489// b.iter(|| SqlBuilder::delete_from("foo"));
4490// }
4491//
4492// #[bench]
4493// fn bench_natural(b: &mut Bencher) {
4494// let builder = SqlBuilder::select_from("foo");
4495// b.iter(|| {
4496// let mut b = builder.clone();
4497// b.natural();
4498// });
4499// }
4500//
4501// //#[bench]
4502// //fn bench_left(b: &mut Bencher) {
4503// // b.iter(|| left());
4504// //}
4505//
4506// //#[bench]
4507// //fn bench_left_outer(b: &mut Bencher) {
4508// // b.iter(|| left_outer());
4509// //}
4510//
4511// //#[bench]
4512// //fn bench_right(b: &mut Bencher) {
4513// // b.iter(|| right());
4514// //}
4515//
4516// //#[bench]
4517// //fn bench_right_outer(b: &mut Bencher) {
4518// // b.iter(|| right_outer());
4519// //}
4520//
4521// //#[bench]
4522// //fn bench_inner(b: &mut Bencher) {
4523// // b.iter(|| inner());
4524// //}
4525//
4526// //#[bench]
4527// //fn bench_cross(b: &mut Bencher) {
4528// // b.iter(|| cross());
4529// //}
4530//
4531// //#[bench]
4532// //fn bench_join(b: &mut Bencher) {
4533// // b.iter(|| join());
4534// //}
4535//
4536// //#[bench]
4537// //fn bench_on(b: &mut Bencher) {
4538// // b.iter(|| on());
4539// //}
4540//
4541// //#[bench]
4542// //fn bench_distinct(b: &mut Bencher) {
4543// // b.iter(|| distinct());
4544// //}
4545//
4546// //#[bench]
4547// //fn bench_fields(b: &mut Bencher) {
4548// // b.iter(|| fields());
4549// //}
4550//
4551// //#[bench]
4552// //fn bench_set_fields(b: &mut Bencher) {
4553// // b.iter(|| set_fields());
4554// //}
4555//
4556// //#[bench]
4557// //fn bench_field(b: &mut Bencher) {
4558// // b.iter(|| field());
4559// //}
4560//
4561// //#[bench]
4562// //fn bench_set_field(b: &mut Bencher) {
4563// // b.iter(|| set_field());
4564// //}
4565//
4566// //#[bench]
4567// //fn bench_set(b: &mut Bencher) {
4568// // b.iter(|| set());
4569// //}
4570//
4571// //#[bench]
4572// //fn bench_set_str(b: &mut Bencher) {
4573// // b.iter(|| set_str());
4574// //}
4575//
4576// //#[bench]
4577// //fn bench_values(b: &mut Bencher) {
4578// // b.iter(|| values());
4579// //}
4580//
4581// //#[bench]
4582// //fn bench_select(b: &mut Bencher) {
4583// // b.iter(|| select());
4584// //}
4585//
4586// //#[bench]
4587// //fn bench_group_by(b: &mut Bencher) {
4588// // b.iter(|| group_by());
4589// //}
4590//
4591// //#[bench]
4592// //fn bench_having(b: &mut Bencher) {
4593// // b.iter(|| having());
4594// //}
4595//
4596// //#[bench]
4597// //fn bench_and_where(b: &mut Bencher) {
4598// // b.iter(|| and_where());
4599// //}
4600//
4601// //#[bench]
4602// //fn bench_and_where_eq(b: &mut Bencher) {
4603// // b.iter(|| and_where_eq());
4604// //}
4605//
4606// //#[bench]
4607// //fn bench_and_where_ne(b: &mut Bencher) {
4608// // b.iter(|| and_where_ne());
4609// //}
4610//
4611// //#[bench]
4612// //fn bench_and_where_gt(b: &mut Bencher) {
4613// // b.iter(|| and_where_gt());
4614// //}
4615//
4616// //#[bench]
4617// //fn bench_and_where_ge(b: &mut Bencher) {
4618// // b.iter(|| and_where_ge());
4619// //}
4620//
4621// //#[bench]
4622// //fn bench_and_where_lt(b: &mut Bencher) {
4623// // b.iter(|| and_where_lt());
4624// //}
4625//
4626// //#[bench]
4627// //fn bench_and_where_le(b: &mut Bencher) {
4628// // b.iter(|| and_where_le());
4629// //}
4630//
4631// //#[bench]
4632// //fn bench_and_where_like(b: &mut Bencher) {
4633// // b.iter(|| and_where_like());
4634// //}
4635//
4636// //#[bench]
4637// //fn bench_and_where_like_right(b: &mut Bencher) {
4638// // b.iter(|| and_where_like_right());
4639// //}
4640//
4641// //#[bench]
4642// //fn bench_and_where_like_left(b: &mut Bencher) {
4643// // b.iter(|| and_where_like_left());
4644// //}
4645//
4646// //#[bench]
4647// //fn bench_and_where_like_any(b: &mut Bencher) {
4648// // b.iter(|| and_where_like_any());
4649// //}
4650//
4651// //#[bench]
4652// //fn bench_and_where_not_like(b: &mut Bencher) {
4653// // b.iter(|| and_where_not_like());
4654// //}
4655//
4656// //#[bench]
4657// //fn bench_and_where_not_like_right(b: &mut Bencher) {
4658// // b.iter(|| and_where_not_like_right());
4659// //}
4660//
4661// //#[bench]
4662// //fn bench_and_where_not_like_left(b: &mut Bencher) {
4663// // b.iter(|| and_where_not_like_left());
4664// //}
4665//
4666// //#[bench]
4667// //fn bench_and_where_not_like_any(b: &mut Bencher) {
4668// // b.iter(|| and_where_not_like_any());
4669// //}
4670//
4671// //#[bench]
4672// //fn bench_and_where_is_null(b: &mut Bencher) {
4673// // b.iter(|| and_where_is_null());
4674// //}
4675//
4676// //#[bench]
4677// //fn bench_and_where_is_not_null(b: &mut Bencher) {
4678// // b.iter(|| and_where_is_not_null());
4679// //}
4680//
4681// //#[bench]
4682// //fn bench_or_where(b: &mut Bencher) {
4683// // b.iter(|| or_where());
4684// //}
4685//
4686// //#[bench]
4687// //fn bench_or_where_eq(b: &mut Bencher) {
4688// // b.iter(|| or_where_eq());
4689// //}
4690//
4691// //#[bench]
4692// //fn bench_or_where_ne(b: &mut Bencher) {
4693// // b.iter(|| or_where_ne());
4694// //}
4695//
4696// //#[bench]
4697// //fn bench_or_where_gt(b: &mut Bencher) {
4698// // b.iter(|| or_where_gt());
4699// //}
4700//
4701// //#[bench]
4702// //fn bench_or_where_ge(b: &mut Bencher) {
4703// // b.iter(|| or_where_ge());
4704// //}
4705//
4706// //#[bench]
4707// //fn bench_or_where_lt(b: &mut Bencher) {
4708// // b.iter(|| or_where_lt());
4709// //}
4710//
4711// //#[bench]
4712// //fn bench_or_where_le(b: &mut Bencher) {
4713// // b.iter(|| or_where_le());
4714// //}
4715//
4716// //#[bench]
4717// //fn bench_or_where_like(b: &mut Bencher) {
4718// // b.iter(|| or_where_like());
4719// //}
4720//
4721// //#[bench]
4722// //fn bench_or_where_like_right(b: &mut Bencher) {
4723// // b.iter(|| or_where_like_right());
4724// //}
4725//
4726// //#[bench]
4727// //fn bench_or_where_like_left(b: &mut Bencher) {
4728// // b.iter(|| or_where_like_left());
4729// //}
4730//
4731// //#[bench]
4732// //fn bench_or_where_like_any(b: &mut Bencher) {
4733// // b.iter(|| or_where_like_any());
4734// //}
4735//
4736// //#[bench]
4737// //fn bench_or_where_not_like(b: &mut Bencher) {
4738// // b.iter(|| or_where_not_like());
4739// //}
4740//
4741// //#[bench]
4742// //fn bench_or_where_not_like_right(b: &mut Bencher) {
4743// // b.iter(|| or_where_not_like_right());
4744// //}
4745//
4746// //#[bench]
4747// //fn bench_or_where_not_like_left(b: &mut Bencher) {
4748// // b.iter(|| or_where_not_like_left());
4749// //}
4750//
4751// //#[bench]
4752// //fn bench_or_where_not_like_any(b: &mut Bencher) {
4753// // b.iter(|| or_where_not_like_any());
4754// //}
4755//
4756// //#[bench]
4757// //fn bench_or_where_is_null(b: &mut Bencher) {
4758// // b.iter(|| or_where_is_null());
4759// //}
4760//
4761// //#[bench]
4762// //fn bench_or_where_is_not_null(b: &mut Bencher) {
4763// // b.iter(|| or_where_is_not_null());
4764// //}
4765//
4766// //#[bench]
4767// //fn bench_union(b: &mut Bencher) {
4768// // b.iter(|| union());
4769// //}
4770//
4771// //#[bench]
4772// //fn bench_union_all(b: &mut Bencher) {
4773// // b.iter(|| union_all());
4774// //}
4775//
4776// //#[bench]
4777// //fn bench_order_by(b: &mut Bencher) {
4778// // b.iter(|| order_by());
4779// //}
4780//
4781// //#[bench]
4782// //fn bench_order_asc(b: &mut Bencher) {
4783// // b.iter(|| order_asc());
4784// //}
4785//
4786// //#[bench]
4787// //fn bench_order_desc(b: &mut Bencher) {
4788// // b.iter(|| order_desc());
4789// //}
4790//
4791// //#[bench]
4792// //fn bench_limit(b: &mut Bencher) {
4793// // b.iter(|| limit());
4794// //}
4795//
4796// //#[bench]
4797// //fn bench_offset(b: &mut Bencher) {
4798// // b.iter(|| offset());
4799// //}
4800//
4801// //#[bench]
4802// //fn bench_sql(b: &mut Bencher) {
4803// // b.iter(|| sql());
4804// //}
4805//
4806// //#[bench]
4807// //fn bench_subquery(b: &mut Bencher) {
4808// // b.iter(|| subquery());
4809// //}
4810//
4811// //#[bench]
4812// //fn bench_subquery_as(b: &mut Bencher) {
4813// // b.iter(|| subquery_as());
4814// //}
4815//
4816// //#[bench]
4817// //fn bench_query(b: &mut Bencher) {
4818// // b.iter(|| query());
4819// //}
4820//
4821// //#[bench]
4822// //fn bench_query_values(b: &mut Bencher) {
4823// // b.iter(|| query_values());
4824// //}
4825//
4826// #[bench]
4827// fn bench_esc(b: &mut Bencher) {
4828// b.iter(|| esc("Hello, 'World'"));
4829// }
4830//
4831// #[bench]
4832// fn bench_quote(b: &mut Bencher) {
4833// b.iter(|| quote("Hello, 'World'"));
4834// }
4835//
4836// #[bench]
4837// fn bench_x_select_only_values(b: &mut Bencher) {
4838// b.iter(|| SqlBuilder::select_values(&["10", "e("100")]).sql());
4839// }
4840//
4841// #[bench]
4842// fn bench_x_select_all_books(b: &mut Bencher) {
4843// b.iter(|| SqlBuilder::select_from("books").sql());
4844// }
4845//
4846// #[bench]
4847// fn bench_x_show_all_prices(b: &mut Bencher) {
4848// b.iter(|| {
4849// SqlBuilder::select_from("books")
4850// .distinct()
4851// .field("price")
4852// .sql()
4853// });
4854// }
4855//
4856// #[bench]
4857// fn bench_x_select_title_and_price_1(b: &mut Bencher) {
4858// b.iter(|| {
4859// SqlBuilder::select_from("books")
4860// .fields(&["title", "price"])
4861// .sql()
4862// });
4863// }
4864//
4865// #[bench]
4866// fn bench_x_select_title_and_price_2(b: &mut Bencher) {
4867// b.iter(|| {
4868// SqlBuilder::select_from("books")
4869// .field("title")
4870// .field("price")
4871// .sql()
4872// });
4873// }
4874//
4875// #[bench]
4876// fn bench_x_select_expensive_books_1(b: &mut Bencher) {
4877// b.iter(|| {
4878// SqlBuilder::select_from("books")
4879// .field("title")
4880// .field("price")
4881// .and_where("price > 100")
4882// .sql()
4883// });
4884// }
4885//
4886// #[bench]
4887// fn bench_x_select_expensive_books_2(b: &mut Bencher) {
4888// b.iter(|| {
4889// SqlBuilder::select_from("books")
4890// .field("title")
4891// .field("price")
4892// .and_where_gt("price", 200)
4893// .sql()
4894// });
4895// }
4896//}