FDWってなによ？

FDW(Foreign Data Wrappe),外部データラッパっていうやつで、SQL/MED(Management of External Data)の規格の一つで簡単にいうと、PostgreSQLにQUERYを発行したら、あら不思議、外部の(たとえばMySQLとかCSV)データが取得できるという変態機能。 twitterAPIに変更あるらしいから今後はわからにけど、twitterのデータを取ってくるとかもできる。というかラッパーが用意されてる。(ちなみに使ってみたら楽しかったｗ)

PostgreSQLに興味がある人向けにまとめてみた。｜PostgreSQL｜お仕事メモ｜Pictnotes

それはワクテカ過ぎる... だがしかし SQLite3 でも出来る！きっと出来る！

Big Sky :: SQLite で twitter のタイムラインを select する。

Oracleでもきっとできる！というかできた！
ser1zw/twitter-public-timeline-for-oracle

f:id:ser1zw:20121005015127p:plain

ご家庭のOracleで、いつでも新鮮なPublic timelineがお楽しみいただけます！

環境

Oracle Database 11g Release 2 Express Edition for Linux x64

ソースコード

GitHubに置いてあります。 ser1zw/twitter-public-timeline-for-oracle

インストールには、後述の

utlhttp.sql を実行してUTL_HTTPパッケージをセットアップ
ネットワークのアクセス制御リスト（ACL）の設定

を行ったあと、SQL*Plusで同梱のinstall.sqlを実行してください。

$ sqlplus YOUR-USER-NAME/YOUR-PASSWORD@localhost:1521/XE @install.sql

インストールすると、TWITTER_PUBLIC_TIMELINEというビューが作成されます。これをSELECTすれば、Public timelineが取得できます。

SELECT created_at, screen_name, text FROM TWITTER_PUBLIC_TIMELINE WHERE LOWER(text) LIKE '%http%';

アンインストールには、同梱のuninstall.sqlを実行してください。

仕組み

PL/SQLのHTTP機能でTwitterのPublic timeline APIからXMLデータを取ってくる
取ってきたXMLデータをOracleのXML処理機能でリレーショナルデータにマッピングする
処理したデータをテーブル・ファンクション（表関数）で返す

という感じで処理をしています。
以下、それぞれどんな感じなのかざっくり解説！

PL/SQLのHTTP機能

OracleのPL/SQLでは、UTL_HTTPパッケージの機能を使うことで、HTTPアクセスができます。

TwitterのPublic timelineからGETするのはこんな感じです。

DECLARE
  url VARCHAR2(100) := 'http://api.twitter.com/1/statuses/public_timeline.xml';
  request UTL_HTTP.REQ;
  response UTL_HTTP.RESP;
  buff VARCHAR2(4000);
BEGIN
  UTL_HTTP.SET_RESPONSE_ERROR_CHECK(FALSE);
  request := UTL_HTTP.BEGIN_REQUEST(url, 'GET');
  UTL_HTTP.SET_HEADER(request, 'User-Agent', 'Mozilla/4.0');
  response := UTL_HTTP.GET_RESPONSE(request);

  IF response.status_code = 200 THEN
    BEGIN
      LOOP
        UTL_HTTP.READ_TEXT(response, buff, LENGTH(buff));
        DBMS_OUTPUT.PUT_LINE(buff);
      END LOOP;
      UTL_HTTP.END_RESPONSE(response);
    EXCEPTION
      WHEN UTL_HTTP.END_OF_BODY THEN
        UTL_HTTP.END_RESPONSE(response);
    END;
  ELSE
    UTL_HTTP.END_RESPONSE(response);
    DBMS_OUTPUT.PUT_LINE('Fail: STATUS CODE: ' || response.status_code);
  END IF;
END;

ただし、使用するにはちょっとだけ準備が必要です。

utlhttp.sql を実行してUTL_HTTPパッケージをセットアップ

スクリプトを実行するだけです。 $ORACLE_HOME/rdbms/admin に utlhttp.sql というスクリプトがあるので、SQL*Plusなどで実行します。 Oracleが動いているサーバでこんな感じのコマンドをたたけばOKです（パスワードやSIDは必要に応じて変更してください）。

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus SYS/YOUR-PASSWORD@localhost:1521/XE AS SYSDBA @utlhttp.sql

