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.Build;
30 import android.provider.FontsContract;
31 import android.util.Log;
32 import android.view.View;
33 import android.widget.AutoCompleteTextView;
34 import android.widget.FilterQueryProvider;
35 import android.widget.SimpleCursorAdapter;
37 import net.ktnx.mobileledger.async.DescriptionSelectedCallback;
38 import net.ktnx.mobileledger.model.Data;
39 import net.ktnx.mobileledger.model.MobileLedgerProfile;
41 import org.jetbrains.annotations.NonNls;
43 import java.io.BufferedReader;
44 import java.io.IOException;
45 import java.io.InputStream;
46 import java.io.InputStreamReader;
47 import java.util.Locale;
49 public final class MLDB {
50 public static final String ACCOUNTS_TABLE = "accounts";
51 public static final String DESCRIPTION_HISTORY_TABLE = "description_history";
52 public static final String OPT_LAST_SCRAPE = "last_scrape";
54 public static final String OPT_PROFILE_UUID = "profile_uuid";
55 private static final String NO_PROFILE = "-";
56 private static MobileLedgerDatabase dbHelper;
57 private static Application context;
58 private static void checkState() {
60 throw new IllegalStateException("First call init with a valid context");
62 public static SQLiteDatabase getDatabase() {
67 db = dbHelper.getWritableDatabase();
69 db.execSQL("pragma case_sensitive_like=ON;");
72 static public int getIntOption(String name, int default_value) {
73 String s = getOption(name, String.valueOf(default_value));
75 return Integer.parseInt(s);
78 Log.d("db", "returning default int value of " + name, e);
82 static public long getLongOption(String name, long default_value) {
83 String s = getOption(name, String.valueOf(default_value));
85 return Long.parseLong(s);
88 Log.d("db", "returning default long value of " + name, e);
92 static public String getOption(String name, String default_value) {
93 Log.d("db", "about to fetch option " + name);
94 SQLiteDatabase db = getDatabase();
95 try (Cursor cursor = db.rawQuery("select value from options where profile = ? and name=?",
96 new String[]{NO_PROFILE, name}))
98 if (cursor.moveToFirst()) {
99 String result = cursor.getString(0);
101 if (result == null) result = default_value;
103 Log.d("db", "option " + name + "=" + result);
106 else return default_value;
108 catch (Exception e) {
109 Log.d("db", "returning default value for " + name, e);
110 return default_value;
113 static public void setOption(String name, String value) {
114 Log.d("option", String.format("%s := %s", name, value));
115 SQLiteDatabase db = MLDB.getDatabase();
116 db.execSQL("insert or replace into options(profile, name, value) values(?, ?, ?);",
117 new String[]{NO_PROFILE, name, value});
119 static public void setLongOption(String name, long value) {
120 setOption(name, String.valueOf(value));
122 @TargetApi(Build.VERSION_CODES.N)
123 public static void hookAutocompletionAdapter(final Context context,
124 final AutoCompleteTextView view,
125 final String table, final String field,
126 final boolean profileSpecific) {
127 hookAutocompletionAdapter(context, view, table, field, profileSpecific, null, null, Data.profile.get());
129 @TargetApi(Build.VERSION_CODES.N)
130 public static void hookAutocompletionAdapter(final Context context,
131 final AutoCompleteTextView view,
132 final String table, final String field,
133 final boolean profileSpecific, final View nextView,
134 final DescriptionSelectedCallback callback,
135 final MobileLedgerProfile profile) {
136 String[] from = {field};
137 int[] to = {android.R.id.text1};
138 SimpleCursorAdapter adapter =
139 new SimpleCursorAdapter(context, android.R.layout.simple_dropdown_item_1line, null,
141 adapter.setStringConversionColumn(1);
143 FilterQueryProvider provider = constraint -> {
144 if (constraint == null) return null;
146 String str = constraint.toString().toUpperCase();
147 Log.d("autocompletion", "Looking for " + str);
148 String[] col_names = {FontsContract.Columns._ID, field};
149 MatrixCursor c = new MatrixCursor(col_names);
153 if (profileSpecific) {
154 sql = String.format("SELECT %s as a, case when %s_upper LIKE ?||'%%' then 1 " +
155 "WHEN %s_upper LIKE '%%:'||?||'%%' then 2 " +
156 "WHEN %s_upper LIKE '%% '||?||'%%' then 3 else 9 end " +
158 "WHERE profile=? AND %s_upper LIKE '%%'||?||'%%' " +
159 "ORDER BY 2, 1;", field, field, field, field, table, field);
160 params = new String[]{str, str, str, profile.getUuid(), str};
163 sql = String.format("SELECT %s as a, case when %s_upper LIKE ?||'%%' then 1 " +
164 "WHEN %s_upper LIKE '%%:'||?||'%%' then 2 " +
165 "WHEN %s_upper LIKE '%% '||?||'%%' then 3 " + "else 9 end " +
166 "FROM %s " + "WHERE %s_upper LIKE '%%'||?||'%%' " +
167 "ORDER BY 2, 1;", field, field, field, field, table, field);
168 params = new String[]{str, str, str, str};
170 Log.d("autocompletion", sql);
171 SQLiteDatabase db = MLDB.getDatabase();
173 try (Cursor matches = db.rawQuery(sql, params)) {
175 while (matches.moveToNext()) {
176 String match = matches.getString(0);
177 int order = matches.getInt(1);
178 Log.d("autocompletion", String.format("match: %s |%d", match, order));
179 c.newRow().add(i++).add(match);
187 adapter.setFilterQueryProvider(provider);
189 view.setAdapter(adapter);
191 if (nextView != null) {
192 view.setOnItemClickListener((parent, itemView, position, id) -> {
193 nextView.requestFocus(View.FOCUS_FORWARD);
194 if (callback != null) {
195 callback.descriptionSelected(String.valueOf(view.getText()));
200 public static synchronized void init(Application context) {
201 MLDB.context = context;
202 if (dbHelper != null)
203 throw new IllegalStateException("It appears init() was already called");
204 dbHelper = new MobileLedgerDatabase(context);
206 public static synchronized void done() {
207 if (dbHelper != null) {
208 Log.d("db", "Closing DB helper");
215 class MobileLedgerDatabase extends SQLiteOpenHelper implements AutoCloseable {
216 private static final String DB_NAME = "MoLe.db";
217 private static final int LATEST_REVISION = 22;
218 private static final String CREATE_DB_SQL = "create_db";
220 private final Application mContext;
222 public MobileLedgerDatabase(Application context) {
223 super(context, DB_NAME, null, LATEST_REVISION);
224 Log.d("db", "creating helper instance");
226 super.setWriteAheadLoggingEnabled(true);
230 public void onCreate(SQLiteDatabase db) {
231 Log.d("db", "onCreate called");
232 applyRevisionFile(db, CREATE_DB_SQL);
236 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
237 Log.d("db", "onUpgrade called");
238 for (int i = oldVersion + 1; i <= newVersion; i++) applyRevision(db, i);
241 private void applyRevision(SQLiteDatabase db, int rev_no) {
242 String rev_file = String.format(Locale.US, "sql_%d", rev_no);
244 applyRevisionFile(db, rev_file);
246 private void applyRevisionFile(SQLiteDatabase db, String rev_file) {
247 final Resources rm = mContext.getResources();
248 int res_id = rm.getIdentifier(rev_file, "raw", mContext.getPackageName());
250 throw new SQLException(String.format(Locale.US, "No resource for %s", rev_file));
251 db.beginTransaction();
252 try (InputStream res = rm.openRawResource(res_id)) {
253 Log.d("db", "Applying " + rev_file);
254 InputStreamReader isr = new InputStreamReader(res);
255 BufferedReader reader = new BufferedReader(isr);
259 while ((line = reader.readLine()) != null) {
260 if (line.startsWith("--")) {
264 if (line.isEmpty()) {
271 catch (Exception e) {
272 throw new RuntimeException(
273 String.format("Error applying %s, line %d", rev_file, line_no), e);
278 db.setTransactionSuccessful();
280 catch (IOException e) {
281 Log.e("db", String.format("Error opening raw resource for %s", rev_file));