SchemaCrawler Calcite multiple datasources test output

CalciteExample.java

package com.example;

import org.apache.calcite.adapter.jdbc.JdbcCatalogSchema;
import org.apache.calcite.jdbc.CalciteConnection;
import org.apache.calcite.jdbc.JavaTypeFactoryImpl;
import org.apache.calcite.rel.type.RelDataTypeField;
import org.apache.calcite.schema.SchemaPlus;
import org.apache.calcite.schema.Table;
import schemacrawler.schema.Catalog;
import schemacrawler.schemacrawler.SchemaCrawlerOptions;
import schemacrawler.schemacrawler.SchemaCrawlerOptionsBuilder;
import schemacrawler.schemacrawler.SchemaRetrievalOptions;
import schemacrawler.schemacrawler.SchemaRetrievalOptionsBuilder;
import schemacrawler.tools.utility.SchemaCrawlerUtility;
import schemacrawler.utility.TypeMap;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

public class CalciteExample {

    public static void main(String[] args) throws Exception {
        // Create first in-memory database
        org.h2.jdbcx.JdbcDataSource h2DataSource1 = new org.h2.jdbcx.JdbcDataSource();
        h2DataSource1.setURL("jdbc:h2:mem:test1;DB_CLOSE_DELAY=-1");
        h2DataSource1.setUser("sa");
        h2DataSource1.setPassword("");
        String fooTableSQL = "CREATE TABLE FOO_TABLE (foo_id INT PRIMARY KEY, foo_name VARCHAR(255));";
        String fooTableRecordsSQL = "INSERT INTO FOO_TABLE (foo_id, foo_name) VALUES (1, 'foo'), (2, 'bar'), (3, 'baz');";
        h2DataSource1.getConnection().createStatement().execute(fooTableSQL);
        h2DataSource1.getConnection().createStatement().execute(fooTableRecordsSQL);

        // Create second in-memory database
        org.h2.jdbcx.JdbcDataSource h2DataSource2 = new org.h2.jdbcx.JdbcDataSource();
        h2DataSource2.setURL("jdbc:h2:mem:test2;DB_CLOSE_DELAY=-1");
        h2DataSource2.setUser("sa");
        h2DataSource2.setPassword("");
        String barTableSQL = "CREATE TABLE BAR_TABLE (bar_id INT PRIMARY KEY, bar_name VARCHAR(255));";
        String barTableRecordsSQL = "INSERT INTO BAR_TABLE (bar_id, bar_name) VALUES (1, 'qux'), (2, 'blah'), (3, 'yadda');";
        h2DataSource2.getConnection().createStatement().execute(barTableSQL);
        h2DataSource2.getConnection().createStatement().execute(barTableRecordsSQL);

        // Create Calcite connection
        CalciteConnection conn = DriverManager.getConnection("jdbc:calcite:").unwrap(CalciteConnection.class);
        SchemaPlus rootSchema = conn.getRootSchema();

        // Add the two in-memory database
        JdbcCatalogSchema schema1 = JdbcCatalogSchema.create(rootSchema, "H2_DB1", h2DataSource1, null);
        JdbcCatalogSchema schema2 = JdbcCatalogSchema.create(rootSchema, "H2_DB2", h2DataSource2, null);
        rootSchema.add("H2_DB1", schema1);
        rootSchema.add("H2_DB2", schema2);

        // How to view Calcite schemas and tables
        System.out.println("DUMPING CALCITE SCHEMAS");
        dumpCalciteSchema(rootSchema);
        System.out.println("=======================");

        // Calcite can now query across databases
        String sql = "SELECT * FROM H2_DB1.PUBLIC.FOO_TABLE, H2_DB2.PUBLIC.BAR_TABLE";
        ResultSet rs = conn.createStatement().executeQuery(sql);
        while (rs.next()) {
            ResultSetMetaData metaData = rs.getMetaData();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                System.out.print(metaData.getColumnName(i) + ": " + rs.getObject(i) + " ");
            }
            System.out.println();
        }
        System.out.println("=======================");

