342 lines
18 KiB
HTML
342 lines
18 KiB
HTML
|
<!DOCTYPE html>
|
|||
|
|
<html>
|
|||
|
|
<head>
|
|||
|
|
<meta charset="utf-8">
|
|||
|
|
<meta name="generator" content="pandoc">
|
|||
|
|
<meta name="viewport" content="width=device-width, initial-scale=1.0, user-scalable=yes">
|
|||
|
|
<meta name="author" content="Dave Morriss">
|
|||
|
|
<title>What’s for dinner? (HPR Show 3386)</title>
|
|||
|
|
<style type="text/css">code{white-space: pre;}</style>
|
|||
|
|
<!--[if lt IE 9]>
|
|||
|
|
<script src="http://html5shim.googlecode.com/svn/trunk/html5.js"></script>
|
|||
|
|
<![endif]-->
|
|||
|
|
<link rel="stylesheet" href="http://hackerpublicradio.org/css/hpr.css">
|
|||
|
|
<style>
|
|||
|
|
blockquote {
|
|||
|
|
font-style: italic;
|
|||
|
|
}
|
|||
|
|
table {
|
|||
|
|
width:100%;
|
|||
|
|
}
|
|||
|
|
table, th, td {
|
|||
|
|
border-collapse: collapse;
|
|||
|
|
padding: 2px;
|
|||
|
|
text-align: left;
|
|||
|
|
}
|
|||
|
|
table#t001, table#t001 th, table#t001 td {
|
|||
|
|
border: 1px solid black;
|
|||
|
|
}
|
|||
|
|
table#t001 th, table#t001 td {
|
|||
|
|
padding: 5px;
|
|||
|
|
text-align: left;
|
|||
|
|
}
|
|||
|
|
table#t001 tr:nth-child(even) {
|
|||
|
|
background-color: #eee;
|
|||
|
|
}
|
|||
|
|
table#t001 tr:nth-child(odd) {
|
|||
|
|
background-color:#fff;
|
|||
|
|
}
|
|||
|
|
table#t001 td:first-child {
|
|||
|
|
background-color: #4D4D4D;
|
|||
|
|
color: white;
|
|||
|
|
}
|
|||
|
|
table#t001 th {
|
|||
|
|
background-color: #4D4D4D;
|
|||
|
|
color: white;
|
|||
|
|
}
|
|||
|
|
</style>
|
|||
|
|
</head>
|
|||
|
|
|
|||
|
|
<body id="home">
|
|||
|
|
<div id="container" class="shadow">
|
|||
|
|
<header>
|
|||
|
|
<h1 class="title">What’s for dinner? (HPR Show 3386)</h1>
|
|||
|
|
<h2 class="subtitle">Some scripts and a database for randomly choosing which meal to cook</h2>
|
|||
|
|
<h2 class="author">Dave Morriss</h2>
|
|||
|
|
<hr/>
|
|||
|
|
</header>
|
|||
|
|
|
|||
|
|
<main id="maincontent">
|
|||
|
|
<article>
|
|||
|
|
<header>
|
|||
|
|
<h1>Table of Contents</h1>
|
|||
|
|
<nav id="TOC">
|
|||
|
|
<ul>
|
|||
|
|
<li><a href="#overview">Overview</a></li>
|
|||
|
|
<li><a href="#database">Database</a>
|
|||
|
|
<ul>
|
|||
|
|
<li><a href="#data-stored-per-meal">Data stored per meal</a></li>
|
|||
|
|
<li><a href="#meal-log">Meal log</a></li>
|
|||
|
|
</ul></li>
|
|||
|
|
<li><a href="#scripts">Scripts</a>
|
|||
|
|
<ul>
|
|||
|
|
<li><a href="#script-overviews">Script overviews</a>
|
|||
|
|
<ul>
|
|||
|
|
<li><a href="#manage_meal"><code>manage_meal</code></a></li>
|
|||
|
|
<li><a href="#choose_meal"><code>choose_meal</code></a></li>
|
|||
|
|
</ul></li>
|
|||
|
|
</ul></li>
|
|||
|
|
<li><a href="#installation">Installation</a></li>
|
|||
|
|
<li><a href="#managing-the-database">Managing the database</a></li>
|
|||
|
|
<li><a href="#making-backups">Making backups</a></li>
|
|||
|
|
<li><a href="#whats-next-for-this-system">What’s next for this system?</a>
|
|||
|
|
<ul>
|
|||
|
|
<li><a href="#possible-to-do-list">Possible ‘To Do’ list</a></li>
|
|||
|
|
</ul></li>
|
|||
|
|
<li><a href="#links">Links</a></li>
|
|||
|
|
</ul>
|
|||
|
|
</nav>
|
|||
|
|
</header>
|
|||
|
|
<h2 id="overview">Overview</h2>
|
|||
|
|
<p>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.</p>
|
|||
|
|
<p>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 <code>choose_meal</code>, 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!</p>
|
|||
|
|
<p>It wasn’t long before <code>choose_meal</code> was rewritten in <a href="https://www.perl.org/" title="Perl scripting language">Perl</a>. This time I decided to use a database, and chose <a href="https://www.sqlite.org/index.html"><code>SQLite</code></a> to create it. My database contained just two tables, one for the meals themselves (called slightly confusingly <code>'meal_history'</code>), and another for a record of the choices made (called <code>'meal_log'</code>) – the ability to produce historical reports seemed like a desirable feature!</p>
|
|||
|
|
<p>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.</p>
|
|||
|
|
<p>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 <a href="https://gitlab.com/davmo/weekly_menus" title="Weekly Menus repository">GitLab repository</a> and the preparation of this show was begun.</p>
|
|||
|
|
<p>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.</p>
|
|||
|
|
<p>I might rethink this in the future however.</p>
|
|||
|
|
<h2 id="database">Database</h2>
|
|||
|
|
<p><small> <b>Note</b>: 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! </small></p>
|
|||
|
|
<h3 id="data-stored-per-meal">Data stored per meal</h3>
|
|||
|
|
<p>The overall design of the database became a little more complicated as time went on. The data held for a given meal became:</p>
|
|||
|
|
<table style="width:80%;margin:1em auto" id="t001">
|
|||
|
|
<caption>
|
|||
|
|
<em>meal_history</em>
|
|||
|
|
</caption>
|
|||
|
|
<tr>
|
|||
|
|
<th>
|
|||
|
|
Item
|
|||
|
|
</th>
|
|||
|
|
<th>
|
|||
|
|
Description
|
|||
|
|
</th>
|
|||
|
|
</tr>
|
|||
|
|
<tr>
|
|||
|
|
<td>
|
|||
|
|
Id
|
|||
|
|
</td>
|
|||
|
|
<td>
|
|||
|
|
A unique numeric key for this meal
|
|||
|
|
</td>
|
|||
|
|
</tr>
|
|||
|
|
<tr>
|
|||
|
|
<td>
|
|||
|
|
Name
|
|||
|
|
</td>
|
|||
|
|
<td>
|
|||
|
|
The unique name for the meal
|
|||
|
|
</td>
|
|||
|
|
</tr>
|
|||
|
|
<tr>
|
|||
|
|
<td>
|
|||
|
|
Minimum delay
|
|||
|
|
</td>
|
|||
|
|
<td>
|
|||
|
|
The minimum number of days between occurrences of the meal
|
|||
|
|
</td>
|
|||
|
|
</tr>
|
|||
|
|
<tr>
|
|||
|
|
<td>
|
|||
|
|
Last eaten
|
|||
|
|
</td>
|
|||
|
|
<td>
|
|||
|
|
The date this meal was last eaten (might be blank if it’s a new addition)
|
|||
|
|
</td>
|
|||
|
|
</tr>
|
|||
|
|
<tr>
|
|||
|
|
<td>
|
|||
|
|
Enabled
|
|||
|
|
</td>
|
|||
|
|
<td>
|
|||
|
|
A yes/no setting (0/1 actually) indicating whether the meal is available for choice. We sometimes give certain meals a “rest” for example.
|
|||
|
|
</td>
|
|||
|
|
</tr>
|
|||
|
|
<tr>
|
|||
|
|
<td>
|
|||
|
|
Notes
|
|||
|
|
</td>
|
|||
|
|
<td>
|
|||
|
|
General notes about the meal. Contents up to the user.
|
|||
|
|
</td>
|
|||
|
|
</tr>
|
|||
|
|
</table>
|
|||
|
|
<p>The delay setting was added to prevent the same meal being chosen repeatedly week after week; to ensure reasonable variety.</p>
|
|||
|
|
<p>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 <em>placeholder</em> where we want to try a particular type of meal but haven’t yet hit on the best recipe.</p>
|
|||
|
|
<p>The notes tend to be used to suggest amendments to a recipe, or to record feedback on a particular choice.</p>
|
|||
|
|
<h3 id="meal-log">Meal log</h3>
|
|||
|
|
<p>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.</p>
|
|||
|
|
<p>The main items stored in the table are:</p>
|
|||
|
|
<table style="width:80%;margin:1em auto" id="t001">
|
|||
|
|
<caption>
|
|||
|
|
<em>meal_log</em>
|
|||
|
|
</caption>
|
|||
|
|
<tr>
|
|||
|
|
<th>
|
|||
|
|
Item
|
|||
|
|
</th>
|
|||
|
|
<th>
|
|||
|
|
Description
|
|||
|
|
</th>
|
|||
|
|
</tr>
|
|||
|
|
<tr>
|
|||
|
|
<td>
|
|||
|
|
Id
|
|||
|
|
</td>
|
|||
|
|
<td>
|
|||
|
|
A unique numeric key for this log entry
|
|||
|
|
</td>
|
|||
|
|
</tr>
|
|||
|
|
<tr>
|
|||
|
|
<td>
|
|||
|
|
Meal id
|
|||
|
|
</td>
|
|||
|
|
<td>
|
|||
|
|
Link to the meal table
|
|||
|
|
</td>
|
|||
|
|
</tr>
|
|||
|
|
<tr>
|
|||
|
|
<td>
|
|||
|
|
Date of entry
|
|||
|
|
</td>
|
|||
|
|
<td>
|
|||
|
|
The date the log entry was written
|
|||
|
|
</td>
|
|||
|
|
</tr>
|
|||
|
|
<tr>
|
|||
|
|
<td>
|
|||
|
|
Minimum delay
|
|||
|
|
</td>
|
|||
|
|
<td>
|
|||
|
|
The minimum number of days at the time the meal was chosen
|
|||
|
|
</td>
|
|||
|
|
</tr>
|
|||
|
|
<tr>
|
|||
|
|
<td>
|
|||
|
|
Previously eaten
|
|||
|
|
</td>
|
|||
|
|
<td>
|
|||
|
|
The date this meal was previously eaten
|
|||
|
|
</td>
|
|||
|
|
</tr>
|
|||
|
|
<tr>
|
|||
|
|
<td>
|
|||
|
|
Last eaten
|
|||
|
|
</td>
|
|||
|
|
<td>
|
|||
|
|
The date this meal was last eaten (might be blank if it’s a new addition)
|
|||
|
|
</td>
|
|||
|
|
</tr>
|
|||
|
|
</table>
|
|||
|
|
<p>For the record, the log table is written to using a <em>trigger</em>, a database feature that performs a task when something changes in a table.</p>
|
|||
|
|
<h2 id="scripts">Scripts</h2>
|
|||
|
|
<p>There are two scripts as part of this system:</p>
|
|||
|
|
<ul>
|
|||
|
|
<li><code>choose_meal</code> – makes a random choice of a single meal from the database</li>
|
|||
|
|
<li><code>manage_meal</code> – allows management of meal entries within the database and can generate reports on the state of the database</li>
|
|||
|
|
</ul>
|
|||
|
|
<p>The scripts are both written in Perl and contain embedded documentation. Calling either of these scripts with the option <code>-help</code> will give a brief summary of how to use it. More in depth information can be obtained by using the <code>-manpage</code> option. Alternatively the documentation in these scripts can be viewed by typing the following in the directory where the scripts are held:</p>
|
|||
|
|
<pre><code>perldoc choose_meal
|
|||
|
|
OR
|
|||
|
|
perldoc manage_meal</code></pre>
|
|||
|
|
<p>The scripts and various other files are to be found in a <a href="https://gitlab.com/davmo/weekly_menus" title="Weekly Menus repository">Git repository on GitLab</a>. 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 <a href="#installation">installation</a> section in these notes.</p>
|
|||
|
|
<h3 id="script-overviews">Script overviews</h3>
|
|||
|
|
<p>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!</p>
|
|||
|
|
<h4 id="manage_meal"><code>manage_meal</code></h4>
|
|||
|
|
<p>The database can be populated with <code>manage_meal</code> using the <code>-add</code> option.</p>
|
|||
|
|
<p>Meals can be added in a disabled state and can be enabled (made available for choice) once you are ready to use the database.</p>
|
|||
|
|
<p>You can see what’s in the database by using the <code>-summary</code> option. Individual meals can be examined with the <code>-list</code> option and the notes viewed by adding <code>-full</code> to that.</p>
|
|||
|
|
<p>The <a href="https://gitlab.com/davmo/weekly_menus" title="Weekly Menus repository">repository</a> contains further examples of the use of this script as well as details of the options it accepts.</p>
|
|||
|
|
<h4 id="choose_meal"><code>choose_meal</code></h4>
|
|||
|
|
<p>With the database populated this script can be run to make a random choice among the meals. I tend to run <code>choose_meal</code> with the <code>-verbose</code> and <code>-dry-run</code> options initially, because that makes it report what meals it is choosing from, but does not save the choice.</p>
|
|||
|
|
<p>By default <code>choose_meal</code> makes a choice for the current date, but can be used to choose for a future date by using the <code>-date=DATE</code> option, as in:</p>
|
|||
|
|
<pre><code>./choose_meal -date=2021-07-19</code></pre>
|
|||
|
|
<p>If you want to make a choice for a future day of the week, like <em>next Friday</em> then it’s possible to use a command substitution containing a call to the GNU <code>date</code> command, as in:</p>
|
|||
|
|
<pre><code>./choose_meal -date=$(date -d 'next Friday' +%F)</code></pre>
|
|||
|
|
<p>An alternative, when the same day is required each week, is to set up the configuration file (see below).</p>
|
|||
|
|
<p>If a date is provided then it overrides the configuration file setting.</p>
|
|||
|
|
<h5 id="configuration-file">Configuration file</h5>
|
|||
|
|
<p>This file is called <code>.choose_meal.yml</code> 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 <code>-config=FILE</code> option.</p>
|
|||
|
|
<p>The file is written in YAML format, so the first line must consist of three dashes (hyphens). The <code>weekday</code> 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 <code>'weekday'</code> must begin in column 1 and end with a colon (<code>':'</code>). The keywords <code>'name'</code> and <code>'language'</code> must be indented by two spaces, and each must end with a colon followed by the day name or language name respectively:</p>
|
|||
|
|
<pre><code>---
|
|||
|
|
weekday:
|
|||
|
|
name: Wednesday
|
|||
|
|
language: English</code></pre>
|
|||
|
|
<p>The repository has a file called <code>.choose_meal.yml_example</code> as an example of how to prepare this file.</p>
|
|||
|
|
<p>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:</p>
|
|||
|
|
<pre><code>{"weekday":{"name":"Wednesday","language":"English"}}</code></pre>
|
|||
|
|
<h2 id="installation">Installation</h2>
|
|||
|
|
<p>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!</p>
|
|||
|
|
<p>I tested this on a Raspberry Pi running Raspberry Pi OS and found I needed to install the following packages:</p>
|
|||
|
|
<pre><code>$ sudo apt install git sqlite3 perl-doc</code></pre>
|
|||
|
|
<p>To get a copy of the code from the repository I created a directory called <code>Git</code> and used the <code>git</code> command to clone the repository:</p>
|
|||
|
|
<pre><code>$ mkdir Git
|
|||
|
|
$ cd Git
|
|||
|
|
$ git clone git@gitlab.com:davmo/weekly_menus.git
|
|||
|
|
|
|||
|
|
</code></pre>
|
|||
|
|
<p>You can keep your version of the software up to date with the repository, if it changes, by running the following <code>git</code> command from the same directory:</p>
|
|||
|
|
<pre><code>$ cd ~/Git/weekly_menus
|
|||
|
|
$ git pull</code></pre>
|
|||
|
|
<p>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 <code>Weekly_menus</code> and copied the relevant files into it:</p>
|
|||
|
|
<pre><code>$ cd
|
|||
|
|
$ mkdir Weekly_menus
|
|||
|
|
$ rsync -vaP --exclude=.git ~/Git/weekly_menus/ ~/Weekly_menus/</code></pre>
|
|||
|
|
<p>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 <code>App::cpanminus</code> to do this which I installed as follows. On the Pi using user <code>pi</code> I wasn’t prompted for a password when using <code>sudo</code>, but you may be. The password required will be your login password. This assumes you have <code>curl</code> installed, which was the case for me:</p>
|
|||
|
|
<pre><code>$ curl -L https://cpanmin.us | perl - --sudo App::cpanminus</code></pre>
|
|||
|
|
<p>Having acquired <code>cpanminus</code> (the command you need, provided by the module) you can collect the remaining Perl modules as follows:</p>
|
|||
|
|
<pre><code>$ cd Weekly_menus
|
|||
|
|
$ cat modules_needed | xargs cpanm -S</code></pre>
|
|||
|
|
<p>This uses the file <code>modules_needed</code>, which is part of the repository. It contains a list of the required modules, and these are fed into <code>xargs</code> which sends them to <code>cpanminus</code> running in <code>sudo</code> 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.</p>
|
|||
|
|
<p>Running this at a later date will ensure that all modules are current and will usually take far less time to run.</p>
|
|||
|
|
<p>Finally, you need to create the database, which can be achieved as shown below. It causes <code>sqlite3</code> to create a database file and populate it using the SQL in <code>meals_db.sql</code>:</p>
|
|||
|
|
<pre><code>$ sqlite3 meals.db < meals_db.sql</code></pre>
|
|||
|
|
<h2 id="managing-the-database">Managing the database</h2>
|
|||
|
|
<p>Use <code>manage_meal</code> to add meals to the database.</p>
|
|||
|
|
<pre><code>$ ./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'</code></pre>
|
|||
|
|
<p>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.</p>
|
|||
|
|
<p>If this seems to be an issue it may be possible to rethink this in future.</p>
|
|||
|
|
<h2 id="making-backups">Making backups</h2>
|
|||
|
|
<p>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 <code>cron</code> called <code>cronjob_bup</code>. This needs to be set up to be called at some particular time of day. Use the command <code>crontab -e</code> to edit your cron settings. I run it at 21:55 nightly with the following line in the <code>crontab</code> file:</p>
|
|||
|
|
<pre><code>55 21 * * * $HOME/Weekly_menus/cronjob_bup
|
|||
|
|
|
|||
|
|
</code></pre>
|
|||
|
|
<p>This specifies the path to the script and tells <code>cron</code> to run it daily at 21:55.</p>
|
|||
|
|
<p>The <code>cronjob_bup</code> script will create a directory <code>~/Weekly_menus/backup</code> and will dump <code>meals.db</code> into it with a name like: <code>meals_db_20210702_215501.sql.bz2</code>. This is a SQL file which is compressed with the <code>bzip2</code> utility. It can be used to restore the database with a command such as the following:</p>
|
|||
|
|
<pre><code>sqlite3 meals.db < <(bzcat backup/meals_db_20210702_215501.sql.bz2)
|
|||
|
|
|
|||
|
|
</code></pre>
|
|||
|
|
<p>This will restore the database structure and data.</p>
|
|||
|
|
<p>The <code>cronjob_bup</code> script will delete backups older than 140 days (approximately 5 months).</p>
|
|||
|
|
<p>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.</p>
|
|||
|
|
<h2 id="whats-next-for-this-system">What’s next for this system?</h2>
|
|||
|
|
<p>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.</p>
|
|||
|
|
<p>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.</p>
|
|||
|
|
<h3 id="possible-to-do-list">Possible ‘To Do’ list</h3>
|
|||
|
|
<ol type="1">
|
|||
|
|
<li>Add a means of including or linking to recipes</li>
|
|||
|
|
<li>Tidy up option processing (which is a bit messy at present)</li>
|
|||
|
|
</ol>
|
|||
|
|
<h2 id="links">Links</h2>
|
|||
|
|
<ul>
|
|||
|
|
<li><p><a href="https://www.perl.org/">Perl</a></p>
|
|||
|
|
<ul>
|
|||
|
|
<li><a href="https://www.perl.org/get.html">Perl 5</a>: (currently v5.35.0)</li>
|
|||
|
|
<li><a href="https://www.raku.org/">Raku</a>: originally called Perl 6, a totally different scripting language.</li>
|
|||
|
|
<li><a href="https://www.perl.com/article/announcing-perl-7/">Perl 7</a>: updates to Perl 5, removal of some historical stuff</li>
|
|||
|
|
</ul></li>
|
|||
|
|
<li><p><a href="https://www.sqlite.org/index.html">SQLite</a>: a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine.</p></li>
|
|||
|
|
<li><p><a href="https://gitlab.com/davmo/weekly_menus">GitLab repository for <em>Weekly Menus</em></a></p></li>
|
|||
|
|
</ul>
|
|||
|
|
</article>
|
|||
|
|
</main>
|
|||
|
|
</div>
|
|||
|
|
</body>
|
|||
|
|
</html>
|