We have now greatly improved the user experience for our reminders lists by separating the private lists from the shared ones. To accomplish this we did something kind of incredible We joined our SQL tables to the sync metadata table that exists in a completely different database. That’s right, we are joining across databases.

And we do this so that we can inspect the iCloud metadata for each reminders list, and in particular whether or not that list is shared. The fact that SQLiteData does not hide this metadata from you, and really quite the opposite, makes it very public and even queryable, makes it so easy to implement features like this.

But one not so ideal thing about what we just did is that we literally copied-and-pasted a large query just so that we could query for the private lists and then the shared lists. Luckily for us there are many ways for us to reuse the logic in our queries so that we don’t have to literally copy-and-paste code around. And we’ve shown a number of these techniques in past episodes, but we are going to show off a whole new one that is more appropriate to use here.

There is a concept in SQL known as database “views”, and they allow you to define table-like entities in your database that are secretly backed by a query. This is a powerful tool to share complex logic amongst many queries and also hide the implementation details of the complex query.

Let’s take a look.