SQL Integration

SQL Integration is an Enterprise only feature

Veracity LRS can synchronize your xAPI statements into an SQL database in real time. The LRS will open a connection to your SQL server, and flush out statements every 300 milliseconds. Statements are not normalized - the data we send into the SQL server is a one-to-one rendering of the incoming xAPI statements.

Veracity can connect to 

1. Maria Db,
   
2. MySQL
   
3. MSSql
   
4. PostgresSQL

Setup

Browse to the "all management tools" page for the LRS you wish to link to SQL.

Locate "Plugins" and click the link. 

On the following page, choose "Activate a plugin"

You may need to add a permission for the give LRS to use this plugin in the administrative tools.

Choose "SQL - Statements"

The plugin's configuration page will appear. On this page, enter the connection string to your database. 

See the connection string section for more info on connecting to your database. 

When the plugin is first installed, it will attempt to connect to the database. If the connsection is successful, you'll see a new menu item in the sidebar. 

If the connection cannot be established, error info will be displayed in the same location. 

If this occurs, correct the problem, then remove and re-add the plugin. If the problem is simply an error in the entry of the connection string, you can return to the plugin configuration and edit the string.

Once the connection is successful, click the "Setup DB" option. This will set up the initial tables. 

The plugin is now operational, and xAPI statements should be sent into your DB in real time. 

Schema

xAPI statements are extracted into a linked set of tables, joined by shared UUIDs. When the value of cell in a given table is a UUID, this represents 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. In the case where links are one to many, several different rows in the target table will contain the same UUID. This indicates 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 show ONLY the links between tables. Each table also contains regular data fields. Column names represent JSON paths. For compatibly with SQL queries, periods in JSON paths are replaced with underscores. 

Example SQL Queries

Get the basic xapi triple of actor, object, verb

1. SELECT 
2.     obj.id, ag.name, stmt.verb_id
3. FROM statements stmt
4. JOIN objects obj ON stmt.object = obj._id
5. JOIN agents ag ON stmt.actor= ag._id 
   

Object	User	Verb ID
https://www.coursera.org/specializations/python/1	Cathyleen Simonne	http://adlnet.gov/expapi/verbs/commented
https://www.coursera.org/specializations/python/1	Cathyleen Simonne	http://adlnet.gov/expapi/verbs/commented
https://www.coursera.org/specializations/python/1	Cathyleen Simonne	http://adlnet.gov/expapi/verbs/terminated
https://www.coursera.org/learn/neural-networks/1	Nerti Zandra	http://adlnet.gov/expapi/verbs/initialized
https://www.coursera.org/learn/neural-networks/1	Nerti Zandra	http://adlnet.gov/expapi/verbs/suspended
https://www.coursera.org/learn/neural-networks/1	Nerti Zandra	http://adlnet.gov/expapi/verbs/resumed

Get terminated statements where the scaled score is greater than 0.6. Return the agent 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.

1. SELECT 
2.    ag.name, stmt.verb_id, lg.value as Object_Title, res.score_scaled
3. FROM statements stmt
4. JOIN objects obj ON stmt.object = obj._id
5. JOIN agents ag ON stmt.actor= ag._id 
6. JOIN definitions def ON def._id = obj.definition
7. JOIN langMaps lg ON def.name = lg._id
8. JOIN results res ON stmt.result = res._id
9. where stmt.verb_id = "http://adlnet.gov/expapi/verbs/terminated"
10. and res.score_scaled > .6

User	Object English title	Score
Cathyleen Simonne	Programming for Everybody (Getting Started with Python)	0.99
Coral Bev	Reproducible Research	0.69
Coral Bev	Programming Languages, Part B Lesson	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 Lesson	0.98
Inga Phillie	The Data Scientist’s Toolbox	0.79

This query finds events where the score is greater than .5, and displays the student 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.

1. SELECT 
2.     ags.name as Student, 
3.     parentMap.value AS Course, 
4.     lessonMap.value AS Lesson,
5.     ress.score_scaled AS Score
6. FROM statements stmts 
7. JOIN contexts as ctxobj on stmts.contexts = ctxobj._id
8. JOIN objects as objs on stmts.object = objs._id
9. JOIN contextActivities as ctxparent  on ctxobj.contextActivities_parent = ctxparent._id
10. JOIN results as ress on stmts.result = ress._id
11. JOIN agents as ags on stmts.actor = ags._id
12. JOIN definitions as obj ON objs.definition = obj._id
13. JOIN langMaps as lessonMap ON obj.name = lessonMap._id
14. JOIN definitions as parent ON ctxparent.definition = parent._id
15. JOIN langMaps as parentMap ON parent.name = parentMap._id 
16. WHERE 
17.     ress.score_scaled > .5
18.     
19. ORDER BY Student, Course, Lesson;

Student	Parent Course	Object	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 (Getting Started with Python)	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: Hyperparameter tuning, Regularization and Optimization	0.57
Theresina Benedikta	Deep Learning	Improving Deep Neural Networks: Hyperparameter tuning, Regularization and Optimization	0.68
Theresina Benedikta	Deep Learning	Improving Deep Neural Networks: Hyperparameter tuning, Regularization and Optimization	0.51
Theresina Benedikta	Deep Learning	Neural Networks and Deep Learning	0.6
Theresina Benedikta	Deep Learning	Neural Networks and Deep Learning	0.54

Connection String Examples

1. postgres://user:pass@example.com:5432/dbname
   
2. mysql://username:password@localhost:3306/xapidemo
   
3. mssql://username:password@localhost/xapidemo
   
4. mariadb://username:password@localhost/xapidemo

• Related Articles

• LMS Integration

  The Veracity Learning offers seamless integration with your LMS to allow instructors and students to access the services of the LRS without leaving their LMS environment. This is accomplished via LTI (Learning Tools Interoperability), a standard from ...

• Elasticsearch Integration

  Overview The LRS can accelerate its dashboard and statement viewer by leveraging Elasticsearch in addition to MongoDB. When configured with the optional Elasticsearch connection, the LRS will synchronize xAPI statements in real time between these two ...

• Single Sign On (SSO) Integration

  Veracity can integrate with your single sign on provider using OpenID Connect or Security Assertion Markup Language (SAML). This integration allows your enterprise users to log into the LRS user interface without providing a password. Integrating a ...

• Versions of Veracity Learning

  Veracity Learning LRS comes in a few versions. The main version, and the one you're most likely looking at right now, is LRS.io. LRS.io This is our cloud hosted SaaS service, where anyone can create an LRS. You can use the free version, which is ...