SQLiteを使ってみる 第4回

これまではクエリ1つ投げるのに複数のAPIを用いてきたが、これはやはり面倒だ。そこで、一連のAPI呼び出しをまとめてやってくれるsqlite3_exec()の出番となるわけだが、ただ使ってみるだけでは芸がないので、今回は趣向を変えてその実装を見てみよう。以下、本家tarball内のsqlite.cより引用。

SQLITE_API int sqlite3_exec(
  sqlite3 *db,                /* The database on which the SQL executes */
  const char *zSql,           /* The SQL to be executed */
  sqlite3_callback xCallback, /* Invoke this callback routine */
  void *pArg,                 /* First argument to xCallback() */
  char **pzErrMsg             /* Write error messages here */
){
  int rc = SQLITE_OK;         /* Return code */
  const char *zLeftover;      /* Tail of unprocessed SQL */
  sqlite3_stmt *pStmt = 0;    /* The current SQL statement */
  char **azCols = 0;          /* Names of result columns */
  int nRetry = 0;             /* Number of retry attempts */
  int callbackIsInit;         /* True if callback data is initialized */
  if( !sqlite3SafetyCheckOk(db) ) return SQLITE_MISUSE_BKPT;
  if( zSql==0 ) zSql = "";
  sqlite3_mutex_enter(db->mutex);
  sqlite3Error(db, SQLITE_OK, 0);
  while( (rc==SQLITE_OK || (rc==SQLITE_SCHEMA && (++nRetry)<2)) && zSql[0] ){
    int nCol;
    char **azVals = 0;
    pStmt = 0;
    rc = sqlite3_prepare(db, zSql, -1, &pStmt, &zLeftover);
    assert( rc==SQLITE_OK || pStmt==0 );
    if( rc!=SQLITE_OK ){
      continue;
    }
    if( !pStmt ){
      /* this happens for a comment or white-space */
      zSql = zLeftover;
      continue;
    }
    callbackIsInit = 0;
    nCol = sqlite3_column_count(pStmt);
    while( 1 ){
      int i;
      rc = sqlite3_step(pStmt);
      /* Invoke the callback function if required */
      if( xCallback && (SQLITE_ROW==rc ||
          (SQLITE_DONE==rc && !callbackIsInit
                           && db->flags&SQLITE_NullCallback)) ){
        if( !callbackIsInit ){
          azCols = sqlite3DbMallocZero(db, 2*nCol*sizeof(const char*) + 1);
          if( azCols==0 ){
            goto exec_out;
          }
          for(i=0; i<nCol; i++){
            azCols[i] = (char *)sqlite3_column_name(pStmt, i);
            /* sqlite3VdbeSetColName() installs column names as UTF8
            ** strings so there is no way for sqlite3_column_name() to fail. */
            assert( azCols[i]!=0 );
          }
          callbackIsInit = 1;
        }
        if( rc==SQLITE_ROW ){
          azVals = &azCols[nCol];
          for(i=0; i<nCol; i++){
            azVals[i] = (char *)sqlite3_column_text(pStmt, i);
            if( !azVals[i] && sqlite3_column_type(pStmt, i)!=SQLITE_NULL ){
              db->mallocFailed = 1;
              goto exec_out;
            }
          }
        }
        if( xCallback(pArg, nCol, azVals, azCols) ){
          rc = SQLITE_ABORT;
          sqlite3VdbeFinalize((Vdbe *)pStmt);
          pStmt = 0;
          sqlite3Error(db, SQLITE_ABORT, 0);
          goto exec_out;
        }
      }
      if( rc!=SQLITE_ROW ){
        rc = sqlite3VdbeFinalize((Vdbe *)pStmt);
        pStmt = 0;
        if( rc!=SQLITE_SCHEMA ){
          nRetry = 0;
          zSql = zLeftover;
          while( sqlite3Isspace(zSql[0]) ) zSql++;
        }
        break;
      }
    }
    sqlite3DbFree(db, azCols);
    azCols = 0;
  }
exec_out:
  if( pStmt ) sqlite3VdbeFinalize((Vdbe *)pStmt);
  sqlite3DbFree(db, azCols);
  rc = sqlite3ApiExit(db, rc);
  if( rc!=SQLITE_OK && ALWAYS(rc==sqlite3_errcode(db)) && pzErrMsg ){
    int nErrMsg = 1 + sqlite3Strlen30(sqlite3_errmsg(db));
    *pzErrMsg = sqlite3Malloc(nErrMsg);
    if( *pzErrMsg ){
      memcpy(*pzErrMsg, sqlite3_errmsg(db), nErrMsg);
    }else{
      rc = SQLITE_NOMEM;
      sqlite3Error(db, SQLITE_NOMEM, 0);
    }
  }else if( pzErrMsg ){
    *pzErrMsg = 0;
  }
  assert( (rc&db->errMask)==rc );
  sqlite3_mutex_leave(db->mutex);
  return rc;
}

