qubl/
lib.rs

1/// Struct that benefits to build queries for interactions with rdbms's.
2#[derive(Debug, Clone)]
3pub struct QueryBuilder<'a> {
4    pub query: String,
5    pub table: String,
6    pub qtype: QueryType,
7    pub list: Vec<KeywordList>,
8    pub hq: Option<[&'a str; 26]>
9}
10
11/// Implementations For QueryBuilder.
12impl<'a> QueryBuilder<'a> {
13    /// Select constructor. Use it if you want to build a Select Query.
14    /// 
15    /// ```rust
16    /// 
17    /// use qubl::{QueryBuilder, ValueType};
18    /// 
19    /// fn main(){
20    ///     let query = QueryBuilder::select(vec!["*"]).unwrap();
21    /// }
22    /// 
23    /// ```
24    pub fn select(fields: Vec<&str>) -> std::result::Result<Self, std::io::Error> {
25        match fields.len() {
26            0 => panic!("you cannot pass an empty vector to the fields argument"),
27            _ => ()
28        }
29
30        let hq = Self::load_hqs();
31        match Self::sanitize_columns(&fields, hq) {
32            Ok(_) => {
33                if fields.len() > 1 && fields[0] == "*" {
34                    let query = "SELECT * FROM".to_string();
35    
36                    return Ok(QueryBuilder {
37                        query,
38                        table: "".to_string(),
39                        qtype: QueryType::Select,
40                        list: vec![KeywordList::Select],
41                        hq: Some(hq)
42                    })
43                } else {
44                    let mut query = "SELECT ".to_string();
45
46                    let length_of_fields = fields.len();
47    
48                    for (i , field) in fields.into_iter().enumerate() {
49                        if i + 1 == length_of_fields {
50                            query = format!("{}{} ", query, field);
51                        } else {
52                            query = format!("{}{}, ", query, field);
53                        }
54                    }
55    
56                    let query = format!("{}FROM", query);
57    
58                    return Ok(QueryBuilder {
59                        query,
60                        table: "".to_string(),
61                        qtype: QueryType::Select,
62                        list: vec![KeywordList::Select],
63                        hq: Some(hq)
64                    })
65                }
66            },
67            Err(_) => {
68                return Err(std::io::Error::new(std::io::ErrorKind::PermissionDenied, "query cannot build because inserted arbitrary query."))
69            }
70        }
71    }
72
73    /// Delete constructor. Use it if you want to build a Delete Query.
74    /// 
75    /// ```rust
76    /// 
77    /// use qubl::{QueryBuilder, ValueType};
78    /// 
79    /// fn main(){
80    ///     let query = QueryBuilder::delete().unwrap();
81    /// }
82    /// 
83    /// ```
84    pub fn delete() -> std::result::Result<Self, std::io::Error> {
85        return Ok(QueryBuilder {
86            query: "DELETE FROM".to_string(),
87            table: "".to_string(),
88            qtype: QueryType::Delete,
89            list: vec![KeywordList::Delete],
90            hq: None
91        })
92    }
93
94    /// Update constructor. Use it if you want to build a Update Query.
95    /// 
96    /// ```rust
97    /// 
98    /// use qubl::{QueryBuilder, ValueType};
99    /// 
100    /// fn main(){
101    ///     let query = QueryBuilder::update().unwrap();
102    /// }
103    /// 
104    /// ```
105    pub fn update() -> std::result::Result<Self, std::io::Error> {
106        return Ok(QueryBuilder {
107            query: "UPDATE".to_string(),
108            table: "".to_string(),
109            qtype: QueryType::Update,
110            list: vec![KeywordList::Update],
111            hq: None
112        })
113    }
114
115    /// Insert constructor. Use it if you want to build a Insert Query.
116    ///     
117    /// ```rust
118    /// 
119    /// use qubl::{QueryBuilder, ValueType};
120    /// 
121    /// fn main(){
122    ///     let fields = vec!["id", "age", "name"];
123    ///     let values = vec![ValueType::Int32(5), ValueType::Int64(25), ValueType::String("necdet".to_string())]
124    /// 
125    ///     let query = QueryBuilder::insert(fields, values).unwrap();
126    /// }
127    /// 
128    /// ```
129    pub fn insert(columns: Vec<&str>, values: Vec<ValueType>) -> std::result::Result<Self, std::io::Error> {
130        match values.len() {
131            0 => panic!("you cannot pass an empty vector to the values argument"),
132            _ => ()
133        }
134
135        let mut query = "INSERT INTO".to_string();
136
137        let hq = Self::load_hqs();
138
139        match QueryBuilder::sanitize_columns(&columns, hq) {
140            Ok(_) => (),
141            Err(_) => {
142                return Err(std::io::Error::new(std::io::ErrorKind::PermissionDenied, "query cannot build on insert constructor: because inserted arbitrary query on columns parameter."))
143            }
144        }
145
146        match QueryBuilder::sanitize_inputs(&values, hq) {
147            Ok(_) => (),
148            Err(_) => {
149                return Err(std::io::Error::new(std::io::ErrorKind::PermissionDenied, "query cannot build on insert constructor: because inserted arbitrary query in values parameter."))
150
151            }
152        }
153
154        let mut columns_string = "(".to_string();
155        let mut values_string = "(".to_string();
156
157        for (i, column) in columns.into_iter().enumerate() {
158            for (p, value) in values.iter().enumerate() {
159                match i == p {
160                    true => {
161                        if i == 0 {
162                            columns_string = format!("{}{}", columns_string, column);        
163                        } else {
164                            columns_string = format!("{}, {}", columns_string, column);
165                        }
166
167                        if p == 0 {
168                            values_string = format!("{}{}", values_string, value);
169                        } else {
170                            values_string = format!("{}, {}", values_string, value);
171                        }
172                    },
173                    false => continue
174                }
175            }
176        }
177
178        query = format!("{} {}) VALUES {})", query, columns_string, values_string);
179
180        return Ok(Self {
181            query,
182            table: "".to_string(),
183            qtype: QueryType::Insert,
184            list: vec![KeywordList::Insert],
185            hq: Some(hq)
186        })
187    }
188
189    /// define the table. It should came after the constructors.
190    /// 
191    /// ```rust
192    /// 
193    /// use qubl::{QueryBuilder, ValueType};
194    /// 
195    /// fn main(){
196    ///     let query = QueryBuilder::select(vec!["*"]).unwrap().table("users");
197    /// }
198    /// 
199    /// ```
200    pub fn table(&mut self, table: &str) -> &mut Self {
201        match self.qtype {
202            QueryType::Select => {
203                self.query = format!("{} {}", self.query, table);
204                self.table = table.to_string();
205            },
206            QueryType::Delete => {
207                self.query = format!("{} {}", self.query, table);
208                self.table = table.to_string()
209            },
210            QueryType::Insert => {
211                let split_the_query = self.query.split(" INTO ").collect::<Vec<&str>>();
212    
213                self.query = format!("INSERT INTO {} {}", table, split_the_query[1]);
214                self.table = table.to_string();
215            }
216            QueryType::Update => {
217                self.query = format!("{} {}", self.query, table);
218                self.table = table.to_string()
219            },
220            QueryType::Count => {
221                self.query = format!("{} {}", self.query, table);
222                self.table = table.to_string()
223            }
224            QueryType::Null => panic!("You cannot add a table before you start a query"),
225            QueryType::Create => panic!("You cannot use create keyword with a QueryBuilder instance")
226        }
227    
228        self.list.push(KeywordList::Table);
229    
230        self
231    }
232    
233
234    /// Count constructor. Use it if you want to learn to length of a table.
235    ///     
236    /// ```rust
237    /// 
238    /// use qubl::{QueryBuilder, ValueType};
239    /// 
240    /// fn main(){
241    ///     let query = QueryBuilder::count("*", Some("length")).table("users");
242    /// }
243    /// 
244    /// ```
245    pub fn count(condition: &str, _as: Option<&str>) -> Self {
246        let query;
247
248        match _as {
249            Some(_as) => query = format!("SELECT COUNT({}) AS {} FROM", condition, _as),
250            None => query = format!("SELECT COUNT({}) FROM", condition)
251        };
252
253        return Self {
254            query,
255            table: "".to_string(),
256            qtype: QueryType::Count,
257            list: vec![KeywordList::Count],
258            hq: Some(Self::load_hqs())
259        }
260    }
261    /// add the "WHERE" keyword with it's synthax.
262    /// ```rust
263    /// 
264    /// use qubl::{QueryBuilder, ValueType};
265    /// 
266    /// fn main(){
267    ///     let query = QueryBuilder::select(vec!["*"]).unwrap().table("users").where_("id", "=", ValueType::Int32(5)).finish();
268    /// 
269    ///     assert_eq!(query, "SELECT * FROM users WHERE id = 5;")
270    /// }
271    /// 
272    /// ```
273    pub fn where_(&mut self, column: &str, mut mark: &str, value: ValueType) -> &mut Self {
274        match Self::sanitize_mark(mark) {
275            Ok(_) => (),
276            Err(error) => panic!("{}", error)
277        }
278
279        match self.hq {
280            Some(_) => (),
281            None => self.hq = Some(Self::load_hqs())
282        }
283
284        match self.sanitize_column(&column) {
285            Ok(_) => (),
286            Err(error) => panic!("{}", error)
287        }
288
289        match self.sanitize_input(&value) {
290            Ok(_) => (),
291            Err(error) => panic!("{}", error)
292        }
293
294        if let ValueType::Null = value {
295            match mark {
296                "=" => mark = "IS",
297                "!=" | "<>" => mark = "IS NOT",
298                "IS" | "IS NOT" => (),
299                _ => mark = "IS"
300            }
301        }
302
303        self.query = format!("{} WHERE {} {} {}", self.query, column, mark, value);
304
305        self.list.push(KeywordList::Where);
306
307        self
308    }
309
310    /// It adds the "IN" keyword with it's synthax. Don't use ".where_cond()" method if you use it.
311    ///     
312    /// ```rust
313    /// 
314    /// use qubl::{QueryBuilder, ValueType};
315    /// 
316    /// fn main(){
317    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
318    ///     let query = QueryBuilder::select(vec!["*"]).unwrap().table("users").where_in("id", &ins).finish();
319    /// 
320    ///     assert_eq!(query, "SELECT * FROM users WHERE id IN (1, 5, 10);")
321    /// }
322    pub fn where_in(&mut self, column: &str, ins: &Vec<ValueType>) -> &mut Self {
323        match ins.len() {
324            0 => panic!("you cannot pass an empty vector to the ins argument"),
325            _ => ()
326        }
327
328        self.query = format!("{} WHERE {} IN (", self.query, column);
329
330        let length_of_ins = ins.len();
331
332        for (index, value) in ins.into_iter().enumerate() {
333            if index + 1 == length_of_ins {
334                self.query = format!("{}{})", self.query, value);
335                    
336                continue;
337            }
338
339            self.query = format!("{}{}, ", self.query, value);
340        }
341
342        self.list.push(KeywordList::WhereIn);
343        self
344    }
345
346    /// It adds the "NOT IN" keyword with it's synthax. Don't use ".where_cond()" method if you use it.
347    ///     
348    /// ```rust
349    /// 
350    /// use qubl::{QueryBuilder, ValueType};
351    /// 
352    /// fn main(){
353    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
354    ///     let query = QueryBuilder::select(vec!["*"]).unwrap().table("users").where_not_in("id", &ins).finish();
355    /// 
356    ///     assert_eq!(query, "SELECT * FROM users WHERE id NOT IN (1, 5, 10);")
357    /// }
358     pub fn where_not_in(&mut self, column: &str, ins: &Vec<ValueType>) -> &mut Self {
359        match ins.len() {
360            0 => panic!("you cannot pass an empty vector to the ins argument"),
361            _ => ()
362        }
363
364        self.query = format!("{} WHERE {} NOT IN (", self.query, column);
365
366        let length_of_ins = ins.len();
367
368        for (index, value) in ins.into_iter().enumerate() {
369            if index + 1 == length_of_ins {
370                self.query = format!("{}{})", self.query, value);
371                    
372                continue;
373            }
374
375            self.query = format!("{}{}, ", self.query, value);
376        }
377
378        self.list.push(KeywordList::WhereNotIn);
379        self
380    }
381
382    /// It adds the "IN" keyword with it's synthax and an empty condition, use it if you want to give more complex condition to "IN" keyword. Don't use ".where_cond()" with it.
383    ///
384    /// ```rust
385    /// 
386    /// use qubl::{QueryBuilder, ValueType};
387    /// 
388    /// fn main(){
389    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
390    ///     let query = QueryBuilder::select(vec!["*"]).unwrap().table("users").where_in_custom("id", "1, 5, 10").finish();
391    /// 
392    ///     assert_eq!(query, "SELECT * FROM users WHERE id IN (1, 5, 10);")
393    /// }
394    pub fn where_in_custom(&mut self, column: &str, query: &str) -> &mut Self {
395        self.query = format!("{} WHERE {} IN ({})", self.query, column, query);
396
397        self.list.push(KeywordList::WhereIn);
398        self
399    }
400
401    /// It adds the "NOT IN" keyword with it's synthax and an empty condition, use it if you want to give more complex condition to "NOT IN" keyword. Don't use ".where_cond()" with it.
402    ///    
403    /// ```rust
404    /// 
405    /// use qubl::{QueryBuilder, ValueType};
406    /// 
407    /// fn main(){
408    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
409    ///     let query = QueryBuilder::select(vec!["*"]).unwrap().table("users").where_not_in_custom("id", "1, 5, 10").finish();
410    /// 
411    ///     assert_eq!(query, "SELECT * FROM users WHERE id NOT IN (1, 5, 10);")
412    /// }
413    pub fn where_not_in_custom(&mut self, column: &str, query: &str) -> &mut Self {
414        self.query = format!("{} WHERE {} NOT IN ({})", self.query, column, query);
415
416        self.list.push(KeywordList::WhereNotIn);
417
418        self
419    }
420
421
422    /// It adds the "IN" keyword with it's synthax, with 'AND' keyword except 'WHERE'.
423    ///     
424    /// ```rust
425    /// 
426    /// use qubl::{QueryBuilder, ValueType};
427    /// 
428    /// fn main(){
429    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
430    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
431    ///                              .table("users")
432    ///                              .where_("class", "=", ValueType::String("10/c".to_string()))
433    ///                              .and_in("id", &ins)
434    ///                              .finish();
435    /// 
436    ///     assert_eq!(query, "SELECT * FROM users WHERE class = '10/c' AND id IN (1, 5, 10);")
437    /// }
438    pub fn and_in(&mut self, column: &str, ins: &Vec<ValueType>) -> &mut Self {
439        match ins.len() {
440            0 => panic!("you cannot pass an empty vector to the ins argument"),
441            _ => ()
442        }
443
444        self.query = format!("{} AND {} IN (", self.query, column);
445
446        let length_of_ins = ins.len();
447
448        for (index, value) in ins.into_iter().enumerate() {
449            if index + 1 == length_of_ins {
450                self.query = format!("{}{})", self.query, value);
451                    
452                continue;
453            }
454
455            self.query = format!("{}{}, ", self.query, value);
456        }
457
458        self.list.push(KeywordList::AndIn);
459        self
460    }
461
462
463    /// It adds the "NOT IN" keyword with it's synthax, with 'AND' keyword except 'WHERE'.
464    ///     
465    /// ```rust
466    /// 
467    /// use qubl::{QueryBuilder, ValueType};
468    /// 
469    /// fn main(){
470    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
471    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
472    ///                              .table("users")
473    ///                              .where_("class", "=", ValueType::String("10/c".to_string()))
474    ///                              .and_not_in("id", &ins)
475    ///                              .finish();
476    /// 
477    ///     assert_eq!(query, "SELECT * FROM users WHERE class = '10/c' AND id NOT IN (1, 5, 10);")
478    /// }
479    pub fn and_not_in(&mut self, column: &str, ins: &Vec<ValueType>) -> &mut Self {
480        match ins.len() {
481            0 => panic!("you cannot pass an empty vector to the ins argument"),
482            _ => ()
483        }
484
485        self.query = format!("{} AND {} NOT IN (", self.query, column);
486
487        let length_of_ins = ins.len();
488
489        for (index, value) in ins.into_iter().enumerate() {
490            if index + 1 == length_of_ins {
491                self.query = format!("{}{})", self.query, value);
492                    
493                continue;
494            }
495
496            self.query = format!("{}{}, ", self.query, value);
497        }
498
499        self.list.push(KeywordList::AndNotIn);
500        self
501    }
502
503    /// It adds the "IN" keyword with it's synthax, with 'AND' keyword except 'WHERE'.
504    ///     
505    /// ```rust
506    /// 
507    /// use qubl::{QueryBuilder, ValueType};
508    /// 
509    /// fn main(){
510    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
511    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
512    ///                              .table("users")
513    ///                              .where_("class", "=", ValueType::String("10/c".to_string()))
514    ///                              .and_in_custom("id", "1, 5, 10")
515    ///                              .finish();
516    /// 
517    ///     assert_eq!(query, "SELECT * FROM users WHERE class = '10/c' AND id IN (1, 5, 10);")
518    /// }
519    pub fn and_in_custom(&mut self, column: &str, query: &str) -> &mut Self {
520        self.query = format!("{} AND {} IN ({})", self.query, column, query);
521
522        self.list.push(KeywordList::AndIn);
523        self
524    }
525
526    /// It adds the "IN" keyword with it's synthax, with 'AND' keyword except 'WHERE'.
527    ///     
528    /// ```rust
529    /// 
530    /// use qubl::{QueryBuilder, ValueType};
531    /// 
532    /// fn main(){
533    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
534    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
535    ///                              .table("users")
536    ///                              .where_("class", "=", ValueType::String("10/c".to_string()))
537    ///                              .and_not_in_custom("id", "1, 5, 10")
538    ///                              .finish();
539    /// 
540    ///     assert_eq!(query, "SELECT * FROM users WHERE class = '10/c' AND id NOT IN (1, 5, 10);")
541    /// }
542    pub fn and_not_in_custom(&mut self, column: &str, query: &str) -> &mut Self {
543        self.query = format!("{} AND {} NOT IN ({})", self.query, column, query);
544
545        self.list.push(KeywordList::AndNotIn);
546
547        self
548    }
549
550    /// It adds the "IN" keyword with it's synthax, with 'OR' keyword except 'WHERE'.
551    ///     
552    /// ```rust
553    /// 
554    /// use qubl::{QueryBuilder, ValueType};
555    /// 
556    /// fn main(){
557    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
558    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
559    ///                              .table("users")
560    ///                              .where_("class", "=", ValueType::String("10/c".to_string()))
561    ///                              .or_in("id", &ins)
562    ///                              .finish();
563    /// 
564    ///     assert_eq!(query, "SELECT * FROM users WHERE class = '10/c' OR id IN (1, 5, 10);")
565    /// }
566    pub fn or_in(&mut self, column: &str, ins: &Vec<ValueType>) -> &mut Self {
567        match ins.len() {
568            0 => panic!("you cannot pass an empty vector to the ins argument"),
569            _ => ()
570        }
571
572        self.query = format!("{} OR {} IN (", self.query, column);
573
574        let length_of_ins = ins.len();
575
576        for (index, value) in ins.into_iter().enumerate() {
577            if index + 1 == length_of_ins {
578                self.query = format!("{}{})", self.query, value);
579                    
580                continue;
581            }
582
583            self.query = format!("{}{}, ", self.query, value);
584        }
585
586        self.list.push(KeywordList::AndIn);
587        self
588    }
589
590    /// It adds the "NOT IN" keyword with it's synthax, with 'OR' keyword except 'WHERE'.
591    ///     
592    /// ```rust
593    /// 
594    /// use qubl::{QueryBuilder, ValueType};
595    /// 
596    /// fn main(){
597    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
598    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
599    ///                              .table("users")
600    ///                              .where_("class", "=", ValueType::String("10/c".to_string()))
601    ///                              .or_not_in("id", &ins)
602    ///                              .finish();
603    /// 
604    ///     assert_eq!(query, "SELECT * FROM users WHERE class = '10/c' OR id NOT IN (1, 5, 10);")
605    /// }
606    pub fn or_not_in(&mut self, column: &str, ins: &Vec<ValueType>) -> &mut Self {
607        match ins.len() {
608            0 => panic!("you cannot pass an empty vector to the ins argument"),
609            _ => ()
610        }
611
612        self.query = format!("{} OR {} NOT IN (", self.query, column);
613
614        let length_of_ins = ins.len();
615
616        for (index, value) in ins.into_iter().enumerate() {
617            if index + 1 == length_of_ins {
618                self.query = format!("{}{})", self.query, value);
619                    
620                continue;
621            }
622
623            self.query = format!("{}{}, ", self.query, value);
624        }
625
626        self.list.push(KeywordList::AndNotIn);
627        self
628    }
629
630    /// It adds the "IN" keyword with it's synthax, with 'AND' keyword except 'WHERE'.
631    ///     
632    /// ```rust
633    /// 
634    /// use qubl::{QueryBuilder, ValueType};
635    /// 
636    /// fn main(){
637    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
638    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
639    ///                              .table("users")
640    ///                              .where_("class", "=", ValueType::String("10/c".to_string()))
641    ///                              .or_in_custom("id", "1, 5, 10")
642    ///                              .finish();
643    /// 
644    ///     assert_eq!(query, "SELECT * FROM users WHERE class = '10/c' OR id IN (1, 5, 10);")
645    /// }
646    pub fn or_in_custom(&mut self, column: &str, query: &str) -> &mut Self {
647        self.query = format!("{} OR {} IN ({})", self.query, column, query);
648
649        self.list.push(KeywordList::AndIn);
650        self
651    }
652
653    /// It adds the "IN" keyword with it's synthax, with 'AND' keyword except 'WHERE'.
654    ///     
655    /// ```rust
656    /// 
657    /// use qubl::{QueryBuilder, ValueType};
658    /// 
659    /// fn main(){
660    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
661    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
662    ///                              .table("users")
663    ///                              .where_("class", "=", ValueType::String("10/c".to_string()))
664    ///                              .or_not_in_custom("id", "1, 5, 10")
665    ///                              .finish();
666    /// 
667    ///     assert_eq!(query, "SELECT * FROM users WHERE class = '10/c' OR id NOT IN (1, 5, 10);")
668    /// }
669    /// 
670    /// ```
671    pub fn or_not_in_custom(&mut self, column: &str, query: &str) -> &mut Self {
672        self.query = format!("{} OR {} NOT IN ({})", self.query, column, query);
673
674        self.list.push(KeywordList::AndNotIn);
675
676        self
677    }
678
679    /// it opens a parenthesis without declaring it's first parameter. It adds this string to the query: "... (WHERE / AND / OR) (" It's suitable for more custom approach with parenthesis, if you only want to use WHERE, AND & OR queries, we suggest you to check `.open_parenthesis_with()` method.
680    /// 
681    /// ```rust
682    /// 
683    /// use qubl::{QueryBuilder, ValueType};
684    /// 
685    /// fn main {
686    ///    let query = QueryBuilder::select(vec!["*"]).unwrap()
687    ///                             .table("users")
688    ///                             .where_("grades", ">", ValueType::Int32(80))
689    ///                             .open_parenthesis(BracketType::And)
690    ///                             .and("height", ">", ValueType::Int32(170))
691    ///                             .or("weight", ">", ValueType::Int32(60))
692    ///                             .close_parenthesis() // you have to close the parenthesis, otherwise it'll panic.
693    ///                             .finish();
694    ///
695    ///    assert_eq!(query, "SELECT * FROM users WHERE grades > 80 AND ( AND height > 170 OR weight > 60);");
696    /// 
697    /// }
698    /// 
699    /// ```
700    pub fn open_parenthesis(&mut self, parenthesis_type: BracketType) -> &mut Self {
701        match self.list.last() {
702            Some(keyword) => match keyword {
703                _ => {
704                    self.query = format!("{} {} (", self.query, parenthesis_type);
705                    
706                    match parenthesis_type {
707                        BracketType::Where => self.list.push(KeywordList::LeftBracketWhere),
708                        BracketType::And => self.list.push(KeywordList::LeftBracketAnd),
709                        BracketType::Or => self.list.push(KeywordList::LeftBracketOr),
710                    }
711                }
712            },
713            None => panic!("that's impossible to come here.")
714        };
715
716        self
717    }
718
719    /// it opens a parenthesis with declaring it's first parameter, it suits very well the most common use cases of parenthesis.
720    /// 
721    /// ```rust
722    /// 
723    /// use qubl::{QueryBuilder, ValueType};
724    /// 
725    /// fn main {
726    /// 
727    ///    let query = QueryBuilder::select(vec!["*"]).unwrap()
728    ///                                    .table("users")
729    ///                                    .where_("grades", ">", ValueType::Int32(80))
730    ///                                    .open_parenthesis_with(BracketType::And, "height", ">", ValueType::Int32(170))
731    ///                                    .open_parenthesis_with(BracketType::Or, "weight", ">", ValueType::Int32(50))
732    ///                                    .and("weight", "<", ValueType::Int32(70))
733    ///                                    .close_parenthesis()
734    ///                                    .close_parenthesis()
735    ///                                    .finish();          
736    ///
737    ///    assert_eq!(query, "SELECT * FROM users WHERE grades > 80 AND (height > 170 OR (weight > 50 AND weight < 70));");    
738    /// 
739    /// }
740    /// 
741    /// ```
742    pub fn open_parenthesis_with(&mut self, parenthesis_type: BracketType, column: &str, mut mark: &str, value: ValueType) -> &mut Self {
743        if let ValueType::Null = value {
744            match mark {
745                "=" => mark = "IS",
746                "!=" | "<>" => mark = "IS NOT",
747                "IS" | "IS NOT" => (),
748                _ => mark = "IS"
749            }
750        }
751
752        match self.list.last() {
753            Some(keyword) => match keyword {
754                _ => {
755                    self.query = format!("{} {} ({} {} {}", self.query, parenthesis_type, column, mark, value);
756                    
757                    match parenthesis_type {
758                        BracketType::Where => self.list.push(KeywordList::LeftBracketWhere),
759                        BracketType::And => self.list.push(KeywordList::LeftBracketAnd),
760                        BracketType::Or => self.list.push(KeywordList::LeftBracketOr),
761                    }
762                }
763            },
764            None => panic!("that's impossible to come here.")
765        };
766
767        self
768    }
769
770    /// Parenthesis closer for open parenthesis. The combined amount of `.open_parenthesis()` and `.open_parenthesis_with()` must match to be the query produced correctly. It'll panic if there is no call of these methods or query don't have "(" character.
771    pub fn close_parenthesis(&mut self) -> &mut Self {
772        if !self.list.iter().any(|keyword| keyword == &KeywordList::LeftBracketWhere || keyword == &KeywordList::LeftBracketAnd || keyword == &KeywordList::LeftBracketOr) {
773            if !self.query.contains("(") {
774                panic!("There is no left bracket exists on that query, panicking....")
775            }
776        } else {
777            self.query = format!("{})", self.query)
778        }
779
780        self
781    }
782
783    /// it benefits to set timezone when you make your query. It's very flexible, always put on very beginning of the query, you can use it later than any other method.
784    pub fn time_zone(&mut self, timezone: Timezone) -> &mut Self {
785        self.query = format!("SET time_zone = {}; {}", timezone, self.query);
786
787        self.list.insert(1, KeywordList::Timezone);
788        self
789    }
790
791    /// it benefits to set global timezone when you make your query. It's very flexible, always put on very beginning of the query, you can use it later than any other method.
792    pub fn global_time_zone(&mut self, timezone: Timezone) -> &mut Self {
793        self.query = format!("SET GLOBAL time_zone = {}; {}", timezone, self.query);
794
795        self.list.insert(1, KeywordList::GlobalTimezone);
796        self
797    }
798
799    /// It adds the "OR" keyword with it's synthax. Warning: It's not ready yet to chaining "AND" and "OR" keywords, for now, applying that kind of complex query use ".append_custom()" method instead.
800    ///
801    /// ```rust
802    /// 
803    /// use qubl::{QueryBuilder, ValueType};
804    /// 
805    /// fn main(){
806    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
807    ///     let query = QueryBuilder::select(vec!["*"]).unwrap().table("users").where_("id", "=", ValueType::Int32(10)).or("name", "=", ValueType::String("necdet".to_string())).finish();
808    /// 
809    ///     assert_eq!(query, "SELECT * FROM users WHERE id = 10 OR name = 'necdet';")
810    /// }
811   pub fn or(&mut self, column: &str, mut mark: &str, value: ValueType) -> &mut Self {
812        match self.sanitize_column(column) {
813            Ok(_) => (),
814            Err(error) => panic!("{}", error)
815        }
816
817        match Self::sanitize_mark(mark) {
818            Ok(_) => (),
819            Err(error) => panic!("{}", error)
820        }
821
822        match self.sanitize_input(&value) {
823            Ok(_) => (),
824            Err(error) => panic!("{}", error)
825        }
826
827        if let ValueType::Null = value {
828            match mark {
829                "=" => mark = "IS",
830                "!=" | "<>" => mark = "IS NOT",
831                "IS" | "IS NOT" => (),
832                _ => mark = "IS"
833            }
834        }
835
836        self.query = format!("{} OR {} {} {}", self.query, column, mark, value);
837
838
839        self.list.push(KeywordList::Or);
840
841        self
842    }
843
844    /// It adds the "SET" keyword with it's synthax.
845    /// 
846    /// ```rust
847    /// 
848    /// use qubl::{QueryBuilder, ValueType};
849    /// 
850    /// fn main(){
851    ///     let query = QueryBuilder::update().unwrap()
852    ///                              .table("users")
853    ///                              .set("name", ValueType::String("arda".to_string()))
854    ///                              .where_("id", "=", ValueType::Int32(1))
855    ///                              .finish();
856    /// 
857    ///     assert_eq!(query, "UPDATE users SET name = 'arda' WHERE id = 1;")
858    /// }
859    /// 
860    /// ```
861    pub fn set(&mut self, column: &str, value: ValueType) -> &mut Self {
862        match self.hq {
863            Some(_) => (),
864            None => self.hq = Some(Self::load_hqs())
865        }
866
867        match self.sanitize_column(column) {
868            Ok(_) => (),
869            Err(error) => panic!("{}", error)
870        }
871
872        match self.sanitize_input(&value) {
873            Ok(_) => (),
874            Err(error) => panic!("{}", error)
875        }
876
877        match self.list.last() {
878            Some(keyword) => {
879                match keyword {
880                    KeywordList::Set => self.query = format!("{}, {} = {}", self.query, column, value),
881                    _ => self.query = format!("{} SET {} = {}", self.query, column, value)
882                }
883            },
884            None => panic!("that's impossible to come here.")
885        }
886
887        self.list.push(KeywordList::Set);
888
889        self
890    }
891
892    /// It adds the "AND" keyword with it's synthax. Warning: It's not ready yet to chaining "OR" and "AND" keywords, for now, applying that kind of complex query use ".append_custom()" method instead.
893    ///
894    /// ```rust
895    /// 
896    /// use qubl::{QueryBuilder, ValueType};
897    /// 
898    /// fn main(){
899    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
900    ///     let query = QueryBuilder::select(vec!["*"]).unwrap().table("users").where_("id", "=", ValueType::Int32(10)).and("name", "=", ValueType::String("necdet".to_string())).finish();
901    /// 
902    ///     assert_eq!(query, "SELECT * FROM users WHERE id = 10 AND name = 'necdet';")
903    /// }
904    pub fn and(&mut self, column: &str, mut mark: &str, value: ValueType) -> &mut Self {
905        match self.sanitize_column(column) {
906            Ok(_) => (),
907            Err(error) => panic!("{}", error)
908        }
909
910        match Self::sanitize_mark(mark) {
911            Ok(_) => (),
912            Err(error) => panic!("{}", error)
913        }
914
915        match self.sanitize_input(&value) {
916            Ok(_) => (),
917            Err(error) => panic!("{}", error)
918        }
919
920        if let ValueType::Null = value {
921            match mark {
922                "=" => mark = "IS",
923                "!=" | "<>" => mark = "IS NOT",
924                "IS" | "IS NOT" => (),
925                _ => mark = "IS"
926            }
927        }
928
929        self.query = format!("{} AND {} {} {}", self.query, column, mark, value);
930
931        self.list.push(KeywordList::And);
932
933        self
934    }
935
936    /// It adds the "OFFSET" keyword with it's synthax. Be careful about it's alignment with "LIMIT" keyword.
937    ///     
938    /// ```rust
939    /// 
940    /// use qubl::{QueryBuilder, ValueType};
941    /// 
942    /// fn main(){
943    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
944    ///     let query = QueryBuilder::select(vec!["*"]).unwrap().table("users").where_("id", "=", ValueType::Int32(10)).limit(5).offset(0).finish();
945    /// 
946    ///     assert_eq!(query, "SELECT * FROM users WHERE id = 10 LIMIT 5 OFFSET 0;")
947    /// }
948    pub fn offset(&mut self, offset: i32) -> &mut Self {
949        self.query = format!("{} OFFSET {}", self.query, offset);
950
951        self.list.push(KeywordList::Offset);
952
953        self
954    }
955
956    /// It adds the "LIMIT" keyword with it's synthax.
957    /// 
958    /// ```rust
959    /// 
960    /// use qubl::{QueryBuilder, ValueType};
961    /// 
962    /// fn main(){
963    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
964    ///     let query = QueryBuilder::select(vec!["*"]).unwrap().table("users").where_("id", "=", ValueType::Int32(10)).limit(5).offset(0).finish();
965    /// 
966    ///     assert_eq!(query, "SELECT * FROM users WHERE id = 10 LIMIT 5 OFFSET 0;")
967    /// }
968    pub fn limit(&mut self, limit: i32) -> &mut Self {
969        self.query = format!("{} LIMIT {}", self.query, limit);
970
971        self.list.push(KeywordList::Limit);
972
973        self
974    }
975
976    /// It adds the "LIKE" keyword with it's synthax.
977    /// ```rust
978    /// 
979    /// use qubl::{QueryBuilder, ValueType};
980    /// 
981    /// fn main(){
982    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
983    ///                              .table("blogs")
984    ///                              .like(vec!["description", "title"], "qubl is awesome!")
985    ///                              .finish();
986    /// 
987    ///     assert_eq!(query, "SELECT * FROM blogs WHERE description LIKE '%qubl is awesome!%' OR title LIKE '%qubl is awesome!%';")
988    /// }
989    /// 
990    /// // it has more niche and different usages, for them check the tests.
991    pub fn like(&mut self, columns: Vec<&str>, operand: &str) -> &mut Self {
992        match columns.len() {
993            0 => panic!("you cannot pass an empty vector to the columns"),
994            _ => ()
995        }
996
997        let hqs = match self.hq {
998            Some(hqs) => hqs,
999            None => {
1000                let load_hqs = Self::load_hqs();
1001                self.hq = Some(load_hqs);
1002
1003                load_hqs
1004            }
1005        };
1006
1007        match Self::sanitize_columns(&columns, hqs) {
1008            Ok(_) => {
1009                match self.sanitize_str(operand){
1010                    Ok(_) => (),
1011                    Err(error) => {
1012                        println!("That Error Occured in like method: {}", error);
1013                
1014                        self.list.push(KeywordList::Like);
1015        
1016                        return self
1017                    }
1018                }
1019        
1020                match self.list.last() {
1021                    Some(keyword) => {
1022                        if keyword == &KeywordList::Where || keyword == &KeywordList::WhereIn || keyword == &KeywordList::WhereNotIn {
1023                            let length_of_columns = columns.len();
1024        
1025                            for (i, column) in columns.into_iter().enumerate() {
1026                                match length_of_columns {
1027                                    1 => {
1028                                        if i == 0 {
1029                                            self.query = format!("{} AND {} LIKE '%{}%'", self.query, column, operand)
1030                                        }  
1031                                    },
1032                                    _ => {
1033                                        if i == 0 {
1034                                            self.query = format!("{} AND ({} LIKE '%{}%'", self.query, column, operand)
1035                                        } else if i + 1 == length_of_columns {
1036                                            self.query = format!("{} OR {} LIKE '%{}%')", self.query, column, operand)
1037                                        } else {
1038                                            self.query = format!("{} OR {} LIKE '%{}%'", self.query, column, operand)
1039                                        }
1040                                    }
1041                                }
1042                            }
1043                        } else if keyword == &KeywordList::LeftBracketWhere || keyword == &KeywordList::LeftBracketAnd || keyword == &KeywordList::LeftBracketOr {
1044                            for (i, column) in columns.into_iter().enumerate() {
1045                                if i == 0 {
1046                                    self.query = format!("{}{} LIKE '%{}%'", self.query, column, operand)
1047                                } else {
1048                                    self.query = format!("{}, AND {} LIKE '%{}%'", self.query, column, operand)
1049                                }
1050                            }
1051                        } else {
1052                            for (i, column) in columns.into_iter().enumerate() {
1053                                if i == 0 {
1054                                    self.query = format!("{} WHERE {} LIKE '%{}%'", self.query, column, operand);
1055                                } else {
1056                                    self.query = format!("{} OR {} LIKE '%{}%'", self.query, column, operand);
1057                                }
1058                            }
1059                        }
1060                    },
1061                    None => panic!("Our current implementation does not support to use '.like()' later not other than WHERE, IN or NOT IN queries.")
1062                }
1063
1064                return self
1065            },
1066            Err(error) => panic!("That error occured in '.like()' method: {}", error)
1067        }
1068    }
1069
1070    /// It adds the "ORDER BY" keyword with it's synthax. It only accepts "ASC", "DESC", "asc", "desc" values.
1071    /// ```rust
1072    /// 
1073    /// use qubl::{QueryBuilder, ValueType};
1074    /// 
1075    /// fn main(){
1076    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
1077    ///                              .table("users")
1078    ///                              .where_("age", ">", ValueType::Int32(25))
1079    ///                              .order_by("id", "ASC")
1080    ///                              .limit(5)
1081    ///                              .offset(0)
1082    ///                              .finish();
1083    /// 
1084    ///     assert_eq!(query, "SELECT * FROM users WHERE age > 25 ORDER BY id ASC LIMIT 5 OFFSET 0;")
1085    /// }
1086    pub fn order_by(&mut self, column: &str, mut ordering: &str) -> &mut Self {
1087        match self.sanitize_column(column) {
1088            Ok(_) => (),
1089            Err(error) => {
1090                println!("{}", error);
1091
1092                self.list.push(KeywordList::OrderBy);
1093
1094                return self
1095            }
1096        }
1097
1098        match ordering {
1099            "asc" => ordering = "ASC",
1100            "desc" => ordering = "DESC",
1101            "ASC" => ordering = "ASC",
1102            "DESC" => ordering = "DESC",
1103            &_ => panic!("Panicking in order_by method: There is no other ordering options than ASC or DESC.")
1104        }
1105
1106        match self.list.last() {
1107            Some(keyword) => match keyword {
1108                KeywordList::OrderBy | KeywordList::Field => self.query = format!("{}, {} {}", self.query, column, ordering),
1109                _ => self.query = format!("{} ORDER BY {} {}", self.query, column, ordering)
1110            },
1111            None => panic!("It's almost impossible you to come here.")
1112        }
1113
1114        self.list.push(KeywordList::OrderBy);
1115
1116        self
1117    }
1118
1119    /// A practical method that adds a query for shuffling the lines.
1120    /// ```rust
1121    /// 
1122    /// use qubl::{QueryBuilder, ValueType};
1123    /// 
1124    /// fn main(){
1125    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
1126    ///                              .table("users")
1127    ///                              .where_("age", ">", ValueType::Int32(25))
1128    ///                              .order_random()
1129    ///                              .limit(5)
1130    ///                              .offset(0)
1131    ///                              .finish();
1132    /// 
1133    ///     assert_eq!(query, "SELECT * FROM users WHERE age > 25 ORDER BY RAND() LIMIT 5 OFFSET 0;")
1134    /// }
1135    pub fn order_random(&mut self) -> &mut Self {
1136        if self.query.contains("ORDER BY") {
1137            panic!("Error in order_random method: you cannot add ordering option twice on a query.");
1138        }
1139
1140        self.query = format!("{} ORDER BY RAND()", self.query);
1141        self.list.push(KeywordList::OrderBy);
1142
1143        self
1144    }
1145
1146    /// Adds "FIELD()" function with it's synthax. It's used on ordering depending on strings.
1147    /// ```rust
1148    /// 
1149    /// use qubl::{QueryBuilder, ValueType};
1150    /// 
1151    /// fn main(){
1152    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
1153    ///                              .table("users")
1154    ///                              .where_("age", ">", ValueType::Int32(25))
1155    ///                              .order_by_field("role", vec!["admin", "member", "observer"])
1156    ///                              .limit(5)
1157    ///                              .offset(0)
1158    ///                              .finish();
1159    /// 
1160    ///     assert_eq!(query, "SELECT * FROM users WHERE age > 25 ORDER BY FIELD(role, 'admin', 'member', 'observer') LIMIT 5 OFFSET 0;")
1161    /// }
1162    pub fn order_by_field(&mut self, column: &str, ordering: Vec<&str>) -> &mut Self {
1163        match ordering.len() {
1164            0 => panic!("you cannot pass an empty vector to the ordering argument"),
1165            _ => ()
1166        }
1167
1168        match self.list.last() {
1169            Some(keyword) => match keyword {
1170                KeywordList::OrderBy => {
1171                    let mut split_the_query = self.query.split(" ORDER BY ");
1172                
1173                    self.query = format!("{} ORDER BY {}, FIELD({}", split_the_query.nth(0).unwrap(), split_the_query.nth(0).unwrap(), column);
1174
1175                    for item in ordering {
1176                        self.query = format!("{}, '{}'", self.query, item)
1177                    }
1178
1179                    self.query = format!("{})", self.query);
1180                },
1181                KeywordList::Field => {
1182                    self.query = format!("{}, FIELD({}", self.query, column);
1183
1184                    for item in ordering {
1185                        self.query = format!("{}, '{}'", self.query, item)
1186                    }
1187
1188                    self.query = format!("{})", self.query);
1189                },
1190                _ => {
1191                    let mut new_part_of_query = format!("ORDER BY FIELD({}", column);
1192
1193                    for item in ordering {
1194                        new_part_of_query = format!("{}, '{}'", new_part_of_query, item)
1195                    }
1196
1197                    self.query = format!("{} {})", self.query, new_part_of_query);
1198                }
1199            },
1200            None => panic!("It's almost impossible you to come here.")
1201        }
1202
1203        self.list.push(KeywordList::Field);
1204
1205        self
1206    }
1207
1208    /// It adds the "GROUP BY" keyword with it's Synthax.
1209    pub fn group_by(&mut self, column: &str) -> &mut Self {
1210        self.query = format!("{} GROUP BY {}", self.query, column);
1211
1212        self.list.push(KeywordList::GroupBy);
1213
1214        self
1215    }
1216
1217    pub fn having(&mut self, column: &str, mut mark: &str, value: ValueType) -> &mut Self {
1218        match self.sanitize_column(column) {
1219            Ok(_) => (),
1220            Err(error) => panic!("{}", error)
1221        }
1222
1223        match Self::sanitize_mark(mark) {
1224            Ok(_) => (),
1225            Err(error) => panic!("{}", error)
1226        }
1227
1228        match self.sanitize_input(&value) {
1229            Ok(_) => (),
1230            Err(error) => panic!("{}", error)
1231        }
1232
1233        if let ValueType::Null = value {
1234            match mark {
1235                "=" => mark = "IS",
1236                "!=" | "<>" => mark = "IS NOT",
1237                "IS" | "IS NOT" => (),
1238                _ => mark = "IS"
1239            }
1240        }
1241
1242        self.query = format!("{} HAVING {} {} {}", self.query, column, mark, value);
1243
1244        self.list.push(KeywordList::Having);
1245
1246        self
1247    }
1248
1249    /// it adds the `INNER JOIN` keyword with it's synthax.
1250    /// ```rust
1251    /// 
1252    /// use qubl::{QueryBuilder, ValueType};
1253    /// 
1254    /// fn main(){ 
1255    ///    let query = QueryBuilder::select(vec!["*"]).unwrap()
1256    ///                             .table("students s")
1257    ///                             .inner_join("grades g", "s.id", "=", "g.student_id")
1258    ///                             .where_("id", "=", ValueType::Int32(10))
1259    ///                             .finish();
1260    ///
1261    ///    assert_eq!(query, "SELECT * FROM students s INNER JOIN grades g ON s.id = g.student_id WHERE id = 10;");
1262    /// }
1263    /// 
1264    /// ```
1265    pub fn inner_join(&mut self, table: &str, left: &str, mark: &str, right: &str) -> &mut Self {
1266        self.query = format!("{} INNER JOIN {} ON {} {} {}", self.query, table, left, mark, right);
1267        self.list.push(KeywordList::InnerJoin);
1268        self
1269    }
1270
1271     /// it adds the `LEFT JOIN` keyword with it's synthax.
1272    /// ```rust
1273    /// 
1274    /// use qubl::{QueryBuilder, ValueType};
1275    /// 
1276    /// fn main(){ 
1277    ///    let query = QueryBuilder::select(vec!["*"]).unwrap()
1278    ///                             .table("students s")
1279    ///                             .left_join("grades g", "s.id", "=", "g.student_id")
1280    ///                             .where_("id", "=", ValueType::Int32(10))
1281    ///                             .finish();
1282    ///
1283    ///    assert_eq!(query, "SELECT * FROM students s LEFT JOIN grades g ON s.id = g.student_id WHERE id = 10;");
1284    /// }
1285    /// 
1286    /// ```
1287    pub fn left_join(&mut self, table: &str, left: &str, mark: &str, right: &str) -> &mut Self {
1288        self.query = format!("{} LEFT JOIN {} ON {} {} {}", self.query, table, left, mark, right);
1289        self.list.push(KeywordList::LeftJoin);
1290        self
1291    }
1292
1293    /// it adds the `RIGHT JOIN` keyword with it's synthax.
1294    /// ```rust
1295    /// 
1296    /// use qubl::{QueryBuilder, ValueType};
1297    /// 
1298    /// fn main(){ 
1299    ///    let query = QueryBuilder::select(vec!["*"]).unwrap()
1300    ///                             .table("students s")
1301    ///                             .right_join("grades g", "s.id", "=", "g.student_id")
1302    ///                             .where_("id", "=", ValueType::Int32(10))
1303    ///                             .finish();
1304    ///
1305    ///    assert_eq!(query, "SELECT * FROM students s RIGHT JOIN grades g ON s.id = g.student_id WHERE id = 10;");
1306    /// }
1307    /// 
1308    /// ```
1309    pub fn right_join(&mut self, table: &str, left: &str, mark: &str, right: &str) -> &mut Self {
1310        self.query = format!("{} RIGHT JOIN {} ON {} {} {}", self.query, table, left, mark, right);
1311        self.list.push(KeywordList::RightJoin);
1312        self
1313    }
1314
1315    /// it adds the `CROSS JOIN` keyword with it's synthax.
1316    /// ```rust
1317    /// 
1318    /// use qubl::{QueryBuilder, ValueType};
1319    /// 
1320    /// fn main(){ 
1321    ///    let query = QueryBuilder::select(vec!["*"]).unwrap()
1322    ///                             .table("students s")
1323    ///                             .cross_join("grades g")
1324    ///                             .where_("id", "=", ValueType::Int32(10))
1325    ///                             .finish();
1326    ///
1327    ///    assert_eq!(query, "SELECT * FROM students s RIGHT JOIN grades g ON s.id = g.student_id WHERE id = 10;");
1328    /// }
1329    /// 
1330    /// ```
1331    pub fn cross_join(&mut self, table: &str) -> &mut Self {
1332        self.query = format!("{} CROSS JOIN {}", self.query, table);
1333        self.list.push(KeywordList::RightJoin);
1334        self
1335    }
1336
1337    /// it adds the `NATURAL JOIN` keyword with it's synthax.
1338    /// ```rust
1339    /// 
1340    /// use qubl::{QueryBuilder, ValueType};
1341    /// 
1342    /// fn main(){ 
1343    ///    let query = QueryBuilder::select(vec!["*"]).unwrap()
1344    ///                             .table("students s")
1345    ///                             .natural_join("grades g")
1346    ///                             .where_("id", "=", ValueType::Int32(10))
1347    ///                             .finish();
1348    ///
1349    ///    assert_eq!(query, "SELECT * FROM students s RIGHT JOIN grades g ON s.id = g.student_id WHERE id = 10;");
1350    /// }
1351    /// 
1352    /// ```
1353    pub fn natural_join(&mut self, table: &str) -> &mut Self {
1354        self.query = format!("{} NATURAL JOIN {}", self.query, table);
1355        self.list.push(KeywordList::RightJoin);
1356        self
1357    }
1358
1359
1360    /// it adds the `UNION` keyword and its synthax. You can pass multiple queries to union with:
1361    /// 
1362    /// ```rust
1363    /// 
1364    /// use qubl::{QueryBuilder, ValueType};
1365    /// 
1366    /// fn main(){
1367    ///     let mut union_1 = QueryBuilder::select(vec!["name", "age", "id"]).unwrap();
1368    ///     union_1.table("users").where_("age", ">", ValueType::Int32(7));
1369    ///
1370    ///     let union_2 = QueryBuilder::select(vec!["name", "age", "id"]).unwrap()
1371    ///                                .table("users")
1372    ///                                .where_("age", "<", ValueType::Int32(15))
1373    ///                                .union(vec![union_1])
1374    ///                                .finish();
1375    ///
1376    ///     assert_eq!(union_2, "(SELECT name, age, id FROM users WHERE age < 15) UNION (SELECT name, age, id FROM users WHERE age > 7);");
1377    /// }
1378    /// 
1379    /// ```
1380    pub fn union(&mut self, others: Vec<QueryBuilder<'_>>) -> &mut Self {
1381        match self.list.last() {
1382            Some(keyword) => {
1383                match keyword {
1384                    KeywordList::Union | KeywordList::UnionAll => {
1385                        for other in others {
1386                            self.query = format!("{} UNION ({})", self.query, other.query)
1387                        }
1388                    },
1389                    _ => {
1390                        self.query = format!("({})", self.query);
1391                        
1392                        for other in others {
1393                            self.query = format!("{} UNION ({})", self.query, other.query)
1394                        }
1395                    }
1396                }
1397            },
1398            None => panic!("it's impossible to came here!")
1399        }
1400
1401        self.list.push(KeywordList::Union);
1402
1403        self
1404    }
1405
1406
1407    /// it adds the `UNION` keyword and its synthax. You can pass multiple queries to union with:
1408    /// 
1409    /// ```rust
1410    /// 
1411    /// use qubl::{QueryBuilder, ValueType};
1412    /// 
1413    /// fn main(){
1414    ///     let mut union_1 = QueryBuilder::select(vec!["id", "title", "description", "published"]).unwrap();
1415    ///     union_1.table("blogs").like(vec!["title"], "text");
1416    ///
1417    ///     let mut union_2 = QueryBuilder::select(vec!["id", "title", "description", "published"]).unwrap();
1418    ///     union_2.table("blogs").like(vec!["description"], "some text");
1419    ///
1420    ///     let union_3 = QueryBuilder::select(vec!["id", "title", "description", "published"]).unwrap()
1421    ///                                .table("blogs")
1422    ///                                .where_("published", "=", ValueType::Boolean(true))
1423    ///                                .union_all(vec![union_1, union_2])
1424    ///                                .finish();
1425    ///
1426    ///     assert_eq!(union_3, "(SELECT id, title, description, published FROM blogs WHERE published = true) UNION ALL (SELECT id, title, description, published FROM blogs WHERE title LIKE '%text%') UNION ALL (SELECT id, title, description, published FROM blogs WHERE description LIKE '%some text%');");
1427    /// }
1428    /// 
1429    /// ```
1430    /// 
1431    pub fn union_all(&mut self, others: Vec<QueryBuilder<'_>>) -> &mut Self {
1432        match self.list.last() {
1433            Some(keyword) => {
1434                match keyword {
1435                    KeywordList::Union | KeywordList::UnionAll => {
1436                        for other in others {
1437                            self.query = format!("{} UNION ALL ({})", self.query, other.query)
1438                        }
1439                    },
1440                    _ => {
1441                        self.query = format!("({})", self.query);
1442                        
1443                        for other in others {
1444                            self.query = format!("{} UNION ALL ({})", self.query, other.query)
1445                        }
1446                    }
1447                }
1448            },
1449            None => panic!("it's impossible to came here!")
1450        }
1451
1452        self.list.push(KeywordList::UnionAll);
1453
1454        self
1455    }
1456
1457    /// A wildcard method that gives you the chance to write a part of your query. Warning, it does not add any keyword to builder, i'll encourage to add proper keyword to it with `.append_keyword()` method for your custom query, otherwise you should continue building your query by yourself with that function, or you've to be prepared to encounter bugs.  
1458    /// 
1459    /// ```rust
1460    /// 
1461    /// use qubl::{QueryBuilder, ValueType};
1462    /// 
1463    /// fn main(){
1464    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
1465    ///                              .table("users")
1466    ///                              .append_custom("WHERE age > 25 ORDER BY FIELD(role, 'admin', 'member', 'observer') LIMIT 5 OFFSET 0")
1467    ///                              .finish();
1468    /// 
1469    ///     assert_eq!(query, "SELECT * FROM users WHERE age > 25 ORDER BY FIELD(role, 'admin', 'member', 'observer') LIMIT 5 OFFSET 0;")
1470    /// }
1471    /// ```
1472    /// 
1473    pub fn append_custom(&mut self, query: &str) -> &mut Self {
1474        self.query = format!("{} {}", self.query, query);
1475
1476        self
1477    }
1478
1479    /// A wildcard method that benefits you to append a keyword to the keyword list, so the QueryBuilder can build your queries properly, later than you appended your custom string to your query. It should be used with `.append_custom()` method. 
1480    /// 
1481    /// ```rust
1482    /// 
1483    /// use qubl::{QueryBuilder, ValueType, KeywordList};
1484    /// 
1485    /// fn main(){
1486    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
1487    ///                              .table("users")
1488    ///                              .append_custom("WHERE age > 25 ORDER BY FIELD(role, 'admin', 'member', 'observer') LIMIT 5 OFFSET 0")
1489    ///                              .append_keyword(KeywordList::Where)
1490    ///                              .append_keyword(KeywordList::Field)
1491    ///                              .append_keyword(KeywordList::Limit)
1492    ///                              .append_keyword(KeywordList::Offset)
1493    ///                              .finish();
1494    /// 
1495    ///     assert_eq!(query, "SELECT * FROM users WHERE age > 25 ORDER BY FIELD(role, 'admin', 'member', 'observer') LIMIT 5 OFFSET 0;")
1496    /// }
1497    /// ```
1498    /// 
1499    pub fn append_keyword(&mut self, keyword: KeywordList) -> &mut Self {
1500        self.list.push(keyword);
1501
1502        self
1503    }
1504    
1505    /// It applies "JSON_EXTRACT()" mysql function with it's Synthax. If you encounter any syntactic bugs or deficiencies about that function, please report it via opening an issue.
1506    /// 
1507    /// ```rust
1508    /// 
1509    /// use qubl::{QueryBuilder, ValueType};
1510    /// 
1511    /// fn main(){
1512    ///     let query = QueryBuilder::select(["*"].to_vec()).unwrap()
1513    ///                              .json_extract("articles", "[0]", Some("blog1"))
1514    ///                              .json_extract("articles", "[1]", Some("blog2"))
1515    ///                              .json_extract("articles", "[2]", Some("blog3"))
1516    ///                              .table("users")
1517    ///                              .where_("published", "=", ValueType::Int32(1))
1518    ///                              .finish();
1519    /// 
1520    ///     assert_eq!(query, "SELECT JSON_EXTRACT(articles, '$[0]') AS blog1, JSON_EXTRACT(articles, '$[1]') AS blog2, JSON_EXTRACT(articles, '$[2]') AS blog3 FROM users WHERE published = 1;")
1521    /// }
1522    /// 
1523    /// ```
1524    pub fn json_extract(&mut self, haystack: &str, needle: &str, _as: Option<&str>) -> &mut Self {
1525        match self.list.last() {
1526            Some(keyword) => {
1527                match keyword {
1528                    KeywordList::LeftBracketWhere | KeywordList::LeftBracketAnd | KeywordList::LeftBracketOr => {
1529                        panic!("you cannot use .json_extract() method with bracket methods for now, this will be implemented on future updates.")
1530                    },
1531                    KeywordList::Where => {
1532                        if _as.is_some() {
1533                            println!("Warning: You've gave _as value to some variant and used it later than 'WHERE' keyword on .json_extract() method. In that usage, that value has no effect, you should gave it none value.");
1534                        }
1535
1536                        match self.table.as_str() == haystack {
1537                            true => {
1538                                let mut split_the_query = self.query.split(haystack);
1539                                let string_for_replace = format!("JSON_EXTRACT({}, '${}')", haystack, needle);
1540
1541                                self.query = format!("SELECT{}{}{}", self.table, string_for_replace, split_the_query.nth(2).unwrap()) 
1542                            },
1543                            false => {
1544                                let string_for_replace = format!("JSON_EXTRACT({}, '${}')", haystack, needle);
1545
1546                                self.query = self.query.replace(haystack,&string_for_replace)
1547                            }
1548                        }
1549                    },
1550                    KeywordList::And => {
1551                        if _as.is_some() {
1552                            println!("Warning: You've gave _as value to some variant and used it later than 'AND' keyword on .json_extract() method. In that usage, that value has no effect, you should gave it none value.");
1553                        }
1554
1555                        let query_to_comp = format!("AND {}", haystack);
1556
1557                        match self.table.as_str() == haystack {
1558                            true => {
1559                                let mut split_the_query = self.query.split(&query_to_comp);
1560                                let string_for_replace = format!("JSON_EXTRACT({}, '${}')", haystack, needle);
1561
1562                                self.query = format!("{}AND {}{}", split_the_query.nth(0).unwrap(), string_for_replace, split_the_query.nth(0).unwrap()) 
1563                            },
1564                            false => {
1565                                match self.query.matches(&query_to_comp).count() {
1566                                    0 => (),
1567                                    1 => {
1568                                        let string_for_replace = format!("AND JSON_EXTRACT({}, '${}')", haystack, needle);
1569
1570                                        self.query = self.query.replace(&query_to_comp,&string_for_replace)
1571                                    }
1572                                    _ => {
1573                                        let split_the_query = self.query.split(&query_to_comp).collect::<Vec<&str>>();
1574
1575                                        let mut last_chunk = "".to_string();
1576                                        let mut new_chunk = "".to_string();
1577                                        let length_of_split = split_the_query.len();
1578                                        
1579                                        for (index, chunk) in split_the_query.into_iter().enumerate() {
1580                                            if index + 1 == length_of_split {
1581                                                last_chunk = chunk.to_string()
1582                                            } else if index == 0 {
1583                                                new_chunk = format!("{}", chunk);
1584                                            } else {
1585                                                new_chunk = format!("{}{}{}", new_chunk, query_to_comp, chunk)
1586                                            }
1587                                        }
1588
1589                                        let string_for_replace = format!("AND JSON_EXTRACT({}, '${}')", haystack, needle);
1590
1591                                        self.query = format!("{} {} {}", new_chunk, string_for_replace, last_chunk)
1592                                    }
1593                                }
1594                            }
1595                        }
1596                    },
1597                    KeywordList::Or => {
1598                        if _as.is_some() {
1599                            println!("Warning: You've gave _as value to some variant and used it later than 'OR' keyword on .json_extract() method. In that usage, that value has no effect, you should gave it none value.");
1600                        }
1601
1602                        let query_to_comp = format!("OR {}", haystack);
1603
1604                        match self.table.as_str() == haystack {
1605                            true => {
1606                                let mut split_the_query = self.query.split(&query_to_comp);
1607                                let string_for_replace = format!("JSON_EXTRACT({}, '${}')", haystack, needle);
1608
1609                                self.query = format!("{}OR {}{}", split_the_query.nth(0).unwrap(), string_for_replace, split_the_query.nth(0).unwrap()) 
1610                            },
1611                            false => {
1612                                match self.query.matches(&query_to_comp).count() {
1613                                    0 => (),
1614                                    1 => {
1615                                        let string_for_replace = format!("OR JSON_EXTRACT({}, '${}')", haystack, needle);
1616
1617                                        self.query = self.query.replace(&query_to_comp,&string_for_replace)
1618                                    }
1619                                    _ => {
1620                                        let split_the_query = self.query.split(&query_to_comp).collect::<Vec<&str>>();
1621
1622                                        let mut last_chunk = "".to_string();
1623                                        let mut new_chunk = "".to_string();
1624                                        let length_of_split = split_the_query.len();
1625                                        
1626                                        for (index, chunk) in split_the_query.into_iter().enumerate() {
1627                                            if index + 1 == length_of_split {
1628                                                last_chunk = chunk.to_string()
1629                                            } else if index == 0 {
1630                                                new_chunk = format!("{}", chunk);
1631                                            } else {
1632                                                new_chunk = format!("{}{}{}", new_chunk, query_to_comp, chunk)
1633                                            }
1634                                        }
1635
1636                                        let string_for_replace = format!("OR JSON_EXTRACT({}, '${}')", haystack, needle);
1637
1638                                        self.query = format!("{} {} {}", new_chunk, string_for_replace, last_chunk)
1639                                    }
1640                                }
1641                            }
1642                        }
1643                    },
1644                    KeywordList::Select => {
1645                        let string_for_put = format!("JSON_EXTRACT({}, '${}')", haystack, needle);
1646
1647                        match _as {
1648                            Some(_as) => self.query = format!("SELECT {} AS {} FROM", string_for_put, _as),
1649                            None => self.query = format!("SELECT {} FROM", string_for_put),
1650                        }
1651                    },
1652                    KeywordList::Table => {
1653                        let string_for_put = format!("JSON_EXTRACT({}, '${}')", haystack, needle);
1654
1655                        match _as {
1656                            Some(_as) => self.query = format!("SELECT {} AS {} FROM {}", string_for_put, _as, self.table),
1657                            None => self.query = format!("SELECT {} FROM {}", string_for_put, self.table),
1658                        }
1659                    },
1660                    KeywordList::OrderBy => {
1661                        if _as.is_some() {
1662                            println!("Warning: You've gave _as value to some variant and used it later than 'ORDER BY' operator on .json_extract() method. In that usage, that value has no effect, you should gave it none value.");
1663                        }
1664
1665                        match self.query.matches(" ORDER BY ").count() {
1666                            0 => (),
1667                            1 => {
1668                                let split_the_query = self.query.clone();
1669                                let mut split_the_query = split_the_query.split(" ORDER BY ");
1670
1671                                let string_for_put = format!("ORDER BY JSON_EXTRACT({}, '${}')", haystack, needle);
1672        
1673                                match _as {
1674                                    Some(_as) => self.query = format!("{} {} AS {}", split_the_query.nth(0).unwrap(), string_for_put, _as),
1675                                    None => self.query = format!("{} {}", split_the_query.nth(0).unwrap(), string_for_put)
1676                                }
1677
1678                                match split_the_query.nth(0) {
1679                                    Some(comparison) => {
1680                                        match comparison.ends_with("ASC") || comparison.ends_with("asc") {
1681                                            true => self.query = format!("{} ASC", self.query),
1682                                            false => match comparison.ends_with("DESC") || comparison.ends_with("desc") {
1683                                                true => self.query = format!("{} DESC", self.query),
1684                                                false => ()
1685                                            }
1686                                        }
1687                                    },
1688                                    None => ()
1689                                }
1690                            },
1691                            _ => ()
1692                        }
1693                    },
1694                    KeywordList::Count => {
1695                        let mut split_the_query = self.query.split(" COUNT");
1696
1697                        let string_for_put = match _as {
1698                            Some(_as) => format!("JSON_EXTRACT({}, '${}') AS {}", haystack, needle, _as),
1699                            None => format!("JSON_EXTRACT({}, '${}')", haystack, needle)
1700                        };
1701
1702                        self.query = format!("SELECT {}, COUNT{}", string_for_put, split_the_query.nth(1).unwrap())
1703                    },
1704                    KeywordList::JsonExtract => {
1705                        let mut split_the_query = self.query.split(" FROM");
1706
1707                        match _as {
1708                            Some(_as) => self.query = format!("{}, JSON_EXTRACT({}, '${}') AS {} FROM", split_the_query.nth(0).unwrap(), haystack, needle, _as),
1709                            None => panic!("If you want to chain .json_extract() methods, you have to give them a tag.")
1710                        }
1711                    }
1712                    _ => ()
1713                }
1714            },
1715            None => ()
1716        }
1717        
1718        self.list.push(KeywordList::JsonExtract);
1719        self
1720    }
1721
1722    /// It applies "JSON_CONTAINS()" mysql function with it's Synthax. If you encounter any syntactic bugs or deficiencies about that function, please report it via opening an issue.
1723    /// 
1724    /// ```rust
1725    /// 
1726    /// use qubl::{QueryBuilder, ValueType, JsonValue};
1727    /// 
1728    /// fn main(){
1729    ///     let value = ValueType::String("blablabla.jpg".to_string());
1730    ///     let prop = vec![("name", &value)];
1731    /// 
1732    ///     let object = JsonValue::MysqlJsonObject(&prop);
1733    /// 
1734    ///     let query = QueryBuilder::select(["*"].to_vec()).unwrap()
1735    ///                              .table("users")
1736    ///                              .where_("pic", "=", ValueType::String("".to_string()))
1737    ///                              .json_contains("pic", object, Some(".name"))
1738    ///                              .finish();
1739    /// 
1740    ///     assert_eq!(query, "SELECT * FROM users WHERE JSON_CONTAINS(pic, JSON_OBJECT('name', 'blablabla.jpg'), '$.name');")
1741    /// }
1742    /// 
1743    /// ```
1744    pub fn json_contains(&mut self, column: &str, needle: JsonValue, path: Option<&str>) -> &mut Self {
1745        match self.list.last().unwrap() {
1746            KeywordList::Select => match path {
1747                Some(path) => match needle {
1748                    JsonValue::Initial(initial) => match initial {
1749                        ValueType::JsonString(needle) => self.query = format!("SELECT JSON_CONTAINS({}, '\"{}\"', '${}') FROM", column, needle, path),
1750                        ValueType::String(needle) => self.query = format!("SELECT JSON_CONTAINS({}, '{}', '${}') FROM", column, needle, path),
1751                        ValueType::Datetime(needle) => self.query = format!("SELECT JSON_CONTAINS({}, '{}', '${}') FROM", column, needle, path),
1752                        _ => self.query = format!("SELECT JSON_CONTAINS({}, {}, '${}') FROM", column, needle, path),
1753                    },
1754                    _ => self.query = format!("SELECT JSON_CONTAINS({}, {}, '${}') FROM", column, needle, path),
1755                }
1756                None => match needle {
1757                    JsonValue::Initial(initial) => match initial {
1758                        ValueType::JsonString(needle) => self.query = format!("SELECT JSON_CONTAINS({}, '\"{}\"') FROM", column, needle),
1759                        ValueType::String(needle) => self.query = format!("SELECT JSON_CONTAINS({}, '{}') FROM", column, needle),
1760                        ValueType::Datetime(needle) => self.query = format!("SELECT JSON_CONTAINS({}, '{}') FROM", column, needle),
1761                        _ => self.query = format!("SELECT JSON_CONTAINS({}, {}) FROM", column, needle)
1762                    },
1763                    _ => self.query = format!("SELECT JSON_CONTAINS({}, {}) FROM", column, needle)
1764                }
1765            },
1766            KeywordList::Where => match path {
1767                Some(path) => {
1768                    let mut split_the_query = self.query.split(" WHERE ");
1769
1770                    let first_half = split_the_query.nth(0);
1771
1772                    match needle {
1773                        JsonValue::Initial(initial) => match initial {
1774                            ValueType::JsonString(needle) => self.query = format!("{} WHERE JSON_CONTAINS({}, '\"{}\"', '${}')", first_half.unwrap(), column, needle, path),
1775                            ValueType::String(needle) => self.query = format!("{} WHERE JSON_CONTAINS({}, '{}', '${}')", first_half.unwrap(), column, needle, path),
1776                            ValueType::Datetime(needle) => self.query = format!("{} WHERE JSON_CONTAINS({}, '{}', '${}')", first_half.unwrap(), column, needle, path),
1777                            _ => self.query = format!("{} WHERE JSON_CONTAINS({}, {}, '${}')", first_half.unwrap(), column, needle, path)
1778                        },
1779                        _ => self.query = format!("{} WHERE JSON_CONTAINS({}, {}, '${}')", first_half.unwrap(), column, needle, path)
1780                    }
1781                },
1782                None => {
1783                    let mut split_the_query = self.query.split(" WHERE ");
1784
1785                    let first_half = split_the_query.nth(0);
1786
1787                    match needle {
1788                        JsonValue::Initial(initial) => match initial {
1789                            ValueType::JsonString(needle) => self.query = format!("{} WHERE JSON_CONTAINS({}, '\"{}\"')", first_half.unwrap(), column, needle),
1790                            ValueType::String(needle) => self.query = format!("{} WHERE JSON_CONTAINS({}, '{}')", first_half.unwrap(), column, needle),
1791                            ValueType::Datetime(needle) => self.query = format!("{} WHERE JSON_CONTAINS({}, '{}')", first_half.unwrap(), column, needle),
1792                            _ => self.query = format!("{} WHERE JSON_CONTAINS({}, {})", first_half.unwrap(), column, needle)
1793                        },
1794                        _ => self.query = format!("{} WHERE JSON_CONTAINS({}, {})", first_half.unwrap(), column, needle)
1795                    }
1796                }
1797            },
1798            KeywordList::LeftBracketWhere => match path {
1799                Some(path) => {
1800                    let mut split_the_query = self.query.split(" WHERE ");
1801
1802                    let first_half = split_the_query.nth(0);
1803
1804                    match needle {
1805                        JsonValue::Initial(initial) => match initial {
1806                            ValueType::JsonString(needle) => self.query = format!("{} WHERE (JSON_CONTAINS({}, '\"{}\"', '${}')", first_half.unwrap(), column, needle, path),
1807                            ValueType::String(needle) => self.query = format!("{} WHERE (JSON_CONTAINS({}, '{}', '${}')", first_half.unwrap(), column, needle, path),
1808                            ValueType::Datetime(needle) => self.query = format!("{} WHERE (JSON_CONTAINS({}, '{}', '${}')", first_half.unwrap(), column, needle, path),
1809                            _ => self.query = format!("{} WHERE (JSON_CONTAINS({}, {}, '${}')", first_half.unwrap(), column, needle, path)
1810                        },
1811                        _ => self.query = format!("{} WHERE (JSON_CONTAINS({}, {}, '${}')", first_half.unwrap(), column, needle, path)
1812                    }
1813                },
1814                None => {
1815                    let mut split_the_query = self.query.split(" WHERE ");
1816
1817                    let first_half = split_the_query.nth(0);
1818
1819                    match needle {
1820                        JsonValue::Initial(initial) => match initial {
1821                            ValueType::JsonString(needle) => self.query = format!("{} WHERE (JSON_CONTAINS({}, '\"{}\"')", first_half.unwrap(), column, needle),
1822                            ValueType::String(needle) => self.query = format!("{} WHERE (JSON_CONTAINS({}, '{}')", first_half.unwrap(), column, needle),
1823                            ValueType::Datetime(needle) => self.query = format!("{} WHERE (JSON_CONTAINS({}, '{}')", first_half.unwrap(), column, needle),
1824                            _ => self.query = format!("{} WHERE (JSON_CONTAINS({}, {})", first_half.unwrap(), column, needle)
1825                        },
1826                        _ => self.query = format!("{} WHERE (JSON_CONTAINS({}, {})", first_half.unwrap(), column, needle)
1827                    }
1828                }
1829            },
1830            KeywordList::And => match path {
1831                Some(path) => {
1832                    let split_the_query = self.query.split(" AND ").collect::<Vec<&str>>();
1833
1834                    let length_of_the_split_the_query = split_the_query.len();
1835
1836                    match split_the_query.len() {
1837                        0 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1838                        1 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1839                        2 => match needle {
1840                            JsonValue::Initial(initial) => match initial {
1841                                ValueType::JsonString(needle) => self.query = format!("{} AND JSON_CONTAINS({}, '\"{}\"', '${}')", split_the_query[0], column, needle, path),
1842                                ValueType::String(needle) => self.query = format!("{} AND JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
1843                                ValueType::Datetime(needle) => self.query = format!("{} AND JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
1844                                _ => self.query = format!("{} AND JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
1845                            },
1846                            _ => self.query = format!("{} AND JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
1847                        },
1848                        _ => {
1849                            let mut concatenated_string = String::new();
1850
1851                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1852                                if index == 0 {
1853                                    concatenated_string = chunk.to_string();
1854                                } else if index + 1 != length_of_the_split_the_query {
1855                                    concatenated_string = format!("{} AND {} ", concatenated_string, chunk)
1856                                }
1857                            }
1858
1859                            match needle {
1860                                JsonValue::Initial(initial) => match initial {
1861                                    ValueType::JsonString(needle) => self.query = format!("{}AND JSON_CONTAINS({}, '\"{}\"', '${}')", concatenated_string, column, needle, path),
1862                                    ValueType::String(needle) => self.query = format!("{}AND JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
1863                                    ValueType::Datetime(needle) => self.query = format!("{}AND JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
1864                                    _ => self.query = format!("{}AND JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
1865                                },
1866                                _ => self.query = format!("{}AND JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
1867                            }
1868                        }
1869                    }
1870                },
1871                None => {
1872                    let split_the_query = self.query.split(" AND ").collect::<Vec<&str>>();
1873
1874                    let length_of_the_split_the_query = split_the_query.len();
1875
1876                    match split_the_query.len() {
1877                        0 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1878                        1 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1879                        2 => match needle {
1880                            JsonValue::Initial(initial) => match initial {
1881                                ValueType::JsonString(needle) => self.query = format!("{} AND JSON_CONTAINS({}, '\"{}\"')",  split_the_query[0], column, needle),
1882                                ValueType::String(needle) => self.query = format!("{} AND JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
1883                                ValueType::Datetime(needle) => self.query = format!("{} AND JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
1884                                _ => self.query = format!("{} AND JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
1885                            },
1886                            _ => self.query = format!("{} AND JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
1887                        },
1888                        _ => {
1889                            let mut concatenated_string = String::new();
1890
1891                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1892                                if index == 0 {
1893                                    concatenated_string = chunk.to_string();
1894                                } else if index + 1 != length_of_the_split_the_query {
1895                                    concatenated_string = format!("{} AND {} ", concatenated_string, chunk)
1896                                }
1897                            }
1898
1899                            match needle {
1900                                JsonValue::Initial(initial) => match initial {
1901                                    ValueType::JsonString(needle) => self.query = format!("{}AND JSON_CONTAINS({}, '\"{}\"')", concatenated_string, column, needle),
1902                                    ValueType::String(needle) => self.query = format!("{}AND JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
1903                                    ValueType::Datetime(needle) => self.query = format!("{}AND JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
1904                                    _ => self.query = format!("{}AND JSON_CONTAINS({}, {})", concatenated_string, column, needle)
1905                                },
1906                                _ => self.query = format!("{}AND JSON_CONTAINS({}, {})", concatenated_string, column, needle)
1907                            }
1908                        }
1909                    }
1910                }
1911            },
1912            KeywordList::LeftBracketAnd => match path {
1913                Some(path) => {
1914                    let split_the_query = self.query.split(" AND ").collect::<Vec<&str>>();
1915
1916                    let length_of_the_split_the_query = split_the_query.len();
1917
1918                    match split_the_query.len() {
1919                        0 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1920                        1 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1921                        2 => match needle {
1922                            JsonValue::Initial(initial) => match initial {
1923                                ValueType::JsonString(needle) => self.query = format!("{} AND (JSON_CONTAINS({}, '\"{}\"', '${}')", split_the_query[0], column, needle, path),
1924                                ValueType::String(needle) => self.query = format!("{} AND 8JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
1925                                ValueType::Datetime(needle) => self.query = format!("{} AND (JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
1926                                _ => self.query = format!("{} AND (JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
1927                            },
1928                            _ => self.query = format!("{} AND (JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
1929                        },
1930                        _ => {
1931                            let mut concatenated_string = String::new();
1932
1933                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1934                                if index == 0 {
1935                                    concatenated_string = chunk.to_string();
1936                                } else if index + 1 != length_of_the_split_the_query {
1937                                    concatenated_string = format!("{} AND {} ", concatenated_string, chunk)
1938                                }
1939                            }
1940
1941                            match needle {
1942                                JsonValue::Initial(initial) => match initial {
1943                                    ValueType::JsonString(needle) => self.query = format!("{}AND (JSON_CONTAINS({}, '\"{}\"', '${}')", concatenated_string, column, needle, path),
1944                                    ValueType::String(needle) => self.query = format!("{}AND (JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
1945                                    ValueType::Datetime(needle) => self.query = format!("{}AND (JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
1946                                    _ => self.query = format!("{}AND (JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
1947                                },
1948                                _ => self.query = format!("{}AND (JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
1949                            }
1950                        }
1951                    }
1952                },
1953                None => {
1954                    let split_the_query = self.query.split(" AND ").collect::<Vec<&str>>();
1955
1956                    let length_of_the_split_the_query = split_the_query.len();
1957
1958                    match split_the_query.len() {
1959                        0 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1960                        1 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1961                        2 => match needle {
1962                            JsonValue::Initial(initial) => match initial {
1963                                ValueType::JsonString(needle) => self.query = format!("{} AND (JSON_CONTAINS({}, '\"{}\"')",  split_the_query[0], column, needle),
1964                                ValueType::String(needle) => self.query = format!("{} AND (JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
1965                                ValueType::Datetime(needle) => self.query = format!("{} AND (JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
1966                                _ => self.query = format!("{} AND (JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
1967                            },
1968                            _ => self.query = format!("{} AND (JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
1969                        },
1970                        _ => {
1971                            let mut concatenated_string = String::new();
1972
1973                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1974                                if index == 0 {
1975                                    concatenated_string = chunk.to_string();
1976                                } else if index + 1 != length_of_the_split_the_query {
1977                                    concatenated_string = format!("{} AND {} ", concatenated_string, chunk)
1978                                }
1979                            }
1980
1981                            match needle {
1982                                JsonValue::Initial(initial) => match initial {
1983                                    ValueType::JsonString(needle) => self.query = format!("{}AND (JSON_CONTAINS({}, '\"{}\"')", concatenated_string, column, needle),
1984                                    ValueType::String(needle) => self.query = format!("{}AND (JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
1985                                    ValueType::Datetime(needle) => self.query = format!("{}AND (JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
1986                                    _ => self.query = format!("{}AND (JSON_CONTAINS({}, {})", concatenated_string, column, needle)
1987                                },
1988                                _ => self.query = format!("{}AND (JSON_CONTAINS({}, {})", concatenated_string, column, needle)
1989                            }
1990                        }
1991                    }
1992                }
1993            },
1994            KeywordList::Or => match path {
1995                Some(path) => {
1996                    let split_the_query = self.query.split(" OR ").collect::<Vec<&str>>();
1997
1998                    let length_of_the_split_the_query = split_the_query.len();
1999
2000                    match split_the_query.len() {
2001                        0 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
2002                        1 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
2003                        2 => match needle {
2004                            JsonValue::Initial(initial) => match initial {
2005                                ValueType::JsonString(needle) => self.query = format!("{} OR JSON_CONTAINS({}, '\"{}\"', '${}')", split_the_query[0], column, needle, path),
2006                                ValueType::String(needle) => self.query = format!("{} OR JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
2007                                ValueType::Datetime(needle) => self.query = format!("{} OR JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
2008                                _ => self.query = format!("{} OR JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
2009                            },
2010                            _ => self.query = format!("{} OR JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
2011                        },
2012                        _ => {
2013                            let mut concatenated_string = String::new();
2014
2015                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
2016                                if index == 0 {
2017                                    concatenated_string = chunk.to_string();
2018                                } else if index + 1 != length_of_the_split_the_query {
2019                                    concatenated_string = format!("{} OR {} ", concatenated_string, chunk)
2020                                }
2021                            }
2022
2023                            match needle {
2024                                JsonValue::Initial(initial) => match initial {
2025                                    ValueType::JsonString(needle) => self.query = format!("{}OR JSON_CONTAINS({}, '\"{}\"', '${}')", concatenated_string, column, needle, path),
2026                                    ValueType::String(needle) => self.query = format!("{}OR JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
2027                                    ValueType::Datetime(needle) => self.query = format!("{}OR JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
2028                                    _ => self.query = format!("{}OR JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
2029                                },
2030                                _ => self.query = format!("{}OR JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
2031                            }
2032                        }
2033                    }
2034                },
2035                None => {
2036                    let split_the_query = self.query.split(" OR ").collect::<Vec<&str>>();
2037
2038                    let length_of_the_split_the_query = split_the_query.len();
2039
2040                    match split_the_query.len() {
2041                        0 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
2042                        1 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
2043                        2 => match needle {
2044                            JsonValue::Initial(initial) => match initial {
2045                                ValueType::JsonString(needle) => self.query = format!("{} OR JSON_CONTAINS({}, '\"{}\"')",  split_the_query[0], column, needle),
2046                                ValueType::String(needle) => self.query = format!("{} OR JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
2047                                ValueType::Datetime(needle) => self.query = format!("{} OR JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
2048                                _ => self.query = format!("{} OR JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
2049                            },
2050                            _ => self.query = format!("{} OR JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
2051                        },
2052                        _ => {
2053                            let mut concatenated_string = String::new();
2054
2055                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
2056                                if index == 0 {
2057                                    concatenated_string = chunk.to_string();
2058                                } else if index + 1 != length_of_the_split_the_query {
2059                                    concatenated_string = format!("{} OR {} ", concatenated_string, chunk)
2060                                }
2061                            }
2062
2063                            match needle {
2064                                JsonValue::Initial(initial) => match initial {
2065                                    ValueType::JsonString(needle) => self.query = format!("{}OR JSON_CONTAINS({}, '\"{}\"')", concatenated_string, column, needle),
2066                                    ValueType::String(needle) => self.query = format!("{}OR JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
2067                                    ValueType::Datetime(needle) => self.query = format!("{}OR JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
2068                                    _ => self.query = format!("{}OR JSON_CONTAINS({}, {})", concatenated_string, column, needle)
2069                                },
2070                                _ => self.query = format!("{}OR JSON_CONTAINS({}, {})", concatenated_string, column, needle)
2071                            }
2072                        }
2073                    }
2074                }
2075            },
2076            KeywordList::LeftBracketOr => match path {
2077                Some(path) => {
2078                    let split_the_query = self.query.split(" OR ").collect::<Vec<&str>>();
2079
2080                    let length_of_the_split_the_query = split_the_query.len();
2081
2082                    match split_the_query.len() {
2083                        0 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
2084                        1 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
2085                        2 => match needle {
2086                            JsonValue::Initial(initial) => match initial {
2087                                ValueType::JsonString(needle) => self.query = format!("{} OR (JSON_CONTAINS({}, '\"{}\"', '${}')", split_the_query[0], column, needle, path),
2088                                ValueType::String(needle) => self.query = format!("{} OR (JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
2089                                ValueType::Datetime(needle) => self.query = format!("{} OR (JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
2090                                _ => self.query = format!("{} OR (JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
2091                            },
2092                            _ => self.query = format!("{} OR (JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
2093                        },
2094                        _ => {
2095                            let mut concatenated_string = String::new();
2096
2097                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
2098                                if index == 0 {
2099                                    concatenated_string = chunk.to_string();
2100                                } else if index + 1 != length_of_the_split_the_query {
2101                                    concatenated_string = format!("{} OR {} ", concatenated_string, chunk)
2102                                }
2103                            }
2104
2105                            match needle {
2106                                JsonValue::Initial(initial) => match initial {
2107                                    ValueType::JsonString(needle) => self.query = format!("{}OR (JSON_CONTAINS({}, '\"{}\"', '${}')", concatenated_string, column, needle, path),
2108                                    ValueType::String(needle) => self.query = format!("{}OR (JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
2109                                    ValueType::Datetime(needle) => self.query = format!("{}OR (JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
2110                                    _ => self.query = format!("{}OR (JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
2111                                },
2112                                _ => self.query = format!("{}OR (JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
2113                            }
2114                        }
2115                    }
2116                },
2117                None => {
2118                    let split_the_query = self.query.split(" OR ").collect::<Vec<&str>>();
2119
2120                    let length_of_the_split_the_query = split_the_query.len();
2121
2122                    match split_the_query.len() {
2123                        0 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
2124                        1 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
2125                        2 => match needle {
2126                            JsonValue::Initial(initial) => match initial {
2127                                ValueType::JsonString(needle) => self.query = format!("{} OR (JSON_CONTAINS({}, '\"{}\"')",  split_the_query[0], column, needle),
2128                                ValueType::String(needle) => self.query = format!("{} OR (JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
2129                                ValueType::Datetime(needle) => self.query = format!("{} OR (JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
2130                                _ => self.query = format!("{} OR (JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
2131                            },
2132                            _ => self.query = format!("{} OR (JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
2133                        },
2134                        _ => {
2135                            let mut concatenated_string = String::new();
2136
2137                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
2138                                if index == 0 {
2139                                    concatenated_string = chunk.to_string();
2140                                } else if index + 1 != length_of_the_split_the_query {
2141                                    concatenated_string = format!("{} OR {} ", concatenated_string, chunk)
2142                                }
2143                            }
2144
2145                            match needle {
2146                                JsonValue::Initial(initial) => match initial {
2147                                    ValueType::JsonString(needle) => self.query = format!("{}OR (JSON_CONTAINS({}, '\"{}\"')", concatenated_string, column, needle),
2148                                    ValueType::String(needle) => self.query = format!("{}OR (JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
2149                                    ValueType::Datetime(needle) => self.query = format!("{}OR (JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
2150                                    _ => self.query = format!("{}OR (JSON_CONTAINS({}, {})", concatenated_string, column, needle)
2151                                },
2152                                _ => self.query = format!("{}OR (JSON_CONTAINS({}, {})", concatenated_string, column, needle)
2153                            }
2154                        }
2155                    }
2156                }
2157            },
2158            _ => panic!("Wrong usage of '.json_contains()' method, it should be used later than either SELECT, WHERE, AND, OR keywords.")
2159        }
2160
2161        self.list.push(KeywordList::JsonContains);
2162
2163        self
2164    }
2165
2166    /// It applies "NOT JSON_CONTAINS()" mysql function with it's Synthax. If you encounter any syntactic bugs or deficiencies about that function, please report it via opening an issue.
2167    /// 
2168    /// ```rust
2169    /// 
2170    /// use qubl::{QueryBuilder, ValueType, JsonValue};
2171    /// 
2172    /// fn main(){
2173    ///     let value = ValueType::String("blablabla.jpg".to_string());
2174    ///     let prop = vec![("name", &value)];
2175    /// 
2176    ///     let object = JsonValue::MysqlJsonObject(&prop);
2177    /// 
2178    ///     let query = QueryBuilder::select(["*"].to_vec()).unwrap()
2179    ///                              .table("users")
2180    ///                              .where_("pic", "=", ValueType::String("".to_string()))
2181    ///                              .not_json_contains("pic", object, Some(".name"))
2182    ///                              .finish();
2183    /// 
2184    ///     assert_eq!(query, "SELECT * FROM users WHERE NOT JSON_CONTAINS(pic, JSON_OBJECT('name', 'blablabla.jpg'), '$.name');")
2185    /// }
2186    /// 
2187    /// ```
2188    pub fn not_json_contains(&mut self, column: &str, needle: JsonValue, path: Option<&str>) -> &mut Self {
2189        match self.list.last().unwrap() {
2190            KeywordList::Select => match path {
2191                Some(path) => match needle {
2192                    JsonValue::Initial(initial) => match initial {
2193                        ValueType::JsonString(needle) => self.query = format!("SELECT NOT JSON_CONTAINS({}, '\"{}\"', '${}') FROM", column, needle, path),
2194                        ValueType::String(needle) => self.query = format!("SELECT NOT JSON_CONTAINS({}, '{}', '${}') FROM", column, needle, path),
2195                        ValueType::Datetime(needle) => self.query = format!("SELECT NOT JSON_CONTAINS({}, '{}', '${}') FROM", column, needle, path),
2196                        _ => self.query = format!("SELECT NOT JSON_CONTAINS({}, {}, '${}') FROM", column, needle, path),
2197                    },
2198                    _ => self.query = format!("SELECT NOT JSON_CONTAINS({}, {}, '${}') FROM", column, needle, path),
2199                }
2200                None => match needle {
2201                    JsonValue::Initial(initial) => match initial {
2202                        ValueType::JsonString(needle) => self.query = format!("SELECT NOT JSON_CONTAINS({}, '\"{}\"') FROM", column, needle),
2203                        ValueType::String(needle) => self.query = format!("SELECT NOT JSON_CONTAINS({}, '{}') FROM", column, needle),
2204                        ValueType::Datetime(needle) => self.query = format!("SELECT NOT JSON_CONTAINS({}, '{}') FROM", column, needle),
2205                        _ => self.query = format!("SELECT NOT JSON_CONTAINS({}, {}) FROM", column, needle)
2206                    },
2207                    _ => self.query = format!("SELECT NOT JSON_CONTAINS({}, {}) FROM", column, needle)
2208                }
2209            },
2210            KeywordList::Where => match path {
2211                Some(path) => {
2212                    let mut split_the_query = self.query.split(" WHERE ");
2213
2214                    let first_half = split_the_query.nth(0);
2215
2216                    match needle {
2217                        JsonValue::Initial(initial) => match initial {
2218                            ValueType::JsonString(needle) => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, '\"{}\"', '${}')", first_half.unwrap(), column, needle, path),
2219                            ValueType::String(needle) => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, '{}', '${}')", first_half.unwrap(), column, needle, path),
2220                            ValueType::Datetime(needle) => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, '{}', '${}')", first_half.unwrap(), column, needle, path),
2221                            _ => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, {}, '${}')", first_half.unwrap(), column, needle, path)
2222                        },
2223                        _ => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, {}, '${}')", first_half.unwrap(), column, needle, path)
2224                    }
2225                },
2226                None => {
2227                    let mut split_the_query = self.query.split(" WHERE ");
2228
2229                    let first_half = split_the_query.nth(0);
2230
2231                    match needle {
2232                        JsonValue::Initial(initial) => match initial {
2233                            ValueType::JsonString(needle) => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, '\"{}\"')", first_half.unwrap(), column, needle),
2234                            ValueType::String(needle) => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, '{}')", first_half.unwrap(), column, needle),
2235                            ValueType::Datetime(needle) => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, '{}')", first_half.unwrap(), column, needle),
2236                            _ => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, {})", first_half.unwrap(), column, needle)
2237                        },
2238                        _ => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, {})", first_half.unwrap(), column, needle)
2239                    }
2240                }
2241            },
2242            KeywordList::LeftBracketWhere => match path {
2243                Some(path) => {
2244                    let mut split_the_query = self.query.split(" WHERE ");
2245
2246                    let first_half = split_the_query.nth(0);
2247
2248                    match needle {
2249                        JsonValue::Initial(initial) => match initial {
2250                            ValueType::JsonString(needle) => self.query = format!("{} WHERE (NOT JSON_CONTAINS({}, '\"{}\"', '${}')", first_half.unwrap(), column, needle, path),
2251                            ValueType::String(needle) => self.query = format!("{} WHERE (NOT JSON_CONTAINS({}, '{}', '${}')", first_half.unwrap(), column, needle, path),
2252                            ValueType::Datetime(needle) => self.query = format!("{} WHERE (NOT JSON_CONTAINS({}, '{}', '${}')", first_half.unwrap(), column, needle, path),
2253                            _ => self.query = format!("{} WHERE (NOT JSON_CONTAINS({}, {}, '${}')", first_half.unwrap(), column, needle, path)
2254                        },
2255                        _ => self.query = format!("{} WHERE (NOT JSON_CONTAINS({}, {}, '${}')", first_half.unwrap(), column, needle, path)
2256                    }
2257                },
2258                None => {
2259                    let mut split_the_query = self.query.split(" WHERE ");
2260
2261                    let first_half = split_the_query.nth(0);
2262
2263                    match needle {
2264                        JsonValue::Initial(initial) => match initial {
2265                            ValueType::JsonString(needle) => self.query = format!("{} WHERE (NOT JSON_CONTAINS({}, '\"{}\"')", first_half.unwrap(), column, needle),
2266                            ValueType::String(needle) => self.query = format!("{} WHERE (NOT JSON_CONTAINS({}, '{}')", first_half.unwrap(), column, needle),
2267                            ValueType::Datetime(needle) => self.query = format!("{} WHERE (NOT JSON_CONTAINS({}, '{}')", first_half.unwrap(), column, needle),
2268                            _ => self.query = format!("{} WHERE (NOT JSON_CONTAINS({}, {})", first_half.unwrap(), column, needle)
2269                        },
2270                        _ => self.query = format!("{} WHERE (NOT JSON_CONTAINS({}, {})", first_half.unwrap(), column, needle)
2271                    }
2272                }
2273            },
2274            KeywordList::And => match path {
2275                Some(path) => {
2276                    let split_the_query = self.query.split(" AND ").collect::<Vec<&str>>();
2277
2278                    let length_of_the_split_the_query = split_the_query.len();
2279
2280                    match split_the_query.len() {
2281                        0 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
2282                        1 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
2283                        2 => match needle {
2284                            JsonValue::Initial(initial) => match initial {
2285                                ValueType::JsonString(needle) => self.query = format!("{} AND NOT JSON_CONTAINS({}, '\"{}\"', '${}')", split_the_query[0], column, needle, path),
2286                                ValueType::String(needle) => self.query = format!("{} AND NOT JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
2287                                ValueType::Datetime(needle) => self.query = format!("{} AND NOT JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
2288                                _ => self.query = format!("{} AND NOT JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
2289                            },
2290                            _ => self.query = format!("{} AND NOT JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
2291                        },
2292                        _ => {
2293                            let mut concatenated_string = String::new();
2294
2295                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
2296                                if index == 0 {
2297                                    concatenated_string = chunk.to_string();
2298                                } else if index + 1 != length_of_the_split_the_query {
2299                                    concatenated_string = format!("{} AND {} ", concatenated_string, chunk)
2300                                }
2301                            }
2302
2303                            match needle {
2304                                JsonValue::Initial(initial) => match initial {
2305                                    ValueType::JsonString(needle) => self.query = format!("{}AND NOT JSON_CONTAINS({}, '\"{}\"', '${}')", concatenated_string, column, needle, path),
2306                                    ValueType::String(needle) => self.query = format!("{}AND NOT JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
2307                                    ValueType::Datetime(needle) => self.query = format!("{}AND NOT JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
2308                                    _ => self.query = format!("{}AND NOT JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
2309                                },
2310                                _ => self.query = format!("{}AND NOT JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
2311                            }
2312                        }
2313                    }
2314                },
2315                None => {
2316                    let split_the_query = self.query.split(" AND ").collect::<Vec<&str>>();
2317
2318                    let length_of_the_split_the_query = split_the_query.len();
2319
2320                    match split_the_query.len() {
2321                        0 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
2322                        1 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
2323                        2 => match needle {
2324                            JsonValue::Initial(initial) => match initial {
2325                                ValueType::JsonString(needle) => self.query = format!("{} AND NOT JSON_CONTAINS({}, '\"{}\"')",  split_the_query[0], column, needle),
2326                                ValueType::String(needle) => self.query = format!("{} AND NOT JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
2327                                ValueType::Datetime(needle) => self.query = format!("{} AND NOT JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
2328                                _ => self.query = format!("{} AND NOT JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
2329                            },
2330                            _ => self.query = format!("{} AND NOT JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
2331                        },
2332                        _ => {
2333                            let mut concatenated_string = String::new();
2334
2335                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
2336                                if index == 0 {
2337                                    concatenated_string = chunk.to_string();
2338                                } else if index + 1 != length_of_the_split_the_query {
2339                                    concatenated_string = format!("{} AND {} ", concatenated_string, chunk)
2340                                }
2341                            }
2342
2343                            match needle {
2344                                JsonValue::Initial(initial) => match initial {
2345                                    ValueType::JsonString(needle) => self.query = format!("{}AND NOT JSON_CONTAINS({}, '\"{}\"')", concatenated_string, column, needle),
2346                                    ValueType::String(needle) => self.query = format!("{}AND NOT JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
2347                                    ValueType::Datetime(needle) => self.query = format!("{}AND NOT JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
2348                                    _ => self.query = format!("{}AND NOT JSON_CONTAINS({}, {})", concatenated_string, column, needle)
2349                                },
2350                                _ => self.query = format!("{}AND NOT JSON_CONTAINS({}, {})", concatenated_string, column, needle)
2351                            }
2352                        }
2353                    }
2354                }
2355            },
2356            KeywordList::LeftBracketAnd => match path {
2357                Some(path) => {
2358                    let split_the_query = self.query.split(" AND ").collect::<Vec<&str>>();
2359
2360                    let length_of_the_split_the_query = split_the_query.len();
2361
2362                    match split_the_query.len() {
2363                        0 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
2364                        1 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
2365                        2 => match needle {
2366                            JsonValue::Initial(initial) => match initial {
2367                                ValueType::JsonString(needle) => self.query = format!("{} AND (NOT JSON_CONTAINS({}, '\"{}\"', '${}')", split_the_query[0], column, needle, path),
2368                                ValueType::String(needle) => self.query = format!("{} AND (NOT JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
2369                                ValueType::Datetime(needle) => self.query = format!("{} AND (NOT JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
2370                                _ => self.query = format!("{} AND (NOT JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
2371                            },
2372                            _ => self.query = format!("{} AND (NOT JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
2373                        },
2374                        _ => {
2375                            let mut concatenated_string = String::new();
2376
2377                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
2378                                if index == 0 {
2379                                    concatenated_string = chunk.to_string();
2380                                } else if index + 1 != length_of_the_split_the_query {
2381                                    concatenated_string = format!("{} AND {} ", concatenated_string, chunk)
2382                                }
2383                            }
2384
2385                            match needle {
2386                                JsonValue::Initial(initial) => match initial {
2387                                    ValueType::JsonString(needle) => self.query = format!("{}AND (NOT JSON_CONTAINS({}, '\"{}\"', '${}')", concatenated_string, column, needle, path),
2388                                    ValueType::String(needle) => self.query = format!("{}AND (NOT JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
2389                                    ValueType::Datetime(needle) => self.query = format!("{}AND (NOT JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
2390                                    _ => self.query = format!("{}AND (NOT JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
2391                                },
2392                                _ => self.query = format!("{}AND (NOT JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
2393                            }
2394                        }
2395                    }
2396                },
2397                None => {
2398                    let split_the_query = self.query.split(" AND ").collect::<Vec<&str>>();
2399
2400                    let length_of_the_split_the_query = split_the_query.len();
2401
2402                    match split_the_query.len() {
2403                        0 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
2404                        1 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
2405                        2 => match needle {
2406                            JsonValue::Initial(initial) => match initial {
2407                                ValueType::JsonString(needle) => self.query = format!("{} AND (NOT JSON_CONTAINS({}, '\"{}\"')",  split_the_query[0], column, needle),
2408                                ValueType::String(needle) => self.query = format!("{} AND (NOT JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
2409                                ValueType::Datetime(needle) => self.query = format!("{} AND (NOT JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
2410                                _ => self.query = format!("{} AND (NOT JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
2411                            },
2412                            _ => self.query = format!("{} AND (NOT JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
2413                        },
2414                        _ => {
2415                            let mut concatenated_string = String::new();
2416
2417                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
2418                                if index == 0 {
2419                                    concatenated_string = chunk.to_string();
2420                                } else if index + 1 != length_of_the_split_the_query {
2421                                    concatenated_string = format!("{} AND {} ", concatenated_string, chunk)
2422                                }
2423                            }
2424
2425                            match needle {
2426                                JsonValue::Initial(initial) => match initial {
2427                                    ValueType::JsonString(needle) => self.query = format!("{}AND (NOT JSON_CONTAINS({}, '\"{}\"')", concatenated_string, column, needle),
2428                                    ValueType::String(needle) => self.query = format!("{}AND (NOT JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
2429                                    ValueType::Datetime(needle) => self.query = format!("{}AND (NOT JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
2430                                    _ => self.query = format!("{}AND (NOT JSON_CONTAINS({}, {})", concatenated_string, column, needle)
2431                                },
2432                                _ => self.query = format!("{}AND (NOT JSON_CONTAINS({}, {})", concatenated_string, column, needle)
2433                            }
2434                        }
2435                    }
2436                }
2437            },
2438            KeywordList::Or => match path {
2439                Some(path) => {
2440                    let split_the_query = self.query.split(" OR ").collect::<Vec<&str>>();
2441
2442                    let length_of_the_split_the_query = split_the_query.len();
2443
2444                    match split_the_query.len() {
2445                        0 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
2446                        1 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
2447                        2 => match needle {
2448                            JsonValue::Initial(initial) => match initial {
2449                                ValueType::JsonString(needle) => self.query = format!("{} OR NOT JSON_CONTAINS({}, '\"{}\"', '${}')", split_the_query[0], column, needle, path),
2450                                ValueType::String(needle) => self.query = format!("{} OR NOT JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
2451                                ValueType::Datetime(needle) => self.query = format!("{} OR NOT JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
2452                                _ => self.query = format!("{} OR NOT JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
2453                            },
2454                            _ => self.query = format!("{} OR NOT JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
2455                        },
2456                        _ => {
2457                            let mut concatenated_string = String::new();
2458
2459                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
2460                                if index == 0 {
2461                                    concatenated_string = chunk.to_string();
2462                                } else if index + 1 != length_of_the_split_the_query {
2463                                    concatenated_string = format!("{} OR {} ", concatenated_string, chunk)
2464                                }
2465                            }
2466
2467                            match needle {
2468                                JsonValue::Initial(initial) => match initial {
2469                                    ValueType::JsonString(needle) => self.query = format!("{}OR NOT JSON_CONTAINS({}, '\"{}\"', '${}')", concatenated_string, column, needle, path),
2470                                    ValueType::String(needle) => self.query = format!("{}OR NOT JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
2471                                    ValueType::Datetime(needle) => self.query = format!("{}OR NOT JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
2472                                    _ => self.query = format!("{}OR NOT JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
2473                                },
2474                                _ => self.query = format!("{}OR NOT JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
2475                            }
2476                        }
2477                    }
2478                },
2479                None => {
2480                    let split_the_query = self.query.split(" OR ").collect::<Vec<&str>>();
2481
2482                    let length_of_the_split_the_query = split_the_query.len();
2483
2484                    match split_the_query.len() {
2485                        0 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
2486                        1 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
2487                        2 => match needle {
2488                            JsonValue::Initial(initial) => match initial {
2489                                ValueType::JsonString(needle) => self.query = format!("{} OR NOT JSON_CONTAINS({}, '\"{}\"')",  split_the_query[0], column, needle),
2490                                ValueType::String(needle) => self.query = format!("{} OR NOT JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
2491                                ValueType::Datetime(needle) => self.query = format!("{} OR NOT JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
2492                                _ => self.query = format!("{} OR NOT JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
2493                            },
2494                            _ => self.query = format!("{} OR NOT JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
2495                        },
2496                        _ => {
2497                            let mut concatenated_string = String::new();
2498
2499                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
2500                                if index == 0 {
2501                                    concatenated_string = chunk.to_string();
2502                                } else if index + 1 != length_of_the_split_the_query {
2503                                    concatenated_string = format!("{} OR {} ", concatenated_string, chunk)
2504                                }
2505                            }
2506
2507                            match needle {
2508                                JsonValue::Initial(initial) => match initial {
2509                                    ValueType::JsonString(needle) => self.query = format!("{}OR NOT JSON_CONTAINS({}, '\"{}\"')", concatenated_string, column, needle),
2510                                    ValueType::String(needle) => self.query = format!("{}OR NOT JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
2511                                    ValueType::Datetime(needle) => self.query = format!("{}OR NOT JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
2512                                    _ => self.query = format!("{}OR NOT JSON_CONTAINS({}, {})", concatenated_string, column, needle)
2513                                },
2514                                _ => self.query = format!("{}OR NOT JSON_CONTAINS({}, {})", concatenated_string, column, needle)
2515                            }
2516                        }
2517                    }
2518                }
2519            },
2520            KeywordList::LeftBracketOr => match path {
2521                Some(path) => {
2522                    let split_the_query = self.query.split(" OR ").collect::<Vec<&str>>();
2523
2524                    let length_of_the_split_the_query = split_the_query.len();
2525
2526                    match split_the_query.len() {
2527                        0 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
2528                        1 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
2529                        2 => match needle {
2530                            JsonValue::Initial(initial) => match initial {
2531                                ValueType::JsonString(needle) => self.query = format!("{} OR (NOT JSON_CONTAINS({}, '\"{}\"', '${}')", split_the_query[0], column, needle, path),
2532                                ValueType::String(needle) => self.query = format!("{} OR (NOT JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
2533                                ValueType::Datetime(needle) => self.query = format!("{} OR (NOT JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
2534                                _ => self.query = format!("{} OR (NOT JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
2535                            },
2536                            _ => self.query = format!("{} OR (NOT JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
2537                        },
2538                        _ => {
2539                            let mut concatenated_string = String::new();
2540
2541                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
2542                                if index == 0 {
2543                                    concatenated_string = chunk.to_string();
2544                                } else if index + 1 != length_of_the_split_the_query {
2545                                    concatenated_string = format!("{} OR {} ", concatenated_string, chunk)
2546                                }
2547                            }
2548
2549                            match needle {
2550                                JsonValue::Initial(initial) => match initial {
2551                                    ValueType::JsonString(needle) => self.query = format!("{}OR (NOT JSON_CONTAINS({}, '\"{}\"', '${}')", concatenated_string, column, needle, path),
2552                                    ValueType::String(needle) => self.query = format!("{}OR (NOT JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
2553                                    ValueType::Datetime(needle) => self.query = format!("{}OR (NOT JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
2554                                    _ => self.query = format!("{}OR (NOT JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
2555                                },
2556                                _ => self.query = format!("{}OR (NOT JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
2557                            }
2558                        }
2559                    }
2560                },
2561                None => {
2562                    let split_the_query = self.query.split(" OR ").collect::<Vec<&str>>();
2563
2564                    let length_of_the_split_the_query = split_the_query.len();
2565
2566                    match split_the_query.len() {
2567                        0 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
2568                        1 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
2569                        2 => match needle {
2570                            JsonValue::Initial(initial) => match initial {
2571                                ValueType::JsonString(needle) => self.query = format!("{} OR (NOT JSON_CONTAINS({}, '\"{}\"')",  split_the_query[0], column, needle),
2572                                ValueType::String(needle) => self.query = format!("{} OR (NOT JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
2573                                ValueType::Datetime(needle) => self.query = format!("{} OR (NOT JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
2574                                _ => self.query = format!("{} OR (NOT JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
2575                            },
2576                            _ => self.query = format!("{} OR (NOT JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
2577                        },
2578                        _ => {
2579                            let mut concatenated_string = String::new();
2580
2581                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
2582                                if index == 0 {
2583                                    concatenated_string = chunk.to_string();
2584                                } else if index + 1 != length_of_the_split_the_query {
2585                                    concatenated_string = format!("{} OR {} ", concatenated_string, chunk)
2586                                }
2587                            }
2588
2589                            match needle {
2590                                JsonValue::Initial(initial) => match initial {
2591                                    ValueType::JsonString(needle) => self.query = format!("{}OR (NOT JSON_CONTAINS({}, '\"{}\"')", concatenated_string, column, needle),
2592                                    ValueType::String(needle) => self.query = format!("{}OR (NOT JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
2593                                    ValueType::Datetime(needle) => self.query = format!("{}OR (NOT JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
2594                                    _ => self.query = format!("{}OR (NOT JSON_CONTAINS({}, {})", concatenated_string, column, needle)
2595                                },
2596                                _ => self.query = format!("{}OR (NOT JSON_CONTAINS({}, {})", concatenated_string, column, needle)
2597                            }
2598                        }
2599                    }
2600                }
2601            },
2602            _ => panic!("Wrong usage of '.not_json_contains()' method, it should be used later than either SELECT, WHERE, AND, OR keywords.")
2603        }
2604
2605        self.list.push(KeywordList::NotJsonContains);
2606
2607        self
2608    }
2609
2610    /// it adds `JSON_ARRAY_APPEND()` mysql function with it's synthax. It's intended to used with only update constructor, don't use it with any other kind of query.
2611    /// 
2612    /// ```rust
2613    /// 
2614    /// use qubl::{QueryBuilder, ValueType, JsonValue};
2615    /// 
2616    /// fn main () {
2617    ///     let lesson = ("lesson", &ValueType::String("math".to_string()));
2618    ///     let point = ("point", &ValueType::Int32(100));
2619    ///
2620    ///     let values = vec![lesson, point];
2621    ///
2622    ///     let object = JsonValue::MysqlJsonObject(&values);
2623    ///
2624    ///     let query = QueryBuilder::update().unwrap()
2625    ///                                 .table("users")
2626    ///                                 .json_array_append("points", Some(""), object.clone())
2627    ///                                 .where_("id", "=", ValueType::Int8(1))
2628    ///                                 .finish();
2629    ///
2630    ///     assert_eq!("UPDATE users SET points = JSON_ARRAY_APPEND(points, '$', JSON_OBJECT('lesson', 'math', 'point', 100)) WHERE id = 1;", query);
2631    /// }
2632    /// 
2633    /// ```
2634    pub fn json_array_append(&mut self, column: &str, path: Option<&str>, object: JsonValue) -> &mut Self {
2635        match self.list.last() {
2636            Some(keyword) => match keyword {
2637                KeywordList::Set => {
2638                    match path {
2639                        Some(path) => match object {
2640                            JsonValue::Initial(initial) => match initial {
2641                                ValueType::JsonString(object) => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '${}', '\"{}\"')", self.query, column, column, path, object),
2642                                ValueType::String(object) => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '${}', '{}')", self.query, column, column, path, object),
2643                                ValueType::Datetime(object) => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '${}', '{}')", self.query, column, column, path, object),
2644                                _ => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '${}', {})", self.query, column, column, path, object),
2645                            },
2646                            _ => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '${}', {})", self.query, column, column, path, object),
2647                        }
2648                        None => match object {
2649                            JsonValue::Initial(initial) => match initial {
2650                                ValueType::JsonString(object) => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '$', '\"{}\"')", self.query, column, column, object),
2651                                ValueType::String(object) => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '$', '{}')", self.query, column, column, object),
2652                                ValueType::Datetime(object) => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '$', '{}')", self.query, column, column, object),
2653                                _ => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '$', {})", self.query, column, column, object)
2654                            },
2655                            _ => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '$', {})", self.query, column, column, object)
2656                        }
2657                    }
2658                },
2659                _ => {
2660                    match path {
2661                        Some(path) => match object {
2662                            JsonValue::Initial(initial) => match initial {
2663                                ValueType::JsonString(object) => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '${}', '\"{}\"')", self.query, column, column, path, object),
2664                                ValueType::String(object) => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '${}', '{}')", self.query, column, column, path, object),
2665                                ValueType::Datetime(object) => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '${}', '{}')", self.query, column, column, path, object),
2666                                _ => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '${}', {})", self.query, column, column, path, object),
2667                            },
2668                            _ => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '${}', {})", self.query, column, column, path, object),
2669                        }
2670                        None => match object {
2671                            JsonValue::Initial(initial) => match initial {
2672                                ValueType::JsonString(object) => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '$', '\"{}\"')", self.query, column, column, object),
2673                                ValueType::String(object) => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '$', '{}')", self.query, column, column, object),
2674                                ValueType::Datetime(object) => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '$', '{}')", self.query, column, column, object),
2675                                _ => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '$', {})", self.query, column, column, object)
2676                            },
2677                            _ => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '$', {})", self.query, column, column, object)
2678                        }
2679                    }
2680                }
2681            },
2682            None => panic!("it's impossible to came here!")
2683        }
2684
2685        self.list.push(KeywordList::JsonArrayAppend);
2686        self
2687    }
2688
2689    /// it adds "JSON_REMOVE()" function with it's synthax. You cannot pass empty strings to paths.
2690    /// 
2691    /// ```rust
2692    /// 
2693    /// use qubl::{QueryBuilder, ValueType};
2694    /// 
2695    /// fn main () {
2696    ///   let query = QueryBuilder::update().unwrap()
2697    ///                            .table("blogs")
2698    ///                            .json_remove("likes", vec!["[10]"])
2699    ///                            .where_("blog_id", "=", ValueType::Int32(20))
2700    ///                            .finish();
2701    ///
2702    ///   assert_eq!(query, "UPDATE blogs SET likes = JSON_REMOVE(likes, '$[10]') WHERE blog_id = 20;");
2703    /// }
2704    /// 
2705    /// ```
2706    pub fn json_remove(&mut self, column: &str, paths: Vec<&str>) -> &mut Self {
2707        match paths.iter().any(|path| *path == "") {
2708            true => panic!("Error: a value in the paths cannot be empty string, panicking..."),
2709            false => ()
2710        }
2711
2712        match self.list.last() {
2713            Some(keyword) => match keyword {
2714                KeywordList::Set => {
2715                    self.query = format!("{}, {} = JSON_REMOVE({}", self.query, column, column);
2716
2717                    for path in paths {
2718                        if path.starts_with("$") {
2719                            self.query = format!("{}, '${}'", self.query, path.replace("$", ""))
2720                        } else {
2721                            self.query = format!("{}, '${}'", self.query, path)
2722                        }
2723                    }
2724
2725                    self.query = format!("{})", self.query)
2726                },
2727                _ => {
2728                    self.query = format!("{} SET {} = JSON_REMOVE({}", self.query, column, column);
2729
2730                    for path in paths {
2731                        if path.starts_with("$") {
2732                            self.query = format!("{}, '${}'", self.query, path.replace("$", ""))
2733                        } else {
2734                            self.query = format!("{}, '${}'", self.query, path)
2735                        }
2736                    }
2737
2738                    self.query = format!("{})", self.query)
2739                }
2740            },
2741            None => panic!("it's impossible to came here!")
2742        }
2743
2744        self.list.push(KeywordList::JsonRemove);
2745        self
2746    }
2747
2748    /// It adds `JSON_SET()` function with it's synthax. It updates values with the specified path.
2749    /// 
2750    /// ```rust
2751    /// 
2752    /// use qubl::{QueryBuilder, ValueType, JsonValue};
2753    /// 
2754    /// fn main () {
2755    /// 
2756    /// let lesson = ("lesson", &ValueType::String("math".to_string()));
2757    /// let point = ("point", &ValueType::Int32(100));
2758    ///
2759    /// let values = vec![lesson, point];
2760    ///
2761    /// let object = JsonValue::MysqlJsonObject(&values);
2762    ///
2763    /// let query = QueryBuilder::update().unwrap()
2764    ///                          .table("users")
2765    ///                          .json_set("points", "[0]", object)
2766    ///                          .where_("id", "=", ValueType::Int32(1))
2767    ///                          .finish();
2768    ///
2769    /// assert_eq!("UPDATE users SET points = JSON_SET(points, '$[0]', JSON_OBJECT('lesson', 'math', 'point', 100)) WHERE id = 1;", query);
2770    /// 
2771    /// }
2772    /// 
2773    /// ```
2774    pub fn json_set(&mut self, column: &str, path: &str, value: JsonValue) -> &mut Self {
2775        match self.list.last() {
2776            Some(keyword) => match keyword {
2777                KeywordList::Set => match value {
2778                    JsonValue::Initial(initial) => match initial {
2779                        ValueType::JsonString(value) => self.query = format!("{}, {} = JSON_SET({}, '${}', '\"{}\"')", self.query, column, column, path, value),
2780                        ValueType::String(value) => self.query = format!("{}, {} = JSON_SET({}, '${}', '{}')", self.query, column, column, path, value),
2781                        ValueType::Datetime(value) => self.query = format!("{}, {} = JSON_SET({}, '${}', '{}')", self.query, column, column, path, value),
2782                        _ => self.query = format!("{}, {} = JSON_SET({}, '${}', {})", self.query, column, column, path, value),
2783                    },
2784                    _ => self.query = format!("{}, {} = JSON_SET({}, '${}', {})", self.query, column, column, path, value),
2785                }
2786                _ => match value {
2787                    JsonValue::Initial(initial) => match initial {
2788                        ValueType::JsonString(value) => self.query = format!("{} SET {} = JSON_SET({}, '${}', '\"{}\"')", self.query, column, column, path, value),
2789                        ValueType::String(value) => self.query = format!("{} SET {} = JSON_SET({}, '${}', '{}')", self.query, column, column, path, value),
2790                        ValueType::Datetime(value) => self.query = format!("{} SET {} = JSON_SET({}, '${}', '{}')", self.query, column, column, path, value),
2791                        _ => self.query = format!("{} SET {} = JSON_SET({}, '${}', {})", self.query, column, column, path, value)
2792                    },
2793                    _ => self.query = format!("{} SET {} = JSON_SET({}, '${}', {})", self.query, column, column, path, value)
2794                }
2795            },
2796            None => panic!("it's impossible to came here!")
2797        }
2798
2799        self.list.push(KeywordList::JsonSet);
2800        self
2801    }
2802
2803    /// It adds `JSON_REPLACE()` function with it's synthax. It updates values with the specified path.
2804    /// 
2805    /// ```rust
2806    /// 
2807    /// use qubl::{QueryBuilder, ValueType, JsonValue};
2808    /// 
2809    /// fn main () {
2810    /// 
2811    /// let value = ValueType::Int32(100);
2812    /// let value = JsonValue::Initial(&value);
2813    ///
2814    /// let query = QueryBuilder::update().unwrap()
2815    ///                          .table("users")
2816    ///                          .json_replace("points", "[0].point", value)
2817    ///                          .where_("id", "=", ValueType::Int32(1))
2818    ///                          .finish();
2819    ///
2820    /// assert_eq!("UPDATE users SET points = JSON_REPLACE(points, '$[0].point', 100) WHERE id = 1;", query)
2821    /// 
2822    /// }
2823    /// 
2824    /// ```
2825    pub fn json_replace(&mut self, column: &str, path: &str, value: JsonValue) -> &mut Self {
2826        match self.list.last() {
2827            Some(keyword) => match keyword {
2828                KeywordList::Set => match value {
2829                    JsonValue::Initial(initial) => match initial {
2830                        ValueType::JsonString(value) => self.query = format!("{}, {} = JSON_REPLACE({}, '${}', '\"{}\"')", self.query, column, column, path, value),
2831                        ValueType::String(value) => self.query = format!("{}, {} = JSON_REPLACE({}, '${}', '{}')", self.query, column, column, path, value),
2832                        ValueType::Datetime(value) => self.query = format!("{}, {} = JSON_REPLACE({}, '${}', '{}')", self.query, column, column, path, value),
2833                        _ => self.query = format!("{}, {} = JSON_REPLACE({}, '${}', {})", self.query, column, column, path, value),
2834                    },
2835                    _ => self.query = format!("{}, {} = JSON_REPLACE({}, '${}', {})", self.query, column, column, path, value),
2836                }
2837                _ => match value {
2838                    JsonValue::Initial(initial) => match initial {
2839                        ValueType::JsonString(value) => self.query = format!("{} SET {} = JSON_REPLACE({}, '${}', '\"{}\"')", self.query, column, column, path, value),
2840                        ValueType::String(value) => self.query = format!("{} SET {} = JSON_REPLACE({}, '${}', '{}')", self.query, column, column, path, value),
2841                        ValueType::Datetime(value) => self.query = format!("{} SET {} = JSON_REPLACE({}, '${}', '{}')", self.query, column, column, path, value),
2842                        _ => self.query = format!("{} SET {} = JSON_REPLACE({}, '${}', {})", self.query, column, column, path, value)
2843                    },
2844                    _ => self.query = format!("{} SET {} = JSON_REPLACE({}, '${}', {})", self.query, column, column, path, value)
2845                }
2846            },
2847            None => panic!("it's impossible to came here!")
2848        }
2849
2850        self.list.push(KeywordList::JsonSet);
2851        self
2852    }
2853
2854    /// finishes the query and returns the result as string.
2855    pub fn finish(&self) -> String {
2856        return format!("{};", self.query);
2857    }
2858
2859    /// gives you an immutable copy of that instance, just for case if you need to share and potentially mutate it across threads.
2860    pub fn copy(&mut self) -> Self {
2861        Self {
2862            query: self.query.clone(),
2863            table: self.table.clone(),
2864            qtype: self.qtype.clone(),
2865            list: self.list.clone(),
2866            hq: self.hq
2867        }
2868    }
2869
2870    fn load_hqs() -> [&'a str; 26] {
2871        [";", "; drop", "admin' #", "admin'/*", "; union", "or 1 = 1",
2872        "or 1 = 1#", "or 1 = 1/*", "or true = true", "or false = false", "or '1' = '1'", "or '1' = '1'#",
2873        "or '1' = '1'/*", "; sleep(", "--", "drop table", "drop schema", "select if", "union select",
2874        "union all", "exec", "master..", "masters..", "information_schema", "load_file", "alter user"]
2875    }
2876
2877    fn sanitize_column(&mut self, column: &str)  -> std::result::Result<(), std::io::Error>  {
2878        match self.hq {
2879            Some(hqs) => {
2880                for _hq in hqs.iter() {
2881                    if &column == _hq {
2882                        return Err(std::io::Error::new(std::io::ErrorKind::PermissionDenied, "You cannot run arbitrary queries"))
2883                    }
2884                }
2885            },
2886            None => return Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "Your input is invalid."))
2887        }
2888
2889        Ok(())
2890    }
2891
2892    /// checks the inputs for potential sql injection patterns and throws error if they exist.
2893    fn sanitize_columns(columns: &Vec<&str>, hqs: [&'a str; 26]) -> std::result::Result<(), std::io::Error> {
2894        if columns.len() == 1 && columns[0] == "" {
2895            return Ok(());
2896        };
2897
2898        for column in columns.iter() {
2899            for hq in hqs.iter() {
2900                if column == hq {
2901                    return Err(std::io::Error::new(std::io::ErrorKind::PermissionDenied, "You cannot run arbitrary queries"))
2902                }
2903            }
2904        }
2905
2906        return Ok(())
2907    }
2908
2909    fn sanitize_inputs(inputs: &Vec<ValueType>, hqs: [&'a str; 26]) -> std::result::Result<(), std::io::Error> {
2910        for input in inputs.iter() {
2911            match input {
2912                ValueType::String(string) | ValueType::Datetime(string) => {
2913                    for hq in hqs.iter() {
2914                        if &string == hq {
2915                            return Err(std::io::Error::new(std::io::ErrorKind::PermissionDenied, "You cannot run arbitrary queries"))
2916                        }
2917                    }
2918                },
2919                _ => continue
2920            }
2921        }
2922
2923        return Ok(())
2924    }
2925
2926    fn sanitize_input(&mut self, input: &ValueType) -> std::result::Result<(), std::io::Error> {
2927        match input {
2928            ValueType::String(string) | ValueType::Datetime(string) => {
2929                match self.hq {
2930                    Some(hqs) => {
2931                        for hq in hqs.iter() {
2932                            if &string == hq {
2933                                return Err(std::io::Error::new(std::io::ErrorKind::PermissionDenied, "You cannot run arbitrary queries"))
2934                            }
2935                        }
2936                    },
2937                    None => return Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "Your input is invalid."))
2938                }
2939            },
2940            _ => return Ok(())
2941        };
2942
2943        Ok(())
2944    }
2945
2946    fn sanitize_mark(input: &str) -> std::result::Result<(), std::io::Error> {
2947        return match input {
2948            "=" | "<" | ">" | "<=" | ">=" | "!=" | "<>" => Ok(()),
2949            _ => Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "comparison operators cannot be other than =, <, >, <=,  >=, != or <>."))
2950        }
2951    }
2952
2953    fn sanitize_str(&mut self, input: &str) -> std::result::Result<(), std::io::Error> {
2954        match self.hq {
2955            Some(hqs) => {
2956                for hq in hqs.iter() {
2957                    if *hq == input {
2958                        return Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "comparison operators cannot be other than =, <, >, <=,  >=, != or <>."))
2959                    }
2960                }
2961            },
2962            None => return Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "comparison operators cannot be other than =, <, >, <=,  >=, != or <>."))
2963        }
2964
2965        Ok(())
2966    }
2967}
2968
2969/// Struct that benefits you to create and use schema's.
2970#[derive(Debug, Clone)]
2971pub struct SchemaBuilder {
2972    pub query: String,
2973    pub schema: String,
2974    pub list: Vec<KeywordList>
2975}
2976
2977/// implementations fon SchemaBuilder
2978impl SchemaBuilder {
2979    pub fn create(name: &str) -> std::result::Result<Self, std::io::Error> {
2980        if name.contains("!") ||
2981           name.contains("-") ||
2982           name.contains("=") ||
2983           name.contains("+") ||
2984           name.contains("%") ||
2985           name.contains("$") ||
2986           name.contains("&") ||
2987           name.contains("#") ||
2988           name.contains("[") ||
2989           name.contains("]") ||
2990           name.contains("{") ||
2991           name.contains("}") ||
2992           name.contains(":") ||
2993           name.contains(";") ||
2994           name.contains("'") ||
2995           name.contains("\"") ||
2996           name.contains(",") ||
2997           name.contains(".") {
2998                return Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "Schame name cannot include these characters: '!', '-', '=', '+', '%', '$', '&', '#', '[', ']', '{', '}', ':', ';', '\"', \"'\", '.', ','"))
2999        }
3000
3001        Ok(Self {
3002            query: format!("CREATE DATABASE {}", name),
3003            schema: name.to_string(),
3004            list: vec![KeywordList::Create]
3005        })
3006    }
3007
3008    pub fn use_another_schema(name: &str) -> std::result::Result<Self, std::io::Error> {
3009        if name.contains("!") ||
3010        name.contains("-") ||
3011        name.contains("=") ||
3012        name.contains("+") ||
3013        name.contains("%") ||
3014        name.contains("$") ||
3015        name.contains("&") ||
3016        name.contains("#") ||
3017        name.contains("[") ||
3018        name.contains("]") ||
3019        name.contains("{") ||
3020        name.contains("}") ||
3021        name.contains(":") ||
3022        name.contains(";") ||
3023        name.contains("'") ||
3024        name.contains("\"") ||
3025        name.contains(",") ||
3026        name.contains(".") {
3027             return Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "Schame name cannot include these characters: '!', '-', '=', '+', '%', '$', '&', '#', '[', ']', '{', '}', ':', ';', '\"', \"'\", '.', ','"))
3028        }
3029
3030        Ok(Self {
3031            query: format!("USE {}", name),
3032            schema: name.to_string(),
3033            list: vec![KeywordList::Use, KeywordList::Create]
3034        })
3035    }
3036
3037    pub fn if_not_exists(&mut self) -> &mut Self {
3038        match self.list[0] {
3039            KeywordList::Create => (),
3040            KeywordList::Table => (),
3041            _ => panic!("if_not_exists method cannot be used without Create or Table queries")
3042        }
3043
3044        let split_the_query =  self.query.split(" DATABASE ").collect::<Vec<&str>>();
3045        self.query = format!("{} DATABASE IF NOT EXISTS {}", split_the_query[0], split_the_query[1]);
3046
3047        self.list.insert(0, KeywordList::IfNotExist);
3048        self
3049    }
3050
3051    pub fn use_schema(&mut self, name: Option<&str>) -> &mut Self {
3052        match name {
3053            Some(schema_name) => {
3054                self.query = format!("USE {}", schema_name)
3055            },
3056            None => {
3057                self.query = format!("USE {}", self.schema);
3058            }
3059        }
3060
3061        self
3062    }
3063
3064    pub fn finish(&self) -> String {
3065        return format!("{};", self.query)
3066    }
3067}
3068
3069/// Struct that benefits you to create Tables. Currently incomplete thoug.
3070#[derive(Debug, Clone)]
3071pub struct TableBuilder {
3072    pub query: String,
3073    pub name: String,
3074    pub schema: String,
3075    pub all: Vec<String>,
3076}
3077
3078/// Struct that benefits to define a foreign key.
3079#[derive(Debug, Clone)]
3080pub struct ForeignKey {
3081    pub first: ForeignKeyItem,
3082    pub second: ForeignKeyItem,
3083    pub on_delete: Option<ForeignKeyActions>,
3084    pub on_update: Option<ForeignKeyActions>,
3085    pub constraint: Option<String>
3086}
3087
3088/// Struct that benefits you to add a foreign key item to a foreign key.
3089#[derive(Debug, Clone)]
3090pub struct ForeignKeyItem {
3091    pub table: String,
3092    pub column: String
3093}
3094
3095/// implementations for TableBuilder
3096impl TableBuilder {
3097    pub fn create(schema_name: &str, table_name: &str) -> Self {
3098        return Self {
3099            query: format!("CREATE TABLE {} (", table_name),
3100            schema: schema_name.to_string(),
3101            name: table_name.to_string(),
3102            all: vec![]
3103        }
3104    }
3105
3106    pub fn if_not_exists(&mut self) -> &mut Self {
3107        self.query = format!("{}IF NOT EXISTS (", self.query.replace("(", ""));
3108
3109        self
3110    }
3111
3112    pub fn add_column(&mut self, column_name: &str) -> &mut Self {
3113        if self.query.ends_with("(") {
3114            self.query = format!("{}{}", self.query, column_name)
3115        } else {
3116            self.query = format!("{}, {}", self.query, column_name)
3117        }
3118
3119        self
3120    }
3121
3122    pub fn col_type(&mut self, type_name: &str) -> &mut Self {
3123        if self.query.ends_with("(") {
3124            panic!("Cannot add type before defining a column name.")
3125        }
3126
3127        self.query = format!("{} {}", self.query, type_name);
3128
3129        self
3130    }
3131
3132    pub fn null(&mut self) -> &mut Self {
3133        self.query = format!("{} NULL", self.query);
3134
3135        self
3136    }
3137
3138    pub fn not_null(&mut self) -> &mut Self {
3139        self.query = format!("{} NOT NULL", self.query);
3140
3141        self
3142    }
3143
3144    pub fn auto_increment(&mut self) -> &mut Self {
3145        self.query = format!("{} AUTO_INCREMENT", self.query);
3146
3147        self
3148    }
3149
3150    pub fn primary_key(&mut self) -> &mut Self {
3151        if self.query.contains("PRIMARY KEY") {
3152            panic!("A table cannot have two primary keys.")
3153        }
3154
3155        self.query = format!("{} PRIMARY KEY", self.query);
3156
3157        self
3158    }
3159
3160    pub fn default(&mut self, value: ValueType) -> &mut Self {
3161        let split_the_query = self.query.clone();
3162        let split_the_query = split_the_query.split(", ").collect::<Vec<&str>>();
3163
3164        let last_query = split_the_query[split_the_query.len() - 1];
3165
3166        if last_query.contains("INT") || 
3167           last_query.contains("TINYINT") ||
3168           last_query.contains("SMALLINT") ||
3169           last_query.contains("MEDIUMINT") ||
3170           last_query.contains("BIGINT") ||
3171           last_query.contains("BIT") ||
3172           last_query.contains("SERIAL") {
3173            match value {
3174                ValueType::Int8(int) => self.query = format!("{} DEFAULT {}", self.query, int),
3175                ValueType::Int16(int) => self.query = format!("{} DEFAULT {}", self.query, int),
3176                ValueType::Int32(int) => self.query = format!("{} DEFAULT {}", self.query, int),
3177                ValueType::Int64(int) => self.query = format!("{} DEFAULT {}", self.query, int),
3178                ValueType::Uint8(int) => self.query = format!("{} DEFAULT {}", self.query, int),
3179                ValueType::Uint16(int) => self.query = format!("{} DEFAULT {}", self.query, int),
3180                ValueType::Uint32(int) => self.query = format!("{} DEFAULT {}", self.query, int),
3181                ValueType::Uint64(int) => self.query = format!("{} DEFAULT {}", self.query, int),
3182                ValueType::Usize(int) => self.query = format!("{} DEFAULT {}", self.query, int),
3183                ValueType::Float32(int) => self.query = format!("{} DEFAULT {}", self.query, int),
3184                ValueType::Float64(int) => self.query = format!("{} DEFAULT {}", self.query, int),
3185                _ => panic!("Error: If your column has the of the types of INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT, BIT or SERIAL, it has to be an i8, i16, i32, i64, i128, usize, u8,  u16, u32, u64.")
3186            }
3187        }
3188
3189        if last_query.contains("BOOL") || 
3190           last_query.contains("BOOLEAN") {
3191            match value {
3192                ValueType::Boolean(boolean) => self.query = format!("{} DEFAULT {}", self.query, boolean),
3193                _ => panic!("If your column type is BOOLEAN, you have to write either true or false.")
3194            }    
3195        }
3196
3197        if last_query.contains("CHAR") ||
3198           last_query.contains("VARCHAR") ||
3199           last_query.contains("TEXT") ||
3200           last_query.contains("TINYTEXT") ||
3201           last_query.contains("MEDIUMTEXT") ||
3202           last_query.contains("LONGTEXT") ||
3203           last_query.contains("BINARY") ||
3204           last_query.contains("VARBINARY") {
3205            match value {
3206                ValueType::String(ref text) => self.query = format!("{} DEFAULT '{}'", self.query, text),
3207                _ => panic!("Error: if your column type is one of the types of CHAR, VARCHAR, TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT, BINARY or VARBINARY, your value type has to be String.")
3208            }
3209        }
3210
3211        if last_query.contains("DATETIME") ||
3212           last_query.contains("TIMESTAMP") {
3213            match value {
3214                ValueType::Datetime(datetime) => self.query = format!("{} DEFAULT {}", self.query, datetime),
3215                _ => panic!("Error: if your column type is one of the types of CHAR, VARCHAR, TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT, BINARY or VARBINARY, your value type has to be String.")
3216            }
3217        }
3218
3219        self
3220    }
3221
3222    pub fn unique(&mut self) -> &mut Self {
3223        self.query = format!("{} UNIQUE", self.query);
3224
3225        self
3226    }
3227
3228    pub fn check(&mut self, condition: &str) -> &mut Self {
3229        self.query = format!("{} CHECK({})", self.query, condition);
3230
3231        self
3232    }
3233
3234    pub fn character_set(&mut self, character_set: &str) -> &mut Self {
3235        self.query = format!("{} CHARACTER SET {}", self.query, character_set);
3236
3237        self
3238    }
3239
3240    pub fn foreign_key(&mut self, opts: ForeignKey) -> &mut Self {
3241        if self.query.starts_with("ALTER TABLE") {
3242            match opts.constraint {
3243                Some(constraint) => self.query = format!("{}, ADD CONSTRAINT {} FOREIGN KEY ({})", self.query, constraint, opts.first.column),
3244                None => self.query = format!("{}, ADD FOREIGN KEY ({})", self.query, opts.first.column)
3245            }
3246            
3247        } else {
3248            match opts.constraint {
3249                Some(constraint) => self.query = format!("{}, CONSTRAINT {} FOREIGN KEY ({})", self.query, constraint, opts.first.column),
3250                None => self.query = format!("{}, FOREIGN KEY ({})", self.query, opts.first.column)
3251            }
3252        }
3253
3254        self.query = format!("{} REFERENCES {}({})", self.query, opts.second.table, opts.second.column);
3255
3256        match opts.on_delete {
3257            Some(on_delete_opt) => self.query = format!("{} ON DELETE {}", self.query, on_delete_opt),
3258            None => ()
3259        }
3260
3261        match opts.on_update {
3262            Some(on_update_opt) => self.query = format!("{} ON UPDATE {}", self.query, on_update_opt),
3263            None => ()
3264        }
3265
3266        self
3267    }
3268
3269    pub fn unsigned(&mut self) -> &mut Self {
3270        self.query = format!("{} UNSIGNED", self.query);
3271
3272        self
3273    }
3274
3275    pub fn zerofill(&mut self) -> &mut Self {
3276        self.query = format!("{} ZEROFILL", self.query);
3277
3278        self
3279    }
3280
3281    pub fn enum_sql(&mut self, enum_vec: Vec<&str>) -> &mut Self {
3282        match enum_vec.len() {
3283            0 => panic!("enum_vec argument cannot be an empty vector"),
3284            _ => ()
3285        }
3286        
3287        self.query = format!("{} ENUM(", self.query);
3288
3289        let length_of_enum_vec = enum_vec.len();
3290        for (index, item) in enum_vec.into_iter().enumerate() {
3291            if index + 1 == length_of_enum_vec {
3292                self.query = format!("{}'{}'", self.query, item)
3293            } else {
3294                self.query = format!("{}'{}', ", self.query, item)
3295            }
3296        }
3297
3298        self
3299    }
3300
3301    pub fn generated_always(&mut self, condition: &str) -> &mut Self {
3302        self.query = format!("{} GENERATED ALWAYS AS {}", self.query, condition);
3303
3304        self
3305    }
3306
3307    pub fn virtual_sql(&mut self) -> &mut Self {
3308        self.query = format!("{} VIRTUAL", self.query);
3309
3310        self
3311    }
3312
3313    pub fn stored(&mut self) -> &mut Self {
3314        self.query = format!("{} STORED", self.query);
3315
3316        self
3317    }
3318
3319    pub fn spatial(&mut self) -> &mut Self {
3320        self.query = format!("{} SPATIAL", self.query);
3321
3322        self
3323    }
3324
3325    pub fn generated(&mut self) -> &mut Self {
3326        self.query = format!("{} GENERATED", self.query);
3327
3328        self
3329    }
3330
3331    pub fn index(&mut self, indexes: Vec<&str>) -> &mut Self {
3332        let length_of_indexes = indexes.len();
3333
3334        match length_of_indexes {
3335            0 => panic!("There is no index here."),
3336            1 => self.query = format!("{}, INDEX({})", self.query, indexes[0]),
3337            _ => {
3338                for (i, index) in indexes.into_iter().enumerate() {
3339                    if i + 1 == length_of_indexes {
3340                        self.query = format!("{}{}", self.query, index);
3341
3342                        continue;
3343                    }
3344
3345                    if i == 0 {
3346                        self.query = format!("{}, INDEX ({}, ", self.query, index);
3347
3348                        continue;
3349                    }
3350
3351                    self.query = format!("{}{}, ", self.query, index)
3352                }
3353            }
3354        }
3355
3356        self
3357    }
3358
3359    pub fn comment(&mut self, comment: &str) -> &mut Self {
3360        self.query = format!("{} COMMENT '{}'", self.query, comment);
3361
3362        self
3363    }
3364
3365    pub fn default_on_null(&mut self, value: ValueType) -> &mut Self {
3366        match value {
3367            ValueType::String(text) => self.query = format!("{} DEFAULT {} ON NULL", self.query, text),
3368            _ => self.query = format!("{} DEFAULT {} ON NULL", self.query, value),
3369        }
3370
3371        self
3372    }
3373
3374    pub fn invisible(&mut self) -> &mut Self {
3375        self.query = format!("{} INVISIBLE", self.query);
3376
3377        self
3378    }
3379
3380    pub fn custom_query(&mut self, query: &str) -> &mut Self {
3381        self.query = format!("{} {}", self.query, query);
3382
3383        self
3384    }
3385
3386    pub fn finish(&mut self) -> String {
3387        return format!("{});", self.query)
3388    }
3389}
3390
3391/// KeywordList enum. It helps to syntactically correcting the queries. 
3392#[derive(Debug, Clone, PartialEq)]
3393pub enum KeywordList {
3394    Select, Update, Delete, Insert, Count, Table, Where, Or, And, Set, 
3395    Finish, OrderBy, GroupBy, Having, Like, Limit, Offset, IfNotExist, Create, Use, WhereIn, 
3396    WhereNotIn, AndIn, AndNotIn, OrIn, OrNotIn, JsonExtract, JsonContains, NotJsonContains, JsonArrayAppend, JsonRemove, JsonSet, JsonReplace, 
3397    Field, Union, UnionAll, Timezone, GlobalTimezone, InnerJoin, LeftJoin, RightJoin, LeftBracketWhere, LeftBracketAnd, LeftBracketOr, RightBracket
3398}
3399
3400/// QueryType enum. It helps to detect the type of a query with more optimized way when is needed.
3401#[derive(Debug, Clone)]
3402pub enum QueryType {
3403    Select, Update, Delete, Insert, Null, Create, Count
3404}
3405
3406/// BracketType enum. It helps you to open brackets with Corresponding keyword of it's variant on sql queries.
3407#[derive(Debug, Clone)]
3408pub enum BracketType {
3409    Where, And, Or
3410}
3411
3412impl std::fmt::Display for BracketType {
3413    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
3414        match self {
3415            BracketType::Where => write!(f, "WHERE"),
3416            BracketType::And => write!(f, "AND"),
3417            BracketType::Or => write!(f, "OR")
3418        }
3419    }
3420}
3421
3422/// ValueType enum. It benefits to detect and format the value with optimized way when you have to work with exact column values. 
3423#[derive(Debug, Clone)]
3424pub enum ValueType {
3425    String(String), Datetime(String), Null, Boolean(bool), Int32(i32), Int16(i16), Int8(i8), Int64(i64), Int128(i128),
3426    Uint8(u8), Uint16(u16), Uint32(u32), Uint64(u64), Usize(usize), Float32(f32), Float64(f64),
3427    EpochTime(i64), JsonString(String)
3428}
3429
3430impl std::fmt::Display for ValueType {
3431    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
3432        match self {
3433            ValueType::String(string) => write!(f, "'{}'", string),
3434            ValueType::JsonString(string) => write!(f, "\"{}\"", string),
3435            ValueType::Datetime(datetime) => match datetime.as_str() {
3436                "CURRENT_TIMESTAMP" | "UNIX_TIMESTAMP" | "CURRENT_DATE" | "CURRENT_TIME" | "NOW()" | "CURDATE()" | "CURTIME()" => write!(f, "{}", datetime),
3437                _ => write!(f, "'{}'", datetime)
3438            },
3439            ValueType::Null => write!(f, "NULL"),
3440            ValueType::Boolean(val) => write!(f, "{}", val),
3441            ValueType::Int8(val) => write!(f, "{}", val),
3442            ValueType::Int16(val) => write!(f, "{}", val),
3443            ValueType::Int32(val) => write!(f, "{}", val),
3444            ValueType::Int64(val) => write!(f, "{}", val),
3445            ValueType::Int128(val) => write!(f, "{}", val),
3446            ValueType::Usize(val) => write!(f, "{}", val),
3447            ValueType::Uint8(val) => write!(f, "{}", val),
3448            ValueType::Uint16(val) => write!(f, "{}", val),
3449            ValueType::Uint32(val) => write!(f, "{}", val),
3450            ValueType::Uint64(val) => write!(f, "{}", val),
3451            ValueType::Float32(val) => write!(f, "{}", val),
3452            ValueType::Float64(val) => write!(f, "{}", val),
3453            ValueType::EpochTime(val) => write!(f, "FROM_UNIXTIME({})", val),
3454        }
3455    }
3456}
3457
3458impl From<String> for ValueType { fn from(value: String) -> Self { ValueType::String(value) } }
3459impl From<bool> for ValueType { fn from(value: bool) -> Self { ValueType::Boolean(value) } }
3460impl From<i8> for ValueType { fn from(value: i8) -> Self { ValueType::Int8(value) } }
3461impl From<i16> for ValueType { fn from(value: i16) -> Self { ValueType::Int16(value) } }
3462impl From<i32> for ValueType { fn from(value: i32) -> Self { ValueType::Int32(value) } }
3463impl From<i64> for ValueType { fn from(value: i64) -> Self { ValueType::Int64(value) } }
3464impl From<i128> for ValueType { fn from(value: i128) -> Self { ValueType::Int128(value) } }
3465impl From<usize> for ValueType { fn from(value: usize) -> Self { ValueType::Usize(value) } }
3466impl From<u8> for ValueType { fn from(value: u8) -> Self { ValueType::Uint8(value) } }
3467impl From<u16> for ValueType { fn from(value: u16) -> Self { ValueType::Uint16(value) } }
3468impl From<u32> for ValueType { fn from(value: u32) -> Self { ValueType::Uint32(value) } }
3469impl From<u64> for ValueType { fn from(value: u64) -> Self { ValueType::Uint64(value) } }
3470impl From<f32> for ValueType { fn from(value: f32) -> Self { ValueType::Float32(value) } }
3471impl From<f64> for ValueType { fn from(value: f64) -> Self { ValueType::Float64(value) } }
3472
3473
3474impl Into<String> for ValueType {
3475    fn into(self) -> String {
3476        match self {
3477            ValueType::String(text) => text,
3478            ValueType::Datetime(datetime) => datetime,
3479            _ => panic!("you cannot convert a ValueType to string unless it's not a String or Datetime variant.")
3480        }
3481    }
3482}
3483
3484impl Into<bool> for ValueType {
3485    fn into(self) -> bool {
3486        match self {
3487            ValueType::Boolean(val) => val,
3488            ValueType::String(text) => match text.as_str() {
3489                "false" | "" | "\0" | "0" => false,
3490                _ => true,
3491            }
3492            ValueType::Null => false,
3493            ValueType::Int8(val) => match val == 0 {
3494                false => true,
3495                true => false
3496            },
3497            ValueType::Int16(val) => match val == 0 {
3498                false => true,
3499                true => false
3500            },
3501            ValueType::Int32(val) => match val == 0 {
3502                false => true,
3503                true => false
3504            },
3505            ValueType::Int64(val) => match val == 0 {
3506                false => true,
3507                true => false
3508            },
3509            ValueType::Int128(val) => match val == 0 {
3510                false => true,
3511                true => false
3512            },
3513            ValueType::Uint8(val) => match val == 0 {
3514                false => true,
3515                true => false
3516            },
3517            ValueType::Uint16(val) => match val == 0 {
3518                false => true,
3519                true => false
3520            },
3521            ValueType::Uint32(val) => match val == 0 {
3522                false => true,
3523                true => false
3524            },
3525            ValueType::Uint64(val) => match val == 0 {
3526                false => true,
3527                true => false
3528            },
3529            ValueType::Float32(val) => match val == 0.0 {
3530                false => true,
3531                true => false
3532            },
3533            ValueType::Float64(val) => match val == 0.0 {
3534                false => true,
3535                true => false
3536            },
3537            _ => panic!("invalid conversion")
3538        }
3539    }
3540}
3541
3542impl Into<f32> for ValueType {
3543    fn into(self) -> f32 {
3544        match self {
3545            ValueType::Float32(num) => num,
3546            ValueType::Float64(num) => num as f32,
3547            _ => panic!("invalid conversion")
3548        }
3549    }
3550}
3551
3552impl Into<f64> for ValueType {
3553    fn into(self) -> f64 {
3554        match self {
3555            ValueType::Float32(num) => num as f64,
3556            ValueType::Float64(num) => num,
3557            _ => panic!("invalid conversion")
3558        }
3559    }
3560}
3561
3562impl Into<i8> for ValueType {
3563    fn into(self) -> i8 {
3564        match self {
3565            ValueType::Int8(num) => num,
3566            ValueType::Int16(num) => match num > 128 || num < -128 {
3567                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3568                false => num as i8
3569            },
3570            ValueType::Int32(num) => match num > 128 || num < -128 {
3571                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3572                false => num as i8
3573            },
3574            ValueType::Int64(num) => match num > 128 || num < -128 {
3575                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3576                false => num as i8
3577            },
3578            ValueType::Int128(num) => match num > 128 || num < -128 {
3579                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3580                false => num as i8
3581            }
3582            ValueType::Uint8(num) => match num > 128 {
3583                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3584                false => num as i8
3585            },
3586            ValueType::Uint16(num) => match num > 128 {
3587                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3588                false => num as i8
3589            },
3590            ValueType::Uint32(num) => match num > 128 {
3591                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3592                false => num as i8
3593            },
3594            ValueType::Usize(num) => match num > 128 {
3595                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3596                false => num as i8
3597            },
3598            ValueType::Uint64(num) => match num > 128 {
3599                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3600                false => num as i8
3601            },
3602            _ => panic!("you cannot convert non numeric values into numeric ones.")
3603        }
3604    }
3605}
3606
3607impl Into<i16> for ValueType {
3608    fn into(self) -> i16 {
3609        match self {
3610            ValueType::Int8(num) => num as i16,
3611            ValueType::Int16(num) => num,
3612            ValueType::Int32(num) => match num > 32_768 || num < -32_768 {
3613                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3614                false => num as i16
3615            },
3616            ValueType::Int64(num) => match num > 32_768 || num < -32_768 {
3617                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3618                false => num as i16
3619            },
3620            ValueType::Int128(num) => match num > 32_768 || num < -32_768 {
3621                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3622                false => num as i16
3623            }
3624            ValueType::Uint8(num) => num as i16,
3625            ValueType::Uint16(num) => match num > 32_768 {
3626                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3627                false => num as i16
3628            },
3629            ValueType::Uint32(num) => match num > 32_768 {
3630                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3631                false => num as i16
3632            },
3633            ValueType::Usize(num) => match num > 32_768 {
3634                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3635                false => num as i16
3636            },
3637            ValueType::Uint64(num) => match num > 32_768 {
3638                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3639                false => num as i16
3640            },
3641            _ => panic!("you cannot convert non numeric values into numeric ones.")
3642        }
3643    }
3644}
3645
3646impl Into<i32> for ValueType {
3647    fn into(self) -> i32 {
3648        match self {
3649            ValueType::Int8(num) => num as i32,
3650            ValueType::Int16(num) => num as i32,
3651            ValueType::Int32(num) => num,
3652            ValueType::Int64(num) => match num > 2_147_483_647 || num < -2_147_483_647 {
3653                true => panic!("you cannot convert i32's into a value which is bigger than the capacity of 32 bit values."),
3654                false => num as i32
3655            },
3656            ValueType::Int128(num) => match num > 2_147_483_647 || num < -2_147_483_647 {
3657                true => panic!("you cannot convert i32's into a value which is bigger than the capacity of 32 bit values."),
3658                false => num as i32
3659            }
3660            ValueType::Uint8(num) => num as i32,
3661            ValueType::Uint16(num) => num as i32,
3662            ValueType::Uint32(num) => match num > 2_147_483_647 {
3663                true => panic!("you cannot convert i32's into a value which is bigger than the capacity of 32 bit values."),
3664                false => num as i32
3665            },
3666            ValueType::Usize(num) => match num > 2_147_483_647 {
3667                true => panic!("you cannot convert i32's into a value which is bigger than the capacity of 32 bit values."),
3668                false => num as i32
3669            },
3670            ValueType::Uint64(num) => match num > 2_147_483_647 {
3671                true => panic!("you cannot convert i32's into a value which is bigger than the capacity of 32 bit values."),
3672                false => num as i32
3673            }
3674            _ => panic!("you cannot convert non numeric values into numeric ones.")
3675        }
3676    }
3677}
3678
3679impl Into<i64> for ValueType {
3680    fn into(self) -> i64 {
3681        match self {
3682            ValueType::EpochTime(epoch) => epoch as i64,
3683            ValueType::Int8(num) => num as i64,
3684            ValueType::Int16(num) => num as i64,
3685            ValueType::Int32(num) => num as i64,
3686            ValueType::Int64(num) => num,
3687            ValueType::Usize(num) => num as i64,
3688            ValueType::Uint8(num) => num as i64,
3689            ValueType::Uint16(num) => num as i64,
3690            ValueType::Uint32(num) => num as i64,
3691            ValueType::Uint64(num) => num as i64,
3692            _ => panic!("you cannot convert non numeric values into numeric ones.")
3693        }
3694    }
3695}
3696
3697impl Into<u8> for ValueType {
3698    fn into(self) -> u8 {
3699        match self {
3700            ValueType::Uint8(num) => num,
3701            ValueType::Uint16(num) => match num > 255 {
3702                true => panic!("you cannot convert u16's if it's value is bigger than capacity of 8 bit values"),
3703                false => num as u8
3704            },
3705            ValueType::Uint32(num) => match num > 255 {
3706                true => panic!("you cannot convert u32's if it's value is bigger than capacity of 8 bit values"),
3707                false => num as u8
3708            },
3709            ValueType::Uint64(num) => match num > 255 {
3710                true => panic!("you cannot convert u64's if it's value is bigger than capacity of 8 bit values"),
3711                false => num as u8
3712            },
3713            ValueType::Usize(num) => match num > 255 {
3714                true => panic!("you cannot convert usizes if it's value is bigger than capacity of 8 bit values"),
3715                false => num as u8
3716            },
3717            ValueType::Int8(num) => match num < 0 {
3718                true => panic!("you cannot convert i8's if it's value is lower than 0"),
3719                false => num as u8
3720            },
3721            ValueType::Int16(num) => match num < 0 || num > 255 {
3722                true => panic!("you cannot convert i16's if it's value is lower than 0 or has a value which is bigger than capacity of 8 bit values."),
3723                false => num as u8
3724            },
3725            ValueType::Int32(num) => match num < 0 || num > 255 {
3726                true => panic!("you cannot convert i32's if it's value is lower than 0 or has a value which is bigger than capacity of 8 bit values."),
3727                false => num as u8
3728            },
3729            ValueType::Int64(num) => match num < 0 || num > 255 {
3730                true => panic!("you cannot convert 64's if it's value is lower than 0 or has a value which is bigger than capacity of 8 bit values."),
3731                false => num as u8
3732            },
3733            ValueType::Int128(num) => match num < 0 || num > 255 {
3734                true => panic!("you cannot convert i128's if it's value is lower than 0 or has a value which is bigger than capacity of 8 bit values."),
3735                false => num as u8
3736            }
3737            _ => panic!("you cannot convert non numeric values into numeric ones.")
3738        }
3739    }
3740}
3741
3742impl Into<u16> for ValueType {
3743    fn into(self) -> u16 {
3744        match self {
3745            ValueType::Uint8(num) => num as u16,
3746            ValueType::Uint16(num) => num,
3747            ValueType::Uint32(num) => match num > 65_535 {
3748                true => panic!("you cannot convert u32's if it's value is bigger than capacity of 32 bit values"),
3749                false => num as u16
3750            },
3751            ValueType::Uint64(num) => match num > 65_535 {
3752                true => panic!("you cannot convert u64's if it's value is bigger than capacity of 32 bit values"),
3753                false => num as u16
3754            },
3755            ValueType::Usize(num) => match num > 65_535 {
3756                true => panic!("you cannot convert usizes if it's value is bigger than capacity of 32 bit values"),
3757                false => num as u16
3758            },
3759            ValueType::Int8(num) => match num < 0 {
3760                true => panic!("you cannot convert i8's if it's value is lower than 0"),
3761                false => num as u16
3762            },
3763            ValueType::Int16(num) => match num < 0 {
3764                true => panic!("you cannot convert i16's if it's value is lower than 0"),
3765                false => num as u16
3766            },
3767            ValueType::Int32(num) => match num < 0 || num > 65_535 {
3768                true => panic!("you cannot convert i32's if it's value is lower than 0 or has a value which is bigger than capacity of 16 bit values."),
3769                false => num as u16
3770            },
3771            ValueType::Int64(num) => match num < 0 || num > 65_535 {
3772                true => panic!("you cannot convert i64's if it's value is lower than 0 or has a value which is bigger than capacity of 16 bit values."),
3773                false => num as u16
3774            },
3775            ValueType::Int128(num) => match num < 0 || num > 65_535 {
3776                true => panic!("you cannot convert i128's if it's value is lower than 0 or has a value which is bigger than capacity of 16 bit values."),
3777                false => num as u16
3778            }
3779            _ => panic!("you cannot convert non numeric values into numeric ones.")
3780        }
3781    }
3782}
3783
3784impl Into<u32> for ValueType {
3785    fn into(self) -> u32 {
3786        match self {
3787            ValueType::Uint8(num) => num as u32,
3788            ValueType::Uint16(num) => num as u32,
3789            ValueType::Uint32(num) => num,
3790            ValueType::Uint64(num) => match num > 4_294_967_295 {
3791                true => panic!("you cannot convert u64's if it's value is bigger than capacity of 32 bit values"),
3792                false => num as u32
3793            },
3794            ValueType::Usize(num) => match num > 4_294_967_295 {
3795                true => panic!("you cannot convert usizes if it's value is bigger than capacity of 32 bit values"),
3796                false => num as u32
3797            },
3798            ValueType::Int8(num) => match num < 0 {
3799                true => panic!("you cannot convert i8's if it's value is lower than 0"),
3800                false => num as u32
3801            },
3802            ValueType::Int16(num) => match num < 0 {
3803                true => panic!("you cannot convert i16's if it's value is lower than 0"),
3804                false => num as u32
3805            },
3806            ValueType::Int32(num) => match num < 0 {
3807                true => panic!("you cannot convert i32's if it's value is lower than 0"),
3808                false => num as u32
3809            },
3810            ValueType::Int64(num) => match num < 0 || num > 4_294_967_295 {
3811                true => panic!("you cannot convert i64's if it's value is lower than 0 or has a value which is bigger than capacity of 32 bit values."),
3812                false => num as u32
3813            },
3814            ValueType::Int128(num) => match num < 0 || num > 4_294_967_295 {
3815                true => panic!("you cannot convert i128's if it's value is lower than 0 or has a value which is bigger than capacity of 32 bit values."),
3816                false => num as u32
3817            }
3818            _ => panic!("you cannot convert non numeric values into numeric ones.")
3819        }
3820    }
3821}
3822
3823impl Into<u64> for ValueType {
3824    fn into(self) -> u64 {
3825        match self {
3826            ValueType::Usize(num) => num as u64,
3827            ValueType::Uint8(num) => num as u64,
3828            ValueType::Uint16(num) => num as u64,
3829            ValueType::Uint32(num) => num as u64,
3830            ValueType::Uint64(num) => num,
3831            ValueType::Int8(num) => match num < 0 {
3832                true => panic!("you cannot turn a negative value into u64"),
3833                false => num as u64
3834            },
3835            ValueType::Int16(num) => match num < 0 {
3836                true => panic!("you cannot turn a negative value into u64"),
3837                false => num as u64
3838            },
3839            ValueType::Int32(num) => match num < 0 {
3840                true => panic!("you cannot turn a negative value into u64"),
3841                false => num as u64
3842            },
3843            ValueType::Int64(num) => match num < 0 {
3844                true => panic!("you cannot turn a negative value into u64"),
3845                false => num as u64
3846            },
3847            ValueType::Int128(num) => match num < 0 {
3848                true => panic!("you cannot turn a negative value into u64"),
3849                false => num as u64
3850            },
3851            _ => panic!("you cannot convert non numeric values into numeric ones.")
3852        }
3853    }
3854}
3855
3856impl Into<usize> for ValueType {
3857    fn into(self) -> usize {
3858        match self {
3859            ValueType::Int8(num) => match num < 0 {
3860                true => panic!("you cannot convert negative numbers to usize"),
3861                false => num as usize
3862            },
3863            ValueType::Int16(num) => match num < 0 {
3864                true => panic!("you cannot convert negative numbers to usize"),
3865                false => num as usize
3866            },
3867            ValueType::Int32(num) => match num < 0 {
3868                true => panic!("you cannot convert negative numbers to usize"),
3869                false => num as usize
3870            },
3871            ValueType::Int64(num) => match num < 0 {
3872                true => panic!("you cannot convert negative numbers to usize"),
3873                false => num as usize
3874            },
3875            ValueType::Int128(num) => match num < 0 {
3876                true => panic!("you cannot convert negative numbers to usize"),
3877                false => num as usize
3878            },
3879            ValueType::Usize(num) => num,
3880            ValueType::Uint8(num) => num as usize,
3881            ValueType::Uint16(num) => num as usize,
3882            ValueType::Uint32(num) => num as usize,
3883            ValueType::Uint64(num) => num as usize,
3884            _ => panic!("you cannot convert non numeric values into numeric ones.")
3885        }
3886    }
3887}
3888
3889/// Enum that benefits you to add json values to structs. They can be used with json functions.
3890/// That variants represents that kind of json values:
3891#[derive(Debug, Clone)]
3892pub enum JsonValue<'a> {
3893    /// 
3894    /// Example Value: ["hello", 21, "again"]
3895    /// 
3896    Array(&'a Vec<ValueType>), 
3897    
3898    /// 
3899    /// Example Value: {"name": "necdet", "message": "hello", "id": 1}
3900    /// 
3901    Object(&'a Vec<(&'a str, &'a ValueType)>), 
3902    
3903    ///
3904    /// example value: [{"name": "necdet", "message": "hello", "id": 1}, {"name": "kemal", "message": "hi", "id": 2}]
3905    /// 
3906    ObjectArray(&'a Vec<Vec<(&'a str, &'a ValueType)>>), 
3907    
3908    /// It's same with `ValueType` enums, just for simply passing it to that enum.
3909    Initial(&'a ValueType), 
3910
3911    /// Mysql Json Object: It writes JSON_OBJECT() mysql function with it's synthax, such as: JSON_OBJECT('name', 'necdet', 'message', 'hello', 'id', 13). It's necessary or more accurate when working most of the json functions.
3912    MysqlJsonObject(&'a Vec<(&'a str, &'a ValueType)>)
3913}
3914
3915impl <'a>std::fmt::Display for JsonValue<'a> {
3916    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
3917        match self {
3918            JsonValue::Array(values) => {
3919                let mut json_str = "[".to_string();
3920
3921                for (index, value) in values.iter().enumerate() {
3922                    if index == 0 {
3923                        json_str = format!("{}{}", json_str, value)
3924                    } else {
3925                        json_str = format!("{}, {}", json_str, value)
3926                    }
3927                }
3928
3929                json_str = format!("{}]", json_str);
3930
3931                write!(f, "{}", json_str)
3932            },
3933            JsonValue::Object(props) => {
3934                let mut json_str = "{".to_string();
3935
3936                for (index, value) in props.iter().enumerate() {
3937                    if index == 0 {
3938                        json_str = format!("{}\"{}\": {}", json_str, value.0, value.1)
3939                    } else {
3940                        json_str = format!("{}, \"{}\": {}", json_str, value.0, value.1)
3941                    }
3942                }
3943
3944                json_str = format!("{}}}", json_str);
3945
3946                write!(f, "{}", json_str)
3947            },
3948            JsonValue::MysqlJsonObject(props) => {
3949                let mut json_str = "JSON_OBJECT(".to_string();
3950
3951                for (index, value) in props.iter().enumerate() {
3952                    if index == 0 {
3953                        json_str = format!("{}'{}', {}", json_str, value.0, value.1)
3954                    } else {
3955                        json_str = format!("{}, '{}', {}", json_str, value.0, value.1)
3956                    }
3957                }
3958
3959                json_str = format!("{})", json_str);
3960
3961                write!(f, "{}", json_str)
3962            },
3963            JsonValue::ObjectArray(array) => {
3964                let mut json_str = "[".to_string();
3965
3966                for (index1, object) in array.into_iter().enumerate() {
3967                    let mut object_str = "{".to_string();
3968
3969                    for (index2, property) in object.into_iter().enumerate() {
3970                        if index2 == 0 {
3971                            object_str = format!("{}\"{}\": {}", object_str, property.0, property.1)
3972                        } else {
3973                            object_str = format!("{}, \"{}\": {}", object_str, property.0, property.1)
3974                        }
3975                    }
3976
3977                    object_str = format!("{}}}", object_str);
3978
3979                    if index1 == 0 {
3980                        json_str = format!("{}{}", json_str, object_str)
3981                    } else {
3982                        json_str = format!("{}, {}", json_str, object_str)
3983                    }
3984                }
3985
3986                write!(f, "{}]", json_str)
3987            },
3988            JsonValue::Initial(value) => write!(f, "{}", value.to_string())
3989        }
3990    }
3991}
3992
3993/// Timezones with Unix Timezone format, Can be used for setting timezone manually.
3994/// It covers european, russian, north american, south american, arabic countries. In next releases, we'll cover other african and asian timezones. 
3995#[derive(Debug, Clone)]
3996pub enum Timezone {
3997    System, Istanbul, Moscow, Kaliningrad, Samara, Ekaterinburg, Omsk, Krasnoyarsk, Irkutsk, Yakutsk,
3998    Vladivostok, Magadan, Kamchatka, Shanghai, London, Paris, Berlin, Madrid, Rome, Amsterdam, Stockholm, Oslo,
3999    Helsinki, Athens, NewYork, Chicago, Denver, LosAngeles, Anchorage, Honolulu, PuertoRico, Riyadh, Dubai, Qatar,
4000    Kuwait, Bahrain, Muscat, Aden, Baghdad, Amman, Beirut, Damascus, Gaza, Hebron, Cairo, Khartoum, Tripoli,
4001    Tunis, BuenosAires, LaPaz, SaoPaulo, Manaus, Recife, Cuiaba, PortoVelho, Santiago, Easter, Bogota, Guayaquil,
4002    Galapagos, Guyana, Asuncion, Lima, Paramaribo, Montevideo, Caracas, StJohns, Halifax, Toronto, Winnipeg,
4003    Edmonton, Vancouver, WhiteHorse, MexicoCity, Mazatlan, Chihuahua, Tijuana, Cancun, Belize, CostaRica, ElSalvador,
4004    Guatemala, Tegucigalpa, Managua, Panama, Apia, Auckland, Bougainville, Chatham, Efate, Enderbury, Fakaofo,
4005    Fiji, Funafuti, Gambier, Guadalcanal, Guam, Johnston, Kanton, Kiritimati, Kosrae, Kwajalein, Majuro, Marquesas,
4006    Midway, Nauru, Niue, Norfolk, Noumea, PagoPago, Palau, Pitcairn, Pohnpei, PortMoresby, Saipan, Rarotonga, Tahiti,
4007    Tarawa, Truk, Wake, Wallis, Yap, Tongatapu
4008}
4009
4010impl std::fmt::Display for Timezone {
4011    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
4012        match self {
4013            Timezone::System => write!(f, "SYSTEM"), Timezone::Istanbul => write!(f, "Europe/Istanbul"), Timezone::Moscow => write!(f, "Europe/Moscow"),
4014            Timezone::Kaliningrad => write!(f, "Europe/Kaliningrad"), Timezone::Samara => write!(f, "Europe/Samara"), Timezone::Ekaterinburg => write!(f, "Asia/Yekaterinburg"),
4015            Timezone::Omsk => write!(f, "Asia/Omsk"), Timezone::Krasnoyarsk => write!(f, "Asia/Krasnoyarsk"), Timezone::Irkutsk => write!(f, "Asia/Irkutsk"),
4016            Timezone::Yakutsk => write!(f, "Asia/Yakutsk"), Timezone::Vladivostok => write!(f, "Asia/Vladivostok"), Timezone::Magadan => write!(f, "Asia/Magadan"),
4017            Timezone::Kamchatka => write!(f, "Asia/Kamchatka"), Timezone::Shanghai => write!(f, "Asia/Shanghai"), Timezone::London => write!(f, "Europe/London"),
4018            Timezone::Paris => write!(f, "Europe/Paris"), Timezone::Berlin => write!(f, "Europe/Berlin"), Timezone::Madrid => write!(f, "Europe/Madrid"),
4019            Timezone::Rome => write!(f, "Europe/Rome"), Timezone::Amsterdam => write!(f, "Europe/Amsterdam"), Timezone::Stockholm => write!(f, "Europe/Stockholm"),
4020            Timezone::Oslo => write!(f, "Europe/Oslo"), Timezone::Helsinki => write!(f, "Europe/Helsinki"), Timezone::Athens => write!(f, "Europe/Athens"),
4021            Timezone::NewYork => write!(f, "America/New_York"), Timezone::Chicago => write!(f, "America/Chicago"), Timezone::Denver => write!(f, "America/Denver"),
4022            Timezone::LosAngeles => write!(f, "America/Los_Angeles"), Timezone::Anchorage => write!(f, "America/Anchorage"), Timezone::Honolulu => write!(f, "Pacific/Honolulu"),
4023            Timezone::PuertoRico => write!(f, "America/Puerto_Rico"), Timezone::Riyadh => write!(f, "Asia/Riyadh"), Timezone::Dubai => write!(f, "Asia/Dubai"),
4024            Timezone::Qatar => write!(f, "Asia/Qatar"), Timezone::Kuwait => write!(f, "Asia/Kuwait"), Timezone::Bahrain => write!(f, "Asia/Bahrain"), Timezone::Muscat => write!(f, "Asia/Muscat"),
4025            Timezone::Aden => write!(f, "Asia/Aden"), Timezone::Baghdad => write!(f, "Asia/Baghdad"), Timezone::Amman => write!(f, "Asia/Amman"), Timezone::Beirut => write!(f, "Asia/Beirut"),
4026            Timezone::Damascus => write!(f, "Asia/Damascus"), Timezone::Gaza => write!(f, "Asia/Gaza"), Timezone::Hebron => write!(f, "Asia/Hebron"), Timezone::Cairo => write!(f, "Africa/Cairo"),
4027            Timezone::Khartoum => write!(f, "Africa/Khartoum"), Timezone::Tripoli => write!(f, "Africa/Tripoli"), Timezone::Tunis => write!(f, "Africa/Tunis"),
4028            Timezone::BuenosAires => write!(f, "America/Argentina/Buenos_Aires"), Timezone::LaPaz => write!(f, "America/La_Paz"), Timezone::SaoPaulo => write!(f, "America/Sao_Paulo"),
4029            Timezone::Manaus => write!(f, "America/Manaus"), Timezone::Recife => write!(f, "America/Recife"), Timezone::Cuiaba => write!(f, "America/Cuiaba"), Timezone::PortoVelho => write!(f, "America/Porto_Velho"),
4030            Timezone::Santiago => write!(f, "America/Santiago"), Timezone::Easter => write!(f, "Pacific/Easter"), Timezone::Bogota => write!(f, "America/Bogota"), Timezone::Guayaquil => write!(f, "America/Guayaquil"),
4031            Timezone::Galapagos => write!(f, "Pacific/Galapagos"), Timezone::Guyana => write!(f, "America/Guyana"), Timezone::Asuncion => write!(f, "America/Asuncion"), Timezone::Lima => write!(f, "America/Lima"),
4032            Timezone::Paramaribo => write!(f, "America/Paramaribo"), Timezone::Montevideo => write!(f, "America/Montevideo"), Timezone::Caracas => write!(f, "America/Caracas"),
4033            Timezone::StJohns => write!(f, "America/St_Johns"), Timezone::Halifax => write!(f, "America/Halifax"), Timezone::Toronto => write!(f, "America/Toronto"), Timezone::Winnipeg => write!(f, "America/Winnipeg"),
4034            Timezone::Edmonton => write!(f, "America/Edmonton"), Timezone::Vancouver => write!(f, "America/Vancouver"), Timezone::WhiteHorse => write!(f, "America/Whitehorse"),
4035            Timezone::MexicoCity => write!(f, "America/Mexico_City"), Timezone::Mazatlan => write!(f, "America/Mazatlan"), Timezone::Chihuahua => write!(f, "America/Chihuahua"),
4036            Timezone::Tijuana => write!(f, "America/Tijuana"), Timezone::Cancun => write!(f, "America/Cancun"), Timezone::Belize => write!(f, "America/Belize"), Timezone::CostaRica => write!(f, "America/Costa_Rica"),
4037            Timezone::ElSalvador => write!(f, "America/El_Salvador"), Timezone::Guatemala => write!(f, "America/Guatemala"), Timezone::Tegucigalpa => write!(f, "America/Tegucigalpa"),
4038            Timezone::Managua => write!(f, "America/Managua"), Timezone::Panama => write!(f, "America/Panama"), Timezone::Apia => write!(f, "Pacific/Apia"),
4039            Timezone::Auckland => write!(f, "Pacific/Auckland"), Timezone::Bougainville => write!(f, "Pacific/Bougainville"), Timezone::Chatham => write!(f, "Pacific/Chatham"),
4040            Timezone::Efate => write!(f, "Pacific/Efate"), Timezone::Enderbury => write!(f, "Pacific/Enderbury"), Timezone::Tongatapu => write!(f, "Pacific/Tongatapu"),
4041            Timezone::Fakaofo => write!(f, "Pacific/Fakaofo"), Timezone::Fiji => write!(f, "Pacific/Fiji"), Timezone::Funafuti => write!(f, "Pacific/Funafuti"),
4042            Timezone::Gambier => write!(f, "Pacific/Gambier"), Timezone::Guadalcanal => write!(f, "Pacific/Guadalcanal"), Timezone::Guam => write!(f, "Pacific/Guam"),
4043            Timezone::Johnston => write!(f, "Pacific/Johnston"), Timezone::Kanton => write!(f, "Pacific/Kanton"), Timezone::Kiritimati => write!(f, "Pacific/Kiritimati"),
4044            Timezone::Kosrae => write!(f, "Pacific/Kosrae"), Timezone::Majuro => write!(f, "Pacific/Majuro"), Timezone::Kwajalein => write!(f, "Pacific/Kwajalein"),
4045            Timezone::Midway => write!(f, "Pacific/Midway"), Timezone::Nauru => write!(f, "Pacific/Nauru"), Timezone::Niue => write!(f, "Pacific/Niue"),
4046            Timezone::Marquesas => write!(f, "Pacific/Marquesas"), Timezone::Norfolk => write!(f, "Pacific/Norfolk"), Timezone::PagoPago => write!(f, "Pacific/Pago_Pago"),
4047            Timezone::Noumea => write!(f, "Pacific/Noumea"), Timezone::Palau => write!(f, "Pacific/Palau"), Timezone::Pitcairn => write!(f, "Pacific/Pitcairn"),
4048            Timezone::Pohnpei => write!(f, "Pacific/Pohnpei"), Timezone::PortMoresby => write!(f, "Pacific/Port_Moresby"), Timezone::Rarotonga => write!(f, "Pacific/Rarotonga"),
4049            Timezone::Tahiti => write!(f, "Pacific/Tahiti"), Timezone::Tarawa => write!(f, "Pacific/Tarawa"), Timezone::Saipan => write!(f, "Pacific/Saipan"),
4050            Timezone::Truk => write!(f, "Pacific/Truk"), Timezone::Wake => write!(f, "Pacific/Wake"), Timezone::Wallis => write!(f, "Pacific/Wallis"),
4051            Timezone::Yap => write!(f, "Pacific/Yap")
4052        }
4053    }
4054}
4055
4056/// Enum that benefits you to define what you want with a foreign key.
4057#[derive(Debug, Clone)]
4058pub enum ForeignKeyActions {
4059    Cascade, Restrict, SetNull, NoAction, SetDefault
4060}
4061
4062impl std::fmt::Display for ForeignKeyActions {
4063    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
4064        match self {
4065            &ForeignKeyActions::Cascade => write!(f, "CASCADE"),
4066            &ForeignKeyActions::NoAction => write!(f, "NO ACTION"),
4067            &ForeignKeyActions::Restrict => write!(f, "RESTRICT"),
4068            &ForeignKeyActions::SetNull => write!(f, "SET NULL"),
4069            &ForeignKeyActions::SetDefault => write!(f, "SET DEFAULT")
4070        }
4071    }
4072}
4073
4074#[cfg(test)]
4075mod test {
4076    use super::*;
4077
4078    #[test]
4079    pub fn test_schema_query_declarative(){
4080        let schema = SchemaBuilder::create("blog_website").unwrap().if_not_exists().finish();
4081
4082        assert_eq!("CREATE DATABASE IF NOT EXISTS blog_website;", schema);
4083    }
4084
4085    #[test]
4086    pub fn test_schema_query_imperative(){
4087        let mut schema = SchemaBuilder::create("blog_website").unwrap();
4088        schema.if_not_exists();
4089        let schema_query = schema.finish();
4090
4091        assert_eq!("CREATE DATABASE IF NOT EXISTS blog_website;", schema_query);
4092    }
4093
4094    #[test]
4095    pub fn test_use_another_schema(){
4096        let schema = SchemaBuilder::use_another_schema("chat_website").unwrap().finish();
4097
4098        assert_eq!("USE chat_website;", schema);
4099    }
4100
4101    #[test]
4102    pub fn test_insert_query(){
4103        let columns = vec!["title", "author", "description"];
4104        let values = vec![ValueType::String("What's Up?".to_string()), ValueType::String("John Doe".to_string()), ValueType::String("Lorem ipsum dolor sit amet, consectetur adipiscing elit.".to_string())];
4105    
4106        let insert_query = QueryBuilder::insert(columns, values).unwrap().table("blogs").finish();
4107
4108        println!("{}", insert_query);
4109        assert_eq!("INSERT INTO blogs (title, author, description) VALUES ('What's Up?', 'John Doe', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.');".to_string(), 
4110                    insert_query);
4111    }
4112
4113    #[test]
4114    pub fn test_update_query(){
4115        let update_query = QueryBuilder::update().unwrap().table("blogs").set("title", ValueType::String("Hello Rust!".to_string())).set("author", ValueType::String("Necdet".to_string())).finish();
4116
4117        assert_eq!("UPDATE blogs SET title = 'Hello Rust!', author = 'Necdet';", update_query);
4118    }
4119
4120    #[test]
4121    pub fn test_delete_query(){
4122        let delete_query = QueryBuilder::delete().unwrap().table("blogs").where_("id", "=", ValueType::String("1".to_string())).finish();
4123
4124        assert_eq!("DELETE FROM blogs WHERE id = '1';", delete_query);
4125    }
4126
4127    #[test]
4128    pub fn test_select_query_declarative(){
4129        let mut select = QueryBuilder::select(["id", "title", "description", "point"].to_vec()).unwrap();
4130
4131        let select_query = select.table("blogs")
4132                                    .where_("id", "=", ValueType::Int32(10))
4133                                    .and("point", ">", ValueType::Int8(90))
4134                                    .or("id", "=", ValueType::Int64(20))
4135                                    .finish();
4136
4137        assert_eq!("SELECT id, title, description, point FROM blogs WHERE id = 10 AND point > 90 OR id = 20;", select_query)
4138    }
4139
4140    #[test]
4141    pub fn test_select_query_imperative(){
4142        let mut select = QueryBuilder::select(["*"].to_vec()).unwrap();
4143
4144        let select_query = select.table("blogs");
4145        select_query.where_("id", "=", ValueType::Uint8(5));
4146        select_query.or("id", "=", ValueType::Usize(25));
4147
4148        let finish_the_select_query = select_query.finish();
4149
4150        assert_eq!("SELECT * FROM blogs WHERE id = 5 OR id = 25;", finish_the_select_query);
4151    }
4152
4153    #[test]
4154    pub fn test_create_table() {
4155        let mut table_builder_2 = TableBuilder::create("blabla", "projects");
4156        let table_builder_2 = table_builder_2.if_not_exists();
4157    
4158        table_builder_2.add_column("id").col_type("INT").primary_key().auto_increment();
4159        table_builder_2.add_column("name").col_type("VARCHAR(40)").not_null();
4160        table_builder_2.add_column("owner_id").col_type("INT").not_null();
4161        
4162        // if we create a table, the first ForeignKeyItem's table field is not necessary.
4163        let opts = ForeignKey {
4164            first: ForeignKeyItem { table: "".to_string(), column: "owner_id".to_string() },
4165            second: ForeignKeyItem { table: "users".to_string(), column: "id".to_string() },
4166            constraint: None,
4167            on_delete: Some(ForeignKeyActions::Cascade),
4168            on_update: None
4169        };
4170        
4171        table_builder_2.foreign_key(opts);
4172    
4173        let table_builder_2 = table_builder_2.finish();
4174
4175        let raw_query = "CREATE TABLE projects IF NOT EXISTS (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(40) NOT NULL, owner_id INT NOT NULL, FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE);".to_string();
4176
4177        assert_eq!(raw_query, table_builder_2);
4178    }
4179
4180    #[test]
4181    pub fn test_time_value_type(){
4182        let columns = ["name", "password", "last_login"].to_vec();
4183        let values = [ValueType::String("necoo33".to_string()), ValueType::String("123456".to_string()), ValueType::Datetime("CURRENT_TIMESTAMP".to_string())].to_vec();
4184    
4185        let time_insert_test = QueryBuilder::insert(columns, values).unwrap().table("users").finish();
4186
4187        assert_eq!(time_insert_test, "INSERT INTO users (name, password, last_login) VALUES ('necoo33', '123456', CURRENT_TIMESTAMP);");
4188
4189        let time_update_test = QueryBuilder::update().unwrap().table("users").set("last_login", ValueType::Datetime("CURRENT_TIMESTAMP".to_string())).where_("name", "=", ValueType::String("necoo33".to_string())).finish();
4190
4191        assert_eq!(time_update_test, "UPDATE users SET last_login = CURRENT_TIMESTAMP WHERE name = 'necoo33';")
4192    }
4193
4194    #[test]
4195    pub fn test_unix_epoch_times(){
4196        let columns = ["name", "password", "last_login"].to_vec();
4197        let values = [ValueType::String("necoo33".to_string()), ValueType::String("123456".to_string()), ValueType::EpochTime(134523452)].to_vec();
4198    
4199        let time_insert_with_unix_epoch_times_test = QueryBuilder::insert(columns, values).unwrap().table("users").finish();
4200        assert_eq!(time_insert_with_unix_epoch_times_test, "INSERT INTO users (name, password, last_login) VALUES ('necoo33', '123456', FROM_UNIXTIME(134523452));");
4201    
4202        let time_update_with_unix_epoch_times_test = QueryBuilder::update().unwrap().table("users").set("last_login", ValueType::EpochTime(3456436)).where_("name", "=", ValueType::String("necoo33".to_string())).finish();
4203
4204        assert_eq!(time_update_with_unix_epoch_times_test, "UPDATE users SET last_login = FROM_UNIXTIME(3456436) WHERE name = 'necoo33';");
4205
4206        let columns = ["name", "password", "last_login", "created_at"].to_vec();
4207
4208        let unix_epoch_times_test_3 = QueryBuilder::select(columns).unwrap().table("users").where_("created_at", ">", ValueType::EpochTime(3234534)).or("last_login", ">=", ValueType::EpochTime(2134432)).offset(0).limit(20).finish();
4209
4210        assert_eq!(unix_epoch_times_test_3, "SELECT name, password, last_login, created_at FROM users WHERE created_at > FROM_UNIXTIME(3234534) OR last_login >= FROM_UNIXTIME(2134432) OFFSET 0 LIMIT 20;")
4211    }
4212
4213    #[test]
4214    pub fn test_where_ins(){
4215        let columns = ["name", "age", "id", "last_login"].to_vec();
4216
4217        let ids = [ValueType::Int32(1), ValueType::Int16(12), ValueType::Int64(8)].to_vec();
4218
4219        let test_where_in = QueryBuilder::select(columns).unwrap().table("users").where_in("id", &ids).finish();
4220
4221        assert_eq!(test_where_in, "SELECT name, age, id, last_login FROM users WHERE id IN (1, 12, 8);");
4222
4223        let columns = ["name", "age", "id", "last_login"].to_vec();
4224
4225        let ids = [ValueType::Int32(1), ValueType::Int16(12), ValueType::Int32(8)].to_vec();
4226
4227        let test_where_not_in = QueryBuilder::select(columns).unwrap().table("users").where_not_in("id", &ids).finish();
4228
4229        assert_eq!(test_where_not_in, "SELECT name, age, id, last_login FROM users WHERE id NOT IN (1, 12, 8);");
4230
4231        let columns = ["name", "age", "id", "last_login"].to_vec();
4232
4233        let test_where_in_custom = QueryBuilder::select(columns).unwrap().table("users").where_in_custom("id", "1, 12, 8").finish();
4234
4235        assert_eq!(test_where_in_custom, "SELECT name, age, id, last_login FROM users WHERE id IN (1, 12, 8);");
4236
4237        let columns = ["name", "age", "id", "last_login"].to_vec();
4238
4239        let test_where_not_in_custom = QueryBuilder::select(columns).unwrap().table("users").where_not_in_custom("id", "1, 12, 8").finish();
4240
4241        assert_eq!(test_where_not_in_custom, "SELECT name, age, id, last_login FROM users WHERE id NOT IN (1, 12, 8);");
4242
4243        // test AND IN's
4244
4245        let columns = ["name", "id", "last_login"].to_vec();
4246
4247        let ids = [ValueType::Int32(1), ValueType::Int16(12), ValueType::Int64(8)].to_vec();
4248
4249        let test_and_in = QueryBuilder::select(columns).unwrap().table("users").where_("age", ">", ValueType::Int32(35)).and_in("id", &ids).finish();
4250
4251        assert_eq!(test_and_in, "SELECT name, id, last_login FROM users WHERE age > 35 AND id IN (1, 12, 8);");
4252
4253        let columns = ["name", "id", "last_login"].to_vec();
4254
4255        let ids = [ValueType::Int32(1), ValueType::Int16(12), ValueType::Int64(8)].to_vec();
4256
4257        let test_and_not_in = QueryBuilder::select(columns).unwrap().table("users").where_("age", ">", ValueType::Int32(35)).and_not_in("id", &ids).finish();
4258
4259        assert_eq!(test_and_not_in, "SELECT name, id, last_login FROM users WHERE age > 35 AND id NOT IN (1, 12, 8);");
4260
4261        // test OR IN's
4262
4263        let columns = ["name", "id", "last_login"].to_vec();
4264
4265        let ids = [ValueType::Int32(1), ValueType::Int16(12), ValueType::Int64(8)].to_vec();
4266
4267        let test_or_in = QueryBuilder::select(columns).unwrap().table("users").where_("age", ">", ValueType::Int32(35)).or_in("id", &ids).finish();
4268
4269        assert_eq!(test_or_in, "SELECT name, id, last_login FROM users WHERE age > 35 OR id IN (1, 12, 8);");
4270
4271        let columns = ["name", "id", "last_login"].to_vec();
4272
4273        let ids = [ValueType::Int32(1), ValueType::Int16(12), ValueType::Int64(8)].to_vec();
4274
4275        let test_or_not_in = QueryBuilder::select(columns).unwrap().table("users").where_("age", ">", ValueType::Int32(35)).or_not_in("id", &ids).finish();
4276
4277        assert_eq!(test_or_not_in, "SELECT name, id, last_login FROM users WHERE age > 35 OR id NOT IN (1, 12, 8);")
4278    }
4279
4280    #[test]
4281    pub fn test_count() {
4282        let count_of_users = QueryBuilder::count("*", None).table("users").where_("age", ">", ValueType::Int32(25)).finish();
4283
4284        assert_eq!(count_of_users, "SELECT COUNT(*) FROM users WHERE age > 25;".to_string());
4285
4286        let count_of_users_as_length = QueryBuilder::count("*", Some("length")).table("users").finish();
4287
4288        assert_eq!(count_of_users_as_length, "SELECT COUNT(*) AS length FROM users;".to_string());
4289    }
4290
4291    #[test]
4292    pub fn test_json_extract(){
4293        // tests with "select()" constructor
4294
4295        let select_query_1 = QueryBuilder::select(["*"].to_vec()).unwrap().json_extract("data", ".age", Some("student_age")).table("students").finish();
4296
4297        assert_eq!(select_query_1, "SELECT JSON_EXTRACT(data, '$.age') AS student_age FROM students;".to_string());
4298        
4299        let select_query_2 = QueryBuilder::select(["*"].to_vec()).unwrap().json_extract("data", ".age", Some("student_age")).table("students").where_("successfull", "=", ValueType::Int8(1)).finish();
4300
4301        assert_eq!(select_query_2, "SELECT JSON_EXTRACT(data, '$.age') AS student_age FROM students WHERE successfull = 1;".to_string());
4302        
4303        let select_query_3 = QueryBuilder::select(["*"].to_vec()).unwrap().json_extract("data", ".age", Some("student_age")).table("students").where_("points", ">", ValueType::Int32(85)).json_extract("points", ".name", None).finish();
4304
4305        assert_eq!(select_query_3, "SELECT JSON_EXTRACT(data, '$.age') AS student_age FROM students WHERE JSON_EXTRACT(points, '$.name') > 85;".to_string());
4306
4307        // tests with ".where_cond()" method
4308
4309        let with_where = QueryBuilder::delete().unwrap().table("users").where_("id", ">", ValueType::Int32(200)).json_extract("id", ".user_id", None).finish();
4310
4311        assert_eq!(with_where, "DELETE FROM users WHERE JSON_EXTRACT(id, '$.user_id') > 200;".to_string());
4312
4313        // tests with ".table()" method
4314        
4315        let fields = ["name", "age"].to_vec();
4316        
4317        let with_table = QueryBuilder::select(fields).unwrap().table("users").json_extract("id", ".user_id", None).finish();
4318
4319        assert_eq!(with_table, "SELECT JSON_EXTRACT(id, '$.user_id') FROM users;".to_string());
4320
4321        // tests with ".and()" method
4322
4323        let fields = ["name", "age"].to_vec();
4324
4325        let with_and_1 = QueryBuilder::select(fields).unwrap().table("height").where_("weight", ">", ValueType::Int32(60)).and("height", ">", ValueType::Float64(1.70)).json_extract("height", ".student_height", None).finish();
4326
4327        assert_eq!(with_and_1, "SELECT name, age FROM height WHERE weight > 60 AND JSON_EXTRACT(height, '$.student_height') > 1.7;".to_string());
4328    
4329        let with_and_2 = QueryBuilder::select(["*"].to_vec()).unwrap().table("students").where_("weight", ">", ValueType::Int32(60)).and("height", ">", ValueType::Float64(1.70)).json_extract("height", ".student_height", None).finish();
4330
4331        assert_eq!(with_and_2, "SELECT * FROM students WHERE weight > 60 AND JSON_EXTRACT(height, '$.student_height') > 1.7;".to_string());
4332
4333        // tests with ".or()" method
4334
4335        let fields = ["name", "age"].to_vec();
4336
4337        let with_or_1 = QueryBuilder::select(fields).unwrap().table("height").where_("weight", ">", ValueType::Int32(60)).or("height", ">", ValueType::Float64(1.71)).json_extract("height", ".student_height", None).finish();
4338
4339        assert_eq!(with_or_1, "SELECT name, age FROM height WHERE weight > 60 OR JSON_EXTRACT(height, '$.student_height') > 1.71;".to_string());
4340    
4341        let with_or_2 = QueryBuilder::select(["*"].to_vec()).unwrap().table("students").where_("weight", ">", ValueType::Int32(60)).or("height", ">", ValueType::Float64(1.71)).json_extract("height", ".student_height", None).finish();
4342
4343        assert_eq!(with_or_2, "SELECT * FROM students WHERE weight > 60 OR JSON_EXTRACT(height, '$.student_height') > 1.71;".to_string());
4344
4345        // tests with "count()" constructor
4346
4347        let count_query_1 = QueryBuilder::count("*", None).json_extract("age", ".student_age", Some("value")).table("students").group_by("points").having("points", ">", ValueType::Int32(75)).finish();
4348
4349        assert_eq!(count_query_1, "SELECT JSON_EXTRACT(age, '$.student_age') AS value, COUNT(*) FROM students GROUP BY points HAVING points > 75;".to_string());
4350
4351        // tests with ".order_by()" method
4352        
4353        let fields = ["title", "desc", "created_at", "updated_at", "keywords", "pics", "likes"].to_vec();
4354
4355        let order_by_query_1 = QueryBuilder::select(fields).unwrap().table("contents").where_("published", "=", ValueType::Int32(1)).order_by("likes", "ASC").json_extract("likes", ".name", None).finish();
4356
4357        assert_eq!(order_by_query_1, "SELECT title, desc, created_at, updated_at, keywords, pics, likes FROM contents WHERE published = 1 ORDER BY JSON_EXTRACT(likes, '$.name') ASC;".to_string());
4358    
4359        // tests with ".json_extract()" method
4360
4361        let json_extract_chaining = QueryBuilder::select(["*"].to_vec()).unwrap().json_extract("articles", "[0]", Some("blog1")).json_extract("articles", "[1]", Some("blog2")).json_extract("articles", "[2]", Some("blog3")).table("users").where_("published", "=", ValueType::Int32(1)).finish();
4362
4363        assert_eq!(json_extract_chaining, "SELECT JSON_EXTRACT(articles, '$[0]') AS blog1, JSON_EXTRACT(articles, '$[1]') AS blog2, JSON_EXTRACT(articles, '$[2]') AS blog3 FROM users WHERE published = 1;".to_string());
4364    }
4365
4366    #[test]
4367    pub fn test_json_contains(){
4368        // test with "select()" constructor:
4369
4370        let ins = [ValueType::Int32(1), ValueType::Int32(5), ValueType::Int64(11)].to_vec();
4371        let select_query = QueryBuilder::select(["*"].to_vec()).unwrap().json_contains("pic", JsonValue::Initial(&ValueType::String("\"/files/hello.jpg\"".to_string())), Some(".path")).table("users").where_in("id", &ins).finish();
4372
4373        assert_eq!(select_query, "SELECT JSON_CONTAINS(pic, '\"/files/hello.jpg\"', '$.path') FROM users WHERE id IN (1, 5, 11);".to_string());
4374
4375        // test with ".where_cond()" method:
4376
4377        let where_query = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").where_("pic", "=", ValueType::String("".to_string())).json_contains("pic", JsonValue::Initial(&ValueType::String("\"blablabla.jpg\"".to_string())), Some(".name")).finish();
4378
4379        assert_eq!(where_query, "SELECT * FROM users WHERE JSON_CONTAINS(pic, '\"blablabla.jpg\"', '$.name');".to_string());
4380
4381        let and_query_1 = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").where_("age", ">", ValueType::Int32(15)).and("asdfasdf", ">", ValueType::String("".to_string())).json_contains("graduation_stats", JsonValue::Initial(&ValueType::Float64(80.11)), Some(".average_point")).finish();
4382
4383        assert_eq!(and_query_1, "SELECT * FROM users WHERE age > 15 AND JSON_CONTAINS(graduation_stats, 80.11, '$.average_point');".to_string());
4384
4385        let and_query_2 = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").where_("age", ">", ValueType::Int32(15)).and("class", "=", ValueType::String("5/c".to_string())).and("asdfasdf", ">", ValueType::String("".to_string())).json_contains("graduation_stats", JsonValue::Initial(&ValueType::Float64(80.11)), Some(".average_point")).finish();
4386
4387        assert_eq!(and_query_2, "SELECT * FROM users WHERE age > 15 AND class = '5/c' AND JSON_CONTAINS(graduation_stats, 80.11, '$.average_point');".to_string());
4388        
4389        let and_query_3 = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").where_("age", ">", ValueType::Int32(15)).and("class", "=", ValueType::String("5/c".to_string())).and("surname", "=", ValueType::String("etiman".to_string())).and("asdfasdf", ">", ValueType::String("".to_string())).json_contains("graduation_stats", JsonValue::Initial(&ValueType::Float64(80.11)), Some(".average_point")).finish();
4390    
4391        assert_eq!(and_query_3, "SELECT * FROM users WHERE age > 15 AND class = '5/c'  AND surname = 'etiman' AND JSON_CONTAINS(graduation_stats, 80.11, '$.average_point');".to_string());
4392
4393        let and_query_4 = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").where_("age", ">", ValueType::Int32(15)).and("sdfgsdfg", "=", ValueType::String("".to_string())).json_contains("parents", JsonValue::Initial(&ValueType::Int32(50)), Some(".age")).and("surname", "=", ValueType::String("etiman".to_string())).and("asdfasdf", ">", ValueType::String("".to_string())).json_contains("graduation_stats", JsonValue::Initial(&ValueType::Float32(80.11)), Some(".average_point")).finish();
4394    
4395        assert_eq!(and_query_4, "SELECT * FROM users WHERE age > 15 AND JSON_CONTAINS(parents, 50, '$.age')  AND surname = 'etiman' AND JSON_CONTAINS(graduation_stats, 80.11, '$.average_point');".to_string());
4396
4397        let and_query_5 = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").where_("age", ">", ValueType::Int32(15)).and("sdfgsdfg", "=", ValueType::String("".to_string())).json_contains("parents", JsonValue::Initial(&ValueType::Int32(50)), Some(".age")).and("asdfasdf", ">", ValueType::String("".to_string())).json_contains("graduation_stats", JsonValue::Initial(&ValueType::Float64(80.11)), Some(".average_point")).finish();
4398    
4399        assert_eq!(and_query_5, "SELECT * FROM users WHERE age > 15 AND JSON_CONTAINS(parents, 50, '$.age') AND JSON_CONTAINS(graduation_stats, 80.11, '$.average_point');".to_string());
4400        
4401        let or_query_1 = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").where_("age", ">", ValueType::Int32(15)).or("asdfasdf", ">", ValueType::String("".to_string())).json_contains("graduation_stats", JsonValue::Initial(&ValueType::Float32(80.11)), Some(".average_point")).finish();
4402
4403        assert_eq!(or_query_1, "SELECT * FROM users WHERE age > 15 OR JSON_CONTAINS(graduation_stats, 80.11, '$.average_point');".to_string());
4404        
4405        let or_query_2 = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").where_("age", ">", ValueType::Int32(15)).or("class", "=", ValueType::String("5/c".to_string())).or("asdfasdf", ">", ValueType::String("".to_string())).json_contains("graduation_stats", JsonValue::Initial(&ValueType::Float64(80.11)), Some(".average_point")).finish();
4406        
4407        assert_eq!(or_query_2, "SELECT * FROM users WHERE age > 15 OR class = '5/c' OR JSON_CONTAINS(graduation_stats, 80.11, '$.average_point');".to_string());
4408                
4409        let or_query_3 = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").where_("age", ">", ValueType::Int32(15)).or("class", "=", ValueType::String("5/c".to_string())).or("surname", "=", ValueType::String("etiman".to_string())).or("asdfasdf", ">", ValueType::String("".to_string())).json_contains("graduation_stats", JsonValue::Initial(&ValueType::Float64(80.11)), Some(".average_point")).finish();
4410            
4411        assert_eq!(or_query_3, "SELECT * FROM users WHERE age > 15 OR class = '5/c'  OR surname = 'etiman' OR JSON_CONTAINS(graduation_stats, 80.11, '$.average_point');".to_string());
4412        
4413        let or_query_4 = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").where_("age", ">", ValueType::Int32(15)).or("sdfgsdfg", "=", ValueType::String("".to_string())).json_contains("parents", JsonValue::Initial(&ValueType::Int32(50)), Some(".age")).or("surname", "=", ValueType::String("etiman".to_string())).or("asdfasdf", ">", ValueType::String("".to_string())).json_contains("graduation_stats", JsonValue::Initial(&ValueType::Float64(80.11)), Some(".average_point")).finish();
4414            
4415        assert_eq!(or_query_4, "SELECT * FROM users WHERE age > 15 OR JSON_CONTAINS(parents, 50, '$.age')  OR surname = 'etiman' OR JSON_CONTAINS(graduation_stats, 80.11, '$.average_point');".to_string());
4416        
4417        let or_query_5 = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").where_("age", ">", ValueType::Int32(15)).or("sdfgsdfg", "=", ValueType::String("".to_string())).json_contains("parents", JsonValue::Initial(&ValueType::Int64(50)), Some(".age")).or("asdfasdf", ">", ValueType::String("".to_string())).json_contains("graduation_stats", JsonValue::Initial(&ValueType::Float32(80.11)), Some(".average_point")).finish();
4418            
4419        assert_eq!(or_query_5, "SELECT * FROM users WHERE age > 15 OR JSON_CONTAINS(parents, 50, '$.age') OR JSON_CONTAINS(graduation_stats, 80.11, '$.average_point');".to_string());
4420
4421        let name = ValueType::JsonString("necdet".to_string());
4422        let id = ValueType::Int32(1);
4423        let is_active = ValueType::Boolean(true);
4424
4425        let object = vec![("name", &name), ("id", &id), ("isActive", &is_active)];
4426
4427        let mysql_json_object = JsonValue::MysqlJsonObject(&object);
4428
4429        let where_query_2 = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").where_("pic", "=", ValueType::String("".to_string())).json_contains("pic", mysql_json_object, Some("")).finish();
4430
4431        assert_eq!("SELECT * FROM users WHERE JSON_CONTAINS(pic, JSON_OBJECT('name', \"necdet\", 'id', 1, 'isActive', true), '$');", where_query_2)
4432    }
4433
4434    #[test]
4435    pub fn test_like_later_than_where_keywords(){
4436        let mut like_query_1 = QueryBuilder::select(["*"].to_vec()).unwrap();
4437
4438        let like_query_1 = like_query_1.table("blogs")
4439                                                      .where_("id", "=", ValueType::Int32(5))
4440                                                      .like(["title", "description"].to_vec(), "hello")
4441                                                      .finish();
4442
4443        assert_eq!(like_query_1, "SELECT * FROM blogs WHERE id = 5 AND (title LIKE '%hello%' OR description LIKE '%hello%');");
4444    
4445        let mut like_query_2 = QueryBuilder::select(["*"].to_vec()).unwrap();
4446
4447        let ins = vec![ValueType::Int32(1), ValueType::Int32(2), ValueType::Int32(3)];
4448        let like_query_2 = like_query_2.table("blogs")
4449                                                          .where_in("id", &ins)
4450                                                          .like(["title", "description", "keywords"].to_vec(), "necdet")
4451                                                          .limit(10)
4452                                                          .offset(0)
4453                                                          .finish();
4454
4455        assert_eq!(like_query_2, "SELECT * FROM blogs WHERE id IN (1, 2, 3) AND (title LIKE '%necdet%' OR description LIKE '%necdet%' OR keywords LIKE '%necdet%') LIMIT 10 OFFSET 0;")
4456    }
4457
4458    #[test]
4459    pub fn test_ordering_functions(){
4460        let order_by_query = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").order_by("id", "asc").order_by("weight", "desc").order_by("point", "asc").finish();
4461
4462        assert_eq!(order_by_query, "SELECT * FROM users ORDER BY id ASC, weight DESC, point ASC;");
4463
4464        let order_by_random_query = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").order_random().finish();
4465
4466        assert_eq!(order_by_random_query, "SELECT * FROM users ORDER BY RAND();");
4467
4468        let roles = ["admin", "moderator", "member", "guest"].to_vec();
4469        let field_query_1 = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").order_by_field("role", roles.clone()).finish();
4470
4471        assert_eq!(field_query_1, "SELECT * FROM users ORDER BY FIELD(role, 'admin', 'moderator', 'member', 'guest');");
4472
4473        let field_query_2 = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").order_by("id", "asc").order_by_field("role", roles.clone()).finish();
4474
4475        assert_eq!(field_query_2, "SELECT * FROM users ORDER BY id ASC, FIELD(role, 'admin', 'moderator', 'member', 'guest');");
4476
4477        let field_query_3 = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").order_by_field("role", roles.clone()).order_by("id", "asc").finish();
4478
4479        assert_eq!(field_query_3, "SELECT * FROM users ORDER BY FIELD(role, 'admin', 'moderator', 'member', 'guest'), id ASC;");
4480        
4481        let field_query_4 = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").order_by_field("role", roles).order_by_field("status", vec!["active", "banned", "unverified"]).finish();
4482
4483        assert_eq!(field_query_4, "SELECT * FROM users ORDER BY FIELD(role, 'admin', 'moderator', 'member', 'guest'), FIELD(status, 'active', 'banned', 'unverified');");
4484    }
4485
4486    #[test]
4487    pub fn test_unions(){
4488        let mut union_1 = QueryBuilder::select(vec!["name", "age", "id"]).unwrap();
4489        union_1.table("users").where_("age", ">", ValueType::Int32(7));
4490
4491        let union_2 = QueryBuilder::select(vec!["name", "age", "id"]).unwrap()
4492                                                          .table("users")
4493                                                          .where_("age", "<", ValueType::Int32(15))
4494                                                          .union(vec![union_1])
4495                                                          .finish();
4496
4497        assert_eq!(union_2, "(SELECT name, age, id FROM users WHERE age < 15) UNION (SELECT name, age, id FROM users WHERE age > 7);");
4498
4499        let mut union_1 = QueryBuilder::select(vec!["id", "title", "description", "published"]).unwrap();
4500        union_1.table("blogs").like(vec!["title"], "text");
4501
4502        let mut union_2 = QueryBuilder::select(vec!["id", "title", "description", "published"]).unwrap();
4503        union_2.table("blogs").like(vec!["description"], "some text");
4504
4505        let union_3 = QueryBuilder::select(vec!["id", "title", "description", "published"]).unwrap()
4506                                                              .table("blogs")
4507                                                              .where_("published", "=", ValueType::Boolean(true))
4508                                                              .union_all(vec![union_1, union_2])
4509                                                              .finish();
4510
4511        assert_eq!(union_3, "(SELECT id, title, description, published FROM blogs WHERE published = true) UNION ALL (SELECT id, title, description, published FROM blogs WHERE title LIKE '%text%') UNION ALL (SELECT id, title, description, published FROM blogs WHERE description LIKE '%some text%');");
4512    }
4513
4514    #[test]
4515    pub fn test_json_value(){
4516        let name = ValueType::JsonString("necdet".to_string());
4517        let age = ValueType::Int8(25);
4518        let id = ValueType::Int32(1);
4519
4520        let values = vec![("name", &name), ("age", &age), ("id", &id)];
4521
4522        let json_object = JsonValue::Object(&values);
4523
4524        assert_eq!("{\"name\": \"necdet\", \"age\": 25, \"id\": 1}", json_object.to_string());
4525
4526        let mysql_json_object = JsonValue::MysqlJsonObject(&values);
4527
4528        assert_eq!("JSON_OBJECT('name', \"necdet\", 'age', 25, 'id', 1)", mysql_json_object.to_string());
4529
4530        let name2 = ValueType::JsonString("cevdet".to_string());
4531        let age2 = ValueType::Int8(24);
4532        let id2 = ValueType::Int32(2);
4533
4534        let name3 = ValueType::JsonString("serap".to_string());
4535        let age3 = ValueType::Int8(21);
4536        let id3 = ValueType::Int32(3);
4537
4538        let object1 = vec![("name", &name), ("age", &age), ("id", &id)];
4539        let object2 = vec![("name", &name2), ("age", &age2), ("id", &id2)];
4540        let object3 = vec![("name", &name3), ("age", &age3), ("id", &id3)];
4541
4542        let objects = vec![object1, object2, object3];
4543        
4544        let json_array = JsonValue::ObjectArray(&objects);
4545
4546        assert_eq!("[{\"name\": \"necdet\", \"age\": 25, \"id\": 1}, {\"name\": \"cevdet\", \"age\": 24, \"id\": 2}, {\"name\": \"serap\", \"age\": 21, \"id\": 3}]", json_array.to_string());
4547    }
4548
4549    #[test]
4550    pub fn test_json_array_append(){
4551        let lesson = ("lesson", &ValueType::String("math".to_string()));
4552        let point = ("point", &ValueType::Int32(100));
4553
4554        let values = vec![lesson, point];
4555        
4556        let object = JsonValue::MysqlJsonObject(&values);
4557
4558        let query = QueryBuilder::update().unwrap()
4559                                         .table("users")
4560                                         .json_array_append("points", Some(""), object.clone())
4561                                         .where_("id", "=", ValueType::Int8(1))
4562                                         .finish();
4563
4564        assert_eq!("UPDATE users SET points = JSON_ARRAY_APPEND(points, '$', JSON_OBJECT('lesson', 'math', 'point', 100)) WHERE id = 1;", query);
4565
4566        let query = QueryBuilder::update().unwrap()
4567                                         .table("users")
4568                                         .set("status", ValueType::String("passed".to_string()))
4569                                         .json_array_append("points", Some(""), object)
4570                                         .where_("id", "=", ValueType::Int8(1))
4571                                         .finish();
4572
4573        assert_eq!("UPDATE users SET status = 'passed', points = JSON_ARRAY_APPEND(points, '$', JSON_OBJECT('lesson', 'math', 'point', 100)) WHERE id = 1;", query);
4574    }
4575
4576    #[test]
4577    pub fn test_json_remove() {
4578        let query = QueryBuilder::update().unwrap()
4579                                         .table("blogs")
4580                                         .json_remove("likes", vec!["[10]"])
4581                                         .where_("blog_id", "=", ValueType::Int32(20))
4582                                         .finish();
4583
4584        assert_eq!(query, "UPDATE blogs SET likes = JSON_REMOVE(likes, '$[10]') WHERE blog_id = 20;");
4585        
4586        let query = QueryBuilder::update().unwrap()
4587                                         .table("blogs")
4588                                         .set("blabla", ValueType::Int32(50))
4589                                         .json_remove("likes", vec!["[10]", "[11]", "[12]"])
4590                                         .where_("blog_id", "=", ValueType::Int32(20))
4591                                         .finish();
4592
4593        println!("{}", query)
4594    }
4595
4596    #[test]
4597    pub fn test_json_set_and_json_replace(){
4598        let lesson = ("lesson", &ValueType::String("math".to_string()));
4599        let point = ("point", &ValueType::Int32(100));
4600
4601        let values = vec![lesson, point];
4602        
4603        let object = JsonValue::MysqlJsonObject(&values);
4604
4605        let query = QueryBuilder::update().unwrap()
4606                                                        .table("users")
4607                                                        .json_set("points", "[0]", object)
4608                                                        .where_("id", "=", ValueType::Int32(1))
4609                                                        .finish();
4610
4611        assert_eq!("UPDATE users SET points = JSON_SET(points, '$[0]', JSON_OBJECT('lesson', 'math', 'point', 100)) WHERE id = 1;", query);
4612
4613        let value = ValueType::Int32(100);
4614        let value = JsonValue::Initial(&value);
4615
4616        let query = QueryBuilder::update().unwrap()
4617                                         .table("users")
4618                                         .json_replace("points", "[0].point", value)
4619                                         .where_("id", "=", ValueType::Int32(1))
4620                                         .finish();
4621
4622        assert_eq!("UPDATE users SET points = JSON_REPLACE(points, '$[0].point', 100) WHERE id = 1;", query)
4623    }
4624
4625    #[test]
4626    pub fn test_json_value_initial_bugfix(){
4627        let file_name_val = ValueType::JsonString("chemistry".to_string());
4628        let file_name_val = JsonValue::Initial(&file_name_val);
4629
4630        let query = QueryBuilder::select(vec!["lesson_points"]).unwrap()
4631                                         .json_extract("points", &format!("[{}]", 2), Some("point"))
4632                                         .table("students")
4633                                         .where_("id", "=", ValueType::Int32(5))
4634                                         .and("adsf", "=", ValueType::Null)
4635                                         .json_contains("points", file_name_val, Some(&format!("[{}].name", 0)))
4636                                         .finish();
4637
4638        assert_eq!("SELECT JSON_EXTRACT(points, '$[2]') AS point FROM students WHERE id = 5 AND JSON_CONTAINS(points, '\"chemistry\"', '$[0].name');", query);
4639    }
4640
4641    #[test]
4642    pub fn test_timezones(){
4643        let query = QueryBuilder::select(vec!["*"]).unwrap().table("users").time_zone(Timezone::Istanbul).finish();
4644
4645        assert_eq!(query, "SET time_zone = Europe/Istanbul; SELECT * FROM users;");
4646        
4647        let query = QueryBuilder::select(vec!["*"]).unwrap()
4648                                         .table("users")
4649                                         .global_time_zone(Timezone::Amsterdam)
4650                                         .where_("id", "=", ValueType::Int32(3))
4651                                         .and("surname", "=", ValueType::String("Doe".to_string()))
4652                                         .finish();
4653
4654        assert_eq!(query, "SET GLOBAL time_zone = Europe/Amsterdam; SELECT * FROM users WHERE id = 3 AND surname = 'Doe';");
4655
4656        let query = QueryBuilder::update().unwrap().table("users").time_zone(Timezone::NewYork).set("age", ValueType::Int32(26)).set("last_online_date", ValueType::Datetime("CURRENT_TIMESTAMP".to_string())).where_("id", "=", ValueType::Int32(234)).finish();
4657
4658        assert_eq!(query, "SET time_zone = America/New_York; UPDATE users SET age = 26, last_online_date = CURRENT_TIMESTAMP WHERE id = 234;");
4659
4660        let query = QueryBuilder::update().unwrap().table("users").set("age", ValueType::Int32(26)).global_time_zone(Timezone::NewYork).set("last_online_date", ValueType::Datetime("CURRENT_TIMESTAMP".to_string())).where_("id", "=", ValueType::Int32(234)).finish();
4661
4662        assert_eq!(query, "SET GLOBAL time_zone = America/New_York; UPDATE users SET age = 26, last_online_date = CURRENT_TIMESTAMP WHERE id = 234;");
4663    }
4664
4665    #[test]
4666    pub fn test_joins(){
4667        let query = QueryBuilder::select(vec!["*"]).unwrap()
4668                                         .table("students s")
4669                                         .inner_join("grades g", "s.id", "=", "g.student_id")
4670                                         .where_("id", "=", ValueType::Int32(10))
4671                                         .finish();
4672
4673        assert_eq!(query, "SELECT * FROM students s INNER JOIN grades g ON s.id = g.student_id WHERE id = 10;");
4674
4675        let query = QueryBuilder::select(vec!["*"]).unwrap()
4676                                         .table("students s")
4677                                         .left_join("grades g", "s.id", "=", "g.student_id")
4678                                         .where_("id", "=", ValueType::Int32(10))
4679                                         .finish();
4680
4681        assert_eq!(query, "SELECT * FROM students s LEFT JOIN grades g ON s.id = g.student_id WHERE id = 10;");
4682
4683        let query = QueryBuilder::select(vec!["*"]).unwrap()
4684                                         .table("students s")
4685                                         .right_join("grades g", "s.id", "=", "g.student_id")
4686                                         .where_("id", "=", ValueType::Int32(10))
4687                                         .finish();
4688
4689        assert_eq!(query, "SELECT * FROM students s RIGHT JOIN grades g ON s.id = g.student_id WHERE id = 10;");
4690
4691        let query = QueryBuilder::select(vec!["*"]).unwrap()
4692                                         .table("students s")
4693                                         .cross_join("grades g")
4694                                         .where_("id", "=", ValueType::Int32(10))
4695                                         .finish();
4696
4697        assert_eq!(query, "SELECT * FROM students s CROSS JOIN grades g WHERE id = 10;");
4698
4699        let query = QueryBuilder::select(vec!["*"]).unwrap()
4700                                         .table("students s")
4701                                         .natural_join("grades g")
4702                                         .where_("id", "=", ValueType::Int32(10))
4703                                         .finish();
4704                                        
4705        assert_eq!(query, "SELECT * FROM students s NATURAL JOIN grades g WHERE id = 10;");
4706    }
4707
4708    #[test]
4709    pub fn test_parentheses(){
4710        let query = QueryBuilder::select(vec!["*"]).unwrap()
4711                                                        .table("users")
4712                                                        .where_("grades", ">", ValueType::Int32(80))
4713                                                        .open_parenthesis(BracketType::And)
4714                                                        .and("height", ">", ValueType::Int32(170))
4715                                                        .or("weight", ">", ValueType::Int32(60))
4716                                                        .close_parenthesis()
4717                                                        .finish();
4718
4719        assert_eq!(query, "SELECT * FROM users WHERE grades > 80 AND ( AND height > 170 OR weight > 60);");
4720
4721        let query = QueryBuilder::select(vec!["*"]).unwrap()
4722                                                .table("users")
4723                                                .where_("grades", ">", ValueType::Int32(80))
4724                                                .open_parenthesis_with(BracketType::And, "height", ">", ValueType::Int32(170))
4725                                                .or("weight", ">", ValueType::Int32(60))
4726                                                .close_parenthesis()
4727                                                .finish();                                                  
4728
4729        assert_eq!(query, "SELECT * FROM users WHERE grades > 80 AND (height > 170 OR weight > 60);");
4730
4731        let query = QueryBuilder::select(vec!["*"]).unwrap()
4732                                        .table("users")
4733                                        .where_("grades", ">", ValueType::Int32(80))
4734                                        .open_parenthesis_with(BracketType::And, "height", ">", ValueType::Int32(170))
4735                                        .open_parenthesis_with(BracketType::Or, "weight", ">", ValueType::Int32(50))
4736                                        .and("weight", "<", ValueType::Int32(70))
4737                                        .close_parenthesis()
4738                                        .close_parenthesis()
4739                                        .finish();          
4740
4741        assert_eq!(query, "SELECT * FROM users WHERE grades > 80 AND (height > 170 OR (weight > 50 AND weight < 70));");             
4742    }
4743}
qubl/lib.rs

qubl/
lib.rs
