Database copy tool
Consider two potentially differing database systems e.g. Postgresql™ and Mysql™.
- Database 1:
-
Hosting an active instance i.e. a set of tables containing data records and optionally views.
- Database 2
-
Yet empty or containing non-conflicting table and view names.
We assume full JDBC™ administrative read access to database 1 and full read/write access to database 2. The copy tool shall allow for the following sequence of operations:
-
Copying all tables among with related records from database 1 to database 2.
Vendor specific SQL syntax rules e.g. when dealing with generated ID values (IDENTITY vs. AUTONUM) must be addressed. It is advisable to keep them in configuration files rather than hard-code them.
-
Transver as much integrity constraints as possible:
-
Data types potentially differing on both systems.
-
null/not nullconstraints. -
Column default values.
-
Primary / candidate key constraints.
-
Foreign key constraints.
-
Check constraints.
-
Considerations:
-
null/not nulldefaults may differ on both database systems. -
Database 2 may be a non-SQL database like Mongodb supporting a limited subset of schema constraints. A copy tool thereby supports database migration.
Tip
Useful technologies:
-
JDBC in general and ResultSetMetaData.html + friends in particular.
-
The tool may be implemented as a CLI application using a standard command line option handling parser. See CLI Comparison as well.
