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.
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:
|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.
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:
|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.
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.
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!
The database can be populated with
manage_meal using the
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.
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
-dry-run options initially, because that makes it report what meals it is choosing from, but does not save the choice.
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:
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.
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
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
'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:
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 firstname.lastname@example.org: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
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
$ sqlite3 meals.db < meals_db.sql
Managing the database
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.
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
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
55 21 * * * $HOME/Weekly_menus/cronjob_bup
This specifies the path to the script and tells
cron to run it daily at 21:55.
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.
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
- Add a means of including or linking to recipes
- Tidy up option processing (which is a bit messy at present)
SQLite: a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine.