Coverage for common/tests/sql_tests.py: 100%

1""" 

2crate_anon/common/tests/sql_tests.py 

3 

4=============================================================================== 

5 

6 Copyright (C) 2015, University of Cambridge, Department of Psychiatry. 

7 Created by Rudolf Cardinal (rnc1001@cam.ac.uk). 

8 

9 This file is part of CRATE. 

10 

11 CRATE is free software: you can redistribute it and/or modify 

12 it under the terms of the GNU General Public License as published by 

13 the Free Software Foundation, either version 3 of the License, or 

14 (at your option) any later version. 

15 

16 CRATE is distributed in the hope that it will be useful, 

17 but WITHOUT ANY WARRANTY; without even the implied warranty of 

18 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 

19 GNU General Public License for more details. 

20 

21 You should have received a copy of the GNU General Public License 

22 along with CRATE. If not, see <https://www.gnu.org/licenses/>. 

23 

24=============================================================================== 

25 

26Unit testing. 

27 

28""" 

29 

30# ============================================================================= 

31# Imports 

32# ============================================================================= 

33 

34import logging 

35from unittest import TestCase 

36 

37from cardinal_pythonlib.sql.sql_grammar_factory import make_grammar 

38from cardinal_pythonlib.sqlalchemy.dialect import SqlaDialectName 

39 

40from crate_anon.common.sql import ( 

41 is_sql_column_type_textual, 

42 get_first_from_table, 

43 matches_fielddef, 

44 matches_tabledef, 

45) 

46 

47log = logging.getLogger(__name__) 

48 

49 

50# ============================================================================= 

51# Unit tests 

52# ============================================================================= 

53 

54_ = """ 

55 _SQLTEST1 = "SELECT a FROM b WHERE c=? AND d LIKE 'blah%' AND e='?'" 

56 _SQLTEST2 = "SELECT a FROM b WHERE c=%s AND d LIKE 'blah%%' AND e='?'" 

57 _SQLTEST3 = translate_sql_qmark_to_percent(_SQLTEST1) 

58""" 

59 

60 

61class SqlTests(TestCase): 

62 # noinspection PyMethodMayBeStatic 

63 def test_sql(self) -> None: 

64 assert matches_tabledef("sometable", "sometable") 

65 assert matches_tabledef("sometable", "some*") 

66 assert matches_tabledef("sometable", "*table") 

67 assert matches_tabledef("sometable", "*") 

68 assert matches_tabledef("sometable", "s*e") 

69 assert not matches_tabledef("sometable", "x*y") 

70 

71 assert matches_fielddef("sometable", "somefield", "*.somefield") 

72 assert matches_fielddef( 

73 "sometable", "somefield", "sometable.somefield" 

74 ) 

75 assert matches_fielddef("sometable", "somefield", "sometable.*") 

76 assert matches_fielddef("sometable", "somefield", "somefield") 

77 

78 grammar = make_grammar(SqlaDialectName.MYSQL) 

79 sql = """ 

80 -- noinspection SqlResolve 

81 SELECT t1.c1, t2.c2 

82 FROM t1 INNER JOIN t2 ON t1.k = t2.k 

83 """ 

84 parsed = grammar.get_select_statement().parseString(sql, parseAll=True) 

85 log.critical(repr(parsed)) 

86 table_id = get_first_from_table(parsed) 

87 log.info(repr(table_id)) 

88 

89 def test_text_detection(self) -> None: 

90 text_types = [ 

91 "LONGTEXT", 

92 'NVARCHAR COLLATE "Latin1_General_CI_AS"', 

93 "NVARCHAR(5)", 

94 'NVARCHAR(7) COLLATE "Latin1_General_CI_AS"', 

95 "NVARCHAR", # the result of NVARCHAR(MAX) 

96 "TEXT", 

97 'VARCHAR COLLATE "Latin1_General_CI_AS"', 

98 'VARCHAR(25) COLLATE "Latin1_General_CI_AS"', 

99 "VARCHAR(5)", 

100 'VARCHAR(7) COLLATE "Latin1_General_CI_AS"', 

101 "VARCHAR", # the result of VARCHAR(MAX) 

102 ] 

103 nontext_types = [ 

104 "BIGINT", 

105 "INTEGER", 

106 "DATETIME", 

107 ] 

108 for sqltype in text_types: 

109 self.assertTrue( 

110 is_sql_column_type_textual(sqltype), 

111 f"Should be detected as textual: {sqltype}", 

112 ) 

113 for sqltype in nontext_types: 

114 self.assertFalse( 

115 is_sql_column_type_textual(sqltype), 

116 f"Should be detected as non-textual: {sqltype}", 

117 )