br_db/types/
mod.rs

1use json::{JsonValue, object};
2static DISABLE_FIELD: &[&str] = &["default", "select", "delete", "insert", "update", "order", "group", "user", "password", "desc", "index", "from", "host", "user", "read", "partition"];
3
4#[cfg(feature = "db-sqlite")]
5pub mod sqlite;
6#[cfg(feature = "db-mysql")]
7pub mod mysql;
8#[cfg(feature = "db-mssql")]
9pub mod mssql;
10#[cfg(feature = "db-pgsql")]
11pub mod pgsql;
12
13pub trait DbMode {
14    /// 数据库是否存在
15    fn database_tables(&mut self) -> JsonValue;
16    /// 创建数据库
17    fn database_create(&mut self, name: &str) -> bool;
18    /// 备份
19    fn backups(&mut self, _filename: &str) -> bool {
20        false
21    }
22}
23
24#[derive(Debug, Clone)]
25pub struct TableOptions {
26    table_name: String,
27    table_title: String,
28    table_key: String,
29    table_fields: JsonValue,
30    table_unique: Vec<String>,
31    table_index: Vec<Vec<String>>,
32    table_partition: bool,
33    table_partition_columns: JsonValue,
34}
35impl TableOptions {
36    pub fn set_table_name(&mut self, name: &str) {
37        self.table_name = name.to_string()
38    }
39    pub fn set_table_title(&mut self, name: &str) {
40        self.table_title = name.to_string()
41    }
42    pub fn set_table_key(&mut self, name: &str) {
43        self.table_key = name.to_string()
44    }
45    pub fn set_table_fields(&mut self, fields: JsonValue) {
46        self.table_fields = fields;
47    }
48    pub fn set_table_unique(&mut self, unique: Vec<&str>) {
49        self.table_unique = unique.iter().map(|s| s.to_string()).collect();
50    }
51    pub fn set_table_index(&mut self, index: Vec<Vec<&str>>) {
52        self.table_index = index.iter().map(|s| s.iter().map(|s| s.to_string()).collect()).collect();
53    }
54    pub fn set_table_partition(&mut self, index: bool) {
55        self.table_partition = index;
56    }
57    pub fn set_table_partition_columns(&mut self, index: JsonValue) {
58        self.table_partition_columns = index;
59    }
60}
61impl Default for TableOptions {
62    fn default() -> Self {
63        Self {
64            table_name: "".to_string(),
65            table_title: "".to_string(),
66            table_key: "".to_string(),
67            table_fields: JsonValue::Null,
68            table_unique: vec![],
69            table_index: vec![],
70            table_partition: false,
71            table_partition_columns: JsonValue::Null,
72        }
73    }
74}
75pub trait Mode: DbMode {
76    fn table_create(&mut self, options: TableOptions) -> JsonValue;
77    fn table_update(&mut self, options: TableOptions) -> JsonValue;
78    /// 获取表信息
79    fn table_info(&mut self, table: &str) -> JsonValue;
80    /// 判断表是否存在
81    fn table_is_exist(&mut self, name: &str) -> bool;
82    /// 当前表
83    fn table(&mut self, name: &str) -> &mut Self;
84    /// 别名
85    fn change_table(&mut self, name: &str) -> &mut Self;
86
87    /// 主键自增
88    fn autoinc(&mut self) -> &mut Self;
89    /// 返回sql语句
90    fn fetch_sql(&mut self) -> &mut Self;
91    /// 排序
92    fn order(&mut self, field: &str, by: bool) -> &mut Self;
93    /// 分组
94    fn group(&mut self, field: &str) -> &mut Self;
95    /// 消除重复记录
96    fn distinct(&mut self) -> &mut Self;
97    /// JSON 字段
98    fn json(&mut self, field: &str) -> &mut Self;
99    /// location 字段
100    fn location(&mut self, field: &str) -> &mut Self;
101    /// 显示字段
102    fn field(&mut self, field: &str) -> &mut Self;
103    /// 隐藏字段
104    fn hidden(&mut self, name: &str) -> &mut Self;
105    /// 查询
106    fn where_and(&mut self, field: &str, compare: &str, value: JsonValue) -> &mut Self;
107    fn where_or(&mut self, field: &str, compare: &str, value: JsonValue) -> &mut Self;
108
109    /// 比较两个列
110    fn where_column(&mut self, field_a: &str, compare: &str, field_b: &str) -> &mut Self;
111    /// 分页
112    fn page(&mut self, page: i32, limit: i32) -> &mut Self;
113    /// 查询指定列并返回数组
114    fn column(&mut self, field: &str) -> JsonValue;
115    /// 总数量
116    fn count(&mut self) -> JsonValue;
117    /// 最大值
118    fn max(&mut self, field: &str) -> JsonValue;
119    /// 最小值
120    fn min(&mut self, field: &str) -> JsonValue;
121    /// 合计
122    fn sum(&mut self, field: &str) -> JsonValue;
123    /// 平均值
124    fn avg(&mut self, field: &str) -> JsonValue;
125    /// 获取集合
126    fn select(&mut self) -> JsonValue;
127    /// 获取单记录
128    fn find(&mut self) -> JsonValue;
129    /// 获取单一列值
130    fn value(&mut self, field: &str) -> JsonValue;
131    /// 添加
132    fn insert(&mut self, data: JsonValue) -> JsonValue;
133    /// 批量添加
134    fn insert_all(&mut self, data: JsonValue) -> JsonValue;
135
136    /// 更新
137    fn update(&mut self, data: JsonValue) -> JsonValue;
138    /// 批量更新
139    fn update_all(&mut self, data: JsonValue) -> JsonValue;
140    /// 删除
141    fn delete(&mut self) -> JsonValue;
142
143    /// 事务开始
144    fn transaction(&mut self) -> bool;
145    /// 事务提交
146    fn commit(&mut self) -> bool;
147    /// 事务回滚
148    fn rollback(&mut self) -> bool;
149    /// sql语句执行
150    fn sql(&mut self, sql: &str) -> Result<JsonValue, String>;
151    fn sql_execute(&mut self, sql: &str) -> Result<JsonValue, String>;
152    /// 自增
153    fn inc(&mut self, field: &str, num: f64) -> &mut Self;
154    /// 自减
155    fn dec(&mut self, field: &str, num: f64) -> &mut Self;
156
157    /// 构造子查询
158    fn buildsql(&mut self) -> String;
159
160    fn join_fields(&mut self, fields: Vec<&str>) -> &mut Self;
161    /// 连结
162    /// * main_table 主表表名
163    /// * main_fields 主表字段
164    /// * right_table 关联表名
165    /// * right_fields 关联字段名
166    fn join(&mut self, main_table: &str, main_fields: &str, right_table: &str, right_fields: &str) -> &mut Self;
167
168    /// 内连接--用来取交集
169    ///
170    /// * table 链表表名
171    /// * main_fields 主表字段
172    /// * second_fields 附表关联字段
173    fn join_inner(&mut self, table: &str, main_fields: &str, second_fields: &str) -> &mut Self;
174}
175
176#[derive(Clone, Debug)]
177pub struct Params {
178    pub mode: String,
179    pub autoinc: bool,
180    pub table: String,
181    pub where_and: Vec<String>,
182    pub where_or: Vec<String>,
183    pub where_column: String,
184    pub inc_dec: JsonValue,
185    pub page: i32,
186    pub limit: i32,
187    pub fields: JsonValue,
188    pub top: String,
189    pub top2: String,
190    pub order: JsonValue,
191    pub group: JsonValue,
192    pub distinct: bool,
193    pub json: JsonValue,
194    pub location: JsonValue,
195    pub sql: bool,
196    pub join: Vec<String>,
197    pub join_inner: Vec<String>,
198    pub join_table: String,
199}
200
201impl Params {
202    pub fn default(mode: &str) -> Self {
203        Self {
204            mode: mode.to_string(),
205            autoinc: false,
206            table: "".to_string(),
207            where_and: vec![],
208            where_or: vec![],
209            where_column: "".to_string(),
210            inc_dec: object! {},
211            page: -1,
212            limit: 10,
213            fields: object! {},
214            top: String::new(),
215            top2: String::new(),
216            order: object! {},
217            group: object! {},
218            distinct: false,
219            json: object! {},
220            location: object! {},
221            sql: false,
222            join: Vec::new(),
223            join_inner: Vec::new(),
224            join_table: "".to_string(),
225        }
226    }
227    pub fn where_sql(&mut self) -> String {
228        let mut where_and_sql = vec![];
229        let mut where_or_sql = vec![];
230        let mut sql = vec![];
231
232        for item in self.where_or.iter() {
233            where_or_sql.push(item.clone());
234        }
235        if !where_or_sql.is_empty() {
236            sql.push(format!(" ( {} ) ", where_or_sql.join(" OR ")));
237        }
238
239        for item in self.where_and.iter() {
240            where_and_sql.push(item.clone());
241        }
242        if !where_and_sql.is_empty() {
243            sql.push(where_and_sql.join(" AND "));
244        }
245
246        if !self.where_column.is_empty() {
247            sql.push(self.where_column.clone());
248        }
249
250        if !sql.is_empty() {
251            return format!("WHERE {}", sql.join(" AND "));
252        }
253        "".to_string()
254    }
255    pub fn page_limit_sql(&mut self) -> String {
256        if self.page == -1 {
257            return "".to_string();
258        }
259        match self.mode.as_str() {
260            "mysql" => {
261                format!("LIMIT {},{}", self.page * self.limit - self.limit, self.limit)
262            }
263            "sqlite" => {
264                format!("LIMIT {} OFFSET {}", self.limit, self.page * self.limit - self.limit)
265            }
266            _ => "".to_string()
267        }
268    }
269    pub fn fields(&mut self) -> String {
270        let mut fields = vec![];
271        for (_, value) in self.fields.entries() {
272            match self.mode.as_str() {
273                "mssql" => {
274                    fields.push(format!("{value}"));
275                }
276                "mysql" => {
277                    if DISABLE_FIELD.contains(&value.as_str().unwrap()) {
278                        fields.push(format!("`{value}`"));
279                    } else {
280                        fields.push(format!("{value}"));
281                    }
282                }
283                _ => {
284                    fields.push(format!("{value}"));
285                }
286            }
287        }
288        let fields = {
289            if fields.is_empty() {
290                "*".into()
291            } else {
292                fields.join(",")
293            }
294        };
295        match self.mode.as_str() {
296            "mysql" => {
297                fields.to_string()
298            }
299            "sqlite" => {
300                fields.to_string()
301            }
302            "mssql" => {
303                fields.to_string()
304            }
305            _ => fields.to_string()
306        }
307    }
308    pub fn top(&mut self) -> String {
309        match self.mode.as_str() {
310            "mssql" => {
311                let wheres = self.where_sql();
312                if !self.top2.is_empty() {
313                    let order = self.order();
314                    if order.is_empty() {
315                        self.top = format!("(select ROW_NUMBER() OVER(ORDER BY rand()) as ROW,* from {} {}) as ", self.table, wheres);
316                    } else {
317                        self.top = format!("(select ROW_NUMBER() OVER({}) as ROW,* from {} {}) as ", order, self.table, wheres);
318                    }
319                    return self.top.to_string();
320                }
321                self.top.to_string()
322            }
323            _ => {
324                "".to_string()
325            }
326        }
327    }
328    pub fn top2(&mut self) -> String {
329        match self.mode.as_str() {
330            "mssql" => {
331                if self.where_and.is_empty() && self.where_or.is_empty() && !self.top2.is_empty() {
332                    return format!("where {}", self.top2);
333                }
334                if (!self.where_and.is_empty() || !self.where_or.is_empty()) && !self.top2.is_empty() {
335                    return format!("AND {}", self.top2);
336                }
337                self.top2.to_string()
338            }
339            _ => {
340                "".to_string()
341            }
342        }
343    }
344    pub fn table(&mut self) -> String {
345        match self.mode.as_str() {
346            "mssql" => {
347                if !self.top2.is_empty() {
348                    return "t".to_string();
349                }
350                self.table.to_string()
351            }
352            _ => {
353                self.table.to_string()
354            }
355        }
356    }
357    pub fn join(&mut self) -> String {
358        match self.mode.as_str() {
359            "mssql" => {
360                self.join.join(" ")
361            }
362            _ => {
363                self.join.join(" ")
364            }
365        }
366    }
367
368    // 当前只匹配了MySQL
369    pub fn join_inner(&mut self) -> String {
370        match self.mode.as_str() {
371            "mysql" => {
372                let mut join_inner = "".to_string();
373                for item in self.join_inner.iter() {
374                    join_inner = format!("{join_inner} {item}");
375                }
376                join_inner.to_string()
377            }
378            _ => {
379                "".to_string()
380            }
381        }
382    }
383
384
385    pub fn order(&mut self) -> String {
386        let mut sql = vec![];
387        for (field, item) in self.order.entries() {
388            match self.mode.as_str() {
389                "mssql" => {
390                    sql.push(format!("{field} {item}"));
391                }
392                "pgsql" => {
393                    sql.push(format!("{field} {item}"));
394                }
395                _ => {
396                    sql.push(format!("{field} {item}"));
397                }
398            }
399        }
400        if !sql.is_empty() {
401            return format!("ORDER BY {}", sql.join(","));
402        }
403        "".to_string()
404    }
405    pub fn group(&mut self) -> String {
406        let mut sql = vec![];
407        for (_, field) in self.group.entries() {
408            match self.mode.as_str() {
409                "mssql" => {
410                    if DISABLE_FIELD.contains(&&*field.clone().to_string()) {
411                        sql.push(format!("`{field}`"));
412                    } else {
413                        sql.push(format!("{}.{}", self.join_table, field));
414                    }
415                }
416                "pgsql" => {
417                    sql.push(format!("{}.{}", self.join_table, field));
418                }
419                _ => {
420                    sql.push(format!("{}.`{}`", self.join_table, field));
421                }
422            }
423        }
424        if !sql.is_empty() {
425            return format!("GROUP BY {}", sql.join(","));
426        }
427        "".to_string()
428    }
429    pub fn distinct(&self) -> String {
430        if self.distinct {
431            "DISTINCT".to_string()
432        } else {
433            "".to_string()
434        }
435    }
436    pub fn select_sql(&mut self) -> String {
437        format!("SELECT {} {} FROM {} {} {} {} {} {} {} {} {}", self.distinct(), self.fields(), self.top(), self.table(), self.join(), self.join_inner(), self.where_sql(), self.top2(), self.group(), self.order(), self.page_limit_sql())
438    }
439}
br_db/types/mod.rs

br_db/types/
mod.rs
