Installing the Sakila Demo Database from Scripts

It happens. Sometimes we mess up our demo database. Sometimes the installation option is missed and it doesn’t get installed. You don’t need to re-install all of MySQL to get the demo database. They can be installed using scripts.

You can download the Sakila database scripts here: https://downloads.mysql.com/docs/sakila-db.zip

Once downloaded, you can follow the steps on MySQL’s guide to run them: MySQL :: Sakila Sample Database :: 4 Installation

To execute the file (be sure you are in the same directory as the scripts) you can run:

source the-script-name.sql


SQL WHERE Clause Operators

The following operators can be used in the WHERE clause to filter data:

Operator Description Example
= Equal WHERE last_name = 'Potter'
> Greater than WHERE price > 5
< Less than WHERE price < 5
>= Greater than or equal WHERE price >= 5
<= Less than or equal WHERE price <= 5
!= or <> Not equal WHERE last_name != 'Potter'
BETWEEN Between a certain range WHERE date_added BETWEEN '01-JAN-19' AND '31-DEC-19'
LIKE Search for a pattern (place a % to represent a wildcard) WHERE last_name LIKE 'Po%'
For example return 'Potter', 'Poe', and 'Pope'.
IN In a specified list WHERE last_name IN ('Potter','Weasley','Granger')
Example 1
SELECT 
  first_name 
, last_name 
FROM contact 
WHERE first_name = 'Harry'
AND last_name = 'Potter';
                    

Example 2
SELECT 
  item_title
, release_date
, item_rating
FROM item 
WHERE release_date BETWEEN '01-JAN-83' AND '31-JAN-83'
AND item_rating != 'R';
                    

Also, keep in mind that these can all be inverted with the NOT keyword.

WHERE NOT last_name = 'Potter' would be synonymous with WHERE last_name != 'Potter'.

Fedora Emergency Mode?

Have something go wrong while you are in a virtual machine and you end up having to force quit? Sometimes that doesn’t end so well with the guest operating system. This can often be the case when running the Fedora instance for CIT225 and you may come face to face with the Fedora Emergency Mode.

You can verify that it is malformed data in the virtual disk by checking the system logs.
journalctl -xb

In the output you may see some red lines that say that a service called fsck failed. This means you most likely have malformed data. Luckily, that same service can fix the problem and have us up and going very quickly. Simply run:
fsck /dev/mapper/fedora-root

Then use the VMware menu to restart or reset the virtual machine. It should boot to Fedora.

CIT225 Fedora Instance Torrent

If you are having problems downloading the Fedora image (especially if you have bad internet connection)

I have created a torrent that should allow you to download incrementally (so if connectivity gets bad it doesn’t have to start all over). Use a torrent client like Transmission (Links to an external site.) and open the attached file in the torrent client.

If the class could also please help seed it would be awesome!

Torrent File: http://tannercrook.com/downloads/FedoraV2020.zip.torrent

 

Flask and SQLAlchemy

Instructions

Now that we have a database ready to use, we need to make our database accessible to our web application framework Flask. To do so, we will be using SQLAlchemy which will not only connect to the database, but will also manage the python objects to make manipulating and using the the data easier.

 

Getting the Project Files

Previously we had set up a basic Flask directory structure. We are going to move those files to back them up, and get the completed project files.

First, lets change our directory name so we can put the new files there:
mv /var/www/app/app /var/www/app/app.bak

Now lets go to our app directory and use git to download the files we need:
cd /var/www/app
git clone https://github.com/tannercrook/Flask-LAMP
And then rename the directory we download: mv Flask-LAMP app

You can now enter the directory and find our Flask structure!

 

 

Build the Python Container

As we did with our first Flask structure, we need to build the virtual environment for Flask and install all of the dependencies. We can use the requirements.txt to install all of the dependencies.

First, let’s make sure we are in the correct location. Running pwd should return /var/www/app/app.

Now, we can create our container for Python:
python3 -m venv venv

Next, we will activate the container, and install the dependencies:
source venv/bin/activate
pip3 install -r requirements.txt

If you get an error installing the requirements, try installing wheel first.

pip3 install wheel

 

 

Now you should have your container and the proper packages!

 

 

Connect to Your Database

The final step is to connect our app to our database. The database should be running on the same server, so all we need to do is supply it with the proper username and password. Note that all paths will be relative to you being in /var/www/app/app

The first file we will edit is models/Connection.py:
nano models/Connection.py

