PHOENIX-2715 Query Log (Ankit Singhal)
[phoenix.git] / phoenix-core / src / it / java / org / apache / phoenix / end2end / TenantSpecificTablesDDLIT.java
1 /*
2 * Licensed to the Apache Software Foundation (ASF) under one
3 * or more contributor license agreements. See the NOTICE file
4 * distributed with this work for additional information
5 * regarding copyright ownership. The ASF licenses this file
6 * to you under the Apache License, Version 2.0 (the
7 * "License"); you may not use this file except in compliance
8 * with the License. You may obtain a copy of the License at
9 *
10 * http://www.apache.org/licenses/LICENSE-2.0
11 *
12 * Unless required by applicable law or agreed to in writing, software
13 * distributed under the License is distributed on an "AS IS" BASIS,
14 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15 * See the License for the specific language governing permissions and
16 * limitations under the License.
17 */
18 package org.apache.phoenix.end2end;
19
20 import static org.apache.phoenix.exception.SQLExceptionCode.CANNOT_DROP_PK;
21 import static org.apache.phoenix.exception.SQLExceptionCode.CANNOT_MUTATE_TABLE;
22 import static org.apache.phoenix.exception.SQLExceptionCode.TABLE_UNDEFINED;
23 import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.KEY_SEQ;
24 import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.ORDINAL_POSITION;
25 import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.SYSTEM_CATALOG_SCHEMA;
26 import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.SYSTEM_FUNCTION_TABLE;
27 import static org.apache.phoenix.schema.PTableType.SYSTEM;
28 import static org.apache.phoenix.schema.PTableType.TABLE;
29 import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
30 import static org.junit.Assert.assertEquals;
31 import static org.junit.Assert.assertFalse;
32 import static org.junit.Assert.assertNotEquals;
33 import static org.junit.Assert.assertTrue;
34 import static org.junit.Assert.fail;
35
36 import java.sql.Connection;
37 import java.sql.DatabaseMetaData;
38 import java.sql.DriverManager;
39 import java.sql.ResultSet;
40 import java.sql.SQLException;
41 import java.util.Arrays;
42 import java.util.Collections;
43 import java.util.HashSet;
44 import java.util.List;
45 import java.util.Properties;
46 import java.util.Set;
47
48 import org.apache.hadoop.hbase.client.HBaseAdmin;
49 import org.apache.phoenix.exception.SQLExceptionCode;
50 import org.apache.phoenix.jdbc.PhoenixConnection;
51 import org.apache.phoenix.jdbc.PhoenixDatabaseMetaData;
52 import org.apache.phoenix.schema.ColumnAlreadyExistsException;
53 import org.apache.phoenix.schema.ColumnNotFoundException;
54 import org.apache.phoenix.schema.PTableType;
55 import org.apache.phoenix.schema.TableAlreadyExistsException;
56 import org.apache.phoenix.schema.TableNotFoundException;
57 import org.apache.phoenix.util.PhoenixRuntime;
58 import org.apache.phoenix.util.PropertiesUtil;
59 import org.apache.phoenix.util.SchemaUtil;
60 import org.apache.phoenix.util.StringUtil;
61 import org.junit.Test;
62
63
64 public class TenantSpecificTablesDDLIT extends BaseTenantSpecificTablesIT {
65
66 @Test
67 public void testCreateTenantSpecificTable() throws Exception {
68 // ensure we didn't create a physical HBase table for the tenant-specific table
69 Connection conn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES));
70 HBaseAdmin admin = conn.unwrap(PhoenixConnection.class).getQueryServices().getAdmin();
71 assertEquals(0, admin.listTables(TENANT_TABLE_NAME).length);
72 }
73
74 @Test
75 public void testCreateTenantTableTwice() throws Exception {
76 try {
77 Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
78 Connection conn = DriverManager.getConnection(PHOENIX_JDBC_TENANT_SPECIFIC_URL, props);
79 conn.createStatement().execute(TENANT_TABLE_DDL);
80 fail();
81 }
82 catch (TableAlreadyExistsException expected) {}
83 }
84
85 @Test
86 public void testCreateTenantViewFromNonMultiTenant() throws Exception {
87 String tableName = generateUniqueName();
88 createTestTable(getUrl(), "CREATE TABLE " + tableName + " (K VARCHAR PRIMARY KEY)");
89 try {
90 String viewName = generateUniqueName();
91 // Only way to get this exception is to attempt to derive from a global, multi-type table, as we won't find
92 // a tenant-specific table when we attempt to resolve the base table.
93 createTestTable(PHOENIX_JDBC_TENANT_SPECIFIC_URL, "CREATE VIEW " + viewName + " (COL VARCHAR) AS SELECT * FROM " + tableName);
94 }
95 catch (TableNotFoundException expected) {
96 }
97 }
98
99 @Test
100 public void testAlteringMultiTenancyForTableWithViewsNotAllowed() throws Exception {
101 Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
102 String multiTenantTable = "MT_" + generateUniqueName();
103 String globalTable = "G_" + generateUniqueName();
104 // create the two base tables
105 try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
106 String ddl = "CREATE TABLE " + multiTenantTable + " (TENANT_ID VARCHAR NOT NULL, PK1 VARCHAR NOT NULL, V1 VARCHAR, V2 VARCHAR, V3 VARCHAR CONSTRAINT NAME_PK PRIMARY KEY(TENANT_ID, PK1)) MULTI_TENANT = true ";
107 conn.createStatement().execute(ddl);
108 ddl = "CREATE TABLE " + globalTable + " (TENANT_ID VARCHAR NOT NULL, PK1 VARCHAR NOT NULL, V1 VARCHAR, V2 VARCHAR, V3 VARCHAR CONSTRAINT NAME_PK PRIMARY KEY(TENANT_ID, PK1)) ";
109 conn.createStatement().execute(ddl);
110 }
111 String t1 = generateUniqueName();
112 props.setProperty(PhoenixRuntime.TENANT_ID_ATTRIB, t1);
113 // create view on multi-tenant table
114 try (Connection tenantConn = DriverManager.getConnection(getUrl(), props)) {
115 String viewName = "V_" + generateUniqueName();
116 String viewDDL = "CREATE VIEW " + viewName + " AS SELECT * FROM " + multiTenantTable;
117 tenantConn.createStatement().execute(viewDDL);
118 }
119 props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
120 // create view on global table
121 try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
122 String viewName = "V_" + generateUniqueName();
123 conn.createStatement().execute("CREATE VIEW " + viewName + " AS SELECT * FROM " + globalTable);
124 }
125 props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
126 try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
127 try {
128 conn.createStatement().execute("ALTER TABLE " + globalTable + " SET MULTI_TENANT = " + true);
129 fail();
130 } catch (SQLException e) {
131 assertEquals(SQLExceptionCode.CANNOT_MUTATE_TABLE.getErrorCode(), e.getErrorCode());
132 }
133
134 try {
135 conn.createStatement().execute("ALTER TABLE " + multiTenantTable + " SET MULTI_TENANT = " + false);
136 fail();
137 } catch (SQLException e) {
138 assertEquals(SQLExceptionCode.CANNOT_MUTATE_TABLE.getErrorCode(), e.getErrorCode());
139 }
140 }
141 }
142
143 @Test(expected=TableNotFoundException.class)
144 public void testDeletionOfParentTableFailsOnTenantSpecificConnection() throws Exception {
145 Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
146 props.setProperty(PhoenixRuntime.TENANT_ID_ATTRIB, TENANT_ID); // connection is tenant-specific
147 Connection conn = DriverManager.getConnection(getUrl(), props);
148 conn.createStatement().execute("DROP TABLE " + PARENT_TABLE_NAME);
149 conn.close();
150 }
151
152 public void testCreationOfParentTableFailsOnTenantSpecificConnection() throws Exception {
153 try {
154 createTestTable(PHOENIX_JDBC_TENANT_SPECIFIC_URL, "CREATE TABLE " + generateUniqueName() + "( \n" +
155 " \"user\" VARCHAR ,\n" +
156 " id INTEGER not null primary key desc\n" +
157 " ) ");
158 fail();
159 } catch (SQLException e) {
160 assertEquals(SQLExceptionCode.CANNOT_CREATE_TENANT_SPECIFIC_TABLE.getErrorCode(), e.getErrorCode());
161 }
162 }
163
164 @Test
165 public void testTenantSpecificAndParentTablesMayBeInDifferentSchemas() throws SQLException {
166 String fullTableName = "DIFFSCHEMA." + generateUniqueName();
167 createTestTable(PHOENIX_JDBC_TENANT_SPECIFIC_URL, "CREATE VIEW " + fullTableName + " ( \n" +
168 " tenant_col VARCHAR) AS SELECT * \n" +
169 " FROM " + PARENT_TABLE_NAME + " WHERE tenant_type_id = 'aaa'");
170 try {
171 createTestTable(PHOENIX_JDBC_TENANT_SPECIFIC_URL, "CREATE VIEW " + fullTableName + "( \n" +
172 " tenant_col VARCHAR) AS SELECT *\n"+
173 " FROM DIFFSCHEMA." + PARENT_TABLE_NAME + " WHERE tenant_type_id = 'aaa'");
174 fail();
175 }
176 catch (SQLException expected) {
177 assertEquals(TABLE_UNDEFINED.getErrorCode(), expected.getErrorCode());
178 }
179 String newDDL =
180 "CREATE TABLE DIFFSCHEMA." + PARENT_TABLE_NAME + " ( \n" +
181 " \"user\" VARCHAR ,\n" +
182 " tenant_id VARCHAR(5) NOT NULL,\n" +
183 " tenant_type_id VARCHAR(3) NOT NULL, \n" +
184 " id INTEGER NOT NULL\n" +
185 " CONSTRAINT pk PRIMARY KEY (tenant_id, tenant_type_id, id)) MULTI_TENANT=true";
186 createTestTable(getUrl(), newDDL);
187 createTestTable(PHOENIX_JDBC_TENANT_SPECIFIC_URL, "CREATE VIEW " + fullTableName + "( \n" +
188 " tenant_col VARCHAR) AS SELECT *\n"+
189 " FROM DIFFSCHEMA." + PARENT_TABLE_NAME + " WHERE tenant_type_id = 'aaa'");
190 }
191
192 @Test
193 public void testTenantSpecificTableCanDeclarePK() throws SQLException {
194 createTestTable(PHOENIX_JDBC_TENANT_SPECIFIC_URL, "CREATE VIEW " + generateUniqueName() + "( \n" +
195 " tenant_col VARCHAR PRIMARY KEY) AS SELECT *\n" +
196 " FROM " + PARENT_TABLE_NAME);
197 }
198
199 @Test(expected=ColumnAlreadyExistsException.class)
200 public void testTenantSpecificTableCannotOverrideParentCol() throws SQLException {
201 createTestTable(PHOENIX_JDBC_TENANT_SPECIFIC_URL, "CREATE VIEW " + generateUniqueName() + " ( \n" +
202 " \"user\" INTEGER) AS SELECT *\n" +
203 " FROM " + PARENT_TABLE_NAME);
204 }
205
206 @Test
207 public void testBaseTableWrongFormatWithTenantTypeId() throws Exception {
208 // only two PK columns for multi_tenant, multi_type
209 try {
210 createTestTable(getUrl(),
211 "CREATE TABLE " + generateUniqueName() +
212 "(TENANT_ID VARCHAR NOT NULL PRIMARY KEY, ID VARCHAR, A INTEGER) MULTI_TENANT=true");
213 fail();
214 }
215 catch (SQLException expected) {
216 assertEquals(SQLExceptionCode.INSUFFICIENT_MULTI_TENANT_COLUMNS.getErrorCode(), expected.getErrorCode());
217 }
218 }
219
220 @Test
221 public void testAddDropColumn() throws Exception {
222 Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
223 Connection conn = DriverManager.getConnection(PHOENIX_JDBC_TENANT_SPECIFIC_URL, props);
224 conn.setAutoCommit(true);
225 try {
226 conn.createStatement().execute("upsert into " + TENANT_TABLE_NAME + " (id, tenant_col) values (1, 'Viva Las Vegas')");
227 conn.createStatement().execute("alter view " + TENANT_TABLE_NAME + " add tenant_col2 char(1) null");
228 conn.createStatement().execute("upsert into " + TENANT_TABLE_NAME + " (id, tenant_col2) values (2, 'a')");
229
230 ResultSet rs = conn.createStatement().executeQuery("select count(*) from " + TENANT_TABLE_NAME);
231 rs.next();
232 assertEquals(2, rs.getInt(1));
233
234 rs = conn.createStatement().executeQuery("select count(*) from " + TENANT_TABLE_NAME + " where tenant_col2 = 'a'");
235 rs.next();
236 assertEquals(1, rs.getInt(1));
237
238 conn.createStatement().execute("alter view " + TENANT_TABLE_NAME + " drop column tenant_col");
239 rs = conn.createStatement().executeQuery("select count(*) from " + TENANT_TABLE_NAME + "");
240 rs.next();
241 assertEquals(2, rs.getInt(1));
242
243 try {
244 rs = conn.createStatement().executeQuery("select tenant_col from " + TENANT_TABLE_NAME);
245 fail();
246 }
247 catch (ColumnNotFoundException expected) {}
248 }
249 finally {
250 conn.close();
251 }
252 }
253
254 @Test
255 public void testDropOfPKInTenantTablesNotAllowed() throws Exception {
256 Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
257 Connection conn = DriverManager.getConnection(PHOENIX_JDBC_TENANT_SPECIFIC_URL, props);
258 try {
259 // try removing a PK col
260 try {
261 conn.createStatement().execute("alter table " + TENANT_TABLE_NAME + " drop column id");
262 fail();
263 }
264 catch (SQLException expected) {
265 assertEquals(CANNOT_DROP_PK.getErrorCode(), expected.getErrorCode());
266 }
267 }
268 finally {
269 conn.close();
270 }
271 }
272
273 @Test
274 public void testColumnMutationInParentTableWithExistingTenantTable() throws Exception {
275 Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
276 Connection conn = DriverManager.getConnection(getUrl(), props);
277 try {
278 try {
279 conn.createStatement().execute("alter table " + PARENT_TABLE_NAME + " drop column id");
280 fail();
281 }
282 catch (SQLException expected) {
283 assertEquals(CANNOT_DROP_PK.getErrorCode(), expected.getErrorCode());
284 }
285
286 // try removing a non-PK col, which is allowed
287 try {
288 conn.createStatement().execute("alter table " + PARENT_TABLE_NAME + " drop column \"user\"");
289 }
290 catch (SQLException expected) {
291 fail("We should be able to drop a non pk base table column");
292 }
293 }
294 finally {
295 conn.close();
296 }
297 }
298
299 @Test
300 public void testDisallowDropParentTableWithExistingTenantTable() throws Exception {
301 Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
302 Connection conn = DriverManager.getConnection(getUrl(), props);
303 try {
304 conn.createStatement().executeUpdate("drop table " + PARENT_TABLE_NAME);
305 fail("Should not have been allowed to drop a parent table to which tenant-specific tables still point.");
306 }
307 catch (SQLException expected) {
308 assertEquals(CANNOT_MUTATE_TABLE.getErrorCode(), expected.getErrorCode());
309 }
310 finally {
311 conn.close();
312 }
313 }
314
315 @Test
316 public void testAllowDropParentTableWithCascadeAndSingleTenantTable() throws Exception {
317 Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
318 Connection conn = DriverManager.getConnection(getUrl(), props);
319 Connection connTenant = null;
320
321 try {
322 // Drop Parent Table
323 conn.createStatement().executeUpdate("DROP TABLE " + PARENT_TABLE_NAME + " CASCADE");
324 connTenant = DriverManager.getConnection(PHOENIX_JDBC_TENANT_SPECIFIC_URL, props);
325
326 validateTenantViewIsDropped(conn);
327 } finally {
328 if (conn != null) {
329 conn.close();
330 }
331 if (connTenant != null) {
332 connTenant.close();
333 }
334 }
335 }
336
337
338 @Test
339 public void testAllDropParentTableWithCascadeWithMultipleTenantTablesAndIndexes() throws Exception {
340 // Create a second tenant table
341 String tenantTable2 = "V_" + generateUniqueName();
342 createTestTable(PHOENIX_JDBC_TENANT_SPECIFIC_URL2, TENANT_TABLE_DDL.replace(TENANT_TABLE_NAME, tenantTable2));
343 //TODO Create some tenant specific table indexes
344
345 Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
346 Connection conn = null;
347 Connection connTenant1 = null;
348 Connection connTenant2 = null;
349
350 try {
351 List<String> sortedCatalogs = Arrays.asList(TENANT_ID, TENANT_ID2);
352 Collections.sort(sortedCatalogs);
353 conn = DriverManager.getConnection(getUrl(), props);
354 DatabaseMetaData meta = conn.getMetaData();
355 ResultSet rs = meta.getTables(null, "", StringUtil.escapeLike(TENANT_TABLE_NAME), new String[] {PTableType.VIEW.getValue().getString()});
356 assertTrue(rs.next());
357 assertEquals(TENANT_ID, rs.getString(PhoenixDatabaseMetaData.TABLE_CAT));
358 assertTableMetaData(rs, null, TENANT_TABLE_NAME, PTableType.VIEW);
359 assertFalse(rs.next());
360
361 rs = meta.getTables(null, "", StringUtil.escapeLike(tenantTable2), new String[] {PTableType.VIEW.getValue().getString()});
362 assertTrue(rs.next());
363 assertEquals(TENANT_ID2, rs.getString(PhoenixDatabaseMetaData.TABLE_CAT));
364 assertTableMetaData(rs, null, tenantTable2, PTableType.VIEW);
365 assertFalse(rs.next());
366
367 rs = meta.getTables(null, "", StringUtil.escapeLike(TENANT_TABLE_NAME_NO_TENANT_TYPE_ID), new String[] {PTableType.VIEW.getValue().getString()});
368 assertTrue(rs.next());
369 assertEquals(TENANT_ID, rs.getString(PhoenixDatabaseMetaData.TABLE_CAT));
370 assertTableMetaData(rs, null, TENANT_TABLE_NAME_NO_TENANT_TYPE_ID, PTableType.VIEW);
371 assertFalse(rs.next());
372
373 // Drop Parent Table
374 conn.createStatement().executeUpdate("DROP TABLE " + PARENT_TABLE_NAME + " CASCADE");
375
376 // Validate Tenant Views are dropped
377 connTenant1 = DriverManager.getConnection(PHOENIX_JDBC_TENANT_SPECIFIC_URL, props);
378 validateTenantViewIsDropped(connTenant1);
379 connTenant2 = DriverManager.getConnection(PHOENIX_JDBC_TENANT_SPECIFIC_URL2, props);
380 validateTenantViewIsDropped(connTenant2);
381
382 // Validate Tenant Metadata is gone for the Tenant Table TENANT_TABLE_NAME
383 rs = meta.getTables(null, "", StringUtil.escapeLike(TENANT_TABLE_NAME), new String[] {PTableType.VIEW.getValue().getString()});
384 assertFalse(rs.next());
385 rs = meta.getTables(null, "", StringUtil.escapeLike(tenantTable2), new String[] {PTableType.VIEW.getValue().getString()});
386 assertFalse(rs.next());
387
388 rs = meta.getTables(null, "", StringUtil.escapeLike(TENANT_TABLE_NAME_NO_TENANT_TYPE_ID), new String[] {PTableType.VIEW.getValue().getString()});
389 assertTrue(rs.next());
390 assertEquals(TENANT_ID, rs.getString(PhoenixDatabaseMetaData.TABLE_CAT));
391 assertTableMetaData(rs, null, TENANT_TABLE_NAME_NO_TENANT_TYPE_ID, PTableType.VIEW);
392 assertFalse(rs.next());
393
394 } finally {
395 if (conn != null) {
396 conn.close();
397 }
398 if (connTenant1 != null) {
399 connTenant1.close();
400 }
401 if (connTenant2 != null) {
402 connTenant2.close();
403 }
404 }
405 }
406
407 private void validateTenantViewIsDropped(Connection connTenant) throws SQLException {
408 // Try and drop tenant view, should throw TableNotFoundException
409 try {
410 String ddl = "DROP VIEW " + TENANT_TABLE_NAME;
411 connTenant.createStatement().execute(ddl);
412 fail("Tenant specific view " + TENANT_TABLE_NAME + " should have been dropped when parent was dropped");
413 } catch (TableNotFoundException e) {
414 //Expected
415 }
416 }
417
418 @Test
419 public void testTableMetadataScan() throws Exception {
420 // create a tenant table with same name for a different tenant to make sure we are not picking it up in metadata scans for TENANT_ID
421 String tenantId2 = "T_" + generateUniqueName();
422 String secondTenatConnectionURL = PHOENIX_JDBC_TENANT_SPECIFIC_URL.replace(TENANT_ID, tenantId2);
423 String tenantTable2 = "V_" + generateUniqueName();
424 createTestTable(secondTenatConnectionURL, TENANT_TABLE_DDL.replace(TENANT_TABLE_NAME, tenantTable2));
425
426 Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
427 Connection conn = DriverManager.getConnection(getUrl(), props);
428 try {
429 // empty string means global tenant id
430 // make sure connections w/o tenant id only see non-tenant-specific tables, both SYSTEM and USER
431 DatabaseMetaData meta = conn.getMetaData();
432 ResultSet rs = meta.getTables("", "", StringUtil.escapeLike(PARENT_TABLE_NAME), new String[] {TABLE.getValue().getString()});
433 assertTrue(rs.next());
434 assertTableMetaData(rs, null, PARENT_TABLE_NAME, TABLE);
435 assertFalse(rs.next());
436
437 rs = meta.getTables("", "", StringUtil.escapeLike(PARENT_TABLE_NAME_NO_TENANT_TYPE_ID), new String[] {TABLE.getValue().getString()});
438 assertTrue(rs.next());
439 assertTableMetaData(rs, null, PARENT_TABLE_NAME_NO_TENANT_TYPE_ID, TABLE);
440 assertFalse(rs.next());
441
442 // make sure connections w/o tenant id only see non-tenant-specific columns
443 rs = meta.getColumns("", null, null, null);
444 while (rs.next()) {
445 assertNotEquals(TENANT_TABLE_NAME, rs.getString("TABLE_NAME"));
446 assertNotEquals(tenantTable2, rs.getString("TABLE_NAME"));
447 }
448
449 List<String> sortedTableNames = Arrays.asList(TENANT_TABLE_NAME, TENANT_TABLE_NAME_NO_TENANT_TYPE_ID);
450 Collections.sort(sortedTableNames);
451 List<String> sortedParentNames;
452 if (sortedTableNames.get(0).equals(TENANT_TABLE_NAME)) {
453 sortedParentNames = Arrays.asList(PARENT_TABLE_NAME, PARENT_TABLE_NAME_NO_TENANT_TYPE_ID);
454 } else {
455 sortedParentNames = Arrays.asList(PARENT_TABLE_NAME_NO_TENANT_TYPE_ID, PARENT_TABLE_NAME);
456 }
457 rs = meta.getSuperTables(TENANT_ID, null, null);
458 assertTrue(rs.next());
459 assertEquals(TENANT_ID, rs.getString(PhoenixDatabaseMetaData.TABLE_CAT));
460 assertEquals(sortedTableNames.get(0), rs.getString(PhoenixDatabaseMetaData.TABLE_NAME));
461 assertEquals(sortedParentNames.get(0), rs.getString(PhoenixDatabaseMetaData.SUPERTABLE_NAME));
462 assertTrue(rs.next());
463 assertEquals(TENANT_ID, rs.getString(PhoenixDatabaseMetaData.TABLE_CAT));
464 assertEquals(sortedTableNames.get(1), rs.getString(PhoenixDatabaseMetaData.TABLE_NAME));
465 assertEquals(sortedParentNames.get(1), rs.getString(PhoenixDatabaseMetaData.SUPERTABLE_NAME));
466 assertFalse(rs.next());
467
468 rs = meta.getSuperTables(tenantId2, null, null);
469 assertTrue(rs.next());
470 assertEquals(tenantId2, rs.getString(PhoenixDatabaseMetaData.TABLE_CAT));
471 assertEquals(tenantTable2, rs.getString(PhoenixDatabaseMetaData.TABLE_NAME));
472 assertEquals(PARENT_TABLE_NAME, rs.getString(PhoenixDatabaseMetaData.SUPERTABLE_NAME));
473 assertFalse(rs.next());
474
475 Set<String> sortedCatalogs = new HashSet<>(Arrays.asList(TENANT_ID, tenantId2));
476 rs = conn.getMetaData().getCatalogs();
477 while (rs.next()) {
478 sortedCatalogs.remove(rs.getString(PhoenixDatabaseMetaData.TABLE_CAT));
479 }
480 assertTrue("Should have found both tenant IDs", sortedCatalogs.isEmpty());
481 } finally {
482 props.clear();
483 conn.close();
484 }
485
486 conn = DriverManager.getConnection(PHOENIX_JDBC_TENANT_SPECIFIC_URL, props);
487 try {
488 // make sure tenant-specific connections only see their own tables and the global tables
489 DatabaseMetaData meta = conn.getMetaData();
490 ResultSet rs = meta.getTables("", SYSTEM_CATALOG_SCHEMA, null, new String[] {PTableType.SYSTEM.getValue().getString()});
491 assertTrue(rs.next());
492 assertTableMetaData(rs, PhoenixDatabaseMetaData.SYSTEM_CATALOG_SCHEMA, PhoenixDatabaseMetaData.SYSTEM_CATALOG_TABLE, PTableType.SYSTEM);
493 assertTrue(rs.next());
494 assertTableMetaData(rs, SYSTEM_CATALOG_SCHEMA, SYSTEM_FUNCTION_TABLE, SYSTEM);
495 assertTrue(rs.next());
496 assertTableMetaData(rs, PhoenixDatabaseMetaData.SYSTEM_CATALOG_SCHEMA, PhoenixDatabaseMetaData.SYSTEM_LOG_TABLE, PTableType.SYSTEM);
497 assertTrue(rs.next());
498 assertTableMetaData(rs, PhoenixDatabaseMetaData.SYSTEM_CATALOG_SCHEMA, PhoenixDatabaseMetaData.TYPE_SEQUENCE, PTableType.SYSTEM);
499 assertTrue(rs.next());
500 assertTableMetaData(rs, SYSTEM_CATALOG_SCHEMA, PhoenixDatabaseMetaData.SYSTEM_STATS_TABLE, PTableType.SYSTEM);
501 assertFalse(rs.next());
502
503 rs = meta.getTables(null, "", StringUtil.escapeLike(tenantTable2), new String[] {TABLE.getValue().getString()});
504 assertFalse(rs.next());
505
506 rs = meta.getTables(null, "", StringUtil.escapeLike(PARENT_TABLE_NAME), new String[] {TABLE.getValue().getString()});
507 assertTrue(rs.next());
508 assertTableMetaData(rs, null, PARENT_TABLE_NAME, TABLE);
509 assertFalse(rs.next());
510
511 rs = meta.getTables(null, "", StringUtil.escapeLike(PARENT_TABLE_NAME_NO_TENANT_TYPE_ID), new String[] {TABLE.getValue().getString()});
512 assertTrue(rs.next());
513 assertTableMetaData(rs, null, PARENT_TABLE_NAME_NO_TENANT_TYPE_ID, TABLE);
514 assertFalse(rs.next());
515
516 rs = meta.getTables(null, "", StringUtil.escapeLike(TENANT_TABLE_NAME), new String[] {PTableType.VIEW.getValue().getString()});
517 assertTrue(rs.next());
518 assertTableMetaData(rs, null, TENANT_TABLE_NAME, PTableType.VIEW);
519 assertFalse(rs.next());
520
521 rs = meta.getTables(null, "", StringUtil.escapeLike(TENANT_TABLE_NAME_NO_TENANT_TYPE_ID), new String[] {PTableType.VIEW.getValue().getString()});
522 assertTrue(rs.next());
523 assertTableMetaData(rs, null, TENANT_TABLE_NAME_NO_TENANT_TYPE_ID, PTableType.VIEW);
524 assertFalse(rs.next());
525
526 // make sure tenants see parent table's columns and their own
527 rs = meta.getColumns(null, null, StringUtil.escapeLike(TENANT_TABLE_NAME), null);
528 assertTrue(rs.next());
529 assertColumnMetaData(rs, null, TENANT_TABLE_NAME, "\"user\"", 1);
530 assertTrue(rs.next());
531 // (tenant_id column is not visible in tenant-specific connection)
532 assertColumnMetaData(rs, null, TENANT_TABLE_NAME, "tenant_type_id", 2);
533 assertEquals(1, rs.getInt(KEY_SEQ));
534 assertTrue(rs.next());
535 assertColumnMetaData(rs, null, TENANT_TABLE_NAME, "id", 3);
536 assertTrue(rs.next());
537 assertColumnMetaData(rs, null, TENANT_TABLE_NAME, "tenant_col", 4);
538 assertFalse(rs.next());
539
540 rs = meta.getColumns(null, null, StringUtil.escapeLike(TENANT_TABLE_NAME_NO_TENANT_TYPE_ID), null);
541 assertTrue(rs.next());
542 assertColumnMetaData(rs, null, TENANT_TABLE_NAME_NO_TENANT_TYPE_ID, "\"user\"", 1);
543 assertTrue(rs.next());
544 // (tenant_id column is not visible in tenant-specific connection)
545 assertColumnMetaData(rs, null, TENANT_TABLE_NAME_NO_TENANT_TYPE_ID, "id", 2);
546 assertTrue(rs.next());
547 assertColumnMetaData(rs, null, TENANT_TABLE_NAME_NO_TENANT_TYPE_ID, "tenant_col", 3);
548 assertFalse(rs.next());
549 }
550 finally {
551 conn.close();
552 }
553 }
554
555 private void assertTableMetaData(ResultSet rs, String schema, String table, PTableType tableType) throws SQLException {
556 assertEquals(schema, rs.getString("TABLE_SCHEM"));
557 assertEquals(table, rs.getString("TABLE_NAME"));
558 assertEquals(tableType.toString(), rs.getString("TABLE_TYPE"));
559 }
560
561 private void assertColumnMetaData(ResultSet rs, String schema, String table, String column) throws SQLException {
562 assertEquals(schema, rs.getString("TABLE_SCHEM"));
563 assertEquals(table, rs.getString("TABLE_NAME"));
564 assertEquals(SchemaUtil.normalizeIdentifier(column), rs.getString("COLUMN_NAME"));
565 }
566
567 private void assertColumnMetaData(ResultSet rs, String schema, String table, String column, int ordinalPosition)
568 throws SQLException {
569 assertColumnMetaData(rs, schema, table, column);
570 assertEquals(ordinalPosition, rs.getInt(ORDINAL_POSITION));
571 }
572 }