1

Suppose I have an existing embedded Derby database and I want to reverse engineer the SQL commands that are necessary to create this database. There are a number of tools that do this by allowing the user to select a table and to get the creation script for the table. SQuirreL SQL, NetBeans, etc. permit this functionality. I am interested in understanding the approach taken to do this.

This problem is motivated by the challenge of programmatically (through JDBC) removing all data from a database (without destroying the structure of the database). In order to do this you generally need to remove the constraints on the database and then issue SQL truncate commands. See this post for and outline of the approach. However, removing the constraints changes the structure.

If I can (through SQL or JDBC methods) reverse engineer the creation script for any database, then when the database needs refreshed (cleared of all data), I could just capture the creation script, delete the database, and then re-create the database via the script. I want to do this for an arbitrary database. An approach using only JDBC that would work with any JDBC compliant database would be ideal.

Other related StackOverFlow Q/A include how to truncate all tables in MySQL The recommendations here seem to be MySQL specific, but do provide some insights. There doesn't seem to be a way to turn of referential constraints for derby.

Perhaps there is an already developed open source API for this. I am looking for some ideas before having to do it myself.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Manuel Rossetti
  • 67
  • 8

1 Answers1

1

I constructed a test using DdlUtils as noted by Gord Thompson. I was able to reproduce a creation script.

I tested on the basic jdbcDemo database that you get with the Derby install. You need to run the supplied WwdEmbedded.java class that is discussed in the Derby docs. to create the database in my example.

Here is example code:

    // the database name
    String dbName = "jdbcDemoDB";
    // define the Derby connection URL to use
    String connURL = "jdbc:derby:" + dbName;

    EmbeddedDataSource eds = new EmbeddedDataSource();
    //eds.setConnectionAttributes(connURL);
    eds.setDatabaseName(dbName);
    System.out.println(eds);
    try {
        Platform p = PlatformFactory.createNewPlatformInstance(eds);
        Database db = p.readModelFromDatabase("temp");
        String createTablesSql = p.getCreateTablesSql(db, true, true);
        System.out.println(createTablesSql);

    } catch (SQLException ex) {
        Logger.getLogger(DbTester.class.getName()).log(Level.SEVERE, null, ex);
    }

The output is like this:

-- WISH_LIST

DROP TABLE WISH_LIST;

-- WISH_LIST

CREATE TABLE WISH_LIST ( WISH_ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY, ENTRY_DATE TIMESTAMP, WISH_ITEM VARCHAR(32) NOT NULL );

Manuel Rossetti
  • 67
  • 8
  • Unfortunately it does not match exactly the SQL string used to create the original table. It is close. CREATE TABLE WISH_LIST (WISH_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT WISH_PK PRIMARY KEY, ENTRY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP, WISH_ITEM VARCHAR(32) NOT NULL) – Manuel Rossetti May 05 '16 at 22:08
  • You mean: you created a column as `GENERATED ALWAYS AS IDENTITY` and DDLUtils reported it as `GENERATED BY DEFAULT AS IDENTITY`? That sounds like a bug in DDLUtils to me. – Bryan Pendleton May 06 '16 at 02:50
  • Not sure if is is a bug in DDLUtils or just how it works. – Manuel Rossetti May 06 '16 at 22:07