Motivation can be found at Using a Prepared Statement section of http://stackoverflow.com/q/1711631/892500

For the record, I'm not sure if this is an issue with the library, or with the way I'm handling observables, but I thought I would document anyway.

I'm noticing that none of the on (onNext, onCompleted, onError) functions are being triggered when performing a toList operation on the resulting observable. To demonstrate...

db.createQuery("users", sql)
  .flatMap(new Func1<SqlBrite.Query, Observable<User>>() {
    @Override
    public Observable<User> call(SqlBrite.Query query) {
      Cursor cursor = query.run();
      List<User> users = new ArrayList<User>();
      try {
        while (cursor.moveToNext()) {
          int id = cursor.getInt(cursor.getColumnIndex("id"));
          String name = cursor.getString(cursor.getColumnIndex("name"));
          users.add(new User(id, name));
        }
        return Observable.from(users);
      } finally {
        cursor.close();
      }
    }
  })
  .toList()
  .subscribe(new Subscriber<List<User>>() {
      @Override
      public void onCompleted() {
        // No op
      }

      @Override
      public void onError(Throwable e) {
        e.printStackTrace();
      }

      @Override
      public void onNext(List<User> users) {
        // Never gets called
      }
  });

If I drop toList, all is fine.

StrictMode is lazy and only detects slow queries on the main thread. This is mostly useless since the query execution time is subjective based on the data in the DB and CPU/disk performance of the device.

There's a few options here:

• Inform StrictMode via noteSlowCall and allow normal penalties to apply. This sucks because there's no granularity between those posting to this method and puts the subjectiveness of what's slow into the libraries. There would be no way to enable slow call penalties from one library but disable it from another. Granted, this is unlikely.
• Add our own strict mode analogous which allowed the detection of queries running on the UI thread.
• Provide a composition point for either observable creation or query object creation. This would allow someone to mix in things like main thread checking, or perhaps default schedulers.

This isn't urgent, so we have time to think about what's most appropriate.

Instead of mapToOneOrNull.

like the title say,how can i do

Sorry about the cryptic title. The issue is better expressed with some code and an explanation.

I'm creating an Observable for listening to sms messages, specifically I care about sent messages.

Observable<SqlBrite.Query> query = resolver.createQuery(Uri.parse("content://sms/"), null, null, null, null, true);

subscription = query
    // Ignore first immediately emitted value
  .skip(1)
    // read the query, output Message object, which has data from the cursor
  .flatMap(…) 
    // filter everything except sent messages
  .filter(…)
    // Only unique messages
  .distinct(…)
    // We only care about certain keyword in the message
  .filter(…)
    // Show a dialog box
  .compose(…)
    // Filter out if user canceled dialog
  .filter(…)
  .subscribeOn(Schedulers.io())
  .observeOn(scheduler)
  .subscribe(…);

Even after filtering on "sent" I'll get about 5 or 6 Messageobjects per actual sent sms message. When I first push a build to my phone, everything works as expected. Some unknown time later (I usually notice the next day) two identical Message objects pass distinct(). Specifically the first two.

Message has correctly implemented equals() and hashCode().

This may or may not be an issue with SqlBrite but I've been battling this for a while so I'm exploring all of my options. (I have a similar message out on the RxJava mailing list).

When I use createQuery with Subscriber as a observer, the onCompleted method isn't called. I think the process have been finished in onNext method.

So i created a class called CursorListAdapter that extends from AbstractList and uses a Cursor as it's backing:

public class CursorListAdapter<T>
        extends AbstractList<T> {

    private Cursor cursor;
    private Func1<Cursor, T> mapper;

    public CursorListAdapter(Func1<Cursor, T> mapper) {
        this.mapper = mapper;
    }

    public CursorListAdapter(Cursor cursor, Func1<Cursor, T> mapper) {
        this.cursor = cursor;
        this.mapper = mapper;
    }

    public void setCursor(Cursor cursor) {
        this.cursor = cursor;
    }

    public synchronized void closeCursor() {
        if (cursor!=null && !cursor.isClosed()) {
            this.cursor.close();;
        }
    }

    public Cursor getCursor() {
        return this.cursor;
    }

    @Override
    public T get(int location) {
        if (cursor==null) {
            throw new IndexOutOfBoundsException("cursor is null");
        } else if (cursor.isClosed()) {
            throw new IndexOutOfBoundsException("cursor is closed");
        }
        if (!cursor.moveToPosition(location)) {
            throw new IndexOutOfBoundsException("moveToPosition failed");
        }
        return mapper.call(cursor);
    }

    @Override
    public int size() {
        return cursor!=null && !cursor.isClosed()
                ? cursor.getCount()
                : 0;
    }
}

and then i created an Observable.Operator to lift(...) Observable<SqlBrite.Query>s into an Observable of the given type:

