@start javaw  -cp "h2-1.4.179.jar;postgresql-8.3-607.jdbc4.jar;%H2DRIVERS%;%CLASSPATH%" org.h2.tools.Console -webAllowOthers -tcpAllowOthers -pgAllowOthers %*
@if errorlevel 1 pause

便利な関数

CSVREAD, CSVWRITE

これで読める．

SELECT * FROM CSVREAD('test.csv');

属性名の指定(無指定の時は1行目が属性名として用いられる)，文字コード，区切り文字，引用符文字(カンマからカンマまでを囲む文字)，エスケープ文字を指定しても読み込める．特に指定したく無いときはnullを渡せば良い．

 CSVREAD('game_logs.txt','ID,SESSION_ID,MESSAGE, ROUND_NUM,','utf-8',',','"','\');

Excelからcsv作った時の定番はこの変かな．char(9)はタブ区切りということ．

SELECT * FROM CSVREAD('HumanSubjects.csv', null, null, char(9));
INSERT INTO TESTTABLE SELECT * FROM CSVREAD('HumanSubjects.csv', null, null, char(9));

SQL

UNIONで和集合が取れる．同じクエリを複数のテーブルに投げて結果をまとめるのに便利．
順番を並び替えたいならUNIONしたあとにORDER BYすればOK．H2に限らないけど，ORDER BY A, Bとすれば，複数の属性を使ってソート出来る．

CREATE VIEW FP AS
SELECT * FROM
(SELECT WASEDA01_DECISION_LOGS.CREATE_AT, SESSION_ID, WASEDA01_USERS.USER_ID, ROUND, VALUE FROM WASEDA01_DECISION_LOGS JOIN WASEDA01_USERS  ON WASEDA01_DECISION_LOGS.USER_ID=WASEDA01_USERS.ID  WHERE NAME='proposition')
UNION
(SELECT WASEDA02_DECISION_LOGS.CREATE_AT, SESSION_ID, WASEDA02_USERS.USER_ID, ROUND, VALUE FROM WASEDA02_DECISION_LOGS JOIN WASEDA02_USERS  ON WASEDA02_DECISION_LOGS.USER_ID=WASEDA02_USERS.ID  WHERE NAME='proposition')
UNION
(SELECT WASEDA03_DECISION_LOGS.CREATE_AT, SESSION_ID, WASEDA03_USERS.USER_ID, ROUND, VALUE FROM WASEDA03_DECISION_LOGS JOIN WASEDA03_USERS  ON WASEDA03_DECISION_LOGS.USER_ID=WASEDA03_USERS.ID  WHERE NAME='proposition')
 ORDER BY SESSION_ID, ROUND;

CASEWHENという関数が使える．一つ目の引数が条件(述語)，二つ目の引数が条件が真の時の式，三つ目の引数は条件が偽の時の式．
CAST関数が使える．CAST(val AS type)という書式で使う．

DROP VIEW  FP_SP;
CREATE VIEW FP_SP AS 
SELECT FP.CREATE_AT, FP.SESSION_ID, FP.ROUND, FP.USER_ID AS FP, CAST(FP.VALUE AS INT) AS PROPOSITION,  CASEWHEN(SP.VALUE='no', 0, 100000-FP.VALUE * CASEWHEN(SP.VALUE='no', 0, 1))  AS PROFIT_OF_FP, SP.USER_ID AS SP, SP.VALUE AS YES_OR_NO, FP.VALUE * CASEWHEN(SP.VALUE='no', 0, 1) AS PROFIT_OF_SP, CASEWHEN(SP.SESSION_ID<='proc-20140519-165442-428', '非匿名', '匿名') AS ANONYMITY FROM FP JOIN SP ON (FP.SESSION_ID=SP.SESSION_ID AND FP.ROUND=SP.ROUND)

CASEWHEN関数の結果を他の関数の引数として渡すことも出来る．

SELECT ANONYMITY, AVG(PROPOSITION), AVG(PROFIT_OF_FP), AVG(PROFIT_OF_SP), SUM(CASEWHEN(YES_OR_NO='no', 0, 1))  AS SUM_OF_YES FROM FP_SP  GROUP BY ANONYMITY;

SELECT * From (SELECT * FROM TABLE_A WHERE ATTR=1) JOIN TABLE_B ON ....のようにSELECTの中でSELECTができる．

他の便利そうな機能

全文検索

ORマッパー

ORマッパーにはpersistを使っている．特に不満はない．でも，apaheのプロジェクトの奴の方が寿命が長いのかなぁ．Apache Commons DbUtils - nodchipの日記．｢薄い｣JavaのO/Rマッパーの紹介 - DbUtils、Persist、Butterfly Persistence - public static void mainあたりを参照．

参考にしたページ

[ツッコミを入れる]

2014-06-24(Tue) [長年日記] 編集

[GDocs][Java][JSON-RPC] JSON-RPCサーバとJavaScriptを使ったGoogle Spreadsheetのデータの読み込み，追加，編集，削除

今さらながら，Spreadsheets Data APIを使うための基礎知識（2）：APIでGoogleスプレッドシートの追加／更新／削除 (1/3) - ＠ITを参考に，Google Spreadsheetにアクセスするコードを書いて，動かして見た．更新がやたら遅い印象．

2枚のスプレッドシートから1枚を選び出し，そのスプレッドシートに含まれる1枚のワークシートから1枚のワークシートを取り出す => 1000ms
2列100行のデータを読み込む => 500ms
2列100行のデータを読み込み，特定の行(20行)を選び出す => 500ms (全行取得してから自分でフィルタリング)
2列100行のデータを読み込み，特定の行(20行)の値を更新 => 10s(10000ms)
2列100行のデータを読み込み，特定の行(1行)の値を更新 => 10s(10000ms)
2列100行を持つ表に対して，1行を挿入 => 500ms

Googleが提供しているサンプルの様にキャッシュしたらどれくらい早くなるのかは試していない．

コードはこの辺においておく．JsonRPCService/src/org/nkjmlab/gdata at master · yuu-nkjm/JsonRPCService

JSON-RPCのサーバと組み合わせて，クライアントからサーバにJSONを投げてGoogle Spreadsheetからの読込/への書込をするのが狙いだった．Javaのサービスクライアント，JavaScriptのサービスクライアントから呼び出せることを確認した．まずはここまで．

トラブルシューティング

6 24, 2014 5:39:47 午後 jp.go.nict.langrid.servicecontainer.handler.jsonrpc.JsonRpcDynamicHandler handle
警告: IOException occurred.
ClientAbortException:  java.net.SocketException: Software caused connection abort: socket write error

というエラーが，サーバ側で頻発していた．これはJavaScriptのajaxでクエリを投げるところが3秒でタイムアウトしていて，サーバ側から3秒以内に結果が戻ってこなかった場合，サーバから切断してしまう．サーバ側は3秒たった後に結果を返そうとしても接続が切れてしまっているので，上記のエラーが出る．ということだと思う．