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