        // Now try to use SchemaCrawler to get the same information
        // It will fail because there are schemas nested in schemas
        //
        //             catalog  schema schema   table
        // Example: (rootSchema/H2_DB1/PUBLIC/FOO_TABLE)
        //
        // It prints:
        //        Schema: H2_DB1
        //        Schema: H2_DB2
        //        Schema: metadata
        CalciteSchemaCrawler.crawlCalciteSchemas(conn);
    }

    // How to view Calcite schemas and tables
    static void dumpCalciteSchema(SchemaPlus rootSchema) {
        for (String datasourceName : rootSchema.getSubSchemaNames()) {
            System.out.println("Datasource: " + datasourceName);
            SchemaPlus datasourceSchema = rootSchema.getSubSchema(datasourceName);
            for (String tableName : datasourceSchema.getTableNames()) {
                System.out.println("  Table: " + tableName);
                Table table = datasourceSchema.getTable(tableName);
                if (table == null) continue;
                for (RelDataTypeField field : table.getRowType(new JavaTypeFactoryImpl()).getFieldList()) {
                    System.out.println("    Field: " + field.getName() + " (" + field.getType().getSqlTypeName() + ")");
                }
            }
            for (String schemaName : datasourceSchema.getSubSchemaNames()) {
                System.out.println("  Schema: " + schemaName);
                SchemaPlus schema = datasourceSchema.getSubSchema(schemaName);
                for (String tableName : schema.getTableNames()) {
                    System.out.println("    Table: " + tableName);
                    Table table = datasourceSchema.getTable(tableName);
                    if (table == null) continue;
                    for (RelDataTypeField field : table.getRowType(new JavaTypeFactoryImpl()).getFieldList()) {
                        System.out.println("    Field: " + field.getName() + " (" + field.getType().getSqlTypeName() + ")");
                    }
                }
            }
        }
    }
}

class CalciteSchemaCrawler {

    private static final SchemaCrawlerOptions options = SchemaCrawlerOptionsBuilder.newSchemaCrawlerOptions();

    // Must override TypeMap or else the Calcite JDBC driver throws an exception due to unimplemented methods
    private static final SchemaRetrievalOptions retrievalOptions = SchemaRetrievalOptionsBuilder.builder()
            .withTypeMap(new TypeMap())
            .toOptions();

    public static void crawlCalciteSchemas(CalciteConnection calciteConn) {
        final Catalog catalog = SchemaCrawlerUtility.getCatalog(calciteConn, retrievalOptions, options, new schemacrawler.tools.options.Config());
        for (final schemacrawler.schema.Schema datasource : catalog.getSchemas()) {
            System.out.println("Schema: " + datasource);
            for (final schemacrawler.schema.Table table : catalog.getTables(datasource)) {
                System.out.print("o--> " + table);
                for (final schemacrawler.schema.Column column : table.getColumns()) {
                    System.out.println("     o--> " + column + " (" + column.getColumnDataType() + ")");
                }
            }
        }
    }
}

output

DUMPING CALCITE SCHEMAS
Datasource: H2_DB1
  Schema: INFORMATION_SCHEMA
    Table: CATALOGS
    Table: COLLATIONS
    Table: COLUMN_PRIVILEGES
    Table: COLUMNS
    Table: CONSTANTS
    Table: CONSTRAINTS
    Table: CROSS_REFERENCES
    Table: DOMAINS
    Table: FUNCTION_ALIASES
    Table: FUNCTION_COLUMNS
    Table: HELP
    Table: IN_DOUBT
    Table: INDEXES
    Table: KEY_COLUMN_USAGE
    Table: LOCKS
    Table: QUERY_STATISTICS
    Table: REFERENTIAL_CONSTRAINTS
    Table: RIGHTS
    Table: ROLES
    Table: SCHEMATA
    Table: SEQUENCES
    Table: SESSION_STATE
    Table: SESSIONS
    Table: SETTINGS
    Table: SYNONYMS
    Table: TABLE_CONSTRAINTS
    Table: TABLE_PRIVILEGES
    Table: TABLE_TYPES
    Table: TABLES
    Table: TRIGGERS
    Table: TYPE_INFO
    Table: USERS
    Table: VIEWS
  Schema: PUBLIC
    Table: FOO_TABLE
Datasource: H2_DB2
  Schema: INFORMATION_SCHEMA
    Table: CATALOGS
    Table: COLLATIONS
    Table: COLUMN_PRIVILEGES
    Table: COLUMNS
    Table: CONSTANTS
    Table: CONSTRAINTS
    Table: CROSS_REFERENCES
    Table: DOMAINS
    Table: FUNCTION_ALIASES
    Table: FUNCTION_COLUMNS
    Table: HELP
    Table: IN_DOUBT
    Table: INDEXES
    Table: KEY_COLUMN_USAGE
    Table: LOCKS
    Table: QUERY_STATISTICS
    Table: REFERENTIAL_CONSTRAINTS
    Table: RIGHTS
    Table: ROLES
    Table: SCHEMATA
    Table: SEQUENCES
    Table: SESSION_STATE
    Table: SESSIONS
    Table: SETTINGS
    Table: SYNONYMS
    Table: TABLE_CONSTRAINTS
    Table: TABLE_PRIVILEGES
    Table: TABLE_TYPES
    Table: TABLES
    Table: TRIGGERS
    Table: TYPE_INFO
    Table: USERS
    Table: VIEWS
  Schema: PUBLIC
    Table: BAR_TABLE
