search

schemacrawler / SchemaCrawler

Free database schema discovery and comprehension tool
http://www.schemacrawler.com/
Other
1.6k stars 199 forks source link

`Table#getTableConstraints()` returning an empty collection despite unique constraint. #248

Closed hendychua closed 5 years ago

hendychua commented 5 years ago

Issue

Table#getTableConstraints() is not returning any constraints.

Please explain the issue briefly

I'm trying to get the table constraints but nothing is returned.

  • Provide the steps that you took to reproduce the issue

Using the following example in Postgres:

create table emp (
id bigserial not null,
name text not null,
number int,
primary key(id),
unique (name, number));

Checking the table shows the constraint:

\d+ emp
                                            Table "public.emp"
 Column |  Type   |                    Modifiers                     | Storage  | Stats target | Description
--------+---------+--------------------------------------------------+----------+--------------+-------------
 id     | bigint  | not null default nextval('emp_id_seq'::regclass) | plain    |              |
 name   | text    | not null                                         | extended |              |
 number | integer |                                                  | plain    |              |
Indexes:
    "emp_pkey" PRIMARY KEY, btree (id)
    "emp_name_number_key" UNIQUE CONSTRAINT, btree (name, number)

How I retrieve the schema in my app:

...
final SchemaRetrievalOptions schemaRetrievalOptions = SchemaCrawlerUtility.matchSchemaRetrievalOptions(connection);
final SchemaCrawlerOptions schemaCrawlerOptions = SchemaCrawlerOptionsBuilder.builder()
    .withSchemaInfoLevel(SchemaInfoLevelBuilder.maximum())
    .toOptions();
final SchemaCrawler schemaCrawler = new SchemaCrawler(connection, schemaRetrievalOptions, schemaCrawlerOptions);
final Catalog catalog = schemaCrawler.crawl();
catalog.getTables().forEach(table -> {
    // table.getTableConstraints() returns empty collection.
    // I expect the constraint "emp_name_number_key" to be present.
});

Environment

Specify the

  • version of SchemaCrawler that you are using

15.04.01

  • version of Java that you are using

Java 11

  • operating system and version that you are using

MAC OSX

  • relational database and version that you are using

Posgres 10.4

  • JDBC driver and version that you are using

I'm using org.postgresql:postgresql:42.2.5 in the project for connecting to the postgres db.

sualeh commented 5 years ago

@hendychua - what is on your classpath? Can you provide the logs?

hendychua commented 5 years ago

Hi @sualeh, My classpath:

/Users/hendy/.m2/repository/us/fatehi/schemacrawler/15.04.01/schemacrawler-15.04.01.jar:/Users/hendy/.m2/repository/org/postgresql/postgresql/42.2.5/postgresql-42.2.5.jar:/Users/hendy/.m2/repository/com/google/guava/guava/24.1.1-jre/guava-24.1.1-jre.jar:/Users/hendy/.m2/repository/org/checkerframework/checker-compat-qual/2.0.0/checker-compat-qual-2.0.0.jar:/Users/hendy/.m2/repository/com/google/errorprone/error_prone_annotations/2.1.3/error_prone_annotations-2.1.3.jar:/Users/hendy/.m2/repository/com/google/j2objc/j2objc-annotations/1.1/j2objc-annotations-1.1.jar:/Users/hendy/.m2/repository/org/codehaus/mojo/animal-sniffer-annotations/1.14/animal-sniffer-annotations-1.14.jar:/Users/hendy/.m2/repository/com/google/code/findbugs/jsr305/3.0.2/jsr305-3.0.2.jar:/Users/hendy/.m2/repository/org/jeasy/easy-random-core/4.0.0.RC1/easy-random-core-4.0.0.RC1.jar:/Users/hendy/.m2/repository/org/objenesis/objenesis/3.0.1/objenesis-3.0.1.jar:/Users/hendy/.m2/repository/io/github/classgraph/classgraph/4.6.18/classgraph-4.6.18.jar:/Users/hendy/.m2/repository/com/fasterxml/jackson/core/jackson-databind/2.9.8/jackson-databind-2.9.8.jar:/Users/hendy/.m2/repository/com/fasterxml/jackson/core/jackson-annotations/2.9.0/jackson-annotations-2.9.0.jar:/Users/hendy/.m2/repository/com/fasterxml/jackson/core/jackson-core/2.9.8/jackson-core-2.9.8.jar

