About Quotations

This project involves developing a simple Web Application that displays quotations based on context such as date, event, or degree of relevance. An example context would be to display a quotation by a famous historical personality on his/her birthday. Another one, to display a quotation relevant to a current event. A last one, where date or event relevance is not available, have the relevance be based on keywords association.

Quotations are often a powerful way to convey meaning, inspiration, and emotion in just a few words. The effect can be even more powerful when the quotation is associated with a specific context. From a development standpoint, a tool can be easily created to uncover these associations. In addition, since quotations and information about the authors who inspired those quotations is readily available online, much of the data can be easily mined using Web automation tools.

Components

This project is still evolving but my initial specification will include the following components:

Description

As mentioned, in the interest of making this a simple Web Application, below are my initial use cases:

  1. The home page by default displays the most relevant quote associated with the current date along with a brief bio about the author (if no date-specific relevance is found, an association derived from keyword relevance is made). This is our detail view. An example date-relevant quotation and bio might be the one below if the current date is October 2nd:

“The future depends on what we do in the present.” - Mahatma Gandhi

Mohandas Karamchand Gandhi, born on this day 1869, was an Indian lawyer, anti-colonial nationalist, and political ethicist, who employed nonviolent resistance to lead the successful campaign for India’s independence from British Rule, and in turn inspire movements for civil rights and freedom across the world (source: extract from Wikipedia)

  1. Refreshing the home page displays the next-most relevant quote (or, if none available, one based on keyword relevance). Still on detail view, an example keyword-relevant quotation using the current month might be the one below if a specific-date context is not available:

“As full of spirit as the month of May, and as gorgeous as the sun in Midsummer” - William Shakespeare

William Shakespeare (bapt. 26 April 1564 – 23 April 1616) was an English poet, playwright, and actor, widely regarded as the greatest writer in the English language and the world’s greatest dramatist. He is often called England’s national poet and the “Bard of Avon” (or simply “the Bard”). His extant works, including collaborations, consist of some 39 plays, 154 sonnets, two long narrative poems, and a few other verses, some of uncertain authorship. His plays have been translated into every major living language and are performed more often than those of any other playwright (source: extract from Wikipedia)

  1. A search field allows a user to enter a search term and return a list (ordered by relevance) of quotations associated with that term. The number returned can be specified from a selection widget (i.e., in increments of 5 or 10). This is our list view that shows only the quotation and author associated with each returned item along with a link to the detail view (that includes the author bio as showbn above)

That’s it for use cases (at least for now)

Data Elements and Schema

  1. The Starting point is cataloging a series of Events and their associated Dates and/or Seasons (from the event Author and/or Keywords relations we will find the most relevant quotation(s))
  2. The Quotations and Authors (as well as any Source associated with each quotation) are of course stored. As we discussed, an author has certain attributes associated with him/her such as Birth/Death Date and Bio information. The quotation might also have one or more Keywords associated with it which can tie it to events or user initiated queries.
  3. Finally, we want to keep track of when the quotation was last shown and thus prevent it from being re-displayed when user refreshes the browser (unless it is included in the returned list of a user initiated search)

Our first draft of the schema or Entity-Relation Diagram (ERD) might look like the one below:

Quotations ERD

In a real query we may get zero or more events associated with a date (in the case of zero, we might pull in event(s) by associating month or season to today’s date). With multiple events, we might give precedence to those events tied to an author as oppposed to just a keyword (though even a single author might have multiple quotations so we would need to decide which one to display)

In the ideal world, a tie breaker might best be broken by pulling in some currently trending topics (perhaps something for version 2.0 of this app!)

Web Automation/Filtering Tools and Data

I have added some Web automation scraper scripts along with the filter and loader scripts under my GitHub Perl repository (see bin/quotes). These will be referenced in the sections that follow. The scripts also use the supporting Util packages under the lib/Util directory and reference configuration files added under the conf/quotes directory.

Web Scrape the Quotes

The purpose of the scrape tools is to pull raw data when possible. Much of the data “cleanup” will be done manually. This will include deciding which quotations and events to include as well as which keywords to associate.

I have created the Web automation scrape_quotes.pl script to pull a few thousand quotations/authors from one or more sites as well as an optional source (for this site, usually a book reference) and keywords. A short extract from the raw data generated is shown below:

You've gotta dance like there's nobody watching###William W. Purkey######dance,heaven,hurt,inspirational,life,love,sing
You know you're in love when you can't fall asleep because reality is finally better than your dreams.###Dr. Seuss######attributed-no-source,dreams,love,reality,sleep
A friend is someone who knows all about you and still loves you.###Elbert Hubbard######friend,friendship,knowledge,love
Darkness cannot drive out darkness: only light can do that. Hate cannot drive out hate: only love can do that.###Martin Luther King Jr.###A Testament of Hope: The Essential Writings and Speeches###darkness,drive-out,hate,inspirational,light,love,peace
We accept the love we think we deserve.###Stephen Chbosky###The Perks of Being a Wallflower###inspirational,love

The script is run with the required command-line option ‘–config’ which points to a scrape site specific configuration file. An example site template is checked into the conf/quotes directory of the perl-scripts repository. For any sites I visit, at least for now, will not plan on using any available feeds (i.e., RSS) to extract data.

Note: Make sure that extraction of content does not infringe on copyrights (especially if used for commercial purposes)

Create the Initial List of Authors

Since the script can potentially retrieve tens of thousands of quotes (of which well over 99% are of no value to me) I decided to perform an intial filtering by author. For this manual exercise I arbitrarily came up with 100 authors (with a few added later) spanning multiple nationalities and occupations. The criteria for picking these authors was that they are (or were) masters in their endevours or occupations, in at least some aspect of their life were worthy of my admiration, and had attributed to them two or more quotations that were inspiring or thought provoking.

Associate Additional Author Information

