XDBSchema - Generation of a Database Schema in XML
XDBSchema is a standalone application that generates an XML representation of a database
using a jdbc connection to the database. This format is required by some tools like
SQLMutation and
SQLFpc.
Download
Download here the latest version.
Next, unpack the ZIP file. The following filles will be present in your directory:
- xdbschema-all.jar: Java archive for the application.
- xdbschema.bat, xdbschema.sh: Launcher command file.
After running it for the first time, the following files are generated:
- xdbschema.settings.xml: Remembers the last used configuration .
- xdbschema.error.xml: If an error occours, some debug data is included in this file.
If you notify a software problem report you should include this file in order to
facilitate the detection of the problem.
First at all, you must be sure that you have your database running and the adequate
jdbc database driver is in your default directory. Here you can find the drivers
for some databases (maven):
Quick Start
Launch the main class of xdbschema.jar using the following java command (where drivername.jar is the name of the vendor's database driver file. Note that you don't need specify
a driver name if your are going to use the jdbc-odbc bridge driver):
java -classpath drivername.jar;xdbschema.jar in2test.application.xdbschema.XDBSchema
Alternatively, you may edit the xdbschema.bat script file specifying this
command and then run the script.
The following window will be displayed:
The first step consists on filling the information that is needed to connect the database
using jdbc (Database connection info at the left of the window). See
Specifying connection info for more information.
Next, you can use the following options:
- Load All Tables in Schema: After clicking this button the middle of the
screen (List of tables for generating the schema) will be filled with the
names of all tables and views that are visible in the schema. This list of tables can be modified
at anytime.
- Generate Schema from Database: After clicking this button the right of
the screen (Database Schema in Xml) will be filled with the XML representation
of the database schema, including all tables and views that have been specified in List of
Tables for generrating the schema. See XML database
schema syntax.
- File option in the menubar allows to load and save the above information.
Specifying connection info
The information required to access the database is explained below:
- Database connection info required for getConnection(): You must specify the driver
class name and connection url along with the access credentials. The driver class
name and connection url is database vendor specific. Some examples of connectinons
are:
| Database Vendor | Class Driver Name | Connection url |
|---|
| Oracle (OCI Driver) |
oracle.jdbc.driver.OracleDriver |
jdbc:oracle:oci:@<SERVER>:<PORT>:ORCL |
| SQL Server |
com.microsoft.sqlserver.jdbc.SQLServerDriver |
jdbc:sqlserver://<SERVER>:<PORT>;database=<DATABASENAME> |
The option Connections in the menubar facitlitates the configuration for
some typical connections.
- Database connection info required for getMetadata(): In order to obtain the database schema, the Java getMetadata methods are used, which require additional information about the database catalog and database schema where to find the information about the database tables and views. This information is not allways required, but in many ocasions is needed, depending on the database vendor and the access privileges of the user who has access to the database. Below
are some common scenarios:
- In Oracle: The database username is asssociated with the schema and
with a null catalog. So as, usually, you will not specify any catalog, but if you don't sypecify
any schema, and click Load All Tables in Schema you will find all tables
and views that this user has access to, possibly including system tables and tables
from other schemas (if user has DBA privileges). Therefore, is highly recommended
to specify the schema.
- In SQLServer: The database username has its own schema, but usually all
tables are created and used under a special user named dbo. The catalog is the database name.
So as, in a common scenario you will specify the database name as catalog name and dbo as schema name.
However, in many cases if you don't specify any information
for schema or catalog you attain the same effect, provided that the user has only
access to his database and dbo schema.
XML database schema syntax
The XML representation of the database schema consists on a root tag <schema>
which contains a series of <table> tags. Each of them contains a <column>
tag for each of the columns in this table. Attributes of the tags contain the information
related to name, data type, etc.
The <schema> tag may include three attributes, namely:
- dbms: Vendor's name of the database.
- catalog: Default catalog as specified in the connection info.
- schema: Default schema as specified in the connection info.
Both catalog and schema attributes are optional. However, they are relevent
in order to determine what tables are to be found. Recall that according to the
SQL Standard, a table specification may be qualified in the form [[catalog.]schema.]name.
If a table name is not qualified (using catalog and/or schema), a default catalog
and/or schema is assumed. In that case the deafaults are the indicated by dbcatalog
and/or dbschema:
- If neither catalog nor schema are specified:
- When loading all tables, all tables that are present in any schema are searched for.
If the user has visibility over more than one catalog/schema and there are tables
with the same name, an error occours because of a duplicated table name.
- Given a set of tables, when you generate the schema, a similar error may occour
if there are tables with same name that are visible. However, you may avoid this
error by qualifying the table names that are duplicated. For instance, if a table
T is present in two schemas S1 and S2, you will distinghish
the tables by their
qualified names S1.T and S2.T
- If catalog and/or schema are specified:
- When loading all tables, all tables that match the catalog/schema
specified are searched for. If some of dbcatalog or dbschema is not specified, it means any.
- Given a set of tables, when the schema is generater, if a table name is not qualified,
the table is searched for in the catalog/schema specified by default. If table name is qualified,
the table is searched for in the catalog/schema as indicated by table name qualifier.
For instance, if an schema S1 is specified by default, a table with name T will be searched in the schema S1, as same
as if S1.T is specified. However, S2.T is specified, table T
will be searched in the schema S2.
Supported database vendors and drivers
Latest builds of these applications has been developed and tested in Eclipse Oxigen 3a using Java JDK 1.8 and JavaCC 4.2
under Windows 10, but may run on lower JRE versions.
As it uses standard jdbc methods to discover the database schema, it will work with
virtually any database vendor, provided that you have a jdbc driver.
As of Java JDK 1.8 the jdbc-odbc bridge
is not supported, you should run a lower version of the JRE in order to use it.
This is the list of configurations that have been used for testing latest builds:
| Database Product Name | Version | Database Driver Name | Version |
|---|
| Oracle 11g XE |
11.2.0.2 |
Oracle JDBC Driver |
11.2.0.1.0 |
|
Microsoft SQL Server 2017 |
14.0.3048 |
Microsoft SQL Server JDBC Driver 4 |
4.0.2206.100 |
]
