SQLiteでテーブルの一覧を取得する場合、コマンドの「.schema」を使う方法もあるが、面倒な時もあるので、以下のようにSELECT文を使う。
SELECT * FROM sqlite_master WHERE type = 'table' ORDER BY name;
WHERE句の条件を外すと、オブジェクトの一覧になって、INDEXの情報なんかも取得できる。
さて、6月も中盤。
4月から3か月間かけて行う新人研修も終盤だが、どうも、今年はイマイチ。
新人がイマイチなのか、我々がイマイチなのか、どちらもダメなのか・・・
それはともかく、中学生や高校生が読めるような漢字を読めない人がいるのがまずウンザリ。
昨今、高等教育の無料化を声高に言う人がいるが、そもそも、初等・中等教育をしっかりしてほしい。
小学生でも読めそうな漢字も読めない大人って、社会に必要なのか？
初歩的な現代国語ぐらいちゃんと勉強させてほしいものだ。
世の中には、ありがた迷惑とか、小さな親切大きなお世話とか、そういうことがわからない人が一杯いるようだ。
そういう人は、「やるな！」「やらないでくれ！」っていっても、「やる」のだなぁ。
人のやること横取りするんじゃないよ！と、叫びたかった、今日の朝。
それはそうと、SQLiteでSELECT文を使ってUPDATEしたい！というのがあって、
そうすると、まぁ、
UPDATE table1 SET col1 = (SELECT col1 FROM table2 WHERE .... )
みたいにすればできるぜ！みたいなことはいっぱい書いてあるんだけど・・・
そうじゃないんです！
JOINで連結させて、ホゲホゲホゲ・・・とやりたいんです！
という要求は満たされないわけ。
で、調べた。
SQLiteはWITH句が使えるらしい。
んで、WITH句をうまく活用して、UPDATEできるらしい。
他のDBのSQL文を
UPDATE table1 SET
col1 = a.col1
, col2 = a.col2
FROM (SELECT .... FROM .... INNER JOIN ....) a
WHERE table1.id = a.id
とすると、
WITH句を使ったSQL文は
WITH a AS (
SELECT .... FROM .... INNER JOIN ....
)
UPDATE table1 SET
col1 = (SELECT col1 FROM a WHERE table1.id = a.id)
, col2 = (SELECT col2 FROM a WHERE table1.id = a.id)
みたいな感じ。
で、これを私が愛用する「A5:SQL Mk-2」（SQL開発ツール）で実行したのだが、うまくいかずに、「あれ？できないの？」とか思って悩んだのだが、ツール上ではうまくいかないだけで、SQLiteのコマンド実行では、ちゃんと動いた。
ホント、統一させてほしいんですけど。
Processing開発。
BezierSQLibライブラリを使ってSQLiteのDBにつないでいるのだが、１つ問題が。
というのも、エラーになったときExceptionが発生しない。
ライブラリ内部で例外処理までしてるのは兎も角、結果がエラーになってもエラーを判別できるフラグがない。
DBにLockがかかってる時にUPDATEしても、コンソールにエラーメッセージは出るが、それだけ・・・
Rollbackするタイミングがつかめない。
このままではマズイ。困った。
こうなったら、直接jdbcライブラリを利用しよう！となった。
プログラムソースのpdeファイルが入っているフォルダの下に「data」フォルダを作って、その中にDBファイルの「test.db」を入れる。
「data」フォルダと同じ並びに「code」フォルダを作って、その中にライブラリ「sqlite-jdbc-***.jar」ファイルを入れておく。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
// ------------------- ( 略 ) -------------------
void getData() {
Connection con = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
con = DriverManager.getConnection("jdbc:sqlite:"+ dataPath("test.db"));
println("Opened database successfully");
stmt = con.createStatement();
stmt.executeUpdate("INSERT INTO m_users (user_cd ,user_name) VALUES ('1010101', 'Lily');");
stmt.close();
con.close();
} catch ( Exception e ) {
println( e.getClass().getName() + ":" + e.getMessage());
}
}
Macでいけたから、あとはWindowsでも確認しよう。
データが1万→2万になったところで、急激にUPDATEの速度が遅くなってしまった。
SQLiteって速いって聞くんだけど・・・
ということで、チューニングについて調べてみた。
INSERTが遅いってのがあったけど、Transactionは入れてるし。
UPDATEが遅いっていわれても、そこを変えるのは・・・と思ったので、以下のサイトを参考に、コマンドプロンプト上でDBの設定をかえた。
パフォーマンスを追求するためのSQLite設定 | ITハンドブック
SQLite「PRAGMA statement」
sqlite> PRAGMA main.journal_mode = PERSIST;
sqlite> PRAGMA main.synchronous=0;
mainはスキーマ名。
で、どうなったかというと、設定を変更後、一回VACUUMをしてから確認したら、劇的に速度向上？
＜追記＞
あぁ・・・勘違いしてた。
どうやら、DBを開いているときだけ設定した状態が保持される様子。
閉じちゃったら元のDefault設定に戻るようだ。
たしかに、開き直したら設定が消えちゃってたよ。
開いた直後だな。設定するのは。
＜追記2 2016/09/14＞
さて、さて、いろいろ試した結果。
結果的に設定を変えて高速化したか？といえば、さほどな気はした。
やはり、他のSQLと同様、INDEXをはるってのが１番で、あとはなるべく実行しない方法を探すのがよいようで。
次に、TRANSACTION。これはかなり大きかった。1,2万行を順にINSERTしていく手前でBEGIN→終了時COMMITとするだけだが、これも速度がだいぶん速くなったように感じた。
DELETE＆INSERTよりREPLACEを使うのがいいと書いている人もいるが、
目からウロコだったのは、INSERT文のVALUES部にUNION ALLしたSELECT文を用いるやり方。
SQLiteで最も速く複数行INSERTする方法 | transhumanist note
要は、複数行の実行をお纏めしちゃうわけで、これはなかなか面白いやり方だと思った。
まぁ、あとできることはVACUUMしてゴミを減らすことかな・・・