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) => self.query = format!("SELECT JSON_CONTAINS({}, {}, '${}') FROM", column, needle, path),
1213                None => self.query = format!("SELECT JSON_CONTAINS({}, {}) FROM", column, needle)
1214            },
1215            KeywordList::Where => match path {
1216                Some(path) => {
1217                    let mut split_the_query = self.query.split(" WHERE ");
1218
1219                    let first_half = split_the_query.nth(0);
1220
1221                    self.query = format!("{} WHERE JSON_CONTAINS({}, {}, '${}')", first_half.unwrap(), column, needle, path);
1222                },
1223                None => {
1224                    let mut split_the_query = self.query.split(" WHERE ");
1225
1226                    let first_half = split_the_query.nth(0);
1227
1228                    self.query = format!("{} WHERE JSON_CONTAINS({}, {})", first_half.unwrap(), column, needle);
1229                }
1230            },
1231            KeywordList::And => match path {
1232                Some(path) => {
1233                    let split_the_query = self.query.split(" AND ").collect::<Vec<&str>>();
1234
1235                    let length_of_the_split_the_query = split_the_query.len();
1236
1237                    match split_the_query.len() {
1238                        0 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1239                        1 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1240                        2 => self.query = format!("{} AND JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path),
1241                        _ => {
1242                            let mut concatenated_string = String::new();
1243
1244                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1245                                if index == 0 {
1246                                    concatenated_string = chunk.to_string();
1247                                } else if index + 1 != length_of_the_split_the_query {
1248                                    concatenated_string = format!("{} AND {} ", concatenated_string, chunk)
1249                                }
1250                            }
1251
1252                            self.query = format!("{}AND JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
1253                        }
1254                    }
1255                },
1256                None => {
1257                    let split_the_query = self.query.split(" AND ").collect::<Vec<&str>>();
1258
1259                    let length_of_the_split_the_query = split_the_query.len();
1260
1261                    match split_the_query.len() {
1262                        0 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1263                        1 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1264                        2 => self.query = format!("{} AND JSON_CONTAINS({}, {})",  split_the_query[0], column, needle),
1265                        _ => {
1266                            let mut concatenated_string = String::new();
1267
1268                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1269                                if index == 0 {
1270                                    concatenated_string = chunk.to_string();
1271                                } else if index + 1 != length_of_the_split_the_query {
1272                                    concatenated_string = format!("{} AND {} ", concatenated_string, chunk)
1273                                }
1274                            }
1275
1276                            self.query = format!("{}AND JSON_CONTAINS({}, {})", concatenated_string, column, needle);
1277                        }
1278                    }
1279                }
1280            },
1281            KeywordList::Or => match path {
1282                Some(path) => {
1283                    let split_the_query = self.query.split(" OR ").collect::<Vec<&str>>();
1284
1285                    let length_of_the_split_the_query = split_the_query.len();
1286
1287                    match split_the_query.len() {
1288                        0 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
1289                        1 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
1290                        2 => self.query = format!("{} OR JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path),
1291                        _ => {
1292                            let mut concatenated_string = String::new();
1293
1294                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1295                                if index == 0 {
1296                                    concatenated_string = chunk.to_string();
1297                                } else if index + 1 != length_of_the_split_the_query {
1298                                    concatenated_string = format!("{} OR {} ", concatenated_string, chunk)
1299                                }
1300                            }
1301
1302                            self.query = format!("{}OR JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path);
1303                        }
1304                    }
1305                },
1306                None => {
1307                    let split_the_query = self.query.split(" OR ").collect::<Vec<&str>>();
1308
1309                    let length_of_the_split_the_query = split_the_query.len();
1310
1311                    match split_the_query.len() {
1312                        0 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
1313                        1 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
1314                        2 => self.query = format!("{} OR JSON_CONTAINS({}, {})",  split_the_query[0], column, needle),
1315                        _ => {
1316                            let mut concatenated_string = String::new();
1317
1318                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1319                                if index == 0 {
1320                                    concatenated_string = chunk.to_string();
1321                                } else if index + 1 != length_of_the_split_the_query {
1322                                    concatenated_string = format!("{} OR {} ", concatenated_string, chunk)
1323                                }
1324                            }
1325
1326                            self.query = format!("{}OR JSON_CONTAINS({}, {})", concatenated_string, column, needle);
1327                        }
1328                    }
1329                }
1330            },
1331            _ => panic!("Wrong usage of '.json_contains()' method, it should be used later than either SELECT, WHERE, AND, OR keywords.")
1332        }
1333
1334        self.list.push(KeywordList::JsonContains);
1335
1336        self
1337    }
1338
1339    /// 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.
1340    /// 
1341    /// ```rust
1342    /// 
1343    /// use qubl::{QueryBuilder, ValueType, JsonValue};
1344    /// 
1345    /// fn main(){
1346    ///     let value = ValueType::String("blablabla.jpg".to_string());
1347    ///     let prop = vec![("name", &value)];
1348    /// 
1349    ///     let object = JsonValue::MysqlJsonObject(&prop);
1350    /// 
1351    ///     let query = QueryBuilder::select(["*"].to_vec()).unwrap()
1352    ///                              .table("users")
1353    ///                              .where_("pic", "=", ValueType::String("".to_string()))
1354    ///                              .not_json_contains("pic", object, Some(".name"))
1355    ///                              .finish();
1356    /// 
1357    ///     assert_eq!(query, "SELECT * FROM users WHERE NOT JSON_CONTAINS(pic, JSON_OBJECT('name', 'blablabla.jpg'), '$.name');")
1358    /// }
1359    /// 
1360    /// ```
1361    pub fn not_json_contains(&mut self, column: &str, needle: JsonValue, path: Option<&str>) -> &mut Self {
1362        match self.list.last().unwrap() {
1363            KeywordList::Select => match path {
1364                Some(path) => self.query = format!("SELECT NOT JSON_CONTAINS({}, {}, '${}') FROM", column, needle, path),
1365                None => self.query = format!("SELECT NOT JSON_CONTAINS({}, {}) FROM", column, needle)
1366            },
1367            KeywordList::Where => match path {
1368                Some(path) => {
1369                    let mut split_the_query = self.query.split(" WHERE ");
1370
1371                    let first_half = split_the_query.nth(0);
1372
1373                    self.query = format!("{} WHERE NOT JSON_CONTAINS({}, {}, '${}')", first_half.unwrap(), column, needle, path);
1374                },
1375                None => {
1376                    let mut split_the_query = self.query.split(" WHERE ");
1377
1378                    let first_half = split_the_query.nth(0);
1379
1380                    self.query = format!("{} WHERE NOT JSON_CONTAINS({}, {})", first_half.unwrap(), column, needle);
1381                }
1382            },
1383            KeywordList::And => match path {
1384                Some(path) => {
1385                    let split_the_query = self.query.split(" AND ").collect::<Vec<&str>>();
1386
1387                    let length_of_the_split_the_query = split_the_query.len();
1388
1389                    match split_the_query.len() {
1390                        0 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1391                        1 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1392                        2 => self.query = format!("{} AND NOT JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path),
1393                        _ => {
1394                            let mut concatenated_string = String::new();
1395
1396                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1397                                if index == 0 {
1398                                    concatenated_string = chunk.to_string();
1399                                } else if index + 1 != length_of_the_split_the_query {
1400                                    concatenated_string = format!("{} AND {} ", concatenated_string, chunk)
1401                                }
1402                            }
1403
1404                            self.query = format!("{}AND NOT JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path)
1405                        }
1406                    }
1407                },
1408                None => {
1409                    let split_the_query = self.query.split(" AND ").collect::<Vec<&str>>();
1410
1411                    let length_of_the_split_the_query = split_the_query.len();
1412
1413                    match split_the_query.len() {
1414                        0 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1415                        1 => panic!("There Is No AND query in QueryBuilder but The AND keyword exist in keyword list, panicking."),
1416                        2 => self.query = format!("{} AND NOT JSON_CONTAINS({}, {})",  split_the_query[0], column, needle),
1417                        _ => {
1418                            let mut concatenated_string = String::new();
1419
1420                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1421                                if index == 0 {
1422                                    concatenated_string = chunk.to_string();
1423                                } else if index + 1 != length_of_the_split_the_query {
1424                                    concatenated_string = format!("{} AND {} ", concatenated_string, chunk)
1425                                }
1426                            }
1427
1428                            self.query = format!("{}AND NOT JSON_CONTAINS({}, {})", concatenated_string, column, needle);
1429                        }
1430                    }
1431                }
1432            },
1433            KeywordList::Or => match path {
1434                Some(path) => {
1435                    let split_the_query = self.query.split(" OR ").collect::<Vec<&str>>();
1436
1437                    let length_of_the_split_the_query = split_the_query.len();
1438
1439                    match split_the_query.len() {
1440                        0 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
1441                        1 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
1442                        2 => self.query = format!("{} OR NOT JSON_CONTAINS({}, {}, '${}')", split_the_query[0], column, needle, path),
1443                        _ => {
1444                            let mut concatenated_string = String::new();
1445
1446                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1447                                if index == 0 {
1448                                    concatenated_string = chunk.to_string();
1449                                } else if index + 1 != length_of_the_split_the_query {
1450                                    concatenated_string = format!("{} OR {} ", concatenated_string, chunk)
1451                                }
1452                            }
1453
1454                            self.query = format!("{}OR NOT JSON_CONTAINS({}, {}, '${}')", concatenated_string, column, needle, path);
1455                        }
1456                    }
1457                },
1458                None => {
1459                    let split_the_query = self.query.split(" OR ").collect::<Vec<&str>>();
1460
1461                    let length_of_the_split_the_query = split_the_query.len();
1462
1463                    match split_the_query.len() {
1464                        0 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
1465                        1 => panic!("There Is No OR query in QueryBuilder but The OR keyword exist in keyword list, panicking."),
1466                        2 => self.query = format!("{} OR NOT JSON_CONTAINS({}, {})",  split_the_query[0], column, needle),
1467                        _ => {
1468                            let mut concatenated_string = String::new();
1469
1470                            for (index, chunk) in  split_the_query.into_iter().enumerate() {
1471                                if index == 0 {
1472                                    concatenated_string = chunk.to_string();
1473                                } else if index + 1 != length_of_the_split_the_query {
1474                                    concatenated_string = format!("{} OR {} ", concatenated_string, chunk)
1475                                }
1476                            }
1477
1478                            self.query = format!("{}OR NOT JSON_CONTAINS({}, {})", concatenated_string, column, needle);
1479                        }
1480                    }
1481                }
1482            },
1483            _ => panic!("Wrong usage of '.json_contains()' method, it should be used later than either SELECT, WHERE, AND, OR keywords.")
1484        }
1485
1486        self.list.push(KeywordList::JsonContains);
1487
1488        self
1489    }
1490
1491    /// 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.
1492    /// 
1493    /// ```rust
1494    /// 
1495    /// use qubl::{QueryBuilder, ValueType, JsonValue};
1496    /// 
1497    /// fn main () {
1498    ///     let lesson = ("lesson", &ValueType::String("math".to_string()));
1499    ///     let point = ("point", &ValueType::Int32(100));
1500    ///
1501    ///     let values = vec![lesson, point];
1502    ///
1503    ///     let object = JsonValue::MysqlJsonObject(&values);
1504    ///
1505    ///     let query = QueryBuilder::update().unwrap()
1506    ///                                 .table("users")
1507    ///                                 .json_array_append("points", Some(""), object.clone())
1508    ///                                 .where_("id", "=", ValueType::Int8(1))
1509    ///                                 .finish();
1510    ///
1511    ///     assert_eq!("UPDATE users SET points = JSON_ARRAY_APPEND(points, '$', JSON_OBJECT('lesson', 'math', 'point', 100)) WHERE id = 1;", query);
1512    /// }
1513    /// 
1514    /// ```
1515    pub fn json_array_append(&mut self, column: &str, path: Option<&str>, object: JsonValue) -> &mut Self {
1516        match self.list.last() {
1517            Some(keyword) => match keyword {
1518                KeywordList::Set => {
1519                    match path {
1520                        Some(path) => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '${}', {})", self.query, column, column, path, object),
1521                        None => self.query = format!("{}, {} = JSON_ARRAY_APPEND({}, '$', {})", self.query, column, column, object)
1522                    }
1523                },
1524                _ => {
1525                    match path {
1526                        Some(path) => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '${}', {})", self.query, column, column, path, object),
1527                        None => self.query = format!("{} SET {} = JSON_ARRAY_APPEND({}, '$', {})", self.query, column, column, object)
1528                    }
1529                }
1530            },
1531            None => panic!("it's impossible to came here!")
1532        }
1533
1534        self.list.push(KeywordList::JsonArrayAppend);
1535        self
1536    }
1537
1538    /// it adds "JSON_REMOVE()" function with it's synthax. You cannot pass empty strings to paths.
1539    /// 
1540    /// ```rust
1541    /// 
1542    /// use qubl::{QueryBuilder, ValueType};
1543    /// 
1544    /// fn main () {
1545    ///   let query = QueryBuilder::update().unwrap()
1546    ///                            .table("blogs")
1547    ///                            .json_remove("likes", vec!["[10]"])
1548    ///                            .where_("blog_id", "=", ValueType::Int32(20))
1549    ///                            .finish();
1550    ///
1551    ///   assert_eq!(query, "UPDATE blogs SET likes = JSON_REMOVE(likes, '$[10]') WHERE blog_id = 20;");
1552    /// }
1553    /// 
1554    /// ```
1555    pub fn json_remove(&mut self, column: &str, paths: Vec<&str>) -> &mut Self {
1556        match paths.iter().any(|path| *path == "") {
1557            true => panic!("Error: a value in the paths cannot be empty string, panicking..."),
1558            false => ()
1559        }
1560
1561        match self.list.last() {
1562            Some(keyword) => match keyword {
1563                KeywordList::Set => {
1564                    self.query = format!("{}, {} = JSON_REMOVE({}", self.query, column, column);
1565
1566                    for path in paths {
1567                        if path.starts_with("$") {
1568                            self.query = format!("{}, '${}'", self.query, path.replace("$", ""))
1569                        } else {
1570                            self.query = format!("{}, '${}'", self.query, path)
1571                        }
1572                    }
1573
1574                    self.query = format!("{})", self.query)
1575                },
1576                _ => {
1577                    self.query = format!("{} SET {} = JSON_REMOVE({}", self.query, column, column);
1578
1579                    for path in paths {
1580                        if path.starts_with("$") {
1581                            self.query = format!("{}, '${}'", self.query, path.replace("$", ""))
1582                        } else {
1583                            self.query = format!("{}, '${}'", self.query, path)
1584                        }
1585                    }
1586
1587                    self.query = format!("{})", self.query)
1588                }
1589            },
1590            None => panic!("it's impossible to came here!")
1591        }
1592
1593        self.list.push(KeywordList::JsonRemove);
1594        self
1595    }
1596
1597    /// finishes the query and returns the result as string.
1598    pub fn finish(&self) -> String {
1599        return format!("{};", self.query);
1600    }
1601
1602    /// gives you an immutable copy of that instance, just for case if you need to share and potentially mutate it across threads.
1603    pub fn copy(&mut self) -> Self {
1604        Self {
1605            query: self.query.clone(),
1606            table: self.table.clone(),
1607            qtype: self.qtype.clone(),
1608            list: self.list.clone(),
1609            hq: self.hq
1610        }
1611    }
1612
1613    fn load_hqs() -> [&'a str; 26] {
1614        [";", "; drop", "admin' #", "admin'/*", "; union", "or 1 = 1",
1615        "or 1 = 1#", "or 1 = 1/*", "or true = true", "or false = false", "or '1' = '1'", "or '1' = '1'#",
1616        "or '1' = '1'/*", "; sleep(", "--", "drop table", "drop schema", "select if", "union select",
1617        "union all", "exec", "master..", "masters..", "information_schema", "load_file", "alter user"]
1618    }
1619
1620    fn sanitize_column(&mut self, column: &str)  -> std::result::Result<(), std::io::Error>  {
1621        match self.hq {
1622            Some(hqs) => {
1623                for _hq in hqs.iter() {
1624                    if &column == _hq {
1625                        return Err(std::io::Error::new(std::io::ErrorKind::PermissionDenied, "You cannot run arbitrary queries"))
1626                    }
1627                }
1628            },
1629            None => return Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "Your input is invalid."))
1630        }
1631
1632        Ok(())
1633    }
1634
1635    /// checks the inputs for potential sql injection patterns and throws error if they exist.
1636    fn sanitize_columns(columns: &Vec<&str>, hqs: [&'a str; 26]) -> std::result::Result<(), std::io::Error> {
1637        if columns.len() == 1 && columns[0] == "" {
1638            return Ok(());
1639        };
1640
1641        for column in columns.iter() {
1642            for hq in hqs.iter() {
1643                if column == hq {
1644                    return Err(std::io::Error::new(std::io::ErrorKind::PermissionDenied, "You cannot run arbitrary queries"))
1645                }
1646            }
1647        }
1648
1649        return Ok(())
1650    }
1651
1652    fn sanitize_inputs(inputs: &Vec<ValueType>, hqs: [&'a str; 26]) -> std::result::Result<(), std::io::Error> {
1653        for input in inputs.iter() {
1654            match input {
1655                ValueType::String(string) | ValueType::Datetime(string) => {
1656                    for hq in hqs.iter() {
1657                        if &string == hq {
1658                            return Err(std::io::Error::new(std::io::ErrorKind::PermissionDenied, "You cannot run arbitrary queries"))
1659                        }
1660                    }
1661                },
1662                _ => continue
1663            }
1664        }
1665
1666        return Ok(())
1667    }
1668
1669    fn sanitize_input(&mut self, input: &ValueType) -> std::result::Result<(), std::io::Error> {
1670        match input {
1671            ValueType::String(string) | ValueType::Datetime(string) => {
1672                match self.hq {
1673                    Some(hqs) => {
1674                        for hq in hqs.iter() {
1675                            if &string == hq {
1676                                return Err(std::io::Error::new(std::io::ErrorKind::PermissionDenied, "You cannot run arbitrary queries"))
1677                            }
1678                        }
1679                    },
1680                    None => return Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "Your input is invalid."))
1681                }
1682            },
1683            _ => return Ok(())
1684        };
1685
1686        Ok(())
1687    }
1688
1689    fn sanitize_mark(input: &str) -> std::result::Result<(), std::io::Error> {
1690        return match input {
1691            "=" | "<" | ">" | "<=" | ">=" | "!=" | "<>" => Ok(()),
1692            _ => Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "comparison operators cannot be other than =, <, >, <=,  >=, != or <>."))
1693        }
1694    }
1695
1696    fn sanitize_str(&mut self, input: &str) -> std::result::Result<(), std::io::Error> {
1697        match self.hq {
1698            Some(hqs) => {
1699                for hq in hqs.iter() {
1700                    if *hq == input {
1701                        return Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "comparison operators cannot be other than =, <, >, <=,  >=, != or <>."))
1702                    }
1703                }
1704            },
1705            None => return Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "comparison operators cannot be other than =, <, >, <=,  >=, != or <>."))
1706        }
1707
1708        Ok(())
1709    }
1710}
1711
1712/// Struct that benefits you to create and use schema's.
1713#[derive(Debug, Clone)]
1714pub struct SchemaBuilder {
1715    pub query: String,
1716    pub schema: String,
1717    pub list: Vec<KeywordList>
1718}
1719
1720/// implementations fon SchemaBuilder
1721impl SchemaBuilder {
1722    pub fn create(name: &str) -> std::result::Result<Self, std::io::Error> {
1723        if name.contains("!") ||
1724           name.contains("-") ||
1725           name.contains("=") ||
1726           name.contains("+") ||
1727           name.contains("%") ||
1728           name.contains("$") ||
1729           name.contains("&") ||
1730           name.contains("#") ||
1731           name.contains("[") ||
1732           name.contains("]") ||
1733           name.contains("{") ||
1734           name.contains("}") ||
1735           name.contains(":") ||
1736           name.contains(";") ||
1737           name.contains("'") ||
1738           name.contains("\"") ||
1739           name.contains(",") ||
1740           name.contains(".") {
1741                return Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "Schame name cannot include these characters: '!', '-', '=', '+', '%', '$', '&', '#', '[', ']', '{', '}', ':', ';', '\"', \"'\", '.', ','"))
1742        }
1743
1744        Ok(Self {
1745            query: format!("CREATE DATABASE {}", name),
1746            schema: name.to_string(),
1747            list: vec![KeywordList::Create]
1748        })
1749    }
1750
1751    pub fn use_another_schema(name: &str) -> std::result::Result<Self, std::io::Error> {
1752        if name.contains("!") ||
1753        name.contains("-") ||
1754        name.contains("=") ||
1755        name.contains("+") ||
1756        name.contains("%") ||
1757        name.contains("$") ||
1758        name.contains("&") ||
1759        name.contains("#") ||
1760        name.contains("[") ||
1761        name.contains("]") ||
1762        name.contains("{") ||
1763        name.contains("}") ||
1764        name.contains(":") ||
1765        name.contains(";") ||
1766        name.contains("'") ||
1767        name.contains("\"") ||
1768        name.contains(",") ||
1769        name.contains(".") {
1770             return Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, "Schame name cannot include these characters: '!', '-', '=', '+', '%', '$', '&', '#', '[', ']', '{', '}', ':', ';', '\"', \"'\", '.', ','"))
1771        }
1772
1773        Ok(Self {
1774            query: format!("USE {}", name),
1775            schema: name.to_string(),
1776            list: vec![KeywordList::Use, KeywordList::Create]
1777        })
1778    }
1779
1780    pub fn if_not_exists(&mut self) -> &mut Self {
1781        match self.list[0] {
1782            KeywordList::Create => (),
1783            KeywordList::Table => (),
1784            _ => panic!("if_not_exists method cannot be used without Create or Table queries")
1785        }
1786
1787        let split_the_query =  self.query.split(" DATABASE ").collect::<Vec<&str>>();
1788        self.query = format!("{} DATABASE IF NOT EXISTS {}", split_the_query[0], split_the_query[1]);
1789
1790        self.list.insert(0, KeywordList::IfNotExist);
1791        self
1792    }
1793
1794    pub fn use_schema(&mut self, name: Option<&str>) -> &mut Self {
1795        match name {
1796            Some(schema_name) => {
1797                self.query = format!("USE {}", schema_name)
1798            },
1799            None => {
1800                self.query = format!("USE {}", self.schema);
1801            }
1802        }
1803
1804        self
1805    }
1806
1807    pub fn finish(&self) -> String {
1808        return format!("{};", self.query)
1809    }
1810}
1811
1812/// Struct that benefits you to create Tables. Currently incomplete thoug.
1813#[derive(Debug, Clone)]
1814pub struct TableBuilder {
1815    pub query: String,
1816    pub name: String,
1817    pub schema: String,
1818    pub all: Vec<String>,
1819}
1820
1821/// Struct that benefits to define a foreign key.
1822#[derive(Debug, Clone)]
1823pub struct ForeignKey {
1824    pub first: ForeignKeyItem,
1825    pub second: ForeignKeyItem,
1826    pub on_delete: Option<ForeignKeyActions>,
1827    pub on_update: Option<ForeignKeyActions>,
1828    pub constraint: Option<String>
1829}
1830
1831/// Struct that benefits you to add a foreign key item to a foreign key.
1832#[derive(Debug, Clone)]
1833pub struct ForeignKeyItem {
1834    pub table: String,
1835    pub column: String
1836}
1837
1838/// implementations for TableBuilder
1839impl TableBuilder {
1840    pub fn create(schema_name: &str, table_name: &str) -> Self {
1841        return Self {
1842            query: format!("CREATE TABLE {} (", table_name),
1843            schema: schema_name.to_string(),
1844            name: table_name.to_string(),
1845            all: vec![]
1846        }
1847    }
1848
1849    pub fn if_not_exists(&mut self) -> &mut Self {
1850        self.query = format!("{}IF NOT EXISTS (", self.query.replace("(", ""));
1851
1852        self
1853    }
1854
1855    pub fn add_column(&mut self, column_name: &str) -> &mut Self {
1856        if self.query.ends_with("(") {
1857            self.query = format!("{}{}", self.query, column_name)
1858        } else {
1859            self.query = format!("{}, {}", self.query, column_name)
1860        }
1861
1862        self
1863    }
1864
1865    pub fn col_type(&mut self, type_name: &str) -> &mut Self {
1866        if self.query.ends_with("(") {
1867            panic!("Cannot add type before defining a column name.")
1868        }
1869
1870        self.query = format!("{} {}", self.query, type_name);
1871
1872        self
1873    }
1874
1875    pub fn null(&mut self) -> &mut Self {
1876        self.query = format!("{} NULL", self.query);
1877
1878        self
1879    }
1880
1881    pub fn not_null(&mut self) -> &mut Self {
1882        self.query = format!("{} NOT NULL", self.query);
1883
1884        self
1885    }
1886
1887    pub fn auto_increment(&mut self) -> &mut Self {
1888        self.query = format!("{} AUTO_INCREMENT", self.query);
1889
1890        self
1891    }
1892
1893    pub fn primary_key(&mut self) -> &mut Self {
1894        if self.query.contains("PRIMARY KEY") {
1895            panic!("A table cannot have two primary keys.")
1896        }
1897
1898        self.query = format!("{} PRIMARY KEY", self.query);
1899
1900        self
1901    }
1902
1903    pub fn default(&mut self, value: ValueType) -> &mut Self {
1904        let split_the_query = self.query.clone();
1905        let split_the_query = split_the_query.split(", ").collect::<Vec<&str>>();
1906
1907        let last_query = split_the_query[split_the_query.len() - 1];
1908
1909        if last_query.contains("INT") || 
1910           last_query.contains("TINYINT") ||
1911           last_query.contains("SMALLINT") ||
1912           last_query.contains("MEDIUMINT") ||
1913           last_query.contains("BIGINT") ||
1914           last_query.contains("BIT") ||
1915           last_query.contains("SERIAL") {
1916            match value {
1917                ValueType::Int8(int) => self.query = format!("{} DEFAULT {}", self.query, int),
1918                ValueType::Int16(int) => self.query = format!("{} DEFAULT {}", self.query, int),
1919                ValueType::Int32(int) => self.query = format!("{} DEFAULT {}", self.query, int),
1920                ValueType::Int64(int) => self.query = format!("{} DEFAULT {}", self.query, int),
1921                ValueType::Uint8(int) => self.query = format!("{} DEFAULT {}", self.query, int),
1922                ValueType::Uint16(int) => self.query = format!("{} DEFAULT {}", self.query, int),
1923                ValueType::Uint32(int) => self.query = format!("{} DEFAULT {}", self.query, int),
1924                ValueType::Uint64(int) => self.query = format!("{} DEFAULT {}", self.query, int),
1925                ValueType::Usize(int) => self.query = format!("{} DEFAULT {}", self.query, int),
1926                ValueType::Float32(int) => self.query = format!("{} DEFAULT {}", self.query, int),
1927                ValueType::Float64(int) => self.query = format!("{} DEFAULT {}", self.query, int),
1928                _ => 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.")
1929            }
1930        }
1931
1932        if last_query.contains("BOOL") || 
1933           last_query.contains("BOOLEAN") {
1934            match value {
1935                ValueType::Boolean(boolean) => self.query = format!("{} DEFAULT {}", self.query, boolean),
1936                _ => panic!("If your column type is BOOLEAN, you have to write either true or false.")
1937            }    
1938        }
1939
1940        if last_query.contains("CHAR") ||
1941           last_query.contains("VARCHAR") ||
1942           last_query.contains("TEXT") ||
1943           last_query.contains("TINYTEXT") ||
1944           last_query.contains("MEDIUMTEXT") ||
1945           last_query.contains("LONGTEXT") ||
1946           last_query.contains("BINARY") ||
1947           last_query.contains("VARBINARY") {
1948            match value {
1949                ValueType::String(ref text) => self.query = format!("{} DEFAULT '{}'", self.query, text),
1950                _ => 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.")
1951            }
1952        }
1953
1954        if last_query.contains("DATETIME") ||
1955           last_query.contains("TIMESTAMP") {
1956            match value {
1957                ValueType::Datetime(datetime) => self.query = format!("{} DEFAULT {}", self.query, datetime),
1958                _ => 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.")
1959            }
1960        }
1961
1962        self
1963    }
1964
1965    pub fn unique(&mut self) -> &mut Self {
1966        self.query = format!("{} UNIQUE", self.query);
1967
1968        self
1969    }
1970
1971    pub fn check(&mut self, condition: &str) -> &mut Self {
1972        self.query = format!("{} CHECK({})", self.query, condition);
1973
1974        self
1975    }
1976
1977    pub fn character_set(&mut self, character_set: &str) -> &mut Self {
1978        self.query = format!("{} CHARACTER SET {}", self.query, character_set);
1979
1980        self
1981    }
1982
1983    pub fn foreign_key(&mut self, opts: ForeignKey) -> &mut Self {
1984        if self.query.starts_with("ALTER TABLE") {
1985            match opts.constraint {
1986                Some(constraint) => self.query = format!("{}, ADD CONSTRAINT {} FOREIGN KEY ({})", self.query, constraint, opts.first.column),
1987                None => self.query = format!("{}, ADD FOREIGN KEY ({})", self.query, opts.first.column)
1988            }
1989            
1990        } else {
1991            match opts.constraint {
1992                Some(constraint) => self.query = format!("{}, CONSTRAINT {} FOREIGN KEY ({})", self.query, constraint, opts.first.column),
1993                None => self.query = format!("{}, FOREIGN KEY ({})", self.query, opts.first.column)
1994            }
1995        }
1996
1997        self.query = format!("{} REFERENCES {}({})", self.query, opts.second.table, opts.second.column);
1998
1999        match opts.on_delete {
2000            Some(on_delete_opt) => self.query = format!("{} ON DELETE {}", self.query, on_delete_opt),
2001            None => ()
2002        }
2003
2004        match opts.on_update {
2005            Some(on_update_opt) => self.query = format!("{} ON UPDATE {}", self.query, on_update_opt),
2006            None => ()
2007        }
2008
2009        self
2010    }
2011
2012    pub fn unsigned(&mut self) -> &mut Self {
2013        self.query = format!("{} UNSIGNED", self.query);
2014
2015        self
2016    }
2017
2018    pub fn zerofill(&mut self) -> &mut Self {
2019        self.query = format!("{} ZEROFILL", self.query);
2020
2021        self
2022    }
2023
2024    pub fn enum_sql(&mut self, enum_vec: Vec<&str>) -> &mut Self {
2025        match enum_vec.len() {
2026            0 => panic!("enum_vec argument cannot be an empty vector"),
2027            _ => ()
2028        }
2029        
2030        self.query = format!("{} ENUM(", self.query);
2031
2032        let length_of_enum_vec = enum_vec.len();
2033        for (index, item) in enum_vec.into_iter().enumerate() {
2034            if index + 1 == length_of_enum_vec {
2035                self.query = format!("{}'{}'", self.query, item)
2036            } else {
2037                self.query = format!("{}'{}', ", self.query, item)
2038            }
2039        }
2040
2041        self
2042    }
2043
2044    pub fn generated_always(&mut self, condition: &str) -> &mut Self {
2045        self.query = format!("{} GENERATED ALWAYS AS {}", self.query, condition);
2046
2047        self
2048    }
2049
2050    pub fn virtual_sql(&mut self) -> &mut Self {
2051        self.query = format!("{} VIRTUAL", self.query);
2052
2053        self
2054    }
2055
2056    pub fn stored(&mut self) -> &mut Self {
2057        self.query = format!("{} STORED", self.query);
2058
2059        self
2060    }
2061
2062    pub fn spatial(&mut self) -> &mut Self {
2063        self.query = format!("{} SPATIAL", self.query);
2064
2065        self
2066    }
2067
2068    pub fn generated(&mut self) -> &mut Self {
2069        self.query = format!("{} GENERATED", self.query);
2070
2071        self
2072    }
2073
2074    pub fn index(&mut self, indexes: Vec<&str>) -> &mut Self {
2075        let length_of_indexes = indexes.len();
2076
2077        match length_of_indexes {
2078            0 => panic!("There is no index here."),
2079            1 => self.query = format!("{}, INDEX({})", self.query, indexes[0]),
2080            _ => {
2081                for (i, index) in indexes.into_iter().enumerate() {
2082                    if i + 1 == length_of_indexes {
2083                        self.query = format!("{}{}", self.query, index);
2084
2085                        continue;
2086                    }
2087
2088                    if i == 0 {
2089                        self.query = format!("{}, INDEX ({}, ", self.query, index);
2090
2091                        continue;
2092                    }
2093
2094                    self.query = format!("{}{}, ", self.query, index)
2095                }
2096            }
2097        }
2098
2099        self
2100    }
2101
2102    pub fn comment(&mut self, comment: &str) -> &mut Self {
2103        self.query = format!("{} COMMENT '{}'", self.query, comment);
2104
2105        self
2106    }
2107
2108    pub fn default_on_null(&mut self, value: ValueType) -> &mut Self {
2109        match value {
2110            ValueType::String(text) => self.query = format!("{} DEFAULT {} ON NULL", self.query, text),
2111            _ => self.query = format!("{} DEFAULT {} ON NULL", self.query, value),
2112        }
2113
2114        self
2115    }
2116
2117    pub fn invisible(&mut self) -> &mut Self {
2118        self.query = format!("{} INVISIBLE", self.query);
2119
2120        self
2121    }
2122
2123    pub fn custom_query(&mut self, query: &str) -> &mut Self {
2124        self.query = format!("{} {}", self.query, query);
2125
2126        self
2127    }
2128
2129    pub fn finish(&mut self) -> String {
2130        return format!("{});", self.query)
2131    }
2132}
2133
2134/// KeywordList enum. It helps to syntactically correcting the queries. 
2135#[derive(Debug, Clone, PartialEq)]
2136pub enum KeywordList {
2137    Select, Update, Delete, Insert, Count, Table, Where, Or, And, Set, 
2138    Finish, OrderBy, GroupBy, Having, Like, Limit, Offset, IfNotExist, Create, Use, In, 
2139    NotIn, JsonExtract, JsonContains, JsonArrayAppend, JsonRemove, Field, Union, UnionAll
2140}
2141
2142/// QueryType enum. It helps to detect the type of a query with more optimized way when is needed.
2143#[derive(Debug, Clone)]
2144pub enum QueryType {
2145    Select, Update, Delete, Insert, Null, Create, Count
2146}
2147
2148/// ValueType enum. It benefits to detect and format the value with optimized way when you have to work with exact column values. 
2149#[derive(Debug, Clone)]
2150pub enum ValueType {
2151    String(String), Datetime(String), Null, Boolean(bool), Int32(i32), Int16(i16), Int8(i8), Int64(i64), Int128(i128),
2152    Uint8(u8), Uint16(u16), Uint32(u32), Uint64(u64), Usize(usize), Float32(f32), Float64(f64),
2153    EpochTime(i64), JsonString(String)
2154}
2155
2156impl std::fmt::Display for ValueType {
2157    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
2158        match self {
2159            ValueType::String(string) => write!(f, "'{}'", string),
2160            ValueType::JsonString(string) => write!(f, "\"{}\"", string),
2161            ValueType::Datetime(datetime) => match datetime.as_str() {
2162                "CURRENT_TIMESTAMP" | "UNIX_TIMESTAMP" | "CURRENT_DATE" | "CURRENT_TIME" | "NOW()" | "CURDATE()" | "CURTIME()" => write!(f, "{}", datetime),
2163                _ => write!(f, "'{}'", datetime)
2164            },
2165            ValueType::Null => write!(f, "NULL"),
2166            ValueType::Boolean(val) => write!(f, "{}", val),
2167            ValueType::Int8(val) => write!(f, "{}", val),
2168            ValueType::Int16(val) => write!(f, "{}", val),
2169            ValueType::Int32(val) => write!(f, "{}", val),
2170            ValueType::Int64(val) => write!(f, "{}", val),
2171            ValueType::Int128(val) => write!(f, "{}", val),
2172            ValueType::Usize(val) => write!(f, "{}", val),
2173            ValueType::Uint8(val) => write!(f, "{}", val),
2174            ValueType::Uint16(val) => write!(f, "{}", val),
2175            ValueType::Uint32(val) => write!(f, "{}", val),
2176            ValueType::Uint64(val) => write!(f, "{}", val),
2177            ValueType::Float32(val) => write!(f, "{}", val),
2178            ValueType::Float64(val) => write!(f, "{}", val),
2179            ValueType::EpochTime(val) => write!(f, "FROM_UNIXTIME({})", val),
2180        }
2181    }
2182}
2183
2184impl From<String> for ValueType { fn from(value: String) -> Self { ValueType::String(value) } }
2185impl From<bool> for ValueType { fn from(value: bool) -> Self { ValueType::Boolean(value) } }
2186impl From<i8> for ValueType { fn from(value: i8) -> Self { ValueType::Int8(value) } }
2187impl From<i16> for ValueType { fn from(value: i16) -> Self { ValueType::Int16(value) } }
2188impl From<i32> for ValueType { fn from(value: i32) -> Self { ValueType::Int32(value) } }
2189impl From<i64> for ValueType { fn from(value: i64) -> Self { ValueType::Int64(value) } }
2190impl From<i128> for ValueType { fn from(value: i128) -> Self { ValueType::Int128(value) } }
2191impl From<usize> for ValueType { fn from(value: usize) -> Self { ValueType::Usize(value) } }
2192impl From<u8> for ValueType { fn from(value: u8) -> Self { ValueType::Uint8(value) } }
2193impl From<u16> for ValueType { fn from(value: u16) -> Self { ValueType::Uint16(value) } }
2194impl From<u32> for ValueType { fn from(value: u32) -> Self { ValueType::Uint32(value) } }
2195impl From<u64> for ValueType { fn from(value: u64) -> Self { ValueType::Uint64(value) } }
2196impl From<f32> for ValueType { fn from(value: f32) -> Self { ValueType::Float32(value) } }
2197impl From<f64> for ValueType { fn from(value: f64) -> Self { ValueType::Float64(value) } }
2198
2199
2200impl Into<String> for ValueType {
2201    fn into(self) -> String {
2202        match self {
2203            ValueType::String(text) => text,
2204            ValueType::Datetime(datetime) => datetime,
2205            _ => panic!("you cannot convert a ValueType to string unless it's not a String or Datetime variant.")
2206        }
2207    }
2208}
2209
2210impl Into<bool> for ValueType {
2211    fn into(self) -> bool {
2212        match self {
2213            ValueType::Boolean(val) => val,
2214            ValueType::String(text) => match text.as_str() {
2215                "false" | "" | "\0" | "0" => false,
2216                _ => true,
2217            }
2218            ValueType::Null => false,
2219            ValueType::Int8(val) => match val == 0 {
2220                false => true,
2221                true => false
2222            },
2223            ValueType::Int16(val) => match val == 0 {
2224                false => true,
2225                true => false
2226            },
2227            ValueType::Int32(val) => match val == 0 {
2228                false => true,
2229                true => false
2230            },
2231            ValueType::Int64(val) => match val == 0 {
2232                false => true,
2233                true => false
2234            },
2235            ValueType::Int128(val) => match val == 0 {
2236                false => true,
2237                true => false
2238            },
2239            ValueType::Uint8(val) => match val == 0 {
2240                false => true,
2241                true => false
2242            },
2243            ValueType::Uint16(val) => match val == 0 {
2244                false => true,
2245                true => false
2246            },
2247            ValueType::Uint32(val) => match val == 0 {
2248                false => true,
2249                true => false
2250            },
2251            ValueType::Uint64(val) => match val == 0 {
2252                false => true,
2253                true => false
2254            },
2255            ValueType::Float32(val) => match val == 0.0 {
2256                false => true,
2257                true => false
2258            },
2259            ValueType::Float64(val) => match val == 0.0 {
2260                false => true,
2261                true => false
2262            },
2263            _ => panic!("invalid conversion")
2264        }
2265    }
2266}
2267
2268impl Into<f32> for ValueType {
2269    fn into(self) -> f32 {
2270        match self {
2271            ValueType::Float32(num) => num,
2272            ValueType::Float64(num) => num as f32,
2273            _ => panic!("invalid conversion")
2274        }
2275    }
2276}
2277
2278impl Into<f64> for ValueType {
2279    fn into(self) -> f64 {
2280        match self {
2281            ValueType::Float32(num) => num as f64,
2282            ValueType::Float64(num) => num,
2283            _ => panic!("invalid conversion")
2284        }
2285    }
2286}
2287
2288impl Into<i8> for ValueType {
2289    fn into(self) -> i8 {
2290        match self {
2291            ValueType::Int8(num) => num,
2292            ValueType::Int16(num) => match num > 128 || num < -128 {
2293                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2294                false => num as i8
2295            },
2296            ValueType::Int32(num) => match num > 128 || num < -128 {
2297                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2298                false => num as i8
2299            },
2300            ValueType::Int64(num) => match num > 128 || num < -128 {
2301                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2302                false => num as i8
2303            },
2304            ValueType::Int128(num) => match num > 128 || num < -128 {
2305                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2306                false => num as i8
2307            }
2308            ValueType::Uint8(num) => match num > 128 {
2309                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2310                false => num as i8
2311            },
2312            ValueType::Uint16(num) => match num > 128 {
2313                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2314                false => num as i8
2315            },
2316            ValueType::Uint32(num) => match num > 128 {
2317                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2318                false => num as i8
2319            },
2320            ValueType::Usize(num) => match num > 128 {
2321                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2322                false => num as i8
2323            },
2324            ValueType::Uint64(num) => match num > 128 {
2325                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2326                false => num as i8
2327            },
2328            _ => panic!("you cannot convert non numeric values into numeric ones.")
2329        }
2330    }
2331}
2332
2333impl Into<i16> for ValueType {
2334    fn into(self) -> i16 {
2335        match self {
2336            ValueType::Int8(num) => num as i16,
2337            ValueType::Int16(num) => num,
2338            ValueType::Int32(num) => match num > 32_768 || num < -32_768 {
2339                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2340                false => num as i16
2341            },
2342            ValueType::Int64(num) => match num > 32_768 || num < -32_768 {
2343                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2344                false => num as i16
2345            },
2346            ValueType::Int128(num) => match num > 32_768 || num < -32_768 {
2347                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2348                false => num as i16
2349            }
2350            ValueType::Uint8(num) => num as i16,
2351            ValueType::Uint16(num) => match num > 32_768 {
2352                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2353                false => num as i16
2354            },
2355            ValueType::Uint32(num) => match num > 32_768 {
2356                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2357                false => num as i16
2358            },
2359            ValueType::Usize(num) => match num > 32_768 {
2360                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2361                false => num as i16
2362            },
2363            ValueType::Uint64(num) => match num > 32_768 {
2364                true => panic!("you cannot convert i16's into a value which is bigger than the capacity of 32 bit values."),
2365                false => num as i16
2366            },
2367            _ => panic!("you cannot convert non numeric values into numeric ones.")
2368        }
2369    }
2370}
2371
2372impl Into<i32> for ValueType {
2373    fn into(self) -> i32 {
2374        match self {
2375            ValueType::Int8(num) => num as i32,
2376            ValueType::Int16(num) => num as i32,
2377            ValueType::Int32(num) => num,
2378            ValueType::Int64(num) => match num > 2_147_483_647 || num < -2_147_483_647 {
2379                true => panic!("you cannot convert i32's into a value which is bigger than the capacity of 32 bit values."),
2380                false => num as i32
2381            },
2382            ValueType::Int128(num) => match num > 2_147_483_647 || num < -2_147_483_647 {
2383                true => panic!("you cannot convert i32's into a value which is bigger than the capacity of 32 bit values."),
2384                false => num as i32
2385            }
2386            ValueType::Uint8(num) => num as i32,
2387            ValueType::Uint16(num) => num as i32,
2388            ValueType::Uint32(num) => match num > 2_147_483_647 {
2389                true => panic!("you cannot convert i32's into a value which is bigger than the capacity of 32 bit values."),
2390                false => num as i32
2391            },
2392            ValueType::Usize(num) => match num > 2_147_483_647 {
2393                true => panic!("you cannot convert i32's into a value which is bigger than the capacity of 32 bit values."),
2394                false => num as i32
2395            },
2396            ValueType::Uint64(num) => match num > 2_147_483_647 {
2397                true => panic!("you cannot convert i32's into a value which is bigger than the capacity of 32 bit values."),
2398                false => num as i32
2399            }
2400            _ => panic!("you cannot convert non numeric values into numeric ones.")
2401        }
2402    }
2403}
2404
2405impl Into<i64> for ValueType {
2406    fn into(self) -> i64 {
2407        match self {
2408            ValueType::EpochTime(epoch) => epoch as i64,
2409            ValueType::Int8(num) => num as i64,
2410            ValueType::Int16(num) => num as i64,
2411            ValueType::Int32(num) => num as i64,
2412            ValueType::Int64(num) => num,
2413            ValueType::Usize(num) => num as i64,
2414            ValueType::Uint8(num) => num as i64,
2415            ValueType::Uint16(num) => num as i64,
2416            ValueType::Uint32(num) => num as i64,
2417            ValueType::Uint64(num) => num as i64,
2418            _ => panic!("you cannot convert non numeric values into numeric ones.")
2419        }
2420    }
2421}
2422
2423impl Into<u8> for ValueType {
2424    fn into(self) -> u8 {
2425        match self {
2426            ValueType::Uint8(num) => num,
2427            ValueType::Uint16(num) => match num > 255 {
2428                true => panic!("you cannot convert u16's if it's value is bigger than capacity of 8 bit values"),
2429                false => num as u8
2430            },
2431            ValueType::Uint32(num) => match num > 255 {
2432                true => panic!("you cannot convert u32's if it's value is bigger than capacity of 8 bit values"),
2433                false => num as u8
2434            },
2435            ValueType::Uint64(num) => match num > 255 {
2436                true => panic!("you cannot convert u64's if it's value is bigger than capacity of 8 bit values"),
2437                false => num as u8
2438            },
2439            ValueType::Usize(num) => match num > 255 {
2440                true => panic!("you cannot convert usizes if it's value is bigger than capacity of 8 bit values"),
2441                false => num as u8
2442            },
2443            ValueType::Int8(num) => match num < 0 {
2444                true => panic!("you cannot convert i8's if it's value is lower than 0"),
2445                false => num as u8
2446            },
2447            ValueType::Int16(num) => match num < 0 || num > 255 {
2448                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."),
2449                false => num as u8
2450            },
2451            ValueType::Int32(num) => match num < 0 || num > 255 {
2452                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."),
2453                false => num as u8
2454            },
2455            ValueType::Int64(num) => match num < 0 || num > 255 {
2456                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."),
2457                false => num as u8
2458            },
2459            ValueType::Int128(num) => match num < 0 || num > 255 {
2460                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."),
2461                false => num as u8
2462            }
2463            _ => panic!("you cannot convert non numeric values into numeric ones.")
2464        }
2465    }
2466}
2467
2468impl Into<u16> for ValueType {
2469    fn into(self) -> u16 {
2470        match self {
2471            ValueType::Uint8(num) => num as u16,
2472            ValueType::Uint16(num) => num,
2473            ValueType::Uint32(num) => match num > 65_535 {
2474                true => panic!("you cannot convert u32's if it's value is bigger than capacity of 32 bit values"),
2475                false => num as u16
2476            },
2477            ValueType::Uint64(num) => match num > 65_535 {
2478                true => panic!("you cannot convert u64's if it's value is bigger than capacity of 32 bit values"),
2479                false => num as u16
2480            },
2481            ValueType::Usize(num) => match num > 65_535 {
2482                true => panic!("you cannot convert usizes if it's value is bigger than capacity of 32 bit values"),
2483                false => num as u16
2484            },
2485            ValueType::Int8(num) => match num < 0 {
2486                true => panic!("you cannot convert i8's if it's value is lower than 0"),
2487                false => num as u16
2488            },
2489            ValueType::Int16(num) => match num < 0 {
2490                true => panic!("you cannot convert i16's if it's value is lower than 0"),
2491                false => num as u16
2492            },
2493            ValueType::Int32(num) => match num < 0 || num > 65_535 {
2494                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."),
2495                false => num as u16
2496            },
2497            ValueType::Int64(num) => match num < 0 || num > 65_535 {
2498                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."),
2499                false => num as u16
2500            },
2501            ValueType::Int128(num) => match num < 0 || num > 65_535 {
2502                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."),
2503                false => num as u16
2504            }
2505            _ => panic!("you cannot convert non numeric values into numeric ones.")
2506        }
2507    }
2508}
2509
2510impl Into<u32> for ValueType {
2511    fn into(self) -> u32 {
2512        match self {
2513            ValueType::Uint8(num) => num as u32,
2514            ValueType::Uint16(num) => num as u32,
2515            ValueType::Uint32(num) => num,
2516            ValueType::Uint64(num) => match num > 4_294_967_295 {
2517                true => panic!("you cannot convert u64's if it's value is bigger than capacity of 32 bit values"),
2518                false => num as u32
2519            },
2520            ValueType::Usize(num) => match num > 4_294_967_295 {
2521                true => panic!("you cannot convert usizes if it's value is bigger than capacity of 32 bit values"),
2522                false => num as u32
2523            },
2524            ValueType::Int8(num) => match num < 0 {
2525                true => panic!("you cannot convert i8's if it's value is lower than 0"),
2526                false => num as u32
2527            },
2528            ValueType::Int16(num) => match num < 0 {
2529                true => panic!("you cannot convert i16's if it's value is lower than 0"),
2530                false => num as u32
2531            },
2532            ValueType::Int32(num) => match num < 0 {
2533                true => panic!("you cannot convert i32's if it's value is lower than 0"),
2534                false => num as u32
2535            },
2536            ValueType::Int64(num) => match num < 0 || num > 4_294_967_295 {
2537                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."),
2538                false => num as u32
2539            },
2540            ValueType::Int128(num) => match num < 0 || num > 4_294_967_295 {
2541                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."),
2542                false => num as u32
2543            }
2544            _ => panic!("you cannot convert non numeric values into numeric ones.")
2545        }
2546    }
2547}
2548
2549impl Into<u64> for ValueType {
2550    fn into(self) -> u64 {
2551        match self {
2552            ValueType::Usize(num) => num as u64,
2553            ValueType::Uint8(num) => num as u64,
2554            ValueType::Uint16(num) => num as u64,
2555            ValueType::Uint32(num) => num as u64,
2556            ValueType::Uint64(num) => num,
2557            ValueType::Int8(num) => match num < 0 {
2558                true => panic!("you cannot turn a negative value into u64"),
2559                false => num as u64
2560            },
2561            ValueType::Int16(num) => match num < 0 {
2562                true => panic!("you cannot turn a negative value into u64"),
2563                false => num as u64
2564            },
2565            ValueType::Int32(num) => match num < 0 {
2566                true => panic!("you cannot turn a negative value into u64"),
2567                false => num as u64
2568            },
2569            ValueType::Int64(num) => match num < 0 {
2570                true => panic!("you cannot turn a negative value into u64"),
2571                false => num as u64
2572            },
2573            ValueType::Int128(num) => match num < 0 {
2574                true => panic!("you cannot turn a negative value into u64"),
2575                false => num as u64
2576            },
2577            _ => panic!("you cannot convert non numeric values into numeric ones.")
2578        }
2579    }
2580}
2581
2582impl Into<usize> for ValueType {
2583    fn into(self) -> usize {
2584        match self {
2585            ValueType::Int8(num) => match num < 0 {
2586                true => panic!("you cannot convert negative numbers to usize"),
2587                false => num as usize
2588            },
2589            ValueType::Int16(num) => match num < 0 {
2590                true => panic!("you cannot convert negative numbers to usize"),
2591                false => num as usize
2592            },
2593            ValueType::Int32(num) => match num < 0 {
2594                true => panic!("you cannot convert negative numbers to usize"),
2595                false => num as usize
2596            },
2597            ValueType::Int64(num) => match num < 0 {
2598                true => panic!("you cannot convert negative numbers to usize"),
2599                false => num as usize
2600            },
2601            ValueType::Int128(num) => match num < 0 {
2602                true => panic!("you cannot convert negative numbers to usize"),
2603                false => num as usize
2604            },
2605            ValueType::Usize(num) => num,
2606            ValueType::Uint8(num) => num as usize,
2607            ValueType::Uint16(num) => num as usize,
2608            ValueType::Uint32(num) => num as usize,
2609            ValueType::Uint64(num) => num as usize,
2610            _ => panic!("you cannot convert non numeric values into numeric ones.")
2611        }
2612    }
2613}
2614
2615/// Enum that benefits you to add json values to structs. They can be used with json functions.
2616/// That variants represents that kind of json values:
2617#[derive(Debug, Clone)]
2618pub enum JsonValue<'a> {
2619    /// 
2620    /// Example Value: ["hello", 21, "again"]
2621    /// 
2622    Array(&'a Vec<ValueType>), 
2623    
2624    /// 
2625    /// Example Value: {"name": "necdet", "message": "hello", "id": 1}
2626    /// 
2627    Object(&'a Vec<(&'a str, &'a ValueType)>), 
2628    
2629    ///
2630    /// example value: [{"name": "necdet", "message": "hello", "id": 1}, {"name": "kemal", "message": "hi", "id": 2}]
2631    /// 
2632    ObjectArray(&'a Vec<Vec<(&'a str, &'a ValueType)>>), 
2633    
2634    /// It's same with `ValueType` enums, just for simply passing it to that enum.
2635    Initial(&'a ValueType), 
2636
2637    /// 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.
2638    MysqlJsonObject(&'a Vec<(&'a str, &'a ValueType)>)
2639}
2640
2641impl <'a>std::fmt::Display for JsonValue<'a> {
2642    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
2643        match self {
2644            JsonValue::Array(values) => {
2645                let mut json_str = "[".to_string();
2646
2647                for (index, value) in values.iter().enumerate() {
2648                    if index == 0 {
2649                        json_str = format!("{}{}", json_str, value)
2650                    } else {
2651                        json_str = format!("{}, {}", json_str, value)
2652                    }
2653                }
2654
2655                json_str = format!("{}]", json_str);
2656
2657                write!(f, "{}", json_str)
2658            },
2659            JsonValue::Object(props) => {
2660                let mut json_str = "{".to_string();
2661
2662                for (index, value) in props.iter().enumerate() {
2663                    if index == 0 {
2664                        json_str = format!("{}\"{}\": {}", json_str, value.0, value.1)
2665                    } else {
2666                        json_str = format!("{}, \"{}\": {}", json_str, value.0, value.1)
2667                    }
2668                }
2669
2670                json_str = format!("{}}}", json_str);
2671
2672                write!(f, "{}", json_str)
2673            },
2674            JsonValue::MysqlJsonObject(props) => {
2675                let mut json_str = "JSON_OBJECT(".to_string();
2676
2677                for (index, value) in props.iter().enumerate() {
2678                    if index == 0 {
2679                        json_str = format!("{}'{}', {}", json_str, value.0, value.1)
2680                    } else {
2681                        json_str = format!("{}, '{}', {}", json_str, value.0, value.1)
2682                    }
2683                }
2684
2685                json_str = format!("{})", json_str);
2686
2687                write!(f, "{}", json_str)
2688            },
2689            JsonValue::ObjectArray(array) => {
2690                let mut json_str = "[".to_string();
2691
2692                for (index1, object) in array.into_iter().enumerate() {
2693                    let mut object_str = "{".to_string();
2694
2695                    for (index2, property) in object.into_iter().enumerate() {
2696                        if index2 == 0 {
2697                            object_str = format!("{}\"{}\": {}", object_str, property.0, property.1)
2698                        } else {
2699                            object_str = format!("{}, \"{}\": {}", object_str, property.0, property.1)
2700                        }
2701                    }
2702
2703                    object_str = format!("{}}}", object_str);
2704
2705                    if index1 == 0 {
2706                        json_str = format!("{}{}", json_str, object_str)
2707                    } else {
2708                        json_str = format!("{}, {}", json_str, object_str)
2709                    }
2710                }
2711
2712                write!(f, "{}]", json_str)
2713            },
2714            JsonValue::Initial(value) => write!(f, "{}", value.to_string())
2715        }
2716    }
2717}
2718
2719/// Enum that benefits you to define what you want with a foreign key.
2720#[derive(Debug, Clone)]
2721pub enum ForeignKeyActions {
2722    Cascade, Restrict, SetNull, NoAction, SetDefault
2723}
2724
2725impl std::fmt::Display for ForeignKeyActions {
2726    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
2727        match self {
2728            &ForeignKeyActions::Cascade => write!(f, "CASCADE"),
2729            &ForeignKeyActions::NoAction => write!(f, "NO ACTION"),
2730            &ForeignKeyActions::Restrict => write!(f, "RESTRICT"),
2731            &ForeignKeyActions::SetNull => write!(f, "SET NULL"),
2732            &ForeignKeyActions::SetDefault => write!(f, "SET DEFAULT")
2733        }
2734    }
2735}
2736
2737#[cfg(test)]
2738mod test {
2739    use super::*;
2740
2741    #[test]
2742    pub fn test_schema_query_declarative(){
2743        let schema = SchemaBuilder::create("blog_website").unwrap().if_not_exists().finish();
2744
2745        assert_eq!("CREATE DATABASE IF NOT EXISTS blog_website;", schema);
2746    }
2747
2748    #[test]
2749    pub fn test_schema_query_imperative(){
2750        let mut schema = SchemaBuilder::create("blog_website").unwrap();
2751        schema.if_not_exists();
2752        let schema_query = schema.finish();
2753
2754        assert_eq!("CREATE DATABASE IF NOT EXISTS blog_website;", schema_query);
2755    }
2756
2757    #[test]
2758    pub fn test_use_another_schema(){
2759        let schema = SchemaBuilder::use_another_schema("chat_website").unwrap().finish();
2760
2761        assert_eq!("USE chat_website;", schema);
2762    }
2763
2764    #[test]
2765    pub fn test_insert_query(){
2766        let columns = vec!["title", "author", "description"];
2767        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())];
2768    
2769        let insert_query = QueryBuilder::insert(columns, values).unwrap().table("blogs").finish();
2770
2771        println!("{}", insert_query);
2772        assert_eq!("INSERT INTO blogs (title, author, description) VALUES ('What's Up?', 'John Doe', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.');".to_string(), 
2773                    insert_query);
2774    }
2775
2776    #[test]
2777    pub fn test_update_query(){
2778        let update_query = QueryBuilder::update().unwrap().table("blogs").set("title", ValueType::String("Hello Rust!".to_string())).set("author", ValueType::String("Necdet".to_string())).finish();
2779
2780        assert_eq!("UPDATE blogs SET title = 'Hello Rust!', author = 'Necdet';", update_query);
2781    }
2782
2783    #[test]
2784    pub fn test_delete_query(){
2785        let delete_query = QueryBuilder::delete().unwrap().table("blogs").where_("id", "=", ValueType::String("1".to_string())).finish();
2786
2787        assert_eq!("DELETE FROM blogs WHERE id = '1';", delete_query);
2788    }
2789
2790    #[test]
2791    pub fn test_select_query_declarative(){
2792        let mut select = QueryBuilder::select(["id", "title", "description", "point"].to_vec()).unwrap();
2793
2794        let select_query = select.table("blogs")
2795                                    .where_("id", "=", ValueType::Int32(10))
2796                                    .and("point", ">", ValueType::Int8(90))
2797                                    .or("id", "=", ValueType::Int64(20))
2798                                    .finish();
2799
2800        assert_eq!("SELECT id, title, description, point FROM blogs WHERE id = 10 AND point > 90 OR id = 20;", select_query)
2801    }
2802
2803    #[test]
2804    pub fn test_select_query_imperative(){
2805        let mut select = QueryBuilder::select(["*"].to_vec()).unwrap();
2806
2807        let select_query = select.table("blogs");
2808        select_query.where_("id", "=", ValueType::Uint8(5));
2809        select_query.or("id", "=", ValueType::Usize(25));
2810
2811        let finish_the_select_query = select_query.finish();
2812
2813        assert_eq!("SELECT * FROM blogs WHERE id = 5 OR id = 25;", finish_the_select_query);
2814    }
2815
2816    #[test]
2817    pub fn test_create_table() {
2818        let mut table_builder_2 = TableBuilder::create("blabla", "projects");
2819        let table_builder_2 = table_builder_2.if_not_exists();
2820    
2821        table_builder_2.add_column("id").col_type("INT").primary_key().auto_increment();
2822        table_builder_2.add_column("name").col_type("VARCHAR(40)").not_null();
2823        table_builder_2.add_column("owner_id").col_type("INT").not_null();
2824        
2825        // if we create a table, the first ForeignKeyItem's table field is not necessary.
2826        let opts = ForeignKey {
2827            first: ForeignKeyItem { table: "".to_string(), column: "owner_id".to_string() },
2828            second: ForeignKeyItem { table: "users".to_string(), column: "id".to_string() },
2829            constraint: None,
2830            on_delete: Some(ForeignKeyActions::Cascade),
2831            on_update: None
2832        };
2833        
2834        table_builder_2.foreign_key(opts);
2835    
2836        let table_builder_2 = table_builder_2.finish();
2837
2838        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();
2839
2840        assert_eq!(raw_query, table_builder_2);
2841    }
2842
2843    #[test]
2844    pub fn test_time_value_type(){
2845        let columns = ["name", "password", "last_login"].to_vec();
2846        let values = [ValueType::String("necoo33".to_string()), ValueType::String("123456".to_string()), ValueType::Datetime("CURRENT_TIMESTAMP".to_string())].to_vec();
2847    
2848        let time_insert_test = QueryBuilder::insert(columns, values).unwrap().table("users").finish();
2849
2850        assert_eq!(time_insert_test, "INSERT INTO users (name, password, last_login) VALUES ('necoo33', '123456', CURRENT_TIMESTAMP);");
2851
2852        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();
2853
2854        assert_eq!(time_update_test, "UPDATE users SET last_login = CURRENT_TIMESTAMP WHERE name = 'necoo33';")
2855    }
2856
2857    #[test]
2858    pub fn test_unix_epoch_times(){
2859        let columns = ["name", "password", "last_login"].to_vec();
2860        let values = [ValueType::String("necoo33".to_string()), ValueType::String("123456".to_string()), ValueType::EpochTime(134523452)].to_vec();
2861    
2862        let time_insert_with_unix_epoch_times_test = QueryBuilder::insert(columns, values).unwrap().table("users").finish();
2863        assert_eq!(time_insert_with_unix_epoch_times_test, "INSERT INTO users (name, password, last_login) VALUES ('necoo33', '123456', FROM_UNIXTIME(134523452));");
2864    
2865        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();
2866
2867        assert_eq!(time_update_with_unix_epoch_times_test, "UPDATE users SET last_login = FROM_UNIXTIME(3456436) WHERE name = 'necoo33';");
2868
2869        let columns = ["name", "password", "last_login", "created_at"].to_vec();
2870
2871        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();
2872
2873        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;")
2874    }
2875
2876    #[test]
2877    pub fn test_where_ins(){
2878        let columns = ["name", "age", "id", "last_login"].to_vec();
2879
2880        let ids = [ValueType::Int32(1), ValueType::Int16(12), ValueType::Int64(8)].to_vec();
2881
2882        let test_where_in = QueryBuilder::select(columns).unwrap().table("users").where_in("id", &ids).finish();
2883
2884        assert_eq!(test_where_in, "SELECT name, age, id, last_login FROM users WHERE id IN (1, 12, 8);");
2885
2886        let columns = ["name", "age", "id", "last_login"].to_vec();
2887
2888        let ids = [ValueType::Int32(1), ValueType::Int16(12), ValueType::Int32(8)].to_vec();
2889
2890        let test_where_not_in = QueryBuilder::select(columns).unwrap().table("users").where_not_in("id", &ids).finish();
2891
2892        assert_eq!(test_where_not_in, "SELECT name, age, id, last_login FROM users WHERE id NOT IN (1, 12, 8);");
2893
2894        let columns = ["name", "age", "id", "last_login"].to_vec();
2895
2896        let test_where_in_custom = QueryBuilder::select(columns).unwrap().table("users").where_in_custom("id", "1, 12, 8").finish();
2897
2898        assert_eq!(test_where_in_custom, "SELECT name, age, id, last_login FROM users WHERE id IN (1, 12, 8);");
2899
2900        let columns = ["name", "age", "id", "last_login"].to_vec();
2901
2902        let test_where_not_in_custom = QueryBuilder::select(columns).unwrap().table("users").where_not_in_custom("id", "1, 12, 8").finish();
2903
2904        assert_eq!(test_where_not_in_custom, "SELECT name, age, id, last_login FROM users WHERE id NOT IN (1, 12, 8);")
2905    }
2906
2907    #[test]
2908    pub fn test_count() {
2909        let count_of_users = QueryBuilder::count("*", None).table("users").where_("age", ">", ValueType::Int32(25)).finish();
2910
2911        assert_eq!(count_of_users, "SELECT COUNT(*) FROM users WHERE age > 25;".to_string());
2912
2913        let count_of_users_as_length = QueryBuilder::count("*", Some("length")).table("users").finish();
2914
2915        assert_eq!(count_of_users_as_length, "SELECT COUNT(*) AS length FROM users;".to_string());
2916    }
2917
2918    #[test]
2919    pub fn test_json_extract(){
2920        // tests with "select()" constructor
2921
2922        let select_query_1 = QueryBuilder::select(["*"].to_vec()).unwrap().json_extract("data", ".age", Some("student_age")).table("students").finish();
2923
2924        assert_eq!(select_query_1, "SELECT JSON_EXTRACT(data, '$.age') AS student_age FROM students;".to_string());
2925        
2926        let select_query_2 = QueryBuilder::select(["*"].to_vec()).unwrap().json_extract("data", ".age", Some("student_age")).table("students").where_("successfull", "=", ValueType::Int8(1)).finish();
2927
2928        assert_eq!(select_query_2, "SELECT JSON_EXTRACT(data, '$.age') AS student_age FROM students WHERE successfull = 1;".to_string());
2929        
2930        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();
2931
2932        assert_eq!(select_query_3, "SELECT JSON_EXTRACT(data, '$.age') AS student_age FROM students WHERE JSON_EXTRACT(points, '$.name') > 85;".to_string());
2933
2934        // tests with ".where_cond()" method
2935
2936        let with_where = QueryBuilder::delete().unwrap().table("users").where_("id", ">", ValueType::Int32(200)).json_extract("id", ".user_id", None).finish();
2937
2938        assert_eq!(with_where, "DELETE FROM users WHERE JSON_EXTRACT(id, '$.user_id') > 200;".to_string());
2939
2940        // tests with ".table()" method
2941        
2942        let fields = ["name", "age"].to_vec();
2943        
2944        let with_table = QueryBuilder::select(fields).unwrap().table("users").json_extract("id", ".user_id", None).finish();
2945
2946        assert_eq!(with_table, "SELECT JSON_EXTRACT(id, '$.user_id') FROM users;".to_string());
2947
2948        // tests with ".and()" method
2949
2950        let fields = ["name", "age"].to_vec();
2951
2952        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();
2953
2954        assert_eq!(with_and_1, "SELECT name, age FROM height WHERE weight > 60 AND JSON_EXTRACT(height, '$.student_height') > 1.7;".to_string());
2955    
2956        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();
2957
2958        assert_eq!(with_and_2, "SELECT * FROM students WHERE weight > 60 AND JSON_EXTRACT(height, '$.student_height') > 1.7;".to_string());
2959
2960        // tests with ".or()" method
2961
2962        let fields = ["name", "age"].to_vec();
2963
2964        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();
2965
2966        assert_eq!(with_or_1, "SELECT name, age FROM height WHERE weight > 60 OR JSON_EXTRACT(height, '$.student_height') > 1.71;".to_string());
2967    
2968        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();
2969
2970        assert_eq!(with_or_2, "SELECT * FROM students WHERE weight > 60 OR JSON_EXTRACT(height, '$.student_height') > 1.71;".to_string());
2971
2972        // tests with "count()" constructor
2973
2974        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();
2975
2976        assert_eq!(count_query_1, "SELECT JSON_EXTRACT(age, '$.student_age') AS value, COUNT(*) FROM students GROUP BY points HAVING points > 75;".to_string());
2977
2978        // tests with ".order_by()" method
2979        
2980        let fields = ["title", "desc", "created_at", "updated_at", "keywords", "pics", "likes"].to_vec();
2981
2982        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();
2983
2984        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());
2985    
2986        // tests with ".json_extract()" method
2987
2988        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();
2989
2990        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());
2991    }
2992
2993    #[test]
2994    pub fn test_json_contains(){
2995        // test with "select()" constructor:
2996
2997        let ins = [ValueType::Int32(1), ValueType::Int32(5), ValueType::Int64(11)].to_vec();
2998        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();
2999
3000        assert_eq!(select_query, "SELECT JSON_CONTAINS(pic, '\"/files/hello.jpg\"', '$.path') FROM users WHERE id IN (1, 5, 11);".to_string());
3001
3002        // test with ".where_cond()" method:
3003
3004        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();
3005
3006        assert_eq!(where_query, "SELECT * FROM users WHERE JSON_CONTAINS(pic, '\"blablabla.jpg\"', '$.name');".to_string());
3007
3008        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();
3009
3010        assert_eq!(and_query_1, "SELECT * FROM users WHERE age > 15 AND JSON_CONTAINS(graduation_stats, 80.11, '$.average_point');".to_string());
3011
3012        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();
3013
3014        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());
3015        
3016        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();
3017    
3018        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());
3019
3020        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();
3021    
3022        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());
3023
3024        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();
3025    
3026        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());
3027        
3028        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();
3029
3030        assert_eq!(or_query_1, "SELECT * FROM users WHERE age > 15 OR JSON_CONTAINS(graduation_stats, 80.11, '$.average_point');".to_string());
3031        
3032        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();
3033        
3034        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());
3035                
3036        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();
3037            
3038        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());
3039        
3040        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();
3041            
3042        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());
3043        
3044        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();
3045            
3046        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());
3047
3048        let name = ValueType::JsonString("necdet".to_string());
3049        let id = ValueType::Int32(1);
3050        let is_active = ValueType::Boolean(true);
3051
3052        let object = vec![("name", &name), ("id", &id), ("isActive", &is_active)];
3053
3054        let mysql_json_object = JsonValue::MysqlJsonObject(&object);
3055
3056        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();
3057
3058        assert_eq!("SELECT * FROM users WHERE JSON_CONTAINS(pic, JSON_OBJECT('name', \"necdet\", 'id', 1, 'isActive', true), '$');", where_query_2)
3059    }
3060
3061    #[test]
3062    pub fn test_like_later_than_where_keywords(){
3063        let mut like_query_1 = QueryBuilder::select(["*"].to_vec()).unwrap();
3064
3065        let like_query_1 = like_query_1.table("blogs")
3066                                                      .where_("id", "=", ValueType::Int32(5))
3067                                                      .like(["title", "description"].to_vec(), "hello")
3068                                                      .finish();
3069
3070        assert_eq!(like_query_1, "SELECT * FROM blogs WHERE id = 5 AND (title LIKE '%hello%' OR description LIKE '%hello%');");
3071    
3072        let mut like_query_2 = QueryBuilder::select(["*"].to_vec()).unwrap();
3073
3074        let ins = vec![ValueType::Int32(1), ValueType::Int32(2), ValueType::Int32(3)];
3075        let like_query_2 = like_query_2.table("blogs")
3076                                                          .where_in("id", &ins)
3077                                                          .like(["title", "description", "keywords"].to_vec(), "necdet")
3078                                                          .limit(10)
3079                                                          .offset(0)
3080                                                          .finish();
3081
3082        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;")
3083    }
3084
3085    #[test]
3086    pub fn test_ordering_functions(){
3087        let order_by_query = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").order_by("id", "asc").order_by("weight", "desc").order_by("point", "asc").finish();
3088
3089        assert_eq!(order_by_query, "SELECT * FROM users ORDER BY id ASC, weight DESC, point ASC;");
3090
3091        let order_by_random_query = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").order_random().finish();
3092
3093        assert_eq!(order_by_random_query, "SELECT * FROM users ORDER BY RAND();");
3094
3095        let roles = ["admin", "moderator", "member", "guest"].to_vec();
3096        let field_query_1 = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").order_by_field("role", roles.clone()).finish();
3097
3098        assert_eq!(field_query_1, "SELECT * FROM users ORDER BY FIELD(role, 'admin', 'moderator', 'member', 'guest');");
3099
3100        let field_query_2 = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").order_by("id", "asc").order_by_field("role", roles.clone()).finish();
3101
3102        assert_eq!(field_query_2, "SELECT * FROM users ORDER BY id ASC, FIELD(role, 'admin', 'moderator', 'member', 'guest');");
3103
3104        let field_query_3 = QueryBuilder::select(["*"].to_vec()).unwrap().table("users").order_by_field("role", roles.clone()).order_by("id", "asc").finish();
3105
3106        assert_eq!(field_query_3, "SELECT * FROM users ORDER BY FIELD(role, 'admin', 'moderator', 'member', 'guest'), id ASC;");
3107        
3108        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();
3109
3110        assert_eq!(field_query_4, "SELECT * FROM users ORDER BY FIELD(role, 'admin', 'moderator', 'member', 'guest'), FIELD(status, 'active', 'banned', 'unverified');");
3111    }
3112
3113    #[test]
3114    pub fn test_unions(){
3115        let mut union_1 = QueryBuilder::select(vec!["name", "age", "id"]).unwrap();
3116        union_1.table("users").where_("age", ">", ValueType::Int32(7));
3117
3118        let union_2 = QueryBuilder::select(vec!["name", "age", "id"]).unwrap()
3119                                                          .table("users")
3120                                                          .where_("age", "<", ValueType::Int32(15))
3121                                                          .union(vec![union_1])
3122                                                          .finish();
3123
3124        assert_eq!(union_2, "(SELECT name, age, id FROM users WHERE age < 15) UNION (SELECT name, age, id FROM users WHERE age > 7);");
3125
3126        let mut union_1 = QueryBuilder::select(vec!["id", "title", "description", "published"]).unwrap();
3127        union_1.table("blogs").like(vec!["title"], "text");
3128
3129        let mut union_2 = QueryBuilder::select(vec!["id", "title", "description", "published"]).unwrap();
3130        union_2.table("blogs").like(vec!["description"], "some text");
3131
3132        let union_3 = QueryBuilder::select(vec!["id", "title", "description", "published"]).unwrap()
3133                                                              .table("blogs")
3134                                                              .where_("published", "=", ValueType::Boolean(true))
3135                                                              .union_all(vec![union_1, union_2])
3136                                                              .finish();
3137
3138        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%');");
3139    }
3140
3141    #[test]
3142    pub fn test_json_value(){
3143        let name = ValueType::JsonString("necdet".to_string());
3144        let age = ValueType::Int8(25);
3145        let id = ValueType::Int32(1);
3146
3147        let values = vec![("name", &name), ("age", &age), ("id", &id)];
3148
3149        let json_object = JsonValue::Object(&values);
3150
3151        assert_eq!("{\"name\": \"necdet\", \"age\": 25, \"id\": 1}", json_object.to_string());
3152
3153        let mysql_json_object = JsonValue::MysqlJsonObject(&values);
3154
3155        assert_eq!("JSON_OBJECT('name', \"necdet\", 'age', 25, 'id', 1)", mysql_json_object.to_string());
3156
3157        let name2 = ValueType::JsonString("cevdet".to_string());
3158        let age2 = ValueType::Int8(24);
3159        let id2 = ValueType::Int32(2);
3160
3161        let name3 = ValueType::JsonString("serap".to_string());
3162        let age3 = ValueType::Int8(21);
3163        let id3 = ValueType::Int32(3);
3164
3165        let object1 = vec![("name", &name), ("age", &age), ("id", &id)];
3166        let object2 = vec![("name", &name2), ("age", &age2), ("id", &id2)];
3167        let object3 = vec![("name", &name3), ("age", &age3), ("id", &id3)];
3168
3169        let objects = vec![object1, object2, object3];
3170        
3171        let json_array = JsonValue::ObjectArray(&objects);
3172
3173        assert_eq!("[{\"name\": \"necdet\", \"age\": 25, \"id\": 1}, {\"name\": \"cevdet\", \"age\": 24, \"id\": 2}, {\"name\": \"serap\", \"age\": 21, \"id\": 3}]", json_array.to_string());
3174    }
3175
3176    #[test]
3177    pub fn test_json_array_append(){
3178        let lesson = ("lesson", &ValueType::String("math".to_string()));
3179        let point = ("point", &ValueType::Int32(100));
3180
3181        let values = vec![lesson, point];
3182        
3183        let object = JsonValue::MysqlJsonObject(&values);
3184
3185        let query = QueryBuilder::update().unwrap()
3186                                         .table("users")
3187                                         .json_array_append("points", Some(""), object.clone())
3188                                         .where_("id", "=", ValueType::Int8(1))
3189                                         .finish();
3190
3191        assert_eq!("UPDATE users SET points = JSON_ARRAY_APPEND(points, '$', JSON_OBJECT('lesson', 'math', 'point', 100)) WHERE id = 1;", query);
3192
3193        let query = QueryBuilder::update().unwrap()
3194                                         .table("users")
3195                                         .set("status", ValueType::String("passed".to_string()))
3196                                         .json_array_append("points", Some(""), object)
3197                                         .where_("id", "=", ValueType::Int8(1))
3198                                         .finish();
3199
3200        assert_eq!("UPDATE users SET status = 'passed', points = JSON_ARRAY_APPEND(points, '$', JSON_OBJECT('lesson', 'math', 'point', 100)) WHERE id = 1;", query);
3201    }
3202
3203    #[test]
3204    pub fn test_json_remove() {
3205        let query = QueryBuilder::update().unwrap()
3206                                         .table("blogs")
3207                                         .json_remove("likes", vec!["[10]"])
3208                                         .where_("blog_id", "=", ValueType::Int32(20))
3209                                         .finish();
3210
3211        assert_eq!(query, "UPDATE blogs SET likes = JSON_REMOVE(likes, '$[10]') WHERE blog_id = 20;");
3212        
3213        let query = QueryBuilder::update().unwrap()
3214                                         .table("blogs")
3215                                         .set("blabla", ValueType::Int32(50))
3216                                         .json_remove("likes", vec!["[10]", "[11]", "[12]"])
3217                                         .where_("blog_id", "=", ValueType::Int32(20))
3218                                         .finish();
3219
3220        println!("{}", query)
3221    }
3222}
qubl/lib.rs

qubl/
lib.rs
