getPrimaryKeys and getExportedKeys fails for `sqlite_schema` #831

onacit · 2023-01-21T10:21:08Z

Describe the bug
getExprotedKeys method fails for the sqlite_schema table.

To Reproduce

    @Test
    void __() throws SQLException {
        try (var connection = DriverManager.getConnection("jdbc:sqlite::memory:")) {
            final var meta = connection.getMetaData();
            log.debug("driverName: {}", meta.getDriverName());
            log.debug("driverVersion: {}", meta.getDriverVersion());
            log.debug("databaseProductVersion: {}", meta.getDatabaseProductVersion());
            log.debug("databaseProductName: {}", meta.getDatabaseProductName());
            log.debug("databaseMajorVersion: {}", meta.getDatabaseMajorVersion());
            log.debug("databaseMinorVersion: {}", meta.getDatabaseMinorVersion());
            try (ResultSet tables = meta.getTables(null, null, "%", null)) {
                while (tables.next()) {
                    final var tableCat = tables.getString("TABLE_CAT");
                    final var tableSchem = tables.getString("TABLE_SCHEM");
                    final var tableName = tables.getString("TABLE_NAME");
                    log.debug("{}/{}/{}", tableCat, tableSchem, tableName);
                    try (ResultSet importedKeys = meta.getImportedKeys(tableCat, tableSchem, tableName)) {
                        while (importedKeys.next()) {
                            final var pktableCat = importedKeys.getString("PKTABLE_CAT");
                            final var pktableSchem = importedKeys.getString("PKTABLE_SCHEM");
                            final var pktableName = importedKeys.getString("PKTABLE_NAME");
                            log.debug("importedKey.pktable: {}/{}/{}", pktableCat, pktableSchem, pktableName);
                        }
                    }
                    try (ResultSet exportedKeys = meta.getExportedKeys(tableCat, tableSchem, tableName)) {
                        while (exportedKeys.next()) {
                        }
                    }
                }
            }
        }
    }

Expected behavior
Should work for the table.

Logs

749  [main] DEBUG com.github.jinahya.database.metadata.bind.MemorySqliteTest - driverName: SQLite JDBC
751  [main] DEBUG com.github.jinahya.database.metadata.bind.MemorySqliteTest - driverVersion: 3.40.0.0
751  [main] DEBUG com.github.jinahya.database.metadata.bind.MemorySqliteTest - databaseProductVersion: 3.40.0
751  [main] DEBUG com.github.jinahya.database.metadata.bind.MemorySqliteTest - databaseProductName: SQLite
751  [main] DEBUG com.github.jinahya.database.metadata.bind.MemorySqliteTest - databaseMajorVersion: 3
752  [main] DEBUG com.github.jinahya.database.metadata.bind.MemorySqliteTest - databaseMinorVersion: 40
756  [main] DEBUG com.github.jinahya.database.metadata.bind.MemorySqliteTest - null/null/sqlite_schema

java.sql.SQLException: Table not found: 'sqlite_schema'

	at org.sqlite.jdbc3.JDBC3DatabaseMetaData$PrimaryKeyFinder.<init>(JDBC3DatabaseMetaData.java:1996)
	at org.sqlite.jdbc3.JDBC3DatabaseMetaData.getExportedKeys(JDBC3DatabaseMetaData.java:1248)
	at com.github.jinahya.database.metadata.bind.MemorySqliteTest.__(MemorySqliteTest.java:98)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:727)
	at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
	at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:156)
	at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:147)
	at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:86)
	at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(InterceptingExecutableInvoker.java:103)
	at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.lambda$invoke$0(InterceptingExecutableInvoker.java:93)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
	at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.invoke(InterceptingExecutableInvoker.java:92)
	at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.invoke(InterceptingExecutableInvoker.java:86)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:217)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:213)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:138)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:68)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:151)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:35)
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:54)
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:147)
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:127)
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:90)
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:55)
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:102)
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:54)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:114)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:86)
	at org.junit.platform.launcher.core.DefaultLauncherSession$DelegatingLauncher.execute(DefaultLauncherSession.java:86)
	at org.junit.platform.launcher.core.SessionPerRequestLauncher.execute(SessionPerRequestLauncher.java:53)
	at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:57)
	at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
	at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
	at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
	at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
	at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)

If applicable, provide logs.

Environment (please complete the following information):

Running `/Users/onacit/gitcl/github.com/jinahya/database-metadata-bind/mvnw`...
Apache Maven 3.8.7 (b89d5959fcde851dcb1c8946a785a163f14e1e29)
Maven home: /Users/onacit/.m2/wrapper/dists/apache-maven-3.8.7-bin/1ktonn2lleg549uah6ngl1r74r/apache-maven-3.8.7
Java version: 17.0.5, vendor: Eclipse Adoptium, runtime: /Library/Java/JavaVirtualMachines/temurin-17.jdk/Contents/Home
Default locale: en_KR, platform encoding: UTF-8
OS name: "mac os x", version: "13.1", arch: "aarch64", family: "mac"

Additional context
Add any other context about the problem here.

The text was updated successfully, but these errors were encountered:

onacit · 2023-01-21T10:33:28Z

The getPrimaryKeys, for the same table, also fails.