上から眺めると、まず目に付くのが排他処理。sqlite3_mutex_enter(), sqlite3_mutex_leave()を用いているのが分かる。次に、最も外側のループの中では、引数として与えられたSQL文を逐一実行する。セミコロンで区切った複数のSQL文も、エラーが検出されない限り最後まで実行されるわけだ。

コールバックも指定できる。…と言うか、クエリによってはこれを使わないとどうにもならない。何も返さないクエリは完了時に1回コールバックが呼ばれるだけなので、無理にコールバックを使わなくてもどうにかなるだろう。一方、表を返すクエリの場合、1行毎に取得した表の内容を引数にしてコールバックが呼び出されるので、基本的に所望の処理はコールバックの中で行う。メモリの解放は気にしなくても良い一方で、コールバックの外でも取得した表の内容を参照したければ、コールバックの中でどこかしら別の領域にコピーして取っておく必要がある。また、表を作成したときに指定した型に関わらず、カラムの内容はTEXT型として取得される。

エラーを検出した場合、エラーメッセージを返すことができるが、このエラーメッセージの領域はsqlite3Malloc()で確保したものである。したがって、エラーメッセージを使い終えたらsqlite3_free()で明示的に解放してやらなければならない。

SQLiteを使ってみる 第3回

結果を返すタイプのクエリを投げてみよう。今回は、以下のような簡単なテーブルを作って、それを丸ごと返してもらう。

日付	品目	価格
11/25	りんご	123
11/26	ごりら	456

まずはサンプルコード。

// vim: set fileencoding=utf-8:
#include <stdio.h>
#include <string.h>
#include <sqlite3.h>
#define DEFAULT_FILENAME "test3.sqlite3"
sqlite3 *open_db(const char *filename) {
    sqlite3 *db;
    int rc;
    rc = sqlite3_open(filename, &db);
    printf("sqlite3_open() : %d\n", rc);
    if (rc != SQLITE_OK) {
        sqlite3_close(db);
        db = NULL;
    }
    return db;
}
int close_db(sqlite3 *db) {
    int rc;
    rc =  sqlite3_close(db);
    printf("sqlite3_close() : %d\n", rc);
    return rc;
}
sqlite3_stmt *exec_query(sqlite3 *db, const char *query) {
    sqlite3_stmt *stmt;
    int rc;
    rc = sqlite3_prepare_v2(db, query, strlen(query), &stmt, NULL);
    printf("sqlite3_prepare_v2() : %d\n", rc);
    if (rc != SQLITE_OK) return NULL;
    rc = sqlite3_step(stmt);
    printf("sqlite3_step() : %d\n", rc);
    return stmt;
}
int fin_query(sqlite3_stmt *stmt) {
    int rc;
    rc = sqlite3_finalize(stmt);
    printf("sqlite3_finalize() : %d\n", rc);
    return rc;
}
int main(int argc, char **argv) {
    sqlite3 *db = NULL;
    int rc;
    char *filename = DEFAULT_FILENAME;
    // open
    if (argc >= 2) filename = argv[1];
    db = open_db(filename);
    if (db == NULL) return 1;
    sqlite3_stmt *stmt;
    rc = 2;
    // create table
    stmt = exec_query(db, "create table 家計簿(日付 text,品目 text,価格 int);");
    if (stmt == NULL) goto ensure;
    fin_query(stmt);
    // insert
    stmt = exec_query(db, "insert into 家計簿(日付,品目,価格) values('11/25','りんご',123);");
    if (stmt == NULL) goto ensure;
    fin_query(stmt);
    stmt = exec_query(db, "insert into 家計簿(日付,品目,価格) values('11/26','ごりら',456);");
    if (stmt == NULL) goto ensure;
    fin_query(stmt);
    // select
    const char *query = "select * from 家計簿";
    rc = sqlite3_prepare_v2(db, query, strlen(query), &stmt, NULL);
    if (rc != SQLITE_OK) goto ensure;
    for (;;) {
        rc = sqlite3_step(stmt);
        printf("sqlite3_step() : %d\n", rc);
        if (rc != SQLITE_ROW) break;
        const unsigned char *text;
        int val;
        // 日付
        text = sqlite3_column_text(stmt, 0);
        if (text != NULL) printf("'%s', ", text);
        // 品目
        text = sqlite3_column_text(stmt, 1);
        if (text != NULL) printf("'%s', ", text);
        // 価格
        val = sqlite3_column_int(stmt, 2);
        printf("%d\n", val);
    }
    fin_query(stmt);
    rc = 0;
ensure:
    // close
    close_db(db);
    return rc;
}

