Can your SQL database do this?

New to LucidDB-dev (and soon 0.9.4) are a handful of scripting modules that support many different languages, such as Jython. Writing code in Java and loading it as a JAR in LucidDB is fun, but cumbersome; thanks to the standards in Java 6 though one can use JavaScript (bundled with Java) or, my preference, Python. As a fun example, let's grab a list of tweets to insert!

There's a Python twitter client here: http://code.google.com/p/python-twitter/ whose dependencies are easy to satisfy. (For Jython, see http://old.nabble.com/Easy_install-on-jython-2.5-td23525500.html for getting an "easy_install" on Jython to get the dependencies.) Just run the Jython's easy_install with "oauth2" and "simplejson" as arguments, and then run jython build.py install inside the twitter client's download folder. If you're good to go with Jython, go get a dev version of LucidDB. Make sure you add your Jython JAR to the classpath.gen file before starting it! For me, that's /home/kevin/jython2.5.2/jython.jar.

There is a caveat here: because we're relying on packages in Jython's site-packages folder, and because we're using the jython.jar in a ScriptEngine behind the scenes, we have to load the sys path ourselves. Fortunately this is done by adding the code import site to our Python, which the standard Jython does behind the scenes by itself.

Here then is our script:

import site

from java.sql import *

conn = DriverManager.getConnection("jdbc:default:connection")

# optionally do these

sql = """create or replace schema twitter

create table twitter.messages(message varchar(140))"""

for st in sql.split("\n"):

  ps = conn.prepareStatement(st)

  ps.execute()



# load twitter

import twitter

api = twitter.Api()

statuses = api.GetUserTimeline("Jachy")

sql = "insert into twitter.messages values "

sql += ",".join( ("(?)" for e in statuses) )

ps = conn.prepareStatement(sql)

idx = 1

for st in statuses:

  ps.setString(idx, st.text)

  idx += 1



ps.execute()

conn.close()

Notice the optional part is just to show that you can do anything. Normally we'd create the tables outside the script. Anyway, how do we run it? With LucidDB you can either run the script by saving it and passing in a filename, or instead of a filename you can just give a raw string to execute. I coded the script without single quotes (which are string delimiters in LucidDB) or semicolons (which LucidDB just freaks out on), so we can load it in a string. Here is my sqlLineClient output:

Connecting to jdbc:luciddb:http://localhost

Connected to: LucidDB (version 0.0.0)

Driver: LucidDbJdbcDriver (version 0.0)

Autocommit status: true

Transaction isolation: TRANSACTION_REPEATABLE_READ

sqlline version 1.0.8-eb by Marc Prud'hommeaux

0: jdbc:luciddb:http://localhost> call sys_root.change_default_character_set_to_unicode();

No rows affected (0.367 seconds)

0: jdbc:luciddb:http://localhost> call applib.execute_script('python', 'import site

. . . . . . . . . . . . . . . . > from java.sql import *

. . . . . . . . . . . . . . . . > conn = DriverManager.getConnection("jdbc:default:connection")

. . . . . . . . . . . . . . . . > # optionally do these

. . . . . . . . . . . . . . . . > sql = """create or replace schema twitter

. . . . . . . . . . . . . . . . > create table twitter.messages(message varchar(140))"""

. . . . . . . . . . . . . . . . > for st in sql.split("

"):

. . . . . . . . . . . . . . . . >   ps = conn.prepareStatement(st)

. . . . . . . . . . . . . . . . >   ps.execute()

. . . . . . . . . . . . . . . . > 

. . . . . . . . . . . . . . . . > # load twitter

. . . . . . . . . . . . . . . . > import twitter

. . . . . . . . . . . . . . . . > api = twitter.Api()

. . . . . . . . . . . . . . . . > statuses = api.GetUserTimeline("Jachy")

. . . . . . . . . . . . . . . . > sql = "insert into twitter.messages values "

. . . . . . . . . . . . . . . . > sql += ",".join( ("(?)" for e in statuses) )

. . . . . . . . . . . . . . . . > ps = conn.prepareStatement(sql)

. . . . . . . . . . . . . . . . > idx = 1

. . . . . . . . . . . . . . . . > for st in statuses:

. . . . . . . . . . . . . . . . >   ps.setString(idx, st.text)

. . . . . . . . . . . . . . . . >   idx += 1

. . . . . . . . . . . . . . . . > 

. . . . . . . . . . . . . . . . > ps.execute()

. . . . . . . . . . . . . . . . > conn.close()

. . . . . . . . . . . . . . . . > ');

No rows affected (2.05 seconds)

0: jdbc:luciddb:http://localhost> select * from twitter.messages;

MESSAGE  I'm the [un]proud owner of a COBOL online manual that'll die on June 1st. Do I dare give it a serious reading? How much do COBOL devs make?



MESSAGE  Ha, take that AT&T. I just curl mirror'd that site you're going to stop serving on June 1st.



MESSAGE  I'm so happy, I have ssh to my computer again.



MESSAGE  @rationalists Hurr. Not really. I bug myself when I think about a joke and realize it's not funny because it's not accurate. :/



MESSAGE  @abuhatem @TheYoungTurks How in the world is that rational? Ah well. Heard of neocameralism?



MESSAGE  A Lisp Alien friend drew a month ago after asking me what to draw http://kevinmccloud.deviantart.com/art/Lisp-Alien-by-Estic-205626045 #lisp



MESSAGE  @bookhling Human lives are fractals, your age is your level of zoom...



MESSAGE  All you non-atheists being raptured today? Ask god if he thinks UTF-8 is the best character coding scheme ever.



MESSAGE  Couldn't sleep, so I ported my PHP blog over to unicode. http://www.thejach.com/view/2011/5/hello_unicode



MESSAGE  Did Eragon get translated into Spanish recently or something?



MESSAGE  Bit by the JSON-requiring-double-quotes bug again. Too used to choice!



MESSAGE  @dhewlett I heard Linus Torvalds can recover files from /dev/null.



MESSAGE  My mom just threw away a bottle of mustard that was in the fridge whose sell-by date was Dec. 22nd, 07.



MESSAGE  I have bad priorities. I'd rather add utf-8 support on the PHP side of my blog (and do the code audit) than implement new comment emails.



MESSAGE  "The wicked flee when no man pursueth: but the righteous are bold as a lion." doesn't seem to encourage religious people to debate.



MESSAGE  Okay, closet Star Wars fan satiated.



MESSAGE  Sir, would it help if you told him it was my pants? #replaceawordinastarwarslinewithpants



MESSAGE  I almost forgot. There's a battle going on! Right here in our

pants. Come and look! #replaceawordinastarwarslinewithpants



MESSAGE  Where are those pants you intercepted? #replaceawordinastarwarslinewithpants



MESSAGE  From my blog: The FFP Machine on an FPGA (sorta) http://www.thejach.com/view/2011/5/the_ffp_machine_implemented_with_an_fpga



20 rows selected (0.122 seconds)

0: jdbc:luciddb:http://localhost>

Pretty cool, huh? (Note the first command I sent was to tell LucidDB to tell the fresh installation it should store unicode.)

---

Posted on 2011-05-25 by Jach

Tags: LucidDB, python, sql

Permalink: https://www.thejach.com/view/id/175

Trackback URL: https://www.thejach.com/view/2011/5/can_your_sql_database_do_this

Back to the top