Configuration

DatabaseType

You can create you’re own databaseType so lets go through how it works.

Selection

On the commandline you specify the databaseType using the option -t. The option can be specified with either [name].properties or just [name]

Example:
-t mysql
or
-t mysql.properties
The search order is:
  1. user.dir/ as supplied
  2. user.dir/ as supplied, suffixed with .properties
  3. Classpath as supplied
  4. Classpath as supplied, suffixed with .properties
  5. Classpath in schemaspy supplied location as supplied
  6. Classpath in schemaspy supplied location as supplied, suffixed with .properties

This actually means that if you supply -t my_conf/mydbtype

It will look for:
  1. file: $user.dir/my_conf/mydbtype
  2. file: $user.dir/my_conf/mydbtype.properties
  3. Classpath: my_conf/mydbtype
  4. Classpath: my_conf/mydbtype.properties
  5. Classpath: org/schemaspy/types/my_conf/mydbtype
  6. Classpath: org/schemaspy/types/my_conf/mydbtype.properties

Layout

It can contain wast amount of properties so we will break it down. The Properties-file can contain instructions.

extends

extends which does what i means, it allows one to override or add properties to an existing databaseType (by specifying a parent/base)

As an example:

extends=mysql

which you can see in mysql-socket.properties

include

include.[n] is a bit different it allows one to add a single property from another databaseType. [n] is substituted for a number. The value has the form of [databaseType]::[key].

As an example:

include.1=mysql::schemaSpec

This would have been valid in the mariadb.properties

Then we have required properties:

description=
Description for the databaseType (mostly used in logging)
connectionSpec=
We will talk more about this one. It’s the connectionUrl used, but it supports token replacement
driver=
FQDN of the JDBC driver as an example org.h2.Driver

ConnectionSpec

Let’s dive a bit deeper into the connectionSpec.

As an example from mysql-socket:

extends=mysql
connectionSpec=jdbc:mysql://<host>/<db>?socketFactory=<socketFactory>&socket=<socket>
socketFactory=ClassName of socket factory which must be in your classpath
socket=Path To Socket

We mentioned extends earlier.
ConnectionSpec contains the connectionUrl used with the jdbc driver, some might refer to it as the connectionString.

connectionSpec allow token replacement, a token is <[tokenName]>.
In the above example we have host, db, socketFactory, socket.

This means that when used it expects the following commandline arguments:

-h [host] (for host)
-db [dbname] (for db)
-socketFactory [socketFactory class]
-socket [path to socket]

host and db are already known, but -socketFactory and -socket has become a new commandline argument. The presence of the keys in the databaseType properties file is only for description, it’s printed when -dbhelp is used as a commandline argument. (db and host located in databaseType mysql which is extended)

There is also a synthetic token that can be replaced <hostOptionalPort> which combines host and port if port is supplied.
Default separator is : but can be changed by specifying another under the key hostPortSeparator

Other Properties

driverPath=
path to classpath resources that will be used when trying to create the jdbc Driver in java same as commandline argument -dp
dbThreads=
number of threads that can be used to analyze the database
schemaSpec=
regular expression used in conjunction with -all (and can be command line param -schemaSpec)

When metadata in JDBC isn’t cutting the mustard. You can replace it with a sql query. They are prepared and supports named parameters as long as they are available. Data is retrieved by column label. So additional columns are ok, but you might need to alias columns so that they are returned correctly to schemaspy.

