Three-Tier App VMware Hands-on Labs

3-Tier Demo App – 2023 Edition – db-01a

This is the second post in a series about creating a simple 3-tier web application for demo purposes. This updates a series from 2017 to use the latest version of Photon OS at this time. The posts in this new series are as follows:

I work bottom-up when creating this application. This way, it is possible to test the components as we go along and address any issues before we get to the end and have to worry about the whole stack. In the diagram, we’ll work right to left.

The Database (db-01a)

There are many choices that could have been made here, but I wanted to minimize the steps required and use the tools I had available by default, wherever possible. 

When I created v1.0 of this application, sqlite3 was installed as part of the base Photon OS install, so, the database server for this application uses a SQLite database. This may seem like an odd choice since it has no native remote access, but it fills the need.

My Python has gotten a bit better since I created the original version, but don’t expect elegance here. I hacked together a simple CGI script to query the database and send the results over HTTP. It is small and does just enough.

Not a DBA? Fear not! SQLite is simple enough that you don’t need any kind of DBA skills to use it. The minimal SQL that I know was enough. Just follow along here and copy/paste away!

The red box in the following diagram highlights the component that we are building in this section.

Database Server

Let’s get started!

Deploy a copy of the base template

Deploy a copy of the base Photon template you created by following the steps in my first post. Name it something that makes sense to you. I called mine db-01a.

Power it up and log in as the root user.

Set the IP address

You need to configure the IP address for this machine. Here are the contents of my /etc/systemd/network/99-static-en.network file for db-01a.

[Match]
Name=e*

[Network]
Address=192.168.120.10/24
Gateway=192.168.120.1
DNS=8.8.8.8
Domains=corp.local

Set the hostname

Update the name in the /etc/hosts and /etc/hostname files with the proper name for this VM (db-01a). Replace every occurrence of the name you used in the template with the name of this VM. Note that the hostname within the prompt string will not change until you log out.

Restart the network to apply the settings

# systemctl restart systemd-networkd

# ip address show

Once you have finished with these steps, make sure you can access the network before moving on. You need Internet access to proceed.

SSH in

At this point, I strongly suggest you SSH to the machine so that you can paste text. Doing the work from the console is possible, but pasting is easier. So, fire up puTTY or whatever else you use as your preferred SSH client.

Install and configure the web server

I am using Apache here since I can get the base install to do what I need in short order. Unfortunately, it is not already present, so we need to install it. Fortunately, it only takes a few minutes:

# tdnf install httpd

Make a directory to store the database file

NOTE: you don’t absolutely need to do this, but I sometimes like to separate my data from the executables and it is a good practice.

# mkdir /etc/httpd/db

Make the “apache” user the owner

# chown apache:apache /etc/httpd/db

Start the web server and set it to start when the VM boots

# systemctl start httpd
# systemctl enable httpd

Create the database’s front-end CGI script

There is not much to this one. It performs a simple query of the database and dumps the result. You can type in the code, but if you have connected via SSH, you can paste it in. I recommend the latter. Keep in mind that Python uses whitespace to give the program structure, so indenting is VERY important. More precisely, the exact amount of indentation does not matter, but the relative indentation of nested blocks to one another matters a lot. 

If that sounds confusing, just make sure the spacing looks like mine.

This script takes an optional parameter named querystring that allows the data to be filtered on the name property of the records. It is a step above the “dump everything” approach we used in previous versions and provides the possibility for some user interaction.

Open a new file, /etc/httpd/cgi-bin/data.py

# vi /etc/httpd/cgi-bin/data.py

and add the following. (This script is available on GitHub: data.py):

#!/usr/bin/env python3
import cgi
import sqlite3

conn = sqlite3.connect('/etc/httpd/db/clients.db')
curs = conn.cursor()
print("Content-type:text/plain\n\n")
form = cgi.FieldStorage()
querystring = form.getvalue("querystring")

if querystring is not None:
    queryval = "%" + querystring + "%"
    select = "SELECT * FROM clients WHERE name LIKE '" + queryval + "'"
else:
    select = "SELECT * FROM clients"

for row in curs.execute(select):
    if len(row) == 4:
        for item in row:
            print(item,'|')
        print("#")
conn.close()

Save and close the file, then mark it executable

# chmod 755 /etc/httpd/cgi-bin/data.py

Install sqlite

This is our simple database.

# tdnf install sqlite

Create the database file and load it with data

SQLite will create the file if it is not already present. Bonus!

# sqlite3 /etc/httpd/db/clients.db

At the sqlite> prompt, create the table:

For the manual process, at the sqlite> prompt, create the table:

CREATE TABLE 'clients' (
 "Rank" integer,
 "Name" varchar(30),
 "Universe" varchar(25),
 "Revenue" varchar(20)
 );

Then, load in some data. Feel free to use whatever you like, but this is a good start:

INSERT INTO 'clients' VALUES
 (1,'CHOAM','Dune','$1.7 trillion'),
 (2,'Acme Corp.','Looney Tunes','$348.7 billion'),
 (3,'Sirius Cybernetics Corp.',"Hitchhiker's Guide",'$327.2 billion'),
 (4,'Buy n Large','Wall-E','$291.8 billion'),
 (5,'Aperture Science, Inc.','Valve','$163.4 billion'),
 (6,'SPECTRE','007','$157.1 billion'),
 (7,'Very Big Corp. of America','Monty Python','$146.6 billion'),
 (8,'Frobozz Magic Co.','Zork','$112.9 billion'),
 (9,'Warbucks Industries',"Lil' Orphan Annie",'$61.5 billion'),
 (10,'Tyrell Corp.','Bladerunner','$59.4 billion'),
 (11,'Wayne Enterprises','Batman','$31.3 billion'),
 (12,'Virtucon','Austin Powers','$24.9 billion'),
 (13,'Globex','The Simpsons','$23.7 billion'),
 (14,'Umbrella Corp.','Resident Evil','$22.6 billion'),
 (15,'Wonka Industries','Charlie and the Chocolate Factory','$21.0 billion'),
 (16,'Stark Industries','Iron Man','$20.3 billion'),
 (17,'Clampett Oil','Beverly Hillbillies','$18.1 billion'),
 (18,'Oceanic Airlines','Lost','$7.8 billion'),
 (19,'Brawndo','Idiocracy','$5.8 billion'),
 (20,'Cyberdyne Systems Corp.','Terminator','$5.5 billion'),
 (21,'Paper Street Soap Company','Fight Club','$5.0 billion'),
 (22,'Gringotts','Harry Potter','$4.4 billion'),
 (23,'Oscorp','Spider-Man','$3.1 billion'),
 (24,'Nakatomi Trading Corp.','Die-Hard','$2.5 billion'),
 (25,'Los Pollos Hermanos','Breaking Bad','$1.3 billion');

Once you are happy with the data you have entered — ensure that you finish with a semi-colon and a newline — press Control-D to close the SQLite session.

(alternative) Create and load the database with the script from GitHub

You can automate the creation and loading of the database by downloading the create_db.sql file from GitHub and running it, though copy and paste usually works fine for me unless the blog’s formatting is broken.

# sqlite3 /etc/httpd/db/clients.db < create_db.sql

Set the database file’s owner

The apache user running the web server needs access to this file for the CGI script to read the data.

# chown apache:apache /etc/httpd/db/clients.db

Enable CGI on the webserver

The default Apache install on Photon OS does not have the CGI module loaded. It is simple enough to turn it on.

Open the web server’s configuration file. The +181 before the file name opens the file at line 181, which is where we want to start:

# vi +181 /etc/httpd/httpd.conf

At line 181, add the following line to load the CGI module:

LoadModule cgi_module /usr/lib/httpd/modules/mod_cgi.so

TIP: if you’re using the vi editor, you can jump to another line in the file by hitting the ESC key, typing the line number and then shift-g. (Not typing a number will take you to the last line in the file)

On line 52, change

Listen 80 -> Listen 3306

And, on line 221, change

ServerName localhost:80 -> db-01a:3306

At line 393, add the following to enable access to the database directory. 

It goes right before a line that starts with <IfModule mime_module>

#database directory
<Directory "/etc/httpd/db">
    AllowOverride None
    Options None
    Require all granted
</Directory>

Save and close the file.

Restart the web server to read the updated configuration

# systemctl restart httpd

Verify

Now, you should be able to access the script via http and you should see the data. Note that you must specify the 3306 port number in the URL.

# curl http://db-01a:3306/cgi-bin/data.py

TIP: We are doing something a little “funny” here by running HTTP over port 3306, but the idea is to show communication via specific ports rather than demonstrate specific protocols running on those ports. Note that 3306 is normally used by MySQL, so this “looks like” a database query until you analyze the packets.

The results of that curl command won’t look too pretty, but the user never sees this back-end data. At this point, the result should look something like this:

root@db-01a [ ~ ]# curl http://db-01a:3306/cgi-bin/data.py

1 |
CHOAM |
Dune |
$1.7 trillion |
#
... (truncated) ...
#
24 |
Nakatomi Trading Corp. |
Die-Hard |
$2.5 billion |
#
25 |
Los Pollos Hermanos |
Breaking Bad |
$1.3 billion |
#

The next piece of the puzzle is the application server, which consumes the data provided by this component and makes it look pretty. If you had no problems with this setup, the rest should be a breeze. This is really the most complicated part of the whole application.

Next post: 3-Tier Demo App – 2023 Edition – app-01a

Doug Baer

Doug Baer worked on the Hands-on Labs program for many years and is now a Solution Architect on VMware's Cloud Incubation team. He is passionate about sharing knowledge, writing, developing…

Related Articles