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

br_db/types/
mssql.rs
