2 How to install Sqltutor

Sqltutor is a CGI script and a PostgreSQL database of SQL tutorials, collections of SQL questions and answers.

2.1 How to get Sqltutor

The project is hosted at the Savannah software forge

http://savannah.gnu.org/projects/sqltutor/

A copy of the Sqltutor can be get by anonymous GIT access

git clone git://git.sv.gnu.org/sqltutor.git

git clone git://git.sv.gnu.org/sqltutor/datasets.git

2.2 Installer script

Under most circumstances installing Sqltutor is a very simple process and takes only answering a few questions to be completed. The whole installation is guided by sqltutor-install.sh script found in Sqltutor git repository.

     $ ./sqltutor-install.sh

To run the script you need to have sudo installed and must be listed in the list of users which may execute sudo (sudoers). The user running the script needs also rights to create PostgreSQL databases and create and maintain database roles.

Installer calls script autogen.sh to create standard configure script, patches implicit settings and finally installs database, CGI script, info files and datasets and tutorials.

Figure 2.1: Installer script

2.3 How to change implicit settings

All Sqltutor database objects are defined in the SCHEMA sqltutor. Five output variables are defined in configure.ac with the following implicit values.

SQLTUTOR_DATABASE=sqltutor

PostgreSQL database name used by Sqltutor.

SQLTUTOR_WWW_USER=sqlquiz

WWW user for CGI script sqlutor with full access rights to all tables.

SQLTUTOR_PASSWORD=sqlkrok

Password for CGI script for the user SQLTUTOR_WWW_USER

SQLTUTOR_WWW_EXEC=sqlexec

Database role used for running queries entered by users. SQLTUTOR_WWW_EXEC is granted SLECT to datasets' tables and revoked all rigths on all other sqltutor tables.

SQLTUTOR_PASSEXEC=sqlkrok

Password for CGI script for the user SQLTUTOR_WWW_EXEC (implicitly the same password as for SQLTUTOR_WWW_USER).

These macros are used by all modules and you can change their implicit values if needed in the configure.ac file.

2.4 Creating the database

To create a database, the PostgreSQL server must be up and running. Database is created with SQL command

     CREATE DATABASE SQLTUTOR_DATABASE

Two database roles must be created for Sqltutor with SQL command CREATE ROLE

     CREATE ROLE SQLTUTOR_WWW_USER LOGIN;
     CREATE ROLE SQLTUTOR_WWW_EXEC LOGIN;

To set passwords for these new roles run psql and enter SQL ALTER command

     ALTER USER SQLTUTOR_WWW_USER WITH PASSWORD 'xxx';
     ALTER USER SQLTUTOR_WWW_EXEC WITH PASSWORD 'yyy';

or passwords can be set directly when creating roles

     CREATE ROLE SQLTUTOR_WWW_USER PASSWORD 'xxx' LOGIN;
     CREATE ROLE SQLTUTOR_WWW_EXEC PASSWORD 'yyy' LOGIN;

If PostgreSQL language is not defined in datatabase template1 you must create it explicitly in the Sqltutor database

     su -
     su - postgres
     psql SQLTUTOR_DATABASE
     CREATE LANGUAGE plpgsql;

2.5 Building and installing Sqltutor

To create all Makefiles needed for building binary CGI script sqltutor, populating Sqltutor database and making info manual go to Sqltutor home directory and run

        ./autogen.sh

to create a configure script (if it is not allready present) and then

        ./configure --bindir=/usr/lib/cgi-bin [ --infodir=/usr/share/info ]

Parameter --bindir defines to which directory CGI script sqltutor will be installed. For general information on using GNU autotool see the standard Basic Installation instructions in the INSTALL.

Sqltutor CGI binary with info manual is installed and the database is populated by running

     make DESTDIR=install_root_directory install

from the main source directory. Parameter DESTDIR is optional and defines a root directory into which binary sqltutor and sqltutor.info will be installed. This parameter is needed if you do not have access ritght for writing to /usr/local/cgi-bin and /usr/share/info (implicit values).

2.6 Populating tutorials and datasets

Tutorials and dataset are stored in a separate git repository

git clone git://git.sv.gnu.org/sqltutor/datasets.git

Because configure script is not included in the repository, you have to create it first

     $ ./autogen

Then run

     $ ./configure
     $ make install

and that's all. Datasets and tutorial can be reinstalled as many times as needed.

To enable postgis extension, you must create geometry type in your database (sqltutor in the following example)

     $ su
     # su postgres
     $ psql -d sqltutor -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
     $ psql -d sqltutor -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql

and explicitly enable postgis tutorials

     $ ./configure --enable-postgis
     $ make install

PostGIS geometry type must be created by a superuser, because normal users do not have permissions to create C procedures in a database.