Memcached UDF for Java DB on OpenSolaris

Trond Norbye recently blogged about how he installed Memcached user-defined functions for MySQL on OpenSolaris. Of course, I told him I could do the same thing in Java DB in less than half an hour, so now he won't stop nagging me until I can show him the code. So, here's how you can get Memcached user-defined functions for Java DB on OpenSolaris:

First, you need to make sure you have Java DB and Memcached installed. The versions in OpenSolaris should work fine. Just install them from pkg.opensolaris.org like this:

kah@tecra:~ % pfexec pkg install SUNWjavadb SUNWmemcached SUNWmemcached-java 
DOWNLOAD                                    PKGS       FILES     XFER (MB)
Completed                                    3/3       61/61     0.28/0.28 

PHASE                                        ACTIONS
Install Phase                                118/118 
PHASE                                          ITEMS
Reading Existing Index                           8/8 
Indexing Packages                                3/3

Next, you need a Java class with a couple of static methods that allow you to manipulate data on Memcached servers. I wrote four very simple methods, init(), set(), get() and delete(), to get access to the basic functionality:

import com.danga.MemCached.Logger;
import com.danga.MemCached.MemCachedClient;
import com.danga.MemCached.SockIOPool;

public class MemcachedUDF {
    public static void init(String servers) {
        SockIOPool pool = SockIOPool.getInstance();
        pool.setServers(servers.split(","));
        pool.initialize();
        // Make MemCachedClient less chatty
        Logger.getLogger(MemCachedClient.class.getName(), Logger.LEVEL_WARN);
    }
    public static void set(String key, String value) {
        MemCachedClient client = new MemCachedClient();
        client.set(key, value);
    }
    public static String get(String key) {
        MemCachedClient client = new MemCachedClient();
        return (String) client.get(key);
    }
    public static void delete(String key) {
        MemCachedClient client = new MemCachedClient();
        client.delete(key);
    }
}

Then compile the class:

kah@tecra:~/src % javac -classpath /usr/share/lib/java/java_memcached-release_2.0.1.jar MemcachedUDF.java

Finally, Java DB must be told how to call the methods. Put the required jar files and the directory where the MemcachedUDF class is located in your CLASSPATH variable. Then start the IJ client and execute CREATE FUNCTION and CREATE PROCEDURE statements to make the methods accessible from SQL.

kah@tecra:~/src % export CLASSPATH=/opt/SUNWjavadb/lib/derbyrun.jar:/usr/share/lib/java/java_memcached-release_2.0.1.jar:$HOME/src
kah@tecra:~/src % java org.apache.derby.tools.ij
ij version 10.3
ij> connect 'jdbc:derby:testdb;create=true';
ij> create procedure memcached_init(servers varchar(32672))
       language java parameter style java
       external name 'MemcachedUDF.init';
0 rows inserted/updated/deleted
ij> create procedure memcached_set(k varchar(32672), v varchar(32672))
       language java parameter style java
       external name 'MemcachedUDF.set';
0 rows inserted/updated/deleted
ij> create function memcached_get(k varchar(32672)) returns varchar(32672)
       language java parameter style java
       external name 'MemcachedUDF.get';
0 rows inserted/updated/deleted
ij> create procedure memcached_delete(k varchar(32672))
       language java parameter style java
       external name 'MemcachedUDF.delete';
0 rows inserted/updated/deleted

There! The UDFs have been defined, and are ready to be used. To test them, we also need a Memcached server to connect to. Simply execute the following command in another terminal window:

kah@tecra:~ % /usr/lib/memcached -p 12345 -l localhost

Let's see how the functions work by looking at an example. The SQL statements below initialize the UDFs and create a table with triggers that automatically update the cache as the contents of the table change.

ij> call memcached_init('localhost:12345');
0 rows inserted/updated/deleted
ij> create table my_table(id varchar(20) primary key, x varchar(20));
0 rows inserted/updated/deleted
ij> create trigger insert_trigger after insert on my_table
       referencing new as n
       for each row
       call memcached_set(n.id, n.x);
0 rows inserted/updated/deleted
ij> create trigger update_trigger after update of x on my_table
       referencing new as n
       for each row
       call memcached_set(n.id, n.x);
0 rows inserted/updated/deleted
ij> create trigger delete_trigger after delete on my_table
       referencing old as o
       for each row
       call memcached_delete(o.id);
0 rows inserted/updated/deleted

Now, if you insert a couple of rows into the table, the values will be readily available in the cache:

ij> insert into my_table values ('one', 'First row'), ('two', 'Second row');
2 rows inserted/updated/deleted
ij> values memcached_get('one'), memcached_get('two');
1
--------------------
First row
Second row

2 rows selected

Also, if you update or delete a row, the changes will be reflected in the cache:

ij> update my_table set x = 'First row - updated' where id = 'one';
1 row inserted/updated/deleted
ij> delete from my_table where id = 'two';
1 row inserted/updated/deleted
ij> values memcached_get('one'), memcached_get('two');
1
--------------------
First row - updated
NULL

2 rows selected

See, it works! :)

Comments:

Knut, you rock!

Posted by David Van Couvering on September 10, 2008 at 03:31 PM CEST #

I knew you would take the challenge!

Please note that you should use SMF to start / stop the memcached server. Check out http://blogs.sun.com/trond/entry/memcached_in_solaris for how to do that :-)

Keep up the good work! I guess the next thing is integrating JavaDB into OpenGrok?

Posted by Trond Norbye on September 11, 2008 at 06:56 AM CEST #

Hey Knut,

Thanks for the simple, elegant example of Java DB's user-defined procedures. A great follow-on example could show how easy it is to wrap a Memcached data set in a Java DB table function. Once the customer does that, they can use their Memcached data as tables, slicing and dicing that data via the full, expressive power of SQL.

Posted by Rick Hillegas on September 12, 2008 at 07:46 AM CEST #

Post a Comment:
Comments are closed for this entry.
About

kah

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today