-
Notifications
You must be signed in to change notification settings - Fork 2.9k
/
TestPostgreSqlCaseInsensitiveMapping.java
185 lines (168 loc) · 9.34 KB
/
TestPostgreSqlCaseInsensitiveMapping.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
/*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package io.trino.plugin.postgresql;
import com.google.common.collect.ImmutableMap;
import com.google.common.collect.ImmutableSet;
import io.trino.testing.AbstractTestQueryFramework;
import io.trino.testing.QueryRunner;
import io.trino.testing.sql.TestTable;
import org.testng.annotations.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.stream.Stream;
import static com.google.common.collect.ImmutableSet.toImmutableSet;
import static io.trino.testing.assertions.Assert.assertEquals;
import static java.lang.String.format;
import static java.util.Locale.ENGLISH;
import static org.assertj.core.api.Assertions.assertThat;
// With case-insensitive-name-matching enabled colliding schema/table names are considered as errors.
// Some tests here create colliding names which can cause any other concurrent test to fail.
@Test(singleThreaded = true)
public class TestPostgreSqlCaseInsensitiveMapping
extends AbstractTestQueryFramework
{
private TestingPostgreSqlServer postgreSqlServer;
@Override
protected QueryRunner createQueryRunner()
throws Exception
{
postgreSqlServer = closeAfterClass(new TestingPostgreSqlServer());
return PostgreSqlQueryRunner.createPostgreSqlQueryRunner(
postgreSqlServer,
ImmutableMap.of(),
ImmutableMap.of("case-insensitive-name-matching", "true"),
ImmutableSet.of());
}
@Test
public void testNonLowerCaseSchemaName()
throws Exception
{
try (AutoCloseable ignore1 = withSchema("\"NonLowerCaseSchema\"");
AutoCloseable ignore2 = withTable("\"NonLowerCaseSchema\".lower_case_name", "(c varchar(5))");
AutoCloseable ignore3 = withTable("\"NonLowerCaseSchema\".\"Mixed_Case_Name\"", "(c varchar(5))");
AutoCloseable ignore4 = withTable("\"NonLowerCaseSchema\".\"UPPER_CASE_NAME\"", "(c varchar(5))")) {
assertThat(computeActual("SHOW SCHEMAS").getOnlyColumn()).contains("nonlowercaseschema");
assertQuery("SHOW SCHEMAS LIKE 'nonlowerc%'", "VALUES 'nonlowercaseschema'");
assertQuery("SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE '%nonlowercaseschema'", "VALUES 'nonlowercaseschema'");
assertQuery("SHOW TABLES FROM nonlowercaseschema", "VALUES 'lower_case_name', 'mixed_case_name', 'upper_case_name'");
assertQuery("SELECT table_name FROM information_schema.tables WHERE table_schema = 'nonlowercaseschema'", "VALUES 'lower_case_name', 'mixed_case_name', 'upper_case_name'");
assertQueryReturnsEmptyResult("SELECT * FROM nonlowercaseschema.lower_case_name");
}
}
@Test
public void testNonLowerCaseTableName()
throws Exception
{
try (AutoCloseable ignore1 = withSchema("\"SomeSchema\"");
AutoCloseable ignore2 = withTable(
"\"SomeSchema\".\"NonLowerCaseTable\"", "AS SELECT * FROM (VALUES ('a', 'b', 'c')) t(lower_case_name, \"Mixed_Case_Name\", \"UPPER_CASE_NAME\")")) {
assertQuery(
"SELECT column_name FROM information_schema.columns WHERE table_schema = 'someschema' AND table_name = 'nonlowercasetable'",
"VALUES 'lower_case_name', 'mixed_case_name', 'upper_case_name'");
assertQuery(
"SELECT column_name FROM information_schema.columns WHERE table_name = 'nonlowercasetable'",
"VALUES 'lower_case_name', 'mixed_case_name', 'upper_case_name'");
assertEquals(
computeActual("SHOW COLUMNS FROM someschema.nonlowercasetable").getMaterializedRows().stream()
.map(row -> row.getField(0))
.collect(toImmutableSet()),
ImmutableSet.of("lower_case_name", "mixed_case_name", "upper_case_name"));
// Note: until https://github.com/prestodb/presto/issues/2863 is resolved, this is *the* way to access the tables.
assertQuery("SELECT lower_case_name FROM someschema.nonlowercasetable", "VALUES 'a'");
assertQuery("SELECT mixed_case_name FROM someschema.nonlowercasetable", "VALUES 'b'");
assertQuery("SELECT upper_case_name FROM someschema.nonlowercasetable", "VALUES 'c'");
assertQuery("SELECT upper_case_name FROM SomeSchema.NonLowerCaseTable", "VALUES 'c'");
assertQuery("SELECT upper_case_name FROM \"SomeSchema\".\"NonLowerCaseTable\"", "VALUES 'c'");
assertUpdate("INSERT INTO someschema.nonlowercasetable (lower_case_name) VALUES ('lower')", 1);
assertUpdate("INSERT INTO someschema.nonlowercasetable (mixed_case_name) VALUES ('mixed')", 1);
assertUpdate("INSERT INTO someschema.nonlowercasetable (upper_case_name) VALUES ('upper')", 1);
assertQuery(
"SELECT * FROM someschema.nonlowercasetable",
"VALUES ('a', 'b', 'c')," +
"('lower', NULL, NULL)," +
"(NULL, 'mixed', NULL)," +
"(NULL, NULL, 'upper')");
}
}
@Test
public void testSchemaNameClash()
throws Exception
{
String[] nameVariants = {"casesensitivename", "\"CaseSensitiveName\"", "\"CASESENSITIVENAME\""};
assertThat(Stream.of(nameVariants)
.map(name -> name.replace("\"", "").toLowerCase(ENGLISH))
.collect(toImmutableSet()))
.hasSize(1);
for (int i = 0; i < nameVariants.length; i++) {
for (int j = i + 1; j < nameVariants.length; j++) {
String schemaName = nameVariants[i];
String otherSchemaName = nameVariants[j];
try (AutoCloseable ignore1 = withSchema(schemaName);
AutoCloseable ignore2 = withSchema(otherSchemaName);
AutoCloseable ignore3 = withTable(schemaName + ".some_table_name", "(c varchar(5))")) {
assertThat(computeActual("SHOW SCHEMAS").getOnlyColumn()).contains("casesensitivename");
assertThat(computeActual("SHOW SCHEMAS").getOnlyColumn().filter("casesensitivename"::equals)).hasSize(1); // TODO change io.trino.plugin.jdbc.JdbcClient.getSchemaNames to return a List
assertQueryFails("SHOW TABLES FROM casesensitivename", "Failed to find remote schema name:.*Multiple entries with same key.*");
assertQueryFails("SELECT * FROM casesensitivename.some_table_name", "Failed to find remote schema name:.*Multiple entries with same key.*");
}
}
}
}
@Test
public void testTableNameClash()
throws Exception
{
String[] nameVariants = {"casesensitivename", "\"CaseSensitiveName\"", "\"CASESENSITIVENAME\""};
assertThat(Stream.of(nameVariants)
.map(name -> name.replace("\"", "").toLowerCase(ENGLISH))
.collect(toImmutableSet()))
.hasSize(1);
for (int i = 0; i < nameVariants.length; i++) {
for (int j = i + 1; j < nameVariants.length; j++) {
try (AutoCloseable ignore1 = withTable("tpch." + nameVariants[i], "(c varchar(5))");
AutoCloseable ignore2 = withTable("tpch." + nameVariants[j], "(d varchar(5))")) {
assertThat(computeActual("SHOW TABLES").getOnlyColumn()).contains("casesensitivename");
assertThat(computeActual("SHOW TABLES").getOnlyColumn().filter("casesensitivename"::equals)).hasSize(1); // TODO, should be 2
assertQueryFails("SHOW COLUMNS FROM casesensitivename", "Failed to find remote table name:.*Multiple entries with same key.*");
assertQueryFails("SELECT * FROM casesensitivename", "Failed to find remote table name:.*Multiple entries with same key.*");
}
}
}
}
private AutoCloseable withSchema(String schemaName)
{
execute("CREATE SCHEMA " + schemaName);
return () -> execute("DROP SCHEMA " + schemaName);
}
/**
* @deprecated Use {@link TestTable} instead.
*/
@Deprecated
private AutoCloseable withTable(String tableName, String tableDefinition)
{
execute(format("CREATE TABLE %s %s", tableName, tableDefinition));
return () -> execute(format("DROP TABLE %s", tableName));
}
private void execute(String sql)
{
try (Connection connection = DriverManager.getConnection(postgreSqlServer.getJdbcUrl(), postgreSqlServer.getProperties());
Statement statement = connection.createStatement()) {
statement.execute(sql);
}
catch (Exception e) {
throw new RuntimeException("Failed to execute statement: " + sql, e);
}
}
}