Using Sqlite with RxJava – > here comes SqlBrite!

Before you proceed any further, make sure you have read my earlier post on RxJava here first. Anyway I won’t talk much about RxJava because I’m still new to it and there’s already plenty of good articles out there, hence I’ll go straight to the coding. Before that, to recap, what I’m trying to achieve is to use SqlBrite for my database access in the Simple Finance Planner app, starting with the Home fragment . And while doing that, I’m also making quite a major changes to it.

Currently (version 2.2.x) here’s how the Home fragment look like:

Basically it just display the current month’s allocation for each section, and how much has been spent and its balance. Not much calculation and database query needed.

And what I’m trying to do (version 2.3):

As you can see from the figure above, now the app will calculate all income, expenses and balance from the start until the selected duration. Furthermore on the right side, you can see there’s a weather icon – this icon will represent your financial state for the section. I’ll explain more in the app when I’ve launched version 2.3, currently suffice to say that this part will involve much more database query and calculations.

So here’s how my previous (2.2) database access looks like:

Firstly here’s the db controller class:


public class DbControl {
    private SQLiteDatabase database;
    private MySQLiteHelper dbHelper;

    public DbControl(Context context) {
        dbHelper = new MySQLiteHelper(context);
    }

    public void open() throws SQLException {
        database = dbHelper.getWritableDatabase();
    }

    public void close() {
        dbHelper.close();
    }

    public BigDecimal getIncome(int startYear, int startMonth, int endYear, int endMonth){

    BigDecimal income;

    Cursor cursor = database.query(MySQLiteHelper.TABLE_INCOME, new String[] {"total"}, "year=" + startYear + " AND month=" + startMonth, null, null, null, null);

    try {
        if (cursor.moveToFirst()) {
            do {
                income = income.add(new BigDecimal(cursor.getString(cursor.getColumnIndex("total"))));
            } while (cursor.moveToNext());
        }
    }catch (Exception e) {
        e.printStackTrace();
    }finally {
        cursor.close();
    }

    return income;
    }
}

And here’s how we call it from our fragment:

public class MenuHome extends Fragment{
 
   private DbControl dbc;
   private BigDecimal income;

   @Override
   public void onActivityCreated(Bundle savedInstanceState){
       super.onActivityCreated(savedInstanceState);
       dbc = new DbControl(getActivity());
       try {
           dbc.open();
           income = dbc.getIncome(2016,12,2017,3);
       }catch (Exception e){
         e.printStackTrace();
       }finally{
         dbc.close();
       }
   }
}

And here’s the new one with SqlBrite; we start with the class that I created to put in all the queries there, which I call it DbHelper


public class DbHelper {

    private static MySQLiteHelper mySQLiteHelper;
    private static SqlBrite sqlBrite = new SqlBrite.Builder().build();
    private static BriteDatabase briteDb;

    public DbHelper(Context context) {
        mySQLiteHelper = new MySQLiteHelper(context);
        sqlBrite = new SqlBrite.Builder().build();
    }
    
    public static Observable<BigDecimal> getIncome(final int endYear, final int endMonth, final int moneyJar){
        briteDb = sqlBrite.wrapDatabaseHelper(mySQLiteHelper, Schedulers.io());
        final String[] args = new String[]{endYear + "", endMonth + "", moneyJar+ "", };
        Observable&amp;amp;amp;lt;BigDecimal&amp;amp;amp;gt; myObservable;
        myObservable = briteDb.createQuery("income","SELECT total FROM income WHERE (year = ? AND month &amp;amp;amp;lt;= ?) AND moneyJar = ? and isDeleted = 0", args)
         .map(new Func1&amp;amp;amp;lt;SqlBrite.Query, BigDecimal&amp;amp;amp;gt;() {
            @Override
                public BigDecimal call(SqlBrite.Query query) {
                    Cursor cursor = query.run();
                    BigDecimal income, incomeTotal = new BigDecimal(0);
                    if (cursor != null) {
                        try {
                            if (cursor.getCount() &amp;amp;amp;gt; 0 &amp;amp;amp;amp;&amp;amp;amp;amp; cursor.moveToFirst()) {
                                do {
                                    income = new BigDecimal(cursor.getString(cursor.getColumnIndex("total")));
                                    incomeTotal = incomeTotal.add(income);
                                } while (cursor.moveToNext());
                            }
                        } catch (Exception e) {
                            e.printStackTrace();
                        } finally {
                            cursor.close();
                        }
                    }
                    return incomeTotal;
                }
            });
           return myObservable;
        }
    }
}

And here’s how we call it from our fragment:


public class MenuSummary extends Fragment{
    private DbHelper dbHelper;
    private static Observable imin;
    private static Subscription s;
    private static Observable&amp;amp;amp;lt;?&amp;amp;amp;gt; getIncome1, getIncome2, getIncome3, getIncome4, getIncome5;

