MySQL is a relational database primarily used for SCORM tracking and reporting data for enablement resources. Included are tables for other features such as tracking video pause/resume.
These instructions describe how to connect to the MySQL server, establish the enablement database, and populate the database with initial data.
Before configuring MySQL for Communities’ enablement feature, be sure to
MySQL should be downloaded and installed following the instructions for the target OS.
As SQL is case insensitive, for case sensitive operating systems, it is necessary to include a setting to lower case all table names.
For example, to specify all lower case table names on a Linux OS:
/etc/my.cnf
[mysqld]
section, add the following line: lower_case_table_names = 1
To provide better multilingual support, it is necessary to use the UTF8 character set.
Change MySQL to have UTF8 as its character set:
Change the MySQL database to default to UTF8:
/etc/my.cnf
[client]
section, add: default-character-set=utf8
[mysqld]
section, add: character-set-server=utf8
MySQL Workbench provides an UI for executing SQL scripts which install the schema and initial data.
MySQL Workbench should be downloaded and installed following the instructions for the target OS.
When the MySQL Workbench is first launched, unless already in use for other purposes, it will not yet show any connections:
MySQL Connections
.Setup New Connection
, enter values appropriate for your platform for demonstration purposes, with the author AEM instance and MySQL on the same server:
Enablement
Standard (TCP/IP)
127.0.0.1
root
no password by default
leave blank
Test Connection
to verify the connection to the running MySQL service.Notes:
3306
.Connection Name
chosen is entered as the datasource
name in JDBC OSGi configuration.Upon opening the new Enablement connection, notice there is a test schema and default user accounts.
The SQL scripts are obtained using CRXDE Lite on the author instance. The SCORM package must be installed:
/libs/social/config/scorm/
folderdatabase_scormengine.sql
database_scorm_integration.sql
One method for downloading the schema is to:
jcr:content
node for the sql file.jcr:data
property is a view link.The Enablement SCORM Database to be created is:
ScormEngineDB
database_scormengine.sql
database_scorm_integration.sql
Be sure to install the schema before installing the data.
If the database name is changed, be sure to specify it correctly in:
In the MySQL Workbench
Open SQL Script ...
database_scormengine.sql
database_scorm_integration.sql
In the Workbench window for the file opened in Step 1, select the lightening (flash) icon
to execute the script.
Note that the execution of the database_scormengine.sql
script to create the SCORM database may take a minute to complete.
Once the scripts are executed, it is necessary to refresh the SCHEMAS
section of the Navigator
in order to see the new database. Use the refresh icon to the right of ‘SCHEMAS’:
After installing and refreshing SCHEMAS, the scormenginedb
will be visible.
The OSGi configuration for Day Commons JDBC Connections Pool configures the MySQL JDBC Driver.
All publish and author AEM instances should point to the same MySQL server.
When MySQL runs on a server different from AEM, the server hostname must be specified in place of ‘localhost’ in the JDBC connector (which populates the ScormEngine config).
On each author and publish AEM instance
Signed in with administrator privileges
Access the web console
Locate the Day Commons JDBC Connections Pool
Select the +
icon to create a new configuration
Enter the following values:
com.mysql.jdbc.Driver
jdbc:mysql://localhost:3306/aem63reporting
specify server in place of localhost if MySQL server is not the same as ‘this’ AEM server.Select Save.
The OSGi configuration for AEM Communities ScormEngine Service configures SCORM for an enablement community’s use of the MySQL server.
This configuration is present when the SCORM package is installed.
All publish and author instances point to the same MySQL server.
When MySQL runs on a server different from AEM, the server hostname must be specified in place of ‘localhost’ in the ScormEngine Serivce, which is typically populated from the JDBC Connection config.
On each author and publish AEM instance
Signed in with administrator privileges
Access the web console
Locate the AEM Communities ScormEngine Service
Select the edit icon
Verify the following parameter values are consistent with the JDBC Connection config:
jdbc:mysql://localhost:3306/ScormEngineDB
ScormEngineDB is the default database name in the SQL scriptsRegarding the following parameter:
Scorm User Password: DO NOT EDIT
For internal use only: It is for a special service user used by AEM Communities to communicate with the scorm engine.
Select Save
To ensure enablement courses work correctly in all browsers, it is necessary to add Mozilla as a User Agent that is not checked by the CSRF filter.
Login to the AEM publish instance with administrator privileges.
Access the web console
Locate Adobe Granite CSRF Filter
.
Select the edit icon.
Select the [+]
icon to add a Safe User Agent.
Enter Mozilla/*
.
Select Save.