4.3. Writing Stored Procedures Inline Using Groovy

Documentation

VoltDB Home » Documentation » Using VoltDB

4.3. Writing Stored Procedures Inline Using Groovy

Writing stored procedures as separate Java classes is good practice; Java is a structured language that encourages good programming habits and helps modularize your code base. However, sometimes — especially when prototyping — you just want to do something quickly and keep everything in one place.

You can write stored procedures directly in the schema definition file (DDL) by embedding the procedure code using the Groovy programming language. Groovy is an object-oriented language that dynamically compiles to Java Virtual Machine (JVM) bytecode. Groovy is not as strict as Java and promotes simpler coding through implicit typing and other shortcuts.

You embed a Groovy stored procedure in the schema file by including the code in the CREATE PROCEDURE AS statement, enclosed by a special marker — three pound signs (###) — before and after the code. For example, the following CREATE PROCEDURE statement implements the Insert stored procedure from the Hello World example using Groovy:

CREATE PROCEDURE Insert AS ###
   sql = new SQLStmt(
      "INSERT INTO HELLOWORLD VALUES (?, ?, ?);" )
   transactOn = { String language,
                  String hello,
                  String world ->
      voltQueueSQL(sql, hello, world, language)
      voltExecuteSQL()
   }
### LANGUAGE GROOVY;

Some important things to note when using embedded Groovy stored procedures:

  • The definitions for VoltTypes, VoltProcedure, and VoltAbortException are automatically included and can be used without explicit import statements.

  • As with Java stored procedures, you must declare all SQL queries as SQLStmt objects at the beginning of the Groovy procedure.

  • You must also define a closure called transactOn, which is invoked the same way the run method is invoked in a Java stored procedure. This closure performs the actual work of the procedure and can accept any arguments that the Java run method can accept and can return a VoltTable, an array of VoltTables, or a long value.

In addition, VoltDB provides special wrappers, tuplerator and buildTable, that help you access VoltTable results and construct VoltTables from scratch. For example, the following code fragment shows the ContestantWinningStates stored procedure from the Voter sample application written in Groovy:

transactOn = { int contestantNumber, int max ->
    voltQueueSQL(resultStmt)
 
    results = []
    state = ""
 
    tuplerator(voltExecuteSQL()[0]).eachRow {
        isWinning = state != it[1]
        state = it[1]
 
        if (isWinning && it[0] == contestantNumber) {
            results << [state: state, votes: it[2]]
        }
    }
    if (max > results.size) max = results.size
    buildTable(state:STRING, num_votes:BIGINT) {
        results.sort { a,b -> b.votes - a.votes }[0..<max].each {
            row it.state, it.votes
        }
    }
}

Finally, it is important to note that Groovy is an interpreted language. It is very useful for quick coding and prototyping. However, Groovy procedures do not perform as well as the equivalent compiled Java classes. For optimal performance, Java stored procedures are recommended.

>