As I produced this list, I also associated the additional fields birth date, death date (if deceased), title/short description and a link (i.e., Wikipedia) to the bio. To automate this scrape, I implemented the script scrape_authors_info.pl that used as input a scrape site specific configuration file (scrape_authors_info.site.template and the list of authors and output the new list authors_info with additional information. The script correctly found the bio and generated the url for all authors in the list (using Wikipedia) but had incomplete and/or erroneous data in 26/100 scrapes (much due to inconsistent formatting of the Wikipedia markup and dates content). Rather than make the script more generalizable and handle at least most of the remaining cases, I decided (at least for now) to fix these manually and add them to a separate file (authors_info_manual). The filter script below can then takes as input a catenated file containing both lists.

Create the Initial List of Events

The initial list of events (at least to be used during the testing phase) was also produced manually and saved to the events.txt file and includes author name (for now just events tied to an author), date of the event, event name, and keywords associated with author/event.

Filter the Quotes and tie them to the Authors/Events

The filter_quotes.pl script creates an intermediate data structure based on the authors information and keyed by the lowercased/dash-separated/stripped full name with the Events and Quotes subsequently associated with the author.

Pretty printed below is the reference output for Albert Einstein generated using the –debug flag (the actual output used as input to the loader, which is shown in the next section, is JSON formatted). A command-line option allows applying a filter by quote size (for this run any quote over 200 characters got tossed). In addition, the quote, keywords, and source are validated for characters outside an ASCII range (i.e., octal \040-\176) which rules out some, but not all, foreign sets. While there a few quotes of interest in this example, they are certainly not Einstein’s best so clearly an admin API will be needed to easily add, update, or remove quotes and other data.

          'albert-einstein' => {
                                 'bio_url' => 'https://en.wikipedia.org/wiki/Albert_Einstein',
                                 'birth_date' => '1879-03-14',
                                 'description' => 'German-born physicist and developer of the theory of relativity (1879-1955)',
                                 'lname_sig' => 'einstein',
                                 'events' => [
                                               {
                                                 'event_date' => '1922-11-09',
                                                 'event' => 'Albert Einstein was awarded the 1921 Nobel Prize in Physics "for his services to Theoretical Physics, 
and especially for his discovery of the law of the photoelectric effect."',
                                                 'keywords' => 'albert-einstein,nobel-physics-prize'
                                               }
                                             ],
                                 'death_date' => '1955-04-18',
                                 'quotes' => [
                                               {
                                                 'quote' => 'I know not with what weapons World War III will be fought, but World War IV will be fought with sticks
 and stones.',
                                                 'keywords' => 'albert-einstein,future,war,wisdom',
                                                 'author' => 'Albert Einstein'
                                               },
                                               {
                                                 'author' => 'Albert Einstein',
                                                 'source' => 'Ideas and Opinions',
                                                 'quote' => 'Student is not a container you have to fill but a torch you have to light up.',
                                                 'keywords' => 'albert-einstein,quote'
                                               },
                                               {
                                                 'keywords' => 'albert-einstein,einstein,inspirational,loner',
                                                 'quote' => 'Although I am a typical loner in my daily life, my awareness of belonging to the invisible community o
f those who strive for truth, beauty, and justice has prevented me from feelings of isolation.',
                                                 'author' => 'Albert Einstein'
                                               },
                                               {
                                                 'keywords' => 'albert-einstein,truth',
                                                 'quote' => 'Anyone who doesn\'t take truth seriously in small matters cannot be trusted in large ones either.',
                                                 'author' => 'Albert Einstein'
                                               },
                                               {
                                                 'author' => 'Albert Einstein',
                                                 'quote' => 'Weak people revenge. Strong people forgive. Intelligent people ignore.',
                                                 'keywords' => 'advice,albert-einstein'
                                               },
                                               {
                                                 'author' => 'Albert Einstein',
                                                 'keywords' => 'albert-einstein',
                                                 'quote' => 'Ethical axioms are found and tested not very differently from the axioms of science. Truth is what sta
nds the test of experience.'
                                               },
                                               {
                                                 'author' => 'Albert Einstein',
                                                 'quote' => 'In the midst of every crisis, lies great opportunity.',
                                                 'keywords' => 'albert-einstein,crisis,opportunity'
                                               },
                                               {
                                                 'author' => 'Albert Einstein',
                                                 'keywords' => 'albert-einstein,stupidity,wired-for-war',
                                                 'quote' => 'Never attribute to malice that which can be adequately explained by stupidity, but don\'t rule out malice.'
                                               },
                                               {
                                                 'author' => 'Albert Einstein',
                                                 'quote' => 'Racism is a disease of white people',
                                                 'keywords' => 'albert-einstein,civil-rights-movement,famous,racism,segregation'
                                               },
                                               {
                                                 'author' => 'Albert Einstein',
                                                 'quote' => 'The most aggravating thing about the younger generation is that I no longer belong to it.',
                                                 'keywords' => 'aggravating,albert-einstein,generation,life,young,youth'
                                               },
                                               {
                                                 'author' => 'Albert Einstein',
                                                 'quote' => 'Kada sedite sat vremena kraj lepe devojke, to prodje kao minut. Sedite minut na vrelu pec i to ce trajati kao sat. To se zove relativitet.',
                                                 'keywords' => 'albert-einstein,relativity,relativity-theory'
                                               },
                                               {
                                                 'author' => 'Albert Einstein',
                                                 'keywords' => 'albert-einstein,philosophy,quantum-physics',
                                                 'quote' => 'That deep emotional conviction of the presence of a superior reasoning power, which is revealed in the incomprehensible universe, forms my idea of God.'
                                               },
                                               {
                                                 'author' => 'Albert Einstein',
                                                 'quote' => 'Aku takut suatu hari teknologi akan melampaui interaksi manusia. Dunia akan memiliki generasi idiot',
                                                 'keywords' => 'albert-einstein'
                                               },
                                               {
                                                 'keywords' => 'austerity,curie,experimental-science,fortune,friendship,grandeur,intuition,marie-curie,nobel-laureate,purity,science,scientist,strength',
                                                 'quote' => 'It was my good fortune to be linked with',
                                                 'source' => 'Out of My Later Years: The Scientist, Philosopher, and Man Portrayed Through His Own Words',
                                                 'author' => 'Albert Einstein'
                                               }
                                             ],
                                 'name' => 'Albert Einstein'
                               },

Alternatively, the filtering script can output the author keywords and then have the quote extraction script apply these directly when searching the site(s) (instead of returning and then filtering a broader search keyed by topic)

Web Framework

As mentioned, the Web Framework used will be Django which is implemented with Python. The application will be running on a Mac OS (Python v3.7.6 and Django v3.0)

Since the setup, at least initially, is not geared to a heavy-weight and/or production environment I will skip the installation of Apache and mod_wsgi as suggested in the installation instructions.

Similarly, I will use SQLite as the database which already comes bundled with Django and should be adequately support our schema design and processing demands. Since SQLite does not effectively deal with concurrency, switching to a more powerfull database such as MySQL, MariaDB or PostgreSQL is recommended if application is made externally facing.

Installing Django

After installing pip you can run the following command (output shown below):

python3 -m pip install django
Collecting django
  Downloading Django-3.0.7-py3-none-any.whl (7.5 MB)
     |████████████████████████████████| 7.5 MB 4.3 MB/s 
Collecting pytz
  Downloading pytz-2020.1-py2.py3-none-any.whl (510 kB)
     |████████████████████████████████| 510 kB 3.7 MB/s 
Collecting asgiref~=3.2
  Downloading asgiref-3.2.7-py2.py3-none-any.whl (19 kB)
Collecting sqlparse>=0.2.2
  Downloading sqlparse-0.3.1-py2.py3-none-any.whl (40 kB)
     |████████████████████████████████| 40 kB 4.3 MB/s 
Installing collected packages: pytz, asgiref, sqlparse, django
Successfully installed asgiref-3.2.7 django-3.0.7 pytz-2020.1 sqlparse-0.3.1

After the installation we can always confirm the version by running:

python3 -m django --version
3.0.7

Creating the Initial Project and App Structure

Using the django-admin utility allows to easily create our initial project structure. From our root git directory run:

django-admin startproject quotations .

In addition to the manage,py (more on this later), the sub-directory quotations, which is the actual Python package for the project, will be created. Using the handy tree command (run brew install tree to install) we can examine the generated files under this directory which we will configure as the application evolves:

tree quotations/
quotations/
├── __init__.py
├── asgi.py
├── settings.py
├── urls.py
└── wsgi.py

We can now test, from the git root directory, that the basic project works by running:

python3 manage.py runserver

If startup goes well you should get the below message:

June 08, 2020 - 21:03:44
Django version 3.0.7, using settings 'quotations.settings'
Starting development server at http://127.0.0.1:8000/
Quit the server with CONTROL-C

Going to that url you should see a standard Django welcome page (in my case “The install worked successfully! Congratulations!”). To use a port other than the default one, you can run the same command with the target port at the end.

Now we will run (from the root directory) the command to create the specific app which we will name myquotes and which will contain our query/display code:

python3 manage.py startapp myquotes

Using our tree command, we verify the app structure:

myquotes/
├── __init__.py
├── admin.py
├── apps.py
├── migrations
│   └── __init__.py
├── models.py
├── tests.py
└── views.py

Finally, before moving on to the models, lets test auto-generating a simple index file and verify that it works (like the standard Django tutorials suggest)

Create a myquotes/urls.py file and add the below code:

from django.urls import path

from . import views

urlpatterns = [
    path('', views.index, name='index'),
]

In the myquotes/views.py add the below content:

from django.http import HttpResponse


def index(request):
    return HttpResponse("This is my test quotations index file.")

To integrate myquotes, in main quotations/urls.py add the below code:

from django.contrib import admin
from django.urls import include, path

urlpatterns = [
    path('myquotes/', include('myquotes.urls')),
    path('admin/', admin.site.urls),
]

Run the application as before (python3 manage.py runserver) and verify the new url rendering at http://127.0.0.1:8000/myquotes with the text you just added.

Setting up the Backend Database and Mapping the ERD to models.py

By default, Django comes bundled with the SQLite database which we will use (at least initially). However, we will need to configure where the database file will be stored. To do this, edit the quotations/settings.py file to include the below new DATA_DIR references (the data/myquotes.sqlite3 file, initially empty, is checked into the git repository)

BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
DATA_DIR = os.path.join(BASE_DIR, 'data')

...

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(DATA_DIR, 'myquotes.sqlite3'),
    }
}

