Just a Note：Handle BigDecimal with Android SQLiteDatabase

Sorry, it is difficult to me to write this article in English.

原本 Daily Money 是使用 SQLite 的 REAL 資料型態儲存金額相關欄位，在程式中則是使用 double 處理金額的運算。
開發過金融系統的人都很清楚，double 存在精確度的問題，所以這次改版，就把 double 改為 BigDecimal。

修改中遇到幾個問題，備忘一下。




1. 程式中原本使用 double 的部分，都改成 BigDecimal。這部分其實沒有太大問題，把幾個 model 物件改掉之後，就會出現一堆紅色 x，再把錯誤修正就好了。
比較麻煩的是存取 db 用的 Cursor 物件並沒有 getBigDecimal 的 method，所以只能退而求其次，使用 BigDecimal amount = new BigDecimal(cursor.getString(index)) 的方式來取得資料。
舉例來說，原本的程式：

double r = 0D;
if (c.moveToNext()) {
    r = c.getDouble(0);
}

修改後：

BigDecimal r = BigDecimal.ZERO;
if (c.moveToNext()) {
    r = new BigDecimal(c.getString(0) == null ? "0" : c.getString(0));
}

2. 承 1.，原本以 REAL 型別儲存的金額欄位需改成 TEXT 型別，但 SQLite 的 ALTER TABLE 不支援 column 的修改，只能新增 column (參考)。所以為了不影響原有資料，只好新增一個欄位，資料型別是 TEXT。並調整 com.bottleworks.dailymoney.data.SQLiteDataHelper 的 VERSION，當程式開啟 db 時，判斷到 db 版本不一致，便執行

ALTER TABLE dm_acc ADD ivb_ TEXT NOT NULL DEFAULT '';
ALTER TABLE dm_det ADD mnb_ TEXT NOT NULL DEFAULT '';
UPDATE dm_acc SET ivb_ = iv_;
UPDATE dm_det SET mnb_ = mn_;


前兩行是 alter table，後兩行是把原本金額欄位的資料抄到新的欄位。

3. 改完之後，原則上差不多就完成了，但是因為金額欄位變成 TEXT 型別，所以如果在 SQL 中進行數值運算的話，會有問題，例如原本程式中有使用 SELECT SUM()。
SQLite 中，SUM() 的欄位即使是 TEXT 型態，還是可以運算，但是會先轉成 double 再運算(註)......原本為了精確度問題把 double 改掉，結果 SUM() 又轉成 double 去運算，簡直就是鬼打牆......所以使用 SUM() 的部分，都改成把資料查出來後，在程式中透過 BigDecimal 進行加總，如此便可以徹底解決精確度的問題，但是可能會有效能的問題......


註：SQLite 的 source code，func.c，如果不是 INTEGER 型別，一律轉成 double 進行運算。

static void sumStep(sqlite3_context *context, int argc, sqlite3_value **argv){
  SumCtx *p;
  int type;
  assert( argc==1 );
  p = sqlite3_aggregate_context(context, sizeof(*p));
  type = sqlite3_value_numeric_type(argv[0]);
  if( p && type!=SQLITE_NULL ){
    p->cnt++;
    if( type==SQLITE_INTEGER ){
      i64 v = sqlite3_value_int64(argv[0]);
      p->rSum += v;
      if( (p->approx|p->overflow)==0 ){
        i64 iNewSum = p->iSum + v;
        int s1 = p->iSum >> (sizeof(i64)*8-1);
        int s2 = v       >> (sizeof(i64)*8-1);
        int s3 = iNewSum >> (sizeof(i64)*8-1);
        p->overflow = (s1&s2&~s3) | (~s1&~s2&s3);
        p->iSum = iNewSum;
      }
    }else{
      p->rSum += sqlite3_value_double(argv[0]);
      p->approx = 1;
    }
  }
}


這篇很難翻成英文......