Faster Than File IO?

Currently I'm doing a big migration project and a part of it is inserting parsed files from one DB to another. DBvolution is playing a big part in the project but not in the way you might expect.

I did try inserting all the rows using the DBV API but DBV is designed for programmer performance not database performance and it took too long. So I switched to using the bulk load facility available in the database.

DBvolution helped in a slightly naughty way: I used my intimate knowledge of the internals to execute the bulk load via JDBC. It wasn't that hard to be honest, but it did make me feel a little dirty. The process isn't really appropriate for DBV and there doesn't seem to be a standard for bulk loading so turning it into an API would be extremely difficult.

The functionality has been added to the PostgresDB class but it's full of caveats and gotchas so don't use it unless you like pain.

While testing the loading process, I had time to think and decided I needed to be able to log the progress. "Log" obviously means "log file" so I started getting out my BufferedWriter code and typing frantically. Very quickly, as usually happens when doing file IO, I stopped typing and started looking for a problem. This time however I realised the problem wasn't encoding Objects into a BufferedWriter, it was that I was using File IO.

The more I thought about it the more I wanted random access to multiple entries in the file and to perform transformations like subtracting the earliest entry time from the latest. That's pretty complicated stuff to be doing on a file, so I'd need to load, parse, and manipulate the entries. It was all getting too complicated.

Until I realised that databases are much better at that kind of thing.

Really what are databases but a big complicated file with an API to load, parse, and manipulate the entries. And I happen to have an excellent API for leveraging database. Oh and I already have a database to use!

Apparently I was a little slow today :-D

Very quickly the idea became a DBRow and the printlns I was using to debug the process became calls to the methods of the DBRow. Using an object allows the development of an API within the PhaseComplete class. The DBV requirement that fields be accessible raises some concerns but it does focus the mind on creating a convenient API.

The pattern for using PhaseComplete is:

PhaseComplete phases = PhaseComplete.preparePhases(localDB);
final String phase = "PHASE1";
if (phases.phaseIsRequired(phase)) {
    // DO STUFF
phases.completePhase(phase);
}
phase = "PHASE2";
if (phases.phaseIsRequired(phase)) {
    // DO STUFF
phases.completePhase(phase);
}
phase.finish();

And the whole class is:

public class PhaseComplete extends DBRow {

    @DBColumn
    @DBPrimaryKey
    @DBAutoIncrement
    public DBInteger pk = new DBInteger();

    @DBColumn
    public DBString phase = new DBString();

    @DBColumn
    public DBDate completionTime = new DBDate();

    @DBColumn
    public DBBoolean finished = new DBBoolean();

    private DBDatabase localDB = null;

    public PhaseComplete() {
        super();
    }

    public static PhaseComplete preparePhases(DBDatabase db) {
        PhaseComplete phase = new PhaseComplete();
        phase.localDB = db;
        try {
            phase.localDB.createTable(phase);
        } catch (SQLException ex) {
            System.out.println("Exception while trying to create the PhaseComplete table: assuming table already exists and proceeding");
        } catch (AutoCommitActionDuringTransactionException ex) {
            System.out.println("Exception while trying to create the PhaseComplete table: assuming table already exists and proceeding");
        }
    return phase;
    }

public boolean phaseIsRequired(String phase) throws SQLException {
    PhaseComplete phaseComplete = new PhaseComplete();
    phaseComplete.phase.setValue(phase);
    phaseComplete.finished.setValue(false);
    return localDB.getDBTable(phaseComplete).getAllRows().isEmpty();
}

public void completePhase(String phaseCompleted) throws SQLException {
    PhaseComplete phaseComplete = new PhaseComplete();
    phaseComplete.phase.setValue(phaseCompleted);
    phaseComplete.completionTime.setValue(new Date());
    phaseComplete.finished.setValue(false);
    localDB.insert(phaseComplete);
}

public void finish() throws SQLException, Exception {
    localDB.implement(new DBScript() {

        @Override
    public DBActionList script(DBDatabase db) throws Exception {
        DBActionList actions = new DBActionList();

        PhaseComplete phaseComplete = new PhaseComplete();
        phaseComplete.finished.setValue(false);
        List<PhaseComplete> allRows = db.getDBTable(phaseComplete).getAllRows();
        for (PhaseComplete row : allRows) {
            row.finished.setValue(true);
        }
        db.update(allRows);
                return actions;
    }});
}
}

Technically this is probably not literally faster than File IO, but the development time was and that's what I was hoping for.