wukenaihe / db-meta

database meta crawl tool

db-meta

Db-meta is a free database schema discovery and easily tool. Db-meta now can support mysql, sql server and oracle. It is easy to extend. Db-meta's output meta is object, so it is easy to use in your project. And our library is thread safety.

Example Usage

You can download the demo and run it by yourself.db-meta-demo

Architecture

Meta data architecture

• Schema:According to the [sql-92](http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt),Catalogs are named collections of schemas in an SQL-environment. Just like "database->catalogs->schema->table", but Oracle only support schema, Mysql only support catalogs, Sql server support all of them. So Schema here is "catalog.schema".
• Constraint:Our Constraint only contain unique and check(Primary and Foreign are not in is)

soft Architecture

API

In order to improve performance, avoid to crawle useless meta ,we use schemalevel.

					              |min   | standard | max |
					--------------|------|----------|-----|
					JdbcDriverInfo| Yes  | Yes      | Yes |
					DatabaseInfo  | Yes  | Yes      | Yes |
					Table         | Yes  | Yes      | Yes |
					Column        | Yes  | Yes      | Yes |
					PrimaryKey    | Yes  | Yes      | Yes |
					Constraint    | Yes  | Yes      | Yes |
					View          | NO   | NO       | Yes |
					Index         | NO   | Yes      | Yes |
					ForeignKey    | NO   | Yes      | Yes |
					Privilege     | NO   | NO       | Yes |
					Trigger       | NO   | NO       | Yes |	

MetaLoader interface is what you need.

Method	Description
Set getTableNames()	Get table names（current Schema）
Table getTable(String tableName)	Get table（SchemaInfolevel.standard）
Table getTable(String tableName,SchemaInfoLevel schemaLevel)	Get table
Table getTable(String tableName,SchemaInfo schemaInfo)
Set getSchemaInfos()	Get current schema information
Schema getSchema()	Get current schema
Schema getSchema(SchemaInfo schemaInfo)	Get schema ,according to the SchemaInfo
Set getProcedureNames()	Get the user's procedure names
Procedure getProcedure(String procedureName)	Get the procedure information,according to the name
Map<String,Procedure> getProcedures()	Get the user's procedures
Set getTriggerNames()	Get the user's trigger names
Trigger getTrigger(String triggerName)	Get the trigger information, according to the trigger name
Map<String, Trigger> getTriggers()	Get the user's triggers
Set getFunctionNames()	Get the user's function names
Function getFunction(String name)	Get the function information, according to the name
Map<String, Function> getFunctions()	Get the user's functions
Database getDatabase()	Get all the meta data of the database(Standard)
Database getDatabase(SchemaInfoLevel level)	Get all the meta data of the database

Licenses

Copyright [2014] [XuMinhua]

Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

Remark

E-mail:[email protected]

db-meta's Issues

SqlServerMetaCrawler抛出无效异常

db-meta/src/main/java/com/cgs/db/meta/retriever/SqlServerMetaCrawler.java

代码第98行，应该是sql server，而源码是Oracle，这里是不是错了
// throw new DatabaseMetaGetMetaException("Get database(Oracle) schema information error!", e);
throw new DatabaseMetaGetMetaException("Get database(sql server) schema information error!", e);

How to write a Bugdojo issue template

Writing detailed, informative and structured issues means the world to testers.
Trust me on this one, I've worked with a lot of them!

The Bugdojo team has come up with a nice template that works well. I'd suggest you follow this template for all the issues that you wish to test in Bugdojo.

Start URL: http://helloworld.com

Steps:
1. Click open tab
2. Update details
3. Press save

Expected: Should save your details

Login: username / password (optional)

How to write a Bugdojo bot command

Congrats! You're all set up!

Hi, I'm bugdojobot. I'm here to help you. We can communicate through the following commands. Allow me to explain what each one does.

@bugdojobot test

Submits the issue for testing by the pool of testers. After a while, you’ll start receiving reports. Look for passed or failed labelled issues.

@bugdojobot retest

When you’ve done some fixes or changes based on the report and want to re-submit the issue for testing, use this command.

@bugdojobot reject

Use the reject command when you’re not happy with the report and want to re-test the issue.

@bugdojobot support

Whenever you find yourself unsure or stuck, you can contact the Bugodojo team by using this command. They’ll receive your comment and reply ASAP.

Sql Server扫描出多余的表的问题