結果は常に1行ずつ参照される。また、参照される行はsqlite3_step()が呼ばれる度に次へと進む。sqlite3_step()の戻り値は、参照する行が残っている場合はSQLITE_ROW、全ての行を返し終えた場合はSQLITE_DONEとなる。

sqlite3_column_text()が文字列へのポインタを返していることに注意。当然、メモリのどこかに文字列を確保しているわけだが、これはsqlite3_step()で次の行へ移ったときやsqlite3_finalize()で後始末するときに、自動的に解放される。このメモリの扱いは、ポインタを返す他のsqlite3_column_*()でも同様。

SQLiteを使ってみる 第2回

接続したDBにクエリを投げて、以下のような簡単なテーブルを作ってみよう。

日付	品目	価格

以下のサンプルではsqlite3_exec()を使わず、sqlite3_prepare_v2(), sqlite3_step(), sqlite3_finalize()を使ってクエリを投げている。

// vim: set fileencoding=utf-8:
#include <stdio.h>
#include <string.h>
#include <sqlite3.h>
#define DEFAULT_FILENAME "test2.sqlite3"
int main(int argc, char **argv) {
    sqlite3 *db = NULL;
    int rc;
    char *filename = DEFAULT_FILENAME;
    // open
    if (argc >= 2) filename = argv[1];
    rc = sqlite3_open(filename, &db);
    if (rc == SQLITE_OK)
        printf("sqlite3_open() : OK, db = %p\n", db);
    else
        printf("sqlite3_open() : NG(%d), db = %p\n", rc, db);
    // create table
    const char *query = "create table 家計簿(日付 text, 品目 text, 価格 int);";
//  query = "creat table 家計簿(日付 text, 品目 text, 価格 int);";
    sqlite3_stmt *stmt = (sqlite3_stmt *) 0xdeadbeaf;
    rc = sqlite3_prepare_v2(db, query, strlen(query), &stmt, NULL);
    if (rc == SQLITE_OK)
        printf("sqlite3_prepare_v2() : OK, stmt = %p\n", stmt);
    else
        printf("sqlite3_prepare_v2() : NG(%d), stmt = %p\n", rc, stmt);
    if (stmt != NULL) {
        rc = sqlite3_step(stmt);
        if (rc == SQLITE_DONE)
            printf("sqlite3_step() : DONE\n");
        else
            printf("sqlite3_step() : NG(%d)\n", rc);
    }
    rc = sqlite3_finalize(stmt);
    if (rc == SQLITE_OK)
        printf("sqlite3_finalize() : OK, stmt = %p\n", stmt);
    else
        printf("sqlite3_finalize() : NG(%d), stmt = %p\n", rc, stmt);
    // close
    rc = sqlite3_close(db);
    if (rc == SQLITE_OK)
        printf("sqlite3_close() : OK, db = %p\n", db);
    else
        printf("sqlite3_close() : NG(%d), db = %p\n", rc, db);
    return 0;
}

sqlite3_prepare_v2()は、UTF-8の文字列で表現されたクエリから内部形式であるsqlite3_stmt型のオブジェクトを生成するAPI。生成されたオブジェクトの場所は、第4引数で参照渡しするポインタ変数に返してもらう。sqlite3_open()とは異なり、失敗したら何もリソースは確保せず、NULLを返す。