In this file, you can also change the default ‘UTC’ timezone if needed (in my case, I set TIME_ZONE = ‘US/Eastern’). As we move along, we will make additional changes to this configuration file.

In order to map our ERD created earlier to the database using the built-in Django Object Relation Mapping (ORM) we will edit the file myquotes/models.py and include the classes (that subclass django.db.models.Model) and generally map to the database tables and the class variables, which map to the database fields.

The Models (latest draft) that capture the ERD shown earlier are defined below:

from django.db import models
from django.core.validators import MaxValueValidator, MinValueValidator
import calendar

# The primary key field for each class is auto-generated
#
class Author(models.Model):
    full_name        = models.CharField(max_length=100, unique=True)
    birth_date       = models.DateField(null=True)
    death_date       = models.DateField(null=True)
    description      = models.CharField(max_length=200, null=True)
    bio_extract      = models.CharField(max_length=800, null=True)
    bio_source_url   = models.URLField(null=True)

class Quotation(models.Model):
    quotation        = models.CharField(max_length=200, unique=True)
    source           = models.CharField(max_length=100, null=True)
    author           = models.ForeignKey(Author, on_delete=models.CASCADE)

class Event(models.Model):
    MONTH_CHOICES    = [(str(i), calendar.month_name[i]) for i in range(1,13)]
    SEASON_CHOICES   = [
        ('WINTER', 'Winter'),
        ('SPRING', 'Spring'),
        ('SUMMER', 'Summer'),
        ('FALL',   'Fall'),
    ]

    def current_year():
        return datetime.date.today().year

    def max_value_current_year(value):
        return MaxValueValidator(current_year())(value)

    event            = models.CharField(max_length=100, unique=True)
    day              = models.IntegerField(validators=[MaxValueValidator(31), MinValueValidator(1)], null=True)
    month            = models.CharField(max_length=9, choices=MONTH_CHOICES, default=1, null=True)
    year             = models.IntegerField(validators=[max_value_current_year, MinValueValidator(-1000)], null=True)
    season           = models.CharField(max_length=6, choices=SEASON_CHOICES, null=True)

class Keyword(models.Model):
    keyword          = models.CharField(max_length=50, unique=True)

class QuotationLastShown(models.Model):
    quotation        = models.OneToOneField(Quotation, on_delete=models.CASCADE)
    last_shown_date  = models.DateField()

class QuotationKeyword(models.Model):
    quotation        = models.OneToOneField(Quotation, on_delete=models.CASCADE)
    keyword          = models.ManyToManyField(Keyword)

class EventAuthor(models.Model):
    event            = models.OneToOneField(Event, on_delete=models.CASCADE)
    author           = models.OneToOneField(Author, on_delete=models.CASCADE)

class EventKeyword(models.Model):
    event            = models.ManyToManyField(Event)
    keyword          = models.ManyToManyField(Keyword)

We will now modified the quotations/settings.py to include the ‘myquotes’ app we just defined in the list of INSTALLED_APPS:

INSTALLED_APPS = [
    'myquotes',
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
]

By running the command python3 manage.py makemigrations myquotes we will create the intermediate format for the models that can be mapped to the database entities (this command can be run every time we change the model and provides a means to fix any syntax problems with the models.py). Once successful, you will get the below output and the intermediate structures stored in the latest migration (i.e., myquotes/migrations/0001_initial.py file)

Migrations for 'myquotes':
  myquotes/migrations/0001_initial.py
    - Create model Author
    - Create model Event
    - Create model Keyword
    - Create model Quotation
    - Create model QuotationLastShown
    - Create model QuotationKeyword
    - Create model EventKeyword
    - Create model EventAuthor

You can also view the SQL (shown below) that gets generated and applied to the database by running the command python3 manage.py sqlmigrate myquotes 0001. Notice the additional entities that get auto-generated such as primary keys and indexes.