Datasource: metadata
  Table: COLUMNS
    Field: tableCat (VARCHAR)
    Field: tableSchem (VARCHAR)
    Field: tableName (VARCHAR)
    Field: columnName (VARCHAR)
    Field: dataType (INTEGER)
    Field: typeName (VARCHAR)
    Field: columnSize (INTEGER)
    Field: bufferLength (INTEGER)
    Field: decimalDigits (INTEGER)
    Field: numPrecRadix (INTEGER)
    Field: nullable (INTEGER)
    Field: remarks (VARCHAR)
    Field: columnDef (VARCHAR)
    Field: sqlDataType (INTEGER)
    Field: sqlDatetimeSub (INTEGER)
    Field: charOctetLength (INTEGER)
    Field: ordinalPosition (INTEGER)
    Field: isNullable (VARCHAR)
    Field: scopeCatalog (VARCHAR)
    Field: scopeSchema (VARCHAR)
    Field: scopeTable (VARCHAR)
    Field: sourceDataType (SMALLINT)
    Field: isAutoincrement (VARCHAR)
    Field: isGeneratedcolumn (VARCHAR)
  Table: TABLES
    Field: tableCat (VARCHAR)
    Field: tableSchem (VARCHAR)
    Field: tableName (VARCHAR)
    Field: tableType (VARCHAR)
    Field: remarks (VARCHAR)
    Field: typeCat (VARCHAR)
    Field: typeSchem (VARCHAR)
    Field: typeName (VARCHAR)
    Field: selfReferencingColName (VARCHAR)
    Field: refGeneration (VARCHAR)
=======================
FOO_ID: 1 FOO_NAME: foo BAR_ID: 1 BAR_NAME: qux 
FOO_ID: 1 FOO_NAME: foo BAR_ID: 2 BAR_NAME: blah 
FOO_ID: 1 FOO_NAME: foo BAR_ID: 3 BAR_NAME: yadda 
FOO_ID: 2 FOO_NAME: bar BAR_ID: 1 BAR_NAME: qux 
FOO_ID: 2 FOO_NAME: bar BAR_ID: 2 BAR_NAME: blah 
FOO_ID: 2 FOO_NAME: bar BAR_ID: 3 BAR_NAME: yadda 
FOO_ID: 3 FOO_NAME: baz BAR_ID: 1 BAR_NAME: qux 
FOO_ID: 3 FOO_NAME: baz BAR_ID: 2 BAR_NAME: blah 
FOO_ID: 3 FOO_NAME: baz BAR_ID: 3 BAR_NAME: yadda 
=======================
Mar 18, 2022 11:08:08 AM schemacrawler.tools.utility.SchemaCrawlerUtility logConnection
INFO: Connected to 
Calcite 1.30.0 
using JDBC driver 
Calcite JDBC Driver 1.30.0
with 
"jdbc:calcite:"
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.RetrievalStopWatch lambda$time$0
INFO: retrieveAdditionalDatabaseInfo not requested
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.RetrievalStopWatch lambda$time$0
INFO: retrieveServerInfo not requested
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.RetrievalStopWatch lambda$time$0
INFO: retrieveDatabaseUsers not requested
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.RetrievalStopWatch lambda$time$0
INFO: retrieveAdditionalJdbcDriverInfo not requested
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.SchemaCrawler crawl
INFO: 
-- generated by: SchemaCrawler 16.16.11
-- generated on: 2022-03-18T15:08:08.129879471
-- database: Calcite 1.30.0
-- driver: Calcite JDBC Driver 1.30.0
-- operating system: Linux 5.10.16.3-microsoft-standard-WSL2
-- JVM system: Eclipse Adoptium OpenJDK 64-Bit Server VM 17.0.1+12

