Home > Blogs > VMware Hands-On Lab (HOL) Blog


HOL Three-Tier Application, Part 2 – DB server

This is the second post in the series about building a three-tier application for demonstration, lab, and education purposes. At this point, you have downloaded Photon OS and prepared the base template according to the specifications. If not, please go to the first post in the series to prepare your environment.

I am trying to release these posts on Wednesdays, but yesterday was a holiday in the US, so I had some time to get this one out the door early.

For the build, I will work bottom-up so 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.

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. 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, the sqlite3 is already present on the Photon image. So is Python. Don’t expect elegance here since I do not normally use Python. I hacked together a simple CGI script to query the database and send the results over HTTP. It is not fancy, but it is small and does just enough for my needs.

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 to get this done.

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

Application - Database

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.

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/10-static-eth0.network file.

[Match]
Name=eth0

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

Set the name

Update the name in the /etc/hosts and /etc/hostname files with the proper name for this VM. 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 change when you log out.

Restart the network to apply the settings

# systemctl restart systemd-networkd

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

SSH in

At this point, I strongly suggest you SSH to the machine so that you can paste text. Doing all of 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 the web server

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

# tdnf install httpd

install-httpd

Make a directory to store the database file and make apache the owner

So you don’t absolutely need to do this, but I sometimes like to separate my data from the executables.

# mkdir /etc/httpd/db
# chown apache:apache /etc/httpd/db

Start the web server and set it to startup 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 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

#!/usr/bin/env python
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 != 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

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:

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:

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.

Set the database file’s owner

The apache user running the web server needs access to this file in order 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 does not have the CGI module loaded. It is simple enough to turn it on:

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

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

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

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

At line 379, 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 can access the script via http and you should see the data.

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

It won’t look too pretty, but the user never sees this back end data. That’s where the application server comes in. At this point, the result should look something like this:

root@db-01a [ ~ ]# curl http://db-01a/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 |
#
root@db-01a [ ~ ]#

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

Thank you for reading!

The next post detailing the build of the App server is available here.

This entry was posted in VMware Hands-on Labs and tagged , , , , on by .

About Doug Baer

Doug Baer is a Staff Technical Marketing Architect on the Hands-on Labs team at VMware. He is VCDX #19 and a vExpert since 2012. His primary focus area is the Hands-on Labs, but there is some storage and data replication that he just can't let go.

2 thoughts on “HOL Three-Tier Application, Part 2 – DB server

  1. Mike Wilson

    Awesome Post! I have been waiting for something like this, simple and quick to deploy. Now I have to wait…. (no way we could speed it up a bit? lol ) Thanks Doug..

  2. Pingback: HOL Three-Tier Application, Part 3 - App Server - VMware Hands-On Lab (HOL) Blog - VMware Blogs

Comments are closed.