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

qubl/
lib.rs
