SonicBase. In-memory database. Durable real-time distributed sql rdbms

Stored Procedures

Stored procedures allow you to embed code in the SonicBase database for manipulating queries and result sets. Stored procedures are written in Java. The stored procedure "execute" method is invoked on one replica of every shard. Additionally, there is an "init" and "finalize" method that are called once on a random server in the cluster. The init is called on the same server as the finalize. Inside the stored procedure you have access to a database connection, which allows you to perform any query on the database. This connection also has the ability to allow you to restrict queries to the server it is running on. This is important for performance. This allows you to traverse the database without going off-box which would incur communication penalties.

With this database connection you can provide a "RecordEvaluator" class that will be called on every record that meets your query criteria. You can indicate the record should be included in the results by returning "true" from your RecordEvaluator. Or you can return "false" to not include the record in the results. Additionally, you can do whatever you want with the record, like insert it into another table. Note that the query you issue in your stored procedure should narrow the results as much as possible so you aren't just doing a table scan.

There are a couple of different ways to handle results returned to the client. If you have a large results set you need to store the results in a temporary table and allow the client to traverse that table to get the results. If you have a small results set(less than 50k results) you can directly return the results to the client.

On the client, you pass in a sql statement of the form "call procedure '[class_name]', [parm1], [parm2],...". The parameters are available on the server in the StoredProcedureContext passed into the stored procedure.

You must place the jar containing your stored procedure in the sonicbase/lib directory and deploy it across the cluster. It is highly recommended that you throroughly test your stored procedure on a small cluster that you can quickly restart and reload your class.

Interfaces

StoredProcedureContext


public interface StoredProcedureContext {

  /**
   * @return the shard this stored procedure is running on
   */
  int getShard();

  /**
   * @return the replica this stored procedure is running on
   */
  int getReplica();

  /**
   * @return the configuration object for this cluster
   */
  ObjectNode getConfig();

  /**
   * @return a number that uniquely identifies this stored procedure
   */
  long getStoredProdecureId();

  /**
   * @return a SonicBaseConnection
   */
  SonicBaseConnection getConnection();

  /**
   * @return a new StoredProcedureResponse
   */
  StoredProcedureResponse createResponse();

  /**
   * @return a new Record
   */
  Record createRecord();

  /**
   * @return parameters passed to the stored procedure from the client
   */
  Parameters getParameters();
}
        

RecordEvaluator


public interface RecordEvaluator {
    /**
    * @param context StoredProcedureContext
    * @param record Record to evaluate
    * @return true if the record should be included in the results
    */
    boolean evaluate(final StoredProcedureContext context, Record record);
}
        

StoredProcedure


public interface StoredProcedure {

  /**
   * Called once an a random server.
   * @param context StoredProcedureContext
   */
  void init(StoredProcedureContext context);

  /**
   * Called once on a replica of every shard.
   * @param context StoredProcedureContext
   * @return StoredProcedureResponse including results that should be returned the the client
   */
  StoredProcedureResponse execute(StoredProcedureContext context);

  /**
   * Called once on the server where init was called.
   * @param context StoredProcedureContext
   * @param responses list of StoredProcedureResponses that were returned from each of the execute calls
   * @return StoredProcedureResponse including results that should be returned the the client
   */
  StoredProcedureResponse finalize(StoredProcedureContext context, List responses);

}
        

StoredProcedureResponse


public interface StoredProcedureResponse {

    /**
    * @return list of Records that will be returned to the client
    */
    List getRecords();

    /**
    * @param records list of records to set in the response
    */
    void setRecords(List records);

    /**
    * @param record a Record to add to the results
    */
    void addRecord(Record record);

}