2 * Copyright © 2019 Damyan Ivanov.
3 * This file is part of MoLe.
4 * MoLe is free software: you can distribute it and/or modify it
5 * under the term of the GNU General Public License as published by
6 * the Free Software Foundation, either version 3 of the License, or
7 * (at your opinion), any later version.
9 * MoLe is distributed in the hope that it will be useful,
10 * but WITHOUT ANY WARRANTY; without even the implied warranty of
11 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 * GNU General Public License terms for details.
14 * You should have received a copy of the GNU General Public License
15 * along with MoLe. If not, see <https://www.gnu.org/licenses/>.
18 package net.ktnx.mobileledger.utils;
20 import android.annotation.TargetApi;
21 import android.app.Application;
22 import android.content.Context;
23 import android.content.res.Resources;
24 import android.database.Cursor;
25 import android.database.MatrixCursor;
26 import android.database.SQLException;
27 import android.database.sqlite.SQLiteDatabase;
28 import android.database.sqlite.SQLiteOpenHelper;
29 import android.os.AsyncTask;
30 import android.os.Build;
31 import android.provider.FontsContract;
32 import android.util.Log;
33 import android.view.View;
34 import android.widget.AutoCompleteTextView;
35 import android.widget.FilterQueryProvider;
36 import android.widget.SimpleCursorAdapter;
38 import net.ktnx.mobileledger.async.DbOpQueue;
39 import net.ktnx.mobileledger.async.DescriptionSelectedCallback;
40 import net.ktnx.mobileledger.model.Data;
41 import net.ktnx.mobileledger.model.MobileLedgerProfile;
43 import org.jetbrains.annotations.NonNls;
45 import java.io.BufferedReader;
46 import java.io.IOException;
47 import java.io.InputStream;
48 import java.io.InputStreamReader;
49 import java.util.Locale;
51 import static net.ktnx.mobileledger.utils.Logger.debug;
53 public final class MLDB {
54 public static final String ACCOUNTS_TABLE = "accounts";
55 public static final String DESCRIPTION_HISTORY_TABLE = "description_history";
56 public static final String OPT_LAST_SCRAPE = "last_scrape";
58 public static final String OPT_PROFILE_UUID = "profile_uuid";
59 private static final String NO_PROFILE = "-";
60 private static MobileLedgerDatabase dbHelper;
61 private static Application context;
62 private static void checkState() {
64 throw new IllegalStateException("First call init with a valid context");
66 public static SQLiteDatabase getDatabase() {
71 db = dbHelper.getWritableDatabase();
73 db.execSQL("pragma case_sensitive_like=ON;");
76 @SuppressWarnings("unused")
77 static public int getIntOption(String name, int default_value) {
78 String s = getOption(name, String.valueOf(default_value));
80 return Integer.parseInt(s);
83 debug("db", "returning default int value of " + name, e);
87 @SuppressWarnings("unused")
88 static public long getLongOption(String name, long default_value) {
89 String s = getOption(name, String.valueOf(default_value));
91 return Long.parseLong(s);
94 debug("db", "returning default long value of " + name, e);
98 static public void getOption(String name, String defaultValue, GetOptCallback cb) {
99 AsyncTask<Void, Void, String> t = new AsyncTask<Void, Void, String>() {
101 protected String doInBackground(Void... params) {
102 SQLiteDatabase db = getDatabase();
103 try (Cursor cursor = db
104 .rawQuery("select value from options where profile = ? and name=?",
105 new String[]{NO_PROFILE, name}))
107 if (cursor.moveToFirst()) {
108 String result = cursor.getString(0);
110 if (result == null) result = defaultValue;
112 debug("async-db", "option " + name + "=" + result);
115 else return defaultValue;
117 catch (Exception e) {
118 debug("db", "returning default value for " + name, e);
123 protected void onPostExecute(String result) {
128 t.executeOnExecutor(AsyncTask.THREAD_POOL_EXECUTOR, (Void) null);
130 static public String getOption(String name, String default_value) {
131 debug("db", "about to fetch option " + name);
132 SQLiteDatabase db = getDatabase();
133 try (Cursor cursor = db.rawQuery("select value from options where profile = ? and name=?",
134 new String[]{NO_PROFILE, name}))
136 if (cursor.moveToFirst()) {
137 String result = cursor.getString(0);
139 if (result == null) result = default_value;
141 debug("db", "option " + name + "=" + result);
144 else return default_value;
146 catch (Exception e) {
147 debug("db", "returning default value for " + name, e);
148 return default_value;
151 static public void setOption(String name, String value) {
152 debug("option", String.format("%s := %s", name, value));
153 DbOpQueue.add("insert or replace into options(profile, name, value) values(?, ?, ?);",
154 new String[]{NO_PROFILE, name, value});
156 @SuppressWarnings("unused")
157 static public void setLongOption(String name, long value) {
158 setOption(name, String.valueOf(value));
160 @TargetApi(Build.VERSION_CODES.N)
161 public static void hookAutocompletionAdapter(final Context context,
162 final AutoCompleteTextView view,
163 final String table, final String field,
164 final boolean profileSpecific) {
165 hookAutocompletionAdapter(context, view, table, field, profileSpecific, null, null,
166 Data.profile.getValue());
168 @TargetApi(Build.VERSION_CODES.N)
169 public static void hookAutocompletionAdapter(final Context context,
170 final AutoCompleteTextView view,
171 final String table, final String field,
172 final boolean profileSpecific, final View nextView,
173 final DescriptionSelectedCallback callback,
174 final MobileLedgerProfile profile) {
175 String[] from = {field};
176 int[] to = {android.R.id.text1};
177 SimpleCursorAdapter adapter =
178 new SimpleCursorAdapter(context, android.R.layout.simple_dropdown_item_1line, null,
180 adapter.setStringConversionColumn(1);
182 FilterQueryProvider provider = constraint -> {
183 if (constraint == null) return null;
185 String str = constraint.toString().toUpperCase();
186 debug("autocompletion", "Looking for " + str);
187 String[] col_names = {FontsContract.Columns._ID, field};
188 MatrixCursor c = new MatrixCursor(col_names);
192 if (profileSpecific) {
193 sql = String.format("SELECT %s as a, case when %s_upper LIKE ?||'%%' then 1 " +
194 "WHEN %s_upper LIKE '%%:'||?||'%%' then 2 " +
195 "WHEN %s_upper LIKE '%% '||?||'%%' then 3 else 9 end " +
197 "WHERE profile=? AND %s_upper LIKE '%%'||?||'%%' " +
198 "ORDER BY 2, 1;", field, field, field, field, table, field);
199 params = new String[]{str, str, str, profile.getUuid(), str};
202 sql = String.format("SELECT %s as a, case when %s_upper LIKE ?||'%%' then 1 " +
203 "WHEN %s_upper LIKE '%%:'||?||'%%' then 2 " +
204 "WHEN %s_upper LIKE '%% '||?||'%%' then 3 " + "else 9 end " +
205 "FROM %s " + "WHERE %s_upper LIKE '%%'||?||'%%' " +
206 "ORDER BY 2, 1;", field, field, field, field, table, field);
207 params = new String[]{str, str, str, str};
209 debug("autocompletion", sql);
210 SQLiteDatabase db = MLDB.getDatabase();
212 try (Cursor matches = db.rawQuery(sql, params)) {
214 while (matches.moveToNext()) {
215 String match = matches.getString(0);
216 int order = matches.getInt(1);
217 debug("autocompletion",
218 String.format(Locale.ENGLISH, "match: %s |%d", match, order));
219 c.newRow().add(i++).add(match);
227 adapter.setFilterQueryProvider(provider);
229 view.setAdapter(adapter);
231 if (nextView != null) {
232 view.setOnItemClickListener((parent, itemView, position, id) -> {
233 nextView.requestFocus(View.FOCUS_FORWARD);
234 if (callback != null) {
235 callback.descriptionSelected(String.valueOf(view.getText()));
240 public static synchronized void init(Application context) {
241 MLDB.context = context;
242 if (dbHelper != null)
243 throw new IllegalStateException("It appears init() was already called");
244 dbHelper = new MobileLedgerDatabase(context);
246 public static synchronized void done() {
247 if (dbHelper != null) {
248 debug("db", "Closing DB helper");
255 class MobileLedgerDatabase extends SQLiteOpenHelper {
256 private static final String DB_NAME = "MoLe.db";
257 private static final int LATEST_REVISION = 22;
258 private static final String CREATE_DB_SQL = "create_db";
260 private final Application mContext;
262 MobileLedgerDatabase(Application context) {
263 super(context, DB_NAME, null, LATEST_REVISION);
264 debug("db", "creating helper instance");
266 super.setWriteAheadLoggingEnabled(true);
270 public void onCreate(SQLiteDatabase db) {
271 debug("db", "onCreate called");
272 applyRevisionFile(db, CREATE_DB_SQL);
276 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
277 debug("db", "onUpgrade called");
278 for (int i = oldVersion + 1; i <= newVersion; i++) applyRevision(db, i);
281 private void applyRevision(SQLiteDatabase db, int rev_no) {
282 String rev_file = String.format(Locale.US, "sql_%d", rev_no);
284 applyRevisionFile(db, rev_file);
286 private void applyRevisionFile(SQLiteDatabase db, String rev_file) {
287 final Resources rm = mContext.getResources();
288 int res_id = rm.getIdentifier(rev_file, "raw", mContext.getPackageName());
290 throw new SQLException(String.format(Locale.US, "No resource for %s", rev_file));
291 db.beginTransaction();
292 try (InputStream res = rm.openRawResource(res_id)) {
293 debug("db", "Applying " + rev_file);
294 InputStreamReader isr = new InputStreamReader(res);
295 BufferedReader reader = new BufferedReader(isr);
299 while ((line = reader.readLine()) != null) {
300 if (line.startsWith("--")) {
304 if (line.isEmpty()) {
311 catch (Exception e) {
312 throw new RuntimeException(
313 String.format("Error applying %s, line %d", rev_file, line_no), e);
318 db.setTransactionSuccessful();
320 catch (IOException e) {
321 Log.e("db", String.format("Error opening raw resource for %s", rev_file));