2012年2月5日日曜日

SQLiteの制約衝突時アルゴリズム「ON CONFLICT句」を使って「表示履歴+お気に入り機能」を作る

今回はAndroidというよりはSQLiteの使い方的なアレです。
あいかわらずですます語尾がおかしいですが気楽なメモです。

■「Zusaar プラグイン for twicca」のあぷでv0.5.x

タイトルの話にもつながりますが、プラグインアプリのあぷでしました。

Zusaar プラグイン for twicca - Android マーケットのアプリ
<https://market.android.com/details?id=com.miquniqu.android.twiccaplugins.zusaar>

主な内容は以下。

  1. 「twicca風な背景、文字色、レイアウトに微調整、参加者・主催者表示の仕方変更」
  2. 「表示履歴+お気に入り機能」

1つめは、配色なんかを本家にあわせてた。あと主催者・参加者の表示状態がわかりにくかったので、一部文言を切り替えるのではなく、トグルでフォント色を切り替える方向にしてみた。初期リリースよりはだいぶわかりやすくなった気がしますね。

2つめのは、このアプリ使ってると「○○さんの主催するイベントが知りたい」ということが良くあってですね。
具体的に言うと「@yusukeyさんの主催する(TwitterAPI勉強会の)イベントが知りたい」ということです。

「Zusaar プラグイン for twicca」はその名の通りtwiccaありきのアプリなので。
任意のユーザのイベント一覧を参照するには
 「twiccaで対象ユーザのプロフィールを開いて」→「プラグインアプリへインテント」
といった手順をとる必要があります。相当面倒です。
twiccaには任意のユーザプロフィールへのショートカットを作成する機能もあるので、そんなに直接見たいなら@yusukeyさんのプロフィールショートカットをホーム画面に貼り付けるという手段もありますが、ホーム画面の1枠がそんな理由で埋まるのはあんまりなので却下です。

まぁ本家(Zusaarサイト)のユーザープロフィール見に行けば解決しますが、スマフォのブラウザはいまいちです。

それじゃぁってことで、下の画面のような表示履歴を作ってみました。

device-2012-02-06-014527

■なぜお気に入りリストじゃなくて表示履歴?

最初は「お気に入りのユーザリスト」機能を、、と思いました。
が、さぁリストつくるぞと能動的に作成するというのも面倒ですよね。

いっそのこと「最新○○件の表示履歴が自動的に記録されている。その中でお気に入りを設定したら優先的に上位に表示する。」ような軽いものがいいんじゃないかということです。

■SQLiteで表示履歴の管理をする

とはいっても、ユーザ表示するたびにSQLiteでINSERTしていって、、、とすると

id screenname date
1  miquniqu    12:00:00
2  username1 12:01:00
3  username2 12:02:00
4  miquniqu    12:03:00

こんな感じのデータになる。
ここから最新の10件取得するSQLは、、、、降順指定で検索することになるので

select screenname,date from user_history order by desc

こんな感じ?
でも、そのまま作ったら自分の名前だらけになりそうです。
電話の通話履歴のように、同じユーザの操作にもそれぞれ意味があるのならこれでいいですが、最新の1回だけほしいです。
とすると

select screenname,max(date) from user_history group by screenname

こんな感じでユーザ毎に最新の日付情報のみ取得かな。(SQL文の検証してない、、)
グルーピングした上で、複数件ほしいとかなら

下記のテーブルから各IDごとの最新2件のデータを一回のSQLで抽出.. - 人力検索はてな <http://q.hatena.ne.jp/1268792573>

こんな感じぽいです。今回は必要ないですが。

■操作する都度レコードが増えていったりとかなんかやだ

上記の方法だと、同じユーザの表示履歴でもどんどんレコードを積んでいくのでちょっと嫌。
欲しいのはユーザ毎に1件だけなので。
あと、その1件もすでに登録済みかどうか確認しながら更新するとか面倒ですね。

■SQLiteの制約衝突時アルゴリズム「ON CONFLICT句」を使おう

