What’s for dinner? (HPR Show 3386)

Some scripts and a database for randomly choosing which meal to cook

Dave Morriss


Table of Contents

Overview

I live on my own, but I cook for members of my family from time to time. Each week we all get together and cook dinner for Wednesday and Thursday. I usually do the cooking but we are starting to share these duties for certain meals.

In 2019 I thought it would be useful if I had some sort of random chooser to decide what next week’s meal was going to be. I wrote a Bash script called choose_meal, using a simple CSV file of meal names and the date last eaten to avoid choosing the same one too often. The shortcomings of this approach soon became apparent!

It wasn’t long before choose_meal was rewritten in Perl. This time I decided to use a database, and chose SQLite to create it. My database contained just two tables, one for the meals themselves (called slightly confusingly 'meal_history'), and another for a record of the choices made (called 'meal_log') – the ability to produce historical reports seemed like a desirable feature!

In 2019 the design of this system was very specific to our needs: one choice per week on a Wednesday. It was not something that could be used by anyone else – which seemed like a bad idea.

In late 2020 and early 2021 the system was redesigned, as will be discussed in the detailed notes. In May 2021 a more general design was added to the public GitLab repository and the preparation of this show was begun.

I had never intended this system to hold recipes. This was partly because I have built a collection of recipes I have constructed from various sources and amended as I have made them. I print these and keep them in a ring-binder for reference as I cook. In some cases the meals described in the database are multi-component ones (such as the dishes that make up a curry for example), so it doesn’t seem appropriate to hold these here.

I might rethink this in the future however.

Database

Note: I was a bit confused by the names and usages of these tables when recording the audio. I guess this goes to show that my name choices were bad!

Data stored per meal

The overall design of the database became a little more complicated as time went on. The data held for a given meal became:

meal_history
Item Description
Id A unique numeric key for this meal
Name The unique name for the meal
Minimum delay The minimum number of days between occurrences of the meal
Last eaten The date this meal was last eaten (might be blank if it’s a new addition)
Enabled A yes/no setting (0/1 actually) indicating whether the meal is available for choice. We sometimes give certain meals a “rest” for example.
Notes General notes about the meal. Contents up to the user.

The delay setting was added to prevent the same meal being chosen repeatedly week after week; to ensure reasonable variety.

Having the capability to disable a meal entry was useful, perhaps because we were bored with it, or because it was seasonal. It’s also been a useful to way to add a placeholder where we want to try a particular type of meal but haven’t yet hit on the best recipe.

The notes tend to be used to suggest amendments to a recipe, or to record feedback on a particular choice.

Meal log

As mentioned earlier, keeping history of previous choices is quite interesting (to me anyway). The database holds a log table which is written to every time a choice is made. This means we can compute how many times a particular meal has been chosen and can look back to see what was chosen when. It’s by no means vital to the functioning of the system though.

The main items stored in the table are:

meal_log
Item Description
Id A unique numeric key for this log entry
Meal id Link to the meal table
Date of entry The date the log entry was written
Minimum delay The minimum number of days at the time the meal was chosen
Previously eaten The date this meal was previously eaten
Last eaten The date this meal was last eaten (might be blank if it’s a new addition)

For the record, the log table is written to using a trigger, a database feature that performs a task when something changes in a table.

Scripts

There are two scripts as part of this system:

  • choose_meal – makes a random choice of a single meal from the database
  • manage_meal – allows management of meal entries within the database and can generate reports on the state of the database

The scripts are both written in Perl and contain embedded documentation. Calling either of these scripts with the option -help will give a brief summary of how to use it. More in depth information can be obtained by using the -manpage option. Alternatively the documentation in these scripts can be viewed by typing the following in the directory where the scripts are held:

perldoc choose_meal
OR
perldoc manage_meal

The scripts and various other files are to be found in a Git repository on GitLab. It is intended that this could be cloned to the system on which it is to be run and then installed in a directory of choice. The repository contains documentation on how this can be done, and there is an installation section in these notes.

Script overviews

To be prepared for use, a database needs to be created and populated with some meals. The number of these depends on how often you plan to choose meals and what delay you set for the meals. You want to ensure that there are meals eligible to be chosen whenever you make a random choice!

manage_meal

The database can be populated with manage_meal using the -add option.

Meals can be added in a disabled state and can be enabled (made available for choice) once you are ready to use the database.

You can see what’s in the database by using the -summary option. Individual meals can be examined with the -list option and the notes viewed by adding -full to that.

The repository contains further examples of the use of this script as well as details of the options it accepts.

choose_meal

With the database populated this script can be run to make a random choice among the meals. I tend to run choose_meal with the -verbose and -dry-run options initially, because that makes it report what meals it is choosing from, but does not save the choice.

By default choose_meal makes a choice for the current date, but can be used to choose for a future date by using the -date=DATE option, as in:

./choose_meal -date=2021-07-19

If you want to make a choice for a future day of the week, like next Friday then it’s possible to use a command substitution containing a call to the GNU date command, as in:

./choose_meal -date=$(date -d 'next Friday' +%F)

An alternative, when the same day is required each week, is to set up the configuration file (see below).

