Quote Engine

A postgresql database for storage of notable quotes and notes.

Author: Jess Moore

License: MIT

Contents

• Installation
• Commands
• Backup quote database
• Restore from backup
• Initial setup from json file

Installation

Data is persisted on the host in folder ./pgdata (not version controlled).

If the container and ./pgdata are deleted and the repo is recloned, then the method to install the data is to restore the data from db dump file ~/quotes-[LAST_DATE].sql.gz

Start docker container using quote database data in ./pgdata.

docker compose up --detach

Confirm by inspecting database in container using:

docker compose exec -it [POSTGRES_SERVICE_NAME] psql -U [POSTGRES_USER] -d [POSTGRES_DB]
\dt
\d quotes
select author from quotes;
\q

or from within container via Docker Desktop:

psql -U [POSTGRES_USER] -d [POSTGRES_DB]
select author from quotes;

Scripts are installed to $HOME/bin with:

chmod u+x db/quote.sh
chmod u+x db/get_quote.sh
ln -s db/quote.sh ~/bin/quote
ln -s db/get_quote.sh ~/bin/get_quote

Commands

Create new quote record

Add new quote. New quotes are added using quote new ... to create the new quote record and quote update ... [id] to add/update content for a specific quote id.

Adding a new quote, returns the id of the new quote record. A new quote record can be created by adding data to any column of a new record. This returns a new quote record id, for example id 37.

quote new author "Megan Davis"

Add/update the data to other columns of the new quote using

quote update [col] [value] [id]

Eg. to add data to the reference ref and date date columns for new quote id 37:

quote update ref "https://www.themonthly.com.au/september-2025/nation-reviewed/garma-chameleon" 37
quote update date "01/09/2025" 37
quote update quote "This is my quote text" 37

Update quote record

Update data in a quote record by using quote update... to change data in specific columns:

quote update [column] [value] [id]

Get random quote

A random quote can be obtained with:

get_quote

It can be printed including date and reference url with:

get_quote -d -r

I learned from the plants I cared for and the forests I walked. But my greatest teacher was the sea kayak. To be on the water changes your experience... It's ordinary... for your days to be defined by the rhythms of the tides and the whims of the weather, ordinary not to produce or consume but simply to be present. The act of paddling... felt like a return to. the world I loved as a kid. It felt like coming to life again.

--Lauren Fuge (https://www.textpublishing.com.au/books/voyagers-our-journey-into-the-anthropocene, 2024)

Search for quote

Search parameters can be provided to filter the data. The search parameter is matched on a case insensitive contains basis.

To return a random quote which contains the word "biodiversity":

get_quote -q "biodiversity"

To return a random quote by Megan Davis use:

get_quote -a "Megan Davis"

Backup quote database

A database dump in sql.gz format is written to ~/quotes-[TODAY].sql.gz with:

bash ./db/backup_db.sh

Add to crontab to create a regular backup.

Restore from backup

Restore the backup to this test database:

bash db/restore_db.sh ~/quotes-[LAST_DATE].sql.gz

The end of the output shows the number of rows in the table:

 setval
--------
     37

Test the database by opening an interactive terminal with psql and check that there are the correct data and number of records in the backup:

docker compose exec -it [POSTGRES_SERVICE_NAME] psql -U [POSTGRES_USER] [POSTGRES_DB]
select id,author from quotes;
select count(*) from quotes;

Initial setup from json file

This can be used to create a quote db table from scratch using a json file quotes.json. See template file provided.

Template: templates/quotes_template.json

First minify the json file such that the json array is on a single line. This is required for the postgres function json_populate_recordset() to successfully read the json array.

Upload the ~/quotes.json in https://jsonformatter.org/ and click Minify / Compact.

Click Download to download the minified json file to ~/Downloads/jsonformatter.txt.

The quotes table can be created and the json file read into the table using ./initdb folder. Data and scripts in the folder are copied across to the container and run when the container is created. Files run in alpha numerical filename order.

Uncomment this folder in the volumes section of docker-compose.yml.

Copy the minified quotes file to ./initdb/quotes.json:

cp ~/Downloads/jsonformatter.txt initdb/quotes.json

On recreating the container the sql initdb/import_quotes_json.sql will create the quotes table and import json data into the quote table:

docker compose up --detach

Verify that table now contains the number of rows in the json file with:

docker compose exec -it [POSTGRES_SERVICE_NAME] psql -U [POSTGRES_USER] -d [POSTGRES_DB]
\dt
\d quotes
select count(*) from quotes;
\q