br_db/types/
mssql.rs

1use crate::types::{DbMode, Mode, Params, TableOptions};
2use crate::Connection;
3use async_std::net::TcpStream;
4use chrono::Local;
5use json::{array, object, JsonValue, Null};
6use log::{error, info, warn};
7use std::thread;
8use tiberius::numeric::Numeric;
9use tiberius::time::time::PrimitiveDateTime;
10use tiberius::{AuthMethod, Client, ColumnType, Config, EncryptionLevel, Query};
11
12lazy_static::lazy_static! {
13    static ref MSSQL_RUNTIME: tokio::runtime::Runtime = {
14        tokio::runtime::Builder::new_multi_thread()
15            .worker_threads(4)
16            .enable_all()
17            .build()
18            .expect("Failed to create MSSQL tokio runtime")
19    };
20}
21
22#[derive(Clone, Debug)]
23pub struct Mssql {
24    /// 当前连接配置
25    pub connection: Connection,
26    /// 当前选中配置
27    pub default: String,
28
29    pub params: Params,
30}
31
32impl Mssql {
33    pub fn connect(connection: Connection, default: String) -> Result<Self, String> {
34        Ok(Self {
35            connection,
36            default,
37            params: Params::default("mssql"),
38        })
39    }
40
41    async fn create_client(config: &Connection) -> Result<Client<TcpStream>, String> {
42        let mut conf = Config::new();
43        conf.host(&config.hostname);
44        let port = config.hostport.parse::<u16>().unwrap_or(1433);
45        conf.port(port);
46        conf.database(&config.database);
47        conf.authentication(AuthMethod::sql_server(&config.username, &config.userpass));
48        conf.encryption(EncryptionLevel::NotSupported);
49
50        let tcp = TcpStream::connect(conf.get_addr())
51            .await
52            .map_err(|e| format!("TCP connect failed: {e}"))?;
53
54        if let Err(e) = tcp.set_nodelay(true) {
55            warn!("Failed to set TCP nodelay: {e}");
56        }
57
58        Client::connect(conf, tcp)
59            .await
60            .map_err(|e| format!("Client connect failed: {e}"))
61    }
62
63    async fn execute_query(config: Connection, sql: String) -> (bool, JsonValue) {
64        let mut client = match Self::create_client(&config).await {
65            Ok(c) => c,
66            Err(e) => {
67                error!("MSSQL connection failed: {e}");
68                return (false, array![]);
69            }
70        };
71
72        let select = Query::new(sql.clone());
73        let stream = match select.query(&mut client).await {
74            Ok(e) => e,
75            Err(e) => {
76                error!("MSSQL query failed: {e}");
77                return (false, array![]);
78            }
79        };
80
81        let rows = match stream.into_results().await {
82            Ok(e) => e,
83            Err(e) => {
84                error!("MSSQL results failed: {e}");
85                return (false, array![]);
86            }
87        };
88
89        if rows.is_empty() || rows[0].is_empty() {
90            return (true, array![]);
91        }
92
93        let cols = match rows[0].first() {
94            Some(row) => row.columns(),
95            None => return (true, array![]),
96        };
97
98        let mut fields = object! {};
99        for col in cols.iter() {
100            fields[col.name()] = match col.column_type() {
101                ColumnType::Image
102                | ColumnType::Datetimen
103                | ColumnType::Datetime
104                | ColumnType::Text
105                | ColumnType::NVarchar
106                | ColumnType::NText
107                | ColumnType::BigChar
108                | ColumnType::BigVarChar => "string",
109                ColumnType::Int2 | ColumnType::Int1 | ColumnType::Int4 | ColumnType::Int8 => "i64",
110                ColumnType::Intn => "i32",
111                ColumnType::Numericn | ColumnType::Money => "f64",
112                _ => {
113                    info!("未知: {} : {:?}", col.name(), col.column_type());
114                    "string"
115                }
116            }
117            .into()
118        }
119
120        let mut list = array![];
121        for row in rows[0].iter() {
122            let mut row_data = object! {};
123            for column in row.columns() {
124                let field = column.name();
125                row_data[field] = Self::extract_column_value(row, field, column.column_type());
126            }
127            let _ = list.push(row_data);
128        }
129        (true, list)
130    }
131
132    fn extract_column_value(row: &tiberius::Row, field: &str, col_type: ColumnType) -> JsonValue {
133        match col_type {
134            ColumnType::BigVarChar
135            | ColumnType::BigChar
136            | ColumnType::Text
137            | ColumnType::NText
138            | ColumnType::NVarchar => match row.try_get::<&str, _>(field) {
139                Ok(Some(e)) => e.into(),
140                Ok(None) => Null,
141                Err(e) => {
142                    error!("String column {field}: {e}");
143                    "".into()
144                }
145            },
146            ColumnType::Image => match row.try_get::<&[u8], _>(field) {
147                Ok(Some(e)) => e.into(),
148                Ok(None) => Null,
149                Err(e) => {
150                    error!("Image column {field}: {e}");
151                    "".into()
152                }
153            },
154            ColumnType::Datetimen | ColumnType::Datetime => {
155                match row.try_get::<PrimitiveDateTime, _>(field) {
156                    Ok(Some(e)) => e.to_string().into(),
157                    Ok(None) => Null,
158                    Err(e) => {
159                        error!("Datetime column {field}: {e}");
160                        "".into()
161                    }
162                }
163            }
164            ColumnType::Intn
165            | ColumnType::Int1
166            | ColumnType::Int2
167            | ColumnType::Int4
168            | ColumnType::Int8 => Self::extract_int_value(row, field),
169            ColumnType::Numericn | ColumnType::Money => Self::extract_numeric_value(row, field),
170            _ => {
171                error!("Unknown column type: {:?}", col_type);
172                "".into()
173            }
174        }
175    }
176
177    fn extract_int_value(row: &tiberius::Row, field: &str) -> JsonValue {
178        if let Ok(Some(v)) = row.try_get::<i32, _>(field) {
179            return v.into();
180        }
181        if let Ok(Some(v)) = row.try_get::<i16, _>(field) {
182            return v.into();
183        }
184        if let Ok(Some(v)) = row.try_get::<u8, _>(field) {
185            return v.into();
186        }
187        if let Ok(Some(v)) = row.try_get::<i64, _>(field) {
188            return v.into();
189        }
190        if let Ok(None) = row.try_get::<i32, _>(field) {
191            return Null;
192        }
193        0.into()
194    }
195
196    fn extract_numeric_value(row: &tiberius::Row, field: &str) -> JsonValue {
197        if let Ok(Some(v)) = row.try_get::<f64, _>(field) {
198            return v.into();
199        }
200        if let Ok(Some(v)) = row.try_get::<Numeric, _>(field) {
201            return v.to_string().parse::<f64>().unwrap_or(0.0).into();
202        }
203        if let Ok(None) = row.try_get::<f64, _>(field) {
204            return Null;
205        }
206        0.0.into()
207    }
208
209    fn query(&mut self, sql: String) -> (bool, JsonValue) {
210        if self.connection.debug {
211            info!("sql: {sql}");
212        }
213        MSSQL_RUNTIME.block_on(Self::execute_query(self.connection.clone(), sql))
214    }
215
216    async fn execute_sql(config: Connection, sql: String) -> (bool, JsonValue) {
217        let mut client = match Self::create_client(&config).await {
218            Ok(c) => c,
219            Err(e) => {
220                error!("MSSQL connection failed: {e}");
221                return (false, JsonValue::from(e));
222            }
223        };
224
225        let result = client.execute(sql.clone(), &[]).await;
226        match result {
227            Ok(r) => {
228                let rows = r.total();
229                (true, JsonValue::from(rows))
230            }
231            Err(e) => {
232                error!("MSSQL execute failed: {e}");
233                (false, JsonValue::from(format!("{e}")))
234            }
235        }
236    }
237
238    fn execute(&mut self, sql: &str) -> (bool, JsonValue) {
239        if self.connection.debug {
240            info!("sql: {sql}");
241        }
242        MSSQL_RUNTIME.block_on(Self::execute_sql(self.connection.clone(), sql.to_string()))
243    }
244}
245
246impl DbMode for Mssql {
247    fn database_tables(&mut self) -> JsonValue {
248        let sql = "SELECT table_name FROM INFORMATION_SCHEMA.TABLES".to_string();
249        match self.sql(sql.as_str()) {
250            Ok(e) => {
251                let mut list = vec![];
252                for item in e.members() {
253                    list.push(item["table_name"].clone());
254                }
255                list.into()
256            }
257            Err(_) => {
258                array![]
259            }
260        }
261    }
262    fn database_create(&mut self, name: &str) -> bool {
263        let sql = format!("CREATE DATABASE [{name}]");
264        let (state, _) = self.execute(sql.as_str());
265        state
266    }
267
268    fn truncate(&mut self, table: &str) -> bool {
269        let sql = format!("TRUNCATE TABLE [{table}]");
270        let (state, _) = self.execute(sql.as_str());
271        state
272    }
273}
274
275impl Mode for Mssql {
276    fn table_create(&mut self, _options: TableOptions) -> JsonValue {
277        todo!()
278    }
279
280    fn table_update(&mut self, _options: TableOptions) -> JsonValue {
281        todo!()
282    }
283
284    fn table_info(&mut self, table: &str) -> JsonValue {
285        let sql = format!("SELECT COLUMN_NAME as name, DATA_TYPE as type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{table}'");
286        let (state, data) = self.query(sql);
287        if state {
288            let mut result = object! {};
289            for item in data.members() {
290                let name = item["name"].to_string();
291                result[name.as_str()] = item.clone();
292            }
293            result
294        } else {
295            object! {}
296        }
297    }
298
299    fn table_is_exist(&mut self, name: &str) -> bool {
300        let sql = format!(
301            "SELECT COUNT(*) as count FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{name}'"
302        );
303        let (state, data) = self.query(sql);
304        if state {
305            data[0]["count"].as_i64().unwrap_or(0) > 0
306        } else {
307            false
308        }
309    }
310    fn table(&mut self, name: &str) -> &mut Self {
311        self.params = Params::default(self.connection.mode.str().as_str());
312        let table_name = format!("{}{}", self.connection.prefix, name);
313        if !super::sql_safety::validate_table_name(&table_name) {
314            error!("Invalid table name: {}", name);
315        }
316        self.params.table = table_name.clone();
317        self.params.join_table = table_name;
318        self
319    }
320
321    fn change_table(&mut self, name: &str) -> &mut Self {
322        self.params.join_table = name.to_string();
323        self
324    }
325
326    fn autoinc(&mut self) -> &mut Self {
327        self.params.autoinc = true;
328        self
329    }
330
331    fn timestamps(&mut self) -> &mut Self {
332        self.params.timestamps = true;
333        self
334    }
335
336    fn fetch_sql(&mut self) -> &mut Self {
337        self.params.sql = true;
338        self
339    }
340
341    fn order(&mut self, field: &str, by: bool) -> &mut Self {
342        self.params.order[field] = {
343            if by {
344                "DESC"
345            } else {
346                "ASC"
347            }
348        }
349        .into();
350        self
351    }
352
353    fn group(&mut self, field: &str) -> &mut Self {
354        let fields: Vec<&str> = field.split(",").collect();
355        for field in fields.iter() {
356            let field = field.to_string();
357            self.params.group[field.as_str()] = field.clone().into();
358            self.params.fields[field.as_str()] = field.clone().into();
359        }
360        self
361    }
362
363    fn distinct(&mut self) -> &mut Self {
364        self.params.distinct = true;
365        self
366    }
367
368    fn json(&mut self, field: &str) -> &mut Self {
369        self.params.json[field] = field.into();
370        self
371    }
372
373    fn column(&mut self, field: &str) -> JsonValue {
374        self.field(field);
375        self.group(field);
376        let sql = self.params.select_sql();
377        let (state, data) = self.query(sql);
378        if state {
379            let mut list = array![];
380            for item in data.members() {
381                let _ = list.push(item[field].clone());
382            }
383            list
384        } else {
385            array![]
386        }
387    }
388
389    fn where_and(&mut self, field: &str, compare: &str, value: JsonValue) -> &mut Self {
390        for f in field.split('|') {
391            if !super::sql_safety::validate_field_name(f) {
392                error!("Invalid field name: {}", f);
393            }
394        }
395        if !super::sql_safety::validate_compare_orator(compare) {
396            error!("Invalid compare operator: {}", compare);
397        }
398        match compare {
399            "is" => {
400                self.params.where_and.push(format!("{field} is {value}"));
401            }
402            "between" => {
403                self.params.where_and.push(format!(
404                    "{} between '{}' AND '{}'",
405                    field, value[0], value[1]
406                ));
407            }
408            "notin" => {
409                let mut text = String::new();
410                for item in value.members() {
411                    text = format!("{text},'{item}'");
412                }
413                text = text.trim_start_matches(",").into();
414                self.params
415                    .where_and
416                    .push(format!("{field} not in ({text})"));
417            }
418            "in" => {
419                let mut text = String::new();
420                for item in value.members() {
421                    text = format!("{text},'{item}'");
422                }
423                text = text.trim_start_matches(",").into();
424                self.params
425                    .where_and
426                    .push(format!("{field} {compare} ({text})"));
427            }
428            _ => {
429                self.params
430                    .where_and
431                    .push(format!("{field} {compare} '{value}'"));
432            }
433        }
434        self
435    }
436
437    fn where_or(&mut self, field: &str, compare: &str, value: JsonValue) -> &mut Self {
438        for f in field.split('|') {
439            if !super::sql_safety::validate_field_name(f) {
440                error!("Invalid field name: {}", f);
441            }
442        }
443        if !super::sql_safety::validate_compare_orator(compare) {
444            error!("Invalid compare operator: {}", compare);
445        }
446        match compare {
447            "between" => {
448                self.params.where_or.push(format!(
449                    "{} between '{}' AND '{}'",
450                    field, value[0], value[1]
451                ));
452            }
453            "notin" => {
454                let mut text = String::new();
455                for item in value.members() {
456                    text = format!("{text},'{item}'");
457                }
458                text = text.trim_start_matches(",").into();
459                self.params
460                    .where_or
461                    .push(format!("{field} not in ({text})"));
462            }
463            "in" => {
464                let mut text = String::new();
465                for item in value.members() {
466                    text = format!("{text},'{item}'");
467                }
468                text = text.trim_start_matches(",").into();
469                self.params
470                    .where_or
471                    .push(format!("{field} {compare} ({text})"));
472            }
473            _ => {
474                self.params
475                    .where_or
476                    .push(format!("{field} {compare} '{value}'"));
477            }
478        }
479        self
480    }
481
482    fn where_raw(&mut self, expr: &str) -> &mut Self {
483        self.params.where_and.push(expr.to_string());
484        self
485    }
486
487    fn where_in_sub(&mut self, field: &str, sub_sql: &str) -> &mut Self {
488        self.params
489            .where_and
490            .push(format!("[{field}] IN ({sub_sql})"));
491        self
492    }
493
494    fn where_not_in_sub(&mut self, field: &str, sub_sql: &str) -> &mut Self {
495        self.params
496            .where_and
497            .push(format!("[{field}] NOT IN ({sub_sql})"));
498        self
499    }
500
501    fn where_exists(&mut self, sub_sql: &str) -> &mut Self {
502        self.params.where_and.push(format!("EXISTS ({sub_sql})"));
503        self
504    }
505
506    fn where_not_exists(&mut self, sub_sql: &str) -> &mut Self {
507        self.params
508            .where_and
509            .push(format!("NOT EXISTS ({sub_sql})"));
510        self
511    }
512
513    fn where_column(&mut self, field_a: &str, compare: &str, field_b: &str) -> &mut Self {
514        self.params.where_column = format!(
515            "{}.`{}` {} {}.`{}`",
516            self.params.table, field_a, compare, self.params.table, field_b
517        );
518        self
519    }
520
521    fn count(&mut self) -> JsonValue {
522        self.params.fields = object! {};
523        self.params.order = object! {};
524        self.params.fields["count"] = "count(*) as count".into();
525        let sql = self.params.select_sql();
526        if self.params.sql {
527            return JsonValue::from(sql.clone());
528        }
529        let (state, data) = self.query(sql);
530        if state {
531            data[0]["count"].clone()
532        } else {
533            JsonValue::from(0)
534        }
535    }
536
537    fn max(&mut self, field: &str) -> JsonValue {
538        self.params.fields[field] = format!("max({field}) as {field}").into();
539        let sql = self.params.select_sql();
540        let (state, data) = self.query(sql);
541        if state {
542            if data.len() > 1 {
543                return data;
544            }
545            data[0][field].clone()
546        } else {
547            array![]
548        }
549    }
550
551    fn min(&mut self, field: &str) -> JsonValue {
552        self.params.fields[field] = format!("min({field}) as {field}").into();
553        let sql = self.params.select_sql();
554        let (state, data) = self.query(sql);
555        if state {
556            if data.len() > 1 {
557                return data;
558            }
559            data[0][field].clone()
560        } else {
561            array![]
562        }
563    }
564
565    fn sum(&mut self, field: &str) -> JsonValue {
566        self.params.fields[field] = format!("sum({field}) as {field}").into();
567        let sql = self.params.select_sql();
568        let (state, data) = self.query(sql);
569        if state {
570            if data.len() > 1 {
571                return data;
572            }
573            data[0][field].clone()
574        } else {
575            array![]
576        }
577    }
578
579    fn avg(&mut self, field: &str) -> JsonValue {
580        self.params.fields[field] = format!("avg({field}) as {field}").into();
581        let sql = self.params.select_sql();
582        let (state, data) = self.query(sql);
583        if state {
584            if data.len() > 1 {
585                return data;
586            }
587            data[0][field].clone()
588        } else {
589            array![]
590        }
591    }
592
593    fn having(&mut self, expr: &str) -> &mut Self {
594        self.params.having.push(expr.to_string());
595        self
596    }
597
598    fn select(&mut self) -> JsonValue {
599        let sql = self.params.select_sql();
600        if self.params.sql {
601            return JsonValue::from(sql.clone());
602        }
603        let (state, data) = self.query(sql.clone());
604        if state {
605            data.clone()
606        } else {
607            if self.connection.debug {
608                info!("{data:?}");
609            }
610            array![]
611        }
612    }
613
614    fn find(&mut self) -> JsonValue {
615        self.page(1, 1);
616        let sql = self.params.select_sql();
617        if self.params.sql {
618            return JsonValue::from(sql.clone());
619        }
620        let (state, data) = self.query(sql.clone());
621        if state {
622            data[0].clone()
623        } else {
624            if self.connection.debug {
625                info!("{data:#?}");
626            }
627            object! {}
628        }
629    }
630
631    fn value(&mut self, field: &str) -> JsonValue {
632        self.params.fields = object! {};
633        self.params.fields[field] = field.into();
634        self.params.page = 1;
635        self.params.limit = 1;
636        let sql = self.params.select_sql();
637        if self.params.sql {
638            return JsonValue::from(sql.clone());
639        }
640        let (state, data) = self.query(sql.clone());
641        match state {
642            true => data[0][field].clone(),
643            false => {
644                if self.connection.debug {
645                    println!("{data:?}");
646                }
647                Null
648            }
649        }
650    }
651
652    fn insert(&mut self, mut data: JsonValue) -> JsonValue {
653        let mut fields = vec![];
654        let mut values = vec![];
655        if !self.params.autoinc && data["id"].is_empty() {
656            let thread_id = format!("{:?}", thread::current().id());
657            let thread_num: u64 = thread_id
658                .trim_start_matches("ThreadId(")
659                .trim_end_matches(")")
660                .parse()
661                .unwrap_or(0);
662            data["id"] = format!(
663                "{:X}{:X}",
664                Local::now().timestamp_nanos_opt().unwrap_or(0),
665                thread_num
666            )
667            .into();
668        }
669        for (field, value) in data.entries() {
670            fields.push(format!("[{field}]"));
671            if value.is_string() || value.is_array() || value.is_object() {
672                values.push(format!("'{}'", value.to_string().replace("'", "''")));
673            } else if value.is_number() || value.is_boolean() || value.is_null() {
674                values.push(format!("{value}"));
675            } else {
676                values.push(format!("'{value}'"));
677            }
678        }
679        let fields_str = fields.join(",");
680        let values_str = values.join(",");
681        let sql = format!(
682            "INSERT INTO {} ({}) VALUES ({});",
683            self.params.table, fields_str, values_str
684        );
685        if self.params.sql {
686            return JsonValue::from(sql.clone());
687        }
688        let (state, result) = self.execute(sql.as_str());
689        match state {
690            true => match self.params.autoinc {
691                true => result,
692                false => data["id"].clone(),
693            },
694            false => {
695                let thread_id = format!("{:?}", thread::current().id());
696                error!("insert失败: {thread_id} {result:?} {sql}");
697                JsonValue::from("")
698            }
699        }
700    }
701    fn insert_all(&mut self, _data: JsonValue) -> JsonValue {
702        todo!()
703    }
704    fn upsert(&mut self, _data: JsonValue, _conflict_fields: Vec<&str>) -> JsonValue {
705        todo!()
706    }
707    fn page(&mut self, page: i32, limit: i32) -> &mut Self {
708        self.params.page = page;
709        self.params.limit = limit;
710        // self.params.top = format!("(select ROW_NUMBER() OVER(order by rand()) as ROW,* from {}) as ", self.params.table);
711        // self.params.top2 = format!("t.ROW between {} and {}", (page - 1) * limit + 1, page * limit);
712        self.params.top2 = format!(
713            "t.ROW between {} and {}",
714            (page - 1) * limit + 1,
715            page * limit
716        );
717        self
718    }
719
720    fn limit(&mut self, count: i32) -> &mut Self {
721        self.params.limit_only = count;
722        self
723    }
724
725    fn update(&mut self, data: JsonValue) -> JsonValue {
726        let mut values = vec![];
727        for (field, value) in data.entries() {
728            if field == "id" {
729                continue;
730            }
731            if value.is_string() || value.is_array() || value.is_object() {
732                values.push(format!(
733                    "[{field}]='{}'",
734                    value.to_string().replace("'", "''")
735                ));
736            } else if value.is_number() || value.is_boolean() || value.is_null() {
737                values.push(format!("[{field}]={value}"));
738            } else {
739                values.push(format!("[{field}]='{value}'"));
740            }
741        }
742        let values_str = values.join(",");
743        let where_sql = self.params.where_sql();
744        let sql = format!(
745            "UPDATE {} SET {} {};",
746            self.params.table, values_str, where_sql
747        );
748        if self.params.sql {
749            return JsonValue::from(sql.clone());
750        }
751        let (state, result) = self.execute(sql.as_str());
752        match state {
753            true => result,
754            false => {
755                error!("update失败: {result:?} {sql}");
756                JsonValue::from(0)
757            }
758        }
759    }
760
761    fn update_all(&mut self, data: JsonValue) -> JsonValue {
762        let mut success_count = 0;
763        for item in data.members() {
764            if item["id"].is_empty() {
765                continue;
766            }
767            let id = item["id"].to_string();
768            let mut values = vec![];
769            for (field, value) in item.entries() {
770                if field == "id" {
771                    continue;
772                }
773                if value.is_string() || value.is_array() || value.is_object() {
774                    values.push(format!(
775                        "[{field}]='{}'",
776                        value.to_string().replace("'", "''")
777                    ));
778                } else if value.is_number() || value.is_boolean() || value.is_null() {
779                    values.push(format!("[{field}]={value}"));
780                } else {
781                    values.push(format!("[{field}]='{value}'"));
782                }
783            }
784            let values_str = values.join(",");
785            let sql = format!(
786                "UPDATE {} SET {} WHERE [id]='{}';",
787                self.params.table, values_str, id
788            );
789            let (state, _) = self.execute(sql.as_str());
790            if state {
791                success_count += 1;
792            }
793        }
794        JsonValue::from(success_count)
795    }
796
797    fn delete(&mut self) -> JsonValue {
798        let where_sql = self.params.where_sql();
799        let sql = format!("DELETE FROM {} {};", self.params.table, where_sql);
800        if self.params.sql {
801            return JsonValue::from(sql.clone());
802        }
803        let (state, result) = self.execute(sql.as_str());
804        match state {
805            true => result,
806            false => {
807                error!("delete失败: {result:?} {sql}");
808                JsonValue::from(0)
809            }
810        }
811    }
812
813    fn field(&mut self, field: &str) -> &mut Self {
814        let list: Vec<&str> = field.split(",").collect();
815        for item in list.iter() {
816            self.params.fields[item.to_string().as_str()] = item.to_string().into();
817        }
818        self
819    }
820
821    fn field_raw(&mut self, expr: &str) -> &mut Self {
822        self.params.fields[expr] = expr.into();
823        self
824    }
825
826    fn hidden(&mut self, name: &str) -> &mut Self {
827        let hidden: Vec<&str> = name.split(",").collect();
828        let sql = format!(
829            "SELECT COLUMN_NAME as name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{}'",
830            self.params.table
831        );
832        let (_, data) = self.query(sql);
833        for item in data.members() {
834            if let Some(name) = item["name"].as_str() {
835                if !hidden.contains(&name) {
836                    self.params.fields[name] = name.into();
837                }
838            }
839        }
840        self
841    }
842
843    fn transaction(&mut self) -> bool {
844        let sql = "BEGIN TRANSACTION";
845        let (state, _) = self.execute(sql);
846        state
847    }
848
849    fn commit(&mut self) -> bool {
850        let sql = "COMMIT";
851        let (state, _) = self.execute(sql);
852        state
853    }
854
855    fn rollback(&mut self) -> bool {
856        let sql = "ROLLBACK";
857        let (state, _) = self.execute(sql);
858        state
859    }
860
861    fn sql(&mut self, sql: &str) -> Result<JsonValue, String> {
862        let (state, data) = self.query(sql.to_string());
863        match state {
864            true => Ok(data),
865            false => Err("".to_string()),
866        }
867    }
868    fn sql_execute(&mut self, sql: &str) -> Result<JsonValue, String> {
869        let (state, data) = self.execute(sql);
870        match state {
871            true => Ok(data),
872            false => Err(data.to_string()),
873        }
874    }
875
876    fn inc(&mut self, field: &str, num: f64) -> &mut Self {
877        self.params.inc_dec[field] = num.into();
878        self
879    }
880
881    fn dec(&mut self, field: &str, num: f64) -> &mut Self {
882        self.params.inc_dec[field] = (-num).into();
883        self
884    }
885
886    fn buildsql(&mut self) -> String {
887        self.fetch_sql();
888        let sql = self.select().to_string();
889        format!("( {} ) {}", sql, self.params.table)
890    }
891
892    fn join(
893        &mut self,
894        main_table: &str,
895        main_fields: &str,
896        right_table: &str,
897        right_fields: &str,
898    ) -> &mut Self {
899        let main_table = if main_table.is_empty() {
900            self.params.table.clone()
901        } else {
902            main_table.to_string()
903        };
904        self.params.join_table = right_table.to_string();
905        self.params.join.push(format!(" LEFT JOIN {right_table} ON {main_table}.{main_fields} = {right_table}.{right_fields} "));
906        self
907    }
908
909    fn join_inner(&mut self, table: &str, main_fields: &str, second_fields: &str) -> &mut Self {
910        let main_fields = if main_fields.is_empty() {
911            "id"
912        } else {
913            main_fields
914        };
915        let second_fields = if second_fields.is_empty() {
916            self.params.table.clone()
917        } else {
918            second_fields.to_string().clone()
919        };
920        let sec_table_name = format!("{}{}", table, "_2");
921        let second_table = format!("{} {}", table, sec_table_name.clone());
922        self.params.join_table = sec_table_name.clone();
923        self.params.join.push(format!(
924            " INNER JOIN {} ON {}.{} = {}.{}",
925            second_table, self.params.table, main_fields, sec_table_name, second_fields
926        ));
927        self
928    }
929
930    fn join_right(
931        &mut self,
932        main_table: &str,
933        main_fields: &str,
934        right_table: &str,
935        right_fields: &str,
936    ) -> &mut Self {
937        let main_table = if main_table.is_empty() {
938            self.params.table.clone()
939        } else {
940            main_table.to_string()
941        };
942        self.params.join_table = right_table.to_string();
943        self.params.join.push(format!(" RIGHT JOIN {right_table} ON {main_table}.{main_fields} = {right_table}.{right_fields} "));
944        self
945    }
946
947    fn join_full(
948        &mut self,
949        main_table: &str,
950        main_fields: &str,
951        right_table: &str,
952        right_fields: &str,
953    ) -> &mut Self {
954        let main_table = if main_table.is_empty() {
955            self.params.table.clone()
956        } else {
957            main_table.to_string()
958        };
959        self.params.join_table = right_table.to_string();
960        self.params.join.push(format!(" FULL OUTER JOIN {right_table} ON {main_table}.{main_fields} = {right_table}.{right_fields} "));
961        self
962    }
963
964    fn union(&mut self, sub_sql: &str) -> &mut Self {
965        self.params.unions.push(format!("UNION {sub_sql}"));
966        self
967    }
968
969    fn union_all(&mut self, sub_sql: &str) -> &mut Self {
970        self.params.unions.push(format!("UNION ALL {sub_sql}"));
971        self
972    }
973
974    fn lock_for_update(&mut self) -> &mut Self {
975        self.params.lock_mode = "FOR UPDATE".to_string();
976        self
977    }
978
979    fn lock_for_share(&mut self) -> &mut Self {
980        self.params.lock_mode = "FOR SHARE".to_string();
981        self
982    }
983
984    fn location(&mut self, field: &str) -> &mut Self {
985        self.params.location[field] = field.into();
986        self
987    }
988
989    fn join_fields(&mut self, fields: Vec<&str>) -> &mut Self {
990        for field in fields {
991            self.params.fields[field] = format!("{field} as {}", field.replace(".", "_")).into();
992        }
993        self
994    }
995
996    fn update_column(&mut self, field_a: &str, compare: &str) -> &mut Self {
997        self.params
998            .update_column
999            .push(format!("{field_a} = {compare}"));
1000        self
1001    }
1002}
br_db/types/mssql.rs

br_db/types/
mssql.rs
