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 import static net.ktnx.mobileledger.utils.Logger.debug;
51 public final class MLDB {
52 public static final String ACCOUNTS_TABLE = "accounts";
53 public static final String DESCRIPTION_HISTORY_TABLE = "description_history";
54 public static final String OPT_LAST_SCRAPE = "last_scrape";
56 public static final String OPT_PROFILE_UUID = "profile_uuid";
57 private static final String NO_PROFILE = "-";
58 private static MobileLedgerDatabase dbHelper;
59 private static Application context;
60 private static void checkState() {
62 throw new IllegalStateException("First call init with a valid context");
64 public static SQLiteDatabase getDatabase() {
69 db = dbHelper.getWritableDatabase();
71 db.execSQL("pragma case_sensitive_like=ON;");
74 static public int getIntOption(String name, int default_value) {
75 String s = getOption(name, String.valueOf(default_value));
77 return Integer.parseInt(s);
80 debug("db", "returning default int value of " + name, e);
84 static public long getLongOption(String name, long default_value) {
85 String s = getOption(name, String.valueOf(default_value));
87 return Long.parseLong(s);
90 debug("db", "returning default long value of " + name, e);
94 static public String getOption(String name, String default_value) {
95 debug("db", "about to fetch option " + name);
96 SQLiteDatabase db = getDatabase();
97 try (Cursor cursor = db.rawQuery("select value from options where profile = ? and name=?",
98 new String[]{NO_PROFILE, name}))
100 if (cursor.moveToFirst()) {
101 String result = cursor.getString(0);
103 if (result == null) result = default_value;
105 debug("db", "option " + name + "=" + result);
108 else return default_value;
110 catch (Exception e) {
111 debug("db", "returning default value for " + name, e);
112 return default_value;
115 static public void setOption(String name, String value) {
116 debug("option", String.format("%s := %s", name, value));
117 SQLiteDatabase db = MLDB.getDatabase();
118 db.execSQL("insert or replace into options(profile, name, value) values(?, ?, ?);",
119 new String[]{NO_PROFILE, name, value});
121 static public void setLongOption(String name, long value) {
122 setOption(name, String.valueOf(value));
124 @TargetApi(Build.VERSION_CODES.N)
125 public static void hookAutocompletionAdapter(final Context context,
126 final AutoCompleteTextView view,
127 final String table, final String field,
128 final boolean profileSpecific) {
129 hookAutocompletionAdapter(context, view, table, field, profileSpecific, null, null, Data.profile.get());
131 @TargetApi(Build.VERSION_CODES.N)
132 public static void hookAutocompletionAdapter(final Context context,
133 final AutoCompleteTextView view,
134 final String table, final String field,
135 final boolean profileSpecific, final View nextView,
136 final DescriptionSelectedCallback callback,
137 final MobileLedgerProfile profile) {
138 String[] from = {field};
139 int[] to = {android.R.id.text1};
140 SimpleCursorAdapter adapter =
141 new SimpleCursorAdapter(context, android.R.layout.simple_dropdown_item_1line, null,
143 adapter.setStringConversionColumn(1);
145 FilterQueryProvider provider = constraint -> {
146 if (constraint == null) return null;
148 String str = constraint.toString().toUpperCase();
149 debug("autocompletion", "Looking for " + str);
150 String[] col_names = {FontsContract.Columns._ID, field};
151 MatrixCursor c = new MatrixCursor(col_names);
155 if (profileSpecific) {
156 sql = String.format("SELECT %s as a, case when %s_upper LIKE ?||'%%' then 1 " +
157 "WHEN %s_upper LIKE '%%:'||?||'%%' then 2 " +
158 "WHEN %s_upper LIKE '%% '||?||'%%' then 3 else 9 end " +
160 "WHERE profile=? AND %s_upper LIKE '%%'||?||'%%' " +
161 "ORDER BY 2, 1;", field, field, field, field, table, field);
162 params = new String[]{str, str, str, profile.getUuid(), str};
165 sql = String.format("SELECT %s as a, case when %s_upper LIKE ?||'%%' then 1 " +
166 "WHEN %s_upper LIKE '%%:'||?||'%%' then 2 " +
167 "WHEN %s_upper LIKE '%% '||?||'%%' then 3 " + "else 9 end " +
168 "FROM %s " + "WHERE %s_upper LIKE '%%'||?||'%%' " +
169 "ORDER BY 2, 1;", field, field, field, field, table, field);
170 params = new String[]{str, str, str, str};
172 debug("autocompletion", sql);
173 SQLiteDatabase db = MLDB.getDatabase();
175 try (Cursor matches = db.rawQuery(sql, params)) {
177 while (matches.moveToNext()) {
178 String match = matches.getString(0);
179 int order = matches.getInt(1);
180 debug("autocompletion", String.format("match: %s |%d", match, order));
181 c.newRow().add(i++).add(match);
189 adapter.setFilterQueryProvider(provider);
191 view.setAdapter(adapter);
193 if (nextView != null) {
194 view.setOnItemClickListener((parent, itemView, position, id) -> {
195 nextView.requestFocus(View.FOCUS_FORWARD);
196 if (callback != null) {
197 callback.descriptionSelected(String.valueOf(view.getText()));
202 public static synchronized void init(Application context) {
203 MLDB.context = context;
204 if (dbHelper != null)
205 throw new IllegalStateException("It appears init() was already called");
206 dbHelper = new MobileLedgerDatabase(context);
208 public static synchronized void done() {
209 if (dbHelper != null) {
210 debug("db", "Closing DB helper");
217 class MobileLedgerDatabase extends SQLiteOpenHelper implements AutoCloseable {
218 private static final String DB_NAME = "MoLe.db";
219 private static final int LATEST_REVISION = 22;
220 private static final String CREATE_DB_SQL = "create_db";
222 private final Application mContext;
224 public MobileLedgerDatabase(Application context) {
225 super(context, DB_NAME, null, LATEST_REVISION);
226 debug("db", "creating helper instance");
228 super.setWriteAheadLoggingEnabled(true);
232 public void onCreate(SQLiteDatabase db) {
233 debug("db", "onCreate called");
234 applyRevisionFile(db, CREATE_DB_SQL);
238 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
239 debug("db", "onUpgrade called");
240 for (int i = oldVersion + 1; i <= newVersion; i++) applyRevision(db, i);
243 private void applyRevision(SQLiteDatabase db, int rev_no) {
244 String rev_file = String.format(Locale.US, "sql_%d", rev_no);
246 applyRevisionFile(db, rev_file);
248 private void applyRevisionFile(SQLiteDatabase db, String rev_file) {
249 final Resources rm = mContext.getResources();
250 int res_id = rm.getIdentifier(rev_file, "raw", mContext.getPackageName());
252 throw new SQLException(String.format(Locale.US, "No resource for %s", rev_file));
253 db.beginTransaction();
254 try (InputStream res = rm.openRawResource(res_id)) {
255 debug("db", "Applying " + rev_file);
256 InputStreamReader isr = new InputStreamReader(res);
257 BufferedReader reader = new BufferedReader(isr);
261 while ((line = reader.readLine()) != null) {
262 if (line.startsWith("--")) {
266 if (line.isEmpty()) {
273 catch (Exception e) {
274 throw new RuntimeException(
275 String.format("Error applying %s, line %d", rev_file, line_no), e);
280 db.setTransactionSuccessful();
282 catch (IOException e) {
283 Log.e("db", String.format("Error opening raw resource for %s", rev_file));