Configuration

Command-Line Arguments

Most of the command-line arguments can be specified in a properties file either with the default name schemaspy.properties or in a file specified using -configFile the command-line arguments should be prefixed with schemaspy. As an example -sso would be schemaspy.sso and -u username would be schemaspy.u=username.

General

[-h]

Print help message

[-dbhelp]

Print databaseType required arguments

[-configFile filePath]

Path to configFile to be used, default is to look for schemaspy.properties

[-o outputDirectory]

Directory to write the generated HTML/graphs to

Database related

Connecting

[-t databaseType]

Type of database (e.g. ora, db2, etc.). Use -dbhelp for a list of built-in types. Defaults to ora.

[-db dbName]

Name of database to connect to.

[-host hostName]

Hostname/ip to connect to, if required by databaseType.

[-port portNumber]

Port that dbms listens to, if required by databaseType.

[-u user]

Valid database user id with read access. A user id is required unless -sso is specified.

[-p password]

Password associated with that user. Defaults to no password.

[-sso]

Single sign-on, used when -u and -p should be ignored.

[-pfp]

Prompt for password, if you don’t want to have password in command history.

[-connprops filePathOrKeyValue]

Either a properties-file with additional properties or a key/value list, pairs separated by ; and key and value separated by \\= example -connprops key1\\=value1;key2\\=value2 see also Supply Connection-properties. ConnectionProperties will always be populated with -u and -p if they exist.

[-dp pathToDrivers]

Looks for drivers here before looking in driverPath in [databaseType].properties. The drivers are usually contained in .jar or .zip files and are typically provided by your database vendor. Multiple jars can be specified using os-specific path separator.

[-loadjars]

Load siblings to jar specified in -dp, only works for single jar entry in -dp

Processing

[-cat catalog]

Filter using a specific catalog this is usually the root of the database and contains schemas.

[-s schema]

Database schema. This is optional if it’s the same as user or isn’t supported by your database. Use -noschema if your database thinks it supports schemas but doesn’t (e.g. older versions of Informix).

[-schemas listOfSchemas]

List of schemas to analyze, separated by space or , or ' or "

[-all]

Try to analyze all schemas in database, schemas can be excluded with -schemSpec which as defaults set by databaseType

[-schemaspec schemaRegEx]

Schemas to analyze, default to all, might be specified by databaseType.

[-dbthreads number]

Specify how many threads/connections should be used when reading data from database, defaults to 15 or as specified by databaseType

[-norows]

Skip fetching number of rows in tables.

[-noviews]

Skip processing of views.

[-i includeTableRegex]

Include table(s) in analysis, defaults to match everything

[-I excludeTableRegex]

Exclude table(s) from analysis, defaults to exclude tables containing $, can be overridden with -I ""

Additional data

[-meta fileOrPath]

Single schema analysis file path to SchemaMeta-xml, when running -all or -schemas path to directory containing SchemaMeta-xmls with pattern (DatabaseName|Schema).meta.xml

Html report related

[-nohtml]

Skip generation of html report.

[-noimplied]

Don’t look for implied relationships.

[-nopages]

Just list data as one long list instead of pages.

[-rails]

Use Rails-based naming convention to find relationships between logical foreign keys and primary keys.

[-template path]

Path to custom mustache template/css directory, needs to contain full set of templates. Bundled templates can be found in jar ‘/layout’ and can be extracted with jar tool or any zip capable tool.

[-maxdet number]

Limit for when tables should be shown with details.

[-css fileName]

Use a custom stylesheet. Bundled stylesheet can be extracted from jar(using zip capable tool), path ‘/layout/schemaSpy.css’

[-desc description]

Add a description to the index page.

Diagram related

[-gv directoryPath]

Path to directory containing graphviz executable(dot).

[-renderer :rendererName]

Specify which renderer to use should be prefixed with ‘:’ example -renderer :cairo

[-hq] or [-lq]

Generate higher or lower-quality diagrams. Various installations of Graphviz (depending on OS and/or version) will default to generat /ing either higher or lower quality images. That is, some might not have the “lower quality” libraries and others might not have the “higher quality” libraries. Higher quality output takes longer to generate and results in significantly larger image files (which take longer to download / display), but the resultant Entity Relationship diagrams generally look better.

[-imageformat outputImageFormat]

The format of the image that gets generated. Supported formats are svg and png. Defaults to png. E.g. -imageformat svg

[-maxdet number]

Limit for when tables shouldn’t be detailed. Evaluated against total number of tables in schema. Defaults to 300.

[-font fontName]

Change font used in diagrams, defaults to ‘Helvetica’

[-fontsize number]

Change font size in large diagrams, defaults to 11

[-rankdirbug]

Switch diagram direction from ‘top to bottom’ to ‘right to left’

[-X excludeColumnRegex]

Exclude column(s), regular expression to exclude column(s) from diagrams, defaults to nothing.

[-x excludeIndirectColumnsRegex]

Exclude column(s) from diagrams where column(s) aren’t directly referenced by focal table, defaults to nothing.

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] the .properties will be added if missing. So if you create one, be sure to have .properties extension.

Example:

-t mysql

or

-t mysql.properties

The search order is:

1. user.dir/
2. Classpath
3. Classpath in schemaspy supplied location

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

It will look for:

1. file: $user.dir/my_conf/mydbtype.properties
2. Classpath: my_conf/mydbtype.properties
3. 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

selectIndexesSql=

INDEX_NAME, TYPE, NON_UNIQUE, COLUMN_NAME, ASC_OR_DESC

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 (text has been deprecated)

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

Define viewTypes

viewTypes=

default is VIEW

SchemaMeta

Is a way to modify input that will affect output from SchemaSpy.

• Add comments/remarks
• Add relationships
• Add remote tables
• Add columns
• Exclude columns from implied relationships
• Exclude columns from diagrams

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. Supports markdown, example see Add markdown comments using additional metadata

<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

<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.

<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

<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

<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

<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>