1 package net.ktnx.mobileledger;
3 import android.content.res.Resources;
4 import android.database.Cursor;
5 import android.database.SQLException;
6 import android.database.sqlite.SQLiteDatabase;
7 import android.util.Log;
9 import java.io.BufferedReader;
10 import java.io.InputStream;
11 import java.io.InputStreamReader;
12 import java.util.Locale;
14 class MobileLedgerDB {
15 static final String DATABASE_NAME = "accounts";
16 static final String OPT_DB_REVISION = "db_revision";
17 private static String db_filename;
18 static SQLiteDatabase db;
20 static String getDb_filename() {
24 static void setDb_filename(String db_filename) {
25 MobileLedgerDB.db_filename = db_filename;
28 static void initDB() {
29 db = SQLiteDatabase.openOrCreateDatabase(db_filename, null);
31 db.execSQL("create table if not exists accounts(name varchar);");
32 db.execSQL("create index if not exists idx_accounts_name on accounts(name);");
33 db.execSQL("create table if not exists options(name varchar, value varchar);");
34 db.execSQL("create unique index if not exists idx_options_name on options(name);");
35 db.execSQL("create table if not exists account_values(account varchar not null, currency varchar not null, value decimal(18,2) not null);");
36 db.execSQL("create index if not exists idx_account_values_account on account_values(account);");
37 db.execSQL("create unique index if not exists un_account_values on account_values(account,currency);");
40 static void applyRevisions(Resources rm, String pkg_name) {
41 int cur_ver = Integer.parseInt(get_option_value(OPT_DB_REVISION, "0"));
43 Log.d("db", "Current DB revision is "+String.valueOf(cur_ver));
45 while (applyRevision(rm, pkg_name, cur_ver+1)) {
49 Log.d("db", "Database revision is "+String.valueOf(cur_ver)+" now");
51 private static boolean applyRevision(Resources rm, String pkg_name, int rev_no) {
52 String rev_file = String.format(Locale.US, "sql_%d", rev_no);
54 int res_id = rm.getIdentifier(rev_file, "raw", pkg_name);
56 Log.d("db", String.format(Locale.US, "No resource for revision %d", rev_no));
59 db.beginTransaction();
60 try (InputStream res = rm.openRawResource(res_id)) {
61 Log.d("db", "Applying revision " + String.valueOf(rev_no));
62 InputStreamReader isr = new InputStreamReader(res);
63 BufferedReader reader = new BufferedReader(isr);
66 while ((line = reader.readLine()) != null) {
70 set_option_value(OPT_DB_REVISION, rev_no);
71 db.setTransactionSuccessful();
72 } catch (Resources.NotFoundException e) {
73 Log.d("db", "SQL revision "+String.valueOf(rev_no)+" not found");
76 catch (SQLException e) {
77 Log.e("db", String.format(Locale.US, "Error applying revision %d: %s", rev_no, e.getMessage()));
81 Log.w("db", "Error reading revision" + String.valueOf(rev_no)+": "+e.getMessage());
91 static int get_option_value(String name, int default_value) {
92 String s = get_option_value(name, String.valueOf(default_value));
94 return Integer.parseInt(s);
101 static long get_option_value(String name, long default_value) {
102 String s = get_option_value(name, String.valueOf(default_value));
104 return Long.parseLong(s);
106 catch (Exception e) {
107 Log.d("db", "returning default long value of "+name, e);
108 return default_value;
112 static String get_option_value(String name, String default_value) {
113 Log.d("db", "about fo fetch option "+name);
114 try (Cursor cursor = db.rawQuery("select value from options where name=?", new String[]{name})) {
115 if (cursor.moveToFirst()) {
116 String result = cursor.getString(0);
118 if (result == null ) result = default_value;
120 Log.d("db", "option "+name+"="+result);
123 else return default_value;
126 Log.d("db", "returning default value for "+name, e);
127 return default_value;
131 static void set_option_value(String name, String value) {
132 Log.d("db", "setting option "+name+"="+value);
133 db.execSQL("insert or replace into options(name, value) values(?, ?);", new String[]{name, value});
136 static void set_option_value(String name, long value) {
137 set_option_value(name, String.valueOf(value));