SQLite入門

SQLite(エスキューライト)はC言語で使える軽量コンパクトなデータベース管理用のライブラリ関数である。 インストールも使い方も簡単である。

Androidでの使用を中心として、SQLiteの使用方法を調べる。

過去には何度かリレーショナルデータベースを使ったことがあるが、ここ数年は遠ざかっているので、 少しずつ思い出そう。

歩数テーブル作成

SQLiteには日付型がない。UNIXタイムで記録する場合には INTEGER型とする。

CREATE TABLE "walk" (
	"日付"	INTEGER,
	"歩数"	INTEGER,
	PRIMARY KEY("日付")
)

日時を INTEGER型とした場合、 日時の取り出しは datetime(日付, 'unixepoch') とする。 日付だけでよい場合は date(日付, 'unixepoch') とする。

ローカルな日時を取り出すときは datetime(日時, 'unixepoch', 'localtime') とする。 日付だけでよい場合は date(日付, 'unixepoch', 'localtime') とする。

歩数データの登録および変更

とりあえず、次のようにして、データ登録できた。パソコンからDB Browser により、テーブルの内容を確認した。

    static void test(Context context) {
        FeedReaderDbHelper dbHelper = new FeedReaderDbHelper(context);
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("日付", 1234589);
        values.put("歩数", 12345);
        long newRowId = db.insert("walk", null, values);
        db.close();
    }

AndroidでSQLiteのDB操作をする

日々の歩数データを SQLiteで管理した場合、年間での月別平均歩数グラフ、 この１週間/１か月での日々の歩数グラフなどを表示したい。

とりあえず、データベースを gis.db、テーブルを pedometer として、そのカラムは date（日付）、cum_steps（通算カウンタ値） 、sd_steps（シャットダウン直前のカウンタ値）とした。

 CREATE TABLE pedometer ( 
  date integer NOT NULL, 
  cum_steps integer NOT NULL, 
  sd_steps integer NOT NULL, 
  PRIMARY KEY(date) )

カウンタ値はスマホ起動時が 0 で、その後はシャットダウンが起きるまで通算されていく。 date は 20241201(2024年12月1日) というような整数(yyyymmdd)である。 cum_steps はその日の最終カウンタ値である。今日の最終カウンタ値から前日の最終カウンタ値を引けば、今日の歩数となる。

シャットダウンがあった場合、直前のカウンタ値を sd_steps に記録している。 1日に２回以上シャットダウンが起きることはほとんどないが、もしあった場合には、その間の歩数を sd_steps に加算しておく。

このため、その日の cum_steps + sd_steps から前日の cum_steps を引いた値がその日の歩数である。

月別の合計歩数は日々の歩数を足せばよいが、例えば11月の合計歩数は、11月30日の cum_steps に11月分の sd_steps に加えて、 10月31日の cum_steps を引いて求めることもできる。

月別歩数テーブルを作成することも考えられるが、少なくとも当面は、 日々の歩数テーブルの全レコードをメモリ上の配列に読込んで、月別歩数を算出する。 精々、１日１回のことであるから問題ないであろう。

レコードの追加

INSERT INTO pedometer (date, cum_steps, sd_steps)
VALUES (20241130, 19699, 0);

レコードの更新

20241106, 7715, 49460 の 49460 を 0 に変えるには

UPDATE pedometer 
   SET sd_steps = 0
 WHERE date = 20241106;

レコードの挿入または更新

INSERT INTO pedometer (date, cum_steps, sd_steps)
  VALUES(20241203, 33942, 0) 
  ON CONFLICT(date) 
  DO UPDATE SET cum_steps=33942, sd_steps=0;

最新レコードの取り出し

date の値が最も大きいレコードの取り出しは次のようにする。

SELECT date, cum_steps, sd_steps
FROM pedometer
ORDER BY date DESC
LIMIT 1;

全レコードをプログラムで取り出す

query の引数は次の通り。

query(String table, String[] columns, String selection, String[] selectionArgs, 
String groupBy, String having, String orderBy, String limit)

全レコードを日付順に取り出す場合、 SQL では

SELECT * FROM pedometer ORDER BY date ASC;

query("pedometer", new String[]{"*"}, null, null, null, null, "date ASC", null);

rowQueryでは

rawQuery("SELECT * FROM pedometer ORDER BY date ASC", null);

月別平均歩数の算出

最終的にはグラフ表示するがその一歩手前のプログラムを以下に示す。

月別の平均歩数は日々の歩数データが記録されていること前提としている。 欠落があっても、shutdown が起きていないならば、days を使う代わりに、 経過日数を使えば、月別の平均歩数を算出できる。

    static void view(Context context) {
        DbHelper dbHelper = new DbHelper(context);
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        // rawQueryでSELECTを実行
        Cursor cursor = db.rawQuery("SELECT * FROM pedometer ORDER BY date ASC", null);
        int ixDate = cursor.getColumnIndex("date");
        int ixCum_steps = cursor.getColumnIndex("cum_steps");
        int ixSd_steps = cursor.getColumnIndex("sd_steps");
        int[][] data = new int[cursor.getCount()][4];
        cursor.moveToFirst();
        for (int i = 0; i < data.length; i++) {
            data[i][0] = cursor.getInt(ixDate);         // date
            data[i][1] = cursor.getInt(ixCum_steps);    // cum_steps
            data[i][2] = cursor.getInt(ixSd_steps);     // sd_steps
            if (data[i][0]%10 == 0) {
                System.out.printf("%d, %d, %d\n", data[i][0], data[i][1], data[i][2]);
            }
            cursor.moveToNext();
        }
        cursor.close();
        db.close();

        // 最初の１日目の歩数は算出できない
        int yyyymm = data[0][0] / 100;
        int sumSteps = 0;
        int days = 0;
        for (int i = 1; i < data.length; i++) {
            data[i][3] = data[i][1] - data[i-1][1] + data[i][2];
            //System.out.printf("%d: %d [%d %d]\n",
            //        data[i][0], data[i][3], data[i][1], data[i][2]);
            if (data[i][0]/100 != yyyymm) {
                // 月が変わった
                System.out.printf("%d: %d\n", yyyymm, sumSteps/days);
                yyyymm = data[i][0]/100;
                sumSteps = data[i][3];
                days = 1;
            } else {
                sumSteps += data[i][3];
                days++;
            }
        }
        System.out.printf("%d: %d\n", yyyymm, sumSteps/days);
    }

万歩計テーブルへのデータ登録

参考文献