public class CursorListAdapterOperator<T>
    implements Observable.Operator<List<T>, SqlBrite.Query> {

    private final Func1<Cursor, T> mapper;

    public CursorListAdapterOperator(Func1<Cursor, T> mapper) {
        this.mapper = mapper;
    }

    @Override
    public Subscriber<? super SqlBrite.Query> call(final Subscriber<? super List<T>> subscriber) {
        return new Subscriber<SqlBrite.Query>(subscriber) {

            final CursorListAdapter<T> list = new CursorListAdapter<>(mapper);

            @Override
            public void onNext(SqlBrite.Query query) {
                try {
                    list.closeCursor();
                    list.setCursor(query.run());
                    if (!subscriber.isUnsubscribed()) {
                        subscriber.onNext(list);
                    }
                } catch (Throwable e) {
                    Exceptions.throwIfFatal(e);
                    onError(OnErrorThrowable.addValueAsLastCause(e, query.toString()));
                }
            }

            @Override
            public void onCompleted() {
                subscriber.onCompleted();
                list.closeCursor();
            }

            @Override
            public void onError(Throwable e) {
                subscriber.onError(e);
                list.closeCursor();
            }
        };
    }
}

I see a StrictMode warning in my console about not calling close on the cursor and I'm wondering if that's something i need to worry about or not. Here's the warning:

    flipagram E/StrictMode: A resource was acquired at attached stack trace but never released. See java.io.Closeable for information on avoiding resource leaks.
    java.lang.Throwable: Explicit termination method 'close' not called
       at dalvik.system.CloseGuard.open(CloseGuard.java:180)
       at android.content.ContentResolver$CursorWrapperInner.<init>(ContentResolver.java:2507)
       at android.content.ContentResolver.query(ContentResolver.java:515)
       at android.content.ContentResolver.query(ContentResolver.java:434)
       at com.squareup.sqlbrite.BriteContentResolver$1.run(BriteContentResolver.java:83)
       at XXXX.CursorListAdapterOperator$1.onNext(CursorListAdapterOperator.java:41)
       at XXXX.CursorListAdapterOperator$1.onNext(CursorListAdapterOperator.java:32)
       at com.squareup.sqlbrite.BackpressureBufferLastOperator$BufferLastSubscriber.onNext(BackpressureBufferLastOperator.java:101)

I'm relatively new to RxJava and SqlBrite - but my Operator should be properly closing the Cursor right?

I would like to have some compile-time or runtime checks about whether or not the current thread is in the transaction

the easiest way - to expose the thread-local transaction and do runtime checks on it

the safest way I think would be to add insert/update/delete methods to the Transaction type
or just serving the BriteDatabase from the Transaction
otherwise it's kind of awkward to carry around both BriteDatabase type for modification methods and a Transaction type for a proof of transaction

what do you think?

In a current application that I'm writing I need to perform a lot of data synchronization. To avoid performing a lot of queries to check if items exist, then updating or insert, ideally I would want to use something like INSERT OR REPLACE and COALESCE on the columns that can be updated.

It would be great if this where to happen in the future to avoid having to query the database a ton of times. I see this being something built into the model builder. If a defined column (by annotation) is editable (by annotation) after inserting then the builder should add a content item such that the value is something like:

values.add(COLUMN_NAME, String.format("COALESCE((SELECT %s FROM %S WHERE id = %s), '%s')", 
COLUMN_NAME, TABLE, ID, value);

This approach allows us to use the current updating / inserting mechanisms without having to use execSql(String sql). The ID would need to be set within the builder before calling the method which adds this content value though.

Hi Jake,
We over at Yahoo open-sourced SquiDB last week, a SQLite DAO layer for Android. The library is pretty fully featured, and I think it addresses the use cases of a lot of your open issues:

• It has query builders to avoid string concatenation (addresses #2)
• It uses model objects for typesafe inserts, updates, and reading data from cursor in a typesafe way (addresses #1 and #3)
• Model objects are generated using an annotation processor (like in PR #16)

We have a custom mechanism for listening for data changes that uses Uris and ContentObservers, but no RxJava support, which it seems like people want. If you think SquiDB addresses the use cases you have in mind for evolving the feature set of sqlbrite, we're definitely open to collaborating or combining efforts! If it turns out what we have isn't the direction you're interested in going, no offense taken at all.

Reproduce:

database.createQuery(TABLE, QUERY)
  .subscribe(query -> {
    try (Transaction = database.newTransaction()) {
      ...
      transaction.markSuccessful();
    }
  });

triggers get sent from SqliteTransaction$onCommit which is called as part of endTransaction, instead of afterwards, causing an exception:
java.lang.IllegalStateException: Cannot perform this operation because the transaction has already been marked successful. The only thing you can do now is call endTransaction().

Switching threads before subscribing works as a temp fix

String concatenation is arduous and error-prone. Provide a means of constructing queries in a way that reduces errors, increases type-safety, and isn't extremely noisy in the code.

Logging, specifically, but we can foresee this being more things in the future.

Since inserts and updates operate on a single table, provide a means of constructing both of these in a way that's not just raw ContentValues creation.

• Creating a query inside of a transaction
• Question marks in a query don't match number of arguments (#220)
• Missing mark success in try/finally or try-with-resources
• Multiple insert/update/delete statements not in a transaction in a single method
• Ensure first argument looks like a table / list of tables
• Second argument looks like SQL and contains all tables from the first argument

I use transactions to insert multiple items via SQLBrite wrapper. Problem is that some items can be already in database, so there are conflicts. I want to use insertWithOnConflict() method with IGNORE for conflicts. How I can accomplish this using SQLBrite?

    sqlBrite.beginTransaction();
            try {
                for (Message msg : messages) {
                    insertMessageViaSQLBrite(msg);
                }
                sqlBrite.setTransactionSuccessful();
            } finally {
                sqlBrite.endTransaction();
            }

Reload SQLiteOpenHelper and re-query

In my app I have a import/export functionality, by importing, the SQLite database file is replaced by one on the sdcard, so I need to reload the SQLiteOpenHelper and then notify all the Queries.

Something like the following method would be really handy!

public void reloadDatabase(SQLiteOpenHelper helper, Set<String> tables){
   // Replace the SQLiteOpenHelper
   synchronized (databaseLock) {
      readableDatabase = null;
      writeableDatabase = null;
      this.helper.close();
      this.helper = helper;
   }
   // Make all Queries to reload
   sendTableTrigger(tables);
}

Or prehaps BriteDatabase can save a Set of tables, filled as we query them, to be notified on a potential database reload?

I guess I could make the user restart the app, but I rather not.

SQLBrite is amazing as it is very simple and allow us to get rid of loaders, and IMO notifying on tables is far better than the Uri system, thanks for creating SQLBrite!

Re-query only

Let's say my app is a calendar, I have an upcoming event list, starting by today's events.
In every event list I want to mark each yesterday's event as missed.

Then I need to reload event's Queries every day at 00:00.
In this particular case it would make sense to make the sendTableTrigger() public and call it every day.

I could also use a Observable.combineLatest with a Query and a PublishSubject and call this Subject every day to run the Query again, but since table triggers are managed the same way, it seems simple to switch sendTableTrigger() to public.

To support this idea; ContentResolver's notifyChange(Uri uri, null) is public.

Hi,
first of all, thanks for this great library.

I was wondering if you are still working on #2 and #1 because I have started my own approach by introducing a DAO layer: https://github.com/sockeqwe/sqlbrite-dao
The idea is to have a DAO base class that already provides a SQL Builder like discussed in #2 with pure sql language support and additionally a little annotation processor to map the data from a cursor to real object. Please note that it is not a ORM nor provides it's own query language for resolving relations. The goal is to write pure sql queries (you don't have to learn another library specific query language) and reduce writing boilerplate code for iterating over a Cursor (this is done by annotation processing).

But before I put more effort into this project, I wanted to ask you if you already work on your own solution for that?

If you are interested in my solution:

There is a DaoManger which extends from SQLiteOpenHelper and kind of represents a database. Internally it instantiates SqlBriteinstance which gets "injected" to the registered DAOs. A Dao represents a database table.

public class CustomerDao extends Dao {

  @Override public void createTable(SQLiteDatabase database) {
    CREATE_TABLE(
                 Customer.TABLE_NAME, 
                 Customer.COL_ID + " INTEGER PRIMARY KEY NOT NULL",
                 Customer.COL_FIRSTNAME + " TEXT", 
                 Customer.COL_LASTNAME + " TEXT")
    .execute(database);
  }

  @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  }

  public Observable<List<Customer>> getCustomers() {
    return query(

               SELECT(Customer.COL_ID, Customer.COL_FIRSTNAME, Customer.COL_LASTNAME)
               .FROM(Customer.TABLE_NAME)

           ).map(new Func1<SqlBrite.Query, List<Customer>>() {
                    @Override public List<Customer> call(SqlBrite.Query query) {

                          Cursor c = query.run();
                          List<Customer> customers = CustomerMapper.list(c); // Annotation Processing
                          return customers;

                      }
           });
  }

  public Observable<Long> insert(int id, String firstname, String lastname) {

    ContentValues values = new ContentValues();
    values.put(Customer.COL_ID, id);
    values.put(Customer.COL_FIRSTNAME, firstname);
    values.put(Customer.COL_LASTNAME, lastname);

    return insert(Customer.TABLE_NAME, values);
  }
}

To repeat my question: Do you work on or have plans to provide a first party solution by your own?

Namely, Observable<Query>. This will allow downstream tooling (i.e. #1) to operate on both transparently. There's also room for sharing work on #3 since the underlying mechanism could be shared (ContentValues).

This has two effects we desire:

• It dispatches triggers to downstream consumers asynchronously in relation to the call to commit the transaction (at least from the view of those downstream operators). This avoids all kinds of problematic situations in very large applications.
• It normalizes the emission thread to be completely independent of subscription thread. Right now subscribeOn only controls emission of the initial value. Subsequent values are emitted on the trigger thread, which application controlled and thus undefined.

Could there be some investigation and perhaps implementation of a mechanic to allow for switching between Android's default SQLite engine or one issued by Requery?

@JakeWharton have you given this any thought?

Hi,
I see in your DbOpenHelper, you overrode the onUpgrade() method. However, you aren't handling this use case. I am wondering if it would be prudent to have something like this:
db.execSQL("DROP TABLE IF EXISTS " + TodoList.TABLE); onCreate(db);

Thanks,
Igor

This would open a rabbit hole of Rx calls on the QueryObservable. But trying to take(1) one Query or skip(1) is a bit of a pain at the moment.

Doing something like take(1).mapToOne() would be really handy. But not sure where you would draw the line at which methods to override.

I guess an alternative for take(1).mapToOne() would be to enable a query(String query, String... args) where no table is passed indicating you just want to run the query once and not observe the table.

For many situations getting the initial data is exactly what you want. However, there are other situations where you only want to be notified when the content changed.

For instance, when an SMS is sent or received. I don't want an immediate notification for that because that SMS may have just been sent or sent a long time ago.

It seems the startWith operator is what provides this functionality

https://github.com/square/sqlbrite/blob/master/sqlbrite/src/main/java/com/squareup/sqlbrite/BriteContentResolver.java#L105

For the time being I can use the skip(1) operator to ignore the first result cleanly.

It would be nice if there were another API to skip the immediate notification…

 public QueryObservable createQuery(
        @NonNull final Uri uri, 
        @Nullable final String[] projection,
        @Nullable final String selection, 
        @Nullable final String[] selectionArgs, 
        @Nullable final String sortOrder, 
        final boolean notifyForDescendents,
        boolean skipFirst)

The existing API could call this one with true passed in as the last argument.

I see that sqlbrite opens transaction using beginTransactionWithListener() which as you may know starts the transaction in EXCLUSIVE mode.
I was wondering why you don't use beginTransactionWithListenerNonExclusive() which makes it possible for read operations to be performed concurrently alongside a write.

I know it should be easy, but I couldn't figure it out on my own.
In my chat app I use SQLBrite wrapper to update chat from local database. Currently my activity holds both UI and SQLBrite. Now I want to extract SQLBrite logic to external class. So while my activity is living I need my extracted SQLBrite also be alive.
Currently I have problem with subscriber. I also extracted him to external class, and its emitting query all the time.
Can I create SqlBrite sqlBrite object in activity and pass it to external class, and do SQL action in that class and listen to changes in Activity class?

this is old code, where everything was in same activity and it was working:

SqlBrite sqlBrite;
sqlBrite = SqlBrite.create(DatabaseManager.DatabaseHelper);
private final CompositeSubscription subscriptions = new CompositeSubscription();

private void saveMessage(Message message) {

        ContentValues values = new ContentValues();
        values.put(DatabaseContract.MessageEntry.COLUMN_NAME_MESSAGE_ID, message.getId());
        values.put(DatabaseContract.MessageEntry.COLUMN_NAME_SERIAL_NUMBER, message.getSerialNumber());
        values.put(DatabaseContract.MessageEntry.COLUMN_NAME_TEXT, message.getText());

        sqlBrite.insert(DatabaseContract.MessageEntry.TABLE_NAME, values, SQLiteDatabase.CONFLICT_IGNORE);
    }

    private void subscribeToDbChanges() {
        String NEW_MSG_QUERY = "SELECT * FROM "
                + DatabaseContract.MessageEntry.TABLE_NAME
                + " WHERE "
                + DatabaseContract.MessageEntry.COLUMN_NAME_CREATED_AT_MILLS
                + " >= ? ORDER BY "
                + DatabaseContract.MessageEntry.COLUMN_NAME_CREATED_AT_MILLS
                + " DESC";

        String grpId = String.valueOf(groupId);
        String msgDate = String.valueOf(dividerMsgDate);

        Observable<Query> freshMessages = sqlBrite.createQuery(DatabaseContract.MessageEntry.TABLE_NAME, NEW_MSG_QUERY, grpId, msgDate);
        subscriptions.add(
                freshMessages.subscribe(query -> parseQueryData(query)));
    }

    private void parseQueryData(Query query) {
        Log.d(TAG, "subscriber called");
        List<Message> messages = new ArrayList<>();
        Cursor c = query.run();
        try {
            while (c.moveToNext()) {
                Message message = new Message();
                message.setLocalId(c.getInt(c.getColumnIndexOrThrow(DatabaseContract.MessageEntry._ID)));
                message.setId(c.getInt(c.getColumnIndexOrThrow(DatabaseContract.MessageEntry.COLUMN_NAME_MESSAGE_ID)));

                messages.add(message);
            }
        } finally {
            c.close();
        }

        updateChatList(messages, "new");
    }

Is it just me, or does SqlBrite not work with Robolectric?

Update the sample to demonstrate with a list containing a few thousand items.

When RxJava 1.1 is released and we update to support it, remove the custom BackpressureBufferLastOperator class in favor of the native onBackpressureLatest() operator.

Hi, thanks for the library.

I was wondering how could I close cursor if I'm using mapToList() operator?
For example, I have a method:

public Observable<List<FilmModel>> getFilms() {
        return mDb.createQuery(DBContract.FilmsTable.TABLE_NAME,
                "SELECT * FROM " + DBContract.FilmsTable.TABLE_NAME)
                .mapToList(new Func1<Cursor, FilmModel>() {
                    @Override
                    public FilmModel call(Cursor cursor) {
                        return DBContract.FilmsTable.parseCursor(cursor);
                    }
                });
    }

Does SQLBite provide any api for closing cursor in this case? Like finallyDo() operator or something like that.

My Setup:

• App that listens to database changes via Loader and updates the UI accordingly
• SyncAdapter, that fills the data via contentenprovider but in a different :sync process.

I wanted to replace the ContentProvider and Loader stuff with SQLBrite. It works fine, but I do not receive the database updates made in the :sync process. I suppose SQLBrite cannot handle this right now?

As a workaround I was thinking about delivering the updates via broadcasts, and call an update in my app process. This seems however like a messy solution, so I was wondering if
a.) SQLBrite does support those inter process update notifications, and I just didn't get it.
b.) A cleaner way to deliver those database update notifications to the app process.

hope, somebody has an idea.

best, MikeT.

Hello,
I keep getting compilation errors:

Error:(42, 25) error: package rx.android.widget does not exist

Please advise,
Igor

Hi,

Although the min sdk specified as 9 in the sqlbrite/src/main/AndroidManifest.xml, the packaged aar contains min sdk 15 in its AndroidManifest.xml. For that reason I am receiving error below

Error:Execution failed for task ':app:processDebugManifest'.
> Manifest merger failed : uses-sdk:minSdkVersion 10 cannot be smaller than version 15 declared in library /Users/gokhanbarisaker/Workspace/Android/MarkaVIP/app/build/intermediates/exploded-aar/com.squareup.sqlbrite/sqlbrite/0.1.0/AndroidManifest.xml
    Suggestion: use tools:overrideLibrary="com.squareup.sqlbrite" to force usage

I added the dependency using

compile 'com.squareup.sqlbrite:sqlbrite:0.1.0'

and I am using jCenter as a maven repository.

I know, I can use tools:overrideLibrary="com.squareup.sqlbrite" option as suggested on the error above. Still it just doesn't feel right, also I believe you will feel the same.

Thanks in advance,

Is support for rxAndroid 1.0.x planned ? There is a lot of (breaking) changes between rxAndroid 0.25.0 and 1.0.1, so we're a bit reluctant to start a new project with the older version ... but then again sqlbrite seems to solve many of our problems

Nobody wants to work with a Query and Cursor. Add support for mapping rows to objects. This should support both table-based and custom view-based objects.

Not entirely sure what's going on, but I'm seeing sporadic crashes with this:

E/AndroidRuntime(19707): Caused by: java.lang.IllegalStateException: more items arrived than were requested
E/AndroidRuntime(19707):     at rx.internal.producers.ProducerArbiter.produced(ProducerArbiter.java:98)
E/AndroidRuntime(19707):     at rx.internal.operators.OperatorConcat$ConcatInnerSubscriber.onNext(OperatorConcat.java:208)
E/AndroidRuntime(19707):     at rx.internal.operators.OperatorFilter$1.onNext(OperatorFilter.java:54)
E/AndroidRuntime(19707):     at rx.subjects.SubjectSubscriptionManager$SubjectObserver.onNext(SubjectSubscriptionManager.java:224)
E/AndroidRuntime(19707):     at rx.subjects.PublishSubject.onNext(PublishSubject.java:114)
E/AndroidRuntime(19707):     at com.squareup.sqlbrite.SqlBrite.sendTableTrigger(SqlBrite.java:153)
E/AndroidRuntime(19707):     at com.squareup.sqlbrite.SqlBrite.access$400(SqlBrite.java:56)
E/AndroidRuntime(19707):     at com.squareup.sqlbrite.SqlBrite$Transaction.onCommit(SqlBrite.java:512)
E/AndroidRuntime(19707):     at android.database.sqlite.SQLiteSession.endTransactionUnchecked(SQLiteSession.java:417)
E/AndroidRuntime(19707):     at android.database.sqlite.SQLiteSession.endTransaction(SQLiteSession.java:401)
E/AndroidRuntime(19707):     at android.database.sqlite.SQLiteDatabase.endTransaction(SQLiteDatabase.java:524)
E/AndroidRuntime(19707):     at com.squareup.sqlbrite.SqlBrite.endTransaction(SqlBrite.java:219)
E/AndroidRuntime(19707):     at com.robinhood.librobinhood.arch.worker.UriWorker.lambda$getSaveAction$159(UriWorker.java:74)
E/AndroidRuntime(19707):     at com.robinhood.librobinhood.arch.worker.UriWorker.access$lambda$0(UriWorker.java)
E/AndroidRuntime(19707):     at com.robinhood.librobinhood.arch.worker.UriWorker$$Lambda$1.call(Unknown Source)
E/AndroidRuntime(19707):     at com.robinhood.librobinhood.arch.BrokebackApiCache.lambda$getCachingObservable$125(BrokebackApiCache.java:70)
E/AndroidRuntime(19707):     at com.robinhood.librobinhood.arch.BrokebackApiCache.access$lambda$1(BrokebackApiCache.java)
E/AndroidRuntime(19707):     at com.robinhood.librobinhood.arch.BrokebackApiCache$$Lambda$2.call(Unknown Source)
E/AndroidRuntime(19707):     at rx.Observable$10.onNext(Observable.java:4238)
E/AndroidRuntime(19707):     at rx.internal.operators.OperatorDoOnEach$1.onNext(OperatorDoOnEach.java:79)
E/AndroidRuntime(19707):     at rx.internal.operators.OperatorDoOnEach$1.onNext(OperatorDoOnEach.java:84)
E/AndroidRuntime(19707):     at rx.subjects.SubjectSubscriptionManager$SubjectObserver.onNext(SubjectSubscriptionManager.java:224)
E/AndroidRuntime(19707):     at rx.internal.operators.NotificationLite.accept(NotificationLite.java:150)
E/AndroidRuntime(19707):     at rx.subjects.ReplaySubject$UnboundedReplayState.accept(ReplaySubject.java:466)
E/AndroidRuntime(19707):     at rx.subjects.ReplaySubject$UnboundedReplayState.replayObserverFromIndex(ReplaySubject.java:515)
E/AndroidRuntime(19707):     at rx.subjects.ReplaySubject$UnboundedReplayState.replayObserver(ReplaySubject.java:503)
E/AndroidRuntime(19707):     at rx.subjects.ReplaySubject.caughtUp(ReplaySubject.java:423)
E/AndroidRuntime(19707):     at rx.subjects.ReplaySubject.onNext(ReplaySubject.java:369)
E/AndroidRuntime(19707):     at rx.Observable$33.onNext(Observable.java:7504)
E/AndroidRuntime(19707):     at rx.observers.SafeSubscriber.onNext(SafeSubscriber.java:130)
E/AndroidRuntime(19707):     at retrofit.RxSupport$2.run(RxSupport.java:56)
E/AndroidRuntime(19707):     at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:422)
E/AndroidRuntime(19707):     at java.util.concurrent.FutureTask.run(FutureTask.java:237)

I'll update with more info as I'm digging in.

As it stands now, a Observable<Query> emits a new query object for every change in the table, even if the corresponding rows of the query haven't changed.

It would be great if means of further filtering the notifications were added to avoid these unnecessary updates (and database queries).

Maybe something like tags for queries and updates/inserts/deletes:

// Tag the query with a string
Observable<Query> users = db.createQuery("users", "tag", "SELECT * FROM users WHERE company=?", "Square");
// This observable would now only be refreshed with database updates that are tagged with "tag"

// Tag an insert with the same tag
db.insert("users", createUser("jw", "Jake Wharton", "Square"), "tag"); // users gets notified

db.insert("users", createUser("lp", "Larry Page", "Google")); // the tag is optional, users wouldn't get notified

Add a method Observable<List<String>> observeTables(String... tables) or similar to BriteDatabase, that notifies observers with a set of affected tables, but does not create any query.

Scenario: We sync cats and dogs stored on our backend, and persist them using a transaction.(pseudo-code, cursor/cv mapping omitted)

try (Transaction transaction = database.newTransaction()) {
    for(Cat cat: cats) {
        database.insert("cat", cat);
    }
    for(Dog dog: dogs) {
        database.insert("dog", dog);
    }
    transaction.markSuccessful();
}

We fetch all cats and dogs when initializing our view. (In real code in a background thread, obviously)

List cats = database.query("SELECT * FROM cat");
List dogs = database.query("SELECT * FROM dog");
catAndDogView.setCatsAndDogs(cats, dogs);

How do I make this reactive?

Observable<List> cats = database.createQuery("cat", "SELECT * FROM cat");
Observable<List> dogs = database.createQuery("dog", "SELECT * FROM dog");
Observable.combineLatest(cats, dogs, (cats, dogs) -> new Object[]{cats, dogs})
    .subscribe(objects -> catAndDogView.setCatsAndDogs(objects[0], objects[1]));

This is flawed, because I'm now getting two updates per transaction. Replacing combineLatest() with zip() won't do neither, because then I'm missing out on updates that involve only cats, or only dogs. A work-around is having each query triggered by changes on either table, but that comes at the expense of running some redundant queries.

As an added benefit of my proposal, having the opportunity to react to table changes myself, would allow me to create DAOs with synchronous methods, and reusing these methods when I want to create reactive versions.

class DAO {
    List<Cat> findBlackCats(BriteDatabase database) {
        return database.query("SELECT * FROM cat WHERE color = black");
    }
}

Observable<List<Cat>> blackCats = database.observeTables("cat")
    .map(ignore -> dao.findBlackCats(database));

My proposal is based on what SquiDB seems to offer.

Is there is a reason that you don't want to called the onCompleted methods?

My app has a data model of a collection of routes that each contain a collection of stops which each contain a collection of payload items. I created a query to load routes, one to load events for a route and one to load payloads for an event. I then wrapped them in RxJava operators to flatmap together in a similar fashion as if this was network data.

 public Observable<List<RouteSQL>> loadRoutes() {
        return db.createQuery(TABLE_NAME, SELECT_ALL)
                .mapToList(MAPPER::map)
                .first()
                .flatMap(Observable::from)
                .flatMap(this::withEvents)
                .cast(RouteSQL.class)
                .toList();
    }

 public Observable<ImmutableRouteSQL> withEvents(RouteSQL route) {
        return loadEvents(route.id())
                .map(((ImmutableRouteSQL) route)::withTransit_events);
    }

    @NonNull
    public Observable<List<TransitEventSQL>> loadEvents(Long routeId) {
        return db.createQuery(TransitEventSQL.TABLE_NAME,
                TransitEventSQL.SELECT_FOR_ROUTE,
                new String[]{String.valueOf(routeId)})
                .mapToList(TransitEventSQL.MAPPER::map)
                .first()
                .flatMap(Observable::from)
                .flatMap(this::eventWithPayload)
                .toList();
    }

 private Observable<ImmutableTransitEventSQL> eventWithPayload(TransitEventSQL event) {
        return loadPayloads(event.id())
                .map(((ImmutableTransitEventSQL) event)::withTransit_event_payloads);
    }

    @NonNull
    private Observable<List<Payload>> loadPayloads(long eventId) {
        return db.createQuery(Payload.TABLE_NAME,
                Payload.SELECT_BY_TRANSIT_ID,
                new String[]{String.valueOf(eventId)})
                .mapToList(Payload.MAPPER::map).first();
    }

The odd piece of code up top is all the first() sprinkled after each mapTo. Without them it seems like I cannot take query for a list, flatmap the list to a stream, then map each entry to another query result and reduce back to a list. The "issue" lies in each query being an endless observable. Am I missing something or is this the proper way to join multiple result sets? Ideally I'd like to be able to flatmap multiple sql brite queries together without having to do the first trick. Thank you for help.

Can someone explain to me the difference between the following?

take(1).lift(Query.mapToOne())

vs

mapToOne().take(1)

Or to put it another way, how does PR #77 address the "empty row" scenario raised in issue #69? My RxJava knowledge is lacking so it seems to me that the two approaches are identical except for the reversed method invocations.

Here's the situation I'm running into:
I've got one table that's especially noisy. All queries to that table are scoped to a particular context_id in the where clause. It's always being written to while knowing that context_id value. Ideally, my query Observable would only emit when the write is for the same context_id. So this means I've got a situation where I know enough about the writes and the reads to reduce unnecessary query Observable emissions, but SqlBrite is making the assumption that I want to trigger for the whole table. We've mitigated the presentation effects of this so far by using throttling, hasStableIds in the RecyclerView destined queries, and other various forms of symptom suppression. But this means we're still performing potentially frequent unnecessary db reads, POJO/cursor allocations, etc.

With the overloaded createQuery method that already exists, I could theoretically create a query with two triggers: the context_id and the table name. This way, if we write in a way that would affect multiple context_ids, I'd still be able to fall back to the existing behavior and trigger the whole table.

Because the triggers in BriteDatabase are just Strings, this doesn't seem to me like too far of a stretch for SqlBrite's behavior. The only hurdles for me right now are a few assumptions being made by BriteDatbase's method signatures that the table and the trigger are the same value.

I see two possible solutions for my particular situation:

1. I could mirror much of the BriteDatabase trigger behavior in a wrapper class and manage my own finer grain triggers.
2. BriteDatabase could support explicit triggers in the insert/update/delete methods.

I'd appreciate anyone's thoughts on if this seems reasonable or if there's another solution altogether. Thanks!

Hi,
in your BriteDatabaseTest, you have a transactionCreatedFromTransactionNotificationWorks(). It says that it

// Tests the case where a transaction is created in the subscriber to a query which gets
// notified as the result of another transaction being committed.

Here is the full code:

@Test public void transactionCreatedFromTransactionNotificationWorks() {
// Tests the case where a transaction is created in the subscriber to a query which gets
// notified as the result of another transaction being committed. With improper ordering, this
// can result in creating a new transaction before the old is committed on the underlying DB.

db.createQuery(TABLE_EMPLOYEE, SELECT_EMPLOYEES)
    .subscribe(new Action1<Query>() {
      @Override public void call(Query query) {
        db.newTransaction().end();
      }
    });

Transaction transaction = db.newTransaction();
try {
  db.insert(TABLE_EMPLOYEE, employee("john", "John Johnson"));
  transaction.markSuccessful();
} finally {
  transaction.end();
}

}

My question is - where does one transaction notify the other transaction here? I am not seeing it...

Thanks,
Igor

Is there a specific reason outlining as to why this library is target towards min SDK level 15 and up? If not, could I push a change for 14 and up instead? With 14 being the lowest end of Android 4, it would be easier assumption that this would then run on all versions of Android, 4 and up.

Quick question:
Is there a way to run a query of an observable manually.

e.g. I have an active subscription for an observable which listens to changes of table A. In my observable the result of the query is modified, so I only have a subset of the queried rows.
This step depends on a parameter that can change randomly and is not in the database, so I need to tell the query to run again once that parameter changes.
Something like notifyDataSetChanged();

Am I doing something weird here?

Currently I just create a new observable with a subscription once the parameter changes, which doesn't feel right.

This is a very simplified version of what I'm doing:

  Observable<List<ListsItem>> lists = db.createQuery(ListsItem.TABLES, ListsItem.QUERY)       
      .map(ListsItem.MAP)
      .replay(1)
      .refCount();
  Subscription subscription = Observable.combineLatest(lists, lists, func2)
      .subscribe();

When using rxjava 1.0.14 it's crashing when calling unsubscribe() (1.0.13 works fine).

Stacktrace

08-21 18:29:49.980  18952-18952/com.example.sqlbrite.todo.development E/AndroidRuntime﹕ FATAL EXCEPTION: main
    Process: com.example.sqlbrite.todo.development, PID: 18952
    java.lang.RuntimeException: Unable to pause activity {com.example.sqlbrite.todo.development/com.example.sqlbrite.todo.ui.MainActivity}: java.lang.IllegalArgumentException: requested -1 < 0
            at android.app.ActivityThread.performPauseActivity(ActivityThread.java:3312)
            at android.app.ActivityThread.performPauseActivity(ActivityThread.java:3259)
            at android.app.ActivityThread.handlePauseActivity(ActivityThread.java:3234)
            at android.app.ActivityThread.access$1000(ActivityThread.java:148)
            at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1303)
            at android.os.Handler.dispatchMessage(Handler.java:102)
            at android.os.Looper.loop(Looper.java:135)
            at android.app.ActivityThread.main(ActivityThread.java:5312)
            at java.lang.reflect.Method.invoke(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:372)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:901)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:696)
     Caused by: java.lang.IllegalArgumentException: requested -1 < 0
            at com.squareup.sqlbrite.BackpressureBufferLastOperator$BufferLastSubscriber$1.request(BackpressureBufferLastOperator.java:52)
            at rx.internal.operators.OperatorReplay$ReplaySubscriber.manageRequests(OperatorReplay.java:521)
            at rx.internal.operators.OperatorReplay$InnerProducer.unsubscribe(OperatorReplay.java:716)
            at rx.internal.util.SubscriptionList.unsubscribeFromAll(SubscriptionList.java:124)
            at rx.internal.util.SubscriptionList.unsubscribe(SubscriptionList.java:113)
            at rx.Subscriber.unsubscribe(Subscriber.java:98)
            at com.example.sqlbrite.todo.ui.ListsFragment.onPause(ListsFragment.java:141)
            at android.support.v4.app.Fragment.performPause(Fragment.java:1950)
            at android.support.v4.app.FragmentManagerImpl.moveToState(FragmentManager.java:1005)
            at android.support.v4.app.FragmentManagerImpl.moveToState(FragmentManager.java:1138)
            at android.support.v4.app.FragmentManagerImpl.moveToState(FragmentManager.java:1120)
            at android.support.v4.app.FragmentManagerImpl.dispatchPause(FragmentManager.java:1943)
            at android.support.v4.app.FragmentActivity.onPause(FragmentActivity.java:393)
            at android.app.Activity.performPause(Activity.java:6064)
            at android.app.Instrumentation.callActivityOnPause(Instrumentation.java:1317)
            at android.app.ActivityThread.performPauseActivity(ActivityThread.java:3292)
            at android.app.ActivityThread.performPauseActivity(ActivityThread.java:3259)
            at android.app.ActivityThread.handlePauseActivity(ActivityThread.java:3234)
            at android.app.ActivityThread.access$1000(ActivityThread.java:148)
            at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1303)
            at android.os.Handler.dispatchMessage(Handler.java:102)
            at android.os.Looper.loop(Looper.java:135)
            at android.app.ActivityThread.main(ActivityThread.java:5312)
            at java.lang.reflect.Method.invoke(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:372)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:901)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:696)