【trace_xe_event_map】
【trace_xe_action_map】
我用的是Sql Server 2014 版本，在获取表的时候，会多获取这两个，暂时没有一个较合适的办法解决，希望大佬能不能解决一下

no support get postgresql meta

no support get postgresql meta

解决Mysql、Oracle、Sql Server中，表备注、字段备注不显示的问题

【解决Sql server表备注、列备注问题】：
通过我对源码的研究，发现作者并未提供Sql Server对表备注和列备注的实现，因此，在阅读源码后，我对以下的一个方法进行修改，就可以解决这个问题了，并且同时支持sqljdbc4、jtds这两个数据源连接。
修改文件位置：db-meta/src/main/java/com/cgs/db/meta/core/MetaLoaderImpl.java
修改方法位置：100行处，public Table getTable(String tableName, SchemaInfoLevel schemaLevel,SchemaInfo schemaInfo) 方法
修改以后内容：直接复制这块代码，占到对应的方法中即可，注意是将该方法中代码全部替换

全部代码内容：
Connection con = JDBCUtils.getConnection(dataSource); MetaCrawler metaCrawler=null; try{ metaCrawler=factory.newInstance(con); Table table = metaCrawler.getTable(tableName, schemaLevel,schemaInfo); // 获取数据源，然后获取驱动名称 Connection connection = dataSource.getConnection(); DatabaseMetaData metaData = connection.getMetaData(); String driverName = metaData.getDriverName(); // Microsoft JDBC Driver 4.0 for SQL Server // jTDS Type 4 JDBC Driver for MS SQL Server and Sybase // 判断是否是sql server驱动，解决表备注和列备注失效的问题 if (driverName != null && driverName.indexOf("SQL Server") != -1) { // 通过查找sql server独有的约束表，将之前所缺少的字段补充上 String sql = "SELECT\r\n" + " CONVERT (NVARCHAR (100),isnull(A.name, '')) AS TABLE_NAME,\r\n" + " CONVERT (NVARCHAR (100),isnull(D.value, '')) AS TABLE_REMARK,\r\n" + " CONVERT (NVARCHAR (100),isnull(B.name, '')) AS COLUMN_NAME,\r\n" + " CONVERT (NVARCHAR (100),isnull(C.value, '')) AS COLUMN_REMARK\r\n" + "FROM sys.tables A\r\n" + "LEFT JOIN sys.columns B ON A.object_id = B.object_id\r\n" + "LEFT JOIN sys.extended_properties C ON A.object_id = C.major_id AND B.column_id = C.minor_id\r\n" + "LEFT JOIN sys.extended_properties D ON A.object_id = D.major_id AND D.minor_id = 0\r\n" + "WHERE A.name = '"+tableName+"'"; PreparedStatement preparedStatement = connection.prepareStatement(sql); ResultSet resultSet = preparedStatement.executeQuery(); // 循环每一个字段，将列备注重新设置 while (resultSet.next()) { // String TABLE_NAME = resultSet.getString("TABLE_NAME"); // 表名 String TABLE_REMARK = resultSet.getString("TABLE_REMARK") ; // 表备注 String COLUMN_NAME = resultSet.getString("COLUMN_NAME") ; // 列名 String COLUMN_REMARK = resultSet.getString("COLUMN_REMARK") ; // 列备注 table.setComment(TABLE_REMARK); table.getColumns().get(COLUMN_NAME).setComment(COLUMN_REMARK); } } return table; }catch(DataAccessException | SQLException e){ logger.debug(e.getMessage(),e); throw new DatabaseMetaGetMetaException("Get tables error!", e); }finally{ JDBCUtils.closeConnection(con); }

【解决Mysql、Oracle表备注、列备注问题】：
我发现数据源最好使用druid，而不是DBCP和C3P0，因为druid可以设置参数的形式，开启mysql、oracle的表备注和列备注
这样就不需要修改这个开源项目的源码了，就像下边这样，接下来，下一个我将介绍统一请求格式

【统一mysql、oracle、sql server的初始化格式，使用单元测试演示】：
直接使用了url一条解决，其实是可以设置端口、账户、密码、数据库名啥的，但是我觉得太麻烦了，为什么我要统一格式呢，主要是，我想做一个代码生成器，支持这三个数据库，所以我就把最上层的创建这里也给统一了，如下图，这里只是创建数据源
mysql：

oracle：

sql server:sqljdbc4

sql server:jtds

异常抛错了

MetaLoaderImpl.java类中的getFunctions、getFunction、getFunctionNames异常抛错了