Logs:

Mar 20, 2019 7:49:11 PM schemacrawler.tools.databaseconnector.BaseDatabaseConnectionOptions getConnection
WARNING: Database password is not provided
Mar 20, 2019 7:49:11 PM schemacrawler.tools.databaseconnector.BaseDatabaseConnectionOptions getConnection
INFO: Making connection to jdbc:postgresql://localhost:5432/testdb
for user 'testuser', with properties {}
Mar 20, 2019 7:49:12 PM schemacrawler.tools.databaseconnector.BaseDatabaseConnectionOptions getConnection
INFO: Opened database connection <org.postgresql.jdbc.PgConnection@75f9eccc>
Mar 20, 2019 7:49:12 PM schemacrawler.tools.databaseconnector.BaseDatabaseConnectionOptions logConnection
INFO: Connected to
PostgreSQL 10.4 (Debian 10.4-2.pgdg90+1)
using JDBC driver
PostgreSQL JDBC Driver 42.2.5
Mar 20, 2019 7:49:12 PM schemacrawler.utility.SchemaCrawlerUtility buildSchemaRetrievalOptions
INFO: Using database plugin for
Mar 20, 2019 7:49:12 PM schemacrawler.crawl.SchemaCrawler crawlDatabaseInfo
INFO: Retrieving database information
Mar 20, 2019 7:49:12 PM schemacrawler.crawl.DatabaseInfoRetriever retrieveServerInfo
INFO: Not retrieving server information, since this was not requested
Mar 20, 2019 7:49:12 PM schemacrawler.crawl.SchemaCrawler crawlDatabaseInfo
INFO: Retrieving JDBC driver information
Mar 20, 2019 7:49:12 PM schemacrawler.crawl.SchemaCrawler crawlDatabaseInfo
INFO: Retrieving SchemaCrawler crawl information
Mar 20, 2019 7:49:12 PM schemacrawler.crawl.SchemaCrawler crawlDatabaseInfo
INFO: Total time taken for <crawlDatabaseInfo> - 00:00:00.601 hours
-  0.0% - 00:00:00.000 - <retrieveDatabaseInfo>
- 97.7% - 00:00:00.587 - <retrieveAdditionalDatabaseInfo>
-  0.0% - 00:00:00.000 - <retrieveServerInfo>
-  0.0% - 00:00:00.000 - <retrieveJdbcDriverInfo>
-  0.2% - 00:00:00.001 - <retrieveAdditionalJdbcDriverInfo>
-  1.8% - 00:00:00.011 - <retrieveCrawlInfo>

Mar 20, 2019 7:49:12 PM schemacrawler.crawl.SchemaCrawler crawl
INFO:
-- generated by: SchemaCrawler 15.04.01
-- generated on: 2019-03-20 19:49:12
-- database: PostgreSQL 10.4 (Debian 10.4-2.pgdg90+1)
-- driver: PostgreSQL JDBC Driver 42.2.5
-- operating system: Mac OS X 10.13.6
-- JVM system: Oracle Corporation Java HotSpot(TM) 64-Bit Server VM 11.0.2+7-LTS

