Clearing the Database

Clearing the Database

The time will inevitably come when there is simply "too much" data in the LRS. Exactly when you cross the "too much" line is up to your discretion, but we typically consider the 100,000 statement mark to be close.

There are two ways to accomplish this, but you should make sure the LRS has been paused during the procedure and restarted afterwards using:

sudo service lrs stop
(or)
sudo service lrs start

The Django Script

Using Django itself is probably the safest way to accomplish this.

Inside of ADL_LRS/adl_lrs/management/commands, there is a clear_models.py script that will attempt to clear out all data in the LRS app. Users themselves will not be affected. To run this, SSH into the LRS and do the following:

(cd to ADL_LRS)
> source ../env/bin/activate
> python manage.py clear_models

This option is hit or miss. If the LRS has <= 100,000 statements, then this might not be a problem. But, if the shell prints Killed then this method will not work.

The SQL Script

The most performant way to clear data is to edit the SQL tables directly. Create or place this script onto whichever machine contains the Postgres DB as clear.sql:

/* 
Script to clear out the ADL LRS
This script will remove all entries of xAPI data, leaving the Agents intact.
Those can be removed with another script.
*/
\echo "Clearing Statement objects ..."
DELETE FROM lrs_statement_context_ca_parent;
DELETE FROM lrs_statement_context_ca_category;
DELETE FROM lrs_statement_context_ca_grouping;
DELETE FROM lrs_statement_context_ca_other;
DELETE FROM lrs_statement;

\echo "Clearing Sub-Statement objects ..."
DELETE FROM lrs_substatement_context_ca_parent;
DELETE FROM lrs_substatement_context_ca_category;
DELETE FROM lrs_substatement_context_ca_grouping;
DELETE FROM lrs_substatement_context_ca_other;
DELETE FROM lrs_substatement;

\echo "Clearing xAPI components ..."
DELETE FROM lrs_verb;

DELETE FROM lrs_activity;
DELETE FROM lrs_activitystate;
DELETE FROM lrs_activityprofile;

DELETE FROM lrs_statementattachment;

\echo "Clearing OAuth cache ..."
DELETE FROM oauth_provider_nonce;
DELETE FROM oauth_provider_consumer;
DELETE FROM oauth_provider_token;

\echo "Clearing hooks ..."
DELETE FROM adl_lrs_hook;

You can then run this using:

sudo –u postgres psql –d lrs –f clear.sql

Given the relational structure of the DB configuration, you may receive messages indicating that certain table entries could not be deleted because they are referenced in other tables. As those definitions cannot be recursive, the caveman approach is to simply continue running the script until those messages stop appearing.

Alternatively, we may need to just rearrange the deletion order to make it more elegant, but repeating the script will work.