Pigment, however, allows pages to be contained in multiple books, so a “release date” is relevant for a page when accessed from a daily book, but not otherwise, meaning that’s not an option in this case. It should be noted that another alternative to storing the data as it comes from the server and calculating the release date of a page based on it’s sort order on each query would be to simply calculate that value when inserting the data. They also understand unix epoch time, as seconds since 1970, so my dataset is usable without even needing to migrate any data.Įven better, you can use modifiers to perform calculations on the values, meaning this logic of figuring out a release date within a month based on the sort order of a page is perfectly possible! These functions understand ISO-8601 style date and time formats, allowing you to store dates and times as human readable strings that retain important information, like time zone offsets, and also retain sortability and comparability. While SQLite may not have a native DateTime data type, it does have a set of datetime functions that make working with time pretty straightforward. More importantly, however, this kind of filtering of datasets is exactly what SQL is designed to do! Whatever efficiency gains I thought I was getting by only filtering page lists for the current month instead of for every book could probably be even greater by pushing this filtering logic down into the query to begin with. On review it’s clear that this is a rather complex piece of code, but is also easily unit testable, so I only felt a little bad about that. Here is a snippet of the data returned by Pigment’s API:Ĭlass Converters ![]() ![]() To support daily pages, each daily book is assigned a month, and the app will “release” the pages in order for each day that month. To accomplish this, we embed the release information into the books in our Room database. That way we only have to download the metadata for a month’s book once, and the user can still access the dailies even without a network connection. This dynamic behavior of only displaying the currently released pages, and dynamically setting the premium flag, all happens on device. ![]() Users can unlock a page forever by simply starting a coloring project with it while it’s free, but there is a limited window in which it’s available. The day’s page is free only for the day (for non-subscribers), and then reverts to being locked once the next page is released. Part of Pigment’s large library of coloring books and pages are the Daily books, which are a different book each month in which a new page is released each day. As I’ve been refactoring a fairly large section of Pigment, my coloring book app for adults, I came across some SQL code which I haven’t touched in a very long time and didn’t seem quite right.
0 Comments
Leave a Reply. |