This week, we released version 1.4.3 of Akiban. This release has a bunch of great new features and bug fixes in it. There is one new feature in this release in particular that I wanted write about today. Akiban now has a preview implementation of stored procedures!

Now that may not sound too exciting in itself so please bear with me. What gets me excited about this feature is that in Akiban, we allow creation of stored procedures in a multitude of languages. Stored procedures can be implemented using:

  • Java
  • Javascript
  • Ruby
  • Python
  • Groovy
  • Clojure

That’s a pretty nice selection! I’m going to show some examples in Ruby here and if people are interested in more examples, please let me know in the comments and I’ll be sure to whip up other examples in different languages.

First things first and we need to make sure Akiban is configured to allow the creation of stored procedures in Ruby. We have a pretty simple property that controls the class path for our stored procedure scripting languages - akserver.routines.class_path. I just need to make sure that property has an absolute path to where my JRuby jar is installed on my system. Once that property is set in my server.properties file, I can restart Akiban and I’m ready to go.

Lets start with a simple example. I just want to call a function that prints out my name.

CREATE PROCEDURE my_name(out name VARCHAR(128))
  LANGUAGE ruby PARAMETER STYLE variables AS $$
    name = 'padraig'
$$;

Now let’s call that stored procedure from the command line:

test=> call my_name();
  name   
---------
 padraig
(1 row)

test=> 

Success! Our hello world example is up and running.

We don’t just have to return simple data types like that. We can also return ruby hashes. For example, here is a stored procedure that returns a ruby hash:

CREATE PROCEDURE ruby_hash(IN x BIGINT, IN y DOUBLE, OUT s DOUBLE, OUT p
DOUBLE)
  LANGUAGE ruby PARAMETER STYLE variables AS $$
{ "p" => $x * $y,
  "s" => $x + $y }
$$;

Notice this example also demonstrates how to pass parameters to a stored procedure. Running the above stored procedure, we get:

test=> call ruby_hash(10, 100);
   s   |   p    
-------+--------
 110.0 | 1000.0
(1 row)

test=>

A common example used when demonstrating a programming language is to implement a function to compute Fibonaaci numbers. Hence, here is a stored procedure to do just that:

CREATE PROCEDURE fib_r(IN x DOUBLE, OUT s DOUBLE)
  LANGUAGE ruby PARAMETER STYLE java EXTERNAL NAME 'do_fib' AS $$
    def do_fib(x, s)
      s[0] = fib(x)
    end
    def fib(n)
      n < 2 ? n : fib(n - 1) + fib(n - 2)
    end
$$;

In the code above, note that PARAMETER STYLE java means that the function named with EXTERNAL NAME takes as many positional arguments as there are parameters. And an example of running it:

test=> call fib_r(10);
  s   
------
 55.0
(1 row)

test=>

A common technique used to speed up this implementation is to use memoization. A stored procedure that uses this technique follows:

CREATE PROCEDURE fib_non_r(IN x DOUBLE, OUT s DOUBLE)
  LANGUAGE ruby PARAMETER STYLE java EXTERNAL NAME 'do_fib' AS $$
    def do_fib(x, s)
      s[0] = fib(x)
    end
    $fibonacci = Hash.new{ |h,k| h[k] = k < 2 ? k : h[k-1] + h[k-2] }
    def fib(n)
      $fibonacci[n]
    end
$$;

Lets turn on some timing and compare the recursive version versus the version that uses memoization.

test=> call fib_r(30);
    s     
----------
 832040.0
(1 row)

Time: 469.492 ms
test=>

test=> call fib_non_r(30);
    s     
----------
 832040.0
(1 row)

Time: 4.649 ms
test=>

As expected, the version that uses memoization is much better. Next I’m going to write a stored procedure that returns some data from a query. Let’s say I create a simple table and insert some data into it like so:

test=> create table t1(id int);
CREATE TABLE
test=> insert into t1 values (1), (2), (3), (4), (5), (6);
INSERT 0 6
test=>

This stored procedure will return all the data from that table and order it by ID. A simple procedure to do that is:

CREATE PROCEDURE get_data()
  LANGUAGE ruby PARAMETER STYLE variables AS $$
    conn =
java.sql.DriverManager.get_connection("jdbc:default:connection")
    conn.create_statement.execute_query("SELECT id FROM t1 ORDER BY id
DESC")
$$;

And let’s call the stored procedure and see what kind of results we get:

test=> call get_data();
 id 
----
  6
  5
  4
  3
  2
  1
(6 rows)

test=>

As a last example, I want to extend this example and have an input parameter that filters the query results to only return ID values that are greater than whatever the input value is.

CREATE PROCEDURE get_data(IN filter BIGINT)
  LANGUAGE ruby PARAMETER STYLE variables AS $$
    conn =
java.sql.DriverManager.get_connection("jdbc:default:connection")
    conn.create_statement.execute_query("SELECT id FROM t1 WHERE id >
#{$filter} ORDER BY id DESC")
$$;

Running the above procedure with a valid input value yields:

test=> call get_data(2);
 id 
----
  6
  5
  4
  3
(4 rows)

test=>

The above were some simple examples of writing stored procedures in Ruby with Akiban. I’ll likely write another post with some more advanced examples when I get a chance. If this interested you, definitely download the 1.4.3 release and play around with it to try this out for yourself. If anybody has any questions or would like more examples and information, please ask in the comments or on our public mailing list and I’ll be happy to answer.



blog comments powered by Disqus

Published

16 November 2012

Category

akiban