:dbname
DatabaseName -db
:schema
Schema -s
:owner
alias for :schema
:table
table that the query relates to (think selectRowCountSql)
:view
alias for :table
:catalog
Catalog -cat
Possible Metadata overrides and expected columns in result:
selectSchemasSql=
schema_comment
selectCatalogsSql=
catalog_comment
selectTablesSql=
table_name, table_catalog, table_schema, table_comment, table_rows
selectViewsSql=
view_name, view_catalog, view_schema, view_comment, view_definition
selectRowCountSql=
row_count
selectColumnTypesSql=
table_name, column_name, column_type, short_column_type
selectRoutinesSql=
routine_name, routine_type, dtd_identifier, routine_body, routine_definition,sql_data_access, security_type, is_deterministic, routine_comment
selectRoutineParametersSql=
specific_name, parameter_name, dtd_identifier, parameter_mode
selectViewSql=
view_definition, text
selectCheckConstraintsSql=
table_name, constraint_name
selectTableIdsSql=
table_name, table_id
selectIndexIdsSql=
table_name, index_name, index_id
selectTableCommentsSql=
table_name, comments
selectColumnCommentsSql=
table_name, column_name, comments
Extend the types of views that exist
viewTypes=
default is VIEW

SchemaMeta

Is a way to modify the processing in and output from SchemaSpy.

All these instructions are defined in xml the schema can be found here

Schema contains documentation but lets go through the above mentioned features.

Add comments/remarks

The xsd currently allows both comments and remarks. However remarks has been deprecated.

So adding a comment will either add, if missing from database, or replace if comments/remarks exist.

1
2
3
4
5
6
7
8
<schemaMeta xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://schemaspy.org/xsd/6/schemameta.xsd" >
    <comments>Database comment</comments>
    <tables>
        <table name="ACCOUNT" comments="Table comment">
            <column name="accountId" comments="Column comment"/>
        </table>
    </tables>
</schemaMeta>

Add relationships

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
<schemaMeta xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://schemaspy.org/xsd/6/schemameta.xsd" >
    <tables>
        <table name="AGENT">
            <column name="acId" type="INT">
                <foreignKey table="ACCOUNT" column="accountId" />
            </column>
            <column name="coId" type="INT">
                <foreignKey table="COMPANY" column="companyId" />
            </column>
        </table>
    </tables>
</schemaMeta>

Add remote tables

Specifying the remoteCatalog and remoteSchema attributes on a table makes it a remote table and as such a logical table.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
<schemaMeta xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://schemaspy.org/xsd/6/schemameta.xsd" >
    <tables>
        <table name="CONTRACT" remoteCatalog="other" remoteSchema="other">
            <column name="contractId" autoUpdated="true" primaryKey="true" type="INT"/>
            <column name="accountId" type="INT">
                <foreignKey table="ACCOUNT" column="accountId"/>
            </column>
            <column name="agentId" type="INT">
                <foreignKey table="AGENT" column="aId"/>
            </column>
        </table>
    </tables>
</schemaMeta>

Add columns

1
2
3
4
5
6
7
<schemaMeta xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://schemaspy.org/xsd/6/schemameta.xsd" >
    <tables>
        <table name="ACCOUNT">
            <column name="this_is_new" type="INT" />
        </table>
    </tables>
</schemaMeta>

Exclude columns from implied relationships

Explicitly disables relationships to or from this column that may be implied by the column’s name, type and size.

Available options: to, from, all, none
Default: none

1
2
3
4
5
6
7
<schemaMeta xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://schemaspy.org/xsd/6/schemameta.xsd" >
    <tables>
        <table name="AGENT">
            <column name="accountId" type="INT" disableImpliedKeys="all"/>
        </table>
    </tables>
</schemaMeta>

Exclude columns from diagrams

Sometimes the associations displayed on a relationships diagram cause the diagram to become much more cluttered than it needs to be. Enable this setting to not show the relationships between this column and other columns.

Use exceptDirect to disable associations on all diagrams except for the diagrams of tables directly (within one degree of separation) connected to this column.

Available options: all, exceptDirect, none
Defaults: none

1
2
3
4
5
6
7
<schemaMeta xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://schemaspy.org/xsd/6/schemameta.xsd" >
    <tables>
        <table name="COUNTRY">
            <column name="countryId" type="INT" disableDiagramAssociations="all"/>
        </table>
    </tables>
</schemaMeta>