br_db/types/
mssql.rs

1use json::{array, JsonValue, object, Null};
2use log::{error, info, warn};
3use tiberius::{AuthMethod, Client, ColumnType, Config, EncryptionLevel, Query};
4use tiberius::numeric::Numeric;
5use crate::Connection;
6use async_std::net::TcpStream;
7use tiberius::time::time::PrimitiveDateTime;
8use crate::types::{DbMode, Mode, Params, TableOptions};
9
10lazy_static::lazy_static! {
11    static ref MSSQL_RUNTIME: tokio::runtime::Runtime = {
12        tokio::runtime::Builder::new_multi_thread()
13            .worker_threads(4)
14            .enable_all()
15            .build()
16            .expect("Failed to create MSSQL tokio runtime")
17    };
18}
19
20#[derive(Clone, Debug)]
21pub struct Mssql {
22    /// 当前连接配置
23    pub connection: Connection,
24    /// 当前选中配置
25    pub default: String,
26
27    pub params: Params,
28}
29
30impl Mssql {
31    pub fn connect(connection: Connection, default: String) -> Result<Self, String> {
32        Ok(Self {
33            connection,
34            default,
35            params: Params::default("mssql"),
36        })
37    }
38
39    async fn create_client(config: &Connection) -> Result<Client<TcpStream>, String> {
40        let mut conf = Config::new();
41        conf.host(&config.hostname);
42        let port = config.hostport.parse::<u16>().unwrap_or(1433);
43        conf.port(port);
44        conf.database(&config.database);
45        conf.authentication(AuthMethod::sql_server(&config.username, &config.userpass));
46        conf.encryption(EncryptionLevel::NotSupported);
47
48        let tcp = TcpStream::connect(conf.get_addr())
49            .await
50            .map_err(|e| format!("TCP connect failed: {e}"))?;
51        
52        if let Err(e) = tcp.set_nodelay(true) {
53            warn!("Failed to set TCP nodelay: {e}");
54        }
55
56        Client::connect(conf, tcp)
57            .await
58            .map_err(|e| format!("Client connect failed: {e}"))
59    }
60
61    async fn execute_query(config: Connection, sql: String) -> (bool, JsonValue) {
62        let mut client = match Self::create_client(&config).await {
63            Ok(c) => c,
64            Err(e) => {
65                error!("MSSQL connection failed: {e}");
66                return (false, array![]);
67            }
68        };
69
70        let select = Query::new(sql.clone());
71        let stream = match select.query(&mut client).await {
72            Ok(e) => e,
73            Err(e) => {
74                error!("MSSQL query failed: {e}");
75                return (false, array![]);
76            }
77        };
78
79        let rows = match stream.into_results().await {
80            Ok(e) => e,
81            Err(e) => {
82                error!("MSSQL results failed: {e}");
83                return (false, array![]);
84            }
85        };
86
87        if rows.is_empty() || rows[0].is_empty() {
88            return (true, array![]);
89        }
90
91        let cols = match rows[0].first() {
92            Some(row) => row.columns(),
93            None => return (true, array![]),
94        };
95
96        let mut fields = object! {};
97        for col in cols.iter() {
98            fields[col.name()] = match col.column_type() {
99                ColumnType::Image | ColumnType::Datetimen | ColumnType::Datetime | ColumnType::Text | ColumnType::NVarchar | ColumnType::NText | ColumnType::BigChar | ColumnType::BigVarChar => "string",
100                ColumnType::Int2 | ColumnType::Int1 | ColumnType::Int4 | ColumnType::Int8 => "i64",
101                ColumnType::Intn => "i32",
102                ColumnType::Numericn | ColumnType::Money => "f64",
103                _ => {
104                    info!("未知: {} : {:?}", col.name(), col.column_type());
105                    "string"
106                }
107            }.into()
108        }
109
110        let mut list = array![];
111        for row in rows[0].iter() {
112            let mut row_data = object! {};
113            for column in row.columns() {
114                let field = column.name();
115                row_data[field] = Self::extract_column_value(row, field, column.column_type());
116            }
117            let _ = list.push(row_data);
118        }
119        (true, list)
120    }
121
122    fn extract_column_value(row: &tiberius::Row, field: &str, col_type: ColumnType) -> JsonValue {
123        match col_type {
124            ColumnType::BigVarChar | ColumnType::BigChar | ColumnType::Text | ColumnType::NText | ColumnType::NVarchar => {
125                match row.try_get::<&str, _>(field) {
126                    Ok(Some(e)) => e.into(),
127                    Ok(None) => Null,
128                    Err(e) => {
129                        error!("String column {field}: {e}");
130                        "".into()
131                    }
132                }
133            }
134            ColumnType::Image => {
135                match row.try_get::<&[u8], _>(field) {
136                    Ok(Some(e)) => e.into(),
137                    Ok(None) => Null,
138                    Err(e) => {
139                        error!("Image column {field}: {e}");
140                        "".into()
141                    }
142                }
143            }
144            ColumnType::Datetimen | ColumnType::Datetime => {
145                match row.try_get::<PrimitiveDateTime, _>(field) {
146                    Ok(Some(e)) => e.to_string().into(),
147                    Ok(None) => Null,
148                    Err(e) => {
149                        error!("Datetime column {field}: {e}");
150                        "".into()
151                    }
152                }
153            }
154            ColumnType::Intn | ColumnType::Int1 | ColumnType::Int2 | ColumnType::Int4 | ColumnType::Int8 => {
155                Self::extract_int_value(row, field)
156            }
157            ColumnType::Numericn | ColumnType::Money => {
158                Self::extract_numeric_value(row, field)
159            }
160            _ => {
161                error!("Unknown column type: {:?}", col_type);
162                "".into()
163            }
164        }
165    }
166
167    fn extract_int_value(row: &tiberius::Row, field: &str) -> JsonValue {
168        if let Ok(Some(v)) = row.try_get::<i32, _>(field) {
169            return v.into();
170        }
171        if let Ok(Some(v)) = row.try_get::<i16, _>(field) {
172            return v.into();
173        }
174        if let Ok(Some(v)) = row.try_get::<u8, _>(field) {
175            return v.into();
176        }
177        if let Ok(Some(v)) = row.try_get::<i64, _>(field) {
178            return v.into();
179        }
180        if let Ok(None) = row.try_get::<i32, _>(field) {
181            return Null;
182        }
183        0.into()
184    }
185
186    fn extract_numeric_value(row: &tiberius::Row, field: &str) -> JsonValue {
187        if let Ok(Some(v)) = row.try_get::<f64, _>(field) {
188            return v.into();
189        }
190        if let Ok(Some(v)) = row.try_get::<Numeric, _>(field) {
191            return v.to_string().parse::<f64>().unwrap_or(0.0).into();
192        }
193        if let Ok(None) = row.try_get::<f64, _>(field) {
194            return Null;
195        }
196        0.0.into()
197    }
198
199    fn query(&mut self, sql: String) -> (bool, JsonValue) {
200        if self.connection.debug {
201            info!("sql: {sql}");
202        }
203        MSSQL_RUNTIME.block_on(Self::execute_query(self.connection.clone(), sql))
204    }
205}
206
207impl DbMode for Mssql {
208    fn database_tables(&mut self) -> JsonValue {
209        let sql = "SELECT table_name FROM INFORMATION_SCHEMA.TABLES".to_string();
210        match self.sql(sql.as_str()) {
211            Ok(e) => {
212                let mut list = vec![];
213                for item in e.members() {
214                    list.push(item["table_name"].clone());
215                }
216                list.into()
217            }
218            Err(_) => {
219                array![]
220            }
221        }
222    }
223    fn database_create(&mut self, _name: &str) -> bool {
224        todo!()
225    }
226}
227
228impl Mode for Mssql {
229    fn table_create(&mut self, _options: TableOptions) -> JsonValue {
230        todo!()
231    }
232
233    fn table_update(&mut self, _options: TableOptions) -> JsonValue {
234        todo!()
235    }
236
237
238    fn table_info(&mut self, _table: &str) -> JsonValue {
239        todo!()
240    }
241    fn table_is_exist(&mut self, name: &str) -> bool {
242        let sql = format!("SELECT count(*) as count FROM sqlite_master WHERE type='table' AND name='{name}'");
243        let (state, data) = self.query(sql);
244        if state {
245            if data[0]["count"].as_i64().unwrap_or(0) > 0 {
246                return true;
247            }
248            false
249        } else {
250            false
251        }
252    }
253    fn table(&mut self, name: &str) -> &mut Self {
254        self.params = Params::default(self.connection.mode.str().as_str());
255        let table_name = format!("{}{}", self.connection.prefix, name);
256        if !super::sql_safety::validate_table_name(&table_name) {
257            error!("Invalid table name: {}", name);
258        }
259        self.params.table = table_name.clone();
260        self.params.join_table = table_name;
261        self
262    }
263
264    fn change_table(&mut self, name: &str) -> &mut Self {
265        self.params.join_table = name.to_string();
266        self
267    }
268
269    fn autoinc(&mut self) -> &mut Self {
270        self.params.autoinc = true;
271        self
272    }
273
274    fn fetch_sql(&mut self) -> &mut Self {
275        self.params.sql = true;
276        self
277    }
278
279    fn order(&mut self, field: &str, by: bool) -> &mut Self {
280        self.params.order[field] = {
281            if by {
282                "DESC"
283            } else {
284                "ASC"
285            }
286        }.into();
287        self
288    }
289
290    fn group(&mut self, field: &str) -> &mut Self {
291        let fields: Vec<&str> = field.split(",").collect();
292        for field in fields.iter() {
293            let field = field.to_string();
294            self.params.group[field.as_str()] = field.clone().into();
295            self.params.fields[field.as_str()] = field.clone().into();
296        }
297        self
298    }
299
300    fn distinct(&mut self) -> &mut Self {
301        self.params.distinct = true;
302        self
303    }
304
305    fn json(&mut self, field: &str) -> &mut Self {
306        self.params.json[field] = field.into();
307        self
308    }
309
310    fn column(&mut self, field: &str) -> JsonValue {
311        self.field(field);
312        self.group(field);
313        let sql = self.params.select_sql();
314        let (state, data) = self.query(sql);
315        if state {
316            let mut list = array![];
317            for item in data.members() {
318                let _ = list.push(item[field].clone());
319            }
320            list
321        } else {
322            array![]
323        }
324    }
325
326    fn where_and(&mut self, field: &str, compare: &str, value: JsonValue) -> &mut Self {
327        match compare {
328            "is" => {
329                self.params.where_and.push(format!("{field} is {value}"));
330            }
331            "between" => {
332                self.params.where_and.push(format!("{} between '{}' AND '{}'", field, value[0], value[1]));
333            }
334            "notin" => {
335                let mut text = String::new();
336                for item in value.members() {
337                    text = format!("{text},'{item}'");
338                }
339                text = text.trim_start_matches(",").into();
340                self.params.where_and.push(format!("{field} not in ({text})"));
341            }
342            "in" => {
343                let mut text = String::new();
344                for item in value.members() {
345                    text = format!("{text},'{item}'");
346                }
347                text = text.trim_start_matches(",").into();
348                self.params.where_and.push(format!("{field} {compare} ({text})"));
349            }
350            _ => {
351                self.params.where_and.push(format!("{field} {compare} '{value}'"));
352            }
353        }
354        self
355    }
356
357    fn where_or(&mut self, field: &str, compare: &str, value: JsonValue) -> &mut Self {
358        match compare {
359            "between" => {
360                self.params.where_or.push(format!("{} between '{}' AND '{}'", field, value[0], value[1]));
361            }
362            "notin" => {
363                let mut text = String::new();
364                for item in value.members() {
365                    text = format!("{text},'{item}'");
366                }
367                text = text.trim_start_matches(",").into();
368                self.params.where_or.push(format!("{field} not in ({text})"));
369            }
370            "in" => {
371                let mut text = String::new();
372                for item in value.members() {
373                    text = format!("{text},'{item}'");
374                }
375                text = text.trim_start_matches(",").into();
376                self.params.where_or.push(format!("{field} {compare} ({text})"));
377            }
378            _ => {
379                self.params.where_or.push(format!("{field} {compare} '{value}'"));
380            }
381        }
382        self
383    }
384
385    fn where_column(&mut self, field_a: &str, compare: &str, field_b: &str) -> &mut Self {
386        self.params.where_column = format!("{}.`{}` {} {}.`{}`", self.params.table, field_a, compare, self.params.table, field_b);
387        self
388    }
389
390    fn count(&mut self) -> JsonValue {
391        self.params.fields = object! {};
392        self.params.order = object! {};
393        self.params.fields["count"] = "count(*) as count".into();
394        let sql = self.params.select_sql();
395        if self.params.sql {
396            return JsonValue::from(sql.clone());
397        }
398        let (state, data) = self.query(sql);
399        if state {
400            data[0]["count"].clone()
401        } else {
402            JsonValue::from(0)
403        }
404    }
405
406    fn max(&mut self, field: &str) -> JsonValue {
407        self.params.fields[field] = format!("max({field}) as {field}").into();
408        let sql = self.params.select_sql();
409        let (state, data) = self.query(sql);
410        if state {
411            if data.len() > 1 {
412                return data;
413            }
414            data[0][field].clone()
415        } else {
416            array![]
417        }
418    }
419
420    fn min(&mut self, field: &str) -> JsonValue {
421        self.params.fields[field] = format!("min({field}) as {field}").into();
422        let sql = self.params.select_sql();
423        let (state, data) = self.query(sql);
424        if state {
425            if data.len() > 1 {
426                return data;
427            }
428            data[0][field].clone()
429        } else {
430            array![]
431        }
432    }
433
434    fn sum(&mut self, field: &str) -> JsonValue {
435        self.params.fields[field] = format!("sum({field}) as {field}").into();
436        let sql = self.params.select_sql();
437        let (state, data) = self.query(sql);
438        if state {
439            if data.len() > 1 {
440                return data;
441            }
442            data[0][field].clone()
443        } else {
444            array![]
445        }
446    }
447
448    fn avg(&mut self, field: &str) -> JsonValue {
449        self.params.fields[field] = format!("avg({field}) as {field}").into();
450        let sql = self.params.select_sql();
451        let (state, data) = self.query(sql);
452        if state {
453            if data.len() > 1 {
454                return data;
455            }
456            data[0][field].clone()
457        } else {
458            array![]
459        }
460    }
461
462    fn select(&mut self) -> JsonValue {
463        let sql = self.params.select_sql();
464        if self.params.sql {
465            return JsonValue::from(sql.clone());
466        }
467        let (state, data) = self.query(sql.clone());
468        if state {
469            data.clone()
470        } else {
471            if self.connection.debug {
472                info!("{data:?}");
473            }
474            array![]
475        }
476    }
477
478    fn find(&mut self) -> JsonValue {
479        self.page(1, 1);
480        let sql = self.params.select_sql();
481        if self.params.sql {
482            return JsonValue::from(sql.clone());
483        }
484        let (state, data) = self.query(sql.clone());
485        if state {
486            data[0].clone()
487        } else {
488            if self.connection.debug {
489                info!("{data:#?}");
490            }
491            object! {}
492        }
493    }
494
495    fn value(&mut self, field: &str) -> JsonValue {
496        self.params.fields = object! {};
497        self.params.fields[field] = field.into();
498        self.params.page = 1;
499        self.params.limit = 1;
500        let sql = self.params.select_sql();
501        if self.params.sql {
502            return JsonValue::from(sql.clone());
503        }
504        let (state, data) = self.query(sql.clone());
505        match state {
506            true => {
507                data[0][field].clone()
508            }
509            false => {
510                if self.connection.debug {
511                    println!("{data:?}");
512                }
513                Null
514            }
515        }
516    }
517
518    fn insert(&mut self, _data: JsonValue) -> JsonValue {
519        todo!()
520        // let mut fields = String::new();
521        // let mut values = String::new();
522        // if !self.params.autoinc && data["id"].is_empty() {
523        //     data["id"] = JsonValue::from(Local::now().timestamp_nanos())
524        // }
525        // for (field, value) in data.entries() {
526        //     fields = format!("{},{}", fields, field);
527        //     values = format!("{},\"{}\"", values, value);
528        // }
529        // fields = fields.trim_start_matches(",").parse().unwrap();
530        // values = values.trim_start_matches(",").parse().unwrap();
531        //
532        // let sql = format!("INSERT INTO {} ({}) VALUES ({});", self.params.table, fields, values);
533        // let (state, ids) = self.execute(sql);
534        // if state {
535        //     return data["id"].clone();
536        // } else {
537        //     if self.connection.debug {
538        //         info!("{:?}", ids);
539        //     }
540        //     return JsonValue::from("");
541        // }
542    }
543    fn insert_all(&mut self, _data: JsonValue) -> JsonValue {
544        todo!()
545    }
546    fn page(&mut self, page: i32, limit: i32) -> &mut Self {
547        self.params.page = page;
548        self.params.limit = limit;
549        // self.params.top = format!("(select ROW_NUMBER() OVER(order by rand()) as ROW,* from {}) as ", self.params.table);
550        // self.params.top2 = format!("t.ROW between {} and {}", (page - 1) * limit + 1, page * limit);
551        self.params.top2 = format!("t.ROW between {} and {}", (page - 1) * limit + 1, page * limit);
552        self
553    }
554
555    fn update(&mut self, _data: JsonValue) -> JsonValue {
556        todo!()
557    }
558
559    fn update_all(&mut self, _data: JsonValue) -> JsonValue {
560        todo!()
561    }
562
563    fn delete(&mut self) -> JsonValue {
564        todo!()
565    }
566
567    fn field(&mut self, field: &str) -> &mut Self {
568        let list: Vec<&str> = field.split(",").collect();
569        for item in list.iter() {
570            self.params.fields[item.to_string().as_str()] = item.to_string().into();
571        }
572        self
573    }
574
575    fn hidden(&mut self, name: &str) -> &mut Self {
576        let hidden: Vec<&str> = name.split(",").collect();
577        let sql = format!("PRAGMA table_info({})", self.params.table);
578        let (_, data) = self.query(sql);
579        for item in data.members() {
580            if let Some(name) = item["name"].as_str() {
581                if !hidden.contains(&name) {
582                    self.params.fields[name] = name.into();
583                }
584            }
585        }
586        self
587    }
588
589    fn transaction(&mut self) -> bool {
590        todo!()
591    }
592
593
594    fn commit(&mut self) -> bool {
595        todo!()
596    }
597
598    fn rollback(&mut self) -> bool {
599        todo!()
600    }
601
602    fn sql(&mut self, sql: &str) -> Result<JsonValue, String> {
603        let (state, data) = self.query(sql.to_string());
604        match state {
605            true => Ok(data),
606            false => Err("".to_string()),
607        }
608    }
609    fn sql_execute(&mut self, _sql: &str) -> Result<JsonValue, String> {
610        todo!()
611    }
612
613    fn inc(&mut self, _field: &str, _num: f64) -> &mut Self {
614        todo!()
615    }
616
617    fn dec(&mut self, _field: &str, _num: f64) -> &mut Self {
618        todo!()
619    }
620
621    fn buildsql(&mut self) -> String {
622        self.fetch_sql();
623        let sql = self.select().to_string();
624        format!("( {} ) {}", sql, self.params.table)
625    }
626    
627    
628
629    fn join(&mut self, main_table: &str, main_fields: &str, right_table: &str, right_fields: &str) -> &mut Self {
630        let main_table = if main_table.is_empty() {
631            self.params.table.clone()
632        } else {
633            main_table.to_string()
634        };
635        self.params.join_table = right_table.to_string();
636        self.params.join.push(format!(" LEFT JOIN {right_table} ON {main_table}.{main_fields} = {right_table}.{right_fields} "));
637        self
638    }
639
640    fn join_inner(&mut self, table: &str, main_fields: &str, second_fields: &str) -> &mut Self {
641        let main_fields = if main_fields.is_empty() { "id" } else { main_fields };
642        let second_fields = if second_fields.is_empty() { self.params.table.clone() } else { second_fields.to_string().clone() };
643        let sec_table_name = format!("{}{}", table, "_2");
644        let second_table = format!("{} {}", table, sec_table_name.clone());
645        self.params.join_table = sec_table_name.clone();
646        self.params.join.push(format!(" INNER JOIN {} ON {}.{} = {}.{}", second_table, self.params.table, main_fields, sec_table_name, second_fields));
647        self
648    }
649
650    fn location(&mut self, _field: &str) -> &mut Self {
651        todo!()
652    }
653
654    fn join_fields(&mut self, fields: Vec<&str>) -> &mut Self {
655        for field in fields {
656            self.params.fields[field] = format!("{field} as {}", field.replace(".", "_")).into();
657        }
658        self
659    }
660
661    fn update_column(&mut self, field_a: &str, compare: &str) -> &mut Self {
662        self.params.update_column.push(format!("{field_a} = {compare}"));
663        self
664    }
665}
br_db/types/mssql.rs

br_db/types/
mssql.rs
