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

qubl/
lib.rs
