初试 SchemaSpy


SchemaSpy 是一个数据库图形化的工具,感觉还挺有意思。用它的初衷是画数据库实验的 ER-model 图。

原网址: SchemaSpy-没错,说的就是我

我在这里使用的是 postgres 数据库,好在属于 SchemaSpy 的支持范围内。

说实话,这网站做的有点难看,我找了半天没找到下载在哪里,最好拿浏览器查找一下 “download”。下好 schemaSpy_5.0.0.jar之后:

$ java -jar schemaSpy_5.0.0.jar -t pgsql -db hos -u Lucius -o Downloads

会报错,看报错原因,是因为 postgres 需要指明端口。

再来。

$ java -jar schemaSpy_5.0.0.jar -t pgsql -db hos -u Lucius -o Downloads -host localhost:5432

继续报错。

Failed to query Graphviz version information
  with: dot -V
  java.io.IOException: Cannot run program "dot": error=2, No such file or directory
Using database properties:
  [schemaSpy_5.0.0.jar]/net/sourceforge/schemaspy/dbTypes/pgsql.properties
java.lang.ClassNotFoundException: org.postgresql.Driver

Failed to load driver 'org.postgresql.Driver'
This entry does not point to a valid file/directory: [/whereever/postgresql-8.0-312.jdbc3.jar]

Use the -dp option to specify the location of the database
drivers for your database (usually in a .jar or .zip/.Z).

原来还没有指定驱动路径。

Postgres JDBC官网 下一个驱动,在命令参数 -dp 后面加上驱动的地址。

$ java -jar schemaSpy_5.0.0.jar -t pgsql -db hos -u Lucius -o Downloads -host localhost:5432 -dp ~/Downloads/postgresql-9.4.1208.jre6.jar

还是没有得到我们想要的结果,看看报错信息。

Using database properties:
  [schemaSpy_5.0.0.jar]/net/sourceforge/schemaspy/dbTypes/pgsql.properties
Gathering schema details...

No tables or views were found in schema 'Lucius'.
The schema does not exist in the database.
Make sure that you specify a valid schema with the -s option and that
  the user specified (Lucius) can read from the schema.
Note that schema names are usually case sensitive.

3 schemas exist in this database.
Some of these "schemas" may be users or system schemas.

information_schema pg_catalog public
These schemas contain tables/views that user 'Lucius' can see:

 information_schema pg_catalog public%

Schema 是什么?


question

数据库中的Schema,为数据库对象的集合,一个用户一般对应一个schema。

官方定义如下: A schema is a collection of database objects (used by a user.).

schema objects are the logical structures that directly refer to the database’s data.

A user is a name defined in the database that can connect to and access objects.

schemas and users help database administrators manage database security.

从定义中我们可以看出schema为数据库对象的集合,为了区分各个集合,我们需要给这个集合起个名字,这些名字就是我们在企业管理器的方案下看到的许多类似用户名的节点,这些类似用户名的节点其实就是一个schema,schema里面包含了各种对象如tables views sequences stored procedures synonyms indexes clusters and database links。

所以这个点我们应该把 schema 改成 public,放在 -s 参数后面,而不是 user 默认的 Lucius。

对了,需要画 ER—model diagram 的话需要安装 graphviz。 mac 用户直接

$ brew install graphviz 

现在配置就搞定了。

$ java -jar schemaSpy_5.0.0.jar -t pgsql -db hos -u Lucius -o Downloads -host localhost:5432 -dp ~/Downloads/postgresql-9.4.1208.jre6.jar -s public
Using database properties:
  [schemaSpy_5.0.0.jar]/net/sourceforge/schemaspy/dbTypes/pgsql.properties
Gathering schema details.....................(1sec)
Writing/graphing summary............(0sec)
Writing/diagramming details..................(2sec)
Wrote relationship details of 18 tables/views to directory 'Downloads' in 4 seconds.
View the results by opening Downloads/index.html

看看效果。

colomn

table

ER

还不错吧,更多的功能以后慢慢探索。