dengine/
my_sql.rs

1use super::mysql;
2use super::mysql::prelude::{ConvIr, FromValue};
3use super::{
4    chrono, round2, Affected, Connectionable, Desult, Dypes, Error, Insertable, Params, Queryable,
5    Rnd2, Row, Rowable, SelectHolder,
6};
7use mysql::Value;
8use std;
9use std::collections::HashMap;
10
11impl From<mysql::Error> for Error {
12    fn from(val: mysql::Error) -> Self {
13        match val {
14            mysql::Error::IoError(x) => Error::LibErr(x.to_string()),
15            mysql::Error::MySqlError(x) => Error::SQLErr(x.to_string()),
16            mysql::Error::DriverError(x) => Error::LibErr(x.to_string()),
17            mysql::Error::UrlError(x) => Error::LibErr(x.to_string()),
18            mysql::Error::FromValueError(x) => {
19                Error::ConversionErr(format!("From mysql Value: {:?}", x))
20            }
21            mysql::Error::FromRowError(x) => {
22                Error::ConversionErr(format!("From mysql row: {:?}", x))
23            }
24        }
25    }
26}
27
28#[derive(Debug)]
29pub struct Connection {
30    host: String,
31    user_name: String,
32    password: String,
33    db_name: String,
34    con: mysql::Pool,
35}
36
37impl Connection {
38    pub fn new(host: String, user_name: String, password: String, db_name: String) -> Self {
39        let con_str = format!(
40            "mysql://{}:{}@{}:{}/{}",
41            user_name, password, "localhost", "3306", db_name
42        );
43
44        let con = mysql::Pool::new(con_str).unwrap();
45        Connection {
46            host,
47            user_name,
48            password,
49            db_name,
50            con,
51        }
52    }
53}
54
55impl Connectionable for Connection {
56    fn execute<P>(&self, sql: &str, params: P) -> Desult<()>
57    where
58        P: std::clone::Clone,
59        Params: std::convert::From<P>,
60    {
61        let params = Params::from(params);
62        let params = mysql::Params::from(params);
63        self.con.prep_exec(sql, &params).map_err(|e| {
64            println!("Database error: {:?}", e);
65            Error::from(e)
66        })?;
67        Ok(())
68    }
69
70    fn value<T, R>(&self, sql: &str, colum: &str, params: R) -> Desult<T>
71    where
72        T: std::convert::From<Dypes>,
73        R: std::clone::Clone,
74        Params: std::convert::From<R>,
75    {
76        let params = Params::from(params);
77        let params = mysql::Params::from(params);
78        let res: Option<mysql::Row> = self.con.first_exec(sql, &params).map_err(|e| {
79            println!("Database error: {:?}", e);
80            Error::from(e)
81        })?;
82
83        let res = match res {
84            Some(x) => Ok::<Dypes, String>(x.get::<Dypes, &str>(colum).unwrap()),
85            None => {
86                return Err(Error::SQLErr(
87                    "Failed to get result out of query".to_string(),
88                ))
89            }
90        };
91
92        match res {
93            Ok(x) => Ok(T::from(x)),
94            Err(_) => Err(Error::Unknown("Failed to unwrap value".to_string())),
95        }
96    }
97
98    fn row<T, R>(&self, sql: &str, params: R) -> Desult<T>
99    where
100        T: Queryable,
101        R: std::clone::Clone,
102        Params: std::convert::From<R>,
103    {
104        let params = Params::from(params);
105        let params = mysql::Params::from(params);
106        let res: Option<mysql::Row> = self.con.first_exec(sql, &params).map_err(|e| {
107            println!("Database error: {:?}", e);
108            Error::from(e)
109        })?;
110
111        let res = match res {
112            Some(x) => Ok::<T, String>(T::new(Row::new(&x))),
113            None => {
114                return Err(Error::SQLErr(
115                    "Failed to get result out of query".to_string(),
116                ))
117            }
118        };
119
120        match res {
121            Ok(x) => Ok(x),
122            Err(_) => Err(Error::Unknown("Failed to unwrap value".to_string())),
123        }
124    }
125
126    fn select<T: Queryable + std::fmt::Debug, P: std::clone::Clone>(
127        &self,
128        sql: &str,
129        params: P,
130        calc_found_rows: bool,
131    ) -> Desult<SelectHolder<T>>
132    where
133        Params: std::convert::From<P>,
134    {
135        let params = Params::from(params);
136        let params = mysql::Params::from(params);
137        let res: Vec<T> = self
138            .con
139            .prep_exec(sql, &params)
140            .map(|result| {
141                result
142                    .map(|x| x.unwrap())
143                    .map(|row| T::new(Row::new(&row)))
144                    .collect()
145            }).map_err(|e| {
146                println!("Database error: {:?}", e);
147                Error::from(e)
148            })?;
149
150        let res_len = res.len();
151
152        if !calc_found_rows {
153            Ok(SelectHolder {
154                data: res,
155                count: res_len,
156            })
157        } else {
158            let start_index = match sql.find(" from ") {
159                Some(x) => x,
160                None => return Err(Error::SQLErr("No from in sql".to_string())),
161            };
162
163            let end_index = match sql.to_lowercase().find(" limit ") {
164                Some(x) => x,
165                None => return Err(Error::SQLErr("No limit in sql".to_string())),
166            };
167
168            let new_sql = "select count(*) as count ".to_string();
169
170            let new_sql = new_sql + &sql[start_index..end_index];
171
172            let count: Vec<usize> = self
173                .con
174                .prep_exec(new_sql, &params)
175                .map(|result| {
176                    result
177                        .map(|x| x.unwrap())
178                        .map(|row| row.get("count").unwrap())
179                        .collect()
180                }).map_err(|e| Error::from(e))?;
181
182            match count.get(0) {
183                Some(x) => {
184                    println!("inside get ",);
185                    return Ok(SelectHolder {
186                        data: res,
187                        count: *x,
188                    });
189                }
190                None => Err(Error::LibErr("Count get error".to_string())),
191            }
192        }
193    }
194
195    fn insert_update<T: Insertable>(&self, table: &str, fields: Vec<T>) -> Desult<Affected> {
196        let mut c_arr = Vec::new();
197        let mut q_arr = Vec::new();
198        let mut a_arr = Vec::new();
199        let data_fields = T::fields();
200        let mut q_str: String = "".to_string();
201        for (i, n) in fields.iter().enumerate() {
202            q_arr.push(Vec::new());
203            let values = n.values();
204            for (j, m) in data_fields.iter().enumerate() {
205                if i == 0 {
206                    c_arr.push(m.to_string());
207                }
208                q_arr[i].push("?");
209                a_arr.push(values[j].clone());
210            }
211            if i != 0 {
212                q_str.push(',');
213            }
214            q_str.push_str(format!("({})", q_arr[i].join(&",")).as_str());
215        }
216
217        let sql = format!(
218            "INSERT INTO {} ({}) VALUES {} ON DUPLICATE KEY UPDATE {}",
219            table,
220            c_arr.join(&","),
221            q_str,
222            Self::gen_dupdate(c_arr)
223        );
224
225        println!("sql is {}", sql);
226        let res: Affected = self
227            .con
228            .prep_exec(sql, &a_arr)
229            .map(|result| Affected {
230                affected_rows: result.affected_rows(),
231                last_insert_id: result.last_insert_id(),
232            }).map_err(|e| {
233                println!("Database error: {:?}", e);
234                Error::from(e)
235            })?;
236
237        Ok(res)
238    }
239
240    fn insert<T: Insertable>(&self, table: &str, fields: Vec<T>) -> Desult<Affected> {
241        let colum_names: Vec<String> = T::fields();
242        let values: Vec<Dypes> = fields.iter().fold(Vec::new(), |mut acc, x| {
243            acc.append(&mut x.values());
244            acc
245        });
246
247        let q_arr: Vec<String> = fields.iter().fold(Vec::new(), |mut acc, _x| {
248            let single: Vec<&str> = std::iter::repeat("?").take(colum_names.len()).collect();
249            acc.push(format!("({})", single.join(",")));
250            acc
251        });
252
253        let sql = format!(
254            "INSERT INTO {} ({}) VALUES ({})",
255            table,
256            colum_names.join(","),
257            q_arr.join(",")
258        );
259
260        let res: Affected = self
261            .con
262            .prep_exec(sql, &values)
263            .map(|result| Affected {
264                affected_rows: result.affected_rows(),
265                last_insert_id: result.last_insert_id(),
266            }).map_err(|e| {
267                println!("Database error: {:?}", e);
268                Error::from(e)
269            })?;
270
271        Ok(res)
272    }
273
274    fn update<T: Insertable>(
275        &self,
276        table: &str,
277        fields: Vec<T>,
278        where_fields: HashMap<&str, Dypes>,
279    ) -> Desult<Affected> {
280        let mut values: Vec<Dypes> = fields.iter().fold(Vec::new(), |mut acc, x| {
281            acc.append(&mut x.values());
282            acc
283        });
284
285        let colum_names: Vec<String> = T::fields();
286
287        let vars: Vec<String> = colum_names.iter().map(|x| format!("{} = ?", x)).collect();
288
289        let where_str: Vec<String> = where_fields
290            .iter()
291            .map(|(key, _)| format!("{}?", key))
292            .collect();
293
294        for value in where_fields.values() {
295            values.push(value.clone());
296        }
297
298        drop(where_fields);
299
300        let sql = format!(
301            "UPDATE {} SET {} WHERE {}",
302            table,
303            vars.join(","),
304            where_str.join(" and ")
305        );
306
307        let res: Affected = self
308            .con
309            .prep_exec(sql, &values)
310            .map(|result| Affected {
311                affected_rows: result.affected_rows(),
312                last_insert_id: result.last_insert_id(),
313            }).map_err(|e| {
314                println!("Database error: {:?}", e);
315                Error::from(e)
316            })?;
317
318        Ok(res)
319    }
320
321    fn gen_dupdate(colums: Vec<String>) -> String {
322        let mut rt = Vec::new();
323        for n in colums {
324            rt.push(format!("{} = VALUES({}) ", n, n));
325        }
326        rt.join(&",")
327    }
328
329    fn delete_ids<T>(
330        &self,
331        table: &str,
332        id_colum: &str,
333        id_values: Vec<T>,
334        in_out: &str,
335    ) -> Desult<Affected>
336    where
337        T: std::clone::Clone,
338        Dypes: std::convert::From<T>,
339    {
340        let id_values: Vec<mysql::Value> = id_values
341            .into_iter()
342            .map(|x| mysql::Value::from(Dypes::from(x)))
343            .collect();
344        //let params = mysql::Params::from(params);
345        let mut c_arr: Vec<char> =
346            Vec::with_capacity(std::mem::size_of::<char>() * id_values.len());
347
348        for _ in &id_values {
349            c_arr.push('?');
350        }
351
352        let sql = format!(
353            "delete from {} where {} {} ({})",
354            table,
355            id_colum,
356            in_out,
357            c_arr
358                .iter()
359                .enumerate()
360                .map(|e| if e.0 != 0 {
361                    format!(",{}", e.1)
362                } else {
363                    e.1.to_string()
364                }).collect::<String>()
365        );
366
367        println!("sql is {}", sql);
368        let res: Affected = self
369            .con
370            .prep_exec(sql, &id_values)
371            .map(|result| Affected {
372                affected_rows: result.affected_rows(),
373                last_insert_id: result.last_insert_id(),
374            }).map_err(|e| {
375                println!("Database error: {:?}", e);
376                Error::from(e)
377            })?;
378
379        Ok(res)
380    }
381
382    fn delete_wid<T>(&self, table: &str, id_colum: &str, id_values: Vec<T>) -> Desult<Affected>
383    where
384        T: std::clone::Clone,
385        Dypes: std::convert::From<T>,
386    {
387        self.delete_ids::<T>(table, id_colum, id_values, "IN")
388    }
389
390    fn delete_nwid<T>(&self, table: &str, id_colum: &str, id_values: Vec<T>) -> Desult<Affected>
391    where
392        T: std::clone::Clone,
393        Dypes: std::convert::From<T>,
394    {
395        self.delete_ids::<T>(table, id_colum, id_values, "NOT IN")
396    }
397
398    fn concat_colums(colums: Vec<&str>) -> String {
399        let s = colums.join(",");
400        format!("concat_ws(' ', {})", s)
401    }
402}
403
404impl From<Dypes> for mysql::Value {
405    fn from(x: Dypes) -> mysql::Value {
406        match x {
407            Dypes::Uint(x) => Value::UInt(x),
408            Dypes::Int(x) => Value::Int(x),
409            Dypes::Float(x) => Value::Float(x),
410            Dypes::String(x) => Value::Bytes(x.into_bytes()),
411            Dypes::Bytes(x) => Value::Bytes(x),
412            Dypes::Null => Value::NULL,
413        }
414    }
415}
416
417#[derive(Debug)]
418pub struct DypesIr {
419    val: Dypes,
420}
421
422impl ConvIr<Dypes> for DypesIr {
423    fn new(v: Value) -> Result<DypesIr, mysql::FromValueError> {
424        let ir = DypesIr {
425            val: Dypes::from(v),
426        };
427        Ok(ir)
428    }
429    fn commit(self) -> Dypes {
430        self.val
431    }
432    fn rollback(self) -> Value {
433        Value::from(self.val)
434    }
435}
436
437impl FromValue for Dypes {
438    type Intermediate = DypesIr;
439}
440
441impl From<Value> for Dypes {
442    fn from(x: Value) -> Dypes {
443        match x {
444            Value::UInt(d) => Dypes::Uint(d),
445            Value::Int(d) => Dypes::Int(d),
446            Value::Float(d) => Dypes::Float(d),
447            Value::Bytes(d) => Dypes::Bytes(d),
448            Value::NULL => Dypes::Null,
449            Value::Date(y, m, d, h, mm, s, ss) => {
450                Dypes::String(date_to_string((y, m, d, h, mm, s, ss)))
451            }
452            Value::Time(n, days, hours, mins, secs, micro_secs) => {
453                Dypes::String(time_to_string((n, days, hours, mins, secs, micro_secs)))
454            }
455        }
456    }
457}
458
459fn date_to_string(date: (u16, u8, u8, u8, u8, u8, u32)) -> String {
460    format!(
461        "{}-{}-{} {}:{}:{}:{}",
462        date.0, date.1, date.2, date.3, date.4, date.5, date.6
463    )
464}
465
466fn time_to_string(time: (bool, u32, u8, u8, u8, u32)) -> String {
467    let n = if time.0 { "+" } else { "-" };
468    format!(
469        "{}{}:{}:{}:{}:{}",
470        n, time.1, time.2, time.3, time.4, time.5
471    )
472}
473
474#[derive(Debug)]
475pub struct Rnd2Ir(f64);
476
477impl mysql::prelude::ConvIr<Rnd2> for Rnd2Ir {
478    fn new(v: mysql::Value) -> Result<Self, mysql::FromValueError> {
479        match v {
480            mysql::Value::Float(fl_val) => Ok(Rnd2Ir(round2(fl_val))),
481            v => Err(mysql::FromValueError(v)),
482        }
483    }
484    fn commit(self) -> Rnd2 {
485        Rnd2::new(self.0)
486    }
487    fn rollback(self) -> mysql::Value {
488        mysql::Value::Float(self.0)
489    }
490}
491
492impl mysql::prelude::FromValue for Rnd2 {
493    type Intermediate = Rnd2Ir;
494}
495
496impl Rowable for mysql::Row {
497    fn get_val(&self, key: &str) -> Option<Dypes> {
498        self.get::<Value, &str>(key).map(|x| Dypes::from(x))
499    }
500
501    fn get_date_string(&self, key: &str, format: &str) -> Desult<String> {
502        let a: mysql::Value = self.get(key).unwrap();
503        let b: (i32, u32, u32, u32, u32, u32, u32) = match a {
504            mysql::Value::Date(a, b, c, d, e, f, g) => (
505                a as i32, b as u32, c as u32, d as u32, e as u32, f as u32, g as u32,
506            ),
507            _ => return Err(Error::date_conv_err(key)),
508        };
509
510        if b.1 <= 0 || b.2 <= 0 {
511            return Err(Error::date_conv_err(key));
512        }
513
514        let date = chrono::NaiveDate::from_ymd(b.0, b.1, b.2);
515
516        let a = date.format(format);
517
518        Ok(format!("{}", a))
519    }
520}
521
522impl From<Params> for mysql::Params {
523    fn from(x: Params) -> mysql::Params {
524        mysql::Params::from(x.values())
525    }
526}