Forum: Building VoltDB Applications

Post: Reducing code duplication in stored procedures

Reducing code duplication in stored procedures
pmahlen
Nov 17, 2011
Hi,
I tried writing the following code:
public abstract class UpsertProcedure
extends VoltProcedure {

private final SQLStmt select;
private final SQLStmt insert;
private final SQLStmt update;

public UpsertProcedure(SQLStmt select, SQLStmt insert, SQLStmt update) {
this.select = select;
this.insert = insert;
this.update = update;
}

protected VoltTable[] executeUpsert(long id, Object[] insertParams, Object[] updateParams) {
voltQueueSQL(select, EXPECT_ZERO_OR_ONE_ROW, id);

VoltTable[] results = voltExecuteSQL();

if (results[0].getRowCount() == 0) {
voltQueueSQL(insert, insertParams);
}
else {
voltQueueSQL(update, updateParams);
}

return voltExecuteSQL();
}
}

public class UpsertAtom extends UpsertProcedure {

private static final SQLStmt SELECT = new SQLStmt("SELECT * FROM atom WHERE atom_id = ?");
private static final SQLStmt INSERT= new SQLStmt("INSERT INTO atom VALUES (?, ?)");
private static final SQLStmt UPDATE = new SQLStmt("UPDATE atom SET label = ? WHERE atom_id = ?");

public UpsertAtom() {
super(SELECT, INSERT, UPDATE);
}

public VoltTable[] run(long atomId, String label) {
return executeUpsert(atomId,
new Object[] {atomId, label},
new Object[] {label, atomId});
}
}

// etc., multiple other upsert classes

The idea would be that the logic for upserts is similar and you could remove some code duplication by simply providing the base class with different SQL queries and parameters. That fails, as the UpsertProcedure class isn't available to the server. I can't find a way to make that available via for instance the VoltCompiler. Is that correct? What are the best practices to use for this sort of problem?

Thanks,
Petter