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    pub fn or_not_in_custom(&mut self, column: &str, query: &str) -> &mut Self {
670        self.query = format!("{} OR {} NOT IN ({})", self.query, column, query);
671
672        self.list.push(KeywordList::AndNotIn);
673
674        self
675    }
676
677    /// 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.
678    pub fn time_zone(&mut self, timezone: Timezone) -> &mut Self {
679        self.query = format!("SET time_zone = {}; {}", timezone, self.query);
680
681        self.list.insert(1, KeywordList::Timezone);
682        self
683    }
684
685    /// 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.
686    pub fn global_time_zone(&mut self, timezone: Timezone) -> &mut Self {
687        self.query = format!("SET GLOBAL time_zone = {}; {}", timezone, self.query);
688
689        self.list.insert(1, KeywordList::GlobalTimezone);
690        self
691    }
692
693    /// 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.
694    ///
695    /// ```rust
696    /// 
697    /// use qubl::{QueryBuilder, ValueType};
698    /// 
699    /// fn main(){
700    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
701    ///     let query = QueryBuilder::select(vec!["*"]).unwrap().table("users").where_("id", "=", ValueType::Int32(10)).or("name", "=", ValueType::String("necdet".to_string())).finish();
702    /// 
703    ///     assert_eq!(query, "SELECT * FROM users WHERE id = 10 OR name = 'necdet';")
704    /// }
705   pub fn or(&mut self, column: &str, mut mark: &str, value: ValueType) -> &mut Self {
706        match self.sanitize_column(column) {
707            Ok(_) => (),
708            Err(error) => panic!("{}", error)
709        }
710
711        match Self::sanitize_mark(mark) {
712            Ok(_) => (),
713            Err(error) => panic!("{}", error)
714        }
715
716        match self.sanitize_input(&value) {
717            Ok(_) => (),
718            Err(error) => panic!("{}", error)
719        }
720
721        if let ValueType::Null = value {
722            match mark {
723                "=" => mark = "IS",
724                "!=" | "<>" => mark = "IS NOT",
725                "IS" | "IS NOT" => (),
726                _ => mark = "IS"
727            }
728        }
729
730        self.query = format!("{} OR {} {} {}", self.query, column, mark, value);
731
732
733        self.list.push(KeywordList::Or);
734
735        self
736    }
737
738    /// It adds the "SET" keyword with it's synthax.
739    /// 
740    /// ```rust
741    /// 
742    /// use qubl::{QueryBuilder, ValueType};
743    /// 
744    /// fn main(){
745    ///     let query = QueryBuilder::update().unwrap()
746    ///                              .table("users")
747    ///                              .set("name", ValueType::String("arda".to_string()))
748    ///                              .where_("id", "=", ValueType::Int32(1))
749    ///                              .finish();
750    /// 
751    ///     assert_eq!(query, "UPDATE users SET name = 'arda' WHERE id = 1;")
752    /// }
753    /// 
754    /// ```
755    pub fn set(&mut self, column: &str, value: ValueType) -> &mut Self {
756        match self.hq {
757            Some(_) => (),
758            None => self.hq = Some(Self::load_hqs())
759        }
760
761        match self.sanitize_column(column) {
762            Ok(_) => (),
763            Err(error) => panic!("{}", error)
764        }
765
766        match self.sanitize_input(&value) {
767            Ok(_) => (),
768            Err(error) => panic!("{}", error)
769        }
770
771        match self.list.last() {
772            Some(keyword) => {
773                match keyword {
774                    KeywordList::Set => self.query = format!("{}, {} = {}", self.query, column, value),
775                    _ => self.query = format!("{} SET {} = {}", self.query, column, value)
776                }
777            },
778            None => panic!("that's impossible to come here.")
779        }
780
781        self.list.push(KeywordList::Set);
782
783        self
784    }
785
786    /// 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.
787    ///
788    /// ```rust
789    /// 
790    /// use qubl::{QueryBuilder, ValueType};
791    /// 
792    /// fn main(){
793    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
794    ///     let query = QueryBuilder::select(vec!["*"]).unwrap().table("users").where_("id", "=", ValueType::Int32(10)).and("name", "=", ValueType::String("necdet".to_string())).finish();
795    /// 
796    ///     assert_eq!(query, "SELECT * FROM users WHERE id = 10 AND name = 'necdet';")
797    /// }
798    pub fn and(&mut self, column: &str, mut mark: &str, value: ValueType) -> &mut Self {
799        match self.sanitize_column(column) {
800            Ok(_) => (),
801            Err(error) => panic!("{}", error)
802        }
803
804        match Self::sanitize_mark(mark) {
805            Ok(_) => (),
806            Err(error) => panic!("{}", error)
807        }
808
809        match self.sanitize_input(&value) {
810            Ok(_) => (),
811            Err(error) => panic!("{}", error)
812        }
813
814        if let ValueType::Null = value {
815            match mark {
816                "=" => mark = "IS",
817                "!=" | "<>" => mark = "IS NOT",
818                "IS" | "IS NOT" => (),
819                _ => mark = "IS"
820            }
821        }
822
823        self.query = format!("{} AND {} {} {}", self.query, column, mark, value);
824
825        self.list.push(KeywordList::And);
826
827        self
828    }
829
830    /// It adds the "OFFSET" keyword with it's synthax. Be careful about it's alignment with "LIMIT" keyword.
831    ///     
832    /// ```rust
833    /// 
834    /// use qubl::{QueryBuilder, ValueType};
835    /// 
836    /// fn main(){
837    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
838    ///     let query = QueryBuilder::select(vec!["*"]).unwrap().table("users").where_("id", "=", ValueType::Int32(10)).limit(5).offset(0).finish();
839    /// 
840    ///     assert_eq!(query, "SELECT * FROM users WHERE id = 10 LIMIT 5 OFFSET 0;")
841    /// }
842    pub fn offset(&mut self, offset: i32) -> &mut Self {
843        self.query = format!("{} OFFSET {}", self.query, offset);
844
845        self.list.push(KeywordList::Offset);
846
847        self
848    }
849
850    /// It adds the "LIMIT" keyword with it's synthax.
851    /// 
852    /// ```rust
853    /// 
854    /// use qubl::{QueryBuilder, ValueType};
855    /// 
856    /// fn main(){
857    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
858    ///     let query = QueryBuilder::select(vec!["*"]).unwrap().table("users").where_("id", "=", ValueType::Int32(10)).limit(5).offset(0).finish();
859    /// 
860    ///     assert_eq!(query, "SELECT * FROM users WHERE id = 10 LIMIT 5 OFFSET 0;")
861    /// }
862    pub fn limit(&mut self, limit: i32) -> &mut Self {
863        self.query = format!("{} LIMIT {}", self.query, limit);
864
865        self.list.push(KeywordList::Limit);
866
867        self
868    }
869
870    /// It adds the "LIKE" keyword with it's synthax.
871    /// ```rust
872    /// 
873    /// use qubl::{QueryBuilder, ValueType};
874    /// 
875    /// fn main(){
876    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
877    ///                              .table("blogs")
878    ///                              .like(vec!["description", "title"], "qubl is awesome!")
879    ///                              .finish();
880    /// 
881    ///     assert_eq!(query, "SELECT * FROM blogs WHERE description LIKE '%qubl is awesome!%' OR title LIKE '%qubl is awesome!%';")
882    /// }
883    /// 
884    /// // it has more niche and different usages, for them check the tests.
885    pub fn like(&mut self, columns: Vec<&str>, operand: &str) -> &mut Self {
886        match columns.len() {
887            0 => panic!("you cannot pass an empty vector to the columns"),
888            _ => ()
889        }
890
891        let hqs = match self.hq {
892            Some(hqs) => hqs,
893            None => {
894                let load_hqs = Self::load_hqs();
895                self.hq = Some(load_hqs);
896
897                load_hqs
898            }
899        };
900
901        match Self::sanitize_columns(&columns, hqs) {
902            Ok(_) => {
903                match self.sanitize_str(operand){
904                    Ok(_) => (),
905                    Err(error) => {
906                        println!("That Error Occured in like method: {}", error);
907                
908                        self.list.push(KeywordList::Like);
909        
910                        return self
911                    }
912                }
913        
914                match self.list.last() {
915                    Some(keyword) => {
916                        if keyword == &KeywordList::Where || keyword == &KeywordList::WhereIn || keyword == &KeywordList::WhereNotIn {
917                            let length_of_columns = columns.len();
918        
919                            for (i, column) in columns.into_iter().enumerate() {
920                                match length_of_columns {
921                                    1 => {
922                                        if i == 0 {
923                                            self.query = format!("{} AND {} LIKE '%{}%'", self.query, column, operand)
924                                        }  
925                                    },
926                                    _ => {
927                                        if i == 0 {
928                                            self.query = format!("{} AND ({} LIKE '%{}%'", self.query, column, operand)
929                                        } else if i + 1 == length_of_columns {
930                                            self.query = format!("{} OR {} LIKE '%{}%')", self.query, column, operand)
931                                        } else {
932                                            self.query = format!("{} OR {} LIKE '%{}%'", self.query, column, operand)
933                                        }
934                                    }
935                                }
936                            }
937                        } else {
938                            for (i, column) in columns.into_iter().enumerate() {
939                                if i == 0 {
940                                    self.query = format!("{} WHERE {} LIKE '%{}%'", self.query, column, operand);
941                                } else {
942                                    self.query = format!("{} OR {} LIKE '%{}%'", self.query, column, operand);
943                                }
944                            }
945                        }
946                    },
947                    None => panic!("Our current implementation does not support to use '.like()' later not other than WHERE, IN or NOT IN queries.")
948                }
949
950                return self
951            },
952            Err(error) => panic!("That error occured in '.like()' method: {}", error)
953        }
954    }
955
956    /// It adds the "ORDER BY" keyword with it's synthax. It only accepts "ASC", "DESC", "asc", "desc" values.
957    /// ```rust
958    /// 
959    /// use qubl::{QueryBuilder, ValueType};
960    /// 
961    /// fn main(){
962    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
963    ///                              .table("users")
964    ///                              .where_("age", ">", ValueType::Int32(25))
965    ///                              .order_by("id", "ASC")
966    ///                              .limit(5)
967    ///                              .offset(0)
968    ///                              .finish();
969    /// 
970    ///     assert_eq!(query, "SELECT * FROM users WHERE age > 25 ORDER BY id ASC LIMIT 5 OFFSET 0;")
971    /// }
972    pub fn order_by(&mut self, column: &str, mut ordering: &str) -> &mut Self {
973        match self.sanitize_column(column) {
974            Ok(_) => (),
975            Err(error) => {
976                println!("{}", error);
977
978                self.list.push(KeywordList::OrderBy);
979
980                return self
981            }
982        }
983
984        match ordering {
985            "asc" => ordering = "ASC",
986            "desc" => ordering = "DESC",
987            "ASC" => ordering = "ASC",
988            "DESC" => ordering = "DESC",
989            &_ => panic!("Panicking in order_by method: There is no other ordering options than ASC or DESC.")
990        }
991
992        match self.list.last() {
993            Some(keyword) => match keyword {
994                KeywordList::OrderBy | KeywordList::Field => self.query = format!("{}, {} {}", self.query, column, ordering),
995                _ => self.query = format!("{} ORDER BY {} {}", self.query, column, ordering)
996            },
997            None => panic!("It's almost impossible you to come here.")
998        }
999
1000        self.list.push(KeywordList::OrderBy);
1001
1002        self
1003    }
1004
1005    /// A practical method that adds a query for shuffling the lines.
1006    /// ```rust
1007    /// 
1008    /// use qubl::{QueryBuilder, ValueType};
1009    /// 
1010    /// fn main(){
1011    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
1012    ///                              .table("users")
1013    ///                              .where_("age", ">", ValueType::Int32(25))
1014    ///                              .order_random()
1015    ///                              .limit(5)
1016    ///                              .offset(0)
1017    ///                              .finish();
1018    /// 
1019    ///     assert_eq!(query, "SELECT * FROM users WHERE age > 25 ORDER BY RAND() LIMIT 5 OFFSET 0;")
1020    /// }
1021    pub fn order_random(&mut self) -> &mut Self {
1022        if self.query.contains("ORDER BY") {
1023            panic!("Error in order_random method: you cannot add ordering option twice on a query.");
1024        }
1025
1026        self.query = format!("{} ORDER BY RAND()", self.query);
1027        self.list.push(KeywordList::OrderBy);
1028
1029        self
1030    }
1031
1032    /// Adds "FIELD()" function with it's synthax. It's used on ordering depending on strings.
1033    /// ```rust
1034    /// 
1035    /// use qubl::{QueryBuilder, ValueType};
1036    /// 
1037    /// fn main(){
1038    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
1039    ///                              .table("users")
1040    ///                              .where_("age", ">", ValueType::Int32(25))
1041    ///                              .order_by_field("role", vec!["admin", "member", "observer"])
1042    ///                              .limit(5)
1043    ///                              .offset(0)
1044    ///                              .finish();
1045    /// 
1046    ///     assert_eq!(query, "SELECT * FROM users WHERE age > 25 ORDER BY FIELD(role, 'admin', 'member', 'observer') LIMIT 5 OFFSET 0;")
1047    /// }
1048    pub fn order_by_field(&mut self, column: &str, ordering: Vec<&str>) -> &mut Self {
1049        match ordering.len() {
1050            0 => panic!("you cannot pass an empty vector to the ordering argument"),
1051            _ => ()
1052        }
1053
1054        match self.list.last() {
1055            Some(keyword) => match keyword {
1056                KeywordList::OrderBy => {
1057                    let mut split_the_query = self.query.split(" ORDER BY ");
1058                
1059                    self.query = format!("{} ORDER BY {}, FIELD({}", split_the_query.nth(0).unwrap(), split_the_query.nth(0).unwrap(), column);
1060
1061                    for item in ordering {
1062                        self.query = format!("{}, '{}'", self.query, item)
1063                    }
1064
1065                    self.query = format!("{})", self.query);
1066                },
1067                KeywordList::Field => {
1068                    self.query = format!("{}, FIELD({}", self.query, column);
1069
1070                    for item in ordering {
1071                        self.query = format!("{}, '{}'", self.query, item)
1072                    }
1073
1074                    self.query = format!("{})", self.query);
1075                },
1076                _ => {
1077                    let mut new_part_of_query = format!("ORDER BY FIELD({}", column);
1078
1079                    for item in ordering {
1080                        new_part_of_query = format!("{}, '{}'", new_part_of_query, item)
1081                    }
1082
1083                    self.query = format!("{} {})", self.query, new_part_of_query);
1084                }
1085            },
1086            None => panic!("It's almost impossible you to come here.")
1087        }
1088
1089        self.list.push(KeywordList::Field);
1090
1091        self
1092    }
1093
1094    /// It adds the "GROUP BY" keyword with it's Synthax.
1095    pub fn group_by(&mut self, column: &str) -> &mut Self {
1096        self.query = format!("{} GROUP BY {}", self.query, column);
1097
1098        self.list.push(KeywordList::GroupBy);
1099
1100        self
1101    }
1102
1103    pub fn having(&mut self, column: &str, mut mark: &str, value: ValueType) -> &mut Self {
1104        match self.sanitize_column(column) {
1105            Ok(_) => (),
1106            Err(error) => panic!("{}", error)
1107        }
1108
1109        match Self::sanitize_mark(mark) {
1110            Ok(_) => (),
1111            Err(error) => panic!("{}", error)
1112        }
1113
1114        match self.sanitize_input(&value) {
1115            Ok(_) => (),
1116            Err(error) => panic!("{}", error)
1117        }
1118
1119        if let ValueType::Null = value {
1120            match mark {
1121                "=" => mark = "IS",
1122                "!=" | "<>" => mark = "IS NOT",
1123                "IS" | "IS NOT" => (),
1124                _ => mark = "IS"
1125            }
1126        }
1127
1128        self.query = format!("{} HAVING {} {} {}", self.query, column, mark, value);
1129
1130        self.list.push(KeywordList::Having);
1131
1132        self
1133    }
1134
1135    /// it adds the `INNER JOIN` keyword with it's synthax.
1136    /// ```rust
1137    /// 
1138    /// use qubl::{QueryBuilder, ValueType};
1139    /// 
1140    /// fn main(){ 
1141    ///    let query = QueryBuilder::select(vec!["*"]).unwrap()
1142    ///                             .table("students s")
1143    ///                             .inner_join("grades g", "s.id", "=", "g.student_id")
1144    ///                             .where_("id", "=", ValueType::Int32(10))
1145    ///                             .finish();
1146    ///
1147    ///    assert_eq!(query, "SELECT * FROM students s INNER JOIN grades g ON s.id = g.student_id WHERE id = 10;");
1148    /// }
1149    /// 
1150    /// ```
1151    pub fn inner_join(&mut self, table: &str, left: &str, mark: &str, right: &str) -> &mut Self {
1152        self.query = format!("{} INNER JOIN {} ON {} {} {}", self.query, table, left, mark, right);
1153        self.list.push(KeywordList::InnerJoin);
1154        self
1155    }
1156
1157     /// it adds the `LEFT JOIN` keyword with it's synthax.
1158    /// ```rust
1159    /// 
1160    /// use qubl::{QueryBuilder, ValueType};
1161    /// 
1162    /// fn main(){ 
1163    ///    let query = QueryBuilder::select(vec!["*"]).unwrap()
1164    ///                             .table("students s")
1165    ///                             .left_join("grades g", "s.id", "=", "g.student_id")
1166    ///                             .where_("id", "=", ValueType::Int32(10))
1167    ///                             .finish();
1168    ///
1169    ///    assert_eq!(query, "SELECT * FROM students s LEFT JOIN grades g ON s.id = g.student_id WHERE id = 10;");
1170    /// }
1171    /// 
1172    /// ```
1173    pub fn left_join(&mut self, table: &str, left: &str, mark: &str, right: &str) -> &mut Self {
1174        self.query = format!("{} LEFT JOIN {} ON {} {} {}", self.query, table, left, mark, right);
1175        self.list.push(KeywordList::LeftJoin);
1176        self
1177    }
1178
1179    /// it adds the `RIGHT JOIN` keyword with it's synthax.
1180    /// ```rust
1181    /// 
1182    /// use qubl::{QueryBuilder, ValueType};
1183    /// 
1184    /// fn main(){ 
1185    ///    let query = QueryBuilder::select(vec!["*"]).unwrap()
1186    ///                             .table("students s")
1187    ///                             .right_join("grades g", "s.id", "=", "g.student_id")
1188    ///                             .where_("id", "=", ValueType::Int32(10))
1189    ///                             .finish();
1190    ///
1191    ///    assert_eq!(query, "SELECT * FROM students s RIGHT JOIN grades g ON s.id = g.student_id WHERE id = 10;");
1192    /// }
1193    /// 
1194    /// ```
1195    pub fn right_join(&mut self, table: &str, left: &str, mark: &str, right: &str) -> &mut Self {
1196        self.query = format!("{} RIGHT JOIN {} ON {} {} {}", self.query, table, left, mark, right);
1197        self.list.push(KeywordList::RightJoin);
1198        self
1199    }
1200
1201    /// it adds the `CROSS JOIN` keyword with it's synthax.
1202    /// ```rust
1203    /// 
1204    /// use qubl::{QueryBuilder, ValueType};
1205    /// 
1206    /// fn main(){ 
1207    ///    let query = QueryBuilder::select(vec!["*"]).unwrap()
1208    ///                             .table("students s")
1209    ///                             .cross_join("grades g")
1210    ///                             .where_("id", "=", ValueType::Int32(10))
1211    ///                             .finish();
1212    ///
1213    ///    assert_eq!(query, "SELECT * FROM students s RIGHT JOIN grades g ON s.id = g.student_id WHERE id = 10;");
1214    /// }
1215    /// 
1216    /// ```
1217    pub fn cross_join(&mut self, table: &str) -> &mut Self {
1218        self.query = format!("{} CROSS JOIN {}", self.query, table);
1219        self.list.push(KeywordList::RightJoin);
1220        self
1221    }
1222
1223    /// it adds the `NATURAL JOIN` keyword with it's synthax.
1224    /// ```rust
1225    /// 
1226    /// use qubl::{QueryBuilder, ValueType};
1227    /// 
1228    /// fn main(){ 
1229    ///    let query = QueryBuilder::select(vec!["*"]).unwrap()
1230    ///                             .table("students s")
1231    ///                             .natural_join("grades g")
1232    ///                             .where_("id", "=", ValueType::Int32(10))
1233    ///                             .finish();
1234    ///
1235    ///    assert_eq!(query, "SELECT * FROM students s RIGHT JOIN grades g ON s.id = g.student_id WHERE id = 10;");
1236    /// }
1237    /// 
1238    /// ```
1239    pub fn natural_join(&mut self, table: &str) -> &mut Self {
1240        self.query = format!("{} NATURAL JOIN {}", self.query, table);
1241        self.list.push(KeywordList::RightJoin);
1242        self
1243    }
1244
1245
1246    /// it adds the `UNION` keyword and its synthax. You can pass multiple queries to union with:
1247    /// 
1248    /// ```rust
1249    /// 
1250    /// use qubl::{QueryBuilder, ValueType};
1251    /// 
1252    /// fn main(){
1253    ///     let mut union_1 = QueryBuilder::select(vec!["name", "age", "id"]).unwrap();
1254    ///     union_1.table("users").where_("age", ">", ValueType::Int32(7));
1255    ///
1256    ///     let union_2 = QueryBuilder::select(vec!["name", "age", "id"]).unwrap()
1257    ///                                .table("users")
1258    ///                                .where_("age", "<", ValueType::Int32(15))
1259    ///                                .union(vec![union_1])
1260    ///                                .finish();
1261    ///
1262    ///     assert_eq!(union_2, "(SELECT name, age, id FROM users WHERE age < 15) UNION (SELECT name, age, id FROM users WHERE age > 7);");
1263    /// }
1264    /// 
1265    /// ```
1266    pub fn union(&mut self, others: Vec<QueryBuilder<'_>>) -> &mut Self {
1267        match self.list.last() {
1268            Some(keyword) => {
1269                match keyword {
1270                    KeywordList::Union | KeywordList::UnionAll => {
1271                        for other in others {
1272                            self.query = format!("{} UNION ({})", self.query, other.query)
1273                        }
1274                    },
1275                    _ => {
1276                        self.query = format!("({})", self.query);
1277                        
1278                        for other in others {
1279                            self.query = format!("{} UNION ({})", self.query, other.query)
1280                        }
1281                    }
1282                }
1283            },
1284            None => panic!("it's impossible to came here!")
1285        }
1286
1287        self.list.push(KeywordList::Union);
1288
1289        self
1290    }
1291
1292
1293    /// it adds the `UNION` keyword and its synthax. You can pass multiple queries to union with:
1294    /// 
1295    /// ```rust
1296    /// 
1297    /// use qubl::{QueryBuilder, ValueType};
1298    /// 
1299    /// fn main(){
1300    ///     let mut union_1 = QueryBuilder::select(vec!["id", "title", "description", "published"]).unwrap();
1301    ///     union_1.table("blogs").like(vec!["title"], "text");
1302    ///
1303    ///     let mut union_2 = QueryBuilder::select(vec!["id", "title", "description", "published"]).unwrap();
1304    ///     union_2.table("blogs").like(vec!["description"], "some text");
1305    ///
1306    ///     let union_3 = QueryBuilder::select(vec!["id", "title", "description", "published"]).unwrap()
1307    ///                                .table("blogs")
1308    ///                                .where_("published", "=", ValueType::Boolean(true))
1309    ///                                .union_all(vec![union_1, union_2])
1310    ///                                .finish();
1311    ///
1312    ///     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%');");
1313    /// }
1314    /// 
1315    /// ```
1316    /// 
1317    pub fn union_all(&mut self, others: Vec<QueryBuilder<'_>>) -> &mut Self {
1318        match self.list.last() {
1319            Some(keyword) => {
1320                match keyword {
1321                    KeywordList::Union | KeywordList::UnionAll => {
1322                        for other in others {
1323                            self.query = format!("{} UNION ALL ({})", self.query, other.query)
1324                        }
1325                    },
1326                    _ => {
1327                        self.query = format!("({})", self.query);
1328                        
1329                        for other in others {
1330                            self.query = format!("{} UNION ALL ({})", self.query, other.query)
1331                        }
1332                    }
1333                }
1334            },
1335            None => panic!("it's impossible to came here!")
1336        }
1337
1338        self.list.push(KeywordList::UnionAll);
1339
1340        self
1341    }
1342
1343    /// 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.  
1344    /// 
1345    /// ```rust
1346    /// 
1347    /// use qubl::{QueryBuilder, ValueType};
1348    /// 
1349    /// fn main(){
1350    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
1351    ///                              .table("users")
1352    ///                              .append_custom("WHERE age > 25 ORDER BY FIELD(role, 'admin', 'member', 'observer') LIMIT 5 OFFSET 0")
1353    ///                              .finish();
1354    /// 
1355    ///     assert_eq!(query, "SELECT * FROM users WHERE age > 25 ORDER BY FIELD(role, 'admin', 'member', 'observer') LIMIT 5 OFFSET 0;")
1356    /// }
1357    /// ```
1358    /// 
1359    pub fn append_custom(&mut self, query: &str) -> &mut Self {
1360        self.query = format!("{} {}", self.query, query);
1361
1362        self
1363    }
1364
1365    /// 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. 
1366    /// 
1367    /// ```rust
1368    /// 
1369    /// use qubl::{QueryBuilder, ValueType, KeywordList};
1370    /// 
1371    /// fn main(){
1372    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
1373    ///                              .table("users")
1374    ///                              .append_custom("WHERE age > 25 ORDER BY FIELD(role, 'admin', 'member', 'observer') LIMIT 5 OFFSET 0")
1375    ///                              .append_keyword(KeywordList::Where)
1376    ///                              .append_keyword(KeywordList::Field)
1377    ///                              .append_keyword(KeywordList::Limit)
1378    ///                              .append_keyword(KeywordList::Offset)
1379    ///                              .finish();
1380    /// 
1381    ///     assert_eq!(query, "SELECT * FROM users WHERE age > 25 ORDER BY FIELD(role, 'admin', 'member', 'observer') LIMIT 5 OFFSET 0;")
1382    /// }
1383    /// ```
1384    /// 
1385    pub fn append_keyword(&mut self, keyword: KeywordList) -> &mut Self {
1386        self.list.push(keyword);
1387
1388        self
1389    }
1390    
1391    /// 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.
1392    /// 
1393    /// ```rust
1394    /// 
1395    /// use qubl::{QueryBuilder, ValueType};
1396    /// 
1397    /// fn main(){
1398    ///     let query = QueryBuilder::select(["*"].to_vec()).unwrap()
1399    ///                              .json_extract("articles", "[0]", Some("blog1"))
1400    ///                              .json_extract("articles", "[1]", Some("blog2"))
1401    ///                              .json_extract("articles", "[2]", Some("blog3"))
1402    ///                              .table("users")
1403    ///                              .where_("published", "=", ValueType::Int32(1))
1404    ///                              .finish();
1405    /// 
1406    ///     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;")
1407    /// }
1408    /// 
1409    /// ```
1410    pub fn json_extract(&mut self, haystack: &str, needle: &str, _as: Option<&str>) -> &mut Self {
1411        match self.list.last() {
1412            Some(keyword) => {
1413                match keyword {
1414                    KeywordList::Where => {
1415                        if _as.is_some() {
1416                            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.");
1417                        }
1418
1419                        match self.table.as_str() == haystack {
1420                            true => {
1421                                let mut split_the_query = self.query.split(haystack);
1422                                let string_for_replace = format!("JSON_EXTRACT({}, '${}')", haystack, needle);
1423
1424                                self.query = format!("SELECT{}{}{}", self.table, string_for_replace, split_the_query.nth(2).unwrap()) 
1425                            },
1426                            false => {
1427                                let string_for_replace = format!("JSON_EXTRACT({}, '${}')", haystack, needle);
1428
1429                                self.query = self.query.replace(haystack,&string_for_replace)
1430                            }
1431                        }
1432                    },
1433                    KeywordList::And => {
1434                        if _as.is_some() {
1435                            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.");
1436                        }
1437
1438                        let query_to_comp = format!("AND {}", haystack);
1439
1440                        match self.table.as_str() == haystack {
1441                            true => {
1442                                let mut split_the_query = self.query.split(&query_to_comp);
1443                                let string_for_replace = format!("JSON_EXTRACT({}, '${}')", haystack, needle);
1444
1445                                self.query = format!("{}AND {}{}", split_the_query.nth(0).unwrap(), string_for_replace, split_the_query.nth(0).unwrap()) 
1446                            },
1447                            false => {
1448                                match self.query.matches(&query_to_comp).count() {
1449                                    0 => (),
1450                                    1 => {
1451                                        let string_for_replace = format!("AND JSON_EXTRACT({}, '${}')", haystack, needle);
1452
1453                                        self.query = self.query.replace(&query_to_comp,&string_for_replace)
1454                                    }
1455                                    _ => {
1456                                        let split_the_query = self.query.split(&query_to_comp).collect::<Vec<&str>>();
1457
1458                                        let mut last_chunk = "".to_string();
1459                                        let mut new_chunk = "".to_string();
1460                                        let length_of_split = split_the_query.len();
1461                                        
1462                                        for (index, chunk) in split_the_query.into_iter().enumerate() {
1463                                            if index + 1 == length_of_split {
1464                                                last_chunk = chunk.to_string()
1465                                            } else if index == 0 {
1466                                                new_chunk = format!("{}", chunk);
1467                                            } else {
1468                                                new_chunk = format!("{}{}{}", new_chunk, query_to_comp, chunk)
1469                                            }
1470                                        }
1471
1472                                        let string_for_replace = format!("AND JSON_EXTRACT({}, '${}')", haystack, needle);
1473
1474                                        self.query = format!("{} {} {}", new_chunk, string_for_replace, last_chunk)
1475                                    }
1476                                }
1477                            }
1478                        }
1479                    },
1480                    KeywordList::Or => {
1481                        if _as.is_some() {
1482                            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.");
1483                        }
1484
1485                        let query_to_comp = format!("OR {}", haystack);
1486
1487                        match self.table.as_str() == haystack {
1488                            true => {
1489                                let mut split_the_query = self.query.split(&query_to_comp);
1490                                let string_for_replace = format!("JSON_EXTRACT({}, '${}')", haystack, needle);
1491
1492                                self.query = format!("{}OR {}{}", split_the_query.nth(0).unwrap(), string_for_replace, split_the_query.nth(0).unwrap()) 
1493                            },
1494                            false => {
1495                                match self.query.matches(&query_to_comp).count() {
1496                                    0 => (),
1497                                    1 => {
1498                                        let string_for_replace = format!("OR JSON_EXTRACT({}, '${}')", haystack, needle);
1499
1500                                        self.query = self.query.replace(&query_to_comp,&string_for_replace)
1501                                    }
1502                                    _ => {
1503                                        let split_the_query = self.query.split(&query_to_comp).collect::<Vec<&str>>();
1504
1505                                        let mut last_chunk = "".to_string();
1506                                        let mut new_chunk = "".to_string();
1507                                        let length_of_split = split_the_query.len();
1508                                        
1509                                        for (index, chunk) in split_the_query.into_iter().enumerate() {
1510                                            if index + 1 == length_of_split {
1511                                                last_chunk = chunk.to_string()
1512                                            } else if index == 0 {
1513                                                new_chunk = format!("{}", chunk);
1514                                            } else {
1515                                                new_chunk = format!("{}{}{}", new_chunk, query_to_comp, chunk)
1516                                            }
1517                                        }
1518
1519                                        let string_for_replace = format!("OR JSON_EXTRACT({}, '${}')", haystack, needle);
1520
1521                                        self.query = format!("{} {} {}", new_chunk, string_for_replace, last_chunk)
1522                                    }
1523                                }
1524                            }
1525                        }
1526                    },
1527                    KeywordList::Select => {
1528                        let string_for_put = format!("JSON_EXTRACT({}, '${}')", haystack, needle);
1529
1530                        match _as {
1531                            Some(_as) => self.query = format!("SELECT {} AS {} FROM", string_for_put, _as),
1532                            None => self.query = format!("SELECT {} FROM", string_for_put),
1533                        }
1534                    },
1535                    KeywordList::Table => {
1536                        let string_for_put = format!("JSON_EXTRACT({}, '${}')", haystack, needle);
1537
1538                        match _as {
1539                            Some(_as) => self.query = format!("SELECT {} AS {} FROM {}", string_for_put, _as, self.table),
1540                            None => self.query = format!("SELECT {} FROM {}", string_for_put, self.table),
1541                        }
1542                    },
1543                    KeywordList::OrderBy => {
1544                        if _as.is_some() {
1545                            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.");
1546                        }
1547
1548                        match self.query.matches(" ORDER BY ").count() {
1549                            0 => (),
1550                            1 => {
1551                                let split_the_query = self.query.clone();
1552                                let mut split_the_query = split_the_query.split(" ORDER BY ");
1553
1554                                let string_for_put = format!("ORDER BY JSON_EXTRACT({}, '${}')", haystack, needle);
1555        
1556                                match _as {
1557                                    Some(_as) => self.query = format!("{} {} AS {}", split_the_query.nth(0).unwrap(), string_for_put, _as),
1558                                    None => self.query = format!("{} {}", split_the_query.nth(0).unwrap(), string_for_put)
1559                                }
1560
1561                                match split_the_query.nth(0) {
1562                                    Some(comparison) => {
1563                                        match comparison.ends_with("ASC") || comparison.ends_with("asc") {
1564                                            true => self.query = format!("{} ASC", self.query),
1565                                            false => match comparison.ends_with("DESC") || comparison.ends_with("desc") {
1566                                                true => self.query = format!("{} DESC", self.query),
1567                                                false => ()
1568                                            }
1569                                        }
1570                                    },
1571                                    None => ()
1572                                }
1573                            },
1574                            _ => ()
1575                        }
1576                    },
1577                    KeywordList::Count => {
1578                        let mut split_the_query = self.query.split(" COUNT");
1579
1580                        let string_for_put = match _as {
1581                            Some(_as) => format!("JSON_EXTRACT({}, '${}') AS {}", haystack, needle, _as),
1582                            None => format!("JSON_EXTRACT({}, '${}')", haystack, needle)
1583                        };
1584
1585                        self.query = format!("SELECT {}, COUNT{}", string_for_put, split_the_query.nth(1).unwrap())
1586                    },
1587                    KeywordList::JsonExtract => {
1588                        let mut split_the_query = self.query.split(" FROM");
1589
1590                        match _as {
1591                            Some(_as) => self.query = format!("{}, JSON_EXTRACT({}, '${}') AS {} FROM", split_the_query.nth(0).unwrap(), haystack, needle, _as),
1592                            None => panic!("If you want to chain .json_extract() methods, you have to give them a tag.")
1593                        }
1594                    }
1595                    _ => ()
1596                }
1597            },
1598            None => ()
1599        }
1600        
1601        self.list.push(KeywordList::JsonExtract);
1602        self
1603    }
1604
1605    /// 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.
1606    /// 
1607    /// ```rust
1608    /// 
1609    /// use qubl::{QueryBuilder, ValueType, JsonValue};
1610    /// 
1611    /// fn main(){
1612    ///     let value = ValueType::String("blablabla.jpg".to_string());
1613    ///     let prop = vec![("name", &value)];
1614    /// 
1615    ///     let object = JsonValue::MysqlJsonObject(&prop);
1616    /// 
1617    ///     let query = QueryBuilder::select(["*"].to_vec()).unwrap()
1618    ///                              .table("users")
1619    ///                              .where_("pic", "=", ValueType::String("".to_string()))
1620    ///                              .json_contains("pic", object, Some(".name"))
1621    ///                              .finish();
1622    /// 
1623    ///     assert_eq!(query, "SELECT * FROM users WHERE JSON_CONTAINS(pic, JSON_OBJECT('name', 'blablabla.jpg'), '$.name');")
1624    /// }
1625    /// 
1626    /// ```
1627    pub fn json_contains(&mut self, column: &str, needle: JsonValue, path: Option<&str>) -> &mut Self {
1628        match self.list.last().unwrap() {
1629            KeywordList::Select => match path {
1630                Some(path) => match needle {
1631                    JsonValue::Initial(initial) => match initial {
1632                        ValueType::JsonString(needle) => self.query = format!("SELECT JSON_CONTAINS({}, '\"{}\"', '${}') FROM", column, needle, path),
1633                        ValueType::String(needle) => self.query = format!("SELECT JSON_CONTAINS({}, '{}', '${}') FROM", column, needle, path),
1634                        ValueType::Datetime(needle) => self.query = format!("SELECT JSON_CONTAINS({}, '{}', '${}') FROM", column, needle, path),
1635                        _ => self.query = format!("SELECT JSON_CONTAINS({}, {}, '${}') FROM", column, needle, path),
1636                    },
1637                    _ => self.query = format!("SELECT JSON_CONTAINS({}, {}, '${}') FROM", column, needle, path),
1638                }
1639                None => match needle {
1640                    JsonValue::Initial(initial) => match initial {
1641                        ValueType::JsonString(needle) => self.query = format!("SELECT JSON_CONTAINS({}, '\"{}\"') FROM", column, needle),
1642                        ValueType::String(needle) => self.query = format!("SELECT JSON_CONTAINS({}, '{}') FROM", column, needle),
1643                        ValueType::Datetime(needle) => self.query = format!("SELECT JSON_CONTAINS({}, '{}') FROM", column, needle),
1644                        _ => self.query = format!("SELECT JSON_CONTAINS({}, {}) FROM", column, needle)
1645                    },
1646                    _ => self.query = format!("SELECT JSON_CONTAINS({}, {}) FROM", column, needle)
1647                }
1648            },
1649            KeywordList::Where => match path {
1650                Some(path) => {
1651                    let mut split_the_query = self.query.split(" WHERE ");
1652
1653                    let first_half = split_the_query.nth(0);
1654
1655                    match needle {
1656                        JsonValue::Initial(initial) => match initial {
1657                            ValueType::JsonString(needle) => self.query = format!("{} WHERE JSON_CONTAINS({}, '\"{}\"', '${}')", first_half.unwrap(), column, needle, path),
1658                            ValueType::String(needle) => self.query = format!("{} WHERE JSON_CONTAINS({}, '{}', '${}')", first_half.unwrap(), column, needle, path),
1659                            ValueType::Datetime(needle) => self.query = format!("{} WHERE JSON_CONTAINS({}, '{}', '${}')", first_half.unwrap(), column, needle, path),
1660                            _ => self.query = format!("{} WHERE JSON_CONTAINS({}, {}, '${}')", first_half.unwrap(), column, needle, path)
1661                        },
1662                        _ => self.query = format!("{} WHERE JSON_CONTAINS({}, {}, '${}')", first_half.unwrap(), column, needle, path)
1663                    }
1664                },
1665                None => {
1666                    let mut split_the_query = self.query.split(" WHERE ");
1667
1668                    let first_half = split_the_query.nth(0);
1669
1670                    match needle {
1671                        JsonValue::Initial(initial) => match initial {
1672                            ValueType::JsonString(needle) => self.query = format!("{} WHERE JSON_CONTAINS({}, '\"{}\"')", first_half.unwrap(), column, needle),
1673                            ValueType::String(needle) => self.query = format!("{} WHERE JSON_CONTAINS({}, '{}')", first_half.unwrap(), column, needle),
1674                            ValueType::Datetime(needle) => self.query = format!("{} WHERE JSON_CONTAINS({}, '{}')", first_half.unwrap(), column, needle),
1675                            _ => self.query = format!("{} WHERE JSON_CONTAINS({}, {})", first_half.unwrap(), column, needle)
1676                        },
1677                        _ => self.query = format!("{} WHERE JSON_CONTAINS({}, {})", first_half.unwrap(), column, needle)
1678                    }
1679                }
1680            },
1681            KeywordList::And => match path {
1682                Some(path) => {
1683                    let split_the_query = self.query.split(" AND ").collect::<Vec<&str>>();
1684
1685                    let length_of_the_split_the_query = split_the_query.len();
1686
1687                    match split_the_query.len() {
1688                        0 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1689                        1 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1690                        2 => match needle {
1691                            JsonValue::Initial(initial) => match initial {
1692                                ValueType::JsonString(needle) => self.query = format!("{} AND JSON_CONTAINS({}, '\"{}\"', '${}')", split_the_query[0], column, needle, path),
1693                                ValueType::String(needle) => self.query = format!("{} AND JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
1694                                ValueType::Datetime(needle) => self.query = format!("{} AND JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
1695                                _ => self.query = format!("{} AND JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
1696                            },
1697                            _ => self.query = format!("{} AND JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
1698                        },
1699                        _ => {
1700                            let mut concatenated_string = String::new();
1701
1702                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1703                                if index == 0 {
1704                                    concatenated_string = chunk.to_string();
1705                                } else if index + 1 != length_of_the_split_the_query {
1706                                    concatenated_string = format!("{} AND {} ", concatenated_string, chunk)
1707                                }
1708                            }
1709
1710                            match needle {
1711                                JsonValue::Initial(initial) => match initial {
1712                                    ValueType::JsonString(needle) => self.query = format!("{}AND JSON_CONTAINS({}, '\"{}\"', '${}')", concatenated_string, column, needle, path),
1713                                    ValueType::String(needle) => self.query = format!("{}AND JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
1714                                    ValueType::Datetime(needle) => self.query = format!("{}AND JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
1715                                    _ => self.query = format!("{}AND JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
1716                                },
1717                                _ => self.query = format!("{}AND JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
1718                            }
1719                        }
1720                    }
1721                },
1722                None => {
1723                    let split_the_query = self.query.split(" AND ").collect::<Vec<&str>>();
1724
1725                    let length_of_the_split_the_query = split_the_query.len();
1726
1727                    match split_the_query.len() {
1728                        0 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1729                        1 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1730                        2 => match needle {
1731                            JsonValue::Initial(initial) => match initial {
1732                                ValueType::JsonString(needle) => self.query = format!("{} AND JSON_CONTAINS({}, '\"{}\"')",  split_the_query[0], column, needle),
1733                                ValueType::String(needle) => self.query = format!("{} AND JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
1734                                ValueType::Datetime(needle) => self.query = format!("{} AND JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
1735                                _ => self.query = format!("{} AND JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
1736                            },
1737                            _ => self.query = format!("{} AND JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
1738                        },
1739                        _ => {
1740                            let mut concatenated_string = String::new();
1741
1742                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1743                                if index == 0 {
1744                                    concatenated_string = chunk.to_string();
1745                                } else if index + 1 != length_of_the_split_the_query {
1746                                    concatenated_string = format!("{} AND {} ", concatenated_string, chunk)
1747                                }
1748                            }
1749
1750                            match needle {
1751                                JsonValue::Initial(initial) => match initial {
1752                                    ValueType::JsonString(needle) => self.query = format!("{}AND JSON_CONTAINS({}, '\"{}\"')", concatenated_string, column, needle),
1753                                    ValueType::String(needle) => self.query = format!("{}AND JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
1754                                    ValueType::Datetime(needle) => self.query = format!("{}AND JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
1755                                    _ => self.query = format!("{}AND JSON_CONTAINS({}, {})", concatenated_string, column, needle)
1756                                },
1757                                _ => self.query = format!("{}AND JSON_CONTAINS({}, {})", concatenated_string, column, needle)
1758                            }
1759                        }
1760                    }
1761                }
1762            },
1763            KeywordList::Or => match path {
1764                Some(path) => {
1765                    let split_the_query = self.query.split(" OR ").collect::<Vec<&str>>();
1766
1767                    let length_of_the_split_the_query = split_the_query.len();
1768
1769                    match split_the_query.len() {
1770                        0 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
1771                        1 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
1772                        2 => match needle {
1773                            JsonValue::Initial(initial) => match initial {
1774                                ValueType::JsonString(needle) => self.query = format!("{} OR JSON_CONTAINS({}, '\"{}\"', '${}')", split_the_query[0], column, needle, path),
1775                                ValueType::String(needle) => self.query = format!("{} OR JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
1776                                ValueType::Datetime(needle) => self.query = format!("{} OR JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
1777                                _ => self.query = format!("{} OR JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
1778                            },
1779                            _ => self.query = format!("{} OR JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
1780                        },
1781                        _ => {
1782                            let mut concatenated_string = String::new();
1783
1784                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1785                                if index == 0 {
1786                                    concatenated_string = chunk.to_string();
1787                                } else if index + 1 != length_of_the_split_the_query {
1788                                    concatenated_string = format!("{} OR {} ", concatenated_string, chunk)
1789                                }
1790                            }
1791
1792                            match needle {
1793                                JsonValue::Initial(initial) => match initial {
1794                                    ValueType::JsonString(needle) => self.query = format!("{}OR JSON_CONTAINS({}, '\"{}\"', '${}')", concatenated_string, column, needle, path),
1795                                    ValueType::String(needle) => self.query = format!("{}OR JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
1796                                    ValueType::Datetime(needle) => self.query = format!("{}OR JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
1797                                    _ => self.query = format!("{}OR JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
1798                                },
1799                                _ => self.query = format!("{}OR JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
1800                            }
1801                        }
1802                    }
1803                },
1804                None => {
1805                    let split_the_query = self.query.split(" OR ").collect::<Vec<&str>>();
1806
1807                    let length_of_the_split_the_query = split_the_query.len();
1808
1809                    match split_the_query.len() {
1810                        0 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
1811                        1 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
1812                        2 => match needle {
1813                            JsonValue::Initial(initial) => match initial {
1814                                ValueType::JsonString(needle) => self.query = format!("{} OR JSON_CONTAINS({}, '\"{}\"')",  split_the_query[0], column, needle),
1815                                ValueType::String(needle) => self.query = format!("{} OR JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
1816                                ValueType::Datetime(needle) => self.query = format!("{} OR JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
1817                                _ => self.query = format!("{} OR JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
1818                            },
1819                            _ => self.query = format!("{} OR JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
1820                        },
1821                        _ => {
1822                            let mut concatenated_string = String::new();
1823
1824                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1825                                if index == 0 {
1826                                    concatenated_string = chunk.to_string();
1827                                } else if index + 1 != length_of_the_split_the_query {
1828                                    concatenated_string = format!("{} OR {} ", concatenated_string, chunk)
1829                                }
1830                            }
1831
1832                            match needle {
1833                                JsonValue::Initial(initial) => match initial {
1834                                    ValueType::JsonString(needle) => self.query = format!("{}OR JSON_CONTAINS({}, '\"{}\"')", concatenated_string, column, needle),
1835                                    ValueType::String(needle) => self.query = format!("{}OR JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
1836                                    ValueType::Datetime(needle) => self.query = format!("{}OR JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
1837                                    _ => self.query = format!("{}OR JSON_CONTAINS({}, {})", concatenated_string, column, needle)
1838                                },
1839                                _ => self.query = format!("{}OR JSON_CONTAINS({}, {})", concatenated_string, column, needle)
1840                            }
1841                        }
1842                    }
1843                }
1844            },
1845            _ => panic!("Wrong usage of '.json_contains()' method, it should be used later than either SELECT, WHERE, AND, OR keywords.")
1846        }
1847
1848        self.list.push(KeywordList::JsonContains);
1849
1850        self
1851    }
1852
1853    /// 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.
1854    /// 
1855    /// ```rust
1856    /// 
1857    /// use qubl::{QueryBuilder, ValueType, JsonValue};
1858    /// 
1859    /// fn main(){
1860    ///     let value = ValueType::String("blablabla.jpg".to_string());
1861    ///     let prop = vec![("name", &value)];
1862    /// 
1863    ///     let object = JsonValue::MysqlJsonObject(&prop);
1864    /// 
1865    ///     let query = QueryBuilder::select(["*"].to_vec()).unwrap()
1866    ///                              .table("users")
1867    ///                              .where_("pic", "=", ValueType::String("".to_string()))
1868    ///                              .not_json_contains("pic", object, Some(".name"))
1869    ///                              .finish();
1870    /// 
1871    ///     assert_eq!(query, "SELECT * FROM users WHERE NOT JSON_CONTAINS(pic, JSON_OBJECT('name', 'blablabla.jpg'), '$.name');")
1872    /// }
1873    /// 
1874    /// ```
1875    pub fn not_json_contains(&mut self, column: &str, needle: JsonValue, path: Option<&str>) -> &mut Self {
1876        match self.list.last().unwrap() {
1877            KeywordList::Select => match path {
1878                Some(path) => match needle {
1879                    JsonValue::Initial(initial) => match initial {
1880                        ValueType::JsonString(needle) => self.query = format!("SELECT NOT JSON_CONTAINS({}, '\"{}\"', '${}') FROM", column, needle, path),
1881                        ValueType::String(needle) => self.query = format!("SELECT NOT JSON_CONTAINS({}, '{}', '${}') FROM", column, needle, path),
1882                        ValueType::Datetime(needle) => self.query = format!("SELECT NOT JSON_CONTAINS({}, '{}', '${}') FROM", column, needle, path),
1883                        _ => self.query = format!("SELECT NOT JSON_CONTAINS({}, {}, '${}') FROM", column, needle, path),
1884                    },
1885                    _ => self.query = format!("SELECT NOT JSON_CONTAINS({}, {}, '${}') FROM", column, needle, path),
1886                }
1887                None => match needle {
1888                    JsonValue::Initial(initial) => match initial {
1889                        ValueType::JsonString(needle) => self.query = format!("SELECT NOT JSON_CONTAINS({}, '\"{}\"') FROM", column, needle),
1890                        ValueType::String(needle) => self.query = format!("SELECT NOT JSON_CONTAINS({}, '{}') FROM", column, needle),
1891                        ValueType::Datetime(needle) => self.query = format!("SELECT NOT JSON_CONTAINS({}, '{}') FROM", column, needle),
1892                        _ => self.query = format!("SELECT NOT JSON_CONTAINS({}, {}) FROM", column, needle)
1893                    },
1894                    _ => self.query = format!("SELECT NOT JSON_CONTAINS({}, {}) FROM", column, needle)
1895                }
1896            },
1897            KeywordList::Where => match path {
1898                Some(path) => {
1899                    let mut split_the_query = self.query.split(" WHERE ");
1900
1901                    let first_half = split_the_query.nth(0);
1902
1903                    match needle {
1904                        JsonValue::Initial(initial) => match initial {
1905                            ValueType::JsonString(needle) => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, '\"{}\"', '${}')", first_half.unwrap(), column, needle, path),
1906                            ValueType::String(needle) => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, '{}', '${}')", first_half.unwrap(), column, needle, path),
1907                            ValueType::Datetime(needle) => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, '{}', '${}')", first_half.unwrap(), column, needle, path),
1908                            _ => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, {}, '${}')", first_half.unwrap(), column, needle, path)
1909                        },
1910                        _ => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, {}, '${}')", first_half.unwrap(), column, needle, path)
1911                    }
1912                },
1913                None => {
1914                    let mut split_the_query = self.query.split(" WHERE ");
1915
1916                    let first_half = split_the_query.nth(0);
1917
1918                    match needle {
1919                        JsonValue::Initial(initial) => match initial {
1920                            ValueType::JsonString(needle) => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, '\"{}\"')", first_half.unwrap(), column, needle),
1921                            ValueType::String(needle) => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, '{}')", first_half.unwrap(), column, needle),
1922                            ValueType::Datetime(needle) => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, '{}')", first_half.unwrap(), column, needle),
1923                            _ => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, {})", first_half.unwrap(), column, needle)
1924                        },
1925                        _ => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, {})", first_half.unwrap(), column, needle)
1926                    }
1927                }
1928            },
1929            KeywordList::And => match path {
1930                Some(path) => {
1931                    let split_the_query = self.query.split(" AND ").collect::<Vec<&str>>();
1932
1933                    let length_of_the_split_the_query = split_the_query.len();
1934
1935                    match split_the_query.len() {
1936                        0 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1937                        1 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1938                        2 => match needle {
1939                            JsonValue::Initial(initial) => match initial {
1940                                ValueType::JsonString(needle) => self.query = format!("{} AND NOT JSON_CONTAINS({}, '\"{}\"', '${}')", split_the_query[0], column, needle, path),
1941                                ValueType::String(needle) => self.query = format!("{} AND NOT JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
1942                                ValueType::Datetime(needle) => self.query = format!("{} AND NOT JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
1943                                _ => self.query = format!("{} AND NOT JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
1944                            },
1945                            _ => self.query = format!("{} AND NOT JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
1946                        },
1947                        _ => {
1948                            let mut concatenated_string = String::new();
1949
1950                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1951                                if index == 0 {
1952                                    concatenated_string = chunk.to_string();
1953                                } else if index + 1 != length_of_the_split_the_query {
1954                                    concatenated_string = format!("{} AND {} ", concatenated_string, chunk)
1955                                }
1956                            }
1957
1958                            match needle {
1959                                JsonValue::Initial(initial) => match initial {
1960                                    ValueType::JsonString(needle) => self.query = format!("{}AND NOT JSON_CONTAINS({}, '\"{}\"', '${}')", concatenated_string, column, needle, path),
1961                                    ValueType::String(needle) => self.query = format!("{}AND NOT JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
1962                                    ValueType::Datetime(needle) => self.query = format!("{}AND NOT JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
1963                                    _ => self.query = format!("{}AND NOT JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
1964                                },
1965                                _ => self.query = format!("{}AND NOT JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
1966                            }
1967                        }
1968                    }
1969                },
1970                None => {
1971                    let split_the_query = self.query.split(" AND ").collect::<Vec<&str>>();
1972
1973                    let length_of_the_split_the_query = split_the_query.len();
1974
1975                    match split_the_query.len() {
1976                        0 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1977                        1 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1978                        2 => match needle {
1979                            JsonValue::Initial(initial) => match initial {
1980                                ValueType::JsonString(needle) => self.query = format!("{} AND NOT JSON_CONTAINS({}, '\"{}\"')",  split_the_query[0], column, needle),
1981                                ValueType::String(needle) => self.query = format!("{} AND NOT JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
1982                                ValueType::Datetime(needle) => self.query = format!("{} AND NOT JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
1983                                _ => self.query = format!("{} AND NOT JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
1984                            },
1985                            _ => self.query = format!("{} AND NOT JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
1986                        },
1987                        _ => {
1988                            let mut concatenated_string = String::new();
1989
1990                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1991                                if index == 0 {
1992                                    concatenated_string = chunk.to_string();
1993                                } else if index + 1 != length_of_the_split_the_query {
1994                                    concatenated_string = format!("{} AND {} ", concatenated_string, chunk)
1995                                }
1996                            }
1997
1998                            match needle {
1999                                JsonValue::Initial(initial) => match initial {
2000                                    ValueType::JsonString(needle) => self.query = format!("{}AND NOT JSON_CONTAINS({}, '\"{}\"')", concatenated_string, column, needle),
2001                                    ValueType::String(needle) => self.query = format!("{}AND NOT JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
2002                                    ValueType::Datetime(needle) => self.query = format!("{}AND NOT JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
2003                                    _ => self.query = format!("{}AND NOT JSON_CONTAINS({}, {})", concatenated_string, column, needle)
2004                                },
2005                                _ => self.query = format!("{}AND NOT JSON_CONTAINS({}, {})", concatenated_string, column, needle)
2006                            }
2007                        }
2008                    }
2009                }
2010            },
2011            KeywordList::Or => match path {
2012                Some(path) => {
2013                    let split_the_query = self.query.split(" OR ").collect::<Vec<&str>>();
2014
2015                    let length_of_the_split_the_query = split_the_query.len();
2016
2017                    match split_the_query.len() {
2018                        0 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
2019                        1 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
2020                        2 => match needle {
2021                            JsonValue::Initial(initial) => match initial {
2022                                ValueType::JsonString(needle) => self.query = format!("{} OR NOT JSON_CONTAINS({}, '\"{}\"', '${}')", split_the_query[0], column, needle, path),
2023                                ValueType::String(needle) => self.query = format!("{} OR NOT JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
2024                                ValueType::Datetime(needle) => self.query = format!("{} OR NOT JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
2025                                _ => self.query = format!("{} OR NOT JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
2026                            },
2027                            _ => self.query = format!("{} OR NOT JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
2028                        },
2029                        _ => {
2030                            let mut concatenated_string = String::new();
2031
2032                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
2033                                if index == 0 {
2034                                    concatenated_string = chunk.to_string();
2035                                } else if index + 1 != length_of_the_split_the_query {
2036                                    concatenated_string = format!("{} OR {} ", concatenated_string, chunk)
2037                                }
2038                            }
2039
2040                            match needle {
2041                                JsonValue::Initial(initial) => match initial {
2042                                    ValueType::JsonString(needle) => self.query = format!("{}OR NOT JSON_CONTAINS({}, '\"{}\"', '${}')", concatenated_string, column, needle, path),
2043                                    ValueType::String(needle) => self.query = format!("{}OR NOT JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
2044                                    ValueType::Datetime(needle) => self.query = format!("{}OR NOT JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
2045                                    _ => self.query = format!("{}OR NOT JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
2046                                },
2047                                _ => self.query = format!("{}OR NOT JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
2048                            }
2049                        }
2050                    }
2051                },
2052                None => {
2053                    let split_the_query = self.query.split(" OR ").collect::<Vec<&str>>();
2054
2055                    let length_of_the_split_the_query = split_the_query.len();
2056
2057                    match split_the_query.len() {
2058                        0 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
2059                        1 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
2060                        2 => match needle {
2061                            JsonValue::Initial(initial) => match initial {
2062                                ValueType::JsonString(needle) => self.query = format!("{} OR NOT JSON_CONTAINS({}, '\"{}\"')",  split_the_query[0], column, needle),
2063                                ValueType::String(needle) => self.query = format!("{} OR NOT JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
2064                                ValueType::Datetime(needle) => self.query = format!("{} OR NOT JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
2065                                _ => self.query = format!("{} OR NOT JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
2066                            },
2067                            _ => self.query = format!("{} OR NOT JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
2068                        },
2069                        _ => {
2070                            let mut concatenated_string = String::new();
2071
2072                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
2073                                if index == 0 {
2074                                    concatenated_string = chunk.to_string();
2075                                } else if index + 1 != length_of_the_split_the_query {
2076                                    concatenated_string = format!("{} OR {} ", concatenated_string, chunk)
2077                                }
2078                            }
2079
2080                            match needle {
2081                                JsonValue::Initial(initial) => match initial {
2082                                    ValueType::JsonString(needle) => self.query = format!("{}OR NOT JSON_CONTAINS({}, '\"{}\"')", concatenated_string, column, needle),
2083                                    ValueType::String(needle) => self.query = format!("{}OR NOT JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
2084                                    ValueType::Datetime(needle) => self.query = format!("{}OR NOT JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
2085                                    _ => self.query = format!("{}OR NOT JSON_CONTAINS({}, {})", concatenated_string, column, needle)
2086                                },
2087                                _ => self.query = format!("{}OR NOT JSON_CONTAINS({}, {})", concatenated_string, column, needle)
2088                            }
2089                        }
2090                    }
2091                }
2092            },
2093            _ => panic!("Wrong usage of '.not_json_contains()' method, it should be used later than either SELECT, WHERE, AND, OR keywords.")
2094        }
2095
2096        self.list.push(KeywordList::NotJsonContains);
2097
2098        self
2099    }
2100
2101    /// 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.
2102    /// 
2103    /// ```rust
2104    /// 
2105    /// use qubl::{QueryBuilder, ValueType, JsonValue};
2106    /// 
2107    /// fn main () {
2108    ///     let lesson = ("lesson", &ValueType::String("math".to_string()));
2109    ///     let point = ("point", &ValueType::Int32(100));
2110    ///
2111    ///     let values = vec![lesson, point];
2112    ///
2113    ///     let object = JsonValue::MysqlJsonObject(&values);
2114    ///
2115    ///     let query = QueryBuilder::update().unwrap()
2116    ///                                 .table("users")
2117    ///                                 .json_array_append("points", Some(""), object.clone())
2118    ///                                 .where_("id", "=", ValueType::Int8(1))
2119    ///                                 .finish();
2120    ///
2121    ///     assert_eq!("UPDATE users SET points = JSON_ARRAY_APPEND(points, '$', JSON_OBJECT('lesson', 'math', 'point', 100)) WHERE id = 1;", query);
2122    /// }
2123    /// 
2124    /// ```
2125    pub fn json_array_append(&mut self, column: &str, path: Option<&str>, object: JsonValue) -> &mut Self {
2126        match self.list.last() {
2127            Some(keyword) => match keyword {
2128                KeywordList::Set => {
2129                    match path {
2130                        Some(path) => match object {
2131                            JsonValue::Initial(initial) => match initial {
2132                                ValueType::JsonString(object) => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '${}', '\"{}\"')", self.query, column, column, path, object),
2133                                ValueType::String(object) => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '${}', '{}')", self.query, column, column, path, object),
2134                                ValueType::Datetime(object) => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '${}', '{}')", self.query, column, column, path, object),
2135                                _ => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '${}', {})", self.query, column, column, path, object),
2136                            },
2137                            _ => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '${}', {})", self.query, column, column, path, object),
2138                        }
2139                        None => match object {
2140                            JsonValue::Initial(initial) => match initial {
2141                                ValueType::JsonString(object) => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '$', '\"{}\"')", self.query, column, column, object),
2142                                ValueType::String(object) => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '$', '{}')", self.query, column, column, object),
2143                                ValueType::Datetime(object) => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '$', '{}')", self.query, column, column, object),
2144                                _ => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '$', {})", self.query, column, column, object)
2145                            },
2146                            _ => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '$', {})", self.query, column, column, object)
2147                        }
2148                    }
2149                },
2150                _ => {
2151                    match path {
2152                        Some(path) => match object {
2153                            JsonValue::Initial(initial) => match initial {
2154                                ValueType::JsonString(object) => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '${}', '\"{}\"')", self.query, column, column, path, object),
2155                                ValueType::String(object) => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '${}', '{}')", self.query, column, column, path, object),
2156                                ValueType::Datetime(object) => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '${}', '{}')", self.query, column, column, path, object),
2157                                _ => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '${}', {})", self.query, column, column, path, object),
2158                            },
2159                            _ => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '${}', {})", self.query, column, column, path, object),
2160                        }
2161                        None => match object {
2162                            JsonValue::Initial(initial) => match initial {
2163                                ValueType::JsonString(object) => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '$', '\"{}\"')", self.query, column, column, object),
2164                                ValueType::String(object) => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '$', '{}')", self.query, column, column, object),
2165                                ValueType::Datetime(object) => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '$', '{}')", self.query, column, column, object),
2166                                _ => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '$', {})", self.query, column, column, object)
2167                            },
2168                            _ => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '$', {})", self.query, column, column, object)
2169                        }
2170                    }
2171                }
2172            },
2173            None => panic!("it's impossible to came here!")
2174        }
2175
2176        self.list.push(KeywordList::JsonArrayAppend);
2177        self
2178    }
2179
2180    /// it adds "JSON_REMOVE()" function with it's synthax. You cannot pass empty strings to paths.
2181    /// 
2182    /// ```rust
2183    /// 
2184    /// use qubl::{QueryBuilder, ValueType};
2185    /// 
2186    /// fn main () {
2187    ///   let query = QueryBuilder::update().unwrap()
2188    ///                            .table("blogs")
2189    ///                            .json_remove("likes", vec!["[10]"])
2190    ///                            .where_("blog_id", "=", ValueType::Int32(20))
2191    ///                            .finish();
2192    ///
2193    ///   assert_eq!(query, "UPDATE blogs SET likes = JSON_REMOVE(likes, '$[10]') WHERE blog_id = 20;");
2194    /// }
2195    /// 
2196    /// ```
2197    pub fn json_remove(&mut self, column: &str, paths: Vec<&str>) -> &mut Self {
2198        match paths.iter().any(|path| *path == "") {
2199            true => panic!("Error: a value in the paths cannot be empty string, panicking..."),
2200            false => ()
2201        }
2202
2203        match self.list.last() {
2204            Some(keyword) => match keyword {
2205                KeywordList::Set => {
2206                    self.query = format!("{}, {} = JSON_REMOVE({}", self.query, column, column);
2207
2208                    for path in paths {
2209                        if path.starts_with("$") {
2210                            self.query = format!("{}, '${}'", self.query, path.replace("$", ""))
2211                        } else {
2212                            self.query = format!("{}, '${}'", self.query, path)
2213                        }
2214                    }
2215
2216                    self.query = format!("{})", self.query)
2217                },
2218                _ => {
2219                    self.query = format!("{} SET {} = JSON_REMOVE({}", self.query, column, column);
2220
2221                    for path in paths {
2222                        if path.starts_with("$") {
2223                            self.query = format!("{}, '${}'", self.query, path.replace("$", ""))
2224                        } else {
2225                            self.query = format!("{}, '${}'", self.query, path)
2226                        }
2227                    }
2228
2229                    self.query = format!("{})", self.query)
2230                }
2231            },
2232            None => panic!("it's impossible to came here!")
2233        }
2234
2235        self.list.push(KeywordList::JsonRemove);
2236        self
2237    }
2238
2239    /// It adds `JSON_SET()` function with it's synthax. It updates values with the specified path.
2240    /// 
2241    /// ```rust
2242    /// 
2243    /// use qubl::{QueryBuilder, ValueType, JsonValue};
2244    /// 
2245    /// fn main () {
2246    /// 
2247    /// let lesson = ("lesson", &ValueType::String("math".to_string()));
2248    /// let point = ("point", &ValueType::Int32(100));
2249    ///
2250    /// let values = vec![lesson, point];
2251    ///
2252    /// let object = JsonValue::MysqlJsonObject(&values);
2253    ///
2254    /// let query = QueryBuilder::update().unwrap()
2255    ///                          .table("users")
2256    ///                          .json_set("points", "[0]", object)
2257    ///                          .where_("id", "=", ValueType::Int32(1))
2258    ///                          .finish();
2259    ///
2260    /// assert_eq!("UPDATE users SET points = JSON_SET(points, '$[0]', JSON_OBJECT('lesson', 'math', 'point', 100)) WHERE id = 1;", query);
2261    /// 
2262    /// }
2263    /// 
2264    /// ```
2265    pub fn json_set(&mut self, column: &str, path: &str, value: JsonValue) -> &mut Self {
2266        match self.list.last() {
2267            Some(keyword) => match keyword {
2268                KeywordList::Set => match value {
2269                    JsonValue::Initial(initial) => match initial {
2270                        ValueType::JsonString(value) => self.query = format!("{}, {} = JSON_SET({}, '${}', '\"{}\"')", self.query, column, column, path, value),
2271                        ValueType::String(value) => self.query = format!("{}, {} = JSON_SET({}, '${}', '{}')", self.query, column, column, path, value),
2272                        ValueType::Datetime(value) => self.query = format!("{}, {} = JSON_SET({}, '${}', '{}')", self.query, column, column, path, value),
2273                        _ => self.query = format!("{}, {} = JSON_SET({}, '${}', {})", self.query, column, column, path, value),
2274                    },
2275                    _ => self.query = format!("{}, {} = JSON_SET({}, '${}', {})", self.query, column, column, path, value),
2276                }
2277                _ => match value {
2278                    JsonValue::Initial(initial) => match initial {
2279                        ValueType::JsonString(value) => self.query = format!("{} SET {} = JSON_SET({}, '${}', '\"{}\"')", self.query, column, column, path, value),
2280                        ValueType::String(value) => self.query = format!("{} SET {} = JSON_SET({}, '${}', '{}')", self.query, column, column, path, value),
2281                        ValueType::Datetime(value) => self.query = format!("{} SET {} = JSON_SET({}, '${}', '{}')", self.query, column, column, path, value),
2282                        _ => self.query = format!("{} SET {} = JSON_SET({}, '${}', {})", self.query, column, column, path, value)
2283                    },
2284                    _ => self.query = format!("{} SET {} = JSON_SET({}, '${}', {})", self.query, column, column, path, value)
2285                }
2286            },
2287            None => panic!("it's impossible to came here!")
2288        }
2289
2290        self.list.push(KeywordList::JsonSet);
2291        self
2292    }
2293
2294    /// It adds `JSON_REPLACE()` function with it's synthax. It updates values with the specified path.
2295    /// 
2296    /// ```rust
2297    /// 
2298    /// use qubl::{QueryBuilder, ValueType, JsonValue};
2299    /// 
2300    /// fn main () {
2301    /// 
2302    /// let value = ValueType::Int32(100);
2303    /// let value = JsonValue::Initial(&value);
2304    ///
2305    /// let query = QueryBuilder::update().unwrap()
2306    ///                          .table("users")
2307    ///                          .json_replace("points", "[0].point", value)
2308    ///                          .where_("id", "=", ValueType::Int32(1))
2309    ///                          .finish();
2310    ///
2311    /// assert_eq!("UPDATE users SET points = JSON_REPLACE(points, '$[0].point', 100) WHERE id = 1;", query)
2312    /// 
2313    /// }
2314    /// 
2315    /// ```
2316    pub fn json_replace(&mut self, column: &str, path: &str, value: JsonValue) -> &mut Self {
2317        match self.list.last() {
2318            Some(keyword) => match keyword {
2319                KeywordList::Set => match value {
2320                    JsonValue::Initial(initial) => match initial {
2321                        ValueType::JsonString(value) => self.query = format!("{}, {} = JSON_REPLACE({}, '${}', '\"{}\"')", self.query, column, column, path, value),
2322                        ValueType::String(value) => self.query = format!("{}, {} = JSON_REPLACE({}, '${}', '{}')", self.query, column, column, path, value),
2323                        ValueType::Datetime(value) => self.query = format!("{}, {} = JSON_REPLACE({}, '${}', '{}')", self.query, column, column, path, value),
2324                        _ => self.query = format!("{}, {} = JSON_REPLACE({}, '${}', {})", self.query, column, column, path, value),
2325                    },
2326                    _ => self.query = format!("{}, {} = JSON_REPLACE({}, '${}', {})", self.query, column, column, path, value),
2327                }
2328                _ => match value {
2329                    JsonValue::Initial(initial) => match initial {
2330                        ValueType::JsonString(value) => self.query = format!("{} SET {} = JSON_REPLACE({}, '${}', '\"{}\"')", self.query, column, column, path, value),
2331                        ValueType::String(value) => self.query = format!("{} SET {} = JSON_REPLACE({}, '${}', '{}')", self.query, column, column, path, value),
2332                        ValueType::Datetime(value) => self.query = format!("{} SET {} = JSON_REPLACE({}, '${}', '{}')", self.query, column, column, path, value),
2333                        _ => self.query = format!("{} SET {} = JSON_REPLACE({}, '${}', {})", self.query, column, column, path, value)
2334                    },
2335                    _ => self.query = format!("{} SET {} = JSON_REPLACE({}, '${}', {})", self.query, column, column, path, value)
2336                }
2337            },
2338            None => panic!("it's impossible to came here!")
2339        }
2340
2341        self.list.push(KeywordList::JsonSet);
2342        self
2343    }
2344
2345    /// finishes the query and returns the result as string.
2346    pub fn finish(&self) -> String {
2347        return format!("{};", self.query);
2348    }
2349
2350    /// gives you an immutable copy of that instance, just for case if you need to share and potentially mutate it across threads.
2351    pub fn copy(&mut self) -> Self {
2352        Self {
2353            query: self.query.clone(),
2354            table: self.table.clone(),
2355            qtype: self.qtype.clone(),
2356            list: self.list.clone(),
2357            hq: self.hq
2358        }
2359    }
2360
2361    fn load_hqs() -> [&'a str; 26] {
2362        [";", "; drop", "admin' #", "admin'/*", "; union", "or 1 = 1",
2363        "or 1 = 1#", "or 1 = 1/*", "or true = true", "or false = false", "or '1' = '1'", "or '1' = '1'#",
2364        "or '1' = '1'/*", "; sleep(", "--", "drop table", "drop schema", "select if", "union select",
2365        "union all", "exec", "master..", "masters..", "information_schema", "load_file", "alter user"]
2366    }
2367
2368    fn sanitize_column(&mut self, column: &str)  -> std::result::Result<(), std::io::Error>  {
2369        match self.hq {
2370            Some(hqs) => {
2371                for _hq in hqs.iter() {
2372                    if &column == _hq {
2373                        return Err(std::io::Error::new(std::io::ErrorKind::PermissionDenied, "You cannot run arbitrary queries"))
2374                    }
2375                }
2376            },
2377            None => return Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "Your input is invalid."))
2378        }
2379
2380        Ok(())
2381    }
2382
2383    /// checks the inputs for potential sql injection patterns and throws error if they exist.
2384    fn sanitize_columns(columns: &Vec<&str>, hqs: [&'a str; 26]) -> std::result::Result<(), std::io::Error> {
2385        if columns.len() == 1 && columns[0] == "" {
2386            return Ok(());
2387        };
2388
2389        for column in columns.iter() {
2390            for hq in hqs.iter() {
2391                if column == hq {
2392                    return Err(std::io::Error::new(std::io::ErrorKind::PermissionDenied, "You cannot run arbitrary queries"))
2393                }
2394            }
2395        }
2396
2397        return Ok(())
2398    }
2399
2400    fn sanitize_inputs(inputs: &Vec<ValueType>, hqs: [&'a str; 26]) -> std::result::Result<(), std::io::Error> {
2401        for input in inputs.iter() {
2402            match input {
2403                ValueType::String(string) | ValueType::Datetime(string) => {
2404                    for hq in hqs.iter() {
2405                        if &string == hq {
2406                            return Err(std::io::Error::new(std::io::ErrorKind::PermissionDenied, "You cannot run arbitrary queries"))
2407                        }
2408                    }
2409                },
2410                _ => continue
2411            }
2412        }
2413
2414        return Ok(())
2415    }
2416
2417    fn sanitize_input(&mut self, input: &ValueType) -> std::result::Result<(), std::io::Error> {
2418        match input {
2419            ValueType::String(string) | ValueType::Datetime(string) => {
2420                match self.hq {
2421                    Some(hqs) => {
2422                        for hq in hqs.iter() {
2423                            if &string == hq {
2424                                return Err(std::io::Error::new(std::io::ErrorKind::PermissionDenied, "You cannot run arbitrary queries"))
2425                            }
2426                        }
2427                    },
2428                    None => return Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "Your input is invalid."))
2429                }
2430            },
2431            _ => return Ok(())
2432        };
2433
2434        Ok(())
2435    }
2436
2437    fn sanitize_mark(input: &str) -> std::result::Result<(), std::io::Error> {
2438        return match input {
2439            "=" | "<" | ">" | "<=" | ">=" | "!=" | "<>" => Ok(()),
2440            _ => Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "comparison operators cannot be other than =, <, >, <=,  >=, != or <>."))
2441        }
2442    }
2443
2444    fn sanitize_str(&mut self, input: &str) -> std::result::Result<(), std::io::Error> {
2445        match self.hq {
2446            Some(hqs) => {
2447                for hq in hqs.iter() {
2448                    if *hq == input {
2449                        return Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "comparison operators cannot be other than =, <, >, <=,  >=, != or <>."))
2450                    }
2451                }
2452            },
2453            None => return Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "comparison operators cannot be other than =, <, >, <=,  >=, != or <>."))
2454        }
2455
2456        Ok(())
2457    }
2458}
2459
2460/// Struct that benefits you to create and use schema's.
2461#[derive(Debug, Clone)]
2462pub struct SchemaBuilder {
2463    pub query: String,
2464    pub schema: String,
2465    pub list: Vec<KeywordList>
2466}
2467
2468/// implementations fon SchemaBuilder
2469impl SchemaBuilder {
2470    pub fn create(name: &str) -> std::result::Result<Self, std::io::Error> {
2471        if name.contains("!") ||
2472           name.contains("-") ||
2473           name.contains("=") ||
2474           name.contains("+") ||
2475           name.contains("%") ||
2476           name.contains("$") ||
2477           name.contains("&") ||
2478           name.contains("#") ||
2479           name.contains("[") ||
2480           name.contains("]") ||
2481           name.contains("{") ||
2482           name.contains("}") ||
2483           name.contains(":") ||
2484           name.contains(";") ||
2485           name.contains("'") ||
2486           name.contains("\"") ||
2487           name.contains(",") ||
2488           name.contains(".") {
2489                return Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "Schame name cannot include these characters: '!', '-', '=', '+', '%', '$', '&', '#', '[', ']', '{', '}', ':', ';', '\"', \"'\", '.', ','"))
2490        }
2491
2492        Ok(Self {
2493            query: format!("CREATE DATABASE {}", name),
2494            schema: name.to_string(),
2495            list: vec![KeywordList::Create]
2496        })
2497    }
2498
2499    pub fn use_another_schema(name: &str) -> std::result::Result<Self, std::io::Error> {
2500        if name.contains("!") ||
2501        name.contains("-") ||
2502        name.contains("=") ||
2503        name.contains("+") ||
2504        name.contains("%") ||
2505        name.contains("$") ||
2506        name.contains("&") ||
2507        name.contains("#") ||
2508        name.contains("[") ||
2509        name.contains("]") ||
2510        name.contains("{") ||
2511        name.contains("}") ||
2512        name.contains(":") ||
2513        name.contains(";") ||
2514        name.contains("'") ||
2515        name.contains("\"") ||
2516        name.contains(",") ||
2517        name.contains(".") {
2518             return Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "Schame name cannot include these characters: '!', '-', '=', '+', '%', '$', '&', '#', '[', ']', '{', '}', ':', ';', '\"', \"'\", '.', ','"))
2519        }
2520
2521        Ok(Self {
2522            query: format!("USE {}", name),
2523            schema: name.to_string(),
2524            list: vec![KeywordList::Use, KeywordList::Create]
2525        })
2526    }
2527
2528    pub fn if_not_exists(&mut self) -> &mut Self {
2529        match self.list[0] {
2530            KeywordList::Create => (),
2531            KeywordList::Table => (),
2532            _ => panic!("if_not_exists method cannot be used without Create or Table queries")
2533        }
2534
2535        let split_the_query =  self.query.split(" DATABASE ").collect::<Vec<&str>>();
2536        self.query = format!("{} DATABASE IF NOT EXISTS {}", split_the_query[0], split_the_query[1]);
2537
2538        self.list.insert(0, KeywordList::IfNotExist);
2539        self
2540    }
2541
2542    pub fn use_schema(&mut self, name: Option<&str>) -> &mut Self {
2543        match name {
2544            Some(schema_name) => {
2545                self.query = format!("USE {}", schema_name)
2546            },
2547            None => {
2548                self.query = format!("USE {}", self.schema);
2549            }
2550        }
2551
2552        self
2553    }
2554
2555    pub fn finish(&self) -> String {
2556        return format!("{};", self.query)
2557    }
2558}
2559
2560/// Struct that benefits you to create Tables. Currently incomplete thoug.
2561#[derive(Debug, Clone)]
2562pub struct TableBuilder {
2563    pub query: String,
2564    pub name: String,
2565    pub schema: String,
2566    pub all: Vec<String>,
2567}
2568
2569/// Struct that benefits to define a foreign key.
2570#[derive(Debug, Clone)]
2571pub struct ForeignKey {
2572    pub first: ForeignKeyItem,
2573    pub second: ForeignKeyItem,
2574    pub on_delete: Option<ForeignKeyActions>,
2575    pub on_update: Option<ForeignKeyActions>,
2576    pub constraint: Option<String>
2577}
2578
2579/// Struct that benefits you to add a foreign key item to a foreign key.
2580#[derive(Debug, Clone)]
2581pub struct ForeignKeyItem {
2582    pub table: String,
2583    pub column: String
2584}
2585
2586/// implementations for TableBuilder
2587impl TableBuilder {
2588    pub fn create(schema_name: &str, table_name: &str) -> Self {
2589        return Self {
2590            query: format!("CREATE TABLE {} (", table_name),
2591            schema: schema_name.to_string(),
2592            name: table_name.to_string(),
2593            all: vec![]
2594        }
2595    }
2596
2597    pub fn if_not_exists(&mut self) -> &mut Self {
2598        self.query = format!("{}IF NOT EXISTS (", self.query.replace("(", ""));
2599
2600        self
2601    }
2602
2603    pub fn add_column(&mut self, column_name: &str) -> &mut Self {
2604        if self.query.ends_with("(") {
2605            self.query = format!("{}{}", self.query, column_name)
2606        } else {
2607            self.query = format!("{}, {}", self.query, column_name)
2608        }
2609
2610        self
2611    }
2612
2613    pub fn col_type(&mut self, type_name: &str) -> &mut Self {
2614        if self.query.ends_with("(") {
2615            panic!("Cannot add type before defining a column name.")
2616        }
2617
2618        self.query = format!("{} {}", self.query, type_name);
2619
2620        self
2621    }
2622
2623    pub fn null(&mut self) -> &mut Self {
2624        self.query = format!("{} NULL", self.query);
2625
2626        self
2627    }
2628
2629    pub fn not_null(&mut self) -> &mut Self {
2630        self.query = format!("{} NOT NULL", self.query);
2631
2632        self
2633    }
2634
2635    pub fn auto_increment(&mut self) -> &mut Self {
2636        self.query = format!("{} AUTO_INCREMENT", self.query);
2637
2638        self
2639    }
2640
2641    pub fn primary_key(&mut self) -> &mut Self {
2642        if self.query.contains("PRIMARY KEY") {
2643            panic!("A table cannot have two primary keys.")
2644        }
2645
2646        self.query = format!("{} PRIMARY KEY", self.query);
2647
2648        self
2649    }
2650
2651    pub fn default(&mut self, value: ValueType) -> &mut Self {
2652        let split_the_query = self.query.clone();
2653        let split_the_query = split_the_query.split(", ").collect::<Vec<&str>>();
2654
2655        let last_query = split_the_query[split_the_query.len() - 1];
2656
2657        if last_query.contains("INT") || 
2658           last_query.contains("TINYINT") ||
2659           last_query.contains("SMALLINT") ||
2660           last_query.contains("MEDIUMINT") ||
2661           last_query.contains("BIGINT") ||
2662           last_query.contains("BIT") ||
2663           last_query.contains("SERIAL") {
2664            match value {
2665                ValueType::Int8(int) => self.query = format!("{} DEFAULT {}", self.query, int),
2666                ValueType::Int16(int) => self.query = format!("{} DEFAULT {}", self.query, int),
2667                ValueType::Int32(int) => self.query = format!("{} DEFAULT {}", self.query, int),
2668                ValueType::Int64(int) => self.query = format!("{} DEFAULT {}", self.query, int),
2669                ValueType::Uint8(int) => self.query = format!("{} DEFAULT {}", self.query, int),
2670                ValueType::Uint16(int) => self.query = format!("{} DEFAULT {}", self.query, int),
2671                ValueType::Uint32(int) => self.query = format!("{} DEFAULT {}", self.query, int),
2672                ValueType::Uint64(int) => self.query = format!("{} DEFAULT {}", self.query, int),
2673                ValueType::Usize(int) => self.query = format!("{} DEFAULT {}", self.query, int),
2674                ValueType::Float32(int) => self.query = format!("{} DEFAULT {}", self.query, int),
2675                ValueType::Float64(int) => self.query = format!("{} DEFAULT {}", self.query, int),
2676                _ => 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.")
2677            }
2678        }
2679
2680        if last_query.contains("BOOL") || 
2681           last_query.contains("BOOLEAN") {
2682            match value {
2683                ValueType::Boolean(boolean) => self.query = format!("{} DEFAULT {}", self.query, boolean),
2684                _ => panic!("If your column type is BOOLEAN, you have to write either true or false.")
2685            }    
2686        }
2687
2688        if last_query.contains("CHAR") ||
2689           last_query.contains("VARCHAR") ||
2690           last_query.contains("TEXT") ||
2691           last_query.contains("TINYTEXT") ||
2692           last_query.contains("MEDIUMTEXT") ||
2693           last_query.contains("LONGTEXT") ||
2694           last_query.contains("BINARY") ||
2695           last_query.contains("VARBINARY") {
2696            match value {
2697                ValueType::String(ref text) => self.query = format!("{} DEFAULT '{}'", self.query, text),
2698                _ => 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.")
2699            }
2700        }
2701
2702        if last_query.contains("DATETIME") ||
2703           last_query.contains("TIMESTAMP") {
2704            match value {
2705                ValueType::Datetime(datetime) => self.query = format!("{} DEFAULT {}", self.query, datetime),
2706                _ => 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.")
2707            }
2708        }
2709
2710        self
2711    }
2712
2713    pub fn unique(&mut self) -> &mut Self {
2714        self.query = format!("{} UNIQUE", self.query);
2715
2716        self
2717    }
2718
2719    pub fn check(&mut self, condition: &str) -> &mut Self {
2720        self.query = format!("{} CHECK({})", self.query, condition);
2721
2722        self
2723    }
2724
2725    pub fn character_set(&mut self, character_set: &str) -> &mut Self {
2726        self.query = format!("{} CHARACTER SET {}", self.query, character_set);
2727
2728        self
2729    }
2730
2731    pub fn foreign_key(&mut self, opts: ForeignKey) -> &mut Self {
2732        if self.query.starts_with("ALTER TABLE") {
2733            match opts.constraint {
2734                Some(constraint) => self.query = format!("{}, ADD CONSTRAINT {} FOREIGN KEY ({})", self.query, constraint, opts.first.column),
2735                None => self.query = format!("{}, ADD FOREIGN KEY ({})", self.query, opts.first.column)
2736            }
2737            
2738        } else {
2739            match opts.constraint {
2740                Some(constraint) => self.query = format!("{}, CONSTRAINT {} FOREIGN KEY ({})", self.query, constraint, opts.first.column),
2741                None => self.query = format!("{}, FOREIGN KEY ({})", self.query, opts.first.column)
2742            }
2743        }
2744
2745        self.query = format!("{} REFERENCES {}({})", self.query, opts.second.table, opts.second.column);
2746
2747        match opts.on_delete {
2748            Some(on_delete_opt) => self.query = format!("{} ON DELETE {}", self.query, on_delete_opt),
2749            None => ()
2750        }
2751
2752        match opts.on_update {
2753            Some(on_update_opt) => self.query = format!("{} ON UPDATE {}", self.query, on_update_opt),
2754            None => ()
2755        }
2756
2757        self
2758    }
2759
2760    pub fn unsigned(&mut self) -> &mut Self {
2761        self.query = format!("{} UNSIGNED", self.query);
2762
2763        self
2764    }
2765
2766    pub fn zerofill(&mut self) -> &mut Self {
2767        self.query = format!("{} ZEROFILL", self.query);
2768
2769        self
2770    }
2771
2772    pub fn enum_sql(&mut self, enum_vec: Vec<&str>) -> &mut Self {
2773        match enum_vec.len() {
2774            0 => panic!("enum_vec argument cannot be an empty vector"),
2775            _ => ()
2776        }
2777        
2778        self.query = format!("{} ENUM(", self.query);
2779
2780        let length_of_enum_vec = enum_vec.len();
2781        for (index, item) in enum_vec.into_iter().enumerate() {
2782            if index + 1 == length_of_enum_vec {
2783                self.query = format!("{}'{}'", self.query, item)
2784            } else {
2785                self.query = format!("{}'{}', ", self.query, item)
2786            }
2787        }
2788
2789        self
2790    }
2791
2792    pub fn generated_always(&mut self, condition: &str) -> &mut Self {
2793        self.query = format!("{} GENERATED ALWAYS AS {}", self.query, condition);
2794
2795        self
2796    }
2797
2798    pub fn virtual_sql(&mut self) -> &mut Self {
2799        self.query = format!("{} VIRTUAL", self.query);
2800
2801        self
2802    }
2803
2804    pub fn stored(&mut self) -> &mut Self {
2805        self.query = format!("{} STORED", self.query);
2806
2807        self
2808    }
2809
2810    pub fn spatial(&mut self) -> &mut Self {
2811        self.query = format!("{} SPATIAL", self.query);
2812
2813        self
2814    }
2815
2816    pub fn generated(&mut self) -> &mut Self {
2817        self.query = format!("{} GENERATED", self.query);
2818
2819        self
2820    }
2821
2822    pub fn index(&mut self, indexes: Vec<&str>) -> &mut Self {
2823        let length_of_indexes = indexes.len();
2824
2825        match length_of_indexes {
2826            0 => panic!("There is no index here."),
2827            1 => self.query = format!("{}, INDEX({})", self.query, indexes[0]),
2828            _ => {
2829                for (i, index) in indexes.into_iter().enumerate() {
2830                    if i + 1 == length_of_indexes {
2831                        self.query = format!("{}{}", self.query, index);
2832
2833                        continue;
2834                    }
2835
2836                    if i == 0 {
2837                        self.query = format!("{}, INDEX ({}, ", self.query, index);
2838
2839                        continue;
2840                    }
2841
2842                    self.query = format!("{}{}, ", self.query, index)
2843                }
2844            }
2845        }
2846
2847        self
2848    }
2849
2850    pub fn comment(&mut self, comment: &str) -> &mut Self {
2851        self.query = format!("{} COMMENT '{}'", self.query, comment);
2852
2853        self
2854    }
2855
2856    pub fn default_on_null(&mut self, value: ValueType) -> &mut Self {
2857        match value {
2858            ValueType::String(text) => self.query = format!("{} DEFAULT {} ON NULL", self.query, text),
2859            _ => self.query = format!("{} DEFAULT {} ON NULL", self.query, value),
2860        }
2861
2862        self
2863    }
2864
2865    pub fn invisible(&mut self) -> &mut Self {
2866        self.query = format!("{} INVISIBLE", self.query);
2867
2868        self
2869    }
2870
2871    pub fn custom_query(&mut self, query: &str) -> &mut Self {
2872        self.query = format!("{} {}", self.query, query);
2873
2874        self
2875    }
2876
2877    pub fn finish(&mut self) -> String {
2878        return format!("{});", self.query)
2879    }
2880}
2881
2882/// KeywordList enum. It helps to syntactically correcting the queries. 
2883#[derive(Debug, Clone, PartialEq)]
2884pub enum KeywordList {
2885    Select, Update, Delete, Insert, Count, Table, Where, Or, And, Set, 
2886    Finish, OrderBy, GroupBy, Having, Like, Limit, Offset, IfNotExist, Create, Use, WhereIn, 
2887    WhereNotIn, AndIn, AndNotIn, OrIn, OrNotIn, JsonExtract, JsonContains, NotJsonContains, JsonArrayAppend, JsonRemove, JsonSet, JsonReplace, 
2888    Field, Union, UnionAll, Timezone, GlobalTimezone, InnerJoin, LeftJoin, RightJoin
2889}
2890
2891/// QueryType enum. It helps to detect the type of a query with more optimized way when is needed.
2892#[derive(Debug, Clone)]
2893pub enum QueryType {
2894    Select, Update, Delete, Insert, Null, Create, Count
2895}
2896
2897/// ValueType enum. It benefits to detect and format the value with optimized way when you have to work with exact column values. 
2898#[derive(Debug, Clone)]
2899pub enum ValueType {
2900    String(String), Datetime(String), Null, Boolean(bool), Int32(i32), Int16(i16), Int8(i8), Int64(i64), Int128(i128),
2901    Uint8(u8), Uint16(u16), Uint32(u32), Uint64(u64), Usize(usize), Float32(f32), Float64(f64),
2902    EpochTime(i64), JsonString(String)
2903}
2904
2905impl std::fmt::Display for ValueType {
2906    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
2907        match self {
2908            ValueType::String(string) => write!(f, "'{}'", string),
2909            ValueType::JsonString(string) => write!(f, "\"{}\"", string),
2910            ValueType::Datetime(datetime) => match datetime.as_str() {
2911                "CURRENT_TIMESTAMP" | "UNIX_TIMESTAMP" | "CURRENT_DATE" | "CURRENT_TIME" | "NOW()" | "CURDATE()" | "CURTIME()" => write!(f, "{}", datetime),
2912                _ => write!(f, "'{}'", datetime)
2913            },
2914            ValueType::Null => write!(f, "NULL"),
2915            ValueType::Boolean(val) => write!(f, "{}", val),
2916            ValueType::Int8(val) => write!(f, "{}", val),
2917            ValueType::Int16(val) => write!(f, "{}", val),
2918            ValueType::Int32(val) => write!(f, "{}", val),
2919            ValueType::Int64(val) => write!(f, "{}", val),
2920            ValueType::Int128(val) => write!(f, "{}", val),
2921            ValueType::Usize(val) => write!(f, "{}", val),
2922            ValueType::Uint8(val) => write!(f, "{}", val),
2923            ValueType::Uint16(val) => write!(f, "{}", val),
2924            ValueType::Uint32(val) => write!(f, "{}", val),
2925            ValueType::Uint64(val) => write!(f, "{}", val),
2926            ValueType::Float32(val) => write!(f, "{}", val),
2927            ValueType::Float64(val) => write!(f, "{}", val),
2928            ValueType::EpochTime(val) => write!(f, "FROM_UNIXTIME({})", val),
2929        }
2930    }
2931}
2932
2933impl From<String> for ValueType { fn from(value: String) -> Self { ValueType::String(value) } }
2934impl From<bool> for ValueType { fn from(value: bool) -> Self { ValueType::Boolean(value) } }
2935impl From<i8> for ValueType { fn from(value: i8) -> Self { ValueType::Int8(value) } }
2936impl From<i16> for ValueType { fn from(value: i16) -> Self { ValueType::Int16(value) } }
2937impl From<i32> for ValueType { fn from(value: i32) -> Self { ValueType::Int32(value) } }
2938impl From<i64> for ValueType { fn from(value: i64) -> Self { ValueType::Int64(value) } }
2939impl From<i128> for ValueType { fn from(value: i128) -> Self { ValueType::Int128(value) } }
2940impl From<usize> for ValueType { fn from(value: usize) -> Self { ValueType::Usize(value) } }
2941impl From<u8> for ValueType { fn from(value: u8) -> Self { ValueType::Uint8(value) } }
2942impl From<u16> for ValueType { fn from(value: u16) -> Self { ValueType::Uint16(value) } }
2943impl From<u32> for ValueType { fn from(value: u32) -> Self { ValueType::Uint32(value) } }
2944impl From<u64> for ValueType { fn from(value: u64) -> Self { ValueType::Uint64(value) } }
2945impl From<f32> for ValueType { fn from(value: f32) -> Self { ValueType::Float32(value) } }
2946impl From<f64> for ValueType { fn from(value: f64) -> Self { ValueType::Float64(value) } }
2947
2948
2949impl Into<String> for ValueType {
2950    fn into(self) -> String {
2951        match self {
2952            ValueType::String(text) => text,
2953            ValueType::Datetime(datetime) => datetime,
2954            _ => panic!("you cannot convert a ValueType to string unless it's not a String or Datetime variant.")
2955        }
2956    }
2957}
2958
2959impl Into<bool> for ValueType {
2960    fn into(self) -> bool {
2961        match self {
2962            ValueType::Boolean(val) => val,
2963            ValueType::String(text) => match text.as_str() {
2964                "false" | "" | "\0" | "0" => false,
2965                _ => true,
2966            }
2967            ValueType::Null => false,
2968            ValueType::Int8(val) => match val == 0 {
2969                false => true,
2970                true => false
2971            },
2972            ValueType::Int16(val) => match val == 0 {
2973                false => true,
2974                true => false
2975            },
2976            ValueType::Int32(val) => match val == 0 {
2977                false => true,
2978                true => false
2979            },
2980            ValueType::Int64(val) => match val == 0 {
2981                false => true,
2982                true => false
2983            },
2984            ValueType::Int128(val) => match val == 0 {
2985                false => true,
2986                true => false
2987            },
2988            ValueType::Uint8(val) => match val == 0 {
2989                false => true,
2990                true => false
2991            },
2992            ValueType::Uint16(val) => match val == 0 {
2993                false => true,
2994                true => false
2995            },
2996            ValueType::Uint32(val) => match val == 0 {
2997                false => true,
2998                true => false
2999            },
3000            ValueType::Uint64(val) => match val == 0 {
3001                false => true,
3002                true => false
3003            },
3004            ValueType::Float32(val) => match val == 0.0 {
3005                false => true,
3006                true => false
3007            },
3008            ValueType::Float64(val) => match val == 0.0 {
3009                false => true,
3010                true => false
3011            },
3012            _ => panic!("invalid conversion")
3013        }
3014    }
3015}
3016
3017impl Into<f32> for ValueType {
3018    fn into(self) -> f32 {
3019        match self {
3020            ValueType::Float32(num) => num,
3021            ValueType::Float64(num) => num as f32,
3022            _ => panic!("invalid conversion")
3023        }
3024    }
3025}
3026
3027impl Into<f64> for ValueType {
3028    fn into(self) -> f64 {
3029        match self {
3030            ValueType::Float32(num) => num as f64,
3031            ValueType::Float64(num) => num,
3032            _ => panic!("invalid conversion")
3033        }
3034    }
3035}
3036
3037impl Into<i8> for ValueType {
3038    fn into(self) -> i8 {
3039        match self {
3040            ValueType::Int8(num) => num,
3041            ValueType::Int16(num) => match num > 128 || num < -128 {
3042                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3043                false => num as i8
3044            },
3045            ValueType::Int32(num) => match num > 128 || num < -128 {
3046                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3047                false => num as i8
3048            },
3049            ValueType::Int64(num) => match num > 128 || num < -128 {
3050                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3051                false => num as i8
3052            },
3053            ValueType::Int128(num) => match num > 128 || num < -128 {
3054                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3055                false => num as i8
3056            }
3057            ValueType::Uint8(num) => match num > 128 {
3058                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3059                false => num as i8
3060            },
3061            ValueType::Uint16(num) => match num > 128 {
3062                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3063                false => num as i8
3064            },
3065            ValueType::Uint32(num) => match num > 128 {
3066                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3067                false => num as i8
3068            },
3069            ValueType::Usize(num) => match num > 128 {
3070                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3071                false => num as i8
3072            },
3073            ValueType::Uint64(num) => match num > 128 {
3074                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3075                false => num as i8
3076            },
3077            _ => panic!("you cannot convert non numeric values into numeric ones.")
3078        }
3079    }
3080}
3081
3082impl Into<i16> for ValueType {
3083    fn into(self) -> i16 {
3084        match self {
3085            ValueType::Int8(num) => num as i16,
3086            ValueType::Int16(num) => num,
3087            ValueType::Int32(num) => match num > 32_768 || num < -32_768 {
3088                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3089                false => num as i16
3090            },
3091            ValueType::Int64(num) => match num > 32_768 || num < -32_768 {
3092                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3093                false => num as i16
3094            },
3095            ValueType::Int128(num) => match num > 32_768 || num < -32_768 {
3096                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3097                false => num as i16
3098            }
3099            ValueType::Uint8(num) => num as i16,
3100            ValueType::Uint16(num) => match num > 32_768 {
3101                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3102                false => num as i16
3103            },
3104            ValueType::Uint32(num) => match num > 32_768 {
3105                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3106                false => num as i16
3107            },
3108            ValueType::Usize(num) => match num > 32_768 {
3109                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3110                false => num as i16
3111            },
3112            ValueType::Uint64(num) => match num > 32_768 {
3113                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
3114                false => num as i16
3115            },
3116            _ => panic!("you cannot convert non numeric values into numeric ones.")
3117        }
3118    }
3119}
3120
3121impl Into<i32> for ValueType {
3122    fn into(self) -> i32 {
3123        match self {
3124            ValueType::Int8(num) => num as i32,
3125            ValueType::Int16(num) => num as i32,
3126            ValueType::Int32(num) => num,
3127            ValueType::Int64(num) => match num > 2_147_483_647 || num < -2_147_483_647 {
3128                true => panic!("you cannot convert i32's into a value which is bigger than the capacity of 32 bit values."),
3129                false => num as i32
3130            },
3131            ValueType::Int128(num) => match num > 2_147_483_647 || num < -2_147_483_647 {
3132                true => panic!("you cannot convert i32's into a value which is bigger than the capacity of 32 bit values."),
3133                false => num as i32
3134            }
3135            ValueType::Uint8(num) => num as i32,
3136            ValueType::Uint16(num) => num as i32,
3137            ValueType::Uint32(num) => match num > 2_147_483_647 {
3138                true => panic!("you cannot convert i32's into a value which is bigger than the capacity of 32 bit values."),
3139                false => num as i32
3140            },
3141            ValueType::Usize(num) => match num > 2_147_483_647 {
3142                true => panic!("you cannot convert i32's into a value which is bigger than the capacity of 32 bit values."),
3143                false => num as i32
3144            },
3145            ValueType::Uint64(num) => match num > 2_147_483_647 {
3146                true => panic!("you cannot convert i32's into a value which is bigger than the capacity of 32 bit values."),
3147                false => num as i32
3148            }
3149            _ => panic!("you cannot convert non numeric values into numeric ones.")
3150        }
3151    }
3152}
3153
3154impl Into<i64> for ValueType {
3155    fn into(self) -> i64 {
3156        match self {
3157            ValueType::EpochTime(epoch) => epoch as i64,
3158            ValueType::Int8(num) => num as i64,
3159            ValueType::Int16(num) => num as i64,
3160            ValueType::Int32(num) => num as i64,
3161            ValueType::Int64(num) => num,
3162            ValueType::Usize(num) => num as i64,
3163            ValueType::Uint8(num) => num as i64,
3164            ValueType::Uint16(num) => num as i64,
3165            ValueType::Uint32(num) => num as i64,
3166            ValueType::Uint64(num) => num as i64,
3167            _ => panic!("you cannot convert non numeric values into numeric ones.")
3168        }
3169    }
3170}
3171
3172impl Into<u8> for ValueType {
3173    fn into(self) -> u8 {
3174        match self {
3175            ValueType::Uint8(num) => num,
3176            ValueType::Uint16(num) => match num > 255 {
3177                true => panic!("you cannot convert u16's if it's value is bigger than capacity of 8 bit values"),
3178                false => num as u8
3179            },
3180            ValueType::Uint32(num) => match num > 255 {
3181                true => panic!("you cannot convert u32's if it's value is bigger than capacity of 8 bit values"),
3182                false => num as u8
3183            },
3184            ValueType::Uint64(num) => match num > 255 {
3185                true => panic!("you cannot convert u64's if it's value is bigger than capacity of 8 bit values"),
3186                false => num as u8
3187            },
3188            ValueType::Usize(num) => match num > 255 {
3189                true => panic!("you cannot convert usizes if it's value is bigger than capacity of 8 bit values"),
3190                false => num as u8
3191            },
3192            ValueType::Int8(num) => match num < 0 {
3193                true => panic!("you cannot convert i8's if it's value is lower than 0"),
3194                false => num as u8
3195            },
3196            ValueType::Int16(num) => match num < 0 || num > 255 {
3197                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."),
3198                false => num as u8
3199            },
3200            ValueType::Int32(num) => match num < 0 || num > 255 {
3201                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."),
3202                false => num as u8
3203            },
3204            ValueType::Int64(num) => match num < 0 || num > 255 {
3205                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."),
3206                false => num as u8
3207            },
3208            ValueType::Int128(num) => match num < 0 || num > 255 {
3209                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."),
3210                false => num as u8
3211            }
3212            _ => panic!("you cannot convert non numeric values into numeric ones.")
3213        }
3214    }
3215}
3216
3217impl Into<u16> for ValueType {
3218    fn into(self) -> u16 {
3219        match self {
3220            ValueType::Uint8(num) => num as u16,
3221            ValueType::Uint16(num) => num,
3222            ValueType::Uint32(num) => match num > 65_535 {
3223                true => panic!("you cannot convert u32's if it's value is bigger than capacity of 32 bit values"),
3224                false => num as u16
3225            },
3226            ValueType::Uint64(num) => match num > 65_535 {
3227                true => panic!("you cannot convert u64's if it's value is bigger than capacity of 32 bit values"),
3228                false => num as u16
3229            },
3230            ValueType::Usize(num) => match num > 65_535 {
3231                true => panic!("you cannot convert usizes if it's value is bigger than capacity of 32 bit values"),
3232                false => num as u16
3233            },
3234            ValueType::Int8(num) => match num < 0 {
3235                true => panic!("you cannot convert i8's if it's value is lower than 0"),
3236                false => num as u16
3237            },
3238            ValueType::Int16(num) => match num < 0 {
3239                true => panic!("you cannot convert i16's if it's value is lower than 0"),
3240                false => num as u16
3241            },
3242            ValueType::Int32(num) => match num < 0 || num > 65_535 {
3243                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."),
3244                false => num as u16
3245            },
3246            ValueType::Int64(num) => match num < 0 || num > 65_535 {
3247                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."),
3248                false => num as u16
3249            },
3250            ValueType::Int128(num) => match num < 0 || num > 65_535 {
3251                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."),
3252                false => num as u16
3253            }
3254            _ => panic!("you cannot convert non numeric values into numeric ones.")
3255        }
3256    }
3257}
3258
3259impl Into<u32> for ValueType {
3260    fn into(self) -> u32 {
3261        match self {
3262            ValueType::Uint8(num) => num as u32,
3263            ValueType::Uint16(num) => num as u32,
3264            ValueType::Uint32(num) => num,
3265            ValueType::Uint64(num) => match num > 4_294_967_295 {
3266                true => panic!("you cannot convert u64's if it's value is bigger than capacity of 32 bit values"),
3267                false => num as u32
3268            },
3269            ValueType::Usize(num) => match num > 4_294_967_295 {
3270                true => panic!("you cannot convert usizes if it's value is bigger than capacity of 32 bit values"),
3271                false => num as u32
3272            },
3273            ValueType::Int8(num) => match num < 0 {
3274                true => panic!("you cannot convert i8's if it's value is lower than 0"),
3275                false => num as u32
3276            },
3277            ValueType::Int16(num) => match num < 0 {
3278                true => panic!("you cannot convert i16's if it's value is lower than 0"),
3279                false => num as u32
3280            },
3281            ValueType::Int32(num) => match num < 0 {
3282                true => panic!("you cannot convert i32's if it's value is lower than 0"),
3283                false => num as u32
3284            },
3285            ValueType::Int64(num) => match num < 0 || num > 4_294_967_295 {
3286                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."),
3287                false => num as u32
3288            },
3289            ValueType::Int128(num) => match num < 0 || num > 4_294_967_295 {
3290                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."),
3291                false => num as u32
3292            }
3293            _ => panic!("you cannot convert non numeric values into numeric ones.")
3294        }
3295    }
3296}
3297
3298impl Into<u64> for ValueType {
3299    fn into(self) -> u64 {
3300        match self {
3301            ValueType::Usize(num) => num as u64,
3302            ValueType::Uint8(num) => num as u64,
3303            ValueType::Uint16(num) => num as u64,
3304            ValueType::Uint32(num) => num as u64,
3305            ValueType::Uint64(num) => num,
3306            ValueType::Int8(num) => match num < 0 {
3307                true => panic!("you cannot turn a negative value into u64"),
3308                false => num as u64
3309            },
3310            ValueType::Int16(num) => match num < 0 {
3311                true => panic!("you cannot turn a negative value into u64"),
3312                false => num as u64
3313            },
3314            ValueType::Int32(num) => match num < 0 {
3315                true => panic!("you cannot turn a negative value into u64"),
3316                false => num as u64
3317            },
3318            ValueType::Int64(num) => match num < 0 {
3319                true => panic!("you cannot turn a negative value into u64"),
3320                false => num as u64
3321            },
3322            ValueType::Int128(num) => match num < 0 {
3323                true => panic!("you cannot turn a negative value into u64"),
3324                false => num as u64
3325            },
3326            _ => panic!("you cannot convert non numeric values into numeric ones.")
3327        }
3328    }
3329}
3330
3331impl Into<usize> for ValueType {
3332    fn into(self) -> usize {
3333        match self {
3334            ValueType::Int8(num) => match num < 0 {
3335                true => panic!("you cannot convert negative numbers to usize"),
3336                false => num as usize
3337            },
3338            ValueType::Int16(num) => match num < 0 {
3339                true => panic!("you cannot convert negative numbers to usize"),
3340                false => num as usize
3341            },
3342            ValueType::Int32(num) => match num < 0 {
3343                true => panic!("you cannot convert negative numbers to usize"),
3344                false => num as usize
3345            },
3346            ValueType::Int64(num) => match num < 0 {
3347                true => panic!("you cannot convert negative numbers to usize"),
3348                false => num as usize
3349            },
3350            ValueType::Int128(num) => match num < 0 {
3351                true => panic!("you cannot convert negative numbers to usize"),
3352                false => num as usize
3353            },
3354            ValueType::Usize(num) => num,
3355            ValueType::Uint8(num) => num as usize,
3356            ValueType::Uint16(num) => num as usize,
3357            ValueType::Uint32(num) => num as usize,
3358            ValueType::Uint64(num) => num as usize,
3359            _ => panic!("you cannot convert non numeric values into numeric ones.")
3360        }
3361    }
3362}
3363
3364/// Enum that benefits you to add json values to structs. They can be used with json functions.
3365/// That variants represents that kind of json values:
3366#[derive(Debug, Clone)]
3367pub enum JsonValue<'a> {
3368    /// 
3369    /// Example Value: ["hello", 21, "again"]
3370    /// 
3371    Array(&'a Vec<ValueType>), 
3372    
3373    /// 
3374    /// Example Value: {"name": "necdet", "message": "hello", "id": 1}
3375    /// 
3376    Object(&'a Vec<(&'a str, &'a ValueType)>), 
3377    
3378    ///
3379    /// example value: [{"name": "necdet", "message": "hello", "id": 1}, {"name": "kemal", "message": "hi", "id": 2}]
3380    /// 
3381    ObjectArray(&'a Vec<Vec<(&'a str, &'a ValueType)>>), 
3382    
3383    /// It's same with `ValueType` enums, just for simply passing it to that enum.
3384    Initial(&'a ValueType), 
3385
3386    /// 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.
3387    MysqlJsonObject(&'a Vec<(&'a str, &'a ValueType)>)
3388}
3389
3390impl <'a>std::fmt::Display for JsonValue<'a> {
3391    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
3392        match self {
3393            JsonValue::Array(values) => {
3394                let mut json_str = "[".to_string();
3395
3396                for (index, value) in values.iter().enumerate() {
3397                    if index == 0 {
3398                        json_str = format!("{}{}", json_str, value)
3399                    } else {
3400                        json_str = format!("{}, {}", json_str, value)
3401                    }
3402                }
3403
3404                json_str = format!("{}]", json_str);
3405
3406                write!(f, "{}", json_str)
3407            },
3408            JsonValue::Object(props) => {
3409                let mut json_str = "{".to_string();
3410
3411                for (index, value) in props.iter().enumerate() {
3412                    if index == 0 {
3413                        json_str = format!("{}\"{}\": {}", json_str, value.0, value.1)
3414                    } else {
3415                        json_str = format!("{}, \"{}\": {}", json_str, value.0, value.1)
3416                    }
3417                }
3418
3419                json_str = format!("{}}}", json_str);
3420
3421                write!(f, "{}", json_str)
3422            },
3423            JsonValue::MysqlJsonObject(props) => {
3424                let mut json_str = "JSON_OBJECT(".to_string();
3425
3426                for (index, value) in props.iter().enumerate() {
3427                    if index == 0 {
3428                        json_str = format!("{}'{}', {}", json_str, value.0, value.1)
3429                    } else {
3430                        json_str = format!("{}, '{}', {}", json_str, value.0, value.1)
3431                    }
3432                }
3433
3434                json_str = format!("{})", json_str);
3435
3436                write!(f, "{}", json_str)
3437            },
3438            JsonValue::ObjectArray(array) => {
3439                let mut json_str = "[".to_string();
3440
3441                for (index1, object) in array.into_iter().enumerate() {
3442                    let mut object_str = "{".to_string();
3443
3444                    for (index2, property) in object.into_iter().enumerate() {
3445                        if index2 == 0 {
3446                            object_str = format!("{}\"{}\": {}", object_str, property.0, property.1)
3447                        } else {
3448                            object_str = format!("{}, \"{}\": {}", object_str, property.0, property.1)
3449                        }
3450                    }
3451
3452                    object_str = format!("{}}}", object_str);
3453
3454                    if index1 == 0 {
3455                        json_str = format!("{}{}", json_str, object_str)
3456                    } else {
3457                        json_str = format!("{}, {}", json_str, object_str)
3458                    }
3459                }
3460
3461                write!(f, "{}]", json_str)
3462            },
3463            JsonValue::Initial(value) => write!(f, "{}", value.to_string())
3464        }
3465    }
3466}
3467
3468/// Timezones with Unix Timezone format, Can be used for setting timezone manually.
3469/// It covers european, russian, north american, south american, arabic countries. In next releases, we'll cover other african and asian timezones. 
3470#[derive(Debug, Clone)]
3471pub enum Timezone {
3472    System, Istanbul, Moscow, Kaliningrad, Samara, Ekaterinburg, Omsk, Krasnoyarsk, Irkutsk, Yakutsk,
3473    Vladivostok, Magadan, Kamchatka, Shanghai, London, Paris, Berlin, Madrid, Rome, Amsterdam, Stockholm, Oslo,
3474    Helsinki, Athens, NewYork, Chicago, Denver, LosAngeles, Anchorage, Honolulu, PuertoRico, Riyadh, Dubai, Qatar,
3475    Kuwait, Bahrain, Muscat, Aden, Baghdad, Amman, Beirut, Damascus, Gaza, Hebron, Cairo, Khartoum, Tripoli,
3476    Tunis, BuenosAires, LaPaz, SaoPaulo, Manaus, Recife, Cuiaba, PortoVelho, Santiago, Easter, Bogota, Guayaquil,
3477    Galapagos, Guyana, Asuncion, Lima, Paramaribo, Montevideo, Caracas, StJohns, Halifax, Toronto, Winnipeg,
3478    Edmonton, Vancouver, WhiteHorse, MexicoCity, Mazatlan, Chihuahua, Tijuana, Cancun, Belize, CostaRica, ElSalvador,
3479    Guatemala, Tegucigalpa, Managua, Panama, Apia, Auckland, Bougainville, Chatham, Efate, Enderbury, Fakaofo,
3480    Fiji, Funafuti, Gambier, Guadalcanal, Guam, Johnston, Kanton, Kiritimati, Kosrae, Kwajalein, Majuro, Marquesas,
3481    Midway, Nauru, Niue, Norfolk, Noumea, PagoPago, Palau, Pitcairn, Pohnpei, PortMoresby, Saipan, Rarotonga, Tahiti,
3482    Tarawa, Truk, Wake, Wallis, Yap, Tongatapu
3483}
3484
3485impl std::fmt::Display for Timezone {
3486    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
3487        match self {
3488            Timezone::System => write!(f, "SYSTEM"), Timezone::Istanbul => write!(f, "Europe/Istanbul"), Timezone::Moscow => write!(f, "Europe/Moscow"),
3489            Timezone::Kaliningrad => write!(f, "Europe/Kaliningrad"), Timezone::Samara => write!(f, "Europe/Samara"), Timezone::Ekaterinburg => write!(f, "Asia/Yekaterinburg"),
3490            Timezone::Omsk => write!(f, "Asia/Omsk"), Timezone::Krasnoyarsk => write!(f, "Asia/Krasnoyarsk"), Timezone::Irkutsk => write!(f, "Asia/Irkutsk"),
3491            Timezone::Yakutsk => write!(f, "Asia/Yakutsk"), Timezone::Vladivostok => write!(f, "Asia/Vladivostok"), Timezone::Magadan => write!(f, "Asia/Magadan"),
3492            Timezone::Kamchatka => write!(f, "Asia/Kamchatka"), Timezone::Shanghai => write!(f, "Asia/Shanghai"), Timezone::London => write!(f, "Europe/London"),
3493            Timezone::Paris => write!(f, "Europe/Paris"), Timezone::Berlin => write!(f, "Europe/Berlin"), Timezone::Madrid => write!(f, "Europe/Madrid"),
3494            Timezone::Rome => write!(f, "Europe/Rome"), Timezone::Amsterdam => write!(f, "Europe/Amsterdam"), Timezone::Stockholm => write!(f, "Europe/Stockholm"),
3495            Timezone::Oslo => write!(f, "Europe/Oslo"), Timezone::Helsinki => write!(f, "Europe/Helsinki"), Timezone::Athens => write!(f, "Europe/Athens"),
3496            Timezone::NewYork => write!(f, "America/New_York"), Timezone::Chicago => write!(f, "America/Chicago"), Timezone::Denver => write!(f, "America/Denver"),
3497            Timezone::LosAngeles => write!(f, "America/Los_Angeles"), Timezone::Anchorage => write!(f, "America/Anchorage"), Timezone::Honolulu => write!(f, "Pacific/Honolulu"),
3498            Timezone::PuertoRico => write!(f, "America/Puerto_Rico"), Timezone::Riyadh => write!(f, "Asia/Riyadh"), Timezone::Dubai => write!(f, "Asia/Dubai"),
3499            Timezone::Qatar => write!(f, "Asia/Qatar"), Timezone::Kuwait => write!(f, "Asia/Kuwait"), Timezone::Bahrain => write!(f, "Asia/Bahrain"), Timezone::Muscat => write!(f, "Asia/Muscat"),
3500            Timezone::Aden => write!(f, "Asia/Aden"), Timezone::Baghdad => write!(f, "Asia/Baghdad"), Timezone::Amman => write!(f, "Asia/Amman"), Timezone::Beirut => write!(f, "Asia/Beirut"),
3501            Timezone::Damascus => write!(f, "Asia/Damascus"), Timezone::Gaza => write!(f, "Asia/Gaza"), Timezone::Hebron => write!(f, "Asia/Hebron"), Timezone::Cairo => write!(f, "Africa/Cairo"),
3502            Timezone::Khartoum => write!(f, "Africa/Khartoum"), Timezone::Tripoli => write!(f, "Africa/Tripoli"), Timezone::Tunis => write!(f, "Africa/Tunis"),
3503            Timezone::BuenosAires => write!(f, "America/Argentina/Buenos_Aires"), Timezone::LaPaz => write!(f, "America/La_Paz"), Timezone::SaoPaulo => write!(f, "America/Sao_Paulo"),
3504            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"),
3505            Timezone::Santiago => write!(f, "America/Santiago"), Timezone::Easter => write!(f, "Pacific/Easter"), Timezone::Bogota => write!(f, "America/Bogota"), Timezone::Guayaquil => write!(f, "America/Guayaquil"),
3506            Timezone::Galapagos => write!(f, "Pacific/Galapagos"), Timezone::Guyana => write!(f, "America/Guyana"), Timezone::Asuncion => write!(f, "America/Asuncion"), Timezone::Lima => write!(f, "America/Lima"),
3507            Timezone::Paramaribo => write!(f, "America/Paramaribo"), Timezone::Montevideo => write!(f, "America/Montevideo"), Timezone::Caracas => write!(f, "America/Caracas"),
3508            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"),
3509            Timezone::Edmonton => write!(f, "America/Edmonton"), Timezone::Vancouver => write!(f, "America/Vancouver"), Timezone::WhiteHorse => write!(f, "America/Whitehorse"),
3510            Timezone::MexicoCity => write!(f, "America/Mexico_City"), Timezone::Mazatlan => write!(f, "America/Mazatlan"), Timezone::Chihuahua => write!(f, "America/Chihuahua"),
3511            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"),
3512            Timezone::ElSalvador => write!(f, "America/El_Salvador"), Timezone::Guatemala => write!(f, "America/Guatemala"), Timezone::Tegucigalpa => write!(f, "America/Tegucigalpa"),
3513            Timezone::Managua => write!(f, "America/Managua"), Timezone::Panama => write!(f, "America/Panama"), Timezone::Apia => write!(f, "Pacific/Apia"),
3514            Timezone::Auckland => write!(f, "Pacific/Auckland"), Timezone::Bougainville => write!(f, "Pacific/Bougainville"), Timezone::Chatham => write!(f, "Pacific/Chatham"),
3515            Timezone::Efate => write!(f, "Pacific/Efate"), Timezone::Enderbury => write!(f, "Pacific/Enderbury"), Timezone::Tongatapu => write!(f, "Pacific/Tongatapu"),
3516            Timezone::Fakaofo => write!(f, "Pacific/Fakaofo"), Timezone::Fiji => write!(f, "Pacific/Fiji"), Timezone::Funafuti => write!(f, "Pacific/Funafuti"),
3517            Timezone::Gambier => write!(f, "Pacific/Gambier"), Timezone::Guadalcanal => write!(f, "Pacific/Guadalcanal"), Timezone::Guam => write!(f, "Pacific/Guam"),
3518            Timezone::Johnston => write!(f, "Pacific/Johnston"), Timezone::Kanton => write!(f, "Pacific/Kanton"), Timezone::Kiritimati => write!(f, "Pacific/Kiritimati"),
3519            Timezone::Kosrae => write!(f, "Pacific/Kosrae"), Timezone::Majuro => write!(f, "Pacific/Majuro"), Timezone::Kwajalein => write!(f, "Pacific/Kwajalein"),
3520            Timezone::Midway => write!(f, "Pacific/Midway"), Timezone::Nauru => write!(f, "Pacific/Nauru"), Timezone::Niue => write!(f, "Pacific/Niue"),
3521            Timezone::Marquesas => write!(f, "Pacific/Marquesas"), Timezone::Norfolk => write!(f, "Pacific/Norfolk"), Timezone::PagoPago => write!(f, "Pacific/Pago_Pago"),
3522            Timezone::Noumea => write!(f, "Pacific/Noumea"), Timezone::Palau => write!(f, "Pacific/Palau"), Timezone::Pitcairn => write!(f, "Pacific/Pitcairn"),
3523            Timezone::Pohnpei => write!(f, "Pacific/Pohnpei"), Timezone::PortMoresby => write!(f, "Pacific/Port_Moresby"), Timezone::Rarotonga => write!(f, "Pacific/Rarotonga"),
3524            Timezone::Tahiti => write!(f, "Pacific/Tahiti"), Timezone::Tarawa => write!(f, "Pacific/Tarawa"), Timezone::Saipan => write!(f, "Pacific/Saipan"),
3525            Timezone::Truk => write!(f, "Pacific/Truk"), Timezone::Wake => write!(f, "Pacific/Wake"), Timezone::Wallis => write!(f, "Pacific/Wallis"),
3526            Timezone::Yap => write!(f, "Pacific/Yap")
3527        }
3528    }
3529}
3530
3531/// Enum that benefits you to define what you want with a foreign key.
3532#[derive(Debug, Clone)]
3533pub enum ForeignKeyActions {
3534    Cascade, Restrict, SetNull, NoAction, SetDefault
3535}
3536
3537impl std::fmt::Display for ForeignKeyActions {
3538    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
3539        match self {
3540            &ForeignKeyActions::Cascade => write!(f, "CASCADE"),
3541            &ForeignKeyActions::NoAction => write!(f, "NO ACTION"),
3542            &ForeignKeyActions::Restrict => write!(f, "RESTRICT"),
3543            &ForeignKeyActions::SetNull => write!(f, "SET NULL"),
3544            &ForeignKeyActions::SetDefault => write!(f, "SET DEFAULT")
3545        }
3546    }
3547}
3548
3549#[cfg(test)]
3550mod test {
3551    use super::*;
3552
3553    #[test]
3554    pub fn test_schema_query_declarative(){
3555        let schema = SchemaBuilder::create("blog_website").unwrap().if_not_exists().finish();
3556
3557        assert_eq!("CREATE DATABASE IF NOT EXISTS blog_website;", schema);
3558    }
3559
3560    #[test]
3561    pub fn test_schema_query_imperative(){
3562        let mut schema = SchemaBuilder::create("blog_website").unwrap();
3563        schema.if_not_exists();
3564        let schema_query = schema.finish();
3565
3566        assert_eq!("CREATE DATABASE IF NOT EXISTS blog_website;", schema_query);
3567    }
3568
3569    #[test]
3570    pub fn test_use_another_schema(){
3571        let schema = SchemaBuilder::use_another_schema("chat_website").unwrap().finish();
3572
3573        assert_eq!("USE chat_website;", schema);
3574    }
3575
3576    #[test]
3577    pub fn test_insert_query(){
3578        let columns = vec!["title", "author", "description"];
3579        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())];
3580    
3581        let insert_query = QueryBuilder::insert(columns, values).unwrap().table("blogs").finish();
3582
3583        println!("{}", insert_query);
3584        assert_eq!("INSERT INTO blogs (title, author, description) VALUES ('What's Up?', 'John Doe', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.');".to_string(), 
3585                    insert_query);
3586    }
3587
3588    #[test]
3589    pub fn test_update_query(){
3590        let update_query = QueryBuilder::update().unwrap().table("blogs").set("title", ValueType::String("Hello Rust!".to_string())).set("author", ValueType::String("Necdet".to_string())).finish();
3591
3592        assert_eq!("UPDATE blogs SET title = 'Hello Rust!', author = 'Necdet';", update_query);
3593    }
3594
3595    #[test]
3596    pub fn test_delete_query(){
3597        let delete_query = QueryBuilder::delete().unwrap().table("blogs").where_("id", "=", ValueType::String("1".to_string())).finish();
3598
3599        assert_eq!("DELETE FROM blogs WHERE id = '1';", delete_query);
3600    }
3601
3602    #[test]
3603    pub fn test_select_query_declarative(){
3604        let mut select = QueryBuilder::select(["id", "title", "description", "point"].to_vec()).unwrap();
3605
3606        let select_query = select.table("blogs")
3607                                    .where_("id", "=", ValueType::Int32(10))
3608                                    .and("point", ">", ValueType::Int8(90))
3609                                    .or("id", "=", ValueType::Int64(20))
3610                                    .finish();
3611
3612        assert_eq!("SELECT id, title, description, point FROM blogs WHERE id = 10 AND point > 90 OR id = 20;", select_query)
3613    }
3614
3615    #[test]
3616    pub fn test_select_query_imperative(){
3617        let mut select = QueryBuilder::select(["*"].to_vec()).unwrap();
3618
3619        let select_query = select.table("blogs");
3620        select_query.where_("id", "=", ValueType::Uint8(5));
3621        select_query.or("id", "=", ValueType::Usize(25));
3622
3623        let finish_the_select_query = select_query.finish();
3624
3625        assert_eq!("SELECT * FROM blogs WHERE id = 5 OR id = 25;", finish_the_select_query);
3626    }
3627
3628    #[test]
3629    pub fn test_create_table() {
3630        let mut table_builder_2 = TableBuilder::create("blabla", "projects");
3631        let table_builder_2 = table_builder_2.if_not_exists();
3632    
3633        table_builder_2.add_column("id").col_type("INT").primary_key().auto_increment();
3634        table_builder_2.add_column("name").col_type("VARCHAR(40)").not_null();
3635        table_builder_2.add_column("owner_id").col_type("INT").not_null();
3636        
3637        // if we create a table, the first ForeignKeyItem's table field is not necessary.
3638        let opts = ForeignKey {
3639            first: ForeignKeyItem { table: "".to_string(), column: "owner_id".to_string() },
3640            second: ForeignKeyItem { table: "users".to_string(), column: "id".to_string() },
3641            constraint: None,
3642            on_delete: Some(ForeignKeyActions::Cascade),
3643            on_update: None
3644        };
3645        
3646        table_builder_2.foreign_key(opts);
3647    
3648        let table_builder_2 = table_builder_2.finish();
3649
3650        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();
3651
3652        assert_eq!(raw_query, table_builder_2);
3653    }
3654
3655    #[test]
3656    pub fn test_time_value_type(){
3657        let columns = ["name", "password", "last_login"].to_vec();
3658        let values = [ValueType::String("necoo33".to_string()), ValueType::String("123456".to_string()), ValueType::Datetime("CURRENT_TIMESTAMP".to_string())].to_vec();
3659    
3660        let time_insert_test = QueryBuilder::insert(columns, values).unwrap().table("users").finish();
3661
3662        assert_eq!(time_insert_test, "INSERT INTO users (name, password, last_login) VALUES ('necoo33', '123456', CURRENT_TIMESTAMP);");
3663
3664        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();
3665
3666        assert_eq!(time_update_test, "UPDATE users SET last_login = CURRENT_TIMESTAMP WHERE name = 'necoo33';")
3667    }
3668
3669    #[test]
3670    pub fn test_unix_epoch_times(){
3671        let columns = ["name", "password", "last_login"].to_vec();
3672        let values = [ValueType::String("necoo33".to_string()), ValueType::String("123456".to_string()), ValueType::EpochTime(134523452)].to_vec();
3673    
3674        let time_insert_with_unix_epoch_times_test = QueryBuilder::insert(columns, values).unwrap().table("users").finish();
3675        assert_eq!(time_insert_with_unix_epoch_times_test, "INSERT INTO users (name, password, last_login) VALUES ('necoo33', '123456', FROM_UNIXTIME(134523452));");
3676    
3677        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();
3678
3679        assert_eq!(time_update_with_unix_epoch_times_test, "UPDATE users SET last_login = FROM_UNIXTIME(3456436) WHERE name = 'necoo33';");
3680
3681        let columns = ["name", "password", "last_login", "created_at"].to_vec();
3682
3683        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();
3684
3685        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;")
3686    }
3687
3688    #[test]
3689    pub fn test_where_ins(){
3690        let columns = ["name", "age", "id", "last_login"].to_vec();
3691
3692        let ids = [ValueType::Int32(1), ValueType::Int16(12), ValueType::Int64(8)].to_vec();
3693
3694        let test_where_in = QueryBuilder::select(columns).unwrap().table("users").where_in("id", &ids).finish();
3695
3696        assert_eq!(test_where_in, "SELECT name, age, id, last_login FROM users WHERE id IN (1, 12, 8);");
3697
3698        let columns = ["name", "age", "id", "last_login"].to_vec();
3699
3700        let ids = [ValueType::Int32(1), ValueType::Int16(12), ValueType::Int32(8)].to_vec();
3701
3702        let test_where_not_in = QueryBuilder::select(columns).unwrap().table("users").where_not_in("id", &ids).finish();
3703
3704        assert_eq!(test_where_not_in, "SELECT name, age, id, last_login FROM users WHERE id NOT IN (1, 12, 8);");
3705
3706        let columns = ["name", "age", "id", "last_login"].to_vec();
3707
3708        let test_where_in_custom = QueryBuilder::select(columns).unwrap().table("users").where_in_custom("id", "1, 12, 8").finish();
3709
3710        assert_eq!(test_where_in_custom, "SELECT name, age, id, last_login FROM users WHERE id IN (1, 12, 8);");
3711
3712        let columns = ["name", "age", "id", "last_login"].to_vec();
3713
3714        let test_where_not_in_custom = QueryBuilder::select(columns).unwrap().table("users").where_not_in_custom("id", "1, 12, 8").finish();
3715
3716        assert_eq!(test_where_not_in_custom, "SELECT name, age, id, last_login FROM users WHERE id NOT IN (1, 12, 8);");
3717
3718        // test AND IN's
3719
3720        let columns = ["name", "id", "last_login"].to_vec();
3721
3722        let ids = [ValueType::Int32(1), ValueType::Int16(12), ValueType::Int64(8)].to_vec();
3723
3724        let test_and_in = QueryBuilder::select(columns).unwrap().table("users").where_("age", ">", ValueType::Int32(35)).and_in("id", &ids).finish();
3725
3726        assert_eq!(test_and_in, "SELECT name, id, last_login FROM users WHERE age > 35 AND id IN (1, 12, 8);");
3727
3728        let columns = ["name", "id", "last_login"].to_vec();
3729
3730        let ids = [ValueType::Int32(1), ValueType::Int16(12), ValueType::Int64(8)].to_vec();
3731
3732        let test_and_not_in = QueryBuilder::select(columns).unwrap().table("users").where_("age", ">", ValueType::Int32(35)).and_not_in("id", &ids).finish();
3733
3734        assert_eq!(test_and_not_in, "SELECT name, id, last_login FROM users WHERE age > 35 AND id NOT IN (1, 12, 8);");
3735
3736        // test OR IN's
3737
3738        let columns = ["name", "id", "last_login"].to_vec();
3739
3740        let ids = [ValueType::Int32(1), ValueType::Int16(12), ValueType::Int64(8)].to_vec();
3741
3742        let test_or_in = QueryBuilder::select(columns).unwrap().table("users").where_("age", ">", ValueType::Int32(35)).or_in("id", &ids).finish();
3743
3744        assert_eq!(test_or_in, "SELECT name, id, last_login FROM users WHERE age > 35 OR id IN (1, 12, 8);");
3745
3746        let columns = ["name", "id", "last_login"].to_vec();
3747
3748        let ids = [ValueType::Int32(1), ValueType::Int16(12), ValueType::Int64(8)].to_vec();
3749
3750        let test_or_not_in = QueryBuilder::select(columns).unwrap().table("users").where_("age", ">", ValueType::Int32(35)).or_not_in("id", &ids).finish();
3751
3752        assert_eq!(test_or_not_in, "SELECT name, id, last_login FROM users WHERE age > 35 OR id NOT IN (1, 12, 8);")
3753    }
3754
3755    #[test]
3756    pub fn test_count() {
3757        let count_of_users = QueryBuilder::count("*", None).table("users").where_("age", ">", ValueType::Int32(25)).finish();
3758
3759        assert_eq!(count_of_users, "SELECT COUNT(*) FROM users WHERE age > 25;".to_string());
3760
3761        let count_of_users_as_length = QueryBuilder::count("*", Some("length")).table("users").finish();
3762
3763        assert_eq!(count_of_users_as_length, "SELECT COUNT(*) AS length FROM users;".to_string());
3764    }
3765
3766    #[test]
3767    pub fn test_json_extract(){
3768        // tests with "select()" constructor
3769
3770        let select_query_1 = QueryBuilder::select(["*"].to_vec()).unwrap().json_extract("data", ".age", Some("student_age")).table("students").finish();
3771
3772        assert_eq!(select_query_1, "SELECT JSON_EXTRACT(data, '$.age') AS student_age FROM students;".to_string());
3773        
3774        let select_query_2 = QueryBuilder::select(["*"].to_vec()).unwrap().json_extract("data", ".age", Some("student_age")).table("students").where_("successfull", "=", ValueType::Int8(1)).finish();
3775
3776        assert_eq!(select_query_2, "SELECT JSON_EXTRACT(data, '$.age') AS student_age FROM students WHERE successfull = 1;".to_string());
3777        
3778        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();
3779
3780        assert_eq!(select_query_3, "SELECT JSON_EXTRACT(data, '$.age') AS student_age FROM students WHERE JSON_EXTRACT(points, '$.name') > 85;".to_string());
3781
3782        // tests with ".where_cond()" method
3783
3784        let with_where = QueryBuilder::delete().unwrap().table("users").where_("id", ">", ValueType::Int32(200)).json_extract("id", ".user_id", None).finish();
3785
3786        assert_eq!(with_where, "DELETE FROM users WHERE JSON_EXTRACT(id, '$.user_id') > 200;".to_string());
3787
3788        // tests with ".table()" method
3789        
3790        let fields = ["name", "age"].to_vec();
3791        
3792        let with_table = QueryBuilder::select(fields).unwrap().table("users").json_extract("id", ".user_id", None).finish();
3793
3794        assert_eq!(with_table, "SELECT JSON_EXTRACT(id, '$.user_id') FROM users;".to_string());
3795
3796        // tests with ".and()" method
3797
3798        let fields = ["name", "age"].to_vec();
3799
3800        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();
3801
3802        assert_eq!(with_and_1, "SELECT name, age FROM height WHERE weight > 60 AND JSON_EXTRACT(height, '$.student_height') > 1.7;".to_string());
3803    
3804        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();
3805
3806        assert_eq!(with_and_2, "SELECT * FROM students WHERE weight > 60 AND JSON_EXTRACT(height, '$.student_height') > 1.7;".to_string());
3807
3808        // tests with ".or()" method
3809
3810        let fields = ["name", "age"].to_vec();
3811
3812        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();
3813
3814        assert_eq!(with_or_1, "SELECT name, age FROM height WHERE weight > 60 OR JSON_EXTRACT(height, '$.student_height') > 1.71;".to_string());
3815    
3816        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();
3817
3818        assert_eq!(with_or_2, "SELECT * FROM students WHERE weight > 60 OR JSON_EXTRACT(height, '$.student_height') > 1.71;".to_string());
3819
3820        // tests with "count()" constructor
3821
3822        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();
3823
3824        assert_eq!(count_query_1, "SELECT JSON_EXTRACT(age, '$.student_age') AS value, COUNT(*) FROM students GROUP BY points HAVING points > 75;".to_string());
3825
3826        // tests with ".order_by()" method
3827        
3828        let fields = ["title", "desc", "created_at", "updated_at", "keywords", "pics", "likes"].to_vec();
3829
3830        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();
3831
3832        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());
3833    
3834        // tests with ".json_extract()" method
3835
3836        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();
3837
3838        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());
3839    }
3840
3841    #[test]
3842    pub fn test_json_contains(){
3843        // test with "select()" constructor:
3844
3845        let ins = [ValueType::Int32(1), ValueType::Int32(5), ValueType::Int64(11)].to_vec();
3846        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();
3847
3848        assert_eq!(select_query, "SELECT JSON_CONTAINS(pic, '\"/files/hello.jpg\"', '$.path') FROM users WHERE id IN (1, 5, 11);".to_string());
3849
3850        // test with ".where_cond()" method:
3851
3852        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();
3853
3854        assert_eq!(where_query, "SELECT * FROM users WHERE JSON_CONTAINS(pic, '\"blablabla.jpg\"', '$.name');".to_string());
3855
3856        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();
3857
3858        assert_eq!(and_query_1, "SELECT * FROM users WHERE age > 15 AND JSON_CONTAINS(graduation_stats, 80.11, '$.average_point');".to_string());
3859
3860        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();
3861
3862        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());
3863        
3864        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();
3865    
3866        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());
3867
3868        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();
3869    
3870        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());
3871
3872        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();
3873    
3874        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());
3875        
3876        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();
3877
3878        assert_eq!(or_query_1, "SELECT * FROM users WHERE age > 15 OR JSON_CONTAINS(graduation_stats, 80.11, '$.average_point');".to_string());
3879        
3880        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();
3881        
3882        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());
3883                
3884        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();
3885            
3886        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());
3887        
3888        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();
3889            
3890        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());
3891        
3892        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();
3893            
3894        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());
3895
3896        let name = ValueType::JsonString("necdet".to_string());
3897        let id = ValueType::Int32(1);
3898        let is_active = ValueType::Boolean(true);
3899
3900        let object = vec![("name", &name), ("id", &id), ("isActive", &is_active)];
3901
3902        let mysql_json_object = JsonValue::MysqlJsonObject(&object);
3903
3904        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();
3905
3906        assert_eq!("SELECT * FROM users WHERE JSON_CONTAINS(pic, JSON_OBJECT('name', \"necdet\", 'id', 1, 'isActive', true), '$');", where_query_2)
3907    }
3908
3909    #[test]
3910    pub fn test_like_later_than_where_keywords(){
3911        let mut like_query_1 = QueryBuilder::select(["*"].to_vec()).unwrap();
3912
3913        let like_query_1 = like_query_1.table("blogs")
3914                                                      .where_("id", "=", ValueType::Int32(5))
3915                                                      .like(["title", "description"].to_vec(), "hello")
3916                                                      .finish();
3917
3918        assert_eq!(like_query_1, "SELECT * FROM blogs WHERE id = 5 AND (title LIKE '%hello%' OR description LIKE '%hello%');");
3919    
3920        let mut like_query_2 = QueryBuilder::select(["*"].to_vec()).unwrap();
3921
3922        let ins = vec![ValueType::Int32(1), ValueType::Int32(2), ValueType::Int32(3)];
3923        let like_query_2 = like_query_2.table("blogs")
3924                                                          .where_in("id", &ins)
3925                                                          .like(["title", "description", "keywords"].to_vec(), "necdet")
3926                                                          .limit(10)
3927                                                          .offset(0)
3928                                                          .finish();
3929
3930        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;")
3931    }
3932
3933    #[test]
3934    pub fn test_ordering_functions(){
3935        let order_by_query = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").order_by("id", "asc").order_by("weight", "desc").order_by("point", "asc").finish();
3936
3937        assert_eq!(order_by_query, "SELECT * FROM users ORDER BY id ASC, weight DESC, point ASC;");
3938
3939        let order_by_random_query = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").order_random().finish();
3940
3941        assert_eq!(order_by_random_query, "SELECT * FROM users ORDER BY RAND();");
3942
3943        let roles = ["admin", "moderator", "member", "guest"].to_vec();
3944        let field_query_1 = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").order_by_field("role", roles.clone()).finish();
3945
3946        assert_eq!(field_query_1, "SELECT * FROM users ORDER BY FIELD(role, 'admin', 'moderator', 'member', 'guest');");
3947
3948        let field_query_2 = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").order_by("id", "asc").order_by_field("role", roles.clone()).finish();
3949
3950        assert_eq!(field_query_2, "SELECT * FROM users ORDER BY id ASC, FIELD(role, 'admin', 'moderator', 'member', 'guest');");
3951
3952        let field_query_3 = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").order_by_field("role", roles.clone()).order_by("id", "asc").finish();
3953
3954        assert_eq!(field_query_3, "SELECT * FROM users ORDER BY FIELD(role, 'admin', 'moderator', 'member', 'guest'), id ASC;");
3955        
3956        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();
3957
3958        assert_eq!(field_query_4, "SELECT * FROM users ORDER BY FIELD(role, 'admin', 'moderator', 'member', 'guest'), FIELD(status, 'active', 'banned', 'unverified');");
3959    }
3960
3961    #[test]
3962    pub fn test_unions(){
3963        let mut union_1 = QueryBuilder::select(vec!["name", "age", "id"]).unwrap();
3964        union_1.table("users").where_("age", ">", ValueType::Int32(7));
3965
3966        let union_2 = QueryBuilder::select(vec!["name", "age", "id"]).unwrap()
3967                                                          .table("users")
3968                                                          .where_("age", "<", ValueType::Int32(15))
3969                                                          .union(vec![union_1])
3970                                                          .finish();
3971
3972        assert_eq!(union_2, "(SELECT name, age, id FROM users WHERE age < 15) UNION (SELECT name, age, id FROM users WHERE age > 7);");
3973
3974        let mut union_1 = QueryBuilder::select(vec!["id", "title", "description", "published"]).unwrap();
3975        union_1.table("blogs").like(vec!["title"], "text");
3976
3977        let mut union_2 = QueryBuilder::select(vec!["id", "title", "description", "published"]).unwrap();
3978        union_2.table("blogs").like(vec!["description"], "some text");
3979
3980        let union_3 = QueryBuilder::select(vec!["id", "title", "description", "published"]).unwrap()
3981                                                              .table("blogs")
3982                                                              .where_("published", "=", ValueType::Boolean(true))
3983                                                              .union_all(vec![union_1, union_2])
3984                                                              .finish();
3985
3986        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%');");
3987    }
3988
3989    #[test]
3990    pub fn test_json_value(){
3991        let name = ValueType::JsonString("necdet".to_string());
3992        let age = ValueType::Int8(25);
3993        let id = ValueType::Int32(1);
3994
3995        let values = vec![("name", &name), ("age", &age), ("id", &id)];
3996
3997        let json_object = JsonValue::Object(&values);
3998
3999        assert_eq!("{\"name\": \"necdet\", \"age\": 25, \"id\": 1}", json_object.to_string());
4000
4001        let mysql_json_object = JsonValue::MysqlJsonObject(&values);
4002
4003        assert_eq!("JSON_OBJECT('name', \"necdet\", 'age', 25, 'id', 1)", mysql_json_object.to_string());
4004
4005        let name2 = ValueType::JsonString("cevdet".to_string());
4006        let age2 = ValueType::Int8(24);
4007        let id2 = ValueType::Int32(2);
4008
4009        let name3 = ValueType::JsonString("serap".to_string());
4010        let age3 = ValueType::Int8(21);
4011        let id3 = ValueType::Int32(3);
4012
4013        let object1 = vec![("name", &name), ("age", &age), ("id", &id)];
4014        let object2 = vec![("name", &name2), ("age", &age2), ("id", &id2)];
4015        let object3 = vec![("name", &name3), ("age", &age3), ("id", &id3)];
4016
4017        let objects = vec![object1, object2, object3];
4018        
4019        let json_array = JsonValue::ObjectArray(&objects);
4020
4021        assert_eq!("[{\"name\": \"necdet\", \"age\": 25, \"id\": 1}, {\"name\": \"cevdet\", \"age\": 24, \"id\": 2}, {\"name\": \"serap\", \"age\": 21, \"id\": 3}]", json_array.to_string());
4022    }
4023
4024    #[test]
4025    pub fn test_json_array_append(){
4026        let lesson = ("lesson", &ValueType::String("math".to_string()));
4027        let point = ("point", &ValueType::Int32(100));
4028
4029        let values = vec![lesson, point];
4030        
4031        let object = JsonValue::MysqlJsonObject(&values);
4032
4033        let query = QueryBuilder::update().unwrap()
4034                                         .table("users")
4035                                         .json_array_append("points", Some(""), object.clone())
4036                                         .where_("id", "=", ValueType::Int8(1))
4037                                         .finish();
4038
4039        assert_eq!("UPDATE users SET points = JSON_ARRAY_APPEND(points, '$', JSON_OBJECT('lesson', 'math', 'point', 100)) WHERE id = 1;", query);
4040
4041        let query = QueryBuilder::update().unwrap()
4042                                         .table("users")
4043                                         .set("status", ValueType::String("passed".to_string()))
4044                                         .json_array_append("points", Some(""), object)
4045                                         .where_("id", "=", ValueType::Int8(1))
4046                                         .finish();
4047
4048        assert_eq!("UPDATE users SET status = 'passed', points = JSON_ARRAY_APPEND(points, '$', JSON_OBJECT('lesson', 'math', 'point', 100)) WHERE id = 1;", query);
4049    }
4050
4051    #[test]
4052    pub fn test_json_remove() {
4053        let query = QueryBuilder::update().unwrap()
4054                                         .table("blogs")
4055                                         .json_remove("likes", vec!["[10]"])
4056                                         .where_("blog_id", "=", ValueType::Int32(20))
4057                                         .finish();
4058
4059        assert_eq!(query, "UPDATE blogs SET likes = JSON_REMOVE(likes, '$[10]') WHERE blog_id = 20;");
4060        
4061        let query = QueryBuilder::update().unwrap()
4062                                         .table("blogs")
4063                                         .set("blabla", ValueType::Int32(50))
4064                                         .json_remove("likes", vec!["[10]", "[11]", "[12]"])
4065                                         .where_("blog_id", "=", ValueType::Int32(20))
4066                                         .finish();
4067
4068        println!("{}", query)
4069    }
4070
4071    #[test]
4072    pub fn test_json_set_and_json_replace(){
4073        let lesson = ("lesson", &ValueType::String("math".to_string()));
4074        let point = ("point", &ValueType::Int32(100));
4075
4076        let values = vec![lesson, point];
4077        
4078        let object = JsonValue::MysqlJsonObject(&values);
4079
4080        let query = QueryBuilder::update().unwrap()
4081                                                        .table("users")
4082                                                        .json_set("points", "[0]", object)
4083                                                        .where_("id", "=", ValueType::Int32(1))
4084                                                        .finish();
4085
4086        assert_eq!("UPDATE users SET points = JSON_SET(points, '$[0]', JSON_OBJECT('lesson', 'math', 'point', 100)) WHERE id = 1;", query);
4087
4088        let value = ValueType::Int32(100);
4089        let value = JsonValue::Initial(&value);
4090
4091        let query = QueryBuilder::update().unwrap()
4092                                         .table("users")
4093                                         .json_replace("points", "[0].point", value)
4094                                         .where_("id", "=", ValueType::Int32(1))
4095                                         .finish();
4096
4097        assert_eq!("UPDATE users SET points = JSON_REPLACE(points, '$[0].point', 100) WHERE id = 1;", query)
4098    }
4099
4100    #[test]
4101    pub fn test_json_value_initial_bugfix(){
4102        let file_name_val = ValueType::JsonString("chemistry".to_string());
4103        let file_name_val = JsonValue::Initial(&file_name_val);
4104
4105        let query = QueryBuilder::select(vec!["lesson_points"]).unwrap()
4106                                         .json_extract("points", &format!("[{}]", 2), Some("point"))
4107                                         .table("students")
4108                                         .where_("id", "=", ValueType::Int32(5))
4109                                         .and("adsf", "=", ValueType::Null)
4110                                         .json_contains("points", file_name_val, Some(&format!("[{}].name", 0)))
4111                                         .finish();
4112
4113        assert_eq!("SELECT JSON_EXTRACT(points, '$[2]') AS point FROM students WHERE id = 5 AND JSON_CONTAINS(points, '\"chemistry\"', '$[0].name');", query);
4114    }
4115
4116    #[test]
4117    pub fn test_timezones(){
4118        let query = QueryBuilder::select(vec!["*"]).unwrap().table("users").time_zone(Timezone::Istanbul).finish();
4119
4120        assert_eq!(query, "SET time_zone = Europe/Istanbul; SELECT * FROM users;");
4121        
4122        let query = QueryBuilder::select(vec!["*"]).unwrap()
4123                                         .table("users")
4124                                         .global_time_zone(Timezone::Amsterdam)
4125                                         .where_("id", "=", ValueType::Int32(3))
4126                                         .and("surname", "=", ValueType::String("Doe".to_string()))
4127                                         .finish();
4128
4129        assert_eq!(query, "SET GLOBAL time_zone = Europe/Amsterdam; SELECT * FROM users WHERE id = 3 AND surname = 'Doe';");
4130
4131        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();
4132
4133        assert_eq!(query, "SET time_zone = America/New_York; UPDATE users SET age = 26, last_online_date = CURRENT_TIMESTAMP WHERE id = 234;");
4134
4135        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();
4136
4137        assert_eq!(query, "SET GLOBAL time_zone = America/New_York; UPDATE users SET age = 26, last_online_date = CURRENT_TIMESTAMP WHERE id = 234;");
4138    }
4139
4140    #[test]
4141    pub fn test_joins(){
4142        let query = QueryBuilder::select(vec!["*"]).unwrap()
4143                                         .table("students s")
4144                                         .inner_join("grades g", "s.id", "=", "g.student_id")
4145                                         .where_("id", "=", ValueType::Int32(10))
4146                                         .finish();
4147
4148        assert_eq!(query, "SELECT * FROM students s INNER JOIN grades g ON s.id = g.student_id WHERE id = 10;");
4149
4150        let query = QueryBuilder::select(vec!["*"]).unwrap()
4151                                         .table("students s")
4152                                         .left_join("grades g", "s.id", "=", "g.student_id")
4153                                         .where_("id", "=", ValueType::Int32(10))
4154                                         .finish();
4155
4156        assert_eq!(query, "SELECT * FROM students s LEFT JOIN grades g ON s.id = g.student_id WHERE id = 10;");
4157
4158        let query = QueryBuilder::select(vec!["*"]).unwrap()
4159                                         .table("students s")
4160                                         .right_join("grades g", "s.id", "=", "g.student_id")
4161                                         .where_("id", "=", ValueType::Int32(10))
4162                                         .finish();
4163
4164        assert_eq!(query, "SELECT * FROM students s RIGHT JOIN grades g ON s.id = g.student_id WHERE id = 10;");
4165
4166        let query = QueryBuilder::select(vec!["*"]).unwrap()
4167                                         .table("students s")
4168                                         .cross_join("grades g")
4169                                         .where_("id", "=", ValueType::Int32(10))
4170                                         .finish();
4171
4172        assert_eq!(query, "SELECT * FROM students s CROSS JOIN grades g WHERE id = 10;");
4173
4174        let query = QueryBuilder::select(vec!["*"]).unwrap()
4175                                         .table("students s")
4176                                         .natural_join("grades g")
4177                                         .where_("id", "=", ValueType::Int32(10))
4178                                         .finish();
4179                                        
4180        assert_eq!(query, "SELECT * FROM students s NATURAL JOIN grades g WHERE id = 10;");
4181    }
4182}
qubl/lib.rs

qubl/
lib.rs
