Skip to content

Commit 8b9694a

Browse files
olavloiteolavloite
authored
feat: support default schema and catalog for PostgreSQL databases (googleapis#1375)
* feat: support default schema and catalog for PostgreSQL databases The JDBC driver would return the empty string as the current catalog and schema for PostgreSQL databases. This is incorrect, as the default catalog and schema for PostgreSQL is the database name and the 'public' schema. * test: add additional testing for getSchema and getCatalog * fix: column name should be TABLE_CAT * fix: emulator now supports spanner_sys
1 parent 4cbd629 commit 8b9694a

6 files changed

Lines changed: 114 additions & 39 deletions

File tree

‎java-spanner-jdbc/src/main/java/com/google/cloud/spanner/jdbc/JdbcConnection.java‎

Lines changed: 43 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -47,6 +47,7 @@
4747
import java.util.Map;
4848
import java.util.function.BiConsumer;
4949
import java.util.function.Function;
50+
import javax.annotation.Nonnull;
5051

5152
/** Jdbc Connection class for Google Cloud Spanner */
5253
class JdbcConnection extends AbstractJdbcConnection {
@@ -394,31 +395,66 @@ public Array createArrayOf(String typeName, Object[] elements) throws SQLExcepti
394395
@Override
395396
public void setCatalog(String catalog) throws SQLException {
396397
// This method could be changed to allow the user to change to another database.
397-
// For now we only support setting an empty string in order to support frameworks
398+
// For now, we only support setting the default catalog in order to support frameworks
398399
// and applications that set this when no catalog has been specified in the connection
399400
// URL.
400401
checkClosed();
401-
JdbcPreconditions.checkArgument("".equals(catalog), "Only catalog \"\" is supported");
402+
checkValidCatalog(catalog);
403+
}
404+
405+
void checkValidCatalog(String catalog) throws SQLException {
406+
String defaultCatalog = getDefaultCatalog();
407+
JdbcPreconditions.checkArgument(
408+
defaultCatalog.equals(catalog),
409+
String.format("Only catalog %s is supported", defaultCatalog));
402410
}
403411

404412
@Override
405413
public String getCatalog() throws SQLException {
406414
checkClosed();
407-
return "";
415+
return getDefaultCatalog();
416+
}
417+
418+
@Nonnull
419+
String getDefaultCatalog() {
420+
switch (getDialect()) {
421+
case POSTGRESQL:
422+
String database = getConnectionOptions().getDatabaseName();
423+
// It should not be possible that database is null, but it's better to be safe than sorry.
424+
return database == null ? "" : database;
425+
case GOOGLE_STANDARD_SQL:
426+
default:
427+
return "";
428+
}
408429
}
409430

410431
@Override
411432
public void setSchema(String schema) throws SQLException {
412433
checkClosed();
413-
// Cloud Spanner does not support schemas, but does contain a pseudo 'empty string' schema that
414-
// might be set by frameworks and applications that read the database metadata.
415-
JdbcPreconditions.checkArgument("".equals(schema), "Only schema \"\" is supported");
434+
checkValidSchema(schema);
435+
}
436+
437+
void checkValidSchema(String schema) throws SQLException {
438+
String defaultSchema = getDefaultSchema();
439+
JdbcPreconditions.checkArgument(
440+
defaultSchema.equals(schema), String.format("Only schema %s is supported", defaultSchema));
416441
}
417442

418443
@Override
419444
public String getSchema() throws SQLException {
420445
checkClosed();
421-
return "";
446+
return getDefaultSchema();
447+
}
448+
449+
@Nonnull
450+
String getDefaultSchema() {
451+
switch (getDialect()) {
452+
case POSTGRESQL:
453+
return "public";
454+
case GOOGLE_STANDARD_SQL:
455+
default:
456+
return "";
457+
}
422458
}
423459

424460
@Override

‎java-spanner-jdbc/src/main/java/com/google/cloud/spanner/jdbc/JdbcDatabaseMetaData.java‎

Lines changed: 7 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -777,11 +777,12 @@ public ResultSet getSchemas() throws SQLException {
777777
}
778778

779779
@Override
780-
public ResultSet getCatalogs() {
780+
public ResultSet getCatalogs() throws SQLException {
781781
return JdbcResultSet.of(
782782
ResultSets.forRows(
783783
Type.struct(StructField.of("TABLE_CAT", Type.string())),
784-
Collections.singletonList(Struct.newBuilder().set("TABLE_CAT").to("").build())));
784+
Collections.singletonList(
785+
Struct.newBuilder().set("TABLE_CAT").to(getConnection().getCatalog()).build())));
785786
}
786787

787788
@Override
@@ -1524,9 +1525,10 @@ public RowIdLifetime getRowIdLifetime() {
15241525
@Override
15251526
public ResultSet getSchemas(String catalog, String schemaPattern) throws SQLException {
15261527
String sql = readSqlFromFile("DatabaseMetaData_GetSchemas.sql", connection.getDialect());
1527-
JdbcPreparedStatement statement =
1528-
prepareStatementReplaceNullWithAnyString(sql, catalog, schemaPattern);
1529-
return statement.executeQueryWithOptions(InternalMetadataQuery.INSTANCE);
1528+
try (JdbcPreparedStatement statement =
1529+
prepareStatementReplaceNullWithAnyString(sql, catalog, schemaPattern)) {
1530+
return statement.executeQueryWithOptions(InternalMetadataQuery.INSTANCE);
1531+
}
15301532
}
15311533

15321534
@Override

‎java-spanner-jdbc/src/test/java/com/google/cloud/spanner/jdbc/JdbcConnectionTest.java‎

Lines changed: 19 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,7 @@
2020
import static com.google.common.truth.Truth.assertWithMessage;
2121
import static org.junit.Assert.assertEquals;
2222
import static org.junit.Assert.assertFalse;
23+
import static org.junit.Assert.assertThrows;
2324
import static org.junit.Assert.assertTrue;
2425
import static org.junit.Assert.fail;
2526
import static org.mockito.Mockito.any;
@@ -700,34 +701,33 @@ public void testCatalog() throws SQLException {
700701
ConnectionOptions options = mockOptions();
701702
when(options.getDatabaseName()).thenReturn("test");
702703
try (JdbcConnection connection = createConnection(options)) {
703-
// The connection should always return the empty string as the current catalog, as no other
704+
// The connection should always return the default catalog as the current catalog, as no other
704705
// catalogs exist in the INFORMATION_SCHEMA.
705-
assertThat(connection.getCatalog()).isEqualTo("");
706+
// The default catalog is the empty string for GoogleSQL databases.
707+
// The default catalog is the database name for PostgreSQL databases.
708+
assertEquals(connection.getDefaultCatalog(), connection.getCatalog());
706709
// This should be allowed.
707-
connection.setCatalog("");
708-
try {
709-
// This should cause an exception.
710-
connection.setCatalog("other");
711-
fail("missing expected exception");
712-
} catch (JdbcSqlExceptionImpl e) {
713-
assertThat(e.getCode()).isEqualTo(Code.INVALID_ARGUMENT);
714-
}
710+
connection.setCatalog(connection.getDefaultCatalog());
711+
// This should cause an exception.
712+
JdbcSqlExceptionImpl exception =
713+
assertThrows(JdbcSqlExceptionImpl.class, () -> connection.setCatalog("other"));
714+
assertEquals(Code.INVALID_ARGUMENT, exception.getCode());
715715
}
716716
}
717717

718718
@Test
719719
public void testSchema() throws SQLException {
720720
try (JdbcConnection connection = createConnection(mockOptions())) {
721-
assertThat(connection.getSchema()).isEqualTo("");
721+
// The connection should always return the default schema as the current schema, as we
722+
// currently do not support setting the connection to a different schema.
723+
// The default schema is the empty string for GoogleSQL databases.
724+
// The default schema is 'public' for PostgreSQL databases.
725+
assertEquals(connection.getDefaultSchema(), connection.getSchema());
722726
// This should be allowed.
723-
connection.setSchema("");
724-
try {
725-
// This should cause an exception.
726-
connection.setSchema("other");
727-
fail("missing expected exception");
728-
} catch (JdbcSqlExceptionImpl e) {
729-
assertThat(e.getCode()).isEqualTo(Code.INVALID_ARGUMENT);
730-
}
727+
connection.setSchema(connection.getDefaultSchema());
728+
JdbcSqlExceptionImpl exception =
729+
assertThrows(JdbcSqlExceptionImpl.class, () -> connection.setSchema("other"));
730+
assertEquals(Code.INVALID_ARGUMENT, exception.getCode());
731731
}
732732
}
733733

‎java-spanner-jdbc/src/test/java/com/google/cloud/spanner/jdbc/JdbcDatabaseMetaDataTest.java‎

Lines changed: 14 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -41,10 +41,19 @@
4141
import java.util.Objects;
4242
import org.junit.Test;
4343
import org.junit.runner.RunWith;
44-
import org.junit.runners.JUnit4;
44+
import org.junit.runners.Parameterized;
45+
import org.junit.runners.Parameterized.Parameter;
46+
import org.junit.runners.Parameterized.Parameters;
4547

46-
@RunWith(JUnit4.class)
48+
@RunWith(Parameterized.class)
4749
public class JdbcDatabaseMetaDataTest {
50+
@Parameter public Dialect dialect;
51+
52+
@Parameters(name = "dialect = {0}")
53+
public static Object[] data() {
54+
return Dialect.values();
55+
}
56+
4857
private static final String DEFAULT_CATALOG = "";
4958
private static final String DEFAULT_SCHEMA = "";
5059
private static final String TEST_TABLE = "FOO";
@@ -314,10 +323,12 @@ public void testGetBestRowIdentifier() throws SQLException {
314323
@Test
315324
public void testGetCatalogs() throws SQLException {
316325
JdbcConnection connection = mock(JdbcConnection.class);
326+
when(connection.getDialect()).thenReturn(dialect);
327+
when(connection.getCatalog()).thenCallRealMethod();
317328
DatabaseMetaData meta = new JdbcDatabaseMetaData(connection);
318329
try (ResultSet rs = meta.getCatalogs()) {
319330
assertThat(rs.next(), is(true));
320-
assertThat(rs.getString("TABLE_CAT"), is(equalTo("")));
331+
assertThat(rs.getString("TABLE_CAT"), is(equalTo(connection.getDefaultCatalog())));
321332
assertThat(rs.next(), is(false));
322333
ResultSetMetaData rsmd = rs.getMetaData();
323334
assertThat(rsmd.getColumnCount(), is(equalTo(1)));

‎java-spanner-jdbc/src/test/java/com/google/cloud/spanner/jdbc/it/ITJdbcDatabaseMetaDataTest.java‎

Lines changed: 17 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -822,18 +822,30 @@ public void testGetViews() throws SQLException {
822822
@Test
823823
public void testGetSchemas() throws SQLException {
824824
try (Connection connection = createConnection(env, database)) {
825+
assertEquals("", connection.getSchema());
825826
try (ResultSet rs = connection.getMetaData().getSchemas()) {
826827
assertThat(rs.next(), is(true));
827828
assertThat(rs.getString("TABLE_SCHEM"), is(equalTo(DEFAULT_SCHEMA)));
828829
assertThat(rs.getString("TABLE_CATALOG"), is(equalTo(DEFAULT_CATALOG)));
829830
assertThat(rs.next(), is(true));
830831
assertThat(rs.getString("TABLE_SCHEM"), is(equalTo("INFORMATION_SCHEMA")));
831832
assertThat(rs.getString("TABLE_CATALOG"), is(equalTo(DEFAULT_CATALOG)));
832-
if (!EmulatorSpannerHelper.isUsingEmulator()) {
833-
assertThat(rs.next(), is(true));
834-
assertThat(rs.getString("TABLE_SCHEM"), is(equalTo("SPANNER_SYS")));
835-
assertThat(rs.getString("TABLE_CATALOG"), is(equalTo(DEFAULT_CATALOG)));
836-
}
833+
assertThat(rs.next(), is(true));
834+
assertThat(rs.getString("TABLE_SCHEM"), is(equalTo("SPANNER_SYS")));
835+
assertThat(rs.getString("TABLE_CATALOG"), is(equalTo(DEFAULT_CATALOG)));
836+
assertFalse(rs.next());
837+
}
838+
}
839+
}
840+
841+
@Test
842+
public void testGetCatalogs() throws SQLException {
843+
try (Connection connection = createConnection(env, database)) {
844+
assertEquals("", connection.getCatalog());
845+
try (ResultSet rs = connection.getMetaData().getCatalogs()) {
846+
assertTrue(rs.next());
847+
assertEquals("", rs.getString("TABLE_CAT"));
848+
837849
assertFalse(rs.next());
838850
}
839851
}

‎java-spanner-jdbc/src/test/java/com/google/cloud/spanner/jdbc/it/ITJdbcPgDatabaseMetaDataTest.java‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -752,6 +752,7 @@ public void testGetViews() throws SQLException {
752752
@Test
753753
public void testGetSchemas() throws SQLException {
754754
try (Connection connection = createConnection(env, database)) {
755+
assertEquals("public", connection.getSchema());
755756
try (ResultSet rs = connection.getMetaData().getSchemas()) {
756757
assertTrue(rs.next());
757758
assertEquals(getDefaultCatalog(database), rs.getString("TABLE_CATALOG"));
@@ -774,6 +775,19 @@ public void testGetSchemas() throws SQLException {
774775
}
775776
}
776777

778+
@Test
779+
public void testGetCatalogs() throws SQLException {
780+
try (Connection connection = createConnection(env, database)) {
781+
assertEquals(database.getId().getDatabase(), connection.getCatalog());
782+
try (ResultSet rs = connection.getMetaData().getCatalogs()) {
783+
assertTrue(rs.next());
784+
assertEquals(database.getId().getDatabase(), rs.getString("TABLE_CAT"));
785+
786+
assertFalse(rs.next());
787+
}
788+
}
789+
}
790+
777791
private static final class Table {
778792
private final String name;
779793
private final String type;

0 commit comments

Comments
 (0)