gotson · 2023-01-26T03:42:57Z

The table information is retrieved from sqlite_master, which doesn't contain information about itself:

The sqlite_schema table contains one row for each table, index, view, and trigger (collectively "objects") in the schema, except there is no entry for the sqlite_schema table itself.

Why would you want to get the imported/exported keys for that table ?

onacit · 2023-01-26T05:51:14Z

I'm writing a library binds what DatabaseMetaData provides into POJOs.
https://javadoc.io/doc/com.github.jinahya/database-metadata-bind/latest/com/github/jinahya/database/metadata/bind/package-summary.html

onacit · 2023-01-26T05:53:36Z

Regarding the sqlite_schema table as a special case is not clients' duty, I think. Thanks.

gotson · 2023-01-26T07:02:23Z

Regarding the sqlite_schema table as a special case is not clients' duty, I think. Thanks.

not sure to understand what you mean by that.

There's no way in SQLite to get the details of sqlite_master table. Our driver can't do something that SQLite can't.

onacit · 2023-01-26T08:44:30Z

I'm not familiar with internals especially regarding with sqlite_master nor sqlite_schema which seem refer the same table.

Here comes the scenario.

Invoke getTables(null, null, "%", null)
- ResultSet contains for the sqlite_schema (no more tables because nobody created yet)
Request other info related for the sqlite_schema may throw that SQLite doesn't know about the table sqlite_schema.

The problem is clients(including me) may request extra information(columns, keys, and so on) for that table.

If the sqlite_schema table is really an internal-use-only master table, e.g. for indexing other tables,
can the driver omit the sqlite_schema from the result of getTables?

Thanks.

gotson · 2023-01-26T09:38:25Z

maybe getExportedKeys and getImportedKeys could have a special case and return an empty result set when called for the table sqlite_schema, what do you think ?

onacit · 2023-01-26T10:01:18Z

@gotson That will be good if you think it is. But, as commented, getPrimaryKeys also fails with Table not found: 'sqlite_schema' message.

Let me report every failing endpoints whenever I found.

Thanks. You should let yourself forget this issue if the codebase gonna be corrupted or broken.

gotson · 2023-01-26T10:08:08Z

in that case getPrimaryKeys should also be special handled. Anyway sqlite_schema does not have any imported/exported/primary keys, it's a special table.

onacit · 2023-01-26T11:44:49Z

Ok. If we can't exclude the sqlite_schema table from the getTables method, it may be handled, as you said, within get(Exported|Imported|PrimaryKeys) methods by returning empty ResultSet.

Thank you.

gotson · 2023-01-26T15:52:15Z

It doesn't make sense to exclude from getTables which allow for returning SYSTEM TABLE types.

github-actions · 2023-02-02T07:17:49Z

🎉 This issue has been resolved in 3.40.1.0 (Release Notes)

onacit added the triage label Jan 21, 2023

gotson added enhancement:JDBC Enhancement specific to the JDBC standard and removed triage labels Jan 27, 2023

gotson self-assigned this Jan 27, 2023

gotson changed the title ~~getExportedKeys fails for 'sqlite_schema'~~ getExportedKeys fails for sqlite_schema Feb 2, 2023

gotson changed the title ~~getExportedKeys fails for sqlite_schema~~ getPrimaryKeys and getExportedKeys fails for sqlite_schema Feb 2, 2023

gotson closed this as completed in 0dc6ad9 Feb 2, 2023

github-actions bot added the released Issue has been released label Feb 2, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

getPrimaryKeys and getExportedKeys fails for `sqlite_schema` #831

getPrimaryKeys and getExportedKeys fails for `sqlite_schema` #831

onacit commented Jan 21, 2023

onacit commented Jan 21, 2023

gotson commented Jan 26, 2023

onacit commented Jan 26, 2023

onacit commented Jan 26, 2023

gotson commented Jan 26, 2023

onacit commented Jan 26, 2023 •

edited

Loading

gotson commented Jan 26, 2023

onacit commented Jan 26, 2023 •

edited

Loading

gotson commented Jan 26, 2023 •

edited

Loading

onacit commented Jan 26, 2023

gotson commented Jan 26, 2023

github-actions bot commented Feb 2, 2023

getPrimaryKeys and getExportedKeys fails for sqlite_schema #831

getPrimaryKeys and getExportedKeys fails for sqlite_schema #831

Comments

onacit commented Jan 21, 2023

onacit commented Jan 21, 2023

gotson commented Jan 26, 2023

onacit commented Jan 26, 2023

onacit commented Jan 26, 2023

gotson commented Jan 26, 2023

onacit commented Jan 26, 2023 • edited Loading

gotson commented Jan 26, 2023

onacit commented Jan 26, 2023 • edited Loading

gotson commented Jan 26, 2023 • edited Loading

onacit commented Jan 26, 2023

gotson commented Jan 26, 2023

github-actions bot commented Feb 2, 2023

getPrimaryKeys and getExportedKeys fails for `sqlite_schema` #831

getPrimaryKeys and getExportedKeys fails for `sqlite_schema` #831

onacit commented Jan 26, 2023 •

edited

Loading

onacit commented Jan 26, 2023 •

edited

Loading

gotson commented Jan 26, 2023 •

edited

Loading