Consider combining efforts with Yahoo's SquiDB

[sbosley]

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.

[JakeWharton]

Prior to making SqlBrite, we researched about 16 existing solutions for databases on Android. It was clear that there was a range of quality and that some would have sufficed, but none were optimal and held true to the core principles we believe a library should.

Over 6 months we wrote a comprehensive solution that looked similar to SquiDB and others. There was a query language, there was declarative table schemas, there were objects for representing a row, there was code generation, there was a gradle plugin, etc.

After this effort, and prior to open sourcing, we came to realize that what we had was just a minor improvement on what was already out there and had lost our core principles. We threw away all the work and wrote what you see here. There was no effort made to improve the Sqlite APIs, hide the SQL itself, or provide any type safety because we wanted to focus on the RxJava-based foundation.

It has query builders to avoid string concatenation

The builders that you have are exactly like those that we created. They're also very similar to other libraries offerings. They're a very obvious and logic choice for Java, and we dislike them.

The only SQL abstraction that I can really tolerate is jOOQ. That's mostly because it's not much of an abstraction and more of a combinatorial-code-generation-explosion of wrapper classes that lets you write type-safe SQL in the least-painful way possible in Java. Unfortunately, it generates far too much code to be useful without heavy ProGuarding (something we don't do).

The point here is, we strongly believe that hiding SQL is a bad idea. The high-level query APIs are nice when all you need is a List<Foo> and some simple where conditionals. These APIs run into problems whenever you need to do complex SQL or it simply generates inefficient SQL because its API knows no better way to express the underlying query.

SQL is everywhere and there's a lot of knowledge to be learned about it. If we ever provide an API helper, it will embrace the SQL language so that rather than learning how to query in Library A's API you are querying in SQL–potentially with helpers that simplify its creation.

It uses model objects for typesafe inserts, updates, and reading data from cursor in a typesafe way

The property concept you have is very much similar to what we had before (a recurring theme!) and also that of jOOQ. The properties propagate a column's type into the API and can prevent you from setting a boolean on an int column (which of course, sqlite will happily read and write).

A big requirement for us is immutability which your model classes seem to be missing. This is further evidenced by the non-thread safe warning–something that we value highly in our RxJava usage moves threads quite a bit. Immutability is strange to think about when you are updating or deleting though. But that leads me to my next point...

We view insertion, updating, and reading as distinct operations that don't need to share an object.

Insertion almost always is the result of pulling data from another source, in another format. Be it a JSON schema, protocol buffer (our case), fields in the UI, or wherever, the data is almost always not in the exact form of your row and certainly even more rarely already an instance of a mapping object for it. For insertion we are going to focus solely on type-safety of data to a column (you can see this in the sample) which not only avoids the needless object allocation for every row, but also makes things like inserting into a subset of columns much more clear.

With immutable objects insertion would be tough to do on the object. Since you almost always are doing a subset of columns and potential want to leverage things like where clauses we again go back to the same pattern for insertion.

Reading is the immutable objects mentioned above. One big thing we want is to make little distinction between row objects for a single table and row objects for a projection of a query's columns. There's no reason you shouldn't be able to get instances of the latter in a type-safe way. Because we are immutable, there's the potential to even expose final fields to save on methods.

Model objects are generated using an annotation processor

Again something we had and chose to eliminate for the initial release. There's already a PR to bring one back for type-safe insertion and I suspect we would do something similar for row-based object mappings.

I think we're on the same page here: no one wants reflection and code generation ends up being a lot simpler in the end. We also toyed with a Gradle plugin that generated code from a tighter DSL which eliminated the need to define all the interface methods or to reference generated code directly.

We have a custom mechanism for listening for data changes that uses Uris and ContentObservers

Our original version was built on this mechanism, but we found it lacking. A lot of data does map to the hierarchical structure of Uris but it can also be very limiting. We also found it very difficult to create a cohesive RxJava notification system on this structure because of the completely opaque nature of these notifications whereas our system despite not being granular in the public API allows for much more granular control in the underlying mechanism.

We did recently merge a feature to listen and thus trigger re-query on content providers provided a Uri via the content observer mechanism though. This is very handy for listening to other data sources but treating it as the same mechanism as a local DB.

Hopefully we'll build on our mechanism a lot more in the coming months to prove out its usefulness over the normal uri/content observer system.

---

I appreciate you reaching out. I certainly intend to keep an eye on the library (as I do the others for DB access) but I don't think we're headed quite in the same direction. Often times our choices reduces widespread applicability to users which yours might be afforded, but the trade-offs will hopefully create something more refined albeit in a much narrower scope of use.

[sbosley]

Fair enough. I agree with you on a lot of points, particularly that hiding SQL is bad--our Query object is meant to be a 1:1 mapping to SQL and support the entire SQLite grammar; anything we discover that doesn't conform to that is considered to be an API bug. I'll have to check out jOOQ. And while we didn't go the way of immutable objects for various reasons, I do understand your point there as well.

Thanks for taking a look and for taking the time to write such a detailed response!

[fourlastor]

I just saw this and I agree with your points. I have a question tho:

What if Yahoo split the project in SqiDB and the query builder (which I suppose at some point generates SQL)? Would it make sense to embrace the Query object as a possible input for SqlBrite#createQuery and SqlBrite#query? That could solve #2 without hindering the vision of the project.

[sbosley]

Hi @fourlastor, thanks for the suggestion! I'm actually not sure that this would be a good direction for either project though. The main reason is that SquiDB's query builder is designed to work pretty closely with SquiDB model objects. While it's technically possible to use Query without model objects, the API is much less friendly. By way of example:

// SquiDB can't do this
query.where("my_table.foo = 'bar'");

// SquiDB works like this instead. 
// The api is harder to use when a model is not defined
query.where(MyModel.FOO.eq("bar"));

I don't think we want to split the SquiDB query builders into a separate project that doesn't rely on the model objects, and I don't think it would be good for SqlBrite to pull something that requires this model object requirement that's not part of their overall vision. That being said, I can see a couple options:

• If the main thing you care about is the RxJava support, you should probably continue to use SqlBrite. SquiDB may add similar features in the future, but there's no immediate timeline for it. If you really want to use the SquiDB query builders, I suppose you could depend on both projects. You'd still have to define model objects for your tables with SquiDB, but you wouldn't have to use them extensively. You could still manage all your DB operations through SqlBrite, and just use the models to construct Query objects, which you can then call compile() on to get a SQL string suitable for SqlBrite.createQuery.
• If you don't specifically need RxJava support, but just don't like the mutability of SquiDB model objects, you could use SquiDB but ignore the model objects when reading DB data. SquiDB makes it pretty easy to work directly with the returned Cursor if you prefer not to use model objects.
• SquiDB is Apache licensed, so you'd also be free to fork it and pull out/modify the Query class in whatever way fits your needs best.

Hope that helps!

[fourlastor]

Thanks for answering!

I'm currently quite happy with SqlBrite, the only real issue I have is writing the query, which gets quite ugly especially with joins (and aliases, I learned to name fields prepending the table name :P).

I could give SquiDB Query a try as soon as I start working on some pet project tho :).