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:
user.dir/
Classpath
Classpath in schemaspy supplied location
This actually means that if you supply -t my_conf/mydbtype
- It will look for:
file: $user.dir/my_conf/mydbtype.properties
Classpath: my_conf/mydbtype.properties
Classpath: org/schemaspy/types/my_conf/mydbtype.properties
Layout
It can contain wast amounts of properties so we will break it down. The Properties-file can contain instructions.
- extends
extends
which does what it 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:
- dbms=
Database Management System should general without version, used for grouping
- description=
Description for this specific databaseType (mostly used in logging) without dbms
- 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
)- tableTypes=
Which types should be considered tables, default is TABLE
- viewTypes=
Which types should be considered views, default is VIEW
- multirowdata=
If rows with same keys/ids should have it’s data appended to the first result, default is false
Sql query instead of DatabaseMetaData
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 overrides:
- selectSchemasSql=
Fetch comments for a schema, expected columns:
schema_comment- selectCatalogsSql=
Fetch comments for a catalog, expected columns:
catalog_comment- selectTablesSql=
Fetch tables, expected columns:
table_name, table_catalog, table_schema, table_comment, table_rows- selectViewsSql=
Fetch views, expected columns:
view_name, view_catalog, view_schema, view_comment, view_definition- selectIndexesSql=
Fetch indexes, expected columns:
INDEX_NAME, TYPE, NON_UNIQUE, COLUMN_NAME, ASC_OR_DESC- selectPrimaryKeysSql=
Fetch table PKs, expected columns:
TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, KEY_SEQ, PK_NAME- selectRowCountSql=
Fetch row count for a table, expected columns:
row_count- selectColumnTypesSql=
Fetch column type for all columns, expected columns:
table_name, column_name, column_type, short_column_type- selectRoutinesSql=
Fetch routines, expected columns:
routine_name, routine_type, dtd_identifier, routine_body, routine_definition,sql_data_access, security_type, is_deterministic, routine_comment- selectRoutineParametersSql=
Fetch parameters for routines, expected columns:
specific_name, parameter_name, dtd_identifier, parameter_mode- selectViewSql=
Fetch definition for a view, expected columns:
view_definition, text (text has been deprecated)- selectViewCommentsSql=
Fetch comments for all views, expected columns:
view_name, comments- selectViewColumnCommentsSql=
Fetch column comments for all views, expected columns:
view_name|table_name, column_name, comments- selectCheckConstraintsSql=
Fetch check constraints for all tables, expected columns:
table_name, constraint_name, text- selectTableIdsSql=
Fetch ids for all tables, expected columns:
table_name, table_id- selectIndexIdsSql=
Fetch ids for all indexes, expected columns:
table_name, index_name, index_id- selectTableCommentsSql=
Fetch comments for all tables, expected columns:
table_name, comments- selectColumnCommentsSql=
Fetch comments for all columns, expected columns:
table_name, column_name, comments- selectSequencesSql=
Fetch all sequences from the database, expected columns:
sequence_name, start_value, increment
start_value and increment defaults to 1 if missing
Included
Dbms |
Description |
Argument -t |
---|---|---|
Amazon Redshift |
Standard |
redshift |
Apache Derby |
Embedded Server |
derby |
Network Server |
derbynet |
|
Apache Hive |
Keytab support |
hive-kerberos-driverwrapper |
Keytab support, zookeeper |
hive-kerberos-driverwrapper-zookeeper |
|
Standard |
hive |
|
Apache Impala |
Standard |
impala |
ClickHouse |
Standard |
clickhouse |
Firebird |
Standard |
firebird |
Force |
Standard |
force |
H2 |
Server 2.0 |
h2-2 |
Server 1.0 |
h2 |
|
HSQLDB |
Server |
hsqldb |
IBM DB2 |
‘App’ Driver |
db2 |
i (former as400) driver |
db2i |
|
z/OS with the ‘App’ Driver |
db2zos |
|
z/OS Type 4 ‘Net’ Driver |
db2zosnet |
|
UDB Type 4 Driver |
udbt4 |
|
Type 4 ‘Net’ Driver |
db2net |
|
IBM Informix |
Standard |
informix |
IBM Netezza |
Standard |
netezza |
MariaDB |
Standard |
mariadb |
Microsoft SQL Server |
jTDS 2008+ instance |
mssql08-jtds-instance |
jTDS 2017+ |
mssql17-jtds |
|
jTDS 2000+ |
mssql-jtds |
|
jTDS 2005+ instance |
mssql05-jtds-instance |
|
jTDS 2000+ instance |
mssql-jtds-instance |
|
2005+ |
mssql05 |
|
2000+ |
mssql |
|
jTDS 2008+ |
mssql08-jtds |
|
2017+ |
mssql17 |
|
2008+ |
mssql08 |
|
jTDS 2005+ |
mssql05-jtds |
|
MySQL |
Unix Socket |
mysql-socket |
TCP/IP |
mysql |
|
Oracle |
Thin Driver, service |
orathin-service |
OCI8 Driver |
ora |
|
Thin Driver |
orathin |
|
PostgreSQL |
11 or later |
pgsql11 |
Before Version 11 |
pgsql |
|
SAP MaxDB |
Standard |
maxdb |
SQLite |
SQLite |
sqlite |
Xerial |
sqlite-xerial |
|
Snowflake |
Standard |
snowflake |
Sybase |
Server (jdbc3) |
sybase |
Server (jdbc2) |
sybase2 |
|
Server (jdbc4) |
sybase3 |
|
Teradata |
Standard |
teradata |