# Browseable MySQL session backend

## Prerequisites

First, make sure you have installed the `DBD::mysql` perl module.

On Debian-based distributions

    apt install libdbd-mysql-perl

On Fedora-based distributions

    yum install 'perl(DBD::mysql)'

## Create database schema

Create the following tables. You may skip the session types you are not going to use, but you need at least `sessions` and `psessions`

    CREATE TABLE sessions (
        id varchar(64) not null primary key,
        a_session text,
        _whatToTrace varchar(64),
        _session_kind varchar(15),
        ipAddr varchar(64),
        _utime bigint,
        _httpSessionType varchar(64),
        user varchar(64)
    ) DEFAULT CHARSET utf8;
    CREATE INDEX i_s__whatToTrace ON sessions (_whatToTrace);
    CREATE INDEX i_s__session_kind ON sessions (_session_kind);
    CREATE INDEX i_s__utime ON sessions (_utime);
    CREATE INDEX i_s_ipAddr ON sessions (ipAddr);
    CREATE INDEX i_s__httpSessionType ON sessions (_httpSessionType);
    CREATE INDEX i_s_user ON sessions (user);

    CREATE TABLE psessions (
        id varchar(64) not null primary key,
        a_session text,
        _session_kind varchar(15),
        _httpSessionType varchar(64),
        _whatToTrace varchar(64),
        ipAddr varchar(64),
        _session_uid varchar(64)
    )  DEFAULT CHARSET utf8;
    CREATE INDEX i_p__session_kind ON psessions (_session_kind);
    CREATE INDEX i_p__httpSessionType ON psessions (_httpSessionType);
    CREATE INDEX i_p__session_uid ON psessions (_session_uid);
    CREATE INDEX i_p_ipAddr ON psessions (ipAddr);
    CREATE INDEX i_p__whatToTrace ON psessions (_whatToTrace);

    CREATE TABLE samlsessions (
        id varchar(64) not null primary key,
        a_session text,
        _session_kind varchar(15),
        _utime bigint,
        ProxyID varchar(64),
        _nameID varchar(128),
        _assert_id varchar(64),
        _art_id varchar(64),
        _saml_id varchar(64)
    )  DEFAULT CHARSET utf8;
    CREATE INDEX i_a__session_kind ON samlsessions (_session_kind);
    CREATE INDEX i_a__utime ON samlsessions (_utime);
    CREATE INDEX i_a_ProxyID ON samlsessions (ProxyID);
    CREATE INDEX i_a__nameID ON samlsessions (_nameID);
    CREATE INDEX i_a__assert_id ON samlsessions (_assert_id);
    CREATE INDEX i_a__art_id ON samlsessions (_art_id);
    CREATE INDEX i_a__saml_id ON samlsessions (_saml_id);

    CREATE TABLE oidcsessions (
        id varchar(64) not null primary key,
        a_session text,
        _session_kind varchar(15),
        _utime bigint
    )  DEFAULT CHARSET utf8;
    CREATE INDEX i_o__session_kind ON oidcsessions (_session_kind);
    CREATE INDEX i_o__utime ON oidcsessions (_utime);

    CREATE TABLE cassessions (
        id varchar(64) not null primary key,
        a_session text,
        _session_kind varchar(15),
        _utime bigint,
        _cas_id varchar(128),
        pgtIou varchar(128)
    ) DEFAULT CHARSET utf8
    CREATE INDEX i_c__session_kind ON cassessions (_session_kind);
    CREATE INDEX i_c__utime        ON cassessions (_utime);
    CREATE INDEX i_c__cas_id       ON cassessions (_cas_id);
    CREATE INDEX i_c_pgtIou        ON cassessions (pgtIou);

## LemonLDAP::NG configuration

Go in the Manager and set the session module to `Apache::Session::Browseable::PgJSON` for each session type you intend to use:

- `General parameters` » `Sessions` » `Session storage` » `Apache::Session module`

- `General parameters` » `Sessions` » `Persistent sessions` » `Apache::Session module`

- `CAS Service` » `CAS sessions module name`

- `OpenID Connect Service` » `Sessions` » `Sessions module name`

- `SAML2 Service` » `Advanced` » `SAML sessions module name`

Then, set the following module options:

Required parameters |  |
--- | --- | ---
Name | Comment | Example
DataSource | The DBI string | dbi:mysql:database=lemonldap-ng
UserName | The database username | lemonldapng
Password | The database password | mysuperpassword
TableName | Table name (optional) | sessions
Index | Fields to index | refer to List of fields to index by session type