In the file, you will see a line (line 6) that looks like the following:
engine = create_engine("postgresql+psycopg2://catalog_app:schoolrocks@localhost/course_catalog", implicit_returning=True)

You will need to edit the username and password to match your database credentials. In the case above, catalog_app is my username and schoolrocks is my password. So edit those two values to match your credentials. If your database name is different, you can edit where it says course_catalog.

The final file we will edit is app.py:
nano app.py

The line we edit here will be similar:
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://catalog_app:schoolrocks@localhost/course_catalog'

You will similar edit the same values to adjust the username and password for the database.

After saving, Flask should now be able to talk to your database.

Think

SQLAlchemy is a package that will deeply connect our database and Python. We use sqlacodegen to generate Python objects (classes) for our various database objects so we can skip writing boilerplate code and custom classes to perform database actions. Take a look at the models/models.py file to see the generated objects. You can see how this is used in the various views/*.py files. You can see how sqlacodegen is used here: https://db.tannercrook.com/python-objects-from-database-for-sqlalchemy/

 

Seeing the Results

Well now everything is set. All we have left to do now is restart apache, and check it out!

sudo service apache2 restart

Now you can navigate to your server IP and view the website.
ex http://10.0.0.60

 

 

Run With It

This is just a very small glimpse into the whole process. I hope that from here, you will begin to explore on your own and discover software of your own.

Your app.bak directory will provide you with a blank slate for Flask that will allow you to repurpose it into something of your own creation. You can then use the example laid out to connect to your own database, and implement it.

Ponder

I hope that you can take it, and run with it.

“Know ye not that they which run in a race run all, but one receiveth the prize? So run, that ye may obtain.” – 1 Corinthians 9:24

 

 

Designing and Building a Database

Instructions

You should have a development environment all set up. Now we will be setting up our database and building the necessary structures for our database.

 

 

Create a PostgreSQL Database

The first thing we need to do is create our database where we will house all of our structures. When we installed postgreSQL, a new user was automatically created that has rights to access the database. So we will switch to that user:

First, switch to root:
sudo su
Then switch to postgres
su postgres

While we could create the database and user with SQL, we will use PostgreSQL utilities to let it do it for us:

Create the database:
createdb course_catalog

We want a user that is specific to the one piece of software for security reasons. So we create a user:
createuser catalog_app
We need to give permissions to the user, so we will log into the psql utility:
psql
And then connect to the course_catalog database:
\connect course_catalog
Set (or change) our users password:
ALTER USER catalog_app WITH PASSWORD 'somesecurepassword';
Finally, grant all privileges on the database to our user:
GRANT ALL PRIVILEGES ON DATABASE course_catalog TO catalog_app;

We can test that everything worked correctly by disconnecting from our postgres user and connecting with our new catalog_app user:

Quit the psql utility:
\q
Exit the postgres user:
exit
Exit the root user:
exit

Now, attempt to connect with our new info:
psql course_catalog catalog_app
You should get an error here. This is because we have not loosened the security to allow logins in this method.

We can change the postgreSQL config to allow this:

Edit the config file:
sudo nano /etc/postgresql/12/main/pg_hba.conf
Use your arrow keys to scroll clear to the bottom and then look for the following line:

local   all             all                                peer

And change it so peer is md5 Like the following:

Screenshot

 

Save and exit the file:
ctrl+o
ctrl+x

If you want to connect to postgres from another computer (like your host with an IDE) you will need to make additional config changes.

sudo nano /etc/postgresql/12/main/pg_hba.conf
and add the following line at the end:

host   all             all              0.0.0.0/0                  peer

Save and exit ctrl+o ctrl+x
sudo nano /etc/postgresql/12/main/postgresql.conf
and uncomment and change the following line:

listen_address = '*'

Save and exit ctrl+o ctrl+x

Restart postgresql to reload with the new configuration:
sudo service postgresql restart

Now, we should be able to login with our credentials:
psql course_catalog catalog_app
Enter the password you used previously and you should be logged in!

You can exit the psql utility at any time by using the \q command.

 

 

Creating the Database

You can get the database creation script from here: https://github.com/tannercrook/Flask-LAMP/blob/master/course_catalog_init.sql

You can download it directly in your server using the terminal:
wget http://tannercrook.com/downloads/course_catalog_init.sql

Then, we can execute the script in psql:
psql course_catalog catalog_app Login using specifying the database and user.
\i course_catalog_init.sql
If everything went well, you can exit psql \q

 

 

Think

I would strongly encourage you to review the database design and structure. While you are doing so, consider how you might change design if courses were managed at the district level, rather than at the school level.

Preparing for Python Integration

We need to make sure we have the proper software to connect to our database with Python.
sudo apt install postgresql-server-dev-12

 

 

 

 

 

 

Building a Flask Foundation

Resources

Not exactly how we do it, but there are additional resources here:
https://www.digitalocean.com/community/tutorials/how-to-deploy-a-flask-application-on-an-ubuntu-vps

Flask Documentation
https://flask.palletsprojects.com/en/1.1.x/

Great book. While it is not free, you can find it in Humble Bundles for a great deal if you watch.
https://www.amazon.com/dp/B07B8DCCN7/ref=cm_sw_em_r_mt_dp_U_ocMGEb8X434MT

Instructions

At this point, you should already have a LAMP stack set up. If you don’t, you can follow my guide here: LAMP Stack on Ubuntu Server

Now that we have our LAMP stack as a foundation, we are going to start building our application layer. Our application layer is going to utilize Flask for the web framework. Flask is Python based and really modular, so it works well for projects of all sizes. It also has a lot of support and libraries which makes the development process much faster.

Flask Directory

To start, we are going to build our Flask directory structure.

Navigate to the apache web folder
cd /var/www
Create a new directory for our project
sudo mkdir app
Assign your user as the owner, and make www-data the group (so it has web access) *Be sure to change my username to yours.
sudo chown tannercrook:www-data app
Navigate into our project’s directory
cd app

We are going to use this as our base hub. This is an area that can hold files that will be separate from the publicly available resources. For example, we may put a database connection file here containing our database credentials for the app to use. Obviously, we don’t want the internet to have access to this file.

So, let’s build our Flask folder and structure:

Build the flask app directory and move into it
mkdir app
cd app
Build the various directories for Flask
mkdir -p models static/styles templates
Build our main app python file
touch app.py

We should now have our base structure for Flask:
/var/www/app/app
├── app.py
├── models
├── static
│   └── styles
└── templates

 

Our Flask Application

Now we need to set up our app.py file so we can configure and test Apache.

* We have already generated a secret key, but if you want to generate one of your own (if you host your own) you can generate it by using the instructions here.


from flask import Flask 
from flask import render_template

app = Flask(__name__)
app.config['SECRET_KEY'] = '\xd2\x04S4\xbc\xce\xe2\x17\xfb\xff\x19C@\xa6e\xc2\xf4\x18\xad\xe8\xc4\xcb'


@app.route('/')
def index():
    return 'Hello, World!'

Place the following code of your empty app.py file using either FTP and your editor or the command line (editing with nano):
nano app.py
To paste: ctrl+shift+v
To save: ctrl+o

Once that is saved, we need to set up our python environment.
python3 -m venv venv
This will create a container for python so we can install components that won’t interfere with other parts of the system. Or if you wanted to have multiple Flask apps running, you don’t have to worry about interference between the two.

To activate our environment
source venv/bin/activate
And you should now see (venv) at the beginning of your prompt.

Now we can install the python dependencies we used in our app.py file.
pip3 install wheel
pip3 install flask

Once we have done that, our Flask directory is set up!

 

Apache and WSGI Configuration

For our webserver, we are using Apache which will direct to WSGI to serve flask URL.

First, we need to set up our WSGI config file in a main directory
cd ..
pwd
Should return /var/www/app

Let’s create the file:
touch app.wsgi
nano app.wsgi


#!/usr/bin/python
import sys
import logging
logging.basicConfig(stream=sys.stderr)
sys.path.insert(0,"/var/www/app/app")

from app import app as application
application.secret_key = '\xd2\x04S4\xbc\xce\xe2\x17\xfb\xff\x19C@\xa6e\xc2\xf4\x18\xad\xe8\xc4\xcb'

WSGI is now ready to go! Now we just need to configure Apache to point to our Flask configuration.

Change permissions on our files
sudo chown -R tannercrook:www-data app
sudo chmod -R 774 /var/www/app
Let’s install the Apache WSGI mod
sudo apt install libapache2-mod-wsgi-py3
Ensure it is enabled
sudo a2enmod wsgi

We will replace the existing default website config with our new config.
sudo nano /etc/apache2/sites-available/000-default.conf

 


<VirtualHost *:80>
        ServerAdmin webmaster@local

        # Build WSGI for Flask
        WSGIDaemonProcess app python-home=/var/www/app/app/venv
        WSGIScriptAlias / /var/www/app/app.wsgi process-group=app application-group=%{GLOBAL}

        <Directory /var/www/app/app/>
                Order allow,deny
                Allow from all
        </Directory>
        
        Alias /static /var/www/app/app/static
        <Directory /var/www/app/app/static/>
                Order allow,deny
                allow from all
        </Directory>

        ErrorLog ${APACHE_LOG_DIR}/error.log
        LogLevel warn

        CustomLog ${APACHE_LOG_DIR}/access.log combined
</VirtualHost>



 

Restart Apache
sudo service apache2 restart

If all went well, you should now be able to enter the IP Address of your server in a browser and see ‘Hello, World!’. This means that you have a working LAMP stack with Flask!

 

 

 

 

 

LAMP Stack on Ubuntu Server

Resources

The Linux Command Line by William Shotts
Free Ebook: http://www.linuxcommand.org/tlcl.php/

Instructions

At this point you should already have a functioning Ubuntu Server up and running. Now we will configure it to have a LAMP stack!

Our LAMP stack will consist of the following:

  • Linux (Ubuntu Server 18.04)
  • Apache
  • PostgreSQL (instead of MySQL)
  • Python (Flask)

The Linux step of LAMP

The first thing we want to do is verify that our server is up-to-date. Note: When prompted for a password in the terminal, it will not show typing, but it is working. Simply type your password and hit enter.
sudo apt update will make sure our repository is up to date so we pull from the correct locations.
sudo apt upgrade will upgrade the software on our server to the newest versions.

Once that finishes, we are all set for the Linux step!

Installing Apache

Apache is software that allows us to run a webserver to deliver files via http(s). In more basic terms, it is a piece of software that will allow us to host our own website, albeit for now on our own network only.

The beautiful part about Linux is how easy it is to install software. Many people shy away from using a terminal because initial responses are usually that it is complex; however, you will find that it is simple and the quickest method.

To install Apache simply execute the command:
sudo apt install apache2

We can verify that everything is working correctly by testing the default webpage. First, we need to find the ip address of our server.

Find the IP address of your server:
ip address

The results will include 2 adapters, the first will be 127.0.0.1 for your internal loopback address. The second is the IP address of your server and should be an IP address in your own network like 192.168.0.X or 10.0.0.X. Take note of the address.

To test our webserver, simply put that IP address into the address bar of the browser of your choice. If you get the following webpage, you are working!

Apache Default Page

 

Installing PostgreSQL

PostgreSQL will be the database we use for our stack. I prefer PostgreSQL as it is a free, open-source product that has all of the features of the most advanced database systems. It is growing in popular among developers and businesses.

To install PostgreSQL
sudo apt install postgresql postgresql-contrib

We will need to do more to setup and configure Postgre for our project. However, for now we just needed it installed. So we are all set for the database portion of our LAMP stack!

 

Installing Python

The last part of our LAMP stack will be Python. Ubuntu Server 18.04+ should have Python3 already installed.

To verify that it is installed
sudo apt install python3

We also need a few extra python components.
sudo apt install python3-venv
sudo apt install python3-pip

And just like that we have our Python components ready.

 

The LAMP Stack Base

Our LAMP stack is all installed and ready but there still is quite a bit of configuring to do.  This is a good base though depending upon which technologies you are using for your project. In our case, we will finish configuring our components as we start building and tying the system together.

 

Bonus: FTP

If  you want FTP on your server, so you can edit files on your host machine with an editor of your choice, you can install it pretty easily.

Install vsftp
sudo apt install vsftpd

Configure it so it is write enabled
sudo nano /etc/vsftpd.conf
Uncomment the line:

write_enable=YES

Restart the service:
sudo service vsftpd restart

Then you  should be able to connect to your server using your credentials using the FTP tool of your choice (Like Filezilla).

 

 

 

 

 

 

 

 

 

Ubuntu Server as Virtual Machine

Resources

VirtualBox (https://www.virtualbox.org/wiki/Downloads) or you can also use VMWare Player or VMWare Workstation or Fusion if you have one of those.

Download the latest LTS Ubuntu Server (20.04): https://releases.ubuntu.com/20.04.1/ubuntu-20.04.1-live-server-amd64.iso

Instructions

Option 1: VMware Player

  1. Download the Ubuntu Server ISO (listed at top of this page).
  2. Select Create a new virtual machine
  3. Select Use ISO image and browse and select the .iso you downloaded. Press ‘Next’.
  4. Enter your name, username, and password. Select ‘Next’.
  5. Choose a location to save your VM files. Select ‘Next’.
  6. Leave storage size at least 20GB. Choose option to store as single file. Select ‘Next’.
  7. Choose ‘Customize Hardware’.
    1. Select ‘Memory’ and set to 2048mb (2GB).
    2. Select ‘Network Adapter’ and select radial ‘Bridged’.
    3. Select ‘Close’.
  8. Select ‘Finish’.

You can now skip to the Ubuntu Installation section.

Option 2: VirtualBox

  1. Install VirtualBox and Download the Ubuntu Server ISO.
  2. In VirtualBox, select Machine > New from the top menu to build a new Virtual Machine:
    1. Screenshot from 2020-03-30 09-42-18
    2. On the next screen, designate either 1024mb or 2048mb of RAM for the server. (Preferably 2GB but if your computer has low RAM, use 1GB)
    3. On the next screen, select to ‘Create a virtual hard disk now’ and then ‘Virtualbox Disk Image’
    4. On the next screen, select ‘Dynamically allocated’
    5. On the next screen, set the disk size to 15GB.  You can set more if you would like, but it is very lean.
  3. We have the container for our virtual machine made. Now we need to put the installation media into our VM so we can boot from it and install.
    1. In the VirtualBox Manager window, select your newly created VM and select the Settings button (or you can right click it and select Settings)
    2. In the resulting screen, select the Storage tab on the left.
    3. Select the optical drive under Controller: IDE
    4. In the Attributes section, click the CD image next to the drop down menu, and use the option to Choose a file to browse to the downloaded Ubuntu Server ISO file.
    5. Check the box for ‘Live CD/DVD’
    6. The result should look like the following:
      1. Screenshot from 2020-03-30 10-11-25
  4. We also want to set up the Virtual Machine’s network so we can see it from our host computer. While still in the Settings window:
    1. Select the Network tab on the left.
    2. On the Adapter 1 tab, change the ‘Attached to: ‘ from NAT to Bridged Adapter
  5. Select the OK button to save our changes.

 

Installing Ubuntu Server

  1. In the VM Manager window, select the Server VM we created, and select Start.
  2. Install Ubuntu Server using the installation guide *Note that the terminal is text-only input. You cannot use a mouse and will use the arrow keys to navigate. Press enter to select. *
    1. Select your language
    2. Select ‘Update to the new installer’
    3. Select your keyboard configuration
    4. On the Network Connections page, select Done to accept defaults
    5. On the Configure Proxy page, select Done to skip
    6. On the Configure Ubuntu Archive Mirror page, select Done to accept defaults
    7. On the Guided Storage Configuration page, ensure Use an entire disk is select, then arrow down and select Done
    8. On the Storage Configuration, select Done to confirm and then select Continue to confirm again.
    9. On the Profile setup screen, enter your information and select Done:
      1. Screenshot from 2020-03-30 10-36-10
    10. On the SSH Setup page, check the box for Install OpenSSH server and select Done.
    11. On the Featured Server Snaps, select Done to skip.
    12. At this point, the operating system will install and update. Wait until it finishes and the option will say ‘Reboot’.
  3. After selecting ‘Reboot’, it will begin to reboot but will show ‘Please remove the installation medium’.
    1. In the VirtualBox Manager window (the one that lists all your virtual machines) Right click the VM and select Close > Power off. Confirm to Power off.
    2. Select the Settings button.
    3. Select Storage in the left panel.
    4. Under the Controller: IDE select the ubuntu-18.04 cd.
    5. Select the cd image next to the drop down menu and select ‘Remove disk…’
    6. Select OK
  4. Start the Virtual Machine again.
    1. It will do some initial setup, press the return key once it pauses to access the login prompt
      1. Screenshot from 2020-03-30 10-47-02
    2. Enter your username
    3. Enter your password * Note it will not show any indication of typing for security purposes. Type the password and hit enter. *
    4. Once your logged in, you should see a screen like the following:
      1. Screenshot from 2020-03-30 10-49-03

 

Congratulations! You have a Ubuntu Server virtual machine running!