Mar 20, 2019 7:49:12 PM schemacrawler.crawl.SchemaCrawler crawlSchemas
INFO: Crawling schemas
Mar 20, 2019 7:49:12 PM schemacrawler.crawl.SchemaRetriever retrieveAllSchemas
INFO: Retrieving all schemas
Mar 20, 2019 7:49:12 PM schemacrawler.crawl.SchemaRetriever retrieveAllCatalogs
INFO: Retrieving all catalogs
Mar 20, 2019 7:49:12 PM schemacrawler.crawl.MetadataResultSet close
INFO: Processed 4 rows for <retrieveAllSchemas>
Mar 20, 2019 7:49:12 PM schemacrawler.crawl.SchemaRetriever retrieveAllSchemas
INFO: Processed 4 schemas
Mar 20, 2019 7:49:12 PM schemacrawler.crawl.SchemaCrawler crawlSchemas
INFO: Total time taken for <crawlSchemas> - 00:00:00.017 hours
- 88.2% - 00:00:00.015 - <retrieveSchemas>
- 11.8% - 00:00:00.002 - <filterAndSortSchemas>

Mar 20, 2019 7:49:12 PM schemacrawler.crawl.SchemaCrawler crawlSchemas
INFO: Retrieved 1 schemas
Mar 20, 2019 7:49:12 PM schemacrawler.crawl.SchemaCrawler crawlColumnDataTypes
INFO: Crawling column data types
Mar 20, 2019 7:49:12 PM schemacrawler.crawl.SchemaCrawler lambda$crawlColumnDataTypes$0
INFO: Retrieving system column data types
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.SchemaCrawler lambda$crawlColumnDataTypes$1
INFO: Retrieving user column data types
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.DatabaseInfoRetriever retrieveUserDefinedColumnDataTypes
INFO: Retrieving data types for schema <testuser>
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.SchemaCrawler crawlColumnDataTypes
INFO: Total time taken for <crawlColumnDataTypes> - 00:00:00.135 hours
- 70.4% - 00:00:00.095 - <retrieveSystemColumnDataTypes>
- 29.6% - 00:00:00.040 - <retrieveUserDefinedColumnDataTypes>

Mar 20, 2019 7:49:13 PM schemacrawler.crawl.SchemaCrawler crawlTables
INFO: Crawling tables
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.SchemaCrawler lambda$crawlTables$18
INFO: Retrieving table names
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.TableRetriever retrieveTables
INFO: Retrieving tables
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.TableRetriever retrieveTablesFromMetadata
INFO: Retrieving tables for schema <testuser>
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.TableRetriever retrieveTablesFromMetadata
INFO: Processed 1 tables
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.MetadataResultSet close
INFO: Processed 1 rows for <retrieveTablesFromMetadata>
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.SchemaCrawler crawlTables
INFO: Retrieved 1 tables
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.SchemaCrawler lambda$crawlTables$19
INFO: Retrieving table columns
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.TableColumnRetriever retrieveHiddenTableColumns
INFO: No hidden table columns SQL provided
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.TableColumnRetriever retrieveTableColumns
INFO: Retrieving table columns
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.SchemaCrawler lambda$crawlTables$20
INFO: Retrieving foreign keys
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.ForeignKeyRetriever retrieveForeignKeys
INFO: Retrieving foreign keys
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.SchemaCrawler lambda$crawlTables$22
INFO: Retrieving primary keys and indexes
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.IndexRetriever retrieveIndexes
INFO: Retrieving indexes
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.IndexRetriever retrievePrimaryKeys
INFO: Retrieving primary keys
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.SchemaCrawler crawlTables
INFO: Retrieving additional table information
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.TableExtRetriever retrieveTriggerInformation
INFO: Not retrieving trigger definitions, since this was not requested
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.TableExtRetriever retrieveViewInformation
INFO: Not retrieving additional view information, since this was not requested
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.TableExtRetriever retrieveTableDefinitions
INFO: Not retrieving table definitions, since this was not requested
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.TableExtRetriever retrieveIndexInformation
INFO: Not retrieving additional index information, since this was not requested
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.TableExtRetriever retrieveIndexColumnInformation
INFO: Not retrieving additional index column information, since this was not requested
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.TableExtRetriever retrieveAdditionalTableAttributes
INFO: Not retrieving additional table attributes, since this was not requested
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.TableExtRetriever retrieveAdditionalColumnAttributes
INFO: Not retrieving additional column attributes, since this was not requested
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.SchemaCrawler crawlTables
INFO: Total time taken for <crawlTables> - 00:00:00.221 hours
-  8.6% - 00:00:00.019 - <retrieveTables>
- 18.6% - 00:00:00.041 - <retrieveColumns>
- 29.0% - 00:00:00.064 - <retrieveForeignKeys>
-  3.2% - 00:00:00.007 - <filterAndSortTables>
- 24.9% - 00:00:00.055 - <retrieveIndexes>
-  0.0% - 00:00:00.000 - <retrieveTableConstraintInformation>
-  0.0% - 00:00:00.000 - <isRetrieveTableConstraintDefinitions>
-  0.0% - 00:00:00.000 - <retrieveTriggerInformation>
-  0.0% - 00:00:00.000 - <retrieveViewInformation>
-  0.0% - 00:00:00.000 - <retrieveTableDefinitions>
-  0.0% - 00:00:00.000 - <retrieveIndexInformation>
-  0.0% - 00:00:00.000 - <retrieveAdditionalTableAttributes>
-  9.5% - 00:00:00.021 - <retrieveTablePrivileges>
-  0.0% - 00:00:00.000 - <retrieveAdditionalColumnAttributes>
-  3.6% - 00:00:00.008 - <retrieveTableColumnPrivileges>

