PostgreSQL Exercises

This is the code for the SQL tutorial site PostgreSQL Exercises. It's made using a simple static site generator and some template html files.

Directories/Files under this project root:

• database - contains sample database configuration files, SQL file to create+populate pgexercises database.
• nginx - contains sample nginx configurations files.
• questions - contains the files that define the exercises. These are used to generate static html files based on the templates under site/website.
• scripts - various scripts to do stuff like generate the html files, upload them to the site, and so on.
• site - web site files and html templates.
• SQLForwarder - simple web application to send queries to and from the DB.
• dbdata.xlsx - just an excel file containing all the db data.

Instructions to get a dev environment working:

Install nginx, apache tomcat 7, PostgreSQL, ant, perl.

Set a password

Modify database/clubdata.sql and database/context.xml to set a password of your choosing

Set up your database

su - postgres
cd $PGEXERCISES_HOME/database
psql -f clubdata.sql

Set up nginx

• Copy nginx/nginx-dev.conf and nginx/mime.type to your nginx config directory (e.g. /etc/nginx/).
• Rename nginx-dev.conf to nginx.conf, and change the root location and SQLForwarder blocks to match your setup.

Configure SQL Forwarder servlet

• Modify the SQLForwarder/build.properties file to tell the build where to get the apache tomcat libraries
• Run 'ant war' on the SQLForwarder project to get a WAR file in SQLForwarder/build/war
• Copy war to $TOMCAT_HOME/webapps
• Copy the database/context.xml file to $TOMCAT_HOME/conf, and modify it appropriately to point at the server/port Postgres uses (default is 5432), use the user name and password you've set up, and so on.
• Copy the SQLForwarder/WebContent/WEB-INF/lib/postgresql-9.3-1100.jdbc4.jar file to $TOMCAT_HOME/lib

Run the pgexercises build

The following should build your html files in site/website/questions:

cd $PGEXERCISES_HOME/scripts
./processdocs.pl ../ 1

Start everything up

Start postgres, tomcat, and nginx if not already started. Hopefully you should be able to see a web page at your nginx port (default localhost:80)!