HDBData - JDBC to XML Backup / Restore Utility

HDBData is a tool for creating backups of JDBC databases (ie., databases accessible from SUN JDBC API drivers) in XML files. HDBData can also restore the data back from the XML.

The most interesting idea behind HDBData, is that the restore process is somehow fault-tolerant. The restore process uses only the data from the table columns that are still present in the database (an intersection of the sets of backup columns and of the existing columns is used). This means, that except for primary key columns, all other columns in database table can be added or removed from the entity-relationship structure as necessary. During restore, data will be inserted only on the existing columns. Missing tables in the new database are also not taken into consideration. This makes this tool useful when your database is changing often, especially during development process, when the database schema changes often and the old test data need to re-entered in the new schema.

Despite its interesting restore process, the HDBData tool is not very generic. Similar, but much more generic processes, such as CWM, exits. HDBData was created for a specific project and the last bugs are fixed in October 2000. It can still be useful, but it may require a rewrite for other real projects. Data restore is possible only when foreign keys are disabled in the database. Only minimal elements of the database table schema are saved in order to make fault-tolerant restore possible.

1. Change the "./hdb.txt" in the current directory to connect to the database. An example "hdb.txt" file for MySQL may look like the following:
   
   

   # MySQL database
   driver=org.gjt.mm.mysql.Driver
   url=jdbc\:mysql\://localhost/peoplink
   user=root
   password=
   

   
   For InstantDB it may look like this:
   

   # InstantDB database
   driver=org.enhydra.instantdb.jdbc.idbDriver
   url=jdbc\:idb\:database/peoplink.prp
   user=
   password=
   

   
   
2. If needed, change "./htypes.txt" in the current directory, to include the database data types non included there. Just to add new types. Don't delete the types that are already there. Currently "htype.txt" looks like this:
   

   # HDBData Data Type File
   
   CHAR=String
   VARCHAR=String
   TEXT=String
   TIMESTAMP=String
   DATETIME=String
   DATE=String
   TINYINT=Int
   SMALLINT=Int
   INTEGER=Int
   INT=Int
   BYTE=Int
   BIT=Boolean
   FLOAT=Double
   REAL=Double
   LONGVARBINARY=Binary
   VARBINARY=Binary
   BINARY=Binary
   LONGBLOB=Binary
   
   

   
   
3. Set classpath as necesary, such as (on NT):
   

   set CLASSPATH = %CLASSPATH%;.;jaxp.jar;parser.jar;
   

   
   The SUN AXP API is used to parse the XML file. The "jaxp.jar" and "parser.jar" are required.
   
   
4. To backup:
   
   

   mkdir mydata
   java org.peoplink.hdb.HDBData -b -d ./mydata/
   

   
   
5. To restore:
   
   

   java org.peoplink.hdb.HDBData -r -d ./mydata/
   

   
   If the directory is omited, "./backup/" directory is used by default.
   
   
6. To find more help, try:
   
   

   java org.peoplink.hdb.HDBData -?
   

   
   

To use HDB directly on code, see HDBData.java for an example. You can init java.sql.Connection yourself, in this case use HUtils.setConnection(Connention con) to set the connection (you must close it when you are finished), or use HUtils.initDB() to have the connection inited by HUtils (HDB) based on hdb.txt file. If verbose output is not required during restore, then set HUtils.verbose=false; in code.

Directory Structure

./hdb.txt	    - Database connection properties file
                  (Java properties file format).
./htypes.txt    - Database to Program data types properites
                  file (Java properties file format).
./backup/ 	    - Default backup/restore directory.
./org/peoplink/hdb/ - HDBData files + source code.

./backup/hdata.dtd - XML DTD file
./backup/hdata.xml - database backup data saved as XML file
./tablename_binarycolumnname_X+.bin - data of the binary fields
                     is saved out of XML file

./backup/hlog.txt - Detailed restore data log.
                    Useful for debug purposes.

Files

These files make up this utility, in "./org/peoplink/hdb/" directory:

HDB.java      - database connection initialization
                by using hdb.txt, used by HUtils.java
HUtils.java   - database metadata & connection utilities
HSQL2XML.java - backup code from database to an hdata.xml file
HXML2SQL.java - database inteligent restore code from hdata.xml
HDBData.java  - simple laucher

HXML2SQL.java is a SAX parser version that consumes less memory than DOM one and can be suitable for large xml data files.

TODO

• Data on xml fields is currently separeted by a HUtils.delimiter (@@^^@@@^). This can be also implemented with a more detailed XML DTD format. Another convenient way is just to escape the HUtils.delimiter in strings (not implemented!).
• Program does not know all the data types internally. For example, it does not know about the DATE type. This is not a big problem with jdbc api. Given that the program backup and restores to the same database, generic jdbc statements can be used. For example, to add support for the DATE type add DATE=String to "htypes.txt". In this case java.sql.ResultSet.getString(...) will be used to read the date filed and later java.sql.PreparedStatement.setString(...), will be used to save it back.
  Not all types can be supported like this. In order to be used with more generic database types, these (and others) internal types may be added:
  

  DATE               - java.sql.Date
  TIME               - java.sql.Time
  TIMESTAMPT         - java.sql.Timestamp (treated as String)
  DECIMAL            - java.math.BigDecimal
  NUMERIC | BIT      - java.lang.Boolean (treated as BIT)
  BIGINT             - java.lang.Long
  REAL               - (treat as doubles)
  VARBINARY | BINARY - (treat as LONGVARBINARY)
  OBJECT             - (treat as binary)
  

  
  (Source: HypersonicSQL documentation)
• The HUtils.typeMap can be filled automatically from any database in the future. (Code can be writen that saves/reads back this information to/from a file.) The types of columns can be collected from metadata and scaned for known patterns (eg. *CHAR* -> String). Thus the program, and not the user, will be responsible for maintaining the "htypes.txt" file. The user will have to edit it only if program logic fails.
• Test code with more databases. Currenlty only MySQL, HypersonicSQL, and Enhydra InstantDB are tested.
• HDBData is only a simpler launcher for the program. A more featured launcher may be added, to enable directory creation/selection etc.

See also a related tool: TGen.