# Get Started

## Requirements

We will need to install python3.6 with sqlite3 support.

This can be done using the system package management, downloaded from [http://www.python.org](), or compiled from sources.

- Installing from the system package management:
    - OSX: `port install python36`
    - FreeBSD: `pkg install python36 py36-sqlite3`
    - Linux: `apt-get install ???`

- Installing from sources:

    - Download from [http://www.python.org]()
    - `unxz Python-3.6.tar.xz`

    - `tar xvf Python-3.6.tar`

    - `cd Python-3.6`

    - `./configure --prefix=$HOME/.local/bin`

    - `make && make install`

This will install python locally under `~/.local/bin`. Make sure to add it to your `PATH` (edit `~/.profile` in OSX or FreeBSD).

Next install pip (if not yet installed):

    python3.6 -m ensurepip --user

This will install pip in your account under `~/.local/bin`.

In the end you should be able to run `pip3 --version` and `python3 -c "import sqlite3"` without errors (sometimes `pip3` is `pip`, `pip3.6` or `pip-3.6`).

Install additional python packages locally on the user area:

    pip3 install --user \

        tornado \

        sqlalchemy \

        pyyaml \

        pygments \

        markdown \

        bcrypt \
        networkx

These are usually installed under

- OSX: `~/Library/python/3.6/lib/python/site-packages/`
- Linux/FreeBSD: `~/.local/lib/python3.6/site-packages/`

Note: If you want to always install python modules on the user account, edit the pip configuration file `~/.config/pip/pip.conf` (FreeBSD, Linux) or `Library/Application Support/pip/pip.conf` (OSX) and add the lines

    [global]
    user = yes

## Installation

Replace USER by your bitbucket username:

    cd path/to/some/directory
    git clone https://USER@bitbucket.org/USER/aprendizations.git

A directory `aprendizations` will be created with the software:

    cd aprendizations

## Configuration

### Database

First we need to create a database:

    ./initdb.py                 # initialize user `0` and empty password

    ./initdb.py --pw alibaba    # initialize user `0` and given password

    ./initdb.py --help          # for the available options

### SSL Certificates

#### Selfsigned

We also need certificates for https. Generate selfsigned certificates using openssl:

    openssl req -x509 -newkey rsa:4096 -keyout key.pem -out cert.pem -days 365 -nodes

and place them in `aprendizations/certs`.

#### LetsEcrypt

    sudo pkg install py27-certbot       # FreeBSD

Shutdown any server running and the firewall, and then run the script to generate the certificate:

    sudo pfctl -d                                   # disable pf firewall

    sudo certbot certonly --standalone -d bit.xdi.uevora.pt

    sudo pfctl -e; sudo pfctl -f /etc/pf.conf       # enable pf firewall

Certificates are saved under `/usr/local/etc/letsencrypt/live/bit.xdi.uevora.pt/` which is readable only by root.

Copy them to `aprendizations/certs` with names `cert.pem` and `key.pem`. And change permissions to be readble (FIXME how to do it with security?)

### Testing

Run a demonstration:

    ./serve.py demo/demo.yaml

and open a browser at `https://127.0.0.1:8443`.

### Firewall configuration

Ports 80 and 443 are only usable by root. For security reasons it is better to run the server as a regular user on higher ports like 8080 for http and 8443 for https. In this case, we should configure port forwarding in the firewall to redirect incoming tcp traffic from 80 to 8080 and 443 to 8443.

#### FreeBSD and pf

Edit `/etc/pf.conf`:

    ext_if="em0"

    rdr on $ext_if proto tcp from any to any port 80 -> 127.0.0.1 port 8080

    rdr on $ext_if proto tcp from any to any port 443 -> 127.0.0.1 port 8443

or `ext_if="vtnet0"` for guest additions under virtual box.

Edit `rc.conf`

    pf_enable="YES"

    pf_flags=""

    pf_rules="/etc/pf.conf"

    # optional logging:
    pflog_enable="YES"

    pflog_flags=""

    pflog_logfile="/var/log/pflog"

Reboot.

## Troubleshooting

#### UnicodeEncodeError

The server should not generate this error, but when using external scripts to generate questions or to correct, these scripts can print unicode strings to stdout. If the terminal does not support unicode, python will generate this exception.

To correct in FreeBSD, edit `~/.login_conf` to use UTF-8, for example:

    me:\

            :charset=UTF-8:\

            :lang=en_US.UTF-8:

## Useful sqlite3 queries

Which students have already done at least one topic?

    sqlite3 students.db "select distinct student_id from studenttopic"


How many topics have done each student?

    sqlite3 students.db "select student_id, count(topic_id) from studenttopic group by student_id"


What questions have more wrong answers?

    sqlite3 students.db "select count(ref), ref from answers where grade<1.0 group by ref order by count(ref) desc"