Devlog: Support for multiple recipe databases

This is a log of stuff I did w/ one of my random pet projects, it’s not terribly interesting and is mostly just so I can get into the habit of writing more. You have been warned.

It took a while but I finally found some time to work on mealplanner again. I wanted to do one sooner, but the latest change took a bit longer to complete, and like every other adult on this planet I’ve got limited free time and energy on my hands.

Anyway, this latest change is a pretty big milestone I think, one of the more complicated and important bits of work before an initial working app.

I said before (I think) that I wanted to support having multiple separate recipe databases that are queried like one big one. This makes importing whole recipe databases and removing them far simpler and more efficient than if we had to merge those recipes into a single local database. Especially since imported recipe databases are not mutable, you’ll never add your own recipe to a collection of recipes sourced elsewhere.

But, it does make searching through the database a bit more complicated. We have to perform a single query, with sorting and pagination, across multiple databases, which isn’t really a normal thing to do (so it’s there’s no easy out of the box solution).

I looked at two possible solutions here (apart from using a database other than SQLite). The first was SQLite’s ATTACH command. It’s possible w/ SQLite to open multiple databases within a single connection. Then we could UNION the different tables and search within them (though the efficiency of this approach is questionable at best).

However, I wanted a better search than just checking whether a recipe contains a given search term via LIKE (and a faster one). I was thinking before of using SQLite’s FTS (Full Text Search) capabilities to provide more useful search functionalities, and it turns out it also provided a way to solve my multiple database querying issue. Well, sort of.

To use FTS you have to create a FTS table whose data is sourced from another place, like one or more other tables. This FTS table is then queried directly. So that’s what I did, I added an FTS table to the primary database that acts as an index for recipes in all databases. This meant having one query I can use to sort and search through every recipe in every database.

I had to manually add the data to the new table, when adding a new recipe or importing a recipe database, but that seems to be the only way to use the FTS feature, so it didn’t end up being too much more work to support multiple databases. I added the FTS index for every recipe, added code to fill the index when importing a recipe database, and got the existing tests to work.

Only interesting problem that came up was a weird little bug in SQLite. On database import mealplanner ran this SQL to populate the FTS table (mind the variables):

ATTACH DATABASE '${db.relative_path}' AS secondary;

INSERT INTO recipes_index
SELECT r.idrecipe AS idrecipe,
       ${db?.iddatabase || 0} AS iddatabase,
       r.name AS name,
       (r.subtitle || ' ' || r.description || ' ' || (
         SELECT GROUP_CONCAT(tips.tip, ' ')
           FROM secondary.recipe_tips AS tips
          WHERE idrecipe = r.idrecipe
       GROUP BY idrecipe
       ) || ' ' || (
         SELECT GROUP_CONCAT(steps.long_desc, ' ')
           FROM secondary.recipe_steps AS steps
          WHERE idrecipe = r.idrecipe
       GROUP BY idrecipe
       )) AS recipe_text,
       (
         SELECT GROUP_CONCAT(ing.name, ' ')
           FROM secondary.recipe_ingredients AS r_ing
      LEFT JOIN secondary.ingredients ing ON ing.idingredient = r_ing.idingredient
          WHERE idrecipe = r.idrecipe
       GROUP BY idrecipe
       ) AS ingredients
  FROM secondary.recipes AS r

It works, except when a recipe has no steps or tips. In this case, recipe_text ends up being set to an empty string. Seems like SQLite has a bug where, if an inner SELECT in a string concatenation expression results in an empty result set, the entire concatenation is just set to ''. I managed to fix this with yet another inner select:

INSERT INTO recipes_index
SELECT r.idrecipe AS idrecipe,
       ${db?.iddatabase || 0} AS iddatabase,
       r.name AS name,
       (r.subtitle || ' ' || r.description || ' ' || (
         SELECT GROUP_CONCAT(tips.tip, ' ')
           FROM (
             SELECT tip, idrecipe FROM secondary.recipe_tips UNION SELECT ' ' AS tip, r.idrecipe AS idrecipe
           ) AS tips
          WHERE idrecipe = r.idrecipe
       GROUP BY idrecipe
       ) || ' ' || (
         SELECT GROUP_CONCAT(steps.long_desc, ' ')
           FROM (
             SELECT long_desc, idrecipe FROM secondary.recipe_steps
             UNION
             SELECT ' ' AS long_desc, r.idrecipe AS idrecipe
           ) AS steps
          WHERE idrecipe = r.idrecipe
       GROUP BY idrecipe
       )) AS recipe_text,
       (
         SELECT GROUP_CONCAT(ing.name, ' ')
           FROM secondary.recipe_ingredients AS r_ing
      LEFT JOIN secondary.ingredients ing ON ing.idingredient = r_ing.idingredient
          WHERE idrecipe = r.idrecipe
       GROUP BY idrecipe
       ) AS ingredients
  FROM secondary.recipes AS r

Strange how the more complicated expression works perfectly.

Next time, I’m gonna hook it all up to the UI and hopefully have an actual screenshot to share :)