Simplify Your Stored Procedure Logic with Expectations

written by Andrew Wilson on December 3, 2012 with 2 comments

John Hugg was talking with me today about a way to reduce the complexity of error checking in a stored procedure and how rarely it is used. VoltDB’s stored procedures let you set “expectations” on each SQL statement. Those expectations can eliminate several lines of code leading to shorter, readable and more reliable stored procedures.

Consider the following sample:

Example.DDL

CREATE TABLE user_table (
user_name        varchar(200)        UNIQUE NOT NULL,
  password         varchar(100)        NOT NULL,
  CONSTRAINT user_name_idx PRIMARY KEY
  (user_name)
  );
PARTITION TABLE user_table ON COLUMN user_name;
CREATE INDEX user_password_idx on user_table (user_name, password);
CREATE PROCEDURE FROM CLASS com.voltdb.sample.procs.LoginProc1;
CREATE PROCEDURE FROM CLASS com.voltdb.sample.procs.LoginProc2;

LoginProc1.java

    @ProcInfo(partitionInfo = "user_table.user_name:0", singlePartition = true)
    public class LoginProc1 extends VoltProcedure {

        public final SQLStmt LOOKUP = new SQLStmt(
                "select * from user_table where user_name=? and password=?");

        public long run(String name, String password) {
            // return 0 if we cannot find the user
            long results = 0;
            voltQueueSQL(LOOKUP, name, password);
            VoltTable[] lookupResults = voltExecuteSQL(true);
            if (lookupResults[0].getRowCount() > 0) {
                results = 1;
            }

            return results;
        }
    }

LoginProc2.java

    @ProcInfo(partitionInfo = "user_table.user_name:0", singlePartition = true)
    public class LoginProc2 extends VoltProcedure {
    public final SQLStmt LOOKUP = new SQLStmt("select * from user_table where user_name=? and password=?");
             public long run(String name, String password) { 
             // Throws a VoltAbortException if the row cannot be found
                 voltQueueSQL(LOOKUP, EXPECT_ONE_ROW, name, password);
                 voltExecuteSQL(true); // always returns true or throws an exception
                 return 1;
        }
    }

LoginProc1 checks for a row count and returns either a 0 or a 1 if the username and password combination could not be found. LoginProc2 sets an expectation that the results of voltExecuteSQL() will return exactly one row, otherwise it will throw an exception and rollback the transaction.

This is the simplest example of an expectation. Consider that more typical procedures will have insert, update or delete operations as well and by using the expectations you are able to reduce or eliminate all the code that would check the success of each statement. The resulting code is shorter, more readable and more reliable as VoltDB is determining whether the outcome of the query is producing the appropriate number of rows. You can then extend the error checking to validate data or perform transformations knowing that your queries were successful.

If you would like to learn more but are not sure where to start, you can find documentation, guides and the latest code updates on our GitHub page.