df_helper/orm/
db.rs

1use std::sync::mpsc;
2use std::thread;
3use json::{array, JsonValue, object};
4use crate::{datetime, tools};
5use crate::orm::fields::field;
6use crate::orm::fields::field::Field;
7use crate::orm::mode::mode::Mode;
8use crate::orm::mode::mssql::Mssql;
9use crate::orm::mode::mysql::Mysql;
10
11pub struct Db {
12    /// 当前数据库类型
13    model: String,
14    /// 当前的数据库
15    database: String,
16    /// 当前数据库配置名称
17    default: String,
18    /// 当前的数据库配置
19    connection: JsonValue,
20    /// 数据库配置集合
21    connections: JsonValue,
22    /// 数据表名称前缀
23    prefix: String,
24    /// id 系统生成 数据库生成
25    idauto: bool,
26    mode: Mode,
27
28    fields: JsonValue,
29    /// 具体数据
30    table: String,
31    field: Vec<String>,
32    page: u64,
33    limit: u64,
34    pagelimit: String,
35    /// 排序
36    order: JsonValue,
37    /// 分组
38    group: String,
39    /// 返回sql
40    fetch_sql: bool,
41    /// 去重
42    distinct: bool,
43    /// 新增id集合
44    ids: JsonValue,
45
46    where_and: JsonValue,
47    where_or: JsonValue,
48    /// 字段比较
49    where_column: JsonValue,
50    /// 累加累减
51    setup: String,
52
53    /// 操作符
54    union: String,
55    union_all: bool,
56}
57
58impl Db {
59    /// 数据库链接配置
60    ///
61    /// ````json
62    /// {
63    ///     "type": "mysql",            数据库类型
64    ///     "hostname": "127.0.0.1",    数据库地址
65    ///     "hostport": 3306,           数据库端口
66    ///     "database": "tests",        数据库名
67    ///     "username": "root",         用户账号
68    ///     "userpass": "11111111",     用户密码
69    ///     "params": [],               链接参数
70    ///     "charset": "utf8mb4",      语言
71    ///     "prefix": ""                数据库前缀
72    ///     "idauto": false,            主键在创建时方式 false 使用自定义生成 true 使用系统生成
73    /// }
74    /// ````
75    pub fn connect(config: JsonValue) -> Self {
76        let connections = config["connections"].clone();
77        let default = config["default"].to_string();
78        let connection = connections[default.clone()].clone();
79        let database = connection["database"].to_string();
80        let model = connection["type"].to_string();
81        let prefix = connection["prefix"].to_string();
82        let idauto = connection["idauto"].as_bool().unwrap();
83        let mode = Db::_mode(model.as_str(), connection.clone());
84        Self {
85            database,
86            default,
87            connection,
88            connections,
89            model,
90            prefix,
91            idauto,
92            mode,
93
94            table: String::new(),
95            field: vec![],
96            fields: object! {},
97            page: 0,
98            limit: 0,
99            pagelimit: String::new(),
100
101            fetch_sql: false,
102            order: array![],
103            group: String::new(),
104            distinct: false,
105            ids: array![],
106            where_and: array![],
107            where_or: array![],
108            where_column: array![],
109            setup: String::new(),
110            union: String::new(),
111            union_all: false,
112        }
113    }
114    fn _mode(model: &str, connection: JsonValue) -> Mode {
115        match model {
116            "mysql" => {
117                let dsn = format!("mysql://{}:{}@{}:{}/{}", connection["username"], connection["userpass"], connection["hostname"], connection["hostport"], connection["database"]);
118                Mode::Mysql(Mysql::connect(dsn.clone(), connection["database"].to_string()))
119            }
120            "mssql" => {
121                let dsn = format!("mssql://{}:{}@{}:{}/{}", connection["username"], connection["userpass"], connection["hostname"], connection["hostport"], connection["database"]);
122                Mode::Mssql(Mssql::connect(dsn.clone(), connection["database"].to_string()))
123            }
124            _ => {
125                let dsn = format!("mysql://{}:{}@{}:{}/{}", connection["username"], connection["userpass"], connection["hostname"], connection["hostport"], connection["database"]);
126                Mode::Mysql(Mysql::connect(dsn.clone(), connection["database"].to_string()))
127            }
128        }
129    }
130    /// 重载参数
131    fn _reset(&mut self) {
132        self.table = String::new();
133        self.field = vec![];
134        self.page = 0;
135        self.limit = 0;
136        self.pagelimit = String::new();
137
138        self.fetch_sql = false;
139        self.order = array![];
140        self.group = String::new();
141        self.distinct = false;
142        self.ids = array![];
143        self.where_and = array![];
144        self.where_or = array![];
145        self.where_column = array![];
146        self.setup = String::new();
147        self.union = String::new();
148        self.union_all = false;
149    }
150    /// 添加配置
151    pub fn add_config(mut self, default: &str, config: JsonValue) -> Self {
152        if self.connections[default].is_empty() {
153            self.connections[default] = config;
154        }
155        self
156    }
157    /// 切换配置
158    pub fn set_connection(&mut self, default: &str) -> &mut Self {
159        self.default = default.to_string();
160        self.connection = self.connections[default].clone();
161        self.model = self.connection["type"].to_string();
162        self.database = self.connection["database"].to_string();
163        self.mode = Db::_mode(self.model.as_str(), self.connection.clone());
164        self
165    }
166    /// 获取配置清单
167    pub fn get_connections(self) -> JsonValue {
168        return self.connections;
169    }
170    /// 执行数据库
171    pub fn database(database: &str, config: JsonValue) -> Self {
172        let mut data = Db::connect(config);
173        data.database = database.to_string();
174        data
175    }
176    /// 查询数据表是否存在
177    pub fn query_table(&mut self, table: &str) -> bool {
178        self._reset();
179        self.mode.query_table(table)
180    }
181    /// 创建数据表
182    pub fn create_table(&mut self, table: &str, fields: JsonValue) -> bool {
183        self._reset();
184        self.table = table.to_string();
185        let title = fields["title"].as_str().unwrap();
186        let key = fields["key"].as_str().unwrap();
187
188        // 唯一约束
189        let mut unique_fields = String::new();
190        let mut unique_name = String::new();
191        let mut unique = String::new();
192        for item in fields["unique"].members() {
193            if unique_fields == "" {
194                unique_fields = format!("`{}`", item);
195                unique_name = format!("unique_{}", item);
196            } else {
197                unique_fields = format!("{},`{}`", unique_fields, item);
198                unique_name = format!("{}_{}", unique_name, item);
199            }
200            unique = format!("UNIQUE KEY  `{}` ({})", unique_name, unique_fields);
201        }
202
203        // 唯一索引
204        let mut index = String::new();
205        for row in fields["index"].members() {
206            let mut index_fields = String::new();
207            let mut index_name = String::new();
208            for item in row.members() {
209                if index_fields == "" {
210                    index_fields = format!("`{}`", item);
211                    index_name = format!("index_{}", item);
212                } else {
213                    index_fields = format!("{},`{}`", index_fields, item);
214                    index_name = format!("{}_{}", index_name, item);
215                }
216            }
217            if index == "" {
218                index = format!("INDEX `{}` ({})", index_name, index_fields);
219            } else {
220                index = format!("{},\r\nINDEX `{}` ({})", index, index_name, index_fields);
221            }
222        }
223
224        let auto = fields["auto"].as_bool().unwrap();
225        let mut fieldsql = String::new();
226
227        for (name, field) in fields["fields"].entries() {
228            let row = self._fields(auto, name, field.clone());
229            if fieldsql == "" { fieldsql = format!("\r\n{}", row.clone()) } else { fieldsql = format!("{},\r\n{}", fieldsql, row.clone()); }
230        }
231
232        fieldsql = format!("{},\r\nPRIMARY KEY(`{}`)", fieldsql, key);
233
234        if unique != "" {
235            fieldsql = format!("{},\r\n{}", fieldsql, unique);
236        }
237        if index != "" {
238            fieldsql = format!("{},\r\n{}", fieldsql, index);
239        }
240
241        let charset = self.connection["charset"].to_string();
242        let collate = format!("{}_bin", charset);
243        let sql = format!("create table IF NOT EXISTS {} ({}\r\n) ENGINE = InnoDB CHARSET = '{}' COLLATE '{}' comment '{}'", self._table(), fieldsql, charset, collate, title);
244        let res = self.mode.create_table(sql.as_str().clone());
245        return res;
246    }
247    /// 更新数据表
248    pub fn update_table(&mut self, table: &str, info: JsonValue) -> bool {
249        self._reset();
250        self.table = table.to_string();
251        let data = self.mode.fieldsinfo(self.table.as_str());
252        let mut add = object! {};
253        let mut del = object! {};
254        let mut put = object! {};
255        // let key = info["key"].clone();
256        let auto = info["auto"].as_bool().unwrap();
257        let fields = info["fields"].clone();
258        for (field, item) in fields.entries() {
259            if data[field].is_empty() {
260                add[field] = item.clone();
261            } else {
262                let old = data[field].clone();
263                let new = item.clone();
264
265                let oldtext = format!("{}|{}|def:{}|r:{}|l:{}|{}|{}|{}|{}|{}", old["title"], old["mode"], old["def"], old["require"], old["length"], old["option"], old["dec"], old["api"], old["table"], old["fields"]);
266                let newtext = format!("{}|{}|def:{}|r:{}|l:{}|{}|{}|{}|{}|{}", new["title"], new["mode"], new["def"], new["require"], new["length"], new["option"], new["dec"], new["api"], new["table"], new["fields"]);
267                if oldtext != newtext {
268                    println!("old> {}", old);
269                    println!("new> {}", new);
270                    println!("old> {}", oldtext);
271                    println!("new> {}", newtext);
272                    put[field] = item.clone();
273                    continue;
274                }
275            }
276        }
277        for (field, item) in data.entries() {
278            if fields[field].is_empty() {
279                del[field] = item.clone();
280            }
281        }
282        let mut sql = array![];
283
284        // sql.push(format!("ALTER TABLE {} DROP UNIQUE", table)).unwrap();
285        // sql.push(format!("ALTER TABLE {} DROP INDEX", table)).unwrap();
286        // sql.push(format!("ALTER TABLE {} DROP PRIMARY KEY", table)).unwrap();
287        // sql.push(format!("ALTER TABLE {} DROP UNIQUE {}", table,key)).unwrap();
288
289        for (field, _) in del.entries() {
290            sql.push(format!("ALTER TABLE {} DROP {}", table, field)).unwrap();
291        }
292        for (field, item) in add.entries() {
293            sql.push(format!("ALTER TABLE {} ADD COLUMN({})", table, self._fields(auto.clone(), field, item.clone()))).unwrap();
294        }
295        for (field, item) in put.entries() {
296            sql.push(format!("ALTER TABLE {} CHANGE {} {}", table, field, self._fields(auto.clone(), field, item.clone()))).unwrap();
297        }
298
299        // 唯一约束
300        let mut unique_fields = String::new();
301        let mut unique_name = String::new();
302        let unique = {
303            let mut unique=String::new();
304            for item in fields["unique"].members() {
305                if unique_fields == "" {
306                    unique_fields = format!("`{}`", item);
307                    unique_name = format!("unique_{}", item);
308                } else {
309                    unique_fields = format!("{},`{}`", unique_fields, item);
310                    unique_name = format!("{}_{}", unique_name, item);
311                }
312                unique = format!("UNIQUE KEY  `{}` ({})", unique_name, unique_fields);
313            }
314            unique
315        };
316
317        // 唯一索引
318        let mut index = String::new();
319        for row in fields["index"].members() {
320            let mut index_fields = String::new();
321            let mut index_name = String::new();
322            for item in row.members() {
323                if index_fields == "" {
324                    index_fields = format!("`{}`", item);
325                    index_name = format!("index_{}", item);
326                } else {
327                    index_fields = format!("{},`{}`", index_fields, item);
328                    index_name = format!("{}_{}", index_name, item);
329                }
330            }
331            if index == "" {
332                index = format!("INDEX `{}` ({})", index_name, index_fields);
333            } else {
334                index = format!("{},\r\nINDEX `{}` ({})", index, index_name, index_fields);
335            }
336        }
337
338        if unique != "" {
339            sql.push(format!("ALTER TABLE {} ADD {}", table, unique)).unwrap();
340        }
341        if index != "" {
342            sql.push(format!("ALTER TABLE {} ADD {}", table, index)).unwrap();
343        }
344        // sql.push(format!("ALTER TABLE {} ADD PRIMARY KEY(`{}`)", table, key)).unwrap();
345
346        let mut error = true;
347        for item in sql.members() {
348            let res = self.mode.update_table(item.as_str().unwrap());
349            println!("{} {}", res, item);
350            if !res {
351                error = false;
352            }
353        }
354        return error;
355    }
356
357    /// 字段信息
358    pub fn fieldsinfo(&mut self) -> JsonValue {
359        let mut data = self.mode.fieldsinfo(self.table.as_str());
360        let fields = self.field.clone();
361        let mut list = array![];
362
363        fn row_data(field: &str, mut item: JsonValue) -> JsonValue {
364            item["label"] = item["title"].clone();
365            item["field"] = field.clone().into();
366            item["name"] = field.clone().into();
367            return item;
368        }
369
370        if fields.is_empty() {
371            for (field, item) in data.entries_mut() {
372                let row = row_data(field.clone().into(), item.clone());
373                list.push(row.clone()).unwrap();
374                self.fields[self.table.as_str().clone()][field.clone()] = row.clone();
375            }
376            return list;
377        }
378        for (field, item) in data.entries_mut() {
379            for index in fields.iter() {
380                if index == field {
381                    let row = row_data(field.clone().into(), item.clone());
382                    list.push(row.clone()).unwrap();
383                    self.fields[self.table.as_str().clone()][field.clone()] = row.clone();
384                }
385            }
386        }
387        return list;
388    }
389    pub fn fieldsinfo_obj(&mut self) -> JsonValue {
390        self.fieldsinfo();
391        return self.fields[self.table.as_str().clone()].clone();
392    }
393    /// 字段sql拼接
394    fn _fields(&mut self, auto: bool, field: &str, data: JsonValue) -> String {
395        let mut row = format!("");
396        let mode = data["mode"].as_str().unwrap();
397        let require = data["require"].as_bool().unwrap_or(false);
398        let title = data["title"].as_str().unwrap_or("未知标题");
399        match mode {
400            "key" => {
401                let length = data["length"].as_i32().unwrap();
402                row = field::Key::field(field, title, length, auto).sql();
403            }
404            "pass" => {
405                let default = data["def"].as_str().unwrap();
406                let length = data["length"].as_i32().unwrap();
407                row = field::Str::pass(require.clone(), field.clone(), title.clone(), length.clone(), default.clone()).sql();
408            }
409            "string" => {
410                let default = data["def"].as_str().unwrap_or("");
411                let length = data["length"].as_i32().unwrap_or(20);
412                row = field::Str::string(require.clone(), field.clone(), title.clone(), length.clone(), default.clone()).sql();
413            }
414            "table" => {
415                let table = data["table"].as_str().unwrap();
416                let fields = data["fields"].as_str().unwrap();
417                let api = data["api"].as_str().unwrap();
418                row = field::Table::field(require.clone(), field.clone(), title.clone(),
419                                          table.clone(), fields.clone(), "", api.clone()).sql();
420            }
421            "text" => {
422                let default = data["def"].as_str().unwrap_or("");
423                row = field::Text::field(require.clone(), field.clone(), title.clone(), default.clone()).sql();
424            }
425            "file" => {
426                let default = data["def"].as_str().unwrap_or("");
427                let length = data["length"].as_i32().unwrap_or(1);
428                row = field::File::field(require.clone(), field.clone(), title.clone(), length.clone(), default.clone()).sql();
429            }
430            "number" => {
431                let length = data["length"].as_i32().unwrap();
432                let dec = data["dec"].as_i32().unwrap();
433                let default = data["def"].as_f32().unwrap();
434                row = field::Number::field(require.clone(), field.clone(), title.clone(),
435                                           length.clone(), dec.clone(),
436                                           default.clone()).sql();
437            }
438            "switch" => {
439                let default = data["def"].as_bool().unwrap_or(false);
440                row = field::Switch::field(require.clone(), field.clone(), title.clone(),
441                                           default.clone()).sql();
442            }
443            "datetime" => {
444                let default = data["def"].as_str().unwrap();
445                row = field::Date::datetime(require.clone(), field.clone(), title.clone(),
446                                            default.clone()).sql();
447            }
448            "year" => {
449                let default = data["def"].as_str().unwrap();
450                row = field::Date::year(require.clone(), field.clone(), title.clone(),
451                                        default.clone()).sql();
452            }
453            "date" => {
454                let default = data["def"].as_str().unwrap();
455                row = field::Date::date(require.clone(), field.clone(), title.clone(),
456                                        default.clone()).sql();
457            }
458            "time" => {
459                let default = data["def"].as_str().unwrap();
460                row = field::Date::time(require.clone(), field.clone(), title.clone(),
461                                        default.clone()).sql();
462            }
463            "timestamp" => {
464                let dec = data["dec"].as_i32().unwrap();
465                let default = data["def"].as_f64().unwrap_or(0.0);
466                row = field::Timestamp::timestamp(require.clone(), field.clone(), title.clone(),
467                                                  dec.clone(), default.clone()).sql();
468            }
469            "select" => {
470                let option = data["option"].clone();
471                let multiple = data["multiple"].as_bool().unwrap();
472                row = field::Select::field(require.clone(), field.clone(), title.clone(),
473                                           option.clone(), multiple.clone(), data["def"].clone()).sql();
474            }
475            _ => {}
476        }
477        format!("{}", row.clone())
478    }
479    /// 删除数据表
480    ///
481    /// * all true 删除表数据与表结构 false 只清空表数据不删除表结构
482    pub fn delete_table(&mut self, table: &str, all: bool) -> bool {
483        self._reset();
484        self.table = table.to_string();
485        let sql = {
486            if all {
487                format!("drop table {}", self._table())
488            } else {
489                format!("truncate table {}", self.table)
490            }
491        };
492        return self.mode.delete_table(sql.as_str());
493    }
494    /// 条件拼装sql
495    fn _where_sql(&mut self) -> String {
496        let wheres = {
497            let mut sql = {
498                if self.where_and.len() <= 0 && self.where_or.len() <= 0
499                {
500                    "".to_string()
501                } else {
502                    let mut where_and = "".to_string();
503                    for item in self.where_and.members() {
504                        match item[1].as_str().unwrap() {
505                            "between" => {
506                                if where_and == "" {
507                                    where_and = format!("`{}` BETWEEN '{}' AND '{}'", item[0], item[2][0], item[2][1]);
508                                } else {
509                                    where_and = format!("{} AND `{}` BETWEEN '{}' AND '{}'", where_and, item[0], item[2][0], item[2][1]);
510                                }
511                            }
512                            "notbetween" => {
513                                if where_and == "" {
514                                    where_and = format!("`{}` NOT BETWEEN '{}' AND '{}'", item[0], item[2][0], item[2][1]);
515                                } else {
516                                    where_and = format!("{} AND {} NOT BETWEEN '{}' AND '{}'", where_and, item[0], item[2][0], item[2][1]);
517                                }
518                            }
519                            "null" => {
520                                let herad = {
521                                    if where_and == "" {
522                                        where_and
523                                    } else {
524                                        format!("{} AND", where_and)
525                                    }
526                                };
527                                if item[2].as_bool().unwrap() {
528                                    where_and = format!("{} `{}` IS NULL", herad, item[0]);
529                                } else {
530                                    where_and = format!("{} `{}` IS NOT NULL", herad, item[0]);
531                                }
532                            }
533                            "in" => {
534                                let inlist = {
535                                    let mut list = "".to_string();
536                                    for item in item[2].members() {
537                                        if list == "" {
538                                            list = format!("\"{}\"", item);
539                                        } else {
540                                            list = format!("{},\"{}\"", list, item);
541                                        }
542                                    }
543                                    list
544                                };
545                                if where_and == "" {
546                                    where_and = format!("`{}` {} ({})", item[0], item[1], inlist);
547                                } else {
548                                    where_and = format!("{} AND `{}` {} ({})", where_and, item[0], item[1], inlist);
549                                }
550                            }
551                            "notin" => {
552                                let inlist = {
553                                    let mut list = "".to_string();
554                                    for item in item[2].members() {
555                                        if list == "" {
556                                            list = format!("\"{}\"", item);
557                                        } else {
558                                            list = format!("{},\"{}\"", list, item);
559                                        }
560                                    }
561                                    list
562                                };
563                                if where_and == "" {
564                                    where_and = format!("`{}` NOT IN ({})", item[0], inlist);
565                                } else {
566                                    where_and = format!("{} AND `{}` NOT IN ({})", where_and, item[0], inlist);
567                                }
568                            }
569                            _ => {
570                                let key = item[0].clone();
571                                let key = tools::string::split(key.to_string(), "|");
572                                if key.len() > 1 {
573                                    let mut tx = "".to_string();
574                                    for keys in key.members() {
575                                        if tx == "" {
576                                            tx = format!("`{}` {} \"{}\"", keys, item[1], item[2]);
577                                        } else {
578                                            tx = format!("{} OR `{}` {} \"{}\"", tx, keys, item[1], item[2]);
579                                        }
580                                    }
581                                    if where_and == "" {
582                                        where_and = format!("{}", tx);
583                                    } else {
584                                        where_and = format!("{} AND ({})", where_and, tx);
585                                    }
586                                } else {
587                                    if where_and == "" {
588                                        where_and = format!("`{}` {} \"{}\"", item[0], item[1], item[2]);
589                                    } else {
590                                        where_and = format!("{} AND `{}` {} \"{}\"", where_and, item[0], item[1], item[2]);
591                                    }
592                                }
593                            }
594                        }
595                    }
596                    if self.where_or.len() > 0 && where_and != "" {
597                        where_and = format!("({}) OR", where_and);
598                    }
599                    let mut where_or = "".to_string();
600
601                    for item in self.where_or.members() {
602                        match item[1].as_str().unwrap() {
603                            "null" => {
604                                let herad = {
605                                    if where_and == "" {
606                                        where_and
607                                    } else {
608                                        format!("{} OR", where_and)
609                                    }
610                                };
611                                if item[2].as_bool().unwrap() {
612                                    where_and = format!("{} `{}` IS NULL", herad, item[0]);
613                                } else {
614                                    where_and = format!("{} `{}` IS NOT NULL", herad, item[0]);
615                                }
616                            }
617                            "in" => {
618                                let inlist = {
619                                    let mut list = "".to_string();
620                                    for item in item[2].members() {
621                                        if list == "" {
622                                            list = format!("\"{}\"", item);
623                                        } else {
624                                            list = format!("{},\"{}\"", list, item);
625                                        }
626                                    }
627                                    list
628                                };
629                                if where_or == "" {
630                                    where_or = format!("`{}` IN ({})", item[0], inlist);
631                                } else {
632                                    where_or = format!("{} OR `{}` IN ({})", where_or, item[0], inlist);
633                                }
634                            }
635                            "notin" => {
636                                let inlist = {
637                                    let mut list = "".to_string();
638                                    for item in item[2].members() {
639                                        if list == "" {
640                                            list = format!("\"{}\"", item);
641                                        } else {
642                                            list = format!("{},\"{}\"", list, item);
643                                        }
644                                    }
645                                    list
646                                };
647                                if where_or == "" {
648                                    where_or = format!("`{}` NOT IN ({})", item[0], inlist);
649                                } else {
650                                    where_or = format!("{} OR `{}` NOT IN ({})", where_or, item[0], inlist);
651                                }
652                            }
653                            "between" => {
654                                if where_or == "" {
655                                    where_or = format!("`{}` BETWEEN {} AND {}", item[0], item[2][0], item[2][1]);
656                                } else {
657                                    where_or = format!("{} OR `{}` BETWEEN {} AND {}", where_or, item[0], item[2][0], item[2][1]);
658                                }
659                            }
660                            "notbetween" => {
661                                if where_or == "" {
662                                    where_or = format!("`{}` NOT BETWEEN {} AND {}", item[0], item[2][0], item[2][1]);
663                                } else {
664                                    where_or = format!("{} OR `{}` NOT BETWEEN {} AND {}", where_or, item[0], item[2][0], item[2][1]);
665                                }
666                            }
667                            _ => {
668                                let key = item[0].clone();
669                                let key = tools::string::split(key.to_string(), "|");
670                                if key.len() > 1 {
671                                    let mut tx = "".to_string();
672                                    for keys in key.members() {
673                                        if tx == "" {
674                                            tx = format!("`{}` {} \"{}\"", keys, item[1], item[2]);
675                                        } else {
676                                            tx = format!("{} OR `{}` {} \"{}\"", tx, keys, item[1], item[2]);
677                                        }
678                                    }
679                                    if where_or == "" {
680                                        where_or = format!("{}", tx);
681                                    } else {
682                                        where_or = format!("{} OR ({})", where_and, tx);
683                                    }
684                                } else {
685                                    if where_or == "" {
686                                        where_or = format!("`{}` {} \"{}\"", item[0], item[1], item[2]);
687                                    } else {
688                                        where_or = format!("{} OR `{}` {} \"{}\"", where_or, item[0], item[1], item[2]);
689                                    }
690                                }
691                            }
692                        }
693                    }
694                    format!("WHERE {} {}", where_and, where_or)
695                }
696            };
697            if self.where_column.len() > 0 {
698                let mut where_column = "".to_string();
699                for item in self.where_column.members() {
700                    if where_column == "" {
701                        where_column = format!("`{}` {} `{}`", item[0], item[1], item[2]);
702                    } else {
703                        where_column = format!("{} AND `{}` {} `{}`", where_column, item[0], item[1], item[2]);
704                    }
705                }
706                if sql.is_empty() {
707                    sql = format!("WHERE ({})", where_column);
708                } else {
709                    sql = format!("{} AND ({})", sql, where_column);
710                }
711            }
712            sql
713        };
714        wheres
715    }
716    /// table 前缀拼装
717    fn _table(&mut self) -> String {
718        let table = {
719            if self.prefix == "" {
720                self.table.clone()
721            } else {
722                format!("{}{}", self.prefix, self.table)
723            }
724        };
725        if self.database == "" {
726            table.clone()
727        } else {
728            format!("`{}`.`{}`", self.database, table)
729        }
730    }
731    /// 查询sql拼装
732    fn _select(&mut self) -> String {
733        let table = self._table();
734        let field = {
735            let mut field = String::new();
736            for item in self.field.iter() {
737                if field == "" {
738                    field = item.clone();
739                } else {
740                    field = format!("{},{}", field, item)
741                }
742            }
743            if field == "" {
744                field = "*".to_string()
745            }
746            if self.distinct {
747                field = format!("DISTINCT {}", field)
748            }
749            field
750        };
751        let order = {
752            let mut order = "".to_string();
753            for item in self.order.members() {
754                if order == "" {
755                    order = format!("ORDER BY {} {}", item[0], item[1]);
756                } else {
757                    order = format!("{},{} {}", order, item[0], item[1]);
758                }
759            }
760            order
761        };
762        let wheres = self._where_sql();
763        let union = {
764            if self.union == "" {
765                self.union.to_string()
766            } else {
767                if self.union_all {
768                    format!("UNION ALL SELECT {} FROM {} {}", field, self.union, wheres)
769                } else {
770                    format!("UNION SELECT {} FROM {} {}", field, self.union, wheres)
771                }
772            }
773        };
774        return format!("SELECT {} FROM {} {} {} {} {} {}", field, table, wheres, union, order, self.group, self.pagelimit);
775    }
776    /// 新增sql拼装
777    fn _insert(&mut self, data: JsonValue) -> String {
778        self.ids = array![];
779        let table = self._table();
780        let mut fields = "".to_string();
781        let (tx, rx) = mpsc::channel();
782        let mut fields_data = data[0].clone();
783        let idauto = self.idauto;
784        thread::spawn(move || {
785            let mut fields = "".to_string();
786            if !idauto {
787                if fields_data["id"].is_empty() {
788                    fields_data["id"] = "".into();
789                }
790            }
791            for (k, _v) in fields_data.entries() {
792                if fields == "" {
793                    fields = format!("`{}`", k);
794                } else {
795                    fields = format!("{},`{}`", fields, k);
796                }
797            }
798            tx.send(fields).unwrap();
799        });
800
801        for recived in rx {
802            fields = recived
803        }
804
805        let mut thrs = vec![];
806
807        let len = data.len();
808        let num = {
809            if len <= 100 {
810                10
811            } else if len > 100 && len <= 1000 {
812                100
813            } else if len > 1000 && len < 10000 {
814                500
815            } else if len >= 10000 && len < 100000 {
816                1000
817            } else {
818                3000
819            }
820        };
821        let count = len / num + 1;
822        let fields_infos = self.mode.fieldsinfo(self.table.as_str());
823        for index in 0..count {
824            let start = {
825                if index > 0 {
826                    index * num
827                } else {
828                    index * num
829                }
830            };
831            let end = {
832                let endlen = (index + 1) * num;
833                if len > endlen {
834                    endlen
835                } else {
836                    len
837                }
838            };
839            let list = data.clone();
840            let pcindex = index.to_string().parse::<i64>().unwrap();
841            let fields_info = fields_infos.clone();
842            let idauto = self.idauto;
843            let thr = thread::spawn(move || {
844                let mut values = "".to_string();
845                let mut ids = vec![];
846                for index in start..end {
847                    let mut item = list[index.clone()].clone();
848                    if item["id"].is_empty() {
849                        if !idauto {
850                            let id = datetime::timestamp::timestamp("us") + pcindex;
851                            item["id"] = format!("{:X}", id).into();
852                        }
853                    }
854                    ids.push(item["id"].clone());
855                    let mut row = "".to_string();
856                    for (k, v) in item.entries() {
857                        let key = fields_info[k].clone();
858                        let mode = key["mode"].as_str().unwrap();
859                        match mode {
860                            "switch" => {
861                                let t = {
862                                    if v.clone() == JsonValue::from(1) {
863                                        true
864                                    } else {
865                                        false
866                                    }
867                                };
868                                if row == "" {
869                                    row = format!("{}", t);
870                                } else {
871                                    row = format!("{},{}", row, t);
872                                }
873                            }
874                            "int" => {
875                                if row == "" {
876                                    row = format!("{}", v);
877                                } else {
878                                    row = format!("{},{}", row, v);
879                                }
880                            }
881                            "float" => {
882                                if row == "" {
883                                    row = format!("{}", v);
884                                } else {
885                                    row = format!("{},{}", row, v);
886                                }
887                            }
888                            _ => {
889                                if row == "" {
890                                    row = format!("\"{}\"", v);
891                                } else {
892                                    row = format!("{},\"{}\"", row, v);
893                                }
894                            }
895                        }
896                    }
897                    if values == "" {
898                        values = format!("({})", row);
899                    } else {
900                        values = format!("{},({})", values, row);
901                    }
902                }
903                (ids, values)
904            });
905            thrs.push(thr);
906        }
907
908        let mut values = "".to_string();
909        for thr in thrs {
910            let (ids, value) = thr.join().unwrap();
911            if value.is_empty() {
912                continue;
913            }
914            self.ids.push(ids.clone()).expect("id 加入 错误");
915            if values == "" {
916                values = format!("{}", value);
917            } else {
918                values = format!("{},{}", values, value);
919            }
920        }
921        return format!("INSERT INTO {} ({}) VALUES {}", table, fields, values);
922    }
923    /// 更新sql拼装
924    fn _update(&mut self, mut data: JsonValue) -> String {
925        self.fieldsinfo();
926        let table = self._table();
927        let mut values = "".to_string();
928        for (k, v) in data.entries_mut() {
929            let field = self.fields[self.table.as_str().clone()][k].clone();
930            match field["mode"].as_str().unwrap() {
931                "switch" => {
932                    if v.to_string() == "0" {
933                        *v = JsonValue::from(false)
934                    }
935                    if v.to_string() == "1" {
936                        *v = JsonValue::from(true)
937                    }
938                    let t = match v.as_bool().unwrap() {
939                        false => {
940                            JsonValue::from(0)
941                        }
942                        true => {
943                            JsonValue::from(1)
944                        }
945                    };
946                    *v = t.clone();
947                }
948                _ => {}
949            }
950
951            if values == "" {
952                values = format!("`{}`=\"{}\"", k, v);
953            } else {
954                values = format!("{},`{}`=\"{}\"", values, k, v);
955            }
956        }
957        if self.setup != "" {
958            if values == "" {
959                values = format!("{}", self.setup);
960            } else {
961                values = format!("{},{}", values, self.setup);
962            }
963        }
964        let wheres = self._where_sql();
965        return format!("UPDATE {} SET {} {}", table, values, wheres);
966    }
967    /// 批量更新
968    /// field 更新主键
969    fn _update_case(&mut self, field: &str, data: JsonValue) -> String {
970        let table = self._table();
971        let mut ids = array![];
972        let mut setlist = object! {};
973        for item in data.members() {
974            let wheres_value = item[field].to_string();
975            ids.push(wheres_value.clone()).expect("加入 wheres_fields 值");
976            for (k, v) in item.entries() {
977                if k == field {
978                    continue;
979                }
980                if setlist[k].is_empty() {
981                    setlist[k] = format!("WHEN {} THEN {}", wheres_value, v).into();
982                } else {
983                    setlist[k] = format!("{} WHEN {} THEN {}", setlist[k], wheres_value, v).into();
984                }
985            }
986        }
987        let mut values = "".to_string();
988        for (key, item) in setlist.entries() {
989            if values == "" {
990                values = format!("`{}` = CASE `{}` {} END", key, field, item);
991            } else {
992                values = format!("{},`{}` = CASE `{}` {} END", values, key, field, item);
993            }
994        }
995        self.where_and(field, "in", ids.into());
996        let wheres = self._where_sql();
997        return format!("UPDATE {} SET {} {}", table, values, wheres);
998    }
999    /// 删除sql拼装
1000    fn _delete(&mut self) -> String {
1001        let table = self._table();
1002
1003        let wheres = self._where_sql();
1004
1005        let order = {
1006            let mut order = "".to_string();
1007            for item in self.order.members() {
1008                if order == "" {
1009                    order = format!("ORDER BY {} {}", item[0], item[1]);
1010                } else {
1011                    order = format!("{},{} {}", order, item[0], item[1]);
1012                }
1013            }
1014            order
1015        };
1016        return format!("DELETE FROM {} {} {} {}", table, wheres, order, self.pagelimit);
1017    }
1018
1019
1020    /// 事务开始
1021    pub fn transaction(&mut self) -> bool {
1022        return self.mode.transaction();
1023    }
1024    /// 提交事务
1025    pub fn commit(&mut self) -> bool {
1026        return self.mode.commit();
1027    }
1028    /// 回滚事务
1029    pub fn rollback(&mut self) -> bool {
1030        return self.mode.rollback();
1031    }
1032
1033    /// 执行数据表
1034    pub fn table(&mut self, name: &str) -> &mut Self {
1035        self._reset();
1036        self.table = name.to_string();
1037        self
1038    }
1039
1040    /// 显示字段
1041    pub fn fields(&mut self, name: &str) -> &mut Self {
1042        self.field = vec![];
1043        let list = tools::string::split(name.to_string(), ",");
1044        for item in list.members() {
1045            self.field.push(item.to_string());
1046        }
1047        self
1048    }
1049    pub fn hidden(&mut self, name: &str) -> &mut Self {
1050        let list = tools::string::split(name.to_string(), ",");
1051        let fields = self.mode.fieldsinfo(self.table.as_str());
1052        let mut row = vec![];
1053        for (field, _) in fields.entries() {
1054            row.push(field.to_string());
1055        }
1056        for item in list.members() {
1057            row.retain(|items| if *items == item.to_string() { false } else { true });
1058        }
1059        self.field = row;
1060        self
1061    }
1062
1063    /// 页码
1064    pub fn page(&mut self, mut page: u64) -> &mut Self {
1065        if page <= 0 {
1066            page = 1;
1067        }
1068        self.page = page;
1069        self.pagelimit = format!("LIMIT {},{}", (self.page - 1) * self.limit, self.limit);
1070        self
1071    }
1072    /// 返回SQL不执行
1073    pub fn fetch_sql(&mut self, open: bool) -> &mut Self {
1074        self.fetch_sql = open;
1075        self
1076    }
1077    /// 排序
1078    pub fn order(&mut self, field: &str, sort: i8) -> &mut Self {
1079        let mut model = "ASC";
1080        if sort != 0 {
1081            model = "DESC";
1082        }
1083        self.order.push(vec![field, model].clone()).expect("插入排序内容");
1084        self
1085    }
1086    /// 分组
1087    pub fn group(&mut self, fields: &str) -> &mut Self {
1088        self.fields(fields);
1089        self.group = format!("GROUP BY {}", fields);
1090        self
1091    }
1092    /// 限制
1093    pub fn limit(&mut self, mut limit: u64) -> &mut Self {
1094        if limit <= 0 {
1095            limit = 10;
1096        }
1097        if self.page <= 0 {
1098            self.page = 1;
1099        }
1100        self.limit = limit;
1101        if self.page == 1 {
1102            self.pagelimit = format!("LIMIT {}", self.limit);
1103        } else {
1104            self.pagelimit = format!("LIMIT {},{}", (self.page - 1) * self.limit, self.limit);
1105        }
1106        self
1107    }
1108
1109    /// 去重复
1110    pub fn distinct(&mut self, open: bool) -> &mut Self {
1111        self.distinct = open;
1112        self
1113    }
1114
1115    /// 两表同字段
1116    pub fn union(&mut self, table: &str, all: bool) -> &mut Self {
1117        self.union = table.to_string();
1118        self.union_all = all;
1119        self
1120    }
1121    /// 设置and条件
1122    pub fn where_and(&mut self, k: &str, compare: &str, mut v: JsonValue) -> &mut Self {
1123        if v == JsonValue::from(true) {
1124            v = 1.into();
1125        }
1126        if v == JsonValue::from(false) {
1127            v = 0.into();
1128        }
1129        self.where_and.push(array![k, compare, v]).expect("设置or条件 错误");
1130        self
1131    }
1132
1133    /// 设置or条件
1134    pub fn where_or(&mut self, k: &str, compare: &str, mut v: JsonValue) -> &mut Self {
1135        if v == JsonValue::from(true) {
1136            v = 1.into();
1137        }
1138        if v == JsonValue::from(false) {
1139            v = 0.into();
1140        }
1141        self.where_or.push(array![k, compare, v]).expect("设置or条件 错误");
1142        self
1143    }
1144
1145    pub fn where_column(&mut self, field1: &str, compare: &str, field2: &str) -> &mut Self {
1146        self.where_column.push(array![field1, compare, field2]).expect("设置where_column条件 错误");
1147        self
1148    }
1149
1150
1151    /// 自增
1152    pub fn inc(&mut self, field: &str, mut data: JsonValue) -> &mut Self {
1153        if data == 0 {
1154            data = JsonValue::from(1);
1155        }
1156        if self.setup == "" {
1157            self.setup = format!("`{}`={}+{}", field, field, data);
1158        } else {
1159            self.setup = format!("{},`{}`={}+{}", self.setup, field, field, data);
1160        }
1161        self
1162    }
1163    /// 自减
1164    pub fn dec(&mut self, field: &str, mut data: JsonValue) -> &mut Self {
1165        if data == 0 {
1166            data = JsonValue::from(1);
1167        }
1168        if self.setup == "" {
1169            self.setup = format!("`{}`= {}-{}", field, field, data);
1170        } else {
1171            self.setup = format!("{} , `{}`= {}-{}", self.setup, field, field, data);
1172        }
1173        self
1174    }
1175
1176
1177    /// 查询-单条记录
1178    pub fn find(&mut self) -> JsonValue {
1179        self.page(1);
1180        self.limit(1);
1181        let sql = self._select();
1182        if self.fetch_sql {
1183            return JsonValue::String(sql);
1184        }
1185        let res = self.mode.select(sql, self.table.clone(), self.field.clone());
1186        if res.len() >= 1 {
1187            return res[0].clone();
1188        }
1189        return res;
1190    }
1191    /// 查询-多条记录
1192    pub fn select(&mut self) -> JsonValue {
1193        let sql = self._select();
1194        if self.fetch_sql {
1195            return JsonValue::String(sql);
1196        }
1197        let res = self.mode.select(sql, self.table.clone(), self.field.clone());
1198        return res;
1199    }
1200
1201    /// 查询-多条列记录
1202    pub fn column(&mut self) -> JsonValue {
1203        let sql = self._select();
1204        if self.fetch_sql {
1205            return JsonValue::String(sql);
1206        }
1207        let res = self.mode.select(sql, self.table.clone(), self.field.clone());
1208        if self.field.len() == 1 {
1209            let mut column = array![];
1210            for item in res.members() {
1211                column.push(item[self.field[0].clone()].clone()).expect("列加入错误");
1212            }
1213            return column;
1214        }
1215        return res;
1216    }
1217
1218
1219    /// 查询单一记录指定字段
1220    pub fn value(&mut self, field: &str) -> JsonValue {
1221        self.page(1);
1222        self.limit(1);
1223        self.fields(field);
1224        let sql = self._select();
1225        if self.fetch_sql {
1226            return JsonValue::String(sql);
1227        }
1228        let res = self.mode.select(sql, self.table.clone(), self.field.clone());
1229        if res.len() >= 1 {
1230            return res[0][field].clone();
1231        }
1232        return JsonValue::Null;
1233    }
1234    /// 添加记录
1235    pub fn add(&mut self, data: JsonValue) -> JsonValue {
1236        let only = data[0].is_empty();
1237        let sql = {
1238            if only {
1239                self._insert(array![data])
1240            } else {
1241                self._insert(data)
1242            }
1243        };
1244        if self.fetch_sql {
1245            return JsonValue::String(sql);
1246        }
1247        let res = self.mode.insert(sql);
1248        if res == self.ids.len() {
1249            if only {
1250                return self.ids[0].clone();
1251            }
1252            return self.ids.clone();
1253        }
1254        return res;
1255    }
1256    /// 更新记录
1257    pub fn update(&mut self, data: JsonValue) -> JsonValue {
1258        let sql = self._update(data);
1259        if self.fetch_sql {
1260            return JsonValue::String(sql);
1261        }
1262        let res = self.mode.update(sql);
1263        return res;
1264    }
1265    /// 批量更新记录
1266    /// field 更新主键
1267    pub fn update_case(&mut self, field: &str, data: JsonValue) -> JsonValue {
1268        let sql = self._update_case(field, data);
1269        if self.fetch_sql {
1270            return JsonValue::String(sql);
1271        }
1272        let res = self.mode.update(sql);
1273        return res;
1274    }
1275    /// 删除记录
1276    pub fn delete(&mut self) -> JsonValue {
1277        let sql = self._delete();
1278        if self.fetch_sql {
1279            return JsonValue::String(sql);
1280        }
1281        let res = self.mode.delete(sql);
1282        return res;
1283    }
1284
1285
1286    /// 统计数量
1287    pub fn count(&mut self) -> JsonValue {
1288        self.field = vec![];
1289        self.fields("COUNT(*) as count");
1290        let sql = self._select();
1291        if self.fetch_sql {
1292            return JsonValue::String(sql);
1293        }
1294        if self.group.is_empty() {
1295            let res = self.mode.aggregate_query("count", sql, self.field.clone());
1296            return res;
1297        }
1298        let res = self.mode.aggregate_query("count_group", sql, self.field.clone());
1299        return res;
1300    }
1301    /// 最大值
1302    pub fn max(&mut self, fields: &str) -> JsonValue {
1303        let list = tools::string::split(fields.to_string(), ",");
1304        for field in list.members() {
1305            self.fields(format!("MAX({}) as {}", field.clone(), field.clone()).as_str());
1306        }
1307        let sql = self._select();
1308        if self.fetch_sql {
1309            return JsonValue::String(sql);
1310        }
1311        let res = self.mode.aggregate_query("max", sql, self.field.clone());
1312        return res;
1313    }
1314    /// 最小值
1315    pub fn min(&mut self, fields: &str) -> JsonValue {
1316        let list = tools::string::split(fields.to_string(), ",");
1317        for field in list.members() {
1318            self.fields(format!("MIN({}) as {}", field.clone(), field.clone()).as_str());
1319        }
1320        let sql = self._select();
1321        if self.fetch_sql {
1322            return JsonValue::String(sql);
1323        }
1324        let res = self.mode.aggregate_query("min", sql, self.field.clone());
1325        return res;
1326    }
1327    /// 平均值
1328    pub fn avg(&mut self, fields: &str) -> JsonValue {
1329        let list = tools::string::split(fields.to_string(), ",");
1330        for field in list.members() {
1331            self.fields(format!("AVG({}) as {}", field.clone(), field.clone()).as_str());
1332        }
1333        let sql = self._select();
1334        if self.fetch_sql {
1335            return JsonValue::String(sql);
1336        }
1337        let res = self.mode.aggregate_query("avg", sql, self.field.clone());
1338        return res;
1339    }
1340    /// 求和
1341    pub fn sum(&mut self, fields: &str) -> JsonValue {
1342        let list = tools::string::split(fields.to_string(), ",");
1343        for field in list.members() {
1344            self.fields(format!("SUM({}) as {}", field.clone(), field.clone()).as_str());
1345        }
1346        let sql = self._select();
1347        if self.fetch_sql {
1348            return JsonValue::String(sql);
1349        }
1350        let res = self.mode.aggregate_query("sum", sql, self.field.clone());
1351        return res;
1352    }
1353}
df_helper/orm/db.rs

df_helper/orm/
db.rs
