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 <[email protected]>

License: LGPLv3 - see LICENSE

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
• 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 7 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
• ant - recommended for building
• ivy - recommended for building

Building from sources (with ant & ivy)

• Run git clone https://github.com/tbrugz/sqldump <project-dir> (if not done already)
• Run ant prepare
• Install Ivy (mkdir -p $HOME/.ant/lib + curl -o $HOME/.ant/lib/ivy-2.5.0.jar https://repo1.maven.org/maven2/org/apache/ivy/ivy/2.5.0/ivy-2.5.0.jar) or ant ivy-install (if not done already)
• (obsolete - see ivy-install) Add to project dir an ivysettings.xml file that points to the sqldump maven repo (like this ; better: cp templates/ivysettings.xml ivysettings.xml)
• (obsolete - see ivy-install) Copy templates/build.properties to build.properties
• (optional) Edit build.properties
• (optional/eclipse) Use IvyDE, import project, right click + Ivy > Resolve
• Run ant resolve
• (optional) ant test
• Run ant dist or ant publish (publishes, by default, to local maven repo: $HOME/.m2/repository) or ant all
• (optional) Publish maven artifacts: Install Maven Ant tasks (curl -o $HOME/.ant/lib/maven-ant-tasks-2.1.3.jar https://repo1.maven.org/maven2/org/apache/maven/maven-ant-tasks/2.1.3/maven-ant-tasks-2.1.3.jar) or ant mvn-ant-tasks-install & ant publish-mvn-files

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)

• 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.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 maven 'modules'

• ant mvn-modules-install (sqlmigrate & sqldump-mondrian modules)

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

Snapshots:

• sonatype snapshots
  • see how to configure settings.xml

Maven dependency config:

<dependency>
	<groupId>org.bitbucket.tbrugz</groupId>
	<artifactId>sqldump</artifactId>
	<version>0.9.17</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

To use with Eclipse, IvyDE is recommended