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