If a date is provided then it overrides the configuration file setting.

Configuration file

This file is called .choose_meal.yml by default and is expected to be in the same directory as the script. Alternative names may be used but need to be provided with the -config=FILE option.

The file is written in YAML format, so the first line must consist of three dashes (hyphens). The weekday setting defines the name of the day of the week that will be selected, and may be provided with a language specification if desired. The word 'weekday' must begin in column 1 and end with a colon (':'). The keywords 'name' and 'language' must be indented by two spaces, and each must end with a colon followed by the day name or language name respectively:

---
weekday:
  name: Wednesday
  language: English

The repository has a file called .choose_meal.yml_example as an example of how to prepare this file.

Note that YAML is related to JSON in structure. If it helps to understand the structure, the example YAML shown above can be represented in JSON form as follows:

{"weekday":{"name":"Wednesday","language":"English"}}

Installation

This process can be somewhat involved. If you’re unlikely to want to use this application then you might be wise to skip this section!

I tested this on a Raspberry Pi running Raspberry Pi OS and found I needed to install the following packages:

$ sudo apt install git sqlite3 perl-doc

To get a copy of the code from the repository I created a directory called Git and used the git command to clone the repository:

$ mkdir Git
$ cd Git
$ git clone git@gitlab.com:davmo/weekly_menus.git

You can keep your version of the software up to date with the repository, if it changes, by running the following git command from the same directory:

$ cd ~/Git/weekly_menus
$ git pull

You could use the clone of the repository to hold your database, but I don’t recommend it. I made another top-level directory called Weekly_menus and copied the relevant files into it:

$ cd
$ mkdir Weekly_menus
$ rsync -vaP --exclude=.git ~/Git/weekly_menus/ ~/Weekly_menus/

The scripts will require a number of Perl modules which you will have to install if you don’t already have them. I used a Perl tool called App::cpanminus to do this which I installed as follows. On the Pi using user pi I wasn’t prompted for a password when using sudo, but you may be. The password required will be your login password. This assumes you have curl installed, which was the case for me:

$ curl -L https://cpanmin.us | perl - --sudo App::cpanminus

Having acquired cpanminus (the command you need, provided by the module) you can collect the remaining Perl modules as follows:

$ cd Weekly_menus
$ cat modules_needed | xargs cpanm -S

This uses the file modules_needed, which is part of the repository. It contains a list of the required modules, and these are fed into xargs which sends them to cpanminus running in sudo mode. This can take a while to complete since many of the modules have dependencies. Testing this on a Raspberry Pi, 77 modules were installed in total.

Running this at a later date will ensure that all modules are current and will usually take far less time to run.

Finally, you need to create the database, which can be achieved as shown below. It causes sqlite3 to create a database file and populate it using the SQL in meals_db.sql:

$ sqlite3 meals.db < meals_db.sql

Managing the database

Use manage_meal to add meals to the database.

$ ./manage_meal -add
Enter details of the new meal:
Name:          Haggis, Neeps & Tatties
Minimum delay: 28
Enabled:       y
Last eaten:
Notes:         McSween's Vegetarian Haggis is pretty good
Added new meal: 'Haggis, Neeps & Tatties'

In the current release meals can be added and edited, enabled and disabled, but not deleted. This is because the log might contain references to older meals and deleting them would break the history.

If this seems to be an issue it may be possible to rethink this in future.

Making backups

It’s a good idea to make backups of the database in case you delete it or mess it up in some way. Included in this system is a script to be run out of cron called cronjob_bup. This needs to be set up to be called at some particular time of day. Use the command crontab -e to edit your cron settings. I run it at 21:55 nightly with the following line in the crontab file:

55 21 * * * $HOME/Weekly_menus/cronjob_bup

This specifies the path to the script and tells cron to run it daily at 21:55.

The cronjob_bup script will create a directory ~/Weekly_menus/backup and will dump meals.db into it with a name like: meals_db_20210702_215501.sql.bz2. This is a SQL file which is compressed with the bzip2 utility. It can be used to restore the database with a command such as the following:

sqlite3 meals.db < <(bzcat backup/meals_db_20210702_215501.sql.bz2)

This will restore the database structure and data.

The cronjob_bup script will delete backups older than 140 days (approximately 5 months).

Making backups to a sub-directory is better than nothing, but if the disk is lost then everything will be lost! I actually run a daily backup task on my main workstation which writes changed files to an external disk. This is a bit better than just saving a local copy, but not good for really vital stuff.

What’s next for this system?

We use this system with the database and associated scripts on a weekly basis. It does all that we want it to do at present. I say “we” but I am the sole user (and developer) at the moment.

Perhaps speaking about it on HPR and releasing it to the world via GitLab will be of use to others. If so, I will be pleased and will welcome feedback.

Possible ‘To Do’ list

  1. Add a means of including or linking to recipes
  2. Tidy up option processing (which is a bit messy at present)
  • Perl

    • Perl 5: (currently v5.35.0)
    • Raku: originally called Perl 6, a totally different scripting language.
    • Perl 7: updates to Perl 5, removal of some historical stuff
  • SQLite: a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine.

  • GitLab repository for Weekly Menus