Mar 20, 2019 7:49:13 PM schemacrawler.crawl.SchemaCrawler crawlRoutines
INFO: Crawling routines
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.SchemaCrawler lambda$crawlRoutines$8
INFO: Retrieving procedure names
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.RoutineRetriever retrieveProcedures
INFO: Retrieving procedures
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.RoutineRetriever retrieveProceduresFromMetadata
INFO: Retrieving procedures for schema <testuser>
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.RoutineRetriever retrieveProceduresFromMetadata
INFO: Processed 0 procedures
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.MetadataResultSet close
INFO: Processed 0 rows for <retrieveProceduresFromMetadata>
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.SchemaCrawler lambda$crawlRoutines$8
INFO: Retrieving function names
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.RoutineRetriever retrieveFunctions
INFO: Retrieving functions
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.RoutineRetriever retrieveFunctionsFromMetadata
INFO: Retrieving functions for schema <testuser>
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.RoutineRetriever retrieveFunctionsFromMetadata
INFO: Processed 0 functions
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.MetadataResultSet close
INFO: Processed 0 rows for <retrieveFunctionsFromMetadata>
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.SchemaCrawler crawlRoutines
INFO: Retrieved 0 routines
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.SchemaCrawler crawlSynonyms
INFO: Crawling synonyms
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.SynonymRetriever retrieveSynonymInformation
INFO: Retrieving synonyms
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.SchemaCrawler crawlSynonyms
INFO: Total time taken for <crawlSynonyms> - 00:00:00.000 hours
-  0.0% - 00:00:00.000 - <retrieveSynonymInformation>
-  0.0% - 00:00:00.000 - <filterAndSortSynonms>

Mar 20, 2019 7:49:13 PM schemacrawler.crawl.SchemaCrawler crawlSequences
INFO: Crawling sequences
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.SequenceRetriever retrieveSequenceInformation
INFO: Retrieving sequences
Mar 20, 2019 7:49:13 PM schemacrawler.crawl.SchemaCrawler crawlSequences
INFO: Total time taken for <crawlSequences> - 00:00:00.000 hours
-  0.0% - 00:00:00.000 - <retrieveSequenceInformation>
-  0.0% - 00:00:00.000 - <filterAndSortSequences>

Constraints: []
sualeh commented 5 years ago

@hendychua - you are missing the SchemaCrawler plugin for PostgreSQL. Please add that jar to your classpath.

hendychua commented 5 years ago

@sualeh It is working now. Thank you for helping!