JCatalog

An example of a jcatalog file is printed below. This is an example output using the jdbcExplorer embedded in CaseTalk. These jCatalog files can be stored in your roaming profile (AppData) to be used by CaseTalk next time you open the Data Explorer.

These are automatically created by CaseTalk when configuring a JDBC Connection. But can also be created manually by your database administrator by running the following commandline:

 java -cp "jdbcMetaData.jar;<your-jdbc-driver.jar>" com.casetalk.JdbcMetaData -c"connectionstring" -u"username" -p"password" -s"schema"

With a similar commandline the JDBC driver can be used to query the database as well.

 java -cp "jdbcMetaData.jar;<your-jdbc-driver.jar>" com.casetalk.JdbcMetaData -c"connectionstring" -u"username" -p"password" -s"schema" [select statement]

Naturally, the offline metadata results cannot be queried for actual data, but it can be used in documenting offline catalog information or use it for reverse engineering and lineage mapping, through drag and drop.

The content of the jCatalog file (as generated by the JdbcExplorer) displayed above is:

 {
   "connection": {
       "catalogs": [{
           "name": "marcowobben",
           "schemas": [
               {
                   "name": "public",
                   "tables": [
                       {
                           "columns": [
                               {
                                   "autoinc": "YES",
                                   "datatype": "int4",
                                   "name": "contact_id",
                                   "nullable": "NO",
                                   "position": "1",
                                   "precision": "10",
                                   "size": "10"
                               },
                               {
                                   "datatype": "int4",
                                   "name": "customer_id",
                                   "nullable": "YES",
                                   "position": "2",
                                   "precision": "10",
                                   "size": "10"
                               },
                               {
                                   "datatype": "varchar",
                                   "name": "contact_name",
                                   "nullable": "NO",
                                   "position": "3",
                                   "precision": "10",
                                   "size": "255"
                               },
                               {
                                   "datatype": "varchar",
                                   "name": "phone",
                                   "nullable": "YES",
                                   "position": "4",
                                   "precision": "10",
                                   "size": "15"
                               },
                               {
                                   "datatype": "varchar",
                                   "name": "email",
                                   "nullable": "YES",
                                   "position": "5",
                                   "precision": "10",
                                   "size": "100"
                               }
                           ],
                           "foreignkeys": [{
                               "columns": [{
                                   "column": "customer_id",
                                   "position": "1",
                                   "ref_column": "customer_id"
                               }],
                               "name": "fk_customer",
                               "ref_table": "customers"
                           }],
                           "name": "contacts",
                           "primarykey": {
                               "columns": [{
                                   "column": "contact_id",
                                   "position": "1"
                               }],
                               "name": "contacts_pkey"
                           }
                       },
                       {
                           "columns": [
                               {
                                   "autoinc": "YES",
                                   "datatype": "int4",
                                   "name": "customer_id",
                                   "nullable": "NO",
                                   "position": "1",
                                   "precision": "10",
                                   "size": "10"
                               },
                               {
                                   "datatype": "varchar",
                                   "name": "customer_name",
                                   "nullable": "NO",
                                   "position": "2",
                                   "precision": "10",
                                   "size": "255"
                               }
                           ],
                           "name": "customers",
                           "primarykey": {
                               "columns": [{
                                   "column": "customer_id",
                                   "position": "1"
                               }],
                               "name": "customers_pkey"
                           }
                       }
                   ]
               }
           ]
       }],
       "string": "jdbc:postgresql://192.168.1.106:5432/marcowobben"
   },
   "createdon": "2023-04-25T11:27:34.795546700Z",
   "user": "admin"
 }

Manual

If crafting such a file by hand, or automated, some details can be skipped leading to a slimmer jcatalog file:

 {
   "connection": {
       "catalogs": [{
           "name": "marcowobben",
           "schemas": [
               {
                   "name": "public",
                   "tables": [
                       {
                           "columns": [
                               {
                                   "name": "contact_id"
                               },
                               {
                                   "name": "customer_id"
                               },
                               {
                                   "name": "contact_name"
                               },
                               {
                                   "name": "phone"
                               },
                               {
                                   "name": "email"
                               }
                           ],
                           "name": "contacts",
                           "primarykey": {
                               "columns": [{
                                   "column": "contact_id",
                                   "position": "1"
                               }],
                               "name": "contacts_pkey"
                           }
                       },
                       {
                           "columns": [
                               {
                                   "name": "customer_id"
                               },
                               {
                                   "name": "customer_name"
                               }
                           ],
                           "name": "customers",
                           "primarykey": {
                               "columns": [{
                                   "column": "customer_id",
                                   "position": "1"
                               }],
                               "name": "customers_pkey"
                           }
                       }
                   ]
               }
           ]
       }]
   }
 }