dameng_helper/
lib.rs

1#![deny(missing_debug_implementations)]
2#[macro_use]
3extern crate log;
4
5#[allow(non_camel_case_types)]
6pub mod data_type;
7pub mod error;
8pub mod table;
9
10pub use data_type::*;
11use odbc_api::buffers::TextRowSet;
12use odbc_api::handles::StatementImpl;
13use odbc_api::{Cursor, CursorImpl, ResultSetMetadata};
14use serde::{Deserialize, Serialize};
15use std::borrow::Cow;
16
17#[cfg(test)]
18#[ctor::ctor]
19fn init_test() {
20    simple_log::quick!();
21}
22
23pub trait DmAdapter {
24    fn get_table_sql(
25        table_names: Vec<String>,
26        db_name: &str,
27        case_sensitive: bool,
28    ) -> TableSqlDescribe;
29    fn get_table_desc(
30        self,
31        describe: TableSqlDescribe,
32    ) -> anyhow::Result<(Vec<String>, Vec<Vec<String>>)>;
33}
34
35#[derive(Debug, Serialize, Deserialize)]
36pub struct TableSqlDescribe {
37    pub db_name: String,
38    pub describe_sql: String,
39    pub column_name_index: usize,
40    pub table_name_index: usize,
41    // See detail dameng database case_sensitive rule: <https://github.com/baoyachi/odbc-bridge/discussions/25>
42    pub case_sensitive: bool,
43}
44
45impl DmAdapter for CursorImpl<StatementImpl<'_>> {
46    fn get_table_sql(
47        table_names: Vec<String>,
48        db_name: &str,
49        case_sensitive: bool,
50    ) -> TableSqlDescribe {
51        // Use sql: `SELECT A.*, B.NAME AS TABLE_NAME FROM SYSCOLUMNS AS a LEFT JOIN SYSOBJECTS AS B ON A.id = B.id WHERE B.name IN ("X")`;
52        // The X is table name;
53        let tables = table_names
54            .iter()
55            .map(|x| format!("'{}'", x))
56            .collect::<Vec<_>>()
57            .join(",");
58        let describe_sql = format!(
59            r#"SELECT A.NAME, A.ID, A.COLID, A.TYPE$, A.LENGTH$, A.SCALE, A.NULLABLE$, A.DEFVAL, B.NAME AS TABLE_NAME, B.CRTDATE FROM SYSCOLUMNS AS a LEFT JOIN SYSOBJECTS AS B ON A.id = B.id WHERE B.name IN ({}) AND B.SCHID IN (SELECT ID FROM SYSOBJECTS WHERE name = '{}');"#,
60            tables, db_name
61        );
62        TableSqlDescribe {
63            db_name: db_name.to_string(),
64            describe_sql,
65            column_name_index: 0,
66            table_name_index: 8,
67            case_sensitive,
68        }
69    }
70
71    fn get_table_desc(
72        mut self,
73        describe: TableSqlDescribe,
74    ) -> anyhow::Result<(Vec<String>, Vec<Vec<String>>)> {
75        debug!("describe:{:?}", describe);
76        let case_sensitive_fn = |row_index: usize, name: Cow<str>| -> String {
77            if !describe.case_sensitive
78                && (row_index == describe.column_name_index
79                    || row_index == describe.table_name_index)
80            {
81                return name.to_uppercase();
82            }
83            name.to_string()
84        };
85
86        let headers = self.column_names()?.collect::<Result<Vec<String>, _>>()?;
87
88        let mut buffers = TextRowSet::for_cursor(1024, &mut self, Some(4096))?;
89        let mut row_set_cursor = self.bind_buffer(&mut buffers)?;
90
91        let mut data = vec![];
92        while let Some(batch) = row_set_cursor.fetch()? {
93            for row_index in 0..batch.num_rows() {
94                let num_cols = batch.num_cols();
95                let row_data: Vec<String> = (0..num_cols)
96                    .map(|col_index| (col_index, batch.at(col_index, row_index).unwrap_or(&[])))
97                    .into_iter()
98                    .map(|(col_index, x)| (col_index, String::from_utf8_lossy(x)))
99                    .map(|(col_index, x)| case_sensitive_fn(col_index, x))
100                    .collect();
101                data.push(row_data);
102            }
103        }
104        Ok((headers, data))
105    }
106}
107
108#[cfg(test)]
109mod tests {
110    const DAMENG_CONNECTION: &str = "Driver={DM8};Server=0.0.0.0;UID=SYSDBA;PWD=SYSDBA001;";
111
112    use odbc_api::Environment;
113    use odbc_api_helper::executor::database::{ConnectionTrait, OdbcDbConnection, Options};
114    use odbc_api_helper::executor::execute::ExecResult;
115    use odbc_api_helper::executor::table::TableDescResult;
116    use odbc_api_helper::executor::SupportDatabase;
117    use odbc_common::Print;
118    use once_cell::sync::Lazy;
119    use regex::Regex;
120
121    #[macro_export]
122    macro_rules! svec {
123            () => {{
124                let v = Vec::<String>::new();
125                v
126
127            }};
128            ($($elem:expr),+ $(,)?) => {{
129                let v = vec![
130                    $( String::from($elem), )*
131                ];
132                v
133            }};
134        }
135
136    pub static ENV: Lazy<Environment> = Lazy::new(|| Environment::new().unwrap());
137
138    /// Validate dameng database crtdate datetime format value
139    ///
140    /// # Example
141    ///
142    /// ```rust
143    ///
144    /// assert_eq!(true, validate_crtdate("2022-10-24 17:28:26.308000"));
145    /// assert_eq!(false, validate_crtdate("2022-10-24 17:28:26 308000"));
146    /// ```
147    fn validate_crtdate(x: &str) -> bool {
148        let regex =
149            Regex::new(r#"^[0-9]{4}-[0-9]{2}-[0-9]{2}\s[0-9]{2}:[0-9]{2}:[0-9]{2}[.]{1}[0-9]{6}"#)
150                .unwrap();
151        regex.is_match(x)
152    }
153
154    fn get_dameng_conn() -> OdbcDbConnection<'static> {
155        let conn = ENV
156            .connect_with_connection_string(DAMENG_CONNECTION)
157            .unwrap();
158
159        let connection = OdbcDbConnection::new(
160            conn,
161            Options::new("SYSDBA".to_string(), SupportDatabase::Dameng),
162        )
163        .unwrap();
164        connection
165    }
166
167    #[test]
168    fn test_print_all_tables() {
169        let connection = get_dameng_conn();
170        let cursor = connection
171            .conn
172            .execute(r#"SELECT * from SYSCOLUMNS limit 10;"#, ())
173            .unwrap()
174            .unwrap();
175        cursor.print_all_tables().unwrap();
176    }
177
178    #[test]
179    fn test_dameng_table_desc() {
180        let connection = get_dameng_conn();
181
182        //1. create table
183        let create_table_t2 = r#"
184CREATE TABLE SYSDBA.T2 (
185	C1 DATETIME WITH TIME ZONE,
186	C2 TIMESTAMP,
187	c3 VARCHAR(100),
188	c4 NUMERIC,
189	c5 TIME WITH TIME ZONE,
190	c6 TIMESTAMP WITH LOCAL TIME ZONE,
191	"NUMBER" NUMBER,
192	"DECIMAL" DECIMAL,
193	"BIT" BIT,
194	"INTEGER" INTEGER,
195	xxx_PLS_INTEGER INTEGER,
196	"BIGINT" BIGINT,
197	"TINYINT" TINYINT,
198	"BYTE" BYTE,
199	"SMALLINT" SMALLINT,
200	"BINARY" BINARY(1),
201	"VARBINARY" VARBINARY(8188),
202	"REAL" REAL,
203	"FLOAT" FLOAT,
204	"DOUBLE" DOUBLE,
205	DOUBLE_PRECISION DOUBLE PRECISION,
206	"CHAR" CHAR(1),
207	"VARCHAR" VARCHAR(8188),
208	TEXT TEXT,
209	IMAGE IMAGE,
210	"BLOB" BLOB,
211	not_null_test VARCHAR(100) DEFAULT 'default_value_hh' NOT NULL,
212	not_null_test_len VARCHAR(100) DEFAULT 'default_value_hh' NOT NULL
213);"#;
214        let exec_result: ExecResult = connection.execute(create_table_t2).unwrap();
215        assert_eq!(exec_result.rows_affected, 0);
216
217        let create_table_t3 = r#"
218CREATE TABLE SYSDBA.t3 (
219	C1 DATETIME WITH TIME ZONE,
220	case_seNSItive TIMESTAMP,
221	c3 VARCHAR(100),
222	c4 NUMERIC,
223	not_null_test_len VARCHAR(100) DEFAULT 'default_value_hh' NOT NULL
224);"#;
225        let exec_result: ExecResult = connection.execute(create_table_t3).unwrap();
226        assert_eq!(exec_result.rows_affected, 0);
227
228        let create_table_t4 = r#"
229CREATE TABLE SYSDBA.T4 (
230	id INT NOT NULL,
231	useR_ID CHARACTER VARYING(8188) NOT NULL,
232	user_name TEXT NOT NULL,
233	"role" TEXT NOT NULL,
234	"source" TEXT NOT NULL
235);"#;
236
237        let exec_result: ExecResult = connection.execute(create_table_t4).unwrap();
238        assert_eq!(exec_result.rows_affected, 0);
239
240        let cursor_impl = connection.conn.execute(r#"SELECT A.NAME, A.ID, A.COLID, A.TYPE$, A.LENGTH$, A.SCALE, A.NULLABLE$, A.DEFVAL, B.NAME AS TABLE_NAME, B.CRTDATE FROM SYSCOLUMNS AS a LEFT JOIN SYSOBJECTS AS B ON A.id = B.id WHERE B.name IN ('T4') AND B.SCHID IN (SELECT ID FROM SYSOBJECTS WHERE name = 'SYSDBA');"#, ()).unwrap().unwrap();
241        cursor_impl.print_all_tables().unwrap();
242
243        //2. query table
244        let mut table_desc = connection
245            .show_table(vec!["T2".to_string(), "T3".to_string(), "T4".to_string()])
246            .unwrap();
247
248        let _: Vec<_> = table_desc
249            .1
250            .iter_mut()
251            .map(|x| {
252                let len = x.len();
253                let id = x.get(1).unwrap().parse::<usize>().unwrap();
254                // id must greater than 0
255                assert!(id > 0);
256
257                // validate CRTDATE value:2022-10-24 17:28:26.308000
258                let crtdate = x.last().unwrap();
259                assert!(validate_crtdate(crtdate));
260                let _ = std::mem::replace(&mut x[1], "1058".to_string());
261                let _ =
262                    std::mem::replace(&mut x[len - 1], "2022-10-24 17:28:26.308000".to_string());
263                x
264            })
265            .collect();
266
267        // test Options case_sensitive:false
268        info!("{}", serde_json::to_string(&table_desc).unwrap());
269        assert_eq!(table_desc, mock_table_result());
270    }
271
272    pub fn mock_table_result() -> TableDescResult {
273        let headers = svec![
274            "NAME",
275            "ID",
276            "COLID",
277            "TYPE$",
278            "LENGTH$",
279            "SCALE",
280            "NULLABLE$",
281            "DEFVAL",
282            "TABLE_NAME",
283            "CRTDATE"
284        ];
285
286        let datas = vec![
287            svec![
288                "C1",
289                "1058",
290                "0",
291                "DATETIME WITH TIME ZONE",
292                "10",
293                "6",
294                "Y",
295                "",
296                "T2",
297                "2022-10-24 17:28:26.308000",
298            ],
299            svec![
300                "C2",
301                "1058",
302                "1",
303                "TIMESTAMP",
304                "8",
305                "6",
306                "Y",
307                "",
308                "T2",
309                "2022-10-24 17:28:26.308000",
310            ],
311            svec![
312                "C3",
313                "1058",
314                "2",
315                "VARCHAR",
316                "100",
317                "0",
318                "Y",
319                "",
320                "T2",
321                "2022-10-24 17:28:26.308000",
322            ],
323            svec![
324                "C4",
325                "1058",
326                "3",
327                "NUMERIC",
328                "0",
329                "0",
330                "Y",
331                "",
332                "T2",
333                "2022-10-24 17:28:26.308000",
334            ],
335            svec![
336                "C5",
337                "1058",
338                "4",
339                "TIME WITH TIME ZONE",
340                "7",
341                "0",
342                "Y",
343                "",
344                "T2",
345                "2022-10-24 17:28:26.308000",
346            ],
347            svec![
348                "C6",
349                "1058",
350                "5",
351                "TIMESTAMP WITH LOCAL TIME ZONE",
352                "8",
353                "4102",
354                "Y",
355                "",
356                "T2",
357                "2022-10-24 17:28:26.308000",
358            ],
359            svec![
360                "NUMBER",
361                "1058",
362                "6",
363                "NUMBER",
364                "0",
365                "0",
366                "Y",
367                "",
368                "T2",
369                "2022-10-24 17:28:26.308000",
370            ],
371            svec![
372                "DECIMAL",
373                "1058",
374                "7",
375                "DECIMAL",
376                "0",
377                "0",
378                "Y",
379                "",
380                "T2",
381                "2022-10-24 17:28:26.308000",
382            ],
383            svec![
384                "BIT",
385                "1058",
386                "8",
387                "BIT",
388                "1",
389                "0",
390                "Y",
391                "",
392                "T2",
393                "2022-10-24 17:28:26.308000",
394            ],
395            svec![
396                "INTEGER",
397                "1058",
398                "9",
399                "INTEGER",
400                "4",
401                "0",
402                "Y",
403                "",
404                "T2",
405                "2022-10-24 17:28:26.308000",
406            ],
407            svec![
408                "XXX_PLS_INTEGER",
409                "1058",
410                "10",
411                "INTEGER",
412                "4",
413                "0",
414                "Y",
415                "",
416                "T2",
417                "2022-10-24 17:28:26.308000",
418            ],
419            svec![
420                "BIGINT",
421                "1058",
422                "11",
423                "BIGINT",
424                "8",
425                "0",
426                "Y",
427                "",
428                "T2",
429                "2022-10-24 17:28:26.308000",
430            ],
431            svec![
432                "TINYINT",
433                "1058",
434                "12",
435                "TINYINT",
436                "1",
437                "0",
438                "Y",
439                "",
440                "T2",
441                "2022-10-24 17:28:26.308000",
442            ],
443            svec![
444                "BYTE",
445                "1058",
446                "13",
447                "BYTE",
448                "1",
449                "0",
450                "Y",
451                "",
452                "T2",
453                "2022-10-24 17:28:26.308000",
454            ],
455            svec![
456                "SMALLINT",
457                "1058",
458                "14",
459                "SMALLINT",
460                "2",
461                "0",
462                "Y",
463                "",
464                "T2",
465                "2022-10-24 17:28:26.308000",
466            ],
467            svec![
468                "BINARY",
469                "1058",
470                "15",
471                "BINARY",
472                "1",
473                "0",
474                "Y",
475                "",
476                "T2",
477                "2022-10-24 17:28:26.308000",
478            ],
479            svec![
480                "VARBINARY",
481                "1058",
482                "16",
483                "VARBINARY",
484                "8188",
485                "0",
486                "Y",
487                "",
488                "T2",
489                "2022-10-24 17:28:26.308000",
490            ],
491            svec![
492                "REAL",
493                "1058",
494                "17",
495                "REAL",
496                "4",
497                "0",
498                "Y",
499                "",
500                "T2",
501                "2022-10-24 17:28:26.308000",
502            ],
503            svec![
504                "FLOAT",
505                "1058",
506                "18",
507                "FLOAT",
508                "8",
509                "0",
510                "Y",
511                "",
512                "T2",
513                "2022-10-24 17:28:26.308000",
514            ],
515            svec![
516                "DOUBLE",
517                "1058",
518                "19",
519                "DOUBLE",
520                "8",
521                "0",
522                "Y",
523                "",
524                "T2",
525                "2022-10-24 17:28:26.308000",
526            ],
527            svec![
528                "DOUBLE_PRECISION",
529                "1058",
530                "20",
531                "DOUBLE PRECISION",
532                "8",
533                "0",
534                "Y",
535                "",
536                "T2",
537                "2022-10-24 17:28:26.308000",
538            ],
539            svec![
540                "CHAR",
541                "1058",
542                "21",
543                "CHAR",
544                "1",
545                "0",
546                "Y",
547                "",
548                "T2",
549                "2022-10-24 17:28:26.308000",
550            ],
551            svec![
552                "VARCHAR",
553                "1058",
554                "22",
555                "VARCHAR",
556                "8188",
557                "0",
558                "Y",
559                "",
560                "T2",
561                "2022-10-24 17:28:26.308000"
562            ],
563            svec![
564                "TEXT",
565                "1058",
566                "23",
567                "TEXT",
568                "2147483647",
569                "0",
570                "Y",
571                "",
572                "T2",
573                "2022-10-24 17:28:26.308000",
574            ],
575            svec![
576                "IMAGE",
577                "1058",
578                "24",
579                "IMAGE",
580                "2147483647",
581                "0",
582                "Y",
583                "",
584                "T2",
585                "2022-10-24 17:28:26.308000",
586            ],
587            svec![
588                "BLOB",
589                "1058",
590                "25",
591                "BLOB",
592                "2147483647",
593                "0",
594                "Y",
595                "",
596                "T2",
597                "2022-10-24 17:28:26.308000",
598            ],
599            svec![
600                "NOT_NULL_TEST",
601                "1058",
602                "26",
603                "VARCHAR",
604                "100",
605                "0",
606                "N",
607                "'default_value_hh'",
608                "T2",
609                "2022-10-24 17:28:26.308000"
610            ],
611            svec![
612                "NOT_NULL_TEST_LEN",
613                "1058",
614                "27",
615                "VARCHAR",
616                "100",
617                "0",
618                "N",
619                "'default_value_hh'",
620                "T2",
621                "2022-10-24 17:28:26.308000",
622            ],
623            svec![
624                "C1",
625                "1058",
626                "0",
627                "DATETIME WITH TIME ZONE",
628                "10",
629                "6",
630                "Y",
631                "",
632                "T3",
633                "2022-10-24 17:28:26.308000"
634            ],
635            svec![
636                "CASE_SENSITIVE",
637                "1058",
638                "1",
639                "TIMESTAMP",
640                "8",
641                "6",
642                "Y",
643                "",
644                "T3",
645                "2022-10-24 17:28:26.308000"
646            ],
647            svec![
648                "C3",
649                "1058",
650                "2",
651                "VARCHAR",
652                "100",
653                "0",
654                "Y",
655                "",
656                "T3",
657                "2022-10-24 17:28:26.308000"
658            ],
659            svec![
660                "C4",
661                "1058",
662                "3",
663                "NUMERIC",
664                "0",
665                "0",
666                "Y",
667                "",
668                "T3",
669                "2022-10-24 17:28:26.308000"
670            ],
671            svec![
672                "NOT_NULL_TEST_LEN",
673                "1058",
674                "4",
675                "VARCHAR",
676                "100",
677                "0",
678                "N",
679                "'default_value_hh'",
680                "T3",
681                "2022-10-24 17:28:26.308000"
682            ],
683            svec![
684                "ID",
685                "1058",
686                "0",
687                "INT",
688                "4",
689                "0",
690                "N",
691                "",
692                "T4",
693                "2022-10-24 17:28:26.308000"
694            ],
695            svec![
696                "USER_ID",
697                "1058",
698                "1",
699                "CHARACTER VARYING",
700                "8188",
701                "0",
702                "N",
703                "",
704                "T4",
705                "2022-10-24 17:28:26.308000"
706            ],
707            svec![
708                "USER_NAME",
709                "1058",
710                "2",
711                "TEXT",
712                "2147483647",
713                "0",
714                "N",
715                "",
716                "T4",
717                "2022-10-24 17:28:26.308000"
718            ],
719            svec![
720                "ROLE",
721                "1058",
722                "3",
723                "TEXT",
724                "2147483647",
725                "0",
726                "N",
727                "",
728                "T4",
729                "2022-10-24 17:28:26.308000"
730            ],
731            svec![
732                "SOURCE",
733                "1058",
734                "4",
735                "TEXT",
736                "2147483647",
737                "0",
738                "N",
739                "",
740                "T4",
741                "2022-10-24 17:28:26.308000"
742            ],
743        ];
744        (headers, datas)
745    }
746}
dameng_helper/lib.rs

dameng_helper/
lib.rs