ON CONFLICT句って何?ということで下記のサイト見てきてください。(丸投げ

SQLite Query Language: ON CONFLICT clause <http://www.sqlite.org/lang_conflict.html>

SQLite が認識できるクエリー言語 <http://net-newbie.com/sqlite/lang.html#conflict>

ON CONFLICT句の指定にもいろいろありますが、アプリとして使いやすそうなのは「IGNOREと、REPLASE」あたりでしょか。

[IGNORE]
制約で衝突したらその衝突要因となった操作をあきらめるけど、全体の操作は正常として進める。

[REPLASE]
制約で衝突したらその衝突要因となった操作で衝突対象を上書きし、全体の操作は正常として進める。

という理解です。

■俺の考えた使いどころ例

使い方が正しいかは知りません。

IGNOREの使いどころ例

 同じ情報や似た情報の登録事象が何度も発生し、初回のみを登録したい場合。

具体例1)
 Twitterから同一のツイートを重複して受信した場合、1件目のみをDB保存する。
具体例2)
 
TwitterからTwitterStreamingAPIでツイートを受信してたら未受信ツイートの削除ツイートを先行して受信してしまったので、削除ツイート側を保存して、後からくるツイートを除外したい。

REPLASEの使いどころ例

 管理上は同じ情報だが一部項目が更新されている場合。

具体例1)
 しょぼいカレンダーからアニメ番組情報を受信してDB保存した。数日後に一部項目の更新が行なわれたため最新情報として上書き更新したい。1件づつ確認するのはいやだ。

■今回の例に当てはめて考える

まず、表示履歴機能向けのテーブル「user_history」を以下のカラムで作成する。

[user_historyテーブル]
_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
SCREENNAME TEXT NOT NULL
DATE REAL NOT NULL

user_historyテーブルはscreennameでユニークなものとする。(制約設定)
このテーブルはユーザ指定の操作が発生するたびに、screennameに対する操作時間を都度INSERTしていく。
その際、「REPLACE」を指定すればscreennameに対する操作時間が最新として上書きされる。
※自分でSELECT、UPDATEしなくて良い。
表示履歴用の検索の際は、dateをdesc指定で検索するだけ。

次にお気に入り機能向けのテーブル「user_favorite」を以下のカラムで作成する。

[user_favoriteテーブル]
_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
SCREENNAME TEXT NOT NULL
FAVORITEFLAG INTEGER NOT NULL

user_favoriteテーブルもscreennameでユニークなものとする。(制約設定)
このテーブルはuser_historyと1:1になる情報としてuser_historyのINSERT時に合わせてIGNORE指定でINSERTしておく。
FAVORITEFLAGは初期値0としてINSERTし、お気に入り指定する際は1にUPDATEする。

初回の履歴登録時には両テーブルともレコードが新規作成される。2回目以降はuser_historyのみ日付更新されてuser_favoriteは更新されないためお気に入り情報が初期化されずにすむ。

たぶんこれでおk

■実際に作ってみる

DBHelperのonCreateで以下のテーブルを作成する。

  1: CREATE TABLE IF NOT EXISTS user_history
  2: (
  3: _ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  4: SCREENNAME TEXT NOT NULL,
  5: DATE REAL NOT NULL
  6: )
  7: 
  8: CREATE UNIQUE INDEX IF NOT EXISTS user_history_uidx1 ON user_history
  9: (
 10: SCREENNAME
 11: )
 12: 
 13: CREATE TABLE IF NOT EXISTS user_favorite
 14: (
 15: _ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
 16: SCREENNAME TEXT NOT NULL,
 17: FAVORITEFLAG INTEGER NOT NULL
 18: )
 19: 
 20: CREATE UNIQUE INDEX IF NOT EXISTS user_favorite_uidx1 ON user_favorite
 21: (
 22: SCREENNAME
 23: )
 24: 