ネットワークのアクセス制御リスト（ACL）の設定

11gから外部ネットワークへのアクセスの際にアクセス制御が行われるようになり、アクセスするユーザやホストを設定しなければならなくなりました。ユーザ SCOTT がホスト api.twitter.com にアクセスできるようにするには、こんな感じで設定すればOKです。

DECLARE
  user VARCHAR2(30) := 'SCOTT';
BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('www.xml', 'WWW ACL', user, TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('www.xml', user, TRUE, 'resolve');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('www.xml', 'api.twitter.com');
END;

ちなみに、最後のアクセス制御リストへの追加を

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('www.xml', '*');

にすれば、どこでもアクセスできるようになります。

参考：DBMS_NETWORK_ACL_ADMIN

XML処理機能

Oracle XML DBの機能を使うと、XMLデータをリレーショナルデータとして（テーブルに入ったデータと同じような感じで）扱えます。今回はPublic timelineのXMLデータから各ツイートをとり出すために、XMLTableを使っています。これがなかなか便利な機能でして、例えばこんな感じのHOGE_XMLテーブルがあったとして

-- HOGE_XMLテーブル
CREATE TABLE HOGE_XML (
  HOGE_ID INTEGER PRIMARY KEY,
  XML_DATA CLOB
)

こんなXMLデータがHOGE_XMLテーブルのXML_DATA列に入っている場合

<?xml version="1.0" encoding="UTF-8"?>
<statuses type="array">
<status>
  <id>123456789</id>
  <text>ほげほげ</text>
  <user>
    <id>12345</id>
    <name>hoge</name>
  </user>
</status>
<status>
  <id>234567890</id>
  <text>ふがふが</text>
  <user>
    <id>23456</id>
    <name>fuga</name>
  </user>
</status>
<status>
  <id>345678901</id>
  <text>ふーばー</text>
  <user>
    <id>34567</id>
    <name>foo</name>
  </user>
</status>
</statuses>

こんな感じのSQLを実行すれば、1つの<status>の要素が1つのレコードとして返ってきます。もちろんWHERE句も使えます。超素敵です。

SELECT
  X.*
FROM
  HOGE_XML H,
  XMLTABLE('/statuses/status'
    PASSING XMLTYPE(H.XML_DATA)
    COLUMNS
      ID VARCHAR2(10) PATH 'id',
      TEXT VARCHAR2(1000) PATH 'text',
      USER_ID VARCHAR2(10) PATH 'user/id',
      USER_NAME VARCHAR2(100) PATH 'user/name'
  ) X
WHERE
  X.USER_NAME IN ('hoge', 'fuga')

f:id:ser1zw:20121005015402p:plain

テーブル・ファンクション（表関数）

テーブル・ファンクションというのは、

テーブル・ファンクションは、行のコレクション(ネストした表またはVARRAY)を戻すユーザー定義のPL/SQLファンクションです。SELECT文のTABLE句の内部でテーブル・ファンクションを起動することで、データベース表のようにこのコレクションから要素を選択できます。

http://docs.oracle.com/cd/E16338_01/appdev.112/b56260/tuning.htm#i52954

です。要は、

SELECT * FROM TABLE(hoge_func(123));

みたいな感じで戻り値をそのままSELECTできるストアドファンクションです。処理のパイプライン化もできます。

ただしSELECTするときにTABLE句が必要になるので、今回は、

CRATE VIEW TWITTER_PUBLIC_TIMELINE
AS
SELECT
  *
FROM
  TABLE(GET_TWITTER_PUBLIC_TIMELINE);

のようなビューを作成し、毎回TABLE句をつけないで済むようにしてみました。

まとめ

そんなわけでOracleでもTwitterのタイムラインを取ってくることができたわけですが、2013年3月5日でXMLのサポートが打ち切られちゃうので、今回のやつは使えなくなります。認証もしてないし。

元ネタであるPostgreSQLのtwiter_fdwでは、WHERE句に q = 'hoge' で指定した条件がSearch APIにパラメータとして渡されるようになってるみたいですね。Oracleだとこういう仕組みは作れるのかな…？

ser1zw's blog

OracleでTwitterのタイムラインをSELECTする

環境