BEGIN;
--
-- Create model Author
--
CREATE TABLE "myquotes_author" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "full_name" varchar(100) NOT NULL UNIQUE, "birth_date" date NOT NULL, "death_date" date NOT NULL, "bio_extract" varchar(400) NOT NULL, "bio_source_url" varchar(200) NOT NULL);
--
-- Create model Event
--
CREATE TABLE "myquotes_event" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "event" varchar(100) NOT NULL UNIQUE, "day" integer NULL, "month" varchar(9) NULL, "year" integer NULL, "season" varchar(6) NULL);
--
-- Create model Keyword
--
CREATE TABLE "myquotes_keyword" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "keyword" varchar(50) NOT NULL UNIQUE);
--
-- Create model Quotation
--
CREATE TABLE "myquotes_quotation" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "quotation" varchar(200) NOT NULL UNIQUE, "quotation_source" varchar(100) NULL, "author_id_id" integer NOT NULL REFERENCES "myquotes_author" ("id") DEFERRABLE INITIALLY DEFERRED);
--
-- Create model QuotationLastShown
--
CREATE TABLE "myquotes_quotationlastshown" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "last_shown_date" date NOT NULL, "quotation_id_id" integer NOT NULL UNIQUE REFERENCES "myquotes_quotation" ("id") DEFERRABLE INITIALLY DEFERRED);
--
-- Create model QuotationKeyword
--
CREATE TABLE "myquotes_quotationkeyword" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "quotation_id_id" integer NOT NULL UNIQUE REFERENCES "myquotes_quotation" ("id") DEFERRABLE INITIALLY DEFERRED);
CREATE TABLE "myquotes_quotationkeyword_keyword_id" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "quotationkeyword_id" integer NOT NULL REFERENCES "myquotes_quotationkeyword" ("id") DEFERRABLE INITIALLY DEFERRED, "keyword_id" integer NOT NULL REFERENCES "myquotes_keyword" ("id") DEFERRABLE INITIALLY DEFERRED);
--
-- Create model EventKeyword
--
CREATE TABLE "myquotes_eventkeyword" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT);
CREATE TABLE "myquotes_eventkeyword_event_id" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "eventkeyword_id" integer NOT NULL REFERENCES "myquotes_eventkeyword" ("id") DEFERRABLE INITIALLY DEFERRED, "event_id" integer NOT NULL REFERENCES "myquotes_event" ("id") DEFERRABLE INITIALLY DEFERRED);
CREATE TABLE "myquotes_eventkeyword_keyword_id" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "eventkeyword_id" integer NOT NULL REFERENCES "myquotes_eventkeyword" ("id") DEFERRABLE INITIALLY DEFERRED, "keyword_id" integer NOT NULL REFERENCES "myquotes_keyword" ("id") DEFERRABLE INITIALLY DEFERRED);
--
-- Create model EventAuthor
--
CREATE TABLE "myquotes_eventauthor" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "author_id_id" integer NOT NULL UNIQUE REFERENCES "myquotes_author" ("id") DEFERRABLE INITIALLY DEFERRED, "event_id_id" integer NOT NULL UNIQUE REFERENCES "myquotes_event" ("id") DEFERRABLE INITIALLY DEFERRED);
CREATE INDEX "myquotes_quotation_author_id_id_e3ea6829" ON "myquotes_quotation" ("author_id_id");
CREATE UNIQUE INDEX "myquotes_quotationkeyword_keyword_id_quotationkeyword_id_keyword_id_f03b0cc7_uniq" ON "myquotes_quotationkeyword_keyword_id" ("quotationkeyword_id", "keyword_id");
CREATE INDEX "myquotes_quotationkeyword_keyword_id_quotationkeyword_id_171b6464" ON "myquotes_quotationkeyword_keyword_id" ("quotationkeyword_id");
CREATE INDEX "myquotes_quotationkeyword_keyword_id_keyword_id_291df85f" ON "myquotes_quotationkeyword_keyword_id" ("keyword_id");
CREATE UNIQUE INDEX "myquotes_eventkeyword_event_id_eventkeyword_id_event_id_74c7f66e_uniq" ON "myquotes_eventkeyword_event_id" ("eventkeyword_id", "event_id");
CREATE INDEX "myquotes_eventkeyword_event_id_eventkeyword_id_0a656bca" ON "myquotes_eventkeyword_event_id" ("eventkeyword_id");
CREATE INDEX "myquotes_eventkeyword_event_id_event_id_9215ad9c" ON "myquotes_eventkeyword_event_id" ("event_id");
CREATE UNIQUE INDEX "myquotes_eventkeyword_keyword_id_eventkeyword_id_keyword_id_b281797a_uniq" ON "myquotes_eventkeyword_keyword_id" ("eventkeyword_id", "keyword_id");
CREATE INDEX "myquotes_eventkeyword_keyword_id_eventkeyword_id_7a573e91" ON "myquotes_eventkeyword_keyword_id" ("eventkeyword_id");
CREATE INDEX "myquotes_eventkeyword_keyword_id_keyword_id_6f5222ff" ON "myquotes_eventkeyword_keyword_id" ("keyword_id");
COMMIT;

We have two more commands to run. The python3 manage.py makemigrations for good measure should return “No changes detected” since this is our firsts version.

python3 manage.py migrate will perform the actual migration of the latest changes applying them to our database store (i.e., data/myquotes.sqlite3). If migration is successful you should see the below output:

Operations to perform:
  Apply all migrations: admin, auth, contenttypes, myquotes, sessions
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying auth.0010_alter_group_name_max_length... OK
  Applying auth.0011_update_proxy_permissions... OK
  Applying myquotes.0001_initial... OK
  Applying sessions.0001_initial... OK

You can also log into the database by issuing the command sqlite3 myquotes.sqlite3 from /data and then running a built-in command like .schema to view the applied entities.

As the project evolved, a few changes were made to the models.py (one in particular, involved removing the “id” suffix from foreign key names since the database mapping automatically added this suffix to these column names). To make these alterations, the _makemigrations can be re-run and prompts answered as shown below. If successful, a summary of the alterations follows.

python3 manage.py makemigrations myquotes
Did you rename eventauthor.author_id to eventauthor.author (a OneToOneField)? [y/N] y
Did you rename eventauthor.event_id to eventauthor.event (a OneToOneField)? [y/N] y
Did you rename eventkeyword.event_id to eventkeyword.event (a ManyToManyField)? [y/N] y
Did you rename eventkeyword.keyword_id to eventkeyword.keyword (a ManyToManyField)? [y/N] y
Did you rename quotation.author_id to quotation.author (a ForeignKey)? [y/N] y
Did you rename quotation.quotation_source to quotation.source (a CharField)? [y/N] y
Did you rename quotationkeyword.keyword_id to quotationkeyword.keyword (a ManyToManyField)? [y/N] y
Did you rename quotationkeyword.quotation_id to quotationkeyword.quotation (a OneToOneField)? [y/N] y
Did you rename quotationlastshown.quotation_id to quotationlastshown.quotation (a OneToOneField)? [y/N] y
Migrations for 'myquotes':
  myquotes/migrations/0002_auto_20200619_1206.py
    - Rename field author_id on eventauthor to author
    - Rename field event_id on eventauthor to event
    - Rename field event_id on eventkeyword to event
    - Rename field keyword_id on eventkeyword to keyword
    - Rename field author_id on quotation to author
    - Rename field quotation_source on quotation to source
    - Rename field keyword_id on quotationkeyword to keyword
    - Rename field quotation_id on quotationkeyword to quotation
    - Rename field quotation_id on quotationlastshown to quotation
    - Add field description to author
    - Alter field bio_extract on author
    - Alter field bio_source_url on author
    - Alter field birth_date on author
    - Alter field death_date on author

The alterations can now be verified as before:

python3 manage.py sqlmigrate myquotes 0002
BEGIN;
--
-- Rename field author_id on eventauthor to author
--
CREATE TABLE "new__myquotes_eventauthor" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "author_id" integer NOT NULL UNIQUE REFERENCES "myquotes_author" ("id") DEFERRABLE INITIALLY DEFERRED, "event_id_id" integer NOT NULL UNIQUE REFERENCES "myquotes_event" ("id") DEFERRABLE INITIALLY DEFERRED);
INSERT INTO "new__myquotes_eventauthor" ("id", "event_id_id", "author_id") SELECT "id", "event_id_id", "author_id_id" FROM "myquotes_eventauthor";
DROP TABLE "myquotes_eventauthor";
ALTER TABLE "new__myquotes_eventauthor" RENAME TO "myquotes_eventauthor";
--
-- Rename field event_id on eventauthor to event
--
CREATE TABLE "new__myquotes_eventauthor" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "author_id" integer NOT NULL UNIQUE REFERENCES "myquotes_author" ("id") DEFERRABLE INITIALLY DEFERRED, "event_id" integer NOT NULL UNIQUE REFERENCES "myquotes_event" ("id") DEFERRABLE INITIALLY DEFERRED);
INSERT INTO "new__myquotes_eventauthor" ("id", "author_id", "event_id") SELECT "id", "author_id", "event_id_id" FROM "myquotes_eventauthor";
DROP TABLE "myquotes_eventauthor";
ALTER TABLE "new__myquotes_eventauthor" RENAME TO "myquotes_eventauthor";
--
-- Rename field event_id on eventkeyword to event
--
CREATE TABLE "myquotes_eventkeyword_event" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "eventkeyword_id" integer NOT NULL REFERENCES "myquotes_eventkeyword" ("id") DEFERRABLE INITIALLY DEFERRED, "event_id" integer NOT NULL REFERENCES "myquotes_event" ("id") DEFERRABLE INITIALLY DEFERRED);
INSERT INTO "myquotes_eventkeyword_event" (id, eventkeyword_id, event_id) SELECT id, eventkeyword_id, event_id FROM "myquotes_eventkeyword_event_id";
DROP TABLE "myquotes_eventkeyword_event_id";
--
-- Rename field keyword_id on eventkeyword to keyword
--
CREATE TABLE "myquotes_eventkeyword_keyword" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "eventkeyword_id" integer NOT NULL REFERENCES "myquotes_eventkeyword" ("id") DEFERRABLE INITIALLY DEFERRED, "keyword_id" integer NOT NULL REFERENCES "myquotes_keyword" ("id") DEFERRABLE INITIALLY DEFERRED);
INSERT INTO "myquotes_eventkeyword_keyword" (id, eventkeyword_id, keyword_id) SELECT id, eventkeyword_id, keyword_id FROM "myquotes_eventkeyword_keyword_id";
DROP TABLE "myquotes_eventkeyword_keyword_id";
--
-- Rename field author_id on quotation to author
--
CREATE TABLE "new__myquotes_quotation" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "quotation" varchar(200) NOT NULL UNIQUE, "quotation_source" varchar(100) NULL, "author_id" integer NOT NULL REFERENCES "myquotes_author" ("id") DEFERRABLE INITIALLY DEFERRED);
INSERT INTO "new__myquotes_quotation" ("id", "quotation", "quotation_source", "author_id") SELECT "id", "quotation", "quotation_source", "author_id_id" FROM "myquotes_quotation";
DROP TABLE "myquotes_quotation";
ALTER TABLE "new__myquotes_quotation" RENAME TO "myquotes_quotation";
CREATE UNIQUE INDEX "myquotes_eventkeyword_event_eventkeyword_id_event_id_7b1d035e_uniq" ON "myquotes_eventkeyword_event" ("eventkeyword_id", "event_id");
CREATE INDEX "myquotes_eventkeyword_event_eventkeyword_id_610edce7" ON "myquotes_eventkeyword_event" ("eventkeyword_id");
CREATE INDEX "myquotes_eventkeyword_event_event_id_2cbaa7ed" ON "myquotes_eventkeyword_event" ("event_id");
CREATE UNIQUE INDEX "myquotes_eventkeyword_keyword_eventkeyword_id_keyword_id_339b537c_uniq" ON "myquotes_eventkeyword_keyword" ("eventkeyword_id", "keyword_id");
CREATE INDEX "myquotes_eventkeyword_keyword_eventkeyword_id_346ba203" ON "myquotes_eventkeyword_keyword" ("eventkeyword_id");
CREATE INDEX "myquotes_eventkeyword_keyword_keyword_id_b4b84aaf" ON "myquotes_eventkeyword_keyword" ("keyword_id");
CREATE INDEX "myquotes_quotation_author_id_6456d063" ON "myquotes_quotation" ("author_id");
--
-- Rename field quotation_source on quotation to source
--
ALTER TABLE "myquotes_quotation" RENAME COLUMN "quotation_source" TO "source";
--
-- Rename field keyword_id on quotationkeyword to keyword
--
CREATE TABLE "myquotes_quotationkeyword_keyword" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "quotationkeyword_id" integer NOT NULL REFERENCES "myquotes_quotationkeyword" ("id") DEFERRABLE INITIALLY DEFERRED, "keyword_id" integer NOT NULL REFERENCES "myquotes_keyword" ("id") DEFERRABLE INITIALLY DEFERRED);
INSERT INTO "myquotes_quotationkeyword_keyword" (id, quotationkeyword_id, keyword_id) SELECT id, quotationkeyword_id, keyword_id FROM "myquotes_quotationkeyword_keyword_id";
DROP TABLE "myquotes_quotationkeyword_keyword_id";
--
-- Rename field quotation_id on quotationkeyword to quotation
--
CREATE TABLE "new__myquotes_quotationkeyword" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "quotation_id" integer NOT NULL UNIQUE REFERENCES "myquotes_quotation" ("id") DEFERRABLE INITIALLY DEFERRED);
INSERT INTO "new__myquotes_quotationkeyword" ("id", "quotation_id") SELECT "id", "quotation_id_id" FROM "myquotes_quotationkeyword";
DROP TABLE "myquotes_quotationkeyword";
ALTER TABLE "new__myquotes_quotationkeyword" RENAME TO "myquotes_quotationkeyword";
CREATE UNIQUE INDEX "myquotes_quotationkeyword_keyword_quotationkeyword_id_keyword_id_ea70edcc_uniq" ON "myquotes_quotationkeyword_keyword" ("quotationkeyword_id", "keyword_id");
CREATE INDEX "myquotes_quotationkeyword_keyword_quotationkeyword_id_56f3530e" ON "myquotes_quotationkeyword_keyword" ("quotationkeyword_id");
CREATE INDEX "myquotes_quotationkeyword_keyword_keyword_id_3312254e" ON "myquotes_quotationkeyword_keyword" ("keyword_id");
--
-- Rename field quotation_id on quotationlastshown to quotation
--
CREATE TABLE "new__myquotes_quotationlastshown" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "last_shown_date" date NOT NULL, "quotation_id" integer NOT NULL UNIQUE REFERENCES "myquotes_quotation" ("id") DEFERRABLE INITIALLY DEFERRED);
INSERT INTO "new__myquotes_quotationlastshown" ("id", "last_shown_date", "quotation_id") SELECT "id", "last_shown_date", "quotation_id_id" FROM "myquotes_quotationlastshown";
DROP TABLE "myquotes_quotationlastshown";
ALTER TABLE "new__myquotes_quotationlastshown" RENAME TO "myquotes_quotationlastshown";
--
-- Add field description to author
--
CREATE TABLE "new__myquotes_author" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "full_name" varchar(100) NOT NULL UNIQUE, "birth_date" date NOT NULL, "death_date" date NOT NULL, "bio_extract" varchar(400) NOT NULL, "bio_source_url" varchar(200) NOT NULL, "description" varchar(200) NULL);
INSERT INTO "new__myquotes_author" ("id", "full_name", "birth_date", "death_date", "bio_extract", "bio_source_url", "description") SELECT "id", "full_name", "birth_date", "death_date", "bio_extract", "bio_source_url", NULL FROM "myquotes_author";
DROP TABLE "myquotes_author";
ALTER TABLE "new__myquotes_author" RENAME TO "myquotes_author";
--
-- Alter field bio_extract on author
--
CREATE TABLE "new__myquotes_author" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "full_name" varchar(100) NOT NULL UNIQUE, "birth_date" date NOT NULL, "death_date" date NOT NULL, "bio_source_url" varchar(200) NOT NULL, "description" varchar(200) NULL, "bio_extract" varchar(800) NULL);
INSERT INTO "new__myquotes_author" ("id", "full_name", "birth_date", "death_date", "bio_source_url", "description", "bio_extract") SELECT "id", "full_name", "birth_date", "death_date", "bio_source_url", "description", "bio_extract" FROM "myquotes_author";
DROP TABLE "myquotes_author";
ALTER TABLE "new__myquotes_author" RENAME TO "myquotes_author";
--
-- Alter field bio_source_url on author
--
CREATE TABLE "new__myquotes_author" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "full_name" varchar(100) NOT NULL UNIQUE, "birth_date" date NOT NULL, "death_date" date NOT NULL, "bio_extract" varchar(800) NULL, "description" varchar(200) NULL, "bio_source_url" varchar(200) NULL);
INSERT INTO "new__myquotes_author" ("id", "full_name", "birth_date", "death_date", "bio_extract", "description", "bio_source_url") SELECT "id", "full_name", "birth_date", "death_date", "bio_extract", "description", "bio_source_url" FROM "myquotes_author";
DROP TABLE "myquotes_author";
ALTER TABLE "new__myquotes_author" RENAME TO "myquotes_author";
--
-- Alter field birth_date on author
--
CREATE TABLE "new__myquotes_author" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "full_name" varchar(100) NOT NULL UNIQUE, "death_date" date NOT NULL, "bio_extract" varchar(800) NULL, "bio_source_url" varchar(200) NULL, "description" varchar(200) NULL, "birth_date" date NULL);
INSERT INTO "new__myquotes_author" ("id", "full_name", "death_date", "bio_extract", "bio_source_url", "description", "birth_date") SELECT "id", "full_name", "death_date", "bio_extract", "bio_source_url", "description", "birth_date" FROM "myquotes_author";
DROP TABLE "myquotes_author";
ALTER TABLE "new__myquotes_author" RENAME TO "myquotes_author";
--
-- Alter field death_date on author
--
CREATE TABLE "new__myquotes_author" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "full_name" varchar(100) NOT NULL UNIQUE, "birth_date" date NULL, "bio_extract" varchar(800) NULL, "bio_source_url" varchar(200) NULL, "description" varchar(200) NULL, "death_date" date NULL);
INSERT INTO "new__myquotes_author" ("id", "full_name", "birth_date", "bio_extract", "bio_source_url", "description", "death_date") SELECT "id", "full_name", "birth_date", "bio_extract", "bio_source_url", "description", "death_date" FROM "myquotes_author";
DROP TABLE "myquotes_author";
ALTER TABLE "new__myquotes_author" RENAME TO "myquotes_author";
COMMIT;

