getDbCatalogName() not working on my command on my locally installed PostgreSQL 9.6

[adriens]

Context

To achieve https://github.com/adriens/schemacrawler-additional-command-lints-as-csv/issues/9 I need to get the database name from my command.

Details

I have merged https://github.com/adriens/schemacrawler-additional-command-lints-as-csv/pull/20/files but the catalog remains empty. In fact it seems like the serverInfoMap map remains empty.

Function source code

private String getDbCatalogName()
  {
    String dbCatalogName = "";
    Map<String, String> serverInfoMap = new HashMap<>();
    for (Property serverInfoProperty: catalog.getDatabaseInfo().getServerInfo())
    {
      serverInfoMap.put(serverInfoProperty.getName(),
                        serverInfoProperty.getValue().toString());
      System.err.println("Key/Value : <" + serverInfoProperty.getName() + "/" + serverInfoProperty.getValue().toString() + ">");

    }
    System.err.println("Map size : <" + serverInfoMap.size() + ">");
    LOGGER.info("MAP : " + serverInfoMap.toString());

    if (serverInfoMap.containsKey("SERVICE_NAME"))
    {
      // Oracle SERVICE_NAME
      dbCatalogName = serverInfoMap.get("SERVICE_NAME");
    }
    else if (serverInfoMap.containsKey("current_database"))
    {
      // PostgreSQL SERVICE_NAME
      dbCatalogName = serverInfoMap.get("current_database");
    }
    else if (serverInfoMap.containsKey("ServerName"))
    {
      // Microsoft SQL Server ServerName
      dbCatalogName = serverInfoMap.get("ServerName");
    }
    else if (serverInfoMap.containsKey("CATALOG_NAME"))
    {
      // HyperSQL (HSQLDB) CATALOG_NAME
      dbCatalogName = serverInfoMap.get("CATALOG_NAME");
    }     

    return dbCatalogName;
  }

Steps to reproduce

Compile the current version of master of https://github.com/adriens/schemacrawler-additional-command-lints-as-csv Drop the produced jar in SCHEMACRAWLER_HOME/lib Run a csv command on you local psql instance (9.6 for me actually) :

schemacrawler -server=postgresql -user=postgres -database=sc_lint_test -user=postgres -password=XXXXXX -c=csv -loglevel=CONFIG

Next, take a look at the logs, the Map remains empty :

dÚc. 13, 2018 3:03:41 PM com.github.adriens.AdditionalCommand getDbCatalogName
INFOS: MAP : {}

... hence cannot get the catalogname :

String dbCatalogName = getDbCatalogName();
    LOGGER.log(Level.INFO, String.format("DATABASE NAME : <%s>", dbCatalogName));

[adriens]

Looks like something is not loaded while called from a new command

[sualeh]

@adriens - what -infolevel are you using?

[sualeh]

Also, please could you trace with -loglevel=ALL

[adriens]

With the following command options :

schemacrawler -server=postgresql -user=postgres -database=sc_lint_test -user=postgres -password=XXXXXX-c=csv -loglevel=INFO -infolevel=maximum

the Map has one element :

INFOS: Got input dbId : <>
dÚc. 15, 2018 6:34:48 PM com.github.adriens.AdditionalCommand execute
INFOS: Got input dbEnv : <>
Key/Value : <current_database/sc_lint_test>
Map size : <1>
dÚc. 15, 2018 6:34:48 PM com.github.adriens.AdditionalCommand getDbCatalogName
INFOS: MAP : {current_database=sc_lint_test}

And I can get the dbName. Althoug, I've upgraded Schemacrawler version to its latest version. It seems like it does find the db Key !

[adriens]

... but when I switch back to this loglevel :

-loglevel=INFO

I don"t have the Map feeded !

dÚc. 15, 2018 6:40:09 PM com.github.adriens.AdditionalCommand getDbCatalogName
INFOS: MAP : {}
dÚc. 15, 2018 6:40:09 PM com.github.adriens.AdditionalCommand execute
INFOS: DATABASE NAME : <>