sqlite3_prepare_v2()が生成したsqlite3_stmt型のオブジェクトを実行するのがsqlite3_step()。今回はテーブルを作成しているだけなので、クエリの実行結果としてテーブルが返ってきたりはしない。

使い終わったsqlite3_stmt型のオブジェクトは、sqlite3_finalize()で解放する。引数にNULLを渡しても実害はないようだ。

コメントアウトしているクエリは、わざとタイプミスをしたもの。これを実行するとsqlite3_prepare_v2()がエラーを返す。また、コンパイルした実行バイナリを2度3度実行すると、2回目以降はsqlite3_prepare_v2()がエラーを吐く。既存のテーブルと同じ名前のテーブルを作成しようとするので、失敗すること自体は想定していたが、sqlite3_step()でこけるものだと思っていた。sqlite3_open()かsqlite3_prepare_v2()で、DBに含まれているテーブル名くらいは見ているのかな？

SQLiteを使ってみる 第1.5回

さて、DBへ接続できるようになったら、いよいよクエリを投げるためのAPIだ。

文字列で表現されたクエリをsqlite3_prepare_v2()で内部形式に変換してから、sqlite3_step_v2()で変換後の式を評価し、使い終わった内部形式のオブジェクトをsqlite3_finalize()で破棄するのが基本のようだ。面倒ならば一連の操作をまとめてやってくれるsqlite3_exec()も使える。

…って、APIを押さえる前に、もう少しSQLをかじらないと投げるクエリが定まらない。コマンドラインでインタラクティブに使えるsqlite3コマンドで、色々いじりながら覚えよう。

SQLiteを使ってみる 第1回

まずはオープンとクローズ。

基本的にはsqlite3_open()でDBへの接続をオープンし、操作を終えたらsqlite3_close()でクローズすれば良い。以下は、オープン・クローズするだけのサンプル。

#include <stdio.h>
#include <sqlite3.h>
#define DEFAULT_FILENAME "test1.sqlite3"
int main(int argc, char **argv) {
    sqlite3 *db = NULL;
    int rc;
    char *filename = DEFAULT_FILENAME;
    // open
    if (argc >= 2) filename = argv[1];
    rc = sqlite3_open(filename, &db);
    if (rc == SQLITE_OK)
        printf("sqlite3_open() : OK, db = %p\n", db);
    else
        printf("sqlite3_open() : NG(%d), db = %p\n", rc, db);
    // do something ...
    // close
    rc = sqlite3_close(db);
    if (rc == SQLITE_OK)
        printf("sqlite3_close() : OK, db = %p\n", db);
    else
        printf("sqlite3_close() : NG(%d), db = %p\n", rc, db);
    return 0;
}

まず、接続はsqlite3型のポインタで表現する。ユーザーは、APIが確保したsqlite3型のオブジェクトの場所を教えてもらうわけだ。sqlite3_open()は戻り値がint型のエラーコードなので、sqlite3型のオブジェクトのアドレスはポインタの参照を介して返してもらう。指定した名前のファイルが存在しなければ、新規ファイルの作成を試みる。なお、sqlite3_open()に限らず、基本的に引数の文字列はUTF-8。名前の末尾に16が付くAPIはUTF-16。

所望の操作を終えたらsqlite3_close()。sqlite3_open()で確保したリソースはこれで解放される。

注意すべきは、sqlite3_open()に失敗してもsqlite3_close()が必要な点。sqlite3型オブジェクトが表現するものは、あくまで「DBへの接続」。そして接続に失敗した場合でも、エラー情報を保持したsqlite3型のオブジェクトが確保される。失敗したからと言って放置せず、sqlite3_close()でこのオブジェクトを捨ててやらなければならない。fopen()に失敗したらfclose()しない普通のファイルとは、使い勝手が異なるところだ。

SQLiteを使ってみる 第0回

最近、時事ネタやキン肉マンの感想ばかり書いていたので、初心に返って久々に技術ネタを書こう。…と言うわけで、ガチガチ低級な組み込み屋さんの苦手分野(?)であるDBを、SQLiteを通じて学んでみようと思う。

SQLiteを選んだのは、ネットワークを考える必要がなく、単なるローカルファイルの操作だけで完結するから。サンプルプログラムも作りやすいだろうし、ちょっとしたツールにもすぐ応用できそうだし。