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, 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        self.query = format!("{} WHERE {} {} {}", self.query, column, mark, value);
295
296        self.list.push(KeywordList::Where);
297
298        self
299    }
300
301    /// It adds the "IN" keyword with it's synthax. Don't use ".where_cond()" method if you use it.
302    ///     
303    /// ```rust
304    /// 
305    /// use qubl::{QueryBuilder, ValueType};
306    /// 
307    /// fn main(){
308    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
309    ///     let query = QueryBuilder::select(vec!["*"]).unwrap().table("users").where_in("id", &ins).finish();
310    /// 
311    ///     assert_eq!(query, "SELECT * FROM users WHERE id IN (1, 5, 10);")
312    /// }
313    pub fn where_in(&mut self, column: &str, ins: &Vec<ValueType>) -> &mut Self {
314        match ins.len() {
315            0 => panic!("you cannot pass an empty vector to the ins argument"),
316            _ => ()
317        }
318
319        self.query = format!("{} WHERE {} IN (", self.query, column);
320
321        let length_of_ins = ins.len();
322
323        for (index, value) in ins.into_iter().enumerate() {
324            if index + 1 == length_of_ins {
325                self.query = format!("{}{})", self.query, value);
326                    
327                continue;
328            }
329
330            self.query = format!("{}{}, ", self.query, value);
331        }
332
333        self.list.push(KeywordList::In);
334        self
335    }
336
337    /// It adds the "NOT IN" keyword with it's synthax. Don't use ".where_cond()" method if you use it.
338    ///     
339    /// ```rust
340    /// 
341    /// use qubl::{QueryBuilder, ValueType};
342    /// 
343    /// fn main(){
344    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
345    ///     let query = QueryBuilder::select(vec!["*"]).unwrap().table("users").where_not_in("id", &ins).finish();
346    /// 
347    ///     assert_eq!(query, "SELECT * FROM users WHERE id NOT IN (1, 5, 10);")
348    /// }
349     pub fn where_not_in(&mut self, column: &str, ins: &Vec<ValueType>) -> &mut Self {
350        match ins.len() {
351            0 => panic!("you cannot pass an empty vector to the ins argument"),
352            _ => ()
353        }
354
355        self.query = format!("{} WHERE {} NOT IN (", self.query, column);
356
357        let length_of_ins = ins.len();
358
359        for (index, value) in ins.into_iter().enumerate() {
360            if index + 1 == length_of_ins {
361                self.query = format!("{}{})", self.query, value);
362                    
363                continue;
364            }
365
366            self.query = format!("{}{}, ", self.query, value);
367        }
368
369        self.list.push(KeywordList::NotIn);
370        self
371    }
372
373    /// 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.
374    ///
375    /// ```rust
376    /// 
377    /// use qubl::{QueryBuilder, ValueType};
378    /// 
379    /// fn main(){
380    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
381    ///     let query = QueryBuilder::select(vec!["*"]).unwrap().table("users").where_in_custom("id", "1, 5, 10").finish();
382    /// 
383    ///     assert_eq!(query, "SELECT * FROM users WHERE id IN (1, 5, 10);")
384    /// }
385    pub fn where_in_custom(&mut self, column: &str, query: &str) -> &mut Self {
386        self.query = format!("{} WHERE {} IN ({})", self.query, column, query);
387
388        self.list.push(KeywordList::In);
389        self
390    }
391
392    /// 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.
393    ///    
394    /// ```rust
395    /// 
396    /// use qubl::{QueryBuilder, ValueType};
397    /// 
398    /// fn main(){
399    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
400    ///     let query = QueryBuilder::select(vec!["*"]).unwrap().table("users").where_not_in_custom("id", "1, 5, 10").finish();
401    /// 
402    ///     assert_eq!(query, "SELECT * FROM users WHERE id NOT IN (1, 5, 10);")
403    /// }
404    pub fn where_not_in_custom(&mut self, column: &str, query: &str) -> &mut Self {
405        self.query = format!("{} WHERE {} NOT IN ({})", self.query, column, query);
406
407        self.list.push(KeywordList::NotIn);
408
409        self
410    }
411
412    /// 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.
413    ///
414    /// ```rust
415    /// 
416    /// use qubl::{QueryBuilder, ValueType};
417    /// 
418    /// fn main(){
419    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
420    ///     let query = QueryBuilder::select(vec!["*"]).unwrap().table("users").where_("id", "=", ValueType::Int32(10)).or("name", "=", ValueType::String("necdet".to_string())).finish();
421    /// 
422    ///     assert_eq!(query, "SELECT * FROM users WHERE id = 10 OR name = 'necdet';")
423    /// }
424   pub fn or(&mut self, column: &str, mark: &str, value: ValueType) -> &mut Self {
425        match self.sanitize_column(column) {
426            Ok(_) => (),
427            Err(error) => panic!("{}", error)
428        }
429
430        match Self::sanitize_mark(mark) {
431            Ok(_) => (),
432            Err(error) => panic!("{}", error)
433        }
434
435        match self.sanitize_input(&value) {
436            Ok(_) => (),
437            Err(error) => panic!("{}", error)
438        }
439
440        self.query = format!("{} OR {} {} {}", self.query, column, mark, value);
441
442
443        self.list.push(KeywordList::Or);
444
445        self
446    }
447
448    /// It adds the "SET" keyword with it's synthax.
449    /// 
450    /// ```rust
451    /// 
452    /// use qubl::{QueryBuilder, ValueType};
453    /// 
454    /// fn main(){
455    ///     let query = QueryBuilder::update().unwrap()
456    ///                              .table("users")
457    ///                              .set("name", ValueType::String("arda".to_string()))
458    ///                              .where_("id", "=", ValueType::Int32(1))
459    ///                              .finish();
460    /// 
461    ///     assert_eq!(query, "UPDATE users SET name = 'arda' WHERE id = 1;")
462    /// }
463    /// 
464    /// ```
465    pub fn set(&mut self, column: &str, value: ValueType) -> &mut Self {
466        match self.hq {
467            Some(_) => (),
468            None => self.hq = Some(Self::load_hqs())
469        }
470
471        match self.sanitize_column(column) {
472            Ok(_) => (),
473            Err(error) => panic!("{}", error)
474        }
475
476        match self.sanitize_input(&value) {
477            Ok(_) => (),
478            Err(error) => panic!("{}", error)
479        }
480
481        match self.list.last() {
482            Some(keyword) => {
483                match keyword {
484                    KeywordList::Set => self.query = format!("{}, {} = {}", self.query, column, value),
485                    _ => self.query = format!("{} SET {} = {}", self.query, column, value)
486                }
487            },
488            None => panic!("that's impossible to come here.")
489        }
490
491        self.list.push(KeywordList::Set);
492
493        self
494    }
495
496    /// 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.
497    ///
498    /// ```rust
499    /// 
500    /// use qubl::{QueryBuilder, ValueType};
501    /// 
502    /// fn main(){
503    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
504    ///     let query = QueryBuilder::select(vec!["*"]).unwrap().table("users").where_("id", "=", ValueType::Int32(10)).and("name", "=", ValueType::String("necdet".to_string())).finish();
505    /// 
506    ///     assert_eq!(query, "SELECT * FROM users WHERE id = 10 AND name = 'necdet';")
507    /// }
508    pub fn and(&mut self, column: &str, mark: &str, value: ValueType) -> &mut Self {
509        match self.sanitize_column(column) {
510            Ok(_) => (),
511            Err(error) => panic!("{}", error)
512        }
513
514        match Self::sanitize_mark(mark) {
515            Ok(_) => (),
516            Err(error) => panic!("{}", error)
517        }
518
519        match self.sanitize_input(&value) {
520            Ok(_) => (),
521            Err(error) => panic!("{}", error)
522        }
523
524        self.query = format!("{} AND {} {} {}", self.query, column, mark, value);
525
526        self.list.push(KeywordList::And);
527
528        self
529    }
530
531    /// It adds the "OFFSET" keyword with it's synthax. Be careful about it's alignment with "LIMIT" keyword.
532    ///     
533    /// ```rust
534    /// 
535    /// use qubl::{QueryBuilder, ValueType};
536    /// 
537    /// fn main(){
538    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
539    ///     let query = QueryBuilder::select(vec!["*"]).unwrap().table("users").where_("id", "=", ValueType::Int32(10)).limit(5).offset(0).finish();
540    /// 
541    ///     assert_eq!(query, "SELECT * FROM users WHERE id = 10 LIMIT 5 OFFSET 0;")
542    /// }
543    pub fn offset(&mut self, offset: i32) -> &mut Self {
544        self.query = format!("{} OFFSET {}", self.query, offset);
545
546        self.list.push(KeywordList::Offset);
547
548        self
549    }
550
551    /// It adds the "LIMIT" keyword with it's synthax.
552    /// 
553    /// ```rust
554    /// 
555    /// use qubl::{QueryBuilder, ValueType};
556    /// 
557    /// fn main(){
558    ///     let ins = vec![ValueType::Int16(1), ValueType::Int64(5), ValueType::Int32(10)];
559    ///     let query = QueryBuilder::select(vec!["*"]).unwrap().table("users").where_("id", "=", ValueType::Int32(10)).limit(5).offset(0).finish();
560    /// 
561    ///     assert_eq!(query, "SELECT * FROM users WHERE id = 10 LIMIT 5 OFFSET 0;")
562    /// }
563    pub fn limit(&mut self, limit: i32) -> &mut Self {
564        self.query = format!("{} LIMIT {}", self.query, limit);
565
566        self.list.push(KeywordList::Limit);
567
568        self
569    }
570
571    /// It adds the "LIKE" keyword with it's synthax.
572    /// ```rust
573    /// 
574    /// use qubl::{QueryBuilder, ValueType};
575    /// 
576    /// fn main(){
577    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
578    ///                              .table("blogs")
579    ///                              .like(vec!["description", "title"], "qubl is awesome!")
580    ///                              .finish();
581    /// 
582    ///     assert_eq!(query, "SELECT * FROM blogs WHERE description LIKE '%qubl is awesome!%' OR title LIKE '%qubl is awesome!%';")
583    /// }
584    /// 
585    /// // it has more niche and different usages, for them check the tests.
586    pub fn like(&mut self, columns: Vec<&str>, operand: &str) -> &mut Self {
587        match columns.len() {
588            0 => panic!("you cannot pass an empty vector to the columns"),
589            _ => ()
590        }
591
592        let hqs = match self.hq {
593            Some(hqs) => hqs,
594            None => {
595                let load_hqs = Self::load_hqs();
596                self.hq = Some(load_hqs);
597
598                load_hqs
599            }
600        };
601
602        match Self::sanitize_columns(&columns, hqs) {
603            Ok(_) => {
604                match self.sanitize_str(operand){
605                    Ok(_) => (),
606                    Err(error) => {
607                        println!("That Error Occured in like method: {}", error);
608                
609                        self.list.push(KeywordList::Like);
610        
611                        return self
612                    }
613                }
614        
615                match self.list.last() {
616                    Some(keyword) => {
617                        if keyword == &KeywordList::Where || keyword == &KeywordList::In || keyword == &KeywordList::NotIn {
618                            let length_of_columns = columns.len();
619        
620                            for (i, column) in columns.into_iter().enumerate() {
621                                match length_of_columns {
622                                    1 => {
623                                        if i == 0 {
624                                            self.query = format!("{} AND {} LIKE '%{}%'", self.query, column, operand)
625                                        }  
626                                    },
627                                    _ => {
628                                        if i == 0 {
629                                            self.query = format!("{} AND ({} LIKE '%{}%'", self.query, column, operand)
630                                        } else if i + 1 == length_of_columns {
631                                            self.query = format!("{} OR {} LIKE '%{}%')", self.query, column, operand)
632                                        } else {
633                                            self.query = format!("{} OR {} LIKE '%{}%'", self.query, column, operand)
634                                        }
635                                    }
636                                }
637                            }
638                        } else {
639                            for (i, column) in columns.into_iter().enumerate() {
640                                if i == 0 {
641                                    self.query = format!("{} WHERE {} LIKE '%{}%'", self.query, column, operand);
642                                } else {
643                                    self.query = format!("{} OR {} LIKE '%{}%'", self.query, column, operand);
644                                }
645                            }
646                        }
647                    },
648                    None => panic!("Our current implementation does not support to use '.like()' later not other than WHERE, IN or NOT IN queries.")
649                }
650
651                return self
652            },
653            Err(error) => panic!("That error occured in '.like()' method: {}", error)
654        }
655    }
656
657    /// It adds the "ORDER BY" keyword with it's synthax. It only accepts "ASC", "DESC", "asc", "desc" values.
658    /// ```rust
659    /// 
660    /// use qubl::{QueryBuilder, ValueType};
661    /// 
662    /// fn main(){
663    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
664    ///                              .table("users")
665    ///                              .where_("age", ">", ValueType::Int32(25))
666    ///                              .order_by("id", "ASC")
667    ///                              .limit(5)
668    ///                              .offset(0)
669    ///                              .finish();
670    /// 
671    ///     assert_eq!(query, "SELECT * FROM users WHERE age > 25 ORDER BY id ASC LIMIT 5 OFFSET 0;")
672    /// }
673    pub fn order_by(&mut self, column: &str, mut ordering: &str) -> &mut Self {
674        match self.sanitize_column(column) {
675            Ok(_) => (),
676            Err(error) => {
677                println!("{}", error);
678
679                self.list.push(KeywordList::OrderBy);
680
681                return self
682            }
683        }
684
685        match ordering {
686            "asc" => ordering = "ASC",
687            "desc" => ordering = "DESC",
688            "ASC" => ordering = "ASC",
689            "DESC" => ordering = "DESC",
690            &_ => panic!("Panicking in order_by method: There is no other ordering options than ASC or DESC.")
691        }
692
693        match self.list.last() {
694            Some(keyword) => match keyword {
695                KeywordList::OrderBy | KeywordList::Field => self.query = format!("{}, {} {}", self.query, column, ordering),
696                _ => self.query = format!("{} ORDER BY {} {}", self.query, column, ordering)
697            },
698            None => panic!("It's almost impossible you to come here.")
699        }
700
701        self.list.push(KeywordList::OrderBy);
702
703        self
704    }
705
706    /// A practical method that adds a query for shuffling the lines.
707    /// ```rust
708    /// 
709    /// use qubl::{QueryBuilder, ValueType};
710    /// 
711    /// fn main(){
712    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
713    ///                              .table("users")
714    ///                              .where_("age", ">", ValueType::Int32(25))
715    ///                              .order_random()
716    ///                              .limit(5)
717    ///                              .offset(0)
718    ///                              .finish();
719    /// 
720    ///     assert_eq!(query, "SELECT * FROM users WHERE age > 25 ORDER BY RAND() LIMIT 5 OFFSET 0;")
721    /// }
722    pub fn order_random(&mut self) -> &mut Self {
723        if self.query.contains("ORDER BY") {
724            panic!("Error in order_random method: you cannot add ordering option twice on a query.");
725        }
726
727        self.query = format!("{} ORDER BY RAND()", self.query);
728        self.list.push(KeywordList::OrderBy);
729
730        self
731    }
732
733    /// Adds "FIELD()" function with it's synthax. It's used on ordering depending on strings.
734    /// ```rust
735    /// 
736    /// use qubl::{QueryBuilder, ValueType};
737    /// 
738    /// fn main(){
739    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
740    ///                              .table("users")
741    ///                              .where_("age", ">", ValueType::Int32(25))
742    ///                              .order_by_field("role", vec!["admin", "member", "observer"])
743    ///                              .limit(5)
744    ///                              .offset(0)
745    ///                              .finish();
746    /// 
747    ///     assert_eq!(query, "SELECT * FROM users WHERE age > 25 ORDER BY FIELD(role, 'admin', 'member', 'observer') LIMIT 5 OFFSET 0;")
748    /// }
749    pub fn order_by_field(&mut self, column: &str, ordering: Vec<&str>) -> &mut Self {
750        match ordering.len() {
751            0 => panic!("you cannot pass an empty vector to the ordering argument"),
752            _ => ()
753        }
754
755        match self.list.last() {
756            Some(keyword) => match keyword {
757                KeywordList::OrderBy => {
758                    let mut split_the_query = self.query.split(" ORDER BY ");
759                
760                    self.query = format!("{} ORDER BY {}, FIELD({}", split_the_query.nth(0).unwrap(), split_the_query.nth(0).unwrap(), column);
761
762                    for item in ordering {
763                        self.query = format!("{}, '{}'", self.query, item)
764                    }
765
766                    self.query = format!("{})", self.query);
767                },
768                KeywordList::Field => {
769                    self.query = format!("{}, FIELD({}", self.query, column);
770
771                    for item in ordering {
772                        self.query = format!("{}, '{}'", self.query, item)
773                    }
774
775                    self.query = format!("{})", self.query);
776                },
777                _ => {
778                    let mut new_part_of_query = format!("ORDER BY FIELD({}", column);
779
780                    for item in ordering {
781                        new_part_of_query = format!("{}, '{}'", new_part_of_query, item)
782                    }
783
784                    self.query = format!("{} {})", self.query, new_part_of_query);
785                }
786            },
787            None => panic!("It's almost impossible you to come here.")
788        }
789
790        self.list.push(KeywordList::Field);
791
792        self
793    }
794
795    /// It adds the "GROUP BY" keyword with it's Synthax.
796    pub fn group_by(&mut self, column: &str) -> &mut Self {
797        self.query = format!("{} GROUP BY {}", self.query, column);
798
799        self.list.push(KeywordList::GroupBy);
800
801        self
802    }
803
804    pub fn having(&mut self, column: &str, mark: &str, value: ValueType) -> &mut Self {
805        match self.sanitize_column(column) {
806            Ok(_) => (),
807            Err(error) => panic!("{}", error)
808        }
809
810        match Self::sanitize_mark(mark) {
811            Ok(_) => (),
812            Err(error) => panic!("{}", error)
813        }
814
815        match self.sanitize_input(&value) {
816            Ok(_) => (),
817            Err(error) => panic!("{}", error)
818        }
819
820        self.query = format!("{} HAVING {} {} {}", self.query, column, mark, value);
821
822        self.list.push(KeywordList::Having);
823
824        self
825    }
826
827
828    /// it adds the `UNION` keyword and its synthax. You can pass multiple queries to union with:
829    /// 
830    /// ```rust
831    /// 
832    /// use qubl::{QueryBuilder, ValueType};
833    /// 
834    /// fn main(){
835    ///     let mut union_1 = QueryBuilder::select(vec!["name", "age", "id"]).unwrap();
836    ///     union_1.table("users").where_("age", ">", ValueType::Int32(7));
837    ///
838    ///     let union_2 = QueryBuilder::select(vec!["name", "age", "id"]).unwrap()
839    ///                                .table("users")
840    ///                                .where_("age", "<", ValueType::Int32(15))
841    ///                                .union(vec![union_1])
842    ///                                .finish();
843    ///
844    ///     assert_eq!(union_2, "(SELECT name, age, id FROM users WHERE age < 15) UNION (SELECT name, age, id FROM users WHERE age > 7);");
845    /// }
846    /// 
847    /// ```
848    pub fn union(&mut self, others: Vec<QueryBuilder<'_>>) -> &mut Self {
849        match self.list.last() {
850            Some(keyword) => {
851                match keyword {
852                    KeywordList::Union | KeywordList::UnionAll => {
853                        for other in others {
854                            self.query = format!("{} UNION ({})", self.query, other.query)
855                        }
856                    },
857                    _ => {
858                        self.query = format!("({})", self.query);
859                        
860                        for other in others {
861                            self.query = format!("{} UNION ({})", self.query, other.query)
862                        }
863                    }
864                }
865            },
866            None => panic!("it's impossible to came here!")
867        }
868
869        self.list.push(KeywordList::Union);
870
871        self
872    }
873
874
875    /// it adds the `UNION` keyword and its synthax. You can pass multiple queries to union with:
876    /// 
877    /// ```rust
878    /// 
879    /// use qubl::{QueryBuilder, ValueType};
880    /// 
881    /// fn main(){
882    ///     let mut union_1 = QueryBuilder::select(vec!["id", "title", "description", "published"]).unwrap();
883    ///     union_1.table("blogs").like(vec!["title"], "text");
884    ///
885    ///     let mut union_2 = QueryBuilder::select(vec!["id", "title", "description", "published"]).unwrap();
886    ///     union_2.table("blogs").like(vec!["description"], "some text");
887    ///
888    ///     let union_3 = QueryBuilder::select(vec!["id", "title", "description", "published"]).unwrap()
889    ///                                .table("blogs")
890    ///                                .where_("published", "=", ValueType::Boolean(true))
891    ///                                .union_all(vec![union_1, union_2])
892    ///                                .finish();
893    ///
894    ///     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%');");
895    /// }
896    /// 
897    /// ```
898    /// 
899    pub fn union_all(&mut self, others: Vec<QueryBuilder<'_>>) -> &mut Self {
900        match self.list.last() {
901            Some(keyword) => {
902                match keyword {
903                    KeywordList::Union | KeywordList::UnionAll => {
904                        for other in others {
905                            self.query = format!("{} UNION ALL ({})", self.query, other.query)
906                        }
907                    },
908                    _ => {
909                        self.query = format!("({})", self.query);
910                        
911                        for other in others {
912                            self.query = format!("{} UNION ALL ({})", self.query, other.query)
913                        }
914                    }
915                }
916            },
917            None => panic!("it's impossible to came here!")
918        }
919
920        self.list.push(KeywordList::UnionAll);
921
922        self
923    }
924
925    /// 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.  
926    /// 
927    /// ```rust
928    /// 
929    /// use qubl::{QueryBuilder, ValueType};
930    /// 
931    /// fn main(){
932    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
933    ///                              .table("users")
934    ///                              .append_custom("WHERE age > 25 ORDER BY FIELD(role, 'admin', 'member', 'observer') LIMIT 5 OFFSET 0")
935    ///                              .finish();
936    /// 
937    ///     assert_eq!(query, "SELECT * FROM users WHERE age > 25 ORDER BY FIELD(role, 'admin', 'member', 'observer') LIMIT 5 OFFSET 0;")
938    /// }
939    /// ```
940    /// 
941    pub fn append_custom(&mut self, query: &str) -> &mut Self {
942        self.query = format!("{} {}", self.query, query);
943
944        self
945    }
946
947    /// 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. 
948    /// 
949    /// ```rust
950    /// 
951    /// use qubl::{QueryBuilder, ValueType, KeywordList};
952    /// 
953    /// fn main(){
954    ///     let query = QueryBuilder::select(vec!["*"]).unwrap()
955    ///                              .table("users")
956    ///                              .append_custom("WHERE age > 25 ORDER BY FIELD(role, 'admin', 'member', 'observer') LIMIT 5 OFFSET 0")
957    ///                              .append_keyword(KeywordList::Where)
958    ///                              .append_keyword(KeywordList::Field)
959    ///                              .append_keyword(KeywordList::Limit)
960    ///                              .append_keyword(KeywordList::Offset)
961    ///                              .finish();
962    /// 
963    ///     assert_eq!(query, "SELECT * FROM users WHERE age > 25 ORDER BY FIELD(role, 'admin', 'member', 'observer') LIMIT 5 OFFSET 0;")
964    /// }
965    /// ```
966    /// 
967    pub fn append_keyword(&mut self, keyword: KeywordList) -> &mut Self {
968        self.list.push(keyword);
969
970        self
971    }
972    
973    /// 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.
974    /// 
975    /// ```rust
976    /// 
977    /// use qubl::{QueryBuilder, ValueType};
978    /// 
979    /// fn main(){
980    ///     let query = QueryBuilder::select(["*"].to_vec()).unwrap()
981    ///                              .json_extract("articles", "[0]", Some("blog1"))
982    ///                              .json_extract("articles", "[1]", Some("blog2"))
983    ///                              .json_extract("articles", "[2]", Some("blog3"))
984    ///                              .table("users")
985    ///                              .where_("published", "=", ValueType::Int32(1))
986    ///                              .finish();
987    /// 
988    ///     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;")
989    /// }
990    /// 
991    /// ```
992    pub fn json_extract(&mut self, haystack: &str, needle: &str, _as: Option<&str>) -> &mut Self {
993        match self.list.last() {
994            Some(keyword) => {
995                match keyword {
996                    KeywordList::Where => {
997                        if _as.is_some() {
998                            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.");
999                        }
1000
1001                        match self.table.as_str() == haystack {
1002                            true => {
1003                                let mut split_the_query = self.query.split(haystack);
1004                                let string_for_replace = format!("JSON_EXTRACT({}, '${}')", haystack, needle);
1005
1006                                self.query = format!("SELECT{}{}{}", self.table, string_for_replace, split_the_query.nth(2).unwrap()) 
1007                            },
1008                            false => {
1009                                let string_for_replace = format!("JSON_EXTRACT({}, '${}')", haystack, needle);
1010
1011                                self.query = self.query.replace(haystack,&string_for_replace)
1012                            }
1013                        }
1014                    },
1015                    KeywordList::And => {
1016                        if _as.is_some() {
1017                            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.");
1018                        }
1019
1020                        let query_to_comp = format!("AND {}", haystack);
1021
1022                        match self.table.as_str() == haystack {
1023                            true => {
1024                                let mut split_the_query = self.query.split(&query_to_comp);
1025                                let string_for_replace = format!("JSON_EXTRACT({}, '${}')", haystack, needle);
1026
1027                                self.query = format!("{}AND {}{}", split_the_query.nth(0).unwrap(), string_for_replace, split_the_query.nth(0).unwrap()) 
1028                            },
1029                            false => {
1030                                match self.query.matches(&query_to_comp).count() {
1031                                    0 => (),
1032                                    1 => {
1033                                        let string_for_replace = format!("AND JSON_EXTRACT({}, '${}')", haystack, needle);
1034
1035                                        self.query = self.query.replace(&query_to_comp,&string_for_replace)
1036                                    }
1037                                    _ => {
1038                                        let split_the_query = self.query.split(&query_to_comp).collect::<Vec<&str>>();
1039
1040                                        let mut last_chunk = "".to_string();
1041                                        let mut new_chunk = "".to_string();
1042                                        let length_of_split = split_the_query.len();
1043                                        
1044                                        for (index, chunk) in split_the_query.into_iter().enumerate() {
1045                                            if index + 1 == length_of_split {
1046                                                last_chunk = chunk.to_string()
1047                                            } else if index == 0 {
1048                                                new_chunk = format!("{}", chunk);
1049                                            } else {
1050                                                new_chunk = format!("{}{}{}", new_chunk, query_to_comp, chunk)
1051                                            }
1052                                        }
1053
1054                                        let string_for_replace = format!("AND JSON_EXTRACT({}, '${}')", haystack, needle);
1055
1056                                        self.query = format!("{} {} {}", new_chunk, string_for_replace, last_chunk)
1057                                    }
1058                                }
1059                            }
1060                        }
1061                    },
1062                    KeywordList::Or => {
1063                        if _as.is_some() {
1064                            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.");
1065                        }
1066
1067                        let query_to_comp = format!("OR {}", haystack);
1068
1069                        match self.table.as_str() == haystack {
1070                            true => {
1071                                let mut split_the_query = self.query.split(&query_to_comp);
1072                                let string_for_replace = format!("JSON_EXTRACT({}, '${}')", haystack, needle);
1073
1074                                self.query = format!("{}OR {}{}", split_the_query.nth(0).unwrap(), string_for_replace, split_the_query.nth(0).unwrap()) 
1075                            },
1076                            false => {
1077                                match self.query.matches(&query_to_comp).count() {
1078                                    0 => (),
1079                                    1 => {
1080                                        let string_for_replace = format!("OR JSON_EXTRACT({}, '${}')", haystack, needle);
1081
1082                                        self.query = self.query.replace(&query_to_comp,&string_for_replace)
1083                                    }
1084                                    _ => {
1085                                        let split_the_query = self.query.split(&query_to_comp).collect::<Vec<&str>>();
1086
1087                                        let mut last_chunk = "".to_string();
1088                                        let mut new_chunk = "".to_string();
1089                                        let length_of_split = split_the_query.len();
1090                                        
1091                                        for (index, chunk) in split_the_query.into_iter().enumerate() {
1092                                            if index + 1 == length_of_split {
1093                                                last_chunk = chunk.to_string()
1094                                            } else if index == 0 {
1095                                                new_chunk = format!("{}", chunk);
1096                                            } else {
1097                                                new_chunk = format!("{}{}{}", new_chunk, query_to_comp, chunk)
1098                                            }
1099                                        }
1100
1101                                        let string_for_replace = format!("OR JSON_EXTRACT({}, '${}')", haystack, needle);
1102
1103                                        self.query = format!("{} {} {}", new_chunk, string_for_replace, last_chunk)
1104                                    }
1105                                }
1106                            }
1107                        }
1108                    },
1109                    KeywordList::Select => {
1110                        let string_for_put = format!("JSON_EXTRACT({}, '${}')", haystack, needle);
1111
1112                        match _as {
1113                            Some(_as) => self.query = format!("SELECT {} AS {} FROM", string_for_put, _as),
1114                            None => self.query = format!("SELECT {} FROM", string_for_put),
1115                        }
1116                    },
1117                    KeywordList::Table => {
1118                        let string_for_put = format!("JSON_EXTRACT({}, '${}')", haystack, needle);
1119
1120                        match _as {
1121                            Some(_as) => self.query = format!("SELECT {} AS {} FROM {}", string_for_put, _as, self.table),
1122                            None => self.query = format!("SELECT {} FROM {}", string_for_put, self.table),
1123                        }
1124                    },
1125                    KeywordList::OrderBy => {
1126                        if _as.is_some() {
1127                            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.");
1128                        }
1129
1130                        match self.query.matches(" ORDER BY ").count() {
1131                            0 => (),
1132                            1 => {
1133                                let split_the_query = self.query.clone();
1134                                let mut split_the_query = split_the_query.split(" ORDER BY ");
1135
1136                                let string_for_put = format!("ORDER BY JSON_EXTRACT({}, '${}')", haystack, needle);
1137        
1138                                match _as {
1139                                    Some(_as) => self.query = format!("{} {} AS {}", split_the_query.nth(0).unwrap(), string_for_put, _as),
1140                                    None => self.query = format!("{} {}", split_the_query.nth(0).unwrap(), string_for_put)
1141                                }
1142
1143                                match split_the_query.nth(0) {
1144                                    Some(comparison) => {
1145                                        match comparison.ends_with("ASC") || comparison.ends_with("asc") {
1146                                            true => self.query = format!("{} ASC", self.query),
1147                                            false => match comparison.ends_with("DESC") || comparison.ends_with("desc") {
1148                                                true => self.query = format!("{} DESC", self.query),
1149                                                false => ()
1150                                            }
1151                                        }
1152                                    },
1153                                    None => ()
1154                                }
1155                            },
1156                            _ => ()
1157                        }
1158                    },
1159                    KeywordList::Count => {
1160                        let mut split_the_query = self.query.split(" COUNT");
1161
1162                        let string_for_put = match _as {
1163                            Some(_as) => format!("JSON_EXTRACT({}, '${}') AS {}", haystack, needle, _as),
1164                            None => format!("JSON_EXTRACT({}, '${}')", haystack, needle)
1165                        };
1166
1167                        self.query = format!("SELECT {}, COUNT{}", string_for_put, split_the_query.nth(1).unwrap())
1168                    },
1169                    KeywordList::JsonExtract => {
1170                        let mut split_the_query = self.query.split(" FROM");
1171
1172                        match _as {
1173                            Some(_as) => self.query = format!("{}, JSON_EXTRACT({}, '${}') AS {} FROM", split_the_query.nth(0).unwrap(), haystack, needle, _as),
1174                            None => panic!("If you want to chain .json_extract() methods, you have to give them a tag.")
1175                        }
1176                    }
1177                    _ => ()
1178                }
1179            },
1180            None => ()
1181        }
1182        
1183        self.list.push(KeywordList::JsonExtract);
1184        self
1185    }
1186
1187    /// 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.
1188    /// 
1189    /// ```rust
1190    /// 
1191    /// use qubl::{QueryBuilder, ValueType, JsonValue};
1192    /// 
1193    /// fn main(){
1194    ///     let value = ValueType::String("blablabla.jpg".to_string());
1195    ///     let prop = vec![("name", &value)];
1196    /// 
1197    ///     let object = JsonValue::MysqlJsonObject(&prop);
1198    /// 
1199    ///     let query = QueryBuilder::select(["*"].to_vec()).unwrap()
1200    ///                              .table("users")
1201    ///                              .where_("pic", "=", ValueType::String("".to_string()))
1202    ///                              .json_contains("pic", object, Some(".name"))
1203    ///                              .finish();
1204    /// 
1205    ///     assert_eq!(query, "SELECT * FROM users WHERE JSON_CONTAINS(pic, JSON_OBJECT('name', 'blablabla.jpg'), '$.name');")
1206    /// }
1207    /// 
1208    /// ```
1209    pub fn json_contains(&mut self, column: &str, needle: JsonValue, path: Option<&str>) -> &mut Self {
1210        match self.list.last().unwrap() {
1211            KeywordList::Select => match path {
1212                Some(path) => match needle {
1213                    JsonValue::Initial(initial) => match initial {
1214                        ValueType::JsonString(needle) => self.query = format!("SELECT JSON_CONTAINS({}, '\"{}\"', '${}') FROM", column, needle, path),
1215                        ValueType::String(needle) => self.query = format!("SELECT JSON_CONTAINS({}, '{}', '${}') FROM", column, needle, path),
1216                        ValueType::Datetime(needle) => self.query = format!("SELECT JSON_CONTAINS({}, '{}', '${}') FROM", column, needle, path),
1217                        _ => self.query = format!("SELECT JSON_CONTAINS({}, {}, '${}') FROM", column, needle, path),
1218                    },
1219                    _ => self.query = format!("SELECT JSON_CONTAINS({}, {}, '${}') FROM", column, needle, path),
1220                }
1221                None => match needle {
1222                    JsonValue::Initial(initial) => match initial {
1223                        ValueType::JsonString(needle) => self.query = format!("SELECT JSON_CONTAINS({}, '\"{}\"') FROM", column, needle),
1224                        ValueType::String(needle) => self.query = format!("SELECT JSON_CONTAINS({}, '{}') FROM", column, needle),
1225                        ValueType::Datetime(needle) => self.query = format!("SELECT JSON_CONTAINS({}, '{}') FROM", column, needle),
1226                        _ => self.query = format!("SELECT JSON_CONTAINS({}, {}) FROM", column, needle)
1227                    },
1228                    _ => self.query = format!("SELECT JSON_CONTAINS({}, {}) FROM", column, needle)
1229                }
1230            },
1231            KeywordList::Where => match path {
1232                Some(path) => {
1233                    let mut split_the_query = self.query.split(" WHERE ");
1234
1235                    let first_half = split_the_query.nth(0);
1236
1237                    match needle {
1238                        JsonValue::Initial(initial) => match initial {
1239                            ValueType::JsonString(needle) => self.query = format!("{} WHERE JSON_CONTAINS({}, '\"{}\"', '${}')", first_half.unwrap(), column, needle, path),
1240                            ValueType::String(needle) => self.query = format!("{} WHERE JSON_CONTAINS({}, '{}', '${}')", first_half.unwrap(), column, needle, path),
1241                            ValueType::Datetime(needle) => self.query = format!("{} WHERE JSON_CONTAINS({}, '{}', '${}')", first_half.unwrap(), column, needle, path),
1242                            _ => self.query = format!("{} WHERE JSON_CONTAINS({}, {}, '${}')", first_half.unwrap(), column, needle, path)
1243                        },
1244                        _ => self.query = format!("{} WHERE JSON_CONTAINS({}, {}, '${}')", first_half.unwrap(), column, needle, path)
1245                    }
1246                },
1247                None => {
1248                    let mut split_the_query = self.query.split(" WHERE ");
1249
1250                    let first_half = split_the_query.nth(0);
1251
1252                    match needle {
1253                        JsonValue::Initial(initial) => match initial {
1254                            ValueType::JsonString(needle) => self.query = format!("{} WHERE JSON_CONTAINS({}, '\"{}\"')", first_half.unwrap(), column, needle),
1255                            ValueType::String(needle) => self.query = format!("{} WHERE JSON_CONTAINS({}, '{}')", first_half.unwrap(), column, needle),
1256                            ValueType::Datetime(needle) => self.query = format!("{} WHERE JSON_CONTAINS({}, '{}')", first_half.unwrap(), column, needle),
1257                            _ => self.query = format!("{} WHERE JSON_CONTAINS({}, {})", first_half.unwrap(), column, needle)
1258                        },
1259                        _ => self.query = format!("{} WHERE JSON_CONTAINS({}, {})", first_half.unwrap(), column, needle)
1260                    }
1261                }
1262            },
1263            KeywordList::And => match path {
1264                Some(path) => {
1265                    let split_the_query = self.query.split(" AND ").collect::<Vec<&str>>();
1266
1267                    let length_of_the_split_the_query = split_the_query.len();
1268
1269                    match split_the_query.len() {
1270                        0 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1271                        1 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1272                        2 => match needle {
1273                            JsonValue::Initial(initial) => match initial {
1274                                ValueType::JsonString(needle) => self.query = format!("{} AND JSON_CONTAINS({}, '\"{}\"', '${}')", split_the_query[0], column, needle, path),
1275                                ValueType::String(needle) => self.query = format!("{} AND JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
1276                                ValueType::Datetime(needle) => self.query = format!("{} AND JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
1277                                _ => self.query = format!("{} AND JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
1278                            },
1279                            _ => self.query = format!("{} AND JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
1280                        },
1281                        _ => {
1282                            let mut concatenated_string = String::new();
1283
1284                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1285                                if index == 0 {
1286                                    concatenated_string = chunk.to_string();
1287                                } else if index + 1 != length_of_the_split_the_query {
1288                                    concatenated_string = format!("{} AND {} ", concatenated_string, chunk)
1289                                }
1290                            }
1291
1292                            match needle {
1293                                JsonValue::Initial(initial) => match initial {
1294                                    ValueType::JsonString(needle) => self.query = format!("{}AND JSON_CONTAINS({}, '\"{}\"', '${}')", concatenated_string, column, needle, path),
1295                                    ValueType::String(needle) => self.query = format!("{}AND JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
1296                                    ValueType::Datetime(needle) => self.query = format!("{}AND JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
1297                                    _ => self.query = format!("{}AND JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
1298                                },
1299                                _ => self.query = format!("{}AND JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
1300                            }
1301                        }
1302                    }
1303                },
1304                None => {
1305                    let split_the_query = self.query.split(" AND ").collect::<Vec<&str>>();
1306
1307                    let length_of_the_split_the_query = split_the_query.len();
1308
1309                    match split_the_query.len() {
1310                        0 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1311                        1 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1312                        2 => match needle {
1313                            JsonValue::Initial(initial) => match initial {
1314                                ValueType::JsonString(needle) => self.query = format!("{} AND JSON_CONTAINS({}, '\"{}\"')",  split_the_query[0], column, needle),
1315                                ValueType::String(needle) => self.query = format!("{} AND JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
1316                                ValueType::Datetime(needle) => self.query = format!("{} AND JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
1317                                _ => self.query = format!("{} AND JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
1318                            },
1319                            _ => self.query = format!("{} AND JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
1320                        },
1321                        _ => {
1322                            let mut concatenated_string = String::new();
1323
1324                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1325                                if index == 0 {
1326                                    concatenated_string = chunk.to_string();
1327                                } else if index + 1 != length_of_the_split_the_query {
1328                                    concatenated_string = format!("{} AND {} ", concatenated_string, chunk)
1329                                }
1330                            }
1331
1332                            match needle {
1333                                JsonValue::Initial(initial) => match initial {
1334                                    ValueType::JsonString(needle) => self.query = format!("{}AND JSON_CONTAINS({}, '\"{}\"')", concatenated_string, column, needle),
1335                                    ValueType::String(needle) => self.query = format!("{}AND JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
1336                                    ValueType::Datetime(needle) => self.query = format!("{}AND JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
1337                                    _ => self.query = format!("{}AND JSON_CONTAINS({}, {})", concatenated_string, column, needle)
1338                                },
1339                                _ => self.query = format!("{}AND JSON_CONTAINS({}, {})", concatenated_string, column, needle)
1340                            }
1341                        }
1342                    }
1343                }
1344            },
1345            KeywordList::Or => match path {
1346                Some(path) => {
1347                    let split_the_query = self.query.split(" OR ").collect::<Vec<&str>>();
1348
1349                    let length_of_the_split_the_query = split_the_query.len();
1350
1351                    match split_the_query.len() {
1352                        0 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
1353                        1 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
1354                        2 => match needle {
1355                            JsonValue::Initial(initial) => match initial {
1356                                ValueType::JsonString(needle) => self.query = format!("{} OR JSON_CONTAINS({}, '\"{}\"', '${}')", split_the_query[0], column, needle, path),
1357                                ValueType::String(needle) => self.query = format!("{} OR JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
1358                                ValueType::Datetime(needle) => self.query = format!("{} OR JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
1359                                _ => self.query = format!("{} OR JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
1360                            },
1361                            _ => self.query = format!("{} OR JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
1362                        },
1363                        _ => {
1364                            let mut concatenated_string = String::new();
1365
1366                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1367                                if index == 0 {
1368                                    concatenated_string = chunk.to_string();
1369                                } else if index + 1 != length_of_the_split_the_query {
1370                                    concatenated_string = format!("{} OR {} ", concatenated_string, chunk)
1371                                }
1372                            }
1373
1374                            match needle {
1375                                JsonValue::Initial(initial) => match initial {
1376                                    ValueType::JsonString(needle) => self.query = format!("{}OR JSON_CONTAINS({}, '\"{}\"', '${}')", concatenated_string, column, needle, path),
1377                                    ValueType::String(needle) => self.query = format!("{}OR JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
1378                                    ValueType::Datetime(needle) => self.query = format!("{}OR JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
1379                                    _ => self.query = format!("{}OR JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
1380                                },
1381                                _ => self.query = format!("{}OR JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
1382                            }
1383                        }
1384                    }
1385                },
1386                None => {
1387                    let split_the_query = self.query.split(" OR ").collect::<Vec<&str>>();
1388
1389                    let length_of_the_split_the_query = split_the_query.len();
1390
1391                    match split_the_query.len() {
1392                        0 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
1393                        1 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
1394                        2 => match needle {
1395                            JsonValue::Initial(initial) => match initial {
1396                                ValueType::JsonString(needle) => self.query = format!("{} OR JSON_CONTAINS({}, '\"{}\"')",  split_the_query[0], column, needle),
1397                                ValueType::String(needle) => self.query = format!("{} OR JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
1398                                ValueType::Datetime(needle) => self.query = format!("{} OR JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
1399                                _ => self.query = format!("{} OR JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
1400                            },
1401                            _ => self.query = format!("{} OR JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
1402                        },
1403                        _ => {
1404                            let mut concatenated_string = String::new();
1405
1406                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1407                                if index == 0 {
1408                                    concatenated_string = chunk.to_string();
1409                                } else if index + 1 != length_of_the_split_the_query {
1410                                    concatenated_string = format!("{} OR {} ", concatenated_string, chunk)
1411                                }
1412                            }
1413
1414                            match needle {
1415                                JsonValue::Initial(initial) => match initial {
1416                                    ValueType::JsonString(needle) => self.query = format!("{}OR JSON_CONTAINS({}, '\"{}\"')", concatenated_string, column, needle),
1417                                    ValueType::String(needle) => self.query = format!("{}OR JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
1418                                    ValueType::Datetime(needle) => self.query = format!("{}OR JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
1419                                    _ => self.query = format!("{}OR JSON_CONTAINS({}, {})", concatenated_string, column, needle)
1420                                },
1421                                _ => self.query = format!("{}OR JSON_CONTAINS({}, {})", concatenated_string, column, needle)
1422                            }
1423                        }
1424                    }
1425                }
1426            },
1427            _ => panic!("Wrong usage of '.json_contains()' method, it should be used later than either SELECT, WHERE, AND, OR keywords.")
1428        }
1429
1430        self.list.push(KeywordList::JsonContains);
1431
1432        self
1433    }
1434
1435    /// 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.
1436    /// 
1437    /// ```rust
1438    /// 
1439    /// use qubl::{QueryBuilder, ValueType, JsonValue};
1440    /// 
1441    /// fn main(){
1442    ///     let value = ValueType::String("blablabla.jpg".to_string());
1443    ///     let prop = vec![("name", &value)];
1444    /// 
1445    ///     let object = JsonValue::MysqlJsonObject(&prop);
1446    /// 
1447    ///     let query = QueryBuilder::select(["*"].to_vec()).unwrap()
1448    ///                              .table("users")
1449    ///                              .where_("pic", "=", ValueType::String("".to_string()))
1450    ///                              .not_json_contains("pic", object, Some(".name"))
1451    ///                              .finish();
1452    /// 
1453    ///     assert_eq!(query, "SELECT * FROM users WHERE NOT JSON_CONTAINS(pic, JSON_OBJECT('name', 'blablabla.jpg'), '$.name');")
1454    /// }
1455    /// 
1456    /// ```
1457    pub fn not_json_contains(&mut self, column: &str, needle: JsonValue, path: Option<&str>) -> &mut Self {
1458        match self.list.last().unwrap() {
1459            KeywordList::Select => match path {
1460                Some(path) => match needle {
1461                    JsonValue::Initial(initial) => match initial {
1462                        ValueType::JsonString(needle) => self.query = format!("SELECT NOT JSON_CONTAINS({}, '\"{}\"', '${}') FROM", column, needle, path),
1463                        ValueType::String(needle) => self.query = format!("SELECT NOT JSON_CONTAINS({}, '{}', '${}') FROM", column, needle, path),
1464                        ValueType::Datetime(needle) => self.query = format!("SELECT NOT JSON_CONTAINS({}, '{}', '${}') FROM", column, needle, path),
1465                        _ => self.query = format!("SELECT NOT JSON_CONTAINS({}, {}, '${}') FROM", column, needle, path),
1466                    },
1467                    _ => self.query = format!("SELECT NOT JSON_CONTAINS({}, {}, '${}') FROM", column, needle, path),
1468                }
1469                None => match needle {
1470                    JsonValue::Initial(initial) => match initial {
1471                        ValueType::JsonString(needle) => self.query = format!("SELECT NOT JSON_CONTAINS({}, '\"{}\"') FROM", column, needle),
1472                        ValueType::String(needle) => self.query = format!("SELECT NOT JSON_CONTAINS({}, '{}') FROM", column, needle),
1473                        ValueType::Datetime(needle) => self.query = format!("SELECT NOT JSON_CONTAINS({}, '{}') FROM", column, needle),
1474                        _ => self.query = format!("SELECT NOT JSON_CONTAINS({}, {}) FROM", column, needle)
1475                    },
1476                    _ => self.query = format!("SELECT NOT JSON_CONTAINS({}, {}) FROM", column, needle)
1477                }
1478            },
1479            KeywordList::Where => match path {
1480                Some(path) => {
1481                    let mut split_the_query = self.query.split(" WHERE ");
1482
1483                    let first_half = split_the_query.nth(0);
1484
1485                    match needle {
1486                        JsonValue::Initial(initial) => match initial {
1487                            ValueType::JsonString(needle) => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, '\"{}\"', '${}')", first_half.unwrap(), column, needle, path),
1488                            ValueType::String(needle) => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, '{}', '${}')", first_half.unwrap(), column, needle, path),
1489                            ValueType::Datetime(needle) => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, '{}', '${}')", first_half.unwrap(), column, needle, path),
1490                            _ => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, {}, '${}')", first_half.unwrap(), column, needle, path)
1491                        },
1492                        _ => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, {}, '${}')", first_half.unwrap(), column, needle, path)
1493                    }
1494                },
1495                None => {
1496                    let mut split_the_query = self.query.split(" WHERE ");
1497
1498                    let first_half = split_the_query.nth(0);
1499
1500                    match needle {
1501                        JsonValue::Initial(initial) => match initial {
1502                            ValueType::JsonString(needle) => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, '\"{}\"')", first_half.unwrap(), column, needle),
1503                            ValueType::String(needle) => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, '{}')", first_half.unwrap(), column, needle),
1504                            ValueType::Datetime(needle) => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, '{}')", first_half.unwrap(), column, needle),
1505                            _ => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, {})", first_half.unwrap(), column, needle)
1506                        },
1507                        _ => self.query = format!("{} WHERE NOT JSON_CONTAINS({}, {})", first_half.unwrap(), column, needle)
1508                    }
1509                }
1510            },
1511            KeywordList::And => match path {
1512                Some(path) => {
1513                    let split_the_query = self.query.split(" AND ").collect::<Vec<&str>>();
1514
1515                    let length_of_the_split_the_query = split_the_query.len();
1516
1517                    match split_the_query.len() {
1518                        0 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1519                        1 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1520                        2 => match needle {
1521                            JsonValue::Initial(initial) => match initial {
1522                                ValueType::JsonString(needle) => self.query = format!("{} AND NOT JSON_CONTAINS({}, '\"{}\"', '${}')", split_the_query[0], column, needle, path),
1523                                ValueType::String(needle) => self.query = format!("{} AND NOT JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
1524                                ValueType::Datetime(needle) => self.query = format!("{} AND NOT JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
1525                                _ => self.query = format!("{} AND NOT JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
1526                            },
1527                            _ => self.query = format!("{} AND NOT JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
1528                        },
1529                        _ => {
1530                            let mut concatenated_string = String::new();
1531
1532                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1533                                if index == 0 {
1534                                    concatenated_string = chunk.to_string();
1535                                } else if index + 1 != length_of_the_split_the_query {
1536                                    concatenated_string = format!("{} AND {} ", concatenated_string, chunk)
1537                                }
1538                            }
1539
1540                            match needle {
1541                                JsonValue::Initial(initial) => match initial {
1542                                    ValueType::JsonString(needle) => self.query = format!("{}AND NOT JSON_CONTAINS({}, '\"{}\"', '${}')", concatenated_string, column, needle, path),
1543                                    ValueType::String(needle) => self.query = format!("{}AND NOT JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
1544                                    ValueType::Datetime(needle) => self.query = format!("{}AND NOT JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
1545                                    _ => self.query = format!("{}AND NOT JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
1546                                },
1547                                _ => self.query = format!("{}AND NOT JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
1548                            }
1549                        }
1550                    }
1551                },
1552                None => {
1553                    let split_the_query = self.query.split(" AND ").collect::<Vec<&str>>();
1554
1555                    let length_of_the_split_the_query = split_the_query.len();
1556
1557                    match split_the_query.len() {
1558                        0 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1559                        1 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1560                        2 => match needle {
1561                            JsonValue::Initial(initial) => match initial {
1562                                ValueType::JsonString(needle) => self.query = format!("{} AND NOT JSON_CONTAINS({}, '\"{}\"')",  split_the_query[0], column, needle),
1563                                ValueType::String(needle) => self.query = format!("{} AND NOT JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
1564                                ValueType::Datetime(needle) => self.query = format!("{} AND NOT JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
1565                                _ => self.query = format!("{} AND NOT JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
1566                            },
1567                            _ => self.query = format!("{} AND NOT JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
1568                        },
1569                        _ => {
1570                            let mut concatenated_string = String::new();
1571
1572                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1573                                if index == 0 {
1574                                    concatenated_string = chunk.to_string();
1575                                } else if index + 1 != length_of_the_split_the_query {
1576                                    concatenated_string = format!("{} AND {} ", concatenated_string, chunk)
1577                                }
1578                            }
1579
1580                            match needle {
1581                                JsonValue::Initial(initial) => match initial {
1582                                    ValueType::JsonString(needle) => self.query = format!("{}AND NOT JSON_CONTAINS({}, '\"{}\"')", concatenated_string, column, needle),
1583                                    ValueType::String(needle) => self.query = format!("{}AND NOT JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
1584                                    ValueType::Datetime(needle) => self.query = format!("{}AND NOT JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
1585                                    _ => self.query = format!("{}AND NOT JSON_CONTAINS({}, {})", concatenated_string, column, needle)
1586                                },
1587                                _ => self.query = format!("{}AND NOT JSON_CONTAINS({}, {})", concatenated_string, column, needle)
1588                            }
1589                        }
1590                    }
1591                }
1592            },
1593            KeywordList::Or => match path {
1594                Some(path) => {
1595                    let split_the_query = self.query.split(" OR ").collect::<Vec<&str>>();
1596
1597                    let length_of_the_split_the_query = split_the_query.len();
1598
1599                    match split_the_query.len() {
1600                        0 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
1601                        1 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
1602                        2 => match needle {
1603                            JsonValue::Initial(initial) => match initial {
1604                                ValueType::JsonString(needle) => self.query = format!("{} OR NOT JSON_CONTAINS({}, '\"{}\"', '${}')", split_the_query[0], column, needle, path),
1605                                ValueType::String(needle) => self.query = format!("{} OR NOT JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
1606                                ValueType::Datetime(needle) => self.query = format!("{} OR NOT JSON_CONTAINS({}, '{}', '${}')", split_the_query[0], column, needle, path),
1607                                _ => self.query = format!("{} OR NOT JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
1608                            },
1609                            _ => self.query = format!("{} OR NOT JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path)
1610                        },
1611                        _ => {
1612                            let mut concatenated_string = String::new();
1613
1614                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1615                                if index == 0 {
1616                                    concatenated_string = chunk.to_string();
1617                                } else if index + 1 != length_of_the_split_the_query {
1618                                    concatenated_string = format!("{} OR {} ", concatenated_string, chunk)
1619                                }
1620                            }
1621
1622                            match needle {
1623                                JsonValue::Initial(initial) => match initial {
1624                                    ValueType::JsonString(needle) => self.query = format!("{}OR NOT JSON_CONTAINS({}, '\"{}\"', '${}')", concatenated_string, column, needle, path),
1625                                    ValueType::String(needle) => self.query = format!("{}OR NOT JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
1626                                    ValueType::Datetime(needle) => self.query = format!("{}OR NOT JSON_CONTAINS({}, '{}', '${}')", concatenated_string, column, needle, path),
1627                                    _ => self.query = format!("{}OR NOT JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
1628                                },
1629                                _ => self.query = format!("{}OR NOT JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
1630                            }
1631                        }
1632                    }
1633                },
1634                None => {
1635                    let split_the_query = self.query.split(" OR ").collect::<Vec<&str>>();
1636
1637                    let length_of_the_split_the_query = split_the_query.len();
1638
1639                    match split_the_query.len() {
1640                        0 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
1641                        1 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
1642                        2 => match needle {
1643                            JsonValue::Initial(initial) => match initial {
1644                                ValueType::JsonString(needle) => self.query = format!("{} OR NOT JSON_CONTAINS({}, '\"{}\"')",  split_the_query[0], column, needle),
1645                                ValueType::String(needle) => self.query = format!("{} OR NOT JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
1646                                ValueType::Datetime(needle) => self.query = format!("{} OR NOT JSON_CONTAINS({}, '{}')",  split_the_query[0], column, needle),
1647                                _ => self.query = format!("{} OR NOT JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
1648                            },
1649                            _ => self.query = format!("{} OR NOT JSON_CONTAINS({}, {})",  split_the_query[0], column, needle)
1650                        },
1651                        _ => {
1652                            let mut concatenated_string = String::new();
1653
1654                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1655                                if index == 0 {
1656                                    concatenated_string = chunk.to_string();
1657                                } else if index + 1 != length_of_the_split_the_query {
1658                                    concatenated_string = format!("{} OR {} ", concatenated_string, chunk)
1659                                }
1660                            }
1661
1662                            match needle {
1663                                JsonValue::Initial(initial) => match initial {
1664                                    ValueType::JsonString(needle) => self.query = format!("{}OR NOT JSON_CONTAINS({}, '\"{}\"')", concatenated_string, column, needle),
1665                                    ValueType::String(needle) => self.query = format!("{}OR NOT JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
1666                                    ValueType::Datetime(needle) => self.query = format!("{}OR NOT JSON_CONTAINS({}, '{}')", concatenated_string, column, needle),
1667                                    _ => self.query = format!("{}OR NOT JSON_CONTAINS({}, {})", concatenated_string, column, needle)
1668                                },
1669                                _ => self.query = format!("{}OR NOT JSON_CONTAINS({}, {})", concatenated_string, column, needle)
1670                            }
1671                        }
1672                    }
1673                }
1674            },
1675            _ => panic!("Wrong usage of '.not_json_contains()' method, it should be used later than either SELECT, WHERE, AND, OR keywords.")
1676        }
1677
1678        self.list.push(KeywordList::NotJsonContains);
1679
1680        self
1681    }
1682
1683    /// 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.
1684    /// 
1685    /// ```rust
1686    /// 
1687    /// use qubl::{QueryBuilder, ValueType, JsonValue};
1688    /// 
1689    /// fn main () {
1690    ///     let lesson = ("lesson", &ValueType::String("math".to_string()));
1691    ///     let point = ("point", &ValueType::Int32(100));
1692    ///
1693    ///     let values = vec![lesson, point];
1694    ///
1695    ///     let object = JsonValue::MysqlJsonObject(&values);
1696    ///
1697    ///     let query = QueryBuilder::update().unwrap()
1698    ///                                 .table("users")
1699    ///                                 .json_array_append("points", Some(""), object.clone())
1700    ///                                 .where_("id", "=", ValueType::Int8(1))
1701    ///                                 .finish();
1702    ///
1703    ///     assert_eq!("UPDATE users SET points = JSON_ARRAY_APPEND(points, '$', JSON_OBJECT('lesson', 'math', 'point', 100)) WHERE id = 1;", query);
1704    /// }
1705    /// 
1706    /// ```
1707    pub fn json_array_append(&mut self, column: &str, path: Option<&str>, object: JsonValue) -> &mut Self {
1708        match self.list.last() {
1709            Some(keyword) => match keyword {
1710                KeywordList::Set => {
1711                    match path {
1712                        Some(path) => match object {
1713                            JsonValue::Initial(initial) => match initial {
1714                                ValueType::JsonString(object) => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '${}', '\"{}\"')", self.query, column, column, path, object),
1715                                ValueType::String(object) => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '${}', '{}')", self.query, column, column, path, object),
1716                                ValueType::Datetime(object) => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '${}', '{}')", self.query, column, column, path, object),
1717                                _ => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '${}', {})", self.query, column, column, path, object),
1718                            },
1719                            _ => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '${}', {})", self.query, column, column, path, object),
1720                        }
1721                        None => match object {
1722                            JsonValue::Initial(initial) => match initial {
1723                                ValueType::JsonString(object) => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '$', '\"{}\"')", self.query, column, column, object),
1724                                ValueType::String(object) => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '$', '{}')", self.query, column, column, object),
1725                                ValueType::Datetime(object) => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '$', '{}')", self.query, column, column, object),
1726                                _ => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '$', {})", self.query, column, column, object)
1727                            },
1728                            _ => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '$', {})", self.query, column, column, object)
1729                        }
1730                    }
1731                },
1732                _ => {
1733                    match path {
1734                        Some(path) => match object {
1735                            JsonValue::Initial(initial) => match initial {
1736                                ValueType::JsonString(object) => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '${}', '\"{}\"')", self.query, column, column, path, object),
1737                                ValueType::String(object) => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '${}', '{}')", self.query, column, column, path, object),
1738                                ValueType::Datetime(object) => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '${}', '{}')", self.query, column, column, path, object),
1739                                _ => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '${}', {})", self.query, column, column, path, object),
1740                            },
1741                            _ => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '${}', {})", self.query, column, column, path, object),
1742                        }
1743                        None => match object {
1744                            JsonValue::Initial(initial) => match initial {
1745                                ValueType::JsonString(object) => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '$', '\"{}\"')", self.query, column, column, object),
1746                                ValueType::String(object) => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '$', '{}')", self.query, column, column, object),
1747                                ValueType::Datetime(object) => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '$', '{}')", self.query, column, column, object),
1748                                _ => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '$', {})", self.query, column, column, object)
1749                            },
1750                            _ => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '$', {})", self.query, column, column, object)
1751                        }
1752                    }
1753                }
1754            },
1755            None => panic!("it's impossible to came here!")
1756        }
1757
1758        self.list.push(KeywordList::JsonArrayAppend);
1759        self
1760    }
1761
1762    /// it adds "JSON_REMOVE()" function with it's synthax. You cannot pass empty strings to paths.
1763    /// 
1764    /// ```rust
1765    /// 
1766    /// use qubl::{QueryBuilder, ValueType};
1767    /// 
1768    /// fn main () {
1769    ///   let query = QueryBuilder::update().unwrap()
1770    ///                            .table("blogs")
1771    ///                            .json_remove("likes", vec!["[10]"])
1772    ///                            .where_("blog_id", "=", ValueType::Int32(20))
1773    ///                            .finish();
1774    ///
1775    ///   assert_eq!(query, "UPDATE blogs SET likes = JSON_REMOVE(likes, '$[10]') WHERE blog_id = 20;");
1776    /// }
1777    /// 
1778    /// ```
1779    pub fn json_remove(&mut self, column: &str, paths: Vec<&str>) -> &mut Self {
1780        match paths.iter().any(|path| *path == "") {
1781            true => panic!("Error: a value in the paths cannot be empty string, panicking..."),
1782            false => ()
1783        }
1784
1785        match self.list.last() {
1786            Some(keyword) => match keyword {
1787                KeywordList::Set => {
1788                    self.query = format!("{}, {} = JSON_REMOVE({}", self.query, column, column);
1789
1790                    for path in paths {
1791                        if path.starts_with("$") {
1792                            self.query = format!("{}, '${}'", self.query, path.replace("$", ""))
1793                        } else {
1794                            self.query = format!("{}, '${}'", self.query, path)
1795                        }
1796                    }
1797
1798                    self.query = format!("{})", self.query)
1799                },
1800                _ => {
1801                    self.query = format!("{} SET {} = JSON_REMOVE({}", self.query, column, column);
1802
1803                    for path in paths {
1804                        if path.starts_with("$") {
1805                            self.query = format!("{}, '${}'", self.query, path.replace("$", ""))
1806                        } else {
1807                            self.query = format!("{}, '${}'", self.query, path)
1808                        }
1809                    }
1810
1811                    self.query = format!("{})", self.query)
1812                }
1813            },
1814            None => panic!("it's impossible to came here!")
1815        }
1816
1817        self.list.push(KeywordList::JsonRemove);
1818        self
1819    }
1820
1821    /// It adds `JSON_SET()` function with it's synthax. It updates values with the specified path.
1822    /// 
1823    /// ```rust
1824    /// 
1825    /// use qubl::{QueryBuilder, ValueType, JsonValue};
1826    /// 
1827    /// fn main () {
1828    /// 
1829    /// let lesson = ("lesson", &ValueType::String("math".to_string()));
1830    /// let point = ("point", &ValueType::Int32(100));
1831    ///
1832    /// let values = vec![lesson, point];
1833    ///
1834    /// let object = JsonValue::MysqlJsonObject(&values);
1835    ///
1836    /// let query = QueryBuilder::update().unwrap()
1837    ///                          .table("users")
1838    ///                          .json_set("points", "[0]", object)
1839    ///                          .where_("id", "=", ValueType::Int32(1))
1840    ///                          .finish();
1841    ///
1842    /// assert_eq!("UPDATE users SET points = JSON_SET(points, '$[0]', JSON_OBJECT('lesson', 'math', 'point', 100)) WHERE id = 1;", query);
1843    /// 
1844    /// }
1845    /// 
1846    /// ```
1847    pub fn json_set(&mut self, column: &str, path: &str, value: JsonValue) -> &mut Self {
1848        match self.list.last() {
1849            Some(keyword) => match keyword {
1850                KeywordList::Set => match value {
1851                    JsonValue::Initial(initial) => match initial {
1852                        ValueType::JsonString(value) => self.query = format!("{}, {} = JSON_SET({}, '${}', '\"{}\"')", self.query, column, column, path, value),
1853                        ValueType::String(value) => self.query = format!("{}, {} = JSON_SET({}, '${}', '{}')", self.query, column, column, path, value),
1854                        ValueType::Datetime(value) => self.query = format!("{}, {} = JSON_SET({}, '${}', '{}')", self.query, column, column, path, value),
1855                        _ => self.query = format!("{}, {} = JSON_SET({}, '${}', {})", self.query, column, column, path, value),
1856                    },
1857                    _ => self.query = format!("{}, {} = JSON_SET({}, '${}', {})", self.query, column, column, path, value),
1858                }
1859                _ => match value {
1860                    JsonValue::Initial(initial) => match initial {
1861                        ValueType::JsonString(value) => self.query = format!("{} SET {} = JSON_SET({}, '${}', '\"{}\"')", self.query, column, column, path, value),
1862                        ValueType::String(value) => self.query = format!("{} SET {} = JSON_SET({}, '${}', '{}')", self.query, column, column, path, value),
1863                        ValueType::Datetime(value) => self.query = format!("{} SET {} = JSON_SET({}, '${}', '{}')", self.query, column, column, path, value),
1864                        _ => self.query = format!("{} SET {} = JSON_SET({}, '${}', {})", self.query, column, column, path, value)
1865                    },
1866                    _ => self.query = format!("{} SET {} = JSON_SET({}, '${}', {})", self.query, column, column, path, value)
1867                }
1868            },
1869            None => panic!("it's impossible to came here!")
1870        }
1871
1872        self.list.push(KeywordList::JsonSet);
1873        self
1874    }
1875
1876    /// It adds `JSON_REPLACE()` function with it's synthax. It updates values with the specified path.
1877    /// 
1878    /// ```rust
1879    /// 
1880    /// use qubl::{QueryBuilder, ValueType, JsonValue};
1881    /// 
1882    /// fn main () {
1883    /// 
1884    /// let value = ValueType::Int32(100);
1885    /// let value = JsonValue::Initial(&value);
1886    ///
1887    /// let query = QueryBuilder::update().unwrap()
1888    ///                          .table("users")
1889    ///                          .json_replace("points", "[0].point", value)
1890    ///                          .where_("id", "=", ValueType::Int32(1))
1891    ///                          .finish();
1892    ///
1893    /// assert_eq!("UPDATE users SET points = JSON_REPLACE(points, '$[0].point', 100) WHERE id = 1;", query)
1894    /// 
1895    /// }
1896    /// 
1897    /// ```
1898    pub fn json_replace(&mut self, column: &str, path: &str, value: JsonValue) -> &mut Self {
1899        match self.list.last() {
1900            Some(keyword) => match keyword {
1901                KeywordList::Set => match value {
1902                    JsonValue::Initial(initial) => match initial {
1903                        ValueType::JsonString(value) => self.query = format!("{}, {} = JSON_REPLACE({}, '${}', '\"{}\"')", self.query, column, column, path, value),
1904                        ValueType::String(value) => self.query = format!("{}, {} = JSON_REPLACE({}, '${}', '{}')", self.query, column, column, path, value),
1905                        ValueType::Datetime(value) => self.query = format!("{}, {} = JSON_REPLACE({}, '${}', '{}')", self.query, column, column, path, value),
1906                        _ => self.query = format!("{}, {} = JSON_REPLACE({}, '${}', {})", self.query, column, column, path, value),
1907                    },
1908                    _ => self.query = format!("{}, {} = JSON_REPLACE({}, '${}', {})", self.query, column, column, path, value),
1909                }
1910                _ => match value {
1911                    JsonValue::Initial(initial) => match initial {
1912                        ValueType::JsonString(value) => self.query = format!("{} SET {} = JSON_REPLACE({}, '${}', '\"{}\"')", self.query, column, column, path, value),
1913                        ValueType::String(value) => self.query = format!("{} SET {} = JSON_REPLACE({}, '${}', '{}')", self.query, column, column, path, value),
1914                        ValueType::Datetime(value) => self.query = format!("{} SET {} = JSON_REPLACE({}, '${}', '{}')", self.query, column, column, path, value),
1915                        _ => self.query = format!("{} SET {} = JSON_REPLACE({}, '${}', {})", self.query, column, column, path, value)
1916                    },
1917                    _ => self.query = format!("{} SET {} = JSON_REPLACE({}, '${}', {})", self.query, column, column, path, value)
1918                }
1919            },
1920            None => panic!("it's impossible to came here!")
1921        }
1922
1923        self.list.push(KeywordList::JsonSet);
1924        self
1925    }
1926
1927    /// finishes the query and returns the result as string.
1928    pub fn finish(&self) -> String {
1929        return format!("{};", self.query);
1930    }
1931
1932    /// gives you an immutable copy of that instance, just for case if you need to share and potentially mutate it across threads.
1933    pub fn copy(&mut self) -> Self {
1934        Self {
1935            query: self.query.clone(),
1936            table: self.table.clone(),
1937            qtype: self.qtype.clone(),
1938            list: self.list.clone(),
1939            hq: self.hq
1940        }
1941    }
1942
1943    fn load_hqs() -> [&'a str; 26] {
1944        [";", "; drop", "admin' #", "admin'/*", "; union", "or 1 = 1",
1945        "or 1 = 1#", "or 1 = 1/*", "or true = true", "or false = false", "or '1' = '1'", "or '1' = '1'#",
1946        "or '1' = '1'/*", "; sleep(", "--", "drop table", "drop schema", "select if", "union select",
1947        "union all", "exec", "master..", "masters..", "information_schema", "load_file", "alter user"]
1948    }
1949
1950    fn sanitize_column(&mut self, column: &str)  -> std::result::Result<(), std::io::Error>  {
1951        match self.hq {
1952            Some(hqs) => {
1953                for _hq in hqs.iter() {
1954                    if &column == _hq {
1955                        return Err(std::io::Error::new(std::io::ErrorKind::PermissionDenied, "You cannot run arbitrary queries"))
1956                    }
1957                }
1958            },
1959            None => return Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "Your input is invalid."))
1960        }
1961
1962        Ok(())
1963    }
1964
1965    /// checks the inputs for potential sql injection patterns and throws error if they exist.
1966    fn sanitize_columns(columns: &Vec<&str>, hqs: [&'a str; 26]) -> std::result::Result<(), std::io::Error> {
1967        if columns.len() == 1 && columns[0] == "" {
1968            return Ok(());
1969        };
1970
1971        for column in columns.iter() {
1972            for hq in hqs.iter() {
1973                if column == hq {
1974                    return Err(std::io::Error::new(std::io::ErrorKind::PermissionDenied, "You cannot run arbitrary queries"))
1975                }
1976            }
1977        }
1978
1979        return Ok(())
1980    }
1981
1982    fn sanitize_inputs(inputs: &Vec<ValueType>, hqs: [&'a str; 26]) -> std::result::Result<(), std::io::Error> {
1983        for input in inputs.iter() {
1984            match input {
1985                ValueType::String(string) | ValueType::Datetime(string) => {
1986                    for hq in hqs.iter() {
1987                        if &string == hq {
1988                            return Err(std::io::Error::new(std::io::ErrorKind::PermissionDenied, "You cannot run arbitrary queries"))
1989                        }
1990                    }
1991                },
1992                _ => continue
1993            }
1994        }
1995
1996        return Ok(())
1997    }
1998
1999    fn sanitize_input(&mut self, input: &ValueType) -> std::result::Result<(), std::io::Error> {
2000        match input {
2001            ValueType::String(string) | ValueType::Datetime(string) => {
2002                match self.hq {
2003                    Some(hqs) => {
2004                        for hq in hqs.iter() {
2005                            if &string == hq {
2006                                return Err(std::io::Error::new(std::io::ErrorKind::PermissionDenied, "You cannot run arbitrary queries"))
2007                            }
2008                        }
2009                    },
2010                    None => return Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "Your input is invalid."))
2011                }
2012            },
2013            _ => return Ok(())
2014        };
2015
2016        Ok(())
2017    }
2018
2019    fn sanitize_mark(input: &str) -> std::result::Result<(), std::io::Error> {
2020        return match input {
2021            "=" | "<" | ">" | "<=" | ">=" | "!=" | "<>" => Ok(()),
2022            _ => Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "comparison operators cannot be other than =, <, >, <=,  >=, != or <>."))
2023        }
2024    }
2025
2026    fn sanitize_str(&mut self, input: &str) -> std::result::Result<(), std::io::Error> {
2027        match self.hq {
2028            Some(hqs) => {
2029                for hq in hqs.iter() {
2030                    if *hq == input {
2031                        return Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "comparison operators cannot be other than =, <, >, <=,  >=, != or <>."))
2032                    }
2033                }
2034            },
2035            None => return Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "comparison operators cannot be other than =, <, >, <=,  >=, != or <>."))
2036        }
2037
2038        Ok(())
2039    }
2040}
2041
2042/// Struct that benefits you to create and use schema's.
2043#[derive(Debug, Clone)]
2044pub struct SchemaBuilder {
2045    pub query: String,
2046    pub schema: String,
2047    pub list: Vec<KeywordList>
2048}
2049
2050/// implementations fon SchemaBuilder
2051impl SchemaBuilder {
2052    pub fn create(name: &str) -> std::result::Result<Self, std::io::Error> {
2053        if name.contains("!") ||
2054           name.contains("-") ||
2055           name.contains("=") ||
2056           name.contains("+") ||
2057           name.contains("%") ||
2058           name.contains("$") ||
2059           name.contains("&") ||
2060           name.contains("#") ||
2061           name.contains("[") ||
2062           name.contains("]") ||
2063           name.contains("{") ||
2064           name.contains("}") ||
2065           name.contains(":") ||
2066           name.contains(";") ||
2067           name.contains("'") ||
2068           name.contains("\"") ||
2069           name.contains(",") ||
2070           name.contains(".") {
2071                return Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "Schame name cannot include these characters: '!', '-', '=', '+', '%', '$', '&', '#', '[', ']', '{', '}', ':', ';', '\"', \"'\", '.', ','"))
2072        }
2073
2074        Ok(Self {
2075            query: format!("CREATE DATABASE {}", name),
2076            schema: name.to_string(),
2077            list: vec![KeywordList::Create]
2078        })
2079    }
2080
2081    pub fn use_another_schema(name: &str) -> std::result::Result<Self, std::io::Error> {
2082        if name.contains("!") ||
2083        name.contains("-") ||
2084        name.contains("=") ||
2085        name.contains("+") ||
2086        name.contains("%") ||
2087        name.contains("$") ||
2088        name.contains("&") ||
2089        name.contains("#") ||
2090        name.contains("[") ||
2091        name.contains("]") ||
2092        name.contains("{") ||
2093        name.contains("}") ||
2094        name.contains(":") ||
2095        name.contains(";") ||
2096        name.contains("'") ||
2097        name.contains("\"") ||
2098        name.contains(",") ||
2099        name.contains(".") {
2100             return Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "Schame name cannot include these characters: '!', '-', '=', '+', '%', '$', '&', '#', '[', ']', '{', '}', ':', ';', '\"', \"'\", '.', ','"))
2101        }
2102
2103        Ok(Self {
2104            query: format!("USE {}", name),
2105            schema: name.to_string(),
2106            list: vec![KeywordList::Use, KeywordList::Create]
2107        })
2108    }
2109
2110    pub fn if_not_exists(&mut self) -> &mut Self {
2111        match self.list[0] {
2112            KeywordList::Create => (),
2113            KeywordList::Table => (),
2114            _ => panic!("if_not_exists method cannot be used without Create or Table queries")
2115        }
2116
2117        let split_the_query =  self.query.split(" DATABASE ").collect::<Vec<&str>>();
2118        self.query = format!("{} DATABASE IF NOT EXISTS {}", split_the_query[0], split_the_query[1]);
2119
2120        self.list.insert(0, KeywordList::IfNotExist);
2121        self
2122    }
2123
2124    pub fn use_schema(&mut self, name: Option<&str>) -> &mut Self {
2125        match name {
2126            Some(schema_name) => {
2127                self.query = format!("USE {}", schema_name)
2128            },
2129            None => {
2130                self.query = format!("USE {}", self.schema);
2131            }
2132        }
2133
2134        self
2135    }
2136
2137    pub fn finish(&self) -> String {
2138        return format!("{};", self.query)
2139    }
2140}
2141
2142/// Struct that benefits you to create Tables. Currently incomplete thoug.
2143#[derive(Debug, Clone)]
2144pub struct TableBuilder {
2145    pub query: String,
2146    pub name: String,
2147    pub schema: String,
2148    pub all: Vec<String>,
2149}
2150
2151/// Struct that benefits to define a foreign key.
2152#[derive(Debug, Clone)]
2153pub struct ForeignKey {
2154    pub first: ForeignKeyItem,
2155    pub second: ForeignKeyItem,
2156    pub on_delete: Option<ForeignKeyActions>,
2157    pub on_update: Option<ForeignKeyActions>,
2158    pub constraint: Option<String>
2159}
2160
2161/// Struct that benefits you to add a foreign key item to a foreign key.
2162#[derive(Debug, Clone)]
2163pub struct ForeignKeyItem {
2164    pub table: String,
2165    pub column: String
2166}
2167
2168/// implementations for TableBuilder
2169impl TableBuilder {
2170    pub fn create(schema_name: &str, table_name: &str) -> Self {
2171        return Self {
2172            query: format!("CREATE TABLE {} (", table_name),
2173            schema: schema_name.to_string(),
2174            name: table_name.to_string(),
2175            all: vec![]
2176        }
2177    }
2178
2179    pub fn if_not_exists(&mut self) -> &mut Self {
2180        self.query = format!("{}IF NOT EXISTS (", self.query.replace("(", ""));
2181
2182        self
2183    }
2184
2185    pub fn add_column(&mut self, column_name: &str) -> &mut Self {
2186        if self.query.ends_with("(") {
2187            self.query = format!("{}{}", self.query, column_name)
2188        } else {
2189            self.query = format!("{}, {}", self.query, column_name)
2190        }
2191
2192        self
2193    }
2194
2195    pub fn col_type(&mut self, type_name: &str) -> &mut Self {
2196        if self.query.ends_with("(") {
2197            panic!("Cannot add type before defining a column name.")
2198        }
2199
2200        self.query = format!("{} {}", self.query, type_name);
2201
2202        self
2203    }
2204
2205    pub fn null(&mut self) -> &mut Self {
2206        self.query = format!("{} NULL", self.query);
2207
2208        self
2209    }
2210
2211    pub fn not_null(&mut self) -> &mut Self {
2212        self.query = format!("{} NOT NULL", self.query);
2213
2214        self
2215    }
2216
2217    pub fn auto_increment(&mut self) -> &mut Self {
2218        self.query = format!("{} AUTO_INCREMENT", self.query);
2219
2220        self
2221    }
2222
2223    pub fn primary_key(&mut self) -> &mut Self {
2224        if self.query.contains("PRIMARY KEY") {
2225            panic!("A table cannot have two primary keys.")
2226        }
2227
2228        self.query = format!("{} PRIMARY KEY", self.query);
2229
2230        self
2231    }
2232
2233    pub fn default(&mut self, value: ValueType) -> &mut Self {
2234        let split_the_query = self.query.clone();
2235        let split_the_query = split_the_query.split(", ").collect::<Vec<&str>>();
2236
2237        let last_query = split_the_query[split_the_query.len() - 1];
2238
2239        if last_query.contains("INT") || 
2240           last_query.contains("TINYINT") ||
2241           last_query.contains("SMALLINT") ||
2242           last_query.contains("MEDIUMINT") ||
2243           last_query.contains("BIGINT") ||
2244           last_query.contains("BIT") ||
2245           last_query.contains("SERIAL") {
2246            match value {
2247                ValueType::Int8(int) => self.query = format!("{} DEFAULT {}", self.query, int),
2248                ValueType::Int16(int) => self.query = format!("{} DEFAULT {}", self.query, int),
2249                ValueType::Int32(int) => self.query = format!("{} DEFAULT {}", self.query, int),
2250                ValueType::Int64(int) => self.query = format!("{} DEFAULT {}", self.query, int),
2251                ValueType::Uint8(int) => self.query = format!("{} DEFAULT {}", self.query, int),
2252                ValueType::Uint16(int) => self.query = format!("{} DEFAULT {}", self.query, int),
2253                ValueType::Uint32(int) => self.query = format!("{} DEFAULT {}", self.query, int),
2254                ValueType::Uint64(int) => self.query = format!("{} DEFAULT {}", self.query, int),
2255                ValueType::Usize(int) => self.query = format!("{} DEFAULT {}", self.query, int),
2256                ValueType::Float32(int) => self.query = format!("{} DEFAULT {}", self.query, int),
2257                ValueType::Float64(int) => self.query = format!("{} DEFAULT {}", self.query, int),
2258                _ => 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.")
2259            }
2260        }
2261
2262        if last_query.contains("BOOL") || 
2263           last_query.contains("BOOLEAN") {
2264            match value {
2265                ValueType::Boolean(boolean) => self.query = format!("{} DEFAULT {}", self.query, boolean),
2266                _ => panic!("If your column type is BOOLEAN, you have to write either true or false.")
2267            }    
2268        }
2269
2270        if last_query.contains("CHAR") ||
2271           last_query.contains("VARCHAR") ||
2272           last_query.contains("TEXT") ||
2273           last_query.contains("TINYTEXT") ||
2274           last_query.contains("MEDIUMTEXT") ||
2275           last_query.contains("LONGTEXT") ||
2276           last_query.contains("BINARY") ||
2277           last_query.contains("VARBINARY") {
2278            match value {
2279                ValueType::String(ref text) => self.query = format!("{} DEFAULT '{}'", self.query, text),
2280                _ => 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.")
2281            }
2282        }
2283
2284        if last_query.contains("DATETIME") ||
2285           last_query.contains("TIMESTAMP") {
2286            match value {
2287                ValueType::Datetime(datetime) => self.query = format!("{} DEFAULT {}", self.query, datetime),
2288                _ => 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.")
2289            }
2290        }
2291
2292        self
2293    }
2294
2295    pub fn unique(&mut self) -> &mut Self {
2296        self.query = format!("{} UNIQUE", self.query);
2297
2298        self
2299    }
2300
2301    pub fn check(&mut self, condition: &str) -> &mut Self {
2302        self.query = format!("{} CHECK({})", self.query, condition);
2303
2304        self
2305    }
2306
2307    pub fn character_set(&mut self, character_set: &str) -> &mut Self {
2308        self.query = format!("{} CHARACTER SET {}", self.query, character_set);
2309
2310        self
2311    }
2312
2313    pub fn foreign_key(&mut self, opts: ForeignKey) -> &mut Self {
2314        if self.query.starts_with("ALTER TABLE") {
2315            match opts.constraint {
2316                Some(constraint) => self.query = format!("{}, ADD CONSTRAINT {} FOREIGN KEY ({})", self.query, constraint, opts.first.column),
2317                None => self.query = format!("{}, ADD FOREIGN KEY ({})", self.query, opts.first.column)
2318            }
2319            
2320        } else {
2321            match opts.constraint {
2322                Some(constraint) => self.query = format!("{}, CONSTRAINT {} FOREIGN KEY ({})", self.query, constraint, opts.first.column),
2323                None => self.query = format!("{}, FOREIGN KEY ({})", self.query, opts.first.column)
2324            }
2325        }
2326
2327        self.query = format!("{} REFERENCES {}({})", self.query, opts.second.table, opts.second.column);
2328
2329        match opts.on_delete {
2330            Some(on_delete_opt) => self.query = format!("{} ON DELETE {}", self.query, on_delete_opt),
2331            None => ()
2332        }
2333
2334        match opts.on_update {
2335            Some(on_update_opt) => self.query = format!("{} ON UPDATE {}", self.query, on_update_opt),
2336            None => ()
2337        }
2338
2339        self
2340    }
2341
2342    pub fn unsigned(&mut self) -> &mut Self {
2343        self.query = format!("{} UNSIGNED", self.query);
2344
2345        self
2346    }
2347
2348    pub fn zerofill(&mut self) -> &mut Self {
2349        self.query = format!("{} ZEROFILL", self.query);
2350
2351        self
2352    }
2353
2354    pub fn enum_sql(&mut self, enum_vec: Vec<&str>) -> &mut Self {
2355        match enum_vec.len() {
2356            0 => panic!("enum_vec argument cannot be an empty vector"),
2357            _ => ()
2358        }
2359        
2360        self.query = format!("{} ENUM(", self.query);
2361
2362        let length_of_enum_vec = enum_vec.len();
2363        for (index, item) in enum_vec.into_iter().enumerate() {
2364            if index + 1 == length_of_enum_vec {
2365                self.query = format!("{}'{}'", self.query, item)
2366            } else {
2367                self.query = format!("{}'{}', ", self.query, item)
2368            }
2369        }
2370
2371        self
2372    }
2373
2374    pub fn generated_always(&mut self, condition: &str) -> &mut Self {
2375        self.query = format!("{} GENERATED ALWAYS AS {}", self.query, condition);
2376
2377        self
2378    }
2379
2380    pub fn virtual_sql(&mut self) -> &mut Self {
2381        self.query = format!("{} VIRTUAL", self.query);
2382
2383        self
2384    }
2385
2386    pub fn stored(&mut self) -> &mut Self {
2387        self.query = format!("{} STORED", self.query);
2388
2389        self
2390    }
2391
2392    pub fn spatial(&mut self) -> &mut Self {
2393        self.query = format!("{} SPATIAL", self.query);
2394
2395        self
2396    }
2397
2398    pub fn generated(&mut self) -> &mut Self {
2399        self.query = format!("{} GENERATED", self.query);
2400
2401        self
2402    }
2403
2404    pub fn index(&mut self, indexes: Vec<&str>) -> &mut Self {
2405        let length_of_indexes = indexes.len();
2406
2407        match length_of_indexes {
2408            0 => panic!("There is no index here."),
2409            1 => self.query = format!("{}, INDEX({})", self.query, indexes[0]),
2410            _ => {
2411                for (i, index) in indexes.into_iter().enumerate() {
2412                    if i + 1 == length_of_indexes {
2413                        self.query = format!("{}{}", self.query, index);
2414
2415                        continue;
2416                    }
2417
2418                    if i == 0 {
2419                        self.query = format!("{}, INDEX ({}, ", self.query, index);
2420
2421                        continue;
2422                    }
2423
2424                    self.query = format!("{}{}, ", self.query, index)
2425                }
2426            }
2427        }
2428
2429        self
2430    }
2431
2432    pub fn comment(&mut self, comment: &str) -> &mut Self {
2433        self.query = format!("{} COMMENT '{}'", self.query, comment);
2434
2435        self
2436    }
2437
2438    pub fn default_on_null(&mut self, value: ValueType) -> &mut Self {
2439        match value {
2440            ValueType::String(text) => self.query = format!("{} DEFAULT {} ON NULL", self.query, text),
2441            _ => self.query = format!("{} DEFAULT {} ON NULL", self.query, value),
2442        }
2443
2444        self
2445    }
2446
2447    pub fn invisible(&mut self) -> &mut Self {
2448        self.query = format!("{} INVISIBLE", self.query);
2449
2450        self
2451    }
2452
2453    pub fn custom_query(&mut self, query: &str) -> &mut Self {
2454        self.query = format!("{} {}", self.query, query);
2455
2456        self
2457    }
2458
2459    pub fn finish(&mut self) -> String {
2460        return format!("{});", self.query)
2461    }
2462}
2463
2464/// KeywordList enum. It helps to syntactically correcting the queries. 
2465#[derive(Debug, Clone, PartialEq)]
2466pub enum KeywordList {
2467    Select, Update, Delete, Insert, Count, Table, Where, Or, And, Set, 
2468    Finish, OrderBy, GroupBy, Having, Like, Limit, Offset, IfNotExist, Create, Use, In, 
2469    NotIn, JsonExtract, JsonContains, NotJsonContains, JsonArrayAppend, JsonRemove, JsonSet, JsonReplace, 
2470    Field, Union, UnionAll
2471}
2472
2473/// QueryType enum. It helps to detect the type of a query with more optimized way when is needed.
2474#[derive(Debug, Clone)]
2475pub enum QueryType {
2476    Select, Update, Delete, Insert, Null, Create, Count
2477}
2478
2479/// ValueType enum. It benefits to detect and format the value with optimized way when you have to work with exact column values. 
2480#[derive(Debug, Clone)]
2481pub enum ValueType {
2482    String(String), Datetime(String), Null, Boolean(bool), Int32(i32), Int16(i16), Int8(i8), Int64(i64), Int128(i128),
2483    Uint8(u8), Uint16(u16), Uint32(u32), Uint64(u64), Usize(usize), Float32(f32), Float64(f64),
2484    EpochTime(i64), JsonString(String)
2485}
2486
2487impl std::fmt::Display for ValueType {
2488    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
2489        match self {
2490            ValueType::String(string) => write!(f, "'{}'", string),
2491            ValueType::JsonString(string) => write!(f, "\"{}\"", string),
2492            ValueType::Datetime(datetime) => match datetime.as_str() {
2493                "CURRENT_TIMESTAMP" | "UNIX_TIMESTAMP" | "CURRENT_DATE" | "CURRENT_TIME" | "NOW()" | "CURDATE()" | "CURTIME()" => write!(f, "{}", datetime),
2494                _ => write!(f, "'{}'", datetime)
2495            },
2496            ValueType::Null => write!(f, "NULL"),
2497            ValueType::Boolean(val) => write!(f, "{}", val),
2498            ValueType::Int8(val) => write!(f, "{}", val),
2499            ValueType::Int16(val) => write!(f, "{}", val),
2500            ValueType::Int32(val) => write!(f, "{}", val),
2501            ValueType::Int64(val) => write!(f, "{}", val),
2502            ValueType::Int128(val) => write!(f, "{}", val),
2503            ValueType::Usize(val) => write!(f, "{}", val),
2504            ValueType::Uint8(val) => write!(f, "{}", val),
2505            ValueType::Uint16(val) => write!(f, "{}", val),
2506            ValueType::Uint32(val) => write!(f, "{}", val),
2507            ValueType::Uint64(val) => write!(f, "{}", val),
2508            ValueType::Float32(val) => write!(f, "{}", val),
2509            ValueType::Float64(val) => write!(f, "{}", val),
2510            ValueType::EpochTime(val) => write!(f, "FROM_UNIXTIME({})", val),
2511        }
2512    }
2513}
2514
2515impl From<String> for ValueType { fn from(value: String) -> Self { ValueType::String(value) } }
2516impl From<bool> for ValueType { fn from(value: bool) -> Self { ValueType::Boolean(value) } }
2517impl From<i8> for ValueType { fn from(value: i8) -> Self { ValueType::Int8(value) } }
2518impl From<i16> for ValueType { fn from(value: i16) -> Self { ValueType::Int16(value) } }
2519impl From<i32> for ValueType { fn from(value: i32) -> Self { ValueType::Int32(value) } }
2520impl From<i64> for ValueType { fn from(value: i64) -> Self { ValueType::Int64(value) } }
2521impl From<i128> for ValueType { fn from(value: i128) -> Self { ValueType::Int128(value) } }
2522impl From<usize> for ValueType { fn from(value: usize) -> Self { ValueType::Usize(value) } }
2523impl From<u8> for ValueType { fn from(value: u8) -> Self { ValueType::Uint8(value) } }
2524impl From<u16> for ValueType { fn from(value: u16) -> Self { ValueType::Uint16(value) } }
2525impl From<u32> for ValueType { fn from(value: u32) -> Self { ValueType::Uint32(value) } }
2526impl From<u64> for ValueType { fn from(value: u64) -> Self { ValueType::Uint64(value) } }
2527impl From<f32> for ValueType { fn from(value: f32) -> Self { ValueType::Float32(value) } }
2528impl From<f64> for ValueType { fn from(value: f64) -> Self { ValueType::Float64(value) } }
2529
2530
2531impl Into<String> for ValueType {
2532    fn into(self) -> String {
2533        match self {
2534            ValueType::String(text) => text,
2535            ValueType::Datetime(datetime) => datetime,
2536            _ => panic!("you cannot convert a ValueType to string unless it's not a String or Datetime variant.")
2537        }
2538    }
2539}
2540
2541impl Into<bool> for ValueType {
2542    fn into(self) -> bool {
2543        match self {
2544            ValueType::Boolean(val) => val,
2545            ValueType::String(text) => match text.as_str() {
2546                "false" | "" | "\0" | "0" => false,
2547                _ => true,
2548            }
2549            ValueType::Null => false,
2550            ValueType::Int8(val) => match val == 0 {
2551                false => true,
2552                true => false
2553            },
2554            ValueType::Int16(val) => match val == 0 {
2555                false => true,
2556                true => false
2557            },
2558            ValueType::Int32(val) => match val == 0 {
2559                false => true,
2560                true => false
2561            },
2562            ValueType::Int64(val) => match val == 0 {
2563                false => true,
2564                true => false
2565            },
2566            ValueType::Int128(val) => match val == 0 {
2567                false => true,
2568                true => false
2569            },
2570            ValueType::Uint8(val) => match val == 0 {
2571                false => true,
2572                true => false
2573            },
2574            ValueType::Uint16(val) => match val == 0 {
2575                false => true,
2576                true => false
2577            },
2578            ValueType::Uint32(val) => match val == 0 {
2579                false => true,
2580                true => false
2581            },
2582            ValueType::Uint64(val) => match val == 0 {
2583                false => true,
2584                true => false
2585            },
2586            ValueType::Float32(val) => match val == 0.0 {
2587                false => true,
2588                true => false
2589            },
2590            ValueType::Float64(val) => match val == 0.0 {
2591                false => true,
2592                true => false
2593            },
2594            _ => panic!("invalid conversion")
2595        }
2596    }
2597}
2598
2599impl Into<f32> for ValueType {
2600    fn into(self) -> f32 {
2601        match self {
2602            ValueType::Float32(num) => num,
2603            ValueType::Float64(num) => num as f32,
2604            _ => panic!("invalid conversion")
2605        }
2606    }
2607}
2608
2609impl Into<f64> for ValueType {
2610    fn into(self) -> f64 {
2611        match self {
2612            ValueType::Float32(num) => num as f64,
2613            ValueType::Float64(num) => num,
2614            _ => panic!("invalid conversion")
2615        }
2616    }
2617}
2618
2619impl Into<i8> for ValueType {
2620    fn into(self) -> i8 {
2621        match self {
2622            ValueType::Int8(num) => num,
2623            ValueType::Int16(num) => match num > 128 || num < -128 {
2624                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2625                false => num as i8
2626            },
2627            ValueType::Int32(num) => match num > 128 || num < -128 {
2628                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2629                false => num as i8
2630            },
2631            ValueType::Int64(num) => match num > 128 || num < -128 {
2632                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2633                false => num as i8
2634            },
2635            ValueType::Int128(num) => match num > 128 || num < -128 {
2636                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2637                false => num as i8
2638            }
2639            ValueType::Uint8(num) => match num > 128 {
2640                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2641                false => num as i8
2642            },
2643            ValueType::Uint16(num) => match num > 128 {
2644                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2645                false => num as i8
2646            },
2647            ValueType::Uint32(num) => match num > 128 {
2648                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2649                false => num as i8
2650            },
2651            ValueType::Usize(num) => match num > 128 {
2652                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2653                false => num as i8
2654            },
2655            ValueType::Uint64(num) => match num > 128 {
2656                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2657                false => num as i8
2658            },
2659            _ => panic!("you cannot convert non numeric values into numeric ones.")
2660        }
2661    }
2662}
2663
2664impl Into<i16> for ValueType {
2665    fn into(self) -> i16 {
2666        match self {
2667            ValueType::Int8(num) => num as i16,
2668            ValueType::Int16(num) => num,
2669            ValueType::Int32(num) => match num > 32_768 || num < -32_768 {
2670                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2671                false => num as i16
2672            },
2673            ValueType::Int64(num) => match num > 32_768 || num < -32_768 {
2674                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2675                false => num as i16
2676            },
2677            ValueType::Int128(num) => match num > 32_768 || num < -32_768 {
2678                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2679                false => num as i16
2680            }
2681            ValueType::Uint8(num) => num as i16,
2682            ValueType::Uint16(num) => match num > 32_768 {
2683                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2684                false => num as i16
2685            },
2686            ValueType::Uint32(num) => match num > 32_768 {
2687                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2688                false => num as i16
2689            },
2690            ValueType::Usize(num) => match num > 32_768 {
2691                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2692                false => num as i16
2693            },
2694            ValueType::Uint64(num) => match num > 32_768 {
2695                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2696                false => num as i16
2697            },
2698            _ => panic!("you cannot convert non numeric values into numeric ones.")
2699        }
2700    }
2701}
2702
2703impl Into<i32> for ValueType {
2704    fn into(self) -> i32 {
2705        match self {
2706            ValueType::Int8(num) => num as i32,
2707            ValueType::Int16(num) => num as i32,
2708            ValueType::Int32(num) => num,
2709            ValueType::Int64(num) => match num > 2_147_483_647 || num < -2_147_483_647 {
2710                true => panic!("you cannot convert i32's into a value which is bigger than the capacity of 32 bit values."),
2711                false => num as i32
2712            },
2713            ValueType::Int128(num) => match num > 2_147_483_647 || num < -2_147_483_647 {
2714                true => panic!("you cannot convert i32's into a value which is bigger than the capacity of 32 bit values."),
2715                false => num as i32
2716            }
2717            ValueType::Uint8(num) => num as i32,
2718            ValueType::Uint16(num) => num as i32,
2719            ValueType::Uint32(num) => match num > 2_147_483_647 {
2720                true => panic!("you cannot convert i32's into a value which is bigger than the capacity of 32 bit values."),
2721                false => num as i32
2722            },
2723            ValueType::Usize(num) => match num > 2_147_483_647 {
2724                true => panic!("you cannot convert i32's into a value which is bigger than the capacity of 32 bit values."),
2725                false => num as i32
2726            },
2727            ValueType::Uint64(num) => match num > 2_147_483_647 {
2728                true => panic!("you cannot convert i32's into a value which is bigger than the capacity of 32 bit values."),
2729                false => num as i32
2730            }
2731            _ => panic!("you cannot convert non numeric values into numeric ones.")
2732        }
2733    }
2734}
2735
2736impl Into<i64> for ValueType {
2737    fn into(self) -> i64 {
2738        match self {
2739            ValueType::EpochTime(epoch) => epoch as i64,
2740            ValueType::Int8(num) => num as i64,
2741            ValueType::Int16(num) => num as i64,
2742            ValueType::Int32(num) => num as i64,
2743            ValueType::Int64(num) => num,
2744            ValueType::Usize(num) => num as i64,
2745            ValueType::Uint8(num) => num as i64,
2746            ValueType::Uint16(num) => num as i64,
2747            ValueType::Uint32(num) => num as i64,
2748            ValueType::Uint64(num) => num as i64,
2749            _ => panic!("you cannot convert non numeric values into numeric ones.")
2750        }
2751    }
2752}
2753
2754impl Into<u8> for ValueType {
2755    fn into(self) -> u8 {
2756        match self {
2757            ValueType::Uint8(num) => num,
2758            ValueType::Uint16(num) => match num > 255 {
2759                true => panic!("you cannot convert u16's if it's value is bigger than capacity of 8 bit values"),
2760                false => num as u8
2761            },
2762            ValueType::Uint32(num) => match num > 255 {
2763                true => panic!("you cannot convert u32's if it's value is bigger than capacity of 8 bit values"),
2764                false => num as u8
2765            },
2766            ValueType::Uint64(num) => match num > 255 {
2767                true => panic!("you cannot convert u64's if it's value is bigger than capacity of 8 bit values"),
2768                false => num as u8
2769            },
2770            ValueType::Usize(num) => match num > 255 {
2771                true => panic!("you cannot convert usizes if it's value is bigger than capacity of 8 bit values"),
2772                false => num as u8
2773            },
2774            ValueType::Int8(num) => match num < 0 {
2775                true => panic!("you cannot convert i8's if it's value is lower than 0"),
2776                false => num as u8
2777            },
2778            ValueType::Int16(num) => match num < 0 || num > 255 {
2779                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."),
2780                false => num as u8
2781            },
2782            ValueType::Int32(num) => match num < 0 || num > 255 {
2783                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."),
2784                false => num as u8
2785            },
2786            ValueType::Int64(num) => match num < 0 || num > 255 {
2787                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."),
2788                false => num as u8
2789            },
2790            ValueType::Int128(num) => match num < 0 || num > 255 {
2791                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."),
2792                false => num as u8
2793            }
2794            _ => panic!("you cannot convert non numeric values into numeric ones.")
2795        }
2796    }
2797}
2798
2799impl Into<u16> for ValueType {
2800    fn into(self) -> u16 {
2801        match self {
2802            ValueType::Uint8(num) => num as u16,
2803            ValueType::Uint16(num) => num,
2804            ValueType::Uint32(num) => match num > 65_535 {
2805                true => panic!("you cannot convert u32's if it's value is bigger than capacity of 32 bit values"),
2806                false => num as u16
2807            },
2808            ValueType::Uint64(num) => match num > 65_535 {
2809                true => panic!("you cannot convert u64's if it's value is bigger than capacity of 32 bit values"),
2810                false => num as u16
2811            },
2812            ValueType::Usize(num) => match num > 65_535 {
2813                true => panic!("you cannot convert usizes if it's value is bigger than capacity of 32 bit values"),
2814                false => num as u16
2815            },
2816            ValueType::Int8(num) => match num < 0 {
2817                true => panic!("you cannot convert i8's if it's value is lower than 0"),
2818                false => num as u16
2819            },
2820            ValueType::Int16(num) => match num < 0 {
2821                true => panic!("you cannot convert i16's if it's value is lower than 0"),
2822                false => num as u16
2823            },
2824            ValueType::Int32(num) => match num < 0 || num > 65_535 {
2825                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."),
2826                false => num as u16
2827            },
2828            ValueType::Int64(num) => match num < 0 || num > 65_535 {
2829                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."),
2830                false => num as u16
2831            },
2832            ValueType::Int128(num) => match num < 0 || num > 65_535 {
2833                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."),
2834                false => num as u16
2835            }
2836            _ => panic!("you cannot convert non numeric values into numeric ones.")
2837        }
2838    }
2839}
2840
2841impl Into<u32> for ValueType {
2842    fn into(self) -> u32 {
2843        match self {
2844            ValueType::Uint8(num) => num as u32,
2845            ValueType::Uint16(num) => num as u32,
2846            ValueType::Uint32(num) => num,
2847            ValueType::Uint64(num) => match num > 4_294_967_295 {
2848                true => panic!("you cannot convert u64's if it's value is bigger than capacity of 32 bit values"),
2849                false => num as u32
2850            },
2851            ValueType::Usize(num) => match num > 4_294_967_295 {
2852                true => panic!("you cannot convert usizes if it's value is bigger than capacity of 32 bit values"),
2853                false => num as u32
2854            },
2855            ValueType::Int8(num) => match num < 0 {
2856                true => panic!("you cannot convert i8's if it's value is lower than 0"),
2857                false => num as u32
2858            },
2859            ValueType::Int16(num) => match num < 0 {
2860                true => panic!("you cannot convert i16's if it's value is lower than 0"),
2861                false => num as u32
2862            },
2863            ValueType::Int32(num) => match num < 0 {
2864                true => panic!("you cannot convert i32's if it's value is lower than 0"),
2865                false => num as u32
2866            },
2867            ValueType::Int64(num) => match num < 0 || num > 4_294_967_295 {
2868                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."),
2869                false => num as u32
2870            },
2871            ValueType::Int128(num) => match num < 0 || num > 4_294_967_295 {
2872                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."),
2873                false => num as u32
2874            }
2875            _ => panic!("you cannot convert non numeric values into numeric ones.")
2876        }
2877    }
2878}
2879
2880impl Into<u64> for ValueType {
2881    fn into(self) -> u64 {
2882        match self {
2883            ValueType::Usize(num) => num as u64,
2884            ValueType::Uint8(num) => num as u64,
2885            ValueType::Uint16(num) => num as u64,
2886            ValueType::Uint32(num) => num as u64,
2887            ValueType::Uint64(num) => num,
2888            ValueType::Int8(num) => match num < 0 {
2889                true => panic!("you cannot turn a negative value into u64"),
2890                false => num as u64
2891            },
2892            ValueType::Int16(num) => match num < 0 {
2893                true => panic!("you cannot turn a negative value into u64"),
2894                false => num as u64
2895            },
2896            ValueType::Int32(num) => match num < 0 {
2897                true => panic!("you cannot turn a negative value into u64"),
2898                false => num as u64
2899            },
2900            ValueType::Int64(num) => match num < 0 {
2901                true => panic!("you cannot turn a negative value into u64"),
2902                false => num as u64
2903            },
2904            ValueType::Int128(num) => match num < 0 {
2905                true => panic!("you cannot turn a negative value into u64"),
2906                false => num as u64
2907            },
2908            _ => panic!("you cannot convert non numeric values into numeric ones.")
2909        }
2910    }
2911}
2912
2913impl Into<usize> for ValueType {
2914    fn into(self) -> usize {
2915        match self {
2916            ValueType::Int8(num) => match num < 0 {
2917                true => panic!("you cannot convert negative numbers to usize"),
2918                false => num as usize
2919            },
2920            ValueType::Int16(num) => match num < 0 {
2921                true => panic!("you cannot convert negative numbers to usize"),
2922                false => num as usize
2923            },
2924            ValueType::Int32(num) => match num < 0 {
2925                true => panic!("you cannot convert negative numbers to usize"),
2926                false => num as usize
2927            },
2928            ValueType::Int64(num) => match num < 0 {
2929                true => panic!("you cannot convert negative numbers to usize"),
2930                false => num as usize
2931            },
2932            ValueType::Int128(num) => match num < 0 {
2933                true => panic!("you cannot convert negative numbers to usize"),
2934                false => num as usize
2935            },
2936            ValueType::Usize(num) => num,
2937            ValueType::Uint8(num) => num as usize,
2938            ValueType::Uint16(num) => num as usize,
2939            ValueType::Uint32(num) => num as usize,
2940            ValueType::Uint64(num) => num as usize,
2941            _ => panic!("you cannot convert non numeric values into numeric ones.")
2942        }
2943    }
2944}
2945
2946/// Enum that benefits you to add json values to structs. They can be used with json functions.
2947/// That variants represents that kind of json values:
2948#[derive(Debug, Clone)]
2949pub enum JsonValue<'a> {
2950    /// 
2951    /// Example Value: ["hello", 21, "again"]
2952    /// 
2953    Array(&'a Vec<ValueType>), 
2954    
2955    /// 
2956    /// Example Value: {"name": "necdet", "message": "hello", "id": 1}
2957    /// 
2958    Object(&'a Vec<(&'a str, &'a ValueType)>), 
2959    
2960    ///
2961    /// example value: [{"name": "necdet", "message": "hello", "id": 1}, {"name": "kemal", "message": "hi", "id": 2}]
2962    /// 
2963    ObjectArray(&'a Vec<Vec<(&'a str, &'a ValueType)>>), 
2964    
2965    /// It's same with `ValueType` enums, just for simply passing it to that enum.
2966    Initial(&'a ValueType), 
2967
2968    /// 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.
2969    MysqlJsonObject(&'a Vec<(&'a str, &'a ValueType)>)
2970}
2971
2972impl <'a>std::fmt::Display for JsonValue<'a> {
2973    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
2974        match self {
2975            JsonValue::Array(values) => {
2976                let mut json_str = "[".to_string();
2977
2978                for (index, value) in values.iter().enumerate() {
2979                    if index == 0 {
2980                        json_str = format!("{}{}", json_str, value)
2981                    } else {
2982                        json_str = format!("{}, {}", json_str, value)
2983                    }
2984                }
2985
2986                json_str = format!("{}]", json_str);
2987
2988                write!(f, "{}", json_str)
2989            },
2990            JsonValue::Object(props) => {
2991                let mut json_str = "{".to_string();
2992
2993                for (index, value) in props.iter().enumerate() {
2994                    if index == 0 {
2995                        json_str = format!("{}\"{}\": {}", json_str, value.0, value.1)
2996                    } else {
2997                        json_str = format!("{}, \"{}\": {}", json_str, value.0, value.1)
2998                    }
2999                }
3000
3001                json_str = format!("{}}}", json_str);
3002
3003                write!(f, "{}", json_str)
3004            },
3005            JsonValue::MysqlJsonObject(props) => {
3006                let mut json_str = "JSON_OBJECT(".to_string();
3007
3008                for (index, value) in props.iter().enumerate() {
3009                    if index == 0 {
3010                        json_str = format!("{}'{}', {}", json_str, value.0, value.1)
3011                    } else {
3012                        json_str = format!("{}, '{}', {}", json_str, value.0, value.1)
3013                    }
3014                }
3015
3016                json_str = format!("{})", json_str);
3017
3018                write!(f, "{}", json_str)
3019            },
3020            JsonValue::ObjectArray(array) => {
3021                let mut json_str = "[".to_string();
3022
3023                for (index1, object) in array.into_iter().enumerate() {
3024                    let mut object_str = "{".to_string();
3025
3026                    for (index2, property) in object.into_iter().enumerate() {
3027                        if index2 == 0 {
3028                            object_str = format!("{}\"{}\": {}", object_str, property.0, property.1)
3029                        } else {
3030                            object_str = format!("{}, \"{}\": {}", object_str, property.0, property.1)
3031                        }
3032                    }
3033
3034                    object_str = format!("{}}}", object_str);
3035
3036                    if index1 == 0 {
3037                        json_str = format!("{}{}", json_str, object_str)
3038                    } else {
3039                        json_str = format!("{}, {}", json_str, object_str)
3040                    }
3041                }
3042
3043                write!(f, "{}]", json_str)
3044            },
3045            JsonValue::Initial(value) => write!(f, "{}", value.to_string())
3046        }
3047    }
3048}
3049
3050/// Enum that benefits you to define what you want with a foreign key.
3051#[derive(Debug, Clone)]
3052pub enum ForeignKeyActions {
3053    Cascade, Restrict, SetNull, NoAction, SetDefault
3054}
3055
3056impl std::fmt::Display for ForeignKeyActions {
3057    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
3058        match self {
3059            &ForeignKeyActions::Cascade => write!(f, "CASCADE"),
3060            &ForeignKeyActions::NoAction => write!(f, "NO ACTION"),
3061            &ForeignKeyActions::Restrict => write!(f, "RESTRICT"),
3062            &ForeignKeyActions::SetNull => write!(f, "SET NULL"),
3063            &ForeignKeyActions::SetDefault => write!(f, "SET DEFAULT")
3064        }
3065    }
3066}
3067
3068#[cfg(test)]
3069mod test {
3070    use super::*;
3071
3072    #[test]
3073    pub fn test_schema_query_declarative(){
3074        let schema = SchemaBuilder::create("blog_website").unwrap().if_not_exists().finish();
3075
3076        assert_eq!("CREATE DATABASE IF NOT EXISTS blog_website;", schema);
3077    }
3078
3079    #[test]
3080    pub fn test_schema_query_imperative(){
3081        let mut schema = SchemaBuilder::create("blog_website").unwrap();
3082        schema.if_not_exists();
3083        let schema_query = schema.finish();
3084
3085        assert_eq!("CREATE DATABASE IF NOT EXISTS blog_website;", schema_query);
3086    }
3087
3088    #[test]
3089    pub fn test_use_another_schema(){
3090        let schema = SchemaBuilder::use_another_schema("chat_website").unwrap().finish();
3091
3092        assert_eq!("USE chat_website;", schema);
3093    }
3094
3095    #[test]
3096    pub fn test_insert_query(){
3097        let columns = vec!["title", "author", "description"];
3098        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())];
3099    
3100        let insert_query = QueryBuilder::insert(columns, values).unwrap().table("blogs").finish();
3101
3102        println!("{}", insert_query);
3103        assert_eq!("INSERT INTO blogs (title, author, description) VALUES ('What's Up?', 'John Doe', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.');".to_string(), 
3104                    insert_query);
3105    }
3106
3107    #[test]
3108    pub fn test_update_query(){
3109        let update_query = QueryBuilder::update().unwrap().table("blogs").set("title", ValueType::String("Hello Rust!".to_string())).set("author", ValueType::String("Necdet".to_string())).finish();
3110
3111        assert_eq!("UPDATE blogs SET title = 'Hello Rust!', author = 'Necdet';", update_query);
3112    }
3113
3114    #[test]
3115    pub fn test_delete_query(){
3116        let delete_query = QueryBuilder::delete().unwrap().table("blogs").where_("id", "=", ValueType::String("1".to_string())).finish();
3117
3118        assert_eq!("DELETE FROM blogs WHERE id = '1';", delete_query);
3119    }
3120
3121    #[test]
3122    pub fn test_select_query_declarative(){
3123        let mut select = QueryBuilder::select(["id", "title", "description", "point"].to_vec()).unwrap();
3124
3125        let select_query = select.table("blogs")
3126                                    .where_("id", "=", ValueType::Int32(10))
3127                                    .and("point", ">", ValueType::Int8(90))
3128                                    .or("id", "=", ValueType::Int64(20))
3129                                    .finish();
3130
3131        assert_eq!("SELECT id, title, description, point FROM blogs WHERE id = 10 AND point > 90 OR id = 20;", select_query)
3132    }
3133
3134    #[test]
3135    pub fn test_select_query_imperative(){
3136        let mut select = QueryBuilder::select(["*"].to_vec()).unwrap();
3137
3138        let select_query = select.table("blogs");
3139        select_query.where_("id", "=", ValueType::Uint8(5));
3140        select_query.or("id", "=", ValueType::Usize(25));
3141
3142        let finish_the_select_query = select_query.finish();
3143
3144        assert_eq!("SELECT * FROM blogs WHERE id = 5 OR id = 25;", finish_the_select_query);
3145    }
3146
3147    #[test]
3148    pub fn test_create_table() {
3149        let mut table_builder_2 = TableBuilder::create("blabla", "projects");
3150        let table_builder_2 = table_builder_2.if_not_exists();
3151    
3152        table_builder_2.add_column("id").col_type("INT").primary_key().auto_increment();
3153        table_builder_2.add_column("name").col_type("VARCHAR(40)").not_null();
3154        table_builder_2.add_column("owner_id").col_type("INT").not_null();
3155        
3156        // if we create a table, the first ForeignKeyItem's table field is not necessary.
3157        let opts = ForeignKey {
3158            first: ForeignKeyItem { table: "".to_string(), column: "owner_id".to_string() },
3159            second: ForeignKeyItem { table: "users".to_string(), column: "id".to_string() },
3160            constraint: None,
3161            on_delete: Some(ForeignKeyActions::Cascade),
3162            on_update: None
3163        };
3164        
3165        table_builder_2.foreign_key(opts);
3166    
3167        let table_builder_2 = table_builder_2.finish();
3168
3169        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();
3170
3171        assert_eq!(raw_query, table_builder_2);
3172    }
3173
3174    #[test]
3175    pub fn test_time_value_type(){
3176        let columns = ["name", "password", "last_login"].to_vec();
3177        let values = [ValueType::String("necoo33".to_string()), ValueType::String("123456".to_string()), ValueType::Datetime("CURRENT_TIMESTAMP".to_string())].to_vec();
3178    
3179        let time_insert_test = QueryBuilder::insert(columns, values).unwrap().table("users").finish();
3180
3181        assert_eq!(time_insert_test, "INSERT INTO users (name, password, last_login) VALUES ('necoo33', '123456', CURRENT_TIMESTAMP);");
3182
3183        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();
3184
3185        assert_eq!(time_update_test, "UPDATE users SET last_login = CURRENT_TIMESTAMP WHERE name = 'necoo33';")
3186    }
3187
3188    #[test]
3189    pub fn test_unix_epoch_times(){
3190        let columns = ["name", "password", "last_login"].to_vec();
3191        let values = [ValueType::String("necoo33".to_string()), ValueType::String("123456".to_string()), ValueType::EpochTime(134523452)].to_vec();
3192    
3193        let time_insert_with_unix_epoch_times_test = QueryBuilder::insert(columns, values).unwrap().table("users").finish();
3194        assert_eq!(time_insert_with_unix_epoch_times_test, "INSERT INTO users (name, password, last_login) VALUES ('necoo33', '123456', FROM_UNIXTIME(134523452));");
3195    
3196        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();
3197
3198        assert_eq!(time_update_with_unix_epoch_times_test, "UPDATE users SET last_login = FROM_UNIXTIME(3456436) WHERE name = 'necoo33';");
3199
3200        let columns = ["name", "password", "last_login", "created_at"].to_vec();
3201
3202        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();
3203
3204        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;")
3205    }
3206
3207    #[test]
3208    pub fn test_where_ins(){
3209        let columns = ["name", "age", "id", "last_login"].to_vec();
3210
3211        let ids = [ValueType::Int32(1), ValueType::Int16(12), ValueType::Int64(8)].to_vec();
3212
3213        let test_where_in = QueryBuilder::select(columns).unwrap().table("users").where_in("id", &ids).finish();
3214
3215        assert_eq!(test_where_in, "SELECT name, age, id, last_login FROM users WHERE id IN (1, 12, 8);");
3216
3217        let columns = ["name", "age", "id", "last_login"].to_vec();
3218
3219        let ids = [ValueType::Int32(1), ValueType::Int16(12), ValueType::Int32(8)].to_vec();
3220
3221        let test_where_not_in = QueryBuilder::select(columns).unwrap().table("users").where_not_in("id", &ids).finish();
3222
3223        assert_eq!(test_where_not_in, "SELECT name, age, id, last_login FROM users WHERE id NOT IN (1, 12, 8);");
3224
3225        let columns = ["name", "age", "id", "last_login"].to_vec();
3226
3227        let test_where_in_custom = QueryBuilder::select(columns).unwrap().table("users").where_in_custom("id", "1, 12, 8").finish();
3228
3229        assert_eq!(test_where_in_custom, "SELECT name, age, id, last_login FROM users WHERE id IN (1, 12, 8);");
3230
3231        let columns = ["name", "age", "id", "last_login"].to_vec();
3232
3233        let test_where_not_in_custom = QueryBuilder::select(columns).unwrap().table("users").where_not_in_custom("id", "1, 12, 8").finish();
3234
3235        assert_eq!(test_where_not_in_custom, "SELECT name, age, id, last_login FROM users WHERE id NOT IN (1, 12, 8);")
3236    }
3237
3238    #[test]
3239    pub fn test_count() {
3240        let count_of_users = QueryBuilder::count("*", None).table("users").where_("age", ">", ValueType::Int32(25)).finish();
3241
3242        assert_eq!(count_of_users, "SELECT COUNT(*) FROM users WHERE age > 25;".to_string());
3243
3244        let count_of_users_as_length = QueryBuilder::count("*", Some("length")).table("users").finish();
3245
3246        assert_eq!(count_of_users_as_length, "SELECT COUNT(*) AS length FROM users;".to_string());
3247    }
3248
3249    #[test]
3250    pub fn test_json_extract(){
3251        // tests with "select()" constructor
3252
3253        let select_query_1 = QueryBuilder::select(["*"].to_vec()).unwrap().json_extract("data", ".age", Some("student_age")).table("students").finish();
3254
3255        assert_eq!(select_query_1, "SELECT JSON_EXTRACT(data, '$.age') AS student_age FROM students;".to_string());
3256        
3257        let select_query_2 = QueryBuilder::select(["*"].to_vec()).unwrap().json_extract("data", ".age", Some("student_age")).table("students").where_("successfull", "=", ValueType::Int8(1)).finish();
3258
3259        assert_eq!(select_query_2, "SELECT JSON_EXTRACT(data, '$.age') AS student_age FROM students WHERE successfull = 1;".to_string());
3260        
3261        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();
3262
3263        assert_eq!(select_query_3, "SELECT JSON_EXTRACT(data, '$.age') AS student_age FROM students WHERE JSON_EXTRACT(points, '$.name') > 85;".to_string());
3264
3265        // tests with ".where_cond()" method
3266
3267        let with_where = QueryBuilder::delete().unwrap().table("users").where_("id", ">", ValueType::Int32(200)).json_extract("id", ".user_id", None).finish();
3268
3269        assert_eq!(with_where, "DELETE FROM users WHERE JSON_EXTRACT(id, '$.user_id') > 200;".to_string());
3270
3271        // tests with ".table()" method
3272        
3273        let fields = ["name", "age"].to_vec();
3274        
3275        let with_table = QueryBuilder::select(fields).unwrap().table("users").json_extract("id", ".user_id", None).finish();
3276
3277        assert_eq!(with_table, "SELECT JSON_EXTRACT(id, '$.user_id') FROM users;".to_string());
3278
3279        // tests with ".and()" method
3280
3281        let fields = ["name", "age"].to_vec();
3282
3283        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();
3284
3285        assert_eq!(with_and_1, "SELECT name, age FROM height WHERE weight > 60 AND JSON_EXTRACT(height, '$.student_height') > 1.7;".to_string());
3286    
3287        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();
3288
3289        assert_eq!(with_and_2, "SELECT * FROM students WHERE weight > 60 AND JSON_EXTRACT(height, '$.student_height') > 1.7;".to_string());
3290
3291        // tests with ".or()" method
3292
3293        let fields = ["name", "age"].to_vec();
3294
3295        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();
3296
3297        assert_eq!(with_or_1, "SELECT name, age FROM height WHERE weight > 60 OR JSON_EXTRACT(height, '$.student_height') > 1.71;".to_string());
3298    
3299        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();
3300
3301        assert_eq!(with_or_2, "SELECT * FROM students WHERE weight > 60 OR JSON_EXTRACT(height, '$.student_height') > 1.71;".to_string());
3302
3303        // tests with "count()" constructor
3304
3305        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();
3306
3307        assert_eq!(count_query_1, "SELECT JSON_EXTRACT(age, '$.student_age') AS value, COUNT(*) FROM students GROUP BY points HAVING points > 75;".to_string());
3308
3309        // tests with ".order_by()" method
3310        
3311        let fields = ["title", "desc", "created_at", "updated_at", "keywords", "pics", "likes"].to_vec();
3312
3313        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();
3314
3315        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());
3316    
3317        // tests with ".json_extract()" method
3318
3319        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();
3320
3321        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());
3322    }
3323
3324    #[test]
3325    pub fn test_json_contains(){
3326        // test with "select()" constructor:
3327
3328        let ins = [ValueType::Int32(1), ValueType::Int32(5), ValueType::Int64(11)].to_vec();
3329        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();
3330
3331        assert_eq!(select_query, "SELECT JSON_CONTAINS(pic, '\"/files/hello.jpg\"', '$.path') FROM users WHERE id IN (1, 5, 11);".to_string());
3332
3333        // test with ".where_cond()" method:
3334
3335        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();
3336
3337        assert_eq!(where_query, "SELECT * FROM users WHERE JSON_CONTAINS(pic, '\"blablabla.jpg\"', '$.name');".to_string());
3338
3339        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();
3340
3341        assert_eq!(and_query_1, "SELECT * FROM users WHERE age > 15 AND JSON_CONTAINS(graduation_stats, 80.11, '$.average_point');".to_string());
3342
3343        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();
3344
3345        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());
3346        
3347        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();
3348    
3349        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());
3350
3351        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();
3352    
3353        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());
3354
3355        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();
3356    
3357        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());
3358        
3359        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();
3360
3361        assert_eq!(or_query_1, "SELECT * FROM users WHERE age > 15 OR JSON_CONTAINS(graduation_stats, 80.11, '$.average_point');".to_string());
3362        
3363        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();
3364        
3365        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());
3366                
3367        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();
3368            
3369        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());
3370        
3371        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();
3372            
3373        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());
3374        
3375        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();
3376            
3377        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());
3378
3379        let name = ValueType::JsonString("necdet".to_string());
3380        let id = ValueType::Int32(1);
3381        let is_active = ValueType::Boolean(true);
3382
3383        let object = vec![("name", &name), ("id", &id), ("isActive", &is_active)];
3384
3385        let mysql_json_object = JsonValue::MysqlJsonObject(&object);
3386
3387        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();
3388
3389        assert_eq!("SELECT * FROM users WHERE JSON_CONTAINS(pic, JSON_OBJECT('name', \"necdet\", 'id', 1, 'isActive', true), '$');", where_query_2)
3390    }
3391
3392    #[test]
3393    pub fn test_like_later_than_where_keywords(){
3394        let mut like_query_1 = QueryBuilder::select(["*"].to_vec()).unwrap();
3395
3396        let like_query_1 = like_query_1.table("blogs")
3397                                                      .where_("id", "=", ValueType::Int32(5))
3398                                                      .like(["title", "description"].to_vec(), "hello")
3399                                                      .finish();
3400
3401        assert_eq!(like_query_1, "SELECT * FROM blogs WHERE id = 5 AND (title LIKE '%hello%' OR description LIKE '%hello%');");
3402    
3403        let mut like_query_2 = QueryBuilder::select(["*"].to_vec()).unwrap();
3404
3405        let ins = vec![ValueType::Int32(1), ValueType::Int32(2), ValueType::Int32(3)];
3406        let like_query_2 = like_query_2.table("blogs")
3407                                                          .where_in("id", &ins)
3408                                                          .like(["title", "description", "keywords"].to_vec(), "necdet")
3409                                                          .limit(10)
3410                                                          .offset(0)
3411                                                          .finish();
3412
3413        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;")
3414    }
3415
3416    #[test]
3417    pub fn test_ordering_functions(){
3418        let order_by_query = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").order_by("id", "asc").order_by("weight", "desc").order_by("point", "asc").finish();
3419
3420        assert_eq!(order_by_query, "SELECT * FROM users ORDER BY id ASC, weight DESC, point ASC;");
3421
3422        let order_by_random_query = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").order_random().finish();
3423
3424        assert_eq!(order_by_random_query, "SELECT * FROM users ORDER BY RAND();");
3425
3426        let roles = ["admin", "moderator", "member", "guest"].to_vec();
3427        let field_query_1 = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").order_by_field("role", roles.clone()).finish();
3428
3429        assert_eq!(field_query_1, "SELECT * FROM users ORDER BY FIELD(role, 'admin', 'moderator', 'member', 'guest');");
3430
3431        let field_query_2 = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").order_by("id", "asc").order_by_field("role", roles.clone()).finish();
3432
3433        assert_eq!(field_query_2, "SELECT * FROM users ORDER BY id ASC, FIELD(role, 'admin', 'moderator', 'member', 'guest');");
3434
3435        let field_query_3 = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").order_by_field("role", roles.clone()).order_by("id", "asc").finish();
3436
3437        assert_eq!(field_query_3, "SELECT * FROM users ORDER BY FIELD(role, 'admin', 'moderator', 'member', 'guest'), id ASC;");
3438        
3439        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();
3440
3441        assert_eq!(field_query_4, "SELECT * FROM users ORDER BY FIELD(role, 'admin', 'moderator', 'member', 'guest'), FIELD(status, 'active', 'banned', 'unverified');");
3442    }
3443
3444    #[test]
3445    pub fn test_unions(){
3446        let mut union_1 = QueryBuilder::select(vec!["name", "age", "id"]).unwrap();
3447        union_1.table("users").where_("age", ">", ValueType::Int32(7));
3448
3449        let union_2 = QueryBuilder::select(vec!["name", "age", "id"]).unwrap()
3450                                                          .table("users")
3451                                                          .where_("age", "<", ValueType::Int32(15))
3452                                                          .union(vec![union_1])
3453                                                          .finish();
3454
3455        assert_eq!(union_2, "(SELECT name, age, id FROM users WHERE age < 15) UNION (SELECT name, age, id FROM users WHERE age > 7);");
3456
3457        let mut union_1 = QueryBuilder::select(vec!["id", "title", "description", "published"]).unwrap();
3458        union_1.table("blogs").like(vec!["title"], "text");
3459
3460        let mut union_2 = QueryBuilder::select(vec!["id", "title", "description", "published"]).unwrap();
3461        union_2.table("blogs").like(vec!["description"], "some text");
3462
3463        let union_3 = QueryBuilder::select(vec!["id", "title", "description", "published"]).unwrap()
3464                                                              .table("blogs")
3465                                                              .where_("published", "=", ValueType::Boolean(true))
3466                                                              .union_all(vec![union_1, union_2])
3467                                                              .finish();
3468
3469        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%');");
3470    }
3471
3472    #[test]
3473    pub fn test_json_value(){
3474        let name = ValueType::JsonString("necdet".to_string());
3475        let age = ValueType::Int8(25);
3476        let id = ValueType::Int32(1);
3477
3478        let values = vec![("name", &name), ("age", &age), ("id", &id)];
3479
3480        let json_object = JsonValue::Object(&values);
3481
3482        assert_eq!("{\"name\": \"necdet\", \"age\": 25, \"id\": 1}", json_object.to_string());
3483
3484        let mysql_json_object = JsonValue::MysqlJsonObject(&values);
3485
3486        assert_eq!("JSON_OBJECT('name', \"necdet\", 'age', 25, 'id', 1)", mysql_json_object.to_string());
3487
3488        let name2 = ValueType::JsonString("cevdet".to_string());
3489        let age2 = ValueType::Int8(24);
3490        let id2 = ValueType::Int32(2);
3491
3492        let name3 = ValueType::JsonString("serap".to_string());
3493        let age3 = ValueType::Int8(21);
3494        let id3 = ValueType::Int32(3);
3495
3496        let object1 = vec![("name", &name), ("age", &age), ("id", &id)];
3497        let object2 = vec![("name", &name2), ("age", &age2), ("id", &id2)];
3498        let object3 = vec![("name", &name3), ("age", &age3), ("id", &id3)];
3499
3500        let objects = vec![object1, object2, object3];
3501        
3502        let json_array = JsonValue::ObjectArray(&objects);
3503
3504        assert_eq!("[{\"name\": \"necdet\", \"age\": 25, \"id\": 1}, {\"name\": \"cevdet\", \"age\": 24, \"id\": 2}, {\"name\": \"serap\", \"age\": 21, \"id\": 3}]", json_array.to_string());
3505    }
3506
3507    #[test]
3508    pub fn test_json_array_append(){
3509        let lesson = ("lesson", &ValueType::String("math".to_string()));
3510        let point = ("point", &ValueType::Int32(100));
3511
3512        let values = vec![lesson, point];
3513        
3514        let object = JsonValue::MysqlJsonObject(&values);
3515
3516        let query = QueryBuilder::update().unwrap()
3517                                         .table("users")
3518                                         .json_array_append("points", Some(""), object.clone())
3519                                         .where_("id", "=", ValueType::Int8(1))
3520                                         .finish();
3521
3522        assert_eq!("UPDATE users SET points = JSON_ARRAY_APPEND(points, '$', JSON_OBJECT('lesson', 'math', 'point', 100)) WHERE id = 1;", query);
3523
3524        let query = QueryBuilder::update().unwrap()
3525                                         .table("users")
3526                                         .set("status", ValueType::String("passed".to_string()))
3527                                         .json_array_append("points", Some(""), object)
3528                                         .where_("id", "=", ValueType::Int8(1))
3529                                         .finish();
3530
3531        assert_eq!("UPDATE users SET status = 'passed', points = JSON_ARRAY_APPEND(points, '$', JSON_OBJECT('lesson', 'math', 'point', 100)) WHERE id = 1;", query);
3532    }
3533
3534    #[test]
3535    pub fn test_json_remove() {
3536        let query = QueryBuilder::update().unwrap()
3537                                         .table("blogs")
3538                                         .json_remove("likes", vec!["[10]"])
3539                                         .where_("blog_id", "=", ValueType::Int32(20))
3540                                         .finish();
3541
3542        assert_eq!(query, "UPDATE blogs SET likes = JSON_REMOVE(likes, '$[10]') WHERE blog_id = 20;");
3543        
3544        let query = QueryBuilder::update().unwrap()
3545                                         .table("blogs")
3546                                         .set("blabla", ValueType::Int32(50))
3547                                         .json_remove("likes", vec!["[10]", "[11]", "[12]"])
3548                                         .where_("blog_id", "=", ValueType::Int32(20))
3549                                         .finish();
3550
3551        println!("{}", query)
3552    }
3553
3554    #[test]
3555    pub fn test_json_set_and_json_replace(){
3556        let lesson = ("lesson", &ValueType::String("math".to_string()));
3557        let point = ("point", &ValueType::Int32(100));
3558
3559        let values = vec![lesson, point];
3560        
3561        let object = JsonValue::MysqlJsonObject(&values);
3562
3563        let query = QueryBuilder::update().unwrap()
3564                                                        .table("users")
3565                                                        .json_set("points", "[0]", object)
3566                                                        .where_("id", "=", ValueType::Int32(1))
3567                                                        .finish();
3568
3569        assert_eq!("UPDATE users SET points = JSON_SET(points, '$[0]', JSON_OBJECT('lesson', 'math', 'point', 100)) WHERE id = 1;", query);
3570
3571        let value = ValueType::Int32(100);
3572        let value = JsonValue::Initial(&value);
3573
3574        let query = QueryBuilder::update().unwrap()
3575                                         .table("users")
3576                                         .json_replace("points", "[0].point", value)
3577                                         .where_("id", "=", ValueType::Int32(1))
3578                                         .finish();
3579
3580        assert_eq!("UPDATE users SET points = JSON_REPLACE(points, '$[0].point', 100) WHERE id = 1;", query)
3581    }
3582
3583    #[test]
3584    pub fn test_json_value_initial_bugfix(){
3585        let file_name_val = ValueType::JsonString("chemistry".to_string());
3586        let file_name_val = JsonValue::Initial(&file_name_val);
3587
3588        let query = QueryBuilder::select(vec!["lesson_points"]).unwrap()
3589                                         .json_extract("points", &format!("[{}]", 2), Some("point"))
3590                                         .table("students")
3591                                         .where_("id", "=", ValueType::Int32(5))
3592                                         .and("adsf", "=", ValueType::Null)
3593                                         .json_contains("points", file_name_val, Some(&format!("[{}].name", 0)))
3594                                         .finish();
3595
3596        assert_eq!("SELECT JSON_EXTRACT(points, '$[2]') AS point FROM students WHERE id = 5 AND JSON_CONTAINS(points, '\"chemistry\"', '$[0].name');", query);
3597    }
3598}
qubl/lib.rs

qubl/
lib.rs