    @Override
    public void onResume(){
        super.onResume();
        displaySummary(selectedYear,selectedMonth);
    }

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        dbHelper = new DbHelper(getContext());
    }

    @Override
    public void onPause(){
        super.onPause();
        dbHelper.close();
        s.unsubscribe();
    }

    private void displaySummary(final int selectedYear, final int selectedMonth){
        getIncome1 = dbHelper.getIncome(selectedYear,selectedMonth,1);
        getIncome2 = dbHelper.getIncome(selectedYear,selectedMonth,2);
        getIncome3 = dbHelper.getIncome(selectedYear,selectedMonth,3);
        getIncome4 = dbHelper.getIncome(selectedYear,selectedMonth,4);
        getIncome5 = dbHelper.getIncome(selectedYear,selectedMonth,5);

        List&amp;amp;amp;lt;Observable&amp;amp;amp;lt;?&amp;amp;amp;gt;&amp;amp;amp;gt; myObservables = Arrays.asList(getIncome1,getIncome2,getIncome3,getIncome4,getIncome5);

        imin = Observable.combineLatest(myObservables, new FuncN&amp;amp;amp;lt;BigDecimal&amp;amp;amp;gt;() {
            @Override
            public BigDecimal call(Object... args) {
                BigDecimal totalIncome;
                totalIncome = (BigDecimal) args[0] + (BigDecimal) args[1] + (BigDecimal) args[2] + (BigDecimal) args[3] + (BigDecimal) args[4];
                return totalIncome;
            }
        }).subscribeOn(Schedulers.io())
          .observeOn(AndroidSchedulers.mainThread());

            s = imin.subscribe(new Action1&amp;amp;amp;lt;BigDecimal&amp;amp;amp;gt;() {
            @Override
            public void call(BigDecimal totalIncome) {
                tvIncome.setText(totalIncome + "");
            }
        }
    }
}

Take note that the codes above are just boilerplate of the actual code, since the actual code is much longer. Let me explain below what the new code above does, starting from DbHelper class:

  • I created this DbHelper class to separate all observable queries, since these queries will later be used by plenty of other fragments and activities
  • Line 7 -11 is basically the constructor. The interesting part starts at line 13, where now we perform our query using  SqlBrite. I think the codes are self-explanatory there (if you have gone through several tutorials and articles about RxJava/RxAndroid that is).
  • One thing that stands out is, instead of returning BigDecimal, our function now return Observable.

Let’s get on to our fragment  MenuSummary which calls the function getIncome.

  • Let’s take a look directly at the function displaySummary starting at line 25 there. As you can see from line 26 – 30 there, we call dbHelper.getIncome and passing in similar arguments save for the last argument.
  • Line 32 is interesting – here we put all the observables into a List of Observable, so that later at line 34 we can combine and manipulate the result of the observables.
  • So what line 34 – 40 does is it adds all the BigDecimal that was returned in Observables from line 26 – 30. And all of this are being done not in UI thread. Line 41 subscribeOn(Schedulers.io()) made sure of this.
  • In line 42 – 48, we set a text view with the BigDecimal which was passed it to.  We can only update our views from the UI/main thread, hence the line 42 observeOn(AndroidSchedulers.mainThread()); made sure of this.

Basically that’s it. Now all the queries and calculations are being done on another thread, but the updating of the view is one on main thread. By using SqlBrite (and RxJava) you can see how compact the codes are.

….

….

However, not all are fine and dandy. When testing the app, I found an issue has been hunting me. Although this issue probably won’t occur on a normal use, but when it happens, it will crash the app. Not good.

If I try to load fragment MenuSummary more than 10 times quickly, the app will again, crash with the message

E/CursorWindow: Could not allocate CursorWindow '/data/data/com.imincode.meniti/databases/meniti' of size 2097152 due to error -12.
E/CursorWindow: Could not allocate CursorWindow '/data/data/com.imincode.meniti/databases/meniti' of size 2097152 due to error -12.
E/CursorWindow: Could not allocate CursorWindow '/data/data/com.imincode.meniti/databases/meniti' of size 2097152 due to error -12.
W/libc: pthread_create failed: couldn't allocate 1064960-byte stack: Out of memory
W/libc: pthread_create failed: couldn't allocate 1064960-byte stack: Out of memory
W/System.err: android.database.CursorWindowAllocationException: Cursor window allocation of 2048 kb failed. 
W/System.err: android.database.CursorWindowAllocationException: Cursor window allocation of 2048 kb failed. 
W/System.err: android.database.CursorWindowAllocationException: Cursor window allocation of 2048 kb failed. 

Currently I’m still finding a way to fix this problem. Really hope that I could get this fixed asap as I want to release version 2.3 soon.

Anyone?

Leave a Reply

Your email address will not be published. Required fields are marked *