Schema

Building your database seems like a reasonable place to start your Unforget project. There are many options for storing your database. You decide to use MySQL. It is not as hip as some of the newer database systems, but you are secure and perhaps a little bit anti-hip. You install MySQL on your Ubuntu server with these commands:

sudo apt install mysql-server
sudo mysql_secure_installation

The second command hardens your MySQL configuration. You use it to disallow remote logins to your database and disable some unnecessary features.

Once MySQL is up and running, you create a script named create_unforget.sql with these commands:

-- Remove any existing database and user.
DROP DATABASE IF EXISTS unforget;
DROP USER IF EXISTS unforget_user@localhost;

-- Create Unforget database and user. Ensure Unicode is fully supported.
CREATE DATABASE unforget CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
CREATE USER unforget_user@localhost IDENTIFIED WITH mysql_native_password BY 'REDACTED';
GRANT ALL PRIVILEGES ON unforget.* TO unforget_user@localhost;

The last three commands create the database and a user who has full privileges for only this database. Using the root user for ordinary queries is unsafe. You replace the text REDACTED with a real password, of course.

The script is run by the root user in the MySQL interpreter with this shell command:

sudo mysql < create_unforget.sql

The first two commands wipe the database and its user. You only want to run this script during this initial setup or if you aren't concerned about losing any memories.

You have an empty database, which you confirm by loading up the MySQL interpreter:

mysql --user unforget_user --password unforget

The --password option tells the interpreter to prompt you for the unforget_user password. The last command-line argument is the name of the database.

Since there's nothing in the database, you can't do much. You check what tables the database has:

mysql> SHOW TABLES;
Empty set (0.00 sec)

It's time to describe the structure of your database in a schema. Your needs are pretty simple. You want to store a bunch of daily memories. Each memory has these fields:

• The year of the memory.
• The month of the memory.
• The day of the memory.
• The journal entry.

You could use a date type for the first three, but you haven't studied those yet. Three separate integers will work fine. On top of these, you add a few extra fields to follow idiomatic database design:

• An integer identifier to serve as a primary key. The year, month, and day may be enough to uniquely identify a memory, but a single identifier is simpler.
• The time at which a memory was first created.
• The time at which a memory was last edited.
• A deleted flag. You decide to never really delete any memories to prevent accidental loss of precious data. Instead you will soft-delete them by setting this flag to true. Memories that have been soft-deleted are effectively ignored but not permanently removed from the database. You, the database administrator, could reinstate them. Probably for a price.

You piece together the following SQL script in schema.sql:

DROP TABLE IF EXISTS memory;

CREATE TABLE memory (
  id SERIAL PRIMARY KEY,
  year INT,
  month INT,
  day INT,
  entry TEXT,
  is_deleted INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This script is run as unforget_user rather than root with this command:

mysql --user unforget_user --password unforget < schema.sql

As with the earlier script, you only want to run this during the initial setup. The first line wipes away any existing table.

Try running the interactive interpreter again and showing the tables. Do you see the memory table? Try running these commands:

DESCRIBE memory;
SELECT * FROM memory;
INSERT INTO memory(year, month, day, entry) VALUES (2015, 4, 10, "I got a pet dog and named him Licky.");
SELECT * FROM memory;

Try inserting another memory. Try selecting a specific one using its id in a WHERE clause. Try deleting one.