Utility to dump schema and data from a RDBMS. Features:

  • Does schema-dumping, using (mainly) standard java API, by way of java.sql.DatabaseMetaData
  • Does data-dumping (formats: csv, xml, html, json, sql 'insert into', sql 'update by PK', fixed column size, blob)
  • Can be used with any JDBC-compliant databases
  • Generates Entity-Relationship diagrams based on Tables and FKs (graphML output - yEd recommended)
  • Flexible schema output patterns (based on schema name, object type and object name)
  • Translation of metadata (column types) between different RDBMS dialects/implementations (partial)

SQLDump also has three subprojects:

Author: Telmo Brugnara <tbrugz@gmail.com>

License: LGPLv3 - see LICENSE.md

SQLDump - Basic Process

SQLDump processing consists of:

  1. 1 Grabber (implementation of SchemaModelGrabber, grabs a SchemaModel)
  2. 'n' Processors (implementation of Processor, usually uses a Connection or SchemaModel) & Dumpers (implementation of SchemaModelDumper, dumps a SchemaModel)

Grabber can be:

  • JDBCSchemaGrabber - Grabs schema metadata from a JDBC connection
  • JAXBSchemaXMLSerializer - Grabs schema metadata from a XML file
  • JSONSchemaSerializer - Grabs schema metadata from a JSON file

Processors can be:

  • DataDump - Dumps data based on grabbed schema (can partition data from 1 table in different files, can dump in different formats)
  • SQLQueries - Dumps data based on SQL-queries (same as DataDump - for each query)
  • CascadingDataDump - Dumps data based on table relationships (FKs), given initial tables/filters
  • graph.ResultSet2GraphML - Dumps a graphML diagram based on a SQL-query
  • SQLDialectTransformer - Transforms schema models between different sql-dialects
  • mondrianschema.Olap4jMDXQueries - Dumps data from olap4j/mondrian engine
  • mondrianschema.MondrianSchema2GraphProcessor - Dumps a graphML diagram based on a Mondrian Schema file

Dumpers can be:

  • SchemaModelScriptDumper - Dumps schema model in SQL-script format (DDL)
  • JAXBSchemaXMLSerializer - Dumps a XML representation of the schema model
  • JSONSchemaSerializer - Dumps a JSON representation of the schema model
  • graph.Schema2GraphML - Generates a Entity-Relationship diagram based on schema model
  • mondrianschema.MondrianSchemaDumper - Generates a Star/Snowflake Mondrian Schema based on schema model
  • xtradumpers.AlterSchemaSuggester - Generates suggestions of SQL-scripts for altering the schema model (beta)

All processing is controlled by a properties file. See sqldump.template.properties for more info.

Usage examples can be found at doc/examples.


Building from sources (with ant & ivy)

  • Run hg clone https://bitbucket.org/tbrugz/sqldump <project-dir> (if not done already)
  • Add to project dir an ivysettings.xml file that points to the sqldump maven repo (like this)
  • Copy build.template.properties to build.properties
  • Edit build.properties
  • Run ant resolve
  • Run ant dist

Running (with sources)

  • Download jdbc jars for your database of choice
  • Edit sqldump.properties
  • Run ant run or
  • Run tbrugz.sqldump.SQLDump, e.g., java -cp bin;lib/kmlutils.jar;lib/commons-logging-1.1.1.jar;lib/log4j-1.2.15.jar;<jdbc-driver-path> tbrugz.sqldump.SQLDump <options>

Not building? Setup env (without sources)

Running (without sources)

  • Download jdbc jars for your database of choice
  • (windows) Run sqldump.bat
  • (unix-like) Run sqldump.sh or run tbrugz.sqldump.SQLDump, e.g., java -cp sqldump.jar:lib/kmlutils.jar:lib/commons-logging-1.1.1.jar:lib/log4j-1.2.15.jar:<jdbc-driver-path> tbrugz.sqldump.SQLDump <options>

Command-line options

  • -propfile=<path-to-prop-file>: loads a different config properties file
  • -propresource=<path-to-resource>: loads a different config properties resource
  • -usesysprop=[true|false]: loads system properties besides the config file properties (default is true)
  • --help: show help and exit
  • --version: show version and exit

Misc/End notes

To build with Jenkins, see doc/jenkins-config.md