[adriens]

This is really strange ❗️

[sualeh]

I have no solution for this, @adriens !

[adriens]

Could you reproduce it ?

[sualeh]

No. Have you checked your code in?

[adriens]

It's the -infolevel=maximum that does somehting that makes it work.

[adriens]

Did you try to apply my protocol ? There really is something with the infolevel. I've browsed your core code but I could find anything related to this issue.

[adriens]

😢

[adriens]

here is a sample of tests on infolevel :

• minimum => Map not retrieved
• standard => Map not retrieved
• detailed => Map not retrieved
• maximum => Map retrieved 👍

[adriens]

... looks like catalog.getDatabaseInfo().getServerInfo() is not retrieved on lower infolevel ...

[adriens]

Wouldn't you for example switch on the infolevel like this piece of code, but on the Map I need ?

 if (infoLevel.isRetrieveUserDefinedColumnDataTypes())
        {
          LOGGER.log(Level.INFO, "Retrieving user column data types");
          for (final Schema schema: retriever.getAllSchemas())
          {
            retriever.retrieveUserDefinedColumnDataTypes(schema);
          }
}```

[adriens]

i'm looking at this kind of code :

 if (infoLevel.isRetrieveAdditionalDatabaseInfo())
        {
          retriever.retrieveAdditionalDatabaseInfo();
        }
        else
        {
          LOGGER
            .log(Level.INFO,
                 "Not retrieving additional database information, since this was not requested");
        }
        return null;
});

[adriens]

Guess I got it !

Have a look at SchemaInfoLevelBuilder.java :

In the maximum infolevel, you have this code :

public static SchemaInfoLevelBuilder maximum()
  {
    final SchemaInfoLevelBuilder maximum = detailed();
    maximum.setRetrieveAdditionalDatabaseInfo(true);
    maximum.setRetrieveServerInfo(true);
    maximum.setRetrieveAdditionalJdbcDriverInfo(true);
    maximum.setRetrieveTablePrivileges(true);
    maximum.setRetrieveTableColumnPrivileges(true);
    maximum.setRetrieveTableDefinitionsInformation(true);
    maximum.setRetrieveForeignKeyDefinitions(true);
    maximum.setRetrievePrimaryKeyDefinitions(true);
    maximum.setRetrieveAdditionalTableAttributes(true);
    maximum.setRetrieveAdditionalColumnAttributes(true);
    maximum.setRetrieveIndexInformation(true);
    maximum.setRetrieveIndexColumnInformation(true);
    maximum.setRetrieveSequenceInformation(true);
    maximum.setRetrieveSynonymInformation(true);
maximum.setTag("maximum");

See the line that does the job :

maximum.setRetrieveAdditionalJdbcDriverInfo(true);

... but in the standard :

public static SchemaInfoLevelBuilder standard()
  {
    final SchemaInfoLevelBuilder standard = minimum();
    standard.setRetrieveColumnDataTypes(true);
    standard.setRetrieveTableColumns(true);
    standard.setRetrieveForeignKeys(true);
    standard.setRetrieveIndexes(true);
    standard.setRetrieveRoutineColumns(true);
    standard.setTag("standard");
    return standard;
}

See that the additional code below is missing :

maximum.setRetrieveAdditionalJdbcDriverInfo(true);

[adriens]

See my code proposal : https://github.com/adriens/SchemaCrawler/pull/1

[adriens]

... or I should upgrade the infolevel from the code... but from my understanding it would make sense to have these datas whatever the infolevel : what's your opininion about that ❔ 🙏

[adriens]

If you think it's better, you can make me a PR where the infolvel is set to maximum so I don't have to ask the end-user to set-it up by himself ?

[sualeh]

See https://github.com/adriens/schemacrawler-additional-command-lints-as-csv/pull/22