Mar 18, 2022 11:08:08 AM schemacrawler.crawl.RetrievalStopWatch lambda$time$0
INFO: Running retrieveSchemas
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.SchemaRetriever retrieveAllSchemas
INFO: Retrieving all schemas
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.SchemaRetriever retrieveAllCatalogs
INFO: Retrieving all catalogs
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.SchemaRetriever retrieveAllCatalogs
INFO: Processed 0 catalogs
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.SchemaRetriever retrieveAllSchemas
INFO: Processed 3 schemas
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.RetrievalStopWatch lambda$time$0
INFO: Running filterAndSortSchemas
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.SchemaCrawler crawlSchemas
INFO: Retrieved 3 schemas
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.RetrievalStopWatch lambda$time$0
INFO: Running retrieveColumnDataTypes
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.DataTypeRetriever retrieveSystemColumnDataTypes
INFO: Retrieving system column data types
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.DataTypeRetriever retrieveSystemColumnDataTypesFromMetadata
INFO: Processed 41 system column data types
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.RetrievalStopWatch lambda$time$0
INFO: retrieveUserDefinedColumnDataTypes not requested
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.RetrievalStopWatch lambda$time$0
INFO: Running retrieveTables
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.SchemaCrawler lambda$crawlTables$12
INFO: Retrieving table names
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.TableRetriever retrieveTables
INFO: Retrieving tables
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.TableRetriever retrieveTablesFromMetadata
INFO: Retrieving tables for schema <H2_DB1>
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.TableRetriever retrieveTablesFromMetadata
INFO: Processed 0 tables
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.TableRetriever retrieveTablesFromMetadata
INFO: Retrieving tables for schema <H2_DB2>
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.TableRetriever retrieveTablesFromMetadata
INFO: Processed 0 tables
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.TableRetriever retrieveTablesFromMetadata
INFO: Retrieving tables for schema <metadata>
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.TableRetriever retrieveTablesFromMetadata
INFO: Processed 0 tables
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.SchemaCrawler crawlTables
INFO: Retrieved 0 tables
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.SchemaCrawler crawlRoutines
INFO: Not retrieving routines, since this was not requested
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.SchemaCrawler crawlSynonyms
INFO: Not retrieving synonyms, since this was not requested
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.SchemaCrawler crawlSequences
INFO: Not retrieving sequences, since this was not requested
Mar 18, 2022 11:08:08 AM schemacrawler.crawl.RetrievalStopWatch stopAndLogTime
INFO: Total time taken for <standard> - 00:00:00.047 hours
-  0.0% - 00:00:00.000 - <retrieveAdditionalDatabaseInfo>
-  0.0% - 00:00:00.000 - <retrieveServerInfo>
-  0.0% - 00:00:00.000 - <retrieveDatabaseUsers>
-  0.0% - 00:00:00.000 - <retrieveAdditionalJdbcDriverInfo>
- 27.7% - 00:00:00.013 - <retrieveSchemas>
-  2.1% - 00:00:00.001 - <filterAndSortSchemas>
- 40.4% - 00:00:00.019 - <retrieveColumnDataTypes>
-  0.0% - 00:00:00.000 - <retrieveUserDefinedColumnDataTypes>
- 23.4% - 00:00:00.011 - <retrieveTables>

Mar 18, 2022 11:08:08 AM schemacrawler.loader.attributes.AttributesCatalogLoader loadCatalog
INFO: Retrieving catalog attributes
Mar 18, 2022 11:08:08 AM schemacrawler.loader.attributes.AttributesCatalogLoader loadCatalog
INFO: Total time taken for <loadAttributes> - 00:00:00.000 hours
-  0.0% - 00:00:00.000 - <retrieveCatalogAttributes>

Mar 18, 2022 11:08:08 AM schemacrawler.loader.counts.TableRowCountsCatalogLoader loadCatalog
INFO: Retrieving table row counts
Mar 18, 2022 11:08:08 AM schemacrawler.loader.counts.TableRowCountsCatalogLoader lambda$loadCatalog$0
INFO: Not retrieving table row counts, since this was not requested
Mar 18, 2022 11:08:08 AM schemacrawler.loader.counts.TableRowCountsCatalogLoader loadCatalog
INFO: Total time taken for <loadTableRowCounts> - 00:00:00.000 hours
-  0.0% - 00:00:00.000 - <retrieveTableRowCounts>
-  0.0% - 00:00:00.000 - <filterEmptyTables>

Mar 18, 2022 11:08:08 AM schemacrawler.loader.weakassociations.WeakAssociationsCatalogLoader loadCatalog
INFO: Finding weak associations
Mar 18, 2022 11:08:08 AM schemacrawler.loader.weakassociations.WeakAssociationsCatalogLoader lambda$loadCatalog$0
INFO: Not retrieving weak associations, since this was not requested
Mar 18, 2022 11:08:08 AM schemacrawler.loader.weakassociations.WeakAssociationsCatalogLoader loadCatalog
INFO: Total time taken for <loadWeakAssociations> - 00:00:00.000 hours
-  0.0% - 00:00:00.000 - <retrieveWeakAssociations>

Schema: H2_DB1
Schema: H2_DB2
Schema: metadata