Java tutorial
/******************************************************************************* * Copyright (c) 2010 Denis Solonenko. * All rights reserved. This program and the accompanying materials * are made available under the terms of the GNU Public License v2.0 * which accompanies this distribution, and is available at * http://www.gnu.org/licenses/old-licenses/gpl-2.0.html * * Contributors: * Denis Solonenko - initial API and implementation ******************************************************************************/ package ru.orangesoftware.financisto2.db; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.support.v4.util.LongSparseArray; import java.util.ArrayList; import java.util.HashMap; import java.util.LinkedList; import java.util.List; import java.util.Map; import ru.orangesoftware.financisto2.blotter.BlotterFilter; import ru.orangesoftware.financisto2.datetime.Period; import ru.orangesoftware.financisto2.filter.Criteria; import ru.orangesoftware.financisto2.filter.WhereFilter; import ru.orangesoftware.financisto2.model.Account; import ru.orangesoftware.financisto2.model.Attribute; import ru.orangesoftware.financisto2.model.Budget; import ru.orangesoftware.financisto2.model.Category; import ru.orangesoftware.financisto2.model.Currency; import ru.orangesoftware.financisto2.model.MyEntity; import ru.orangesoftware.financisto2.model.Payee; import ru.orangesoftware.financisto2.model.Project; import ru.orangesoftware.financisto2.model.SystemAttribute; import ru.orangesoftware.financisto2.model.Transaction; import ru.orangesoftware.financisto2.model.TransactionAttributeInfo; import ru.orangesoftware.financisto2.model.TransactionInfo; import ru.orangesoftware.financisto2.utils.MyPreferences; import ru.orangesoftware.financisto2.utils.MyPreferences.AccountSortOrder; import ru.orangesoftware.financisto2.utils.RecurUtils; import ru.orangesoftware.financisto2.utils.RecurUtils.Recur; import ru.orangesoftware.financisto2.utils.Utils; import ru.orangesoftware.orb.EntityManager; import ru.orangesoftware.orb.Expression; import ru.orangesoftware.orb.Expressions; import ru.orangesoftware.orb.Query; import static ru.orangesoftware.financisto2.db.DatabaseHelper.ACCOUNT_TABLE; import static ru.orangesoftware.financisto2.db.DatabaseHelper.ATTRIBUTES_TABLE; import static ru.orangesoftware.financisto2.db.DatabaseHelper.AccountColumns; import static ru.orangesoftware.financisto2.db.DatabaseHelper.BUDGET_TABLE; import static ru.orangesoftware.financisto2.db.DatabaseHelper.CATEGORY_ATTRIBUTE_TABLE; import static ru.orangesoftware.financisto2.db.DatabaseHelper.CURRENCY_TABLE; import static ru.orangesoftware.financisto2.db.DatabaseHelper.TRANSACTION_ATTRIBUTE_TABLE; import static ru.orangesoftware.financisto2.utils.StringUtil.capitalize; class MyEntityManager extends EntityManager { public final Context context; MyEntityManager(Context context) { this.context = context; } private <T extends MyEntity> ArrayList<T> getAllEntitiesList(Class<T> clazz, boolean include0) { Query<T> q = createQuery(clazz); q.where(include0 ? Expressions.gte("id", 0) : Expressions.gt("id", 0)); q.asc("title"); Cursor c = q.execute(); try { T e0 = null; ArrayList<T> list = new ArrayList<T>(); while (c.moveToNext()) { T e = EntityManager.loadFromCursor(c, clazz); if (e.id == 0) { e0 = e; } else { list.add(e); } } if (e0 != null) { list.add(0, e0); } return list; } finally { c.close(); } } private <T extends MyEntity> ArrayList<T> getAllEntitiesList(Class<T> clazz, boolean include0, boolean onlyActive) { Query<T> q = createQuery(clazz); Expression include0Ex = include0 ? Expressions.gte("id", 0) : Expressions.gt("id", 0); if (onlyActive) { q.where(Expressions.and(include0Ex, Expressions.eq("isActive", 1))); } else { q.where(include0Ex); } q.asc("title"); Cursor c = q.execute(); try { T e0 = null; ArrayList<T> list = new ArrayList<T>(); while (c.moveToNext()) { T e = EntityManager.loadFromCursor(c, clazz); if (e.id == 0) { e0 = e; } else { list.add(e); } } if (e0 != null) { list.add(0, e0); } return list; } finally { c.close(); } } /* =============================================== * TRANSACTION INFO * =============================================== */ public TransactionInfo getTransactionInfo(long transactionId) { return get(TransactionInfo.class, transactionId); } public List<TransactionAttributeInfo> getAttributesForTransaction(long transactionId) { Query<TransactionAttributeInfo> q = createQuery(TransactionAttributeInfo.class).asc("name"); q.where(Expressions.and(Expressions.eq("transactionId", transactionId), Expressions.gte("attributeId", 0))); Cursor c = q.execute(); try { List<TransactionAttributeInfo> list = new LinkedList<TransactionAttributeInfo>(); while (c.moveToNext()) { TransactionAttributeInfo ti = loadFromCursor(c, TransactionAttributeInfo.class); list.add(ti); } return list; } finally { c.close(); } } public TransactionAttributeInfo getSystemAttributeForTransaction(SystemAttribute sa, long transactionId) { Query<TransactionAttributeInfo> q = createQuery(TransactionAttributeInfo.class); q.where(Expressions.and(Expressions.eq("transactionId", transactionId), Expressions.eq("attributeId", sa.id))); Cursor c = q.execute(); try { if (c.moveToFirst()) { return loadFromCursor(c, TransactionAttributeInfo.class); } return null; } finally { c.close(); } } /* =============================================== * ACCOUNT * =============================================== */ public Account getAccount(long id) { return get(Account.class, id); } public Cursor getAccountsForTransaction(Transaction t) { return getAllAccounts(true, t.fromAccountId, t.toAccountId); } public Cursor getAllActiveAccounts() { return getAllAccounts(true); } public Cursor getAllAccounts() { return getAllAccounts(false); } private Cursor getAllAccounts(boolean isActiveOnly, long... includeAccounts) { AccountSortOrder sortOrder = MyPreferences.getAccountSortOrder(context); Query<Account> q = createQuery(Account.class); if (isActiveOnly) { int count = includeAccounts.length; if (count > 0) { Expression[] ee = new Expression[count + 1]; for (int i = 0; i < count; i++) { ee[i] = Expressions.eq("id", includeAccounts[i]); } ee[count] = Expressions.eq("isActive", 1); q.where(Expressions.or(ee)); } else { q.where(Expressions.eq("isActive", 1)); } } q.desc("isActive"); if (sortOrder.asc) { q.asc(sortOrder.property); } else { q.desc(sortOrder.property); } return q.asc("title").execute(); } public long saveAccount(Account account) { return saveOrUpdate(account); } public List<Account> getAllAccountsList() { return getAllAccountsList(false); } public List<Account> getAllAccountsList(boolean activeOnly) { List<Account> list = new ArrayList<Account>(); Cursor c = getAllAccounts(activeOnly); try { while (c.moveToNext()) { Account a = EntityManager.loadFromCursor(c, Account.class); list.add(a); } } finally { c.close(); } return list; } public LongSparseArray<Account> getAllAccountsMap() { LongSparseArray<Account> accountsMap = new LongSparseArray<Account>(); List<Account> list = getAllAccountsList(false); for (Account account : list) { accountsMap.put(account.id, account); } return accountsMap; } /* =============================================== * CURRENCY * =============================================== */ private static final String UPDATE_DEFAULT_FLAG = "update currency set is_default=0"; public long saveOrUpdate(Currency currency) { SQLiteDatabase db = db(); db.beginTransaction(); try { if (currency.isDefault) { db.execSQL(UPDATE_DEFAULT_FLAG); } long id = super.saveOrUpdate(currency); db.setTransactionSuccessful(); return id; } finally { db.endTransaction(); } } public int deleteCurrency(long id) { String sid = String.valueOf(id); Currency c = load(Currency.class, id); writeDeleteLog(CURRENCY_TABLE, c.remoteKey); return db().delete(CURRENCY_TABLE, "_id=? AND NOT EXISTS (SELECT 1 FROM " + ACCOUNT_TABLE + " WHERE " + AccountColumns.CURRENCY_ID + "=?)", new String[] { sid, sid }); } public Cursor getAllCurrencies(String sortBy) { Query<Currency> q = createQuery(Currency.class); return q.desc("isDefault").asc(sortBy).execute(); } public List<Currency> getAllCurrenciesList() { return getAllCurrenciesList("name"); } public List<Currency> getAllCurrenciesList(String sortBy) { Query<Currency> q = createQuery(Currency.class); return q.desc("isDefault").asc(sortBy).list(); } public Map<String, Currency> getAllCurrenciesByTtitleMap() { return entitiesAsTitleMap(getAllCurrenciesList("name")); } /* =============================================== * TRANSACTIONS * =============================================== */ // public Cursor getBlotter(WhereFilter blotterFilter) { // long t0 = System.currentTimeMillis(); // try { // Query<TransactionInfo> q = createQuery(TransactionInfo.class); // if (!blotterFilter.isEmpty()) { // q.where(blotterFilter.toWhereExpression()); // } // q.desc("dateTime"); // return q.list(); // } finally { // Log.d("BLOTTER", "getBlotter executed in "+(System.currentTimeMillis()-t0)+"ms"); // } // } // // public Cursor getTransactions(WhereFilter blotterFilter) { // return null; // } // public Cursor getAllProjects(boolean includeNoProject) { // Query<Project> q = createQuery(Project.class); // if (!includeNoProject) { // q.where(Expressions.neq("id", 0)); // } // return q.list(); // } public Project getProject(long id) { return get(Project.class, id); } public ArrayList<Project> getAllProjectsList(boolean includeNoProject) { return getAllEntitiesList(Project.class, includeNoProject); } public ArrayList<Project> getActiveProjectsList(boolean includeNoProject) { return getAllEntitiesList(Project.class, includeNoProject, true); } public Map<String, Project> getAllProjectsByTitleMap(boolean includeNoProject) { return entitiesAsTitleMap(getAllProjectsList(includeNoProject)); } public LongSparseArray<Project> getAllProjectsByIdMap(boolean includeNoProject) { return entitiesAsIdMap(getAllProjectsList(includeNoProject)); } // public Category getCategoryByLeft(long left) { // Query<Category> q = createQuery(Category.class); // q.where(Expressions.eq("left", left)); // return q.uniqueResult(); // } // // public Cursor getAllCategories(boolean includeNoCategory) { // Query<CategoryInfo> q = createQuery(CategoryInfo.class); // if (!includeNoCategory) { // q.where(Expressions.neq("id", 0)); // } // return q.list(); // } // // public Cursor getAllCategoriesWithoutSubtree(long id) { // Category c = load(Category.class, id); // Query<CategoryInfo> q = createQuery(CategoryInfo.class); // q.where(Expressions.not(Expressions.and( // Expressions.gte("left", c.left), // Expressions.lte("right", c.right) // ))); // return q.list(); // } public long insertBudget(Budget budget) { SQLiteDatabase db = db(); db.beginTransaction(); try { if (budget.id > 0) { deleteBudget(budget.id); } long id = 0; Recur recur = RecurUtils.createFromExtraString(budget.recur); Period[] periods = RecurUtils.periods(recur); for (int i = 0; i < periods.length; i++) { Period p = periods[i]; budget.id = -1; budget.parentBudgetId = id; budget.recurNum = i; budget.startDate = p.start; budget.endDate = p.end; long bid = super.saveOrUpdate(budget); if (i == 0) { id = bid; } } db.setTransactionSuccessful(); return id; } finally { db.endTransaction(); } } public void deleteBudget(long id) { SQLiteDatabase db = db(); Budget b = load(Budget.class, id); writeDeleteLog(BUDGET_TABLE, b.remoteKey); db.delete(BUDGET_TABLE, "_id=?", new String[] { String.valueOf(id) }); String sql = "select remote_key from " + BUDGET_TABLE + " where parent_budget_id=" + id + ""; Cursor cursorCursor = db.rawQuery(sql, null); if (cursorCursor.moveToFirst()) { do { String rKey = cursorCursor.getString(0); writeDeleteLog(BUDGET_TABLE, rKey); } while (cursorCursor.moveToNext()); } cursorCursor.close(); db.delete(BUDGET_TABLE, "parent_budget_id=?", new String[] { String.valueOf(id) }); } public void deleteBudgetOneEntry(long id) { SQLiteDatabase db = db(); Budget b = load(Budget.class, id); writeDeleteLog(BUDGET_TABLE, b.remoteKey); db.delete(BUDGET_TABLE, "_id=?", new String[] { String.valueOf(id) }); } public ArrayList<Budget> getAllBudgets(WhereFilter filter) { Query<Budget> q = createQuery(Budget.class); Criteria c = filter.get(BlotterFilter.DATETIME); if (c != null) { long start = c.getLongValue1(); long end = c.getLongValue2(); q.where(Expressions.and(Expressions.lte("startDate", end), Expressions.gte("endDate", start))); } Cursor cursor = q.execute(); try { ArrayList<Budget> list = new ArrayList<Budget>(); while (cursor.moveToNext()) { Budget b = MyEntityManager.loadFromCursor(cursor, Budget.class); list.add(b); } return list; } finally { cursor.close(); } } public void deleteProject(long id) { SQLiteDatabase db = db(); db.beginTransaction(); try { delete(Project.class, id); ContentValues values = new ContentValues(); values.put("project_id", 0); db.update("transactions", values, "project_id=?", new String[] { String.valueOf(id) }); db.setTransactionSuccessful(); } finally { db.endTransaction(); } } public ArrayList<TransactionInfo> getAllScheduledTransactions() { Query<TransactionInfo> q = createQuery(TransactionInfo.class); q.where(Expressions.and(Expressions.eq("isTemplate", 2), Expressions.eq("parentId", 0))); return (ArrayList<TransactionInfo>) q.list(); } public ArrayList<Category> getAllCategoriesList(boolean includeNoCategory) { return getAllEntitiesList(Category.class, includeNoCategory); } public Payee insertPayee(String payee) { if (Utils.isEmpty(payee)) { return Payee.EMPTY; } else { Payee p = getPayee(payee); if (p == null) { p = new Payee(); p.title = payee; p.id = saveOrUpdate(p); } return p; } } public Payee getPayee(String payee) { Query<Payee> q = createQuery(Payee.class); q.where(Expressions.eq("title", payee)); return q.uniqueResult(); } public Cursor getAllPayees() { Query<Payee> q = createQuery(Payee.class); return q.asc("title").execute(); } public List<Payee> getAllPayeeList() { return getAllEntitiesList(Payee.class, true); } public Map<String, Payee> getAllPayeeByTitleMap() { return entitiesAsTitleMap(getAllPayeeList()); } public LongSparseArray<Payee> getAllPayeeByIdMap() { return entitiesAsIdMap(getAllPayeeList()); } public Cursor getAllPayeesLike(CharSequence constraint) { Query<Payee> q = createQuery(Payee.class); q.where(Expressions.or(Expressions.like("title", "%" + constraint + "%"), Expressions.like("title", "%" + capitalize(constraint.toString()) + "%"))); return q.asc("title").execute(); } public List<Transaction> getSplitsForTransaction(long transactionId) { Query<Transaction> q = createQuery(Transaction.class); q.where(Expressions.eq("parentId", transactionId)); return q.list(); } public List<TransactionInfo> getSplitsInfoForTransaction(long transactionId) { Query<TransactionInfo> q = createQuery(TransactionInfo.class); q.where(Expressions.eq("parentId", transactionId)); return q.list(); } public List<TransactionInfo> getTransactionsForAccount(long accountId) { Query<TransactionInfo> q = createQuery(TransactionInfo.class); q.where(Expressions.and(Expressions.eq("fromAccount.id", accountId), Expressions.eq("parentId", 0))); q.desc("dateTime"); return q.list(); } public void reInsertCategory(Category c) { reInsert(c); } public Currency getHomeCurrency() { Query<Currency> q = createQuery(Currency.class); q.where(Expressions.eq("isDefault", "1")); //uh-oh Currency homeCurrency = q.uniqueResult(); if (homeCurrency == null) { homeCurrency = Currency.EMPTY; } return homeCurrency; } private long writeDeleteLog(String tableName, String remoteKey) { if (remoteKey == null || remoteKey.length() == 0) { return 0; } ContentValues row = new ContentValues(); row.put(DatabaseHelper.deleteLogColumns.TABLE_NAME, tableName); row.put(DatabaseHelper.deleteLogColumns.REMOTE_KEY, remoteKey); row.put(DatabaseHelper.deleteLogColumns.DELETED_ON, System.currentTimeMillis()); return db().insert(DatabaseHelper.DELETE_LOG_TABLE, null, row); } private static <T extends MyEntity> Map<String, T> entitiesAsTitleMap(List<T> entities) { Map<String, T> map = new HashMap<String, T>(); for (T e : entities) { map.put(e.title, e); } return map; } private static <T extends MyEntity> LongSparseArray<T> entitiesAsIdMap(List<T> entities) { LongSparseArray<T> map = new LongSparseArray<T>(); for (T e : entities) { map.put(e.id, e); } return map; } public void deleteAttribute(long id) { SQLiteDatabase db = db(); db.beginTransaction(); try { Attribute attr = get(Attribute.class, id); String key = attr.remoteKey; String[] p = new String[] { String.valueOf(id) }; db.delete(ATTRIBUTES_TABLE, DatabaseHelper.AttributeColumns.ID + "=?", p); db.delete(CATEGORY_ATTRIBUTE_TABLE, DatabaseHelper.CategoryAttributeColumns.ATTRIBUTE_ID + "=?", p); db.delete(TRANSACTION_ATTRIBUTE_TABLE, DatabaseHelper.TransactionAttributeColumns.ATTRIBUTE_ID + "=?", p); db.setTransactionSuccessful(); writeDeleteLog(ATTRIBUTES_TABLE, key); } finally { db.endTransaction(); } } public Attribute getSystemAttribute(SystemAttribute a) { Attribute sa = get(Attribute.class, a.id); sa.name = context.getString(a.titleId); return sa; } public List<Attribute> getAllAttributes() { return createQuery(Attribute.class).where(Expressions.gt("id", 0)).asc("name").list(); } public LongSparseArray<Attribute> getAllAttributesMap() { LongSparseArray<Attribute> array = new LongSparseArray<Attribute>(); List<Attribute> attributes = getAllAttributes(); for (Attribute attribute : attributes) { array.put(attribute.getId(), attribute); } return array; } }