SQL Integration

SQL Integration

Notes
Only the Enterprise version of the LRS supports SQL Integration.
The Veracity LRS can send xAPI statements to a SQL database in real time. The LRS opens a connection to your SQL server, and synchs statements every 300 milliseconds. The LRS won’t convert the data — it sends a one-to-one rendering of the incoming xAPI statements to the SQL server.

Veracity can connect to:
  1. MariaDB
  2. Microsoft SQL Server
  3. MySQL
  4. PostgreSQL

Setup

  1. In the store you want to link to SQL, in the left menu, select Management > All Management Tools.

  2. Scroll down to the Integrations and Extensions section and click Plugins.

  3. On the Manage Plugins page, click the Activate a Plugin button.

    NOTE: The LRS administrator must give you the privilege to use this tool.



  4. Choose SQL – Statements.



  5. In the plugin's configuration page, enter the Connection String to your database.



    Here are some examples of a typical Connection String for the four databases:
    1. mariadb://username:password@localhost/xapidemo
    2. mssql://username:password@localhost/xapidemo
    3. mysql://username:password@localhost:3306/xapidemo
    4. postgres://user:pass@example.com:5432/dbname

  6. The first time you install the plugin, the LRS will try to connect to the database. If the connection is successful, then you'll see a new menu item in the sidebar.



    If the LRS can’t connect, then it’ll show an error message instead. If the problem is simply a typo in the connection string, then go back to the plugin’s configuration page and edit the string. For all other errors, remove and re-add the plugin.


  7. Once the connection is successful, click the Setup DB option. This will set-up the initial tables.
The plugin is now operational, and the LRS should start sending xAPI statements to your SQL database in real time.

Schema



The LRS extracts xAPI statements into a linked set of tables, joined by shared UUIDs. If the value of cell in a table is a UUID, then it’s a link to another table where the _id row in the target table contains the given UUID in the source table. Links may be one-to-one or one-to-many. If links are one-to-many, then several different rows in the target table will contain the same UUID. This shows that the field of the source table is an array, and each field with the given UUID in the target table is a member of that array. The above table shows only the links between tables. Each table also contains regular data fields. Column names represent JSON paths. For compatibly with SQL queries, the LRS replaces periods in JSON paths with underscores.

Example SQL Queries

Example 1
Get the basic xAPI triplet of actor, object, and verb. 
SELECT
    obj.id, ag.name, stmt.verb_id
FROM statements stmt
JOIN objects obj ON stmt.object = obj._id
JOIN agents ag ON stmt.actor= ag._id

Objects
Agents
Verb ID
…specializations/python/1 Cathyleen Simonne …commented
…specializations/python/1 Cathyleen Simonne …commented
…specializations/python/1 Cathyleen Simonne …terminated
…learn/neural-networks/1 Nerti Zandra …initialized
…learn/neural-networks/1 Nerti Zandra …suspended
…learn/neural-networks/1 Nerti Zandra …resumed

Example 2
Get terminated statements where the scaled score is greater than 0.6. Return the agent’s name, the object title, and the score. This query is somewhat more complex, as it must link to the language map table to find the display name of the object. 
SELECT
    ag.name, stmt.verb_id, lg.value AS Object_Title, res.score_scaled
FROM statements stmt
JOIN objects obj ON stmt.object = obj._id
JOIN agents ag ON stmt.actor = ag._id
JOIN definitions def ON def._id = obj.definition
JOIN langMaps lg ON def.name = lg._id
JOIN results res ON stmt.result = res._id
WHERE
    stmt.verb_id = "http://adlnet.gov/expapi/verbs/terminated"
    AND res.score_scaled > .6
Agents
Object Definition Name
Score
Cathyleen Simonne Programming for Everybody… 0.99
Coral Bev Reproducible Research 0.69
Coral Bev Programming Languages, Part B… 0.83
Coral Bev Using Python to Access Web Data 0.81
Coral Bev Regression Models 0.82
Coral Bev Exploratory Data Analysis 0.91
Inga Phillie Programming Languages, Part B… 0.98
Inga Phillie The Data Scientist’s Toolbox 0.79

Example 3
This query finds events where the score is greater than 0.5 and displays the student’s name and both the lesson display name and the parent course display name. It is somewhat more complex as it must join to the language map table twice, once for the course and once for the lesson. 
SELECT
    ags.name AS Student,
    parentMap.value AS Course,
    lessonMap.value AS Lesson,
    res.score_scaled AS Score
FROM statements stmts
JOIN contexts AS ctxobj ON stmts.contexts = ctxobj._id
JOIN objects AS objs ON stmts.object = objs._id
JOIN contextActivities AS ctxparent ON ctxobj.contextActivities_parent = ctxparent._id
JOIN results AS ress ON stmts.result = ress._id
JOIN agents AS ags ON stmts.actor = ags._id
JOIN definitions AS obj ON objs.definition = obj._id
JOIN langMaps AS lessonMap ON obj.name = lessonMap._id
JOIN definitions AS parent ON ctxparent.definition = parent._id
JOIN langMaps AS parentMap ON parent.name = parentMap._id
WHERE
    res.score_scaled > .5
ORDER BY Student, Course, Lesson;

Student
Course
Lesson
Score
Thelma Cherey Deep Learning Neural Networks and Deep Learning 0.88
Thelma Cherey Deep Learning Sequence Models 0.58
Thelma Cherey Deep Learning Structuring Machine Learning Projects 0.57
Thelma Cherey Learn to Program and Analyze Data with Python Programming for Everybody… 0.58
Thelma Cherey Learn to Program and Analyze Data with Python Python Data Structures 0.93
Theresina Benedikta Deep Learning Improving Deep Neural Networks… 0.57
Theresina Benedikta Deep Learning Improving Deep Neural Networks… 0.68
Theresina Benedikta Deep Learning Improving Deep Neural Networks… 0.51
Theresina Benedikta Deep Learning Neural Networks and Deep Learning 0.6
Theresina Benedikta Deep Learning Neural Networks and Deep Learning 0.54