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.DbOpQueue;
38 import net.ktnx.mobileledger.async.DescriptionSelectedCallback;
39 import net.ktnx.mobileledger.model.Data;
40 import net.ktnx.mobileledger.model.MobileLedgerProfile;
42 import org.jetbrains.annotations.NonNls;
44 import java.io.BufferedReader;
45 import java.io.IOException;
46 import java.io.InputStream;
47 import java.io.InputStreamReader;
48 import java.util.Locale;
50 import static net.ktnx.mobileledger.utils.Logger.debug;
52 public final class MLDB {
53 public static final String ACCOUNTS_TABLE = "accounts";
54 public static final String DESCRIPTION_HISTORY_TABLE = "description_history";
55 public static final String OPT_LAST_SCRAPE = "last_scrape";
57 public static final String OPT_PROFILE_UUID = "profile_uuid";
58 private static final String NO_PROFILE = "-";
59 private static MobileLedgerDatabase dbHelper;
60 private static Application context;
61 private static void checkState() {
63 throw new IllegalStateException("First call init with a valid context");
65 public static SQLiteDatabase getDatabase() {
70 db = dbHelper.getWritableDatabase();
72 db.execSQL("pragma case_sensitive_like=ON;");
75 @SuppressWarnings("unused")
76 static public int getIntOption(String name, int default_value) {
77 String s = getOption(name, String.valueOf(default_value));
79 return Integer.parseInt(s);
82 debug("db", "returning default int value of " + name, e);
86 @SuppressWarnings("unused")
87 static public long getLongOption(String name, long default_value) {
88 String s = getOption(name, String.valueOf(default_value));
90 return Long.parseLong(s);
93 debug("db", "returning default long value of " + name, e);
97 static public String getOption(String name, String default_value) {
98 debug("db", "about to fetch option " + name);
99 SQLiteDatabase db = getDatabase();
100 try (Cursor cursor = db.rawQuery("select value from options where profile = ? and name=?",
101 new String[]{NO_PROFILE, name}))
103 if (cursor.moveToFirst()) {
104 String result = cursor.getString(0);
106 if (result == null) result = default_value;
108 debug("db", "option " + name + "=" + result);
111 else return default_value;
113 catch (Exception e) {
114 debug("db", "returning default value for " + name, e);
115 return default_value;
118 static public void setOption(String name, String value) {
119 debug("option", String.format("%s := %s", name, value));
120 DbOpQueue.add("insert or replace into options(profile, name, value) values(?, ?, ?);",
121 new String[]{NO_PROFILE, name, value});
123 @SuppressWarnings("unused")
124 static public void setLongOption(String name, long value) {
125 setOption(name, String.valueOf(value));
127 @TargetApi(Build.VERSION_CODES.N)
128 public static void hookAutocompletionAdapter(final Context context,
129 final AutoCompleteTextView view,
130 final String table, final String field,
131 final boolean profileSpecific) {
132 hookAutocompletionAdapter(context, view, table, field, profileSpecific, null, null,
133 Data.profile.getValue());
135 @TargetApi(Build.VERSION_CODES.N)
136 public static void hookAutocompletionAdapter(final Context context,
137 final AutoCompleteTextView view,
138 final String table, final String field,
139 final boolean profileSpecific, final View nextView,
140 final DescriptionSelectedCallback callback,
141 final MobileLedgerProfile profile) {
142 String[] from = {field};
143 int[] to = {android.R.id.text1};
144 SimpleCursorAdapter adapter =
145 new SimpleCursorAdapter(context, android.R.layout.simple_dropdown_item_1line, null,
147 adapter.setStringConversionColumn(1);
149 FilterQueryProvider provider = constraint -> {
150 if (constraint == null) return null;
152 String str = constraint.toString().toUpperCase();
153 debug("autocompletion", "Looking for " + str);
154 String[] col_names = {FontsContract.Columns._ID, field};
155 MatrixCursor c = new MatrixCursor(col_names);
159 if (profileSpecific) {
160 sql = String.format("SELECT %s as a, case when %s_upper LIKE ?||'%%' then 1 " +
161 "WHEN %s_upper LIKE '%%:'||?||'%%' then 2 " +
162 "WHEN %s_upper LIKE '%% '||?||'%%' then 3 else 9 end " +
164 "WHERE profile=? AND %s_upper LIKE '%%'||?||'%%' " +
165 "ORDER BY 2, 1;", field, field, field, field, table, field);
166 params = new String[]{str, str, str, profile.getUuid(), str};
169 sql = String.format("SELECT %s as a, case when %s_upper LIKE ?||'%%' then 1 " +
170 "WHEN %s_upper LIKE '%%:'||?||'%%' then 2 " +
171 "WHEN %s_upper LIKE '%% '||?||'%%' then 3 " + "else 9 end " +
172 "FROM %s " + "WHERE %s_upper LIKE '%%'||?||'%%' " +
173 "ORDER BY 2, 1;", field, field, field, field, table, field);
174 params = new String[]{str, str, str, str};
176 debug("autocompletion", sql);
177 SQLiteDatabase db = MLDB.getDatabase();
179 try (Cursor matches = db.rawQuery(sql, params)) {
181 while (matches.moveToNext()) {
182 String match = matches.getString(0);
183 int order = matches.getInt(1);
184 debug("autocompletion",
185 String.format(Locale.ENGLISH, "match: %s |%d", match, order));
186 c.newRow().add(i++).add(match);
194 adapter.setFilterQueryProvider(provider);
196 view.setAdapter(adapter);
198 if (nextView != null) {
199 view.setOnItemClickListener((parent, itemView, position, id) -> {
200 nextView.requestFocus(View.FOCUS_FORWARD);
201 if (callback != null) {
202 callback.descriptionSelected(String.valueOf(view.getText()));
207 public static synchronized void init(Application context) {
208 MLDB.context = context;
209 if (dbHelper != null)
210 throw new IllegalStateException("It appears init() was already called");
211 dbHelper = new MobileLedgerDatabase(context);
213 public static synchronized void done() {
214 if (dbHelper != null) {
215 debug("db", "Closing DB helper");
222 class MobileLedgerDatabase extends SQLiteOpenHelper {
223 private static final String DB_NAME = "MoLe.db";
224 private static final int LATEST_REVISION = 22;
225 private static final String CREATE_DB_SQL = "create_db";
227 private final Application mContext;
229 MobileLedgerDatabase(Application context) {
230 super(context, DB_NAME, null, LATEST_REVISION);
231 debug("db", "creating helper instance");
233 super.setWriteAheadLoggingEnabled(true);
237 public void onCreate(SQLiteDatabase db) {
238 debug("db", "onCreate called");
239 applyRevisionFile(db, CREATE_DB_SQL);
243 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
244 debug("db", "onUpgrade called");
245 for (int i = oldVersion + 1; i <= newVersion; i++) applyRevision(db, i);
248 private void applyRevision(SQLiteDatabase db, int rev_no) {
249 String rev_file = String.format(Locale.US, "sql_%d", rev_no);
251 applyRevisionFile(db, rev_file);
253 private void applyRevisionFile(SQLiteDatabase db, String rev_file) {
254 final Resources rm = mContext.getResources();
255 int res_id = rm.getIdentifier(rev_file, "raw", mContext.getPackageName());
257 throw new SQLException(String.format(Locale.US, "No resource for %s", rev_file));
258 db.beginTransaction();
259 try (InputStream res = rm.openRawResource(res_id)) {
260 debug("db", "Applying " + rev_file);
261 InputStreamReader isr = new InputStreamReader(res);
262 BufferedReader reader = new BufferedReader(isr);
266 while ((line = reader.readLine()) != null) {
267 if (line.startsWith("--")) {
271 if (line.isEmpty()) {
278 catch (Exception e) {
279 throw new RuntimeException(
280 String.format("Error applying %s, line %d", rev_file, line_no), e);
285 db.setTransactionSuccessful();
287 catch (IOException e) {
288 Log.e("db", String.format("Error opening raw resource for %s", rev_file));