To apply the changes, the actual migration can now be run which will pick-up the latest changes:

python3 manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, myquotes, sessions
Running migrations:
  Applying myquotes.0002_auto_20200619_1206... OK

Now that our models and database are complete, we are ready to begin populating our store (at least initially, with some test data)

Database Loader Script

The load_quotes_sqlite.pl script takes as input the JSON formatted data structure generated by the filter_quotes.pl script and loads the data into the database tables.

As with the other scripts, running ./load_quotes_sqlite.pl –help will yield usage information (if needed, you may need to install the CPAN JSON module used by the script). A reference database.conf.template can be found in this repository and it can be modified accordingly and used with the –db-conf command-line option.

./load_quotes_sqlite.pl --help
Usage:   ./load_quotes_sqlite.pl --db-conf <database config file> [ --debug --verbose ] < <author JSON>
Example: ./load_quotes_sqlite.pl --db-conf myquotes.conf --debug --verbose < filter_quotes_output.json

Using the Django Shell to Access the Data

While you can query the SQLite tables directly to look at the loaded data using SQL, a more direct and user-friendly way is to use the models API. In the example below we log into the shell and import the Event class.

We can display the partial list of abstracted objects and then filter by an attribute (in this case id) to display Event data associated with that id.

python3 manage.py shell
Python 3.7.6 (default, Dec 27 2019, 09:51:13) 
[Clang 10.0.0 (clang-1000.11.45.5)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from myquotes.models import Event
>>> Event.objects.all()
<QuerySet [<Event: Event object (1)>, <Event: Event object (2)>, <Event: Event object (3)>, <Event: Event object (4)>, <Event: Event object (5)>, <Event: Event object (6)>, <Event: Event object (7)>, <Event: Event object (8)>, <Event: Event object (9)>, <Event: Event object (10)>, <Event: Event object (11)>, <Event: Event object (12)>, <Event: Event object (13)>, <Event: Event object (14)>, <Event: Event object (15)>, <Event: Event object (16)>, <Event: Event object (17)>, <Event: Event object (18)>, <Event: Event object (19)>, <Event: Event object (20)>, '...(remaining elements truncated)...']>
>>> Event.objects.filter(id=1).values()
<QuerySet [{'id': 1, 'event': "President's Day honors and celebrates the life and achievements of George Washington, the first President of the United States (1789-1797) and 'The Father of his Country'. On this day we also honor and remember all past US presidents, and in particular Washington, Abraham Lincoln and Thomas Jefferson.", 'day': 15, 'month': '02', 'year': 2021, 'season': 'Winter'}]>
>>> exit()

Setting Up the Admin API

The Admin API will allow us to manage users and groups and perform other meta-operations. By default, you should have the required Apps (i.e., django.contrib.admin and django.contrib.auth) under the INSTALLED_APPS section of your quotations/settings.py (as well as the supporting MIDDLEWARE libraries and TEMPLATES)

Now create the superuser by running the command below and following the prompt sets that follow:

python3 manage.py createsuperuser
Username (leave blank to use 'svpineo'): admin
Email address: svpineo@gmail.com                
Password: 
Password (again): 
Superuser created successfully.

Next, ensure that the Web application is running and the admin app is accessible by going to the URL http://localhost:8000/admin, assuming the default port (if necessary restart the Web application by running python3 manage.py runserver). You should see the Admin Login Web page shown below:

Admin_Login

After entering the admin username and password created above, you should now see the Admin Web page shown below. We will revisit this API as we develop the App.

Admin Web Page

Creating the Admin CRUD (Create, Read, Update Delete) API

Example Generic List View: Listing Events

We will build this API incrementally. The first step will be to simply create a listing of all events. For this, we will first create a myquotes/templates directory and add to it the below event.html file with the contents as shown:

<h1>Events</h1>
<ul>

{% for event in event_list %}
    <li>{{ event.event }}</li>
{% endfor %}

</ul>

Next we will modify the myquotes/view.py to use the Django generic ListView library and integrate the template:

from django.views.generic.list import ListView

from myquotes.models import Event
    
# 'context_object_name' by default is 'event_list' but can be overriden
#
class EventListView(ListView):
    model = Event
    template_name = "event.html"

The quotations/urls.py can now be modified to use this new class view, in addition to our admin one defined earlier (the _myquotes/urls.py, at least for now, is not necessary):

from django.contrib import admin
from django.urls import include, path

from myquotes.views import EventListView
    
urlpatterns = [
    path('myquotes/event/', EventListView.as_view()),
    path('admin/', admin.site.urls),
]

Finally, the quotations/settings.py needs to be modified to find the new template by including the following:

At the end of the “Build paths…” section add:

MYQUOTES_TEMPLATES = os.path.join(BASE_DIR, 'myquotes/templates/')

Under the TEMPLATES data structure add:

TEMPLATE_DIRS = (
    'MYQUOTES_TEMPLATES',
)

If we now go to our url http://localhost:8000/myquotes/event/ we should see our “Events” headline followed by the bulleted listing of events.

Admin CRUD API for Events

Now that we have worked on our simple listing API, the Admin API to implement our CRUD functionality should be even easier since it leverages the existing admin libraries. For the event example, we will first modify the myquotes/models.py Event class as shown below. Basically we changed the return value of the max_value_current_year method to use the datetime library (so will also need to add the from datetime import date at the top of the models.py). We also changed the values for SEASON_CHOICES to match the data loaded. Finally, we added the class return value so that the Event event name is returned rather than the object reference.

class Event(models.Model):
    MONTH_CHOICES    = [(str(i), calendar.month_name[i]) for i in range(1,13)]
    SEASON_CHOICES   = [
        ('Winter', 'Winter'),
        ('Spring', 'Spring'),
        ('Summer', 'Summer'),
        ('Fall',   'Fall'),
    ]

    def max_value_current_year():
        return date.today().year 

    event            = models.CharField(max_length=400, unique=True)
    day              = models.IntegerField(validators=[MaxValueValidator(31), MinValueValidator(1)], null=True) 
    month            = models.CharField(max_length=9, choices=MONTH_CHOICES, default=1, null=True)
    year             = models.IntegerField(validators=[MaxValueValidator(max_value_current_year()+1), MinValueValidator(-1000)], null=True)
    season           = models.CharField(max_length=6, choices=SEASON_CHOICES, null=True)

    def __str__(self):
        return self.event

We will also modify the myquotes/admin.py to include the following:

from django.contrib import admin
    
from myquotes.models import Event
    
admin.site.register(Event)

We can now log on to the Admin API like we did before by going to http://localhost:8000/admin but this time we will see a link to our Events API:

Admin Events

If we click on the Events link, we will see the listing of all the event names. As shown in the screenshot below, we can check multiple events, select the action Delete selected events, and delete the selected events in one batch (which we will not do just quite yet)

Admin Events Delete

Alternatively, if we click on the ADD EVENT + (top-right corner) it will bring us to a form that we can fill out to manually add an event:

Admin Event Add

and once the event is successfully added, it will show up at the top position of the listing:

Admin Event Added

Finally, we can test changing the event we just added.

Admin Event Changed

and then, once we save and confirm that it has changed, deleting it.

Admin Event Delete

This completes our manual CRUD of Events verification. The next step will be to extend the Admin CRUD API to include Authors, Quotations, Keywords, and their relationships but, before we get there, some cleanup is in order.

New Alterations to the Schema

As I was loading the myquotes_authors table, a few of the instance date fields (i.e., birth_date and death_date) got loaded with bogus values. This came to light as I was trying to create a view to display the author attributes but kept on getting validation errors. The bogus value in most cases was a year without an associated month and day which produced a validation error at the level of the Object Relational Mapper (ORM) models used by the view. The most accurate solution to this problem was first to add the associated integer attributes birth and death year/day to capture the years and days and, like I also did with the Event, use the mapping function to generate the month display names for the month fields (thus a birth_date and/or death_date missing the required year, month, and/or day would be set to null)

While I was at it, I also decided to cleanup the models.py by adding a Date class to handle all these functions now used more than once and make it easy to set/modify some associated global variables (in essence following the coders axiom DRY or Do Not Repeat Yourself!). Since for authors some of the months will be null (either because they cannot be found or author hasn’t died) the new range starts and defaults to zero. For author we will also return self.full_name for the Admin display.

The new changes are shown below:

# Handling dates
#
class Date():
    MONTH_CHOICES    = [(str(i), calendar.month_name[i]) for i in range(0,13)]

    SEASON_CHOICES   = [
        ('Winter', 'Winter'),
        ('Spring', 'Spring'),
        ('Summer', 'Summer'),
        ('Fall',   'Fall'),
    ]

    MAX_FUTURE_YEARS = 5
    MIN_YEAR         = -1000

    def max_value_current_year():
        return date.today().year


# The primary key field for each class is auto-generated
#
class Author(models.Model):
    full_name        = models.CharField(max_length=100, unique=True)
    birth_date       = models.DateField(null=True)
    birth_day        = models.IntegerField(validators=[MaxValueValidator(31), MinValueValidator(1)], null=True) 
    birth_month      = models.CharField(max_length=9, choices=Date.MONTH_CHOICES, default=0, null=True)
    birth_year       = models.IntegerField(validators=[MaxValueValidator(Date.max_value_current_year()+Date.MAX_FUTURE_YEARS), MinValueValidator(Date.MIN_YEAR)], null=True)
    death_date       = models.DateField(null=True)
    death_day        = models.IntegerField(validators=[MaxValueValidator(31), MinValueValidator(1)], null=True) 
    death_month      = models.CharField(max_length=9, choices=Date.MONTH_CHOICES, default=0, null=True)
    death_year       = models.IntegerField(validators=[MaxValueValidator(Date.max_value_current_year()+Date.MAX_FUTURE_YEARS), MinValueValidator(Date.MIN_YEAR)], null=True)
    description      = models.CharField(max_length=200, null=True)
    bio_extract      = models.CharField(max_length=800, null=True)
    bio_source_url   = models.URLField(null=True)

    def __str__(self):
        return self.full_name

Next, we want to apply (in particular the author table) the alterations to our underlying database but, before doing that it is wise to delete the data to avoid any referential integrety issues that might interfere with the process. Fortunately, all this data was generated with our automation scripts and will be easy to reconstitute.

To automate the deletes, a simple batch file (i.e., bin/tables_delete.sql) with the delete statements will suffice:

delete from myquotes_quotationlastshown;
delete from myquotes_quotationkeyword;
delete from myquotes_quotationkeyword_keyword;
delete from myquotes_eventkeyword_keyword;
delete from myquotes_eventkeyword_event;
delete from myquotes_eventauthor;
delete from myquotes_eventkeyword;
delete from myquotes_event;
delete from myquotes_author;
delete from myquotes_quotation;
delete from myquotes_keyword;

From our new bin directory we can now run sqlite3 ../data/myquotes.sqlite3 < tables_delete.sql and do the cleanup instantly. Of course, our production version integrating manual updates will require backups and some additional logic.

There are other ways to flush data using the manager: python3 manage.py help flush (though depending how its used will also delete data added during the migration)

We are now ready to apply our new changes by creating our migrations file:

python3 manage.py makemigrations
Migrations for 'myquotes':
  myquotes/migrations/0003_auto_20200629_0938.py
    - Add field birth_day to author
    - Add field birth_month to author
    - Add field birth_year to author
    - Add field death_day to author
    - Add field death_month to author
    - Add field death_year to author
    - Alter field event on event
    - Alter field season on event
    - Alter field year on event

and then applying it:

python3 manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, myquotes, sessions
Running migrations:
  Applying myquotes.0003_auto_20200629_0938... OK

The final step, of course, before we are ready to finish implementing our Admin API is to reload the data but not before fixing the handling of our dates and modifications to the author table. Fortunately, all changes were confined to the loader script and included:

Our new myquotes/admin.py now includes Authors:

from django.contrib import admin

from myquotes.models import Author, Event

admin.site.register(Author)
admin.site.register(Event)

and can now be accessed from the Admin Web API:

Admin Authors

Clicking on Authors we can view a list of authors:

Admin Authors List

and the detail view, by clicking on an individual author:

Admin Author Detail

As a final step, we can test the years range validations by attempting to save the profile after modifying the years:

Admin Author Validation

Of course, this uncovered an additional problem which is that our main date fields did not change. To fix this, I removed both of the original date attributes from models.py (birth_date and death_date) as their data is now redundant. I also removed the bio_extract attribute as this data is readily available in the bio_source_url (the description, if needed, can also be used to capture summarized biographical data).

As before, after making the changes to the models.py and loader script and deleting the existing data, I re-applied the new migration (below) and re-loaded the data to generate our new pared down authors schema and associated data.

python3 manage.py makemigrations
Migrations for 'myquotes':
  myquotes/migrations/0004_auto_20200630_0925.py
    - Remove field bio_extract from author
    - Remove field birth_date from author
    - Remove field death_date from author
    - Alter field birth_month on author
    - Alter field death_month on author
    - Alter field month on event

python3 manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, myquotes, sessions
Running migrations:
  Applying myquotes.0004_auto_20200630_0925... OK

And our new pared down Author form:

Author Detail Pared

The two remaining lists to be added to our Admin API are quotations and keywords. For quotation, since the additional context is needed, we will return both the quotation and author referenced by the foreign key with some additional formatting (as shown below). The keyword, will simply be, as before, a single value returned.

class Quotation(models.Model):
    quotation        = models.CharField(max_length=200, unique=True)
    source           = models.CharField(max_length=100, null=True)
    author           = models.ForeignKey(Author, on_delete=models.CASCADE)

    def __str__(self):
        return '"' + self.quotation + '" - ' + self.author.full_name

For the additional relationship classes that contain the many-to-many attributes additional customization of admin.py will be needed. We will review that next.

First, we will further customize our own Admin API by leveraging the existing admin.ModelAdmin built-in class. For Authors we will sort our listing by full_name (descending by default) and add the Description field by creating the AuthorAdmin class in our admin.py:

class AuthorAdmin(admin.ModelAdmin):
    list_display = ['full_name', 'description' ]
    ordering = ['full_name']

admin.site.register(Author, AuthorAdmin)

The default listing, now displays the sorted list along with the description:

Author List Description

The Web Framework Views

Display Random Quote

The most simple view will be to simply display a random quote along with its source and author every time the Web page refreshes. Once the general look is established, we can go back and add more information to this detailed view as well as modify the backend to incorporate date and keywords.

Our simple view shown below, which will display quotation, source (if not null), and author, will implement a template and include some simple CSS in order to center the text and give it some additional attributes (i.e., italic, header).

from django.http import HttpResponse
  
from django.template import loader
        
from myquotes.models import Quotation
        
import random
        
def index(request):
        
    count       = Quotation.objects.count()
    rand_num    = random.randint(1, count)
        
    quotation   = Quotation.objects.all()[rand_num:rand_num+1]
  
    template = loader.get_template('index.html')
    context = {
        'quotation': quotation,
    }
    return HttpResponse(template.render(context, request))

Our template myquotes/templates/index.html is:

<html>
  <style>
    body {
        background: white;
        color: black;
        font-style: italic;
        border-radius: 1em;
        padding: 1em;
        position: absolute;
        top: 50%;
        left: 50%;
        margin-right: -50%;
        transform: translate(-50%, -50%)
    }
  </style>
  <body>
    <h1></h1>
  </body>
</html>

Finally, we will also add the new entry to quotations/urls.py:

from myquotes import views

urlpatterns = [
    path('admin/', admin.site.urls),
    path('myquotes/', views.index)

A sample quotation and author, which will change every time the page is refreshed, is shown below:

Sample Quotation

Display Author Associated Event Driven (i.e., date) Quotation

Next we will display an event based on date and the associated author/quotation. We will test the case where an event is found and the case where one is not found (in which case, like before, we will just display a random quotation)

For the view, we will now perform a random query against the EventAuthor model filtered by month (this way we will ensure that an event and associated author will always be retrieved). Our fall-back, like before, is to retrieve a random quote if no event is found (a fall-back we will likely need once we filter by both month and day)

def index(request):
    now = datetime.datetime.now()

    year      = now.year
    month     = now.month
    day       = now.day

    # Initialize
    #
    event          = ''
    author         = ''
    quotation      = ''

    # Query Events   
    #
    events_count   = Event.objects.filter(month=month).count()
    event_rand_num = random.randint(1, events_count)
        
    event_author   = EventAuthor.objects.select_related().filter(event__month=month)[event_rand_num-1:event_rand_num]
    if event_author:
        event     = event_author[0].event
        author    = event_author[0].author

    if event and author:
        # Get a random quotation associated with that author
        #
        quotes_count    = Quotation.objects.filter(author=author).count()
        quotes_rand_num = random.randint(1, quotes_count)
        quotation       = Quotation.objects.filter(author=author)[quotes_rand_num-1:quotes_rand_num]

    # Print the random quotation
    #
    else:
        all_count = Quotation.objects.count()
        rand_num  = random.randint(1, all_count)

        quotation = Quotation.objects.all()[rand_num-1:rand_num]

    # Display the random quotation
    #
    template = loader.get_template('index.html')
    context = {
        'event': event,
        'quotation': quotation,
    }

    return HttpResponse(template.render(context, request))

We will use the same template we used before but modified to render the event in addition to the quotation:

<html>
  <style>
    body {
        background: white;
        color: black;
        font-style: italic;
        border-radius: 1em;
        padding: 1em;
        position: absolute;
        top: 50%;
        left: 50%;
        margin-right: -50%;
        transform: translate(-50%, -50%)
    }
  </style>
  <body>
    
    <h1></h1>
  </body>
</html>

An example display using this new criteria is shown below:

Event and Quotation

References