（一）SchemaSpy
官方网站：http://schemaspy.sourceforge.net/
该软件名称的官方解释为：Graphical Database Schema Metadata Browser
- SchemaSpy_5.0.0.jar（原版）
- SchemaSpy_5.0.0_mod.jar（我的格式修改版）
- SchemaSpy.mht（帮助文档）
- jtds-1.2.5.jar（最新 强大、开源、纯java的 sqlserverJDBC驱动）
- jd-gui.exe （java class 反编译器）
- __SchemaSpy.cmd （我自己写的 启动器，加入 -charset UTF-8 参数）
- Dot.java（修改版的）
（二）常见问题及解决（不断补充中）
1、中文支持问题
该软件有个隐藏参数：-charset UTF-8
命令选项中加入该参数即可解决文档的乱码问题，但不能解决 Graphviz 图表的中文问题
2、 软件所依赖的Graphviz版本问题
该软件运行必须依赖于Graphviz，主要是依赖其中的Dot命令。
官方的要求是 Graphviz 2.2.1 或 2.4+，否则会出现Dot命令生成图表失败的问题。原因是SchemaSpy自作聪明的判断引擎对默认PNG质量的格式控制，使得内部调用Dot参数 -Tpng命令后添加了质量修饰符":gd"等参数，从而和Graphviz的支持格式不一致。
修改：最简单的方式是：修改 net.sourceforge.schemaspy.util 下Dot.class 文件。修改反编译后的源文件的 generateDiagram 方法的调用参数：5.0的版本为：
String[] arrayOfString = { getExe(), "-T" + getFormat() + getRenderer(), paramFile1.toString(), "-o" + paramFile2, "-Tcmapx" };
修改为
/* 254 */ String[] arrayOfString = { getExe(), "-Tjpeg", paramFile1.toString(), "-o" + paramFile2, "-Tcmapx" };
注意：该处我根本就不用png格式，而是jpeg格式！！！！
3、和“2”相关的问题，因为该软件的最新版本5.0看起来也已经很老了，所有很多的代码和配置不一定适合最新的jdk、db系统，所以，你知道的.......慢慢改呗！
（三）支持的数据库类型
| Type | Description |
|---|---|
| db2 | IBM DB2 with 'app' Driver |
| db2net | IBM DB2 with 'net' Driver |
| udbt4 | DB2 UDB Type 4 Driver |
| db2zos | DB2 for z/OS |
| derby | Derby (JavaDB) Embedded Server |
| derbynet | Derby (JavaDB) Network Server |
| firebird | Firebird |
| hsqldb | HSQLDB Server |
| informix | Informix |
| maxdb | MaxDB |
| mssql | Microsoft SQL Server |
| mssql05 | Microsoft SQL Server 2005 |
| mssql-jtds | Microsoft SQL Server with jTDS Driver |
| mssql05-jtds | Microsoft SQL Server 2005 with jTDS Driver |
| mysql | MySQL |
| ora | Oracle with OCI8 Driver |
| orathin | Oracle with Thin Driver |
| pgsql | PostgreSQL |
| sqlite | SQLite |
| sybase | Sybase Server with JDBC3 Driver |
| sybase2 | Sybase Server with JDBC2 Driver |
| teradata | Teradata (requires -connprops) |
基本使用语法
Commonly used parameters:
| Parameter | Description | |
|---|---|---|
-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 |
| * | -u user |
Valid database user id with read access. A user id is required unless -sso is specified. |
-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). |
|
-p password |
Password associated with that user. Defaults to no password. | |
| * | -ooutputDirectory |
Directory to write the generated HTML/graphs to |
-dppathToDrivers |
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. | |
-hq |
Generate either higher or lower-quality diagrams. Various installations of Graphviz (depending on OS and/or version) will default to generating 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. |
Parameters marked with '*' are required.
Less commonly used parameters:
-gv pathToGraphviz |
By default SchemaSpy expects the dot executable to be in the PATH environment variable. Use this option to explicitly specify where Graphviz is installed. |
-desc "Schema description" |
Displays the specified textual description on summary pages. If your description includes an equals sign then escape it with a backslash. For example: -desc "<a href\='http://schemaspy.sourceforge.net'>SchemaSpy</a>". |
-all |
Evaluate all schemas in a database. Generates a high-level index of the schemas evaluated and allows for traversal of cross-schema foreign key relationships. Use with -schemaSpec "schemaRegularExpression" to narrow-down the schemas to include. |
-schemas"schema1,schema2" |
Evaluate specified schemas. Similar to -all, but explicitly specifies which schema to evaluate without interrogating the database's metadata. Can be used with databases like MySQL where a database isn't composed of multiple schemas. |
-meta metafile |
metafile is either the name of an individual XML file or the directory that contains meta files. If a directory is specified then it is expected to contain files matching the pattern [schema].meta.xml.For databases that don't have schema substitute database for schema. See Providing Additional Metadata for details. |
-connpropspropsfile orkey\=value; |
Specifies additional properties to be used when connecting to the database. Either specify a .properties file (with key=value entries) or specify the entries directly, escaping the ='s with \= and separating each key\=value pair with a ;. |
-i"tableNamesRegex" |
Only include matching tables/views. This is a regular expression that's used to determine which tables/views to include. For example: -i "(.*book.*)|(library.*)" includes only those tables/views with 'book' in their names or that start with 'library'.You might want to use -desc with this option to describe the subset of tables. |
-I"tableNamesRegex" |
Exclude matching tables/views. This regular expression excludes matching tables/views from the analysis. Can be used in conjunction with -i. |
-x"columnNamesRegex" |
Exclude matching columns from relationship analysis to simplify the generated graphs. This is a regular expression that's used to determine which columns to exclude. It must match table name, followed by a dot, followed by column name. For example: -x "(book.isbn)|(borrower.address)"Note that each column name regular expression must be surround by ()'s and separated from other column names by a |. Excluded relationships will still show up on detail pages. |
-X"columnNamesRegex" |
Same as -x but excluded relationships will not show up on detail pages. |
-noviews |
Exclude all views. |
-ahic |
Allow HTML In Comments. Any HTML embedded in comments normally gets encoded so that it's rendered as text. This option allows it to be rendered as HTML. |
-norows |
Don't query or display row counts. |
-noimplied |
Don't include implied foreign key relationships in the generated table details. |
-sso |
Single Sign-On. Don't require a user to be specified with -u to simplify configuration when running in a single sign-on environment. |
-pfp |
Prompt For Password. Prompts for the password so it doesn't appear on the command line. |
-nohtml |
Only generate files needed for insertion/deletion of data (e.g. for scripts) and an XML representation of the schema. |
-loglevel |
Specifies how verbose logging of programmatic flow should be. The levels in descending order are:
|
（四）示例
You run SchemaSpy from the command line:
java -jar schemaSpy.jar -t dbType -db dbName [-s schema] -u user [-p password] -o outputDir |
A MySQL example:
java -jar schemaSpy.jar -t mysql -o library -host localhost -db library -u user -p password |
An MS SQL Server example:
java -jar schemaSpy.jar -t mssql -db library -host localhost -port 1433 -u user -p password -o library |
我的使用jtds驱动的 MS Sqlserver 实例
java -jar schemaSpy_5.0.0_mod.jar -charset UTF-8 -t mssql05-jtds -dp .\jtds-1.2.5.jar -db %db% -host localhost -port 1433 -u sa -p sa -s dbo -o %db%