Code to try it in the SqlBrite sample: https://gist.github.com/gabrielittner/e428bd24592f194c518c/revisions

I had a glimpse at the asRows method of the Query abstract class
and it looks to me like :

exceptions are swallowed (no onError calls)
onCompleted gets called even if the observable has been unsubscribed.

This feels weird... Or am I wrong ?

I posted my issue to StackOverflow http://stackoverflow.com/questions/29820730/android-sqlbrite-not-getting-latest-inserted-msg

I use SQLBrite subscriber for database changes. This is my Observable and subscriber:

String NEW_MSG_QUERY = "SELECT * FROM "
                + GrouviDatabaseContract.MessageEntry.TABLE_NAME
                + " WHERE "
                + GrouviDatabaseContract.MessageEntry.COLUMN_NAME_GROUP_ID
                + " LIKE ? AND "
                + GrouviDatabaseContract.MessageEntry.COLUMN_NAME_MESSAGE_ID
                + " >= ? ORDER BY "
                + GrouviDatabaseContract.MessageEntry.COLUMN_NAME_CREATED_AT
                + " DESC";
String grpId = String.valueOf(groupId);
String msgId = String.valueOf(dividerMsgId);
Observable<Query> freshMessages = sqlBrite.createQuery(GrouviDatabaseContract.MessageEntry.TABLE_NAME, NEW_MSG_QUERY, grpId, msgId);
freshMessages.subscribe(new Action1<Query>() {
    @Override
    public void call(Query query) {
        Log.d(TAG, "cursor worked");
        parseQueryData(query);
    }
});

My insert code:

ContentValues values = new ContentValues();
values.put(GrouviDatabaseContract.MessageEntry.COLUMN_NAME_MESSAGE_ID, message.getId());
........
values.put(GrouviDatabaseContract.MessageEntry.COLUMN_NAME_USER_NAME, message.getUserName());
values.put(GrouviDatabaseContract.MessageEntry.COLUMN_NAME_IS_BLOCKED, isBlocked);
sqlBrite.insert(GrouviDatabaseContract.MessageEntry.TABLE_NAME, values);

I successfully insert new items via SQLBrite wrapped SQLiteHelper. Problem is that I am not getting the latest inserted message. I got all messages, but the latest inserted. By logs, everything seems correct. Message is inserted, and then Subscriber is triggered.

Am I doing everything right?