SQLDump

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:

SQLRun: runs SQL scripts from files - may be used for regenerating database from dumped SQL-scripts. See sqlrun.template.properties for more info
SQLDiff: generates a diff from 2 schema models. See sqldiff.template.properties for more info
SQLMigrate: tool for schema migration. See sqlmigrate/README.md and sqlmigrate.template.properties for more info
Diff2Queries: generates data diff from 2 SQL queries. See diff2q.template.properties for more info

Author: Telmo Brugnara <tbrugz@gmail.com>

License: LGPLv3 - see LICENSE

SQLDump - Basic Process

SQLDump processing consists of:

1 Grabber (implementation of SchemaModelGrabber, grabs a SchemaModel)
'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
SQLRunProcessor - Loads properties into SQLRun and execute statements & importers
mondrianschema.Olap4jMDXQueries - Dumps data from olap4j/mondrian engine
mondrianschema.MondrianSchema2GraphProcessor - Dumps a graphML diagram based on a Mondrian Schema file
mondrianschema.MondrianSchemaValidator - Validates a mondrian schema
xtraproc.StatsProc - Grabs statistics from database

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)
xtradumpers.DropScriptDumper - Generates drop SQL-scripts

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

Usage examples can be found at doc/examples.

Dependencies

java 8 or newer
apache-commons-logging
log4j - optional but recommended
database-dependent JDBC jars ( e.g. PostgreSQL, MySQL, Oracle, Derby, HSQLDB, H2, SQLite, MariaDB, Drizzle, MonetDB, Firebird, Virtuoso, jTDS/SQLServer, Neo4j )
kmlutils - optional, for graphML output
jettison - optional, for JSON output
olap4j & mondrian - optional, for Mondrian Schema output, validation & data dump
maven - for building

Building from sources (with maven)

Run git clone https://github.com/tbrugz/sqldump <project-dir> (if not done already)
Run mvn package
(install artifacts locally) mvn install
(deploy snapshot artifacts to snoatype) mvn deploy

[deprecated] 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>

[deprecated] Not building? Setup env (without sources)

Download sqldump.jar jar from sqldump maven repo (e.g.: sqldump 0.9.16)
Download jar dependencies, especially apache-commons-logging, to lib (may be downloaded from sqldump/downloads)
(windows) Download sqldump.bat.template as sqldump.bat or (unix-like) download sqldump.sh.template as sqldump.sh
Download latest version of sqldump.template.properties as sqldump.properties
Edit sqldump.properties and (windows) sqldump.bat or (unix-like) sqldump.sh (you may include command-line options at end)

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-core.jar:lib/kmlutils.jar:lib/commons-logging-1.1.1.jar:lib/log4j-1.2.15.jar:<jdbc-driver-path> tbrugz.sqldump.SQLDump <options>

Building or running with Docker

See docker/README.md or docker/BUILD.md

Command-line options

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

Artifact repositories

Releases:

maven central/sqldump
maven central/org.bitbucket.tbrugz

Snapshots:

sonatype snapshots
- see how to configure settings.xml

Maven dependency config:

<dependency>
	<groupId>org.bitbucket.tbrugz</groupId>
	<artifactId>sqldump</artifactId>
	<version>0.10</version>
</dependency>

Publishing

To publish on maven central, see doc/maven-sonatype.md
To check code quality on sonarcloud, see doc/sonarqube.md

Misc/End notes

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

Sqldump

Install / Use

README