あと、各種登録検索はこんな感じ。
insertHistoryとinsertFavoriteは同一コネクション・トランザクション指定で上位から呼び出す。

  1: 
  2:   // 履歴情報:1件登録
  3:   private long insertHistory(SQLiteDatabase _db, String screenname) {
  4: 
  5:     // REPLACE指定で元のデータを上書きする
  6:     StringBuilder sb = new StringBuilder();
  7:     sb.append("INSERT OR REPLACE INTO ");
  8:     sb.append("user_history");
  9:     sb.append("(SCREENNAME,DATE)");
 10:     sb.append("values");
 11:     sb.append("(?,julianday('now'));");
 12: 
 13:     // IDは自動採番
 14:     SQLiteStatement stmt = _db.compileStatement(sb.toString());
 15:     stmt.bindString(1, screenname);
 16: 
 17:     long retID = stmt.executeInsert();
 18: 
 19:     stmt.close();
 20: 
 21:     return retID;
 22:   }
 23: 
 24:   // 履歴情報(お気に入り):1件登録
 25:   private long insertFavorite(SQLiteDatabase db, String screenname) {
 26: 
 27:     // IGNORE指定で元のデータを残して処理をなかったことにする
 28:     StringBuilder sb = new StringBuilder();
 29:     sb.append("INSERT OR IGNORE INTO ");
 30:     sb.append("user_favorite");
 31:     sb.append("(SCREENNAME,FAVORITE)");
 32:     sb.append("values");
 33:     sb.append("(?,?);");
 34: 
 35:     // IDは自動採番
 36:     SQLiteStatement stmt = db.compileStatement(sb.toString());
 37:     stmt.bindString(1, screenname);
 38:     stmt.bindLong(2, 0);
 39: 
 40:     long retID = stmt.executeInsert();
 41: 
 42:     stmt.close();
 43: 
 44:     return retID;
 45:   }
 46: 
 47:   // 履歴情報(お気に入り):1件更新
 48:   private void updateFavorite(SQLiteDatabase db, String screenname, boolean favorite) {
 49: 
 50:     StringBuilder sb = new StringBuilder();
 51:     sb.append("UPDATE ");
 52:     sb.append("user_favorite");
 53:     sb.append(" SET FAVORITE = ?");
 54:     sb.append(" WHERE");
 55:     sb.append(" SCREENNAME = ?;");
 56: 
 57:     // IDは自動採番
 58:     SQLiteStatement stmt = db.compileStatement(sb.toString());
 59: 
 60:     // 受信状態:受信無し=0、保存有り=1
 61:     int index = 1;
 62: 
 63:     if (favorite) {
 64:       // 設定
 65:       stmt.bindLong(index++, 1);
 66:     } else {
 67:       // 解除
 68:       stmt.bindLong(index++, 0);
 69:     }
 70:     stmt.bindString(index++, screenname);
 71: 
 72:     stmt.execute();
 73: 
 74:     stmt.close();
 75: 
 76:     return;
 77:   }
 78: 
 79:   // 件数指定検索
 80:   public List<UserData> selectLimit(SQLiteDatabase _db, int _limit) {
 81:     String methodName = "selectLimit";
 82: 
 83:     List<UserData> list = new ArrayList<UserData>();
 84: 
 85:     StringBuilder sb = new StringBuilder();
 86:     sb.append("select uh.SCREENNAME, datetime(uh.DATE) as DATE, uf.FAVORITE from ");
 87:     sb.append("user_history").append(" uh");
 88:     sb.append(" LEFT JOIN ").append("user_favorite").append(" uf ON uh.SCREENNAME=uf.SCREENNAME");
 89: 
 90:     sb.append(" ORDER BY uf.FAVORITE DESC, uh.DATE DESC");
 91:     sb.append(" LIMIT ").append(_limit);
 92: 
 93:     Cursor cur = _db.rawQuery(sb.toString(), null);
 94:     try {
 95:       if (cur.moveToFirst()) {
 96:         do {
 97:           UserData data = new UserData();
 98:           data.screenname = cur.getString(cur.getColumnIndex("SCREENNAME"));
 99:           if (cur.getInt(cur.getColumnIndex("FAVORITE")) == UserData.FLAG_ON) {
100:             data.setFavorite(true);
101:           } else {
102:             data.setFavorite(false);;
103:           }
104: 
105:           list.add(data);
106:         } while (cur.moveToNext());
107:       }
108:     } finally {
109:       cur.close();
110:     }
111: 
112:     return list;
113:   }
114: 

カラム名とか間違ってたりする部分は読み替えてください。
あと、ダイアログ側は何とかしてください。(をぃ


ダイアログ上のListViewにsetOnItemLongClickListenerを実装して、ListAdapterの持っている情報をメモリ反映した上で、adapter.notifyDataSetChanged();lv.invalidateViews();とかしてバイブレートするだけです。


だいたい書きたいことは書けたので今回はこの辺で。

0 件のコメント:

コメントを投稿